.NET Development Examined

Exporting to a Formatted Spreadsheet

Monday, February 7, 2011

Recently I had the need to export a grid from my ASP.NET page to an Excel spreadsheet. While searching for solutions I came across a post by Erika Ehrli titled How to Export Data to Excel from an ASP.NET Application + Avoid the File Format Differ Prompt. This article is well written and as the title suggests talks about the pesky prompt you receive when exporting an ASP.NET view to a spreadsheet. It also outlines 4 different approaches to exporting to Excel, I highly recommend you check it out.

For my purpose I settled on generating the spreadsheet from a GridView. This is a common technique and very simple to implement. However I wanted a little more control over the formatting of the resultant spreadsheet, and this was a limitation Erika listed. So with a little more research here is how I managed to exercise formatting control of the spreadsheet while still using simple ASP.NET techniques.

To start with you will need a ASP.NET page that contains a populated GridView. For this exercise I choose a subset of the Customers table in the Northwind database. I’m not going to bore you with how to setup the .aspx page, just use whatever data binding techniques you normally would use to create the page. I also applied the “Colorful” auto format to the grid. Running the page shows the simple grid shown below. Hopefully when we are done we will have the same grid, including the formatting, in Excel.

The first set is to modify the GridView template to use CSS styles instead of the hard coded colors that are applied when using the Autofomat. When done the markup looks as follows:

<asp:GridView ID="GridViewCustomer" runat="server" AutoGenerateColumns="False"
        CellPadding="4" DataKeyNames="CustomerID" DataSourceID="SqlDataSourceCustomer"
    <HeaderStyle CssClass="GridHeader" />
    <RowStyle CssClass="GridRow" />
    <AlternatingRowStyle CssClass="GridRowAlt" />
        <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True"
            SortExpression="CustomerID" />
        <asp:BoundField DataField="CompanyName" HeaderText="CompanyName"
            SortExpression="CompanyName" />
        <asp:BoundField DataField="ContactName" HeaderText="ContactName"
            SortExpression="ContactName" />
        <asp:BoundField DataField="ContactTitle" HeaderText="ContactTitle"
            SortExpression="ContactTitle" />

The CSS file is simple as well:

Now we have our grid setup and the styling based on a CSS style sheet.  We need to implement the code to export to Excel. I’m not going to cover the code in depth, there are plenty of article that do that. We are focusing on the formatting of the Excel spreadsheet, but we have to get there first. Add the function “ExportToExcell” that accepts a GridView as a parameter:
public void ExportToExcel(GridView gv)
    Response.Buffer = true;
    Response.ContentType = "application/vnd.ms-excel";
    Response.Write(@"<html xmlns:x=\urn:schemas-microsoft-com:office:excel\>");
    Response.AddHeader("content-disposition", "attachment;filename=Export.xls");
    Response.Charset = "";
    this.EnableViewState = false;
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);


Now add a button calling the ExportToExcel function and pass your GridView as an argument. You will also need to add the using “System.IO” to the top of your page and add the following override statement:
//This is here to overcome the error indicating that the grid must be placed in a form
//tag with runat=server, even though the grid is
public override void VerifyRenderingInServerForm(Control control)

If the coding Gods are with you when you run your page and click on the Export button you will be prompted to save or open the file. If you select “Open” Excel should fire up, and if you have not applied the registry setting in the KB Article you will see the prompt “The file you are trying to open, ‘Export[1].xls’, is in a different format…”. Select “Yes” to open the file. You should see a plain, unformatted spread sheet containing your data. So far so good, but we want color!

Now comes the fun part. While still in Excel select the header row, then right click and select “Format Cells”. Our header has a dark red background and white text, so click on the “Font” tab and select White for the “Color” drop down. Then click on the “Fill” tab and pick the dark red color. Select any other formatting you wish to apply (i.e. font style, size).
Now select the first row of data and set the background fill a color. Don’t worry about trying to match the color fill from our web page, we just need the place holder for the background color for now. Repeat this process for any custom formatting styles you have.

Now we need to see how Excel is applying those styles to determine how we can apply them from our web page. In Excel click the “File”, “Save As” and save the file as an .html formatted file. Excel will warn you about losing features, but we are not concerned about that. Open the folder where you saved the file and you will see a sub-folder called xxx_files where xxx is the name of your document. Open the sub-folder and we see several files, one of which is “stylesheet.css”. Eureka, we’ve struck gold!
Open the style sheet and towards the bottom you will find the CSS Classes you defined in Excel. It may take a little digging to determine which style is which; mine were called .xl66 and .xl67. But looking through the style sheet you can see how Excel applies different styling. All that is left now is to make sure we have a style sheet sent to Excel before we export.

Back in our “ExportToExcel” function we need to add the following code above the “Response.AddHeader” line:
Response.Write(".GridHeader {background:#990000;color:white;font-weight:bold;}");
Response.Write(".GridRow {background-color:#FFFBD6;Color:#333333;}");
Response.Write(".GridRowAlt {background-color:White;}");

Save and run your .NET page and try the export function again. You should now have a formatted spread sheet that matches your .NET page.

The key here is in our code the style name matches our CSS Stylesheet name (GridHeader, GridRow, and GridRowAlt). Because ASP.NET applies these CSS styles to the table cells generated on the htm output, they are also sent to Excel. All we needed to do was define the styles for Excel. In this simple example it was not totally necessary to explore Excel’s formatting, for the most part it follows general CSS style nomenclature. At some point you may run across something that the “Save As” HTML helps you through.

I’ll leave you with one last tip; sometimes you may want the grid lines to show in Excel, but not on your web page. To accomplish this simple put the line “gv.GridLines=GridLines.Both”  before the “gv.RenderControl(htw)” line in your ExportToExcel function.


Add Comment