使用ClosedXML与OpenFileDialog将Excel导入DataGrid时的文件路径传递异常问题

Looks like you’re hitting that error because you’re using placeholder strings ("...") instead of the actual file path and sheet name from your OpenFileDialog. Let’s fix this step by step, plus tweak your method to work as intended:

Step 1: Update the ImportExceltoDataTable Method #

Your current method defines a sheetName parameter but doesn’t actually use it—it always loads the first worksheet. Let’s adjust it to respect the passed sheet name (with a fallback to the first sheet if no name is provided):

public static DataTable ImportExceltoDataTable(string filePath, string sheetName) {
    using (XLWorkbook wb = new(filePath)) {
        // Use the provided sheet name, or fall back to the first worksheet
        IXLWorksheet ws = string.IsNullOrWhiteSpace(sheetName) 
            ? wb.Worksheet(1) 
            : wb.Worksheet(sheetName);
        
        DataTable dt = new();
        bool firstRow = true;

        foreach (IXLRow row in ws.Rows()) {
            if (firstRow) {
                // Add column names from the first row (only cells with data)
                foreach (IXLCell cell in row.CellsUsed()) {
                    dt.Columns.Add(cell.CachedValue.ToString());
                }
                firstRow = false;
            } else {
                dt.Rows.Add();
                int i = 0;
                // Only process cells that contain data to avoid empty column issues
                foreach (IXLCell cell in row.CellsUsed()) {
                    dt.Rows[dt.Rows.Count - 1][i] = cell.CachedValue.ToString();
                    i++;
                }
            }
        }
        return dt;
    }
}

Step 2: Pass the Correct Path from OpenFileDialog #

The OpenFileDialog has a FileName property that gives you the full, valid path of the selected file. Replace the placeholders with this value:

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

if (of.ShowDialog() == true) {
    // Pass the selected file path, use empty string to load the first worksheet
    DataTable excelData = ImportExceltoDataTable(of.FileName, string.Empty);
    dataGrid.ItemsSource = excelData.DefaultView;
}

Why This Fixes the Error #

• The of.FileName value is a valid full path to your .xlsx file, so ClosedXML can recognize the file extension and load the file without throwing the "Empty extension is not supported" exception.
• We updated the method to actually use the sheetName parameter, giving you the flexibility to load specific sheets if needed (just pass the sheet name instead of an empty string).
• Using CellsUsed() instead of all cells helps avoid issues with empty columns or rows in your Excel file.

Extra Recommendations #

• Add try-catch blocks to handle edge cases like missing files, invalid sheet names, or corrupted Excel files.
• Ensure you’ve installed the latest version of the ClosedXML NuGet package in your project.

内容的提问来源于stack exchange，提问作者Ole M