Examples of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator$CellValue

org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator
Mimics the 'data view' of a cell. This allows formula evaluator to return a CellValue instead of precasting the value to String or Number or boolean type. @author Amol S. Deshmukh < amolweb at ya hoo dot com >

    cell.setCellValue(15.0);
    assertEquals("NPER(12,4500,100000,100000)", cell.getCellFormula());
    assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cell.getCachedFormulaResultType());
    assertEquals(15.0, cell.getNumericCellValue(), 0.0);


    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    fe.evaluateFormulaCell(cell);
    assertEquals(HSSFCell.CELL_TYPE_ERROR, cell.getCachedFormulaResultType());
    assertEquals(HSSFErrorConstants.ERROR_NUM, cell.getErrorCellValue());
  }

View Full Code Here

    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("B1%");
    row.createCell(1).setCellValue(50.0);
    
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv;
    try {
      cv = fe.evaluate(cell);
    } catch (RuntimeException e) {
      if(e.getCause() instanceof NullPointerException) {
        throw new AssertionFailedError("Identified bug 44608");
      }
      // else some other unexpected error

View Full Code Here

    
    cell.setCellFormula("myFunc()");
    String actualFormula=cell.getCellFormula();
    assertEquals("myFunc()", actualFormula);
    
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    // Check out what ExternalFunction.evaluate() does:
    CellValue evalResult;
    try {
      evalResult = fe.evaluate(cell);
    } catch (NotImplementedException e) {
      assertEquals("Error evaluating cell Sheet1!B1", e.getMessage());
      assertEquals("myFunc", e.getCause().getMessage());
      return;
    }

View Full Code Here

    }




    cellB1.setCellValue(1.0); // range will be C1:D1


    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv;
    try {
      cv = fe.evaluate(cellA1);
    } catch (IllegalArgumentException e) {
      if (e.getMessage().equals("Unexpected ref arg class (org.apache.poi.ss.formula.LazyAreaEval)")) {
        throw new AssertionFailedError("Identified bug 46948");
      }
      throw e;
    }


    assertEquals(12.0, cv.getNumberValue(), 0.0);


    cellB1.setCellValue(2.0); // range will be C1:E1
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1);
    assertEquals(21.0, cv.getNumberValue(), 0.0);


    cellB1.setCellValue(0.0); // range will be C1:C1
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1);
    assertEquals(5.0, cv.getNumberValue(), 0.0);
  }

View Full Code Here

   * @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) {
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
        Collection<String> funcs = FunctionEval.getSupportedFunctionNames();


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

View Full Code Here

  }
  
  public void testEvaluate() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("externalFunctionExample.xls");
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    confirmCellEval(sheet, 0, 0, fe, "YEARFRAC(B1,C1)", 29.0/90.0);
    confirmCellEval(sheet, 1, 0, fe, "YEARFRAC(B2,C2)", 0.0);
    confirmCellEval(sheet, 2, 0, fe, "YEARFRAC(B3,C3,D3)", 0.0);
    confirmCellEval(sheet, 3, 0, fe, "IF(ISEVEN(3),1.2,1.6)", 1.6);
    confirmCellEval(sheet, 4, 0, fe, "IF(ISODD(3),1.2,1.6)", 1.2);

View Full Code Here


    cellA1.setCellFormula("SUM(C1:OFFSET(C1,0,B1))");


    cellB1.setCellValue(1.0); // range will be C1:D1


    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv;
    try {
      cv = fe.evaluate(cellA1);
    } catch (IllegalArgumentException e) {
      if (e.getMessage().equals("Unexpected ref arg class (org.apache.poi.ss.formula.LazyAreaEval)")) {
        throw new AssertionFailedError("Identified bug 46948");
      }
      throw e;
    }


    assertEquals(12.0, cv.getNumberValue(), 0.0);


    cellB1.setCellValue(2.0); // range will be C1:E1
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1);
    assertEquals(21.0, cv.getNumberValue(), 0.0);


    cellB1.setCellValue(0.0); // range will be C1:C1
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1);
    assertEquals(5.0, cv.getNumberValue(), 0.0);
  }

View Full Code Here

                evaluator = HSSFFormulaEvaluator.create( (HSSFWorkbook)workbook,
                                                         null,
                                                         getFunctions() ) ;
            }


            evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        }


        return evaluator ;


    }

View Full Code Here

        HSSFSheet sheet = wb.createSheet("new sheet");
        HSSFRow row1 = sheet.createRow((short) 0);
        
        this.cell11 = row1.createCell((short) 0);
        
        this.evaluator = new HSSFFormulaEvaluator(sheet, wb);
        this.evaluator.setCurrentRow(row1);
    }

View Full Code Here

        HSSFCell cell = row.createCell((short)0);


         
        cell.setCellFormula("isblank(Sheet2!A1:A1)");
        
        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet1, wb);
        fe.setCurrentRow(row);
        CellValue result = fe.evaluate(cell);
        assertEquals(HSSFCell.CELL_TYPE_BOOLEAN, result.getCellType());
        assertEquals(true, result.getBooleanValue());
        
        cell.setCellFormula("isblank(D7:D7)");
        
        result = fe.evaluate(cell);
        assertEquals(HSSFCell.CELL_TYPE_BOOLEAN, result.getCellType());
        assertEquals(true, result.getBooleanValue());
        
   }

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator$CellValue

com.adaptiweb.utils.poi.HSSFRowSource

com.aesthete.swingobjects.fw.common.util.ExcelHelper

com.canoo.ant.table.ExcelPropertyTable

org.apache.poi.hssf.model.FormulaParser

org.apache.poi.hssf.model.TestFormulaParserEval

org.apache.poi.hssf.model.Workbook

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

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

org.apache.poi.hssf.record.formula.atp.TestYearFracCalculatorFromSpreadsheet

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

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.