Back to Devexpress

SpreadsheetControl.CellEndEdit Event

windowsforms-devexpress-dot-xtraspreadsheet-dot-spreadsheetcontrol-62dfa611.md

latest12.6 KB
Original Source

SpreadsheetControl.CellEndEdit Event

Occurs before a cell editor is closed and the entered value is committed.

Namespace : DevExpress.XtraSpreadsheet

Assembly : DevExpress.XtraSpreadsheet.v25.2.dll

NuGet Package : DevExpress.Win.Spreadsheet

Declaration

csharp
public event CellEndEditEventHandler CellEndEdit
vb
Public Event CellEndEdit As CellEndEditEventHandler

Event Data

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

PropertyDescription
CancelGets or sets whether the operation performed on the processed event should be canceled. Inherited from SpreadsheetCellCancelEventArgs.
CellGets the cell for which the event is fired. Inherited from SpreadsheetCellEventArgsBase.
ColumnIndexGets the index of the column that contains the cell. Inherited from SpreadsheetCellEventArgsBase.
EditorTextGets or sets the string entered in the cell editor.
FormulaGets the formula that is currently contained in the cell. Inherited from SpreadsheetCellEventArgsBase.
FormulaInvariantGets the formula in the invariant culture that is currently contained in the cell. Inherited from SpreadsheetCellEventArgsBase.
RowIndexGets the index of the row that contains the cell. Inherited from SpreadsheetCellEventArgsBase.
SheetNameGets the name of the worksheet that contains the cell. Inherited from SpreadsheetCellEventArgsBase.
ValueGets the value currently contained in the cell. Inherited from SpreadsheetCellEventArgsBase.
WorksheetGets the worksheet that contains the cell. Inherited from SpreadsheetCellEventArgsBase.

Remarks

Handle the CellEndEdit event to perform any actions before a cell editor (the in-place editor or formula bar) is closed and the entered value is committed to an active cell or selected cells. This event fires when a user clicks outside the edited cell, or presses ENTER, CTRL+ENTER or CTRL+SHIFT+ENTER to complete the input, or the SpreadsheetControl.CloseCellEditor method is called with a parameter value other than CellEditorEnterValueMode.Cancel.

The Cell parameter returns a cell for which the cell editor is activated. The Value and Formula parameters return a value and formula currently contained in this cell. The EditorText parameter returns a string that a user enters. To cancel the closure of a cell editor, set the event’s Cancel parameter to true.

When a user presses ESC or the SpreadsheetControl.CloseCellEditor method is called with the CellEditorEnterValueMode.Cancel parameter, the Spreadsheet rolls back the entered cell value and raises the SpreadsheetControl.CellCancelEdit event.

Handle the SpreadsheetControl.CellBeginEdit event to perform any actions before the cell editor is activated.

After a cell value was changed via the SpreadsheetControl‘s UI, the SpreadsheetControl.CellValueChanged event occurs. You can specify whether this event should occur each time a new value is committed to a cell, or only if the committed value differs from the previous value. Use the SpreadsheetCellEditorBehaviorOptions.CommitMode property to access this option.

Example

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

The following events are used to perform this task:

csharp
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;
    }
}
vb
Imports DevExpress.Spreadsheet
Imports DevExpress.XtraSpreadsheet
Imports System.Drawing
Imports System.Globalization
Imports System.Windows.Forms
' ...

Partial Public Class Form1
    Inherits DevExpress.XtraBars.Ribbon.RibbonForm
    Public Sub New()
        InitializeComponent()
        AddHandler spreadsheetControl1.CellBeginEdit, AddressOf SpreadsheetControl1_CellBeginEdit
        AddHandler spreadsheetControl1.CellEndEdit, AddressOf SpreadsheetControl1_CellEndEdit
        AddHandler spreadsheetControl1.CellValueChanged, AddressOf SpreadsheetControl1_CellValueChanged

        Dim workbook As IWorkbook = spreadsheetControl1.Document
        workbook.LoadDocument("Products.xlsx", DocumentFormat.Xlsx)
    End Sub

    Private ReadOnly Property CurrentCulture() As CultureInfo
        Get
            Return spreadsheetControl1.Options.Culture
        End Get
    End Property

    Private Sub SpreadsheetControl1_CellBeginEdit(ByVal sender As Object, ByVal e As SpreadsheetCellCancelEventArgs)
        ' Allow users to edit cells only in the 'Unit Price' and 'Units in Stock' columns.
        If e.Cell.ColumnIndex < 2 OrElse e.Cell.ColumnIndex > 3 OrElse e.Cell.RowIndex < 1 OrElse e.Cell.RowIndex > 19 Then
            e.Cancel = True
            MessageBox.Show("You can edit only the 'Unit Price' and 'Units in Stock' values.", "Warning", MessageBoxButtons.OK)
        End If
    End Sub

    Private Sub SpreadsheetControl1_CellEndEdit(ByVal sender As Object, ByVal e As SpreadsheetCellValidatingEventArgs)
        ' Validate a value that a user enters into a cell.
        Dim editorText As String = e.EditorText
        Dim validValue As Boolean = If(e.Cell.ColumnIndex = 2, ValidateUnitPrice(editorText), ValidateUnitsInStock(editorText))
        If Not validValue Then
            e.Cancel = True
            MessageBox.Show("The value you entered is invalid.", "Warning", MessageBoxButtons.OK)
        End If
    End Sub

    Private Function ValidateUnitPrice(ByVal text As String) As Boolean
        ' Validate values in the 'Unit Price' column.
        ' Convert the obtained string to a Double value.
        Dim result As Double
        Dim conversionResult As Boolean = Double.TryParse(text, NumberStyles.AllowDecimalPoint, CurrentCulture, result)
        Return conversionResult AndAlso result > 0
    End Function

    Private Function ValidateUnitsInStock(ByVal text As String) As Boolean
        ' Validate values in the 'Units in Stock' column.
        ' Convert the obtained string to an integer value.
        Dim result As Integer
        Return Integer.TryParse(text, NumberStyles.None, CurrentCulture, result)
    End Function

    Private Sub SpreadsheetControl1_CellValueChanged(ByVal sender As Object, ByVal e As SpreadsheetCellEventArgs)
        If e.Cell.ColumnIndex <> 3 Then
            Return
        End If
        ' Highlight zero values in the 'Units in Stock' column.
        e.Cell.FillColor = If(e.Value.NumericValue = 0, Color.LightPink, Color.White)
    End Sub

End Class

See Also

CellCancelEdit

CellBeginEdit

CellEditorOpened

CellValueChanged

SpreadsheetControl Class

SpreadsheetControl Members

DevExpress.XtraSpreadsheet Namespace