improved work with cell comments in XSSF, also added support for cell comments to SS interfaces

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@884918 13f79535-47bb-0310-9956-ffa450edef68

8 files changed, 514 insertions, 118 deletions

diff --git a/src/ooxml/java/org/apache/poi/xssf/model/CommentsTable.java b/src/ooxml/java/org/apache/poi/xssf/model/CommentsTable.java
index 6caec6e734..da47d90a2c 100644
--- a/src/ooxml/java/org/apache/poi/xssf/model/CommentsTable.java
+++ b/src/ooxml/java/org/apache/poi/xssf/model/CommentsTable.java
@@ -24,7 +24,6 @@ import org.apache.poi.ss.util.CellReference;
 import org.apache.poi.xssf.usermodel.XSSFComment;
 import org.apache.poi.POIXMLDocumentPart;
 import org.apache.xmlbeans.XmlException;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTAuthors;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCommentList;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComments;
@@ -33,11 +32,13 @@ import org.apache.poi.openxml4j.opc.PackagePart;
 import org.apache.poi.openxml4j.opc.PackageRelationship;
 
 public class CommentsTable extends POIXMLDocumentPart {
-    protected CTComments comments;
+    private CTComments comments;
 
     public CommentsTable() {
         super();
         comments = CTComments.Factory.newInstance();
+        comments.addNewCommentList();
+        comments.addNewAuthors().addAuthor("");
     }
 
     public CommentsTable(PackagePart part, PackageRelationship rel) throws IOException {
@@ -70,62 +71,60 @@ public class CommentsTable extends POIXMLDocumentPart {
     public int getNumberOfComments() {
         return comments.getCommentList().sizeOfCommentArray();
     }
+
     public int getNumberOfAuthors() {
-        return getCommentsAuthors().sizeOfAuthorArray();
+        return comments.getAuthors().sizeOfAuthorArray();
     }
 
     public String getAuthor(long authorId) {
-        return getCommentsAuthors().getAuthorArray((int)authorId);
+        return comments.getAuthors().getAuthorArray((int)authorId);
     }
 
     public int findAuthor(String author) {
-        for (int i = 0 ; i < getCommentsAuthors().sizeOfAuthorArray() ; i++) {
-            if (getCommentsAuthors().getAuthorArray(i).equals(author)) {
+        for (int i = 0 ; i < comments.getAuthors().sizeOfAuthorArray() ; i++) {
+            if (comments.getAuthors().getAuthorArray(i).equals(author)) {
                 return i;
             }
         }
         return addNewAuthor(author);
     }
 
-    public XSSFComment findCellComment(int row, int column) {
-        return findCellComment(
-                (new CellReference(row, column)).formatAsString() );
+    public XSSFComment findCellComment(String cellRef) {
+        CTComment ct = getCTComment(cellRef);
+        return ct == null ? null : new XSSFComment(this, ct, null);
     }
 
-    public XSSFComment findCellComment(String cellRef) {
-        for (CTComment comment : getCommentsList().getCommentArray()) {
+    public CTComment getCTComment(String cellRef) {
+        for (CTComment comment : comments.getCommentList().getCommentArray()) {
             if (cellRef.equals(comment.getRef())) {
-                return new XSSFComment(this, comment);
+                return comment;
             }
         }
         return null;
     }
 
-    /**
-     * Generates a new XSSFComment, associated with the
-     *  current comments list.
-     */
-    public XSSFComment addComment() {
-        return new XSSFComment(this, getCommentsList().addNewComment());
-    }
-
-    private CTCommentList getCommentsList() {
-        if (comments.getCommentList() == null) {
-            comments.addNewCommentList();
-        }
-        return comments.getCommentList();
+    public CTComment newComment() {
+        CTComment ct = comments.getCommentList().addNewComment();
+        ct.setRef("A1");
+        ct.setAuthorId(0);
+        return ct;
     }
 
-    private CTAuthors getCommentsAuthors() {
-        if (comments.getAuthors() == null) {
-            comments.addNewAuthors();
+    public boolean removeComment(String cellRef) {
+        CTCommentList lst = comments.getCommentList();
+        if(lst != null) for(int i=0; i < lst.sizeOfCommentArray(); i++) {
+            CTComment comment = lst.getCommentArray(i);
+            if (cellRef.equals(comment.getRef())) {
+                lst.removeComment(i);
+                return true;
+            }
         }
-        return comments.getAuthors();
+        return false;
     }
 
     private int addNewAuthor(String author) {
-        int index = getCommentsAuthors().sizeOfAuthorArray();
-        getCommentsAuthors().insertAuthor(index, author);
+        int index = comments.getAuthors().sizeOfAuthorArray();
+        comments.getAuthors().insertAuthor(index, author);
         return index;
     }
 
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
index cacc057352..258415efa9 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
@@ -807,11 +807,29 @@ public final class XSSFCell implements Cell {
      * Assign a comment to this cell. If the supplied comment is null,
      * the comment for this cell will be removed.
      *
-     * @param comment comment associated with this cell
+     * @param comment the XSSFComment associated with this cell
      */
     public void setCellComment(Comment comment) {
-        String cellRef = new CellReference(_row.getRowNum(), getColumnIndex()).formatAsString();
-        getSheet().setCellComment(cellRef, (XSSFComment)comment);
+        if(comment == null) {
+            removeCellComment();
+            return;
+        }
+
+        comment.setRow(getRowIndex());
+        comment.setColumn(getColumnIndex());
+    }
+
+    /**
+     * Removes the comment for this cell, if there is one.
+    */
+    public void removeCellComment() {
+        XSSFComment comment = getCellComment();
+        if(comment != null){
+            String ref = _cell.getR();
+            XSSFSheet sh = getSheet();
+            sh.getCommentsTable(false).removeComment(ref);
+            sh.getVMLDrawing(false).removeCommentShape(getRowIndex(), getColumnIndex());
+        }
     }
 
     /**
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFClientAnchor.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFClientAnchor.java
index 7bf4057131..28efca9def 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFClientAnchor.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFClientAnchor.java
@@ -217,4 +217,8 @@ public final class XSSFClientAnchor extends XSSFAnchor implements ClientAnchor {
         return anchorType;
     }
 
+    public boolean isSet(){
+        return !(cell1.getCol() == 0 && cell2.getCol() == 0 &&
+                 cell1.getRow() == 0 && cell2.getRow() == 0);
+    }
 }
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFComment.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFComment.java
index 1868fc251d..a7e577d190 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFComment.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFComment.java
@@ -20,84 +20,149 @@ import org.apache.poi.ss.usermodel.Comment;
 import org.apache.poi.ss.usermodel.RichTextString;
 import org.apache.poi.ss.util.CellReference;
 import org.apache.poi.xssf.model.CommentsTable;
-import org.apache.poi.xssf.usermodel.helpers.RichTextStringHelper;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRst;
+import schemasMicrosoftComVml.CTShape;
+
+import java.math.BigInteger;
 
 public class XSSFComment implements Comment {
 	
-	private CTComment comment;
-	private CommentsTable comments;
+	private final CTComment _comment;
+	private final CommentsTable _comments;
+    private final CTShape _vmlShape;
+
+    /**
+     * cached reference to the string with the comment text
+     */
+    private XSSFRichTextString _str;
 
-	/**
+    /**
 	 * Creates a new XSSFComment, associated with a given
 	 *  low level comment object.
-	 * If, as an end user, you want a new XSSFComment
-	 *  object, the please ask your sheet for one.
 	 */
-	public XSSFComment(CommentsTable comments, CTComment comment) {
-		this.comment = comment;
-		this.comments = comments;
+	public XSSFComment(CommentsTable comments, CTComment comment, CTShape vmlShape) {
+		_comment = comment;
+		_comments = comments;
+        _vmlShape = vmlShape;
 	}
 
-	public String getAuthor() {
-		return comments.getAuthor((int)comment.getAuthorId());
+    /**
+     *
+     * @return Name of the original comment author. Default value is blank.
+     */
+    public String getAuthor() {
+		return _comments.getAuthor((int) _comment.getAuthorId());
 	}
 
+    /**
+     * Name of the original comment author. Default value is blank.
+     *
+     * @param author the name of the original author of the comment
+     */
+    public void setAuthor(String author) {
+        _comment.setAuthorId(
+                _comments.findAuthor(author)
+        );
+    }
+
+    /**
+     * @return the 0-based column of the cell that the comment is associated with.
+     */
 	public int getColumn() {
-		return (new CellReference(comment.getRef())).getCol();
+		return new CellReference(_comment.getRef()).getCol();
 	}
 
+    /**
+     * @return the 0-based row index of the cell that the comment is associated with.
+     */
 	public int getRow() {
-		return (new CellReference(comment.getRef())).getRow();
+		return new CellReference(_comment.getRef()).getRow();
 	}
 
-	public boolean isVisible() {
-		// TODO Auto-generated method stub
-		return true;
+    /**
+     * @return whether the comment is visible
+     */
+    public boolean isVisible() {
+        boolean visible = false;
+        if(_vmlShape != null){
+            String style = _vmlShape.getStyle();
+            visible = style != null && style.indexOf("visibility:visible") != -1;
+        }
+		return visible;
 	}
 
-	public void setAuthor(String author) {
-		comment.setAuthorId(
-				comments.findAuthor(author)
-		);
-	}
+    /**
+     * @param visible whether the comment is visible
+     */
+    public void setVisible(boolean visible) {
+        if(_vmlShape != null){
+            String style;
+            if(visible) style = "position:absolute;visibility:visible";
+            else style = "position:absolute;visibility:hidden";
+            _vmlShape.setStyle(style);
+        }
+    }
 
-	public void setColumn(short col) {
-		initializeRef();
-		String newRef = 
-			(new CellReference(getRow(), col)).formatAsString();
-		comment.setRef(newRef);
-	}
-
-	private void initializeRef() {
-		if (comment.getRef() == null) {
-			comment.setRef("A1");
-		}
+    /**
+     * Set the column of the cell that contains the comment
+     *
+     * @param col the 0-based column of the cell that contains the comment
+     */
+    public void setColumn(int col) {
+        CellReference ref = new CellReference(getRow(), col);
+		_comment.setRef(ref.formatAsString());
+        if(_vmlShape != null) _vmlShape.getClientDataArray(0).setColumnArray(0, new BigInteger(String.valueOf(col)));
 	}
 
+    /**
+     * Set the row of the cell that contains the comment
+     *
+     * @param row the 0-based row of the cell that contains the comment
+     */
 	public void setRow(int row) {
-		initializeRef();
 		String newRef =
 			(new CellReference(row, getColumn())).formatAsString();
-		comment.setRef(newRef);
-	}
+		_comment.setRef(newRef);
+        if(_vmlShape != null) _vmlShape.getClientDataArray(0).setRowArray(0, new BigInteger(String.valueOf(row)));
+    }
 	
-	public RichTextString getString() {
-		return RichTextStringHelper.convertFromRst(comment.getText());
+    /**
+     * @return the rich text string of the comment
+     */
+	public XSSFRichTextString getString() {
+		if(_str == null) {
+            CTRst rst = _comment.getText();
+            if(rst != null) _str = new XSSFRichTextString(_comment.getText());
+        }
+        return _str;
 	}
 
+    /**
+     * Sets the rich text string used by this comment.
+     *
+     * @param string  the XSSFRichTextString used by this object.
+     */
 	public void setString(RichTextString string) {
-		CTRst text = comment.addNewText();
-		RichTextStringHelper.convertToRst(string, text);
+        if(!(string instanceof XSSFRichTextString)){
+            throw new IllegalArgumentException("Only XSSFRichTextString argument is supported");
+        }
+        _str = (XSSFRichTextString)string;
+        _comment.setText(_str.getCTRst());
 	}
 	
 	public void setString(String string) {
-		RichTextString richTextString = new XSSFRichTextString(string);
-		setString(richTextString);
+		setString(new XSSFRichTextString(string));
 	}
 
-	public void setVisible(boolean visible) {
-		// TODO Auto-generated method stub
-	}
+    /**
+     * @return the xml bean holding this comment's properties
+     */
+    protected CTComment getCTComment(){
+        return _comment;
+    }
+
+    protected CTShape getCTShape(){
+        return _vmlShape;
+    }
 }
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDrawing.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDrawing.java
index fc38802a3e..230819a02f 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDrawing.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDrawing.java
@@ -25,6 +25,7 @@ import java.util.Map;
 import javax.xml.namespace.QName;
 
 import org.apache.poi.POIXMLDocumentPart;
+import org.apache.poi.xssf.model.CommentsTable;
 import org.apache.poi.openxml4j.opc.PackagePart;
 import org.apache.poi.openxml4j.opc.PackagePartName;
 import org.apache.poi.openxml4j.opc.PackageRelationship;
@@ -234,6 +235,35 @@ public final class XSSFDrawing extends POIXMLDocumentPart implements Drawing {
     }
 
     /**
+     * Creates a cell comment.
+     *
+     * @param anchor    the client anchor describes how this comment is attached
+     *                  to the sheet.
+     * @return  the newly created comment.
+     */
+    @Override
+    public XSSFComment createCellComment(ClientAnchor anchor)
+    {
+        XSSFClientAnchor ca = (XSSFClientAnchor)anchor;
+        XSSFSheet sheet = (XSSFSheet)getParent();
+
+        //create comments and vmlDrawing parts if they don't exist
+        CommentsTable comments = sheet.getCommentsTable(true);
+        XSSFVMLDrawing vml = sheet.getVMLDrawing(true);
+        schemasMicrosoftComVml.CTShape vmlShape = vml.newCommentShape();
+        if(ca.isSet()){
+            String position =
+                    ca.getCol1() + ", 0, " + ca.getRow1() + ", 0, " +
+                    ca.getCol2() + ", 0, " + ca.getRow2() + ", 0";
+            vmlShape.getClientDataArray(0).setAnchorArray(0, position);
+        }
+        XSSFComment shape = new XSSFComment(comments, comments.newComment(), vmlShape);
+        shape.setColumn(ca.getCol1());
+        shape.setRow(ca.getRow1());
+        return shape;
+    }
+
+    /**
      * Create and initialize a CTTwoCellAnchor that anchors a shape against top-left and bottom-right cells.
      *
      * @return a new CTTwoCellAnchor
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRelation.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRelation.java
index e617acbab6..d2ba007e3a 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRelation.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRelation.java
@@ -118,7 +118,7 @@ public final class XSSFRelation extends POIXMLRelation {
 			"application/vnd.openxmlformats-officedocument.vmlDrawing",
 			"http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing",
 			"/xl/drawings/vmlDrawing#.vml",
-			null
+			XSSFVMLDrawing.class
 	);
 
 	public static final XSSFRelation CUSTOM_XML_MAPPINGS = new XSSFRelation(
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
index 879f63357f..d0f0d13941 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
@@ -54,38 +54,7 @@ import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;
 import org.apache.xmlbeans.XmlException;
 import org.apache.xmlbeans.XmlOptions;
 import org.openxmlformats.schemas.officeDocument.x2006.relationships.STRelationshipId;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBreak;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCommentList;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDrawing;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTHeaderFooter;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTHyperlink;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCell;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCells;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTOutlinePr;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageBreak;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageMargins;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageSetUpPr;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPane;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPrintOptions;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSelection;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetData;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetFormatPr;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetPr;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetProtection;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetView;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetViews;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.STPane;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.STPaneState;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;
 
 /**
  * High level representation of a SpreadsheetML worksheet.
@@ -352,6 +321,48 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
     }
 
     /**
+     * Get VML drawing for this sheet (aka 'legacy' drawig)
+     *
+     * @param autoCreate if true, then a new VML drawing part is created
+     *
+     * @return the VML drawing of <code>null</code> if the drawing was not found and autoCreate=false
+     */
+    protected XSSFVMLDrawing getVMLDrawing(boolean autoCreate) {
+        XSSFVMLDrawing drawing = null;
+        CTLegacyDrawing ctDrawing = worksheet.getLegacyDrawing();
+        if(ctDrawing == null) {
+            if(autoCreate) {
+                //drawingNumber = #drawings.size() + 1
+                int drawingNumber = getPackagePart().getPackage().getPartsByContentType(XSSFRelation.VML_DRAWINGS.getContentType()).size() + 1;
+                drawing = (XSSFVMLDrawing)createRelationship(XSSFRelation.VML_DRAWINGS, XSSFFactory.getInstance(), drawingNumber);
+                String relId = drawing.getPackageRelationship().getId();
+
+                //add CTLegacyDrawing element which indicates that this sheet contains drawing components built on the drawingML platform.
+                //The relationship Id references the part containing the drawing definitions.
+                ctDrawing = worksheet.addNewLegacyDrawing();
+                ctDrawing.setId(relId);
+            }
+        } else {
+            //search the referenced drawing in the list of the sheet's relations
+            for(POIXMLDocumentPart p : getRelations()){
+                if(p instanceof XSSFVMLDrawing) {
+                    XSSFVMLDrawing dr = (XSSFVMLDrawing)p;
+                    String drId = dr.getPackageRelationship().getId();
+                    if(drId.equals(ctDrawing.getId())){
+                        drawing = dr;
+                        break;
+                    }
+                    break;
+                }
+            }
+            if(drawing == null){
+                logger.log(POILogger.ERROR, "Can't find VML drawing with id=" + ctDrawing.getId() + " in the list of the sheet's relationships");
+            }
+        }
+        return drawing;
+    }
+
+    /**
      * Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
      * @param colSplit	  Horizonatal position of split.
      * @param rowSplit	  Vertical position of split.
@@ -392,12 +403,14 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
     /**
      * Creates a new comment for this sheet. You still
      *  need to assign it to a cell though
+     *
+     * @deprecated since Nov 2009 this method is not compatible with the common SS interfaces,
+     * use {@link org.apache.poi.xssf.usermodel.XSSFDrawing#createCellComment
+     *  (org.apache.poi.ss.usermodel.ClientAnchor)} instead
      */
+    @Deprecated
     public XSSFComment createComment() {
-        if (sheetComments == null) {
-            sheetComments = (CommentsTable)createRelationship(XSSFRelation.SHEET_COMMENTS, XSSFFactory.getInstance(), (int)sheet.getSheetId());
-        }
-        return sheetComments.addComment();
+        return createDrawingPatriarch().createCellComment(new XSSFClientAnchor());
     }
 
     /**
@@ -438,7 +451,14 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
         if (sheetComments == null) {
             return null;
         }
-        return sheetComments.findCellComment(row, column);
+
+        String ref = new CellReference(row, column).formatAsString();
+        CTComment ctComment = sheetComments.getCTComment(ref);
+        if(ctComment == null) return null;
+
+        XSSFVMLDrawing vml = getVMLDrawing(false);
+        return new XSSFComment(sheetComments, ctComment,
+                vml == null ? null : vml.findCommentShape(row, column));
     }
 
     public XSSFHyperlink getHyperlink(int row, int column) {
@@ -2161,8 +2181,10 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
      *
      * @param cellRef cell region
      * @param comment the comment to assign
+     * @deprecated since Nov 2009 use {@link XSSFCell#setCellComment(org.apache.poi.ss.usermodel.Comment)} instead
      */
-    public void setCellComment(String cellRef, XSSFComment comment) {
+    @Deprecated
+    public static void setCellComment(String cellRef, XSSFComment comment) {
         CellReference cellReference = new CellReference(cellRef);
 
         comment.setRow(cellReference.getRow());
@@ -2234,8 +2256,13 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
     /**
      * Returns the sheet's comments object if there is one,
      *  or null if not
+     *
+     * @param create create a new comments table if it does not exist
      */
-    protected CommentsTable getCommentsTable() {
+    protected CommentsTable getCommentsTable(boolean create) {
+        if(sheetComments == null && create){
+            sheetComments = (CommentsTable)createRelationship(XSSFRelation.SHEET_COMMENTS, XSSFFactory.getInstance(), (int)sheet.getSheetId());
+        }
         return sheetComments;
     }
 
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java
new file mode 100644
index 0000000000..a27d657bce
--- /dev/null
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java
@@ -0,0 +1,253 @@
+/* ====================================================================
+   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.xssf.usermodel;
+
+import org.apache.poi.POIXMLDocumentPart;
+import org.apache.poi.openxml4j.opc.PackagePart;
+import org.apache.poi.openxml4j.opc.PackageRelationship;
+import org.apache.xmlbeans.XmlException;
+import org.apache.xmlbeans.XmlOptions;
+import org.apache.xmlbeans.XmlObject;
+import org.apache.xmlbeans.XmlCursor;
+import org.w3c.dom.Node;
+import schemasMicrosoftComOfficeOffice.*;
+
+import javax.xml.namespace.QName;
+import java.io.*;
+import java.util.*;
+import java.util.regex.Pattern;
+import java.util.regex.Matcher;
+import java.math.BigInteger;
+
+import schemasMicrosoftComVml.*;
+import schemasMicrosoftComVml.STTrueFalse;
+import schemasMicrosoftComOfficeExcel.CTClientData;
+import schemasMicrosoftComOfficeExcel.STObjectType;
+
+/**
+ * Represents a SpreadsheetML VML drawing.
+ *
+ * <p>
+ * In Excel 2007 VML drawings are used to describe properties of cell comments,
+ * although the spec says that VML is deprecated:
+ * </p>
+ * <p>
+ * The VML format is a legacy format originally introduced with Office 2000 and is included and fully defined
+ * in this Standard for backwards compatibility reasons. The DrawingML format is a newer and richer format
+ * created with the goal of eventually replacing any uses of VML in the Office Open XML formats. VML should be
+ * considered a deprecated format included in Office Open XML for legacy reasons only and new applications that
+ * need a file format for drawings are strongly encouraged to use preferentially DrawingML
+ * </p>
+ *
+ * See 6.4 VML - SpreadsheetML Drawing in Office Open XML Part 4 - Markup Language Reference.pdf
+ *
+ * @author Yegor Kozlov
+ */
+public final class XSSFVMLDrawing extends POIXMLDocumentPart {
+    private static final QName QNAME_SHAPE_LAYOUT = new QName("urn:schemas-microsoft-com:office:office", "shapelayout");
+    private static final QName QNAME_SHAPE_TYPE = new QName("urn:schemas-microsoft-com:vml", "shapetype");
+    private static final QName QNAME_SHAPE = new QName("urn:schemas-microsoft-com:vml", "shape");
+
+    /**
+     * regexp to parse shape ids, in VML they have weird form of id="_x0000_s1026"
+     */
+    private static final Pattern ptrn_shapeId = Pattern.compile("_x0000_s(\\d+)");
+
+    private List<QName> _qnames = new ArrayList<QName>();
+    private List<XmlObject> _items = new ArrayList<XmlObject>();
+    private String _shapeTypeId;
+    private int _shapeId = 1024;
+
+    /**
+     * Create a new SpreadsheetML drawing
+     *
+     * @see XSSFSheet#createDrawingPatriarch()
+     */
+    protected XSSFVMLDrawing() {
+        super();
+        newDrawing();
+    }
+
+    /**
+     * Construct a SpreadsheetML drawing from a package part
+     *
+     * @param part the package part holding the drawing data,
+     * the content type must be <code>application/vnd.openxmlformats-officedocument.drawing+xml</code>
+     * @param rel  the package relationship holding this drawing,
+     * the relationship type must be http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing
+     */
+    protected XSSFVMLDrawing(PackagePart part, PackageRelationship rel) throws IOException, XmlException {
+        super(part, rel);
+        read(getPackagePart().getInputStream());
+    }
+
+
+    protected void read(InputStream is) throws IOException, XmlException {
+        XmlObject root = XmlObject.Factory.parse(is);
+
+        _qnames = new ArrayList<QName>();
+        _items = new ArrayList<XmlObject>();
+        for(XmlObject obj : root.selectPath("$this/xml/*")) {
+            Node nd = obj.getDomNode();
+            QName qname = new QName(nd.getNamespaceURI(), nd.getLocalName());
+            if (qname.equals(QNAME_SHAPE_LAYOUT)) {
+                _items.add(CTShapeLayout.Factory.parse(obj.xmlText()));
+            } else if (qname.equals(QNAME_SHAPE_TYPE)) {
+                CTShapetype st = CTShapetype.Factory.parse(obj.xmlText());
+                _items.add(st);
+                _shapeTypeId = st.getId();
+            } else if (qname.equals(QNAME_SHAPE)) {
+                CTShape shape = CTShape.Factory.parse(obj.xmlText());
+                String id = shape.getId();
+                if(id != null) {
+                    Matcher m = ptrn_shapeId.matcher(id);
+                    if(m.find()) _shapeId = Math.max(_shapeId, Integer.parseInt(m.group(1)));
+                }
+                _items.add(shape);
+            } else {
+                _items.add(XmlObject.Factory.parse(obj.xmlText()));
+            }
+            _qnames.add(qname);
+        }
+    }
+
+    protected List<XmlObject> getItems(){
+        return _items;
+    }
+
+    protected void write(OutputStream out) throws IOException {
+        XmlObject rootObject = XmlObject.Factory.newInstance();
+        XmlCursor rootCursor = rootObject.newCursor();
+        rootCursor.toNextToken();
+        rootCursor.beginElement("xml");
+
+        for(int i=0; i < _items.size(); i++){
+            XmlCursor xc = _items.get(i).newCursor();
+            rootCursor.beginElement(_qnames.get(i));
+            while(xc.toNextToken() == XmlCursor.TokenType.ATTR) {
+                Node anode = xc.getDomNode();
+                rootCursor.insertAttributeWithValue(anode.getLocalName(), anode.getNamespaceURI(), anode.getNodeValue());
+            }
+            xc.toStartDoc();
+            xc.copyXmlContents(rootCursor);
+            rootCursor.toNextToken();
+            xc.dispose();
+        }
+        rootCursor.dispose();
+
+        XmlOptions xmlOptions = new XmlOptions(DEFAULT_XML_OPTIONS);
+        xmlOptions.setSavePrettyPrint();
+        HashMap<String, String> map = new HashMap<String, String>();
+        map.put("urn:schemas-microsoft-com:vml", "v");
+        map.put("urn:schemas-microsoft-com:office:office", "o");
+        map.put("urn:schemas-microsoft-com:office:excel", "x");
+        xmlOptions.setSaveSuggestedPrefixes(map);
+
+        rootObject.save(out, xmlOptions);
+    }
+
+    @Override
+    protected void commit() throws IOException {
+        PackagePart part = getPackagePart();
+        OutputStream out = part.getOutputStream();
+        write(out);
+        out.close();
+    }
+
+    /**
+     * Initialize a new Speadsheet VML drawing
+     */
+    private void newDrawing(){
+        CTShapeLayout layout = CTShapeLayout.Factory.newInstance();
+        layout.setExt(STExt.EDIT);
+        CTIdMap idmap = layout.addNewIdmap();
+        idmap.setExt(STExt.EDIT);
+        idmap.setData("1");
+        _items.add(layout);
+        _qnames.add(QNAME_SHAPE_LAYOUT);
+
+        CTShapetype shapetype = CTShapetype.Factory.newInstance();
+        _shapeTypeId = "_xssf_cell_comment";
+        shapetype.setId(_shapeTypeId);
+        shapetype.setCoordsize("21600,21600");
+        shapetype.setSpt(202);
+        shapetype.setPath2("m,l,21600r21600,l21600,xe");
+        shapetype.addNewStroke().setJoinstyle(STStrokeJoinStyle.MITER);
+        CTPath path = shapetype.addNewPath();
+        path.setGradientshapeok(STTrueFalse.T);
+        path.setConnecttype(STConnectType.RECT);
+        _items.add(shapetype);
+        _qnames.add(QNAME_SHAPE_TYPE);
+    }
+
+    protected CTShape newCommentShape(){
+        CTShape shape = CTShape.Factory.newInstance();
+        shape.setId("_x0000_s" + (++_shapeId));
+        shape.setType("#" + _shapeTypeId);
+        shape.setStyle("position:absolute; visibility:hidden");
+        shape.setFillcolor("#ffffe1");
+        shape.setInsetmode(STInsetMode.AUTO);
+        shape.addNewFill().setColor("#ffffe1");
+        CTShadow shadow = shape.addNewShadow();
+        shadow.setOn(STTrueFalse.T);
+        shadow.setColor("black");
+        shadow.setObscured(STTrueFalse.T);
+        shape.addNewPath().setConnecttype(STConnectType.NONE);
+        shape.addNewTextbox().setStyle("mso-direction-alt:auto");
+        CTClientData cldata = shape.addNewClientData();
+        cldata.setObjectType(STObjectType.NOTE);
+        cldata.addNewMoveWithCells();
+        cldata.addNewSizeWithCells();
+        cldata.addNewAnchor().setStringValue("1, 15, 0, 2, 3, 15, 3, 16");
+        cldata.addNewAutoFill().setStringValue("False");
+        cldata.addNewRow().setBigIntegerValue(new BigInteger("0"));
+        cldata.addNewColumn().setBigIntegerValue(new BigInteger("0"));
+        _items.add(shape);
+        _qnames.add(QNAME_SHAPE);
+        return shape;
+    }
+
+    /**
+     * Find a shape with ClientData of type "NOTE" and the specified row and column
+     *
+     * @return the comment shape or <code>null</code>
+     */
+    protected CTShape findCommentShape(int row, int col){
+        for(XmlObject itm : _items){
+            if(itm instanceof CTShape){
+                CTShape sh = (CTShape)itm;
+                if(sh.sizeOfClientDataArray() > 0){
+                    CTClientData cldata = sh.getClientDataArray(0);
+                    if(cldata.getObjectType() == STObjectType.NOTE){
+                        int crow = cldata.getRowArray(0).intValue();
+                        int ccol = cldata.getColumnArray(0).intValue();
+                        if(crow == row && ccol == col) {
+                            return sh;
+                        }
+                    }
+                }
+            }
+        }
+        return null;
+    }
+
+    protected boolean removeCommentShape(int row, int col){
+        CTShape shape = findCommentShape(row, col);
+        return shape != null && _items.remove(shape);
+    }
+}
\ No newline at end of file