Search in sources :

Example 1 with CellValue

use of org.apache.poi.ss.usermodel.CellValue in project poi by apache.

the class ExcelAntWorkbookUtil method evaluateCell.

/**
     * Uses a String in standard Excel format (SheetName!CellId) to locate a
     * cell and evaluate it.
     *
     * @param cellName
     * @param expectedValue
     * @param precision
     */
public ExcelAntEvaluationResult evaluateCell(String cellName, double expectedValue, double precision) {
    ExcelAntEvaluationResult evalResults = null;
    Cell cell = getCell(cellName);
    FormulaEvaluator evaluator = getEvaluator(excelFileName);
    CellValue resultOfEval = evaluator.evaluate(cell);
    if (resultOfEval.getErrorValue() == 0) {
        // the evaluation did not encounter errors
        double result = resultOfEval.getNumberValue();
        double delta = Math.abs(result - expectedValue);
        if (delta > precision) {
            evalResults = new ExcelAntEvaluationResult(false, false, resultOfEval.getNumberValue(), "Results was out of range based on precision " + " of " + precision + ".  Delta was actually " + delta, delta, cellName);
        } else {
            evalResults = new ExcelAntEvaluationResult(false, true, resultOfEval.getNumberValue(), "Evaluation passed without error within in range.", delta, cellName);
        }
    } else {
        String errorMeaning = null;
        try {
            errorMeaning = FormulaError.forInt(resultOfEval.getErrorValue()).getString();
        } catch (IllegalArgumentException iae) {
            errorMeaning = "unknown error code: " + Byte.toString(resultOfEval.getErrorValue());
        }
        evalResults = new ExcelAntEvaluationResult(true, false, resultOfEval.getNumberValue(), "Evaluation failed due to an evaluation error of " + resultOfEval.getErrorValue() + " which is " + errorMeaning, 0, cellName);
    }
    return evalResults;
}
Also used : CellValue(org.apache.poi.ss.usermodel.CellValue) Cell(org.apache.poi.ss.usermodel.Cell) HSSFFormulaEvaluator(org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator) XSSFFormulaEvaluator(org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator) FormulaEvaluator(org.apache.poi.ss.usermodel.FormulaEvaluator)

Example 2 with CellValue

use of org.apache.poi.ss.usermodel.CellValue in project poi by apache.

the class BaseFormulaEvaluator method evaluateInCell.

/**
     * If cell contains formula, it evaluates the formula, and
     *  puts the formula result back into the cell, in place
     *  of the old formula.
     * Else if cell does not contain formula, this method leaves
     *  the cell unchanged.
     * Note that the same instance of HSSFCell is returned to
     * allow chained calls like:
     * <pre>
     * int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType();
     * </pre>
     * Be aware that your cell value will be changed to hold the
     *  result of the formula. If you simply want the formula
     *  value computed for you, use {@link #evaluateFormulaCellEnum(Cell)}}
     * @param cell
     * @return the {@code cell} that was passed in, allowing for chained calls
     */
@Override
public Cell evaluateInCell(Cell cell) {
    if (cell == null) {
        return null;
    }
    Cell result = cell;
    if (cell.getCellTypeEnum() == CellType.FORMULA) {
        CellValue cv = evaluateFormulaCellValue(cell);
        setCellValue(cell, cv);
        // cell will no longer be a formula cell
        setCellType(cell, cv);
    }
    return result;
}
Also used : CellValue(org.apache.poi.ss.usermodel.CellValue) Cell(org.apache.poi.ss.usermodel.Cell)

Example 3 with CellValue

use of org.apache.poi.ss.usermodel.CellValue in project poi by apache.

the class TestProper method confirm.

private void confirm(String formulaText, String expectedResult) {
    cell11.setCellFormula(formulaText);
    evaluator.clearAllCachedResultValues();
    CellValue cv = evaluator.evaluate(cell11);
    if (cv.getCellTypeEnum() != CellType.STRING) {
        throw new AssertionFailedError("Wrong result type: " + cv.formatAsString());
    }
    String actualValue = cv.getStringValue();
    assertEquals(expectedResult, actualValue);
}
Also used : CellValue(org.apache.poi.ss.usermodel.CellValue) AssertionFailedError(junit.framework.AssertionFailedError)

Example 4 with CellValue

use of org.apache.poi.ss.usermodel.CellValue in project poi by apache.

the class TestStructuredReferences method confirm.

private static void confirm(FormulaEvaluator fe, Cell cell, double expectedResult) {
    fe.clearAllCachedResultValues();
    CellValue cv = fe.evaluate(cell);
    if (cv.getCellTypeEnum() != CellType.NUMERIC) {
        fail("expected numeric cell type but got " + cv.formatAsString());
    }
    assertEquals(expectedResult, cv.getNumberValue(), 0.0);
}
Also used : CellValue(org.apache.poi.ss.usermodel.CellValue)

Example 5 with CellValue

use of org.apache.poi.ss.usermodel.CellValue in project poi by apache.

the class TestStructuredReferences method confirm.

private static void confirm(FormulaEvaluator fe, Cell cell, String expectedResult) {
    fe.clearAllCachedResultValues();
    CellValue cv = fe.evaluate(cell);
    if (cv.getCellTypeEnum() != CellType.STRING) {
        fail("expected String cell type but got " + cv.formatAsString());
    }
    assertEquals(expectedResult, cv.getStringValue());
}
Also used : CellValue(org.apache.poi.ss.usermodel.CellValue)

Aggregations

CellValue (org.apache.poi.ss.usermodel.CellValue)63 Test (org.junit.Test)24 Cell (org.apache.poi.ss.usermodel.Cell)18 HSSFCell (org.apache.poi.hssf.usermodel.HSSFCell)15 HSSFFormulaEvaluator (org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator)14 HSSFWorkbook (org.apache.poi.hssf.usermodel.HSSFWorkbook)13 AssertionFailedError (junit.framework.AssertionFailedError)12 FormulaEvaluator (org.apache.poi.ss.usermodel.FormulaEvaluator)11 HSSFRow (org.apache.poi.hssf.usermodel.HSSFRow)10 HSSFSheet (org.apache.poi.hssf.usermodel.HSSFSheet)10 Row (org.apache.poi.ss.usermodel.Row)10 Workbook (org.apache.poi.ss.usermodel.Workbook)8 Sheet (org.apache.poi.ss.usermodel.Sheet)7 CellType (org.apache.poi.ss.usermodel.CellType)4 CellReference (org.apache.poi.ss.util.CellReference)4 CellReference (org.apache.poi.hssf.util.CellReference)2 ValueEval (org.apache.poi.ss.formula.eval.ValueEval)2 FreeRefFunction (org.apache.poi.ss.formula.functions.FreeRefFunction)2 File (java.io.File)1 EmptyStackException (java.util.EmptyStackException)1