IronShay

Converting ASP.Net 2.0 GridView to Excel CSV and HTML Formats

This time I'd like to share a small utility I've made that converts ASP.Net GridView object into CSV or HTML files that can be opened using the MS-Excel application.

[Link to download the code file is at the end of the post].

Why using the GridView?
If you're already using a GridView in your page to show some data, why not use its out-of-the-box localization and formatting capabilities? Every time that I run into an ASPX page that shows data with an "export to excel" feature that ends up with the same data view, I think "why don't we use the asp.net control for this too?"...

Converting to CSV
Converting a GridView to CSV is quite simple... All it takes is to loop over the grid's rows and join the cells' texts.
The tricky part is to change the content type of the server response so the client's browser will open the CSV using its related application (and not inside the browser).
Here is the method that does that:

/// <summary>
/// Converts a given GridView to a CSV file and sends it to the browser as the response stram
/// </summary>
/// <param name="grid">The filled GridView object to convert.</param>
/// <param name="fileName">The filename that will appear to the user in the download dialog.</param>
public static void ConvertGridToCSV(GridView grid, string fileName)
{
    // Clear the response output stream before start
    HttpContext.Current.Response.Clear();

    // Set the response headers to fit our CSV file
    HttpContext.Current.Response.ContentType = "text/plain";
    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);

    using (StreamWriter writer = new StreamWriter(HttpContext.Current.Response.OutputStream))
    {
        // Add header row
        writer.WriteLine(GetCSVLine(grid.HeaderRow.Cells));

        // Add all the data rows
        foreach (GridViewRow row in grid.Rows)
        {
            writer.WriteLine(GetCSVLine(row.Cells));
        }
    }

    // End the current response. Otherwise, excel will open with the whole page inside.
    HttpContext.Current.Response.End();
}

Let's explain the sequence:

1. [Line 4] We clear the response stream so all we do here will be sent to the client and nothing else.
2. [Lines 7-8] We set the response content type to be plain text and tells the browser that the page it loads is a file attachment and it should download it, not show it.
3. [Line 23] End the response stream so no other stuff will be inserted into our CSV stream.

* The GetCSVLine is a private method that joins the texts of the row cells into a comma separated string (you can see it in the code file).

Converting to HTML
This one is more tricky... It's not as simple as it sounds but it's not complicated either.
Let's dive into the code right away:

/// <summary>
/// Converts the given grid to HTML and sends it back to the browser as an Excel file.
/// </summary>
/// <param name="grid">The grid to convert.</param>
public static void ConvertGridToHTML(GridView grid)
{
    string gridHTML = String.Empty;

    using (StringWriter stringWriter = new StringWriter())
    {
        // Create a new page with a form element and the grid
        Page page = new Page();
        HtmlForm form = new HtmlForm();
        form.Controls.Add(grid);
        page.Controls.Add(form);

        // Get the HTML of the page
        HtmlTextWriter writer = new HtmlTextWriter(stringWriter);

        page.RenderControl(writer);

        writer.Flush();

        // Get the html
        gridHTML = stringWriter.ToString();
    }

    // Clear the response output stream before start
    HttpContext.Current.Response.Clear();

    // Set the response headers to fit our excel file
    HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

    HttpContext.Current.Response.Write(gridHTML);

    // End the current response. Otherwise, excel will open with the whole page inside.
    HttpContext.Current.Response.End();
}

Now the explanation:

1. [Lines 5-22] Here we form a dummy page. All it contains is an HtmlForm and our grid (lines 8-11). This is done because trying to get the grid's html alone (using RenderControl) ends up with an error "GridView must be nested inside a form with a runat=server attribute".
2. After we have the Grid's html, we just need to set the response and we're all good...
3. [Line 25] We clear the response stream so no other stuff will mess with our response stream.
4. [Line 28] We set the content type to be an ms-excel file. The client will end up opening the response in Excel.
5. [Line 30] We add the html of our grid to the response stream.
6. [Line 33] We end the response stream so it will be sent to the client right away.

Conclusion
Using a GridView for creating our exported objects is quite effective and time saving. The grid does all the formatting a localization stuff, which you'll do anyway for it, and all you have to do is take that and send it to the user as a different file type. Great!
Pay attention that this code assumes that the grid is already binded to the data.

Download code file: GridViewToExcel.cs (3.95 kb)

All the best,
Shay