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

        assertEquals(Cell.CELL_TYPE_NUMERIC, intF.getCachedFormulaResultType());
        
        assertEquals("(C2:D3 D3:E4)", intF.getCellFormula());
        
        // Check we can evaluate it correctly
        FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
        assertEquals("4.0", eval.evaluate(intF).formatAsString());
    }

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

        baseTestSharedFormulas("shared_formulas.xlsx");
    }


    public void testSharedFormulas_evaluateInCell(){
        XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.openSampleWorkbook("49872.xlsx");
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        XSSFSheet sheet = wb.getSheetAt(0);


        double result = 3.0;


        // B3 is a master shared formula, C3 and D3 don't have the formula written in their f element.
        // Instead, the attribute si for a particular cell is used to figure what the formula expression
        // should be based on the cell's relative location to the master formula, e.g.
        // B3:        <f t="shared" ref="B3:D3" si="0">B1+B2</f>
        // C3 and D3: <f t="shared" si="0"/>


        // get B3 and evaluate it in the cell
        XSSFCell b3 = sheet.getRow(2).getCell(1);
        assertEquals(result, evaluator.evaluateInCell(b3).getNumericCellValue());


        //at this point the master formula is gone, but we are still able to evaluate dependent cells
        XSSFCell c3 = sheet.getRow(2).getCell(2);
        assertEquals(result, evaluator.evaluateInCell(c3).getNumericCellValue());


        XSSFCell d3 = sheet.getRow(2).getCell(3);
        assertEquals(result, evaluator.evaluateInCell(d3).getNumericCellValue());
    }

View Full Code Here

        Workbook[] wbs = new Workbook[] {
                HSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xls"),
                XSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xlsx")
        };
        for (Workbook wb : wbs) {
            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
            Sheet s1 = wb.getSheetAt(0);


            
            // Simple SUM over numbers
            Cell sumF = s1.getRow(2).getCell(0);
            assertNotNull(sumF);
            assertEquals("SUM(Sheet1:Sheet3!A1)", sumF.getCellFormula());
            assertEquals("Failed for " + wb.getClass(), "66.0", evaluator.evaluate(sumF).formatAsString());
            
            
            // Various Stats formulas on numbers
            Cell avgF = s1.getRow(2).getCell(1);
            assertNotNull(avgF);
            assertEquals("AVERAGE(Sheet1:Sheet3!A1)", avgF.getCellFormula());
            assertEquals("22.0", evaluator.evaluate(avgF).formatAsString());
            
            Cell minF = s1.getRow(3).getCell(1);
            assertNotNull(minF);
            assertEquals("MIN(Sheet1:Sheet3!A$1)", minF.getCellFormula());
            assertEquals("11.0", evaluator.evaluate(minF).formatAsString());
            
            Cell maxF = s1.getRow(4).getCell(1);
            assertNotNull(maxF);
            assertEquals("MAX(Sheet1:Sheet3!A$1)", maxF.getCellFormula());
            assertEquals("33.0", evaluator.evaluate(maxF).formatAsString());
            
            Cell countF = s1.getRow(5).getCell(1);
            assertNotNull(countF);
            assertEquals("COUNT(Sheet1:Sheet3!A$1)", countF.getCellFormula());
            assertEquals("3.0", evaluator.evaluate(countF).formatAsString());
            
            
            // Various CountAs on Strings
            Cell countA_1F = s1.getRow(2).getCell(2);
            assertNotNull(countA_1F);
            assertEquals("COUNTA(Sheet1:Sheet3!C1)", countA_1F.getCellFormula());
            assertEquals("3.0", evaluator.evaluate(countA_1F).formatAsString());
            
            Cell countA_2F = s1.getRow(2).getCell(3);
            assertNotNull(countA_2F);
            assertEquals("COUNTA(Sheet1:Sheet3!D1)", countA_2F.getCellFormula());
            assertEquals("0.0", evaluator.evaluate(countA_2F).formatAsString());
            
            Cell countA_3F = s1.getRow(2).getCell(4);
            assertNotNull(countA_3F);
            assertEquals("COUNTA(Sheet1:Sheet3!E1)", countA_3F.getCellFormula());
            assertEquals("3.0", evaluator.evaluate(countA_3F).formatAsString());
        }
    }

View Full Code Here

        Workbook[] wbs = new Workbook[] {
                HSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xls"),
                XSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xlsx")
        };
        for (Workbook wb : wbs) {
            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
            Sheet s1 = wb.getSheetAt(0);


            
            // SUM over a range
            Cell sumFA = s1.getRow(2).getCell(7);
            assertNotNull(sumFA);
            assertEquals("SUM(Sheet1:Sheet3!A1:B2)", sumFA.getCellFormula());
            assertEquals("Failed for " + wb.getClass(), "110.0", evaluator.evaluate(sumFA).formatAsString());


            
            // Various Stats formulas on ranges of numbers
            Cell avgFA = s1.getRow(2).getCell(8);
            assertNotNull(avgFA);
            assertEquals("AVERAGE(Sheet1:Sheet3!A1:B2)", avgFA.getCellFormula());
            assertEquals("27.5", evaluator.evaluate(avgFA).formatAsString());
            
            Cell minFA = s1.getRow(3).getCell(8);
            assertNotNull(minFA);
            assertEquals("MIN(Sheet1:Sheet3!A$1:B$2)", minFA.getCellFormula());
            assertEquals("11.0", evaluator.evaluate(minFA).formatAsString());
            
            Cell maxFA = s1.getRow(4).getCell(8);
            assertNotNull(maxFA);
            assertEquals("MAX(Sheet1:Sheet3!A$1:B$2)", maxFA.getCellFormula());
            assertEquals("44.0", evaluator.evaluate(maxFA).formatAsString());
            
            Cell countFA = s1.getRow(5).getCell(8);
            assertNotNull(countFA);
            assertEquals("COUNT(Sheet1:Sheet3!$A$1:$B$2)", countFA.getCellFormula());
            assertEquals("4.0", evaluator.evaluate(countFA).formatAsString());
        }
    }

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


    @Test
    public void bug49783() 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

    }


    @Test
    public void bug49156() 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(), 0);
       
       FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
       formulaEvaluator.evaluateFormulaCell(c);
       
       assertEquals("SUM(\n1,2\n)", c.getCellFormula());
       assertEquals(3.0, c.getNumericCellValue(), 0);


       // For 51875
       Cell b3 = s.getRow(2).getCell(1);
       formulaEvaluator.evaluateFormulaCell(b3);
       assertEquals("B1+B2", b3.getCellFormula()); // The newline is lost for shared formulas
       assertEquals(3.0, b3.getNumericCellValue(), 0);
    }

View Full Code Here

     * Bug 53101:
     */
    @Test
    public void bug5301(){
        Workbook workbook = XSSFTestDataSamples.openSampleWorkbook("53101.xlsx");
        FormulaEvaluator evaluator =
                workbook.getCreationHelper().createFormulaEvaluator();
        // A1: SUM(B1: IZ1)
        double a1Value =
                evaluator.evaluate(workbook.getSheetAt(0).getRow(0).getCell(0)).getNumberValue();


        // Assert
        assertEquals(259.0, a1Value, 0.0);


        // KY: SUM(B1: IZ1)
        /*double ky1Value =*/
                evaluator.evaluate(workbook.getSheetAt(0).getRow(0).getCell(310)).getNumberValue();


        // Assert
        assertEquals(259.0, a1Value, 0.0);
    }

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.