Value changed of a cell with formula

3 posts, 1 answers
  1. Fabrice
    Fabrice avatar
    22 posts
    Member since:
    Jan 2015

    Posted 08 Apr Link to this post

    Apparently the worksheet's Cells.CellPropertyChanged event is not raised when a cell with a formula is recalculated because one of the source cells was changed. Is there any way of getting notified in this situation?

     

  2. Answer
    Nikolay Demirev
    Admin
    Nikolay Demirev avatar
    103 posts

    Posted 13 Apr Link to this post

    Hello,

    The CellPropertyChanged event is used only for notifying if some of the cell properties are changed. As the value of the cell is a formula value, when the formula is recalculated the actual cell value is not changed because the formula remains the same, only the result of its evaluation is changed.

    When a cell has a formula value it is stored in the FormulaCellValue object as a FormulaExpression. When some of the cells, which the formula refers are changed the FormulaExpression is invalidated and UI is notified for that invalidation. When the UI is updated all invalidated formulas are evaluated and the new result is presented in the UI.

    You could use the following code snippet to track if some FormulaExpression is invalidated. Please bear in mind that you have to detach from the events to prevent memory leaks or use weak events.

    Worksheet worksheet = this.radSpreadsheet.ActiveWorksheet;
    worksheet.Cells.CellPropertyChanged += this.Cells_CellPropertyChanged;
     
    private void Cells_CellPropertyChanged(object sender, CellPropertyChangedEventArgs e)
    {
        Worksheet worksheet = this.radSpreadsheet.ActiveWorksheet;
     
        if (e.Property == CellPropertyDefinitions.ValueProperty)
        {
            CellRange cellRange = e.CellRange;
            for (int rowIndex = cellRange.FromIndex.RowIndex; rowIndex <= cellRange.ToIndex.RowIndex; rowIndex++)
            {
                for (int columnIndex = cellRange.FromIndex.ColumnIndex; columnIndex <= cellRange.ToIndex.ColumnIndex; columnIndex++)
                {
                    FormulaCellValue formulaValue = worksheet.Cells[rowIndex, columnIndex].GetValue().Value as FormulaCellValue;
                    if (formulaValue != null)
                    {
                        formulaValue.Value.ValueInvalidated += this.Value_ValueInvalidated; // use weak events to prevent memory leaks
                    }
                }
            }
        }
    }
     
    private void Value_ValueInvalidated(object sender, EventArgs e)
    {
        // the formula needs recalculation
    }


    Regards,
    Nikolay Demirev
    Telerik
    Do you need help with upgrading your AJAX, WPF or WinForms project? Check the Telerik API Analyzer and share your thoughts.
  3. UI for WPF is Visual Studio 2017 Ready
  4. Fabrice
    Fabrice avatar
    22 posts
    Member since:
    Jan 2015

    Posted 13 Apr in reply to Nikolay Demirev Link to this post

    Thanks for the feedback, will give it a try
Back to Top