Examples of HSSFFormulaEvaluator

org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator
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.hssf.usermodel.HSSFFormulaEvaluator

    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell((short)0);
    cell.setCellFormula("B1%");
    row.createCell((short)1).setCellValue(50.0);
    
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
    fe.setCurrentRow(row);
    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

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

    
    public void genericTest() throws Exception {
        HSSFSheet s = workbook.getSheetAt( 0 );
        HSSFRow r = s.getRow(getBeginRow());
        short endcolnum = r.getLastCellNum();
        HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(s, workbook);
        evaluator.setCurrentRow(r);


        HSSFCell c = null;
        for (short colnum=getBeginCol(); colnum < endcolnum; colnum++) {
            try {
            c = r.getCell(colnum);
            if (c==null || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA)
                continue;
            
            HSSFFormulaEvaluator.CellValue actualValue = evaluator.evaluate(c);
            
            HSSFCell expectedValueCell = getExpectedValueCell(s, r, c);
            assertEquals("Formula: " + c.getCellFormula() 
                    + " @ " + getBeginRow() + ":" + colnum, 
                    expectedValueCell, actualValue);

View Full Code Here

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


  public void testEvaluateMultipleWorkbooks() {
    HSSFWorkbook wbA = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaA.xls");
    HSSFWorkbook wbB = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaB.xls");


    HSSFFormulaEvaluator evaluatorA = new HSSFFormulaEvaluator(wbA);
    HSSFFormulaEvaluator evaluatorB = new HSSFFormulaEvaluator(wbB);


    // Hook up the workbook evaluators to enable evaluation of formulas across books
    String[] bookNames = { "multibookFormulaA.xls", "multibookFormulaB.xls", };
    HSSFFormulaEvaluator[] evaluators = { evaluatorA, evaluatorB, };
    HSSFFormulaEvaluator.setupEnvironment(bookNames, evaluators);


    HSSFCell cell;


    HSSFSheet aSheet1 = wbA.getSheetAt(0);
    HSSFSheet bSheet1 = wbB.getSheetAt(0);


    // Simple case - single link from wbA to wbB
    confirmFormula(wbA, 0, 0, 0, "[multibookFormulaB.xls]BSheet1!B1");
    cell = aSheet1.getRow(0).getCell(0);
    confirmEvaluation(35, evaluatorA, cell);




    // more complex case - back link into wbA
    // [wbA]ASheet1!A2 references (among other things) [wbB]BSheet1!B2
    confirmFormula(wbA, 0, 1, 0, "[multibookFormulaB.xls]BSheet1!$B$2+2*A3");
    // [wbB]BSheet1!B2 references (among other things) [wbA]AnotherSheet!A1:B2
    confirmFormula(wbB, 0, 1, 1, "SUM([multibookFormulaA.xls]AnotherSheet!$A$1:$B$2)+B3");


    cell = aSheet1.getRow(1).getCell(0);
    confirmEvaluation(264, evaluatorA, cell);


    // change [wbB]BSheet1!B3 (from 50 to 60)
    HSSFCell cellB3 = bSheet1.getRow(2).getCell(1);
    cellB3.setCellValue(60);
    evaluatorB.notifyUpdateCell(cellB3);
    confirmEvaluation(274, evaluatorA, cell);


    // change [wbA]ASheet1!A3 (from 100 to 80)
    HSSFCell cellA3 = aSheet1.getRow(2).getCell(0);
    cellA3.setCellValue(80);

View Full Code Here

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

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("1+IF(1,,)");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv;
    try {
      cv = fe.evaluate(cell);
    } catch (RuntimeException e) {
      throw new AssertionFailedError("Missing arg result not being handled correctly.");
    }
    assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType());
    // adding blank to 1.0 gives 1.0
    assertEquals(1.0, cv.getNumberValue(), 0.0);


    // check with string operand
    cell.setCellFormula("\"abc\"&IF(1,,)");
    fe.notifySetFormula(cell);
    cv = fe.evaluate(cell);
    assertEquals(HSSFCell.CELL_TYPE_STRING, cv.getCellType());
    // adding blank to "abc" gives "abc"
    assertEquals("abc", cv.getStringValue());
    
    // check CHOOSE()
    cell.setCellFormula("\"abc\"&CHOOSE(2,5,,9)");
    fe.notifySetFormula(cell);
    cv = fe.evaluate(cell);
    assertEquals(HSSFCell.CELL_TYPE_STRING, cv.getCellType());
    // adding blank to "abc" gives "abc"
    assertEquals("abc", cv.getStringValue());
  }

View Full Code Here

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

    }




    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

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

  private static void testCountFunctionFromSpreadsheet(String FILE_NAME, int START_ROW_IX, int COL_IX_ACTUAL, int COL_IX_EXPECTED, String functionName) {


    int failureCount = 0;
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(FILE_NAME);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    int maxRow = sheet.getLastRowNum();
    for (int rowIx=START_ROW_IX; rowIx<maxRow; rowIx++) {
      HSSFRow row = sheet.getRow(rowIx);
      if(row == null) {
        continue;
      }
      HSSFCell cell = row.getCell(COL_IX_ACTUAL);
      CellValue cv = fe.evaluate(cell);
      double actualValue = cv.getNumberValue();
      double expectedValue = row.getCell(COL_IX_EXPECTED).getNumericCellValue();
      if (actualValue != expectedValue) {
        System.err.println("Problem with test case on row " + (rowIx+1) + " "
            + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");

View Full Code Here

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


  public void testEvaluateMultipleWorkbooks() {
    HSSFWorkbook wbA = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaA.xls");
    HSSFWorkbook wbB = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaB.xls");


    HSSFFormulaEvaluator evaluatorA = new HSSFFormulaEvaluator(wbA);
    HSSFFormulaEvaluator evaluatorB = new HSSFFormulaEvaluator(wbB);


    // Hook up the workbook evaluators to enable evaluation of formulas across books
    String[] bookNames = { "multibookFormulaA.xls", "multibookFormulaB.xls", };
    HSSFFormulaEvaluator[] evaluators = { evaluatorA, evaluatorB, };
    HSSFFormulaEvaluator.setupEnvironment(bookNames, evaluators);


    HSSFCell cell;


    HSSFSheet aSheet1 = wbA.getSheetAt(0);
    HSSFSheet bSheet1 = wbB.getSheetAt(0);


    // Simple case - single link from wbA to wbB
    confirmFormula(wbA, 0, 0, 0, "[multibookFormulaB.xls]BSheet1!B1");
    cell = aSheet1.getRow(0).getCell(0);
    confirmEvaluation(35, evaluatorA, cell);




    // more complex case - back link into wbA
    // [wbA]ASheet1!A2 references (among other things) [wbB]BSheet1!B2
    confirmFormula(wbA, 0, 1, 0, "[multibookFormulaB.xls]BSheet1!$B$2+2*A3");
    // [wbB]BSheet1!B2 references (among other things) [wbA]AnotherSheet!A1:B2
    confirmFormula(wbB, 0, 1, 1, "SUM([multibookFormulaA.xls]AnotherSheet!$A$1:$B$2)+B3");


    cell = aSheet1.getRow(1).getCell(0);
    confirmEvaluation(264, evaluatorA, cell);


    // change [wbB]BSheet1!B3 (from 50 to 60)
    HSSFCell cellB3 = bSheet1.getRow(2).getCell(1);
    cellB3.setCellValue(60);
    evaluatorB.notifyUpdateCell(cellB3);
    confirmEvaluation(274, evaluatorA, cell);


    // change [wbA]ASheet1!A3 (from 100 to 80)
    HSSFCell cellA3 = aSheet1.getRow(2).getCell(0);
    cellA3.setCellValue(80);

View Full Code Here

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

    final int COL_IX_EXPECTED = 3;


    int failureCount = 0;
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(FILE_NAME);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    int maxRow = sheet.getLastRowNum();
    for (int rowIx=START_ROW_IX; rowIx<maxRow; rowIx++) {
      HSSFRow row = sheet.getRow(rowIx);
      if(row == null) {
        continue;
      }
      HSSFCell cell = row.getCell(COL_IX_ACTUAL);
      CellValue cv = fe.evaluate(cell);
      double actualValue = cv.getNumberValue();
      double expectedValue = row.getCell(COL_IX_EXPECTED).getNumericCellValue();
      if (actualValue != expectedValue) {
        System.err.println("Problem with test case on row " + (rowIx+1) + " "
            + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");

View Full Code Here

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

  /**
   * Translates StackOverflowError into AssertionFailedError
   */
  private static CellValue evaluateWithCycles(HSSFWorkbook wb, HSSFCell testCell)
      throws AssertionFailedError {
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    try {
      return evaluator.evaluate(testCell);
    } catch (StackOverflowError e) {
      throw new AssertionFailedError( "circular reference caused stack overflow error");
    }
  }

View Full Code Here

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

    cellB1.setCellFormula("1+C1");
    cellC1.setCellFormula("1+D1");
    cellD1.setCellFormula("1+E1");
    cellE1.setCellFormula("1+A1");
    
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv;
    
    // Happy day flow - evaluate A1 first 
    cv = fe.evaluate(cellA1);
    assertEquals(Cell.CELL_TYPE_NUMERIC, cv.getCellType());
    assertEquals(42.0, cv.getNumberValue(), 0.0);
    cv = fe.evaluate(cellB1); // no circ-ref-error because A1 result is cached
    assertEquals(Cell.CELL_TYPE_NUMERIC, cv.getCellType());
    assertEquals(46.0, cv.getNumberValue(), 0.0);
    
    // Show the bug - evaluate another cell from the loop first
    fe.clearAllCachedResultValues();
    cv = fe.evaluate(cellB1);
    if (cv.getCellType() == ErrorEval.CIRCULAR_REF_ERROR.getErrorCode()) {
      throw new AssertionFailedError("Identified bug 46898");
    }
    assertEquals(Cell.CELL_TYPE_NUMERIC, cv.getCellType());
    assertEquals(46.0, cv.getNumberValue(), 0.0);


    // start evaluation on another cell
    fe.clearAllCachedResultValues();
    cv = fe.evaluate(cellE1);
    assertEquals(Cell.CELL_TYPE_NUMERIC, cv.getCellType());
    assertEquals(43.0, cv.getNumberValue(), 0.0);
    
    
  }

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.