c# - 将巨大的 Excel 工作表导入 Datatable

Question

我有一个带有两个选项卡的 Excel 工作表。

一个约为 700 k，另一个约为 25k。问题是当我加载文件时，我的内存被耗尽并且崩溃了!如何处理巨大的文件，因为有些文件甚至可能超过一百万行。

这是我目前使用的代码:

  OleDbConnection cnn = new OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileName + "';Extended Properties=Excel 12.0;");
                cnn.Open();

                string qry = "SELECT * FROM [Detail$]";
                OleDbDataAdapter odp = new OleDbDataAdapter(qry, cnn);
                odp.Fill(detailTable);
                DataSet tmp = new DataSet();
                if (detailTable.Rows.Count > 0)
                {
                    Console.WriteLine("Total " + detailTable.Rows.Count + " Detail rows Loaded");
                    // MessageBox.Show("Input Sheet UPLOADED !");

                }
                qry = "SELECT * FROM [Gallery$]";
                OleDbDataAdapter odp1 = new OleDbDataAdapter(qry, cnn);
                odp1.Fill(galleryTable);
                if (galleryTable.Rows.Count > 0)
                {
                    Console.WriteLine("Total " + galleryTable.Rows.Count + " Gallery Numbers Loaded");
                    //  MessageBox.Show("Input Sheet UPLOADED !");

                }

Answer 1

好的，我可以建议您使用 DbDataAdapter 类的 DbDataAdapter.Fill(Int32, Int32, DataTable[]) 重载方法在“ block ”模式:

public int Fill(
    int startRecord,
    int maxRecords,
    params DataTable[] dataTables
)

使用此方法和我的代码示例，您可以一次处理大量行，而不是处理内存中的完整 Excel 数据。每次填充后，处理您的临时数据表对象，这样您就可以避免内存泄漏。

这里是你如何做到的:

        const string fileName = "myData.xlsx";
        const string excelConnString = "provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileName + "';Extended Properties=Excel 12.0;";

        using (var cnn = new OleDbConnection(excelConnString))
        {
            cnn.Open();

            const string countQuery = "SELECT COUNT(*) FROM [Detail$]";
            using (var cmd = new OleDbCommand(countQuery, cnn))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader == null) return;

                    reader.Read();
                    var rowsCount = ((int)reader[0]);

                    const string query = "SELECT * FROM [Detail$]";
                    using (var odp = new OleDbDataAdapter(query, cnn))
                    {
                        var detailTable = new DataTable();
                        var recordToStartFetchFrom = 0; //zero-based record number to start with.
                        const int chunkSize = 100;
                        while (recordToStartFetchFrom <= rowsCount)
                        {
                            var diff = rowsCount - recordToStartFetchFrom;
                            int internalChunkSize = diff < 100 ? diff : chunkSize;
                            odp.Fill(recordToStartFetchFrom, internalChunkSize, detailTable);

                            foreach (DataRow row in detailTable.Rows)
                            {
                                Console.WriteLine("{1} {0}", row.ItemArray[0], row.ItemArray[1]);
                            }

                            Console.WriteLine("--------- {0}-{1} Rows Processed ---------", recordToStartFetchFrom, recordToStartFetchFrom + internalChunkSize);

                            recordToStartFetchFrom += chunkSize;

                            detailTable.Dispose();
                            detailTable = null;
                            detailTable = new DataTable();
                        }
                    }
                    Console.ReadLine();
                }
            }
        }