Worksheet Management in Windows Forms Spreadsheet

10 Sep 20266 minutes to read

This section explains the operations you can perform on worksheets in a workbook using the Syncfusion WinForms Spreadsheet control.

Insert and Delete

The Spreadsheet allows you to insert and delete worksheets in a workbook.

C#

//Insert Sheet
spreadsheet.AddSheet();
	
//Insert sheet with name
spreadsheet.AddSheet("Sheet4", 3);

//Delete Sheet
spreadsheet.RemoveSheet("Sheet2");

Hide and Unhide

The Spreadsheet allows you to hide and unhide worksheets in a workbook. Hidden sheets remain in the workbook and can be displayed again using UnhideSheet.

C#

//Hide Sheet
spreadsheet.HideSheet("Sheet2");

//Unhide Sheet
spreadsheet.UnhideSheet("Sheet2");

Rename a Sheet programmatically

The Spreadsheet allows you to rename a worksheet in the workbook programmatically by using the RenameSheet method.

C#

//To Rename a sheet programmatically
spreadsheet.RenameSheet("ExistingSheetName", "NewSheetName");

Show or Hide Sheet Tabs

The Spreadsheet provides support to show and hide all the worksheet tabs in the workbook programmatically by using the ShowSheetTabs property. This property can be configured programmatically during control initialization or updated dynamically at runtime. The default value is true, which displays all worksheet tabs.

The ShowSheetTabs property controls the visibility of the sheet tab bar at the bottom of the Spreadsheet. When set to false, the entire sheet tab bar is hidden, preventing users from switching between sheets through the tab interface. You can toggle this setting based on your application requirements.

Show Sheet Tabs

To display all the worksheet tabs in the workbook, set the ShowSheetTabs property to true.

C#

//Show Sheet Tabs
spreadsheet.ShowSheetTabs = true;

Hide Sheet Tabs

To hide all the worksheet tabs in the workbook, set the ShowSheetTabs property to false.

C#

//Hide Sheet Tabs
spreadsheet.ShowSheetTabs = false;

Protection

Protecting a Worksheet

The Spreadsheet allows you to protect a worksheet with or without a password to prevent users from modifying its contents. Protection can be configured through the ExcelSheetProtection options.

The available protection options are:

• LockedCells - Allows users to select the locked cells of the protected worksheet.

• UnLockedCells - Allows users to select the unlocked cells of the protected worksheet.

• FormattingCells - Allows users to format any cell on a protected worksheet.

• FormattingRows - Allows users to format any row on a protected worksheet.

• FormattingColumns - Allows users to format any column on a protected worksheet.

• InsertingRows - Allows users to insert rows on the protected worksheet.

• InsertingColumns - Allows users to insert columns on the protected worksheet.

• InsertingHyperlinks - Allows users to insert hyperlinks on the protected worksheet.

• DeletingRows - Allows users to delete rows on the protected worksheet.

• DeletingColumns - Allows users to delete columns on the protected worksheet.

• Objects - Allows users to edit objects such as graphic cells, charts, and rich text boxes, and etc.

C#

//Protect the sheet with password
spreadsheet.ProtectSheet(spreadsheet.ActiveSheet, "123");

//Protect the sheet with Protection options
spreadsheet.ProtectSheet(spreadsheet.ActiveSheet, "123", ExcelSheetProtection.FormattingCells);

//Unprotect the sheet
spreadsheet.UnProtectSheet(spreadsheet.ActiveSheet, "123");

Protecting a Workbook

The Spreadsheet allows you to protect the structure and windows of a workbook. Protecting the structure prevents a user from adding, deleting, or displaying hidden worksheets. Protecting the windows controls the size and position of the workbook window.

C#

// To Protect the Workbook 
spreadsheet.Protect(true, true, "123");

//To Unprotect the Workbook
spreadsheet.Unprotect("123");

Gridlines

The Spreadsheet allows you to control the visibility of Gridlines in a worksheet.

C#

//To show GridLines
spreadsheet.SetGridLinesVisibility(true);

//To hide GridLines
spreadsheet.SetGridLinesVisibility(false);

Headings

The Spreadsheet allows you to show or hide the row and column headers in a worksheet.

C#

//To hide the Header cells visibility
spreadsheet.SetRowColumnHeadersVisibility(false);

Zooming

The Spreadsheet allows you to zoom in and zoom out of a worksheet view. The AllowZooming property determines whether users are allowed to change the zoom level interactively.

C#

//zoom factor
spreadsheet.SetZoomFactor("Sheet1", 200);

The Events associated with zooming are:

. ZoomFactorChanged

. ZoomFactorChanging

Events

The following table lists the events raised by the Spreadsheet during worksheet management operations.

Events	Description
WorkbookCreating	Occurs when the workbook is to be created in the Spreadsheet.
WorkbookLoaded	Occurs when the workbook is loaded in the Spreadsheet.
WorksheetAdding	Occurs when a worksheet is to be added in the Spreadsheet.
WorksheetAdded	Occurs when a worksheet is added in the Spreadsheet.
WorksheetRemoving	Occurs when a worksheet is to be removed from the Spreadsheet.
WorksheetRemoved	Occurs when a worksheet is removed from the Spreadsheet.
WorkbookUnloaded	Occurs when the workbook is unloaded or removed from the Spreadsheet.
ZoomFactorChanged	Occurs when the zoom factor in Spreadsheet is changed.
ZoomFactorChanging	Occurs when the zoom factor in Spreadsheet is to be changed.
ResizingColumns	Occurs when performing the resizing columns in Spreadsheet.
ResizingRows	Occurs when performing the resizing rows in Spreadsheet.
CellCommentOpening	Occurs when opening the comments in the cells of Spreadsheet.
CellTooltipOpening	Occurs when opening the tool tips of cells in Spreadsheet.
CellContextMenuOpening	Occurs when opening the context menu of the cell in Spreadsheet.
QueryRange	Occurs when grid queries for IRange information about a specific cell while rendering.