SpreadsheetControl.CellValueChanged Event

Occurs after the cell content was changed via the SpreadsheetControl UI.

Namespace: DevExpress.XtraSpreadsheet

Assembly: DevExpress.XtraSpreadsheet.v20.2.dll

Declaration

public event CellValueChangedEventHandler CellValueChanged
Public Event CellValueChanged As CellValueChangedEventHandler

Event Data

The CellValueChanged event's data class is SpreadsheetCellEventArgs. The following properties provide information specific to this event:

Property Description
Action Identifies an action which caused a change of the cell value.
Cell Gets the cell for which the event is fired.
ColumnIndex Gets the index of the column that contains the cell.
Formula Gets the formula that is currently contained in the cell.
FormulaInvariant Gets the formula in the invariant culture that is currently contained in the cell.
OldFormula Gets the cell's previous formula.
OldFormulaInvariant Gets the cell's previous formula in the invariant culture.
OldValue Gets the cell's previous value.
RowIndex Gets the index of the row that contains the cell.
SheetName Gets the name of the worksheet that contains the cell.
Value Gets the value currently contained in the cell.
Worksheet Gets the worksheet that contains the cell.

Remarks

The CellValueChanged event can fire in the following cases:

NOTE

The CellValueChanged event does not occur when a cell value was changed in code. Set the SpreadsheetControl.Options.Events.RaiseOnModificationsViaAPI property (WorkbookEventOptions.RaiseOnModificationsViaAPI) to true to raise this event when changes are made in code.

The CellValueChanged event does not occur after a formula was recalculated and its result was changed, even if the recalculation was triggered in the SpreadsheetControl UI.

Example

The example below shows how to validate user input for a sample spreadsheet:

Spreadsheet_Cell_Edit_Events

The following events are used to perform this task:

  • SpreadsheetControl.CellBeginEdit

    Handle this event to specify that a user can edit cells only in the "Unit Price" and "Units in Stock" columns.

  • SpreadsheetControl.CellEndEdit

    This event validates a value that a user enters into a cell. If the entered value is not a valid number, the Spreadsheet cancels the input and displays a warning.

  • SpreadsheetControl.CellValueChanged

    If a cell value in the "Units in Stock" column is equal to zero, the cell's background color changes to light pink.

using DevExpress.Spreadsheet;
using DevExpress.XtraSpreadsheet;
using System.Drawing;
using System.Globalization;
using System.Windows.Forms;
// ...

public partial class Form1 : DevExpress.XtraBars.Ribbon.RibbonForm
{
    public Form1()
    {
        InitializeComponent();
        spreadsheetControl1.CellBeginEdit += SpreadsheetControl1_CellBeginEdit;
        spreadsheetControl1.CellEndEdit += SpreadsheetControl1_CellEndEdit;
        spreadsheetControl1.CellValueChanged += SpreadsheetControl1_CellValueChanged;

        IWorkbook workbook = spreadsheetControl1.Document;
        workbook.LoadDocument("Products.xlsx", DocumentFormat.Xlsx);

    }

    private CultureInfo CurrentCulture => spreadsheetControl1.Options.Culture;

    private void SpreadsheetControl1_CellBeginEdit(object sender, SpreadsheetCellCancelEventArgs e)
    {
        // Allow users to edit cells only in the 'Unit Price' and 'Units in Stock' columns.
        if (e.Cell.ColumnIndex < 2 || e.Cell.ColumnIndex > 3 || e.Cell.RowIndex < 1 || e.Cell.RowIndex > 19)
        {
            e.Cancel = true;
            MessageBox.Show("You can edit only the 'Unit Price' and 'Units in Stock' values.", "Warning", MessageBoxButtons.OK);
        }
    }

    private void SpreadsheetControl1_CellEndEdit(object sender, SpreadsheetCellValidatingEventArgs e)
    {
        // Validate a value that a user enters into a cell.
        string editorText = e.EditorText;
        bool validValue = e.Cell.ColumnIndex == 2 ? ValidateUnitPrice(editorText) : ValidateUnitsInStock(editorText);
        if (!validValue)
        {
            e.Cancel = true;
            MessageBox.Show("The value you entered is invalid.", "Warning", MessageBoxButtons.OK);
        }
    }

    private bool ValidateUnitPrice(string text)
    {
        // Validate values in the 'Unit Price' column.
        // Convert the obtained string to a Double value. 
        bool conversionResult = double.TryParse(text, NumberStyles.AllowDecimalPoint, CurrentCulture, out double result);
        return conversionResult && result > 0;
    }

    private bool ValidateUnitsInStock(string text)
    {
        // Validate values in the 'Units in Stock' column.
        // Convert the obtained string to an integer value. 
        return int.TryParse(text, NumberStyles.None, CurrentCulture, out int result);
    }

    private void SpreadsheetControl1_CellValueChanged(object sender, SpreadsheetCellEventArgs e)
    {
        if (e.Cell.ColumnIndex != 3)
            return;
        // Highlight zero values in the 'Units in Stock' column.
        e.Cell.FillColor = (e.Value.NumericValue == 0) ? Color.LightPink : Color.White;
    }
}
See Also