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
Having just used Veggerby.Excel in a project, I have (of course) found something that does not work appropriately. Hence I have fixed this and a new version is available here.
- Styling changed a bit to allow for inheriting styles from workbook, rows, columns and cells
- Internal management of styles – to avoid storing duplicate styles
- And a couple of other things
How many times have you developed a web-application that requires user to be able to get their data out of your nice, clean, rich web application into an Excel sheet. I know I have more than one time. However either you need to invest in some 3rd party library that is way past the stage where you would scream “feature creep” or it will require your web-server to have MS Office installed locally and furthermore require you to have a reference to System.Windows.Forms (in your nice clean web-app).
Well, some maybe, can live with this, but there is actually a very simple way to make Excel “eat” your data. Only one small problem – it is only supported as of Office 2003.
It is of course the XML format – easy to understand, easy to develop. However a wrapper onto of this format can be a little nice. Therefor: introducing Veggerby.Excel (version 0.1.0).
Current feature list includes:
- Basic styling
- Formulas with easy to use referencing (not requiring absolute referencing)
- Cell merging
- And some others…
Check it out and read (a little) more here: Veggerby.Excel