Worksheet Management in UWP Spreadsheet (SfSpreadsheet)

23 Jul 20264 minutes to read

This section explains the operations that can be performed on a worksheet.

Insert and Delete

SfSpreadsheet supports inserting and deleting worksheets in a workbook.

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

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

Hide and Unhide

SfSpreadsheet supports hiding and unhiding worksheets in a workbook.

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

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

Rename a Sheet Programmatically

SfSpreadsheet provides support 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

SfSpreadsheet supports protecting the worksheet with or without a password. This helps to prevent a user from modifying the contents of the worksheet. The protection of a worksheet can also be done with ExcelSheetProtection options.

The Protect sheet options are

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

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

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

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

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

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

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

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

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

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

  • Objects - Allows the users to edit the objects such as Graphic cells like charts, rich text box, and so on.

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

SfSpreadsheet supports protecting the structure and windows of a workbook. Protecting the structure prevents a user from adding or deleting worksheets, or from displaying hidden worksheets. Protecting the windows in the workbook allows you control the size and position of the workbook.

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

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

Gridlines

SfSpreadsheet supports controlling the visibility and color of the Gridlines in a worksheet.

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

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

Headings

SfSpreadsheet supports controlling the visibility of row and column headers in a worksheet.

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

Zooming

SfSpreadsheet supports zooming in and out of a worksheet view. The property AllowZooming determines whether zooming is allowed.

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

The Events associated with Zooming are:

  • ZoomFactorChanged
  • ZoomFactorChanging

Events

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