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.
I ran the profiler and 98.3% of memory under number of large fragments is taken by Infragistics controls. Under Generation 2 77MB is help mostly be Infragistics. If I run my report again it will increase by 77MB each time.
What do you need specifically?
I think that should be sufficient. Anything that can show you rooting paths will work. You just need to find out what is holding onto the Workbook instance.
Mike,
Do you recommend a profiler? I was going to use ANTS from Redgate.
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?