how to read data from a sheet in excel file

[tanilov]

Hi all,

I need to read data from an excel file and populate a dataset.
After some search I found following code:

connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" +
filename.ToString() +
";Extended Properties=\"Excel 8.0;HDR=YES;\"";
DataSet myDataSet = new DataSet();
OleDbDataAdapter myCommand = new OleDbDataAdapter(
"SELECT * FROM [Sheet1$]",
connString);

myCommand.Fill(myDataSet, "ExcelInfo");

but in order to work, you need to know the name of the sheet you want
to read.
Is there a way to read data in the first sheet (even if you don't know
the name)?

Thanks a lot,
Danilo

 

[jpuopolo]

Hi....

The key is not the sheet name, but defining a virtual table name.

In essence, to create a virtual table name, you need to highlight the
column headers (you have HDR=YES in your example, so I assume you have
a header row in the spreadsheet) and type the name of the virtual table
in the upper left hand corner of the sheet.

There may be a way to iterate over the virtual table names
programatically. If you don't want to or can't go down this road, you
can certainly get the Sheet names programatically (see the Excel Object
Model documentation) so that you can build the call dynamically...

I hope this helps...

jpuopolo