Two savecellinfos for a single update to a formula cell

[Ben Fraser]

I'm using a virtual grid with formula cells. Everything works but I have noticed something odd. When I type a formula into a cell the SaveCellInfo event is fired twice. The first time I get back the correct GridFormulaTag but the CellValue still holds the old value of the cell. The second time the formula tag is correct and the cell value holds the formula I've just typed in. This doesn't cause any errors since it is the second update that is saved, but it does seem inefficent. I've created a small example to show the problem, what can I do to avoid the first update?

Cheers,
Ben

SeperateModelTest0.zip

[Administrator]

Here is what is going on.

As your user types in a formula, during the validation process, the formula engine actually parses the formula to make sure it is OK and an exception does not have to be thrown during cell validation. So, at this point, the engine saves the parsed formula which has not yet been computed. This saving triggers a SaveCellInfo event. Then later, when the cell is being drawn, a call to GetFormattedText gets the saved parsed formula and computes it, saving the computed value in the process. It is this saving of the fully populated FormulaTag at this point which triggers the second call to SaveCellInfo.

Now, if we did not do the first save during validation, during the GetFormattedText call, the formula would have been parsed (for a second time) and computed. So, getting the two SaveCellInfo's allows the engine to avoid parsing the formula twice. It is not clear which would be more efficient, always parsing twice or sometimes hitting a SaveCellInfo event handler twice (since external SaveCellInfo's would only be hit in virtual formula grids). We chose to avoid the always double parsing.

We could add a public property that could be set to not save the parsed formula during validation which would avoid the double SaveCellInfo, but I am not sure it is really needed. I guess it would depend upon exactly what work you needed to do in SaveCellInfo.

[Ben Fraser]

I can understand the position that not many people use virtual grids with formula cells and I agree that the approach you have described is the correct one in general.

However, we are using the virtual grid because we are storing all the actual data on a server. A message is generated to the server after each SaveCellInfo which in turn updates all other clients viewing the sheet at that point in time. This is a bigger performance hit than a bit of local parsing. We need to avoid a double SaveCellInfo.

A public property to avoid the double save would be a Good Thing for our application. If possible we would like to see that implemented.

Thank You,
Ben

[Administrator]

I think you can avoid the second hit of SaveCellInfo by just going ahead and creating the GridFormulaTag yourself in your SaveCellInfo code the first time it is hit. This way, the tag is already present when GetFormattedText is called and will not have to be created (and saved thru a SaveCellInfo) at that time. So, in your sample, add

private GridFormulaEngine engine;

private void Form1_Load(object sender, EventArgs e)
{
//....
this.engine = ((GridFormulaCellModel)gridControl1.Model.CellModels["FormulaCell"]).Engine;
}

        public void GridSaveCellInfo(object sender, GridSaveCellInfoEventArgs e)
        {
            try
            {
                if (e.ColIndex > 0 && e.RowIndex > 0)
                {
                    WithFormula withFormula = new WithFormula();
                    withFormula.value = e.Style.CellValue.ToString();
                    if (e.Style.FormulaTag != null)
                    {
                        withFormula.tag = e.Style.FormulaTag;
                    }
                    else if(withFormula.value.StartsWith("="))
                    {
                        string parsedFormula = engine.Parse(withFormula.value.Substring(1));
                        string computedValue = engine.ComputedValue(parsedFormula);
                        withFormula.tag = new GridFormulaTag(parsedFormula, computedValue, e.RowIndex, e.ColIndex);
                    }

                    _extData[e.RowIndex - 1, e.ColIndex - 1] = withFormula;
                }
            }
            catch { }
            e.Handled = true;
        }

[Ben Fraser]

Ummm...the formula tag is already populated just fine on the first call to SaveCellInfo, it is the cell value that is the problem.

I tried your code anyway, no dice, the SaveCellInfo method still gets hit twice.

Perhaps you are thinking of something else?

If you happened to mean filling in the CellValue property from the formula tag in the SaveCellInfo that didn't work for me either.

Are there any other alternatives?

Thanks,
Ben

[Administrator]

I am sorry. I had tweaked our library code to include a flag, and my ccode was using this flag when I tried the above solution that only hit SaveCellInfo once for me.

Below is the sample back. It uses the above technique with a custom formula celltype to allow you to avoid SaveCellInfo processing during the renderer.OnValidate call. This means you can avoid the double access to your server for each change.


SeperateModelTest1.zip

[Ben Fraser]

Your fix does improve efficency for us and that is critical. Thank you. However, the result is not always correct.

Using the code you have supplied, any cell that has a formula at start up will not update correctly when new values are entered into the cell.

I've added initial formulas to all of row 4 in the modified example we're using via the ExternalData constructor. Run the program and modify cell C4 from =A3 + 6 to =A3 + 66. The CellValue gets updated correctly but the formula result is still 206, rather than 266. If you then change the value of A3 to something else, the result in C4 reverts to using A3 + 6 rather than A3 + 66.

By adding a break point to the line "if (!renderer.IgnoreSaveCellInfo)" in GridSaveCellInfo of Form1.cs it can be observed that the initial (ignored) hit has the correct FormulaTag value and an incorrect CellValue while the second (saved) hit has the right CellValue but an incorrect FormulaTag. If the IgnoreCellInfo check is removed, saving the correct FormulaTag on the first hit seems to magically update the CellValue to the correct amount; everything then works fine.

This problem does not seem to effect any cells that are not initally loaded with a formula.

What's going on here?

Thanks,
Ben

SeperateModelTest2.zip

[Administrator]

I think you can handle this problem by subscribing to the CurrentCellValidating event and making sure the formula information for the modified cell is reset before the grid starts its SaveCellInfo cycle.



SeperateModelTest3.zip