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
dg.RenderControl(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>");
hw.WriteLine("<br>");

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("<table>");
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>");

hw.WriteLine("</table>");
hw.WriteLine("<br>");

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.

Advertisements

Unexpected benefits of Precompilation of LINQ

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

I once had a manager who told me – I can solve any maintenance problem by adding a layer of abstraction.  I can solve any performance problem by removing a layer of abstraction.

I think LINQ to SQL is a wonderful way to abstract the persistence layer elegant, easy to use, easy to manipulate, and easy to maintain lines of code.  Instead of writing SQL which amounts to “how to retrieve” the data – you manipulate an expression tree that gets closer to specifying “what data I want”.  The upside of this is huge – you can change the expression tree at any level of your code, and let .NET decide how to best write the SQL at the last possible moment – which effectively gits rid of dealing with intermediate results and inefficiently written SQL.  Unfortunately, this abstraction does indeed cause a performance hit – the translation/compilation of the tree to SQL – and it’s probably much bigger than you would think.  See http://peterkellner.net/2009/05/06/linq-to-sql-slow-performance-compilequery-critical/ to see what I mean.  In my analysis (using ANTS Profiler), when using uncompiled LINQ – the performance hit is usually about 80% compilation and only 20% retrieving the data!  Thankfully, .NET does allow you to precompile a LINQ query and save the compilation to use over and over again.

Your natural tendency when hearing those kind of numbers might be to precompile every single LINQ query you write.  There’s a big downside to doing that, though – you lose the ability to manipulate the compiled query in other parts of your code.  Another downside is that the precompilation code itself is fairly ugly and hard to read/maintain.

I’m a big believer in avoiding “premature optimization”.  What happens if you precompile everything, and in a version or two Microsoft resolves the issue and caches compilations for you behind the scenes?  You have written a ton of ugly code that breaks a major benefit of LINQ to SQL and is totally unnecessary.

Instead, I recommend you go after the low hanging fruit first – precompile the most frequently accessed queries in your application and the ones that gain no benefit from manipulating the expression tree.  In the applications I work on – there is a perfect case that fits both of these – the “get” method that returns the LINQ object representation of a single row in the database.  These are hit quite often – and there is absolutely no case where the expression tree is further refined.

The old way it was written:

	public static Item Get(int itemid) {
		return (from i in DataContext.Items
			where i.ItemID == itemid
		select i).First();
	}

The new way with Precompiled LINQ:

	private static Func<ModelDataContext, int, Item>
		GetQuery = CompiledQuery.Compile(
			(ModelDataContext DataContext, int itemid) =>

				(from i in DataContext.Items
					where i.ItemID == itemid
				select i).First()

				);

	public static Item Get(int itemid) {
		return GetQuery.Invoke(DataContext,itemid);
	}

Applying this fairly simple change to the application, I’d estimate we got 80%+ of the benefits of compiled LINQ, at the expense of a few extra lines of code per object/table and absolutely no loss of the tree manipulation.