Bug 55814 - Comments are not cloned properly by XSSFWorkbook.cloneSheet(...)
Summary: Comments are not cloned properly by XSSFWorkbook.cloneSheet(...)
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.14-FINAL
Hardware: All All
: P2 minor with 5 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on: 52425 56017
  Show dependency tree
Reported: 2013-11-22 23:30 UTC by Corey Teffetalor
Modified: 2017-11-04 13:46 UTC (History)
1 user (show)

Example XLSX, Java, and Outputs (19.17 KB, application/x-zip-compressed)
2013-11-22 23:30 UTC, Corey Teffetalor

Note You need to log in before you can comment on or make changes to this bug.
Description Corey Teffetalor 2013-11-22 23:30:10 UTC
Created attachment 31068 [details]
Example XLSX, Java, and Outputs

When cloning an XSSFWorksheet with the cloneSheet method, the cloned sheet does not display the comments associated with the original sheet:

This appears to be due to <legacyDrawing r:id="..."/> not appearing in the <worksheet /> element.

XSSFWorksheet.getCTWorksheet().setLegacyDrawing(old XSSFWorksheet.getCTWorksheet().getLegacyDrawing());

Appears to work around or resolve the issue with comments not appearing.

I suspect there is a further problem where after cloneSheet(...) is called any modification of comments for both sheets will become linked. The relationships from the sheet XML point toward the same comment XML document, instead of a cloned copy of that document.

Example Code (Included in Zip)

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class POICloneComments {
	public static void main(String[] args) {
		try {
			XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream("comment.xlsx"));

			int oldsheetIndex = wb.getSheetIndex("example");
			XSSFSheet oldsheet = wb.getSheetAt(oldsheetIndex);
			XSSFSheet newsheet = wb.cloneSheet(oldsheetIndex);

			wb.write(new FileOutputStream("outnocomment.xlsx"));

			wb = new XSSFWorkbook(new FileInputStream("comment.xlsx"));

			oldsheetIndex = wb.getSheetIndex("example");
			oldsheet = wb.getSheetAt(oldsheetIndex);
			newsheet = wb.cloneSheet(oldsheetIndex);

			// This fixes the comment going missing

			wb.write(new FileOutputStream("outhascomment.xlsx"));
		} catch (IOException e) {
Comment 1 Fabian Fischer 2015-03-03 12:26:40 UTC
I can confirm the bug (poi-3.11, poi-3.12-beta1). Unfortonatly the workaround "newsheet.getCTWorksheet().setLegacyDrawing(oldsheet.getCTWorksheet().getLegacyDrawing());" does not work for me. When using the workaround, the resulting excel-document is corrupted.
Comment 2 Fabian Fischer 2016-06-22 13:19:22 UTC
The problem still exists in poi-3.14-FINAL.
The workaround now works for me.
Comment 3 Dominik Stadler 2017-11-04 13:46:14 UTC
This seems to have been fixed at some point, I could not reproduce this any more, cloning sheets seems to correctly clone comments as well now. Bug #52425 and especially bug #56017 sound related.

Added some unit-test that tries to reproduce the problem via r1814290.