记录一下关于C#导入EXCEL数据到数据库的方法

第一次做EXCEL导入SQL数据库的功能，查阅并借鉴了很多博主的方法，在这里记录一下，方便以后取用，有改进的地方也请大家指出，这里前端使用的是layUI，这里使用了两种方法，步骤都是先将文件上传至服务器，再读到DataTable中，前一种是通过sql一条一条的insert，而第二种通过SQLBulkCopy来操作。

public string uploadExcelToSQL()

    {
        DBHelper db = new DBHelper();
        var file = Context.Request.Files[0];
        //获取文件路径
        string filenPath = Context.Request.Files[0].FileName;
        //获取文件名
        string fileName = Path.GetFileNameWithoutExtension(Context.Request.Files[0].FileName);
        //获取文件扩展名
        string fileExtName = filenPath.Substring(filenPath.LastIndexOf(".") + 1);

        string response = null;
        System.Data.DataTable dt = new System.Data.DataTable();
        try
        {
            string sFileName = "Files/" + fileName;
            if (File.Exists(Server.MapPath(sFileName)))
            {
                File.Delete(Server.MapPath(sFileName));
            }
            file.SaveAs(Server.MapPath(sFileName));
            //HDR=Yes，这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES  
            string connstr2003 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(sFileName) + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
            string connstr2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(sFileName) + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
            OleDbConnection conn;
            if (fileExtName == "xls")
            {
                conn = new OleDbConnection(connstr2003);
            }
            else
            {
                conn = new OleDbConnection(connstr2007);
            }
            conn.Open();
            string sql = "select * from [Sheet1$]";
            OleDbCommand cmd = new OleDbCommand(sql, conn);

            OleDbDataReader sdr = cmd.ExecuteReader();

            dt.Load(sdr);
            sdr.Close();
            conn.Close();
            //删除服务器里上传的文件  
            if (File.Exists(Server.MapPath(sFileName)))
            {
                File.Delete(Server.MapPath(sFileName));
            }
            //return dt;
        }
        catch (Exception e)
        {

        }

        int errorcount = 0;//记录错误信息条数  
        int insertcount = 0;//记录插入成功条数  

        int updatecount = 0;//记录更新信息条数  
        string strcon = "Data Source=(local);Initial Catalog=NPMclz;User Id=sa;Password=zzy789864;";
        SqlConnection con = new SqlConnection(strcon);//链接数据库  
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            string companyName = dt.Rows[i][0].ToString();
            string societyCreditCode = dt.Rows[i][1].ToString();
            string legalPerson = dt.Rows[i][2].ToString();
            string lgpsPhone = dt.Rows[i][3].ToString();
            string agent = dt.Rows[i][4].ToString();
            string agentPhone = dt.Rows[i][5].ToString();
            string lgpsAddress = dt.Rows[i][6].ToString();
            string runAddress = dt.Rows[i][7].ToString();
            string mainBusinessForm = dt.Rows[i][8].ToString();
            string cartingStyle = dt.Rows[i][9].ToString();
            string runWay = dt.Rows[i][10].ToString();
            string isSchool = dt.Rows[i][11].ToString();
            string isRunByNet = dt.Rows[i][12].ToString();
            string netAddress = dt.Rows[i][13].ToString();
            string isCenterKitchen = dt.Rows[i][14].ToString();
            string isSendForGroup = dt.Rows[i][15].ToString();
            string runProject = dt.Rows[i][16].ToString();
            string licenseCode = dt.Rows[i][17].ToString();
            string supervisionOrg = dt.Rows[i][18].ToString();
            string supervisionPerson = dt.Rows[i][19].ToString();
            string certificateOrg = dt.Rows[i][20].ToString();
            string certificatePerson = dt.Rows[i][21].ToString();
            string certificateDate = dt.Rows[i][22].ToString();
            string overDate = dt.Rows[i][23].ToString();
            string outWarehouse = dt.Rows[i][24].ToString();
            string logoutDate = dt.Rows[i][25].ToString();
            if (companyName != "" && licenseCode != "")
            {
                string sqlStr = null;
                //SqlCommand selectcmd = new SqlCommand("select count(*) from users where licenseCode='" + licenseCode + "'");
                //int count = Convert.ToInt32(selectcmd.ExecuteScalar());
                sqlStr = string.Format("select count(*) from Company where licenseCode='" + licenseCode + "'");
                int count = db.ExecuteScalarToInt(sqlStr);
                if (count > 0)
                {
                    updatecount++;
                }
                else
                {
                    //SqlCommand insertcmd = new SqlCommand(, con);                      
                    //insertcmd.ExecuteNonQuery();

                    sqlStr = string.Format("insert into Company(companyName,societyCreditCode,legalPerson,lgpsPhone,agent,agentPhone,lgpsAddress,runAddress,mainBusinessForm,cartingStyle,runWay,isSchool,isRunByNet,netAddress,isCenterKitchen,isSendForGroup,runProject,licenseCode,supervisionOrg,supervisionPerson,certificateOrg,certificatePerson,certificateDate,overDate,outWarehouse,logoutDate) values ('" + companyName + "', '" + societyCreditCode + "', '" + legalPerson + "', '" + lgpsPhone + "', '" + agent + "', '" + agentPhone + "', '" + lgpsAddress + "', '" + runAddress + "', '" + mainBusinessForm + "', '" + cartingStyle + "', '" + runWay + "', '" + isSchool + "', '" + isRunByNet + "', '" + netAddress + "', '" + isCenterKitchen + "', '" + isSendForGroup + "', '" + runProject + "', '" + licenseCode + "', '" + supervisionOrg + "', '" + supervisionPerson + "', '" + certificateOrg + "', '" + certificatePerson + "', '" + certificateDate + "', '" + overDate + "','" + outWarehouse + "','" + logoutDate + "')");
                    db.ExecuteNonQuery(sqlStr);
                    insertcount++;
                }
            }
            else
            {
                errorcount++;
            }
        }
        //Response.Write((insertcount + "条数据导入成功！" + updatecount + "条数据重复！" + errorcount + "条数据部分信息为空没有导入！"));
        response = "{\"code\": 0,\"msg\": \"成功\",\"data\": {\"成功导入数\": \"" + insertcount + "\",\"重复数目\": \"" + updatecount + "\",\"未导入数目\": \"" + errorcount + "\"}}"; ;
        return response;
    }

}

更新线

============================================================================

使用前面的这个方法他是一条一条通过insert插入数据，有一个很大的问题，就是要反复写sql insert数据，导致插入速度异常缓慢。后来在网上查询得知了使用SQLBulkCopy可以可快速插入数据，于是尝试了一下，发现真的可以，特此记录一下

public string SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dt)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlBulkCopy sqlbulkcopy =
new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
            {
                try
                {
                    sqlbulkcopy.DestinationTableName = TableName;
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                    }
                    sqlbulkcopy.WriteToServer(dt);
                    return "200";
                }
                catch (System.Exception ex)
                {
                    throw (ex);
                }
            }
        }
    }