I am on a CF8 server (the company hasn't upgraded) an I need to create an xslx file.
I don't know of a solid way to do that in CF8. If you were on at least CF9, there is CFSPREADSHEET (for tag-based code) or SPREADSHEETNEW() (for CFSCRIPT code.) I prefer the latter, as it gives much more granular control.
I am actually looking at that now.
I’ve downloaded the code and changed the ext to .xslx and it creates a file but it won’t open.
Will give it a try.
I'm not sure if this will work on CF8, but here's a UDF I optimized to generate better CSV so that dates, strings, non-numeric-string and other values are preserved when converting to Excel. Use this w/Total CSV Converter... or just return the CSV file and it will open nicely in current versions of Excel.
Here's some sample code regarding how to convert a CSV file to XLSX:
I was using ColdFusion 9 (and tested this using CF10) on a client's data export that was taking ~10 minutes to perform. If I used Ben Nadel's POI library, it wouldn't take as along... but only because it was hitting Java HEAP RAM limitation faster and throwing an error. Once I switched to Total CSV Converter, the conversion process only took ~10 seconds.
Jamo, do you have a full code sample of this?
Full sample? The only thing that is critical is the command-line syntax for use w/Total CSV Converter. All you need to do is pass the full path & filename of the existing CSV and Excel file to create.
<cfexecute name="C:\TotalCSVConverter\CSVConverter.exe" arguments="""#InFile#"" ""#OutFile#"" -kfs -c XLSX -comma ##44 -quote ##34 -fh -TM 0.5 -LM 0.5 -BM 0.5 -RM 0.5 -ps A4" variable="Response" timeout="60"></cfexecute>
In addition, you can use this program to convert to JSON, XML, PDF, HTML, Word Doc, DBF, etc... It appears to be portable and can be installed anywhere on a Windows server. There's a helpful onboard wizard that assists with locating files and setting up new jobs through an easy to use GUI.