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

   * @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(sheet, workbook);


    int rowIndex = startRowIndex;
    while (true) {
      HSSFRow r = sheet.getRow(rowIndex);
      String targetFunctionName = getTargetFunctionName(r);

View Full Code Here

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

public final class TestAddress extends TestCase {


    public void testAddress() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFCell cell = wb.createSheet().createRow(0).createCell(0);
        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);


        String formulaText = "ADDRESS(1,2)";
        confirmResult(fe, cell, formulaText, "$B$1");


        formulaText = "ADDRESS(22,44)";

View Full Code Here

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

      }else{
        return String.valueOf(cell.getNumericCellValue());
      }
    case HSSFCell.CELL_TYPE_FORMULA:
      if(evaluator==null) {
        evaluator = new HSSFFormulaEvaluator(workBook);
      }
      CellValue cellvalue=evaluator.evaluate(cell);
      return cellvalue.formatAsString();
    }
    return null;

View Full Code Here

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


    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

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

     * 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

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

     *  http://office.microsoft.com/en-001/excel-help/ppmt-function-HP010342774.aspx
     */
    public void testFromFile() {


        HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("finance.xls");
        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);


        HSSFSheet example1 = wb.getSheet("PPMT");
        HSSFCell ex1cell1 = example1.getRow(5).getCell(0);
        fe.evaluate(ex1cell1);
        assertEquals(-75.62, ex1cell1.getNumericCellValue(), 0.1);


        HSSFCell ex1cell2 = example1.getRow(16).getCell(0);
        fe.evaluate(ex1cell2);
        assertEquals(-27598.05, ex1cell2.getNumericCellValue(), 0.1);


    }

View Full Code Here

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

    public void setUp() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("new sheet");
        cell11 = sheet.createRow(0).createCell(0);
        cell11.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        evaluator = new HSSFFormulaEvaluator(wb);
    }

View Full Code Here

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

        row.createCell(6).setCellValue(0.08d);


        HSSFCell cell = row.createCell(7);
        cell.setCellFormula("MIRR(A1:E1, F1, G1)");


        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
        fe.clearAllCachedResultValues();
        fe.evaluateFormulaCell(cell);
        double res = cell.getNumericCellValue();
        assertEquals(0.18736225093, res, 0.00000001);
    }

View Full Code Here

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

    }


    public void testMirrFromSpreadsheet() {
        HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("mirrTest.xls");
        HSSFSheet sheet = wb.getSheet("Mirr");
        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
        StringBuilder failures = new StringBuilder();
        int failureCount = 0;
        int[] resultRows = {9, 19, 29, 45};


        for (int rowNum : resultRows) {
            HSSFRow row = sheet.getRow(rowNum);
            HSSFCell cellA = row.getCell(0);
            try {
                CellValue cv = fe.evaluate(cellA);
                assertFormulaResult(cv, cellA);
            } catch (Throwable e) {
                if (failures.length() > 0) failures.append('\n');
                failures.append("Row[").append(cellA.getRowIndex() + 1).append("]: ").append(cellA.getCellFormula()).append(" ");
                failures.append(e.getMessage());
                failureCount++;
            }
        }


        HSSFRow row = sheet.getRow(37);
        HSSFCell cellA = row.getCell(0);
        CellValue cv = fe.evaluate(cellA);
        assertEquals(ErrorEval.DIV_ZERO.getErrorCode(), cv.getErrorValue());


        if (failures.length() > 0) {
            throw new AssertionFailedError(failureCount + " IRR assertions failed:\n" + failures.toString());
        }

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.