public void TestBug56835CellComment()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
// first comment works
IClientAnchor anchor = new XSSFClientAnchor(1, 1, 2, 2, 3, 3, 4, 4);
XSSFComment comment = Drawing.CreateCellComment(anchor) as XSSFComment;
Assert.IsNotNull(comment);
try
{
Drawing.CreateCellComment(anchor);
Assert.Fail("Should fail if we try to add the same comment for the same cell");
}
catch (ArgumentException e)
{
// expected
}
}
finally
{
wb.Close();
}
}
public void SetString()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = (XSSFSheet)wb.CreateSheet();
XSSFComment comment = (XSSFComment)sh.CreateDrawingPatriarch().CreateCellComment(new XSSFClientAnchor());
//passing HSSFRichTextString is incorrect
try
{
comment.String = (new HSSFRichTextString(TEST_RICHTEXTSTRING));
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("Only XSSFRichTextString argument is supported", e.Message);
}
//simple string argument
comment.SetString(TEST_RICHTEXTSTRING);
Assert.AreEqual(TEST_RICHTEXTSTRING, comment.String.String);
//if the text is already Set, it should be overridden, not Added twice!
comment.SetString(TEST_RICHTEXTSTRING);
CT_Comment ctComment = comment.GetCTComment();
// Assert.Fail("TODO test case incomplete!?");
//XmlObject[] obj = ctComment.selectPath(
// "declare namespace w='"+XSSFRelation.NS_SPREADSHEETML+"' .//w:text");
//Assert.AreEqual(1, obj.Length);
Assert.AreEqual(TEST_RICHTEXTSTRING, comment.String.String);
//sequential call of comment.String should return the same XSSFRichTextString object
Assert.AreSame(comment.String, comment.String);
XSSFRichTextString richText = new XSSFRichTextString(TEST_RICHTEXTSTRING);
XSSFFont font1 = (XSSFFont)wb.CreateFont();
font1.FontName = ("Tahoma");
font1.FontHeight = 8.5;
font1.IsItalic = true;
font1.Color = IndexedColors.BlueGrey.Index;
richText.ApplyFont(0, 5, font1);
//check the low-level stuff
comment.String = richText;
//obj = ctComment.selectPath(
// "declare namespace w='"+XSSFRelation.NS_SPREADSHEETML+"' .//w:text");
//Assert.AreEqual(1, obj.Length);
Assert.AreSame(comment.String, richText);
//check that the rich text is Set in the comment
CT_RPrElt rPr = richText.GetCTRst().GetRArray(0).rPr;
Assert.AreEqual(true, rPr.GetIArray(0).val);
Assert.AreEqual(8.5, rPr.GetSzArray(0).val);
Assert.AreEqual(IndexedColors.BlueGrey.Index, (short)rPr.GetColorArray(0).indexed);
Assert.AreEqual("Tahoma", rPr.GetRFontArray(0).val);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public override bool Equals(Object obj)
{
if (!(obj is XSSFComment))
{
return(false);
}
XSSFComment other = (XSSFComment)obj;
return((GetCTComment() == other.GetCTComment()) &&
(GetCTShape() == other.GetCTShape()));
}
public void Bug51158()
{
// create a workbook
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = wb1.CreateSheet("Test Sheet") as XSSFSheet;
XSSFRow row = sheet.CreateRow(2) as XSSFRow;
XSSFCell cell = row.CreateCell(3) as XSSFCell;
cell.SetCellValue("test1");
//XSSFCreationHelper helper = workbook.GetCreationHelper();
//cell.Hyperlink=(/*setter*/helper.CreateHyperlink(0));
XSSFComment comment = (sheet.CreateDrawingPatriarch() as XSSFDrawing).CreateCellComment(new XSSFClientAnchor()) as XSSFComment;
Assert.IsNotNull(comment);
comment.SetString("some comment");
// ICellStyle cs = workbook.CreateCellStyle();
// cs.ShrinkToFit=(/*setter*/false);
// row.CreateCell(0).CellStyle=(/*setter*/cs);
// write the first excel file
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
Assert.IsNotNull(wb2);
sheet = wb2.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
Assert.IsNull(row.GetCell(4));
// add a new cell to the sheet
cell = row.CreateCell(4) as XSSFCell;
cell.SetCellValue("test2");
// write the second excel file
XSSFWorkbook wb3 = XSSFTestDataSamples.WriteOutAndReadBack(wb2) as XSSFWorkbook;
Assert.IsNotNull(wb3);
sheet = wb3.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
Assert.AreEqual("test2", row.GetCell(4).StringCellValue);
wb3.Close();
wb2.Close();
wb1.Close();
}
public void TestAuthor()
{
CommentsTable sheetComments = new CommentsTable();
CT_Comment ctComment = sheetComments.CreateComment();
Assert.AreEqual(1, sheetComments.GetNumberOfAuthors());
XSSFComment comment = new XSSFComment(sheetComments, ctComment, null);
Assert.AreEqual("", comment.Author);
comment.Author = ("Apache POI");
Assert.AreEqual("Apache POI", comment.Author);
Assert.AreEqual(2, sheetComments.GetNumberOfAuthors());
comment.Author = ("Apache POI");
Assert.AreEqual(2, sheetComments.GetNumberOfAuthors());
comment.Author = ("");
Assert.AreEqual("", comment.Author);
Assert.AreEqual(2, sheetComments.GetNumberOfAuthors());
}
public void TestGetSetCol()
{
CommentsTable sheetComments = new CommentsTable();
XSSFVMLDrawing vml = new XSSFVMLDrawing();
CT_Comment ctComment = sheetComments.CreateComment();
CT_Shape vmlShape = vml.newCommentShape();
XSSFComment comment = new XSSFComment(sheetComments, ctComment, vmlShape);
comment.Column = (1);
Assert.AreEqual(1, comment.Column);
Assert.AreEqual(1, new CellReference(ctComment.@ref).Col);
Assert.AreEqual(1, vmlShape.GetClientDataArray(0).GetColumnArray(0));
comment.Column = (5);
Assert.AreEqual(5, comment.Column);
Assert.AreEqual(5, new CellReference(ctComment.@ref).Col);
Assert.AreEqual(5, vmlShape.GetClientDataArray(0).GetColumnArray(0));
}
public void TestConstructor()
{
CommentsTable sheetComments = new CommentsTable();
Assert.IsNotNull(sheetComments.GetCTComments().commentList);
Assert.IsNotNull(sheetComments.GetCTComments().authors);
Assert.AreEqual(1, sheetComments.GetCTComments().authors.SizeOfAuthorArray());
Assert.AreEqual(1, sheetComments.GetNumberOfAuthors());
CT_Comment ctComment = sheetComments.CreateComment();
CT_Shape vmlShape = new CT_Shape();
XSSFComment comment = new XSSFComment(sheetComments, ctComment, vmlShape);
Assert.AreEqual(null, comment.String.String);
Assert.AreEqual(0, comment.Row);
Assert.AreEqual(0, comment.Column);
Assert.AreEqual("", comment.Author);
Assert.AreEqual(false, comment.Visible);
}
public void Author()
{
CommentsTable sheetComments = new CommentsTable();
CT_Comment ctComment = sheetComments.NewComment(CellAddress.A1);
Assert.AreEqual(1, sheetComments.GetNumberOfAuthors());
XSSFComment comment = new XSSFComment(sheetComments, ctComment, null);
Assert.AreEqual("", comment.Author);
comment.Author = ("Apache POI");
Assert.AreEqual("Apache POI", comment.Author);
Assert.AreEqual(2, sheetComments.GetNumberOfAuthors());
comment.Author = ("Apache POI");
Assert.AreEqual(2, sheetComments.GetNumberOfAuthors());
comment.Author = ("");
Assert.AreEqual("", comment.Author);
Assert.AreEqual(2, sheetComments.GetNumberOfAuthors());
}
public void GetSetRow()
{
CommentsTable sheetComments = new CommentsTable();
XSSFVMLDrawing vml = new XSSFVMLDrawing();
CT_Comment ctComment = sheetComments.NewComment(CellAddress.A1);
CT_Shape vmlShape = vml.newCommentShape();
XSSFComment comment = new XSSFComment(sheetComments, ctComment, vmlShape);
comment.Row = (1);
Assert.AreEqual(1, comment.Row);
Assert.AreEqual(1, new CellReference(ctComment.@ref).Row);
Assert.AreEqual(1, vmlShape.GetClientDataArray(0).GetRowArray(0));
comment.Row = (5);
Assert.AreEqual(5, comment.Row);
Assert.AreEqual(5, new CellReference(ctComment.@ref).Row);
Assert.AreEqual(5, vmlShape.GetClientDataArray(0).GetRowArray(0));
}
public void TestGetSetCol()
{
CommentsTable sheetComments = new CommentsTable();
XSSFVMLDrawing vml = new XSSFVMLDrawing();
CT_Comment ctComment = sheetComments.CreateComment();
CT_Shape vmlShape = vml.newCommentShape();
XSSFComment comment = new XSSFComment(sheetComments, ctComment, vmlShape);
comment.Column = (1);
Assert.AreEqual(1, comment.Column);
Assert.AreEqual(1, new CellReference(ctComment.@ref).Col);
Assert.AreEqual(1, vmlShape.GetClientDataArray(0).GetColumnArray(0));
comment.Column = (5);
Assert.AreEqual(5, comment.Column);
Assert.AreEqual(5, new CellReference(ctComment.@ref).Col);
Assert.AreEqual(5, vmlShape.GetClientDataArray(0).GetColumnArray(0));
}
public void Constructor()
{
CommentsTable sheetComments = new CommentsTable();
Assert.IsNotNull(sheetComments.GetCTComments().commentList);
Assert.IsNotNull(sheetComments.GetCTComments().authors);
Assert.AreEqual(1, sheetComments.GetCTComments().authors.SizeOfAuthorArray());
Assert.AreEqual(1, sheetComments.GetNumberOfAuthors());
CT_Comment ctComment = sheetComments.NewComment(CellAddress.A1);
CT_Shape vmlShape = new CT_Shape();
XSSFComment comment = new XSSFComment(sheetComments, ctComment, vmlShape);
Assert.AreEqual(null, comment.String.String);
Assert.AreEqual(0, comment.Row);
Assert.AreEqual(0, comment.Column);
Assert.AreEqual("", comment.Author);
Assert.AreEqual(false, comment.Visible);
}
/**
* Creates a comment.
* @param anchor the client anchor describes how this comment is attached
* to the sheet.
* @return the newly Created comment.
*/
public IComment CreateCellComment(IClientAnchor 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);
NPOI.OpenXmlFormats.Vml.CT_Shape vmlShape = vml.newCommentShape();
if (ca.IsSet())
{
String position =
ca.Col1 + ", 0, " + ca.Row1 + ", 0, " +
ca.Col2 + ", 0, " + ca.Row2 + ", 0";
vmlShape.GetClientDataArray(0).SetAnchorArray(0, position);
}
String ref1 = new CellReference(ca.Row1, ca.Col1).FormatAsString();
XSSFComment shape = new XSSFComment(comments, comments.NewComment(ref1), vmlShape);
return(shape);
}
public void TestBug58175()
{
IWorkbook wb = new SXSSFWorkbook();
try
{
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(1);
ICell cell = row.CreateCell(3);
cell.SetCellValue("F4");
ICreationHelper factory = wb.GetCreationHelper();
// When the comment box is visible, have it show in a 1x3 space
IClientAnchor anchor = factory.CreateClientAnchor();
anchor.Col1 = (cell.ColumnIndex);
anchor.Col2 = (cell.ColumnIndex + 1);
anchor.Row1 = (row.RowNum);
anchor.Row2 = (row.RowNum + 3);
XSSFClientAnchor ca = (XSSFClientAnchor)anchor;
// create comments and vmlDrawing parts if they don't exist
CommentsTable comments = (((SXSSFWorkbook)wb).XssfWorkbook
.GetSheetAt(0) as XSSFSheet).GetCommentsTable(true);
XSSFVMLDrawing vml = (((SXSSFWorkbook)wb).XssfWorkbook
.GetSheetAt(0) as XSSFSheet).GetVMLDrawing(true);
CT_Shape vmlShape1 = vml.newCommentShape();
if (ca.IsSet())
{
String position = ca.Col1 + ", 0, " + ca.Row1
+ ", 0, " + ca.Col2 + ", 0, " + ca.Row2
+ ", 0";
vmlShape1.GetClientDataArray(0).SetAnchorArray(0, position);
}
// create the comment in two different ways and verify that there is no difference
XSSFComment shape1 = new XSSFComment(comments, comments.NewComment(CellAddress.A1), vmlShape1);
shape1.Column = (ca.Col1);
shape1.Row = (ca.Row1);
CT_Shape vmlShape2 = vml.newCommentShape();
if (ca.IsSet())
{
String position = ca.Col1 + ", 0, " + ca.Row1
+ ", 0, " + ca.Col2 + ", 0, " + ca.Row2
+ ", 0";
vmlShape2.GetClientDataArray(0).SetAnchorArray(0, position);
}
CellAddress ref1 = new CellAddress(ca.Row1, ca.Col1);
XSSFComment shape2 = new XSSFComment(comments, comments.NewComment(ref1), vmlShape2);
Assert.AreEqual(shape1.Author, shape2.Author);
Assert.AreEqual(shape1.ClientAnchor, shape2.ClientAnchor);
Assert.AreEqual(shape1.Column, shape2.Column);
Assert.AreEqual(shape1.Row, shape2.Row);
Assert.AreEqual(shape1.GetCTComment().ToString(), shape2.GetCTComment().ToString());
Assert.AreEqual(shape1.GetCTComment().@ref, shape2.GetCTComment().@ref);
/*CommentsTable table1 = shape1.CommentsTable;
* CommentsTable table2 = shape2.CommentsTable;
* Assert.AreEqual(table1.CTComments.toString(), table2.CTComments.toString());
* Assert.AreEqual(table1.NumberOfComments, table2.NumberOfComments);
* Assert.AreEqual(table1.Relations, table2.Relations);*/
Assert.AreEqual(vmlShape1.ToString().Replace("_x0000_s\\d+", "_x0000_s0000"),
vmlShape2.ToString().Replace("_x0000_s\\d+", "_x0000_s0000"),
"The vmlShapes should have equal content afterwards");
}
finally
{
wb.Close();
}
}
public void GetSetRow()
{
CommentsTable sheetComments = new CommentsTable();
XSSFVMLDrawing vml = new XSSFVMLDrawing();
CT_Comment ctComment = sheetComments.NewComment("A1");
CT_Shape vmlShape = vml.newCommentShape();
XSSFComment comment = new XSSFComment(sheetComments, ctComment, vmlShape);
comment.Row = (1);
Assert.AreEqual(1, comment.Row);
Assert.AreEqual(1, new CellReference(ctComment.@ref).Row);
Assert.AreEqual(1, vmlShape.GetClientDataArray(0).GetRowArray(0));
comment.Row = (5);
Assert.AreEqual(5, comment.Row);
Assert.AreEqual(5, new CellReference(ctComment.@ref).Row);
Assert.AreEqual(5, vmlShape.GetClientDataArray(0).GetRowArray(0));
}
/**
* Creates a comment.
* @param anchor the client anchor describes how this comment is attached
* to the sheet.
* @return the newly Created comment.
*/
public IComment CreateCellComment(IClientAnchor 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);
NPOI.OpenXmlFormats.Vml.CT_Shape vmlShape = vml.newCommentShape();
if (ca.IsSet())
{
String position =
ca.Col1 + ", 0, " + ca.Row1 + ", 0, " +
ca.Col2 + ", 0, " + ca.Row2 + ", 0";
vmlShape.GetClientDataArray(0).SetAnchorArray(0, position);
}
XSSFComment shape = new XSSFComment(comments, comments.CreateComment(), vmlShape);
shape.Column = (ca.Col1);
shape.Row = (ca.Row1);
return shape;
}
public void TestCommentShowsBox()
{
XSSFWorkbook wb = new XSSFWorkbook();
wb.CreateSheet();
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
XSSFCell cell = (XSSFCell)sheet.CreateRow(0).CreateCell(0);
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFCreationHelper factory = (XSSFCreationHelper)wb.GetCreationHelper();
XSSFClientAnchor anchor = (XSSFClientAnchor)factory.CreateClientAnchor();
anchor.Col1 = cell.ColumnIndex;
anchor.Col2 = cell.ColumnIndex + 3;
anchor.Row1 = cell.RowIndex;
anchor.Row2 = cell.RowIndex + 5;
XSSFComment comment = (XSSFComment)drawing.CreateCellComment(anchor);
XSSFRichTextString str = (XSSFRichTextString)factory.CreateRichTextString("this is a comment");
comment.String = str;
cell.CellComment = comment;
XSSFVMLDrawing vml = sheet.GetVMLDrawing(false);
CT_Shapetype shapetype = null;
ArrayList items = vml.GetItems();
foreach (object o in items)
{
if (o is CT_Shapetype)
{
shapetype = (CT_Shapetype)o;
}
}
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.stroked);
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.filled);
using (MemoryStream ws = new MemoryStream())
{
wb.Write(ws);
using (MemoryStream rs = new MemoryStream(ws.GetBuffer()))
{
wb = new XSSFWorkbook(rs);
sheet = (XSSFSheet)wb.GetSheetAt(0);
vml = sheet.GetVMLDrawing(false);
shapetype = null;
items = vml.GetItems();
foreach (object o in items)
{
if (o is CT_Shapetype)
{
shapetype = (CT_Shapetype)o;
}
}
//wb.Write(new FileStream("comments.xlsx", FileMode.Create));
//using (MemoryStream ws2 = new MemoryStream())
//{
// vml.Write(ws2);
// throw new System.Exception(System.Text.Encoding.UTF8.GetString(ws2.GetBuffer()));
//}
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.stroked);
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.filled);
}
}
}
/**
* Assign a cell comment to a cell region in this worksheet
*
* @param cellRef cell region
* @param comment the comment to assign
* @deprecated since Nov 2009 use {@link XSSFCell#SetCellComment(NPOI.SS.usermodel.Comment)} instead
*/
public static void SetCellComment(String cellRef, XSSFComment comment)
{
CellReference cellReference = new CellReference(cellRef);
comment.Row = (cellReference.Row);
comment.Column = (cellReference.Col);
}
