Formula recalc does not always raise SetCellInfo

[Tom Abraham]

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

[Tom Abraham]

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.

[Tom Abraham]

This is 3.0.1.0.

[Administrator]

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?

[Tom Abraham]

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.

[Administrator]

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.

[Tom Abraham]

I''m using 3.2.1.1 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

[Administrator]

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

[Byron Tate]

I''m on 3.2.1.0 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.

[Administrator]

This is fixed in the 3.3 release.