Import data from excel to sqlserver, and import data from excel to sqlserver

Import data from excel to sqlserver, and import data from excel to sqlserver

1. Read excel Data to 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. Write data to the database

Protected void Button2_Click (object sender, EventArgs e) {string filename = FileUpload1.FileName; string savePath = Server. MapPath (("~ /Fujiian/") + 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] ["name"]. toString (); string huanxianweizhi = dr [I] ["loop location"]. toString (); string quyu = dr [I] ["region"]. toString (); string sqlstr1 = "select * from dbo. test where name = '"+ quyu +"' "; // convert the region table to 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] ["purpose"]. toString (); string chengjiaotaoshu = dr [I] ["number of transactions"]. toString (); string jianzhumianji = dr [I] ["building area"]. toString (); string chengjiaozongjia = dr [I] ["total price"]. toString (); string dangrijunjia = dr [I] ["average price of the Day"]. toString (); string chengjiaoriqi = dr [I] ["deal date"]. toString (); string qitashuoming = dr [I] ["other instructions"]. toString (); string bankuai = dr [I] ["section"]. 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 (name) values ('"+ title +"') "; SqlCommand myCmd = new SqlCommand (sqlstr, myconn); myCmd. executeNonQuery (); myconn. close ();}}