Interactive Features in Windows Forms Spreadsheet

9 Oct 20236 minutes to read

This section explains about the interactive operations with Spreadsheet

Clipboard Operations

Spreadsheet 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, Spreadsheet does not support the format settings (paste options).

For Cut Operation,

C#
//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,

C#
//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,

C#
//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 Spreadsheet, by using DefaultPasteOption property.

Undo or Redo

Spreadsheet 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

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

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

C#
spreadsheet.HistoryManager.Enabled = false;

To programmatically, invoke the Undo/Redo operations,

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

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

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 Spreadsheet. To disable the TabItem context menu, set the AllowTabItemContextMenu property to false.

C#
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.

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

//Custom TabItem ContextMenus

public ContextMenu CustomTabItemContextMenu()
{
    var contextMenu = new ContextMenuStrip();
    contextMenu.BackColor = Color.White;
    contextMenu.RenderMode = ToolStripRenderMode.System;
    var insertRowIcon = new Image() { Source = new BitmapImage(new Uri(@"..\..\Icon\insertRow.png", UriKind.Relative)) };
    var insertRow = new ToolStripMenuItem() { BackColor = Color.White, Text = "InsertRow" };           
    insertRow.Image = insertRowIcon;
    insertRow.Click += insertRow_Click;
    var deleteRowIcon = new Image() { Source = new BitmapImage(new Uri(@"..\..\Icon\deleteRow.png", UriKind.Relative)) };
    var deleteRow = new ToolStripMenuItem() { BackColor = Color.White, Text = "DeleteRow"};
    deleteRow.Image = deleteRowIcon;
    deleteRow.Click += deleteRow_Click;
    contextMenu.Items.Add(insertRow);
    contextMenu.Items.Add(deleteRow);
    return contextMenu;
}

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

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

C#
spreadsheet.AllowCellContextMenu = false;

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

Adding the customized menu items in the CellContextMenuOpening Event,

C#
spreadsheet.ActiveGrid.CellContextMenuOpening += ActiveGrid_CellContextMenuOpening;

void ActiveGrid_CellContextMenuOpening(object sender, CellContextMenuOpeningEventArgs e)
{
    //Adding Customized Menu item
    var PasteSpecial = new ToolStripMenuItem(){ BackColor = Color.White, Name = "PasteSpecial"};
    PasteSpecial.Text = "PasteSpecial";
    Image paste = new Image() { Source = new BitmapImage(new Uri(@"..\..\Icon\paste.png", UriKind.Relative)) };
    PasteSpecial.Image = paste;
    PasteSpecial.Click += PasteSpecial_Click;
    spreadsheet.ActiveGrid.CellContextMenu.Items.Add(PasteSpecial);
       
    //Remove the existing Context menu
    spreadsheet.ActiveGrid.CellContextMenu.Items.RemoveAt(2);
}

Cell Comments

Spreadsheet 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 Spreadsheet, set the ShowComment property of SpreadsheetGrid to true.

C#
spreadsheet.ActiveGrid.ShowComment = true;

To set the comments for particular cell at run time,

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