excel select all table/worksheet name

Question

I'm using SQL to query Excel File as HERE, and I know I can get the content of worksheet by something like:

var ds = new DataSet();
var conn = new OleDbConnection();
conn.Open();
var cmd = new OleDbDataAdapter("Select * FROM [Sheet1$]", conn);
cmd.Fill(ds, "ds1");

and I want to ask is there way to get all table names?

I've tried this answer but not working.

score 1 · Answer 1 · answered Aug 14 '18 at 08:21

1

Are you asking about getting data from all sheets? If yes, then please see below post:

How to import all the Excel sheets to DataSet in C#

answered Aug 14 '18 at 08:21

Dhiren

153
1
13

score 1 · Answer 2 · answered Aug 14 '18 at 08:33

I tried the code from the example link and it works for me:

            var result = reader.AsDataSet(new ExcelDataSetConfiguration()
            {
                UseColumnDataType = true,
                ConfigureDataTable = (data) => new ExcelDataTableConfiguration()
                {
                    UseHeaderRow = true
                }
            });

            DataTableCollection tables = result.Tables;

            for (int i = 0; i < tables.Count; i++)
            {
                Console.WriteLine(tables[i].TableName);
                //here I can work with current table
                for (int j = 0; j < tables[i].Columns.Count; j++)
                {
                    Console.WriteLine(tables[i].Columns[j].ColumnName);
                }
            }

yu yang Jian · Accepted Answer · 2018-08-15T03:08:43.043

I just find a way to get all worksheets name by:

var ds = new DataSet();
var conn = _connNoHeader = new OleDbConnection(string.Format("Provider={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=No;\";Data Source={1}", provider, filePath));           
conn.Open();

DataTable tables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 
                                             new object[] { null, null, null, "TABLE" });

Console.WriteLine("The tables are:");
foreach (DataRow row in tables.Rows)
    Console.Write("  {0}", row[2]);

// here are all worksheet name
var allWorksheetNames = tables.Rows.OfType<DataRow>().Select(row => row[2].ToString().TrimEnd('$')).ToList();

This solution comes from HERE.

excel select all table/worksheet name

3 Answers3