/**
* 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 FormulaCellCacheEntry Remove(IEvaluationCell cell)
{
FormulaCellCacheEntry tmp = (FormulaCellCacheEntry)_formulaEntriesByCell[cell.IdentityKey];
_formulaEntriesByCell.Remove(cell);
return(tmp);
}
/**
* 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 ForkedEvaluationCell(ForkedEvaluationSheet sheet, IEvaluationCell masterCell)
{
_sheet = sheet;
_masterCell = masterCell;
// start with value blank, but expect construction to be immediately
SetValue(BlankEval.instance); // followed by a proper call to SetValue()
}
public ForkedEvaluationCell(ForkedEvaluationSheet sheet, IEvaluationCell masterCell)
{
_sheet = sheet;
_masterCell = masterCell;
// start with value blank, but expect construction to be immediately
SetValue(BlankEval.instance); // followed by a proper call to SetValue()
}
public ForkedEvaluationCell GetOrCreateUpdatableCell(int rowIndex, int columnIndex)
{
RowColKey key = new RowColKey(rowIndex, columnIndex);
ForkedEvaluationCell result = null;
if (_sharedCellsByRowCol.ContainsKey(key))
{
result = _sharedCellsByRowCol[(key)];
}
if (result == null)
{
IEvaluationCell mcell = _masterSheet.GetCell(rowIndex, columnIndex);
if (mcell == null)
{
CellReference cr = new CellReference(rowIndex, columnIndex);
throw new InvalidOperationException("Underlying cell '"
+ cr.FormatAsString() + "' is missing in master sheet.");
}
result = new ForkedEvaluationCell(this, mcell);
if (_sharedCellsByRowCol.ContainsKey(key))
{
_sharedCellsByRowCol[key] = result;
}
else
{
_sharedCellsByRowCol.Add(key, result);
}
}
return(result);
}
/**
* Returns a CellValue wrapper around the supplied ValueEval instance.
*/
protected override CellValue EvaluateFormulaCellValue(ICell cell)
{
IEvaluationCell evalCell = ToEvaluationCell(cell);
ValueEval eval = _bookEvaluator.Evaluate(evalCell);
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 + ")");
}
/**
* Dereferences a single value from any AreaEval or RefEval evaluation
* result. If the supplied evaluationResult is just a plain value, it is
* returned as-is.
*
* @return a {@link NumberEval}, {@link StringEval}, {@link BoolEval}, or
* {@link ErrorEval}. Never <code>null</code>. {@link BlankEval} is
* converted to {@link NumberEval#ZERO}
*/
public static ValueEval DereferenceResult(ValueEval evaluationResult, OperationEvaluationContext ec)
{
ValueEval value;
if (ec == null)
{
throw new ArgumentNullException("OperationEvaluationContext ec is null");
}
if (ec.GetWorkbook() == null)
{
throw new ArgumentNullException("OperationEvaluationContext ec.getWorkbook() is null");
}
IEvaluationSheet evalSheet = ec.GetWorkbook().GetSheet(ec.SheetIndex);
IEvaluationCell evalCell = evalSheet.GetCell(ec.RowIndex, ec.ColumnIndex);
if (evalCell.IsPartOfArrayFormulaGroup && evaluationResult is AreaEval)
{
value = OperandResolver.GetElementFromArray((AreaEval)evaluationResult, evalCell);
}
else
{
value = DereferenceResult(evaluationResult, ec.RowIndex, ec.ColumnIndex);
}
return(value);
}
public Ptg[] GetFormulaTokens(IEvaluationCell EvalCell)
{
XSSFCell xssfCell = ((XSSFEvaluationCell)EvalCell).GetXSSFCell();
XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.Create((IWorkbook)this._uBook);
return(FormulaParser.Parse(xssfCell.CellFormula, (IFormulaParsingWorkbook)evaluationWorkbook, FormulaType.CELL, this._uBook.GetSheetIndex(xssfCell.Sheet)));
}
/**
* Gets the value from a non-formula cell.
* @param cell may be <c>null</c>
* @return {@link BlankEval} if cell is <c>null</c> or blank, never <c>null</c>
*/
/* package */
internal static ValueEval GetValueFromNonFormulaCell(IEvaluationCell cell)
{
if (cell == null)
{
return(BlankEval.instance);
}
CellType cellType = cell.CellType;
switch (cellType)
{
case CellType.NUMERIC:
return(new NumberEval(cell.NumericCellValue));
case CellType.STRING:
return(new StringEval(cell.StringCellValue));
case CellType.BOOLEAN:
return(BoolEval.ValueOf(cell.BooleanCellValue));
case CellType.BLANK:
return(BlankEval.instance);
case CellType.ERROR:
return(ErrorEval.ValueOf(cell.ErrorCellValue));
}
throw new Exception("Unexpected cell type (" + cellType + ")");
}
/**
* Retrieves a single value from an area evaluation utilizing the 2D indices of the cell
* within its own area reference to index the value in the area evaluation.
*
* @param ae area reference after evaluation
* @param cell the source cell of the formula that contains its 2D indices
* @return a <tt>NumberEval</tt>, <tt>StringEval</tt>, <tt>BoolEval</tt> or <tt>BlankEval</tt>. or <tt>ErrorEval<tt>
* Never <code>null</code>.
*/
public static ValueEval GetElementFromArray(AreaEval ae, IEvaluationCell cell)
{
CellRangeAddress range = cell.ArrayFormulaRange;
int relativeRowIndex = cell.RowIndex - range.FirstRow;
int relativeColIndex = cell.ColumnIndex - range.FirstColumn;
if (ae.IsColumn)
{
if (ae.IsRow)
{
return(ae.GetRelativeValue(0, 0));
}
else if (relativeRowIndex < ae.Height)
{
return(ae.GetRelativeValue(relativeRowIndex, 0));
}
}
else if (!ae.IsRow && relativeRowIndex < ae.Height && relativeColIndex < ae.Width)
{
return(ae.GetRelativeValue(relativeRowIndex, relativeColIndex));
}
else if (ae.IsRow && relativeColIndex < ae.Width)
{
return(ae.GetRelativeValue(0, relativeColIndex));
}
return(ErrorEval.NA);
}
/**
* Used by the lazy ref evals whenever they need To Get the value of a contained cell.
*/
/* package */
public ValueEval EvaluateReference(IEvaluationSheet sheet, int sheetIndex, int rowIndex,
int columnIndex, EvaluationTracker tracker)
{
IEvaluationCell cell = sheet.GetCell(rowIndex, columnIndex);
return(EvaluateAny(cell, sheetIndex, rowIndex, columnIndex, tracker));
}
public void NotifyDeleteCell(int bookIndex, int sheetIndex, IEvaluationCell cell)
{
if (cell.CellType == Frame.Utils.NPOI.SS.UserModel.CellType.FORMULA)
{
FormulaCellCacheEntry fcce = _formulaCellCache.Remove(cell);
if (fcce == null)
{
// formula cell Has not been evaluated yet
}
else
{
fcce.SetSensitiveInputCells(null);
fcce.RecurseClearCachedFormulaResults(_evaluationListener);
}
}
else
{
Loc loc = new Loc(bookIndex, sheetIndex, cell.RowIndex, cell.ColumnIndex);
PlainValueCellCacheEntry pcce = _plainCellCache.Get(loc);
if (pcce == null)
{
// cache entry doesn't exist. nothing To do
}
else
{
pcce.RecurseClearCachedFormulaResults(_evaluationListener);
}
}
}
public override void OnChangeFromBlankValue(int sheetIndex, int rowIndex, int columnIndex,
IEvaluationCell cell, ICacheEntry entry)
{
Log("changeFromBlank", rowIndex, columnIndex, entry.GetValue());
if (entry.GetValue() == null)
{ // hack to tell the difference between formula and plain value
// perhaps the API could be improved: onChangeFromBlankToValue, onChangeFromBlankToFormula
if (!_formulaCellsByCacheEntry.ContainsKey(entry))
{
_formulaCellsByCacheEntry.Add(entry, cell);
}
else
{
_formulaCellsByCacheEntry[entry] = cell;
}
}
else
{
Loc loc = new Loc(0, sheetIndex, rowIndex, columnIndex);
if (!_plainCellLocsByCacheEntry.ContainsKey(entry))
{
_plainCellLocsByCacheEntry.Add(entry, loc);
}
else
{
_plainCellLocsByCacheEntry[entry] = loc;
}
}
}
public Ptg[] GetFormulaTokens(IEvaluationCell EvalCell)
{
XSSFCell cell = ((XSSFEvaluationCell)EvalCell).GetXSSFCell();
XSSFEvaluationWorkbook frBook = XSSFEvaluationWorkbook.Create(_uBook);
return(FormulaParser.Parse(cell.CellFormula, frBook, FormulaType.Cell, _uBook.GetSheetIndex(cell.Sheet)));
}
public override Ptg[] GetFormulaTokens(IEvaluationCell evalCell)
{
XSSFCell cell = ((XSSFEvaluationCell)evalCell).GetXSSFCell();
int sheetIndex = _uBook.GetSheetIndex(cell.Sheet);
int rowIndex = cell.RowIndex;
return(FormulaParser.Parse(cell.GetCellFormula(this), this, FormulaType.Cell, sheetIndex, rowIndex));
}
public Ptg[] GetFormulaTokens(IEvaluationCell cell)
{
if (cell is ForkedEvaluationCell)
{
// doesn't happen yet because formulas cannot be modified from the master workbook
throw new Exception("Updated formulas not supported yet");
}
return(_masterBook.GetFormulaTokens(cell));
}
/**
* @return <c>null</c> if not found
*/
public FormulaCellCacheEntry Get(IEvaluationCell cell)
{
if (_formulaEntriesByCell.ContainsKey(cell.IdentityKey))
{
return((FormulaCellCacheEntry)_formulaEntriesByCell[cell.IdentityKey]);
}
else
{
return(null);
}
}
public FormulaCellCacheEntry GetOrCreateFormulaCellEntry(IEvaluationCell cell)
{
FormulaCellCacheEntry result = _formulaCellCache.Get(cell);
if (result == null)
{
result = new FormulaCellCacheEntry();
_formulaCellCache.Put(cell, result);
}
return(result);
}
public IEvaluationCell GetCell(int rowIndex, int columnIndex)
{
// cache for performance: ~30% speedup due to caching
if (_cellCache == null)
{
_cellCache = new Dictionary <CellKey, IEvaluationCell>(_xs.LastRowNum * 3);
foreach (IRow row in _xs)
{
int rowNum = row.RowNum;
foreach (ICell cell in row)
{
// cast is safe, the iterator is just defined using the interface
CellKey key1 = new CellKey(rowNum, cell.ColumnIndex);
IEvaluationCell evalcell1 = new XSSFEvaluationCell((XSSFCell)cell, this);
_cellCache.Add(key1, evalcell1);
}
}
}
CellKey key = new CellKey(rowIndex, columnIndex);
IEvaluationCell evalcell = null;
if (_cellCache.ContainsKey(key))
{
evalcell = _cellCache[key];
}
// If cache is stale, update cache with this one cell
// This is a compromise between rebuilding the entire cache
// (which would quickly defeat the benefit of the cache)
// and not caching at all.
// See bug 59958: Add cells on the fly to the evaluation sheet cache on cache miss
if (evalcell == null)
{
XSSFRow row = _xs.GetRow(rowIndex) as XSSFRow;
if (row == null)
{
return(null);
}
XSSFCell cell = row.GetCell(columnIndex) as XSSFCell;
if (cell == null)
{
return(null);
}
evalcell = new XSSFEvaluationCell(cell, this);
_cellCache[key] = evalcell;
}
return(evalcell);
}
public Ptg[] GetFormulaTokens(IEvaluationCell evalCell)
{
ICell cell = ((HSSFEvaluationCell)evalCell).HSSFCell;
//if (false)
//{
// // re-parsing the formula text also works, but is a waste of time
// // It is useful from time to time to run all unit tests with this code
// // to make sure that all formulas POI can evaluate can also be parsed.
// return FormulaParser.Parse(cell.CellFormula, _uBook, FormulaType.CELL, _uBook.GetSheetIndex(cell.Sheet));
//}
FormulaRecordAggregate fr = (FormulaRecordAggregate)((HSSFCell)cell).CellValueRecord;
return(fr.FormulaTokens);
}
public override void OnStartEvaluate(IEvaluationCell cell, ICacheEntry entry)
{
if (!_formulaCellsByCacheEntry.ContainsKey(entry))
{
_formulaCellsByCacheEntry.Add(entry, cell);
}
else
{
_formulaCellsByCacheEntry[entry] = cell;
}
ICell hc = _book.GetSheetAt(0).GetRow(cell.RowIndex).GetCell(cell.ColumnIndex);
Log("start", cell.RowIndex, cell.ColumnIndex, FormulaExtractor.GetPtgs(hc));
}
public static ValueEval EvaluateArrayFunction(ArrayFunction func, ValueEval[] args,
OperationEvaluationContext ec)
{
IEvaluationSheet evalSheet = ec.GetWorkbook().GetSheet(ec.SheetIndex);
IEvaluationCell evalCell = evalSheet.GetCell(ec.RowIndex, ec.ColumnIndex);
if (evalCell != null)
{
if (evalCell.IsPartOfArrayFormulaGroup)
{
// array arguments must be evaluated relative to the function defining range
Util.CellRangeAddress ca = evalCell.ArrayFormulaRange;
return(func.EvaluateArray(args, ca.FirstRow, ca.FirstColumn));
}
else if (ec.IsArraymode)
{
return(func.EvaluateArray(args, ec.RowIndex, ec.ColumnIndex));
}
}
return(null);
}
public int Compare(ICacheEntry oa, ICacheEntry ob)
{
IEvaluationCell a = GetCell(oa);
IEvaluationCell b = GetCell(ob);
int cmp;
cmp = a.RowIndex - b.RowIndex;
if (cmp != 0)
{
return(cmp);
}
cmp = a.ColumnIndex - b.ColumnIndex;
if (cmp != 0)
{
return(cmp);
}
if (a.Sheet == b.Sheet)
{
return(0);
}
throw new Exception("Incomplete code - don't know how to order sheets");
}
/**
* @return whether cell at rowIndex and columnIndex is a subtotal
* @see org.apache.poi.ss.formula.functions.Subtotal
*/
public bool IsSubTotal(int rowIndex, int columnIndex)
{
bool subtotal = false;
IEvaluationCell cell = Sheet.GetCell(rowIndex, columnIndex);
if (cell != null && cell.CellType == CellType.Formula)
{
IEvaluationWorkbook wb = _bookEvaluator.Workbook;
foreach (Ptg ptg in wb.GetFormulaTokens(cell))
{
if (ptg is FuncVarPtg)
{
FuncVarPtg f = (FuncVarPtg)ptg;
if ("SUBTOTAL".Equals(f.Name))
{
subtotal = true;
break;
}
}
}
}
return(subtotal);
}
public override void OnClearCachedValue(ICacheEntry entry)
{
int rowIndex;
int columnIndex;
IEvaluationCell cell = _formulaCellsByCacheEntry.ContainsKey(entry) ? _formulaCellsByCacheEntry[entry] : null;
if (cell == null)
{
Loc loc = _plainCellLocsByCacheEntry.ContainsKey(entry) ? _plainCellLocsByCacheEntry[entry] : null;
if (loc == null)
{
throw new InvalidOperationException("can't find cell or location");
}
rowIndex = loc.RowIndex;
columnIndex = loc.ColumnIndex;
}
else
{
rowIndex = cell.RowIndex;
columnIndex = cell.ColumnIndex;
}
Log("clear", rowIndex, columnIndex, entry.GetValue());
}
public Ptg[] GetFormulaTokens(IEvaluationCell evalCell)
{
ICell cell = ((HSSFEvaluationCell)evalCell).HSSFCell;
//if (false)
//{
// // re-parsing the formula text also works, but is a waste of time
// // It is useful from time to time to run all unit tests with this code
// // to make sure that all formulas POI can evaluate can also be parsed.
// try
// {
// return HSSFFormulaParser.Parse(cell.CellFormula, _uBook, FormulaType.CELL, _uBook.GetSheetIndex(cell.GetSheet()));
// }
// catch (FormulaParseException e)
// {
// // Note - as of Bugzilla 48036 (svn r828244, r828247) POI is capable of evaluating
// // IntesectionPtg. However it is still not capable of parsing it.
// // So FormulaEvalTestData.xls now contains a few formulas that produce errors here.
// logger.Log(POILogger.ERROR, e.Message);
// }
//}
FormulaRecordAggregate fr = (FormulaRecordAggregate)((HSSFCell)cell).CellValueRecord;
return(fr.FormulaTokens);
}
/**
* Should be called To tell the cell value cache that the specified cell Has just been
* deleted.
*/
public void NotifyDeleteCell(IEvaluationCell cell)
{
int sheetIndex = GetSheetIndex(cell.Sheet);
_cache.NotifyDeleteCell(_workbookIx, sheetIndex, cell);
}
public void NotifyDeleteCell(int bookIndex, int sheetIndex, IEvaluationCell cell)
{
if (cell.CellType == NPOI.SS.UserModel.CellType.FORMULA)
{
FormulaCellCacheEntry fcce = _formulaCellCache.Remove(cell);
if (fcce == null)
{
// formula cell Has not been evaluated yet
}
else
{
fcce.SetSensitiveInputCells(null);
fcce.RecurseClearCachedFormulaResults(_evaluationListener);
}
}
else
{
Loc loc = new Loc(bookIndex, sheetIndex, cell.RowIndex, cell.ColumnIndex);
PlainValueCellCacheEntry pcce = _plainCellCache.Get(loc);
if (pcce == null)
{
// cache entry doesn't exist. nothing To do
}
else
{
pcce.RecurseClearCachedFormulaResults(_evaluationListener);
}
}
}
public override void OnStartEvaluate(IEvaluationCell cell, ICacheEntry entry)
{
_countCacheMisses++;
}
public FormulaCellCacheEntry Remove(IEvaluationCell cell)
{
FormulaCellCacheEntry tmp = (FormulaCellCacheEntry)_formulaEntriesByCell[cell.IdentityKey];
_formulaEntriesByCell.Remove(cell);
return tmp;
}
public Ptg[] GetFormulaTokens(IEvaluationCell EvalCell)
{
XSSFCell cell = ((XSSFEvaluationCell)EvalCell).GetXSSFCell();
XSSFEvaluationWorkbook frBook = XSSFEvaluationWorkbook.Create(_uBook);
String formulaText = CleanXSSFFormulaText(cell.CellFormula);
return FormulaParser.Parse(formulaText, frBook, FormulaType.CELL, _uBook.GetSheetIndex(cell.Sheet));
}
/**
* @return never <c>null</c>, never {@link BlankEval}
*/
private ValueEval EvaluateAny(IEvaluationCell srcCell, int sheetIndex,
int rowIndex, int columnIndex, EvaluationTracker tracker)
{
bool shouldCellDependencyBeRecorded = _stabilityClassifier == null ? true
: !_stabilityClassifier.IsCellFinal(sheetIndex, rowIndex, columnIndex);
ValueEval result;
if (srcCell == null || srcCell.CellType != CellType.FORMULA)
{
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;
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);
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 " + cce.GetValue().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 cce.GetValue();
return(result);
}
/**
* @return never <c>null</c>, never {@link BlankEval}
*/
private ValueEval EvaluateAny(IEvaluationCell srcCell, int sheetIndex,
int rowIndex, int columnIndex, EvaluationTracker tracker)
{
bool shouldCellDependencyBeRecorded = _stabilityClassifier == null ? true
: !_stabilityClassifier.IsCellFinal(sheetIndex, rowIndex, columnIndex);
ValueEval result;
if (srcCell == null || srcCell.CellType != CellType.Formula)
{
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;
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);
result = EvaluateFormula(ec, ptgs);
evalListener.OnEndEvaluate(cce, result);
}
tracker.UpdateCacheResult(result);
}
catch (NotImplementedException e)
{
throw AddExceptionInfo(e, sheetIndex, rowIndex, columnIndex);
}
catch (RuntimeException re)
{
if (re.InnerException is WorkbookNotFoundException && _ignoreMissingWorkbooks)
{
LogInfo(re.InnerException.Message + " - Continuing with cached value!");
switch (srcCell.CachedFormulaResultType)
{
case CellType.Numeric:
result = new NumberEval(srcCell.NumericCellValue);
break;
case CellType.String:
result = new StringEval(srcCell.StringCellValue);
break;
case CellType.Blank:
result = BlankEval.instance;
break;
case CellType.Boolean:
result = BoolEval.ValueOf(srcCell.BooleanCellValue);
break;
case CellType.Error:
result = ErrorEval.ValueOf(srcCell.ErrorCellValue);
break;
case CellType.Formula:
default:
throw new RuntimeException("Unexpected cell type '" + srcCell.CellType + "' found!");
}
}
else
{
throw re;
}
}
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 " + cce.GetValue());
}
// 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 cce.GetValue();
return result;
}
/**
* Gets the value from a non-formula cell.
* @param cell may be <c>null</c>
* @return {@link BlankEval} if cell is <c>null</c> or blank, never <c>null</c>
*/
/* package */
internal static ValueEval GetValueFromNonFormulaCell(IEvaluationCell cell)
{
if (cell == null)
{
return BlankEval.instance;
}
CellType cellType = cell.CellType;
switch (cellType)
{
case CellType.Numeric:
return new NumberEval(cell.NumericCellValue);
case CellType.String:
return new StringEval(cell.StringCellValue);
case CellType.Boolean:
return BoolEval.ValueOf(cell.BooleanCellValue);
case CellType.Blank:
return BlankEval.instance;
case CellType.Error:
return ErrorEval.ValueOf(cell.ErrorCellValue);
}
throw new Exception("Unexpected cell type (" + cellType + ")");
}
/**
* @return <c>null</c> if not found
*/
public FormulaCellCacheEntry Get(IEvaluationCell cell)
{
return((FormulaCellCacheEntry)_formulaEntriesByCell[cell.IdentityKey]);
}
public virtual void OnChangeFromBlankValue(int sheetIndex, int rowIndex, int columnIndex,
IEvaluationCell cell, ICacheEntry entry)
{
// do nothing
}
public Ptg[] GetFormulaTokens(IEvaluationCell evalCell)
{
ICell cell = ((HSSFEvaluationCell)evalCell).HSSFCell;
//if (false)
//{
// // re-parsing the formula text also works, but is a waste of time
// // It is useful from time to time to run all unit tests with this code
// // to make sure that all formulas POI can evaluate can also be parsed.
// return FormulaParser.Parse(cell.CellFormula, _uBook, FormulaType.CELL, _uBook.GetSheetIndex(cell.Sheet));
//}
FormulaRecordAggregate fr = (FormulaRecordAggregate)((HSSFCell)cell).CellValueRecord;
return fr.FormulaTokens;
}
public Ptg[] GetFormulaTokens(IEvaluationCell EvalCell)
{
XSSFCell cell = ((XSSFEvaluationCell)EvalCell).GetXSSFCell();
XSSFEvaluationWorkbook frBook = XSSFEvaluationWorkbook.Create(_uBook);
return FormulaParser.Parse(cell.CellFormula, frBook, FormulaType.Cell, _uBook.GetSheetIndex(cell.Sheet));
}
public Ptg[] GetFormulaTokens(IEvaluationCell evalCell)
{
ICell cell = ((HSSFEvaluationCell)evalCell).HSSFCell;
//if (false)
//{
// // re-parsing the formula text also works, but is a waste of time
// // It is useful from time to time to run all unit tests with this code
// // to make sure that all formulas POI can evaluate can also be parsed.
// try
// {
// return HSSFFormulaParser.Parse(cell.CellFormula, _uBook, FormulaType.Cell, _uBook.GetSheetIndex(cell.GetSheet()));
// }
// catch (FormulaParseException e)
// {
// // Note - as of Bugzilla 48036 (svn r828244, r828247) POI is capable of evaluating
// // IntesectionPtg. However it is still not capable of parsing it.
// // So FormulaEvalTestData.xls now contains a few formulas that produce errors here.
// logger.Log(POILogger.ERROR, e.Message);
// }
//}
FormulaRecordAggregate fr = (FormulaRecordAggregate)((HSSFCell)cell).CellValueRecord;
return fr.FormulaTokens;
}
public void NotifyUpdateCell(int bookIndex, int sheetIndex, IEvaluationCell cell)
{
FormulaCellCacheEntry fcce = _formulaCellCache.Get(cell);
int rowIndex = cell.RowIndex;
int columnIndex = cell.ColumnIndex;
Loc loc = new Loc(bookIndex, sheetIndex, cell.RowIndex, cell.ColumnIndex);
PlainValueCellCacheEntry pcce = _plainCellCache.Get(loc);
if (cell.CellType == Frame.Utils.NPOI.SS.UserModel.CellType.FORMULA)
{
if (fcce == null)
{
fcce = new FormulaCellCacheEntry();
if (pcce == null)
{
if (_evaluationListener != null)
{
_evaluationListener.OnChangeFromBlankValue(sheetIndex, rowIndex,
columnIndex, cell, fcce);
}
UpdateAnyBlankReferencingFormulas(bookIndex, sheetIndex, rowIndex, columnIndex);
}
_formulaCellCache.Put(cell, fcce);
}
else
{
fcce.RecurseClearCachedFormulaResults(_evaluationListener);
fcce.ClearFormulaEntry();
}
if (pcce == null)
{
// was formula cell before - no Change of type
}
else
{
// changing from plain cell To formula cell
pcce.RecurseClearCachedFormulaResults(_evaluationListener);
_plainCellCache.Remove(loc);
}
}
else
{
ValueEval value = WorkbookEvaluator.GetValueFromNonFormulaCell(cell);
if (pcce == null)
{
if (value != BlankEval.instance)
{
pcce = new PlainValueCellCacheEntry(value);
if (fcce == null)
{
if (_evaluationListener != null)
{
_evaluationListener.OnChangeFromBlankValue(sheetIndex, rowIndex,
columnIndex, cell, pcce);
}
UpdateAnyBlankReferencingFormulas(bookIndex, sheetIndex, rowIndex, columnIndex);
}
_plainCellCache.Put(loc, pcce);
}
}
else
{
if (pcce.UpdateValue(value))
{
pcce.RecurseClearCachedFormulaResults(_evaluationListener);
}
if (value == BlankEval.instance)
{
_plainCellCache.Remove(loc);
}
}
if (fcce == null)
{
// was plain cell before - no Change of type
}
else
{
// was formula cell before - now a plain value
_formulaCellCache.Remove(cell);
fcce.SetSensitiveInputCells(null);
fcce.RecurseClearCachedFormulaResults(_evaluationListener);
}
}
}
public void Put(IEvaluationCell cell, FormulaCellCacheEntry entry)
{
_formulaEntriesByCell[cell.IdentityKey] = entry;
}
public FormulaCellCacheEntry GetOrCreateFormulaCellEntry(IEvaluationCell cell)
{
FormulaCellCacheEntry result = _formulaCellCache.Get(cell);
if (result == null)
{
result = new FormulaCellCacheEntry();
_formulaCellCache.Put(cell, result);
}
return result;
}
public override void OnStartEvaluate(IEvaluationCell cell, ICacheEntry entry)
{
_evalCount++;
}
public void NotifyUpdateCell(int bookIndex, int sheetIndex, IEvaluationCell cell)
{
FormulaCellCacheEntry fcce = _formulaCellCache.Get(cell);
int rowIndex = cell.RowIndex;
int columnIndex = cell.ColumnIndex;
Loc loc = new Loc(bookIndex, sheetIndex, cell.RowIndex, cell.ColumnIndex);
PlainValueCellCacheEntry pcce = _plainCellCache.Get(loc);
if (cell.CellType == NPOI.SS.UserModel.CellType.FORMULA)
{
if (fcce == null)
{
fcce = new FormulaCellCacheEntry();
if (pcce == null)
{
if (_evaluationListener != null)
{
_evaluationListener.OnChangeFromBlankValue(sheetIndex, rowIndex,
columnIndex, cell, fcce);
}
UpdateAnyBlankReferencingFormulas(bookIndex, sheetIndex, rowIndex, columnIndex);
}
_formulaCellCache.Put(cell, fcce);
}
else
{
fcce.RecurseClearCachedFormulaResults(_evaluationListener);
fcce.ClearFormulaEntry();
}
if (pcce == null)
{
// was formula cell before - no Change of type
}
else
{
// changing from plain cell To formula cell
pcce.RecurseClearCachedFormulaResults(_evaluationListener);
_plainCellCache.Remove(loc);
}
}
else
{
ValueEval value = WorkbookEvaluator.GetValueFromNonFormulaCell(cell);
if (pcce == null)
{
if (value != BlankEval.instance)
{
pcce = new PlainValueCellCacheEntry(value);
if (fcce == null)
{
if (_evaluationListener != null)
{
_evaluationListener.OnChangeFromBlankValue(sheetIndex, rowIndex,
columnIndex, cell, pcce);
}
UpdateAnyBlankReferencingFormulas(bookIndex, sheetIndex, rowIndex, columnIndex);
}
_plainCellCache.Put(loc, pcce);
}
}
else
{
if (pcce.UpdateValue(value))
{
pcce.RecurseClearCachedFormulaResults(_evaluationListener);
}
if (value == BlankEval.instance)
{
_plainCellCache.Remove(loc);
}
}
if (fcce == null)
{
// was plain cell before - no Change of type
}
else
{
// was formula cell before - now a plain value
_formulaCellCache.Remove(cell);
fcce.SetSensitiveInputCells(null);
fcce.RecurseClearCachedFormulaResults(_evaluationListener);
}
}
}
/**
* @return <c>null</c> if not found
*/
public FormulaCellCacheEntry Get(IEvaluationCell cell)
{
return (FormulaCellCacheEntry)_formulaEntriesByCell[cell.IdentityKey];
}
public void Put(IEvaluationCell cell, FormulaCellCacheEntry entry)
{
_formulaEntriesByCell[cell.IdentityKey] = entry;
}
public ValueEval Evaluate(IEvaluationCell srcCell)
{
int sheetIndex = GetSheetIndex(srcCell.Sheet);
return EvaluateAny(srcCell, sheetIndex, srcCell.RowIndex, srcCell.ColumnIndex, new EvaluationTracker(_cache));
}
public virtual void OnStartEvaluate(IEvaluationCell cell, ICacheEntry entry)
{
// do nothing
}
/**
* @return never <c>null</c>, never {@link BlankEval}
*/
private ValueEval EvaluateAny(IEvaluationCell srcCell, int sheetIndex,
int rowIndex, int columnIndex, EvaluationTracker tracker)
{
bool shouldCellDependencyBeRecorded = _stabilityClassifier == null ? true
: !_stabilityClassifier.IsCellFinal(sheetIndex, rowIndex, columnIndex);
ValueEval result;
if (srcCell == null || srcCell.CellType != CellType.FORMULA)
{
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;
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);
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 " + cce.GetValue().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 cce.GetValue();
return result;
}
