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

are you familiar with looping, counters etc.. this is actually quite simple use a loop and the key word `continue` inside that loop until a condition is met.. — MethodMan, Jun 17 '15 at 21:35

score 0 · Answer 1 · edited May 23 '17 at 12:32

0

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.

edited May 23 '17 at 12:32

Community

1
1

answered Jun 17 '15 at 20:43

Names

26
5

score 0 · Answer 2 · answered Jun 18 '15 at 16:47

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;

score 0 · Accepted Answer · answered Jun 18 '15 at 17:25

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.

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

3 Answers3