Подтвердить что ты не робот

Лучший/самый быстрый способ чтения листа Excel в DataTable?

Я надеюсь, что кто-то здесь может указать мне в правильном направлении - я пытаюсь создать довольно надежную служебную программу для быстрого чтения данных из листа Excel (может быть .xls OR.xlsx) в DataTable и как можно более плотно.

Я придумал эту процедуру в VB (хотя я был бы так же доволен хорошим ответом С#):

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

Мне интересно, если это лучший способ сделать это или если есть более эффективные способы (или просто более интеллектуальные способы - возможно, Linq/native.Net провайдеры)?

ТАКЖЕ, просто быстрый и глупый дополнительный вопрос. Нужно ли включать код, например y.Dispose() и y = Nothing, или это будет позаботиться, поскольку переменная должна умереть в конце процедуры, правильно?

Спасибо!!

4b9b3361

ОТВЕТЫ

Ответ 1

Я всегда использовал 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()

Поставщик ACE.OLEDB будет читать файлы .xls и .xlsx, и я всегда нашел скорость неплохой.

Ответ 2

Если вы хотите сделать то же самое в С# на основе Ciarán Answer

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

вот еще один способ чтения Excel в DataTable без использования OLEDB очень быстрый Имейте в виду, что файл 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;
}

Ответ 3

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

Ответ 4

Это способ чтения из 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());
}

Ответ 5

Вы можете использовать OpenXml SDK для *.xlsx файлов. Он работает очень быстро. Я сделал простую реализацию С# IDataReader для этого sdk. См. здесь. Теперь вы можете легко прочитать Excel файл в DataTable, и вы можете импортировать файл excel в базу данных sql-сервера (используйте SqlBulkCopy). ExcelDataReader читается очень быстро. На моей машине 10000 записей меньше 3 сек и 60000 менее 8 сек.

Считайте пример DataTable:

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

Ответ 6

Казалось, это работает очень хорошо для меня.

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

Ответ 7

Я нашел это довольно легко, как это

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

Примечание: вам необходимо установить пакет SharpZipLib для этого

Install-Package SharpZipLib

аккуратный и чистый!;)

Ответ 8

Приведенный ниже код проверяется мной и очень прост, понятен, удобен и быстр. Этот код сначала берет все имена листов, а затем помещает все таблицы этого файла excel в DataSet.

    public static DataSet ToDataSet(string exceladdress, int startRecord = 0, int maxRecord = -1, string condition = "")
    {
        DataSet result = new DataSet();
        using (OleDbConnection connection = new OleDbConnection(
                (exceladdress.TrimEnd().ToLower().EndsWith("x"))
                ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + exceladdress + "';" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'"
                : "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + exceladdress + "';Extended Properties=Excel 8.0;"))
            try
            {
                connection.Open();
                DataTable schema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                foreach (DataRow drSheet in schema.Rows)
                    if (drSheet["TABLE_NAME"].ToString().Contains("$"))
                    {
                        string s = drSheet["TABLE_NAME"].ToString();
                        if (s.StartsWith("'")) s = s.Substring(1, s.Length - 2);
                        System.Data.OleDb.OleDbDataAdapter command =
                            new System.Data.OleDb.OleDbDataAdapter(string.Join("", "SELECT * FROM [", s, "] ", condition), connection);
                        DataTable dt = new DataTable();
                        if (maxRecord > -1 && startRecord > -1) command.Fill(startRecord, maxRecord, dt);
                        else command.Fill(dt);
                        result.Tables.Add(dt);
                    }
                return result;
            }
            catch (Exception ex) { return null; }
            finally { connection.Close(); }
    }

Наслаждаться...

Ответ 9

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

Ответ 10

Используйте приведенный ниже фрагмент, это будет полезно.

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

Ответ 11

Я использовал этот метод, и для меня он настолько эффективен и быстрый.

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

}

Общий анализ Эндрю Риссинга

Ответ 12

Вот еще один способ сделать это

public DataSet CreateTable(string source)
{
    using (var connection = new OleDbConnection(GetConnectionString(source, true)))
    {
        var dataSet = new DataSet();
        connection.Open();
        var schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        if (schemaTable == null)
            return dataSet;

        var sheetName = "";
        foreach (DataRow row in schemaTable.Rows)
        {
            sheetName = row["TABLE_NAME"].ToString();
            break;
        }

        var command = string.Format("SELECT * FROM [{0}$]", sheetName);
        var adapter = new OleDbDataAdapter(command, connection);
        adapter.TableMappings.Add("TABLE", "TestTable");
        adapter.Fill(dataSet);
        connection.Close();

        return dataSet;
    }
}

//

private string GetConnectionString(string source, bool hasHeader)
{
    return string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};
    Extended Properties=\"Excel 12.0;HDR={1};IMEX=1\"", source, (hasHeader ? "YES" : "NO"));
}