Really Nice Looking Export to Excel using Table Formatting

Inevitably, someone is going to want your data exportable to an Excel format, no matter how many different ways you let them look at right on your site–and they’re going to want it to look really nice.

If you’re just going to a CSV format, it’s pretty easy to do, but if it opens in Excel, none of the cells are formatted initially.  It can look good eventually, but you have to manually format it after the export.  It would sure be nice if you could format it better from the code itself.  For me, I thought it would be too tedious and time-consuming to actually create the Excel Spreadsheet.  I wanted something quicker.

Using a DataGrid for the bulk of the actual data works well as it renders as a table, and Excel seems to work well with table formats.  I used an HtmlTextWriter for this purpose.

HtmlTextWriter hw

However, the DataGrid wasn’t the only thing I wanted to export.  What I wanted was a nice big heading that was bolded, nice subheadings, nice spacing, and a bunch of things like that.  What I found is that if I just wrote table structure syntax, I could do basically everything I wanted.

What I did here was to write a large, centered heading that spanned 8 columns (the entire width of my report).  Both the “th” and the “h1” tags impact the way it renders in Excel.  I added the colspan and alignment attributes on the th tag, and it worked great.  Then I added a blank link by simply writing a br tag.  Here’s the code:

hw.WriteLine("<table><tr><th colspan=\"8\" align=\"center\"><h1>My Nice Looking Report</h1></th></tr></table>");

The rendering just follows down the page pretty nicely.  I added subheaders with h2 tags, and I played around with color and other attributes as well.  Then, after you export to Excel, the moment you open it up, all the formatting is there.  It looks pretty nice–particularly so in product demos.  Even if clients opt for csv format so they can put it into reporting software, the look that this provides gives great curb appeal.

Here’s the code for the sub heading and the legend for the report.  The term takes up two cells, and the description is confined to the other 6 cells (just because the total width was 8 cells/td’s).

hw.WriteLine("<tr><td colspan=\"8\"><h3>Report Legend</h3></td></tr>");

hw.WriteLine("<tr><td colspan='2'>Incomplete Evaluations</td><td colspan='6'>Evaluations assigned but not been completed.</td></tr>");
hw.WriteLine("<tr><td colspan='2'>Completed Evaluations</td><td colspan='6'>Evaluations assigned that have been completed.</td></tr>");
hw.WriteLine("<tr><td colspan='2'>Request Reviews</td><td colspan='6'>Total number of requests for reviews.</td></tr>");
hw.WriteLine("<tr><td colspan='2'>Unfulfilled Request Reviews</td><td colspan='6'>Total number of requests not yet reviewed.</td></tr>");
hw.WriteLine("<tr><td colspan='2'>Request Responses</strong>/td><td colspan='6'>Total number of request review responses. </td></tr>");


Again, this works really well for me. It allows me to easily control the format without actually creating an Excel Spreadsheet in the code itself.