While working in for loops I noticed after profiling the code that the section of code which seems to REALLY slow things down is: shiftCellsDown in SheetUtil. This method moves a block of cells down manually. I tried to replace it with POI's sheet.shiftRows(rowStart, rowEnd, numRows, true,false); which partially works (but has some issues for unknown reasons). The POI call is infinitely faster than the manual method of moving cells and would boost the performance greatly inside JETT for loops. Could I have some help to determine a more performant way to get this running faster? Also how does one get added to the developers of this project, i'd like a shot if possible?
Another big speed improvement i found is by commenting out in SheetUtils.shiftForBlock() the line calling: copyColumnWidthsRight(sheet, toShift.getLeftColNum(), toShift.getRightColNum(), translateRight);
For reports using copyRight, i get speed improvements for larger data running 5 minutes instead of > 2 hours. I suggest adding a new optional flag in baselooptag to control if column resizing should be called or not: copyColumnWidthsRight=true and default it to false.
Another especially slow performing issue seems to be when using richtext properties (like setting a font color for an expression in a cell with multi expressions). We had a report take 300 seconds, and just by removing the richtext formatting of one expression setting font to green, cut the time down to 12 seconds. WE discovered this because we had two reports that did nearly the same thing but one had some font coloring in a cell.
Last edit: Mark Vejvoda 2014-06-10
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
When I first started creating JETT, I tried Sheet#shiftRows for shifting content up/down also. I remember running into some issues; things didn't work quite right. I remember giving up and instead creating shiftCellsDown and shiftCellsUp to do it myself. They would still be necessary in those cases when there is content to the left or right of the block that shouldn't be shifted at all.
I like your suggestion of adding an attribute in BaseLoopTag optionally to turn off copying column widths right. Especially when attempting to copy column widths right in a vertical repeating block, this has been problematic.
I've placed everything rich-text-string-related in RichTextStringUtil. Unfortunately, a lot of JETT's effort in determining where formatting runs begin and end lies with "walking" the rich text string. I would take an educated guess and say that the "walking" would be a critical loop. I did find a getLengthOfFormattingRun method in XSSFRichTextString, but no corresponding method in HSSFRichTextString. At some point I'll try to use that method at least for .xlsx templates to see if that speeds up rich text operations.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
While working in for loops I noticed after profiling the code that the section of code which seems to REALLY slow things down is: shiftCellsDown in SheetUtil. This method moves a block of cells down manually. I tried to replace it with POI's sheet.shiftRows(rowStart, rowEnd, numRows, true,false); which partially works (but has some issues for unknown reasons). The POI call is infinitely faster than the manual method of moving cells and would boost the performance greatly inside JETT for loops. Could I have some help to determine a more performant way to get this running faster? Also how does one get added to the developers of this project, i'd like a shot if possible?
Another big speed improvement i found is by commenting out in SheetUtils.shiftForBlock() the line calling: copyColumnWidthsRight(sheet, toShift.getLeftColNum(), toShift.getRightColNum(), translateRight);
For reports using copyRight, i get speed improvements for larger data running 5 minutes instead of > 2 hours. I suggest adding a new optional flag in baselooptag to control if column resizing should be called or not: copyColumnWidthsRight=true and default it to false.
Another especially slow performing issue seems to be when using richtext properties (like setting a font color for an expression in a cell with multi expressions). We had a report take 300 seconds, and just by removing the richtext formatting of one expression setting font to green, cut the time down to 12 seconds. WE discovered this because we had two reports that did nearly the same thing but one had some font coloring in a cell.
Last edit: Mark Vejvoda 2014-06-10
When I first started creating JETT, I tried
Sheet#shiftRowsfor shifting content up/down also. I remember running into some issues; things didn't work quite right. I remember giving up and instead creatingshiftCellsDownandshiftCellsUpto do it myself. They would still be necessary in those cases when there is content to the left or right of the block that shouldn't be shifted at all.I like your suggestion of adding an attribute in
BaseLoopTagoptionally to turn off copying column widths right. Especially when attempting to copy column widths right in a vertical repeating block, this has been problematic.I've placed everything rich-text-string-related in
RichTextStringUtil. Unfortunately, a lot of JETT's effort in determining where formatting runs begin and end lies with "walking" the rich text string. I would take an educated guess and say that the "walking" would be a critical loop. I did find agetLengthOfFormattingRunmethod inXSSFRichTextString, but no corresponding method inHSSFRichTextString. At some point I'll try to use that method at least for .xlsx templates to see if that speeds up rich text operations.