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 >

  private Object getCellValue(Cell cell, FormulaEvaluator evaluator)
   throws ExcelManipulateException{  
    if(cell == null) return null;
    //log.debug("Read Value for: " + ExcelUtil.getCellIndex(cell.getRowIndex(), cell.getColumnIndex()));
    Object value = null;
    CellValue cellValue = evaluator.evaluate(cell);
    if(cellValue == null) {
      logger.debug("{}: null",ExcelUtil.getCellIndex(cell.getRowIndex(), cell.getColumnIndex()));
      return null;
    }
    switch(cellValue.getCellType()){
    case Cell.CELL_TYPE_BLANK:
    case Cell.CELL_TYPE_ERROR:      
      break;      
    case Cell.CELL_TYPE_BOOLEAN:
      value = cellValue.getBooleanValue();
      break;
    case Cell.CELL_TYPE_NUMERIC:
      if(DateUtil.isCellDateFormatted(cell)) {
        value = DateUtil.getJavaDate(cellValue.getNumberValue());
      }else
        value = cellValue.getNumberValue();
      break;
    case Cell.CELL_TYPE_STRING:
      value = cellValue.getStringValue();
    }
    logger.debug("{}: {}", ExcelUtil.getCellIndex(cell.getRowIndex(), cell.getColumnIndex()), value);
    return value;
  }

View Full Code Here

  public void testEvaluateSimple() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("testNames.xls");
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFCell cell = sheet.getRow(8).getCell(0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv = fe.evaluate(cell);
    assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType());
    assertEquals(3.72, cv.getNumberValue(), 0.0);
  }

View Full Code Here

    // Set the complex flag - POI doesn't usually manipulate this flag
    NameRecord nameRec = TestHSSFName.getNameRecord(definedName);
    nameRec.setOptionFlag((short)0x10); // 0x10 -> complex


    HSSFFormulaEvaluator hsf = new HSSFFormulaEvaluator(wb);
    CellValue value;
    try {
      value = hsf.evaluate(cellA1);
    } catch (RuntimeException e) {
      if (e.getMessage().equals("Don't now how to evalate name 'Is_Multicar_Vehicle'")) {
        throw new AssertionFailedError("Identified bug 47048a");
      }
      throw e;
    }


    assertEquals(Cell.CELL_TYPE_NUMERIC, value.getCellType());
    assertEquals(5.33, value.getNumberValue(), 0.0);
  }

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




    cellB1.setCellValue(1.0); // range will be C1:D1


    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv;
    try {
      cv = fe.evaluate(cellA1);
    } catch (IllegalArgumentException e) {
      if (e.getMessage().equals("Unexpected ref arg class (org.apache.poi.ss.formula.LazyAreaEval)")) {
        throw new AssertionFailedError("Identified bug 46948");
      }
      throw e;
    }


    assertEquals(12.0, cv.getNumberValue(), 0.0);


    cellB1.setCellValue(2.0); // range will be C1:E1
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1);
    assertEquals(21.0, cv.getNumberValue(), 0.0);


    cellB1.setCellValue(0.0); // range will be C1:C1
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1);
    assertEquals(5.0, cv.getNumberValue(), 0.0);
  }

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

   */
  public int evaluateFormulaCell(Cell cell) {
    if (cell == null || cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
      return -1;
    }
    CellValue cv = evaluateFormulaCellValue(cell);
    // cell remains a formula cell, but the cached value is changed
    setCellValue(cell, cv);
    return cv.getCellType();
  }

View Full Code Here

    if (cell == null) {
      return null;
    }
    HSSFCell result = (HSSFCell) cell;
    if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
      CellValue cv = evaluateFormulaCellValue(cell);
      setCellValue(cell, cv);
      setCellType(cell, cv); // cell will no longer be a formula cell
    }
    return result;
  }

View Full Code Here

   */
  private CellValue evaluateFormulaCellValue(Cell cell) {
    ValueEval eval = _bookEvaluator.evaluate(new HSSFEvaluationCell((HSSFCell)cell));
    if (eval instanceof NumberEval) {
      NumberEval ne = (NumberEval) eval;
      return new CellValue(ne.getNumberValue());
    }
    if (eval instanceof BoolEval) {
      BoolEval be = (BoolEval) eval;
      return CellValue.valueOf(be.getBooleanValue());
    }
    if (eval instanceof StringEval) {
      StringEval ne = (StringEval) eval;
      return new CellValue(ne.getStringValue());
    }
    if (eval instanceof ErrorEval) {
      return CellValue.getError(((ErrorEval)eval).getErrorCode());
    }
    throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")");

View Full Code Here

          System.out.println("\t" + c.substring(c.lastIndexOf('.')+1) );
        }
        System.out.println("-> " + cell.getCellFormula());
      }
      
      CellValue evalResult = eval.evaluate(cell);
      assertNotNull(evalResult);
    }
  }

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.