Skip to main content
TCotton1
Participant
September 20, 2017
Question

Memory leak in spreadsheet functions CF11?

  • September 20, 2017
  • 2 replies
  • 1085 views

The following code to generate a spreadsheet and then download to the client will not free up memory at completion.

Running multiple times will (depending on the size of the heap and the number of rows in the database) exhaust the heap.

With 100,000 rows and a 2GB heap Java will hang on the 4th attempt to run the code.

<cfset spreadsheet = spreadSheetNew("Report",True)>

<cfset spreadsheetAddRow(spreadsheet,"Id,Name,Date,State,Zip")>

<cfquery name="MyQuery" datasource="test1">
SELECT    id, Name, Date, State, Zip     
FROM      myTable

</cfquery>

<cfset spreadSheetAddrows(spreadsheet,myQuery)>

<cfset spreadsheetFormatRow(spreadsheet,

{           
bold=true,            
fontsize=12    
},     
1)>

<cfset filename = "Report.xlsx">

<cfheader name="content-disposition" value="attachment;filename=#filename#">

<cfcontent type="application/msexcel"variable="#spreadsheetReadBinary(spreadsheet)#"reset="true">

Taking a heap dump and analyzing shows coldfusion.excel.Excel as the major heap user.

Changing the code to first write the spreadsheet to a file, read that file back into memory and then send to the client seems to fix the issue.

<cfset spreadsheet = spreadSheetNew("Report",True)>
<cfset spreadsheetAddRow(spreadsheet,"Id,Name,Date,State,Zip")>
<cfquery name="MyQuery" datasource="test1">
        SELECT    id, Name, Date, State, Zip
        FROM      myTable
</cfquery>
<cfset spreadSheetAddrows(spreadsheet,myQuery)>
<cfset spreadsheetFormatRow(spreadsheet,
        {
                bold=true,
                fontsize=12
        },
        1)>

<cfset spreadsheetWrite(spreadsheet,"/var/www/html/report.xlsx","yes")>
<cfset spreadsheet = spreadsheetRead("/var/www/html/report.xlsx", "Report")>
<cfset filename = "Report.xlsx">
<cffile action="delete" file="/var/www/html/report.xlsx">
<cfheader name="content-disposition" value="attachment; filename=#filename#">
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(spreadsheet)#" reset="true">

This is repeatable on ColdFusion 11 with update 9 and 12 on Linux. I don't have a windows install to test on.

Is this a bug or something the developers of the code are doing wrong?

Thank you.

This topic has been closed for replies.

2 replies

TCotton1
TCotton1Author
Participant
September 22, 2017

I was able to test with ColdFusion 2016 on Windows and Linux. I saw the same issue in both.

Carl Von Stetten
Legend
September 27, 2017

I don't see any bugs listed in the ColdFusion Bug tracker that match what you are seeing.  Can you file one?

-Carl V.

TCotton1
TCotton1Author
Participant
September 27, 2017
BKBK
Community Expert
Community Expert
September 21, 2017

Use the correct MIME type. It may or may not be the cause of the issue, but you will be doing the right thing. The MIME types are

XLS:  application/vnd.ms-excel

XLSX: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet