Introduction

This article describes a simple approach for adding a calculation engine to ComponentOne FlexGrid for Silverlight. This second part also describes the implementation of a FlexBook class, which extends the Silverlight version of C1FlexGrid and uses the same CalcEngine class (discussed in Part 1) to add formula support to the grid. The custom CalcEngine classes are virtually identical in both WinForms and Silverlight. The grid implementations are different, mainly because of the fundamental differences in the platforms themselves. The Silverlight sample is more extensive and implements many other Excel-like features such as XLSX import/export, undo/redo, and more. Those features are not discussed here, since this article focuses on the formula support only. The ExcelBook sample can be downloaded from here: ExcelBook_Silverlight_CS

Using the CalcEngine with the C1FlexGrid for Silverlight

This section describes how you can use the CalcEngine class to implement ExcelBook, a control that derives from the Silverlight version of the C1FlexGrid and includes a calculation engine. The ExcelBook sample looks like the image below. The sample uses the following two main classes:

The ExcelCalcEngine class

This class inherits from the CalcEngine class described earlier and extends it to support references to cell ranges (e.g. “=SUM(A1:A10)”). It is virtually identical to the FlexCalcEngine class described in part one.

The C1FlexGridBook class

This class is a grid control that inherits from the C1FlexGrid. It stores values in the cells in unbound mode as usual and includes an ExcelCalcEngine member to evaluate formulas stored in cells. ExcelCalcEngine member The ExcelCalcEngine is responsible for evaluating the expressions contained in grid cells. The engine is created in the control’s constructor as follows:

/// <summary>  
/// Initializes a new instance of a C1FlexGridBook.  
/// </summary>  
public C1FlexGridBook()  
{  
    this.DefaultStyleKey = typeof(C1FlexGridBook);  

    // create context menu  
    _menu = new ExcelContextMenu(this);  

    // create calc engine  
    _ce = new ExcelCalcEngine(this);  
#if DEBUG  
    _ce.Test();  
#endif  

    // add some rows/columns by default  
    for (int r = 0; r < 50; r++)  
    {  
        Rows.Add(new ExcelRow());  
    }  
    for (int c = 0; c < 10; c++)  
    {  
        Columns.Add(new Column());  
    }  
}  

Notice how the grid is populated with ExcelRow objects rather than the standard Row objects. The C1FlexGridBook class implements a number of Excel-like features in addition to the calculation engine. These include rich cell-level styling, undo/redo, a multi-sheet tabbed interface, as well as XLSX import and export. Many of these features rely on the custom ExcelRow class which is described later. The calculation engine is used in the implementation of an Evaluate method implemented as follows:

/// <summary>  
/// Evaluates an expression and returns the result.  
/// </summary>  
/// <param name="expression">String containing the expression to evaluate.</param>  
/// <returns>The result of the expression.</returns>  
public object Evaluate(string expression)  
{  
    return _ce.Evaluate(expression);  
}  

The ExcelRow class

The ExcelRow class is responsible for keeping track of individual cell styles and for evaluating cells that contain formulas. It has a GetFormula helper method that returns the formula contained in a cell, or null if the cell is empty or contains non-formula values (e.g. strings, numbers, etc). The GetFormula method is implemented as follows:

/// <summary>  
/// Gets the formula in a cell.  
/// </summary>  
/// <param name="col">Column that defines the cell to retrieve.</param>  
/// <returns>A formula string or null.</returns>  
public string GetFormula(Column col)  
{  
    var value = base.GetUnboundValue(col) as string;  
    return !string.IsNullOrEmpty(value) && value[0] == '='  
        ? value  
        : null;  
}  

The ExcelRow class also implements an Evaluate method that detects formulas in cells and evaluates them. The method has a parameter that allows the caller to specify whether exceptions should be thrown or converted into error strings. This parameter is set to true when the calling context already has a try/catch block in place (e.g. when evaluating cell references). The Evaluate method is implemented as follows:

// evaluates the content of a cell  
object Evaluate(object val)  
{  
    var str = val as string;  
    if (!string.IsNullOrEmpty(str) && str[0] == '=')  
    {  
        var flex = this.Grid as C1FlexGridBook;  
        if (flex != null)  
        {  
            return flex.CalcEngine.Evaluate(str);  
        }  
    }  
    return val;  
}  

When showing cells in regular (non-edit) mode, the grid evaluates the formulas and shows the result. When showing cells in edit mode, however, the grid must display the actual expression. To accommodate this, the ExcelRow implements a GetDataEditor method that is used by the cell factory to initialize the contents of the cell editor.

/// <summary>  
/// Gets the data in a cell as it should be shown in the editor  
/// (full formulas, raw values).  
/// </summary>  
/// <param name="col">Column that defines the cell to retrieve.</param>  
public string GetDataEditor(Column col)  
{  
    var text = GetFormula(col);  
    if (text == null)  
    {  
        var val = GetDataRaw(col);  
        text = val != null ? val.ToString() : string.Empty;  
    }  
    return text;  
}  

Finally, the ExcelRow class overrides the GetData, GetDataRaw and GetDataFormatted methods to evaluate and optionally format the values contained in cells. Both of these methods rely on the Evaluate method described above:

// <summary>  
// Gets the value in a cell, evaluating expressions represented by  
// strings that start with an equals sign.  
// </summary>  
public object GetValue(Column col)  
{  
   var val = base.GetUnboundValue(col);  
   return Evaluate(val);  
}  
/// <summary>  
/// Gets the raw data in a cell (evaluates formulas but does not format the values).  
/// </summary>  
public override object GetDataRaw(Column col)  
{  
    try  
    {  
        return GetValue(col);  
    }  
    catch (Exception x)  
    {  
      return "** ERR: " + x.Message;  
    }  
}  
/// <summary>  
/// Overridden to apply formatting when getting data.  
/// </summary>  
public override string GetDataFormatted(Column col)  
{  
   // get data as usual  
    var data = GetDataRaw(col);  

    // apply format if value is formattable  
    var ifmt = data as IFormattable;  
    if (ifmt != null)  
    {  
        // get cell format  
        var s = GetCellStyle(col) as ExcelCellStyle;  
        var fmt = s != null && !string.IsNullOrEmpty(s.Format)  
            ? s.Format  
            : "#,##0.#########"; // set default precision to 9 digits  
        data = ifmt.ToString(fmt, CultureInfo.CurrentUICulture);  
    }  

    // done  
    return data != null ? data.ToString() : string.Empty;  
}  

Conclusion

Both the WinForms and Silverlight samples consist of custom CalcEngine classes that connect the calculation engine to the grid context, and custom grid classes that use the calculation engine to evaluate cells on demand. The custom CalcEngine classes are virtually identical in the WinForms and Silverlight samples. The grid implementations are different, mainly because of the fundamental differences in the platforms themselves, and also because the Silverlight sample is more extensive and includes many features not present in the WinForms sample. You can also check out a live demo of the ExcelBook sample below. http://demo.componentone.com/Silverlight/ExcelBook/ Either sample can be customized and extended further to add more Excel-like features. If you have comments or suggestions, please comment below and let us know.