private CollaboratingWorkbooksEnvironment(String[] workbookNames, WorkbookEvaluator[] evaluators, int nItems)
{
Hashtable m = new Hashtable(nItems * 3 / 2);
Hashtable uniqueEvals = new Hashtable(nItems * 3 / 2);
for (int i = 0; i < nItems; i++)
{
String wbName = workbookNames[i];
WorkbookEvaluator wbEval = evaluators[i];
if (m.ContainsKey(wbName))
{
throw new ArgumentException("Duplicate workbook name '" + wbName + "'");
}
if (uniqueEvals.ContainsKey(wbEval))
{
String msg = "Attempted To register same workbook under names '"
+ uniqueEvals[(wbEval) + "' and '" + wbName + "'"];
throw new ArgumentException(msg);
}
uniqueEvals[wbEval]=wbName;
m[wbName] = wbEval;
}
UnhookOldEnvironments(evaluators);
//HookNewEnvironment(evaluators, this); - moved to Setup method above
_unhooked = false;
_evaluators = evaluators;
_evaluatorsByName = m;
}
public SheetRefEvaluator(WorkbookEvaluator bookEvaluator, EvaluationTracker tracker,
EvaluationWorkbook _workbook, int sheetIndex)
{
_bookEvaluator = bookEvaluator;
_tracker = tracker;
_sheet = _workbook.GetSheet(sheetIndex);
_sheetIndex = sheetIndex;
}
public SheetRefEvaluator(WorkbookEvaluator bookEvaluator, EvaluationTracker tracker, int sheetIndex)
{
if (sheetIndex < 0)
{
throw new ArgumentException("Invalid sheetIndex: " + sheetIndex + ".");
}
_bookEvaluator = bookEvaluator;
_tracker = tracker;
_sheetIndex = sheetIndex;
}
public OperationEvaluationContext(WorkbookEvaluator bookEvaluator, IEvaluationWorkbook workbook, int sheetIndex, int srcRowNum,
int srcColNum, EvaluationTracker tracker)
{
_bookEvaluator = bookEvaluator;
_workbook = workbook;
_sheetIndex = sheetIndex;
_rowIndex = srcRowNum;
_columnIndex = srcColNum;
_tracker = tracker;
}
public static void Setup(String[] workbookNames, WorkbookEvaluator[] evaluators)
{
int nItems = workbookNames.Length;
if (evaluators.Length != nItems)
{
throw new ArgumentException("Number of workbook names is " + nItems
+ " but number of evaluators is " + evaluators.Length);
}
if (nItems < 1)
{
throw new ArgumentException("Must provide at least one collaborating worbook");
}
new CollaboratingWorkbooksEnvironment(workbookNames, evaluators, nItems);
}
private CollaboratingWorkbooksEnvironment(Dictionary <String, WorkbookEvaluator> evaluatorsByName, WorkbookEvaluator[] evaluators)
{
Dictionary <WorkbookEvaluator, String> uniqueEvals = new Dictionary <WorkbookEvaluator, String>(evaluators.Length);
foreach (String wbName in evaluatorsByName.Keys)
{
WorkbookEvaluator wbEval = evaluatorsByName[(wbName)];
if (uniqueEvals.ContainsKey(wbEval))
{
String msg = "Attempted to register same workbook under names '"
+ uniqueEvals[(wbEval)] + "' and '" + wbName + "'";
throw new ArgumentException(msg);
}
uniqueEvals.Add(wbEval, wbName);
}
UnhookOldEnvironments(evaluators);
HookNewEnvironment(evaluators, this);
_unhooked = false;
_evaluators = evaluators;
_evaluatorsByName = evaluatorsByName;
}
public EvaluationConditionalFormatRule(WorkbookEvaluator workbookEvaluator, ISheet sheet, IConditionalFormatting formatting, int formattingIndex, IConditionalFormattingRule rule, int ruleIndex, CellRangeAddress[] regions)
{
this.workbookEvaluator = workbookEvaluator;
this.sheet = sheet;
this.formatting = formatting;
this.rule = rule;
this.formattingIndex = formattingIndex;
this.ruleIndex = ruleIndex;
this.priority = rule.Priority;
this.regions = regions;
formula1 = rule.Formula1;
formula2 = rule.Formula2;
text = rule.Text;
lowerText = text == null ? null : text.ToLowerInvariant();
numberFormat = rule.NumberFormat;
@operator = (OperatorEnum)rule.ComparisonOperation;
type = rule.ConditionType;
}
/**
* @param udfFinder pass <code>null</code> for default (AnalysisToolPak only)
*/
private HSSFFormulaEvaluator(NPOI.SS.UserModel.Workbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder)
{
_bookEvaluator = new WorkbookEvaluator(HSSFEvaluationWorkbook.Create(workbook), stabilityClassifier, udfFinder);
}
/**
* Coordinates several formula evaluators together so that formulas that involve external
* references can be evaluated.
* @param workbookNames the simple file names used to identify the workbooks in formulas
* with external links (for example "MyData.xls" as used in a formula "[MyData.xls]Sheet1!A1")
* @param evaluators all evaluators for the full set of workbooks required by the formulas.
*/
public static void SetupEnvironment(String[] workbookNames, HSSFFormulaEvaluator[] evaluators)
{
WorkbookEvaluator[] wbEvals = new WorkbookEvaluator[evaluators.Length];
for (int i = 0; i < wbEvals.Length; i++)
{
wbEvals[i] = evaluators[i]._bookEvaluator;
}
CollaboratingWorkbooksEnvironment.Setup(workbookNames, wbEvals);
}
private OperationEvaluationContext CreateContext()
{
HSSFWorkbook wb = new HSSFWorkbook();
wb.CreateSheet();
HSSFEvaluationWorkbook workbook = HSSFEvaluationWorkbook.Create(wb);
WorkbookEvaluator workbookEvaluator = new WorkbookEvaluator(workbook, new IStabilityClassifier1(), null);
OperationEvaluationContext ctx = new OperationEvaluationContext(workbookEvaluator,
workbook, 0, 0, 0, null);
return ctx;
}
/**
* @param udfFinder pass <code>null</code> for default (AnalysisToolPak only)
*/
public HSSFFormulaEvaluator(IWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder)
{
_bookEvaluator = new WorkbookEvaluator(HSSFEvaluationWorkbook.Create(workbook), stabilityClassifier, udfFinder);
}
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);
}
}
}
public HSSFFormulaEvaluator(HSSFWorkbook workbook)
{
_bookEvaluator = new WorkbookEvaluator(HSSFEvaluationWorkbook.Create(workbook));
}
private CollaboratingWorkbooksEnvironment(Dictionary<String, WorkbookEvaluator> evaluatorsByName, WorkbookEvaluator[] evaluators)
{
Dictionary<WorkbookEvaluator, String> uniqueEvals = new Dictionary<WorkbookEvaluator, String>(evaluators.Length);
foreach (String wbName in evaluatorsByName.Keys)
{
WorkbookEvaluator wbEval = evaluatorsByName[(wbName)];
if (uniqueEvals.ContainsKey(wbEval))
{
String msg = "Attempted to register same workbook under names '"
+ uniqueEvals[(wbEval)] + "' and '" + wbName + "'";
throw new ArgumentException(msg);
}
uniqueEvals.Add(wbEval, wbName);
}
UnhookOldEnvironments(evaluators);
HookNewEnvironment(evaluators, this);
_unhooked = false;
_evaluators = evaluators;
_evaluatorsByName = evaluatorsByName;
}
private CollaboratingWorkbooksEnvironment(String[] workbookNames, WorkbookEvaluator[] evaluators, int nItems)
: this(toUniqueMap(workbookNames, evaluators, nItems), evaluators)
{
}
protected BaseFormulaEvaluator(WorkbookEvaluator bookEvaluator)
{
this._bookEvaluator = bookEvaluator;
}
/**
* @param stabilityClassifier used to optimise caching performance. Pass <code>null</code>
* for the (conservative) assumption that any cell may have its defInition Changed After
* Evaluation begins.
* @deprecated (Sep 2009) (reduce overloading) use {@link #Create(XSSFWorkbook, NPOI.ss.formula.IStabilityClassifier, NPOI.ss.formula.udf.UDFFinder)}
*/
public XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier)
{
_bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(workbook), stabilityClassifier, null);
_book = workbook;
}
public void Test57196_WorkbookEvaluator()
{
//Environment.SetEnvironmentVariable("NPOI.UTIL.POILogger", "NPOI.UTIL.SystemOutLogger");
//Environment.SetEnvironmentVariable("poi.log.level", "3");
try
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet("Sheet1") as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFCell cell = row.CreateCell(0) as XSSFCell;
cell.SetCellValue("0");
cell = row.CreateCell(1) as XSSFCell;
cell.SetCellValue(0);
cell = row.CreateCell(2) as XSSFCell;
cell.SetCellValue(0);
// simple formula worked
cell.CellFormula = (/*setter*/"DEC2HEX(O2+D2)");
WorkbookEvaluator workbookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(wb), null, null);
workbookEvaluator.DebugEvaluationOutputForNextEval = (/*setter*/true);
workbookEvaluator.Evaluate(new XSSFEvaluationCell(cell));
// this already failed! Hex2Dec did not correctly handle RefEval
cell.CellFormula = (/*setter*/"HEX2DEC(O8)");
workbookEvaluator.ClearAllCachedResultValues();
workbookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(wb), null, null);
workbookEvaluator.DebugEvaluationOutputForNextEval = (/*setter*/true);
workbookEvaluator.Evaluate(new XSSFEvaluationCell(cell));
// slightly more complex one failed
cell.CellFormula = (/*setter*/"HEX2DEC(O8)-O2+D2");
workbookEvaluator.ClearAllCachedResultValues();
workbookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(wb), null, null);
workbookEvaluator.DebugEvaluationOutputForNextEval = (/*setter*/true);
workbookEvaluator.Evaluate(new XSSFEvaluationCell(cell));
// more complicated failed
cell.CellFormula = (/*setter*/"DEC2HEX(HEX2DEC(O8)-O2+D2)");
workbookEvaluator.ClearAllCachedResultValues();
workbookEvaluator.DebugEvaluationOutputForNextEval = (/*setter*/true);
workbookEvaluator.Evaluate(new XSSFEvaluationCell(cell));
// what other similar functions
cell.CellFormula = (/*setter*/"DEC2BIN(O8)-O2+D2");
workbookEvaluator.ClearAllCachedResultValues();
workbookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(wb), null, null);
workbookEvaluator.DebugEvaluationOutputForNextEval = (/*setter*/true);
workbookEvaluator.Evaluate(new XSSFEvaluationCell(cell));
// what other similar functions
cell.CellFormula = (/*setter*/"DEC2BIN(A1)");
workbookEvaluator.ClearAllCachedResultValues();
workbookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(wb), null, null);
workbookEvaluator.DebugEvaluationOutputForNextEval = (/*setter*/true);
workbookEvaluator.Evaluate(new XSSFEvaluationCell(cell));
// what other similar functions
cell.CellFormula = (/*setter*/"BIN2DEC(B1)");
workbookEvaluator.ClearAllCachedResultValues();
workbookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(wb), null, null);
workbookEvaluator.DebugEvaluationOutputForNextEval = (/*setter*/true);
workbookEvaluator.Evaluate(new XSSFEvaluationCell(cell));
}
finally
{
//System.ClearProperty("NPOI.UTIL.POILogger");
//System.ClearProperty("poi.log.level");
}
}
public ConditionalFormattingEvaluator(IWorkbook wb, IWorkbookEvaluatorProvider provider)
{
this.workbook = wb;
this.workbookEvaluator = provider.GetWorkbookEvaluator();
}
private ForkedEvaluator(IEvaluationWorkbook masterWorkbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder)
{
_sewb = new ForkedEvaluationWorkbook(masterWorkbook);
_evaluator = new WorkbookEvaluator(_sewb, stabilityClassifier, udfFinder);
}
private XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder)
{
_bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(workbook), stabilityClassifier, udfFinder);
_book = workbook;
}
private OperationEvaluationContext CreateContext()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.SetCellValue("13.43");
cell = row.CreateCell(1);
cell.SetCellValue("8");
cell = row.CreateCell(2);
cell.SetCellValue("-8");
cell = row.CreateCell(3);
cell.SetCellValue("1");
HSSFEvaluationWorkbook workbook = HSSFEvaluationWorkbook.Create(wb);
WorkbookEvaluator workbookEvaluator = new WorkbookEvaluator(workbook, new IStabilityClassifier1(), null);
OperationEvaluationContext ctx = new OperationEvaluationContext(workbookEvaluator,
workbook, 0, 0, 0, null);
return ctx;
}
private void UnhookOldEnvironments(WorkbookEvaluator[] evaluators)
{
ArrayList oldEnvs = new ArrayList();
for (int i = 0; i < evaluators.Length; i++)
{
oldEnvs.Add(evaluators[i].GetEnvironment());
}
CollaboratingWorkbooksEnvironment[] oldCWEs = new CollaboratingWorkbooksEnvironment[oldEnvs.Count];
oldCWEs = (CollaboratingWorkbooksEnvironment[])oldEnvs.ToArray(typeof(CollaboratingWorkbooksEnvironment));
for (int i = 0; i < oldCWEs.Length; i++)
{
oldCWEs[i].Unhook();
}
}
public OperationEvaluationContext(WorkbookEvaluator bookEvaluator, IEvaluationWorkbook workbook, int sheetIndex, int srcRowNum,
int srcColNum, EvaluationTracker tracker)
: this(bookEvaluator, workbook, sheetIndex, srcRowNum, srcColNum, tracker, isSingleValue : true)
{
}
private static void HookNewEnvironment(WorkbookEvaluator[] evaluators, CollaboratingWorkbooksEnvironment env)
{
// All evaluators will need To share the same cache.
// but the cache takes an optional evaluation listener.
int nItems = evaluators.Length;
IEvaluationListener evalListener = evaluators[0].GetEvaluationListener();
// make sure that all evaluators have the same listener
for (int i = 0; i < nItems; i++)
{
if (evalListener != evaluators[i].GetEvaluationListener())
{
// This would be very complex To support
throw new Exception("Workbook evaluators must all have the same evaluation listener");
}
}
EvaluationCache cache = new EvaluationCache(evalListener);
for (int i = 0; i < nItems; i++)
{
evaluators[i].AttachToEnvironment(env, cache, i);
}
}
private static Dictionary<String, WorkbookEvaluator> toUniqueMap(String[] workbookNames, WorkbookEvaluator[] evaluators, int nItems)
{
Dictionary<String, WorkbookEvaluator> evaluatorsByName = new Dictionary<String, WorkbookEvaluator>(nItems * 3 / 2);
for (int i = 0; i < nItems; i++)
{
String wbName = workbookNames[i];
WorkbookEvaluator wbEval = evaluators[i];
if (evaluatorsByName.ContainsKey(wbName))
{
throw new ArgumentException("Duplicate workbook name '" + wbName + "'");
}
evaluatorsByName.Add(wbName, wbEval);
}
return evaluatorsByName;
}
