How to Read an Excel Table placed not in First Cell

Question

I have an Excel workbook where the Table is placed after the 9th row in the worksheet. How am I supposed to read the Table at that point?

Currently, I am able to read the Excel worksheet using Microsoft.ACE.OLEDB.12.0 provider like this:

OleDbConnection connection = new OleDbConnection();
var connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0; data source={fileName}; Extended Properties=Excel 8.0;";
connection.ConnectionString = connectionString;
connection.Open();
DataTable dbSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dbSchema == null || dbSchema.Rows.Count < 1)
{
    throw new Exception("Error: Could not determine the name of the first worksheet.");
}
string firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();

var adapter = new OleDbDataAdapter($"SELECT * FROM [{firstSheetName}]", connectionString);
var ds = new DataSet();
adapter.Fill(ds, "anyNameHere");
DataTable table = ds.Tables[0];
MessageBox.Show($"No of Records found: {table.Rows.Count}");

What I observe in the above code is that the Table is read but null values are yielded for non-table fields. However, I will need to do a filter for Row Number after n ( if n is the place where the table is placed) if I am supposed to get the intended.

I would welcome if this can be achieved by other means instead of OleDbConnection

Answer 1

the Table is placed after the 9th row in the worksheet

So you know the index of the heading

I would welcome if this can be achieved by other means instead of OleDbConnection

Actually, I use ExcelDataReader.Mapping, you can specify the row of the heading, here is how it works:

I have this data in an excel file

Model

public class SheetData
{
    public string Name { set; get; }
    public int Value { set; get; }
}

Usage (note that HeadingIndex takes value of 8 = 9 - 1)

using var stream = File.OpenRead(@"C:\Users\mosul\Desktop\Sample.xlsx");
using var importer = new ExcelImporter(stream);
var sheet = importer.ReadSheet();
sheet.HeadingIndex = 8;
var data = sheet.ReadRows<SheetData>().ToList();
Console.WriteLine(data.Count); // 3

That's it.