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.
Mike,
Do you recommend a profiler? I was going to use ANTS from Redgate.
There is probably some sort of instance list in the profiler. Find the instance of Workbook in that list which is using a large amount of memory and see what the rooting path is for that instance. That will tell you what is holding a reference to the Workbook instance.
It looks like Progress.ClrBridge. I have attached a zip that contains a pdf.
Yes, it looks like there is a type named Progess.ClrBridge.ProMarshal which contains a dictionary called _handlemap that is holding onto the cell formats from our Excel library and keeping them (and therefore all data related to the Workbook) in memory. It looks like that type is created by OpenEdge, which is what you're using I'm guessing. I could be wrong, but at this time, it seems like an issue with OpenEdge and not with the Excel library. You should try raising this issue with the OpenEdge support team.
Yes I am using openEdge and have opened a call with them. I have sent them a reproducible project for them to review.
Thanks for taking the time to look at this.
You're welcome. Let me know if there's anything else you need from my end.