Worksheet Management in Windows Forms Spreadsheet

23 Jul 20265 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.

//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.

//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.

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

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.

//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.

// 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.

//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.

//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.

//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.