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; }