Excel导入SQL：如何实现免指定工作表名与自动列映射？

当然可行！我们可以利用OLEDB的元数据查询能力和SQL Server的表结构信息，实现完全自动化的Excel导入，无需手动指定工作表和列映射。下面是具体的实现步骤和修改后的完整代码：

1. 自动获取Excel工作表名 #

OLEDB连接可以直接读取Excel的Schema信息，从中筛选出有效的工作表（通常名称以$结尾）。我们可以用GetOleDbSchemaTable方法获取所有表，然后过滤出工作表：

// 先打开Excel连接获取工作表名
excelConnection.Open();
DataTable schemaTable = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// 筛选出真正的工作表（排除系统隐藏表，只保留以$结尾的名称）
string targetSheetName = schemaTable.AsEnumerable()
    .Where(row => !string.IsNullOrEmpty(row["TABLE_NAME"].ToString()) 
                  && row["TABLE_NAME"].ToString().EndsWith("$"))
    .Select(row => row["TABLE_NAME"].ToString())
    .FirstOrDefault(); // 这里取第一个工作表，若要支持多表可改为遍历或让用户选择
excelConnection.Close(); // 先关闭连接，后续查询时再重新打开

2. 自动列映射（匹配Excel与SQL列） #

要实现动态列映射，我们需要：

• 读取Excel数据的列名
• 读取目标SQL表的列名
• 自动匹配相同名称的列（建议忽略大小写，避免因大小写不一致导致匹配失败）

具体代码如下：

// 获取目标SQL表的所有列名
List<string> sqlTableColumns = new List<string>();
using (SqlConnection sqlConn = new SqlConnection(strConnection))
{
    sqlConn.Open();
    // 查询目标表的列信息
    DataTable sqlSchema = sqlConn.GetSchema("Columns", new string[] { null, null, "Dados" });
    sqlTableColumns = sqlSchema.AsEnumerable()
        .Select(row => row["COLUMN_NAME"].ToString())
        .ToList();
}

// 读取Excel数据时获取列名
dReader = cmd.ExecuteReader();
DataTable excelSchema = dReader.GetSchemaTable();
List<string> excelColumns = excelSchema.AsEnumerable()
    .Select(row => row["ColumnName"].ToString())
    .ToList();

// 自动添加匹配的列映射
using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
{
    sqlBulk.DestinationTableName = "Dados";
    foreach (string excelCol in excelColumns)
    {
        // 忽略大小写匹配SQL列
        string matchingSqlCol = sqlTableColumns.FirstOrDefault(sqlCol => 
            string.Equals(sqlCol, excelCol, StringComparison.OrdinalIgnoreCase));
        if (!string.IsNullOrEmpty(matchingSqlCol))
        {
            sqlBulk.ColumnMappings.Add(excelCol, matchingSqlCol);
        }
        // 可选：如果需要处理列名不一致的情况，可以在这里添加自定义映射规则
        // 比如替换空格、特殊字符等
    }
    sqlBulk.WriteToServer(dReader);
}

修改后的完整代码 #

整合以上逻辑，修正原代码中的冗余（比如重复的路径变量、无用的SqlDataAdapter），最终代码如下：

protected void Upload_Click(object sender, EventArgs e) 
{ 
    // 处理文件上传路径（统一变量，避免重复）
    string uploadFolder = Server.MapPath("~/Nova pasta/");
    string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
    string excelPath = Path.Combine(uploadFolder, fileName);
    FileUpload1.SaveAs(excelPath);

    // SQL连接字符串
    String strConnection = @"Data Source=PEDRO-PC\SQLEXPRESS;Initial Catalog=costumizado;Persist Security Info=True;User ID=sa;Password=1234"; 
    // Excel连接字符串
    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\""; 

    // 1. 自动获取工作表名
    string targetSheetName = null;
    using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
    {
        excelConnection.Open();
        DataTable schemaTable = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        targetSheetName = schemaTable.AsEnumerable()
            .Where(row => row["TABLE_NAME"].ToString().EndsWith("$"))
            .Select(row => row["TABLE_NAME"].ToString())
            .FirstOrDefault();
    }

    if (string.IsNullOrEmpty(targetSheetName))
    {
        // 处理没有找到工作表的情况，比如提示用户
        Response.Write("未找到有效的Excel工作表");
        return;
    }

    // 2. 读取Excel数据并自动映射列导入SQL
    using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
    {
        OleDbCommand cmd = new OleDbCommand($"Select * from [{targetSheetName}]", excelConnection); 
        excelConnection.Open(); 
        OleDbDataReader dReader = cmd.ExecuteReader(); 

        // 获取SQL表列名
        List<string> sqlTableColumns = new List<string>();
        using (SqlConnection sqlConn = new SqlConnection(strConnection))
        {
            sqlConn.Open();
            DataTable sqlSchema = sqlConn.GetSchema("Columns", new string[] { null, null, "Dados" });
            sqlTableColumns = sqlSchema.AsEnumerable()
                .Select(row => row["COLUMN_NAME"].ToString())
                .ToList();
        }

        // 获取Excel列名
        DataTable excelSchema = dReader.GetSchemaTable();
        List<string> excelColumns = excelSchema.AsEnumerable()
            .Select(row => row["ColumnName"].ToString())
            .ToList();

        // 执行批量导入
        using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection)) 
        { 
            sqlBulk.DestinationTableName = "Dados"; 
            // 自动添加匹配的列映射
            foreach (string excelCol in excelColumns)
            {
                string matchingSqlCol = sqlTableColumns.FirstOrDefault(sqlCol => 
                    string.Equals(sqlCol, excelCol, StringComparison.OrdinalIgnoreCase));
                if (!string.IsNullOrEmpty(matchingSqlCol))
                {
                    sqlBulk.ColumnMappings.Add(excelCol, matchingSqlCol);
                }
            }
            sqlBulk.WriteToServer(dReader); 
        } 
        excelConnection.Close(); 
    }

    // 导入完成提示
    Response.Write("Excel数据导入成功！");
}

注意事项 #

• 多工作表处理：如果你的Excel有多个工作表，上面的代码默认取第一个，你可以改成让用户选择（比如用DropDownList展示所有工作表名），或者遍历所有工作表批量导入。
• 列名匹配规则：如果Excel和SQL的列名有差异（比如Excel是DataMov，SQL是Data Mov.），可以在匹配时添加自定义规则（比如去除空格、替换特殊字符）。
• 数据类型兼容性：确保Excel列的数据类型和SQL列兼容，比如Excel的日期列对应SQL的datetime类型，避免导入时出现类型转换错误。
• IMEX=1设置：这个参数可以让OLEDB把混合数据类型的列当作文本读取，避免丢失数据，建议保留。

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