Examples of FormulaEvaluator

io.lumify.core.formula.FormulaEvaluator
Not Thread Safe, Do not share instance across threads
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

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

  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

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

        File truthFile = HSSFTestDataSamples.getSampleFile("54686_fraction_formats.txt");
        BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(truthFile)));
        Workbook wb = HSSFTestDataSamples.openSampleWorkbook("54686_fraction_formats.xls");
        Sheet sheet = wb.getSheetAt(0);
        DataFormatter formatter = new DataFormatter();
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();


        // Skip over the header row
        String truthLine = reader.readLine();
        String[] headers = truthLine.split("\t");
        truthLine = reader.readLine();

View Full Code Here

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

  
  public void testGetEvaluator() {
    fixture = new ExcelAntWorkbookUtilTestHelper( 
                mortgageCalculatorFileName ) ;
    
    FormulaEvaluator evaluator = fixture.getEvaluator( 
                                          mortgageCalculatorFileName ) ;
    
    assertNotNull( evaluator ) ;

View Full Code Here

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


  public void testGetEvaluatorXLSX() {
    fixture = new ExcelAntWorkbookUtilTestHelper( 
                "test-data/spreadsheet/sample.xlsx") ;
    
    FormulaEvaluator evaluator = fixture.getEvaluator( 
        "test-data/spreadsheet/sample.xlsx" ) ;
    
    assertNotNull( evaluator ) ;
   }

View Full Code Here

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

        Cell cell3 = sh.createRow(3).createCell(0);
        cell3.setCellFormula("IFERROR(C1,\"error\")");
        
        double accuracy = 1E-9;


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


        assertEquals("Checks that the cell is numeric",
            Cell.CELL_TYPE_NUMERIC, evaluator.evaluate(cell1).getCellType());
        assertEquals("Divides 210 by 35 and returns 6.0",
                6.0, evaluator.evaluate(cell1).getNumberValue(), accuracy);
        
        
        assertEquals("Checks that the cell is numeric",
            Cell.CELL_TYPE_STRING, evaluator.evaluate(cell2).getCellType());        
        assertEquals("Rounds -10 to a nearest multiple of -3 (-9)",
                "Error in calculation", evaluator.evaluate(cell2).getStringValue());
        
        assertEquals("Check that C1 returns string", 
            Cell.CELL_TYPE_STRING, evaluator.evaluate(cell3).getCellType());
        assertEquals("Check that C1 returns string \"error\"", 
            "error", evaluator.evaluate(cell3).getStringValue());
    }

View Full Code Here

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

   * @param startRowIndex row index in the spreadsheet where the first function/operator is found
   * @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);

View Full Code Here

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

          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

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

    }


    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

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


    }


    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

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

       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());


       // 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());
    }

View Full Code Here

0 1 2 3 4 5

TOP

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.