Excel sheets not being retrieved in their default order

Question

When I try to read excel sheet names from my C# code it's returning them in alphabetical order. I actually need them in their normal order.

This is because I have a scenario where users need to enter a sheet number then based on that I need to process that sheet result. But when I read from code it's definitely returning the sheets in alphabetical order, how can i get those sheet names in its normal order?

For getting sheet names we are using code like

sheetNamesDT = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);

Answer 1

As per MSDN, In a case of spreadsheets inside of Excel it might not work because Excel files are not real databases. So you will be not able to get the sheets name in order of their visualization in workbook.

Code to get sheets name as per their visual appearance using interop:

Add reference to Microsoft Excel 12.0 Object Library.

Following code will give the sheets name in the actual order stored in workbook, not the sorted name but for this you need to install office at that machine.

Sample Code:

using Microsoft.Office.Interop.Excel;

string filename = "C:\\romil.xlsx";

object missing = System.Reflection.Missing.Value;

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbook wb =excel.Workbooks.Open(filename,  missing,  missing,  missing,  missing,missing,  missing,  missing,  missing,  missing,  missing,  missing,  missing,  missing,  missing);

ArrayList sheetname = new ArrayList();

foreach (Microsoft.Office.Interop.Excel.Worksheet  sheet in wb.Sheets)
{
    sheetname.Add(sheet.Name);
}