/**
* Used by the lazy ref evals whenever they need To Get the value of a contained cell.
*/
/* package */
public ValueEval EvaluateReference(EvaluationSheet sheet, int sheetIndex, int rowIndex,
int columnIndex, EvaluationTracker tracker)
{
EvaluationCell cell = sheet.GetCell(rowIndex, columnIndex);
return(EvaluateAny(cell, sheetIndex, rowIndex, columnIndex, tracker));
}
public void NotifyDeleteCell(int bookIndex, int sheetIndex, EvaluationCell 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);
}
}
}
/**
* 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(EvaluationCell 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 + ")");
}
public FormulaCellCacheEntry Remove(EvaluationCell cell)
{
FormulaCellCacheEntry tmp = (FormulaCellCacheEntry)_formulaEntriesByCell[cell];
_formulaEntriesByCell.Remove(cell);
return(tmp);
}
public override void OnStartEvaluate(EvaluationCell cell, ICacheEntry entry)
{
_formulaCellsByCacheEntry.Remove(entry);
_formulaCellsByCacheEntry.Add(entry, cell);
Cell hc = _book.GetSheetAt(0).GetRow(cell.RowIndex).GetCell(cell.ColumnIndex);
log("start", cell.RowIndex, cell.ColumnIndex, FormulaExtractor.GetPtgs(hc));
}
public FormulaCellCacheEntry GetOrCreateFormulaCellEntry(EvaluationCell cell)
{
FormulaCellCacheEntry result = _formulaCellCache.Get(cell);
if (result == null)
{
result = new FormulaCellCacheEntry();
_formulaCellCache.Put(cell, result);
}
return(result);
}
public FormulaCellCacheEntry GetOrCreateFormulaCellEntry(EvaluationCell cell)
{
FormulaCellCacheEntry result = _formulaCellCache.Get(cell);
if (result == null)
{
result = new FormulaCellCacheEntry();
_formulaCellCache.Put(cell, result);
}
return result;
}
public Ptg[] GetFormulaTokens(EvaluationCell evalCell)
{
Cell 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 int Compare(Object oa, Object ob)
{
EvaluationCell a = GetCell(oa);
EvaluationCell 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 InvalidOperationException("Incomplete code - don't know how to order sheets");
}
public override void OnClearCachedValue(ICacheEntry entry)
{
int rowIndex;
int columnIndex;
EvaluationCell cell = (EvaluationCell)_formulaCellsByCacheEntry[entry];
if (cell == null)
{
Loc loc = (Loc)_plainCellLocsByCacheEntry[entry];
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 void Put(EvaluationCell cell, FormulaCellCacheEntry entry)
{
_formulaEntriesByCell[cell] = entry;
}
/**
* @return <c>null</c> if not found
*/
public FormulaCellCacheEntry Get(EvaluationCell cell)
{
return((FormulaCellCacheEntry)_formulaEntriesByCell[cell]);
}
public FormulaCellCacheEntry Remove(EvaluationCell cell)
{
FormulaCellCacheEntry tmp = (FormulaCellCacheEntry)_formulaEntriesByCell[cell];
_formulaEntriesByCell.Remove(cell);
return tmp;
}
public void Put(EvaluationCell cell, FormulaCellCacheEntry entry)
{
_formulaEntriesByCell[cell] = entry;
}
public ValueEval Evaluate(EvaluationCell srcCell)
{
int sheetIndex = GetSheetIndex(srcCell.Sheet);
return(EvaluateAny(srcCell, sheetIndex, srcCell.RowIndex, srcCell.ColumnIndex, new EvaluationTracker(_cache)));
}
/**
* @return never <c>null</c>, never {@link BlankEval}
*/
private ValueEval EvaluateAny(EvaluationCell srcCell, int sheetIndex,
int rowIndex, int columnIndex, EvaluationTracker tracker)
{
if (srcCell == null || srcCell.CellType != CellType.FORMULA)
{
ValueEval result = GetValueFromNonFormulaCell(srcCell);
tracker.AcceptPlainValueDependency(_workbookIx, sheetIndex, rowIndex, columnIndex, result);
return result;
}
FormulaCellCacheEntry cce = _cache.GetOrCreateFormulaCellEntry(srcCell);
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
{
ValueEval result;
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);
}
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());
}
return cce.GetValue();
}
/**
* @return never <c>null</c>, never {@link BlankEval}
*/
private ValueEval EvaluateAny(EvaluationCell srcCell, int sheetIndex,
int rowIndex, int columnIndex, EvaluationTracker tracker)
{
if (srcCell == null || srcCell.CellType != CellType.FORMULA)
{
ValueEval result = GetValueFromNonFormulaCell(srcCell);
tracker.AcceptPlainValueDependency(_workbookIx, sheetIndex, rowIndex, columnIndex, result);
return(result);
}
FormulaCellCacheEntry cce = _cache.GetOrCreateFormulaCellEntry(srcCell);
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
{
ValueEval result;
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);
}
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());
}
return(cce.GetValue());
}
/**
* Should be called To tell the cell value cache that the specified cell Has just been
* deleted.
*/
public void NotifyDeleteCell(EvaluationCell cell)
{
int sheetIndex = GetSheetIndex(cell.Sheet);
_cache.NotifyDeleteCell(_workbookIx, sheetIndex, cell);
}
public void NotifyUpdateCell(int bookIndex, int sheetIndex, EvaluationCell cell)
{
FormulaCellCacheEntry fcce = _formulaCellCache.Get(cell);
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)
{
if (pcce == null)
{
UpdateAnyBlankReferencingFormulas(bookIndex, sheetIndex, cell.RowIndex, cell.ColumnIndex);
}
fcce = new FormulaCellCacheEntry();
_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 (fcce == null)
{
UpdateAnyBlankReferencingFormulas(bookIndex, sheetIndex, cell.RowIndex, cell.ColumnIndex);
}
pcce = new PlainValueCellCacheEntry(value);
_plainCellCache.Put(loc, pcce);
if (_evaluationListener != null)
{
_evaluationListener.OnReadPlainValue(sheetIndex, cell.RowIndex, cell.ColumnIndex, pcce);
}
}
else
{
if (pcce.UpdateValue(value))
{
pcce.RecurseClearCachedFormulaResults(_evaluationListener);
}
}
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);
}
}
}
/**
* 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(EvaluationCell cell)
{
if (cell == null)
{
return BlankEval.INSTANCE;
}
int cellType = cell.CellType;
switch (cellType)
{
case HSSFCell.CELL_TYPE_NUMERIC:
return new NumberEval(cell.NumericCellValue);
case HSSFCell.CELL_TYPE_STRING:
return new StringEval(cell.StringCellValue);
case HSSFCell.CELL_TYPE_BOOLEAN:
return BoolEval.ValueOf(cell.BooleanCellValue);
case HSSFCell.CELL_TYPE_BLANK:
return BlankEval.INSTANCE;
case HSSFCell.CELL_TYPE_ERROR:
return ErrorEval.ValueOf(cell.ErrorCellValue);
}
throw new Exception("Unexpected cell type (" + cellType + ")");
}
public ReplPage(object context = null)
{
evaluatorHost = new EvaluatorHost(context ?? "a");
evaluatorHost.Evaluate("1");
Content =
new Grid
{
RowSpacing = 2,
ColumnSpacing = 2,
BackgroundColor = Color.Gray,
RowDefinitions =
{
new RowDefinition()
{
Height = new GridLength(50, GridUnitType.Absolute)
},
new RowDefinition(),
new RowDefinition {
Height = new GridLength(100, GridUnitType.Absolute)
},
new RowDefinition {
Height = new GridLength(30, GridUnitType.Absolute)
}
},
ColumnDefinitions =
{
new ColumnDefinition(),
new ColumnDefinition {
Width = new GridLength(80, GridUnitType.Absolute)
}
},
Children =
{
new Grid
{
BackgroundColor = Color.Black,
Children =
{
new Label
{
TextColor = Color.White,
Text = "HAPPY HAPPY REPL",
FontFamily = "Apple-Kid",
FontSize = 42,
HorizontalTextAlignment = TextAlignment.Center,
VerticalTextAlignment = TextAlignment.Center,
}
}
}.Row(0).ColSpan(2),
new Grid
{
BackgroundColor = Color.Black
}.Row(3).ColSpan(2),
new CollectionView
{
BackgroundColor = Color.Black,
ItemsSource = Evaluations,
ItemTemplate = new DataTemplate(() =>
{
var cell = new EvaluationCell();
cell.Bind(EvaluationCell.EvaluationProperty);
return(cell);
}),
IsVisible = false
}
.Assign(out CollectionView)
.Row(1).ColSpan(2),
GetReplView(),
new Editor
{
IsSpellCheckEnabled = false,
Keyboard = Keyboard.Create(KeyboardFlags.None),
BackgroundColor = Color.Black,
TextColor = Color.White,
}.Row(2)
.Invoke(x =>
{
x.TextChanged += async delegate
{
if (string.IsNullOrWhiteSpace(x.Text))
{
return;
}
var lastChar = x.Text.LastOrDefault();
if (lastChar == '\n')
{
await Evaluate(Editor.Text);
return;
}
};
})
.Assign(out Editor),
new Button
{
Text = "Eval",
TextColor = Color.White,
FontFamily = "Apple-Kid",
FontSize = 38,
BackgroundColor = Color.Black
}.Row(2).Col(1)
.Invoke(x =>
{
x.Clicked += async delegate{ await Evaluate(Editor.Text); };
}),
}
}.Assign(out MainGrid);
}
public override void OnStartEvaluate(EvaluationCell cell, ICacheEntry entry)
{
_evalCount++;
}
public Ptg[] GetFormulaTokens(EvaluationCell evalCell)
{
Cell 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 void NotifyDeleteCell(int bookIndex, int sheetIndex, EvaluationCell 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 ValueEval Evaluate(EvaluationCell srcCell)
{
int sheetIndex = GetSheetIndex(srcCell.Sheet);
return EvaluateAny(srcCell, sheetIndex, srcCell.RowIndex, srcCell.ColumnIndex, new EvaluationTracker(_cache));
}
public void NotifyUpdateCell(int bookIndex, int sheetIndex, EvaluationCell cell)
{
FormulaCellCacheEntry fcce = _formulaCellCache.Get(cell);
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)
{
if (pcce == null)
{
UpdateAnyBlankReferencingFormulas(bookIndex, sheetIndex, cell.RowIndex, cell.ColumnIndex);
}
fcce = new FormulaCellCacheEntry();
_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 (fcce == null)
{
UpdateAnyBlankReferencingFormulas(bookIndex, sheetIndex, cell.RowIndex, cell.ColumnIndex);
}
pcce = new PlainValueCellCacheEntry(value);
_plainCellCache.Put(loc, pcce);
if (_evaluationListener != null)
{
_evaluationListener.OnReadPlainValue(sheetIndex, cell.RowIndex, cell.ColumnIndex, pcce);
}
}
else
{
if (pcce.UpdateValue(value))
{
pcce.RecurseClearCachedFormulaResults(_evaluationListener);
}
}
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);
}
}
}
/**
* 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(EvaluationCell 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 + ")");
}
public override void OnStartEvaluate(EvaluationCell cell, ICacheEntry entry)
{
_countCacheMisses++;
}
public override void OnEndEvaluate(ICacheEntry entry, ValueEval result)
{
EvaluationCell cell = (EvaluationCell)_formulaCellsByCacheEntry[entry];
log("end", cell.RowIndex, cell.ColumnIndex, result);
}
public virtual void OnStartEvaluate(EvaluationCell cell, ICacheEntry entry)
{
// do nothing
}
public override void OnClearDependentCachedValue(ICacheEntry entry, int depth)
{
EvaluationCell cell = (EvaluationCell)_formulaCellsByCacheEntry[entry];
log("Clear" + depth, cell.RowIndex, cell.ColumnIndex, entry.GetValue());
}
/**
* @return <c>null</c> if not found
*/
public FormulaCellCacheEntry Get(EvaluationCell cell)
{
return (FormulaCellCacheEntry)_formulaEntriesByCell[cell];
}
public Ptg[] GetFormulaTokens(EvaluationCell evalCell)
{
HSSFCell cell = ((HSSFEvaluationCell)evalCell).GetHSSFCell();
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 HSSFFormulaParser.Parse(cell.CellFormula, _uBook);
}
FormulaRecord fr = ((FormulaRecordAggregate)cell.CellValueRecord).FormulaRecord;
return fr.ParsedExpression;
}
/**
* Should be called To tell the cell value cache that the specified cell Has just been
* deleted.
*/
public void NotifyDeleteCell(EvaluationCell cell)
{
int sheetIndex = GetSheetIndex(cell.Sheet);
_cache.NotifyDeleteCell(_workbookIx, sheetIndex, cell);
}
