Package org.apache.poi.ss.usermodel

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


    *  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

TOP

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

Copyright © 2018 www.massapicom. All rights reserved.
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.