DataGridView with Formula Support

• Download FormulaDataGridView project source code - 1.4 MB

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