Examples with HSSFFormulaEvaluator org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator used on opensource projects

Example 16 with HSSFFormulaEvaluator

use of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator in project poi by apache.

the class TestRank method testFromFile.

public void testFromFile() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("rank.xls");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet example1 = wb.getSheet("Example 1");
    HSSFCell ex1cell1 = example1.getRow(7).getCell(0);
    assertEquals(3.0, fe.evaluate(ex1cell1).getNumberValue());
    HSSFCell ex1cell2 = example1.getRow(8).getCell(0);
    assertEquals(5.0, fe.evaluate(ex1cell2).getNumberValue());
    HSSFSheet example2 = wb.getSheet("Example 2");
    for (int rownum = 1; rownum <= 10; rownum++) {
        HSSFCell cell = example2.getRow(rownum).getCell(2);
        //cached formula result
        double cachedResult = cell.getNumericCellValue();
        assertEquals(cachedResult, fe.evaluate(cell).getNumberValue());
    }
    HSSFSheet example3 = wb.getSheet("Example 3");
    for (int rownum = 1; rownum <= 10; rownum++) {
        HSSFCell cellD = example3.getRow(rownum).getCell(3);
        //cached formula result
        double cachedResultD = cellD.getNumericCellValue();
        assertEquals(new CellReference(cellD).formatAsString(), cachedResultD, fe.evaluate(cellD).getNumberValue());
        HSSFCell cellE = example3.getRow(rownum).getCell(4);
        //cached formula result
        double cachedResultE = cellE.getNumericCellValue();
        assertEquals(new CellReference(cellE).formatAsString(), cachedResultE, fe.evaluate(cellE).getNumberValue());
        HSSFCell cellF = example3.getRow(rownum).getCell(5);
        //cached formula result
        double cachedResultF = cellF.getNumericCellValue();
        assertEquals(new CellReference(cellF).formatAsString(), cachedResultF, fe.evaluate(cellF).getNumberValue());
    }
}

Example 17 with HSSFFormulaEvaluator

use of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator in project poi by apache.

the class TestExternalNameReference method testEvaluate.

public void testEvaluate() throws Exception {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("XRefCalc.xls");
    HSSFWorkbook wb2 = HSSFTestDataSamples.openSampleWorkbook("XRefCalcData.xls");
    CellReference cellRef = new CellReference(wb.getName("QUANT").getRefersToFormula());
    HSSFCell cell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
    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").getRefersToFormula());
    HSSFCell uccell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
    cellRef = new CellReference(wb.getName("COST").getRefersToFormula());
    HSSFCell ccell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
    cellRef = new CellReference(wb.getName("TOTALCOST").getRefersToFormula());
    HSSFCell tccell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
    evaluator.evaluateFormulaCellEnum(uccell);
    evaluator.evaluateFormulaCellEnum(ccell);
    evaluator.evaluateFormulaCellEnum(tccell);
    assertEquals(NEW_PART_COST, uccell.getNumericCellValue());
    assertEquals(NEW_PART_COST * NEW_QUANT, ccell.getNumericCellValue());
    assertEquals(NEW_PART_COST * NEW_QUANT * MARKUP_COST_2, tccell.getNumericCellValue());
}

Example 18 with HSSFFormulaEvaluator

use of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator in project poi by apache.

the class TestMirr method testEvaluateInSheet.

public void testEvaluateInSheet() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow 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);
    HSSFCell cell = row.createCell(7);
    cell.setCellFormula("MIRR(A1:E1, F1, G1)");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    fe.clearAllCachedResultValues();
    fe.evaluateFormulaCellEnum(cell);
    double res = cell.getNumericCellValue();
    assertEquals(0.18736225093, res, 0.00000001);
}

Example 19 with HSSFFormulaEvaluator

use of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator in project poi by apache.

the class TestMirr method testMirrFromSpreadsheet.

public void testMirrFromSpreadsheet() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("mirrTest.xls");
    HSSFSheet sheet = wb.getSheet("Mirr");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    StringBuilder failures = new StringBuilder();
    int failureCount = 0;
    int[] resultRows = { 9, 19, 29, 45 };
    for (int rowNum : resultRows) {
        HSSFRow row = sheet.getRow(rowNum);
        HSSFCell cellA = row.getCell(0);
        try {
            CellValue cv = fe.evaluate(cellA);
            assertFormulaResult(cv, cellA);
        } catch (Throwable e) {
            if (failures.length() > 0)
                failures.append('\n');
            failures.append("Row[").append(cellA.getRowIndex() + 1).append("]: ").append(cellA.getCellFormula()).append(" ");
            failures.append(e.getMessage());
            failureCount++;
        }
    }
    HSSFRow row = sheet.getRow(37);
    HSSFCell cellA = row.getCell(0);
    CellValue cv = fe.evaluate(cellA);
    assertEquals(ErrorEval.DIV_ZERO.getErrorCode(), cv.getErrorValue());
    if (failures.length() > 0) {
        throw new AssertionFailedError(failureCount + " IRR assertions failed:\n" + failures);
    }
}

Example 20 with HSSFFormulaEvaluator

use of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator in project poi by apache.

the class TestNper method testEvaluate_bug_45732.

@Test
public void testEvaluate_bug_45732() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFCell cell = sheet.createRow(0).createCell(0);
    cell.setCellFormula("NPER(12,4500,100000,100000)");
    cell.setCellValue(15.0);
    assertEquals("NPER(12,4500,100000,100000)", cell.getCellFormula());
    assertEquals(CellType.NUMERIC, cell.getCachedFormulaResultTypeEnum());
    assertEquals(15.0, cell.getNumericCellValue(), 0.0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    fe.evaluateFormulaCellEnum(cell);
    assertEquals(CellType.ERROR, cell.getCachedFormulaResultTypeEnum());
    assertEquals(FormulaError.NUM.getCode(), cell.getErrorCellValue());
    wb.close();
}