ColdFusion Spreadsheet Memory issue

Question

I am using ColdFusion to export a fairly few number of rows (around 1000) but large number of columns (around 300) to Excel. It is a multi-sheet Excel file with at least two of the sheets having the large number of columns. Using cfspreadsheet throws a Java Heap Error. Updating JVM settings value shows no improvement. What is the best way to export to Excel without causing the Java Heap Error?

Edit: I have tried few ways to fix the issue within the program. I am using the xml Workbook within cfsavecontent to build the multiple sheets and render the result using cfcontent. In this case, cfcontent might be utilizing high amount of memory resulting in the heap space error.

<cfsavecontent variables="REQUEST.xmlData">
<cfoutput>
<xml version="1.0"?>
<?mso-application progid="Excel.sheet"?>
<Workbook>
   ...other contents
</Workbook>
</cfoutput>
</cfsavecontent>

For second workaround, I am using querynew to build the contents and dump the final result in an excel using <Cfspreadsheet action="write">. For subsequent sheets, I am using <cfspreadsheet action="update">. The ultimate goal is serve the excel using <cflocation url="excelPath">, but in this case, cfspreadsheet update is taking forever throwing out of memory error.

If updating jvm is not an option, what other ways do you suggest to implement to overcome the memory issues.

Caddy

It's hard to give any suggestions for improvement when we haven't seen the code or JVM settings that you have tried. – Goblet

Hi, I have updated my question with the details of what I tried. The code in my case is very long, so I included only the overview of each method I tried. – Caddy

That is a lot of columns. How many sheets? Large spreadsheets always consume a fair amount of memory. However, duping queries and using <cfspreadsheet action="update"> (copies existing sheets) probably is not a very memory efficient method. CFSpreadsheet is designed for simple dumps. Anything beyond that and you are better off using spreadsheet functions. What happens if you use spreadsheet functions to populate and generate multiple sheets? – Banderilla

I haven't tried using Spreadsheet functions except for spreadhseetaddrows and formatting. Is there a way we can just write the binary object directly using spreadsheetwrite function? – Caddy

Yes, take a look at the example in the linked thread. That is exactly what it does. See also the docs. – Banderilla

... if you mean pass in a query and generate a file, like cfspreadsheet does - the answer is "no". – Banderilla

like the one in my example with cfsavecontent. Can we use the variables 'REQUEST.xmlData' to be written directly to the file? – Caddy

I tried using SpreadsheetAddRows and Spreasheetwrite with gateway did not receive timely response error. Really running out of options with coldfusion. – Caddy

What do you mean by gateway? Also, what about the other questions: what are your JVM settings? How many sheets total? It would help to post a small example that reproduces the issue. Use queryNew() to simulate your db query. – Banderilla

Instead of using cfsavecontent why not append it to a file? that way the generated file content is never in memory, one other trick I've found is if you were generating multiple file to do it within a cfthread wait for it to complete by joining it in the main thread then calling Java System.gc() – Grimaud

Answer 1

I'm a little late to the party...

From what I can tell, cfspreadsheet tries to materialize the entire file in memory before flushing to disk. With cfsavecontent you're doing this explicitly.

You're familiar with building Workbook XML, so all that your cfsavecontent approach needs is streaming to disk.

This can be accomplished by digging into the underlying Java libraries. java.io.FileWriter can append to a file without keeping the entire file in memory:

var append = true;
var writer = createobject("java", "java.io.FileWriter").init(filename, append);
try {
  writer.append("<xml version=""1.0""?>\n<?mso-application progid=""Excel.sheet""?>\n<Workbook>\n");
  // Build your workbook in chunks
  // for (var row in query)
  //   writer.append(markup)
  writer.append("</Workbook>");
} finally {
  writer.close();
}

From testing, I believe FileWriter invokes flush regularly, so I've omitted it, but I can't find any documentation stating that's the case. I never saw memory usage get very high but YMMV.

Morava

No Java experience here. I have an array holding 83K rows of comma-delimited data. Looping whole array with spreadsheetAddRow() works if I limit < 50K, dies beyond that. I tried, with oSht=spreadsheetNew(), loop the array and every 1K rows use your code with writer.append(oSht);oSht=spreadsheetNew(); for next 1K rows. CF wants your code as a function (done), and guidance what args to pass would be big help. – Rawley