Interactive Features in WPF Spreadsheet (SfSpreadsheet)

28 Jul 20216 minutes to read

This section explains about the interactive operations with SfSpreadsheet

Clipboard Operations

SfSpreadsheet provides support for all the clipboard operations to with all the format settings when copied within a workbook.

You can use the following shortcut keys for Clipboard operations like Excel

Operations Shortcut Keys
Cut Ctrl + X
Copy Ctrl + C
Paste Ctrl + V

The following are a list of paste options used while performing paste operation,

Options Description
Paste To paste with all the format options in the source range
Formula To paste the formulas alone
Keep Source Formatting To maintain the source range’s formatting
Value To paste the values alone
Format To paste only the formats alone without pasting the values.
Value & Source Formatting To maintain the source range original format and paste only values

NOTE

When the content is copied from external source, SfSpreadsheet does not support the format settings (paste options).

For Cut Operation,

//To perform cut operation for selected ranges
var range = spreadsheet.ActiveGrid.SelectedRanges.ActiveRange;
spreadsheet.ActiveGrid.CopyPaste.Copy(range, true);

//To perform cut operation
spreadsheet.ActiveGrid.CopyPaste.Cut();

For Copy Operation,

//To perform copy operation for selected ranges
var range = spreadsheet.ActiveGrid.SelectedRanges.ActiveRange;
spreadsheet.ActiveGrid.CopyPaste.Copy(range, false);

//To perform Copy operation
spreadsheet.ActiveGrid.CopyPaste.Copy();

For Paste Operation,

//To perform paste operation
spreadsheet.ActiveGrid.CopyPaste.Paste();

//To perform paste operation with range and Paste Options
var copyPaste = spreadsheet.ActiveGrid.CopyPaste as SpreadsheetCopyPaste;
copyPaste.Paste(range);
copyPaste.Paste(range, PasteOptions.Paste);

TIPS

Users can also set their default PasteOptions while pasting in SfSpreadsheet, by using DefaultPasteOption property.

Undo/Redo

SfSpreadsheet provides support for Undo/Redo functionality like Microsoft Excel.

The shortcut keys used for Undo/Redo Operations

Operations Shortcut Keys
Undo Ctrl + Z
Redo Ctrl + Y

SfSpreadsheet has History Manager class that supports the implementation of undo/ redo operations

By default, Undo/Redo operations in SfSpreadsheet is enabled. To disable the Undo/Redo operations, set the Enabled property of History Manager to be false.

spreadsheet.HistoryManager.Enabled = false;

To programmatically, invoke the Undo/Redo operations,

spreadsheet.HistoryManager.Enabled = true;
spreadsheet.HistoryManager.Undo();
spreadsheet.HistoryManager.Redo();

Context menu

Context menu in SfSpreadsheet is customizable menu which can be used for various functionalities

TabItem Context menu

TabItem Context menu opens when the user right-click on the sheet tab and contains the menus related to worksheet operations.

By default, TabItem Context menu is enabled in SfSpreadsheet. To disable the TabItem context menu, set the AllowTabItemContextMenu property to false.

spreadsheet.AllowTabItemContextMenu = false;

Default TabItem context menu has options like Insert, Delete, Hide/Unhide and Protect sheet. You can also customize the TabItem Context menu by setting IsCustomTabItemContextMenuEnabled property to be true and you can add your customized menu items.

spreadsheet.IsCustomTabItemContextMenuEnabled = true;
spreadsheet.TabItemContextMenu = CustomTabItemContextMenu();

//Custom TabItem ContextMenus

public ContextMenu CustomTabItemContextMenu()
{
    var contextMenu = new ContextMenu();
    var insertRowIcon = new Image() { Source = new BitmapImage(new Uri(@"..\..\Icon\insertRow.png", UriKind.Relative)) };
    var insertRow = new MenuItem() { Header = "InsertRow" };           
    insertRow.Icon = insertRowIcon;
    insertRow.Click += insertRow_Click;

    var deleteRowIcon = new Image() { Source = new BitmapImage(new Uri(@"..\..\Icon\deleteRow.png", UriKind.Relative)) };
    var deleteRow = new MenuItem() { Header = "DeleteRow"};
    deleteRow.Icon = deleteRowIcon;
    deleteRow.Click += deleteRow_Click;
    
    contextMenu.Items.Add(insertRow);
    contextMenu.Items.Add(deleteRow);
    return contextMenu;
 }

Cell Context menu

Cell Context menu opens when the user right-click on a worksheet cell or selection of cells in SfSpreadsheet.

By default, Cell Context menu is enabled in SfSpreadsheet. To disable the Cell Context menu, set the AllowCellContextMenu property as false.

spreadsheet.AllowCellContextMenu = false;

Users can also customize the Cell Context menu of SfSpreadsheet by using CellContextMenuOpening Event of SpreadsheetGrid.

Adding the customized menu items in the CellContextMenuOpening Event,

spreadsheet.ActiveGrid.CellContextMenuOpening += ActiveGrid_CellContextMenuOpening;

void ActiveGrid_CellContextMenuOpening(object sender, CellContextMenuOpeningEventArgs e)
{
    //Adding Customized Menu item
    MenuItem PasteSpecial = new MenuItem();
    PasteSpecial.Header = "Pastespecial";
    Image paste = new Image() { Source = new BitmapImage(new Uri(@"..\..\Icon\paste.png", UriKind.Relative)) };
    PasteSpecial.Icon = paste;
    spreadsheet.ActiveGrid.CellContextMenu.Items.Add(PasteSpecial);
        
    //Remove the existing Context menu
    spreadsheet.ActiveGrid.CellContextMenu.Items.RemoveAt(2);
}

TIPS

Custom Cell Context menu can also by added by assigning the customized menu items to the CellContextMenu property of SpreadsheetGrid. For your reference, CustomContextMenu

Cell Comments

SfSpreadsheet provides support for cell comments like in excel to give the reader additional context for the data it contains. You can set the comment height and color for the particular comments at runtime by invoking CellCommentOpening Event of SpreadsheetGrid

To enable the comment in SfSpreadsheet, set the ShowComment property of SpreadsheetGrid to true.

spreadsheet.ActiveGrid.ShowComment = true;

To set the comments for particular cell at run time,

spreadsheet.ActiveSheet.Range["E5"].AddComment().Text = "Sample Comment";
spreadsheet.ActiveGrid.InvalidateCell(5, 5);

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.