public void TestShiftWithComments()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("comments.xls");
Npoi.Core.SS.UserModel.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);
MemoryStream outputStream = new MemoryStream();
wb.Write(outputStream);
// 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 = new HSSFWorkbook(new MemoryStream(outputStream.ToArray()));
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);
}
private static void ConfirmEmptyRow(Npoi.Core.SS.UserModel.ISheet s, int rowIx)
{
IRow row = s.GetRow(rowIx);
Assert.IsTrue(row == null || row.PhysicalNumberOfCells == 0);
}
public void TestEvaluateAll()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s1 = wb.CreateSheet();
Npoi.Core.SS.UserModel.ISheet s2 = wb.CreateSheet();
wb.SetSheetName(0, "S1");
wb.SetSheetName(1, "S2");
IRow s1r1 = s1.CreateRow(0);
IRow s1r2 = s1.CreateRow(1);
IRow s2r1 = s2.CreateRow(0);
ICell s1r1c1 = s1r1.CreateCell(0);
ICell s1r1c2 = s1r1.CreateCell(1);
ICell s1r1c3 = s1r1.CreateCell(2);
s1r1c1.SetCellValue(22.3);
s1r1c2.SetCellValue(33.4);
s1r1c3.CellFormula = ("SUM(A1:B1)");
ICell s1r2c1 = s1r2.CreateCell(0);
ICell s1r2c2 = s1r2.CreateCell(1);
ICell s1r2c3 = s1r2.CreateCell(2);
s1r2c1.SetCellValue(-1.2);
s1r2c2.SetCellValue(-3.4);
s1r2c3.CellFormula = ("SUM(A2:B2)");
ICell s2r1c1 = s2r1.CreateCell(0);
s2r1c1.CellFormula = ("S1!A1");
// Not Evaluated yet
Assert.AreEqual(0.0, s1r1c3.NumericCellValue, 0);
Assert.AreEqual(0.0, s1r2c3.NumericCellValue, 0);
Assert.AreEqual(0.0, s2r1c1.NumericCellValue, 0);
// Do a full Evaluate, as per our docs
// uses EvaluateFormulaCell()
for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
{
Npoi.Core.SS.UserModel.ISheet sheet = wb.GetSheetAt(sheetNum);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext();)
{
IRow r = (IRow)rit.Current;
for (IEnumerator cit = r.GetEnumerator(); cit.MoveNext();)
{
ICell c = (ICell)cit.Current;
if (c.CellType == Npoi.Core.SS.UserModel.CellType.Formula)
{
evaluator.EvaluateFormulaCell(c);
// For Testing - all should be numeric
Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Numeric, evaluator.EvaluateFormulaCell(c));
}
}
}
}
// Check now as expected
Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
Assert.AreEqual("SUM(A1:B1)", wb.GetSheetAt(0).GetRow(0).GetCell(2).CellFormula);
Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Formula, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);
Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
Assert.AreEqual("SUM(A2:B2)", wb.GetSheetAt(0).GetRow(1).GetCell(2).CellFormula);
Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Formula, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);
Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0);
Assert.AreEqual("'S1'!A1", wb.GetSheetAt(1).GetRow(0).GetCell(0).CellFormula);
Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Formula, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType);
// Now do the alternate call, which zaps the formulas
// uses EvaluateInCell()
for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
{
Npoi.Core.SS.UserModel.ISheet sheet = wb.GetSheetAt(sheetNum);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext();)
{
IRow r = (IRow)rit.Current;
for (IEnumerator cit = r.GetEnumerator(); cit.MoveNext();)
{
ICell c = (ICell)cit.Current;
if (c.CellType == Npoi.Core.SS.UserModel.CellType.Formula)
{
evaluator.EvaluateInCell(c);
}
}
}
}
Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Numeric, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);
Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Numeric, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);
Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0);
Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Numeric, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType);
}
public void TestCreate()
{
HSSFWorkbook wb = new HSSFWorkbook();
ICell cell;
Npoi.Core.SS.UserModel.ISheet sheet = wb.CreateSheet("Hyperlinks");
//URL
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
IHyperlink link = new HSSFHyperlink(HyperlinkType.Url);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = link;
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new HSSFHyperlink(HyperlinkType.File);
link.Address = ("link1.xls");
cell.Hyperlink = link;
//e-mail link
cell = sheet.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new HSSFHyperlink(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
Npoi.Core.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Target Sheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target Cell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.Document);
link.TextMark = ("'Target Sheet'!A1");
cell.Hyperlink = link;
//serialize and read again
MemoryStream out1 = new MemoryStream();
wb.Write(out1);
wb = new HSSFWorkbook(new MemoryStream(out1.ToArray()));
sheet = wb.GetSheet("Hyperlinks");
cell = sheet.GetRow(0).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("http://poi.apache.org/", link.Address);
cell = sheet.GetRow(1).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("link1.xls", link.Address);
cell = sheet.GetRow(2).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("mailto:[email protected]?subject=Hyperlinks", link.Address);
cell = sheet.GetRow(3).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("'Target Sheet'!A1", link.TextMark);
}