How to use DataTable as ItemsSource of my DataGrid

Viewed 119

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 DataGrid with 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!

1 Answers

You can set the ItemsSource to the DefaultView of the DataTable:

dataGrid.ItemsSource = ImportExceltoDataTable("...", "...").DefaultView;

Unlike a DataTable, a DataView implements the required IEnumerable interface.

Related