C# 将 Excel 工作表读入数据表的最佳/最快方法？

Best /Fastest way to read an Excel Sheet into a DataTable?

提问by John Bustos

I'm hoping someone here can point me in the right direction - I'm trying to create a fairly robust utility program to read the data from an Excel sheet (may be .xls OR .xlsx) into a DataTable as quickly and leanly as possible.

我希望这里有人能指出我正确的方向 - 我正在尝试创建一个相当强大的实用程序，以将数据从 Excel 工作表（可能是 .xls 或 .xlsx）读取到 DataTable 中可能的。

I came up with this routine in VB (although I'd be just as happy with a good C# answer):

我在 VB 中想出了这个例程（尽管我对一个好的 C# 答案同样满意）：

Public Shared Function ReadExcelIntoDataTable(ByVal FileName As String, ByVal SheetName As String) As DataTable
    Dim RetVal As New DataTable

    Dim strConnString As String
    strConnString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & FileName & ";"

    Dim strSQL As String 
    strSQL = "SELECT * FROM [" & SheetName & "$]"

    Dim y As New Odbc.OdbcDataAdapter(strSQL, strConnString)

    y.Fill(RetVal)

    Return RetVal

End Function

I'm wondering if this is the best way to do it or if there are better / more efficent ways (or just more intelligent ways - Maybe Linq / native .Net providers) to use instead?

我想知道这是否是最好的方法，或者是否有更好/更有效的方法（或者只是更智能的方法 - 也许是 Linq/本机 .Net 提供商）来代替？

ALSO, just a quick and silly additional question - Do I need to include code such as y.Dispose()and y = Nothingor will that be taken care of since the variable should die at the end of the routine, right??

另外，只是一个快速而愚蠢的附加问题 - 我是否需要包含诸如y.Dispose()和y = Nothing或这样的代码，因为变量应该在例程结束时死亡，对吧？

Thanks!!

谢谢！！

采纳答案by Ciarán

I have always used OLEDBfor this, something like...

我一直用OLEDB这个，像...

    Dim sSheetName As String
    Dim sConnection As String
    Dim dtTablesList As DataTable
    Dim oleExcelCommand As OleDbCommand
    Dim oleExcelReader As OleDbDataReader
    Dim oleExcelConnection As OleDbConnection

    sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test.xls;Extended Properties=""Excel 12.0;HDR=No;IMEX=1"""

    oleExcelConnection = New OleDbConnection(sConnection)
    oleExcelConnection.Open()

    dtTablesList = oleExcelConnection.GetSchema("Tables")

    If dtTablesList.Rows.Count > 0 Then
        sSheetName = dtTablesList.Rows(0)("TABLE_NAME").ToString
    End If

    dtTablesList.Clear()
    dtTablesList.Dispose()

    If sSheetName <> "" Then

        oleExcelCommand = oleExcelConnection.CreateCommand()
        oleExcelCommand.CommandText = "Select * From [" & sSheetName & "]"
        oleExcelCommand.CommandType = CommandType.Text

        oleExcelReader = oleExcelCommand.ExecuteReader

        nOutputRow = 0

        While oleExcelReader.Read

        End While

        oleExcelReader.Close()

    End If

    oleExcelConnection.Close()

The ACE.OLEDBprovider will read both .xlsand .xlsxfiles and I have always found the speed quite good.

该ACE.OLEDB供应商将同时读取.xls和.xlsx文件，我一直觉得速度相当不错。

回答by rony sc

This is the way to read from excel oledb

这是从excel oledb中读取的方式

try
{
    System.Data.OleDb.OleDbConnection MyConnection;
    System.Data.DataSet DtSet;
    System.Data.OleDb.OleDbDataAdapter MyCommand;
    string strHeader7 = "";
    strHeader7 = (hdr7) ? "Yes" : "No";
    MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fn + ";Extended Properties=\"Excel 12.0;HDR=" + strHeader7 + ";IMEX=1\"");
    MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + wks + "$]", MyConnection);
    MyCommand.TableMappings.Add("Table", "TestTable");
    DtSet = new System.Data.DataSet();
    MyCommand.Fill(DtSet);
    dgv7.DataSource = DtSet.Tables[0];
    MyConnection.Close();
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}

回答by MethodMan

If you want to do the same thing in C# based on Ciarán Answer

如果你想根据Ciarán Answer在 C# 中做同样的事情

string sSheetName = null;
string sConnection = null;
DataTable dtTablesList = default(DataTable);
OleDbCommand oleExcelCommand = default(OleDbCommand);
OleDbDataReader oleExcelReader = default(OleDbDataReader);
OleDbConnection oleExcelConnection = default(OleDbConnection);

sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test.xls;Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";

oleExcelConnection = new OleDbConnection(sConnection);
oleExcelConnection.Open();

dtTablesList = oleExcelConnection.GetSchema("Tables");

if (dtTablesList.Rows.Count > 0) 
{
    sSheetName = dtTablesList.Rows[0]["TABLE_NAME"].ToString();
}

dtTablesList.Clear();
dtTablesList.Dispose();


if (!string.IsNullOrEmpty(sSheetName)) {
    oleExcelCommand = oleExcelConnection.CreateCommand();
    oleExcelCommand.CommandText = "Select * From [" + sSheetName + "]";
    oleExcelCommand.CommandType = CommandType.Text;
    oleExcelReader = oleExcelCommand.ExecuteReader();
    nOutputRow = 0;

    while (oleExcelReader.Read())
    {
    }
    oleExcelReader.Close();
}
oleExcelConnection.Close();

here is another way read Excel into a DataTable without using OLEDB very quick Keep in mind that the file ext would have to be .CSV for this to work properly

这是在不使用 OLEDB 的情况下非常快速地将 Excel 读入数据表的另一种方法 请记住，文件 ext 必须是 .CSV 才能正常工作

private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
{
    csvData = new DataTable(defaultTableName);
    try
    {
        using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
        {
            csvReader.SetDelimiters(new string[]
            {
                tableDelim 
            });
            csvReader.HasFieldsEnclosedInQuotes = true;
            string[] colFields = csvReader.ReadFields();
            foreach (string column in colFields)
            {
                DataColumn datecolumn = new DataColumn(column);
                datecolumn.AllowDBNull = true;
                csvData.Columns.Add(datecolumn);
            }

            while (!csvReader.EndOfData)
            {
                string[] fieldData = csvReader.ReadFields();
                //Making empty value as null
                for (int i = 0; i < fieldData.Length; i++)
                {
                    if (fieldData[i] == string.Empty)
                    {
                        fieldData[i] = string.Empty; //fieldData[i] = null
                    }
                    //Skip rows that have any csv header information or blank rows in them
                    if (fieldData[0].Contains("Disclaimer") || string.IsNullOrEmpty(fieldData[0]))
                    {
                        continue;
                    }
                }
                csvData.Rows.Add(fieldData);
            }
        }
    }
    catch (Exception ex)
    {
    }
    return csvData;
}

回答by Debasish

public DataTable ImportExceltoDatatable(string filepath)
{
    // string sqlquery= "Select * From [SheetName$] Where YourCondition";
    string sqlquery = "Select * From [SheetName$] Where Id='ID_007'";
    DataSet ds = new DataSet();
    string constring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
    OleDbConnection con = new OleDbConnection(constring + "");
    OleDbDataAdapter da = new OleDbDataAdapter(sqlquery, con);
    da.Fill(ds);
    DataTable dt = ds.Tables[0];
    return dt;
}

回答by Vince Pike

''' <summary>
''' ReadToDataTable reads the given Excel file to a datatable.
''' </summary>
''' <param name="table">The table to be populated.</param>
''' <param name="incomingFileName">The file to attempt to read to.</param>
''' <returns>TRUE if success, FALSE otherwise.</returns>
''' <remarks></remarks>
Public Function ReadToDataTable(ByRef table As DataTable,
                                incomingFileName As String) As Boolean
    Dim returnValue As Boolean = False
    Try

        Dim sheetName As String = ""
        Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & incomingFileName & ";Extended Properties=""Excel 12.0;HDR=No;IMEX=1"""
        Dim tablesInFile As DataTable
        Dim oleExcelCommand As OleDbCommand
        Dim oleExcelReader As OleDbDataReader
        Dim oleExcelConnection As OleDbConnection

        oleExcelConnection = New OleDbConnection(connectionString)
        oleExcelConnection.Open()

        tablesInFile = oleExcelConnection.GetSchema("Tables")

        If tablesInFile.Rows.Count > 0 Then
            sheetName = tablesInFile.Rows(0)("TABLE_NAME").ToString
        End If

        If sheetName <> "" Then

            oleExcelCommand = oleExcelConnection.CreateCommand()
            oleExcelCommand.CommandText = "Select * From [" & sheetName & "]"
            oleExcelCommand.CommandType = CommandType.Text

            oleExcelReader = oleExcelCommand.ExecuteReader

            'Determine what row of the Excel file we are on
            Dim currentRowIndex As Integer = 0

            While oleExcelReader.Read
                'If we are on the First Row, then add the item as Columns in the DataTable
                If currentRowIndex = 0 Then
                    For currentFieldIndex As Integer = 0 To (oleExcelReader.VisibleFieldCount - 1)
                        Dim currentColumnName As String = oleExcelReader.Item(currentFieldIndex).ToString
                        table.Columns.Add(currentColumnName, GetType(String))
                        table.AcceptChanges()
                    Next
                End If
                'If we are on a Row with Data, add the data to the SheetTable
                If currentRowIndex > 0 Then
                    Dim newRow As DataRow = table.NewRow
                    For currentFieldIndex As Integer = 0 To (oleExcelReader.VisibleFieldCount - 1)
                        Dim currentColumnName As String = table.Columns(currentFieldIndex).ColumnName
                        newRow(currentColumnName) = oleExcelReader.Item(currentFieldIndex)
                        If IsDBNull(newRow(currentFieldIndex)) Then
                            newRow(currentFieldIndex) = ""
                        End If
                    Next
                    table.Rows.Add(newRow)
                    table.AcceptChanges()
                End If

                'Increment the CurrentRowIndex
                currentRowIndex += 1
            End While

            oleExcelReader.Close()

        End If

        oleExcelConnection.Close()
        returnValue = True
    Catch ex As Exception
        'LastError = ex.ToString
        Return False
    End Try


    Return returnValue
End Function

回答by Sergey Petrov

You can use OpenXml SDK for *.xlsx files. It works very quickly. I made simple C# IDataReader implementation for this sdk. See here. Now you can easy read excel file to DataTable and you can import excel file to sql server database (use SqlBulkCopy). ExcelDataReader reads very fast. On my machine 10000 records less 3 sec and 60000 less 8 sec.

您可以将 OpenXml SDK 用于 *.xlsx 文件。它工作得非常快。我为这个 sdk 做了简单的 C# IDataReader 实现。见这里。现在您可以轻松地将 excel 文件读取到 DataTable，并且可以将 excel 文件导入 sql server 数据库（使用 SqlBulkCopy）。ExcelDataReader 读取速度非常快。在我的机器上，10000 条记录少于 3 秒，60000 条记录少于 8 秒。

Read to DataTable example:

读取到数据表示例：

class Program
{
    static void Main(string[] args)
    {
        var dt = new DataTable();
        using (var reader = new ExcelDataReader(@"data.xlsx"))
            dt.Load(reader);

        Console.WriteLine("done: " + dt.Rows.Count);
        Console.ReadKey();
   }
}

回答by althaf a s

Use the below snippet it will be helpfull.

使用下面的代码片段会有所帮助。

string POCpath = @"G:\Althaf\abc.xlsx";

string POCConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + POCpath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";

OleDbConnection POCcon = new OleDbConnection(POCConnection);
OleDbCommand POCcommand = new OleDbCommand();
DataTable dt = new DataTable();
OleDbDataAdapter POCCommand = new OleDbDataAdapter("select * from [Sheet1$] ", POCcon);
POCCommand.Fill(dt);
Console.WriteLine(dt.Rows.Count);

回答by Hexxx

This seemed to work pretty well for me.

这对我来说似乎很有效。

private DataTable ReadExcelFile(string sheetName, string path)
{

    using (OleDbConnection conn = new OleDbConnection())
    {
        DataTable dt = new DataTable();
        string Import_FileName = path;
        string fileExtension = Path.GetExtension(Import_FileName);
        if (fileExtension == ".xls")
            conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
        if (fileExtension == ".xlsx")
            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";
        using (OleDbCommand comm = new OleDbCommand())
        {
            comm.CommandText = "Select * from [" + sheetName + "$]";
            comm.Connection = conn;
            using (OleDbDataAdapter da = new OleDbDataAdapter())
            {
                da.SelectCommand = comm;
                da.Fill(dt);
                return dt;
            }
        }
    }
}

回答by morethanyell

I've used this method and for me, it is so efficient and fast.

我已经使用过这种方法，对我来说，它是如此高效和快速。

// Step 1. Download NuGet source of Generic Parsing by Andrew Rissing
// Step 2. Reference this to your project
// Step 3. Reference Microsoft.Office.Interop.Excel to your project
// Step 4. Follow the logic below

public static DataTable ExcelSheetToDataTable(string filePath) {

    // Save a copy of the Excel file as CSV
    var xlApp = new XL.Application();
    var xlWbk = xlApp.Workbooks.Open(filePath);
    var tempPath =
        Path.Combine(Environment
            .GetFolderPath(Environment.SpecialFolder.UserProfile)
            , "AppData"
            , "Local",
            , "Temp"
            , Path.GetFileNameWithoutExtension(filePath) + ".csv");

    xlApp.DisplayAlerts = false;
    xlWbk.SaveAs(tempPath, XL.XlFileFormat.xlCSV);
    xlWbk.Close(SaveChanges: false);
    xlApp.Quit();

    // The actual parsing
    using (var parser = new GenericParserAdapter(tempPath)) {
        parser.FirstRowHasHeader = true;
        return parser.GetDataTable();
    }

}

Generic Parsing by Andrew Rissing

Andrew Rissa 的通用解析

回答by Pratap Singh Mehra

I found it pretty easy like this

我发现这样很容易

    using System;
    using System.Data;
    using System.IO;
    using Excel;

    public DataTable ExcelToDataTableUsingExcelDataReader(string storePath)
    {
        FileStream stream = File.Open(storePath, FileMode.Open, FileAccess.Read);

        string fileExtension = Path.GetExtension(storePath);
        IExcelDataReader excelReader = null;
        if (fileExtension == ".xls")
        {
            excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
        }
        else if (fileExtension == ".xlsx")
        {
            excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
        }

        excelReader.IsFirstRowAsColumnNames = true;
        DataSet result = excelReader.AsDataSet();
        var test = result.Tables[0];
        return result.Tables[0];
    }

Note: you need to install SharpZipLib package for this

注意：您需要为此安装 SharpZipLib 包

Install-Package SharpZipLib

neat and clean! ;)

干净整洁！;)