Using Excel OleDb to get sheet names IN SHEET ORDER

Question

I'm using OleDb to read from an excel workbook with many sheets.

I need to read the sheet names, but I need them in the order they are defined in the spreadsheet; so If I have a file that looks like this;

|_____|_____|____|____|____|____|____|____|____|
|_____|_____|____|____|____|____|____|____|____|
|_____|_____|____|____|____|____|____|____|____|
\__GERMANY__/\__UK__/\__IRELAND__/

Then I need to get the dictionary

1="GERMANY", 
2="UK", 
3="IRELAND"

I've tried using OleDbConnection.GetOleDbSchemaTable(), and that gives me the list of names, but it alphabetically sorts them. The alpha-sort means I don't know which sheet number a particular name corresponds to. So I get;

GERMANY, IRELAND, UK

which has changed the order of UK and IRELAND.

The reason I need it to be sorted is that I have to let the user choose a range of data by name or index; they can ask for 'all the data from GERMANY to IRELAND' or 'data from sheet 1 to sheet 3'.

Any ideas would be greatly appreciated.

if I could use the office interop classes, this would be straightforward. Unfortunately, I can't because the interop classes don't work reliably in non-interactive environments such as windows services and ASP.NET sites, so I needed to use OLEDB.

Answer 1

Can't find this in actual MSDN documentation, but a moderator in the forums said

I am afraid that OLEDB does not preserve the sheet order as they were in Excel

and gave this code:

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelBook = xlApp.Workbooks.Open("D:\\Book1.xlsx"); 

String[] excelSheets = new String[excelBook.Worksheets.Count];
int i = 0;
foreach(Microsoft.Office.Interop.Excel.Worksheet wSheet in excelBook.Worksheets)    
{
  excelSheets[i] = wSheet.Name;
  i++;
}

Excel Sheet Names in Sheet Order

Seems like this would be a common enough requirement that there would be a decent workaround.