wpf-devexpress-dot-xpf-dot-spreadsheet-dot-spreadsheetcontrol-b0f62739.md
Occurs before a cell editor is closed.
Namespace : DevExpress.Xpf.Spreadsheet
Assembly : DevExpress.Xpf.Spreadsheet.v25.2.dll
NuGet Package : DevExpress.Wpf.Spreadsheet
public event CellEndEditEventHandler CellEndEdit
Public Event CellEndEdit As CellEndEditEventHandler
The CellEndEdit event's data class is SpreadsheetCellValidatingEventArgs. The following properties provide information specific to this event:
| Property | Description |
|---|---|
| Cancel | Gets or sets whether the operation performed on the processed event should be canceled. Inherited from SpreadsheetCellCancelEventArgs. |
| Cell | Gets the cell for which the event is fired. Inherited from SpreadsheetCellEventArgsBase. |
| ColumnIndex | Gets the index of the column that contains the cell. Inherited from SpreadsheetCellEventArgsBase. |
| EditorText | Gets or sets the string entered in the cell editor. |
| Formula | Gets the formula that is currently contained in the cell. Inherited from SpreadsheetCellEventArgsBase. |
| FormulaInvariant | Gets the formula in the invariant culture that is currently contained in the cell. Inherited from SpreadsheetCellEventArgsBase. |
| RowIndex | Gets the index of the row that contains the cell. Inherited from SpreadsheetCellEventArgsBase. |
| SheetName | Gets the name of the worksheet that contains the cell. Inherited from SpreadsheetCellEventArgsBase. |
| Value | Gets the value currently contained in the cell. Inherited from SpreadsheetCellEventArgsBase. |
| Worksheet | Gets the worksheet that contains the cell. Inherited from SpreadsheetCellEventArgsBase. |
The example below shows how to validate user input for a sample spreadsheet:
The following events are used to perform this task:
SpreadsheetControl.CellBeginEditHandle this event to specify that a user can edit cells only in the “Unit Price” and “Units in Stock” columns.SpreadsheetControl.CellEndEditThis 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.CellValueChangedIf a cell value in the “Units in Stock” column is equal to zero, the cell’s background color changes to light pink.
<Grid>
<dxs:SpreadsheetControl x:Name="spreadsheetControl"
DocumentSource="pack://application:,,,/Spreadsheet WPF;component/Products.xlsx"
CommandBarStyle="Ribbon"
ShowFormulaBar="True"
ShowStatusBar="True"
CellBeginEdit="SpreadsheetControl1_CellBeginEdit"
CellEndEdit="SpreadsheetControl1_CellEndEdit"
CellValueChanged="SpreadsheetControl1_CellValueChanged" />
</Grid>
private CultureInfo CurrentCulture => spreadsheetControl.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;
}
Private ReadOnly Property CurrentCulture() As CultureInfo
Get
Return spreadsheetControl.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
See Also