Created attachment 33418 [details] template for process File created by MS Excel 2007. I can't to read comment at A1. System.out.println(wb.getSheetAt(0).getCellComment(0, 0)); => null System.out.println(wb.getSheetAt(0).getRow(0).getCell(0).getCellComment()); =>null Sometimes, after edit and resave the file - cell comment read correctly.
I tried the following using POI 3.14 beta 1 InputStream fis = FileInputStream("bug58818.xls"); Workbook wb = HSSFWorkbook(fis); fis.close(); Sheet sh = wb.getSheetAt(0); sh.getCellComments() -> returns empty Map sh.getRow(0).getCell(0).getCellComment(); -> returns null sh.getCellComment(0, 0); -> returns null Opening the file in Excel 2013, I found a comment in cell A1 with the text `jx:area(lastCell = "C25")\n`. I was able to reproduce your problem and this is a valid bug. The problem is that the comment doesn't have a position (HSSFComment.hasPosition returns false). I modified HSSFSheet.findCellCommentLocations to print comment.getString().getString() on each HSSFComment it finds in the drawing patriarch, and then POI found the cell comment. Disabling the check for hasPosition() will throw a NPE at comment.getRow() or comment.getColumn(). private void findCellCommentLocations(HSSFShapeContainer container, Map<CellAddress, HSSFComment> locations) { for (Object object : container.getChildren()) { HSSFShape shape = (HSSFShape) object; if (shape instanceof HSSFComment) { // recursively search down the drawing patriarch tree findCellCommentLocations((HSSFShapeGroup) shape, locations); continue; } if (shape instanceof HSSFComment) { HSSFComment comment = (HSSFComment) shape; + System.out.println(comment.getString().getString()); if (comment.hasPosition()) { locations.put(new CellAddress(comment.getRow(), comment.getColumn()), comment); } + else { + // found unanchored comment. How should this be handled? + // does comment contain any information that might be relevant here? + locations.put(CellAddress.A1, comment); + } HSSFComment.hasPosition() is false here because the HSSFComment object (created with the constructor HSSFComment(EscherContainerRecord, ObjectRecord, TextObjectRecord, NoteRecord)) sets NoteRecord as null. hasPosition and getRow/getColumn rely on this NoteRecord to identify the location. There are two likely explanations here: * data for creating a NoteRecord exists, but the NoteRecord object is not passed to the constructor of HSSFComment * the Excel file does not have a NoteRecord for some cell comments. When this is the case, Excel uses A1 as the default, implied location of the comment. For behavior parity, POI would need to default cell comments to A1 if no position is specified. HSSFComment(EscherContainerRecord spContainer, ObjRecord objRecord, TextObjectRecord textObjectRecord, NoteRecord _note) EscherContainerRecord spContainer org.apache.poi.ddf.EscherContainerRecord (SpContainer): isContainer: true version: 0x000F instance: 0x0000 recordId: 0xF004 numchildren: 5 children: Child 0: org.apache.poi.ddf.EscherSpRecord: RecordId: 0xF00A Version: 0x0002 ShapeType: 0x00CA ShapeId: 1027 Flags: HAVEANCHOR|HASSHAPETYPE (0x00000A00) Child 1: org.apache.poi.ddf.EscherOptRecord: isContainer: false version: 0x0003 instance: 0x000E recordId: 0xF00B numchildren: 0 properties: propNum: 128, RAW: 0x0080, propName: text.textid, complex: false, blipId: false, value: 62207824 (0x03B53750) propNum: 133, RAW: 0x0085, propName: text.wraptext, complex: false, blipId: false, value: 1 (0x00000001) propNum: 139, RAW: 0x008B, propName: text.bidir, complex: false, blipId: false, value: 2 (0x00000002) propNum: 191, RAW: 0x00BF, propName: text.sizetexttofitshape, complex: false, blipId: false, value: 655368 (0x000A0008) propNum: 344, RAW: 0x0158, propName: unknown, complex: false, blipId: false, value: 0 (0x00000000) propNum: 385, RAW: 0x0181, propName: fill.fillcolor, complex: false, blipId: false, value: 14811135 (0x00E1FFFF) propNum: 387, RAW: 0x0183, propName: fill.fillbackcolor, complex: false, blipId: false, value: 14811135 (0x00E1FFFF) propNum: 389, RAW: 0x0185, propName: fill.crmod, complex: false, blipId: false, value: 268435700 (0x100000F4) propNum: 447, RAW: 0x01BF, propName: fill.nofillhittest, complex: false, blipId: false, value: 1048592 (0x00100010) propNum: 451, RAW: 0x01C3, propName: linestyle.crmod, complex: false, blipId: false, value: 268435700 (0x100000F4) propNum: 513, RAW: 0x0201, propName: shadowstyle.color, complex: false, blipId: false, value: 0 (0x00000000) propNum: 515, RAW: 0x0203, propName: shadowstyle.crmod, complex: false, blipId: false, value: 268435700 (0x100000F4) propNum: 575, RAW: 0x023F, propName: shadowstyle.shadowobscured, complex: false, blipId: false, value: 196611 (0x00030003) propNum: 959, RAW: 0x03BF, propName: groupshape.print, complex: false, blipId: false, value: 131072 (0x00020000) Child 2: org.apache.poi.ddf.EscherClientAnchorRecord: RecordId: 0xF010 Version: 0x0000 Instance: 0x0000 Flag: 3 Col1: 1 DX1: 169 Row1: 0 DY1: 30 Col2: 2 DX2: 832 Row2: 4 DY2: 196 Extra Data: No Data Child 3: org.apache.poi.ddf.EscherClientDataRecord: RecordId: 0xF011 Version: 0x0000 Instance: 0x0000 Extra Data: No Data Child 4: org.apache.poi.ddf.EscherTextboxRecord: isContainer: false version: 0x0000 instance: 0x0000 recordId: 0xF00D numchildren: 0 ObjectRecord [OBJ] SUBRECORD: [ftCmo] .objectType = 0x0019 (25 ) .objectId = 0x00000003 (3 ) .option = 0x4011 (16401 ) .locked = true .printable = true .autofill = false .autoline = true .reserved1 = 0x03B53750 (62207824 ) .reserved2 = 0x032FCC60 (53464160 ) .reserved3 = 0x00000000 (0 ) [/ftCmo] SUBRECORD: [ftNts ] size = 22 reserved = [41, 68, 11, C0, EC, 21, 65, 46, 86, B6, E6, 6B, EF, 8E, 10, 2B, 00, 00, 10, 00, 00, 00] [/ftNts ] SUBRECORD: [ftEnd] [/ftEnd] [/OBJ] TextObjectRecord [TXO] .options = 0x0212 .isHorizontal = 1 .isVertical = 1 .textLocked = true .textOrientation= 0x0000 .reserved4 = 0x0000 .reserved5 = 0x0000 .reserved6 = 0x0000 .textLength = 0x001A .reserved7 = 0x00000000 .string = jx:area(lastCell = "C25") .textrun = 6 .textrun = 24 [/TXO] NoteRecord null This is the stack trace that identifies how the HSSFComment was created: File "<stdin>", line 1, in <module> at org.apache.poi.hssf.usermodel.HSSFComment.<init>(HSSFComment.java:62) at org.apache.poi.hssf.usermodel.HSSFShapeFactory.createShapeTree(HSSFShapeFactory.java:108) at org.apache.poi.hssf.usermodel.HSSFPatriarch.buildShapeTree(HSSFPatriarch.java:540) at org.apache.poi.hssf.usermodel.HSSFPatriarch.<init>(HSSFPatriarch.java:87) at org.apache.poi.hssf.usermodel.HSSFSheet.getPatriarch(HSSFSheet.java:1989) at org.apache.poi.hssf.usermodel.HSSFSheet.getDrawingPatriarch(HSSFSheet.java:1941) at org.apache.poi.hssf.usermodel.HSSFSheet.getCellComments(HSSFSheet.java:2348) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) In HSSFShapeFactory.java line 108: The _note argument to the HSSFComment constructor is null, which equals agg.getNoteRecordByObj(objRecord)). Either getNoteRecordByObj is misbehaving or there are duplicates in the `for (EscherRecord record : container.getChildRecords())` that are screwing up the lookup. Savchenko, do you have any files where POI doesn't recognized a cell comment NOT located at A1?
Created attachment 33508 [details] fix EscherAggregate.createAggregate It seems like the the break statement in createAggregate [2] leaves some records unread. In attachment 33418 [details], the NoteRecord has object id 3, and is skipped by the break statement. Removing the break statement fixes the described error (HSSFSheet.getCellComments) but fails a unit test [1]. [1] src/testcases/org/apache/poi/hssf/model/TestDrawingAggregate.java [2] > // any NoteRecords that follow the drawing block must be aggregated and and saved in the tailRec collection > while (loc < records.size()) { > if (sid(records, loc) == NoteRecord.sid) { > NoteRecord r = (NoteRecord) records.get(loc); > agg.addTailRecord(r); > } > else { > break; > } > loc++; > }
Created attachment 33509 [details] failed junit
> Savchenko, do you have any files where POI doesn't recognized a cell comment > NOT located at A1? No, I have not. I found no problems with the other cells
Any news?