How to use Range.PasteSpecial in the xlwings API way

The PasteSpecial method of the Range object in Excel VBA is a powerful feature for pasting data with specific attributes or operations, such as values, formats, or formulas. In xlwings, this functionality is exposed through the api property, which provides direct access to the underlying Excel object model. This allows Python scripts to leverage Excel’s native capabilities for advanced data manipulation.

Functionality
PasteSpecial is used to paste a copied range into a target range with special options. It is commonly applied after copying data using the Copy method. Key uses include pasting only values (excluding formulas), formats, column widths, or performing mathematical operations like addition or multiplication on the existing data. This is essential for tasks like data cleaning, report formatting, and batch updates without altering underlying formulas.

Syntax in xlwings
In xlwings, you access PasteSpecial via the api property of a Range object. The basic syntax is:

range.api.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

Parameters:

• Paste: Specifies the part of the copied data to paste. It is an Excel constant (e.g., xlPasteAll for everything, xlPasteValues for values only). Common values are accessed through xlwings.constants.
• Operation: Optional. Specifies a mathematical operation to apply during paste, such as xlNone (default, no operation), xlAdd, xlMultiply. Also from xlwings.constants.
• SkipBlanks: Optional. If True, blanks in the copied range are not pasted, preventing overwriting of existing data. Default is False.
• Transpose: Optional. If True, the data is transposed (rows and columns swapped) during paste. Default is False.

For example, xlPasteValues corresponds to the Excel constant -4163. In xlwings, you can use xlwings.constants.xlPasteValues or the numeric value directly. The parameters are typically passed as keyword arguments for clarity.

Code Examples

1. Pasting only values from a copied range:

import xlwings as xw
wb = xw.Book('example.xlsx')
sheet = wb.sheets['Sheet1']
# Copy range A1:B5
sheet.range('A1:B5').copy()
# Paste special as values into D1
sheet.range('D1').api.PasteSpecial(Paste=xw.constants.xlPasteValues)

This copies data from A1:B5 and pastes only the values (no formulas or formatting) starting at D1.

2. Pasting formats and performing an addition operation:

import xlwings as xw
wb = xw.Book('data.xlsx')
sheet = wb.sheets[0]
# Copy range C1:C10
sheet.range('C1:C10').copy()
# Paste special with formats and add to existing values in E1:E10
sheet.range('E1').api.PasteSpecial(Paste=xw.constants.xlPasteFormats, Operation=xw.constants.xlAdd)

Here, the formats from C1:C10 are applied to E1:E10, and the values are added to the existing ones in the target range.

3. Using transpose and skipping blanks:

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open('source.xlsx')
sheet = wb.sheets['Data']
# Copy a horizontal range
sheet.range('A1:J1').copy()
# Paste special as values, transposed vertically, skipping blanks
sheet.range('A2').api.PasteSpecial(Paste=-4163, Transpose=True, SkipBlanks=True)
wb.save()
app.quit()