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

    @Test
    @Ignore("This bug is still to be fixed")
    public void testBug56420SumIfNPE() throws Exception {
        XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("56420.xlsx");
        
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();


        Sheet sheet = wb.getSheetAt(0);
        Row r = sheet.getRow(2);
        Cell c = r.getCell(2);
        assertEquals("SUMIF($A$1:$A$4,A3,$B$1:$B$4)", c.getCellFormula());
        evaluator.evaluateInCell(c);
    }

View Full Code Here

        // Note the formula, as stored in the file, has the external name index not filename
        // TODO Provide a way to get the one with the filename
        assertEquals("[0]!NR_Global_B2", cRefWName.getCellFormula());
        
        // Try to evaluate them
        FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
        assertEquals("Test A1", eval.evaluate(cRefSName).getStringValue());
        assertEquals(142, (int)eval.evaluate(cRefWName).getNumberValue());
        
        // Try to evaluate everything
        eval.evaluateAll();
    }

View Full Code Here

   * @param testFocusFunctionName name of a single function/operator to test alone. 
   * Typically pass <code>null</code> to test all functions
   */
  private void processFunctionGroup(int startRowIndex, String testFocusFunctionName) {
 
    FormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);


    int rowIndex = startRowIndex;
    while (true) {
      Row r = sheet.getRow(rowIndex);
      String targetFunctionName = getTargetFunctionName(r);

View Full Code Here

   * @param testFocusFunctionName name of a single function/operator to test alone. 
   * Typically pass <code>null</code> to test all functions
   */
  private void processFunctionGroup(int startRowIndex, String testFocusFunctionName) {
 
    FormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);


    int rowIndex = startRowIndex;
    while (true) {
      Row r = sheet.getRow(rowIndex);
      String targetFunctionName = getTargetFunctionName(r);

View Full Code Here

    *  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

          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 testCountifBug51498() throws Exception {
    final int REF_COL = 4;
    final int EVAL_COL = 3;
    
        HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("51498.xls");
    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);
            HSSFCell cellRef = sheet.getRow(i).getCell(9);


            double expectedValue = cellRef.getNumericCellValue();
            double actualValue = evaluator.evaluate(cellFmla).getNumberValue();


            assertEquals(
                    "Problem with a formula at " +
                            new CellReference(cellFmla).formatAsString() + "[" + cellFmla.getCellFormula()+"] ",
                    expectedValue, actualValue, 0.0001);
        }


        // string criteria
        for (int i = 1; i < 9; i++) {
            HSSFCell cellFmla = sheet.getRow(i).getCell(13);
            HSSFCell cellRef = sheet.getRow(i).getCell(14);


            double expectedValue = cellRef.getNumericCellValue();
            double actualValue = evaluator.evaluate(cellFmla).getNumberValue();


            assertEquals(
                    "Problem with a formula at " +
                            new CellReference(cellFmla).formatAsString() + "[" + cellFmla.getCellFormula()+"] ",
                    expectedValue, actualValue, 0.0001);

View Full Code Here

                               List<? extends DataListener> listeners ) {
        int maxRows = sheet.getLastRowNum();


        CellRangeAddress[] mergedRanges = getMergedCells( sheet );
        DataFormatter formatter = new DataFormatter();
        FormulaEvaluator formulaEvaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();


        for ( int i = 0; i <= maxRows; i++ ) {
            Row row = sheet.getRow( i );
            int lastCellNum = row != null ? row.getLastCellNum() : 0;
            newRow( listeners, i, lastCellNum );


            for ( int cellNum = 0; cellNum < lastCellNum; cellNum++ ) {
                Cell cell = row.getCell( cellNum );
                if ( cell == null ) {
                    continue;
                }
                double num = 0;


                CellRangeAddress merged = getRangeIfMerged( cell,
                                                            mergedRanges );


                if ( merged != null ) {
                    Cell topLeft = sheet.getRow( merged.getFirstRow() ).getCell( merged.getFirstColumn() );
                    newCell( listeners,
                             i,
                             cellNum,
                             formatter.formatCellValue( topLeft ),
                             topLeft.getColumnIndex() );


                } else {
                    switch ( cell.getCellType() ) {
                        case Cell.CELL_TYPE_FORMULA:
                            CellValue cv = formulaEvaluator.evaluate( cell );
                            String cellValue = getCellValue( cv );
                            newCell( listeners,
                                     i,
                                     cellNum,
                                     cellValue,

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

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.