ASP.NET MVC中用EF导入动态表名Excel至SQL Server表可行吗？

答案：可以实现

你的现有代码已经具备基础导入逻辑，但在动态获取Excel工作表名称的部分存在问题，导致当前代码只能读取固定名称的工作表。以下是修正后的完整实现，同时优化了部分冗余逻辑：

核心修正点 #

• 修复动态获取Excel工作表名称的逻辑，不再硬编码工作表名
• 合并重复的数据插入逻辑，减少代码冗余
• 优化资源释放和异常处理，提升代码健壮性

完整实现代码 #

[HttpPost]
public ActionResult ExcelView(HttpPostedFileBase postedFile)
{
    try
    {
        if (postedFile == null)
        {
            ViewBag.Result = "请选择要上传的Excel文件";
            return View();
        }

        // 保存上传的Excel文件到服务器指定目录
        string uploadPath = Server.MapPath("~/Uploads/");
        if (!Directory.Exists(uploadPath))
        {
            Directory.CreateDirectory(uploadPath);
        }
        string filePath = Path.Combine(uploadPath, Path.GetFileName(postedFile.FileName));
        string extension = Path.GetExtension(postedFile.FileName);
        postedFile.SaveAs(filePath);

        // 根据Excel版本选择对应的连接字符串
        string conString = string.Empty;
        switch (extension.ToLower())
        {
            case ".xls": // Excel 97-03版本
                conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": // Excel 07及以上版本
                conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
            default:
                ViewBag.Result = "不支持的文件格式";
                return View();
        }
        conString = string.Format(conString, filePath);

        DataTable excelData = new DataTable();
        string sheetName = string.Empty;

        // 连接Excel并动态获取工作表名称
        using (OleDbConnection connExcel = new OleDbConnection(conString))
        {
            connExcel.Open();
            // 获取Excel中的所有工作表元数据
            DataTable schemaTable = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            if (schemaTable == null || schemaTable.Rows.Count == 0)
            {
                ViewBag.Result = "Excel文件中未找到有效工作表";
                return View();
            }
            // 获取第一个工作表的名称（需处理多个工作表可循环遍历schemaTable.Rows）
            sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString();
            connExcel.Close();

            // 读取目标工作表数据到DataTable
            using (OleDbCommand cmdExcel = new OleDbCommand($"SELECT * FROM [{sheetName}]", connExcel))
            using (OleDbDataAdapter odaExcel = new OleDbDataAdapter(cmdExcel))
            {
                connExcel.Open();
                odaExcel.Fill(excelData);
                connExcel.Close();
            }
        }

        if (excelData.Rows.Count == 0)
        {
            ViewBag.Result = "Excel工作表中无有效数据";
            return View();
        }

        // 使用Entity Framework操作SQL Server数据库
        using (NECCOI_DBEntities1 entities = new NECCOI_DBEntities1())
        {
            // 清空现有数据（若业务需增量导入可删除此段逻辑）
            if (entities.tblStores.Any())
            {
                entities.tblStores.RemoveRange(entities.tblStores);
                entities.SaveChanges();
                Log.Error("已清空现有数据，准备导入新数据");
            }
            else
            {
                Log.Error("数据库无现有数据，直接导入新数据");
            }

            // 遍历Excel数据并插入到数据库表
            foreach (DataRow row in excelData.Rows)
            {
                entities.tblStores.Add(new tblStore
                {
                    Div = Convert.ToString(row["Div"]),
                    Mkt = Convert.ToString(row["Mkt"]),
                    Store = Convert.ToString(row["Store"]),
                    IP = Convert.ToString(row["IP"]),
                    TID = Convert.ToString(row["TID"]),
                    Corp_Fran_flag = Convert.ToString(row["Corp Fran flag"]),
                    Address = Convert.ToString(row["Address"]),
                    City = Convert.ToString(row["City"]),
                    County = Convert.ToString(row["County"]),
                    State = Convert.ToString(row["State"]),
                    Zip = Convert.ToString(row["Zip"]),
                    Hours = Convert.ToString(row["Hours"]),
                    tmz = Convert.ToString(row["tmz"]),
                    MO_ = Convert.ToString(row["MO?"]),
                    Gas_ = Convert.ToString(row["Gas?"]),
                    Gas_integrated = Convert.ToString(row["Gas integrated"]),
                    Gas_Vendor = Convert.ToString(row["Gas Vendor"]),
                    Reg = int.TryParse(row["Reg"].ToString(), out int regValue) ? regValue : 0,
                    Citgo_TID = Convert.ToString(row["Citgo TID"]),
                    Voice = Convert.ToString(row["Voice"]),
                    Store_Closed_Date = row["Store Closed Date"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(row["Store Closed Date"]),
                    RIS_Installation = row["RIS Installation"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(row["RIS Installation"]),
                    Planned_Open_date = row["Planned Open date"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(row["Planned Open date"])
                });
            }

            entities.SaveChanges();
            ViewBag.Result = "文件导入成功";
            Log.Error(ViewBag.Result);
        }

        return View();
    }
    catch (Exception ex)
    {
        Log.Error(ex.Message);
        ViewBag.Result = $"导入失败：{ex.Message}";
        return View();
    }
}

Web.config中的Excel连接字符串配置 #

需在项目配置文件中添加以下连接字符串：

<connectionStrings>
  <add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;'" />
  <add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;'" />
</connectionStrings>

关键说明 #

1. 动态工作表适配：通过GetOleDbSchemaTable方法读取Excel的工作表元数据，彻底摆脱硬编码表名的限制
2. 数据类型安全：使用int.TryParse替代强制类型转换，避免因Excel数据格式错误导致的异常
3. 资源自动释放：所有数据库连接、EF上下文均使用using语句包裹，确保资源及时释放
4. 业务灵活调整：若无需覆盖现有数据，可删除RemoveRange相关代码，改为增量导入逻辑

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