Working with UI Selection
This article aims to present the API of the Selection class and demonstrate how to retrieve and change the current selection and also store and restore its state. It contains the following subsections:
What is UI Selection?
In order to interact with the working surface of RadSpreadsheet, the user creates a UI selection. The selection can be two types: cell selection and shape selection.
The cell selection designates a region/regions of cells and performs a given action onto these cells. The selection can be a single cell, a rectangular region of cells, or a composition of rectangular regions. The UI selection can contain numerous selected cells, however, only one of the cells is active at a time.
The shape selection can contain one or more shapes.
Selection Properties
The RadWorksheetEditor class exposes a Selection property of type Selection that provides rich API for retrieving and changing the UI Selection of the RadSpreadsheet. The class Selection exposes several properties that provide information about the current selection. The following list outlines the properties of Selection:
ActiveCell: Returns a CellSelection instance containing the active cell.
ActiveCellMode: Gets or sets the ActiveCellMode to either Display or Edit.
ActiveCellIndex: The CellIndex of the ActiveCell.
ActiveRange: The CellRange containing the ActiveCell.
SelectedRanges: The collection of selected ranges.
IsRowSelection: A Boolean value indicating if the selection is a single row selection.
IsColumnSelection: A Boolean value indicating if the selection is a single column selection.
Cells: Returns an object that represents the cells contained in the selection.
Rows: Returns object that describes the contained rows.
Columns: Returns object that describes the contained columns.
IsCellSelection: Gets a value indicating whether the active selection at the moment is cell selection or shape selection.
ShapeSelection: Returns an object that represents the shapes contained in the selection.
The cell selection and the properties related to it can be accessed and used, even if the current active selection is shape selection. In such case, while the shape selection is the one which is visible, the underlying cell selection continues to exist.
Retrieving the Active Cell and All Selected Cells
To get the region of cells that are currently selected, first you need to get the Selection property of the RadWorksheetEditor. Once you have an instance of the Selection class, you can use its Cells property to retrieve the selected regions.
The Selection class also exposes an ActiveCell property that designates the current active cell. The next example demonstrates how to change the ActiveCellMode to Edit.
Change the ActiveCellMode
Selection selection = this.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection;
selection.ActiveCellMode = ActiveCellMode.Edit;
Dim selection = Me.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection
selection.ActiveCellMode = ActiveCellMode.Edit
Changing the UI Selection
The UI cell selection can be a single cell or a rectangular region of cells. If you hold down the Ctrl key, you can select multiple rectangular regions of cells. Note that these cell ranges do not have to be adjacent. In fact, they can be dispersed across the worksheet and can even intersect. The next several examples aim to illustrate how to create one and multiple-region selection through the API of the RadSpreadsheet.
The UI shape selection can consist of a single shape or more. If you hold down the Ctrl key you can add multiple shapes to the selection or remove them from it. If you remove all shapes from the shape selection, the cell selection will become the active selection automatically. The cells included in the selection will be the cells which were selected when this selection was used last.
The Selection class exposes an overloaded Select method that offers flexible ways to change the UI selection. All of the overloads have an optional Boolean parameter named clearSelection. As its name suggests the argument indicates whether the current selection will be cleared. If set to true, the parameter will cause the current selection to be wiped out and if set to false, the newly selected region will be added to the current selection.
One of the Select overloads gets a CellIndex parameter that points to the cell to be selected (or added to the selection). The following example demonstrates how the clearSelection parameter changes the outcome of this Select() method.
Selecting a Cell
Selection selection_2 = this.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection;
selection_2.Select(new CellIndex(0, 0), false);
Dim selection_2 = Me.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection
selection_2.Select(New CellIndex(0, 0), False)
The Selection class also offers a Select() method that takes a CellRange as an argument. The overload selects (or adds to the current selection) the designated region and makes the top left cell to be the Active one. The next sample snippet shows how to select the C3:D4 cell region:
Select using CellRange
Selection selection_3 = this.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection;
selection_3.Select(new CellRange(0, 0, 2, 2));
Dim selection_3 = Me.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection
selection_3.Select(New CellRange(0, 0, 2, 2))
Another overload of the Select method takes as input two CellIndex instances that indicate the start and the end cell indexes of the selected region. Note that unlike the Select(CellRange) method, this overload makes the cell with the start CellIndex the active one. The following snippet illustrates how use the method to select the region B7 to E3. Note that the active cell is B7, not B3.
Here is a sample code on how you can achieve the result from the above image through the RadSpreadsheet API:
Select Region
Selection selection = this.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection;
selection.ActiveCellMode = ActiveCellMode.Edit;
Dim selection = Me.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection
selection.ActiveCellMode = ActiveCellMode.Edit
A similar logic applies to the Select() methods which take shapes as their parameters.
The following result can be achieved with the sample code below:
Select Image
Selection selection_5 = this.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection;
var image = this.radSpreadsheet.SpreadsheetElement.ActiveWorksheet.Images.First() as FloatingShapeBase;
selection_5.Select(image);
Dim selection_5 = Me.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection
Dim image = TryCast(Me.radSpreadsheet.SpreadsheetElement.ActiveWorksheet.Images.First(), FloatingShapeBase)
selection_5.Select(image)
If you would like to select the second image while deselecting the first one, this can be achieved with the following code:
Select Next Image
Selection selection_6 = this.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection;
var image2 = this.radSpreadsheet.SpreadsheetElement.ActiveWorksheet.Images.ElementAt(1) as FloatingShapeBase;
selection_6.Select(image2, true);
Dim selection_6 = Me.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection
Dim image2 = TryCast(Me.radSpreadsheet.SpreadsheetElement.ActiveWorksheet.Images.ElementAt(1), FloatingShapeBase)
selection_6.Select(image2, True)
The result will be the following:
Note that even though the clearSelection parameter is set to true, this will clear only the shape selection. The underlying cell selection will remain intact and will become visible in its previous state if the shape selection becomes empty.
The static class NameConverter provides some methods for converting cell names to indexes and vice versa. You could use the method NameConverter.ConvertCellNameToIndex which takes the cell name (e.g. "a3") and returns the row and column index of the cell.
Selecting All Cells in a Worksheet
The Selection class also exposes a SelectAll method that selects all cells in the worksheet. Keep in mind, though, that each worksheet contains over 16 000 columns and 1 000 000 rows. That said, performing a computationally intensive task on all cells may slow down the performance of RadSpreadsheet. To avoid such issues in performance crucial scenarios we highly recommend you to select only the UsedCellRange of the worksheet. This is a property of Worksheet class that returns a CellRange starting from A1 to the bottom-right cell that comprises all cells containing a value.
The following code snippet demonstrates how to use the SelectAll method and the UsedCellRange property with the Select(CellRange) method:
Selecting all or the used Cells
Selection selection_7 = this.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection;
selection_7.SelectAll();
CellRange usedRange = this.radSpreadsheet.SpreadsheetElement.ActiveWorksheet.UsedCellRange;
selection_7.Select(usedRange);
Dim selection_7 = Me.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection
selection_7.SelectAll()
Dim usedRange As CellRange = Me.radSpreadsheet.SpreadsheetElement.ActiveWorksheet.UsedCellRange
selection_7.Select(usedRange)
Selection Events
RadSpreadsheet has several selection events you can subscribe to:
SelectionUpdate: Raised when the selection is updated in any way, this may mean that a change has only begun or it has finished.
SelectionChanging: Raised when an update of the selection has begun.
SelectionChanged: Raised when the update of the selection has finished.
SelectionChangingCanceled: Raised when an update was started but subsequently canceled.
ActiveCellModeChanged: Raised when the mode of the active cell changes, from Display to Edit or vice versa.
SelectionTypeChanged: Raised when the active selection changes between cell selection and shape selection.
In some scenarios when you make a complex selection and want an event to be fired only once (at the end of this complex selection) it is convenient to use BeginUpdate() and EndUpdate() methods.
The following example shows how to make a complex selection from three parts, which will fire SelectionChanged event only once, instead of three times, thanks to the use of BeginUpdate() and EndUpdate() methods.
Selection selection = this.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection;
selection.BeginUpdate();
selection.Select(new CellRange(4, 3, 8, 5));
selection.Select(new CellRange(5, 2, 3, 3), clearSelection: false);
selection.Select(new CellIndex(2, 1), clearSelection: false);
selection.EndUpdate();
Dim selection = Me.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection
selection.BeginUpdate()
selection.Select(New CellRange(4, 3, 8, 5))
selection.Select(New CellRange(5, 2, 3, 3), clearSelection:=False)
selection.Select(New CellIndex(2, 1), clearSelection:=False)
selection.EndUpdate()
Saving and Restoring the UI Selection
With the RadSpreadsheet API you have the ability to save the current selection in a SelectionState instance and later easily restore the selection with a single method call. For such scenarios the Selection class exposes two methods: CreateSelectionState and RestoreSelectionState that save and restore the selection, respectively.
The following example makes a single CellRange selection and saves it in a SelectionState instance. After adding some new cells to the selection the old selection is restored through the RestoreSelectionState method.
Selection selection = this.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection;
selection.Select(new CellRange(4, 3, 8, 5));
SelectionState selectionState = selection.CreateSelectionState();
selection.Select(new CellRange(5, 2, 3, 3), clearSelection: false);
selection.Select(new CellIndex(2, 1), clearSelection: false);
selection.RestoreSelectionState(selectionState);
Dim selection = Me.radSpreadsheet.SpreadsheetElement.ActiveWorksheetEditor.Selection
selection.Select(New CellRange(4, 3, 8, 5))
Dim selectionState As SelectionState = selection.CreateSelectionState()
selection.Select(New CellRange(5, 2, 3, 3), clearSelection:=False)
selection.Select(New CellIndex(2, 1), clearSelection:=False)
selection.RestoreSelectionState(selectionState)
Filling the Selection with Data
The automatic filling of data in RadSpreadsheet can also be performed through the selection to ease the access to the feature. This is done by using the fill handle, which can be found at the bottom right corner of the current selection, provided that this selection consists of only one range.
More information concerning automatically filling data is available in RadSpreadProcessing's documentation that represents the model of RadSpreadsheet. Check the Repeat Values and Series articles.
In order to use the fill handle, select the initial set of data, click and hold the handle and drag it in the desired direction.
The initially selected area will remain marked while the part of selection you are dragging will be colored in Grey.
Once you've selected the desired range, you can drop the selection. The selected range will be filled with data the same way as it would be if you had used the autofill data series.
Using Selection to Complete Formulas
Selection can also be used to help entering formulas and to give visual cues for understanding these which are already there.
In order to use the selection to enter a formula, start typing it by entering the equals (=) sign. Then, use the mouse to select other cells and enter operators between them. On each click references to the cells or ranges will be entered in the formula. Just as with the regular mode of the selection, holding down Ctrl selects multiple ranges and Shift can be used to expand the current selection.
This mode of the selection can be also used when entering the arguments of a function and is available both in the cell editor and the formula bar.
If a formula is already entered and you would like to see the references used in it, enter edit mode and they will be selected automatically.
