Worksheet

• Jan 23, 2019
• 5 minutes to read

A worksheet (Worksheet) represents a single document within a spreadsheet workbook. A worksheet is used to store and manipulate data in the SpreadsheetControl. By default, the spreadsheet contains only one worksheet with the default name displayed on the Sheet Tab Selector at the bottom of the control. You can customize the tab appearance for each worksheet using the WorksheetView.TabColor property, which allows you to specify the worksheet tab color. You can also control the visibility of the Sheet Tab Selector, as well as the vertical and horizontal scrollbars, from the SpreadsheetControl.Options property, which provides access to various properties of the SpreadsheetControl such as SpreadsheetControlOptions.TabSelector, SpreadsheetControlOptions.VerticalScrollbar and SpreadsheetControlOptions.HorizontalScrollbar.

End-User Capabilities

To provide an interface that allows end-users to easily work with the spreadsheet documents, add a set of Ribbon pages to the SpreadsheetControl. For an example on how to provide a Ribbon UI for the SpreadsheetControl, see the Getting Started topic.

The SpreadsheetControl also supports keyboard shortcuts for performing operations in worksheets. For details, refer to the Shortcuts section.

The table below lists the basic end-user operations for working with worksheets via Ribbon pages (or the spreadsheet context menu), and the corresponding API methods and properties.

• Insert a new worksheet
• Delete a worksheet
• Rename a worksheet
• Hide or display a worksheet
• Specify print options
• Scroll a worksheet
• Zoom a worksheet

 

Action	End-user capabilities	API methods/properties
Insert a new worksheet	Click the New Sheet button at the right edge of the Sheet tab bar. In the Cells group within the Home tab, click the Insert button and select the Insert Sheet item. Right-click the sheet tab of the existing worksheet and select the Insert item from the context menu.	WorksheetCollection.Add WorksheetCollection.Insert How to: Add a New Worksheet
Delete a worksheet	In the Cells group within the Home tab, click the Delete button and select the Delete Sheet item. Right-click the sheet tab of the worksheet to be deleted, and then select the Delete item in the context menu.	WorksheetCollection.Remove WorksheetCollection.RemoveAt How to: Delete a Worksheet
Rename a worksheet	Double-click the sheet tab of the worksheet in the Sheet Tab Selector, to invoke the Rename Sheet dialog box. Right-click the sheet tab of the worksheet to be renamed, and select the Rename item from the context menu.	Worksheet.Name How to: Rename a Worksheet
Hide or display a worksheet	In the Cells group of the Home tab, click the Format button and select: Hide & Unhide | Hide Sheet to hide the current worksheet. (Note that this command is disabled for end-users when a workbook includes only one worksheet.) Unhide Sheet… to display a hidden worksheet. Right-click the sheet tab of the worksheet to be hidden and select the Hide item from the context menu. Right-click any visible sheet tab, and select the Unhide item from the context menu to display the hidden worksheet. Note that end-users can hide multiple worksheets at once, but they can display only one worksheet at a time.	Worksheet.Visible Worksheet.VisibilityType How to: Show and Hide a Worksheet
Specify print options	Set Page Margins In the Page Setup group within the Page Layout tab, click the Margins button and select the margin sizes. Set Page Orientation In the Page Setup group, click the Orientation button and select Portrait or Landscape. Change Paper Size In the Page Setup group, click the Size button and select the required paper size.	WorksheetView.Margins WorksheetView.Orientation WorksheetView.PaperKind How to: Specify Print Settings
Scroll a worksheet	Use vertical and horizontal scrollbars to scroll a worksheet to the desired location.	Worksheet.ScrollTo Worksheet.ScrollToRow Worksheet.ScrollToColumn
Zoom a worksheet	In the Zoom group within the View tab, click the Zoom In or Zoom Out button. Click the Zoom 100% button to restore the worksheet to normal size.	WorksheetView.Zoom How to: Zoom In and Out of a Worksheet

End-User Restrictions

In some real-world applications, it might be necessary to prevent end-users from modifying worksheets. For instance, it may be necessary to prohibit end-users from deleting or renaming worksheets. To implement end-user restrictions, use the SpreadsheetControlOptions.Behavior property, which provides access to the restriction settings. The table below lists the restrictions you can set when working with worksheets in the SpreadsheetControl.

Restriction	Description
SpreadsheetWorksheetBehaviorOptions.Delete	Gets or sets whether or not end-users are allowed to delete worksheets.
SpreadsheetWorksheetBehaviorOptions.Hide	Gets or sets whether or not end-users are allowed to hide worksheets.
SpreadsheetWorksheetBehaviorOptions.Insert	Gets or sets whether or not end-users are allowed to insert worksheets.
SpreadsheetWorksheetBehaviorOptions.Rename	Gets or sets whether or not end-users are allowed to rename worksheets.
SpreadsheetWorksheetBehaviorOptions.Unhide	Gets or sets whether or not end-users are allowed to unhide worksheets.
SpreadsheetBehaviorOptions.OfficeScrolling	Gets or sets whether or not end-users are allowed to use the Office Scrolling feature.
SpreadsheetBehaviorOptions.Zooming	Gets or sets whether or not end-users are allowed to zoom in and out of documents.
SpreadsheetBehaviorOptions.MaxZoomFactor	Gets or sets the upper limit of document zooming.
SpreadsheetBehaviorOptions.MinZoomFactor	Gets or sets the lower limit of document zooming.

Set the required property to DocumentCapability.Disabled or DocumentCapability.Hidden to disable or hide the corresponding command in the Ribbon UI and the spreadsheet popup menu. You can also prevent the context menu from being displayed, as described in the How to: Customize or Hide the Popup Menu example.