public void TestReadComments()
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook("SimpleWithComments." + _testDataProvider.StandardFileNameExtension);
ISheet sheet = wb.GetSheetAt(0);
ICell cell;
IRow row;
IComment comment;
for (int rownum = 0; rownum < 3; rownum++)
{
row = sheet.GetRow(rownum);
cell = row.GetCell(0);
comment = cell.CellComment;
Assert.IsNull(comment, "Cells in the first column are not commented");
Assert.IsNull(sheet.GetCellComment(rownum, 0));
}
for (int rownum = 0; rownum < 3; rownum++)
{
row = sheet.GetRow(rownum);
cell = row.GetCell(1);
comment = cell.CellComment;
Assert.IsNotNull(comment, "Cells in the second column have comments");
Assert.IsNotNull(sheet.GetCellComment(rownum, 1), "Cells in the second column have comments");
Assert.AreEqual("Yegor Kozlov", comment.Author);
Assert.IsFalse(comment.String.String == string.Empty, "cells in the second column have not empyy notes");
Assert.AreEqual(rownum, comment.Row);
Assert.AreEqual(cell.ColumnIndex, comment.Column);
}
}
public void TestFind()
{
IWorkbook book = _testDataProvider.CreateWorkbook();
ISheet sheet = book.CreateSheet();
Assert.IsNull(sheet.GetCellComment(0, 0));
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
Assert.IsNull(sheet.GetCellComment(0, 0));
Assert.IsNull(cell.CellComment);
}
public void TestBug56017()
{
IWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56017.xlsx");
ISheet sheet = wb.GetSheetAt(0);
IComment comment = sheet.GetCellComment(0, 0);
Assert.IsNotNull(comment);
Assert.AreEqual("Amdocs", comment.Author);
Assert.AreEqual("Amdocs:\ntest\n", comment.String.String);
sheet.ShiftRows(0, 1, 1);
// comment in row 0 is gone
comment = sheet.GetCellComment(0, 0);
Assert.IsNull(comment);
// comment is now in row 1
comment = sheet.GetCellComment(1, 0);
Assert.IsNotNull(comment);
Assert.AreEqual("Amdocs", comment.Author);
Assert.AreEqual("Amdocs:\ntest\n", comment.String.String);
// FileOutputStream outputStream = new FileOutputStream("/tmp/56017.xlsx");
// try {
// wb.Write(outputStream);
// } finally {
// outputStream.Close();
// }
IWorkbook wbBack = XSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.IsNotNull(wbBack);
ISheet sheetBack = wbBack.GetSheetAt(0);
// comment in row 0 is gone
comment = sheetBack.GetCellComment(0, 0);
Assert.IsNull(comment);
// comment is now in row 1
comment = sheetBack.GetCellComment(1, 0);
Assert.IsNotNull(comment);
Assert.AreEqual("Amdocs", comment.Author);
Assert.AreEqual("Amdocs:\ntest\n", comment.String.String);
}
public void GetCellComment()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
IDrawing dg = sheet.CreateDrawingPatriarch();
IComment comment = dg.CreateCellComment(workbook.GetCreationHelper().CreateClientAnchor());
ICell cell = sheet.CreateRow(9).CreateCell(2);
comment.Author = (/*setter*/ "test C10 author");
cell.CellComment = (/*setter*/ comment);
Assert.IsNotNull(sheet.GetCellComment(9, 2));
Assert.AreEqual("test C10 author", sheet.GetCellComment(9, 2).Author);
Assert.IsNotNull(_testDataProvider.WriteOutAndReadBack(workbook));
workbook.Close();
}
public void TestBug56017()
{
IWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56017.xlsx");
ISheet sheet = wb.GetSheetAt(0);
IComment comment = sheet.GetCellComment(new CellAddress(0, 0));
Assert.IsNotNull(comment);
Assert.AreEqual("Amdocs", comment.Author);
Assert.AreEqual("Amdocs:\ntest\n", comment.String.String);
sheet.ShiftRows(0, 1, 1);
// comment in row 0 is gone
comment = sheet.GetCellComment(new CellAddress(0, 0));
Assert.IsNull(comment);
// comment is now in row 1
comment = sheet.GetCellComment(new CellAddress(1, 0));
Assert.IsNotNull(comment);
Assert.AreEqual("Amdocs", comment.Author);
Assert.AreEqual("Amdocs:\ntest\n", comment.String.String);
IWorkbook wbBack = XSSFTestDataSamples.WriteOutAndReadBack(wb);
wb.Close();
Assert.IsNotNull(wbBack);
ISheet sheetBack = wbBack.GetSheetAt(0);
// comment in row 0 is gone
comment = sheetBack.GetCellComment(new CellAddress(0, 0));
Assert.IsNull(comment);
// comment is now in row 1
comment = sheetBack.GetCellComment(new CellAddress(1, 0));
Assert.IsNotNull(comment);
Assert.AreEqual("Amdocs", comment.Author);
Assert.AreEqual("Amdocs:\ntest\n", comment.String.String);
wbBack.Close();
}
public void Bug57838DeleteRowsWthCommentsBug()
{
IWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("57838.xlsx");
ISheet sheet = wb.GetSheetAt(0);
IComment comment1 = sheet.GetCellComment(new CellAddress(2, 1));
Assert.IsNotNull(comment1);
IComment comment2 = sheet.GetCellComment(new CellAddress(2, 2));
Assert.IsNotNull(comment2);
IRow row = sheet.GetRow(2);
Assert.IsNotNull(row);
sheet.RemoveRow(row); // Remove row from index 2
row = sheet.GetRow(2);
Assert.IsNull(row); // Row is null since we deleted it.
comment1 = sheet.GetCellComment(new CellAddress(2, 1));
Assert.IsNull(comment1); // comment should be null but will Assert.Fail due to bug
comment2 = sheet.GetCellComment(new CellAddress(2, 2));
Assert.IsNull(comment2); // comment should be null but will Assert.Fail due to bug
wb.Close();
}
public void TestShiftWithComments()
{ // TODO - enable XSSF Test
IWorkbook wb = _testDataProvider.OpenSampleWorkbook("comments." + _testDataProvider.StandardFileNameExtension);
ISheet sheet = wb.GetSheet("Sheet1");
Assert.AreEqual(3, sheet.LastRowNum);
// Verify comments are in the position expected
Assert.IsNotNull(sheet.GetCellComment(0, 0));
Assert.IsNull(sheet.GetCellComment(1, 0));
Assert.IsNotNull(sheet.GetCellComment(2, 0));
Assert.IsNotNull(sheet.GetCellComment(3, 0));
String comment1 = sheet.GetCellComment(0, 0).String.String;
Assert.AreEqual(comment1, "comment top row1 (index0)\n");
String comment3 = sheet.GetCellComment(2, 0).String.String;
Assert.AreEqual(comment3, "comment top row3 (index2)\n");
String comment4 = sheet.GetCellComment(3, 0).String.String;
Assert.AreEqual(comment4, "comment top row4 (index3)\n");
// Shifting all but first line down to Test comments Shifting
sheet.ShiftRows(1, sheet.LastRowNum, 1, true, true);
// Test that comments were Shifted as expected
Assert.AreEqual(4, sheet.LastRowNum);
Assert.IsNotNull(sheet.GetCellComment(0, 0));
Assert.IsNull(sheet.GetCellComment(1, 0));
Assert.IsNull(sheet.GetCellComment(2, 0));
Assert.IsNotNull(sheet.GetCellComment(3, 0));
Assert.IsNotNull(sheet.GetCellComment(4, 0));
String comment1_Shifted = sheet.GetCellComment(0, 0).String.String;
Assert.AreEqual(comment1, comment1_Shifted);
String comment3_Shifted = sheet.GetCellComment(3, 0).String.String;
Assert.AreEqual(comment3, comment3_Shifted);
String comment4_Shifted = sheet.GetCellComment(4, 0).String.String;
Assert.AreEqual(comment4, comment4_Shifted);
// Write out and read back in again
// Ensure that the Changes were persisted
wb = _testDataProvider.WriteOutAndReadBack(wb);
sheet = wb.GetSheet("Sheet1");
Assert.AreEqual(4, sheet.LastRowNum);
// Verify comments are in the position expected After the shift
Assert.IsNotNull(sheet.GetCellComment(0, 0));
Assert.IsNull(sheet.GetCellComment(1, 0));
Assert.IsNull(sheet.GetCellComment(2, 0));
Assert.IsNotNull(sheet.GetCellComment(3, 0));
Assert.IsNotNull(sheet.GetCellComment(4, 0));
comment1_Shifted = sheet.GetCellComment(0, 0).String.String;
Assert.AreEqual(comment1, comment1_Shifted);
comment3_Shifted = sheet.GetCellComment(3, 0).String.String;
Assert.AreEqual(comment3, comment3_Shifted);
comment4_Shifted = sheet.GetCellComment(4, 0).String.String;
Assert.AreEqual(comment4, comment4_Shifted);
}
public void TestShiftWithComments()
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook("comments." + _testDataProvider.StandardFileNameExtension);
ISheet sheet = wb.GetSheet("Sheet1");
Assert.AreEqual(3, sheet.LastRowNum);
// Verify comments are in the position expected
Assert.IsNotNull(sheet.GetCellComment(0, 0));
Assert.IsNull(sheet.GetCellComment(1, 0));
Assert.IsNotNull(sheet.GetCellComment(2, 0));
Assert.IsNotNull(sheet.GetCellComment(3, 0));
String comment1 = sheet.GetCellComment(0, 0).String.String;
Assert.AreEqual(comment1, "comment top row1 (index0)\n");
String comment3 = sheet.GetCellComment(2, 0).String.String;
Assert.AreEqual(comment3, "comment top row3 (index2)\n");
String comment4 = sheet.GetCellComment(3, 0).String.String;
Assert.AreEqual(comment4, "comment top row4 (index3)\n");
//Workbook wbBack = _testDataProvider.writeOutAndReadBack(wb);
// Shifting all but first line down to Test comments Shifting
sheet.ShiftRows(1, sheet.LastRowNum, 1, true, true);
// Test that comments were Shifted as expected
Assert.AreEqual(4, sheet.LastRowNum);
Assert.IsNotNull(sheet.GetCellComment(0, 0));
Assert.IsNull(sheet.GetCellComment(1, 0));
Assert.IsNull(sheet.GetCellComment(2, 0));
Assert.IsNotNull(sheet.GetCellComment(3, 0));
Assert.IsNotNull(sheet.GetCellComment(4, 0));
String comment1_Shifted = sheet.GetCellComment(0, 0).String.String;
Assert.AreEqual(comment1, comment1_Shifted);
String comment3_Shifted = sheet.GetCellComment(3, 0).String.String;
Assert.AreEqual(comment3, comment3_Shifted);
String comment4_Shifted = sheet.GetCellComment(4, 0).String.String;
Assert.AreEqual(comment4, comment4_Shifted);
// Write out and read back in again
// Ensure that the Changes were persisted
wb = _testDataProvider.WriteOutAndReadBack(wb);
sheet = wb.GetSheet("Sheet1");
Assert.AreEqual(4, sheet.LastRowNum);
// Verify comments are in the position expected After the shift
Assert.IsNotNull(sheet.GetCellComment(0, 0));
Assert.IsNull(sheet.GetCellComment(1, 0));
Assert.IsNull(sheet.GetCellComment(2, 0));
Assert.IsNotNull(sheet.GetCellComment(3, 0));
Assert.IsNotNull(sheet.GetCellComment(4, 0));
comment1_Shifted = sheet.GetCellComment(0, 0).String.String;
Assert.AreEqual(comment1, comment1_Shifted);
comment3_Shifted = sheet.GetCellComment(3, 0).String.String;
Assert.AreEqual(comment3, comment3_Shifted);
comment4_Shifted = sheet.GetCellComment(4, 0).String.String;
Assert.AreEqual(comment4, comment4_Shifted);
// Shifting back up again, now two rows
sheet.ShiftRows(2, sheet.LastRowNum, -2, true, true);
// TODO: it seems HSSFSheet does not correctly remove comments from rows that are overwritten
// by Shifting rows...
if (!(wb is HSSFWorkbook))
{
Assert.AreEqual(2, sheet.LastRowNum);
// Verify comments are in the position expected
Assert.IsNull(sheet.GetCellComment(0, 0),
"Had: " + (sheet.GetCellComment(0, 0) == null ? "null" : sheet.GetCellComment(0, 0).String.String));
Assert.IsNotNull(sheet.GetCellComment(1, 0));
Assert.IsNotNull(sheet.GetCellComment(2, 0));
}
comment1 = sheet.GetCellComment(1, 0).String.String;
Assert.AreEqual(comment1, "comment top row3 (index2)\n");
String comment2 = sheet.GetCellComment(2, 0).String.String;
Assert.AreEqual(comment2, "comment top row4 (index3)\n");
}
public void TestCreate()
{
String cellText = "Hello, World";
String commentText = "We can set comments in POI";
String commentAuthor = "Apache Software Foundation";
int cellRow = 3;
int cellColumn = 1;
IWorkbook wb = _testDataProvider.CreateWorkbook();
ICreationHelper factory = wb.GetCreationHelper();
ISheet sheet = wb.CreateSheet();
Assert.IsNull(sheet.GetCellComment(cellRow, cellColumn));
ICell cell = sheet.CreateRow(cellRow).CreateCell(cellColumn);
cell.SetCellValue(factory.CreateRichTextString(cellText));
Assert.IsNull(cell.CellComment);
Assert.IsNull(sheet.GetCellComment(cellRow, cellColumn));
IDrawing patr = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = factory.CreateClientAnchor();
anchor.Col1 = (2);
anchor.Col2 = (5);
anchor.Row1 = (1);
anchor.Row2 = (2);
IComment comment = patr.CreateCellComment(anchor);
Assert.IsFalse(comment.Visible);
comment.Visible = (true);
Assert.IsTrue(comment.Visible);
IRichTextString string1 = factory.CreateRichTextString(commentText);
comment.String = (string1);
comment.Author = (commentAuthor);
cell.CellComment = (comment);
Assert.IsNotNull(cell.CellComment);
Assert.IsNotNull(sheet.GetCellComment(cellRow, cellColumn));
//verify our Settings
Assert.AreEqual(commentAuthor, comment.Author);
Assert.AreEqual(commentText, comment.String.String);
Assert.AreEqual(cellRow, comment.Row);
Assert.AreEqual(cellColumn, comment.Column);
wb = _testDataProvider.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0);
cell = sheet.GetRow(cellRow).GetCell(cellColumn);
comment = cell.CellComment;
Assert.IsNotNull(comment);
Assert.AreEqual(commentAuthor, comment.Author);
Assert.AreEqual(commentText, comment.String.String);
Assert.AreEqual(cellRow, comment.Row);
Assert.AreEqual(cellColumn, comment.Column);
Assert.IsTrue(comment.Visible);
// Change slightly, and re-test
comment.String = (factory.CreateRichTextString("New Comment Text"));
comment.Visible = (false);
wb = _testDataProvider.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0);
cell = sheet.GetRow(cellRow).GetCell(cellColumn);
comment = cell.CellComment;
Assert.IsNotNull(comment);
Assert.AreEqual(commentAuthor, comment.Author);
Assert.AreEqual("New Comment Text", comment.String.String);
Assert.AreEqual(cellRow, comment.Row);
Assert.AreEqual(cellColumn, comment.Column);
Assert.IsFalse(comment.Visible);
}
// 备注
static public IComment GetIComment(ISheet sheet, int rowIndex, int columnIndex)
{
return(sheet.GetCellComment(rowIndex, columnIndex));
}
public IComment GetCellComment(int row, int column)
{
return(_sheet.GetCellComment(row, column));
}
