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. Image for the cookie policy date

Formula recalc does not always raise SetCellInfo

I''m creating an Excel-like application in which a virtual grid is filled with FormulaCells. I need to store the calculated values in a database. Unfortunately, the SetCellInfo event is not always raised when a formula cell recalculates. Specifically, it appears to affect a VISIBLE (on-screen) formula cell that depends on a NON-VISIBLE (off-screen) formula cell. The visible cell recalculates and displays the correct value, but the non-visible cell only receives one SetCellInfo event, always with an empty value. Even after scrolling the non-visible cell onto the screen, it never receives a SetCellInfo event WITH a value. This is a big issue for us. I have been forced to call RecalculateRange with the entire grid, and I''m not sure that it solves all cases. I''m unable to get the file upload to work, so here is code for a repro (based on formulas in GDBD sample but for a GridControl): Imports System Imports System.Drawing Imports System.Collections Imports System.ComponentModel Imports System.Windows.Forms Imports System.Data Imports Syncfusion.Windows.Forms.Grid Public Class Form1 Inherits System.Windows.Forms.Form Private gridControl1 As Syncfusion.Windows.Forms.Grid.GridControl Private components As System.ComponentModel.Container = Nothing Public Sub New() '' '' Required for Windows Form Designer support '' InitializeComponent() End Sub Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose() End If End If MyBase.Dispose(disposing) End Sub ''Dispose #Region "Windows Form Designer generated code" ''/ ''/ Required method for Designer support - do not modify ''/ the contents of this method with the code editor. ''/ Private Sub InitializeComponent() Me.gridControl1 = New Syncfusion.Windows.Forms.Grid.GridControl CType(Me.gridControl1, System.ComponentModel.ISupportInitialize).BeginInit() Me.SuspendLayout() '' ''gridControl1 '' Me.gridControl1.AllowDragSelectedCols = True Me.gridControl1.Anchor = CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _ Or System.Windows.Forms.AnchorStyles.Left) _ Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles) Me.gridControl1.ColWidthEntries.AddRange(New Syncfusion.Windows.Forms.Grid.GridColWidth() {New Syncfusion.Windows.Forms.Grid.GridColWidth(0, 35)}) Me.gridControl1.Location = New System.Drawing.Point(8, 8) Me.gridControl1.Name = "gridControl1" Me.gridControl1.ShowCurrentCellBorderBehavior = Syncfusion.Windows.Forms.Grid.GridShowCurrentCellBorder.GrayWhenLostFocus Me.gridControl1.Size = New System.Drawing.Size(328, 160) Me.gridControl1.SmartSizeBox = False Me.gridControl1.TabIndex = 0 Me.gridControl1.Text = "gridControl1" '' ''Form1 '' Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13) Me.ClientSize = New System.Drawing.Size(344, 174) Me.Controls.Add(Me.gridControl1) Me.Name = "Form1" Me.Text = "Form1" CType(Me.gridControl1, System.ComponentModel.ISupportInitialize).EndInit() Me.ResumeLayout(False) End Sub ''InitializeComponent #End Region _ Shared Sub Main() Application.Run(New Form1) End Sub ''Main Private formulaTags As ArrayList Private dt As DataTable Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load dt = New DataTable("MyTable") Dim nRows As Integer = 10 dt.Columns.Add(New DataColumn("Col1", GetType(Double))) dt.Columns.Add(New DataColumn("Col2", GetType(Double))) dt.Columns.Add(New DataColumn("Col3", GetType(Double))) dt.Columns.Add(New DataColumn("formula", GetType(String))) Dim i As Integer While i < nRows Dim dr As DataRow = dt.NewRow() dr(0) = System.Convert.ToDouble(2 * i) dr(1) = System.Convert.ToDouble(i) If i Mod 2 = 0 Then dr(2) = -1 Else dr(2) = 0 End If dt.Rows.Add(dr) i += 1 End While '' Maintain formulaTags for the cells ====================== formulaTags = New ArrayList For i = 0 To dt.Rows.Count + 1 formulaTags.Add(Nothing) Next AddHandler Me.gridControl1.Model.QueryCellInfo, AddressOf GridQueryCellInfo AddHandler Me.gridControl1.Model.SaveCellInfo, AddressOf GridSaveCellInfo End Sub ''Form1_Load Private Sub GridSaveCellInfo(ByVal sender As Object, ByVal e As GridSaveCellInfoEventArgs) If Not e.Style.FormulaTag Is Nothing Then Debug.WriteLine("FormulaTag: " & e.Style.FormulaTag.Formula & " <--> " & e.Style.FormulaTag.Text) End If If e.ColIndex = 4 AndAlso e.RowIndex > 0 AndAlso e.RowIndex <= dt.Rows.Count Then formulaTags((e.RowIndex - 1)) = e.Style.FormulaTag e.Handled = True End If End Sub ''GridSaveCellInfo Private Sub GridQueryCellInfo(ByVal sender As Object, ByVal e As GridQueryCellInfoEventArgs) If e.ColIndex = 4 And e.RowIndex > 0 Then e.Style.CellType = "FormulaCell" If e.RowIndex = 10 Then e.Style.Text = "=SUM(D8:D9)" e.Style.FormulaTag = CType(Me.formulaTags((e.RowIndex - 1)), GridFormulaTag) ElseIf e.RowIndex = 2 Then e.Style.Text = "=D10" e.Style.FormulaTag = CType(Me.formulaTags((e.RowIndex - 1)), GridFormulaTag) Else e.Style.Text = e.RowIndex Return End If e.Handled = True Return End If If e.ColIndex > 0 And e.ColIndex < 4 And e.RowIndex > 0 Then e.Style.Text = dt.Rows(e.RowIndex - 1)(e.ColIndex - 1) End If End Sub ''GridQueryCellInfo End Class ''Form1

9 Replies

TA Tom Abraham March 15, 2005 10:13 PM UTC

To clarify, no "value" means that FormulaTag.Text is an empty string. Watch the debug output in the test case and you''ll see that the SUM formula is hit once with an empty FormulaTag.Text and never again, even if you scroll around the grid.

TA Tom Abraham March 15, 2005 10:59 PM UTC

This is

AD Administrator Syncfusion Team March 16, 2005 12:42 AM UTC

The act of retrieving the style for a cell will guarantee the FormulaTag s populated. You can see this by adding a button to the form in your sample with this handler. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim style As GridStyleInfo = Me.gridControl1(2, 4) If Not style.FormulaTag Is Nothing Then Debug.WriteLine("FormulaTag: " & style.FormulaTag.Formula & " <--> " & style.FormulaTag.Text) Else Console.WriteLine("Nothing") End If style = Me.gridControl1(10, 4) If Not style.FormulaTag Is Nothing Then Debug.WriteLine("FormulaTag: " & style.FormulaTag.Formula & " <--> " & style.FormulaTag.Text) Else Console.WriteLine("Nothing") End If End Sub In your case, do you know cells that have formulas? If so, when you are ready to save them, can you iterate through them retrieiving the cell style?

TA Tom Abraham March 16, 2005 04:11 PM UTC

This is a virtual grid, so I have a data store that holds the formula, formula tag, data value and style information. I''m feeding that stuff back to the grid all the time in the QueryCellInfo. The grid is a mix of formula cells and textbox cells. Whenever anything changes in the grid, including the value of a formula, I''m relying on the fact that SaveCellInfo will be called so that I can capture the changes into the data store for the next QueryCellInfo. Iterating through the cells isn''t really an option. The missing event seems like it could be a bug. Is there anything else I can do? Calling RecalculateRange on the whole table, to get the otherwise-missing values, takes almost 60 seconds on a fast machine.

AD Administrator Syncfusion Team March 16, 2005 07:10 PM UTC

We have added a property, ForceSaveCellInfo, to the GridFormulaEngine that will let you force this event to be raised even when the affected cell is off the screen. The default value of this property will be false to continue the current default behavior as this is more efficient. This property will be in teh next release which is in the works, but I do not know when it will actually hit.

TA Tom Abraham June 22, 2005 07:29 PM UTC

I''m using and GridFormulaEngine.ForceSaveCellInfo doesn''t seem to do anything. Everything is FormulaCells. The cell that I change has three SUMIF formulas above it, and since those are on-screen I get SaveCellInfo and CellsChanged for each of those. I also have other cells and more SUMIFs above them that use the changed cell as a source in their formulas. They are not on-screen and do not get SaveCellInfo or CellsChanged, despite ForceSaveCellInfo = True. Tom

AD Administrator Syncfusion Team June 23, 2005 12:07 AM UTC

I can see the problem here. We will get this fixed.

BT Byron Tate July 15, 2005 09:02 PM UTC

I''m on and see the same thing. I tried this: m_Engine.ForceSaveCellInfo = true; m_Engine.RecalculateRange(GridRangeInfo.Table(),true,true); still doesn''t call SaveCellInfo event for undisplayed rows.

AD Administrator Syncfusion Team July 15, 2005 10:47 PM UTC

This is fixed in the 3.3 release.

Live Chat Icon For mobile
Up arrow icon