We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Adjusting formulas on row insert

Thread ID:





68861 Oct 3,2007 09:18 PM UTC Oct 19,2007 11:13 AM UTC WinForms 8
Tags: GridControl
Asked On October 3, 2007 09:18 PM UTC


I programmatically insert new rows and columns through the following code

GridControl1.Model.Rows.InsertRange(1, 1)

I have a formula referencing a cell below the inserted row, i.e. (=B3)

The B3 is not adjusting to B4. Is there any property I must set to force this to occur?



haneefm [Syncfusion]
Replied On October 3, 2007 09:36 PM UTC

Hi Nick,

You can try setting FormulaCopyFlags property to tell the grid to adjust references in different situations.

GridFormulaEngine engine = ((GridFormulaCellModel)grid.Model.CellModels["FormulaCell"]).Engine;
engine.FormulaCopyFlags = GridFormulaCopyFlags.ClipBoardReferencesAdjusted | GridFormulaCopyFlags.InsDelRangeReferencesUpdated;

Please refer this sample that show what are the things that are supported: (all the FormulaCopyFlags)

Best regards,

Replied On October 3, 2007 11:48 PM UTC

Thank you for the quick response and sample code Haneef.

I seem to be having a problem when processing multiple inserts with beginUpdate and endUpdate. It seems to adjust the formula for the first insert but neglects any other insert. Below is the code I use:

Dim gm As GridModel = GridControl1.Model

Dim formulaModel As GridFormulaCellModel = gm.CellModels.Item("FormulaCell")
Dim engine As GridFormulaEngine = formulaModel.Engine
engine.FormulaCopyFlags = engine.FormulaCopyFlags Or GridFormulaCopyFlags.InsDelRangeReferencesUpdated

Me.GridControl1.Model.Rows.InsertRange(1, 1)
Me.GridControl1.Model.Rows.InsertRange(2, 1)
gm.EndUpdate()The result would shift a formula =B5 to =B6 but not B7.

I am running version Is this problem present in the current release or is there a work around?

Thank you for the help.


Administrator [Syncfusion]
Replied On October 4, 2007 12:24 PM UTC

Locking the drawing is preventing some of the events that the formulaengine listens to from being raised on the two sequential calls to InsertRows. And this is causing the engine to not be able to update things properly.

Here are some things that worked for me to avoid this problem.

1) Remove the grid.BeginUpdate / grid.EndUpdate calls. (Then all the events are raised and the references get updated.

2) End the update immediately after the InsertRows call, and then restart it.

Me.GridControl1.Model.Rows.InsertRange(1, 1)
Me.GridControl1.Model.Rows.InsertRange(2, 1)

3) Instead of sequentially inserting the 2 rows, insert them in one call.

Me.GridControl1.Model.Rows.InsertRange(1, 2)

This problem is also in the latest code base.

Replied On October 4, 2007 08:40 PM UTC

Thank you for the work arounds.

My code is doing far more than the example posted. Normally rows will not be inserted seqentially but in a scattered fashion in different points in the grid. i.e insert at row 10, 5 lines, insert at column 5 lines 3, etc.

There are many operations occuring besides row and column inserts (such as shifting and applying values and formatting) and efficency is a big issue. The begin and end update statements are used for efficency purposes. I open and close the begin and end after all my operations have occurred and refresh only the parts on the grid which changed. Everything is shifted correctly on the grid but the formulas are not adjusting. Option 2 seems like a possible work around.

Are there plans to fix this bug or should I submit it to direct-trac?

Thank you for the help.


Administrator [Syncfusion]
Replied On October 5, 2007 09:11 AM UTC

The problem lies with an optimization line code in our formula engine. The line checks if the affected cell is visible, and if it is, does not actually force the change. This prevents some blinking on the visible cells and speeds things up a little as the cells are automatically updated as they are drawn due to the insert or insert. So, only non-visible affected cells are 'forced' to be adjusted. Well, when you are in a BeginUpdate block, the visible affected cells are not automatically adjusted as they are not automatically redrawn. The fix for this problem would be to ignore this optimization if the grid.Updating property is set true. This will require a code change in our library that we will make. It should be available in the next release. If you want to track this defect process, submit a Direct Trac support incident referring to this forum post.

Until there is a release with this code change, calling the EndUpdate/BeginUpate immediately after the Insert should avoid this problem.

Replied On October 5, 2007 06:08 PM UTC

Very good to hear. Thank you for all the information and all your help.

Replied On October 18, 2007 09:03 PM UTC

Sorry to repoen the topic but I am having trouble closing all the BeginUpdate statements. I comment out every BeginUpdate and EndUpdate method calls I can find and still encounter the same issue. Is there any other code which may cause the events to be surpressed? Is there any way to manually call the surpressed events which prevent formula recalculation after each insert and remove?

Thank you.


Administrator [Syncfusion]
Replied On October 19, 2007 11:13 AM UTC

What version of our libraries are you using? Is it, the latest version? There were problems with cell references updating in earlier versions, but we thought they were fixed by 5.x.

I do not have any specific ideas about what to look for. Essentially, style.GetFormattedtext has to be triggerred between every Insert/Remove call. Calling grid.BeginUpdate interferes with process, and that was what was causing the problem in the sample you provided. What else are you doing at the point you are inserting/deleting rows/columns?


This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

or the page will be automatically redirected to sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon