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

Example 6 with HSSFFormulaEvaluator

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

the class TestMissingArgEval method testEvaluateMissingArgs.

public void testEvaluateMissingArgs() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFCell cell = sheet.createRow(0).createCell(0);
    cell.setCellFormula("if(true,)");
    fe.clearAllCachedResultValues();
    CellValue cv;
    try {
        cv = fe.evaluate(cell);
    } catch (EmptyStackException e) {
        throw new AssertionFailedError("Missing args evaluation not implemented (bug 43354");
    }
    // MissingArg -> BlankEval -> zero (as formula result)
    assertEquals(0.0, cv.getNumberValue(), 0.0);
    // MissingArg -> BlankEval -> empty string (in concatenation)
    cell.setCellFormula("\"abc\"&if(true,)");
    fe.clearAllCachedResultValues();
    assertEquals("abc", fe.evaluate(cell).getStringValue());
}

Example 7 with HSSFFormulaEvaluator

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

the class TestYearFracCalculatorFromSpreadsheet method testAll.

@Test
public void testAll() throws Exception {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("yearfracExamples.xls");
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator(wb);
    int nSuccess = 0;
    Iterator<Row> rowIterator = sheet.rowIterator();
    while (rowIterator.hasNext()) {
        HSSFRow row = (HSSFRow) rowIterator.next();
        HSSFCell cell = row.getCell(SS.YEARFRAC_FORMULA_COLUMN);
        if (cell == null || cell.getCellTypeEnum() != CellType.FORMULA) {
            continue;
        }
        processRow(row, cell, formulaEvaluator);
        nSuccess++;
    }
    assertTrue("No test sample cases found", nSuccess > 0);
    wb.close();
}

Example 8 with HSSFFormulaEvaluator

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

the class TestCountFuncs method testCountifExamples.

/**
     * Two COUNTIF examples taken from
     * http://office.microsoft.com/en-us/excel-help/countif-function-HP010069840.aspx?CTT=5&origin=HA010277524
     */
public void testCountifExamples() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("countifExamples.xls");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet1 = wb.getSheet("MSDN Example 1");
    for (int rowIx = 7; rowIx <= 12; rowIx++) {
        HSSFRow row = sheet1.getRow(rowIx - 1);
        // cell containing a formula with COUNTIF
        HSSFCell cellA = row.getCell(0);
        assertEquals(CellType.FORMULA, cellA.getCellTypeEnum());
        // cell with a reference value
        HSSFCell cellC = row.getCell(2);
        assertEquals(CellType.NUMERIC, cellC.getCellTypeEnum());
        CellValue cv = fe.evaluate(cellA);
        double actualValue = cv.getNumberValue();
        double expectedValue = cellC.getNumericCellValue();
        assertEquals("Problem with a formula at  " + new CellReference(cellA).formatAsString() + ": " + cellA.getCellFormula() + " :" + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ", expectedValue, actualValue, 0.0001);
    }
    HSSFSheet sheet2 = wb.getSheet("MSDN Example 2");
    for (int rowIx = 9; rowIx <= 14; rowIx++) {
        HSSFRow row = sheet2.getRow(rowIx - 1);
        // cell containing a formula with COUNTIF
        HSSFCell cellA = row.getCell(0);
        assertEquals(CellType.FORMULA, cellA.getCellTypeEnum());
        // cell with a reference value
        HSSFCell cellC = row.getCell(2);
        assertEquals(CellType.NUMERIC, cellC.getCellTypeEnum());
        CellValue cv = fe.evaluate(cellA);
        double actualValue = cv.getNumberValue();
        double expectedValue = cellC.getNumericCellValue();
        assertEquals("Problem with a formula at " + new CellReference(cellA).formatAsString() + "[" + cellA.getCellFormula() + "]: " + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ", expectedValue, actualValue, 0.0001);
    }
}

Example 9 with HSSFFormulaEvaluator

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

the class TestCountFuncs method testCountFunctionFromSpreadsheet.

private static void testCountFunctionFromSpreadsheet(String FILE_NAME, int START_ROW_IX, int COL_IX_ACTUAL, int COL_IX_EXPECTED, String functionName) {
    int failureCount = 0;
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(FILE_NAME);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    int maxRow = sheet.getLastRowNum();
    for (int rowIx = START_ROW_IX; rowIx < maxRow; rowIx++) {
        HSSFRow row = sheet.getRow(rowIx);
        if (row == null) {
            continue;
        }
        HSSFCell cell = row.getCell(COL_IX_ACTUAL);
        CellValue cv = fe.evaluate(cell);
        double actualValue = cv.getNumberValue();
        double expectedValue = row.getCell(COL_IX_EXPECTED).getNumericCellValue();
        if (actualValue != expectedValue) {
            System.err.println("Problem with test case on row " + (rowIx + 1) + " " + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
            failureCount++;
        }
    }
    if (failureCount > 0) {
        throw new AssertionFailedError(failureCount + " " + functionName + " evaluations failed. See stderr for more details");
    }
}

Example 10 with HSSFFormulaEvaluator

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

the class TestIndirect method testBasic.

@Test
public void testBasic() throws Exception {
    HSSFWorkbook wbA = createWBA();
    HSSFCell c = wbA.getSheetAt(0).createRow(5).createCell(2);
    HSSFFormulaEvaluator feA = new HSSFFormulaEvaluator(wbA);
    // non-error cases
    confirm(feA, c, "INDIRECT(\"C2\")", 23);
    confirm(feA, c, "INDIRECT(\"C2\", TRUE)", 23);
    confirm(feA, c, "INDIRECT(\"$C2\")", 23);
    confirm(feA, c, "INDIRECT(\"C$2\")", 23);
    // area ref
    confirm(feA, c, "SUM(INDIRECT(\"Sheet2!B1:C3\"))", 351);
    // spaces in area ref
    confirm(feA, c, "SUM(INDIRECT(\"Sheet2! B1 : C3 \"))", 351);
    // special chars in sheet name
    confirm(feA, c, "SUM(INDIRECT(\"'John''s sales'!A1:C1\"))", 93);
    // redundant sheet name quotes
    confirm(feA, c, "INDIRECT(\"'Sheet1'!B3\")", 32);
    // case-insensitive sheet name
    confirm(feA, c, "INDIRECT(\"sHeet1!B3\")", 32);
    // spaces around cell ref
    confirm(feA, c, "INDIRECT(\" D3 \")", 34);
    // spaces around cell ref
    confirm(feA, c, "INDIRECT(\"Sheet1! D3 \")", 34);
    // explicit arg1. only TRUE supported so far
    confirm(feA, c, "INDIRECT(\"A1\", TRUE)", 11);
    // de-reference area ref (note formula is in C4)
    confirm(feA, c, "INDIRECT(\"A1:G1\")", 13);
    // indirect defined name
    confirm(feA, c, "SUM(INDIRECT(A4))", 50);
    // indirect defined name pointinh to other sheet
    confirm(feA, c, "SUM(INDIRECT(B4))", 351);
    // simple error propagation:
    // arg0 is evaluated to text first
    confirm(feA, c, "INDIRECT(#DIV/0!)", ErrorEval.DIV_ZERO);
    confirm(feA, c, "INDIRECT(#DIV/0!)", ErrorEval.DIV_ZERO);
    confirm(feA, c, "INDIRECT(#NAME?, \"x\")", ErrorEval.NAME_INVALID);
    confirm(feA, c, "INDIRECT(#NUM!, #N/A)", ErrorEval.NUM_ERROR);
    // arg1 is evaluated to boolean before arg0 is decoded
    confirm(feA, c, "INDIRECT(\"garbage\", #N/A)", ErrorEval.NA);
    // empty string is not valid boolean
    confirm(feA, c, "INDIRECT(\"garbage\", \"\")", ErrorEval.VALUE_INVALID);
    // must be "TRUE" or "FALSE"
    confirm(feA, c, "INDIRECT(\"garbage\", \"flase\")", ErrorEval.VALUE_INVALID);
    // spaces around sheet name (with or without quotes makes no difference)
    confirm(feA, c, "INDIRECT(\"'Sheet1 '!D3\")", ErrorEval.REF_INVALID);
    confirm(feA, c, "INDIRECT(\" Sheet1!D3\")", ErrorEval.REF_INVALID);
    confirm(feA, c, "INDIRECT(\"'Sheet1' !D3\")", ErrorEval.REF_INVALID);
    // bad quote escaping
    confirm(feA, c, "SUM(INDIRECT(\"'John's sales'!A1:C1\"))", ErrorEval.REF_INVALID);
    // unknown external workbook
    confirm(feA, c, "INDIRECT(\"[Book1]Sheet1!A1\")", ErrorEval.REF_INVALID);
    // unknown sheet
    confirm(feA, c, "INDIRECT(\"Sheet3!A1\")", ErrorEval.REF_INVALID);
    //        if (false) { // TODO - support evaluation of defined names
    //            confirm(feA, c, "INDIRECT(\"Sheet1!IW1\")", ErrorEval.REF_INVALID); // bad column
    //            confirm(feA, c, "INDIRECT(\"Sheet1!A65537\")", ErrorEval.REF_INVALID); // bad row
    //        }
    // space in cell ref
    confirm(feA, c, "INDIRECT(\"Sheet1!A 1\")", ErrorEval.REF_INVALID);
    wbA.close();
}