上传Excel并高效将EXCEL导入数据库

上传EXCEL文件到服务器

        if (FileUpload_excel.HasFile)
        {
            string excelFileName = FileUpload_excel.FileName;
            string fileExt = System.IO.Path.GetExtension(FileUpload_excel.FileName).ToLower();

            if (fileExt==".xls" || fileExt==".xlsx")
            {
                try
                {
                    string FileNewName = "Gtcs"+DateTime.Now.ToString("yyyyMMddHHmmssffff") + fileExt;
                    string FilePath = HttpContext.Current.Server.MapPath("./UploadFiles/");
                    this.FileUpload_excel.SaveAs(FilePath + FileNewName);
                    string FullPathName = FilePath + FileNewName;

                }
                catch (Exception ext)
                {
                    
                }
            }

        }

 

根据EXCEL文件路径读取内容到DataSet

   //根据路径读取Excel到DataSet
   private DataSet ReadExcelFile(string FullPathName)
    {
        string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + FullPathName + ";Extended Properties=\"Excel 8.0; HDR=No; IMEX=1;\"";
        OleDbConnection conn = new OleDbConnection(strCon);
        string sql = "select F1 ,F2 ,F3 from [Sheet1$]";
        conn.Open();
        OleDbDataAdapter myCommand = new OleDbDataAdapter(sql, strCon);
        DataSet ds = new DataSet();
        myCommand.Fill(ds, "[Sheet1$]");
        conn.Close();
        return ds;
    }

 

这就是重点了，高效将DataSet的内容写入到数据库中

                using (System.Data.SqlClient.SqlBulkCopy SQLBC = new System.Data.SqlClient.SqlBulkCopy(connStr))
                {
                    SQLBC.ColumnMappings.Add("F1", "db_F1");
                    SQLBC.ColumnMappings.Add("F2", "db_F2");
                    SQLBC.ColumnMappings.Add("F3", "db_F3");
                    SQLBC.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(SQLBC_SQLRowsCopide);
                    SQLBC.BatchSize = 10000;//每次传输的行数
                    SQLBC.NotifyAfter = 10000;//生成通知的行数
                    SQLBC.DestinationTableName = "excel_gtcsTemp";//目标表名
                    SQLBC.WriteToServer(ds.Tables[0]);
                }

 

 

    private void SQLBC_SQLRowsCopide(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)
    {
        //Response.Write("Rows:" + e.RowsCopied.ToString() + "<br>"); 
　　　　　　
    }