DataGridView with formula support

In this article I will present a custom DataGridView control that allows using formulas composed by arithmetic expressions and references to the different cells of the control. In addition to the class library with the control, I have prepared a small application to show how to use the different features it offers.

In this link you can download the source code of the FormulaDataGridView project, written in csharp using Visual Studio 2015.

The project consists of three class libraries. FormulaDataGridView is the implementation of the control itself, BNFUP implements the formula compiler, and GridFormulas contains the execution engine of the compiled formulas. In this article I will comment on the use of the DataGridView. In this other article you can read more about the syntax definition and rule editor of the BNFUP universal compiler.

Syntax of the formulas

The formulas that can be used with the control are assigned to each of the DataGridView cells, they consist of arithmetic expressions that can use the following operators: subtraction and unary minus (-), sum (+), product (*), quotient (/) and exponentiation (^). You can also use sub formulas in parentheses.

The elements to which you can apply these operations are the following:

• Numbers: they can have decimals.
• Variables: There are two variables, col is the index of the column of the cell with the formula, and row is the index of the row.
• Cell references: they are written in brackets ([ and ]) and the coordinates of the column and the row are separated by a colon (:), first the column and then the row. The coordinates can be any expression, but their value is rounded to an integer value. For example, [col: row-1] refers to the cell above the current cell. Its value is the value of the cell. If the cell is empty or its value is not numeric, it is ignored.
• Aggregation functions: These functions process a range of cells. All of them have two parameters, separated by the comma character (,), the first must be the reference to the first cell to be processed and the second the reference to the last cell. They can be in different rows. The functions available are: sum (sum of cell values), prod (product), avg (arithmetic mean), max and min (which return the maximum and minimum values respectively), var and svar (variance and sample variance) and sd and ssd (standard deviation and sample standard deviation). For example, sum([0: 0], [col-1: row]).
• Mathematical functions: They only have one parameter, which can be any arithmetic expression. There are the following functions: ln (neperian logarithm), log (base 10 logarithm), exp (the number e raised to the argument), sqrt (square root), abs (absolute value), ceil (the minor integer greater than or equal to the number), floor (the largest integer less than or equal to the number) sin (sin), cos (cosine) and tan (tangent). For example sqrt([0: 0] * [1:0] + ln (5)).

The language-defining syntax is found in the formulas.bnf file, in the TestForm project. It is a binary file, which can be modified to extend the language. To open it you will need the BNFUPEditor editor, which you can download from the BNFUP project, in the article linked above.

To extend the language it is also necessary to modify the GridFormulas project classes. The previous article also explains everything necessary to modify this type of projects.

TestForm application

The TestForm sample application shows how to use the control in your own projects. It looks like that:

The first three controls are used to add columns and rows to the DataGridView. To add a new column, type a name in the text box and click Add Column. You can add rows once the grid has columns with New Row.

The Data Bind button simply creates a DataTable object with three columns and two rows, with two formulas in the last column, and binds it to the control using the DataSouce property. You can use this example to see how to pass formulas to the DataGridView control directly from data. Here is the event handler code:

DataTable dt = new DataTable();
dt.Columns.Add("A0");
dt.Columns.Add("A1");
dt.Columns.Add("Total");
DataRow row = dt.NewRow();
row["A0"] = 1;
row["A1"] = 3;
row["Total"] = "=sum([0:row],[col-1:row])";
dt.Rows.Add(row);
row = dt.NewRow();
row["A0"] = 5;
row["A1"] = 7;
row["Total"] = "=avg([0:row],[col-1:row])";
dt.Rows.Add(row);
frmGrid.Columns.Clear();
frmGrid.DataSource = dt;
frmGrid.BindFormulas();

Formulas must be passed with a = character at the beginning, to recognize them as such. Once the control is bound, it is enough to call the BindFormulas method to convert them to compiled formulas. This is the result:

When you select a cell with a formula, it appears in the TextBox control associated with the FormulaDataGridView, using the FormulaEditor property. You can modify the formula and recompile it with the button to the right of the TextBox. This button simply calls the UpdateFormula method, without parameters.

You can also update the formula of a particular cell programmatically with another version of the UpdateFormula method, which receives the formula text, the column and the row index of the cell as parameters.

You can see that the cells referenced by the formula are highlighted in yellow. This is implemented in the ShowReferencedCells method of the TestForm application:

private void ShowReferencedCells()
{
foreach (DataGridViewRow row in frmGrid.Rows)
{
foreach (DataGridViewCell cell in row.Cells)
{
cell.Style.BackColor = Color.White;
}
}
foreach (DataGridViewCell cell in frmGrid.SelectedCells)
{
FormulaBase frm = frmGrid.GetFormula(cell.ColumnIndex, cell.RowIndex);
if (frm != null)
{
foreach (Point p in frm.CellReferences)
{
frmGrid.Rows[p.Y].Cells[p.X].Style.BackColor = Color.Yellow;
}
}
}
}

The formula can be obtained with the GetFormula method of the FormulaDataGridView control, passing the index of the column and the row. The object it returns is of FormulaBase class, which has a CellReferences property that enumerate all the cells referenced in the form of Point structures, with the column in the X property and the row in the Y property.

If the form editor is empty when you call the UpdateFormula method, the formula is removed, even though its value remains in the cell.

The other two buttons are for storing the contents of the FormulaDataGridView in a csv file and for loading the data from a file of the same type. The first row of the file will contain the names of the columns, and the rest of the rows the data. Cells with formulas are saved with the text of the formula preceded by a = character. The character used to separate the fields is the semicolon (;), which is not used in any of the expressions in the formulas, so, if your data contains this character, the file will not be saved correctly

The FormulaDataGridView control has two methods to deal with this type of files, SaveCSV and ReadCSV, to which you have to pass as parameter the file name. You can load a sample file that is in the directory of the TestForm application, gridsample.csv.

Finally, there is a method, Initialize, which clears all formulas in the FormulaDataGridView control, and a LanguageFile property with which you can change the file with the syntax used by the formulas, which must be a .bnf file, generated with The BNFUPEditor tool. Simply assign this property a text string with the path of the file.

And that's all you need to know to be able to use this control in your applications. Remember to add the references to the FormulaDataGridView.dll, BNFUP.dll, and GridFormulas.dll assemblies in your project.