YES, it is an java excel poi. Here is the code, maybe you can help us to find out the memory leak.
The code is part of a function that I pass parameter such as the data, the column names and formats.
<cfif not DirectoryExists("#getDirectoryFromPath(getCurrentTemplatePath())#excelDumpFiles")>
<cfdirectory action="create" directory="#getDirectoryFromPath(getCurrentTemplatePath())#excelDumpFiles">
</cfif>
<cfset filepath = "#getDirectoryFromPath(getCurrentTemplatePath())#excelDumpFiles\#fileName#" />
<cfset xlsFile = createObject("java","java.io.FileOutputStream").init(filepath) />
<cfset writeableWorkbook = createObject("java","org.apache.poi.xssf.streaming.SXSSFWorkbook").init(1000) />
<cfset cellStyleStatic = createObject("java","org.apache.poi.xssf.usermodel.XSSFCellStyle") />
<cfset region = createObject("java","org.apache.poi.ss.util.CellRangeAddress") />
<!--- set up data formats --->
<cfset moneyFormat = writeableWorkbook.createDataFormat() />
<cfset wholeNumMoneyFormat = writeableWorkbook.createDataFormat() />
<cfset dec1Format = writeableWorkbook.createDataFormat() />
<cfset dec2Format = writeableWorkbook.createDataFormat() />
<cfset dec3Format = writeableWorkbook.createDataFormat() />
<cfset percentFormat = writeableWorkbook.createDataFormat() />
<cfset wholeNumFormat = writeableWorkbook.createDataFormat() />
<cfset moneyStyle = writeableWorkbook.createCellStyle() />
<cfset moneyStyle.setDataFormat(moneyFormat.getFormat("$##,####0.00_);[Red]($##,####0.00)")) />
<cfset wholeNumMoneyStyle = writeableWorkbook.createCellStyle() />
<cfset wholeNumMoneyStyle.setDataFormat(wholeNumMoneyFormat.getFormat("$##,####0_);[Red]($##,####0)")) />
<cfset dec1Style = writeableWorkbook.createCellStyle() />
<cfset dec1Style.setDataFormat(dec1Format.getFormat("##,####0.0_);[Red](##,####0.0)")) />
<cfset dec2Style = writeableWorkbook.createCellStyle() />
<cfset dec2Style.setDataFormat(dec2Format.getFormat("##,####0.00_);[Red](##,####0.00)")) />
<cfset dec3Style = writeableWorkbook.createCellStyle() />
<cfset dec3Style.setDataFormat(dec3Format.getFormat("##,####0.000_);[Red](##,####0.000)")) />
<cfset percentStyle = writeableWorkbook.createCellStyle() />
<cfset percentStyle.setDataFormat(percentFormat.getFormat("##,####0.00_)%;[Red](##,####0.00)%")) />
<cfset wholeNumStyle = writeableWorkbook.createCellStyle() />
<cfset wholeNumStyle.setDataFormat(wholeNumFormat.getFormat("##,####0_);[Red](##,####0)")) />
<cfset numberOfLoops = (ArrayLen(arguments) - 1) / 5 />
<cfset argTemp = 1 />
<cfloop from="1" to="#numberOfLoops#" index="argIndex">
<cfif arguments[argIndex + argTemp] >
<cfset argTemp = argTemp + 1 />
<cfset rowCount = 0>
<!--- NEW SHEET --->
<cfset newSheet = writeableWorkbook.createSheet() />
<cfset temp = writeableWorkbook.setSheetName(sheetNumber, arguments[argIndex + argTemp]) />
<!--- add meta data only for the first workbook or worksheet --->
<cfif sheetNumber eq 0>
<cfset newSheet.addMergedRegion(region.init(0,0,0,20))> <!--- first row, last row, first col, last col --->
<cfset newRow = newSheet.createRow(javaCast("int",0))>
<cfset newCell = newRow.createCell(javaCast("int",0))>
<cfset temp = newCell.setCellType(javaCast("int",1))> <!--- string --->
<cfset temp = newCell.setCellValue(javaCast("string",reqString))>
<cfset rowCount = rowCount + 1 />
</cfif>
<!--- Get Data --->
<cfset argTemp = argTemp + 1 />
<cfset qryExcelData = arguments[argIndex + argTemp] />
<!--- create column span headers --->
<cfset argTemp = argTemp + 1 />
<cfset newRow = newSheet.createRow(javaCast("int",rowCount)) />
<cfset styleAlignCenter = writeableWorkbook.createCellStyle() />
<cfset styleAlignCenter.setAlignment(cellStyleStatic.ALIGN_CENTER) />
<cfloop from="1" to="#ArrayLen(arguments[argIndex + argTemp])#" index="index">
<cfset newSheet.addMergedRegion(region.init(rowCount,rowCount,javaCast("int",StructFind(arguments[argIndex + argTemp][index],"spanStart")),javaCast("int",StructFind(arguments[argIndex + argTemp][index],"spanEnd"))))> <!--- first row, last row, first col, last col --->
<cfset newCell = newRow.createCell(javaCast("int",StructFind(arguments[argIndex + argTemp][index],"spanStart")))>
<cfset newCell.setCellStyle(styleAlignCenter)>
<cfset temp = newCell.setCellType(javaCast("int",1))> <!--- string --->
<cfset temp = newCell.setCellValue(javaCast("string",StructFind(arguments[argIndex + argTemp][index],"columnHeader")))>
</cfloop>
<!--- create column headers --->
<cfset argTemp = argTemp + 1 />
<cfset rowCount = rowCount + 1>
<cfset newRow = newSheet.createRow(javaCast("int",rowCount))>
<cfloop from="1" to="#ArrayLen(arguments[argIndex + argTemp])#" index="index">
<cfset newCell = newRow.createCell(javaCast("int",index-1))>
<cfset temp = newCell.setCellType(javaCast("int",1))> <!--- string --->
<cfset temp = newCell.setCellValue(javaCast("string",StructFind(arguments[argIndex + argTemp][index],"columnHeader")))>
</cfloop>
<!--- Loop thru the data --->
<cfset colLength = ArrayLen(arguments[argIndex + argTemp]) />
<!--- <cfset colLength = 7 /> --->
<cfoutput query="qryExcelData">
<cfset rowCount = rowCount + 1>
<cfset newRow = newSheet.createRow(javaCast("int",rowCount))>
<cfloop from="1" to="#colLength#" index="index">
<cfset tempCol = StructFind(arguments[argIndex + argTemp][index],'columnName') />
<cfset tempFormat = StructFind(arguments[argIndex + argTemp][index],'excelFormat') />
<cfset newCell = newRow.createCell(javaCast("int",index-1))>
<cfif isNumeric(qryExcelData["#tempCol#"][qryExcelData.currentRow])>
<cfif FindNoCase(".",tempFormat)>
<cfset decimalLen = Len(Trim(REReplace(tempFormat,"[$,.]","","ALL"))) />
</cfif>
<cfif tempCol eq "ColumnNameA">
<cfset newCell.setCellType(javaCast("int",1)) /> <!--- string --->
<cfset Evaluate("qryExcelData.#tempCol#") />
<cfset newCell.setCellValue(javaCast("string",IIF(temp,'TRUE','FALSE'))) />
<cfelse>
<cfset temp = newCell.setCellType(javaCast("int",0))> <!--- numeric --->
<cfif FindNoCase(",",tempFormat) and not FindNoCase("$",tempFormat)>
<cfif decimalLen eq 1>
<cfset temp = newCell.setCellStyle(dec1Style)>
<cfelseif decimalLen eq 2>
<cfset temp = newCell.setCellStyle(dec2Style)>
<cfelseif decimalLen eq 3>
<cfset temp = newCell.setCellStyle(dec3Style)>
<cfelse>
<cfset temp = newCell.setCellStyle(wholeNumStyle)>
</cfif>
<cfelseif FindNoCase("$",tempFormat)>
<cfif NOT FindNoCase(".",tempFormat)>
<cfset temp = newCell.setCellStyle(wholeNumMoneyStyle)>
<cfelse>
<cfset temp = newCell.setCellStyle(moneyStyle)>
</cfif>
</cfif>
<cfset temp = newCell.setCellValue(javaCast("double",qryExcelData["#tempCol#"][qryExcelData.currentRow]))>
</cfif>
<cfelseif isDate(qryExcelData["#tempCol#"][qryExcelData.currentRow])>
<cfset tempVal = qryExcelData["#tempCol#"][qryExcelData.currentRow] />
<cfif second(tempVal) gt 0>
<cfset tempVal = '#DateFormat(tempVal,"MM/DD/YYYY")# #TimeFormat(tempVal,"HH:mm:ss")#' />
<cfelse>
<cfset tempVal = DateFormat(tempVal,"MM/DD/YYYY") />
</cfif>
<cfset newCell.setCellType(javaCast("int",1)) /> <!--- string --->
<cfset newCell.setCellValue(javaCast("string",tempVal))>
<cfelse>
<cfif tempCol neq "">
<cfset temp = newCell.setCellValue(javaCast("string",qryExcelData["#tempCol#"][qryExcelData.currentRow]))>
<cfelse>
<cfset temp = newCell.setCellValue(javaCast("string","#tempFormat#"))>
</cfif>
</cfif>
<cfset decimalLen = 0 />
</cfloop>
</cfoutput>
<!--- auto expand columns --->
<cfloop from="0" to="#ArrayLen(arguments[argIndex + argTemp])#" index="index">
<cfset newSheet.autoSizeColumn(javaCast("int",index)) />
</cfloop>
<cfset sheetNumber = sheetNumber + 1>
<cfelse>
<cfset argargTemp + 5 />
</cfif>
</cfloop>
<!--- Write Excel File --->
<cfset writeableWorkbook.write(xlsFile)>
<cfset xlsFile.close()>
<cfset writeableWorkbook.dispose() />
<cfcatch>
<cfset dataStatus = "Excel creation failed.<br>#cfcatch.message# #cfcatch.detail#" />
<!--- <cfset application.debug(cfcatch) /><cfabort> --->
</cfcatch>
</cftry>
1 Correct answer
14
Replies
AdChoices