c#npoi分段读取大文件excel到DataTable

以下是使用NPOI流模式读取大文件Excel并将数据读取到DataTable中的示例代码：

using System.Data;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

public DataTable ReadExcelToDataTable(string filePath, int batchSize = 1000)
{
    DataTable dt = new DataTable();

    using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
    {
        var workbook = WorkbookFactory.Create(stream, ImportOption.All);

        var sheet = workbook.GetSheetAt(0);
        var rowCount = sheet.LastRowNum;

        // 获取表头
        var headerRow = sheet.GetRow(0);
        var cellCount = headerRow.LastCellNum;

        for (int i = 0; i < cellCount; i++)
        {
            var cell = headerRow.GetCell(i);
            if (cell == null) continue;

            var columnName = cell.ToString().Trim();
            if (string.IsNullOrEmpty(columnName)) continue;

            dt.Columns.Add(columnName);
        }

        // 批量读取数据
        var startIndex = 1;
        while (startIndex <= rowCount)
        {
            var endIndex = startIndex + batchSize - 1;
            if (endIndex > rowCount) endIndex = rowCount;

            for (int i = startIndex; i <= endIndex; i++)
            {
                var row = sheet.GetRow(i);
                if (row == null) continue;

                var dataRow = dt.NewRow();

                for (int j = 0; j < cellCount; j++)
                {
                    var cell = row.GetCell(j);
                    if (cell == null) continue;

                    dataRow[j] = cell.ToString();
                }

                dt.Rows.Add(dataRow);
            }

            startIndex += batchSize;
        }

        workbook.Close();
    }

    return dt;
}

这个示例代码将数据读取到DataTable中，并且支持设置批量读取的大小。您可以根据实际需求进行修改，例如设置读取的起始行、批量读取等等。