aspx后台实现Excel导入到SqlServer中

拿来主义

 

 

private void Excel2Sql()
    {
        DataSet ds = new DataSet();

        try
        {
            //获取全部数据
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=E:/t.xls;" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            strExcel = string.Format("select * from [{0}$]", "Sheet1");
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            myCommand.Fill(ds, "tb_Test");
            conn.Close();

            //如果目标表不存在则创建
            string strSql = string.Format("if object_id('{0}')is null create table {0}(", "tb_Test");
            foreach (DataColumn c in ds.Tables[0].Columns)
            {
                strSql += string.Format("[{0}]varchar(255),", c.ColumnName);
            }
            strSql = strSql.Trim(',') + ")";
            using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
            {
                try
                {
                    sqlconn.Close();
                }
                catch
                {
               
                }
                sqlconn.Open();
                System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
                command.CommandText = strSql;
                command.ExecuteNonQuery();
                sqlconn.Close();
            }
            //用bcp导入数据
            using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(ConfigurationManager.AppSettings["ConnectionString"]))
            {
                //bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
                bcp.BatchSize = 100;    //每次传输的行数
                bcp.DestinationTableName = "tb_Test";
                bcp.WriteToServer(ds.Tables[0]);
            }
            Response.Write("<script>alert('success');</script>");
        }
        catch (Exception ex)
        {
            throw(ex);
        }
    }