private static void ConfirmResult(HSSFFormulaEvaluator fe, ICell cell, String formulaText, String expectedResult) { cell.CellFormula=(formulaText); fe.NotifyUpdateCell(cell); CellValue result = fe.Evaluate(cell); Assert.AreEqual(result.CellType, CellType.STRING); Assert.AreEqual(expectedResult, result.StringValue); }
private static void ConfirmResult(HSSFFormulaEvaluator fe, ICell cell, String formulaText, int expectedResult) { cell.CellFormula=(formulaText); fe.NotifyUpdateCell(cell); CellValue result = fe.Evaluate(cell); Assert.AreEqual(result.CellType, CellType.NUMERIC); Assert.AreEqual(expectedResult, result.NumberValue, 0.0); }
private static void ConfirmError(HSSFFormulaEvaluator fe, ICell cell, String formulaText, int expectedErrorCode) { cell.CellFormula=(formulaText); fe.NotifyUpdateCell(cell); CellValue result = fe.Evaluate(cell); Assert.AreEqual(result.CellType, CellType.ERROR); Assert.AreEqual(expectedErrorCode, result.ErrorValue); }
public void TestRangeUsingOffsetFunc_bug46948() { HSSFWorkbook wb = new HSSFWorkbook(); IRow row = wb.CreateSheet("Sheet1").CreateRow(0); ICell cellA1 = row.CreateCell(0); ICell cellB1 = row.CreateCell(1); row.CreateCell(2).SetCellValue(5.0); // C1 row.CreateCell(3).SetCellValue(7.0); // D1 row.CreateCell(4).SetCellValue(9.0); // E1 cellA1.CellFormula = ("SUM(C1:OFFSET(C1,0,B1))"); cellB1.SetCellValue(1.0); // range will be C1:D1 HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); CellValue cv; try { cv = fe.Evaluate(cellA1); } catch (ArgumentException e) { if (e.Message.Equals("Unexpected ref arg class (NPOI.SS.Formula.LazyAreaEval)")) { throw new AssertionException("Identified bug 46948"); } throw e; } Assert.AreEqual(12.0, cv.NumberValue, 0.0); cellB1.SetCellValue(2.0); // range will be C1:E1 fe.NotifyUpdateCell(cellB1); cv = fe.Evaluate(cellA1); Assert.AreEqual(21.0, cv.NumberValue, 0.0); cellB1.SetCellValue(0.0); // range will be C1:C1 fe.NotifyUpdateCell(cellB1); cv = fe.Evaluate(cellA1); Assert.AreEqual(5.0, cv.NumberValue, 0.0); }
public void TestEvaluateMultipleWorkbooks() { HSSFWorkbook wbA = HSSFTestDataSamples.OpenSampleWorkbook("multibookFormulaA.xls"); HSSFWorkbook wbB = HSSFTestDataSamples.OpenSampleWorkbook("multibookFormulaB.xls"); HSSFFormulaEvaluator EvaluatorA = new HSSFFormulaEvaluator(wbA); HSSFFormulaEvaluator EvaluatorB = new HSSFFormulaEvaluator(wbB); // Hook up the workbook Evaluators to enable Evaluation of formulas across books String[] bookNames = { "multibookFormulaA.xls", "multibookFormulaB.xls", }; HSSFFormulaEvaluator[] Evaluators = { EvaluatorA, EvaluatorB, }; HSSFFormulaEvaluator.SetupEnvironment(bookNames, Evaluators); ISheet aSheet1 = wbA.GetSheetAt(0); ISheet bSheet1 = wbB.GetSheetAt(0); // Simple case - single link from wbA to wbB ConfirmFormula(wbA, 0, 0, 0, "[multibookFormulaB.xls]BSheet1!B1"); ICell cell = aSheet1.GetRow(0).GetCell(0); ConfirmEvaluation(35, EvaluatorA, cell); // more complex case - back link into wbA // [wbA]ASheet1!A2 references (among other things) [wbB]BSheet1!B2 ConfirmFormula(wbA, 0, 1, 0, "[multibookFormulaB.xls]BSheet1!$B$2+2*A3"); // [wbB]BSheet1!B2 references (among other things) [wbA]AnotherSheet!A1:B2 ConfirmFormula(wbB, 0, 1, 1, "SUM([multibookFormulaA.xls]AnotherSheet!$A$1:$B$2)+B3"); cell = aSheet1.GetRow(1).GetCell(0); ConfirmEvaluation(264, EvaluatorA, cell); // change [wbB]BSheet1!B3 (from 50 to 60) ICell cellB3 = bSheet1.GetRow(2).GetCell(1); cellB3.SetCellValue(60); EvaluatorB.NotifyUpdateCell(cellB3); ConfirmEvaluation(274, EvaluatorA, cell); // change [wbA]ASheet1!A3 (from 100 to 80) ICell cellA3 = aSheet1.GetRow(2).GetCell(0); cellA3.SetCellValue(80); EvaluatorA.NotifyUpdateCell(cellA3); ConfirmEvaluation(234, EvaluatorA, cell); // change [wbA]AnotherSheet!A1 (from 2 to 3) ICell cellA1 = wbA.GetSheetAt(1).GetRow(0).GetCell(0); cellA1.SetCellValue(3); EvaluatorA.NotifyUpdateCell(cellA1); ConfirmEvaluation(235, EvaluatorA, cell); }
public void TestDateWithNegativeParts_bug48528() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("Sheet1"); HSSFRow row = (HSSFRow)sheet.CreateRow(1); HSSFCell cell = (HSSFCell)row.CreateCell(0); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); // 5th Feb 2012 = 40944 // 1st Feb 2012 = 40940 // 5th Jan 2012 = 40913 // 5th Dec 2011 = 40882 // 5th Feb 2011 = 40579 cell.CellFormula=("DATE(2012,2,1)"); fe.NotifyUpdateCell(cell); Assert.AreEqual(40940.0, fe.Evaluate(cell).NumberValue); cell.CellFormula=("DATE(2012,2,1+4)"); fe.NotifyUpdateCell(cell); Assert.AreEqual(40944.0, fe.Evaluate(cell).NumberValue); cell.CellFormula=("DATE(2012,2-1,1+4)"); fe.NotifyUpdateCell(cell); Assert.AreEqual(40913.0, fe.Evaluate(cell).NumberValue); cell.CellFormula=("DATE(2012,2,1-27)"); fe.NotifyUpdateCell(cell); Assert.AreEqual(40913.0, fe.Evaluate(cell).NumberValue); cell.CellFormula=("DATE(2012,2-2,1+4)"); fe.NotifyUpdateCell(cell); Assert.AreEqual(40882.0, fe.Evaluate(cell).NumberValue); cell.CellFormula=("DATE(2012,2,1-58)"); fe.NotifyUpdateCell(cell); Assert.AreEqual(40882.0, fe.Evaluate(cell).NumberValue); cell.CellFormula=("DATE(2012,2-12,1+4)"); fe.NotifyUpdateCell(cell); Assert.AreEqual(40579.0, fe.Evaluate(cell).NumberValue); }