c#大批量Exce数据l导入数据库

protected void Button1_Click(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有文件
            {
                Response.Write("<script>alert('请您选择Excel文件')</script> ");
                return;//当无文件时,返回
            }
            string IsXls = Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
            if (IsXls != ".xlsx" && IsXls != ".xls")
            {
                Response.Write(FileUpload1.FileName);
                Response.Write("<script>alert('只可以选择Excel文件')</script>");
                return;//当选择的不是Excel文件时,返回
            }

            string filename = GetTimeStamp() + IsXls;
            string savePath = Server.MapPath(("~\\upload\\") + filename);//Server.MapPath 服务器上的指定虚拟路径相对应的物理文件路径
                                                                         //savePath ="D:\vsproject\Projects\exceltestweb\exceltestweb\uploadfiles\test.xls"
                                                                         //Response.Write(savePath);
            DataTable ds = new DataTable();
            FileUpload1.SaveAs(savePath);//将文件保存到指定路径

            DataTable dt = GetExcelDatatable(savePath);//读取excel数据

            SHelp _sql = new SHelp(0);

            DataTable _dtx = _sql.GetData("select MaxFid from t_systemmax where TabelName='t_inapply'");

            Int64 MaxFid = Convert.ToInt64(_dtx.Rows[0][0].ToString());

            dt.Columns.Add("Fid");

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                try
                {
                    while (dt.Rows[i][0].ToString() == "")
                    {
                        dt.Rows.RemoveAt(i);
                    }

                    MaxFid += 1;
                    dt.Rows[i]["Fid"] = MaxFid.ToString();
                }
                catch
                {
                    break;
                }


            }


            for (int i = 0; i < dt.Columns.Count; i++)
            {
                dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.Trim();
            }

            dt.Columns["发货金额（含税）"].ColumnName = "发货金额";

            int x = _sql.SqlBulkCopyByDatatable("t_inapply", dt);

            if (x == -1)
            {
                Response.Write("<script>alert('上传数据列不一致！');</script>");
            }
            else
            {
                x = _sql.ExecuteNonQuery("update t_systemmax set MaxFid=(select isnull(MAX(Fid),0) from t_inapply) where TabelName='t_inapply'  update t_inapply set FDate=GETDATE() where FDate is null ");
                Response.Write("<script>alert('上传文件读取数据成功！');</script>");
            }



            File.Delete(savePath);//删除文件

        }
    }


    /// <summary>
    /// 从excel文件中读取数据
    /// </summary>
    /// <param name="fileUrl">实体文件的存储路径</param>
    /// <returns></returns>
    private static DataTable GetExcelDatatable(string fileUrl)
    {
        //支持.xls和.xlsx，即包括office2010等版本的;HDR=Yes代表第一行是标题，不是数据；
        string cmdText = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileUrl + "; Extended Properties=\"Excel 12.0;HDR=Yes\"";
        System.Data.DataTable dt = null;
        //建立连接
        OleDbConnection conn = new OleDbConnection(cmdText);

        string tableName = "";
        try
        {
            //打开连接
            if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
            {
                conn.Open();

                 dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                tableName = dt.Rows[0][2].ToString().Trim();

                dt = null;
            }

            System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string strSql = "select * from ["+ tableName + "]";   //这里指定表明为Sheet1,如果修改过表单的名称，请使用修改后的名称
            OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            dt = ds.Tables[0]; ;
            return dt;
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }
    }

   


    /// <summary>
    /// 获取时间戳
    /// </summary>
    /// <returns></returns>
    public string GetTimeStamp()
    {
        TimeSpan ts = DateTime.Now - new DateTime(1970, 1, 1, 0, 0, 0, 0);
        return Convert.ToInt64(ts.TotalSeconds).ToString();
    }

/// <summary>
    /// 将表中资料批量插入到数据库
    /// 
    /// </summary>
    /// <param name="connectionString"></param>
    /// <param name="TableName"></param>
    /// <param name="dt"></param>
    public int SqlBulkCopyByDatatable(string TableName, DataTable dt)
    {
        using (this._conn = new SqlConnection(sqlAdd))
        {
            using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(sqlAdd, 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 1;
                }
                catch (System.Exception ex)
                {
                    return -1;
                }
            }
        }
    }