NET导入 Excel方法

最新推荐文章于 2024-05-13 17:07:16 发布

转载最新推荐文章于 2024-05-13 17:07:16 发布 · 547 阅读

文章标签：

#excel #properties #string #path #button #object

本博客介绍了一段代码，用于从用户上传的Excel文件中读取优惠券信息，并将其导入到数据库中。代码包括文件路径验证、文件名处理、文件保存和数据导入流程。

protected void Button1_Click(object sender, EventArgs e)
    {
        DataTable dt = null;
        string path = "", f_folder, f_name;
        int j = 0;
        try
        {
            path = this.FileUpload1.FileName;
            if (path == "")
            {
                Response.Write("<script>alert('请选择Excel文件路径!')</script>");
                return;
            }

            f_name = System.DateTime.Now.ToString();

            f_name = f_name.Replace(" ", "");
            f_name = f_name.Replace("-", "");
            f_name = f_name.Replace(":", "");
            f_name = f_name.Replace("/", "");
            f_folder = Server.MapPath(@"../upfile/" + f_name + ".xls");
            this.FileUpload1.PostedFile.SaveAs(f_folder);

            // string sheetname = this.txtTable_Name.Text.Trim();
            dt = ExcelDataSource(f_folder, "Sheet1");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Cut c = new Cut();
                c.Name=dt.Rows[i]["优惠券号"].ToString();
                c.Cuts = dt.Rows[i]["折扣"].ToString();
                if (CutsManager.Add(c) == "1")
                {
                    j++;
                }
            }
            if (File.Exists(Server.MapPath(@"../upfile/" + f_name + ".xls")))
            {
                File.Delete(f_folder);
            }
            string s = "成功导入输入" + j + "条！";
            if (j == dt.Rows.Count)
            {
                Response.Write("<script>alert('" + s + "')</script>");

            }
        }
        catch
        {
            Response.Write("<script>alert('数据格式出错！')</script>");
        }
    }
    public DataTable ExcelDataSource(string filepath, string sheetname)
    {
        string strConn = String.Empty;
        if (System.IO.Path.GetExtension(filepath).Equals(".xlsx"))
        {
            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';data source=" + filepath;
        }
        else if (System.IO.Path.GetExtension(filepath).Equals(".xls"))
        {
            //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties=Excel 8.0;";
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        }
        OleDbConnection conn = new OleDbConnection(strConn);
        OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "$]", strConn);
        DataTable ds = new DataTable();
        oada.Fill(ds);
        conn.Close();
        return ds;
    }