From time to time I ear some people saying their new web application supports data export to Excel format.
So far so good … but they don’t tell the all story … in fact almost all the times what is happening is they are exporting data to a Comma-Separated file or simply exporting GridView rendered HTML to an xls file.
Ok … it works but it’s not something I would be proud of.
So … yesterday I decided to take a look at the Office Open XML File Formats Specification (Microsoft Office 2007+ format) based on well-known technologies: ZIP and XML.
I start by installing Open XML SDK 2.0 for Microsoft Office and playing with some samples.
Then I decided to try it on a more complex web application and the “file is corrupt and cannot be opened.” message start happening.
Google show us that many people suffer from the same and it seems there are many reasons that can trigger this message. Some are related to the process itself, others with encodings or even styling.
Well, none solved my problem and I had to dig … well not that much, I simply change the output file extension to zip and extract the zip content.
Then I did the same to the output file from my first sample, compare both zip contents with SourceGear DiffMerge and found that my problem was Culture related.
Yes, my complex application sets the Thread.CurrentThread.CurrentCulture to a non-English culture.
For sample purposes I was simply using the ToString method to convert numbers and dates to a string representation but forgot that XML is culture invariant and thus using a decimal separator other than “.” will result in a deserialization problem.
I solve the “file is corrupt and cannot be opened.” by using Convert.ToString(object, CultureInfo.InvariantCulture) method instead of the ToString method.
Hope this can help someone.