public void TestShortCircuitIfEvaluation()
{
// Set up a simple IF() formula that has measurable evaluation cost for its operands.
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
ICell cellA1 = row.CreateCell(0);
cellA1.CellFormula = "if(B1,C1,D1+E1+F1)";
// populate cells B1..F1 with simple formulas instead of plain values so we can use
// EvaluationListener to check which parts of the first formula get evaluated
for (int i = 1; i < 6; i++)
{
// formulas are just literal constants "1".."5"
row.CreateCell(i).CellFormula = i.ToString();
}
EvalCountListener evalListener = new EvalCountListener();
WorkbookEvaluator evaluator = WorkbookEvaluatorTestHelper.CreateEvaluator(wb, evalListener);
ValueEval ve = evaluator.Evaluate(HSSFEvaluationTestHelper.WrapCell(cellA1));
int evalCount = evalListener.EvalCount;
if (evalCount == 6)
{
// Without short-circuit-if evaluation, evaluating cell 'A1' takes 3 extra evaluations (for D1,E1,F1)
throw new AssertionException("Identifed bug 48195 - Formula evaluator should short-circuit IF() calculations.");
}
Assert.AreEqual(3, evalCount);
Assert.AreEqual(2.0, ((NumberEval)ve).NumberValue, 0D);
}
/**
* If cell Contains a formula, the formula is Evaluated and returned,
* else the CellValue simply copies the appropriate cell value from
* the cell and also its cell type. This method should be preferred over
* EvaluateInCell() when the call should not modify the contents of the
* original cell.
*
* @param sheetName the name of the sheet Containing the cell
* @param rowIndex zero based
* @param columnIndex zero based
* @return <code>null</code> if the supplied cell is <code>null</code> or blank
*/
public ValueEval Evaluate(String sheetName, int rowIndex, int columnIndex)
{
IEvaluationCell cell = _sewb.GetEvaluationCell(sheetName, rowIndex, columnIndex);
switch (cell.CellType)
{
case CellType.Boolean:
return(BoolEval.ValueOf(cell.BooleanCellValue));
case CellType.Error:
return(ErrorEval.ValueOf(cell.ErrorCellValue));
case CellType.Formula:
return(_evaluator.Evaluate(cell));
case CellType.Numeric:
return(new NumberEval(cell.NumericCellValue));
case CellType.String:
return(new StringEval(cell.StringCellValue));
case CellType.Blank:
return(null);
}
throw new InvalidOperationException("Bad cell type (" + cell.CellType + ")");
}
/**
* Returns a CellValue wrapper around the supplied ValueEval instance.
* @param eval
*/
private CellValue EvaluateFormulaCellValue(ICell cell)
{
ValueEval eval = _bookEvaluator.Evaluate(new HSSFEvaluationCell((HSSFCell)cell));
if (eval is NumberEval)
{
NumberEval ne = (NumberEval)eval;
return(new CellValue(ne.NumberValue));
}
if (eval is BoolEval)
{
BoolEval be = (BoolEval)eval;
return(CellValue.ValueOf(be.BooleanValue));
}
if (eval is StringEval)
{
StringEval ne = (StringEval)eval;
return(new CellValue(ne.StringValue));
}
if (eval is ErrorEval)
{
return(CellValue.GetError(((ErrorEval)eval).ErrorCode));
}
throw new InvalidOperationException("Unexpected eval class (" + eval.GetType().Name + ")");
}
/**
* Returns a CellValue wrapper around the supplied ValueEval instance.
*/
private CellValue EvaluateFormulaCellValue(ICell cell)
{
if (!(cell is XSSFCell))
{
throw new ArgumentException("Unexpected type of cell: " + cell.GetType() + "." +
" Only XSSFCells can be Evaluated.");
}
ValueEval eval = _bookEvaluator.Evaluate(new XSSFEvaluationCell((XSSFCell)cell));
if (eval is NumberEval)
{
NumberEval ne = (NumberEval)eval;
return(new CellValue(ne.NumberValue));
}
if (eval is BoolEval)
{
BoolEval be = (BoolEval)eval;
return(CellValue.ValueOf(be.BooleanValue));
}
if (eval is StringEval)
{
StringEval ne = (StringEval)eval;
return(new CellValue(ne.StringValue));
}
if (eval is ErrorEval)
{
return(CellValue.GetError(((ErrorEval)eval).ErrorCode));
}
throw new Exception("Unexpected eval class (" + eval.GetType().Name + ")");
}
/**
* If cell Contains a formula, the formula is Evaluated and returned,
* else the CellValue simply copies the appropriate cell value from
* the cell and also its cell type. This method should be preferred over
* EvaluateInCell() when the call should not modify the contents of the
* original cell.
*
* @param sheetName the name of the sheet Containing the cell
* @param rowIndex zero based
* @param columnIndex zero based
* @return <code>null</code> if the supplied cell is <code>null</code> or blank
*/
public ValueEval Evaluate(String sheetName, int rowIndex, int columnIndex)
{
IEvaluationCell cell = _sewb.GetEvaluationCell(sheetName, rowIndex, columnIndex);
switch (cell.CellType)
{
case CellType.BOOLEAN:
return(BoolEval.ValueOf(cell.BooleanCellValue));
case CellType.ERROR:
return(ErrorEval.ValueOf(cell.ErrorCellValue));
case CellType.FORMULA:
return(_evaluator.Evaluate(cell));
case CellType.NUMERIC:
return(new NumberEval(cell.NumericCellValue));
case CellType.STRING:
return(new StringEval(cell.StringCellValue));
case CellType.BLANK:
return(null);
}
throw new InvalidOperationException("Bad cell type (" + cell.CellType + ")");
}
public void TestSlowEvaluate45376()
{
/*
* Note - to observe behaviour without caching, disable the call to
* updateValue() from FormulaCellCacheEntry.updateFormulaResult().
*/
// Firstly set up a sequence of formula cells where each depends on the previous multiple
// times. Without caching, each subsequent cell take about 4 times longer to Evaluate.
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
for (int i = 1; i < 10; i++)
{
ICell cell = row.CreateCell(i);
char prevCol = (char)('A' + i - 1);
String prevCell = prevCol + "1";
// this formula is inspired by the offending formula of the attachment for bug 45376
String formula = "IF(DATE(YEAR(" + prevCell + "),MONTH(" + prevCell + ")+1,1)<=$D$3," +
"DATE(YEAR(" + prevCell + "),MONTH(" + prevCell + ")+1,1),NA())";
cell.CellFormula = (formula);
}
row.CreateCell(0).SetCellValue(new DateTime(2000, 1, 1, 0, 0, 0));
// Choose cell A9, so that the Assert.Failing Test case doesn't take too long to execute.
ICell cell1 = row.GetCell(8);
EvalListener evalListener = new EvalListener();
WorkbookEvaluator evaluator = WorkbookEvaluatorTestHelper.CreateEvaluator(wb, evalListener);
ValueEval ve = evaluator.Evaluate(HSSFEvaluationTestHelper.WrapCell(cell1));
int evalCount = evalListener.GetCountCacheMisses();
if (evalCount > 10)
{
// Without caching, evaluating cell 'A9' takes 21845 evaluations which consumes
// much time (~3 sec on Core 2 Duo 2.2GHz)
Console.Error.WriteLine("Cell A9 took " + evalCount + " intermediate evaluations");
throw new AssertionException("Identifed bug 45376 - Formula evaluator should cache values");
}
// With caching, the evaluationCount is 8 which is a big improvement
// Note - these expected values may change if the WorkbookEvaluator is
// ever optimised to short circuit 'if' functions.
Assert.AreEqual(8, evalCount);
// The cache hits would be 24 if fully evaluating all arguments of the
// "IF()" functions (Each of the 8 formulas has 4 refs to formula cells
// which result in 1 cache miss and 3 cache hits). However with the
// short-circuit-if optimisation, 2 of the cell refs get skipped
// reducing this metric 8.
Assert.AreEqual(8, evalListener.GetCountCacheHits());
// confirm the evaluation result too
Assert.AreEqual(ErrorEval.NA, ve);
wb.Close();
}
public ValueEval EvaluateCell(String cellRefText)
{
return(_evaluator.Evaluate(WrapCell(GetOrCreateCell(cellRefText))));
}
