把EXCEL文件的数据通过页面插入数据库

来源：互联网发布：ug8.0编程视频教程全集编辑：程序博客网时间：2024/04/20 03:15

前台代码：

<body>    <form id="form1" runat="server">    <div>        boob <asp:FileUpload ID="FileUpload1" runat="server" /><br />        <br />        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="导入Excle预览" />        <asp:GridView ID="GridView1" runat="server">        </asp:GridView>        </div>        <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="确定将以上数据导入数据库"            Visible="False" />    </form></body>

后台代码：

protected void Button1_Click(object sender, EventArgs e)        {            string type1 = FileUpload1.PostedFile.ContentType;            string type = type1.Substring(type1.LastIndexOf("-") + 1, 5);            if (string.Equals(type, "excel"))            {                string newName = Server.MapPath("App_Data//") + DateTime.Now.ToString("hhmmss") + ".xls";                FileUpload1.SaveAs(newName);                string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + newName + ";Extended Properties=Excel 8.0;";                OleDbConnection conn = new OleDbConnection(connStr);                if (conn.State.ToString() == "Closed")                {                    conn.Open();                }                OleDbDataAdapter oda = new OleDbDataAdapter("select * from [Sheet1$]", conn);                DataSet ds = new DataSet();                oda.Fill(ds);                conn.Close();                GridView1.DataSource = ds;                GridView1.DataBind();                File.Delete(newName);                Session["paper"] = ds;                Button2.Visible = true;            }            else            {                Page.RegisterStartupScript("", "<script>alert('文件格式不正确')</script>");            }        }        protected void Button2_Click(object sender, EventArgs e)        {            DataSet ds = (DataSet)Session["paper"];            SqlConnection conn = new SqlConnection("Server=PC-20120222VHZA;DataBase=Test;Integrated Security=True");            if (conn.State.ToString() == "Closed")            {                conn.Open();            }            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)            {                string user = ds.Tables[0].Rows[i][0].ToString();                string paper = ds.Tables[0].Rows[i][1].ToString();                string score = ds.Tables[0].Rows[i][2].ToString();                if (Sms.isrepetion(user, paper))                {                    string sql = "insert into [dbo].[paper]([user],[paper],[score]) values('" + user + "','" + paper + "','" + score + "')";                    SqlCommand cmd = new SqlCommand(sql, conn);                    try                    {                        cmd.CommandType = CommandType.Text;                        cmd.ExecuteNonQuery();                    }                    catch (Exception ex)                    {                        Response.Write("插入失败!由于：" + ex.Message);                    }                }            }            conn.Close();            Response.Write("<SCRIPT>alert('数据已成功导入到数据库！');</SCRIPT>");        }