aspx页面中，DataTable中的数据导出到Excel文件中

最新推荐文章于 2019-12-27 00:19:43 发布

逸剑听潮

最新推荐文章于 2019-12-27 00:19:43 发布

阅读量1.9k

点赞数

分类专栏： .Net技术

本文链接：https://blog.csdn.net/li_gf/article/details/9169779

版权

.Net技术专栏收录该内容

6 篇文章 0 订阅

订阅专栏

方法一：直接将DataTable中的数据通过GridView导出到Excel中，并显示下载界面

    public void ExportToExcel(DataTable tab,string filename)
    {
        System.Web.UI.WebControls.DataGrid source = new System.Web.UI.WebControls.DataGrid();
        source.DataSource = tab.DefaultView;
        source.AllowPaging = false;
        source.HeaderStyle.BackColor = System.Drawing.Color.LightGray;
        source.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        source.HeaderStyle.Font.Bold = true;
        source.DataBind();
        //防止出现乱码,加上这行可以防止在只有一行数据时出现乱码
        HttpContext.Current.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=UTF-8>");

        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename + ".xls", System.Text.Encoding.UTF8));
        HttpContext.Current.Response.ContentType = "application/ms-excel";

HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;

//关闭控件的视图状态
//source.Page.EnableViewState = false;

        //初始化HtmlWriter
        System.IO.StringWriter writer = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWriter = new System.Web.UI.HtmlTextWriter(writer);
        source.RenderControl(htmlWriter);

        //输出
        HttpContext.Current.Response.Write(writer.ToString());
        HttpContext.Current.Response.End();
    }

方法二：先将DataTable中的数据生成保存在服务器端的Excel文件，然后将文件读取到内存，输出到客户端并显示下载界面

    public static void ExportToExcel(System.Web.UI.Page page, System.Data.DataTable tab, string FileName)
    {
        System.Web.HttpResponse httpResponse = page.Response;
        System.Web.UI.WebControls.DataGrid dataGrid = new System.Web.UI.WebControls.DataGrid();
        dataGrid.DataSource = tab.DefaultView;
        dataGrid.AllowPaging = false;
        dataGrid.HeaderStyle.BackColor = System.Drawing.Color.LightGray;
        dataGrid.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        dataGrid.HeaderStyle.Font.Bold = true;
        dataGrid.DataBind();
        httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8));
        //filename="*.xls";
        httpResponse.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        httpResponse.ContentType = "application/ms-excel";
        httpResponse.Charset = "UTF-8";
        System.IO.StringWriter tw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
        dataGrid.RenderControl(hw);
        string filePath = page.Server.MapPath(".") + "//Files//" + FileName;
        if (System.IO.File.Exists(filePath))
        {
            System.IO.File.Delete(filePath);
        }

        httpResponse.Write(tw.ToString());
        //System.IO.StreamWriter sw = System.IO.File.CreateText(filePath);
        //sw.Write(tw.ToString());
        //sw.Close();
        //DownFile(httpResponse, FileName, filePath);
        httpResponse.End();
    }

    private static bool DownFile(System.Web.HttpResponse Response, string fileName, string fullPath)
    {
        try
        {
            Response.ContentType = "application/ms-excel";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            System.IO.FileStream fs = System.IO.File.OpenRead(fullPath);
            int fLen = Convert.ToInt32(fs.Length);

            byte[] readData = new byte[fLen];
            fs.Read(readData, 0, fLen);
            Response.BinaryWrite(readData);

            fs.Close();
            System.IO.File.Delete(fullPath);
            return true;
        }
        catch
        {
            return false;
        }
    }