Exporting to Excel from GridView when grid changed by code behind

Tweet

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.