Working with CalcEngine in Windows Forms Calculation Engine

24 Aug 202118 minutes to read

CalcEngine encapsulates the code required to parse and compute the formulas. It manages several library functions, hash tables for Essential Calculate.
All the data’s in CalcEngine is assumed to be part of a rectangular array reference through cell coordinates.

Computation using CalcEngine

The CalcEngine interface allows the CalcEngine class to communicate with arbitrary data sources that implement this interface.
If any user defined class which implements the ICalcData interface needs to be integrated with CalcEngine, then that class’s object can be passed as parameter
to the CalcEngine constructor.

The ParseAndComputeFormula method of CalcEngine parses and computes the string formula passed in and updates the computed value.

Using ICalcData

The ICalcData object can be integrated into CalcEngine by passing it through constructor. Now, you can compute the expressions or equations using ParseAndComputeFormula.

C#

//Class derived from ICalcData,
CalcData calcData = new CalcData();

CalcEngine engine = new CalcEngine(calcData);

//Computing Expressions,

string formula = "(5+25) *2";
string result = engine.ParseAndComputeFormula(formula);

//Computing In-Built formulas,

string formula = “SUM (4,5,6)”;
string result = engine.ParseAndComputeFormula(formula);

Download Calculation with Engine demo from GitHub

Using CalcQuickBase

CalcQuickBase is predefined derived class from ICalcData interface. So, user can pass the CalcQuickBase's object as parameter to the constructor of CalcEngine for
computations.

C#

CalcQuickBase calcQuick = new CalcQuickBase();

//Computing Expressions,

string formula = "(5+25) *2";
string result = calcQuick.Engine.ParseAndComputeFormula(formula);

//Computing In-Built formulas,

string formula = “SUM (4,5,6)”;
string result = calcQuick.Engine.ParseAndComputeFormula(formula);

Data Objects maintained in CalcEngine

To track information used during calculations, CalcEngine manages several hash tables. In CalcEngine, the data’s are referred in cell coordinates and these references are maintained as keys in the hash tables.

Below find the list of hash tables in CalcEngine and a description of their keys and values.

Hash Table	Key	Value	Description
FormulaInfoTable	Cell reference	FormulaInfo object	Tracks formula/value information for this cell.
DependentCells	Cell reference	Hashtable object	Tracks cells that depend on this cell.
DependentFormulaCells	Formula cell reference	Hashtable object	Tracks cells in which the formula cell depends upon.
NamedRanges	Name string	Value string	Associates the named range with its value.
LibraryFunctions	Function name	LibraryFunction delegate	Associates the function name with its method.

Properties in CalcEngine

ExcelLikeComputations

To return the result of formula computations of Essential Calculate like Microsoft Excel computational result, this bool property ExcelLikeComputations is set to true.

C#

//Class derived from ICalcData,
CalcData calcData = new CalcData();

CalcEngine engine = new CalcEngine(calcData);

engine.ExcelLikeComputations = true;

//Computing the DATE formula similar to excel,
string formula = “DATE(2004,5,6)”;

string result = engine.ParseAndComputeFormula(formula);

AllowShortCircuitIFs

To specifically avoid the computing of non-used alternative in IF function calculations, set AllowShortCircuitIFs to true.
The default value is false for code legacy consistency. But when it is set as true, only the necessary alternative of an IF function is computed which
leads to increase in performance.

AlwaysComputeDuringRefresh

If PullUpdatedValue method is exclusively used to retrieve the computed values, then setting AlwaysComputeDuringRefresh property to false
may be more efficient as it will only recompute the value once during the calculations.

CalculatingSuspended

CalculatingSuspended property is set to true, to suspend calculations while a series of changes are made to dependent cells either by the user or programmatically.
When the changes are complete, set this property to false, and then call RecalculateRange to recalculate the affected ranges.

C#

//Class derived from ICalcData,
CalcData calcData = new CalcData();

CalcEngine engine = new CalcEngine(calcData);

// Turn off calculations
engine.CalculatingSuspended = true;

// Makes multiple updates to cells involved in calculation
for (int i = 0; i < 5; i++)
{
    for (int j = 0; j < 5; j++)
    {
        calcData.SetValueRowCol(random.Next(5) + 1,i,j);
    }
}

// Turn on calculations
engine.CalculatingSuspended = false;

// invoke RecalculateRange, so any formulas in the data can be computed. 
engine.RecalculateRange(RangeInfo.Cells(4, 4, 2, 1), calcData);

UseFormulaValues

This property is set to true, when a cell contains more dependency cells to return the formula value stored in FormulaInfoTable to avoid
repeated calculations.

CheckDanglingStack

While computing certain formulas, Essential Calculate ignores the dangling value in the calculation stack and returns the computed value.
If you want this situation flagged as a invalid formula, then set CheckDanglingStack property to true. The default value is false for backward compatibility purpose.

ForceRefreshCall

If a value changes, then the Refresh method is called recursively every time whenever the ValueChanged event of ICalcData is fired. Setting this ForceRefreshCall property to false will call the Refresh method for only the cells whose calculated value is actually modified.

FormulaCharacter

In general, in order for Essential Calculate to recognize a string as containing a formula; then the string is required to start with the FormulaCharacter.
A static property that gets or sets the character to identify the formula. The default value is “=”.

GetValueFromArgPreserveLeadingZeros

If you want to preserve the leading zeros in GetValueFromArg method, then set GetValueFromArgPreserveLeadingZeros to true.

C#

//Class derived from ICalcData,
CalcData calcData = new CalcData();

//Assign a value with leading zeros to "A1" cell,
calcData.SetValueRowCol(04510, 1, 1);

CalcEngine engine = new CalcEngine(calcData);

//Setting this property to true, for preserving leading zeros,
engine.GetValueFromArgPreserveLeadingZeros = true;

//Returns the result with leading zeros,
string result = engine.GetValueFromArg("A1")

IterationMaxCount

Gets or sets the maximum number of iterative calls that can be made on a cell to avoid circular exception. The default value is 0 indicating that iterative
calculation support is turned off. ThrowCircularException property will be automatically set to true when you set a non-zero value to IterationMaxCount.

IterationMaxTolerance

Gets or sets the success tolerance used by the CalcEngine's iterative calculation support. The iterations in calculation continues until either the iteration count
exceeds IterationMaxCount, or two successive iteration return values have a relative difference less than IterationMaxTolerance. The default value is 0.001.

LockDependencies

A bool property that gets or sets whether a changed value should trigger dependent changes.

MaximumRecursiveCalls

Specifies the maximum number of recursive calls that can be used to compute a cell value.This property comes into play when you have a calculated formula cell that depends on
another calculated formula that depends on another calculated formula and so on. If the depends on another formula exceeds MaximumRecursiveCalls, you will see a too complex message displayed in the cell. The default value is 100, but you can set it higher or lower depending upon your expected needs. The purpose of the limit is to avoid a circular
reference locking up your application.

MaxStackDepth

Gets or sets the maximum calculation stack depth. The default value is 50. This is the number of recursive calls that can be made during calculations.

ParseArgumentSeparator

A static property that gets or sets the character to be recognized by the parsing code as the delimiter for arguments in a named formula’s argument list. The default value is ‘,’.

C#

//Assign the argument separator,
CalcEngine.ParseArgumentSeparator = ';';

ParseDecimalSeparator

A static property that gets or sets the character to be recognized by the parsing engine as decimal separator for numbers. The default value is ‘.’.

C#

//Assign the decimal separator,
CalcEngine.ParseDecimalSeparator = ',';

ParseDateTimeSeparator

A static property that gets/sets the character to be recognized by the parsing engine as decimal separator for date. The default value is ‘/’.

C#

//Assign the date time separator,
CalcEngine.ParseDateTimeSeparator = '-';

RethrowLibraryComputationExceptions

Gets or sets whether the CalcEngine throws any exception that occurs during the computation of a library function. It throws an error message from FormulaErrorStrings collection. The default value of of RethrowLibraryComputationExceptions is false.The following page demonstrates the ErrorString and FormulaErrorString values.

RethrowParseExceptions

The default value of RethrowParseExceptions is false.If the value is set as true, CalcEngine throws an exception from FormulaErrorStrings collection. If the value is set as false, and the parsed formula is not a valid one,it returns error message from ErrorStringscollection without throwing exception.

SupportLogicalOperators

If you want to use any logical operators such as AND, OR, XOR,… in a formula, you can set SupportLogicalOperators property of CalcEngine as true .

C#

//Class derived from ICalcData,
CalcData calcData = new CalcData();

CalcEngine engine = new CalcEngine(calcData);

engine.SupportLogicalOperators = true;

string result = engine.ParseAndComputeFormula(“=IF("C"="S "OR" C"="C",1,2)”);

SupportsSheetRanges

Gets or sets whether the sheet range notation is supported. The default value is true. For backward compatibility with earlier versions that did
not support the sheet range notation, you can set SupportsSheetRanges property to false.

C#

//Initialization of first ICalcData object in CalcEngine,
calcData = new CalcData();
CalcEngine engine = new CalcEngine(calcData);

//Initialization of second ICalcData object in CalcEngine,
calcData1 = new calcData1();
engine = new CalcEngine(calcData1);

//Create a unique family id,
int i = CalcEngine.CreateSheetFamilyID();

//Register the first ICalcData object as "Sheet1",
engine.RegisterGridAsSheet("Sheet1", calcData, i);

//Register the second ICalcData object as "Sheet2",
engine.RegisterGridAsSheet("Sheet2", calcData1, i);

engine.SupportsSheetRanges = true;

string result = engine.ParseAndComputeFormula(“=SUM(sheet1!B1:B4,sheet2!B1:B4)”);

ThrowCircularException

Gets or sets whether the CalcEngine should throw an exception when a circular calculation is encountered. If ThrowCircularException property is true, then CalcEngine will throw an exception when it detects a circular calculation, else no exception is thrown and the calculation will loop recursively until MaximumRecursiveCalls is exceeded.

UseDependencies

Gets or sets whether the CalcEngine should track dependencies or not. If you invoke PullUpdatedValue to access the computations, then setting UseDependencies property to false will make things more
efficient as any requested computed value will be fully computed every time it is retrieved. In this situation, the CalcEngine does not need to track dependencies.

UseNoAmpersandQuotes

Gets or sets whether the computational result of strings should be returned with double quotes or not.

C#

//Class derived from ICalcData,
CalcData calcData = new CalcData();

CalcEngine engine = new CalcEngine(calcData);

engine.UseNoAmpersandQuotes = true;

string result = engine.ParseAndComputeFormula("=CONCAT(\"abc\",\"sample\")");

UseDatesInCalculations

Gets or sets whether dates can be used as operands in calculations. The default value is false. Setting UseDatesInCalculations property to true,
you can compute DateTime related calculations in your application.

C#

//Class derived from ICalcData,
CalcData calcData = new CalcData();

CalcEngine engine = new CalcEngine(calcData);

engine.UseDatesInCalculations = true;

calcData.SetValueRowCol("02/10/2017 11:15 am", 1, 1);
calcData.SetValueRowCol("02/12/2017 9:45 am", 2, 1);

//Computing the difference in hours between two date time values,
string result = engine.ParseAndComputeFormula("=(A2-A1)*24");

TreatStringsAsZero

Setting the property TreatStringsAsZero as true means that if a nonempty string is encountered during an arithmetic
operation or computation, it will be treated as zero.

Methods in CalcEngine

GetValueFromArg

This method takes the argument and checks whether it is a parsed formula, a raw number, or a cell reference. It returns the string
by computing the value based on the argument passed in.

C#

//Class derived from ICalcData,
CalcData calcData = new CalcData();

CalcEngine engine = new CalcEngine(calcData);

string result = engine.GetValueFromArg("=SUM(4,5,6)");

GetValueFromParentObject

This method takes the argument and check whether it is a formula or a cell reference and returns either the computed value or the cell value.

C#

//Class derived from ICalcData,
CalcData calcData = new CalcData();

//Assign values,
calcData.SetValueRowCol(2, 1, 2);
calcData.SetValueRowCol(3, 2, 2);
calcData.SetValueRowCol(4, 3, 2);
calcData.SetValueRowCol("=SUM(B1,B2,B3)", 1, 4);

CalcEngine engine = new CalcEngine(calcData);

//Passing the cell reference,
string result = engine.GetValueFromParentObject("D1")

//Passing the row/column index,
string result = engine.GetValueFromParentObject(1, 4);

NOTE

GetValueFromParentObject does not accepts the argument as raw number or parsed formula, it only accepts the cell references whereas GetValueFromArg accepts parsed formula, a raw number, or a cell reference

ParseAndComputeFormula

A method that parses and computes the string formula passed in. For more reference, see here.

ParseFormula

A method that parses the string formula passed in. For more reference, see here.

ComputeFormula

A method that computes a parsed formula. For more reference, see here.

RegisterGridAsSheet

A method that registers an ICalcData object so it can be referenced in a formula from another ICalcData object. For more reference, see here.

UpdateCalcID

Every formula has a calculation ID level associated with it. Every time a formula is retrieved, its calculation ID level is compared with the CalcEngine
ID level. If they do not agree, the formula is recomputed. Invoking UpdateCalcID will force any formula to be recomputed the next time it is retrieved.

C#

//Class derived from ICalcData,
CalcData calcData = new CalcData();

//Assign values initially,
calcData.SetValueRowCol(2, 1, 2);
calcData.SetValueRowCol(3, 2, 2);
calcData.SetValueRowCol(4, 3, 2);
calcData.SetValueRowCol("=SUM(B1,B2,B3)", 1, 4);

CalcEngine engine = new CalcEngine(calcData);

//compute the formula,
string val = engine.ParseAndComputeFormula("D1");

//Changing the value of B2 cell,
calcData.SetValueRowCol(20, 2, 2);

//Invoking UpdateCalcID method to retrieve latest change,
engine.UpdateCalcID();

//Computing the formula with latest changes,
string val1 = engine.ParseAndComputeFormula("D1");

PullUpdatedValue

This method retrieves the value in the requested cell reference by computing with latest changes of cells that affect the value of the requested cell.

C#

//Initialization of first ICalcData object in CalcEngine,
calcData = new CalcData();
CalcEngine engine = new CalcEngine(calcData);

//Initialization of second ICalcData object in CalcEngine,
ICalcData calcData1 = new CalcData1();
engine = new CalcEngine(calcData1);

//Register the first ICalcData object as "Sheet1",
engine.RegisterGridAsSheet("Sheet1", calcData, 1);

//Register the second ICalcData object as "Sheet2",
engine.RegisterGridAsSheet("Sheet2", calcData1, 1);

//Assign a formula in the calcData1's A1 cell,
calcData1.SetValueRowCol("=SUM(Sheet1!A1, Sheet1!B2, Sheet1!C2, Sheet1!D2)",1,1);

Random r = new Random();

engine.CalculatingSuspended = true;

//Set random values,
calcData.SetValueRowCol(r.Next(74) + 15, 1, 1);
calcData.SetValueRowCol(r.Next(2), 2, 2);
calcData.SetValueRowCol(r.Next(50),3, 2);
calcData.SetValueRowCol(r.Next(15),4, 2);
calcData.SetValueRowCol(r.Next(11),5, 2);
calcData.SetValueRowCol(33 + r.Next(1972),6, 2);
calcData.SetValueRowCol(r.Next(2),7, 2);
calcData.SetValueRowCol(r.Next(20),8, 5);

//Calculations are suspended so need to pull the computed value to make sure it has been calculated with the latest changes,
engine.UpdateCalcID();
engine.PullUpdatedValue(engine.GetSheetID(calcData1), 1, 1);

//Get the Calculated value from calcData1 object,
string val = calcData1.GetValueRowCol(1,1).ToString();

engine.CalculatingSuspended = false;

RecalculateRange

Recalculates any formula cells in the specified range of CalcEngine.

C#

// Creates some data object that implements ICalcData.
this.data = new ArrayCalcData(a);

// Creates a CalcEngine object using this ICalcData object.
CalcEngine engine = new CalcEngine(this.data);

//Turn off calculations.
engine.CalculatingSuspended = true;      

// Makes multiple updates to this.data.

// Turn on calculations.
engine.CalculatingSuspended = false;

// Calls RecalculateRange so any formulas in the data can be computed.            
engine.RecalculateRange(RangeInfo.Cells(1, 1, nRows + 1, nCols + 1), data);

UpdateDependenciesAndCell

This method triggers a calculation for any cells depending upon the given cell.

C#

//Class derived from ICalcData,
CalcData calcData = new CalcData();

//Assign values initially,
calcData.SetValueRowCol(2, 1, 2);
calcData.SetValueRowCol(3, 2, 2);
calcData.SetValueRowCol(4, 3, 2);
calcData.SetValueRowCol("=SUM(B1,B2,B3)", 1, 4);

CalcEngine engine = new CalcEngine(calcData);

//Add the formula in formula info hash table,
engine.FormulaInfoTable.Add("D1", new FormulaInfo{FormulaText = "=SUM(B1,B2,B3)"});

//compute the formula,
string val = engine.ParseAndComputeFormula("D1");

//Changing the value of B2 cell,
calcData.SetValueRowCol(20, 2, 2);

//Invoke UpdateDependenciesAndCell method for the formula cell,
engine.UpdateDependenciesAndCell("D1");

//Computing the formula with latest changes,
string val1 = engine.ParseAndComputeFormula("D1");

Get list of supported formulas in CalcEngine

To get the list of supported formulas in CalcEngine, you could use the LibraryFunctions property from CalcEngine.

C#

//Class derived from ICalcData,
CalcData calcData = new CalcData();

CalcEngine engine = new CalcEngine(calcData);

//To get the list of build in functions,
var computationFunctions = engine.LibraryFunctions;