Click here to Skip to main content
15,936,119 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi guys,

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

i can do that with the below code,

C#
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();

       }



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.
Posted
Updated 11-Aug-15 4:22am
v2
Comments
Herman<T>.Instance 11-Aug-15 10:08am    
add more header rows to the gridview
sunnykvinod 11-Aug-15 10:22am    
Headerrows not working

1 solution

Hi,

If you wrap your GridView in a Panel control, then add label controls for the title and report date, something like:
ASP.NET
<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.
C#
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();
 
        }


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.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900