export Excel to DataTable using NPOI

Question

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 ...

Answer 1

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);
}