Issues with comma separated documents and Excel
This is just a FYI in case you ever run into this problem.
If you are dynamically creating a delimited text file to use for importing into Excel, you might want to take note of this in order to prevent catching flack from the overzealous users of your projects.
First, forget about the CSV extension. Don't use it. The document may open up correctly if downloaded and opened from a URL but if the users save it to their desktop and then open it, Excel seems to ignore the cell delimiter and stuffs everything into column A.
Also, Netscape likes to consider CSV files as text and displays it in the browser instead of opening Excel.
In order to resolve the above issues and others, perform the following steps.- Use the XLS file extension. Excel, upon opening the document, will realize it's a text file and will translate it.
- Use the TAB - Chr(9) - as the cell delimiter.
- Write the file to the server's hard drive.
- Use <CFCONTENT> to send the file to the user.
In your URL for downloading the document to the user, simply call your template again with a URL variable specifying the file to send the user and the following code will send it to the user without altering whatever information is currently being displayed on the web site. Assuming your template name is "GenerateReport.cfm", your HTML would read: <a href="GenerateReport.cfm?SendFile=#filename#">
The two Find functions ensure the user isn't trying to get the program to download something they are not supposed to be downloading.
Example HTML/CFML code:
<!--- See if we need to download the user's report to them --->
<CFPARAM name="URL.SendFile" DEFAULT="">
<CFIF URL.SendFile NEQ "" AND Find("/",URL.SendFile) EQ 0 AND Find("\",URL.SendFile) EQ 0>
<cfcontent type="application/vnd.ms-excel" file="#GenFilePath##URL.SendFile#" deletefile="No">
<CFABORT>
</CFIF>
Return to the Cold Fusion Tips-N-Tricks topic list