如何从GridView将数据导出到Excel工作表? [英] How to export data to excel sheet from gridview?

问题描述

大家好，

我想将gridview中的数据导出到excel表.

我可以使用以下代码做到这一点，

Hi guys,

i want to export data in the gridview export to excel sheet.

i can do that with the below code,

private void ExportGridToExcel()
       {
           Response.Clear();
           Response.Buffer = true;
           Response.ClearContent();
           Response.ClearHeaders();
           Response.Charset = "";
           string FileName = "Report" + DateTime.Now + ".xls";
           StringWriter strwritter = new StringWriter();
           HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);
           Response.Cache.SetCacheability(HttpCacheability.NoCache);
           Response.ContentType = "application/vnd.ms-excel";
           Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
           GridView1.GridLines = GridLines.Both;
           GridView1.HeaderStyle.Font.Bold = true;
           GridView1.RenderControl(htmltextwrtter);
           Response.Write(strwritter.ToString());
           Response.End();

       }

但我也想在Excel工作表的顶部单元格中添加一些更多信息，例如``报告标题''和``报告日期''.我该如何实现.

But I also want to add some more info to the excel sheet like ''title of report'' and ''date of the report'' in the top cells of the sheet.how could i achieve that.

推荐答案

如果将GridView包装在Panel控件中，则为标题和报告日期添加标签控件，例如:

Hi,

If you wrap your GridView in a Panel control, then add label controls for the title and report date, something like:

<asp:panel id="pnlContainer" runat="server" xmlns:asp="#unknown">
    <h4><asp:label id="lblTitle" runat="server" text="Your Report Title"></asp:label></h4>
    <asp:label id="lblReportDate" runat="server"></asp:label>
    <asp:gridview id="GridView1" runat="server">
        <columns>
        ...
        <!-- The rest of your GridView Columns -->
        ...
        </columns>
    </asp:gridview>
</asp:panel>

然后，您可以将该面板渲染回客户端.

You can then render that Panel back to the client.

private void ExportGridToExcel()
        {
            lblReportDate.Text = DateTime.Now.ToString();
            Response.Clear();
            Response.Buffer = true;
            Response.ClearContent();
            Response.ClearHeaders();
            Response.Charset = "";
            string FileName = "Report" + DateTime.Now + ".xls";
            StringWriter strwritter = new StringWriter();
            HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
            GridView1.GridLines = GridLines.Both;
            GridView1.HeaderStyle.Font.Bold = true;
            Panel1.RenderControl(htmltextwrtter);
            Response.Write(strwritter.ToString());
            Response.End();
 
        }

由于您没有生成真正的excel文件，因此您只能使用呈现文档的格式.
看看 EPPlus [创建/读取/编辑Advance Excel 2007/2010使用EPPlus在C#.Net中生成报表 [ ^ ]
2)插入访问格式过滤器设置公式标题 [ ^ ]

...希望能帮上忙.

Because you are not generating a true excel file, you are limited in the format of the rendered document.
You would be much better off have a look at a component like EPPlus[^] that is designed to generate excel documents, and provides lots of functionality that is available in an Excel document.

Articles explaining how to use can be found:
1) Create/Read/Edit Advance Excel 2007/2010 Report in C#.Net using EPPlus[^]
2) Insert Access Format Filter Setting Formula Header[^]

... hope it helps.

这篇关于如何从GridView将数据导出到Excel工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！