vcl-168056-expressspreadsheet-concepts-working-with-rows-and-columns.md
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 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.
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.
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
TdxSpreadSheetTableView *ATableView;
//...
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
TdxSpreadSheetTableView *ATableView;
//...
ATableView =dxSpreadSheet1->ActiveSheetAsTable;
ATableView->Options->DefaultRowHeight = 40; // Assign the new default row height value
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
TdxSpreadSheetTableView *ATableView;
//...
ATableView = dxSpreadSheet1->ActiveSheetAsTable;
if(ATableView->Columns->Items[1] == NULL) // If the second column within the spreadsheet does not exist
{
ATableView->Columns->CreateItem(1); // Create the column
}
ATableView->Columns->Items[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
TdxSpreadSheetTableView *ATableView;
//...
ATableView = dxSpreadSheet1->ActiveSheetAsTable;
if(ATableView->Rows->Items[1] == NULL) // If the second row within the spreadsheet does not exist
{
ATableView->Rows->CreateItem(1); // Create the row
}
ATableView->Rows->Items[1]->Size = 40; // Resize the created row
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.
dxSpreadSheet1.ActiveSheetAsTable.InsertColumns(1, 1);
dxSpreadSheet1->ActiveSheetAsTable->InsertColumns(1, 1);
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.
dxSpreadSheet1.ActiveSheetAsTable.DeleteColumns(1, 2);
dxSpreadSheet1->ActiveSheetAsTable->DeleteColumns(1, 2);
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.
dxSpreadSheet1.ActiveSheetAsTable.InsertRows(1, 1);
dxSpreadSheet1->ActiveSheetAsTable->InsertRows(1, 1);
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.
dxSpreadSheet1.ActiveSheetAsTable.DeleteRows(1, 2);
dxSpreadSheet1->ActiveSheetAsTable->DeleteRows(1, 2);
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;
//...
TdxSpreadSheetTableView *ATableView;
//...
ATableView = dxSpreadSheet1->ActiveSheetAsTable;
if(ATableView->Columns->Items[1] == NULL)
{
ATableView->Columns->CreateItem(1);
}
ATableView->Columns->Items[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;
//...
TdxSpreadSheetTableView *ATableView;
//...
ATableView = static_cast<TdxSpreadSheetTableView*>(dxSpreadSheet1->Sheets[1]);
for(int ARowCounter = 1; ARowCounter < 4; ARowCounter++)
{
if(ATableView->Rows->Items[ARowCounter] == NULL)
{
ATableView->Rows->CreateItem(ARowCounter);
}
ATableView->Rows->Items[ARowCounter]->Visible = false;
}
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.
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:
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.