Back to Devexpress

SpreadsheetControl.CellBeginEdit Event

wpf-devexpress-dot-xpf-dot-spreadsheet-dot-spreadsheetcontrol-e8039c32.md

latest10.0 KB
Original Source

SpreadsheetControl.CellBeginEdit Event

Occurs before a cell editor is opened.

Namespace : DevExpress.Xpf.Spreadsheet

Assembly : DevExpress.Xpf.Spreadsheet.v25.2.dll

NuGet Package : DevExpress.Wpf.Spreadsheet

Declaration

csharp
public event CellBeginEditEventHandler CellBeginEdit
vb
Public Event CellBeginEdit As CellBeginEditEventHandler

Event Data

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

PropertyDescription
CancelGets or sets whether the operation performed on the processed event should be canceled.
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.
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 CellBeginEdit event to perform any actions when a user activates a cell editor (the in-place editor or formula bar).

Use the Cell parameter to access a cell for which the cell editor is opened. The Value and Formula parameters return a value and formula currently contained in this cell.

To prevent a user from editing a cell value, handle this event and set its Cancel parameter to true.

After a cell editor is activated, the SpreadsheetControl.CellEditorOpened event fires. The SpreadsheetControl.CellEndEdit or SpreadsheetControl.CellCancelEdit event occurs before a cell editor is closed, depending on whether the entered value is committed to a cell or rolled back.

Example

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.

xaml
<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>
csharp
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;
}
vb
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

CellEditorOpened

CellEndEdit

CellCancelEdit

SpreadsheetControl Class

SpreadsheetControl Members

DevExpress.Xpf.Spreadsheet Namespace