How to: Assign Custom In-place Editors to Worksheet Cells
- 6 minutes to read
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.
Predefined Cell In-Place Editors
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.
- A Range object that specifies a cell or cell range to which the custom in-place editor should be assigned.
- A CustomCellInplaceEditorType enumeration value that specifies the type of the custom editor you wish to use for cell editing. If you select the CustomCellInplaceEditorType.Custom value, you must handle the SpreadsheetControl.CustomCellEdit event to define a custom in-place editor to be assigned to cells.
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.
Note
A complete sample project is available at https://github.com/DevExpress-Examples/how-to-assign-custom-in-place-editors-to-worksheet-cells-t385401
// Use a date editor as the in-place editor for cells located in the "Order Date" column of the worksheet table.
Range dateEditRange = worksheet["Table[Order Date]"];
worksheet.CustomCellInplaceEditors.Add(dateEditRange, CustomCellInplaceEditorType.DateEdit);
// Use a combo box editor as the in-place editor for cells located in the "Category" column of the worksheet table.
// The editor's items are obtained from a cell range in the current worksheet.
Range comboBoxRange = worksheet["Table[Category]"];
worksheet.CustomCellInplaceEditors.Add(comboBoxRange, CustomCellInplaceEditorType.ComboBox, ValueObject.FromRange(worksheet["J3:J9"]));
// Use a check editor as the in-place editor for cells located in the "Discount" column of the worksheet table.
Range checkBoxRange = worksheet["Table[Discount]"];
worksheet.CustomCellInplaceEditors.Add(checkBoxRange, CustomCellInplaceEditorType.CheckBox);
// Use the custom control (SpinEdit) as the in-place editor for cells located in the "Quantity" column of the worksheet table.
// To provide the required editor, handle the CustomCellEdit event.
Range customRange = worksheet["Table[Qty]"];
worksheet.CustomCellInplaceEditors.Add(customRange, CustomCellInplaceEditorType.Custom, "MySpinEdit");
The image below shows the result.
User-Defined Cell In-Place Editors
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.
Important
The custom cell in-place editors assigned to cells using the SpreadsheetControl.CustomCellEdit event are not saved to a file.
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.
Note
A complete sample project is available at https://github.com/DevExpress-Examples/how-to-assign-custom-in-place-editors-to-worksheet-cells-t385401
spreadsheetControl.CustomCellEdit += spreadsheetControl1_CustomCellEdit;
// ...
private void spreadsheetControl1_CustomCellEdit(object sender, DevExpress.XtraSpreadsheet.SpreadsheetCustomCellEditEventArgs e)
{
// Specify a type of the custom editor assigned to cells of the "Quantity" table column.
// To identify the custom editor, use a value of ValueObject associated with it.
if (e.ValueObject.IsText && e.ValueObject.TextValue == "MySpinEdit")
{
// Create a repository item corresponding to a SpinEdit control and specify its settings.
RepositoryItemSpinEdit repository = new RepositoryItemSpinEdit();
repository.AutoHeight = false;
repository.BorderStyle = DevExpress.XtraEditors.Controls.BorderStyles.NoBorder;
repository.MinValue = 1;
repository.MaxValue = 1000;
repository.IsFloatValue = false;
// Assign the SpinEdit editor to a cell.
e.RepositoryItem = repository;
}
}
The image below shows the result.
