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

最新推荐文章于 2024-02-24 23:53:23 发布

mafei912

最新推荐文章于 2024-02-24 23:53:23 发布

阅读量369

点赞数

分类专栏： ASP.net 文章标签： excel dataset button string server 数据库

本文链接：https://blog.csdn.net/mafei912/article/details/7453939

版权

ASP.net 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

前台代码：

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