I have a standard CAF Export Table Button that is configured to Export a Table and when you open the Excel using versions prior to Office 2007,it has every CSV in its own Excel Column. However, in versions prior to Office 2007, it displays the standard Excel “format” message and when you click OK, all the columns appear in a single column.
If I give the extension as xls in the Export Table button “Export File Name” property, it always opens the data in a single column. Is this as expected?
I know this is not the forum to ask this, but if anyone has experienced the same and if you were able to resolve, can you please advise?
I have the Encoding set to UTF-16 because there are a few French characters in the data.
The BOM story…
Also, the value in first cell of the cell is prefixed by the character þÿ which upon Googling I gather it is a Byte Mark Order set to indicate the start of a stream of data. Is there a way to get rid of this?
And also, if the “Export File Name” is bound to a String variable whose value is set at Run Time, the control never opens the file with this name but with the default export.csv. I have hence had to specify a value ( hard coded !!) as Blah_Blah_Blah.csv.
Any help with the above issues is much appreciated.
Have you had a chance to look at the File Export Sample on the Communities site? There is a line that sets the Content Type which is probably critical to the success of Microsoft Excel properly interpreting the data:
Yes Mark, I used streamFileDataToResponse to resolve this.
When you open a csv file extension in Excel 2003 and older versions, the default separator is Tab and not comma.
So I used a StringBuffer to hold the data with Tab separation instead of comma. This will work with Excel 2003 with file extension “.csv” but not in Excel 2007. To make it work in both versions, I changed the extension to “.xls”. Below is the contentType setting.
However, when the file is opened in either of Excel versions, it throws an alert that “The contents are not in the correct format blah blah, this will blow up your PC etc… Do you want to open it?” and the customer is not happy about it.
I know all this will be solved if you create an actual Excel application using JXL, but when you weigh it against time, this was the quickest.
Ok, it looks like you’re doing everything correctly. The best I can recommend is to keep experimenting with different params, but i suspect you’ve already been doing that.
If you’ve narrowed down two different params that work for 2003 & 2007, maybe you can convince the customer to have two different download buttons that the users can choose from based on what version of Office they have installed?