Monday, 3 January 2011

Exporting to Excel from GridView - Getting Started


This post and series of other posts will will discuss about different ways of exporting the data to Excel from ASP.NET Web Applications.

Eventhough there are many ways, all of them follow a basic concept of exporting the data to excel. In this blog I am explaining five ways to export data to excel. They are

  1. Exporting the data from GridView with all the columns shown on the page (link).
  2. Exporting the data from GridView by adding some new columns & removing existing columns (link).
  3. Exporting the data to Excel where page does not contains any GridView (link).
  4. Exporting the data to a Templated Excel sheet (link).

Before going to the actual implementation, we will look at the basic concept on Exporting to Excel.

For example, I want to export an Employee details which contains two columns EmployeeID, EmployeeName as the below figure shows.


Considering the layout of an excel sheet, it has many Rows and Columns which is same as Table. So if we want to export the data to excel, a simple way is to have a table and export the same structure into Excel sheet (just like copy and paste from another excel sheet).

But in Web Application we have to make the code behind to export a table to a file with .xls extension (saving a web page in.xls extension which has only GridView). For Ex:

The simple code looks as follows
string strTable = @"<table>
                        <tr>
                            <th>Employee ID</th>
                            <th>Employee Name</th>
                        </tr>
                        <tr>
                            <td>101</td>
                            <td>Maria Anders</td>
                        </tr>
                        <tr>
                            <td>102</td>
                            <td>Ana Trujillo</td>
                        </tr>
                    </table>";
Context.Response.ClearContent();
Context.Response.ContentType = "application/ms-excel";
Context.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", "ExcelFileName"));
Context.Response.Charset = "";
Context.Response.Write(strTable);
Context.Response.End();

If we look at the code,

  1. I have a string containing a script representing a table.
  2. Clear the contents from the response stream. So no header, footer and other controls.
  3. Set what kind of output stream required. By default it is html (text/html), we required to set as excel type (ms-excel).
  4. Set the excel file name to be exported.
  5. Write the html script to the response and calling end method. So it writes to Excel file.

Instead of constructing a table script at runtime, I can have it in aspx script also and export from code behind. For Ex:
<table runat="server" id="tableEmployee">
    <tr>
        <th>Employee ID</th>
        <th>Employee Name</th>
    </tr>
    <tr>
        <td>101</td>
        <td>Maria Anders</td>
    </tr>
    <tr>
        <td>102</td>
        <td>Ana Trujillo</td>
    </tr>
</table>
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);
tableEmployee.RenderControl(htmlwriter);

Context.Response.Write(stringwriter);
Context.Response.End();

Here I am getting the HTML script using line # 6 to 8 and writing to the response. We will be getting the HTML sctipt from GridView instead of GridView in actual implementation. Actually in GridView, the ASP.NET runtime rendering GridView as a table and transferred to client to shows in the browser (Right click the page and view the source. The GridView will be a table).


4 Responses to “Exporting to Excel from GridView - Getting Started”

  • Priya says:
    14 September 2012 at 19:06

    Good one. We may also face some exceptions while trying to Render conrols to response. This example explains how to overcome those errors and exceptions while trying to export data from table or grid view to excel sheet.

  • Anonymous says:
    7 December 2012 at 11:19

    how to apply forecolor in headers using this table

  • Thiru says:
    7 December 2012 at 13:17

    You need to apply styles as we do in html script. The table is same HTML script, so we can apply all types of styles here before exporting.

    For Ex - to apply forecolor for header -

    <table runat="server" id="tableEmployee">
    <tr style='color:green;text-weight:bold'>
    <th>Employee ID</th> <th>Employee Name</th>
    </tr> <tr> <td>101</td> <td>Maria Anders</td>
    </tr>
    <tr> <td>102</td> <td>Ana Trujillo</td>
    </tr>
    </table>

  • Richard Brown says:
    28 October 2016 at 18:55


    Thiru, thank you for the best comment. Very informative, and most importantly - useful
    Richard Brown dataroom

Post a Comment