Clipboard Operations

• Mar 26, 2024
• 8 minutes to read

The Spreadsheet and Report Designer controls provide a wide range of options for moving or copying cell data between worksheets and/or external applications via the clipboard in multiple formats by using:

• Public API Methods

• Key Combinations and Context Menus

• Clipboard-Related Commands

• Paste Special Options

The Spreadsheet/Report Designer controls support the copy and cut operations only with a single selection in a worksheet. An attempt to copy or move multiple cell ranges by using any technique supported by the controls raises an EdxSpreadSheetError exception. Performing a copy or cut operation in a worksheet that has no selected content also results in the same error.

Supported Clipboard Data Formats

A cell or cell range cut or copied from a worksheet, is present in the clipboard in the following data formats:

Clipboard Data Format	Description	Example
ExpressSpreadSheet internal binary	The data format, native to the Spreadsheet and Report Designer controls, is designed for moving or copying the complete cell data structure (including values, formatting, associated comment containers, references, and formula expressions) or a floating container between worksheets.
SpreadsheetML (XML SpreadSheet)	The data format, native to modern Microsoft Excel® versions, stores the same data as the ExpressSpreadSheet binary format, except for custom floating containers (including shapes, pictures, and text boxes).
HTML	Stores a formatted cell range, ready for pasting into a rich text document opened by a word processor application (including your Rich Edit control-based projects). The format does not store any floating containers.
H
Plain text (TXT)	Stores only the strings of cell values delimited by tab characters, without any concomitant data.

Pasting the clipboard content into the Spreadsheet or Report Designer control produces different effects depending on:

• The clipboard data format that depends on the initial data source (an external application or the control itself);

• The clipboard content type (that is, a cell, cell range, or floating container);

• The current selection in the active worksheet (that is, a cell, cell range, multiple cell/cell range selections, floating container).

A cell or cell range pasted from Microsoft Excel (in SpreadsheetML format) or ExpressSpreadSheet-based application (in ExpressSpreadSheet binary format) includes values, formatting, formula expressions, and associated comment containers. You can limit the pasted cell data by specifying the Paste Special settings.

Public API Methods

The Spreadsheet/Report Designer control’s public API provides two groups of procedures for moving/copying the content between worksheets and the clipboard:

• Procedures that populate the clipboard with cell data or a floating container in all supported data formats (CopyToClipboard and CutToClipboard);

• The PasteFromClipboard procedure that copies the clipboard’s content to the active worksheet.

Call the worksheet’s CopyToClipboard or CutToClipboard procedure to copy or move the current cell selection to the clipboard, respectively. Likewise, you can call the CopyToClipboard and CutToClipboard procedures to copy or cut the selected floating container (including comments).

The PasteFromClipboard procedure inserts the clipboard’s content to the active worksheet. Calling this procedure produces different effects if the clipboard contains:

• Cell data in the SpreadsheetML format. The procedure inserts the copied/cut cell range including all formatting settings, anchored comments, expressions, and references. In this case, you can use the full range of available paste special options.

• Cell data in any other format. The procedure applies the destination cell styles to the pasted cell range.

• Plain text. The procedure inserts a text string to the last selected range’s upper-left cell, applying the current cell formatting to its new content.

• RTF-formatted text. The procedure works similarly to the plain text case, however, if the destination cell already contains RTF-formatted text, the pasted RTF string retains its formatting.

• A floating container (including shapes, pictures, text boxes, and comments). The procedure anchors a container to the focused cell’s upper-left corner. If a floating container is already selected, the pasted container shifts by 10 pixels down and right to expose the overlapped selected container. Since a cell cannot have more than one anchored comment container, pasting a comment container into the commented focused cell raises an exception.

• A bitmap image. In this case, the procedure creates a new floating picture container hosting the pasted image at the focused cell’s upper-left corner.

To identify if calling the CopyToClipboard, CutToClipboard, and PasteFromClipboard procedures has an effect, you can invoke the CanCopyToClipboard, CanCutToClipboard, and CanPasteFromClipboard functions, respectively.

Key Combinations and Context Menus

Key combinations and context menus are available out-of-the-box and allow an end-user to access all clipboard operations in a familiar Excel-like environment.

The Cut, Copy, Paste, and Paste Special… options are listed in the Cell and Row/Column Header context menus.

If the clipboard contains data in the SpreadsheetML (XML Spreadsheet) format, both the Cell and Row/Column context menus provide the Paste Special submenu instead of the “Paste Special…” menu item:

The following clipboard-related keystrokes are available to an end-user:

• The Ctrl+C and Ctrl+Insert key combinations copy the current selection (i.e., a single cell range or floating container) to the clipboard. These keystrokes are equivalent to calling a worksheet’s CopyToClipboard procedure and executing the CopySelection command provided by the Spreadsheet control;

• The Ctrl+X and Shift+Del key combinations cut the current selection (i.e., a single cell range or floating container) to the clipboard. These keystrokes are equivalent to calling a worksheet’s CutToClipboard procedure and executing the CutSelection command provided by the Spreadsheet control;

• The Ctrl+V and Shift+Insert key combinations paste the current clipboard content into the active worksheet. These keystrokes are equivalent to calling a worksheet’s PasteFromClipboard procedure or executing the PasteSelection command provided by the Spreadsheet control.

Clipboard-Related Commands

Both Spreadsheet controls provide the CopySelection, CutSelection, and PasteSelection commands implemented as the action objects. To allow an end-user to execute these commands, you can link them manually to elements of your UI or invoke the Generate Ribbon/Toolbar UI dialog and check the “Home – Clipboard” category to generate the fully functional UI automatically.

In a generated Ribbon UI, the clipboard commands are displayed in the Clipboard toolbar tab group:

If you selected a TdxBarManager object as the UI generation target, these commands are listed in the Clipboard toolbar, respectively:

You can also execute TdxSpreadSheetCopySelection, TdxSpreadSheetCutSelection, and TdxSpreadSheetPasteSelection action objects programmatically by calling their Execute function, if required.

Paste Special Options

The Paste Special functionality available both to end-users and via an API, allows you to choose which pasted data is actually inserted into the destination cell range. Note that all paste special options are applicable only to the clipboard data in the ExpressSpreadSheet binary and SpreadsheetML (XML Spreadsheet) formats.

API Paste Special Options

The second overloaded PasteFromClipboard procedure variant allows you to pass a Paste Special option set as the AOptions parameter, including any combination of individual option flags corresponding to specific data stored in the clipboard.

These Paste Special options are in effect only if the clipboard contains data copied or cut from the Spreadsheet/Report Designer control (the ExpressSpreadSheet binary format) or Mircrosoft Excel® (the SpreadsheetML format). If the clipboard contains cell data in any other spreadsheet application format, the PasteFromClipboard procedure ignores all the Paste Special options and inserts only the cell values applying the destination cell format to them.

End-User Paste Special Options

Paste special options are available to an end-user via the cell context menu‘s “Paste Special” submenu when the clipboard contains cell data in the SpreadsheetML (XML Spreadsheet) format. The following Paste Special menu items correspond to specific sets of TdxSpreadSheetClipboardPasteOption values:

• “Paste” corresponds the dxSpreadSheetDefaultPasteOptions global constant value and pastes cell values, all formatting options, formula expressions, and associated comment containers.

• “Values” corresponds to the cpoValues value and pastes only cell values. If a cell contains a formula expression, it is calculated, and the resulting value is pasted to the corresponding destination cell.

• “Values and Number Formatting” corresponds to the collectively applied cpoValues and cpoNumberFormatting values and pastes only cell values and their number formatting, without source cell backgrounds, border styles, etc.

• “Values and Source Formatting” corresponds to the collectively applied cpoValues, cpoNumberFormatting, and cpoStyles values and pastes cell values and all source formatting.

• “Formulas” corresponds to the collectively applied cpoValues and cpoFormulas values and pastes both cell values and formula expressions, without any formatting.

• “Formulas and Number Formatting” corresponds to the collectively applied cpoValues, cpoFormulas, and cpoNumberFormatting values and pastes formula expressions with cell values, applying the source number formatting to them.

• “Keep Source Formatting” corresponds to the collectively applied cpoValues, cpoFormulas, cpoNumberFormatting, and cpoStyles values and pastes cell values and all source formatting, including cell backgrounds, border styles, etc.

• “Keep Source Column Widths” corresponds to the collectively applied cpoValues, cpoFormulas, cpoNumberFormatting, cpoStyles, and cpoColumnWidths values and pastes cell values and all source formatting, including the column widths.

• Paste Special… invokes the Paste Special dialog:

The Paste Special dialog provides all available cell data pasting options that an end-user can employ in custom combinations.