C# Convert Excel .xlsx columns to JSON using oledb, is this still the best way to proceed?

Question

I have 2 populated columns in Excel Latitude and Longitude. I would like to combine these columns for each row and convert to JSON looking something like this example "{\"Locations\" : [[50.452603, 30.522025],[30.621929, 31.012975]]}"; I've read a good way to start is to use oledb data adapter to fill a datatable. But the examples normally refer to .xls files, where as I am using the newer .xlsx. Is this still the best way of proceeding?

I have tried the below but am getting an error System.Data.OleDb.OleDbException: 'Cannot update. Database or object is read-only.' It also doesnt like the dataGridView1.DataBind(), saying there is not a definition for DataBind.

String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + "A:\\Home\\Test.xlsx;" +
"Extended Properties=Excel 8.0;";

OleDbConnection objConn = new OleDbConnection(sConnectionString);

objConn.Open();

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM MyRange1", objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

objAdapter1.SelectCommand = objCmdSelect;

DataSet objDataset1 = new DataSet();

objAdapter1.Fill(objDataset1, "XLData");

dataGridView1.DataSource = objDataset1.Tables[0].DefaultView;
dataGridView1.DataBind();

objConn.Close();

Answer 1

No, using OLEDB isn't a good way to proceed, because it only works on Windows and requires installing the Access drivers. xlsx files are ZIP files containing XML files so they don't need OLEDB at all. You can read the XML files directly or even better, use a library like ExcelDataReader or EPPlus, or ClosedXML to read the data. Those are just the 3 I remember right now.

With ExcelDataReader you can read Excel data with a DbDataReader and even load the data into a DataSet directly, with a single call to AsDataset()

using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read));
using (var reader = ExcelReaderFactory.CreateReader(stream));
do
{
    while (reader.Read())
    {
        // reader.GetDouble(0);
    }
} while (reader.NextResult()); //Move to the next sheet

or

using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read));
using (var reader = ExcelReaderFactory.CreateReader(stream));
var result = reader.AsDataSet();