Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Sheets / Customizing Clipboard Operation Options
In This Topic
    Customizing Clipboard Operation Options
    In This Topic

    There are several Clipboard operations (such as copy, cut, and paste) that are automatically set for a sheet with default settings; they are built-in to Spread. But Spread also gives you the ability to customize these operations on actual applications that you develop, depending on your specific needs. You can customize how the user can interact with the contents of the Clipboard when users perform copying and pasting actions in cells in the spreadsheet. You can implement those operations at your discretion in code. These customizations include:

    The following members are used to determine Clipboard-related interaction:

    The spreadsheet methods in the SheetView class that involve Clipboard operation are:

    The corresponding shape methods in the SheetView class that involve Clipboard operation are:

    If there are locked cells in the range to cut or paste then the Clipboard operation is not performed.

    The .NET version of the product handles Clipboard operations differently from the way that the COM version does.

    You can also set how some Clipboard-related features perform when the user is in edit mode in a cell on the Spread. You can set whether the pop-up menu appears while in edit mode within a cell using the AutoMenu property in SuperEditBase class and whether the user can perform Clipboard operations with the shortcut keys with the AllowClipboardKeys property in SuperEditBase class.

    For more information about copying and pasting, see Copying Data on a Sheet.

    Deactivating Clipboard Operations

    You can set whether the shortcut keys are available to the end user for them to use to perform Clipboard operations by setting the AutoClipboard property in the FpSpread class. You can deactivate all the Clipboard operations with components by setting the AutoClipboard property to False in the FpSpread class. The default setting is True. You can deactivate some Clipboard operations such as pasting (Ctrl+V) by deactivating the input map definition for short-cut keys and Clipboard operations (Ctrl+C, Ctrl+V, and Ctrl+X).

    Excluding Headers from Clipboard Operations

    You can set whether to include headers when using Clipboard operations by setting the ClipboardOptions property in the FpSpread class and the ClipboardOptions enumeration. The default setting, AllHeaders, allows all headers to be included.

    Obtaining the Clipboard Contents

    Values which are copied on sheets are controlled by the Clipboard class which is provided from the .NET Framework. You can obtain Clipboard contents by using respective operations in this Clipboard class.

    Cell data is copied onto the Clipboard in advance by calling ClipboardCopy method in the SheetView class at the Load event. Then, calling GetDataObject method enables you to obtain those Clipboard contents for use, such as for text format determination.

    Deactivating Pasting

    The ClipboardPasting event occurs when pasting is performed (Ctrl+V) on sheets. You can deactivate pasting by canceling this event under certain circumstances. You can prevent pasting by obtaining the timing when pasting is performed, and canceling the action.

    Changing the Scope of Pasting

    When cells are copied or cut to the Clipboard, all the data aspects including values, formats, and formulas, are available by default for pasting. You can configure to paste values only, for example, by changing the input map definitions. The default setting is ClipboardPasteAll, which enables pasting all the aspects of the data. The ClipboardPasteOptions enumeration allows you to set the scope of what is pasted when a Clipboard paste is performed by the user.

    Disabling Pasting in Invisible Cells

    You can set whether to paste the clipboard values in invisible cells, row, and columns by using the PasteSkipInvisibleRange option from the Features class. It accepts a boolean value and is False by default.

    This option only takes effect when RichClipboard is set to True.

    When PasteSkipInvisibleRange = True When PasteSkipInvisibleRange = False (default value)
    Pasting Data in Invisible Range Skip Pasting Data in Invisible Range

    The following example allows you to skip pasting the data in an invisible range.

    C#
    Copy Code 
    fpSpread1.LegacyBehaviors = LegacyBehaviors.None;
fpSpread1.Features.RichClipboard = true;
fpSpread1.ActiveSheet.Rows[7].Visible = false;
fpSpread1.ActiveSheet.Rows[10, 11].Visible = false;
fpSpread1.AsWorkbook().Features.PasteSkipInvisibleRange = true;
    Visual Basic
    Copy Code 
    FpSpread1.LegacyBehaviors = LegacyBehaviors.None
FpSpread1.Features.RichClipboard = true
FpSpread1.ActiveSheet.Rows(7).Visible = false
FpSpread1.ActiveSheet.Rows(10, 11).Visible = false
FpSpread1.AsWorkbook().Features.PasteSkipInvisibleRange = true

    Performing the Clipboard Operations in Code

    Various methods are provided in the SheetView class for Clipboard processes. You can run them when you want. These include:

    Enabling Multi-Range Selection for Copy/Paste

    The RichClipboard property in the IFeatures interface indicates whether you can copy/paste data across multi-ranges. This property can be implemented with flat style mode only.

    multi-range-copypaste

    This multi-range selection for copy/paste option only takes effect when the RichClipboard property is set to true.

    C#
    Copy Code 
    // Flat style
fpSpread1.LegacyBehaviors &= ~LegacyBehaviors.Style;
// Setting RichClipboard property
fpSpread1.Features.RichClipboard= true;
    Visual Basic
    Copy Code 
    ' Flat style
FpSpread1.LegacyBehaviors = FpSpread1.LegacyBehaviors And Not LegacyBehaviors.Style
' Setting RichClipboard property
FpSpread1.Features.RichClipboard = True

    Pasting Special Cell Content

    Spread allows you to copy and paste cell contents into a worksheet. When you use the Copy and Paste options, all attributes are copied by default.

    Instead of a simple direct paste, you can also use the Paste Special to paste the contents of the clipboard in a specific way. It is particularly useful when you want to control how data is transferred from the clipboard into your worksheet. For example, you can use Paste Special to paste only values without formulas, to transpose data, or to include conditional formatting during the paste process.

    Using code

    The following example code shows how to paste cell content and formatting using the IRange.PasteSpecial API. Note that the RichClipboard feature must be set to True to select various pasting operations.

    C#
    Copy Code 
    // Add cell content
fpSpread1.Features.RichClipboard = true;
fpSpread1.Features.EnhancedShapeEngine = true;
IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet;
TestActiveSheet.SetValue(0, 0, new object[,] { { 1, 2, 3, null, 5 } });
var rangeA1C1 = TestActiveSheet.Cells["A1:C1"];
rangeA1C1.FormatConditions.AddIconSetCondition();
var cmt = TestActiveSheet.Cells["E2"].AddComment("visible comment");
cmt.Visible = true;
var thread = TestActiveSheet.Cells["D2"].AddCommentThreaded("thread");
thread.AddReply("reply1");
thread.AddReply("reply2");
var cellB2 = TestActiveSheet.Cells["B2"];
cellB2.Borders.LineStyle = GrapeCity.Spreadsheet.BorderLineStyle.Thick;
cellB2.Borders.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Red);
// Copy and paste specific cell content
TestActiveSheet.Cells["A1:E2"].Copy(true);
TestActiveSheet.Cells["E8"].PasteSpecial(GrapeCity.Spreadsheet.PasteType.AllExceptBorders, GrapeCity.Spreadsheet.PasteSpecialOperation.None, false, false);
    VB
    Copy Code 
    ' Add cell content
FpSpread1.Features.RichClipboard = True
FpSpread1.Features.EnhancedShapeEngine = True
Dim TestActiveSheet As IWorksheet = FpSpread1.AsWorkbook().ActiveSheet
TestActiveSheet.SetValue(0, 0, New Object(,) {
{1, 2, 3, Nothing, 5}})
Dim rangeA1C1 = TestActiveSheet.Cells("A1:C1")
rangeA1C1.FormatConditions.AddIconSetCondition()
Dim cmt = TestActiveSheet.Cells("E2").AddComment("visible comment")
cmt.Visible = True
Dim thread = TestActiveSheet.Cells("D2").AddCommentThreaded("thread")
thread.AddReply("reply1")
thread.AddReply("reply2")
Dim cellB2 = TestActiveSheet.Cells("B2")
cellB2.Borders.LineStyle = GrapeCity.Spreadsheet.BorderLineStyle.Thick
cellB2.Borders.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Red)
' Copy and paste specific cell content
TestActiveSheet.Cells("A1:E2").Copy(True)
TestActiveSheet.Cells("E8").PasteSpecial(GrapeCity.Spreadsheet.Past eType.AllExceptBorders, GrapeCity.Spreadsheet.PasteSpecialOperation.None, False, False)

    At Runtime

    The following steps show how to use the Paste Special functionality at runtime.

    1. Run the code below to load a spread control with the ribbonBar having RichClipboard property as True.
      C#
      Copy Code 
      fpSpread1.Features.RichClipboard = true;
ribbonBar1.Attach(fpSpread1);
      Visual Basic
      Copy Code 
      FpSpread1.Features.RichClipboard = True
ribbonBar1.Attach(FpSpread1)
    2. Select the cell range that contains the data or other attributes that you want to copy.
    3. On the Home tab of the ribbonBar, click Copy or press Ctrl + C key.
    4. Right-click on the cell where you want to paste the copied data and select the paste options.

    5. Select paste options by directly selecting the icons or by using the settings from the Paste Special dialog.

    The options on the Paste menu will depend on the type of selected data. For example, if the target sheet has no conditional formatting, the option “Merge Conditional Formats” is not visible and is disabled on the Paste Special dialog.

    Paste Menu Options

    There are numerous options to paste copied cell data. The following are available paste menu options in Spread.

    Select To paste
    Paste All cell contents and formatting, including linked data.
    Formulas Only the formulas.
    Formulas & Number Formatting Only formulas and number formatting options.

    Keep Source Formatting

    All cell contents and formatting using the theme that was applied to the source data.

    No Borders

    		 All cell contents and formatting except cell borders.
    Keep Source Column Widths All cell contents and formatting and column widths.
    Transpose Reorients the content of copied cells when pasting. Data in rows is pasted into columns and vice versa.
    Paste Values Only the values as displayed in the cells.
    Values Number Formatting Only the values and number formatting.
    Values Source Formatting Only the values and number color and font size formatting.
    Formatting All cell formatting, including number and source formatting.
    Paste Link Link the pasted data to the original data.
    When you paste a link to the data that you copied, Excel enters an absolute reference to the copied cell or range of cells in the new location.
    Paste as Picture A copy of the image.
    Linked Picture A copy of the image with a link to the original cells (if you make any changes to the original cells those changes are reflected in the pasted image).
    Column widths Paste the width of one column or range of columns to another column or range of columns.
    Merge Conditional Formatting Combine conditional formatting from the copied cells with conditional formatting present in the paste area.

    Paste Special Options

    Spread provides the following advanced Paste Special options.

    Select To paste
    All All cell contents and formatting, including linked data.
    Formulas Only the formulas.
    Values Only the values as displayed in the cells.
    Formats Cell formatting.
    Comments Only comments attached to the cell.
    Validation Only data validation rules.
    All using source theme All cell contents and formatting using the theme that was applied to the source data.
    All except borders Cell contents and formatting, except cell borders.
    Column widths Width of one column or range of columns to another column or range of columns.
    Formulas and number formats Only formulas and number formatting.
    Values and number formats Only values and number formatting.
    All merging conditional formats All cell contents and formatting. Combine conditional formatting from the copied cells with conditional formatting present in the paste area.

    Operation Options

    The operation options mathematically combine values between the copy and paste areas. Note that Operations will be disabled when selected Paste Type is Formats, Comments and Notes, Validation.

    Select To paste
    None Paste the contents of the copy area without a mathematical operation.
    Add Add the values in the copy area to the values in the paste area.
    Subtract Subtract the values in the copy area from the values in the paste area.
    Multiply Multiply the values in the paste area by the values in the copy area.
    Divide Divide the values in the paste area by the values in the copy area.

    Other options

    The table below describes additional Paste Special options.

    Select To paste
    Skip Blanks Avoid replacing values or attributes in your paste area when blank cells occur in the copy area.
    Transpose Reorients the content of copied cells when pasting. Data in rows is pasted into columns and vice versa. Transpose is disabled if the copied range contains a sparkline.
    Paste Link

    If the data is a picture, links to the source picture. If the source picture is changed, this one will change too.

    Note that the Paste Link button is enabled only if the following conditions are fulfilled:

    • Paste Type is All or All except borders.
    • Operation is None.
    • Transpose and Skip Blanks are unchecked.

    Limitations

    See Also
    Copying Data on a Sheet
    Repeatedly Filling a Range of Cells with Copied Cells
    Copying Data on a Sheet
    Repeatedly Filling a Range of Cells with Copied Cells