ASP.NET 将 excel 导入 SQL

来源：互联网发布：qemu运行arm linux 编辑：程序博客网时间：2024/05/21 22:59

button的事件，凑合着看，需要先上传到服务器然后再读取

if (FileUpload1.PostedFile != null)
        {
            string str = FileUpload1.PostedFile.FileName;
            int i = str.LastIndexOf("\\");
            String filename=str.Substring(i+1);
            FileUpload1.PostedFile.SaveAs(@Server.MapPath("xls") + filename);

            Label1.Text = "文件名为" + filename;
            string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Server.MapPath("xls") + "\\" + filename + ";Extended Properties=Excel 8.0";
            OleDbConnection thisconnection = new OleDbConnection(conn);
            thisconnection.Open();
            string Sql = "select * from [Sheet1$]";
            OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
            DataSet ds = new DataSet();
            mycommand.Fill(ds, "[Sheet1$]");
            thisconnection.Close();

            string conn1 = System.Configuration.ConfigurationManager.ConnectionStrings["ZOPGConn"].ToString();
            SqlConnection thisconnection1 = new SqlConnection(conn1);
            thisconnection1.Open();
            int count = ds.Tables["[Sheet1$]"].Rows.Count;

            for (int j = 0; j < count; j++)
            {
                string id_1, id_2, id_3;
                id_1 = ds.Tables["[Sheet1$]"].Rows[j]["id1"].ToString();
                id_2 = ds.Tables["[Sheet1$]"].Rows[j]["id2"].ToString();
                id_3 = ds.Tables["[Sheet1$]"].Rows[j]["id3"].ToString();
                string excelsql = "insert into test(id1,id2,id3) values ('" + id_1 + "','" + id_2 + "','" + id_3 + "') ";
                SqlCommand mycommand1 = new SqlCommand(excelsql, thisconnection1);
                mycommand1.ExecuteNonQuery();
            }
            Response.Write("更新成功");
            thisconnection1.Close();

}