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.

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.