操作Excel数据（导入导出）

最新推荐文章于 2024-05-16 10:46:56 发布

原创最新推荐文章于 2024-05-16 10:46:56 发布 · 938 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #dataset #exception #string #properties #电话

dotnet/C# 专栏收录该内容

18 篇文章

订阅专栏

本文介绍了一个使用ASP.NET进行Excel文件导入导出的具体实现案例。文章详细展示了如何验证文件类型、读取Excel数据并保存到数据库，以及如何从数据库导出数据到Excel文件的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

导入：

  string fileContentType = FileUpload1.PostedFile.ContentType;



  if (fileContentType.Trim() != "application/vnd.ms-excel")

  {

      Response.Write("<script>alert('对不起,您打开的文件类型不正确！');</script>");

      return;

  }

//从上传的excel文件中取出电话号码

    private bool ImportExcel(string file)

    {

        if (!File.Exists(file))

        {

            Response.Write("<script>alert('对不起,您的电话文件不存在！');</script>");

        }

        try

        {

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=/"Excel 8.0;/"";

            OleDbConnection conn = new OleDbConnection(strConn);

            conn.Open();



            OleDbDataAdapter da = new OleDbDataAdapter("select * from [teltemplate$]", conn);

            DataSet ds = new DataSet();

            da.Fill(ds);



            int sum = 1, except = 1;



            String[] str = new String[9];



            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

            {

                str[0] = ds.Tables[0].Rows[i][0].ToString().Trim();

                str[1] = ds.Tables[0].Rows[i][1].ToString().Trim();

                str[2] = ds.Tables[0].Rows[i][2].ToString().Trim();

                str[3] = ds.Tables[0].Rows[i][3].ToString().Trim();

                str[4] = ds.Tables[0].Rows[i][4].ToString().Trim();

                str[5] = ds.Tables[0].Rows[i][5].ToString().Trim();

                str[6] = ds.Tables[0].Rows[i][6].ToString().Trim();

                str[7] = ds.Tables[0].Rows[i][7].ToString().Trim();

                str[8] = ds.Tables[0].Rows[i][8].ToString().Trim();

                if (this.SaveTel(str) == -1)

                {

                    except++;

                }

                sum++;

            }

            conn.Close();



            sum = sum - 1 - (except - 1);



            Response.Write("<script>alert('共导入" + sum.ToString() + "个电话数据！');</script>");

        }

        catch (Exception e)

        {

            Response.Write("<script>alert('导入失败，请对照模板，检查文件格式！');</script>");

        }





        return true;

    }

导出

    protected void btnDown_Click(object sender, EventArgs e)

    {

        try

        {

            Response.Clear();

            Response.Buffer = true;

            Response.Charset = "GB2312";

            Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode("中介总账", System.Text.Encoding.UTF8) + ".xls");

            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

            Response.ContentType = "application/ms-excel";

            Response.Write("<center><font size=8>中介总账</font></center>");

            Response.Write("<br><font size=3>月份：" + this.lblmonth.Text + "<br>");

            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

            this.GridView1.RenderControl(oHtmlTextWriter);            

            Response.Output.Write(oStringWriter.ToString());

            Response.Flush();

            Response.End();

        }

        catch (Exception exc)

        {

            this.AlertMessage("下载失败！");

        }

    }



    public override void VerifyRenderingInServerForm(Control control)

    {



    }