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); }
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