Useful code for exporting to Excel xlsx

I wanted to "give back" by posting this little code sample for exporting a query into Excel xlsx

Kudos to Charlie Arehart for helping me increase the server memory and java heap size.

This code works for me using CF 9 on Windows Server 2008 R2. By the way, CFSpreadsheet seems to consume a lot of memory on larger exports. For this reason, we increased the server's physical memory, then used CF Administrator to increase the jvm heap size in the Administrator -> Java and JVM settings. My max java heap size is now 3072.

Need help? I recommend you contact Charlie Arehart on server issues: charlie@carehart.org

Code:

Example query is called "Myquery" Spreadsheet is called "Myspreadsheet" I export the spreadsheet to a subdirectory called "xlsx" I use cflocation to direct the browser to the spreadsheet. The browser prompts the user to open, save or cancel.

<cfset var_filenameis = "Myspreadsheet.xlsx">

<cfset SpreadsheetObj = spreadsheetNew("true")>

<cfset SpreadsheetObj = spreadsheetNew("#var_filenameis#","yes")>

<cfspreadsheet action="write" filename="./xlsx/#var_filenameis#" query="Myquery" overwrite="true">

<cflocation url = "./xlsx/#var_filenameis#">