I am trying to build a excel xlsx workbook using Coldfusion. I am use the various spreadsheetXYZ functions to do so. A problem I have come across is that some of the exports I am running have lots of data, and so it seems memory becomes of concern. My understanding is that the coldfusion spreadsheet functions keep everything in memory until spreadsheetWrite is called.
Is there a more efficient way to generate a workbook, with multiple sheets and lots of data?
https://github.com/cfsimplicity/spreadsheet-cfml
Spreadsheet CFML - Standalone library for working with spreadsheets in CFML
It has more functionality than the built-in cfspredsheet tag. Most notably for your purposes newStreamingXlsx which keeps large excel sheets memory efficient.
spreadsheet = New spreadsheet();
workbook = spreadsheet.newStreamingXlsx( streamingWindowSize=10 );
spreadsheet.addRows( workbook, data ); //Data can come from a query/array
spreadsheet.download( workbook,"report.xlsx" );
And this is just a simple example, There are many other features included like: addAutofilter, addFreezePane, addImage, addInfo, addPageBreaks, writeToCsv, etc.