Using Excel OleDb to get sheet names IN SHEET ORDER
c#, excel, oledb, server-side
Solution
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
Excel Sheet Names in Sheet Order
Seems like this would be a common enough requirement that there would be a decent workaround.
Problem
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.