.net 无论使用 OleDb 的工作表名称如何，从 Excel 文档中获取第一张工作表

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

提问by naspinski

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.

我有用户将他们的工作表命名为各种疯狂的东西，但我希望能够获得 Excel 文档的第一张工作表，而不管它的名称是什么。

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.

谢谢你。

采纳答案by naspinski

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");
}

回答by melaouhia mohamed amine

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();

回答by Anirudh Gaur

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

在我使用数据网格“DataGridView1”加载工作表的所有内容的情况下，此代码运行良好

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()

回答by Kayot

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.

基本上是 Anirudh Gaur 答案的副本。我对代码进行了一些重新格式化并将其放入一个函数中。我添加了一个 StringBuilder，所以我可以用 SELECT 语句做更多的事情。

Upvotes go to Anirudh Gaur

投票给 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

回答by Mohammad Fathi MiMFa

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";
}

回答by BinaryMisfit

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

您可以使用GetOleDbSchemaTable(VB) 或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.

使用 Tables Enum 它将返回所有工作表名称的列表，然后您可以使用它来动态构建所需的 SQL。

You can use:

您可以使用：

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

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

使用 OleDbSchemaGuid 信息可以检索

• Columns
• Foreign keys
• Indexes
• Primary keys
• Tables
• Views

• 列
• 外键
• 索引
• 主键
• 表
• 观看次数

Full MSDN documentation available here

此处提供完整的 MSDN 文档

回答by mateusz stacel

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;
        }

回答by Mahek Shah

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)

回答by Mahek Shah

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..

您可以像这样获得 sheet1 名称并以这种方式使用。如果您想获得其他工作表名称，您可以将值从 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)