Excel has wonderful features but its defaults are extremely frustrating. Many of them I simply do not understand, for example if a column has leading zeroes then the user probably wants to keep them! However, I came across one issue which I had not experienced until now.
Anyone who has had to deal with internationalisation will probably be aware of the differences in European decimals for example 1,000.00 in UK & US is written as 1.000,00 in many European countries. Microsoft Windows has region settings which take these into account. Excel uses the region settings as the default method for formatting files. This seems reasonable but it contains a setting called list separator which it uses as the default separator for reading a CSV file. If your region is one where "," is the standard for decimals then this separator will default to ";".
This means that if you double click a CSV file which is comma separated it will open and display in a single column. What is worse is if your data has a ";" in it then this will be used as a separator and the data will be split on this value. So a CSV file which opens with a double click perfectly on a computer in the US will open incorrectly on a computer in Germany. If you were to alter your CSV output to ";" separated then the file would open in Germany fine, but the US client would find the data all in the first column.
You cannot even get the end user to alter their region settings to use "," as the list separator because Excel will ignore this because you have "," as a decimal separator. Microsoft do not appear to have worked very hard at helping the user with the default behaviour.
There is of course a workaround which is to open Excel and use the import option to specify how to handle the file, but this feels like a punch in the face in terms of usability.
So what should use do if you want to allow your web application to output CSV files for end users to multiple regions where you expect many of them will use Excel to consume them?