C# NPOI流读取Excel到DataTable
时间: 2024-03-21 20:42:23 浏览: 13
可以使用NPOI库来读取Excel文件并将其转换为DataTable对象。以下是示例代码:
```csharp
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.IO;
public static DataTable ReadExcelToDataTable(string filePath, bool isFirstRowHeader = true)
{
DataTable dataTable = new DataTable();
using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = null;
if (Path.GetExtension(filePath) == ".xls")
{
workbook = new HSSFWorkbook(fileStream);
}
else if (Path.GetExtension(filePath) == ".xlsx")
{
workbook = new XSSFWorkbook(fileStream);
}
if (workbook != null)
{
ISheet sheet = workbook.GetSheetAt(0);
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
ICell cell = headerRow.GetCell(i);
if (cell != null)
{
string columnName = isFirstRowHeader ? cell.StringCellValue : "Column" + i;
DataColumn column = new DataColumn(columnName);
dataTable.Columns.Add(column);
}
}
int rowCount = sheet.LastRowNum;
for (int i = (isFirstRowHeader ? 1 : 0); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
DataRow dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dataTable.Rows.Add(dataRow);
}
}
}
}
return dataTable;
}
```
使用方法:
```csharp
DataTable dataTable = ReadExcelToDataTable("path/to/excel/file.xlsx");
```
其中,`isFirstRowHeader`参数表示Excel表格的第一行是否为表头。如果为`true`,则会将第一行作为表头。如果为`false`,则会自动为每一列生成一个列名。