Worksheet Management
- 6 minutes to read
A spreadsheet document, (i.e., a workbook, imported from a supported file or created by the Spreadsheet control component) contains at least a single sheet used to list, format, and analyze data. In addition to a comprehensive worksheet management API, the ExpressSpreadSheet control provides multiple built-in UI elements that allow end-users to perform the following operations with sheets:
Add and Delete
Switch Between Sheets
Hide and Display
Rename
Rearrange the Sheet Order
Add and Delete Sheets
To add a new sheet to the current document programmatically, you can call the AddSheet function provided by the Spreadsheet control. By default, the newly created worksheet has an automatically generated name that consists of “Sheet” string followed by a number. However, you can pass a meaningful name as the function’s ACaption parameter or rename the sheet later.
The following code example inserts two new sheets into the current document. The first sheet has the explicitly assigned “Added Page” name while the second sheet receives the automatically generated name (“Sheet5”):
If the current spreadsheet document is not protected, an end-user can create a new sheet with an automatically generated name by using one of the following options:
Click the New Worksheet button on the caption bar.
Click the Insert item in the caption bar’s context menu.
Press the Shift+F11 key combination.
Execute the InsertSheet command, provided that it is linked to one of UI elements in your spreadsheet-based application.
To delete a specific worksheet, you can invoke the sheet’s Free method:
If the current spreadsheet document is not protected, an end-user can delete sheets by using one of the following options:
Click the Delete item in the caption bar’s context menu;
Execute the DeleteSheet command, provided that it is linked to one of the UI elements in your spreadsheet-based application.
Note that end-users cannot delete sheets in documents that have only one visible sheet. However, this restriction does not apply to calling the sheet’s Free method.
To track the total number of visible sheets in the document, you can use the Spreadsheet control’s VisibleSheetCount property while the total sheet count is returned by the SheetCount property.
Switch Between Sheets
Since the Spreadsheet control component can display only one sheet at a time, you can switch between sheets programmatically by using:
- The ActiveSheetIndex property provided by the control. The following code example demonstrates how to use this property to navigate forward through all sheets. When the last worksheet is reached, the first sheet becomes active:
if(dxSpreadSheet1.ActiveSheetIndex < dxSpreadSheet1.SheetCount - 1) then
dxSpreadSheet1.ActiveSheetIndex := dxSpreadSheet1.ActiveSheetIndex + 1
else
dxSpreadSheet1.ActiveSheetIndex := 0;
- The ActiveSheet property provided by the control. To make a sheet object active, you can assign it to this property:
- The Active property provided by individual sheets. To activate a sheet, simply set this property to True:
Even without the use of additional UI elements in your spreadsheet-based application, an end-user is able to:
Navigate between sheets by clicking their tab buttons on the caption bar;
Scroll the tab buttons list (if there are too many tabs to display simultaneously) by clicking the First, Last, Next, and Prev buttons.
Hide and Display Sheets
You can hide a specific worksheet within the current spreadsheet document and then display the hidden sheet again if required. To accomplish this, use the sheet’s Visible property.
Set the Visible property to False to hide a worksheet, making it inaccessible by using the caption bar.
The following line of code hides the second worksheet.
If the Visible property is set to True, the sheet becomes visible. The respective sheet tab reappears within the caption bar at the bottom of the Spreadsheet control.
Use the following code line to display the previously hidden worksheet.
To hide or show a sheet within a spreadsheet document, an end-user can use the caption bar’s context menu. To hide a specific worksheet, an end-user can right-click the tab button corresponding to the required sheet, and then click the Hide menu item that is available as long as the document has at least two visible sheets.
To show previously hidden worksheets, an end-user can click the Unhide menu item in the caption bar context menu, which becomes active if the current workbook has at least a single hidden worksheet. This menu item can be used to invoke the Unhide dialog, which allows an end-user to pick a single sheet from the list of hidden worksheets and make it visible by clicking the OK button.
Rename Sheets
You can rename worksheets by using the worksheet’s Caption property. The TdxSpreadSheetCustomView object corresponds to a particular worksheet returned by the Spreadsheet control‘s Sheets property.
The following code example shows how to rename a worksheet programmatically:
All worksheet names within the same spreadsheet document must be unique. Therefore, if the new name assigned to the TdxSpreadSheetCustomView.Caption property matches the name of any other sheet, the EdxSpreadSheetError exception is raised:
An end-user can rename any existing worksheets by using the Rename menu item in the caption bar context menu. Picking this item invokes the Rename Sheet dialog that allows an end-user to type a new name for a currently selected sheet and confirm it by clicking the OK button.
The length of a worksheet’s new name cannot exceed the number of characters determined by the dxSpreadSheetMaxCaptionLength global constant. The Spreadsheet control automatically truncates “exceeding” tailing characters. If the resulting truncated name matches one of the existing sheet captions, the control replaces one tailing character (or more, if required) with numbers. For instance, if the truncated matching name ends with “Sheet” the last character is replaced with “1”, resulting in “Shee1”. Then, if the resulting name matches an already existing name, the tailing “1” character is replaced with “2”, and so on.
Rearrange the Sheet Order
The order of worksheet tab buttons within the caption bar is determined by sheet indexes. To move the sheet tab right or left, you can increase or decrease its Index property value, respectively.
An end-user can drag-and-drop worksheet tabs to rearrange their order within the caption bar. For instance, the following image shows that the Sheet5 tab is about to be placed between the Sheet2 and Sheet3 tabs.
