Examples of org.apache.poi.ss.usermodel.CellValue

org.apache.poi.ss.usermodel.CellValue
Mimics the 'data view' of a cell. This allows formula evaluator to return a CellValue instead of precasting the value to String or Number or boolean type. @author Amol S. Deshmukh < amolweb at ya hoo dot com >

            }
            HSSFCell c = r.getCell(SS.COLUMN_INDEX_EVALUATION);
            if (c == null || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
                continue;
            }
            CellValue actualValue = evaluator.evaluate(c);
            HSSFCell expectedValueCell = r.getCell(SS.COLUMN_INDEX_EXPECTED_RESULT);
            String rowComment = getRowCommentColumnValue(r);


            String msgPrefix = formatTestCaseDetails(this.getFilename(),sheetName, r.getRowNum(), c, currentGroupComment, rowComment);
            try {

View Full Code Here

    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    HSSFSheet sheet = workbook.getSheetAt(0);


    // numeric criteria
        for (int i = 0; i < 8; i++) {
      CellValue expected = evaluator.evaluate(sheet.getRow(i).getCell(REF_COL));
      CellValue actual = evaluator.evaluate(sheet.getRow(i).getCell(EVAL_COL));
      assertEquals(expected.formatAsString(), actual.formatAsString());
    }


        // boolean criteria
        for (int i = 0; i < 8; i++) {
            HSSFCell cellFmla = sheet.getRow(i).getCell(8);

View Full Code Here

            HSSFCell cellA = row.getCell(0);  // cell containing a formula with COUNTIF
            assertEquals(HSSFCell.CELL_TYPE_FORMULA, cellA.getCellType());
            HSSFCell cellC = row.getCell(2);  // cell with a reference value
            assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cellC.getCellType());


            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);
            HSSFCell cellA = row.getCell(0);  // cell containing a formula with COUNTIF
            assertEquals(HSSFCell.CELL_TYPE_FORMULA, cellA.getCellType());
            HSSFCell cellC = row.getCell(2);  // cell with a reference value
            assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cellC.getCellType());


            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()+"]: "

View Full Code Here

      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++;

View Full Code Here

    }


    private void confirm(String formulaText, double expectedResult) {
        cell11.setCellFormula(formulaText);
        evaluator.clearAllCachedResultValues();
        CellValue cv = evaluator.evaluate(cell11);
        if (cv.getCellType() != Cell.CELL_TYPE_NUMERIC) {
            throw new AssertionFailedError("Wrong result type: " + cv.formatAsString());
        }
        double actualValue = cv.getNumberValue();
        assertEquals(expectedResult, actualValue, 0);
    }

View Full Code Here

        // 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 cellB = row.getCell(1);
            try {
                CellValue cv = fe.evaluate(cellB);
                assertFormulaResult(cv, cellB);
            } catch (Throwable e){
                if(failures.length() > 0) failures.append('\n');
                failures.append("Row[" + (cellB.getRowIndex() + 1)+ "]: " + cellB.getCellFormula() + " ");
                failures.append(e.getMessage());

View Full Code Here


        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.toString());
        }

View Full Code Here

    Cell c = formulasRow.getCell(SS.COLUMN_INDEX_ACTUAL_VALUE);
    if (c == null || c.getCellType() != Cell.CELL_TYPE_FORMULA) {
      return result;
    }


    CellValue actualValue = evaluator.evaluate(c);


    try {
      confirmExpectedResult("Function '" + targetFunctionName + "': Test: '" + targetTestName + "' Formula: " + c.getCellFormula() 
      + " @ " + formulasRow.getRowNum() + ":" + SS.COLUMN_INDEX_ACTUAL_VALUE,
          expectedValueCell, actualValue);

View Full Code Here

       for(int i=0; i<wb.getNumberOfSheets(); i++) {
          Sheet s = wb.getSheetAt(i);
          for(Row r : s) {
             for(Cell c : r) {
                if(c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    CellValue cv = eval.evaluate(c);
                    if(cv.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        // assert that the calculated value agrees with
                        // the cached formula result calculated by Excel
                        double cachedFormulaResult = c.getNumericCellValue();
                        double evaluatedFormulaResult = cv.getNumberValue();
                        assertEquals(c.getCellFormula(), cachedFormulaResult, evaluatedFormulaResult, 1E-7);
                    }
                }
             }
          }

View Full Code Here

      case HSSFCell.CELL_TYPE_ERROR:
        return CellValue.getError(cell.getErrorCellValue());
      case HSSFCell.CELL_TYPE_FORMULA:
        return evaluateFormulaCellValue(cell);
      case HSSFCell.CELL_TYPE_NUMERIC:
        return new CellValue(cell.getNumericCellValue());
      case HSSFCell.CELL_TYPE_STRING:
        return new CellValue(cell.getRichStringCellValue().getString());
      case HSSFCell.CELL_TYPE_BLANK:
        return null;
    }
    throw new IllegalStateException("Bad cell type (" + cell.getCellType() + ")");
  }

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of org.apache.poi.ss.usermodel.CellValue

loxia.support.excel.impl.DefaultExcelReader

org.apache.poi.hssf.model.TestFormulaParserEval

org.apache.poi.hssf.record.formula.eval.TestCircularReferences

org.apache.poi.hssf.record.formula.eval.TestExternalFunction

org.apache.poi.hssf.record.formula.eval.TestFormulaBugs

org.apache.poi.hssf.record.formula.eval.TestFormulasFromSpreadsheet

org.apache.poi.hssf.record.formula.eval.TestMissingArgEval

org.apache.poi.hssf.record.formula.eval.TestPercentEval

org.apache.poi.hssf.record.formula.eval.TestRangeEval

org.apache.poi.hssf.record.formula.functions.TestCountFuncs

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.