Exporting to Excel from GridView - Getting Started

Tweet

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).

The other links on Export to Excel from GridView:

1. Exporting to Excel from GridView - Getting Started
2. Exporting to Excel from GridView (All columns and rows - Normal Method)
3. Exporting to Excel from GridView (Adding & Removing columns with all rows)
4. Exporting to Excel from GridView (using a custom class without GridView)
5. Exporting to Excel using Excel Application object
6. Exporting the data to a Templated Excel sheet using Excel Application object
7. Exporting the data to Excel sheet with image embedded using Excel Application object