如何通过C#按钮控件将多个Excel工作表导入对应SQL表？

Got it, let's walk through how to build this feature step by step. I'll cover everything from setup to core code, plus key things to watch out for.

准备工作 #

Before diving into code, make sure you have these sorted:

• NuGet Packages: Install two essential packages:
  • EPPlus (for reading Excel files—note: v4.x is free for non-commercial use; newer versions require a license)
  • Microsoft.Data.SqlClient (or System.Data.SqlClient for older .NET frameworks)
• Schema Matching: Ensure each Excel worksheet's columns match the corresponding SQL table's columns (names, data types, and order should align as much as possible to avoid mapping headaches).
• SQL Permissions: Make sure your application has INSERT permissions on the target SQL tables.

核心实现代码 #

Here's a complete example for a button click event. We'll use OpenFileDialog to let the user pick an Excel file, then loop through each worksheet to import data into its matching SQL table.

按钮点击事件代码 #

using OfficeOpenXml;
using Microsoft.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.IO;

private void btnImportExcel_Click(object sender, EventArgs e)
{
    // 让用户选择Excel文件
    using (OpenFileDialog openFileDialog = new OpenFileDialog())
    {
        openFileDialog.Filter = "Excel Files|*.xlsx;*.xls";
        if (openFileDialog.ShowDialog() != DialogResult.OK)
            return;

        string excelFilePath = openFileDialog.FileName;
        string sqlConnectionString = "Server=YOUR_SERVER_NAME;Database=YOUR_DB_NAME;Integrated Security=True;"; // 替换为你的连接字符串

        // 设置EPPlus的License上下文（针对v4.x+）
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // 如果是商业用途，请使用LicenseContext.Commercial并获取许可证

        try
        {
            using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFilePath)))
            {
                // 遍历Excel中的每个工作表
                foreach (ExcelWorksheet worksheet in package.Workbook.Worksheets)
                {
                    // 假设工作表名称和SQL表名称完全一致，你可以根据需要修改这个映射逻辑
                    string targetSqlTable = worksheet.Name;

                    // 将工作表数据转换为DataTable
                    DataTable dataTable = ConvertWorksheetToDataTable(worksheet);

                    if (dataTable.Rows.Count == 0)
                    {
                        MessageBox.Show($"工作表 {worksheet.Name} 没有数据，跳过导入。");
                        continue;
                    }

                    // 导入DataTable到SQL表
                    ImportDataTableToSql(dataTable, targetSqlTable, sqlConnectionString);
                }

                MessageBox.Show("所有工作表导入完成！");
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show($"导入过程出错: {ex.Message}");
        }
    }
}

// 辅助方法：将Excel工作表转换为DataTable
private DataTable ConvertWorksheetToDataTable(ExcelWorksheet worksheet)
{
    DataTable dataTable = new DataTable();

    // 添加列（假设第一行是表头）
    foreach (ExcelRangeBase cell in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column])
    {
        dataTable.Columns.Add(cell.Text);
    }

    // 添加行数据（从第二行开始）
    for (int row = 2; row <= worksheet.Dimension.End.Row; row++)
    {
        DataRow dataRow = dataTable.NewRow();
        for (int col = 1; col <= worksheet.Dimension.End.Column; col++)
        {
            var cellValue = worksheet.Cells[row, col].Value;
            dataRow[col - 1] = cellValue ?? DBNull.Value;
        }
        dataTable.Rows.Add(dataRow);
    }

    return dataTable;
}

// 辅助方法：使用SqlBulkCopy高效导入DataTable到SQL
private void ImportDataTableToSql(DataTable dataTable, string targetTable, string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = targetTable;
            bulkCopy.BatchSize = 1000; // 批量插入大小，可根据数据量调整

            // 如果Excel列名和SQL列名完全一致，自动映射；否则手动添加映射
            foreach (DataColumn column in dataTable.Columns)
            {
                bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
            }

            try
            {
                bulkCopy.WriteToServer(dataTable);
            }
            catch (Exception ex)
            {
                throw new Exception($"导入表 {targetTable} 时出错: {ex.Message}", ex);
            }
        }
    }
}

关键注意事项 #

• EPPlus License: If you're using this in a commercial application, make sure to get a valid license for EPPlus v5+. For non-commercial use, v4.x is free and works perfectly.
• Connection String: Replace YOUR_SERVER_NAME and YOUR_DB_NAME with your actual SQL server details. If using SQL authentication, add User ID=XXX;Password=XXX; to the connection string.
• Data Type Handling: The example uses raw cell values, but you might need to convert values to match SQL data types (e.g., dates, integers). You can modify the ConvertWorksheetToDataTable method to handle specific types, like checking worksheet.Cells[row, col].Value.GetType() and casting appropriately.
• Error Handling: The basic try-catch blocks help catch issues, but you might want to add more granular error handling (e.g., logging failed rows, validating data before insertion).
• Large Datasets: SqlBulkCopy is way more efficient than inserting rows one by one, especially for big datasets. Adjust the BatchSize based on your system's performance.

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