public void TestRemoveCell() { IWorkbook workbook = _testDataProvider.CreateWorkbook(); ISheet sheet = workbook.CreateSheet(); IRow row = sheet.CreateRow(0); Assert.AreEqual(0, row.PhysicalNumberOfCells); Assert.AreEqual(-1, row.LastCellNum); Assert.AreEqual(-1, row.FirstCellNum); row.CreateCell(1); Assert.AreEqual(2, row.LastCellNum); Assert.AreEqual(1, row.FirstCellNum); Assert.AreEqual(1, row.PhysicalNumberOfCells); row.CreateCell(3); Assert.AreEqual(4, row.LastCellNum); Assert.AreEqual(1, row.FirstCellNum); Assert.AreEqual(2, row.PhysicalNumberOfCells); row.RemoveCell(row.GetCell(3)); Assert.AreEqual(2, row.LastCellNum); Assert.AreEqual(1, row.FirstCellNum); Assert.AreEqual(1, row.PhysicalNumberOfCells); row.RemoveCell(row.GetCell(1)); Assert.AreEqual(-1, row.LastCellNum); Assert.AreEqual(-1, row.FirstCellNum); Assert.AreEqual(0, row.PhysicalNumberOfCells); workbook = _testDataProvider.WriteOutAndReadBack(workbook); sheet = workbook.GetSheetAt(0); row = sheet.GetRow(0); Assert.AreEqual(-1, row.LastCellNum); Assert.AreEqual(-1, row.FirstCellNum); Assert.AreEqual(0, row.PhysicalNumberOfCells); }
public void TestCreateSave() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet s1 = wb.CreateSheet(); IRow r1 = s1.CreateRow(0); ICell r1c1 = r1.CreateCell(0); r1c1.SetCellValue(2.2); int num0 = wb.NumberOfFonts; IFont font = wb.CreateFont(); font.Boldweight = (short)(FontBoldWeight.BOLD); font.IsStrikeout = (true); font.Color = (IndexedColors.YELLOW.Index); font.FontName = ("Courier"); short font1Idx = font.Index; wb.CreateCellStyle().SetFont(font); Assert.AreEqual(num0 + 1, wb.NumberOfFonts); ICellStyle cellStyleTitle = wb.CreateCellStyle(); cellStyleTitle.SetFont(font); r1c1.CellStyle = (cellStyleTitle); // Save and re-load wb = _testDataProvider.WriteOutAndReadBack(wb); s1 = wb.GetSheetAt(0); Assert.AreEqual(num0 + 1, wb.NumberOfFonts); short idx = s1.GetRow(0).GetCell(0).CellStyle.FontIndex; IFont fnt = wb.GetFontAt(idx); Assert.IsNotNull(fnt); Assert.AreEqual(IndexedColors.YELLOW.Index, fnt.Color); Assert.AreEqual("Courier", fnt.FontName); // Now add an orphaned one IFont font2 = wb.CreateFont(); font2.IsItalic = (true); font2.FontHeightInPoints = (short)15; short font2Idx = font2.Index; wb.CreateCellStyle().SetFont(font2); Assert.AreEqual(num0 + 2, wb.NumberOfFonts); // Save and re-load wb = _testDataProvider.WriteOutAndReadBack(wb); s1 = wb.GetSheetAt(0); Assert.AreEqual(num0 + 2, wb.NumberOfFonts); Assert.IsNotNull(wb.GetFontAt(font1Idx)); Assert.IsNotNull(wb.GetFontAt(font2Idx)); Assert.AreEqual(15, wb.GetFontAt(font2Idx).FontHeightInPoints); Assert.IsTrue(wb.GetFontAt(font2Idx).IsItalic); }
public void TestPrintSetupLandscapeNew() { IWorkbook workbook = _testDataProvider.CreateWorkbook(); ISheet sheetL = workbook.CreateSheet("LandscapeS"); ISheet sheetP = workbook.CreateSheet("LandscapeP"); // Check two aspects of the print Setup Assert.IsFalse(sheetL.PrintSetup.Landscape); Assert.IsFalse(sheetP.PrintSetup.Landscape); Assert.AreEqual(1, sheetL.PrintSetup.Copies); Assert.AreEqual(1, sheetP.PrintSetup.Copies); // Change one on each sheetL.PrintSetup.Landscape = (/*setter*/ true); sheetP.PrintSetup.Copies = (/*setter*/ (short)3); // Check taken Assert.IsTrue(sheetL.PrintSetup.Landscape); Assert.IsFalse(sheetP.PrintSetup.Landscape); Assert.AreEqual(1, sheetL.PrintSetup.Copies); Assert.AreEqual(3, sheetP.PrintSetup.Copies); // Save and re-load, and check still there workbook = _testDataProvider.WriteOutAndReadBack(workbook); sheetL = workbook.GetSheet("LandscapeS"); sheetP = workbook.GetSheet("LandscapeP"); Assert.IsTrue(sheetL.PrintSetup.Landscape); Assert.IsFalse(sheetP.PrintSetup.Landscape); Assert.AreEqual(1, sheetL.PrintSetup.Copies); Assert.AreEqual(3, sheetP.PrintSetup.Copies); }
public void TestHide() { wbU.SetSheetHidden(0, SheetState.Hidden); Assert.IsTrue(wbU.IsSheetHidden(0)); Assert.IsFalse(wbU.IsSheetHidden(1)); IWorkbook wb2 = _testDataProvider.WriteOutAndReadBack(wbU); Assert.IsTrue(wb2.IsSheetHidden(0)); Assert.IsFalse(wb2.IsSheetHidden(1)); }
public void TestDataValidation() { Log("\nTest no. 2 - Test Excel's Data validation mechanism"); IWorkbook wb = _testDataProvider.CreateWorkbook(); WorkbookFormatter wf = new WorkbookFormatter(wb); Log(" Create sheet for Data Validation's number types ... "); AddSimpleNumericValidations(wf); Log("done !"); Log(" Create sheet for 'List' Data Validation type ... "); AddListValidations(wf, wb); Log("done !"); Log(" Create sheet for 'Date' and 'Time' Data Validation types ... "); AddDateTimeValidations(wf, wb); Log("done !"); Log(" Create sheet for 'Text length' Data Validation type... "); AddTextLengthValidations(wf); Log("done !"); // Custom Validation type Log(" Create sheet for 'Custom' Data Validation type ... "); AddCustomValidations(wf); Log("done !"); wb = _testDataProvider.WriteOutAndReadBack(wb); }
public void TestBasicTypes() { IWorkbook wb1 = _testDataProvider.CreateWorkbook(); ICreationHelper CreateHelper = wb1.GetCreationHelper(); ICell cell; IHyperlink link; ISheet sheet = wb1.CreateSheet("Hyperlinks"); //URL cell = sheet.CreateRow(0).CreateCell((short)0); cell.SetCellValue("URL Link"); link = CreateHelper.CreateHyperlink(HyperlinkType.Url); link.Address = ("http://poi.apache.org/"); cell.Hyperlink = (link); //link to a file in the current directory cell = sheet.CreateRow(1).CreateCell((short)0); cell.SetCellValue("File Link"); link = CreateHelper.CreateHyperlink(HyperlinkType.File); link.Address = ("hyperinks-beta4-dump.txt"); cell.Hyperlink = (link); //e-mail link cell = sheet.CreateRow(2).CreateCell((short)0); cell.SetCellValue("Email Link"); link = CreateHelper.CreateHyperlink(HyperlinkType.Email); //note, if subject Contains white spaces, make sure they are url-encoded link.Address = ("mailto:[email protected]?subject=Hyperlinks"); cell.Hyperlink = (link); //link to a place in this workbook //create a target sheet and cell ISheet sheet2 = wb1.CreateSheet("Target Sheet"); sheet2.CreateRow(0).CreateCell((short)0).SetCellValue("Target Cell"); cell = sheet.CreateRow(3).CreateCell((short)0); cell.SetCellValue("Worksheet Link"); link = CreateHelper.CreateHyperlink(HyperlinkType.Document); link.Address = ("'Target Sheet'!A1"); cell.Hyperlink = (link); IWorkbook wb2 = _testDataProvider.WriteOutAndReadBack(wb1); sheet = wb2.GetSheetAt(0); link = sheet.GetRow(0).GetCell(0).Hyperlink; Assert.AreEqual("http://poi.apache.org/", link.Address); link = sheet.GetRow(1).GetCell(0).Hyperlink; Assert.AreEqual("hyperinks-beta4-dump.txt", link.Address); link = sheet.GetRow(2).GetCell(0).Hyperlink; Assert.AreEqual("mailto:[email protected]?subject=Hyperlinks", link.Address); link = sheet.GetRow(3).GetCell(0).Hyperlink; Assert.AreEqual("'Target Sheet'!A1", link.Address); wb2.Close(); }
public void TestUnicodeNamedRange() { IWorkbook workBook = _testDataProvider.CreateWorkbook(); workBook.CreateSheet("Test"); IName name = workBook.CreateName(); name.NameName = ("\u03B1"); name.RefersToFormula = ("Test!$D$3:$E$8"); IWorkbook workBook2 = _testDataProvider.WriteOutAndReadBack(workBook); IName name2 = workBook2.GetNameAt(0); Assert.AreEqual("\u03B1", name2.NameName); Assert.AreEqual("Test!$D$3:$E$8", name2.RefersToFormula); }
public void Test23094() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet s = wb.CreateSheet(); IRow r = s.CreateRow(0); r.CreateCell(0).CellFormula = "HYPERLINK(\"http://jakarta.apache.org\",\"Jakarta\")"; r.CreateCell(1).CellFormula = "HYPERLINK(\"http://google.com\",\"Google\")"; wb = _testDataProvider.WriteOutAndReadBack(wb); r = wb.GetSheetAt(0).GetRow(0); ICell cell_0 = r.GetCell(0); Assert.AreEqual("HYPERLINK(\"http://jakarta.apache.org\",\"Jakarta\")", cell_0.CellFormula); ICell cell_1 = r.GetCell(1); Assert.AreEqual("HYPERLINK(\"http://google.com\",\"Google\")", cell_1.CellFormula); }
public void TestBoolErr() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet s = wb.CreateSheet("testSheet1"); IRow r; ICell c; r = s.CreateRow(0); c = r.CreateCell(1); //c.SetCellType(HSSFCellType.Boolean); c.SetCellValue(true); c = r.CreateCell(2); //c.SetCellType(HSSFCellType.Boolean); c.SetCellValue(false); r = s.CreateRow(1); c = r.CreateCell(1); //c.SetCellType(HSSFCellType.Error); c.SetCellErrorValue((byte)0); c = r.CreateCell(2); //c.SetCellType(HSSFCellType.Error); c.SetCellErrorValue((byte)7); wb = _testDataProvider.WriteOutAndReadBack(wb); s = wb.GetSheetAt(0); r = s.GetRow(0); c = r.GetCell(1); Assert.IsTrue(c.BooleanCellValue, "bool value 0,1 = true"); c = r.GetCell(2); Assert.IsTrue(c.BooleanCellValue == false, "bool value 0,2 = false"); r = s.GetRow(1); c = r.GetCell(1); Assert.IsTrue(c.ErrorCellValue == 0, "bool value 0,1 = 0"); c = r.GetCell(2); Assert.IsTrue(c.ErrorCellValue == 7, "bool value 0,2 = 7"); }
public void TestReadArrayFormula() { ICell[] cells; IWorkbook workbook1 = _testDataProvider.CreateWorkbook(); ISheet sheet1 = workbook1.CreateSheet(); cells = sheet1.SetArrayFormula("SUM(A1:A3*B1:B3)", CellRangeAddress.ValueOf("C4:C6")).FlattenedCells; Assert.AreEqual(3, cells.Length); cells = sheet1.SetArrayFormula("MAX(A1:A3*B1:B3)", CellRangeAddress.ValueOf("A4:A6")).FlattenedCells; Assert.AreEqual(3, cells.Length); ISheet sheet2 = workbook1.CreateSheet(); cells = sheet2.SetArrayFormula("MIN(A1:A3*B1:B3)", CellRangeAddress.ValueOf("D2:D4")).FlattenedCells; Assert.AreEqual(3, cells.Length); IWorkbook workbook2 = _testDataProvider.WriteOutAndReadBack(workbook1); workbook1.Close(); sheet1 = workbook2.GetSheetAt(0); for (int rownum = 3; rownum <= 5; rownum++) { ICell cell1 = sheet1.GetRow(rownum).GetCell(2); Assert.IsTrue(cell1.IsPartOfArrayFormulaGroup); ICell cell2 = sheet1.GetRow(rownum).GetCell(0); Assert.IsTrue(cell2.IsPartOfArrayFormulaGroup); } sheet2 = workbook2.GetSheetAt(1); for (int rownum = 1; rownum <= 3; rownum++) { ICell cell1 = sheet2.GetRow(rownum).GetCell(3); Assert.IsTrue(cell1.IsPartOfArrayFormulaGroup); } workbook2.Close(); }
public void TestShiftRows() { // Read Initial file in String sampleName = "SimpleMultiCell." + _testDataProvider.StandardFileNameExtension; IWorkbook wb = _testDataProvider.OpenSampleWorkbook(sampleName); ISheet s = wb.GetSheetAt(0); // Shift the second row down 1 and write to temp file s.ShiftRows(1, 1, 1); { Console.WriteLine("Shift the second row down 1"); var msg = string.Format("1a {0}-{1}-{2}-{3}-{4}-{5}", GetRowValue(s, 0), GetRowValue(s, 1), GetRowValue(s, 2), GetRowValue(s, 3), GetRowValue(s, 4), GetRowValue(s, 5)); Console.WriteLine(msg); } wb = _testDataProvider.WriteOutAndReadBack(wb); // Read from temp file and check the number of cells in each // row (in original file each row was unique) s = wb.GetSheetAt(0); { var msg = string.Format("1b {0}-{1}-{2}-{3}-{4}-{5}", GetRowValue(s, 0), GetRowValue(s, 1), GetRowValue(s, 2), GetRowValue(s, 3), GetRowValue(s, 4), GetRowValue(s, 5)); Console.WriteLine(msg); } Assert.AreEqual(s.GetRow(0).PhysicalNumberOfCells, 1); ConfirmEmptyRow(s, 1); Assert.AreEqual(s.GetRow(2).PhysicalNumberOfCells, 2); Assert.AreEqual(s.GetRow(3).PhysicalNumberOfCells, 4); Assert.AreEqual(s.GetRow(4).PhysicalNumberOfCells, 5); // Shift rows 1-3 down 3 in the current one. This Tests when // 1 row is blank. Write to a another temp file s.ShiftRows(0, 2, 3); { Console.WriteLine("Shift rows 1-3 down 3 in the current one"); var msg = string.Format("2a {0}-{1}-{2}-{3}-{4}-{5}", GetRowValue(s, 0), GetRowValue(s, 1), GetRowValue(s, 2), GetRowValue(s, 3), GetRowValue(s, 4), GetRowValue(s, 5)); Console.WriteLine(msg); } wb = _testDataProvider.WriteOutAndReadBack(wb); // Read and ensure things are where they should be s = wb.GetSheetAt(0); { var msg = string.Format("2b {0}-{1}-{2}-{3}-{4}-{5}", GetRowValue(s, 0), GetRowValue(s, 1), GetRowValue(s, 2), GetRowValue(s, 3), GetRowValue(s, 4), GetRowValue(s, 5)); Console.WriteLine(msg); } ConfirmEmptyRow(s, 0); ConfirmEmptyRow(s, 1); ConfirmEmptyRow(s, 2); Assert.AreEqual(s.GetRow(3).PhysicalNumberOfCells, 1); ConfirmEmptyRow(s, 4); Assert.AreEqual(s.GetRow(5).PhysicalNumberOfCells, 2); // Read the first file again wb = _testDataProvider.OpenSampleWorkbook(sampleName); s = wb.GetSheetAt(0); // Shift rows 3 and 4 up and write to temp file s.ShiftRows(2, 3, -2); { Console.WriteLine("Shift rows 3 and 4 up"); var msg = string.Format("3a {0}-{1}-{2}-{3}-{4}-{5}", GetRowValue(s, 0), GetRowValue(s, 1), GetRowValue(s, 2), GetRowValue(s, 3), GetRowValue(s, 4), GetRowValue(s, 5)); Console.WriteLine(msg); } wb = _testDataProvider.WriteOutAndReadBack(wb); s = wb.GetSheetAt(0); { var msg = string.Format("3b {0}-{1}-{2}-{3}-{4}-{5}", GetRowValue(s, 0), GetRowValue(s, 1), GetRowValue(s, 2), GetRowValue(s, 3), GetRowValue(s, 4), GetRowValue(s, 5)); Console.WriteLine(msg); } Assert.AreEqual(s.GetRow(0).PhysicalNumberOfCells, 3); Assert.AreEqual(s.GetRow(1).PhysicalNumberOfCells, 4); ConfirmEmptyRow(s, 2); ConfirmEmptyRow(s, 3); Assert.AreEqual(s.GetRow(4).PhysicalNumberOfCells, 5); }
public void TestBool() { IWorkbook wb1 = _testDataProvider.CreateWorkbook(); ISheet s = wb1.CreateSheet("testSheet1"); IRow r; ICell c; // B1 r = s.CreateRow(0); c = r.CreateCell(1); Assert.AreEqual(0, c.RowIndex); Assert.AreEqual(1, c.ColumnIndex); c.SetCellValue(true); Assert.AreEqual(true, c.BooleanCellValue, "B1 value"); // C1 c = r.CreateCell(2); Assert.AreEqual(0, c.RowIndex); Assert.AreEqual(2, c.ColumnIndex); c.SetCellValue(false); Assert.AreEqual(false, c.BooleanCellValue, "C1 value"); // Make sure values are saved and re-read correctly. IWorkbook wb2 = _testDataProvider.WriteOutAndReadBack(wb1); wb1.Close(); s = wb2.GetSheet("testSheet1"); r = s.GetRow(0); Assert.AreEqual(2, r.PhysicalNumberOfCells, "Row 1 should have 2 cells"); c = r.GetCell(1); Assert.AreEqual(0, c.RowIndex); Assert.AreEqual(1, c.ColumnIndex); Assert.AreEqual(CellType.Boolean, c.CellType); Assert.AreEqual(true, c.BooleanCellValue, "B1 value"); c = r.GetCell(2); Assert.AreEqual(0, c.RowIndex); Assert.AreEqual(2, c.ColumnIndex); Assert.AreEqual(CellType.Boolean, c.CellType); Assert.AreEqual(false, c.BooleanCellValue, "C1 value"); wb2.Close(); }
public void TestCreateSheet() { IWorkbook wb = _testDataProvider.CreateWorkbook(); Assert.AreEqual(0, wb.NumberOfSheets); //getting a sheet by invalid index or non-existing name Assert.IsNull(wb.GetSheet("Sheet1")); try { wb.GetSheetAt(0); Assert.Fail("should have thrown exceptiuon due to invalid sheet index"); } catch (ArgumentException ex) { // expected during successful Test // no negative index in the range message Assert.IsFalse(ex.Message.Contains("-1")); } ISheet sheet0 = wb.CreateSheet(); ISheet sheet1 = wb.CreateSheet(); Assert.AreEqual("Sheet0", sheet0.SheetName); Assert.AreEqual("Sheet1", sheet1.SheetName); Assert.AreEqual(2, wb.NumberOfSheets); //fetching sheets by name is case-insensitive ISheet originalSheet = wb.CreateSheet("Sheet3"); ISheet fetchedSheet = wb.GetSheet("sheet3"); if (fetchedSheet == null) { Assert.Fail("Identified bug 44892"); } Assert.AreEqual("Sheet3", fetchedSheet.SheetName); Assert.AreEqual(3, wb.NumberOfSheets); Assert.AreSame(originalSheet, fetchedSheet); try { wb.CreateSheet("sHeeT3"); Assert.Fail("should have thrown exceptiuon due to duplicate sheet name"); } catch (ArgumentException e) { // expected during successful Test Assert.AreEqual("The workbook already contains a sheet named 'sHeeT3'", e.Message); } //names cannot be blank or contain any of /\*?[] String[] invalidNames = { "", "Sheet/", "Sheet\\", "Sheet?", "Sheet*", "Sheet[", "Sheet]", "'Sheet'", "My:Sheet" }; foreach (String sheetName in invalidNames) { try { wb.CreateSheet(sheetName); Assert.Fail("should have thrown exception due to invalid sheet name: " + sheetName); } catch (ArgumentException) { // expected during successful Test } } //still have 3 sheets Assert.AreEqual(3, wb.NumberOfSheets); //change the name of the 3rd sheet wb.SetSheetName(2, "I Changed!"); //try to assign an invalid name to the 2nd sheet try { wb.SetSheetName(1, "[I'm invalid]"); Assert.Fail("should have thrown exceptiuon due to invalid sheet name"); } catch (ArgumentException) { // expected during successful Test } //try to assign an invalid name to the 2nd sheet try { wb.CreateSheet(null); Assert.Fail("should have thrown exceptiuon due to invalid sheet name"); } catch (ArgumentException) { // expected during successful Test } try { wb.SetSheetName(2, null); Assert.Fail("should have thrown exceptiuon due to invalid sheet name"); } catch (ArgumentException) { // expected during successful Test } //check Assert.AreEqual(0, wb.GetSheetIndex("sheet0")); Assert.AreEqual(1, wb.GetSheetIndex("sheet1")); Assert.AreEqual(2, wb.GetSheetIndex("I Changed!")); Assert.AreSame(sheet0, wb.GetSheet("sheet0")); Assert.AreSame(sheet1, wb.GetSheet("sheet1")); Assert.AreSame(originalSheet, wb.GetSheet("I Changed!")); Assert.IsNull(wb.GetSheet("unknown")); //serialize and read again IWorkbook wb2 = _testDataProvider.WriteOutAndReadBack(wb); wb.Close(); Assert.AreEqual(3, wb2.NumberOfSheets); Assert.AreEqual(0, wb2.GetSheetIndex("sheet0")); Assert.AreEqual(1, wb2.GetSheetIndex("sheet1")); Assert.AreEqual(2, wb2.GetSheetIndex("I Changed!")); wb.Close(); }
public void TestRemoveRow() { IWorkbook workbook = _testDataProvider.CreateWorkbook(); ISheet sheet1 = workbook.CreateSheet(); Assert.AreEqual(0, sheet1.PhysicalNumberOfRows); Assert.AreEqual(0, sheet1.FirstRowNum); Assert.AreEqual(0, sheet1.LastRowNum); IRow row0 = sheet1.CreateRow(0); Assert.AreEqual(1, sheet1.PhysicalNumberOfRows); Assert.AreEqual(0, sheet1.FirstRowNum); Assert.AreEqual(0, sheet1.LastRowNum); sheet1.RemoveRow(row0); Assert.AreEqual(0, sheet1.PhysicalNumberOfRows); Assert.AreEqual(0, sheet1.FirstRowNum); Assert.AreEqual(0, sheet1.LastRowNum); sheet1.CreateRow(1); IRow row2 = sheet1.CreateRow(2); Assert.AreEqual(2, sheet1.PhysicalNumberOfRows); Assert.AreEqual(1, sheet1.FirstRowNum); Assert.AreEqual(2, sheet1.LastRowNum); Assert.IsNotNull(sheet1.GetRow(1)); Assert.IsNotNull(sheet1.GetRow(2)); sheet1.RemoveRow(row2); Assert.IsNotNull(sheet1.GetRow(1)); Assert.IsNull(sheet1.GetRow(2)); Assert.AreEqual(1, sheet1.PhysicalNumberOfRows); Assert.AreEqual(1, sheet1.FirstRowNum); Assert.AreEqual(1, sheet1.LastRowNum); IRow row3 = sheet1.CreateRow(3); ISheet sheet2 = workbook.CreateSheet(); try { sheet2.RemoveRow(row3); Assert.Fail("Expected exception"); } catch (ArgumentException e) { Assert.AreEqual("Specified row does not belong to this sheet", e.Message); } IRow row20 = sheet2.CreateRow(0); row20.CreateCell(0).SetCellValue("row20"); IRow row24 = sheet2.CreateRow(4); row24.CreateCell(0).SetCellValue("row24"); sheet2.RemoveRow(row24); workbook = _testDataProvider.WriteOutAndReadBack(workbook); sheet2 = workbook.GetSheetAt(1); Assert.IsNull(sheet2.GetRow(4)); Assert.AreEqual("row20", sheet2.GetRow(0).GetCell(0).StringCellValue); }
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); }