Tuesday, 2 April 2013

Exporting to Excel from GridView when grid changed by code behind

This is a small post, which explains how to export to excel from GridView where the GridView has been changed from the code behind by adding/deleting some rows and updated some data. When GridView changed from code behind, the export might not include the updated version done from the code behind.

Understanding a sample scenario:

Before going for implementation, let us understand the scenario where this implementation is applicable. I have a GridView, which binds some sample data from an XML file. The GridView on screen will be as below –

Now I want to change same GridView by adding group total and grand total as per the post Group Total and Grand Total in GridView - Part 2. Now my GridView shows as below –

Here, the group total, grand total rows are added from code behind by doing some logic. I want to export the same GridView to Excel. I followed normal way of export to excel as per the post Exporting to Excel from GridView (All columns and rows - Normal Method). But I am not getting all the rows exported to the Excel, and the total values are not getting exported. The excel shows as below –

I am not sure why this happened. But seems it exported only the number of rows as per the data it binds. Also RowDataBound event also not fired while exporting, so excel not updated with the total value.

Solution –

The implementation is very simple. The actual requirement while exporting to excel is, we required the html script which needs to be exported to excel. In a normal way, we render the GridView at the time of export (again) and get the html script. The html script is then used for exporting to excel. The code is below –
PrepareGridViewForExport(grdViewOrders);

Context.Response.ClearContent();
Context.Response.ContentType = "application/ms-excel";
Context.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", "ExcelFileName"));
Context.Response.Charset = "";
System.IO.StringWriter stringwriter = new System.IO.StringWriter();
HtmlTextWriter htmlwriter = new HtmlTextWriter(stringwriter);
grdViewOrders.RenderControl(htmlwriter);
Context.Response.Write(stringwriter.ToString());
Context.Response.End();
But instead of doing this, we can use the actual html script which already been rendered on the screen. So to export the actual html on screen to excel, I added a div as parent to GridView. I also added a hidden control for storing the html script before going to code behind. In the export button, I called a javascript function which gets the html script of GridView and assign to the hidden control (before going to code behind).

function AssignExportHTML() {
    document.getElementById("<%= hidGridView.ClientID %>").value = htmlEscape(forExport.innerHTML);
}
function htmlEscape(str) {
    return String(str)
    .replace(/&/g, '&amp;')
    .replace(/"/g, '&quot;')
    .replace(/'/g, '&#39;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;');
}
In the code behind, I used the hidden control value for exporting to the grid.
Context.Response.ClearContent();
Context.Response.ContentType = "application/ms-excel";
Context.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", "ExcelFileName"));
Context.Response.Charset = "";
System.IO.StringWriter stringwriter = new System.IO.StringWriter();
//HtmlTextWriter htmlwriter = new HtmlTextWriter(stringwriter);
//grdViewOrders.RenderControl(htmlwriter);
stringwriter.Write(System.Web.HttpUtility.HtmlDecode(hidGridView.Value));
Context.Response.Write(stringwriter.ToString());
Context.Response.End();
Now my export will be as below –

To get the same style and colors used in the GridView, we can add the css style sheet inside the div control. So while exporting, the style sheets also goes to the excel sheet.

Now the excel looks as below –

Note: This implementation can be used for exporting any kind grid, tables etc to excel as shown in the screen.

8 Responses to “Exporting to Excel from GridView when grid changed by code behind”

  • Anonymous says:
    12 June 2013 at 00:38

    Can you post the code in VB

  • Thirumalai M says:
    22 July 2013 at 19:58

    Hi Try this,

    PrepareGridViewForExport(grdViewOrders)

    Context.Response.ClearContent()
    Context.Response.ContentType = "application/ms-excel"
    Context.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}.xls", "ExcelFileName"))
    Context.Response.Charset = ""
    Dim stringwriter As New System.IO.StringWriter()
    Dim htmlwriter As New HtmlTextWriter(stringwriter)
    grdViewOrders.RenderControl(htmlwriter)
    Context.Response.Write(stringwriter.ToString())
    Context.Response.[End]()

    Instead you can look at the original implementation on existing Exporting to Excel from GridView articles. You can get working code also.
    You can search the other posts, from All Article link (http://www.dotnettwitter.com/p/all-articles.html).

  • Macy Ann Austria says:
    15 May 2015 at 12:53

    can you post the whole code behind that program ? in VB.NET ?
    Badly Needed, thanks :)
    - Beginner, ASP.NET

  • This comment has been removed by the author.
    Thanh Liem says:
    1 December 2015 at 08:14

    This comment has been removed by the author.

  • Thanh Liem says:
    1 December 2015 at 08:17

    I get the error : The name 'hidGridView' does not exist in the current context Source Error: Line 502: function AssignExportHTML() { Line 503: Line 504: document.getElementById("<%= hidGridView.ClientID %>").value = htmlEscape(forExport.innerHTML); Line 505: } Line 506: function htmlEscape(str) {
    If export the nested child Gridview , because the Hidden Field and div , i put near the nested gridview instead in master gridview, So how to correct it

  • Thanh Liem says:
    1 December 2015 at 08:21

    I modified the script like this :
    function AssignExportHTML() {
    var hidGridView = document.child.getElementById('<%=hidGridView%>');
    hidGridView.value = htmlEscape(forExport.innerHTML);
    }
    but failed, how to do?

  • Liss says:
    29 January 2016 at 20:25

    Not works.

  • Thirumalai M says:
    30 January 2016 at 14:04

    hi Liss. What is the issue?

Post a Comment