Getting the first sheet from an Excel document regardless of sheet name with OleDb

Question

I have users that name their sheets all sorts of crazy things, but I want to be able to get the first sheet of the Excel document regardless of what it is named.

I currently use:

OleDbDataAdapter adapter = new OleDbDataAdapter(
"SELECT * FROM [sheetName$]", connString);

How would I go about getting the first sheet no matter what it is named?

Thank you.

possible duplicate of [Using Excel OleDb to get sheet names IN SHEET ORDER](http://stackoverflow.com/questions/1164698/using-excel-oledb-to-get-sheet-names-in-sheet-order) — Conrad Frix, Dec 26 '13 at 20:52

score 38 · Accepted Answer · answered Sep 17 '09 at 11:18

ended up using this:

using (OleDbConnection conn = new OleDbConnection(connString))
{
    conn.Open();
    dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    Sheet1= dtSchema.Rows[0].Field<string>("TABLE_NAME");
}

score 20 · Answer 2 · edited Dec 14 '12 at 10:23

20

OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="Path"; Extended Properties=Excel 12.0;Persist Security Info=False;");

oconn.Open();
myCommand.Connection = oconn;
DataTable dbSchema = oconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,  null);
if (dbSchema == null || dbSchema.Rows.Count < 1)
{
    throw new Exception("Error: Could not determine the name of the first worksheet.");
}
string firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();

edited Dec 14 '12 at 10:23

Leigh

28,765
10
55
103

answered Dec 14 '12 at 10:04

melaouhia mohamed amine

210
2
3

3

Based on my tests, this returns the first sheet _alphabetically_, not the first sheet based on the order in which they appear in the workbook (left to right). – Doppelganger Dec 02 '15 at 18:53

score 3 · Answer 3 · answered Apr 13 '13 at 14:16

This code has worked fine where i have used the data grid "DataGridView1" to load all the content of the sheet

Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet : Dim filteext As String = ""

 ''check for the file type
 If IO.Path.GetExtension(fileName) = "xls" Then
                filteext = "Excel 8.0"
 ElseIf IO.Path.GetExtension(fileName) = ".xlsx" Then
                filteext = "Excel 12.0"
 End If

''open connection

 MyConnection = New System.Data.OleDb.OleDbConnection _
               ("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties=" & filteext & ";")
            MyConnection.Open()

  Dim myTableName = MyConnection.GetSchema("Tables").Rows(0)("TABLE_NAME")

  Dim MyCommand As OleDbDataAdapter = New OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", myTableName), MyConnection)


   MyCommand.TableMappings.Add("Table", "TestTable")
            DtSet = New System.Data.DataSet

    MyCommand.Fill(DtSet)

    DataGridView1.DataSource = DtSet.Tables(0)
            'DtSet.DataSetName. 

    MyConnection.Close()

score 3 · Answer 4 · edited Apr 22 '19 at 09:36

It`s my solution ▼ (Easy, Fast, Executable, Understandable)

internal static DataTable GetExcelSheet(string excelFile,string sheetName = "")
{
    string fullPathToExcel = Path.GetFullPath(excelFile);
    string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel " + (excelFile.ToLower().EndsWith("x") ? "12.0" : "8.0") + ";HDR=yes'", fullPathToExcel);
    return GetDataTable(connString, "SELECT * FROM [" + (string.IsNullOrEmpty(sheetName) ? GetTableName(connString, 0) : sheetName + "$") + "]");
}

private static DataTable GetDataTable(string connectionString, string sql)
{
    DataTable dt = new DataTable();

    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        using (OleDbCommand cmd = new OleDbCommand(sql, conn))
        {
            using (OleDbDataReader rdr = cmd.ExecuteReader())
            {
                dt.Load(rdr);
                return dt;
            }
        }
    }
}
private static string GetTableName(string connectionString, int row = 0)
{
    OleDbConnection conn = new OleDbConnection(connectionString);
    try
    {
        conn.Open();
        return conn.GetSchema("Tables").Rows[row]["TABLE_NAME"] + "";
    }
    catch { }
    finally { conn.Close();}
    return "sheet1";
}

score 2 · Answer 5 · answered May 12 '16 at 18:27

Basically a copy of Anirudh Gaur's answer. I did some reformatting of the code and put it into a function. I added a StringBuilder so I can do more with the SELECT statement.

Upvotes go to Anirudh Gaur

Private Function Load_XLS(FileName As String) As DataTable
    Dim DataTable As New DataTable
    Dim Format As String = ""
    If IO.Path.GetExtension(FileName) = ".xls" Then
        Format = "Excel 8.0"
    ElseIf IO.Path.GetExtension(FileName) = ".xlsx" Then
        Format = "Excel 12.0"
    End If

    Using Connection As New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & FileName & "';Extended Properties=" & Format & ";")
        Connection.Open()

        Dim TableName As String = Connection.GetSchema("Tables").Rows(0)("TABLE_NAME")

        Dim SQLCommand As New Text.StringBuilder
        SQLCommand.AppendLine("SELECT *")
        SQLCommand.AppendLine("FROM [{0}]")

        Dim Command As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(String.Format(SQLCommand.ToString, TableName), Connection)

        Command.Fill(DataTable)

        Connection.Close()
    End Using
    Return DataTable
End Function

BinaryMisfit · Answer 6 · 2009-09-17T11:26:51.130

1

You can use the GetOleDbSchemaTable (VB) or GetOleDbSchemaTable (C#).

Using the Tables Enum it will return a list of all the worksheet names, which you can then use to dynamically build the required SQL.

You can use:

MySchemaTable = MyConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

All the Worksheets names will be returned as part of a DataTable you can them itterate through.

Using the OleDbSchemaGuid information can be retrieved on

Columns

Foreign keys

Indexes

Primary keys

Tables

Views

Full MSDN documentation available here

edited Sep 17 '09 at 11:26

answered Sep 17 '09 at 11:05

BinaryMisfit

29,219
2
37
44

it looks like that returns a datatable, how do I get the sheet name out? is it a named field? – naspinski Sep 17 '09 at 11:10
also, what parameters do I pass in for the restrictions : Object [ ]? – naspinski Sep 17 '09 at 11:12
@naspinski - The articles I linked contains all the required information. I haven't worked with OLEDB in the last 2 years. If I recall correctly the table name will be in the COLUMN_NAME field. – BinaryMisfit Sep 17 '09 at 11:28

Mahek Shah · Answer 7 · 2019-02-06T08:50:07.567

You can use this approach also for getting sheet name. See comments for more understanding

myExcelConn.Open()

//GET DATA FROM EXCEL SHEET.
Dim str As String = String.Empty
Dim Sheets As DataTable = myExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
For i As Integer = 0 To Sheets.Rows.Count - 1
    str += Sheets.Rows(i)("TABLE_NAME").ToString() + "," //It will return sheet1,sheet2,sheet3 according to my excel file
Next

Dim objOleDB As New OleDbCommand("SELECT *FROM [" + str.Split(",")(0) + "]", myExcelConn) //It will select sheet1
Me.Label1.Text = str.Split(",")(0).Replace("$", "")
// READ THE DATA EXTRACTED FROM THE EXCEL FILE.
Dim objBulkReader As OleDbDataReader
objBulkReader = objOleDB.ExecuteReader

Dim dt As DataTable = New DataTable
dt.Load(objBulkReader)

//FINALLY, BIND THE EXTRACTED DATA TO THE GRIDVIEW.
GridView1.DataSource = dt
GridView1.DataBind()
//If you want sheet2 data
 Dim objOleDB1 As New OleDbCommand("SELECT *FROM [" + str.Split(",")(1).Split(",")(0) + "]", myExcelConn)
//If you want sheet3 data
 Dim objOleDB2 As New OleDbCommand("SELECT *FROM [" + str.Split(",")(2).Split(",")(0) + "]", myExcelConn)

Hi, please don't just drop a chunk of code as an answer, please also explain how it solves the issue the user is encountering =) — Rob, Feb 06 '19 at 07:23

score 0 · Answer 8 · answered Feb 07 '19 at 06:07

You can get sheet1 name like this and use in this manner.If you want to get other sheet names you can increase value from 0,1,2..

 Dim myExcelConn As OleDbConnection = _
                New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                    Server.MapPath(".") & "\" & FileUpload1.FileName() & _
                    ";Extended Properties=Excel 12.0;")
    Dim Sheets As DataTable = myExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
                Sheet1 = Sheets.Rows(0)("TABLE_NAME").ToString()
  Dim objOleDB As New OleDbCommand("SELECT *FROM [" + Sheet1 + "]", myExcelConn)

score 0 · Answer 9 · answered Oct 24 '19 at 14:31

That's my solution

 private static string GetExcelWorkSheet(string pathToExcelFile)
        {
            Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();

            Microsoft.Office.Interop.Excel.Workbook theWorkbook = null;

            theWorkbook = ExcelObj.Workbooks.Open(pathToExcelFile);

            Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;

            // Get the reference of first worksheet. Index start at 1 
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

            // Get the name of worksheet.
            string strWorksheetName = worksheet.Name; 

            return strWorksheetName;
        }

Getting the first sheet from an Excel document regardless of sheet name with OleDb

9 Answers9

Linked