Working with Rows and Columns
- 10 minutes to read
A worksheet’s Table View consists of cells arranged into rows and columns with default size and style settings. Specific row and column objects are used to store custom visual and data settings. A cell object creation (which happens every time users enter data or customize a new cell’s appearance) also automatically creates respective row and column objects if they had not existed before.
On the row and column object level, the ExpressSpreadSheet control allows you to change individual row/column dimensions, and customize their appearance. Additionally, you can insert, delete, hide, display, lock, and unlock rows/columns either in groups or individually.
Refer to one of the subtopics listed below for the information on working with rows and columns within a worksheet:
Row and Column Headers;
Rows and Columns as Table View Objects;
Resizing of All Rows and Columns within a Worksheet;
Resizing of Individual Rows and Columns;
Inserting or Deleting a Row or a Column;
Hiding a Row or a Column;
Row/Column Header Context Menu;
Freezing Columns and Rows.
Row and Column Headers
Row and column headers are used to both mark and identify individual rows and columns. While the spreadsheet can be freely scrolled in both horizontal and vertical directions, the header panels remain at the left and top edges of the Table View area. An end-user can change the width/height of a column/row by dragging the boundaries of the corresponding column/row header.
By default, row and column headers use the A1 Reference Style where numbers represent rows and letters represent columns. Switching the reference style to R1C1 where both rows and columns of the worksheet are numbered is the way to customize the standard headers in addition to using skins and styles. Note that using R1C1 instead of the default A1 Reference Style also affects the references to cells and cell ranges both in formulas and defined names.
To enable the R1C1 Reference style, set the Spreadsheet’s OptionsView.R1C1Reference property to True.
To learn how to create custom column and row headers, refer to the Freezing Columns and Rows topic.
Rows and Columns as Table View Objects
Table Views consist of cells, arranged into rows and columns (i.e., horizontal and vertical table items). Rows and columns are implemented as specifically designed objects that serve as containers for cells. Thus, all appearance and style changes made to a row or column are automatically applied to all cells within it.
As with cells, new worksheets in spreadsheet applications are created without any row and column objects in the memory by default. To change properties of individual rows and columns, including size, visibility, and appearance settings, you need to create them either directly or by creating cells that belong to them. Each cell you create automatically creates both a row and a column with the respective indexes.
Resizing of All Rows or Columns within a Worksheet
For individual column and row customization, create and access rows and columns as Table View objects via the TdxSpreadSheetTableView class’ Rows and Columns properties. To resize all rows at once, use the Table View’s Options.DefaultRowHeight property, while the Options.DefaultColumnWidth property allows you to resize all columns.
The following code examples demonstrate how you can customize standard size of rows and columns within a spreadsheet.
var
ATableView: TdxSpreadSheetTableView;
//...
ATableView := dxSpreadSheet1.ActiveSheetAsTable;
ATableView.Options.DefaultColumnWidth := 140; // Assign the new default column width value
var
ATableView: TdxSpreadSheetTableView;
//...
ATableView := dxSpreadSheet1.ActiveSheetAsTable;
ATableView.Options.DefaultRowHeight := 40; // Assign the new default row height value
Resizing of Individual Rows and Columns
In addition to the capability to resize all rows and columns at once, the ExpressSpreadSheet control allows you to resize individual columns and rows within a worksheet’s Table View. To accomplish this, access individual columns and rows via the Table View’s Columns.Items and Rows.Items properties, respectively. Note that in order to access and resize these particular rows and columns, you need to create them first via the Rows.CreateItem and Columns.CreateItem methods, respectively. Then you can access these rows and columns by specifying their indexes and resize them as illustrated in the following code examples.
var
ATableView: TdxSpreadSheetTableView;
//...
ATableView := dxSpreadSheet1.ActiveSheetAsTable;
if(ATableView.Columns[1] = nil) then // If the second column within the spreadsheet does not exist
ATableView.Columns.CreateItem(1); // Create the column
ATableView.Columns[1].Size := 140; // Resize the created column
var
ATableView: TdxSpreadSheetTableView;
//...
ATableView := dxSpreadSheet1.ActiveSheetAsTable;
if(ATableView.Rows[1] = nil) then // If the second row within the spreadsheet does not exist
ATableView.Rows.CreateItem(1); // Create the row
ATableView.Rows[1].Size = 40; // Resize the created row
Inserting or Deleting a Row or a Column
The ExpressSpreadSheet control allows you to insert or delete a specified number of rows or columns at a specified position within a worksheet‘s Table View.
The following code example inserts one column of blank cells after the first column within the currently active spreadsheet. Refer to the InsertColumns method description to learn more about it.
You can delete columns in a manner, similar to inserting them, as shown in the code example below. Refer to the DeleteColumns method description for details.
Inserting or deleting rows can be done just as easily. For instance, the following code example inserts one row of blank cells following the first row within the currently active spreadsheet. Refer to the InsertRows method description for details.
To delete rows from worksheet’s Table View, use the DeleteRows method. The code example below removes two rows, following the first one within the currently active spreadsheet.
Hiding a Row or a Column
You can hide rows and columns. This is useful, for instance, when there are a number of columns or rows that contain formulas which you do not want to appear on screen.
To hide a specific column, use the Visible property of the TdxSpreadSheetTableColumn object, accessible via the Columns.Items property of a worksheet‘s Table View. If the current value of the Visible property is False, a specified column cannot be found within a worksheet’s Table View; set this property to True again to make the column reappear with all of its contents.
Note that in order to switch the column’s visibility that column should exist in memory as a column object within a Table View. Use the Columns.CreateItem to create the column before making attempts to access it.
The following code example hides the second column within the currently active worksheet.
//...
var
ATableView: TdxSpreadSheetTableView;
//...
ATableView := dxSpreadSheet1.ActiveSheetAsTable;
if(ATableView.Columns[1] = nil) then
ATableView.Columns.CreateItem(1);
ATableView.Columns[1].Visible := False;
Hiding a row is similar to hiding a column. You need to use the Visible property of the TdxSpreadSheetTableRow object, accessible via the Rows.Items property of a worksheet’s Table View. If the current value of the Visible property is False, a specified row cannot be found within a worksheet’s Table View; set this property to True again to make the column reappear with all of its contents.
As in the case of columns, make sure that the row whose visibility you are going to switch actually exists. Create the row via the Rows.CreateItem method, if required.
The code example below hides second, third, and fourth rows within the second worksheet.
//...
var
ATableView: TdxSpreadSheetTableView;
ARowCounter: Integer;
//...
ATableView := TdxSpreadSheetTableView(dxSpreadSheet1.Sheets[1]);
for ARowCounter := 1 to 3 do
begin
if(ATableView.Rows[ARowCounter] = nil) then
ATableView.Rows.CreateItem(ARowCounter);
ATableView.Rows[ARowCounter].Visible := False;
end;
Row/Column Header Context Menu
The ExpressSpreadSheet control allows an end-user to invoke a context menu specific to both row and column headers of the current worksheet‘s Table View. This context menu provides access to the following operations without using additional controls within your application:
Cut, Copy, or Paste column or row contents. Cut, Copy, and Paste context menu options emulate the Ctrl+X, Ctrl+C, and Ctrl+V key combinations, respectively;
Merge the entire row or column of cells into the single cell and split it back into the row or column of cells;
Clear rows or columns of contents;
Invoke the Format Cells Dialog for a selected row or column;
Hide/show selected rows and columns.
Note that you can insert or delete only a single selected row or column at once by using the header context menu.
Freezing Columns and Rows
The ExpressSpreadSheet allows you to create custom column and row headers for a spreadsheet. These column and row headers are formed by a number of immobilized (or “frozen”) first sequential rows and columns within a spreadsheet, respectively. The custom headers are separated from the rest of the scrollable Table View’s content area by the horizontal and vertical frozen pane separators, whose color and width can be freely customized.
There are three main options in creating custom headers for a spreadsheet:
- Freeze one or more first sequential columns at the left edge of the Table View area to create the custom row header. To accomplish this, you can call the FreezeColumns method or assign an index of the last frozen column to the FrozenColumn property. Note that the UnfreezePanes method is not the only method to make frozen columns scrollable again; calling the FreezeRows method unfreezes the previously frozen columns as well.
- Freeze one or more rows at the top edge of the Table View area. You can create the custom column header either by calling the FreezeRows method or using the FrozenRow property. Like frozen columns, frozen rows can become scrollable again through invoking either the FreezeColumns or UnfreezePanes method.
- Freeze a number of sequential columns and rows at the left and top edges of the Table View area simultaneously. This option allows you to create both custom row and column headers simultaneously either by calling the FreezePanes method or by assigning the required number of immobilized columns and rows to the FrozenColumn and FrozenRow properties, respectively.
The next step in header customization is removal of the standard headers. The combination of the workbook and spreadsheet level header availability settings determines whether the standard column and row headers are displayed within a particular spreadsheet. For instance, if you do not need standard headers for all worksheets within the ExpressSpreadSheet control, simply assign False to the OptionsView.Headers property. If you require individual header availability settings for different worksheets, you can use their Options.Headers property to override the global header availability setting. Note that unlike the standard headers, these custom headers do not allow resizing of rows and columns by dragging their respective boundaries.