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

Example 1 with HSSFFormulaEvaluator

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

the class TestProper method testValidHSSF.

@Test
public void testValidHSSF() {
    HSSFWorkbook wb = new HSSFWorkbook();
    evaluator = new HSSFFormulaEvaluator(wb);
    confirm(wb);
}

Example 2 with HSSFFormulaEvaluator

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

the class TestFormulaParserEval method testEvaluateFormulaWithRowBeyond32768_Bug44539.

public void testEvaluateFormulaWithRowBeyond32768_Bug44539() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    wb.setSheetName(0, "Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("SUM(A32769:A32770)");
    // put some values in the cells to make the evaluation more interesting
    sheet.createRow(32768).createCell(0).setCellValue(31);
    sheet.createRow(32769).createCell(0).setCellValue(11);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue result;
    try {
        result = fe.evaluate(cell);
    } catch (FormulaParseException e) {
        if (!e.getMessage().equals("Found reference to named range \"A\", but that named range wasn't defined!")) {
            throw new AssertionFailedError("Identifed bug 44539");
        }
        throw e;
    }
    assertEquals(CellType.NUMERIC, result.getCellTypeEnum());
    assertEquals(42.0, result.getNumberValue(), 0.0);
}

Example 3 with HSSFFormulaEvaluator

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

the class TestEvaluationCache method testBlankCellChangedToValueCell_bug46053.

/**
	 * Make sure that when blank cells are changed to value/formula cells, any dependent formulas
	 * have their cached results cleared.
	 */
public void testBlankCellChangedToValueCell_bug46053() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cellA1 = row.createCell(0);
    HSSFCell cellB1 = row.createCell(1);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    cellA1.setCellFormula("B1+2.2");
    cellB1.setCellValue(1.5);
    fe.notifyUpdateCell(cellA1);
    fe.notifyUpdateCell(cellB1);
    CellValue cv;
    cv = fe.evaluate(cellA1);
    assertEquals(3.7, cv.getNumberValue(), 0.0);
    cellB1.setCellType(CellType.BLANK);
    fe.notifyUpdateCell(cellB1);
    // B1 was used to evaluate A1
    cv = fe.evaluate(cellA1);
    assertEquals(2.2, cv.getNumberValue(), 0.0);
    // changing B1, so A1 cached result should be cleared
    cellB1.setCellValue(0.4);
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1);
    if (cv.getNumberValue() == 2.2) {
        // looks like left-over cached result from before change to B1
        throw new AssertionFailedError("Identified bug 46053");
    }
    assertEquals(2.6, cv.getNumberValue(), 0.0);
}

Example 4 with HSSFFormulaEvaluator

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

the class BaseTestFunctionsFromSpreadsheet method processFunctionGroup.

private static void processFunctionGroup(List<Object[]> data, HSSFSheet sheet, final int startRowIndex, String testFocusFunctionName, String filename) {
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet.getWorkbook());
    String currentGroupComment = "";
    final int maxRows = sheet.getLastRowNum() + 1;
    for (int rowIndex = startRowIndex; rowIndex < maxRows; rowIndex++) {
        HSSFRow r = sheet.getRow(rowIndex);
        if (r == null) {
            continue;
        }
        String newMarkerValue = getCellTextValue(r, SS.COLUMN_INDEX_MARKER, "marker");
        if (SS.TEST_CASES_END_MARKER.equalsIgnoreCase(newMarkerValue)) {
            // normal exit point
            return;
        }
        if (SS.SKIP_CURRENT_TEST_CASE_MARKER.equalsIgnoreCase(newMarkerValue)) {
            // currently disabled test case row
            continue;
        }
        if (newMarkerValue != null) {
            currentGroupComment = newMarkerValue;
        }
        HSSFCell evalCell = r.getCell(SS.COLUMN_INDEX_EVALUATION);
        if (evalCell == null || evalCell.getCellTypeEnum() != CellType.FORMULA) {
            continue;
        }
        String rowComment = getCellTextValue(r, SS.COLUMN_ROW_COMMENT, "row comment");
        String testName = (currentGroupComment + '\n' + rowComment).replace("null", "").trim().replace("\n", " - ");
        if ("".equals(testName)) {
            testName = evalCell.getCellFormula();
        }
        data.add(new Object[] { testName, filename, sheet, rowIndex, evaluator });
    }
    fail("Missing end marker '" + SS.TEST_CASES_END_MARKER + "' on sheet '" + sheet.getSheetName() + "'");
}

Example 5 with HSSFFormulaEvaluator

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

the class TestMissingArgEval method testCountFuncs.

public void testCountFuncs() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFCell cell = sheet.createRow(0).createCell(0);
    // 4 missing args, C5 is blank 
    cell.setCellFormula("COUNT(C5,,,,)");
    assertEquals(4.0, fe.evaluate(cell).getNumberValue(), 0.0);
    // 2 missing args, C5 is blank 
    cell.setCellFormula("COUNTA(C5,,)");
    fe.clearAllCachedResultValues();
    assertEquals(2.0, fe.evaluate(cell).getNumberValue(), 0.0);
}