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

Example 21 with HSSFFormulaEvaluator

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

the class TestPPMT method testFromFile.

/**
     *  http://office.microsoft.com/en-001/excel-help/ppmt-function-HP010342774.aspx
     */
public void testFromFile() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("finance.xls");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet example1 = wb.getSheet("PPMT");
    HSSFCell ex1cell1 = example1.getRow(5).getCell(0);
    fe.evaluate(ex1cell1);
    assertEquals(-75.62, ex1cell1.getNumericCellValue(), 0.1);
    HSSFCell ex1cell2 = example1.getRow(16).getCell(0);
    fe.evaluate(ex1cell2);
    assertEquals(-27598.05, ex1cell2.getNumericCellValue(), 0.1);
}

Example 22 with HSSFFormulaEvaluator

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

the class TestIndirect method testMultipleWorkbooks.

@Test
public void testMultipleWorkbooks() throws Exception {
    HSSFWorkbook wbA = createWBA();
    HSSFCell cellA = wbA.getSheetAt(0).createRow(10).createCell(0);
    HSSFFormulaEvaluator feA = new HSSFFormulaEvaluator(wbA);
    HSSFWorkbook wbB = createWBB();
    HSSFCell cellB = wbB.getSheetAt(0).createRow(10).createCell(0);
    HSSFFormulaEvaluator feB = new HSSFFormulaEvaluator(wbB);
    String[] workbookNames = { "MyBook", "Figures for January" };
    HSSFFormulaEvaluator[] evaluators = { feA, feB };
    HSSFFormulaEvaluator.setupEnvironment(workbookNames, evaluators);
    // same wb
    confirm(feB, cellB, "INDIRECT(\"'[Figures for January]## Look here!'!A1\")", 42);
    // across workbooks
    confirm(feA, cellA, "INDIRECT(\"'[Figures for January]## Look here!'!A1\")", 42);
    // 2 level recursion
    // set up (and check) first level
    confirm(feB, cellB, "INDIRECT(\"[MyBook]Sheet2!A1\")", 50);
    // points to cellB
    confirm(feA, cellA, "INDIRECT(\"'[Figures for January]Sheet1'!A11\")", 50);
    wbB.close();
    wbA.close();
}

Example 23 with HSSFFormulaEvaluator

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

the class TestIrr method testEvaluateInSheet.

public void testEvaluateInSheet() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow 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);
    HSSFCell cell = row.createCell(5);
    cell.setCellFormula("IRR(A1:E1)");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    fe.clearAllCachedResultValues();
    fe.evaluateFormulaCellEnum(cell);
    double res = cell.getNumericCellValue();
    assertEquals(0.143d, Math.round(res * 1000d) / 1000d);
}

Example 24 with HSSFFormulaEvaluator

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

the class TestIrr method testIrrFromSpreadsheet.

public void testIrrFromSpreadsheet() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("IrrNpvTestCaseData.xls");
    HSSFSheet sheet = wb.getSheet("IRR-NPV");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    StringBuffer failures = new StringBuffer();
    int failureCount = 0;
    // FormulaEvaluator as of r1041407 throws "Unexpected ptg class (org.apache.poi.ss.formula.ptg.ArrayPtg)"
    for (int rownum = 9; rownum <= 15; rownum++) {
        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[" + (cellA.getRowIndex() + 1) + "]: " + cellA.getCellFormula() + " ");
            failures.append(e.getMessage());
            failureCount++;
        }
        //IRR-NPV relationship: NPV(IRR(values), values) = 0
        HSSFCell cellC = row.getCell(2);
        try {
            CellValue cv = fe.evaluate(cellC);
            // should agree within 0.01%
            assertEquals(0, cv.getNumberValue(), 0.0001);
        } catch (Throwable e) {
            if (failures.length() > 0)
                failures.append('\n');
            failures.append("Row[" + (cellC.getRowIndex() + 1) + "]: " + cellC.getCellFormula() + " ");
            failures.append(e.getMessage());
            failureCount++;
        }
    }
    if (failures.length() > 0) {
        throw new AssertionFailedError(failureCount + " IRR assertions failed:\n" + failures);
    }
}

Example 25 with HSSFFormulaEvaluator

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

the class TestIntercept method testFromFile.

/**
     *  Example from
     *  http://office.microsoft.com/en-us/excel-help/intercept-function-HP010062512.aspx?CTT=5&origin=HA010277524
     */
public void testFromFile() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("intercept.xls");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet example1 = wb.getSheet("Example 1");
    HSSFCell a8 = example1.getRow(7).getCell(0);
    assertEquals("INTERCEPT(A2:A6,B2:B6)", a8.getCellFormula());
    fe.evaluate(a8);
    assertEquals(0.048387097, a8.getNumericCellValue(), 0.000000001);
}