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

org.apache.poi.ss.usermodel.FormulaEvaluator
Evaluates formula cells.
For performance reasons, this class keeps a cache of all previously calculated intermediate cell values. Be sure to call {@link #clearAllCachedResultValues()} if any workbook cells are changed betweencalls to evaluate~ methods on this class. @author Amol S. Deshmukh < amolweb at ya hoo dot com > @author Josh Micich

    *  are made.
    * This is a helpful wrapper around looping over all
    *  cells, and calling evaluateFormulaCell on each one.
    */
  public static void evaluateAllFormulaCells(Workbook wb) {
      FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
      evaluateAllFormulaCells(wb, evaluator);
  }

View Full Code Here


    }


    public void test49156() throws Exception {
        Workbook wb = XSSFTestDataSamples.openSampleWorkbook("49156.xlsx");
        FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();


        Sheet sheet = wb.getSheetAt(0);
        for(Row row : sheet){
            for(Cell cell : row){
                if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
                    formulaEvaluator.evaluateInCell(cell); // caused NPE on some cells
                }
            }
        }
    }

View Full Code Here

       Cell c = s.getRow(0).getCell(0);
       
       assertEquals("SUM(\n1,2\n)", c.getCellFormula());
       assertEquals(3.0, c.getNumericCellValue());
       
       FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
       formulaEvaluator.evaluateFormulaCell(c);
       
       assertEquals("SUM(\n1,2\n)", c.getCellFormula());
       assertEquals(3.0, c.getNumericCellValue());
    }

View Full Code Here

          Cell cfn = r.createCell(2, Cell.CELL_TYPE_FORMULA);
          cfn.setCellFormula("A1");  
          Cell cfs = r.createCell(3, Cell.CELL_TYPE_FORMULA);
          cfs.setCellFormula("B1");
          
          FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
          assertEquals(Cell.CELL_TYPE_NUMERIC, fe.evaluate(cfn).getCellType());
          assertEquals(Cell.CELL_TYPE_STRING, fe.evaluate(cfs).getCellType());
          fe.evaluateFormulaCell(cfn);
          fe.evaluateFormulaCell(cfs);
          
          // Now test
          assertEquals(Cell.CELL_TYPE_NUMERIC, cn.getCellType());
          assertEquals(Cell.CELL_TYPE_STRING, cs.getCellType());
          assertEquals(Cell.CELL_TYPE_FORMULA, cfn.getCellType());

View Full Code Here

    }


    public void test49783() throws Exception {
        Workbook wb =  XSSFTestDataSamples.openSampleWorkbook("49783.xlsx");
        Sheet sheet = wb.getSheetAt(0);
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Cell cell;


        cell = sheet.getRow(0).getCell(0);
        assertEquals("#REF!*#REF!", cell.getCellFormula());
        assertEquals(Cell.CELL_TYPE_ERROR, evaluator.evaluateInCell(cell).getCellType());
        assertEquals("#REF!", FormulaError.forInt(cell.getErrorCellValue()).getString());


        Name nm1 = wb.getName("sale_1");
        assertNotNull("name sale_1 should be present", nm1);
        assertEquals("Sheet1!#REF!", nm1.getRefersToFormula());
        Name nm2 = wb.getName("sale_2");
        assertNotNull("name sale_2 should be present", nm2);
        assertEquals("Sheet1!#REF!", nm2.getRefersToFormula());


        cell = sheet.getRow(1).getCell(0);
        assertEquals("sale_1*sale_2", cell.getCellFormula());
        assertEquals(Cell.CELL_TYPE_ERROR, evaluator.evaluateInCell(cell).getCellType());
        assertEquals("#REF!", FormulaError.forInt(cell.getErrorCellValue()).getString());
    }

View Full Code Here

    }


    private void evaluateInCell(Cell cell) throws IOException {
        try {
            Workbook workbook = cell.getSheet().getWorkbook();
            FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
            formulaEvaluator.evaluateInCell(cell);
        } catch (RuntimeException e) {
            throw new IOException(MessageFormat.format(
                    "セルの数式を計算できませんでした: (pos=({1}, {2}), id={0})",
                    id,
                    cell.getRowIndex() + 1,

View Full Code Here

            if (logger.isInfoEnabled()) {
                logger.info("cell({},{}) is a formula. Attempting to evaluate: {}",
                        new Object[] { cell.getRowIndex(), cell.getColumnIndex(), cell.getCellFormula() });
            }


            final FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();


            // calculates the formula and puts it's value back into the cell
            final Cell evaluatedCell = evaluator.evaluateInCell(cell);


            return getCellValue(wb, evaluatedCell);
        } catch (RuntimeException e) {
            logger.warn("Exception occurred while evaluating formula at position ({},{}): {}", new Object[] { cell.getRowIndex(),
                    cell.getColumnIndex(), e.getMessage() });

View Full Code Here

  
  private void readSimpleBlock(Workbook wb, int sheetNo, ExcelBlock blockDefinition,
      OgnlStack stack, ReadStatus readStatus){
    //Simple Block will only care about cells in these Block
    Sheet sheet = wb.getSheetAt(sheetNo);
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    
    for(ExcelCell cellDefinition: blockDefinition.getCells()){
      Row row = sheet.getRow(cellDefinition.getRow());
      Cell cell = row == null ? null : row.getCell(cellDefinition.getCol());      
      try{

View Full Code Here

   */
  private Object readBlock(Workbook wb, int sheetNo, ExcelBlock blockDefinition, 
      int startRow, ReadStatus readStatus) 
    throws Exception{
    Sheet sheet = wb.getSheetAt(sheetNo);
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    
    if(blockDefinition.getLoopClass() == null){
      Map<String, Object> result = new HashMap<String, Object>();
      
      for(ExcelCell cellDefinition: blockDefinition.getCells()){

View Full Code Here

      }
    }    
  }
  
  private void reCalculateWorkbook(Workbook wb){
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
        Sheet sheet = wb.getSheetAt(sheetNum);
        for(Row r : sheet) {
            for(Cell c : r) {
                if(c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    evaluator.evaluateFormulaCell(c);
                }
            }
        }
    }
  }

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

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

com.asakusafw.testdriver.excel.ExcelSheetDataModelSource

loxia.support.excel.impl.DefaultExcelReader

loxia.support.excel.impl.DefaultExcelWriter

org.apache.metamodel.excel.ExcelUtils

org.apache.poi.hssf.record.formula.Area3DPtg

org.apache.poi.hssf.record.formula.AreaPtg

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

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

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

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

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.