Bug 52764 - Unmodified cell comments disappear after HSSFWorkbook.write
Summary: Unmodified cell comments disappear after HSSFWorkbook.write
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.8-dev
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks: 53010
  Show dependency tree
 
Reported: 2012-02-25 01:11 UTC by Laszlo Kustra
Modified: 2012-08-19 20:52 UTC (History)
0 users



Attachments
Test program to reproduce bug. (2.79 KB, application/octet-stream)
2012-02-25 01:11 UTC, Laszlo Kustra
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Laszlo Kustra 2012-02-25 01:11:42 UTC
Created attachment 28383 [details]
Test program to reproduce bug.

Steps to reproduce bug (see attached test program):
 1. load an existing .xls file that has comments on multiple cells into a HSSFWorkbook
 2. delete the comment from one of the cells and create a new comment on the same cell. Do not touch any of the other cells containing comments.
 3. write the HSSFWorkbook instance to a file.
 4. the resulting file will only contain a single comment on the cell that we modified. Any other previously existing comments disappeared into thin air.

The test program...
 1. creates A1_B1_commented.xls in the current directory. In this file there is a single sheet with comments on the A1 and B1 cells.
 2. loads the created file again and creates a new comment for A1, then writes the HSSFWorkbook to B1_comment_lost.xls
 3. as the name suggests, the comment for B1 is gone (can be checked in LibreOffice or Apache POI), though nobody ever touched it.

Tested on with the latest svn release (poi-3.8-beta6-20120225).
Comment 1 Nick Burch 2012-02-25 05:56:25 UTC
How are you creating the additional comments? Does it by any chance include a call to createDrawingPatriarch?

If so, you should note the warning in the javadocs - 
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#createDrawingPatriarch() - "This will have the effect of removing any existing drawings on this sheet."

(Comments are a kind of drawing)
Comment 2 Laszlo Kustra 2012-02-25 14:12:59 UTC
(In reply to comment #1)
> How are you creating the additional comments? Does it by any chance include a
> call to createDrawingPatriarch?
> 
> If so, you should note the warning in the javadocs - 
> http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#createDrawingPatriarch()
> - "This will have the effect of removing any existing drawings on this sheet."
> 
> (Comments are a kind of drawing)

Please have a look at the attached test program to see the answer for yourself.
I only call the createDrawingPatriarch() method once, when creating the sheet.
The second time, after loading the file from the disk to modify cell comments, I call the getDrawingPatriarch() method to get the existing instance.
Comment 3 Yegor Kozlov 2012-02-27 08:49:28 UTC
It is a known limitation of POI: if a template workbook has graphics (shapes, comments, pictures, etc.) then an attempt to modify / append new graphic elements *may* destroy the original drawing. The behavior is not fully deterministic: in simple cases graphics is preserved OK, it more complex it is lost.

In other words, POI is good to create new drawings from scratch, but it can't append / modify existing drawings.

There is a plan to improve it, but unfortunately it is a quite a big piece of work and no one has volunteered to implement it.


Yegor
Comment 4 Evgeniy Berlog 2012-08-15 20:28:58 UTC
In your code you try to create two comments for one cell. Thus, Excel shows warning "The data may have been lost". Now POI checks all the comments for each sheet and throws an exception if find two or more comments for single cell.

Regards, Evgeniy