Skip to main content Skip to footer

A Calculation Engine for FlexGrid, Part 1: WinForms

Introduction

This article describes a simple approach for adding a calculation engine to ComponentOne FlexGrid. Part 1 focuses on the WinForms version and part 2 will be on Silverlight. The first section describes the implementation of the CalcEngine class, which is responsible for converting strings into “expression” objects that can be evaluated. The second section describes the implementation of a FlexCalc class, which extends the WinForms version of the C1FlexGrid and uses the CalcEngine class to add formula support to the grid, so users can type expressions such as “=Sum(A1:A10) * 12%” into a cell and see the result. The FlexCalc sample can be downloaded from here: FlexCalc_WinForms_CS

The CalcEngine class

The CalcEngine class includes an expression parser and an evaluator. The parser converts strings into expressions, and the evaluator calculates the value of the expressions. For example:


var ce = new CalcEngine();  
var expression = ce.Parse("1+2+3");  
var value = expression.Evaluate();  

Alternatively, the CalcEngine can parse and evaluate strings with a single call to the Evaluate method. For example:


var ce = new CalcEngine();  
value = ce.Evaluate("1+2+3");  

The CalcEngine class is independent of the FlexGrid and may be re-used in other projects that require expression evaluation. The class is extensible and can be customized for specific applications as described below. 1. DataContext: The CalcEngine has a DataContext property that allows you to add objects to the engine's context. For example, if you have a Customer class with a Sales property, you could calculate sales commissions using this code:


var ce = new CalcEngine();  
ce.DataContext = myCustomer;  
var commission = ce.Evaluate("Sales * 7%");  

  1. Functions: The CalcEngine has about 70 built-in functions found in Excel (please refer to the source code for a complete list). If you need more functions, use the RegisterFunction method. This method takes the function name, number of parameters, and a delegate that is invoked when the function is called. For example, the code below registers an “Atan2” function that takes two parameters and an “Atn” function that takes one parameter only. Notice how the delegate (or lambda) receives a list of Expression objects as parameters and casts them to the expected parameter types: ~~~

var ce = new CalcEngine();

// register ATAN2 function using a regular delegate
ce.RegisterFunction("ATAN2", Atan2, 2);
static object Atan2(List p)
{
return Math.Atan2((double)p[0], (double)p[1]);
}

// register ATAN function using a lambda expression
ce.RegisterFunction("ATAN", p => Math.Atn((double)p[0]), 1);

**External Objects**: The **CalcEngine** supports objects that are added to the engine context dynamically. For example, Excel ranges are represented by strings such as "A1". To support this, the samples described here derive from the base **CalcEngine** and override the **GetExternalObject** method to create cell range objects dynamically.

Using the CalcEngine with FlexGrid for WinForms
-----------------------------------------------

This section describes how you can use the **CalcEngine** class to implement **FlexCalc**, a control that derives from the WinForms version of the **C1FlexGrid** and includes a calculation engine. The **FlexCalc** sample looks like the image below, and is available for download from here: [FlexCalc\_WinForms\_CS](//cdn.mescius.io/assets/developer/blogs/legacy/c1/2011/08/FlexCalc_WinForms_CS.zip). [![](//cdn.mescius.io/assets/developer/blogs/legacy/c1/2011/08/C1FlexGridCalculationEngine.png "C1FlexGridCalculationEngine")](//cdn.mescius.io/assets/developer/blogs/legacy/c1/2011/08/C1FlexGridCalculationEngine.png) The sample uses the following two main classes:

The FlexCalcEngine class
------------------------

This class inherits from the **CalcEngine** class described earlier and extends it to support references to cell ranges (e.g. “=SUM(A1:A10)”). The main implementation aspects of the **FlexCalcEngine** class are the following: **Constructor** The constructor takes a reference to a **FlexCalc** object that represents the grid control that contains the data that will be used by the **FlexCalcEngine**. When a user writes an expression such as “=SUM(A1:A10)”, the cells of the owner grid are used to compute the result. The constructor also specifies that identifiers may contain the characters ‘$’ and ‘:’, which are used to express ranges (e.g. “$A$1”).

///


/// Initializes a new instance of a FlexCalcEngine.
///

/// Grid that provides data for the engine. public FlexCalcEngine(FlexCalc flex)
{
// save reference to owner grid
_flex = flex;

// parse multi-cell range references ($A2:B$4)  
IdentifierChars = "$:";  

}

**DataContext Property** The **FlexCalcEngine** overrides the **DataContext** property to use the context of the owner grid. This ensures that when the developer assigns an object to the grid’s **DataContext** property, the object becomes available to the calculation engine and can be used in expressions.

///


/// Exposes the grid's DataContext to the CalcEngine.
///

public override object DataContext
{
get { return _flex.DataContext; }
set { _flex.DataContext = value; }
}

**GetExternalObject method** The **FlexCalcEngine** overrides the **GetExternalObject** method to parse range identifiers into **CellRangeReference** objects. These objects are responsible for getting the values stored in cell ranges, and are described in a later section. This illustrates one of the extensibility mechanisms provided by the **CalcEngine** base class.

///


/// Parses references to cell ranges.
///

/// String representing a cell range
/// (e.g. "A1" or "A1:B12".
/// A object that represents the
/// given range.

public override object GetExternalObject(string identifier)
{
CellRange rng;
var cells = identifier.Split(':');
if (cells.Length > 0 && cells.Length < 3)
{
rng = GetRange(cells[0]);
if (cells.Length > 1)
{
rng = MergeRanges(rng, GetRange(cells[1]));
}
if (rng.IsValid)
{
return new CellRangeReference(_flex, rng);
}
}

// this doesn't look like a range  
return null;  

}

**CellRangeReference helper class** The **CellRangeReference** class represents cell ranges and returns the cell values to the engine. It implements two important interfaces: **CalcEngine.IValueOject** (which has a single **GetValue** method that allows the class to return custom values to the engine) and **IEnumerable** (used to implement multi-value objects such as ranges with multiple cells, which are used in functions such as “Sum”, “Count”, and “Average”). The **CellRangeReference** class is implemented as follows:

///


/// Represents cell ranges and returns the cell values to the calc engine.
///

class CellRangeReference :
CalcEngine.IValueObject,
IEnumerable
{
FlexCalc _flex;
CellRange _rng;
static Dictionary<CellRange, bool> _eval =
new Dictionary<CellRange, bool>();

public CellRangeReference(FlexCalc flex, CellRange rng)  
{  
    _flex = flex;  
    _rng = rng;  
}  

// ** IValueObject  
// gets the value contained in the first cell of the range  
public object GetValue()  
{  
    return GetValue(_rng);  
}  

// ** IEnumerable  
// enumerates the values in every cell in the range  
public IEnumerator GetEnumerator()  
{  
    for (int r = \_rng.TopRow; r <= \_rng.BottomRow; r++)  
    {  
        for (int c = \_rng.LeftCol; c <= \_rng.RightCol; c++)  
        {  
            var rng = _flex.GetCellRange(r, c);  
            yield return GetValue(rng);  
        }  
    }  
}  
Both **GetValue** and **GetEnumerator** use a **GetValue** method that returns the value stored in a grid cell. The implementation of the **GetValue** method uses a static dictionary to keep track of the cell ranges being currently evaluated. This is done to detect circular references (e.g. cell “A1” could contain an expression such as “=SUM(A1:A10”). The actual calculation is deferred to the owner grid, which handles cells that contain simple values or expressions. The **GetValue** method is implemented as follows:
// ** implementation  
object GetValue(CellRange rng)  
{  
    if (_eval.ContainsKey(rng))  
    {  
        throw new Exception("Circular Reference");  
    }  

    try  
    {  
        _eval[rng] = true;  
        return _flex.Evaluate(rng.r1, rng.c1);  
    }  
    finally  
    {  
        _eval.Remove(rng);  
    }  
}  

}


The FlexCalc class
------------------

This class is a grid control that inherits from the **C1FlexGrid**. It stores values in the cells in unbound mode as usual, and overrides the **GetDataDisplay** method to evaluate cells that contain formulas and return their values. The main implementation aspects of the **FlexCalc** class are the following: **FlexCalcEngine member** The **FlexCalc** control has a **FlexCalcEngine** that is responsible for evaluating the expressions contained in grid cells. The engine is used to implement an **Evaluate** method that has two overloads as shown below:

// evaluates the content of a cell using the calc engine
public object Evaluate(int row, int col)
{
var val = this[row, col];
var text = val as string;
if (!string.IsNullOrEmpty(text))
{
double dbl;
if (text[0] == '=')
{
val = _ce.Evaluate(text);
}
else if (double.TryParse(text, out dbl))
{
val = dbl;
}
}
return val;
}
// evaluates an expression using the calc engine
public object Evaluate(string expression)
{
return _ce.Evaluate(expression);
}

**GetDataDisplay member** The **FlexCalc** overrides the **GetDataDisplay** method to evaluate formulas so the grid shows the formula results instead of the formulas themselves. As in Excel, numeric results are formatted to 9 digits by default, and leading single quotes are removed from the display value.

// get data for display (evaluates formulas)
public override string GetDataDisplay(int row, int col,
out System.Drawing.Image img,
out C1.Win.C1FlexGrid.CheckEnum chk)
{
// no image, no checkbox
img = null;
chk = C1.Win.C1FlexGrid.CheckEnum.None;

// get raw value  
object val = null;  
try  
{  
    val = Evaluate(row, col);  
}  
catch (Exception x)  
{  
    val = "ERR: " + x.Message;  
}  

// apply cell format  
var ifmt = val as IFormattable;  
if (ifmt != null)  
{  
    var s = GetCellStyleDisplay(row, col);  
    var fmt = !string.IsNullOrEmpty(s.Format)  
        ? s.Format  
        : "#,##0.#########"; // set default precision to 9 digits  
    val = ifmt.ToString(fmt, CultureInfo.CurrentCulture);  
}  
else  
{  
    // remove leading quotes (as Excel)  
    var text = val as string;  
    if (!string.IsNullOrEmpty(text) && text[0] == '\\'')  
    {  
        val = text.Substring(1);  
    }  
}  

// done  
return val != null ? val.ToString() : null;  

}

**SetData method** The **FlexCalc** overrides the **SetData** method to invalidate all visible cells that contain formulas when any cell value changes. This ensures that all formulas are recalculated, so results are updated automatically. This is a much simpler alternative than keeping track of formula dependencies throughout the grid.

// invalidate all cells that contain formulas after any edits
public override bool SetData(int row, int col, object value)
{
var retVal = base.SetData(row, col, value);
if (retVal)
{
for (int r = TopRow; r <= BottomRow; r++)
{
for (int c = LeftCol; c <= RightCol; c++)
{
var text = this.GetData(r, c) as string;
if (!string.IsNullOrEmpty(text) && text[0] == '=')
{
Invalidate(r, c);
}
}
}
}
return retVal;
}

The **FlexCalc** class also has a few additional methods used to render row and column headers, as well as an Excel-style selection marquee. Those methods are not related to the calculation engine and therefore are not listed here. The sample also implements a formula bar and a status bar. The formula bar appears above the grid and shows the coordinates of the current cell using Excel notation and allows viewing and editing the content of the selected cell. The status bar appears below the grid and shows basic statistics for extended selections (average, count, and sum). The summary statistics are not calculated using the **C1FlexGrid’s** **Aggregate** method, because that method uses the raw cell content and would interpret formulas as regular strings. Instead, the aggregates are calculated using formulas, as shown below.

// show summary statistics for extended selections
void _flex_SelChange(object sender, EventArgs e)
{
var status = "Ready";
if (_flex.Selection.IsValid && !_flex.Selection.IsSingleCell)
{
var sel = _flex.GetAddress(_flex.Selection);
var avg = _flex.Evaluate(string.Format("Average({0})", sel));
var count = _flex.Evaluate(string.Format("Count({0})", sel));
var sum = _flex.Evaluate(string.Format("Sum({0})", sel));
if ((double)count > 0)
{
status = string.Format(
"Average: {0:#,##0.##} Count: {1:n0} Sum: {2:#,##0.##}",
avg, count, sum);
}
}
_lblStatus.Text = status;
}

~~~

Conclusion

The CalcEngine class connects the calculation engine to the grid context, and custom grid classes that use the calculation engine to evaluate cells on demand. The sample can be customized and extended further to add more Excel-like features. The second part of this article will describe the same implementation for C1FlexGrid in Silverlight. If you have comments or suggestions, please comment below and let us know.

MESCIUS inc.

comments powered by Disqus