--- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (revision 1702503) +++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (working copy) @@ -52,6 +52,7 @@ import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellRange; import org.apache.poi.ss.usermodel.CellStyle; +import org.apache.poi.ss.usermodel.Comment; import org.apache.poi.ss.usermodel.DataValidation; import org.apache.poi.ss.usermodel.DataValidationHelper; import org.apache.poi.ss.usermodel.Footer; @@ -60,6 +61,7 @@ import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.AreaReference; +import org.apache.poi.ss.util.CellAddress; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.ss.util.CellReference; @@ -3965,4 +3967,8 @@ } return col.getOutlineLevel(); } + + public Map getCellComments(){ + return this.sheetComments.getCellComments(); + } } --- src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (revision 1702503) +++ src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (working copy) @@ -21,6 +21,7 @@ import java.util.ArrayList; import java.util.Iterator; import java.util.List; +import java.util.Map; import java.util.TreeMap; import org.apache.poi.ddf.EscherRecord; @@ -56,9 +57,11 @@ import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellRange; import org.apache.poi.ss.usermodel.CellStyle; +import org.apache.poi.ss.usermodel.Comment; import org.apache.poi.ss.usermodel.DataValidation; import org.apache.poi.ss.usermodel.DataValidationHelper; import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.util.CellAddress; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.ss.util.CellReference; @@ -2194,6 +2197,33 @@ return null; } + public Map getCellComments() { + HSSFPatriarch patriarch = getDrawingPatriarch(); + if (null == patriarch) { + patriarch = createDrawingPatriarch(); + } + + + Map locations = new TreeMap(); + findCellCommentLocations(patriarch, locations); + return locations; + } + private void findCellCommentLocations(HSSFShapeContainer container, Map locations){ + for (Object object : container.getChildren()) { + HSSFShape shape = (HSSFShape) object; + if (shape instanceof HSSFShapeGroup) { + findCellCommentLocations((HSSFShapeGroup) shape, locations); + continue; + } + if (shape instanceof HSSFComment) { + HSSFComment comment = (HSSFComment) shape; + if (comment.hasPosition()) { + locations.put(new CellAddress(comment.getRow(), comment.getColumn()), comment); + } + } + } + } + public CellRangeAddress getRepeatingRows() { return getRepeatingRowsOrColums(true); --- src/ooxml/java/org/apache/poi/xssf/model/CommentsTable.java (revision 1702503) +++ src/ooxml/java/org/apache/poi/xssf/model/CommentsTable.java (working copy) @@ -21,10 +21,14 @@ import java.io.OutputStream; import java.util.HashMap; import java.util.Map; +import java.util.Map.Entry; +import java.util.TreeMap; import org.apache.poi.POIXMLDocumentPart; import org.apache.poi.openxml4j.opc.PackagePart; import org.apache.poi.openxml4j.opc.PackageRelationship; +import org.apache.poi.ss.usermodel.Comment; +import org.apache.poi.ss.util.CellAddress; import org.apache.poi.xssf.usermodel.XSSFComment; import org.apache.xmlbeans.XmlException; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment; @@ -117,15 +121,32 @@ @SuppressWarnings("deprecation") //YK: getXYZArray() array accessors are deprecated in xmlbeans with JDK 1.5 support public CTComment getCTComment(String cellRef) { // Create the cache if needed + prepareCTCommentCache(); + + // Return the comment, or null if not known + return commentRefs.get(cellRef); + } + + public Map getCellComments(){ + prepareCTCommentCache(); + final TreeMap map = new TreeMap(); + + for(final Entry e: commentRefs.entrySet()){ + map.put(CellAddress.fromString(e.getKey()), new XSSFComment(this, e.getValue(), null)); + } + + return map; + } + + @SuppressWarnings("deprecation") //YK: getXYZArray() array accessors are deprecated in xmlbeans with JDK 1.5 support + private void prepareCTCommentCache() { + // Create the cache if needed if(commentRefs == null) { commentRefs = new HashMap(); for (CTComment comment : comments.getCommentList().getCommentArray()) { commentRefs.put(comment.getRef(), comment); } } - - // Return the comment, or null if not known - return commentRefs.get(cellRef); } /** --- src/java/org/apache/poi/ss/usermodel/Sheet.java (revision 1702503) +++ src/java/org/apache/poi/ss/usermodel/Sheet.java (working copy) @@ -19,8 +19,10 @@ import java.util.Iterator; import java.util.List; +import java.util.Map; import org.apache.poi.hssf.util.PaneInformation; +import org.apache.poi.ss.util.CellAddress; import org.apache.poi.ss.util.CellRangeAddress; /** @@ -1067,4 +1069,11 @@ * you take it out of them. */ int getColumnOutlineLevel(int columnIndex); + + + /** + * Returns all cell comments of this sheet. + * @return A map of CellAdress objects and their associated comments. + */ + Map getCellComments(); } --- src/java/org/apache/poi/ss/util/CellAddress.java (nonexistent) +++ src/java/org/apache/poi/ss/util/CellAddress.java (working copy) @@ -0,0 +1,101 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.ss.util; + +import java.util.Locale; + +/** + * This class is a container for POI usermodel row=0 column=0 cell references. + * It is barely a container for these two coordinates. The implementation + * of the Comparable interface sorts by "natural" order top left to bottom right. + */ +public class CellAddress implements Comparable{ + private final int _row; + private final int _col; + + /** + * Create a new CellAddress object. + * + * @param row Row index (first row is 0) + * @param column Column index (first column is 0) + */ + public CellAddress(int row, int column) { + super(); + this._row = row; + this._col = column; + } + + public int getRow() { + return _row; + } + + public int getColumn() { + return _col; + } + + public int compareTo(CellAddress o) { + int r = this._row-o._row; + if (r!=0) return r; + + r = this._col-o._col; + if (r!=0) return r; + + return 0; + } + + public boolean equals(Object o) { + if (this == o) { + return true; + } + if(!(o instanceof CellAddress)) { + return false; + } + + CellAddress cr = (CellAddress) o; + return _row == cr._row + && _col == cr._col + ; + } + + public int hashCode() { + return this._row + this._col<<16; + } + + public static CellAddress fromString(String reference) { + int length = reference.length(); + + int loc = 0; + // step over column name chars until first digit for row number. + for (; loc < length; loc++) { + char ch = reference.charAt(loc); + if (Character.isDigit(ch)) { + break; + } + } + + String sCol = reference.substring(0,loc).toUpperCase(Locale.ROOT); + String sRow = reference.substring(loc); + + + return new CellAddress(Integer.parseInt(sRow)-1, CellReference.convertColStringToIndex(sCol)); + } + + public String toString() { + return CellReference.convertNumToColString(this._col)+(this._row+1); + } +} --- src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java (revision 1702503) +++ src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java (working copy) @@ -41,6 +41,7 @@ import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.SheetConditionalFormatting; import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.util.CellAddress; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.SheetUtil; import org.apache.poi.xssf.usermodel.XSSFDataValidation; @@ -1539,4 +1540,8 @@ public int getColumnOutlineLevel(int columnIndex) { return _sh.getColumnOutlineLevel(columnIndex); } + + public Map getCellComments() { + return _sh.getCellComments(); + } }