WPF中实现点击打开按钮将选中Excel文件数据加载至DataGrid的技术求助

Hey there! Since you're new to C# and WPF, let's walk through how to get your selected Excel file's data into that DataGrid. Here's a straightforward approach using the EPPlus library (it's super popular for handling Excel files in .NET):

Step 1: Install EPPlus via NuGet #

First, you need to add the EPPlus package to your project. Open the NuGet Package Manager (right-click your project → Manage NuGet Packages), search for EPPlus, and install the latest version.

Step 2: Update Your Code #

Add these using directives at the top of your code file:

using OfficeOpenXml;
using System.Data;
using System.IO;

Then modify your button click event and add a helper method to read Excel data into a DataTable (which works great with DataGrid):

private void btndilog_Click(object sender, RoutedEventArgs e)
{
    var dialog = new Microsoft.Win32.OpenFileDialog();
    dialog.FileName = "Select Excel File"; // Updated this to be more intuitive
    dialog.DefaultExt = ".xlsx";
    dialog.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";

    bool? result = dialog.ShowDialog();

    if (result == true)
    {
        string filename = dialog.FileName;
        try
        {
            // Load Excel data into DataTable
            DataTable excelData = ReadExcelToDataTable(filename);
            // Bind the DataTable to your DataGrid (replace 'dataGrid1' with your actual DataGrid name)
            dataGrid1.ItemsSource = excelData.DefaultView;
            // Ensure DataGrid auto-generates columns (you can also set this in XAML)
            dataGrid1.AutoGenerateColumns = true;
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Oops, something went wrong: {ex.Message}");
        }
    }
}

private DataTable ReadExcelToDataTable(string filePath)
{
    DataTable dataTable = new DataTable();
    
    // Set EPPlus license context (required for newer versions)
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // Switch to LicenseContext.Commercial for business use
    
    using (ExcelPackage package = new ExcelPackage(new FileInfo(filePath)))
    {
        ExcelWorksheet worksheet = package.Workbook.Worksheets[0]; // Grab the first worksheet
        
        // Add columns to DataTable using Excel's header row (row 1)
        for (int col = 1; col <= worksheet.Dimension.End.Column; col++)
        {
            dataTable.Columns.Add(worksheet.Cells[1, col].Text);
        }
        
        // Add rows to DataTable from Excel's data rows (starting at row 2)
        for (int row = 2; row <= worksheet.Dimension.End.Row; row++)
        {
            DataRow dataRow = dataTable.NewRow();
            for (int col = 1; col <= worksheet.Dimension.End.Column; col++)
            {
                dataRow[col - 1] = worksheet.Cells[row, col].Text;
            }
            dataTable.Rows.Add(dataRow);
        }
    }
    
    return dataTable;
}

Step 3: Quick XAML Check #

Make sure your DataGrid in XAML has AutoGenerateColumns="True" (or you can set it in code like we did above). Example XAML snippet:

<DataGrid x:Name="dataGrid1" AutoGenerateColumns="True" Margin="10"/>

Notes to Keep in Mind #

• EPPlus License: If you're building this for commercial use, you'll need a commercial license for EPPlus. For non-commercial projects, the non-commercial license works perfectly.
• Handling Edge Cases: The try-catch block helps catch issues like corrupted files or unexpected formatting. You can expand it to handle specific errors if needed.
• Multiple Worksheets: If you need to let users pick which worksheet to load, you can add a step to list worksheet names and let them select one before reading data.

That's it! When you select an Excel file now, its data should populate your DataGrid automatically. Let me know if you hit any roadblocks!

内容的提问来源于stack exchange，提问作者Suyesh Pandey