Worksheet Management in WPF Spreadsheet (SfSpreadsheet)

26 Feb 20245 minutes to read

This section explains about the operations that are performed with the worksheet.

Insert and Delete worksheet

SfSpreadsheet provides support to insert and delete the worksheets in a workbook.

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

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

Hide and Unhide worksheets

SfSpreadsheet provides support to hide and unhide the worksheets in a workbook.

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

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

Hide or unhide sheet tabs

Spreadsheet provides support to hide and unhide the all worksheet tabs in the workbook using the ShowSheetTabs property. The Default value is true.

private void SpreadsheetControl_Loaded(object sender, RoutedEventArgs e)
{
    spreadsheetControl.ShowSheetTabs = false;
}
<syncfusion:SfSpreadsheet x:Name="spreadsheetControl" ShowSheetTabs ="False" />

Rename a worksheet

SfSpreadsheet provides support to rename a worksheet in the workbook by using RenameSheet method. After invoking this method, the sheet tab enters into editing mode and now the users can change the name of the sheet in the tab.

//Rename sheet
spreadsheet.RenameSheet("Sheet1");

Rename a worksheet programmatically

SfSpreadsheet provides support to rename a worksheet in the workbook programmatically by using RenameSheet method.

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

Worksheet Protection

Protecting a worksheet

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

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 textbox, 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

SfSpreadsheet provides support to protect the structure and windows of a workbook. By protecting the structure, prevent a user from adding or deleting worksheets or from displaying hidden worksheets. By protecting the windows in the workbook, you can control the size of the workbook, etc.

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

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

Gridlines

SfSpreadsheet provides support to control the visibility and color of the Gridlines in a worksheet.

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

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

Headings

SfSpreadsheet provides support to control the visibility of row and column headers in a worksheet

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

Zooming

SfSpreadsheet provides support to zoom in and zoom out of a worksheet view. The property AllowZooming determines whether to allow zooming or not.

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

The Events associated with the Zooming are

. ZoomFactorChanged

. ZoomFactorChanging

Events

Events Description

WorkbookCreating

Occurs when the workbook is to be created in SfSpreadsheet.

WorkbookLoaded

Occur when the workbook is loaded in SfSpreadsheet.

WorksheetAdding

Occurs when the worksheet is to be added in SfSpreadsheet.

WorksheetAdded

Occurs when the worksheet is added in SfSpreadsheet.

WorksheetRemoving

Occurs when the worksheet is to be removed from SfSpreadsheet.

WorksheetRemoved

Occurs when the worksheet is removed from SfSpreadsheet.

WorkbookUnloaded

Occurs when the workbook is unloaded or removed 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 grid queries for IRange information about a specific cell while rendering.

NOTE

You can refer to our WPF Spreadsheet feature tour page for its groundbreaking feature representations. You can also explore our WPF Spreadsheet example to know how to render and configure the spreadsheet.