你的位置:首页 > ASP.net教程

[ASP.net教程]excel导入数据到sqlserver


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[0].Select();    for (int i = 0; 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[0].Rows[0][0].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();    }  }