4 Replies Latest reply on Sep 27, 2017 9:17 AM by TCotton1

    Memory leak in spreadsheet functions CF11?

    TCotton1

      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.