In this article, you can see the implementation of a custom DataGridView which will allow you to add complex formulas to their cells.

## Introduction

The project presented in this article is a custom `DataGridView`

implementation that allows you to add complex formulas to their cells.

The implementation of the formula engine is based on the **BNFUP** universal compiler, which is explained in this article.

The `FormulaDataGridView`

project contains the implementation of the grid itself, whereas the `GridFormulas`

project is for the formula engine implementation. The `BNFUP`

project is necessary to provide parsing and compiling services.

So, if you plan to use the grid in your projects, you have to put references to three different class libraries, *BNFUP.dll*, *FormulaDataGridView.dll* and *GridFormulas.dll*.

The formulas are provided in plain text strings and are arithmetic expressions that can use the **-**,**+**,*****,**/** and **^** (exponentiation) operators.

The elements with which you can use those operators are of the following types:

**Numbers**, that can have decimals. **Variables**. There are two variables, `col`

contains the column index of the cell that contains the formula, and `row`

the row index. **Cell references**. You can get the value of a cell of the grid putting a reference to it in brackets (**[** and **]**). To indicate the column and row, you can use any expression, but the value will be rounded to a integer. Separate the two expressions with a colon character (**:**). The first place is for the column index, and the second for the row, by example: `[col:row-1]`

refers to the cell above that with the formula. **Math functions**. They have only one argument, which can be any arithmetic expression. The allowed functions are `abs`

(absolute value), `ceil`

(the lower integer greater than or equal to the argument), `floor`

(the biggest integer less than or equal to the argument), `ln`

(neperian logarithm), `log`

(base 10 logarithm), `sin`

(sine), `cos`

(cosine), `tan`

(tangent), `exp`

(e raised to the argument) and `sqrt`

(square root). For example, with `sqrt([0:0])`

, you get the square root of the first cell in the grid. **Aggregate functions**. They have two comma separated arguments, which must be cell references. The first argument indicates the first row to process, and the second the last cell. All cells in the rectangle defined by these two are processed by the function. The available functions are `max`

and `min`

(the maximum and minimum value), `sum`

and `prod`

(the sum and product of the values), `avg`

(the arithmetic mean), `var`

and `svar`

(variance and sampling variance) and `sd`

and `ssd`

(standard deviation and sampling standard deviation). For example, `sum([0:row],[col-1:row])`

calculates the sum of all the cells at the left of the formula.

The `DataGridView`

is very easy to use, you barely have to write a few lines of code, but for better understanding, I have added the `TestForm`

project to the solution, which shows how to use all the grid features. You can read more about this project in this article of my blog, here in Spanish.

## Using the Code

Really, the `FormulaDataGridView`

control is nothing more than a normal `DataGridView`

. No custom cell types nor other complex stuff are added. It can be used as usual. The formulas are managed internally by the grid, using a `Dictionary`

which links them with their cell coordinates, and I only have used a few new properties and methods to extend his functionality. This is the list of them:

public string LanguageFile { get; set; }
public TextBox FormulaEditor { get; set; }
public FormulaBase GetFormula(int col, int row);
public void SaveCSV(string filename);
public void ReadCSV(string filename);
public void UpdateFormula();
public void UpdateFormula(string formula, int col, int row);
public void BindFormulas();
public void Initialize();

With the `FormulaEditor`

property, you can provide a `TextBox`

control to allow the user to edit the formulas in the grid. When the formula has changed, you must use the `UpdateFormula`

method without parameters to compile it. If the text is an empty string, the formula is deleted from all the selected cells in the grid, otherwise, each of the selected cells gets an instance of the compiled formula. All of them are different, so, if you change any of them, the rest will remain unchanged.

You can also change programmatically the formula of a cell with the `UpdateFormula`

method version with parameters. You must provide a `string`

with the formula and the column and row index of the cell.

If you are binding the `DataGrid`

with some kind of data source, you can also provide formulas in the data fields by providing them as text strings starting with the `=`

character. In this case, you have to call the `BindFormulas`

method to indicate the grid that it has to compile them. Think, by example, in a SQL command like this:

SELECT Q1, Q2, Q3, Q4, '=sum([0:row],[col-1:row])' as Total FROM TABLE;

If you fill the grid with a command like this, five columns will be created, and the last will be a calculated column with the sum of the first four columns of each row.

With the `Initialize`

method, you can clear all the formulas in the grid.

The `SaveCSV`

and `ReadCSV`

methods can be used to save the grid data or load it from a **csv** file. The first row of that file will contain the column headers, and the fields must be separated with the semicolon character (**;**), which is not used anyplace in the formulas. If a cell has an associated formula, the formula is saved in the file as text, starting with the `=`

character in the corresponding cell position.

The `GetFormula`

method returns the formula associated with a given cell or `null`

, if the cell has no formula. The formula has a generic `FormulaBase`

type, defined in the `GridFormulas`

project. From this class, you only need to know the `CellReferences`

property, which enumerate all the cells referenced in the formula in the form of Point structures, if you want to give feedback to the user when the cell with the formula is selected, the `Value`

property, which returns the result of the formula, and the `AsString`

property, which gives you the formula as a text string.

The true complexity of the project is not in the `DataGridView`

itself, but in the `GridFormulas`

project. As you have the source code, you can extend the language used in the formulas, mainly by adding more functions. To do so, you have to deal with BNF rules and the `BNFUPEditor`

tool, which allows you to define the language used to compile them. Look at the article referenced above to learn how to use this tool. This is the definition, in BNF format, of the current language implemented in the project:

<number>::=<digit>[<rnumber>]
|<decimalsep><rdecimal>;
<rnumber>::=<digit>[<rnumber>]
|<decimalsep><rdecimal>;
<rdecimal>::=<digit>[<rdecimal>];
<digit>::={0-9};
<decimalsep>::={,\.};
<variable>::='col','row';
<cell>::='['<expr>'';''<expr>']';
<function>::='sum','avg','max','min','prod','var','sd','svar','ssd' '('<cell>','<cell>')'
|'sqrt','ln','exp','sin','cos','tan','abs','ceil','floor','log' '('<expr>')';
<<expr>>::=<expr2>['+','-'<expr>];
<expr2>::=<expr1>['*','/'<expr2>];
<expr1>::=<expr0>['^'<expr1>];
<expr0>::=['-']<element>;
<element>::=<pexpr>
|<number>
|<variable>
|<cell>
|<function>;
<pexpr>::='('<expr>')';

The class that implement the functions in the `GridFormulas`

project is `Function`

. If you plan to add more functions to the language, modify the rules in the *functions.bnf* file (in the `TestForm`

project directory) using the `BNFUPEditor`

rule editor, and add the new function names and implementation to the `Function`

class. These are the methods you have to take into account for that:

public override bool AddItem(ICompilableObject item);
public override double Value { get; set; }
private double PerformFunction(int c1, int r1, int c2, int r2);
private double PerformFunction();

The `AddItem`

method is called when the object is built. Here, you have to validate the function names. In the `Value`

property, you have to return the value of the function result. The method `PerformFunction`

is where you have to implement the function itself. There are two versions. The one without parameters is for the math functions, which get their argument from the `_exp`

variable. The version with parameters is for the aggregate functions, and the parameters are the initial and final indexes of the column and row of the processed cells.

Once you have modified the language syntax and the *GridFormulas.dll* library, you have two options. One of them is use the `LanguageFile`

property of the `FormulaDataGridView`

control to pass the path of this file for the grid to use the extended language. The other is substitute the *.bnf* language file in the resources file of the `FromulaDataGridView`

project, and let the grid use it when it is created to build the rule table.

And that's all! I think that this control is very easy to use and hope that it can be useful for someone to improve their applications with extra features.

Thanks for reading!!!

## History

- 14
^{th} January, 2017: Initial version

I'm working with computers since the 80's of the past century, when I received as a present a 48K Spectrum which changed all my life plans, from a scientific career to a technical one. I started working in assembler language, in low lewel systems, mainly in the electromedical field. Today I work as a freelance, mainly in .NET Framework / database solutions, using the C# language.

I'm interested in scientific computer applications, and I,m learning AI and data analytics technics. I also own a technical blog, http://software-tecnico-libre.es/en/stl-index, where I publish some of the practice works of this learning process.