April 5th, 2013, 09:53 AM
Exporting data with cfx_csvExport
I'm running into an issue trying to export double byte data into .csv format using Excel. The database currently stores Chinese characters for a name. When I run a report and try to export that data in .csv format using the custom tag cfx_csvExport the data shows as ?????
I understand that Excel doesn't handle double byte characters very well.
This seems to be an issue on the side of Excel right? Is there a way I can export double byte characters using the custom tag successfully?
April 5th, 2013, 11:25 AM
I've never used that custom tag so I have no idea. I tried a Google Search for the tag but nothing comes up, so my guess is that it's really old. Newer versions of CF have built-in Excel generation features, using (I believe) Apache POI.
April 5th, 2013, 11:44 AM
Unfortunately I'm on ColdFusion Mx7 and there won't be plans to upgrade.
I pulled this code and I'm trying to get it to work to generate an excel file.
This loads the data in Excel, however when Excel load up the following message appears before opening the spreadsheet.
<cfif url.format eq "excel">
<cfheader name="Content-Disposition" value="attachment; filename=test.xls">
<cfcontent type="application/vnd.ms-excel; charset=utf-8" reset="yes">
<tr> <th>VendorCourseID</th> <th>CourseName</th> <th>IsLive</th>
<cfoutput query="courses"> <tr> <td>#vendorCourseID#</td> <td>#coursename#</td> <td>#islive#</td>
<cfabort /> </cfif>
'The file you are trying to open, 'test-1.xls' is in a different format then specified by the file extension. Verify the file is not corrupted ans is from a trusted source before opening the file.'
I go ahead and open the file and the double byte characters are still messed up.
デル オンライントレーニングにようこそ(Japan test)
April 5th, 2013, 12:18 PM
If you can view the content without the Excel conversion and you can see the UTF-8 characters, then the problem has to be within the CFX tag that's generating the file.
April 13th, 2013, 06:51 PM
That is due to Excel's Extension Hardening security feature . Whenever file content does not match the extension, the user is warned. In your case it is because you are actually returning html, but cfcontent claims it is an .xls file. You cannot disable the warning from the server. The only way to avoid it is make the file extension match the content.
(Faux-Excel exports used to be very handy, but the new security restrictions make them a less than ideal option these days)
If you are using HTML, you must also include a <meta> tag that specifies charset=UTF-8. See this thread for details .
Last edited by cfSearching; April 13th, 2013 at 07:04 PM.
April 13th, 2013, 06:59 PM
If you need generate a true binary Excel file, take a look at the POIUtility.cfc. I believe the early versions supported MX7.
That said, you should not need any extra tags or components to generate a CSV file. It is essentially just a text file, you can generate yourself - on any version of CF. Take a look at cflib.org. It has several functions for converting queries to CSV.