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


    protected abstract String getFilename();


    private int processTestSheet(HSSFWorkbook workbook, int sheetIndex, String sheetName) {
        HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
        int maxRows = sheet.getLastRowNum()+1;
        int result = Result.NO_EVALUATIONS_FOUND; // so far


        String currentGroupComment = null;
        for(int rowIndex=SS.START_TEST_CASES_ROW_INDEX; rowIndex<maxRows; rowIndex++) {
            HSSFRow r = sheet.getRow(rowIndex);
            String newMarkerValue = getMarkerColumnValue(r);
            if(r == null) {
                continue;
            }
            if(SS.TEST_CASES_END_MARKER.equalsIgnoreCase(newMarkerValue)) {
                // normal exit point
                return result;
            }
            if(SS.SKIP_CURRENT_TEST_CASE_MARKER.equalsIgnoreCase(newMarkerValue)) {
                // currently disabled test case row
                continue;
            }
            if(newMarkerValue != null) {
                currentGroupComment = newMarkerValue;
            }
            HSSFCell c = r.getCell(SS.COLUMN_INDEX_EVALUATION);
            if (c == null || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
                continue;
            }
            CellValue actualValue = evaluator.evaluate(c);
            HSSFCell expectedValueCell = r.getCell(SS.COLUMN_INDEX_EXPECTED_RESULT);
            String rowComment = getRowCommentColumnValue(r);


            String msgPrefix = formatTestCaseDetails(this.getFilename(),sheetName, r.getRowNum(), c, currentGroupComment, rowComment);
            try {

View Full Code Here

     * Two COUNTIF examples taken from
     * http://office.microsoft.com/en-us/excel-help/countif-function-HP010069840.aspx?CTT=5&origin=HA010277524
     */
    public void testCountifExamples() {
        HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("countifExamples.xls");
        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);


        HSSFSheet sheet1 = wb.getSheet("MSDN Example 1");
        for (int rowIx=7; rowIx<=12; rowIx++) {
            HSSFRow row = sheet1.getRow(rowIx-1);
            HSSFCell cellA = row.getCell(0);  // cell containing a formula with COUNTIF
            assertEquals(HSSFCell.CELL_TYPE_FORMULA, cellA.getCellType());
            HSSFCell cellC = row.getCell(2);  // cell with a reference value
            assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cellC.getCellType());


            CellValue cv = fe.evaluate(cellA);
            double actualValue = cv.getNumberValue();
            double expectedValue = cellC.getNumericCellValue();
            assertEquals(
                    "Problem with a formula at  " + new CellReference(cellA).formatAsString()
                            + ": " + cellA.getCellFormula() + " :"
                    + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ",
                    expectedValue, actualValue, 0.0001);
        }


        HSSFSheet sheet2 = wb.getSheet("MSDN Example 2");
        for (int rowIx=9; rowIx<=14; rowIx++) {
            HSSFRow row = sheet2.getRow(rowIx-1);
            HSSFCell cellA = row.getCell(0);  // cell containing a formula with COUNTIF
            assertEquals(HSSFCell.CELL_TYPE_FORMULA, cellA.getCellType());
            HSSFCell cellC = row.getCell(2);  // cell with a reference value
            assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cellC.getCellType());


            CellValue cv = fe.evaluate(cellA);
            double actualValue = cv.getNumberValue();
            double expectedValue = cellC.getNumericCellValue();


            assertEquals(
                    "Problem with a formula at " +

View Full Code Here

  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


    // put some values in the cells to make the evaluation more interesting
    sheet.createRow(32768).createCell((short)0).setCellValue(31);
    sheet.createRow(32769).createCell((short)0).setCellValue(11);
    
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
    fe.setCurrentRow(row);
    CellValue result;
    try {
      result = fe.evaluate(cell);
    } catch (FormulaParseException e) {
      if(e.getMessage().equals("Found reference to named range \"A\", but that named range wasn't defined!")) {
        fail("Identifed bug 44539");
      }
      throw new RuntimeException(e);

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

     }
  }


  private int processTestSheet(HSSFWorkbook workbook, int sheetIndex, String sheetName) {
    HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, workbook);
    int maxRows = sheet.getLastRowNum()+1;
    int result = Result.NO_EVALUATIONS_FOUND; // so far
    
    String currentGroupComment = null;
    for(int rowIndex=SS.START_TEST_CASES_ROW_INDEX; rowIndex<maxRows; rowIndex++) {
      HSSFRow r = sheet.getRow(rowIndex);
      String newMarkerValue = getMarkerColumnValue(r);
      if(r == null) {
        continue;
      }
      if(SS.TEST_CASES_END_MARKER.equalsIgnoreCase(newMarkerValue)) {
        // normal exit point
        return result;
      }
      if(SS.SKIP_CURRENT_TEST_CASE_MARKER.equalsIgnoreCase(newMarkerValue)) {
        // currently disabled test case row
        continue;
      }
      if(newMarkerValue != null) {
        currentGroupComment = newMarkerValue;
      }
      HSSFCell c = r.getCell(SS.COLUMN_INDEX_EVALUATION);
      if (c == null || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
        continue;
      }
      evaluator.setCurrentRow(r);
      CellValue actualValue = evaluator.evaluate(c);
      HSSFCell expectedValueCell = r.getCell(SS.COLUMN_INDEX_EXPECTED_RESULT);
      String rowComment = getRowCommentColumnValue(r);
      
      String msgPrefix = formatTestCaseDetails(sheetName, r.getRowNum(), c, currentGroupComment, rowComment);
      try {

View Full Code Here

        
        cell.setCellFormula("myFunc()");
        String actualFormula=cell.getCellFormula();
        assertEquals("myFunc()", actualFormula);
    
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
    fe.setCurrentRow(row);
    CellValue evalResult = fe.evaluate(cell);
    
    // Check the return value from ExternalFunction.evaluate()
    // TODO - make this test assert something more interesting as soon as ExternalFunction works a bit better
    assertEquals(HSSFCell.CELL_TYPE_ERROR, evalResult.getCellType());
    assertEquals(ErrorEval.FUNCTION_NOT_IMPLEMENTED.getErrorCode(), evalResult.getErrorValue());

View Full Code Here

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

View Full Code Here

    
    HSSFRow row = sheet.createRow(0);
    HSSFCell testCell = row.createCell((short)0);
    testCell.setCellFormula("A1");


    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
    evaluator.setCurrentRow(row);
    CellValue cellValue = evaluateWithCycles(wb, sheet, row, testCell);
    
    confirmCycleErrorCode(cellValue);
  }

View Full Code Here

    row.createCell((short)1).setCellFormula("C1");
    row.createCell((short)2).setCellFormula("D1");
    HSSFCell testCell = row.createCell((short)3);
    testCell.setCellFormula("A1");


    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
    evaluator.setCurrentRow(row);
    CellValue cellValue = evaluateWithCycles(wb, sheet, row, testCell);
    
    confirmCycleErrorCode(cellValue);
  }

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

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

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.