Cells and Cell Objects
- 7 minutes to read
Cell Data Storage Concept
Each Table View worksheet in the Spreadsheet or Report Designer control is visually divided into individual cells arranged into rows and columns. Worksheets store the information on each customized cell, including its value, formatting, style settings, and/or formula expression as a separate TdxSpreadSheetCell object. Unused empty cells do not exist in the memory when a Spreadsheet/Report Designer control-based application is initialized. Call the worksheet’s CreateCell function to create a new cell object. The controls also automatically create a cell object when an end-user enters a value or changes the default cell formatting.
Use the control’s DefaultCellStyle property to customize the cell style applied to uninitialized cells and initialized cells with unchanged style settings and formatting. Deleting a cell object restores the default cell style for the corresponding cell in a worksheet.
For more information regarding cells and cell objects, refer to one of the following sections:
Accessing Cells;
Cell References;
Assigning Cell Values;
Selecting Cells;
Hide/Display Cells;
Formatting Cells.
Accessing Cells
The ExpressSpreadSheet Suite‘s public API provides two ways to access a cell object:
Using either the worksheet‘s or row/column object‘s Cells property. If the cell object corresponding to the specified property index(es) does not exist, the property returns nil.
Calling either the worksheet‘s, row object‘s, or column object‘s CreateCell function.
You can use the returned cell object to:
Enter a value or formula expression into the corresponding cell;
Customize cell formatting;
Hide or display the cell;
You can use the cell object’s Row and Column properties to access the row and column objects corresponding to the row and column to which the cell belongs.
If you no longer need a particular cell object, delete it by calling the worksheet’s DeleteCells procedure to prevent memory leakage. Call the DeleteAllCells procedure to remove all cell objects in the worksheet.
Note that destroying row and column objects also results in destroying their cell objects.
Cell References
Each cell in a Table View worksheet has its own unique position and address that you can use for referring in other cells, formula expressions, and defined names. The Spreadsheet and Report Designer controls support two cell reference styles and two cell reference types:
Reference Type | Description | A1 Reference Example | R1C1 Reference Example |
|---|---|---|---|
Absolute Reference | Both the column and row numbers (designations) remain unchanged when you copy or move an expression containing the reference to another cell. | =$A$2 | =R2C1 |
Partially Relative Reference | The row number remains the same when you copy or move an expression containing the reference to another cell. The absolute horizontal offset (in cells) between the source and destination cells is added to the column number in the reference. The column letter designation undergoes the corresponding change in the case of the A1 cell reference type. | =A$2 | =R[2]C1 |
The column number remains the same when you copy or move an expression containing the reference to another cell. The absolute vertical offset (in cells) between the source and destination cells is added to the row number in the reference. | =$A2 | =R2C[1] | |
Relative Reference | Both the horizontal and vertical absolute offsets (in cells) between the source and destination cells are added to the column and row numbers in the copied reference, respectively. The column letter designation undergoes the corresponding change in the case of the A1 cell reference type. | =A2 | =R[2]C[1] |
Assigning Cell Values
The Spreadsheet and Report Designer controls allow you to enter cell values either programmatically or by using an in-place cell editor.
To enter a Boolean, numeric value, or an unformatted Unicode string into a cell programmatically, access its cell object and use one of its members, depending on the required data format:
To assign a formula expression to a cell object, you can:
Call its SetText procedure passing True as the AFormulaChecking parameter;
Assign a copy of the required formula object to the AsFormula property.
The Spreadsheet and Report Designer controls also provide the capability to work with formatted cell content if the ExpressRichEditControl Suite is installed and the ExpressSpreadSheet In-place RichEdit Support by Developer Express Inc. design time package is enabled in your application project. Call the dxSpreadSheetTextService.SetAsRTF function passing a cell object corresponding to the destination cell and an RTF-formatted string as parameters. Refer to the How to Work with Rich Formatted Content topic for detailed information on working with the formatted text-related API and end-user cell content formatting capabilities.
The focused cell in the active worksheet is the destination for text typing. Pressing a key corresponding to a character invokes an in-place cell editor if the Spreadsheet or Report Designer controls has focus and none of its cells are being edited. The character entered into an in-place editor replaces the current cell’s content if an end-user terminates cell editing and confirms the changes by:
Clicking any other cell within the active worksheet;
Pressing the Ctrl+Shift+Enter key combination;
Pressing the Enter key or the Shift+Enter key combination;
Double-click a cell to invoke an in-place editor containing the previous cell value.
Press the Esc key to discard any cell value changes made using an in-place cell editor.
The full range of in-place editor-related options available to end-users is described in the End-User Cell Editing Options topic.
The content entered into a cell is parsed automatically to create the respective formatting templates or formula expressions (if the entered string starts with the equals (=) sign).
Selecting Cells
Cell selection can range from a single focused cell to one or more selected cell ranges. Each individual selected cell or cell range corresponds to a single item in the active worksheet’s collection of cell selections:
Call the worksheet’s Selection.SelectCell and Selection.Add methods to select a single cell or cell range programmatically. Call the Selection.SelectColumns and Selection.SelectRows procedures to select specific columns and rows, respectively. Pass the [ssCtrl] value as the cell selection method’s optional AShift parameter to retain the current cell selection.
Call the Selection.SelectAll procedure to select all cells at once. When the cell selections are no longer needed, call the Selection.Clear procedure.
Call the worksheet’s Selection.FocusedCell, Selection.FocusedColumn, Selection.FocusedRow, and Selection.FocusedContainer to move focus between cells, columns, rows, and floating containers (including comments). Note that you cannot select more than one floating container at a time.
The end-user selection management functionality includes the capability to:
Move the cell focus left, right, up, and down by pressing the respective Arrow keys.
Move the cell focus left, right, up, and down by pressing Shift+Tab Tab Shift+Enter and Enter If the active worksheet is protected, the Spreadsheet control can skip locked cells, depending on the OptionsBehavior.EnterKeyNavigation property value.
Select a cell range by dragging the mouse to the cell that designates the opposite corner of the selection area. Alternatively, an end-user can select an arbitrary cell range by pressing the arrow keys while holding down the Shift key.
Select multiple individual cells by clicking them while holding down the Ctrl key.
Select the range of all occupied cells by pressing the Ctrl+A or Ctrl+Shift+Spacebar key combination. The consecutive use of the same key combination selects all cells within the active worksheet.
Select an entire row or column to which the focused cell belongs by pressing the Shift+Spacebar or Ctrl+Spacebar key combination, respectively. Alternatively, clicking row and column headers selects the respective rows and columns;
Select the first cell within the current row by pressing the Home key.
Use any combination of available selection techniques to select either non-adjacent individual cells or cell ranges.
Hiding/Displaying Cells
To hide all cells in the column or row to which a cell object belongs, set its Column.Visible or Row.Visible property to False. End-users can hide and display previously hidden rows and columns by using the row/column context menu:
Formatting Cells
Cell formatting settings are available via a cell object‘s Style property. Use it to customize the appearance of a cell and its content, including such parameters as font, border style, fill pattern, text alignment, etc. The full range of available cell formatting options is listed in the TdxSpreadSheetCellStyle class description.
An end-user can assign custom cell formatting by using the Format Cells Dialog available from the cell context menu
For detailed information on cell insert, delete, merge, split, clear, and sort operations, refer to the Cell Management topic.