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); }
/** * 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()); }
/** * 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 } }
/** @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); }