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);
}