Using Excel Oledb to Get Sheet Names in Sheet Order

Using Excel OleDb to get sheet names IN SHEET ORDER

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.

OleDb - Retrieving Excel worksheet names also retrieves Defined Names

My first thoughts were wrong so I came up with this as a workaround. The actual worksheet names returned should always end with $, so I hacked it to check for that. Messy but you get the general idea I'm sure.

OleDbConnection connExcel = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;"
        + @"Data Source=c:\test.xlsx;"
        + @"Extended Properties=""Excel 12.0 Xml;HDR=Yes;""");

        OleDbCommand cmdExcel = new OleDbCommand();
        cmdExcel.Connection = connExcel;
        connExcel.Open();

        DataTable testTable = connExcel.GetSchema("Tables");

        String[] excelSheets = new String[testTable.Rows.Count];
        int i = 0;

        foreach (DataRow row in testTable.Rows)
        {
            excelSheets[i] = row["TABLE_NAME"].ToString();

            if (excelSheets[i].EndsWith("$"))
            {
                Console.WriteLine(excelSheets[i] = row["TABLE_NAME"].ToString());
                i++;
            }
            else
            {
                i++;
            }

        }

        Console.ReadLine();

Order of sheets in Excel file while reading with GetOleDbSchemaTable

According to this post, the moderator says I am afraid that OLEDB does not preserve the sheet order as they were in Excel..

While Googling though, I found this SO answer that might help you out.

how can get all excel sheet name in c# using OleDbDataAdapter

OleDbDataAdaptor has nothing to do with Sheet names; Sheet names belong to Excel.Workbook, and Excel Workbook belongs to Excel.Application.
You would need to iterate through workbook sheet names:

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application xl = new Excel.Application();;
Excel.Workbook wb = xl.Workbooks.Open("WorkBookfullPath", 0, true);

foreach (Excel.Worksheet ws in wb.Worksheets) {
        {
             string wsName = ws.Name;  
        }

You don't really need here OleDbDataAdapter, in this case (in case you need to read data from worksheet) you can just read from Excel into 2 dimentional array (1st dimension is rows and second dimension is columns):

object[,] data = ws.UsedRange.Value2; // change UsedRange range to your table range, and  you can also use ws.UsedRange.FormulaR1C1

Get sheets names from excel ASP.Net

After many search i found out that "OLEDB" not support reading sheets in original order (read sheets names in alphabetical order only) but there are many way support reading sheets names in original order from excel file.



Helped link: Reading sheets in original order from excel file,


OLEDB does not preserve the sheet order as they were in Excel.

Get all sheet names in an Excel file in order

Try this,

OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
String[] sheetNames = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
   sheetNames[i] = row["TABLE_NAME"].ToString();
   i++;
}

Quick way to determine the names of the sheets of an Excel file using SAS?

Is there a reason you can't utilize a LIBNAME MyData EXCEL "<filename>"; ?

The LIBNAME only creates a LIBREF that is a nickname pointing to a data source. The members of a library can be obtained in Proc SQL using DICTIONARY.TABLES

Example:

* create a sample workbook that has several sheets (one per name);

ods excel file='sandbox.xlsx' options(sheet_name="#BYVAL1");

options nobyline;
ods noresults; /* prevent automatic opening when destination closed */

proc print data=sashelp.class;
  by name;
run;

ods excel close;

/*
 * Determine sheet names from DICTIONARY.TABLES
 * The metadata for a sheet name is the value in MEMNAME column
 * The memnames have trailing dollar sign ($)
 * The $ should be familiar to Excel users that understand
 *   <workbook>!<sheetname>$<cell-reference> syntax
 */

libname students EXCEL 'sandbox.xlsx';

proc sql;
  reset noprint;
  select compress(memname,'$') into :SHEET_NAMES separated by ' '
  from dictionary.tables
  where libname = "STUDENTS"
  ;

libname students;

%put &=SHEET_NAMES;

Log

SHEET_NAMES=Alfred Alice Barbara Carol Henry James Jane Janet Jeffrey John Joyce Judy Louise Mary
Philip Robert Ronald Thomas William

---

Related Topics