Use OldeDbConnection to read excel sheet using 9th row as the column names?

Question

I'm trying to read an excel sheet using oleDbConnection and OleDbDataAdapter and then filling a DataSet with that information. What i need to figure out how to do is ignore the first 8 rows in the excel sheet and read it as if the 9th row was the first row, (making the column names the values in the 9th row).

Thanks, Alex

Answer 1

Nobody answered my question good.. But i figured it out on my lonesome.

I solved the problem by reading the excel sheet into a dataTable. Then removing the first 8 rows from the dataTable. then i created a second datatable, manually added the columns(use the Columns.Add command) and used a foreach(row in OGdataTable) to add all the rows to the new table.

If anybody wants more clarification on this, go ahead and ask.

Answer 2

Check out How to open an Excel file in C#?

To add some input on your specific scenario: there is a Worksheet object that has a Rows attribute.

Answer 3

If you are open to other approaches I'd suggest to use LinqToExcel you can find it on NuGet, this way you'll end up with a IQueryable object instead of some messy Dataset, e.g.:

Defining your entity:

class MyType
{
    public string A { get; set; }
    public string B { get; set; }
    public string C { get; set; }
}

If you know the range you want to read you can do something like:

var excel=new ExcelQueryFactory(@"c:\temp\temp.xlsx");
    var q = from r in excel.WorksheetRange<MyType>("A9","C12")
            select r;

Then you can use q to filter, iterate, etc...

If you don't know how many rows are there in the file to define the range you can do something like this instead:

var excel=new ExcelQueryFactory(@"c:\temp\temp.xlsx");
int rowCount=excel.WorksheetNoHeader().Count();
    var q = from r in excel.WorksheetRange<MyType>("A9","C"+rowCount)
            select r;