I'm trying to import an Excel sheet into a DataTable using NPOI, use the first row of the Excel sheet as headers, then bind the DataTable to a DataGrid in WPF. However, the first two columns of data are completely missing; everything else is fine.
I'm also not sure why there's a 4th empty row being created, though I'm not as concerned about that as compared to the missing data. All the data in Excel are text (including the numbers), so there are no differences in data type between the columns of data.
Here's the code I tried using NPOI:
private void CreateDataTable()
{
String filePath = @"filepath";
using (var fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
var workbook = WorkbookFactory.Create(fileStream);
var sheet = workbook.GetSheetAt(0);
var dt = new DataTable();
var headerRow = sheet.GetRow(0);
dt.Columns.Clear();
dt.Rows.Clear();
// Create columns based on first row in Excel sheet (headers).
foreach (var cell in headerRow)
{
dt.Columns.Add(cell.ToString(), typeof(string));
}
// Starting from 2nd row in Excel sheet, create dataRow and add to DataTable.
for (int i = 1; i <= sheet.LastRowNum; i++)
{
var dataRow = dt.NewRow();
var row = sheet.GetRow(i);
for (int j = 0; j < dt.Columns.Count; j++)
{
var cell = row.GetCell(j);
dataRow[j] = cell?.ToString() ?? string.Empty;
}
dt.Rows.Add(dataRow);
}
// Bind to DataGrid.
DataGrid.ItemsSource = dt.DefaultView;
}
}
I'm still pretty new to C# (and programming in general), so I hope to learn something from this :) Thank you so much for your time!
