Click here to Skip to main content
13,189,324 members (67,371 online)
Click here to Skip to main content
Add your own
alternative version

Stats

6.7K views
212 downloads
3 bookmarked
Posted 14 Jan 2017

DataGridView with formula support

, 14 Jan 2017
Rate this:
Please Sign up or sign in to vote.
Custom DataGridView that allow you to add formulas to 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] referes 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 firs cell in the grid.
  • Aggregate functions. They have two comma separated arguments, which must be cell references. The firs argument indicates the firs 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]) calculate 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 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, otherwhise, 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 programatically 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 fromulas. 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, an 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 read!!!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Miguel Diaz Kusztrich
Software Developer (Senior) Free lance
Spain Spain
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.

You may also be interested in...

Pro

Comments and Discussions

 
BugTest-Application behaves like ?? Pin
Mr.PoorEnglish21-Jan-17 3:00
memberMr.PoorEnglish21-Jan-17 3:00 
GeneralRe: Test-Application behaves like ?? Pin
Miguel Diaz Kusztrich22-Jan-17 1:26
professionalMiguel Diaz Kusztrich22-Jan-17 1:26 
BugRe: Test-Application behaves like ?? Pin
Mr.PoorEnglish22-Jan-17 2:01
memberMr.PoorEnglish22-Jan-17 2:01 
GeneralRe: Test-Application behaves like ?? Pin
Miguel Diaz Kusztrich22-Jan-17 8:02
professionalMiguel Diaz Kusztrich22-Jan-17 8:02 
GeneralMy vote of 5 Pin
Member 1236439016-Jan-17 21:22
memberMember 1236439016-Jan-17 21:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171016.2 | Last Updated 14 Jan 2017
Article Copyright 2017 by Miguel Diaz Kusztrich
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid