I am creating an Excel worksheet that contains about 250,000 rows. Some of these rows will contain an image which is an actual customers signature. After creating the workbook the memory is not released. A total of 1.2GB is being used. If I remove the code that is creating the images the memory consumption is dramatically reduced but is still never released until I exit my application. If run the report again it adds to the memory consumption.
Is the code below flawed?
DEFINE VARIABLE oImage AS System.Drawing.Image NO-UNDO.
DEFINE VARIABLE myImage AS System.Drawing.Image NO-UNDO.
DEFINE VARIABLE oImageShape AS Infragistics.Excel.WorksheetImage NO-UNDO.
DEFINE VARIABLE lcImage AS LONGCHAR NO-UNDO.
DEFINE VARIABLE iError AS INTEGER NO-UNDO.
DEFINE VARIABLE iCnt AS INTEGER NO-UNDO.
DEFINE VARIABLE oCellFormat1 AS CLASS Infragistics.Excel.IWorksheetCellFormat NO-UNDO.
DEFINE VARIABLE oCellFormat2 AS CLASS Infragistics.Excel.IWorksheetCellFormat NO-UNDO.
DEFINE VARIABLE oCellFormat3 AS CLASS Infragistics.Excel.IWorksheetCellFormat NO-UNDO.
DEFINE VARIABLE oCellFormat4 AS CLASS Infragistics.Excel.IWorksheetCellFormat NO-UNDO.
DEFINE VARIABLE oWorksheetCell AS CLASS Infragistics.Excel.WorksheetCell NO-UNDO.
DEFINE VARIABLE oWorksheetRow AS CLASS Infragistics.Excel.WorksheetRow NO-UNDO.
DEFINE VARIABLE oWorksheetColumn AS CLASS Infragistics.Excel.WorksheetColumn NO-UNDO.
ASSIGN
iColumn = 1
iRow = 0
oWorksheetColumn = oWorksheet:COLUMNS:Item[iColumn]
.
/* setup a blank column that will give us space for our border */
oWorksheet:COLUMNS:Item[0]:Width = 1 * 256.
/* setup our first column that will have the totals description */
oWorksheetColumn:WIDTH = 64 * 256.
/* The following font aligns things correctly for display as well as print and also solved the bold issue */
oWorksheetColumn:CellFormat:Font:Name = "Courier New".
/* let's set the FormatString for the column as we have performance issues when doing for each cell */
oWorksheetColumn:CellFormat:FormatString ="@".
/* create our cellformat we will use for borders */
oCellFormat1 = oWorkbook:CreateNewWorksheetCellFormat().
oCellFormat1:LeftBorderStyle = CellBorderLineStyle:Double
oCellFormat1:RightBorderStyle = CellBorderLineStyle:DOUBLE.
oCellFormat2 = oWorkbook:CreateNewWorksheetCellFormat().
oCellFormat2:LeftBorderStyle = CellBorderLineStyle:Double
oCellFormat2:RightBorderStyle = CellBorderLineStyle:DOUBLE
oCellformat2:TopBorderStyle = CellBorderLineStyle:DOUBLE.
oCellFormat3 = oWorkbook:CreateNewWorksheetCellFormat().
oCellFormat3:LeftBorderStyle = CellBorderLineStyle:Double
oCellFormat3:RightBorderStyle = CellBorderLineStyle:DOUBLE
oCellformat3:BottomBorderStyle = CellBorderLineStyle:DOUBLE.
oCellFormat4 = oWorkbook:CreateNewWorksheetCellFormat().
oCellFormat4:LeftBorderStyle = CellBorderLineStyle:Double
oCellFormat4:RightBorderStyle = CellBorderLineStyle:DOUBLE
oCellformat4:BottomBorderStyle = CellBorderLineStyle:DOUBLE
FOR EACH ttblReceiptLines:
IF LENGTH(ttblReceiptLines.SigData) GT 0 AND ttblReceiptLines.SigData NE ? THEN
DO:
ASSIGN iRow = iRow + 1.
COPY-LOB ttblReceiptLines.SigData TO lcImage NO-ERROR.
IF lcImage NE ? AND LENGTH (lcImage) GT 0 THEN
ASSIGN myImage = ClientSuper:Base64ToImage(lcImage) NO-ERROR.
IF VALID-OBJECT (myImage) THEN
ASSIGN oImage = NEW System.Drawing.Bitmap (myImage) NO-ERROR.
IF VALID-OBJECT (oImage) THEN
oWorksheet:Rows:Item[iRow]:Height = oImage:HEIGHT * 3.
oImageShape = NEW Infragistics.Excel.WorksheetImage (oImage).
oImageShape:TopLeftCornerCell = oWorksheet:Rows:Item[iRow]:Cells:Item[iColumn].
oImageShape:TopLeftCornerPosition = NEW PointF(0.5, 0.5).
oImageShape:BottomRightCornerCell = oWorksheet:Rows:Item[iRow]:Cells:Item[iColumn].
oImageShape:BottomRightCornerPosition = NEW PointF (99.5, 99.5).
oImageshape:PositioningMode = Infragistics.Excel.ShapePositioningMode:MoveAndSizeWithCells.
oWorksheet:Shapes:Add(oImageShape).
END.
/* we cannot dispose or we will get an error when attempting to create the workbook */
/* oImage:Dispose().*/
DELETE OBJECT oImage NO-ERROR.
myImage:Dispose().
DELETE OBJECT myImage NO-ERROR.
DELETE OBJECT oImageShape NO-ERROR.
ELSE
iRow = iRow +1
oWorksheetRow = oWorksheet:Rows:Item[iRow]
oWorksheetCell = oWorksheetRow:Cells:Item[iColumn]
oWorksheetCell:VALUE = ttblReceiptLines.PrintLine.
IF ttblReceiptLines.PrintBold THEN /* this will cause the lines not to line up as bold takes more space */
oWorksheetCell:CellFormat:Font:Bold = Infragistics.Excel.ExcelDefaultableBoolean:TRUE.
IF ttblReceiptLines.PrintRGBColor NE 0 THEN
oWorksheetCell:CellFormat:Font:Color = System.Drawing.Color:FromArgb(ttblReceiptLines.PrintRGBColor).
IF ttblReceiptLines.CellBorder EQ 3 THEN /* end of receipt */
oWorksheetCell = oWorksheet:Rows:Item[iRow]:Cells:Item[iColumn].
oWorksheetCell:CellFormat:FillPatternForegroundColor = System.Drawing.Color:LightGray.
oWorksheetCell:CellFormat:FillPattern = Infragistics.Excel.FillPatternStyle:Solid.
iRow = iRow +1.
CATCH e AS Progress.Lang.Error:
DO iError = 1 TO e:NumMessages:
MESSAGE e:GetMessage(iError)
VIEW-AS ALERT-BOX.
DELETE OBJECT e NO-ERROR.
END CATCH.
FINALLY:
DELETE OBJECT oCellFormat1 NO-ERROR.
DELETE OBJECT oCellFormat2 NO-ERROR.
DELETE OBJECT oCellFormat3 NO-ERROR.
DELETE OBJECT oCellFormat4 NO-ERROR.
END FINALLY.
Is the caller holding onto oWorksheet or the owning Workbook instance? If that is being rooted anywhere, all the data, formatting, and images in the workbook will remain in memory even after the workbook is saved to a file. All references to the Workbook and its sub-objects must be released for the GC to cleanup the memory they use.
oWorksheet as well as oWorkbook is being deleted/released when the report ends. That is what has me stumped is why the memory is not released when these objects are deleted/released.
I can do that but will probably be next week. I will let you know what I find.
Thanks.
Well something is being rooted, either by our code or yours. Is it possible to run your application through a memory profile and inspect the rooting paths for the Workbook instance after creating the file?