Worksheet Management in WPF Spreadsheet (SfSpreadsheet)

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

C#

//Insert a new sheet at the end of the workbook
spreadsheet.AddSheet();

//Insert a sheet with a name at a specific index
spreadsheet.AddSheet("Sheet4", 3);

//Delete a sheet by name
spreadsheet.RemoveSheet("Sheet2");

Hide and Unhide worksheets

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

C#

//Hide a sheet by name
spreadsheet.HideSheet("Sheet 2");

//Unhide a sheet by name
spreadsheet.UnhideSheet("Sheet 2");

Hide or unhide sheet tabs

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

C#

//Wire up the Loaded event in the XAML page constructor or code-behind
spreadsheetControl.Loaded += SpreadsheetControl_Loaded;

private void SpreadsheetControl_Loaded(object sender, RoutedEventArgs e)
{
    spreadsheetControl.ShowSheetTabs = false;
}

XAML

<syncfusion:SfSpreadsheet x:Name="spreadsheetControl" ShowSheetTabs="False" />

Rename a worksheet

SfSpreadsheet provides support to rename a worksheet in the workbook by using the RenameSheet method. After invoking this method, the sheet tab enters editing mode so the user can change the name directly in the tab.

C#

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

Rename a worksheet programmatically

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

C#

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

Worksheet Protection

Protecting a worksheet

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

The ExcelSheetProtection options are:

• LockedCells - Allows the user to select the locked cells of the protected worksheet.
• UnLockedCells - Allows the user to select the unlocked cells of the protected worksheet.
• FormattingCells - Allows the user to format any cell on a protected worksheet.
• FormattingRows - Allows the user to format any row on a protected worksheet.
• FormattingColumns - Allows the user to format any column on a protected worksheet.
• InsertingRows - Allows the user to insert rows on the protected worksheet.
• InsertingColumns - Allows the user to insert columns on the protected worksheet.
• InsertingHyperlinks - Allows the user to insert hyperlinks on the protected worksheet.
• DeletingRows - Allows the user to delete rows on the protected worksheet.
• DeletingColumns - Allows the user to delete columns on the protected worksheet.
• Objects - Allows the user to edit objects such as graphics, charts, rich textboxes, 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

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

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

C#

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

C#

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

Zooming

SfSpreadsheet provides support to zoom in and zoom out of a worksheet view. The AllowZooming property determines whether zooming is allowed.

C#

//Set the zoom factor to 200% on Sheet1
spreadsheet.SetZoomFactor("Sheet1", 200);

The events associated with zooming are:

• ZoomFactorChanged — occurs after the zoom factor changes.
• ZoomFactorChanging — occurs before the zoom factor changes.

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 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 in SfSpreadsheet.
CellTooltipOpening	Occurs when opening the tool tips of cells in SfSpreadsheet.
CellContextMenuOpening	Occurs when opening the context menu of a cell in SfSpreadsheet.
QueryRange	Occurs when the grid queries for IRange information about a specific cell while rendering.

NOTE

You can refer to our WPF Spreadsheet Editor 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.