excel导入数据到sqlserver

时间：2025-03-31 12:37:43

1、读取excel数据到dataset

public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName)

    {

        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";

        OleDbConnection ExcelConn = new OleDbConnection(strCon);

        try

        {

            string strCom = string.Format("SELECT * FROM [Sheet1$]");

            ExcelConn.Open();

            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);

            DataSet ds = new DataSet();

            myCommand.Fill(ds, "[" + tableName + "$]");

            ExcelConn.Close();

            return ds;

        }

        catch

        {

            ExcelConn.Close();

            return null;

        }

    }

2、将数据写入到数据库

 protected void Button2_Click(object sender, EventArgs e)

    {

        string filename = FileUpload1.FileName;

        string savePath = Server.MapPath(("~/fujian/") + filename);

        FileUpload1.SaveAs(savePath);

        DataSet ds = ExcelSqlConnection(savePath, filename);

        //GridView1.DataSource = ds;

        //GridView1.DataBind();

        DataRow[] dr = ds.Tables[].Select();

        for (int i = ; i < dr.Length; i++)

        {

            SqlConnection myconn = myconnect();

            myconn.Open();

            string title = dr[i]["名称"].ToString();

            string huanxianweizhi = dr[i]["环线位置"].ToString();

            string quyu = dr[i]["区域"].ToString();

            string sqlstr1 = "select * from dbo.test where name='" + quyu + "'";    //区域中间表转换

            SqlCommand myCmd1 = new SqlCommand(sqlstr1, myconn);

            SqlDataAdapter mydata = new SqlDataAdapter(myCmd1);

            DataSet my = new DataSet();

            mydata.Fill(my);

            GridView1.DataSource = my;

            GridView1.DataBind();

            string quyu1 = my.Tables[].Rows[][].ToString(); ;

            //SqlDataAdapter adapt = new SqlDataAdapter(sqlstr1, myconn);

            DataSet ds1 = new DataSet();

            string zuoluo = dr[i]["座落"].ToString();

            string yongtu = dr[i]["用途"].ToString();

            string chengjiaotaoshu = dr[i]["成交套数"].ToString();

            string jianzhumianji = dr[i]["建筑面积"].ToString();

            string chengjiaozongjia = dr[i]["成交总价"].ToString();

            string dangrijunjia = dr[i]["当日均价"].ToString();

            string chengjiaoriqi = dr[i]["成交日期"].ToString();

            string qitashuoming = dr[i]["其他说明"].ToString();

            string bankuai = dr[i]["板块"].ToString();

            //SqlConnection myconn = myconnect();

            //myconn.Open();

            string sqlstr = "insert into dbo.youweishuju(名称,环线位置,区域,座落,用途,成交套数,建筑面积,成交总价,当日均价,成交日期,其他说明,板块)values('" + title + "','" + huanxianweizhi + "','" + quyu1 + "','" + zuoluo + "','" + yongtu + "','" + chengjiaotaoshu + "','"+ jianzhumianji +"','"+ chengjiaozongjia +"','"+ dangrijunjia +"','"+ chengjiaoriqi +"','"+ qitashuoming +"','"+ bankuai +"')";

            //string sqlstr = "insert into dbo.youweishuju(名称)values('" + title + "')";

            SqlCommand myCmd = new SqlCommand(sqlstr, myconn);

            myCmd.ExecuteNonQuery();

            myconn.Close();

        }

    }

相关文章