I created an Xlsx export using cold fusion.It throws the following Errors 1.java heap space,GC overhaed limit and Software caused connection abort: socket write error.

This is my Piece of code:
      // Procedure to get results
     <cfquery name="ExportData" datasource="#session.DataSource#">
                  EXEC xxx 6, #yy#,'#Dateformat(now(),'mm/dd/yyyy')#','#StartDate#', '#EndDate#', #rx#, NULL
   </cfquery>

This procedure gives the result set more than 1 lakh records.From this I formed the 'QoQ' from the above procedure.

'Query of Queries to get result set'

   <cfquery name="Residents" dbType="query">
                  SELECT ContactID, State, Location, Name, MIDate, MODate, MoveOutReason, MovedToDesc, DateOfBirth, Race, Gender, FirstTotalPts, LastTotalPts
                  FROM ExportData
                  WHERE SectionIND = 'Residents'
            </cfquery>

    <cffunction name="QueriesToXLS" access="public">
    <cfargument name="queryArr" required="true" /><!--- An Array of Query Objects --->
    <cfargument name="sheetNameArr" required="false" /><!--- Optional sheet names to use instead of "Sheet1","Sheet2",... --->
    <cfset tempPath = GetTempDirectory() & CreateUuid() & ".xlsx" /><!--- Creaete a Temp XLS File --->
    <cfset counter = 1 />
    <cfloop array="#ARGUMENTS.queryArr#" index="i">
        <cfset sheetName = "Sheet#counter#" />
         <cfif isDefined("ARGUMENTS.sheetNameArr")>
            <cfset sheetName = ARGUMENTS.sheetNameArr[counter] />
        </cfif>
        <cfspreadsheet action="update" filename="#tempPath#" query="i" sheetName="#sheetName#" />
        <cfset counter += 1 />
    </cfloop>
    <cfreturn SpreadsheetRead(tempPath) />
    </cffunction>

// function to form xlsx file --
<cfset xlsData = QueriesToXLS(
                  [Residents, x,y,z,a,f],
                  ["Residents", " x","y","z","a","f"]) />

If I runs the Procedure for smaller date range it gives the excel file.When runs for larger date ranges for example 13 thousand records it throws the above mentioned errors?How to overcome these errors.Please help me out on this.
boopathi sAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

boopathi sAuthor Commented:
Now I solved the java heap error using below code instead of  this<cfspreadsheet action="update" filename="#tempPath#" query="i" sheetName="#sheetName#" />and aslo the file size has been more than 5 mb how to compress file size.

<!--- Residents --->
            <cfset sheet = SpreadSheetNew("Reisdents") />
        <cfset SpreadsheetAddRow(sheet,"ContactID, State, Location, Name, MIDate, MODate, MoveOutReason, MovedToDesc, DateOfBirth, Race, Gender, FirstTotalPts, LastTotalPts") />
        <cfset SpreadsheetAddRows(sheet,Residents) />
            
            <!--- Incidents --->
        <cfset SpreadSheetCreateSheet(sheet, "Incidents") />
            <cfset SpreadSheetSetActiveSheet(sheet, "Incidents") />
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_agx_Commented:
Now I solved the java heap error

That code is better than "update", but both spreadsheets and QofQ's take a lot of memory, so you might still need to increase memory at some point if the query rows grow substantially.

how to compress file size
Are you using any formatting or just the code posted? I don't think CF offers any tools for compressing xlsx files. AFAIK, the only options are manually opening and saving it in Excel OR for .xls format (only), there's the underlying POI class HSSFOptimiser.  IIRC the latter only works for .xls files.
0
_agx_Commented:
"Update" uses more memory than spreadsheet functions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.