public void TestEvaluateInSheetExample2() { HSSFWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet("Sheet1"); IRow row = sheet.CreateRow(0); sheet.CreateRow(1).CreateCell(0).SetCellValue(0.08d); sheet.CreateRow(2).CreateCell(0).SetCellValue(-40000d); sheet.CreateRow(3).CreateCell(0).SetCellValue(8000d); sheet.CreateRow(4).CreateCell(0).SetCellValue(9200d); sheet.CreateRow(5).CreateCell(0).SetCellValue(10000d); sheet.CreateRow(6).CreateCell(0).SetCellValue(12000d); sheet.CreateRow(7).CreateCell(0).SetCellValue(14500d); ICell cell = row.CreateCell(8); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); // Enumeration cell.CellFormula = ("NPV(A2, A4,A5,A6,A7,A8)+A3"); fe.ClearAllCachedResultValues(); fe.EvaluateFormulaCell(cell); double res = cell.NumericCellValue; Assert.AreEqual(1922.06d, Math.Round(res * 100d) / 100d); // Range cell.CellFormula = ("NPV(A2, A4:A8)+A3"); fe.ClearAllCachedResultValues(); fe.EvaluateFormulaCell(cell); res = cell.NumericCellValue; Assert.AreEqual(1922.06d, Math.Round(res * 100d) / 100d); }
public void TestExistingWorkbook() { HSSFSheet lSheet = (HSSFSheet)mainWorkbook.GetSheetAt(0); HSSFCell lA1Cell = (HSSFCell)lSheet.GetRow(0).GetCell(0); HSSFCell lB1Cell = (HSSFCell)lSheet.GetRow(1).GetCell(0); HSSFCell lC1Cell = (HSSFCell)lSheet.GetRow(2).GetCell(0); Assert.AreEqual(CellType.Formula, lA1Cell.CellType); Assert.AreEqual(CellType.Formula, lB1Cell.CellType); Assert.AreEqual(CellType.Formula, lC1Cell.CellType); HSSFFormulaEvaluator lMainWorkbookEvaluator = new HSSFFormulaEvaluator(mainWorkbook); HSSFFormulaEvaluator lSourceEvaluator = new HSSFFormulaEvaluator(sourceWorkbook); HSSFFormulaEvaluator.SetupEnvironment( new String[] { MAIN_WORKBOOK_FILENAME, SOURCE_DUMMY_WORKBOOK_FILENAME }, new HSSFFormulaEvaluator[] { lMainWorkbookEvaluator, lSourceEvaluator }); Assert.AreEqual(CellType.Numeric, lMainWorkbookEvaluator.EvaluateFormulaCell(lA1Cell)); Assert.AreEqual(CellType.String, lMainWorkbookEvaluator.EvaluateFormulaCell(lB1Cell)); Assert.AreEqual(CellType.Boolean, lMainWorkbookEvaluator.EvaluateFormulaCell(lC1Cell)); Assert.AreEqual(20.0d, lA1Cell.NumericCellValue, 0.00001d); Assert.AreEqual("Apache rocks!", lB1Cell.StringCellValue); Assert.AreEqual(false, lC1Cell.BooleanCellValue); }
public void TestEvaluate() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls"); HSSFWorkbook wb2 = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalcData.xls"); CellReference cellRef = new CellReference(wb.GetName("QUANT").RefersToFormula); ICell cell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col); cell.SetCellValue(NEW_QUANT); cell = wb2.GetSheet("CostSheet").GetRow(1).GetCell(1); cell.SetCellValue(NEW_PART_COST); HSSFFormulaEvaluator Evaluator = new HSSFFormulaEvaluator(wb); HSSFFormulaEvaluator EvaluatorCost = new HSSFFormulaEvaluator(wb2); String[] bookNames = { "XRefCalc.xls", "XRefCalcData.xls" }; HSSFFormulaEvaluator[] Evaluators = { Evaluator, EvaluatorCost, }; HSSFFormulaEvaluator.SetupEnvironment(bookNames, Evaluators); cellRef = new CellReference(wb.GetName("UNITCOST").RefersToFormula); ICell uccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col); cellRef = new CellReference(wb.GetName("COST").RefersToFormula); ICell ccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col); cellRef = new CellReference(wb.GetName("TOTALCOST").RefersToFormula); ICell tccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col); Evaluator.EvaluateFormulaCell(uccell); Evaluator.EvaluateFormulaCell(ccell); Evaluator.EvaluateFormulaCell(tccell); Assert.AreEqual(NEW_PART_COST, uccell.NumericCellValue); Assert.AreEqual(NEW_PART_COST * NEW_QUANT, ccell.NumericCellValue); Assert.AreEqual(NEW_PART_COST * NEW_QUANT * MARKUP_COST_2, tccell.NumericCellValue); }
public void TestEvaluate_bug_45732() { HSSFWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet("Sheet1"); ICell cell = sheet.CreateRow(0).CreateCell(0); cell.CellFormula = ("NPER(12,4500,100000,100000)"); cell.SetCellValue(15.0); Assert.AreEqual("NPER(12,4500,100000,100000)", cell.CellFormula); Assert.AreEqual(CellType.NUMERIC, cell.CachedFormulaResultType); Assert.AreEqual(15.0, cell.NumericCellValue, 0.0); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); fe.EvaluateFormulaCell(cell); Assert.AreEqual(CellType.ERROR, cell.CachedFormulaResultType); Assert.AreEqual(HSSFErrorConstants.ERROR_NUM, cell.ErrorCellValue); }
public void TestEvaluateInSheet() { HSSFWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet("Sheet1"); IRow row = sheet.CreateRow(0); row.CreateCell(0).SetCellValue(-4000d); row.CreateCell(1).SetCellValue(1200d); row.CreateCell(2).SetCellValue(1410d); row.CreateCell(3).SetCellValue(1875d); row.CreateCell(4).SetCellValue(1050d); ICell cell = row.CreateCell(5); cell.CellFormula = ("IRR(A1:E1)"); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); fe.ClearAllCachedResultValues(); fe.EvaluateFormulaCell(cell); double res = cell.NumericCellValue; Assert.AreEqual(0.143d, Math.Round(res * 1000d) / 1000d); }
/** * Loops over all cells in all sheets of the supplied * workbook. * For cells that contain formulas, their formulas are * Evaluated, and the results are saved. These cells * remain as formula cells. * For cells that do not contain formulas, no Changes * are made. * This is a helpful wrapper around looping over all * cells, and calling EvaluateFormulaCell on each one. */ public static void EvaluateAllFormulaCells(HSSFWorkbook wb) { for (int i = 0; i < wb.NumberOfSheets; i++) { NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(i); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb); for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext();) { HSSFRow r = (HSSFRow)rit.Current; //evaluator.SetCurrentRow(r); for (IEnumerator cit = r.GetCellEnumerator(); cit.MoveNext();) { NPOI.SS.UserModel.Cell c = (HSSFCell)cit.Current; if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA) { evaluator.EvaluateFormulaCell(c); } } } } }
public void TestDummyToSource() { bool changed = mainWorkbook.ChangeExternalReference("DOESNOTEXIST", SOURCE_WORKBOOK_FILENAME); Assert.IsFalse(changed); changed = mainWorkbook.ChangeExternalReference(SOURCE_DUMMY_WORKBOOK_FILENAME, SOURCE_WORKBOOK_FILENAME); Assert.IsTrue(changed); HSSFSheet lSheet = (HSSFSheet)mainWorkbook.GetSheetAt(0); HSSFCell lA1Cell = (HSSFCell)lSheet.GetRow(0).GetCell(0); Assert.AreEqual(CellType.FORMULA, lA1Cell.CellType); HSSFFormulaEvaluator lMainWorkbookEvaluator = new HSSFFormulaEvaluator(mainWorkbook); HSSFFormulaEvaluator lSourceEvaluator = new HSSFFormulaEvaluator(sourceWorkbook); HSSFFormulaEvaluator.SetupEnvironment( new String[] { MAIN_WORKBOOK_FILENAME, SOURCE_WORKBOOK_FILENAME }, new HSSFFormulaEvaluator[] { lMainWorkbookEvaluator, lSourceEvaluator }); Assert.AreEqual(CellType.NUMERIC, lMainWorkbookEvaluator.EvaluateFormulaCell(lA1Cell)); Assert.AreEqual(20.0d, lA1Cell.NumericCellValue, 0.00001d); }
public void TestZipCodeFormulas() { HSSFWorkbook wb = new HSSFWorkbook(); ISheet s = wb.CreateSheet(); s.CreateRow(0); ICell c1 = s.GetRow(0).CreateCell(0); ICell c2 = s.GetRow(0).CreateCell(1); ICell c3 = s.GetRow(0).CreateCell(2); // As number and string c1.CellFormula = ("70164"); c2.CellFormula = ("\"70164\""); c3.CellFormula = ("\"90210\""); // Check the formulas Assert.AreEqual("70164", c1.CellFormula); Assert.AreEqual("\"70164\"", c2.CellFormula); // And Check the values - blank ConfirmCachedValue(0.0, c1); ConfirmCachedValue(0.0, c2); ConfirmCachedValue(0.0, c3); // Try changing the cached value on one of the string // formula cells, so we can see it updates properly c3.SetCellValue(new HSSFRichTextString("Test")); ConfirmCachedValue("Test", c3); try { double a = c3.NumericCellValue; throw new AssertionException("exception should have been thrown"); } catch (InvalidOperationException e) { Assert.AreEqual("Cannot get a numeric value from a text formula cell", e.Message); } // Now Evaluate, they should all be changed HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb); eval.EvaluateFormulaCell(c1); eval.EvaluateFormulaCell(c2); eval.EvaluateFormulaCell(c3); // Check that the cells now contain // the correct values ConfirmCachedValue(70164.0, c1); ConfirmCachedValue("70164", c2); ConfirmCachedValue("90210", c3); // Write and read HSSFWorkbook nwb = WriteOutAndReadBack(wb); HSSFSheet ns = (HSSFSheet)nwb.GetSheetAt(0); ICell nc1 = ns.GetRow(0).GetCell(0); ICell nc2 = ns.GetRow(0).GetCell(1); ICell nc3 = ns.GetRow(0).GetCell(2); // Re-Check ConfirmCachedValue(70164.0, nc1); ConfirmCachedValue("70164", nc2); ConfirmCachedValue("90210", nc3); CellValueRecordInterface[] cvrs = ns.Sheet.GetValueRecords(); for (int i = 0; i < cvrs.Length; i++) { CellValueRecordInterface cvr = cvrs[i]; if (cvr is FormulaRecordAggregate) { FormulaRecordAggregate fr = (FormulaRecordAggregate)cvr; if (i == 0) { Assert.AreEqual(70164.0, fr.FormulaRecord.Value, 0.0001); Assert.IsNull(fr.StringRecord); } else if (i == 1) { Assert.AreEqual(0.0, fr.FormulaRecord.Value, 0.0001); Assert.IsNotNull(fr.StringRecord); Assert.AreEqual("70164", fr.StringRecord.String); } else { Assert.AreEqual(0.0, fr.FormulaRecord.Value, 0.0001); Assert.IsNotNull(fr.StringRecord); Assert.AreEqual("90210", fr.StringRecord.String); } } } Assert.AreEqual(3, cvrs.Length); }
public void TestXRefs() { IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls"); IWorkbook wbData = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalcData.xls"); ICell cell; // VLookup on a name in another file cell = wb.GetSheetAt(0).GetRow(1).GetCell(2); Assert.AreEqual(CellType.Formula, cell.CellType); Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType); Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001); // WARNING - this is wrong! // The file name should be Showing, but bug #45970 is fixed // we seem to loose it Assert.AreEqual("VLOOKUP(PART,COSTS,2,FALSE)", cell.CellFormula); // Simple reference to a name in another file cell = wb.GetSheetAt(0).GetRow(1).GetCell(4); Assert.AreEqual(CellType.Formula, cell.CellType); Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType); Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001); // WARNING - this is wrong! // The file name should be Showing, but bug #45970 is fixed // we seem to loose it Assert.AreEqual("Cost*Markup_Cost", cell.CellFormula); // Evaluate the cells HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb); HSSFFormulaEvaluator.SetupEnvironment( new String[] { "XRefCalc.xls", "XRefCalcData.xls" }, new HSSFFormulaEvaluator[] { eval, new HSSFFormulaEvaluator(wbData) } ); eval.EvaluateFormulaCell( wb.GetSheetAt(0).GetRow(1).GetCell(2) ); eval.EvaluateFormulaCell( wb.GetSheetAt(0).GetRow(1).GetCell(4) ); // Re-check VLOOKUP one cell = wb.GetSheetAt(0).GetRow(1).GetCell(2); Assert.AreEqual(CellType.Formula, cell.CellType); Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType); Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001); // Re-check ref one cell = wb.GetSheetAt(0).GetRow(1).GetCell(4); Assert.AreEqual(CellType.Formula, cell.CellType); Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType); Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001); // Add a formula that refers to one of the existing external workbooks cell = wb.GetSheetAt(0).GetRow(1).CreateCell(40); cell.CellFormula = (/*setter*/"Cost*[XRefCalcData.xls]MarkupSheet!$B$1"); // Check is was stored correctly Assert.AreEqual("Cost*[XRefCalcData.xls]MarkupSheet!$B$1", cell.CellFormula); // Check it Evaluates correctly eval.EvaluateFormulaCell(cell); Assert.AreEqual(24.60 * 1.8, cell.NumericCellValue); // Try to add a formula for a new external workbook, won't be allowed to start try { cell = wb.GetSheetAt(0).GetRow(1).CreateCell(42); cell.CellFormula = (/*setter*/"[alt.xls]Sheet0!$A$1"); Assert.Fail("New workbook not linked, shouldn't be able to Add"); } catch (Exception e) { } // Link our new workbook HSSFWorkbook alt = new HSSFWorkbook(); alt.CreateSheet().CreateRow(0).CreateCell(0).SetCellValue("In another workbook"); wb.LinkExternalWorkbook("alt.xls", alt); // Now add a formula that refers to our new workbook cell.CellFormula = (/*setter*/"[alt.xls]Sheet0!$A$1"); Assert.AreEqual("[alt.xls]Sheet0!$A$1", cell.CellFormula); // Evaluate it, without a link to that workbook try { eval.Evaluate(cell); Assert.Fail("No cached value and no link to workbook, shouldn't Evaluate"); } catch (Exception e) { } // Add a link, check it does HSSFFormulaEvaluator.SetupEnvironment( new String[] { "XRefCalc.xls", "XRefCalcData.xls", "alt.xls" }, new HSSFFormulaEvaluator[] { eval, new HSSFFormulaEvaluator(wbData), new HSSFFormulaEvaluator(alt) } ); eval.EvaluateFormulaCell(cell); Assert.AreEqual("In another workbook", cell.StringCellValue); // Save and re-load wb = HSSFTestDataSamples.WriteOutAndReadBack(wb as HSSFWorkbook); eval = new HSSFFormulaEvaluator(wb); HSSFFormulaEvaluator.SetupEnvironment( new String[] { "XRefCalc.xls", "XRefCalcData.xls", "alt.xls" }, new HSSFFormulaEvaluator[] { eval, new HSSFFormulaEvaluator(wbData), new HSSFFormulaEvaluator(alt) } ); // Check the one referring to the previously existing workbook behaves cell = wb.GetSheetAt(0).GetRow(1).GetCell(40); Assert.AreEqual("Cost*[XRefCalcData.xls]MarkupSheet!$B$1", cell.CellFormula); eval.EvaluateFormulaCell(cell); Assert.AreEqual(24.60 * 1.8, cell.NumericCellValue); // Now check the newly Added reference cell = wb.GetSheetAt(0).GetRow(1).GetCell(42); Assert.AreEqual("[alt.xls]Sheet0!$A$1", cell.CellFormula); eval.EvaluateFormulaCell(cell); Assert.AreEqual("In another workbook", cell.StringCellValue); }
/** * Loops over all cells in all sheets of the supplied * workbook. * For cells that contain formulas, their formulas are * Evaluated, and the results are saved. These cells * remain as formula cells. * For cells that do not contain formulas, no Changes * are made. * This is a helpful wrapper around looping over all * cells, and calling EvaluateFormulaCell on each one. */ public static void EvaluateAllFormulaCells(HSSFWorkbook wb) { for (int i = 0; i < wb.NumberOfSheets; i++) { NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(i); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb); for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext(); ) { HSSFRow r = (HSSFRow)rit.Current; //evaluator.SetCurrentRow(r); for (IEnumerator cit = r.GetCellEnumerator(); cit.MoveNext(); ) { NPOI.SS.UserModel.Cell c = (HSSFCell)cit.Current; if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA) evaluator.EvaluateFormulaCell(c); } } } }
/** * Sum across multiple workbooks * eg =SUM($Sheet2.A1:$Sheet3.A1) * DISABLED - We currently get the formula wrong, and mis-evaluate */ public void DISABLEDtest48703() { HSSFWorkbook wb = OpenSample("48703.xls"); Assert.AreEqual(3, wb.NumberOfSheets); // Check reading the formula ISheet sheet = wb.GetSheetAt(0); IRow r = sheet.GetRow(0); ICell c = r.GetCell(0); Assert.AreEqual("SUM(Sheet2!A1:Sheet3!A1)", c.CellFormula); Assert.AreEqual(4.0, c.NumericCellValue); // Check the evaluated result HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb); eval.EvaluateFormulaCell(c); Assert.AreEqual(4.0, c.NumericCellValue); }
/** * * Returns the Formatted value of a cell as a <tt>String</tt> regardless * of the cell type. If the Excel FormatBase pattern cannot be Parsed then the * cell value will be Formatted using a default FormatBase. * * When passed a null or blank cell, this method will return an empty * String (""). Formula cells will be evaluated using the given * {@link HSSFFormulaEvaluator} if the evaluator is non-null. If the * evaluator is null, then the formula String will be returned. The caller * is responsible for setting the currentRow on the evaluator * * * @param cell The cell (can be null) * @param evaluator The HSSFFormulaEvaluator (can be null) * @return a string value of the cell */ public String FormatCellValue(HSSFCell cell, HSSFFormulaEvaluator evaluator) { if (cell == null) { return ""; } int cellType = cell.CellType; if (evaluator != null && cellType == HSSFCell.CELL_TYPE_FORMULA) { try { cellType = evaluator.EvaluateFormulaCell(cell); } catch (Exception e) { throw new Exception("Did you forGet to set the current" + " row on the HSSFFormulaEvaluator?", e); } } switch (cellType) { case HSSFCell.CELL_TYPE_BLANK: return ""; case HSSFCell.CELL_TYPE_FORMULA: // should only occur if evaluator is null return cell.CellFormula; case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.IsCellDateFormatted(cell)) { return GetFormattedDateString(cell); } return GetFormattedNumberString(cell); case HSSFCell.CELL_TYPE_STRING: return cell.RichStringCellValue.String; case HSSFCell.CELL_TYPE_BOOLEAN: return cell.BooleanCellValue?"TRUE":"FALSE"; case HSSFCell.CELL_TYPE_ERROR: return NPOI.HSSF.Record.Formula.Eval.ErrorEval.GetText(cell.ErrorCellValue); } throw new Exception("Unexpected celltype (" + cellType + ")"); }
public void TestEvaluateInSheet() { IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet("Sheet1"); IRow row = sheet.CreateRow(0); row.CreateCell(0).SetCellValue(-7500d); row.CreateCell(1).SetCellValue(3000d); row.CreateCell(2).SetCellValue(5000d); row.CreateCell(3).SetCellValue(1200d); row.CreateCell(4).SetCellValue(4000d); row.CreateCell(5).SetCellValue(0.05d); row.CreateCell(6).SetCellValue(0.08d); ICell cell = row.CreateCell(7); cell.CellFormula = (/*setter*/"MIRR(A1:E1, F1, G1)"); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); fe.ClearAllCachedResultValues(); fe.EvaluateFormulaCell(cell); double res = cell.NumericCellValue; Assert.AreEqual(0.18736225093, res, 0.00000001); }
public void TestXRefs() { IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls"); IWorkbook wbData = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalcData.xls"); ICell cell; // VLookup on a name in another file cell = wb.GetSheetAt(0).GetRow(1).GetCell(2); Assert.AreEqual(CellType.FORMULA, cell.CellType); Assert.AreEqual(CellType.NUMERIC, cell.CachedFormulaResultType); Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001); // WARNING - this is wrong! // The file name should be Showing, but bug #45970 is fixed // we seem to loose it Assert.AreEqual("VLOOKUP(PART,COSTS,2,FALSE)", cell.CellFormula); // Simple reference to a name in another file cell = wb.GetSheetAt(0).GetRow(1).GetCell(4); Assert.AreEqual(CellType.FORMULA, cell.CellType); Assert.AreEqual(CellType.NUMERIC, cell.CachedFormulaResultType); Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001); // WARNING - this is wrong! // The file name should be Showing, but bug #45970 is fixed // we seem to loose it Assert.AreEqual("Cost*Markup_Cost", cell.CellFormula); // Evaluate the cells HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb); HSSFFormulaEvaluator.SetupEnvironment( new String[] { "XRefCalc.xls", "XRefCalcData.xls" }, new HSSFFormulaEvaluator[] { eval, new HSSFFormulaEvaluator(wbData) } ); eval.EvaluateFormulaCell( wb.GetSheetAt(0).GetRow(1).GetCell(2) ); eval.EvaluateFormulaCell( wb.GetSheetAt(0).GetRow(1).GetCell(4) ); // Re-check VLOOKUP one cell = wb.GetSheetAt(0).GetRow(1).GetCell(2); Assert.AreEqual(CellType.FORMULA, cell.CellType); Assert.AreEqual(CellType.NUMERIC, cell.CachedFormulaResultType); Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001); // Re-check ref one cell = wb.GetSheetAt(0).GetRow(1).GetCell(4); Assert.AreEqual(CellType.FORMULA, cell.CellType); Assert.AreEqual(CellType.NUMERIC, cell.CachedFormulaResultType); Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001); }
public void TestEvaluateAll() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet s1 = wb.CreateSheet(); NPOI.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.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.SS.UserModel.CellType.FORMULA) { evaluator.EvaluateFormulaCell(c); // For Testing - all should be numeric Assert.AreEqual(NPOI.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.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.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.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.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.SS.UserModel.CellType.FORMULA) { evaluator.EvaluateInCell(c); } } } } Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0); Assert.AreEqual(NPOI.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.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.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType); }
public void TestClassCast_bug44861() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44861.xls"); // Check direct HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb); // And via calls int numSheets = wb.NumberOfSheets; for (int i = 0; i < numSheets; i++) { NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(i); HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb); for (IEnumerator rows = s.GetRowEnumerator(); rows.MoveNext(); ) { IRow r = (IRow)rows.Current; for (IEnumerator cells = r.GetEnumerator(); cells.MoveNext(); ) { ICell c = (ICell)cells.Current; eval.EvaluateFormulaCell(c); } } } }