Search code examples
apache-poicomments

POI 3.17 creating Cell Comments in a cloned sheet creates inconsistent xlsx

I used the cloneSheet method to copy a sheet within the same workbook which already contains comments. Afterwards new comments where added to this new sheet and the excel saved.

When openening the file with Excel 365, it complained about /xl/comments1.xml and recovered the file. The newly created comments are available. The comments from the clone are removed during the recovery.

Opening the zip file and looking at the /xl/comments1.xml, it shows a difference.

Last entry is the newly created comment

Is this an issue with the cloneSheet method or is Microsoft using new ways?

Solution

  • As mature the apache poi project even is, it is long not finished yet. So one who needs using it must nevertheless know about the internals of the used file systems.

    So how are comments stored in Excel's Office Open XML (*.xlsx) file system?

    The whole file system is a ZIP archive. The sheet data of first sheet is in /xl/worksheets/sheet1.xml within that ZIP. The XML there has

    ...
<legacyDrawing r:id="rId2"/>
...

    which points to a legacy VMLDrawing having rId2 in the relation parts XML of the first sheet.

    The relation parts XML of the first sheet is /xl/worksheets/_rels/sheet1.xml.rels and looks like

    <Relationships>
 <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments" Target="../comments1.xml"/>
 <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing" Target="../drawings/vmlDrawing1.vml"/>
...
</Relationships>

    So rId2 points to /xl/drawings/vmlDrawing1.vml and rId3 points to /xl/comments1.xml.

    So the vmlDrawing1.vml contains the anchors of the comments' shapes on the sheet while the 'comments1.xml` contains the comments' contents.

    Now what is method public XSSFSheet cloneSheet(int sheetNum, String newName) of XSSFWorkbook doing?

    At first, it copies all sheet's relations. So also the relations to the VMLDrawing and the Comments parts are copied. So if we are cloning first sheet, then after cloning /xl/worksheets/_rels/sheet2.xml.rels will have the same content as /xl/worksheets/_rels/sheet1.xml.rels has.

    But then it states: "Cloning sheets with comments is not yet supported." and removes the <legacyDrawing r:id="rId2"/> from the sheet's XML. But the previous copied relations are not removed.

    So as the result we have a cloned sheet without comments linked in the sheet but having relations to the comments and their shapes set.

    If we now creating new comments in that cloned sheet, then new /xl/drawings/vmlDrawing2.vml also is created inclusive its relation in /xl/worksheets/_rels/sheet2.xml.rels. So after that we have a /xl/worksheets/_rels/sheet2.xml.rels which points to /xl/drawings/vmlDrawing1.vml and to /xl/drawings/vmlDrawing2.vml as well. But that's not allowed and so Excel throws error while opening and suggests repairing.

    Furthermore the new created comments are stored in /xl/comments1.xml which also is wrong because each sheet needs its own comments part. That happens because while cloning theXSSFSheet the field private CommentsTable sheetComments also is cloned and contains the old comments table of the source sheet now.

    So for being able creating comments in the cloned sheet, we need get rid of the wrong relations and also get rid of the wrong CommentsTable in field sheetComments of the XSSFSheet.

    Example:

    import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.POIXMLDocumentPart.RelationPart;

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

import java.lang.reflect.Field;

class ExcelCloneSheetHavingComments {

 public static void main(String[] args) throws Exception {

  Workbook workbook = WorkbookFactory.create(new FileInputStream("ExcelHavingComments.xlsx"));

  Sheet sheetClone = workbook.cloneSheet(0);
  workbook.setSheetName(workbook.getSheetIndex(sheetClone), "Cloned first Sheet");

  if (sheetClone instanceof XSSFSheet) {
   XSSFSheet xssfSheet = (XSSFSheet)sheetClone;

   // get rid of the wrong relations
   for (POIXMLDocumentPart.RelationPart relationPart : xssfSheet.getRelationParts()) {
    if (relationPart.getDocumentPart() instanceof org.apache.poi.xssf.usermodel.XSSFVMLDrawing
     || relationPart.getDocumentPart() instanceof org.apache.poi.xssf.model.CommentsTable) {
     relationPart.getRelationship().getSource().removeRelationship(relationPart.getRelationship().getId());
    } 
   }

   // get rid of the wrong org.apache.poi.xssf.model.CommentsTable
   Field sheetComments = XSSFSheet.class.getDeclaredField("sheetComments"); 
   sheetComments.setAccessible(true); 
   sheetComments.set(xssfSheet, null);
  }  


  Drawing drawing = sheetClone.createDrawingPatriarch();
  Comment comment = drawing.createCellComment(drawing.createAnchor(0, 0, 0, 0, 2, 1, 4, 4));
  comment.setString(new XSSFRichTextString("Comment in Cell C2 in cloned sheet"));


  workbook.write(new FileOutputStream("CopyOfExcelHavingComments.xlsx"));
  workbook.close();

 }
}

    This does not copying the comments from source sheet. But of course this also wil be possible now using Sheet.getCellComments in source sheet and then Drawing.createCellComment in cloned sheet.