/**
* Opens the sheet we wrote out by BinomialOperator and makes sure the formulas
* all Match what we expect (x operator y)
*/
private static void BinomialVerify(string operator1, HSSFWorkbook wb)
{
Npoi.Core.SS.UserModel.ISheet s = wb.GetSheetAt(0);
IRow r = null;
ICell c = null;
//get our minimum values
r = s.GetRow(0);
c = r.GetCell(1);
Assert.IsTrue(("1" + operator1 + "1").Equals(c.CellFormula),
"minval Formula is as expected 1" + operator1 + "1 != " + c.CellFormula);
for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2))
{
r = s.GetRow(x);
for (int y = 1; y < 256 && y > 0; y++)
{
c = r.GetCell(y);
Assert.IsTrue(("" + x + operator1 + y).Equals(c.CellFormula),
"loop Formula is as expected " + x + operator1 + y + "!=" + c.CellFormula
);
}
}
//Test our maximum values
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue(
("" + short.MaxValue + operator1 + short.MaxValue).Equals(c.CellFormula), "maxval Formula is as expected"
);
}
public void TestOnARealFile()
{
HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("DateFormats.xls");
Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
InternalWorkbook wb = workbook.Workbook;
IRow row;
ICell cell;
Npoi.Core.SS.UserModel.ICellStyle style;
double aug_10_2007 = 39304.0;
// Should have dates in 2nd column
// All of them are the 10th of August
// 2 US dates, 3 UK dates
row = sheet.GetRow(0);
cell = row.GetCell(1);
style = cell.CellStyle;
Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001);
Assert.AreEqual("d-mmm-yy", style.GetDataFormatString());
Assert.IsTrue(DateUtil.IsInternalDateFormat(style.DataFormat));
Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString()));
Assert.IsTrue(DateUtil.IsCellDateFormatted(cell));
row = sheet.GetRow(1);
cell = row.GetCell(1);
style = cell.CellStyle;
Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001);
Assert.IsFalse(DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat));
Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString()));
Assert.IsTrue(DateUtil.IsCellDateFormatted(cell));
row = sheet.GetRow(2);
cell = row.GetCell(1);
style = cell.CellStyle;
Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001);
Assert.IsTrue(DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat));
Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString()));
Assert.IsTrue(DateUtil.IsCellDateFormatted(cell));
row = sheet.GetRow(3);
cell = row.GetCell(1);
style = cell.CellStyle;
Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001);
Assert.IsFalse(DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat));
Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString()));
Assert.IsTrue(DateUtil.IsCellDateFormatted(cell));
row = sheet.GetRow(4);
cell = row.GetCell(1);
style = cell.CellStyle;
Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001);
Assert.IsFalse(DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat));
Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString()));
Assert.IsTrue(DateUtil.IsCellDateFormatted(cell));
}
public void Test44410()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SingleLetterRanges.xls");
Npoi.Core.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb);
// =index(C:C,2,1) -> 2
IRow rowIDX = sheet.GetRow(3);
// =sum(C:C) -> 6
IRow rowSUM = sheet.GetRow(4);
// =sum(C:D) -> 66
IRow rowSUM2D = sheet.GetRow(5);
// Test the sum
ICell cellSUM = rowSUM.GetCell(0);
FormulaRecordAggregate frec = (FormulaRecordAggregate)((HSSFCell)cellSUM).CellValueRecord;
Ptg[] ops = frec.FormulaRecord.ParsedExpression;
Assert.AreEqual(2, ops.Length);
Assert.AreEqual(typeof(AreaPtg), ops[0].GetType());
Assert.AreEqual(typeof(FuncVarPtg), ops[1].GetType());
// Actually stored as C1 to C65536
// (last row is -1 === 65535)
AreaPtg ptg = (AreaPtg)ops[0];
Assert.AreEqual(2, ptg.FirstColumn);
Assert.AreEqual(2, ptg.LastColumn);
Assert.AreEqual(0, ptg.FirstRow);
Assert.AreEqual(65535, ptg.LastRow);
Assert.AreEqual("C:C", ptg.ToFormulaString());
// Will show as C:C, but won't know how many
// rows it covers as we don't have the sheet
// to hand when turning the Ptgs into a string
Assert.AreEqual("SUM(C:C)", cellSUM.CellFormula);
// But the evaluator knows the sheet, so it
// can do it properly
Assert.AreEqual(6, eva.Evaluate(cellSUM).NumberValue, 0);
// Test the index
// Again, the formula string will be right but
// lacking row count, Evaluated will be right
ICell cellIDX = rowIDX.GetCell(0);
Assert.AreEqual("INDEX(C:C,2,1)", cellIDX.CellFormula);
Assert.AreEqual(2, eva.Evaluate(cellIDX).NumberValue, 0);
// Across two colums
ICell cellSUM2D = rowSUM2D.GetCell(0);
Assert.AreEqual("SUM(C:D)", cellSUM2D.CellFormula);
Assert.AreEqual(66, eva.Evaluate(cellSUM2D).NumberValue, 0);
}
public void TestStyleNames()
{
HSSFWorkbook wb = OpenSample("WithExtendedStyles.xls");
Npoi.Core.SS.UserModel.ISheet s = wb.GetSheetAt(0);
ICell c1 = s.GetRow(0).GetCell(0);
ICell c2 = s.GetRow(1).GetCell(0);
ICell c3 = s.GetRow(2).GetCell(0);
HSSFCellStyle cs1 = (HSSFCellStyle)c1.CellStyle;
HSSFCellStyle cs2 = (HSSFCellStyle)c2.CellStyle;
HSSFCellStyle cs3 = (HSSFCellStyle)c3.CellStyle;
Assert.IsNotNull(cs1);
Assert.IsNotNull(cs2);
Assert.IsNotNull(cs3);
// Check we got the styles we'd expect
Assert.AreEqual(10, cs1.GetFont(wb).FontHeightInPoints);
Assert.AreEqual(9, cs2.GetFont(wb).FontHeightInPoints);
Assert.AreEqual(12, cs3.GetFont(wb).FontHeightInPoints);
Assert.AreEqual(15, cs1.Index);
Assert.AreEqual(23, cs2.Index);
Assert.AreEqual(24, cs3.Index);
Assert.IsNull(cs1.ParentStyle);
Assert.IsNotNull(cs2.ParentStyle);
Assert.IsNotNull(cs3.ParentStyle);
Assert.AreEqual(21, cs2.ParentStyle.Index);
Assert.AreEqual(22, cs3.ParentStyle.Index);
// Now Check we can get style records for
// the parent ones
Assert.IsNull(wb.Workbook.GetStyleRecord(15));
Assert.IsNull(wb.Workbook.GetStyleRecord(23));
Assert.IsNull(wb.Workbook.GetStyleRecord(24));
Assert.IsNotNull(wb.Workbook.GetStyleRecord(21));
Assert.IsNotNull(wb.Workbook.GetStyleRecord(22));
// Now Check the style names
Assert.AreEqual(null, cs1.UserStyleName);
Assert.AreEqual(null, cs2.UserStyleName);
Assert.AreEqual(null, cs3.UserStyleName);
Assert.AreEqual("style1", cs2.ParentStyle.UserStyleName);
Assert.AreEqual("style2", cs3.ParentStyle.UserStyleName);
// now apply a named style to a new cell
ICell c4 = s.GetRow(0).CreateCell(1);
c4.CellStyle = (cs2);
Assert.AreEqual("style1", ((HSSFCellStyle)c4.CellStyle).ParentStyle.UserStyleName);
}
public void TestReadEmployeeSimple()
{
HSSFWorkbook workbook = OpenSample("Employee.xls");
Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
Assert.AreEqual(EMPLOYEE_INFORMATION, sheet.GetRow(1).GetCell(1).RichStringCellValue.String);
Assert.AreEqual(LAST_NAME_KEY, sheet.GetRow(3).GetCell(2).RichStringCellValue.String);
Assert.AreEqual(FIRST_NAME_KEY, sheet.GetRow(4).GetCell(2).RichStringCellValue.String);
Assert.AreEqual(SSN_KEY, sheet.GetRow(5).GetCell(2).RichStringCellValue.String);
}
public void Test44297()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44297.xls");
IRow row;
ICell cell;
Npoi.Core.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb);
row = sheet.GetRow(0);
cell = row.GetCell(0);
Assert.AreEqual("31+46", cell.CellFormula);
Assert.AreEqual(77, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(1);
cell = row.GetCell(0);
Assert.AreEqual("30+53", cell.CellFormula);
Assert.AreEqual(83, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(2);
cell = row.GetCell(0);
Assert.AreEqual("SUM(A1:A2)", cell.CellFormula);
Assert.AreEqual(160, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(4);
cell = row.GetCell(0);
Assert.AreEqual("32767+32768", cell.CellFormula);
Assert.AreEqual(65535, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(7);
cell = row.GetCell(0);
Assert.AreEqual("32744+42333", cell.CellFormula);
Assert.AreEqual(75077, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(8);
cell = row.GetCell(0);
Assert.AreEqual("327680/32768", cell.CellFormula);
Assert.AreEqual(10, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(9);
cell = row.GetCell(0);
Assert.AreEqual("32767+32769", cell.CellFormula);
Assert.AreEqual(65536, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(10);
cell = row.GetCell(0);
Assert.AreEqual("35000+36000", cell.CellFormula);
Assert.AreEqual(71000, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(11);
cell = row.GetCell(0);
Assert.AreEqual("-1000000-3000000", cell.CellFormula);
Assert.AreEqual(-4000000, eva.Evaluate(cell).NumberValue, 0);
}
public void TestModifySimple()
{
HSSFWorkbook workbook = OpenSample("Simple.xls");
Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
ICell cell = sheet.GetRow(0).GetCell(0);
cell.SetCellValue(new HSSFRichTextString(REPLACED));
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
sheet = workbook.GetSheetAt(0);
cell = sheet.GetRow(0).GetCell(0);
Assert.AreEqual(REPLACED, cell.RichStringCellValue.String);
}
public void TestSheetFunctions()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet("A");
IRow r = null;
ICell c = null;
r = s.CreateRow(0);
c = r.CreateCell(0); c.SetCellValue(1);
c = r.CreateCell(1); c.SetCellValue(2);
s = wb.CreateSheet("B");
r = s.CreateRow(0);
c = r.CreateCell(0); c.CellFormula = ("AVERAGE(A!A1:B1)");
c = r.CreateCell(1); c.CellFormula = ("A!A1+A!B1");
c = r.CreateCell(2); c.CellFormula = ("A!$A$1+A!$B1");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheet("B");
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue(("AVERAGE(A!A1:B1)").Equals(c.CellFormula), "expected: AVERAGE(A!A1:B1) got: " + c.CellFormula);
c = r.GetCell(1);
Assert.IsTrue(("A!A1+A!B1").Equals(c.CellFormula), "expected: A!A1+A!B1 got: " + c.CellFormula);
}
/**
* All multi-binomial operator Tests use this to Create a worksheet with a
* huge set of x operator y formulas. Next we call BinomialVerify and verify
* that they are all how we expect.
*/
private static void BinomialOperator(string operator1)
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
//get our minimum values
r = s.CreateRow(0);
c = r.CreateCell(1);
c.CellFormula = (1 + operator1 + 1);
for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2))
{
r = s.CreateRow(x);
for (int y = 1; y < 256 && y > 0; y++)
{
c = r.CreateCell(y);
c.CellFormula = ("" + x + operator1 + y);
}
}
//make sure we do the maximum value of the Int operator
if (s.LastRowNum < short.MaxValue)
{
r = s.GetRow(0);
c = r.CreateCell(0);
c.CellFormula = ("" + short.MaxValue + operator1 + short.MaxValue);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
BinomialVerify(operator1, wb);
}
/**
* Writes a function then Tests to see if its correct
*
*/
public void RefAreaArrayFunctionTest(string function)
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellFormula = (function + "(A2:A4,B2:B4)");
c = r.CreateCell(1);
c.CellFormula = (function + "($A$2:$A4,B$2:B4)");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue(
(function + "(A2:A4,B2:B4)").Equals(c.CellFormula), "function =" + function + "(A2:A4,B2:B4)"
);
c = r.GetCell(1);
Assert.IsTrue((function + "($A$2:$A4,B$2:B4)").Equals(c.CellFormula),
"function =" + function + "($A$2:$A4,B$2:B4)"
);
}
public void TestAbsRefs()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r;
ICell c;
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellFormula = ("A3+A2");
c = r.CreateCell(1);
c.CellFormula = ("$A3+$A2");
c = r.CreateCell(2);
c.CellFormula = ("A$3+A$2");
c = r.CreateCell(3);
c.CellFormula = ("$A$3+$A$2");
c = r.CreateCell(4);
c.CellFormula = ("SUM($A$3,$A$2)");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue(("A3+A2").Equals(c.CellFormula), "A3+A2");
c = r.GetCell(1);
Assert.IsTrue(("$A3+$A2").Equals(c.CellFormula), "$A3+$A2");
c = r.GetCell(2);
Assert.IsTrue(("A$3+A$2").Equals(c.CellFormula), "A$3+A$2");
c = r.GetCell(3);
Assert.IsTrue(("$A$3+$A$2").Equals(c.CellFormula), "$A$3+$A$2");
c = r.GetCell(4);
Assert.IsTrue(("SUM($A$3,$A$2)").Equals(c.CellFormula), "SUM($A$3,$A$2)");
}
public void TestManyRows()
{
HSSFWorkbook workbook = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet sheet = workbook.CreateSheet();
IRow row;
ICell cell;
int i, j;
for (i = 0, j = 32771; j > 0; i++, j--)
{
row = sheet.CreateRow(i);
cell = row.CreateCell(0);
cell.SetCellValue(i);
}
sanityChecker.CheckHSSFWorkbook(workbook);
Assert.AreEqual(32770, sheet.LastRowNum, "LAST ROW == 32770");
cell = sheet.GetRow(32770).GetCell(0);
double lastVal = cell.NumericCellValue;
HSSFWorkbook wb = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
Npoi.Core.SS.UserModel.ISheet s = wb.GetSheetAt(0);
row = s.GetRow(32770);
cell = row.GetCell(0);
Assert.AreEqual(lastVal, cell.NumericCellValue, 0, "Value from last row == 32770");
Assert.AreEqual(32770, s.LastRowNum, "LAST ROW == 32770");
}
public void TestSquareMacro()
{
HSSFWorkbook w = OpenSample("SquareMacro.xls");
Npoi.Core.SS.UserModel.ISheet s0 = w.GetSheetAt(0);
IRow[] r = { s0.GetRow(0), s0.GetRow(1) };
ICell a1 = r[0].GetCell(0);
Assert.AreEqual("square(1)", a1.CellFormula);
Assert.AreEqual(1d, a1.NumericCellValue, 1e-9);
ICell a2 = r[1].GetCell(0);
Assert.AreEqual("square(2)", a2.CellFormula);
Assert.AreEqual(4d, a2.NumericCellValue, 1e-9);
ICell b1 = r[0].GetCell(1);
Assert.AreEqual("IF(TRUE,square(1))", b1.CellFormula);
Assert.AreEqual(1d, b1.NumericCellValue, 1e-9);
ICell b2 = r[1].GetCell(1);
Assert.AreEqual("IF(TRUE,square(2))", b2.CellFormula);
Assert.AreEqual(4d, b2.NumericCellValue, 1e-9);
ICell c1 = r[0].GetCell(2);
Assert.AreEqual("square(square(1))", c1.CellFormula);
Assert.AreEqual(1d, c1.NumericCellValue, 1e-9);
ICell c2 = r[1].GetCell(2);
Assert.AreEqual("square(square(2))", c2.CellFormula);
Assert.AreEqual(16d, c2.NumericCellValue, 1e-9);
ICell d1 = r[0].GetCell(3);
Assert.AreEqual("square(one())", d1.CellFormula);
Assert.AreEqual(1d, d1.NumericCellValue, 1e-9);
ICell d2 = r[1].GetCell(3);
Assert.AreEqual("square(two())", d2.CellFormula);
Assert.AreEqual(4d, d2.NumericCellValue, 1e-9);
}
private static void ConfirmCell(Npoi.Core.SS.UserModel.ISheet sheet, int rowIx, int colIx,
double expectedValue, String expectedFormula)
{
ICell cell = sheet.GetRow(rowIx).GetCell(colIx);
Assert.AreEqual(expectedValue, cell.NumericCellValue, 0.0);
Assert.AreEqual(expectedFormula, cell.CellFormula);
}
private static void ConfirmResolveCellRef(HSSFWorkbook wb, CellReference cref)
{
Npoi.Core.SS.UserModel.ISheet s = wb.GetSheet(cref.SheetName);
IRow r = s.GetRow(cref.Row);
ICell c = r.GetCell((int)cref.Col);
Assert.IsNotNull(c);
}
private static DateTime ReadCell(HSSFWorkbook workbook, int rowIdx, int colIdx)
{
Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
IRow row = sheet.GetRow(rowIdx);
ICell cell = row.GetCell(colIdx);
return(cell.DateCellValue);
}
public void TestShiftRows()
{
// Read initial file in
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleMultiCell.xls");
Npoi.Core.SS.UserModel.ISheet s = wb.GetSheetAt(0);
// Shift the second row down 1 and Write to temp file
s.ShiftRows(1, 1, 1);
wb = HSSFTestDataSamples.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);
Assert.AreEqual(1, s.GetRow(0).PhysicalNumberOfCells);
ConfirmEmptyRow(s, 1);
Assert.AreEqual(2, s.GetRow(2).PhysicalNumberOfCells);
Assert.AreEqual(4, s.GetRow(3).PhysicalNumberOfCells);
Assert.AreEqual(5, s.GetRow(4).PhysicalNumberOfCells);
// 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);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
// Read and ensure things are where they should be
s = wb.GetSheetAt(0);
ConfirmEmptyRow(s, 0);
ConfirmEmptyRow(s, 1);
ConfirmEmptyRow(s, 2);
Assert.AreEqual(1, s.GetRow(3).PhysicalNumberOfCells);
ConfirmEmptyRow(s, 4);
Assert.AreEqual(2, s.GetRow(5).PhysicalNumberOfCells);
// Read the first file again
wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleMultiCell.xls");
s = wb.GetSheetAt(0);
// Shift rows 3 and 4 up and Write to temp file
s.ShiftRows(2, 3, -2);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
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 TestShiftWithFormulas()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("ForShifting.xls");
Npoi.Core.SS.UserModel.ISheet sheet = wb.GetSheet("Sheet1");
Assert.AreEqual(20, sheet.LastRowNum);
ConfirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
ConfirmRow(sheet, 1, 2, 172, 1, "ROW(D2)", "100+B2", "COUNT(D2:E2)");
ConfirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");
ConfirmCell(sheet, 6, 1, 271, "200+B1");
ConfirmCell(sheet, 7, 1, 272, "200+B2");
ConfirmCell(sheet, 8, 1, 273, "200+B3");
ConfirmCell(sheet, 14, 0, 0.0, "A12"); // the cell referred to by this formula will be replaced
// -----------
// Row index 1 -> 11 (row "2" -> row "12")
sheet.ShiftRows(1, 1, 10);
// Now Check what sheet looks like after move
// no changes on row "1"
ConfirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
// row "2" is now empty
Assert.AreEqual(0, sheet.GetRow(1).PhysicalNumberOfCells);
// Row "2" moved to row "12", and the formula has been updated.
// note however that the cached formula result (2) has not been updated. (POI differs from Excel here)
ConfirmRow(sheet, 11, 2, 172, 1, "ROW(D12)", "100+B12", "COUNT(D12:E12)");
// no changes on row "3"
ConfirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");
ConfirmCell(sheet, 14, 0, 0.0, "#REF!");
// Formulas on rows that weren't shifted:
ConfirmCell(sheet, 6, 1, 271, "200+B1");
ConfirmCell(sheet, 7, 1, 272, "200+B12"); // this one moved
ConfirmCell(sheet, 8, 1, 273, "200+B3");
// Check formulas on other sheets
Npoi.Core.SS.UserModel.ISheet sheet2 = wb.GetSheet("Sheet2");
ConfirmCell(sheet2, 0, 0, 371, "300+Sheet1!B1");
ConfirmCell(sheet2, 1, 0, 372, "300+Sheet1!B12");
ConfirmCell(sheet2, 2, 0, 373, "300+Sheet1!B3");
ConfirmCell(sheet2, 11, 0, 300, "300+Sheet1!#REF!");
// Note - named ranges formulas have not been updated
}
public void TestCreateDocumentLink()
{
HSSFWorkbook wb = new HSSFWorkbook();
//link to a place in this workbook
IHyperlink link;
ICell cell;
Npoi.Core.SS.UserModel.ISheet sheet = wb.CreateSheet("Hyperlinks");
//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 A1 has a link to 'Target Sheet-1'!A1
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.Document);
link.TextMark = ("'Target Sheet'!A1");
cell.Hyperlink = (link);
//cell B1 has a link to cell A1 on the same sheet
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.Document);
link.Address = ("'Hyperlinks'!A1");
cell.Hyperlink = (link);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheet("Hyperlinks");
cell = sheet.GetRow(0).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("'Target Sheet'!A1", link.TextMark);
Assert.AreEqual("'Target Sheet'!A1", link.Address);
cell = sheet.GetRow(1).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("'Hyperlinks'!A1", link.TextMark);
Assert.AreEqual("'Hyperlinks'!A1", link.Address);
}
public void TestReadSimple()
{
HSSFWorkbook workbook = OpenSample("Simple.xls");
Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
ICell cell = sheet.GetRow(0).GetCell(0);
Assert.AreEqual(REPLACE_ME, cell.RichStringCellValue.String);
}
public void TestDateWindowingRead()
{
DateTime date = new DateTime(2000, 1, 1);
// first Check a file with 1900 Date Windowing
HSSFWorkbook workbook = OpenSample("1900DateWindowing.xls");
Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
Assert.AreEqual(date, sheet.GetRow(0).GetCell(0).DateCellValue,
"Date from file using 1900 Date Windowing");
// now Check a file with 1904 Date Windowing
workbook = OpenSample("1904DateWindowing.xls");
sheet = workbook.GetSheetAt(0);
Assert.AreEqual(date, sheet.GetRow(0).GetCell(0).DateCellValue,
"Date from file using 1904 Date Windowing");
}
private static void setValue(Npoi.Core.SS.UserModel.ISheet sheet, int rowIndex, int colIndex, double value)
{
IRow row = sheet.GetRow(rowIndex);
if (row == null)
{
row = sheet.CreateRow(rowIndex);
}
row.CreateCell(colIndex).SetCellValue(value);
}
public void TestCloneSheet()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("HyperlinksOnManySheets.xls");
ICell cell;
IHyperlink link;
Npoi.Core.SS.UserModel.ISheet sheet = wb.CloneSheet(0);
cell = sheet.GetRow(4).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("http://poi.apache.org/", link.Address);
cell = sheet.GetRow(8).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("http://poi.apache.org/hssf/", link.Address);
}
public void TestWriteModifySheetSimple()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetSimple",
".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
for (int rownum = 0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
for (int cellnum = 0; cellnum < 50; cellnum += 2)
{
c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ (((double)rownum / 1000)
+ ((double)cellnum / 10000)));
c = r.CreateCell(cellnum + 1);
c.SetCellValue(new HSSFRichTextString("TEST"));
}
}
for (int rownum = 0; rownum < 25; rownum++)
{
r = s.GetRow(rownum);
s.RemoveRow(r);
}
for (int rownum = 75; rownum < 100; rownum++)
{
r = s.GetRow(rownum);
s.RemoveRow(r);
}
wb.Write(out1);
out1.Close();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(74, s.LastRowNum, "LAST ROW == 74");
Assert.AreEqual(25, s.FirstRowNum, "FIRST ROW == 25");
}
public void TestSumIf()
{
string function = "SUMIF(A1:A5,\">4000\",B1:B5)";
HSSFWorkbook wb = OpenSample("sumifformula.xls");
Npoi.Core.SS.UserModel.ISheet s = wb.GetSheetAt(0);
IRow r = s.GetRow(0);
ICell c = r.GetCell(2);
Assert.AreEqual(function, c.CellFormula);
wb = new HSSFWorkbook();
s = wb.CreateSheet();
r = s.CreateRow(0);
c = r.CreateCell(0); c.SetCellValue(1000);
c = r.CreateCell(1); c.SetCellValue(1);
r = s.CreateRow(1);
c = r.CreateCell(0); c.SetCellValue(2000);
c = r.CreateCell(1); c.SetCellValue(2);
r = s.CreateRow(2);
c = r.CreateCell(0); c.SetCellValue(3000);
c = r.CreateCell(1); c.SetCellValue(3);
r = s.CreateRow(3);
c = r.CreateCell(0); c.SetCellValue(4000);
c = r.CreateCell(1); c.SetCellValue(4);
r = s.CreateRow(4);
c = r.CreateCell(0); c.SetCellValue(5000);
c = r.CreateCell(1); c.SetCellValue(5);
r = s.GetRow(0);
c = r.CreateCell(2); c.CellFormula = (function);
HSSFTestDataSamples.WriteOutAndReadBack(wb);
}
public void TestReadSheetWithRK()
{
HSSFWorkbook h = OpenSample("rk.xls");
Npoi.Core.SS.UserModel.ISheet s = h.GetSheetAt(0);
ICell c = s.GetRow(0).GetCell(0);
Npoi.Core.SS.UserModel.CellType a = c.CellType;
Assert.AreEqual(a, Npoi.Core.SS.UserModel.CellType.Numeric);
}
/// <summary>
/// Gets the row height in points.
/// </summary>
/// <param name="sheet">The sheet.</param>
/// <param name="rowNum">The row num.</param>
/// <returns></returns>
private float GetRowHeightInPoints(Npoi.Core.SS.UserModel.ISheet sheet, int rowNum)
{
Npoi.Core.SS.UserModel.IRow row = sheet.GetRow(rowNum);
if (row == null)
{
return(sheet.DefaultRowHeightInPoints);
}
else
{
return(row.HeightInPoints);
}
}
public void TestReadSimpleWithDataFormat()
{
HSSFWorkbook workbook = OpenSample("SimpleWithDataFormat.xls");
Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
IDataFormat format = workbook.CreateDataFormat();
ICell cell = sheet.GetRow(0).GetCell(0);
Assert.AreEqual(1.25, cell.NumericCellValue, 1e-10);
Assert.AreEqual(format.GetFormat(cell.CellStyle.DataFormat), "0.0");
}
public void TestEvaluateSimple()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("TestNames.xls");
Npoi.Core.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
ICell cell = sheet.GetRow(8).GetCell(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
Npoi.Core.SS.UserModel.CellValue cv = fe.Evaluate(cell);
Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Numeric, cv.CellType);
Assert.AreEqual(3.72, cv.NumberValue, 0.0);
}
private static void SetCell(HSSFWorkbook workbook, int rowIdx, int colIdx, DateTime date)
{
Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
IRow row = sheet.GetRow(rowIdx);
ICell cell = row.GetCell(colIdx);
if (cell == null)
{
cell = row.CreateCell(colIdx);
}
cell.SetCellValue(date);
}
