public void TestFindCommentShape()
{
XSSFVMLDrawing vml = new XSSFVMLDrawing();
vml.Read(POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream("vmlDrawing1.vml"));
CT_Shape sh_a1 = vml.FindCommentShape(0, 0);
Assert.IsNotNull(sh_a1);
Assert.AreEqual("_x0000_s1025", sh_a1.id);
CT_Shape sh_b1 = vml.FindCommentShape(0, 1);
Assert.IsNotNull(sh_b1);
Assert.AreEqual("_x0000_s1026", sh_b1.id);
CT_Shape sh_c1 = vml.FindCommentShape(0, 2);
Assert.IsNull(sh_c1);
CT_Shape sh_d1 = vml.newCommentShape();
Assert.AreEqual("_x0000_s1027", sh_d1.id);
sh_d1.GetClientDataArray(0).SetRowArray(0, 0);
sh_d1.GetClientDataArray(0).SetColumnArray(0, 3);
Assert.AreSame(sh_d1, vml.FindCommentShape(0, 3));
//newly created drawing
XSSFVMLDrawing newVml = new XSSFVMLDrawing();
Assert.IsNull(newVml.FindCommentShape(0, 0));
sh_a1 = newVml.newCommentShape();
Assert.AreEqual("_x0000_s1025", sh_a1.id);
sh_a1.GetClientDataArray(0).SetRowArray(0, 0);
sh_a1.GetClientDataArray(0).SetColumnArray(0, 1);
Assert.AreSame(sh_a1, newVml.FindCommentShape(0, 1));
}
public void TestNew()
{
XSSFVMLDrawing vml = new XSSFVMLDrawing();
ArrayList items = vml.GetItems();
Assert.AreEqual(2, items.Count);
Assert.IsTrue(items[0] is CT_ShapeLayout);
CT_ShapeLayout layout = (CT_ShapeLayout)items[0];
Assert.AreEqual(ST_Ext.edit, layout.ext);
Assert.AreEqual(ST_Ext.edit, layout.idmap.ext);
Assert.AreEqual("1", layout.idmap.data);
Assert.IsTrue(items[1] is CT_Shapetype);
CT_Shapetype type = (CT_Shapetype)items[1];
Assert.AreEqual("21600,21600", type.coordsize);
Assert.AreEqual(202.0f, type.spt);
Assert.AreEqual("m,l,21600r21600,l21600,xe", type.path2);
Assert.AreEqual("_xssf_cell_comment", type.id);
Assert.AreEqual(ST_TrueFalse.t, type.path.gradientshapeok);
Assert.AreEqual(ST_ConnectType.rect, type.path.connecttype);
CT_Shape shape = vml.newCommentShape();
Assert.AreEqual(3, items.Count);
Assert.AreSame(items[2], shape);
Assert.AreEqual("#_xssf_cell_comment", shape.type);
Assert.AreEqual("position:absolute; visibility:hidden", shape.style);
Assert.AreEqual("#ffffe1", shape.fillcolor);
Assert.AreEqual(ST_InsetMode.auto, shape.insetmode);
Assert.AreEqual("#ffffe1", shape.fill.color);
CT_Shadow shadow = shape.shadow;
Assert.AreEqual(ST_TrueFalse.t, shadow.on);
Assert.AreEqual("black", shadow.color);
Assert.AreEqual(ST_TrueFalse.t, shadow.obscured);
Assert.AreEqual(ST_ConnectType.none, shape.path.connecttype);
Assert.AreEqual("mso-direction-alt:auto", shape.textbox.style);
CT_ClientData cldata = shape.GetClientDataArray(0);
Assert.AreEqual(ST_ObjectType.Note, cldata.ObjectType);
Assert.AreEqual(1, cldata.SizeOfMoveWithCellsArray());
Assert.AreEqual(1, cldata.SizeOfSizeWithCellsArray());
Assert.AreEqual("1, 15, 0, 2, 3, 15, 3, 16", cldata.anchor);
Assert.AreEqual(ST_TrueFalseBlank.@false, cldata.autoFill);
Assert.AreEqual(0, cldata.GetRowArray(0));
Assert.AreEqual(0, cldata.GetColumnArray(0));
//serialize and read again
MemoryStream out1 = new MemoryStream();
vml.Write(out1);
XSSFVMLDrawing vml2 = new XSSFVMLDrawing();
vml2.Read(new MemoryStream(out1.ToArray()));
ArrayList items2 = vml2.GetItems();
Assert.AreEqual(3, items2.Count);
Assert.IsTrue(items2[0] is CT_ShapeLayout);
Assert.IsTrue(items2[1] is CT_Shapetype);
Assert.IsTrue(items2[2] is CT_Shape);
}
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 TestGetSetRow()
{
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.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 GetSetCol()
{
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.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));
}
/**
* 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 TestFindCommentShape()
{
XSSFVMLDrawing vml = new XSSFVMLDrawing();
vml.Read(POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream("vmlDrawing1.vml"));
CT_Shape sh_a1 = vml.FindCommentShape(0, 0);
Assert.IsNotNull(sh_a1);
Assert.AreEqual("_x0000_s1025", sh_a1.id);
CT_Shape sh_b1 = vml.FindCommentShape(0, 1);
Assert.IsNotNull(sh_b1);
Assert.AreEqual("_x0000_s1026", sh_b1.id);
CT_Shape sh_c1 = vml.FindCommentShape(0, 2);
Assert.IsNull(sh_c1);
CT_Shape sh_d1 = vml.newCommentShape();
Assert.AreEqual("_x0000_s1027", sh_d1.id);
sh_d1.GetClientDataArray(0).SetRowArray(0, 0);
sh_d1.GetClientDataArray(0).SetColumnArray(0, 3);
Assert.AreSame(sh_d1, vml.FindCommentShape(0, 3));
//newly created drawing
XSSFVMLDrawing newVml = new XSSFVMLDrawing();
Assert.IsNull(newVml.FindCommentShape(0, 0));
sh_a1 = newVml.newCommentShape();
Assert.AreEqual("_x0000_s1025", sh_a1.id);
sh_a1.GetClientDataArray(0).SetRowArray(0, 0);
sh_a1.GetClientDataArray(0).SetColumnArray(0, 1);
Assert.AreSame(sh_a1, newVml.FindCommentShape(0, 1));
}
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 TestNew()
{
XSSFVMLDrawing vml = new XSSFVMLDrawing();
ArrayList items = vml.GetItems();
Assert.AreEqual(2, items.Count);
Assert.IsTrue(items[0] is CT_ShapeLayout);
CT_ShapeLayout layout = (CT_ShapeLayout)items[0];
Assert.AreEqual(ST_Ext.edit, layout.ext);
Assert.AreEqual(ST_Ext.edit, layout.idmap.ext);
Assert.AreEqual("1", layout.idmap.data);
Assert.IsTrue(items[1] is CT_Shapetype);
CT_Shapetype type = (CT_Shapetype)items[1];
Assert.AreEqual("21600,21600", type.coordsize);
Assert.AreEqual(202.0f, type.spt);
Assert.AreEqual("m,l,21600r21600,l21600,xe", type.path2);
Assert.IsTrue(type.id.StartsWith("_x0000_"));
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, type.path.gradientshapeok);
Assert.AreEqual(ST_ConnectType.rect, type.path.connecttype);
CT_Shape shape = vml.newCommentShape();
Assert.AreEqual(3, items.Count);
Assert.AreSame(items[2], shape);
Assert.IsTrue(shape.type.StartsWith("#_x0000_"));
Assert.AreEqual("position:absolute; visibility:hidden", shape.style);
Assert.AreEqual("#ffffe1", shape.fillcolor);
Assert.AreEqual(ST_InsetMode.auto, shape.insetmode);
Assert.AreEqual("#ffffe1", shape.fill.color);
CT_Shadow shadow = shape.shadow;
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shadow.on);
Assert.AreEqual("black", shadow.color);
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shadow.obscured);
Assert.AreEqual(ST_ConnectType.none, shape.path.connecttype);
Assert.AreEqual("mso-direction-alt:auto", shape.textbox.style);
CT_ClientData cldata = shape.GetClientDataArray(0);
Assert.AreEqual(ST_ObjectType.Note, cldata.ObjectType);
Assert.AreEqual(1, cldata.SizeOfMoveWithCellsArray());
Assert.AreEqual(1, cldata.SizeOfSizeWithCellsArray());
Assert.AreEqual("1, 15, 0, 2, 3, 15, 3, 16", cldata.anchor);
Assert.AreEqual(ST_TrueFalseBlank.@false, cldata.autoFill);
Assert.AreEqual(0, cldata.GetRowArray(0));
Assert.AreEqual(0, cldata.GetColumnArray(0));
//serialize and read again
MemoryStream out1 = new MemoryStream();
vml.Write(out1);
XSSFVMLDrawing vml2 = new XSSFVMLDrawing();
vml2.Read(new MemoryStream(out1.ToArray()));
ArrayList items2 = vml2.GetItems();
Assert.AreEqual(3, items2.Count);
Assert.IsTrue(items2[0] is CT_ShapeLayout);
Assert.IsTrue(items2[1] is CT_Shapetype);
Assert.IsTrue(items2[2] is CT_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);
}
}
}
public void TestRemoveCommentShape()
{
XSSFVMLDrawing vml = new XSSFVMLDrawing();
vml.Read(POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream("vmlDrawing1.vml"));
CT_Shape sh_a1 = vml.FindCommentShape(0, 0);
Assert.IsNotNull(sh_a1);
Assert.IsTrue(vml.RemoveCommentShape(0, 0));
Assert.IsNull(vml.FindCommentShape(0, 0));
}
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));
}