Importing Excel to DataGrid using ClosedXML and OpenFileDialog

Question

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

And on a click-event, I am trying to pick my file using OpenFileDialog, see below:

OpenFileDialog of = new();
of.Filter = "Excel Files | *.xlsx;";
of.Title = "Import Excel file.";

if (of.ShowDialog()==true) {

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

But I do not know how to notify the DataTable that I've chosen a file in my OpenFileDialog. At the first line in the DataTable method, I get the following exception error:

System.ArgumentException: 'Empty extension is not supported'

Which makes sense... How can I tell it what file I've picked?

Answer 1

You may want to re-think your approach to reading the excel file. One possible issue is the if (firstRow) { … … statement, which is odd and makes a dangerous assumption. The code “assumes” that each column of data “has” a header cell. In other words, the number of columns added to the DataTable will be determined by the number of cells found (with some text in the cell) on the “FIRST” row. What if a column of data does NOT have a header cell?

Therefore, if there are any rows that have data to the right of the first row’s cells with headers, then the DataTable will not have the correct number of columns… and, when the code gets to these cells in the else portion below… the code will most likely crash when i goes beyond dt’s column count.

The code needs to guarantee that dt has the correct number of columns to avoid the problem described above. One way to help is to find the two cells that define the “top-left” cell where the data begins (as it may not necessarily always be the first cell in the worksheet) and “bottom-right” cell where the “last” cell with data is located.

Once we have these two cells (top-left and bottom-right)… then, we can determine how many columns are needed in the DataTable… and… we can almost guarantee that all the data in the worksheet will fit in the DataTable.

Below is one possible solution using the ideas described above. Note, the code below does not use a particular worksheet name and simply uses the first worksheet in the given workbook.

private void Button_Click(object sender, RoutedEventArgs e) {
  OpenFileDialog of = new OpenFileDialog();
  of.Filter = "Excel Files | *.xlsx;";
  of.Title = "Import Excel file.";
  if (of.ShowDialog() == true) {
    dataGrid.ItemsSource = ImportExceltoDataTable(of.FileName).DefaultView;
  }
}

public static DataTable ImportExceltoDataTable(string filePath) {
  using (XLWorkbook wb = new XLWorkbook(filePath)) {
    IXLWorksheet ws = wb.Worksheet(1);
    int tl_Row = ws.FirstCellUsed().Address.RowNumber;
    int tl_Col = ws.FirstCellUsed().Address.ColumnNumber;
    int br_Row = ws.LastCellUsed().Address.RowNumber;
    int br_Col = ws.LastCellUsed().Address.ColumnNumber;
    DataTable dt = new DataTable();
    // add dt columns using the first row of data
    for (int i = tl_Col; i <= br_Col; i++) {
      dt.Columns.Add(ws.Cell(tl_Row, i).CachedValue.ToString());
    }
    IXLRow currentRow;
    // add data from the worksheet to dt - we already used the first row of data for the columns
    for (int dtRow = 0; dtRow < br_Row - tl_Row; dtRow++) {
      currentRow = ws.Row(tl_Row + dtRow + 1);
      dt.Rows.Add();
      for (int dtCol = 0; dtCol < br_Col - tl_Col + 1; dtCol++) {
        dt.Rows[dtRow][dtCol] = currentRow.Cell(tl_Col + dtCol).CachedValue; 
      }
    }
    return dt;
  }
}

I hope this makes sense and helps.