使用 Excel 将 SQL 批量复制到 SQL Server，但进行数据修改

Question

我正在阅读 SQLBulkCopy 并希望使用它将数千行从 Excel 文档导入到 SQL Server。我一直在阅读直接执行此操作而不修改数据的文章。在执行 SQLBulkCopy 之前，我需要对 Excel 文档中的数据进行一些修改和验证。可以这样做吗？我假设从重载中我可以修改数据并创建一个大型 DataTable，并使用 WriteToServer 导入该 DataTable。

Answer 1

您可能需要一个 DataReader 或 DataSet，可以在导入之前对其进行迭代以进行验证/修改。

此实用程序可能会帮助您 - http://exceldatareader.codeplex.com/

Answer 2

这对我来说效果很好：

    public ActionResult Create(HttpPostedFileBase file)
    {
        string strConnection = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;

        //file upload path
        var fileName = Path.GetFileName(file.FileName);
        // store the file inside ~/App_Data/uploads folder
        var path = Path.Combine(Server.MapPath("~/App_Data/uploads"), fileName);
        file.SaveAs(path);

        //Create connection string to Excel work book
        string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
        //Create Connection to Excel work book
        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
        //Create OleDbCommand to fetch data from Excel
        excelConnection.Open();
        DataTable dt = new DataTable();

        dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        if (dt == null)
        {
            return null;
        }

        String[] excelSheets = new String[dt.Rows.Count];
        int t = 0;
        //excel data saves in temp file here.
        foreach (DataRow row in dt.Rows)
        {
            excelSheets[t] = row["TABLE_NAME"].ToString();
            t++;
        }

        OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);

        string query = string.Format("SELECT * FROM [{0}]", excelSheets[0]);

        OleDbCommand cmd = new OleDbCommand(query, excelConnection);
        //excelConnection.Open();
        OleDbDataReader dReader;
        dReader = cmd.ExecuteReader();
        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
        //Give your Destination table name
        sqlBulk.DestinationTableName = "[FSM].[DFS_Akustik]";
        sqlBulk.WriteToServer(dReader);
        excelConnection.Close();

        ViewBag.view_dfs_akustik = dbman.View_DFS_Akustik.ToList();
        return View();
    }