POI 3.17 在克隆工作表中创建单元格注释会导致 xlsx 不一致 [英] POI 3.17 creating Cell Comments in a cloned sheet creates inconsistent xlsx
问题描述
我使用了 cloneSheet 方法来复制同一个工作簿中已经包含注释的工作表.之后将新评论添加到此新工作表并保存 excel.
当用 Excel 365 打开文件时，它抱怨 /xl/comments1.xml 并恢复了文件.新创建的评论可用.在恢复过程中删除了来自克隆的注释.
打开 zip 文件并查看 /xl/comments1.xml，它显示出不同.
这是 cloneSheet 方法的问题还是微软使用了新方法?
虽然apache poi 项目已经很成熟了，但还没有完成.因此，需要使用它的人必须了解所用文件系统的内部结构.
那么注释是如何存储在 Excel 的 Office Open XML (*.xlsx) 文件系统中的?
整个文件系统是一个 ZIP 档案.第一张工作表的工作表数据位于该 ZIP 内的 /xl/worksheets/sheet1.xml 中.那里的 XML 有
它指向在第一个工作表的关系部分 XML 中具有 rId2 的旧 VMLDrawing.
第一张表的关系部分XML是/xl/worksheets/_rels/sheet1.xml.rels，看起来像
<关系><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"/>...</关系>所以 rId2 指向 /xl/drawings/vmlDrawing1.vml 而 rId3 指向 /xl/comments1.xml.
因此 vmlDrawing1.vml 包含表单上注释形状的锚点，而comments1.xml"包含注释内容.
现在什么是方法 public XSSFSheet cloneSheet(int sheetNum, String newName) of XSSFWorkbook 在做什么?
首先，它复制所有工作表的关系.因此，与 VMLDrawing 和 Comments 部分的关系也被复制.因此，如果我们克隆第一个工作表，那么克隆后 /xl/worksheets/_rels/sheet2.xml.rels 将具有与 /xl/worksheets/_rels/sheet1.xml 相同的内容.rels 有.
但随后它指出:尚不支持克隆带有注释的工作表."并从工作表的 XML 中删除
因此，我们有一个克隆的工作表，没有在工作表中链接的评论，但与评论及其形状集有关系.
如果我们现在在克隆的工作表中创建新的注释，那么也会创建新的 /xl/drawings/vmlDrawing2.vml，包括它在 /xl/worksheets/_rels/sheet2 中的关系.xml.rels.所以在那之后我们有一个 /xl/worksheets/_rels/sheet2.xml.rels 指向 /xl/drawings/vmlDrawing1.vml 和 /xl/drawings/vmlDrawing2.vml 也是如此.但这是不允许的，所以 Excel 在打开时抛出错误并建议修复.
此外，新创建的注释存储在 /xl/comments1.xml 中，这也是错误的，因为每个工作表都需要自己的注释部分.发生这种情况是因为在克隆 XSSFSheet 时，字段 private CommentsTable sheetComments 也被克隆并现在包含源表的旧注释表.
所以为了能够在克隆的工作表中创建评论，我们需要摆脱错误的关系，并摆脱sheetComments字段中错误的CommentsTable代码>XSSFSheet.
示例:
import org.apache.poi.ss.usermodel.*;导入 org.apache.poi.xssf.usermodel.*;导入 org.apache.poi.POIXMLDocumentPart;导入 org.apache.poi.POIXMLDocumentPart.RelationPart；导入 java.io.FileInputStream;导入 java.io.FileOutputStream;导入 java.lang.reflect.Field;类 ExcelCloneSheetHavingComments {public static void main(String[] args) 抛出异常 {工作簿工作簿 = WorkbookFactory.create(new FileInputStream("ExcelHavingComments.xlsx"));工作表 sheetClone = workbook.cloneSheet(0);workbook.setSheetName(workbook.getSheetIndex(sheetClone), "克隆第一张表");if (sheetClone instanceof XSSFSheet) {XSSFSheet xssfSheet = (XSSFSheet)sheetClone;//去掉错误的关系对于 (POIXMLDocumentPart.RelationPart 关系部分:xssfSheet.getRelationParts()) {if (relationPart.getDocumentPart() instanceof org.apache.poi.xssf.usermodel.XSSFVMLDrawing||关系部分.getDocumentPart() instanceof org.apache.poi.xssf.model.CommentsTable) {关系部分.getRelationship().getSource().removeRelationship(relationPart.getRelationship().getId());}}//去掉错误的 org.apache.poi.xssf.model.CommentsTableField sheetComments = XSSFSheet.class.getDeclaredField("sheetComments");sheetComments.setAccessible(true);sheetComments.set(xssfSheet, null);}绘图绘图 = sheetClone.createDrawingPatriarch();评论 comment = drawing.createCellComment(drawing.createAnchor(0, 0, 0, 0, 2, 1, 4, 4));comment.setString(new XSSFRichTextString("克隆工作表中单元格 C2 中的注释"));workbook.write(new FileOutputStream("CopyOfExcelHavingComments.xlsx"));workbook.close();}}这不会从源表中复制注释.但当然，现在也可以使用 Sheet.getCellComments 在源工作表中，然后 Drawing.createCellComment 在克隆工作表中.
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.
Is this an issue with the cloneSheet method or is Microsoft using new ways?
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.
这篇关于POI 3.17 在克隆工作表中创建单元格注释会导致 xlsx 不一致的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
