C＃SQL批量从Excel插入到Excel和DataTable之间的SQL列映射

Question

我试图将记录插入SQL，但是当我尝试在Excel和SQL表之间映射列时，我会遇到问题。如果我取出映射，则插入功能可行，但数据最终位于错误的字段中。给出的错误是，即使数据源也不包含指定的列？关于为什么它不识别Excel中的列标题的任何建议。

protected void LoadFile(object sender, EventArgs e)
    {
        {
            //Upload and save the file
            string path = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
            FileUpload1.SaveAs(path);
            string conString = string.Empty;
            string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            switch (extension)
            {
                case ".xls": //Excel 97-03
                    conString = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "; Extended Properties="Excel 8.0;HDR=Yes;IMEX=2"");
                    break;
                case ".xlsx": //Excel 07 or higher
                    conString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= "+ path +";Extended Properties ='Excel 12.0 Xml;HDR=YES;IMEX=1';");
                    break;
                    //conString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + "; Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
            }
            conString = string.Format(conString, path);
            using (OleDbConnection excel_con = new OleDbConnection(conString))
            {

                excel_con.Open();
               string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
                DataSet ds = new DataSet();
                DataTable dtExcelData = new DataTable();
                OleDbConnection OleDbcon = new OleDbConnection(conString);
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet1 + "]", OleDbcon);

                OleDbcon.Open();
                cmd.Connection = OleDbcon;
                objAdapter1 = new OleDbDataAdapter(cmd);
                objAdapter1.Fill(ds);
                dtExcelData = ds.Tables[0];
                excel_con.Close();

                using (SqlConnection con = new SqlConnection(ConString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        //Set the database table name
                        sqlBulkCopy.DestinationTableName = "dbo.mytable";
                        //[OPTIONAL]: Map the Excel columns with that of the database table
                        sqlBulkCopy.ColumnMappings.Add("Tag","Tag");
                        sqlBulkCopy.ColumnMappings.Add("ExitDate","ExitDate");
                        sqlBulkCopy.ColumnMappings.Add("Exit","Exit");
                        sqlBulkCopy.ColumnMappings.Add("Amount","Amount");
                        con.Open();
                        sqlBulkCopy.WriteToServer(dtExcelData);
                        con.Close();
                    }
                }
            }
        }`

任何帮助将不胜感激，谢谢！

Answer 1

从您的图像中看来，excel中的列标题不齐平（可能表明空间）。出口一列的列看起来像是它们在开头的空间，这将打破映射。我已经尝试了您的代码，并且似乎正在工作。

对于SQL批量复制映射源列必须与Excel中的列名完全匹配，并且目标列必须与您要插入的SQL表中的列名完全匹配。