如何将数据从 Excel 导入到 SQL Server

发布网友发布时间：2022-04-20 06:38

共2个回答

懂视网时间：2022-04-08 09:19

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();
 }
 }

excel导入数据到sqlserver

标签：

热心网友时间：2022-04-08 06:27

方法/步骤
　　1、打开要导入的Excel文件，观察第一列是为字段还是数据
　　打开SQLServer,在需要导入的数据点击右键【任务】-【导入数据】

　　出现导入导出向导。
　　点击下一步，进入【选择数据源】页面，注意红框设置。

　　点击下一步，进入【选择目标】页面，注意红框设置。

　　点击下一步，进入【指定表复制或查询】页面，注意红框设置。

　　点击下一步，进入【选择源表和源视图】页面，注意红框设置。

　　下一步，直到完成。出现【执行结果页面】。

　　excel导入sqlserver 导入数据到sqlserver
　　最后在SqlServer查询表。

全部栏目

如何将数据从 Excel 导入到 SQL Server