This is a migrated thread and some comments may be shown as answers.

Value changed of a cell with formula

2 Answers 69 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Fabrice
Top achievements
Rank 1
Fabrice asked on 08 Apr 2016, 04:07 PM

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 Answers, 1 is accepted

Sort by
0
Accepted
Nikolay Demirev
Telerik team
answered on 13 Apr 2016, 11:19 AM
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.
0
Fabrice
Top achievements
Rank 1
answered on 13 Apr 2016, 11:22 AM
Thanks for the feedback, will give it a try
Tags
Spreadsheet
Asked by
Fabrice
Top achievements
Rank 1
Answers by
Nikolay Demirev
Telerik team
Fabrice
Top achievements
Rank 1
Share this question
or