I'm not sure why you are using both interop and oleDb.
As you already have the workbook open via interop then continue to access the sheets using that - you have the option then of referring to them via their index [1], [2] etc (i.e. ignoring the name - have a separate list of sheetnames if you absolutely need them), or you can use
foreach
For example: (untested)
Microsoft.Office.Interop.Excel.Application excelFileObject = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workBookObject = null;
workBookObject = excelFileObject.Workbooks.Open(File, 0, true, 5, "", "", false,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Sheets sheets = workBookObject.Worksheets;
DataSet oDS = new DataSet();
foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in sheets)
{
DataTable obj_Dataset = FillDataSetFromWorkSheet(sheet);
oDS.Tables.Add(obj_Dataset);
}
with
private DataTable FillDataSetFromWorkSheet(Microsoft.Office.Interop.Excel.Worksheet sheet)
{
var oDT = new DataTable();
for (int colIndex = 0; colIndex < MaxCols; colIndex++)
oDT.Columns.Add(string.Format("Column{0}", colIndex));
for (int rowIndex = 1; rowIndex <= MaxRows; rowIndex++)
{
var row = oDT.NewRow();
for (int colIndex = 0; colIndex < MaxCols; colIndex++)
row[colIndex] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, colIndex + 1]).Value2);
oDT.Rows.Add(row);
}
return oDT;
}
I just had
MaxCols
and
MaxRows
as constants but you could derive them as required.