I want to load an Excel-file into my DataGrid. Using ClosedXML.
I have this method:
public static DataTable ImportExceltoDataTable(string filePath, string sheetName) {
using (XLWorkbook wb = new(filePath)) {
IXLWorksheet ws = wb.Worksheet(1);
DataTable dt = new();
bool firstRow = true;
foreach (IXLRow row in ws.Rows()) {
if (firstRow) {
foreach (IXLCell cell in row.Cells()) {
dt.Columns.Add(cell.CachedValue.ToString());
}
firstRow = false;
} else {
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells(row.FirstCellUsed().Address.ColumnNumber, row.LastCellUsed().Address.ColumnNumber)) {
dt.Rows[dt.Rows.Count - 1][i} = cell.CachedValue.ToString();
i++;
}
}
}
return dt;
}
}
Here's my click-event:
OpenFileDialog of = new();
of.Filter = "Excel Files | *.xlsx;";
of.Title = "Import Excel file.";
if (of.ShowDialog()==true) {
dataGrid.ItemsSource = ImportExceltoDataTable("...", "...").DefaultView;
}
I am trying to achieve the following
- Click a button, choose an Excel-file, populate my
DataGridwith its contents.
Updated after adding solution provided by @mm8:
I now get an Empty extension is not supported error, as I don't know how to connect the OpenFileDialog-selection of the click-event, to the initiation of the DataTable.
Very grateful for any assistance!