Bug 60894 - When using low memory SXSSFWorkBook still uses high memory usage for cell comments
Summary: When using low memory SXSSFWorkBook still uses high memory usage for cell com...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SXSSF (show other bugs)
Version: 3.15-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-03-21 09:00 UTC by Paul Taylor
Modified: 2021-12-21 14:45 UTC (History)
0 users



Attachments
window 10 download for pc full version (103.28 KB, image/png)
2019-07-27 20:05 UTC, Munna
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Paul Taylor 2017-03-21 09:00:31 UTC
My application creates a spreadsheet, this can be rather large (upto 500,000 rows, each row spread over six sheets, each with about 20 columns).

I use Apache Poi and moved to using SXSSFWorkBook as writes data to temporary files so that memory used is not proportional to size of the final spreadsheet and this works wells.

But the spreadsheet represents metadata changed, and when metadata has changed I want to add a comment to the cell showing the old value. I got this working for small spreadsheets but it always fails with heap memory errors when attempted for large files.

It seems all Comments are stored in memory rather than stored in temporary files like the rows themselves.
 
In the code below Worksheet is my own wrapper class, I only create one DrawingPatriach class per sheet, but it looks like I have to create an anchor and RichTextString  for each comment I need.

private void addCellComment(Row r, Cell c, Worksheet sheet, String value)
{
    String formattedValue    =  value.replace('\u0000', '\n');
    int    rowCount         =  value.split("\\\\u000").length;
    ClientAnchor anchor = factory.createClientAnchor();
    anchor.setCol1(c.getColumnIndex());
    anchor.setCol2(c.getColumnIndex()+2);
    anchor.setRow1(r.getRowNum());
    anchor.setRow2(r.getRowNum()+rowCount);

    Drawing drawing = sheet.getDrawing();
    Comment comment = drawing.createCellComment(anchor);
    RichTextString str = factory.createRichTextString(formattedValue);
    comment.setString(str);
    c.setCellComment(comment);
}
Comment 1 PJ Fanning 2021-12-21 14:45:45 UTC
SXSSF workbook has no optimisation for comments - they are stored in memory - all comments for the workbook.

I have an issue and work-in-progress PR in a separate project - https://github.com/pjfanning/poi-shared-strings/issues/18