creating .xslx files in coldfusion 8

[atlcf]

Hello All

I am on a CF8 server (the company hasn't upgraded) an I need to create an xslx file.

Any suggestions?

[WolfShade]

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.

HTH,

^_^

[Carl Von Stetten]

You might also look at Apache POI and Ben Nadel's CFC wrapper: GitHub - bennadel/POIUtility.cfc: A ColdFusion component (and set of custom tags) for reading and wr...

[atlcf]

Thanks

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.

[James Moberg]

The best way I've found to export data from ColdFusion 4, 5, 6, 7, 8, 9, 10 & 11 to XLSX format is to use CFExecute and Total CSV Converter

https://www.coolutils.com/TotalCSVConverter

[atlcf]

Thanks!

Will give it a try.

[James Moberg]

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.

https://gist.github.com/JamoCA/b2d0467dd914909f00bf

[James Moberg]

Here's some sample code regarding how to convert a CSV file to XLSX:
https://gist.github.com/JamoCA/75399b2eee3ad792a2e7

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.

[atlcf]

Thank you

[atlcf]

Jamo, do you have a full code sample of this?

[James Moberg]

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.