logo标签列表

如何按需加载Excel行到C#数据表中

c#.netdatatableoledbienumerable
3
我有一个需求，需要从微软Excel的一个工作表中填充dataTable。由于该工作表可能包含大量数据，因此要求在遍历数据表的foreach循环期间，该数据表应按需获取Microsoft Excel工作表中的数据来填充数据表。
也就是说，如果工作表中有1000000条记录，则数据表应根据当前循环中foreach当前项的位置，以100条为一批进行数据获取。
欢迎任何指针或建议。
看一下NPOI库，以读取Excel文件并尝试完成所需的操作。 - Stefano Cavion
我已经使用了NPOI库和ClosedXML来读取Excel文件，并将行作为批量加载而不是一次性加载。为此，我编写了自定义的枚举器enumerator，在其中定义了可配置的批量大小，并在moveNext中检查批量大小，如果需要，则加载下一批行。但是，加载是通过从当前位置迭代每一行来进行的。这样可以工作，但性能不佳，请问有什么不同的方法可以实现呢？ - user3048027
4
请提供您的示例代码，有人将能够提供帮助。 - Sekhar
你有什么期望？你认为“不佳的表现”是什么？ - Tu deschizi eu inchid
6个回答
0

**我现在没有带有Visual Studio的PC，所以这段代码还没有经过测试，直到我晚些时候才能测试它。

但它仍然会给你一个大致的思路，告诉你需要做什么。

private void ExcelDataPages(int firstRecord, int numberOfRecords)
{
    
    Excel.Application dataApp = new Excel.Application(); 
    Excel.Workbook dataWorkbook = new Excel.Workbook();
    int x = 0;
    
    dataWorkbook.DisplayAlerts = false;
    dataWorkbook.Visible = false;
    dataWorkbook.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityLow;
    dataWorkbook = dataApp.Open(@"C:\Test\YourWorkbook.xlsx");
    
    try
    {
        Excel.Worksheet dataSheet = dataWorkbook.Sheet("Name of Sheet");
        
        while (x < numberOfRecords)
        {
            Range currentRange = dataSheet.Rows[firstRecord + x]; //For all columns in row 
    

            foreach (Range r in currentRange.Cells) //currentRange represents all the columns in the row
            {
                // do what you need to with the Data here.
            }
             x++;
        }
    }
    catch (Exception ex)
    {
        //Enter in Error handling
    }

    dataWorkbook.Close(false); //Depending on how quick you will access the next batch of data, you may not want to close the Workbook, reducing load time each time.  This may also mean you need to move the open of the workbook to a higher level in your class, or if this is the main process of the app, make it static, stopping the garbage collector from destroying the connection.
    dataApp.Quit();

}
0

请尝试以下代码 - 它使用NuGet包DocumentFormat.OpenXml。 代码来自Using OpenXmlReader，但我修改了它以向DataTable添加数据。由于您需要多次从同一Excel文件读取数据，因此最好使用SpreadSheetDocument的实例打开Excel文件，并在完成后将其处理掉。由于SpreedSheetDocument实例需要在应用程序退出之前被处理掉，因此使用了IDisposable。

在“ToDo”处，您需要使用自己的代码替换创建DataTable列的代码，以创建适合您项目的正确列。

我使用包含大约15,000行的Excel文件测试了下面的代码。每次读取100行时，第一次读取大约需要500毫秒-800毫秒，而随后的读取大约需要100毫秒-400毫秒。

创建一个类（名称：HelperOpenXml）

HelperOpenXml.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data;
using System.Diagnostics;

namespace ExcelReadSpecifiedRowsUsingOpenXml
{
    public class HelperOpenXml : IDisposable
    {
        public string Filename { get; private set; } = string.Empty;
        public int RowCount { get; private set; } = 0;

        private SpreadsheetDocument spreadsheetDocument = null;

        private DataTable dt = null;
        

        public HelperOpenXml(string filename)
        {
            this.Filename = filename;
        }

        public void Dispose()
        {
            if (spreadsheetDocument != null)
            {
                try
                {
                    spreadsheetDocument.Dispose();
                    dt.Clear();
                }
                catch(Exception ex)
                {
                    throw ex;
                }
            }
        }

        public DataTable GetRowsSax(int startRow, int endRow, bool firstRowIsHeader = false)
        {
            int startIndex = startRow;
            int endIndex = endRow;

            if (firstRowIsHeader)
            {
                //if first row is header, increment by 1
                startIndex = startRow + 1;
                endIndex = endRow + 1;
            }

            if (spreadsheetDocument == null)
            {
                //create new instance
                spreadsheetDocument = SpreadsheetDocument.Open(Filename, false);

                //create new instance
                dt = new DataTable();

                //ToDo: replace 'dt.Columns.Add(...)' below with your code to create the DataTable columns
                //add columns to DataTable
                dt.Columns.Add("A");
                dt.Columns.Add("B");
                dt.Columns.Add("C");
                dt.Columns.Add("D");
                dt.Columns.Add("E");
                dt.Columns.Add("F");
                dt.Columns.Add("G");
                dt.Columns.Add("H");
                dt.Columns.Add("I");
                dt.Columns.Add("J");
                dt.Columns.Add("K");

            }
            else
            {
                //remove existing data from DataTable
                dt.Rows.Clear(); 

            }

            WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;

            int numWorkSheetParts = 0;

            foreach (WorksheetPart worksheetPart in workbookPart.WorksheetParts)
            {
                using (OpenXmlReader reader = OpenXmlReader.Create(worksheetPart))
                {
                    int rowIndex = 0;

                    //use the reader to read the XML
                    while (reader.Read())
                    {
                        if (reader.ElementType == typeof(Row))
                        {
                            reader.ReadFirstChild();

                            List<string> cValues = new List<string>();
                            int colIndex = 0;
                            do
                            {
                                //only get data from desired rows
                                if ((rowIndex > 0 && rowIndex >= startIndex && rowIndex <= endIndex) ||
                                (rowIndex == 0 && !firstRowIsHeader && rowIndex >= startIndex && rowIndex <= endIndex))
                                {

                                    if (reader.ElementType == typeof(Cell))
                                    {
                                        Cell c = (Cell)reader.LoadCurrentElement();

                                        string cellRef = c.CellReference; //ex: A1, B1, ..., A2, B2

                                        string cellValue = string.Empty;

                                        //string/text data is stored in SharedString
                                        if (c.DataType != null && c.DataType == CellValues.SharedString)
                                        {
                                            SharedStringItem ssi = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(int.Parse(c.CellValue.InnerText));

                                            cellValue = ssi.Text.Text;
                                        }
                                        else
                                        {
                                            cellValue = c.CellValue.InnerText;
                                        }

                                        //Debug.WriteLine("{0}: {1} ", c.CellReference, cellValue);

                                        //add value to List which is used to add a row to the DataTable
                                        cValues.Add(cellValue);
                                    }
                                }

                                colIndex += 1; //increment

                            } while (reader.ReadNextSibling());

                            if (cValues.Count > 0)
                            {
                                //if List contains data, use it to add row to DataTable
                                dt.Rows.Add(cValues.ToArray()); 
                            }

                            rowIndex += 1; //increment

                            if (rowIndex > endIndex)
                            {
                                break; //exit loop
                            }
                        }
                    }
                }

                numWorkSheetParts += 1; //increment
            }

            DisplayDataTableData(dt); //display data in DataTable

            return dt;
        }

        
        private void DisplayDataTableData(DataTable dt)
        {
            foreach (DataColumn dc in dt.Columns)
            {
                Debug.WriteLine("colName: " + dc.ColumnName);
            }

            foreach (DataRow r in dt.Rows)
            {
                Debug.WriteLine(r[0].ToString() + " " + r[1].ToString());
            }
        }

    }
}

用法:

private string excelFilename = @"C:\Temp\Test.xlsx";
private HelperOpenXml helperOpenXml = null;

            ...

private void GetData(int startIndex, int endIndex, bool firstRowIsHeader)
{
    helperOpenXml.GetRowsSax(startIndex, endIndex, firstRowIsHeader);
}

注意：在应用程序退出之前，一定要调用Dispose()（例如：helperOpenXml.Dispose();）。

更新：

OpenXML将日期存储为自1900年1月1日以来的天数。对于1900年1月1日之前的日期，它们存储在SharedString中。有关更多信息，请参见使用Open XML SDK从xlsx读取日期。

以下是代码片段：

Cell c = (Cell)reader.LoadCurrentElement();
             ...
string cellValue = string.Empty
             ...
cellValue = c.CellValue.InnerText;

double dateCellValue = 0;
Double.TryParse(cellValue, out dateCellValue);

DateTime dt = DateTime.FromOADate(dateCellValue);

cellValue = dt.ToString("yyyy/MM/dd");
我在处理具有日期值和以1.71E + 15格式存储的大数字的单元格时遇到了问题。有人可以帮忙吗？ - user3048027
@user3048027：您没有提供任何样本数据。我在上面的帖子末尾添加了一段代码片段，展示了如何将单元格中的int值转换为Date值。不确定您遇到了什么问题，导致出现了“1.71E+15”。如果需要，可以使用Decimal.TryParse将字符串值“1.71E+15”转换为Decimal。然后使用Decimal.ToString（...）将其转换为所需的字符串格式。 - Tu deschizi eu inchid
0

我使用EPPlus DLL的这段代码，请不要忘记添加引用。但是应该检查是否符合您的要求。

public DataTable ReadExcelDatatable(bool hasHeader = true)
{
    using (var pck = new OfficeOpenXml.ExcelPackage())
    {
        using (var stream = File.OpenRead(this._fullPath))
        {
            pck.Load(stream);
        }

        var ws = pck.Workbook.Worksheets.First();

        DataTable tbl = new DataTable();

        int i = 1;
        foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
        {
            //table head
            tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));

            tbl.Columns.Add(_tableHead[i]);
            i++;
        }

        var startRow = hasHeader ? 2 : 1;
        
        for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
        {
            var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
            DataRow row = tbl.Rows.Add();
            foreach (var cell in wsRow)
            {
                row[cell.Start.Column - 1] = cell.Text;
            }
        }

        return tbl;
    }
}
0
另一个简单的替代方案是：查看NUGET包ExcelDataReader，附加信息请参见https://github.com/ExcelDataReader/ExcelDataReader 使用示例： 
[Fact] 
void Test_ExcelDataReader() 
{
    
    System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
    var scriptPath = Path.GetDirectoryName(Util.CurrentQueryPath); // LinqPad script path
    var filePath = $@"{scriptPath}\TestExcel.xlsx";
    using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
    {
        // Auto-detect format, supports:
        //  - Binary Excel files (2.0-2003 format; *.xls)
        //  - OpenXml Excel files (2007 format; *.xlsx, *.xlsb)
        using (var reader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream))
        {
            var result = reader.AsDataSet();
            // The result of each spreadsheet is in result.Tables
            var t0 = result.Tables[0];
            Assert.True(t0.Rows[0][0].Dump("R0C0").ToString()=="Hello", "Expected 'Hello'");
            Assert.True(t0.Rows[0][1].Dump("R0C1").ToString()=="World!", "Expected 'World!'");          
        } // using
    } // using
} // fact

在开始阅读之前，您需要按照以下方式设置编码提供程序：

 System.Text.Encoding.RegisterProvider(
      System.Text.CodePagesEncodingProvider.Instance);

单元格的寻址方式如下：

 var t0 = result.Tables[0]; // table 0 is the first worksheet
 var cell = t0.Rows[0][0];  // on table t0, read cell row 0 column 0

你可以轻松地通过以下方式使用 for 循环遍历行和列：

for (int r = 0; r < t0.Rows.Count; r++)
{
    var row = t0.Rows[r];
    var columns = row.ItemArray;
    for (int c = 0; c < columns.Length; c++)
    {
        var cell = columns[c];
        cell.Dump();
    }
}
0

我会给你一个不同的答案。如果将一百万行数据加载到DataTable中性能较差，可以考虑使用驱动程序来加载数据：如何高效地打开大型Excel文件

DataSet excelDataSet = new DataSet();

string filePath = @"c:\temp\BigBook.xlsx";

// For .XLSXs we use =Microsoft.ACE.OLEDB.12.0;, for .XLS we'd use Microsoft.Jet.OLEDB.4.0; with  "';Extended Properties=\"Excel 8.0;HDR=YES;\"";
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filePath + "';Extended Properties=\"Excel 12.0;HDR=YES;\"";

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open();
    OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
    ("select * from [Sheet1$]", conn);
    objDA.Fill(excelDataSet);
    //dataGridView1.DataSource = excelDataSet.Tables[0];
}

接下来，使用DataView过滤DataSet的DataTable。使用DataView的RowFilter属性，您可以根据列值指定行的子集。

DataView prodView = new DataView(excelDataSet.Tables[0],  
"UnitsInStock <= ReorderLevel",  
"SupplierID, ProductName",  
DataViewRowState.CurrentRows);

参考：https://www.c-sharpcorner.com/article/dataview-in-C-Sharp/

或者，您也可以直接使用DataTables的DefaultView RowFilter：

excelDataSet.Tables[0].DefaultView.RowFilter = "Amount >= 5000 and Amount <= 5999 and Name = 'StackOverflow'";
0
嗨。感谢您的输入。我阅读了提供的链接，但示例仅显示如何逐个单元格地读取。我尝试查找如何读取特定的部分/区域或几行特定的行，但没有找到相关信息。请问是否有相关示例或文档可以提供参考？ - user3048027
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接