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中，并且支持设置批量读取的大小。您可以根据实际需求进行修改，例如设置读取的起始行、批量读取等等。