Export GridView to Excel

In Scott Guthrie’s August 30th Links I saw a link to Matt Berseth’s blog about exporting a GridView to Excel.

I have used a method similar to this earlier and apart from it being very easily developed, I have found a very, very annoying issue with this method.

The first thing is you need to make sure the output is localized (in regards to formatting), especially for floating point values and dates (but as I’ll explain, this really is not enough).

What I actually found was that it was/is a pretty tricky thing to accomplish. Normally you would of course just set the Culture in Web.config, in the section or imperatively on the current thread. For my Windows is using the Danish regional settings, which means that fx. a date is formatted as dd/mm/yyyy, however at the company where I work (we’re global) we have a standard date format (yyyy-mm-dd) which is being set by group policy.

What ASP.NET does when using the Culture, it uses the default settings for the specified language(s) in the request header. This means for me it would specify “da” as language in the browser. This will make ASP.NET set the “da-DK” Culture and format numbers based on the standard danish settings for numbers, dates, etc. Which means that if I make a request to this kind of Excel sheet containing a localized date it will be formatted “dd/mm/yyyy” which will cause Excel to misinterpret it since it expects my date formats to be yyyy-mm-dd.

The above perhaps sound a but hypothetical, but consider this: I have set my language to english in the browser (since I don’t want fx. Windows Update to send me the danish updates to my english OS – which is would otherwise do). So with my language to “en-us” in the browser, I open the Excel sheet using Matt’s method and it will/should format the HTML table output based on the “en-US” culture, however Excel will try to interpret this using my Windows Regional Settings, which is danish. What a mess!!!

In this case when running Matt’s example I get this in my browser:

and this in Excel:

The problem is here that in Danish numerical layout the decimal symbol is , (comma) and the thousand separator is . (dot) – the complete opposite of en-US. This causes Excel to interpret the UnitPrice in Matt’s example as a number with a thousands separator and not a decimal symbol (even though it is displayed with 4 digits after the decimal/thousand separator). So we get UnitPrice’s of 140000, 98000, 348000, etc. (thousand separators left out on purpose ) instead of 14, 9.8, 34.8, etc. (here dot is decimal symbol). Even worse is it for Discount, since a Excel will not interpret a number with 0 in front of the thousand separator as a valid number so all the 0 (zeros) in that column are interpreted as numerical zeros, but 0.15 is not interpreted as a number and is left as a “text” and thereby unusable for use in formulas.

If Matt has included dates in this example it would be even worse!

So bottom line is: this is a very simple method and can be very usefull (especially if you have only text values), but if you need to make sure data is consistent, it can be a big pain since it relies on Excel interpreting the HTML output (strings) which is/can be formatted in a number of different ways.

This is not just theoretical, I have experienced these issues in real-life on a real project. If consistent data is needed it would probably require a native format for the export, such as the one used by Veggerby.Excel. That was the lesson I learned from this