implementing a grid which can handle both cell references and variables

[Administrator]

I am trying to implement a grid where I can have a formula in a cell which contains both references to cells and variables. An example might be something like:

= (3.1416 * [RADIUS] ^ 2) / sum(c1:c10)

where [RADIUS] has been defined using the ICalcData interface and whose value can be determined by using the CalcQuickBase parseAndCompute function.

I tried writing a parsing function and placing it in the CurrentCellEditingComplete event handler, but the gridControl's parser is activated before control reaches the event handler and all I get is an error message about invalid characters before or after an operator. How would I go about implementing this type of functionality?

[Arulraj A]

Hi Jan,

Thanks for your interest in Syncfusion Products.

To handle formula calculation in grid cells, you can make use of GridFormulaEngine instead of CalcQuick or CalcEngine. In this, the use of named range concept implements your requirement. The GridFormulaEngine’s AddNamedRange method does this.

You can set a value for a name and make use of the name as reference for the value in grid cell.
For e.g.:
this.engine.addNamedRange(“RADIUS”,”50”); // Do this in the code
Instead of “50” you can give any textbox value also.

While giving the input in the grid cells, give the input as like below.
= (3.1416 * RADIUS ^ 2) / sum(c1:c10)
This will give the correct result.

Please refer to the browser sample “Named Range For Formula Demo” shipped with Syncfusion Dashboard in the following location.
[Install Drive]:\Syncfusion\{Installed version}\Windows\Grid.Windows\Samples\2.0\Formula Support\Named Range For Formula Demo\cs

Regards,
Arulraj.A

[Administrator]

I looked at the method you suggested. I guess my description of the problem was lacking clarity. The name and value of the variable won't be known until runtime. I could invoke the named range editor, but I'm not sure how I would associate the named range with the cell that the user used for declaring the variable. (Which I may need to do.) While I may be able to get around that by simply telling the user that he/she has no choice but to use the editor, it's not an ideal approach. Also, can values in the named range editor be defined as equations describing a combination of one or more of the other named ranges? I already know that I will run into that situation.

example: (3.1416 * RADIUS ^ 2) / sum(c1:c10)
where: RADIUS = FOO + BAR
FOO = 5
and BAR = 6

(named ranges could be used for FOO and BAR. What about for RADIUS?)

[Administrator]

Ignore the update. I tried it and it seems to work with nested definitions (at least simple ones). I will explore further and update this if I get really stuck.

[Christopher Issac Sunder K]

Hi Jan,

Thanks for the update.

Let us know if you need any further clarifications regarding this.

Regards,
Christopher K.