This example explains how to provide custom in-place editors for cells in a worksheet. A cell in-place editor in the Spreadsheet control is activated when an end-user double-clicks a cell or press F2 when a cell is selected. With custom cell editors, you can address multiple usage scenarios such as an ability to create a data entry form within a document to make the data input process as easy as possible for your end-users.
A Spreadsheet control supports a set of predefined editors for in-place editing of cell values. These include: ComboBoxEdit, DateEdit and CheckEdit editors. Information about predefined cell in-place editors contained in a workbook is saved to a file in the XLS, XLSX, XLT, or XLTX formats, so these editors can be successfully restored when you load a document into the Spreadsheet control again.
Custom cell editors specified in a worksheet are stored in the CustomCellInplaceEditorCollection collection, which can be accessed by using the Worksheet.CustomCellInplaceEditors property. To assign a custom in-place editor of a particular type to a cell or cell range in a worksheet, use the CustomCellInplaceEditorCollection.Add method and pass the following parameters.
The CustomCellInplaceEditorCollection.Add method is overloaded to accept two optional parameters.
-
A value associated with a custom in-place editor. This value is represented by the ValueObject instance and can be used in the following way.
If you use a combo box editor for cell editing (CustomCellInplaceEditorType.ComboBox), the ValueObject allows you to supply items for the editor's drop-down list. You can directly pass a string of comma-separated items to the CustomCellInplaceEditorCollection.Add method or use the ValueObject.FromRange method to obtain the required items from a cell range in a worksheet. Using types of values other than a text string or cell range is not allowed (otherwise, a System.ArgumentException will be raised).
The ValueObject instance is also associated with the SpreadsheetControl.CustomCellEdit event and provided through the event data class (SpreadsheetCustomCellEditEventArgs). Use the event's SpreadsheetCustomCellEditEventArgs.ValueObject parameter to obtain the ValueObject's value and then identify the custom editor to which this value belongs to adjust the editor’s properties as required.
-
The useOnlyVisibleDataRange parameter. This Boolean parameter is relevant only for a combo box editor whose items originate from a cell range (using the ValueObject.FromRange property) and allows you to specify whether values of hidden cells should be included into the editor's item list. Using this parameter in other cases will trigger a System.ArgumentException.
The example below demonstrates how to use different custom cell editors to edit values of specific table columns.
| C#:Form1.cs |
Range dateEditRange = worksheet["Table[Order Date]"];
worksheet.CustomCellInplaceEditors.Add(dateEditRange, CustomCellInplaceEditorType.DateEdit);
Range comboBoxRange = worksheet["Table[Category]"];
worksheet.CustomCellInplaceEditors.Add(comboBoxRange, CustomCellInplaceEditorType.ComboBox, ValueObject.FromRange(worksheet["J3:J9"]));
Range checkBoxRange = worksheet["Table[Discount]"];
worksheet.CustomCellInplaceEditors.Add(checkBoxRange, CustomCellInplaceEditorType.CheckBox);
Range customRange = worksheet["Table[Qty]"];
worksheet.CustomCellInplaceEditors.Add(customRange, CustomCellInplaceEditorType.Custom, "MySpinEdit");
|
| VB:Form1.vb |
Dim dateEditRange As Range = worksheet("Table[Order Date]")
worksheet.CustomCellInplaceEditors.Add(dateEditRange, CustomCellInplaceEditorType.DateEdit)
Dim comboBoxRange As Range = worksheet("Table[Category]")
worksheet.CustomCellInplaceEditors.Add(comboBoxRange, CustomCellInplaceEditorType.ComboBox, ValueObject.FromRange(worksheet("J3:J9")))
Dim checkBoxRange As Range = worksheet("Table[Discount]")
worksheet.CustomCellInplaceEditors.Add(checkBoxRange, CustomCellInplaceEditorType.CheckBox)
Dim customRange As Range = worksheet("Table[Qty]")
worksheet.CustomCellInplaceEditors.Add(customRange, CustomCellInplaceEditorType.Custom, "MySpinEdit")
|
The image below shows the result.
If the predefined editors do not comply with your requirements, handle the SpreadsheetControl.CustomCellEdit event to assign your own custom editor to worksheet cells. This event fires when an end-user is about to start editing a cell and allows you to supply a custom in-place editor to the edited cell. The event's Cell parameter provides access to the cell for which the cell editor is activated. To provide an editor for editing a cell value, assign a corresponding RepositoryItem descendant to the event's SpreadsheetCustomCellEditEventArgs.RepositoryItem parameter. A repository item stores properties and events related to a specific editor. It has all the information required for creating a corresponding fully functional editor. Refer to the Editor Class Structure topic for additional information on this mechanism.
In the code example above, cells of the "Quantity" table column have been marked as containing a custom cell in-place editor. The following example demonstrates how to use the SpreadsheetControl.CustomCellEdit event to assign a specific editor (SpinEdit) to these cells.
| C#:Form1.cs |
spreadsheetControl.CustomCellEdit += spreadsheetControl1_CustomCellEdit;
private void spreadsheetControl1_CustomCellEdit(object sender, DevExpress.XtraSpreadsheet.SpreadsheetCustomCellEditEventArgs e)
{
if (e.ValueObject.IsText && e.ValueObject.TextValue == "MySpinEdit")
{
RepositoryItemSpinEdit repository = new RepositoryItemSpinEdit();
repository.AutoHeight = false;
repository.BorderStyle = DevExpress.XtraEditors.Controls.BorderStyles.NoBorder;
repository.MinValue = 1;
repository.MaxValue = 1000;
repository.IsFloatValue = false;
e.RepositoryItem = repository;
}
}
|
| VB:Form1.vb |
AddHandler spreadsheetControl.CustomCellEdit, AddressOf spreadsheetControl1_CustomCellEdit
Private Sub spreadsheetControl1_CustomCellEdit(ByVal sender As Object, ByVal e As DevExpress.XtraSpreadsheet.SpreadsheetCustomCellEditEventArgs)
If e.ValueObject.IsText AndAlso e.ValueObject.TextValue = "MySpinEdit" Then
Dim repository As New RepositoryItemSpinEdit()
repository.AutoHeight = False
repository.BorderStyle = DevExpress.XtraEditors.Controls.BorderStyles.NoBorder
repository.MinValue = 1
repository.MaxValue = 1000
repository.IsFloatValue = False
e.RepositoryItem = repository
End If
End Sub
|
The image below shows the result.
