Working with ICalcData in Windows Forms Calculation Engine

18 Nov 201810 minutes to read

Essential Calculate provides calculation support to arbitrary business objects through the ICalcData interface. To add calculation support to classes that represent data in a row/column format, such as a data grid, implement the ICalcData interface in your class.

Methods and Events in ICalcData

ICalcData has three methods and one event. This interface allows the CalcEngine class in Essential Calculate to communicate with arbitrary data sources that implement this interface.

SetValueRowCol

SetValueRowCol method is used to set the value to mentioned row and column index. Essential Calculate expects all indexes (row and column integer values) to be one-based.

An example of defining the SetValueRowCol method in a custom class (CalcData) is shown below,

//Custom class,

public class CalcData : ICalcData
{
    Dictionary<string, object> values = new Dictionary<string, object>();

    //Defining SetValueRowCol method in Custom(user defined) Class,
    public void SetValueRowCol(object value, int row, int col)
    {
        var key = RangeInfo.GetAlphaLabel(col) + row;
        if (!values.ContainsKey(key))
            values.Add(key, value);
        else if (values.ContainsKey(key) && values[key] != value)
            values[key] = value;
    }
}

//Main class,

public void Main()
{
    CalcData calcData = new CalcData();

    //To set the data value of a specified row and column,
    calcData.SetValueRowCol(90, 1, 1);
    calcData.SetValueRowCol(50, 1, 2);
}

GetValueRowCol

GetValueRowCol method is used to get the value from mentioned row and column index. Essential Calculate expects all indexes (row and column integer values) to be one-based.

An example of defining the GetValueRowCol method in a custom class (CalcData) is shown below,

//Custom class,

public class CalcData : ICalcData
{
    Dictionary<string, object> values = new Dictionary<string, object>();

    //Defining GetValueRowCol method in Custom(user defined) Class,
    public object GetValueRowCol(int row, int col)
    {
        object value = null;
        var key = RangeInfo.GetAlphaLabel(col) + row;
        this.values.TryGetValue(key, out value);
        return value;
    }
}

//Main class,

public void Main()
{
    CalcData calcData = new CalcData();

    //To get the data value of a specified row and column,
    var value1 = calcData.GetValueRowCol(1, 1);
    var value2 = calcData.GetValueRowCol(1, 2);
}

WireParentObject

WireParentObject is a method that wires the ParentObject after the CalcEngine object is created or when a RegisterGridAsSheet call is made. The purpose is to give the data object
a chance to do any initialization steps it may need, such as subscribe the events to handle the changes in data notifications.

ValueChanged

ValueChanged event of the ICalcData interface occurs whenever a value is changed. The CalcEngine listens to this event and accordingly reacts to data changes. This event is how the CalcEngine processes formulas and tracks dependencies.

//Custom class,
public class CalcData : ICalcData
{
    public event ValueChangedEventHandler ValueChanged;

    private void OnValueChanged(int row, int col, string value)
    {
        if (ValueChanged != null)
            ValueChanged(this, new ValueChangedEventArgs(row, col, value));
    }
}

Computation using ICalcData

The following example shows the computation of a formula using the ICalcData interface.

Creating a Class from ICalcData

Create a CalcData class that implements the ICalcData interface,

public class CalcData : ICalcData
{
    public event ValueChangedEventHandler ValueChanged;

    Dictionary<string, object> values = new Dictionary<string, object>();
    public object GetValueRowCol(int row, int col)
    {
        object value = null;
        var key = RangeInfo.GetAlphaLabel(col) + row;
        this.values.TryGetValue(key, out value);
        return value;
    }

    public void SetValueRowCol(object value, int row, int col)
    {
        var key = RangeInfo.GetAlphaLabel(col) + row;
        if (!values.ContainsKey(key))
            values.Add(key, value);
        else if (values.ContainsKey(key) && values[key] != value)
            values[key] = value;
    }

    public void WireParentObject(){}

    private void OnValueChanged(int row, int col, string value)
    {
        if (ValueChanged != null)
            ValueChanged(this, new ValueChangedEventArgs(row, col, value));
    }
}

Setting Value into ICalcData

The SetValueRowCol method is used to set a value on an ICalcData object.

CalcData calcData = new CalcData();

calcData.SetValueRowCol(10, 1, 1);

calcData.SetValueRowCol(20, 1, 2);

Initialization of CalcEngine

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

CalcData calcData = new CalcData();

CalcEngine engine = new CalcEngine(calcData);

Evaluation of formula

The ParseAndComputeFormula method of CalcEngine is used to evaluate formulas using the values from an ICalcData object by cell references.

CalcData calcData = new CalcData();

calcData.SetValueRowCol(10, 1, 1);

calcData.SetValueRowCol(20, 1, 2);

CalcEngine engine = new CalcEngine(calcData);

string formula = "SUM(A1, B1)";

string result = engine.ParseAndComputeFormula(formula);

How to use custom control with CalcEngine

You can use any of our Tools controls with CalcEngine, but the control must implement ICalcData.

this.grid.ItemsSource = dt.DefaultView; 
engine = new CalcEngine(this.grid); 
 
public class CustomGrid : DataGrid,ICalcData 
{ 
    public CustomGrid() 
    { } 
 
    public event ValueChangedEventHandler ValueChanged; 
 
    public object GetValueRowCol(int row, int col) 
    { 
        if (row < 0 || col < 0) 
            return "Invalid cell"; 
        string s = (this.Items[row-1] as DataRowView).Row.ItemArray[col-1].ToString(); 
        return s; 
    } 
 
    public void SetValueRowCol(object value, int row, int col) 
    { 
        //To set the value to specific cell.    
    } 
 
    public void WireParentObject() 
    { 
         //To trigger any of event for parent. This method is called when calcEngine assigned the parent object as CustomDataGrid. 
    } 
}

A sample that demonstrates using custom control with CalcEngine is available here

To support cross-references among several ICalcData objects, you must register the objects with a single instance of the CalcEngine.
For more reference, refer here.