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