How to use Worksheet.PasteSpecial in the xlwings API way

The PasteSpecial method of the Worksheet object in Excel is a powerful feature for pasting data with specific attributes, such as values, formats, or formulas, rather than a simple copy-paste. In xlwings, this functionality is accessible through the api property, which provides direct access to the underlying Excel object model. This allows for precise control over how data is transferred between ranges or applications, making it essential for tasks like consolidating reports, applying number formats, or skipping blanks during data integration.

Functionality
The primary purpose of PasteSpecial is to paste clipboard contents into a worksheet range with specified options. Unlike the standard Paste method, it enables selective pasting—for example, pasting only the values from copied cells while discarding formulas, or pasting only the column widths. This is particularly useful when you need to manipulate data without altering underlying formulas or when preparing data for presentation.

Syntax
In xlwings, you call PasteSpecial via the Excel object model. The method is applied to a Range object where the paste will occur. The basic syntax is:

sheet.range("A1").api.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

The parameters are:

• Paste: Specifies the part of the copied data to paste. It is an enumeration from the XlPasteType constants. Common values include:
• xlPasteAll (-4104): Pastes everything.
• xlPasteValues (-4163): Pastes only values.
• xlPasteFormats (-4122): Pastes only formats.
• xlPasteFormulas (-4123): Pastes only formulas.
• Operation: Optional. Specifies a mathematical operation to apply during the paste, from the XlPasteSpecialOperation constants. For example, xlPasteSpecialOperationAdd (2) adds the copied data to the destination values. If omitted, no operation is performed.
• SkipBlanks: Optional. A boolean (True or False) that, when set to True, prevents blank cells from the copied range from overwriting existing data in the destination.
• Transpose: Optional. A boolean that, when True, transposes rows and columns during the paste.

These parameters are passed as keyword arguments in Python, and you can refer to Excel’s VBA documentation for exact constant values. In practice, you often use numeric equivalents (e.g., -4163 for xlPasteValues).

Code Examples
Here are practical xlwings API examples demonstrating PasteSpecial:

1. Paste Values Only: Copy data from one range and paste only the values into another, ignoring formulas and formats.

import xlwings as xw
wb = xw.Book("example.xlsx")
sheet = wb.sheets["Sheet1"]
# Copy data from A1:B5
sheet.range("A1:B5").api.Copy()
# Paste only values into D1
sheet.range("D1").api.PasteSpecial(Paste=-4163) # xlPasteValues
# Clear clipboard to avoid persistent paste prompts
wb.app.api.CutCopyMode = False

2. Paste Formats and Transpose: Copy a range and paste only its formatting to a new location, while also transposing the layout.

import xlwings as xw
wb = xw.Book("report.xlsx")
sheet = wb.sheets["Data"]
# Copy the header range
sheet.range("A1:D1").api.Copy()
# Paste formats with transposition to A10
sheet.range("A10").api.PasteSpecial(Paste=-4122, Transpose=True) # xlPasteFormats
wb.app.api.CutCopyMode = False

3. Paste with Operation and Skip Blanks: Copy a range of numbers and add them to an existing dataset, skipping any blanks in the copied data.

import xlwings as xw
wb = xw.Book("budget.xlsx")
sheet = wb.sheets["Summary"]
# Copy values from a source range
sheet.range("F1:F10").api.Copy()
# Paste with addition operation, skipping blanks
sheet.range("G1").api.PasteSpecial(Paste=-4163, Operation=2, SkipBlanks=True)
wb.app.api.CutCopyMode = False