使用NPOI将Excel导出到DataTable [英] export Excel to DataTable using NPOI

问题描述

我想使用NPOI读取Excel Tables 2010 xlsx，然后将数据导出到DataTables，但不知道如何使用它。谁能一步一步教我如何将Excel导出到Datatable？我已经下载了NPOI.dll，已添加为参考，但不知道进一步... ...

I want to read Excel Tables 2010 xlsx using NPOI and then export data to DataTables but don't know how to use it. Can anyone show me step by step how to export Excel to Datatable? I have downloaded NPOI.dll, added to reference but don't know what further ...

推荐答案

有关最小代码您可以使用NPOI将Excel文件转换为数据集：

Here's about the minimum code you can use to convert an Excel file to a DataSet using NPOI:

IWorkbook workbook;
using (var stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
{
    workbook = new HSSFWorkbook(stream); // XSSFWorkbook for XLSX
}

var sheet = workbook.GetSheetAt(0); // zero-based index of your target sheet
var dataTable = new DataTable(sheet.SheetName);

// write the header row
var headerRow = sheet.GetRow(0);
foreach (var headerCell in headerRow)
{
    dataTable.Columns.Add(headerCell.ToString());
}

// write the rest
for(int i = 1; i< sheet.PhysicalNumberOfRows; i++)
{
    var sheetRow = sheet.GetRow(i);
    var dtRow = dataTable.NewRow();
    dtRow.ItemArray = dataTable.Columns
        .Cast<DataColumn>()
        .Select(c => sheetRow.GetCell(c.Ordinal, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString())
        .ToArray();
    dataTable.Rows.Add(dtRow);
}

这篇关于使用NPOI将Excel导出到DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！