Creating an Excel-like Formula Bar in .NET WinForms

The ComponentOne Calculation Engine “C1CalcEngine”, a .NET Standard library that enables parsing and computing expression/formulas, is available for all ComponentOne .NET platforms and can be used to facilitate formula calculation.

The library is equipped with many advanced features, like extensive calculation support for business objects, an out-of-the-box expression parser for parsing text expressions that follow the Microsoft Excel format, and more.

Image shows Excel-styled expression in Excel-like formula bar along FlexGrid.

One such advanced feature is Cross-worksheet reference. This article will walk through how this feature can be used in building Excel-like Formula Bar in .NET WinForms application.

What is Cross-worksheet Cell Reference?

A cell reference is a set of coordinates that specify a cell/cell range position on a worksheet. Cell references in formulas are used to obtain and process data contained in the corresponding cells. One advantage of using references is that the formulas' results are automatically updated each time the values of these cells change.

Additionally, there are scenarios where data is distributed among multiple sheets. In such situations, cells can reference cells located in other worksheets. This is where the C1CalcEngine cross-worksheet referencing feature is helpful.

To refer to cells in another worksheet, identifying the target cell/cell-range is needed, but the sheet where the cells are located needs to be identified. To do this, like Excel, the target worksheet name is defined before the cell/cell-range reference, separated by an exclamation point (!).

=Sum(sheet1!A1:B2) + Max(sheet2!A1:C7) 

Here, the expression uses two sheets, “sheet1” and “sheet2,” and applies mathematical functions “Sum” and “Max”, respectively.

How to Create Excel-like Formula Bar

In Excel, the Formula Bar is a visual element that allows end-users to edit data values and formulas contained in worksheet cells.

The image shows the Formula Bar component in MS Excel.

Using C1CalcEngine, it’s easy to add Excel-like Formula Bar behavior in your .NET application. Let’s see how.

Steps

As a non-UI component, C1CalcEngine does not provide any visual components to display on the form. Therefore, to stimulate the Formula Bar behavior in your WinForms application and define formulas similar to Excel, you will need to build your own formula bar.

To build an Excel-like Formula bar, you can use TextBox and Button controls to display/enter expressions and use C1CalcEngine to parse these expressions for calculations. The steps below demonstrate how to add an Excel-like formula bar in your .NET WinForms application from scratch.

We’ll be dividing the complete implementation into the following parts:

• Setting up application
• Adding References to libraries
• Creating DataSource
• Binding C1CalcEngine to DataSource
• Assigning Excel-styled Expressions
• Evaluating Expressions using C1CalcEngine

Setting Up the Application

1. Create a new Windows Forms Application and name it _CalcEngine_CrossShee__t_.
2. Add a TextBox control to display the expression.
3. Add a Button control to trigger the expression's parsing and set its “Text” property to Evaluate.
4. Add a Label control to the Form and set its “Text” property to Result.
5. Add another TextBox control to display the expression evaluation result.
6. For illustration, add two FlexGrid controls to the Form to represent two Excel-worksheets that contain cross-reference.

Post-completion of this step, the form should look similar to the above image.

Add References

1. Install C1CalcEngine NuGet package using NuGet Package Manager.
2. Switch to the code view and add the following namespaces:

using C1.CalcEngine;
using C1.CalcEngine.ExcelEngine;
using C1.Win.C1FlexGrid;

Create Data Source

To use cross-sheet references, all referenced worksheets should be passed as a data source for C1CalcEngine, and each element of the list will act as a sheet for C1CalcEngine. The expressions evaluated by the C1CalcEngine use these sheets and their names to implement cross-sheet referencing.

Therefore, let's create a custom DataTable that implements the IDataSheet interface, to be used as FlexGrid's DataSource. This generates a bound FlexGrid control to be used as a DataSource for C1CalcEngine.

//Custom DataTable implementing IDataSheet
public class SheetTable : DataTable, IDataSheet
{
    public string Name
    {
        get => TableName;
        set => TableName = value;
    }
    public object GetValue(int col, int row)
    {
        return Rows[row][col];
    }
}

Bind CalcEngine to Data Source

Define C1CalcEngine and a string variable globally using the following code:

private C1CalcEngine _calcEngine;
private const string ColumnNames = "ABCDEF";

Add the following method to initialize SheetTable class to be used as C1FlexGrid’s DataSource:

//Generate DataTable to be used as FlexGrid's DataSource
public SheetTable GetDataTable(string sheetName)
{
    var table = new SheetTable();
    table.Name = sheetName;
    foreach (var col in ColumnNames)
        table.Columns.Add(col.ToString(), typeof(int));
    for (int i = 0; i < 80; i++)
        table.Rows.Add(new object[] { i * 2, i * 3, i * 4, i * 5, i * 6, i * 7 });
    return table;
}

Use the method defined above to generate FlexGrids' DataSource and use the sheets as C1CalcEngine's DataSource:

var sheet1 = GetDataTable("Sheet1");
var sheet2 = GetDataTable("Sheet2");
c1FlexGrid1.DataSource = sheet1;
c1FlexGrid2.DataSource = sheet2;

_calcEngine.DataSource = new List<IDataSheet> { sheet1, sheet2 };

Assign Expression

To update Formula bar (expression textbox) as the selection changes in worksheets (FlexGrid), handle the SelChange event of FlexGrid controls. Define the event handler as follows to generate C1CalcEngine expressions based on the current selection of FlexGrid instances.

//Get grid selection and create Excel expression
private void grid_SelChange(object sender, System.EventArgs e)
{    
    tbExpression.Text = string.Format("=Sum(sheet1!{0}) + Sum(sheet2!{1})", GetMark(c1FlexGrid1), GetMark(sheet2));
}

Define the following method to translate the FlexGrid selection to Excel expression notation so that it gets evaluated by C1CalcEngine:

private string GetMark(C1FlexGrid grid)
{
    var sel = grid.Selection;
    return string.Format("{0}{1}:{2}{3}", ColumnNames[sel.LeftCol - 1], sel.TopRow, ColumnNames[sel.RightCol - 1], sel.BottomRow);
}

The image shows the formula bar with a sample expression.

Evaluate Result

To evaluate the created Excel-styled expressions, use the TryEvaluate method of the C1CalcEngine class. For this, handle the Click event of the button and add the following code to it:

//Evaluate the created Excel expression
private void btnEvaluate_Click(object sender, EventArgs e)
{
    _calcEngine.Expression = tbExpression.Text;
    if (_calcEngine.TryEvaluate(out object result))        
        tbResult.Text = (result ?? "").ToString();
    else        
        tbResult.Text = _calcEngine.GetErrors().FirstOrDefault()?.FullMessage ?? "";
}

Be sure to download the sample application and try using it in your use case scenario. If you have any suggestions, feel free to leave a comment below.