Esempio n. 1
0
  private static void operationRefTest(String operator) {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r = null;
    HSSFCell c = null;

    // get our minimum values
    r = s.createRow(0);
    c = r.createCell(1);
    c.setCellFormula("A2" + operator + "A3");

    for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
      r = s.createRow(x);

      for (int y = 1; y < 256 && y > 0; y++) {

        String ref = null;
        String ref2 = null;
        short refx1 = 0;
        short refy1 = 0;
        short refx2 = 0;
        short refy2 = 0;
        if (x + 50 < Short.MAX_VALUE) {
          refx1 = (short) (x + 50);
          refx2 = (short) (x + 46);
        } else {
          refx1 = (short) (x - 4);
          refx2 = (short) (x - 3);
        }

        if (y + 50 < 255) {
          refy1 = (short) (y + 50);
          refy2 = (short) (y + 49);
        } else {
          refy1 = (short) (y - 4);
          refy2 = (short) (y - 3);
        }

        c = r.getCell(y);
        CellReference cr = new CellReference(refx1, refy1, false, false);
        ref = cr.formatAsString();
        cr = new CellReference(refx2, refy2, false, false);
        ref2 = cr.formatAsString();

        c = r.createCell(y);
        c.setCellFormula("" + ref + operator + ref2);
      }
    }

    // make sure we do the maximum value of the Int operator
    if (s.getLastRowNum() < Short.MAX_VALUE) {
      r = s.getRow(0);
      c = r.createCell(0);
      c.setCellFormula("" + "B1" + operator + "IV255");
    }

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    operationalRefVerify(operator, wb);
  }
Esempio n. 2
0
  /**
   * Opens the sheet we wrote out by binomialOperator and makes sure the formulas all match what we
   * expect (x operator y)
   */
  private static void operationalRefVerify(String operator, HSSFWorkbook wb) {

    HSSFSheet s = wb.getSheetAt(0);
    HSSFRow r = null;
    HSSFCell c = null;

    // get our minimum values
    r = s.getRow(0);
    c = r.getCell(1);
    // get our minimum values
    assertTrue(
        "minval Formula is as expected A2" + operator + "A3 != " + c.getCellFormula(),
        (("A2" + operator + "A3").equals(c.getCellFormula())));

    for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
      r = s.getRow(x);

      for (int y = 1; y < 256 && y > 0; y++) {

        int refx1;
        int refy1;
        int refx2;
        int refy2;
        if (x + 50 < Short.MAX_VALUE) {
          refx1 = x + 50;
          refx2 = x + 46;
        } else {
          refx1 = x - 4;
          refx2 = x - 3;
        }

        if (y + 50 < 255) {
          refy1 = y + 50;
          refy2 = y + 49;
        } else {
          refy1 = y - 4;
          refy2 = y - 3;
        }

        c = r.getCell(y);
        CellReference cr = new CellReference(refx1, refy1, false, false);
        String ref = cr.formatAsString();
        ref = cr.formatAsString();
        cr = new CellReference(refx2, refy2, false, false);
        String ref2 = cr.formatAsString();

        assertTrue(
            "loop Formula is as expected " + ref + operator + ref2 + "!=" + c.getCellFormula(),
            (("" + ref + operator + ref2).equals(c.getCellFormula())));
      }
    }

    // test our maximum values
    r = s.getRow(0);
    c = r.getCell(0);

    assertEquals("B1" + operator + "IV255", c.getCellFormula());
  }
Esempio n. 3
0
  /**
   * Adds the current cell reference to the exception for easier debugging. Would be nice to get the
   * formula text as well, but that seems to require too much digging around and casting to get the
   * FormulaRenderingWorkbook.
   */
  private NotImplementedException addExceptionInfo(
      NotImplementedException inner, int sheetIndex, int rowIndex, int columnIndex) {

    try {
      String sheetName = _workbook.getSheetName(sheetIndex);
      CellReference cr = new CellReference(sheetName, rowIndex, columnIndex, false, false);
      String msg = "Error evaluating cell " + cr.formatAsString();
      return new NotImplementedException(msg, inner);
    } catch (Exception e) {
      // avoid bombing out during exception handling
      e.printStackTrace();
      return inner; // preserve original exception
    }
  }
Esempio n. 4
0
  /** @return never <code>null</code>, never {@link BlankEval} */
  private ValueEval evaluateAny(
      EvaluationCell srcCell,
      int sheetIndex,
      int rowIndex,
      int columnIndex,
      EvaluationTracker tracker) {

    // avoid tracking dependencies for cells that have constant definition
    boolean shouldCellDependencyBeRecorded =
        _stabilityClassifier == null
            ? true
            : !_stabilityClassifier.isCellFinal(sheetIndex, rowIndex, columnIndex);
    if (srcCell == null || srcCell.getCellType() != Cell.CELL_TYPE_FORMULA) {
      ValueEval result = getValueFromNonFormulaCell(srcCell);
      if (shouldCellDependencyBeRecorded) {
        tracker.acceptPlainValueDependency(_workbookIx, sheetIndex, rowIndex, columnIndex, result);
      }
      return result;
    }

    FormulaCellCacheEntry cce = _cache.getOrCreateFormulaCellEntry(srcCell);
    if (shouldCellDependencyBeRecorded || cce.isInputSensitive()) {
      tracker.acceptFormulaDependency(cce);
    }
    IEvaluationListener evalListener = _evaluationListener;
    ValueEval result;
    if (cce.getValue() == null) {
      if (!tracker.startEvaluate(cce)) {
        return ErrorEval.CIRCULAR_REF_ERROR;
      }
      OperationEvaluationContext ec =
          new OperationEvaluationContext(
              this, _workbook, sheetIndex, rowIndex, columnIndex, tracker);

      try {

        Ptg[] ptgs = _workbook.getFormulaTokens(srcCell);
        if (evalListener == null) {
          result = evaluateFormula(ec, ptgs);
        } else {
          evalListener.onStartEvaluate(srcCell, cce, ptgs);
          result = evaluateFormula(ec, ptgs);
          evalListener.onEndEvaluate(cce, result);
        }

        tracker.updateCacheResult(result);
      } catch (NotImplementedException e) {
        throw addExceptionInfo(e, sheetIndex, rowIndex, columnIndex);
      } finally {
        tracker.endEvaluate(cce);
      }
    } else {
      if (evalListener != null) {
        evalListener.onCacheHit(sheetIndex, rowIndex, columnIndex, cce.getValue());
      }
      return cce.getValue();
    }
    if (isDebugLogEnabled()) {
      String sheetName = getSheetName(sheetIndex);
      CellReference cr = new CellReference(rowIndex, columnIndex);
      logDebug("Evaluated " + sheetName + "!" + cr.formatAsString() + " to " + result.toString());
    }
    // Usually (result === cce.getValue())
    // But sometimes: (result==ErrorEval.CIRCULAR_REF_ERROR, cce.getValue()==null)
    // When circular references are detected, the cache entry is only updated for
    // the top evaluation frame
    return result;
  }
 /**
  * Used to decide whether sheet names like 'AB123' need delimiting due to the fact that they look
  * like cell references.
  *
  * <p>This code is currently being used for translating formulas represented with <code>Ptg</code>
  * tokens into human readable text form. In formula expressions, a sheet name always has a
  * trailing '!' so there is little chance for ambiguity. It doesn't matter too much what this
  * method returns but it is worth noting the likely consumers of these formula text strings:
  *
  * <ol>
  *   <li>POI's own formula parser
  *   <li>Visual reading by human
  *   <li>VBA automation entry into Excel cell contents e.g. ActiveCell.Formula = "=c64!A1"
  *   <li>Manual entry into Excel cell contents
  *   <li>Some third party formula parser
  * </ol>
  *
  * At the time of writing, POI's formula parser tolerates cell-like sheet names in formulas with
  * or without delimiters. The same goes for Excel(2007), both manual and automated entry.
  *
  * <p>For better or worse this implementation attempts to replicate Excel's formula renderer.
  * Excel uses range checking on the apparent 'row' and 'column' components. Note however that the
  * maximum sheet size varies across versions.
  *
  * @see org.apache.poi.hssf.util.CellReference
  */
 /* package */ static boolean cellReferenceIsWithinRange(
     String lettersPrefix, String numbersSuffix) {
   return CellReference.cellReferenceIsWithinRange(
       lettersPrefix, numbersSuffix, SpreadsheetVersion.EXCEL97);
 }