Archive

How to use Worksheet.PivotTableWizard in the xlwings API way

The PivotTableWizard method in Excel’s object model is a legacy but powerful tool for programmatically creating pivot tables. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model. The PivotTableWizard method belongs to the Worksheet object and allows for the dynamic generation of pivot tables based on specified source data and parameters. It is particularly useful when you need to automate the creation of pivot tables with custom configurations that might be cumbersome to set up manually through the Excel interface.

The syntax for calling PivotTableWizard via xlwings is as follows:
worksheet.api.PivotTableWizard(SourceType, SourceData, TableDestination, TableName, RowGrand, ColumnGrand, SaveData, HasAutoFormat, AutoPage, Reserved, BackgroundQuery, OptimizeCache, PageFieldOrder, PageFieldWrapCount, ReadData, Connection)
Here, worksheet is an xlwings Sheet object. The parameters control various aspects of the pivot table creation. Key parameters include:

• SourceType: Specifies the source of data. It can be xlDatabase (Excel range), xlExternal (external data source), xlConsolidation (multiple ranges), or xlScenario. Commonly, xlDatabase is used.
• SourceData: The range containing the source data. This can be a Range object or a string address.
• TableDestination: A Range object specifying the top-left cell where the pivot table should be placed.
• TableName: A string for the pivot table’s name.
  Other parameters like RowGrand and ColumnGrand control the display of grand totals, while SaveData determines if data is saved with the pivot table. Many parameters are optional and can be omitted by using None in Python.

For example, consider creating a pivot table from data in Sheet1 ranging from A1 to D100, placing the pivot table in Sheet2 starting at cell A3. The following xlwings code demonstrates this:

import xlwings as xw
from xlwings.constants import PivotTableSourceType

# Connect to the active workbook
wb = xw.Book.active
source_sheet = wb.sheets['Sheet1']
destination_sheet = wb.sheets['Sheet2']

# Define source data range
source_range = source_sheet.range('A1:D100')

# Create the pivot table
destination_sheet.api.PivotTableWizard(
SourceType=PivotTableSourceType.xlDatabase,
SourceData=source_range.api,
TableDestination=destination_sheet.range('A3').api,
TableName='SalesPivotTable',
RowGrand=True,
ColumnGrand=True
)

How to use Worksheet.PivotTables in the xlwings API way

The PivotTables member of the Worksheet object in the Excel object model is a collection that provides access to all pivot tables on a specific worksheet. Through the xlwings API, this collection allows for programmatic control over pivot tables, enabling tasks such as creating new pivot tables, modifying existing ones, refreshing data, or extracting information. This is particularly useful for automating reporting, data analysis workflows, and ensuring that pivot tables reflect the latest data without manual intervention.

In xlwings, the PivotTables collection is accessed via a Worksheet object. The syntax is straightforward: ws.pivot_tables, where ws is an xlwings Sheet object representing the worksheet. This returns a collection of PivotTable objects. You can iterate through this collection or access a specific pivot table by its name using indexing, e.g., ws.pivot_tables['PivotTable1'].

Key methods and properties available through the PivotTable object in xlwings include:

• refresh(): Updates the pivot table with the latest data from its source.
• name: Gets or sets the name of the pivot table.
• source_data: Gets or sets the range address of the source data (e.g., 'Sheet1!$A$1:$D$100').
• table_range1: Returns an xlwings Range object representing the entire pivot table, useful for copying or formatting.

For example, to list all pivot tables on a worksheet and refresh them:

import xlwings as xw

# Connect to the active workbook
wb = xw.books.active
ws = wb.sheets['SalesData']

# Iterate through all pivot tables and refresh each
for pt in ws.pivot_tables:
    print(f"Refreshing Pivot Table: {pt.name}")
    pt.refresh()

To create a new pivot table using xlwings, you typically use the api property to access the underlying Excel VBA object model, as xlwings does not have a dedicated high-level method for this. Here is an example that creates a pivot table from a source range:

import xlwings as xw

wb = xw.books.active
source_ws = wb.sheets['TransactionData']
pivot_ws = wb.sheets['Analysis']

# Define the source data range
source_range = source_ws.range('A1').expand('table')

# Use the Excel API via xlwings to create the pivot table
pc = wb.api.PivotCaches().Create(SourceType=xw.constants.PivotTableSourceType.xlDatabase,
SourceData=source_range.api)
pt = pc.CreatePivotTable(TableDestination=pivot_ws.range('A3').api,
TableName='MonthlySales')

# Configure the pivot table fields (using the Excel API)
pt.PivotFields('Region').Orientation = xw.constants.PivotFieldOrientation.xlRowField
pt.PivotFields('Month').Orientation = xw.constants.PivotFieldOrientation.xlColumnField
pt.PivotFields('Sales').Orientation = xw.constants.PivotFieldOrientation.xlDataField

Another common task is to modify an existing pivot table’s data source. Suppose you have extended your source data; you can update the pivot cache accordingly:

ws = xw.books.active.sheets['Dashboard']
pt = ws.pivot_tables[0] # Access the first pivot table in the collection

# Update the source data range to include new rows/columns
new_source_range = "TransactionData!$A$1:$F$500"
pt.source_data = new_source_range
pt.refresh()

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

How to use Worksheet.Paste in the xlwings API way

The Paste method in the Worksheet object of the xlwings API is a powerful feature for transferring data from the clipboard directly into an Excel worksheet. This method is particularly useful when you need to paste data that has been copied from other applications or within Excel itself, automating the process without manual intervention. It leverages the underlying Excel object model, providing a seamless way to integrate clipboard operations into your Python scripts.

Functionality:
The primary function of the Paste method is to insert the contents of the clipboard at a specified location in the worksheet. It can handle various data types, including text, numbers, formulas, and formatting, depending on what is currently stored in the clipboard. This method is essential for automating data import tasks, such as pasting data from external sources like web pages or other documents.

Syntax:
In xlwings, the Paste method is called on a Worksheet object. The basic syntax is:

worksheet.api.Paste(Destination)

• worksheet: An xlwings Worksheet object representing the target worksheet.
• Destination: (Optional) A Range object specifying the top-left cell where the pasted data will be placed. If omitted, the paste operation occurs at the current selection in the worksheet. In xlwings, you can specify the destination using worksheet.range('A1') or similar.

Note: The Paste method is accessed via the .api property in xlwings, which provides direct access to the underlying Excel object model (e.g., VBA methods). This is because Paste is not natively wrapped in the high-level xlwings API but is available through the low-level API.

Parameters:
The Destination parameter is optional and can be set as follows:

• If provided, it must be an Excel Range object (accessed via xlwings range method). For example, worksheet.range('B2') would start pasting at cell B2.
• If not provided, the paste uses the currently selected cell in Excel, which may be unpredictable in automated scripts. It is generally recommended to specify the destination explicitly.

Code Examples:
Here are practical examples of using the Paste method with xlwings:

1. Basic Paste Operation: Copy data from another workbook and paste it into a specific cell.

import xlwings as xw

# Open the source and target workbooks
source_wb = xw.Book('source.xlsx')
target_wb = xw.Book('target.xlsx')

# Copy data from source worksheet (e.g., range A1:C10)
source_wb.sheets['Sheet1'].range('A1:C10').copy()

# Paste the copied data into target worksheet at cell D5
target_sheet = target_wb.sheets['Sheet1']
target_sheet.api.Paste(Destination=target_sheet.range('D5').api)

2. Pasting Without Specifying Destination: This uses the current selection, which can be set beforehand.

import xlwings as xw

wb = xw.Book('data.xlsx')
sheet = wb.sheets['Sheet1']

# Copy data from a range
sheet.range('A1:A5').copy()

# Select a cell where you want to paste (e.g., C1)
sheet.range('C1').select()

# Paste at the selected location
sheet.api.Paste()

3. Automating Data Import from Clipboard: Assume data is copied from an external application like a web browser.

import xlwings as xw
import pyperclip # Third-party library to access clipboard

# Simulate copying data to clipboard (in real use, data might be copied manually or via automation)
pyperclip.copy('Sample text from clipboard')

# Open Excel and paste the clipboard content
wb = xw.Book()
sheet = wb.sheets[0]
sheet.api.Paste(Destination=sheet.range('A1').api)

# This will paste "Sample text from clipboard" into cell A1

How to use Worksheet.OLEObjects in the xlwings API way

The OLEObjects member of the Worksheet object in Excel represents a collection of all OLE objects (such as embedded documents, ActiveX controls, or other insertable objects) on a specific worksheet. In xlwings, this collection is accessible via the api property, which provides direct access to the underlying Excel object model. This allows for programmatic control over embedded objects, including enumeration, modification, and interaction with OLE controls within a workbook.

Functionality:
The primary use of the OLEObjects collection in xlwings is to manage embedded OLE objects. You can count the objects, retrieve a specific object by its index or name, modify properties (e.g., size, placement), or even invoke methods associated with ActiveX controls. This is particularly useful for automating dashboards or forms that contain interactive elements like buttons, list boxes, or embedded charts from other applications.

Syntax:
Accessing the OLEObjects collection in xlwings follows the pattern: worksheet.api.OLEObjects. This returns a collection object. To reference a specific OLE object, you can use:

• worksheet.api.OLEObjects(Index) where Index is the object’s numeric position (1-based) or its name as a string.
• worksheet.api.OLEObjects.Item(Index) which is functionally equivalent.

Common properties and methods of an OLE object (returned from the collection) include:

• Name: Gets or sets the object’s name.
• Left, Top, Width, Height: Control the object’s position and dimensions in points.
• Object: Provides access to the underlying OLE object’s native interface (e.g., for an ActiveX control, you can access its specific properties).
• Delete(): Removes the object from the worksheet.

Code Examples:

1. List all OLE objects on a worksheet:

import xlwings as xw
wb = xw.Book('workbook.xlsx')
ws = wb.sheets['Sheet1']
ole_objects = ws.api.OLEObjects
count = ole_objects.Count
print(f"Number of OLE objects: {count}")
for i in range(1, count + 1):
    obj = ole_objects(i)
    print(f"Object {i}: Name='{obj.Name}', Type='{obj.progID}'")

2. Resize and reposition an OLE object by name:

# Assume an embedded Word document object named "DocObject1"
obj = ws.api.OLEObjects("DocObject1")
obj.Left = 100 # Points from left edge
obj.Top = 50 # Points from top
obj.Width = 200
obj.Height = 150

3. Interact with an ActiveX command button:

# Access the button, then its specific properties via the Object property
button = ws.api.OLEObjects("CommandButton1")
# Change the button caption
button.Object.Caption = "Click Me"
# The Object property exposes the ActiveX control's native interface

4. Delete all OLE objects on a sheet:

ole_objects = ws.api.OLEObjects
for i in range(ole_objects.Count, 0, -1): # Iterate backwards when deleting
    ole_objects(i).Delete()

How to use Worksheet.Move in the xlwings API way

The Move member of the Worksheet object in the Excel object model, accessible via the xlwings library in Python, provides a programmatic way to reposition a worksheet within its workbook. This functionality is essential for organizing workbook structure, such as reordering sheets for logical presentation or moving a newly created sheet to a specific location. Unlike simply activating a sheet, the Move method physically changes the sheet’s index in the workbook’s tab order.

In xlwings, the method is accessed through a Sheet object (which represents a Worksheet). The core syntax for its API call is:

sheet.move(before, after)

Both parameters, before and after, are optional but mutually exclusive—you should specify only one. They accept either an xlwings Sheet object or an integer index.

• before: The sheet before which the current sheet will be placed. If specified as a Sheet object, the moved sheet will be positioned immediately before that specific sheet. If specified as an integer (1-based index), the moved sheet will be moved to a position before the sheet currently at that index.
• after: The sheet after which the current sheet will be placed. If specified as a Sheet object, the moved sheet will be positioned immediately after that specific sheet. If specified as an integer (1-based index), the moved sheet will be moved to a position after the sheet currently at that index.

If neither before nor after is specified, Excel will create a new workbook containing only the moved worksheet. The following table summarizes the behavior:

Parameter Provided	Resulting Action
before=target	Moves the sheet to a position immediately before the target sheet.
after=target	Moves the sheet to a position immediately after the target sheet.
Neither parameter	Moves the sheet to a new, single-sheet workbook.

Code Examples:

1. Move a sheet to the beginning of the workbook (before the first sheet):

import xlwings as xw
wb = xw.Book("report.xlsx")
sheet_to_move = wb.sheets["DataSheet"]
first_sheet = wb.sheets[0] # Index 0 refers to the first sheet
sheet_to_move.move(before=first_sheet)

2. Move a sheet to the end of the workbook (after the last sheet):

import xlwings as xw
wb = xw.Book()
summary_sheet = wb.sheets.add("Summary")
last_sheet = wb.sheets[-1] # Index -1 refers to the last sheet
summary_sheet.move(after=last_sheet)

3. Move a sheet to a specific index position (e.g., to become the third sheet):

import xlwings as xw
wb = xw.Book()
analysis_sheet = wb.sheets["Analysis"]
# To place it as the third sheet, move it before the current third sheet.
# Index is 1-based in the `move` method context for this operation.
analysis_sheet.move(before=3)

4. Move a sheet relative to another named sheet:

import xlwings as xw
wb = xw.Book("dashboard.xlsx")
chart_sheet = wb.sheets["Charts"]
pivot_sheet = wb.sheets["PivotTables"]
# Place the Charts sheet right after the PivotTables sheet
chart_sheet.move(after=pivot_sheet)

How to use Worksheet.ExportAsFixedFormat in the xlwings API way

The ExportAsFixedFormat member of the Worksheet object in Excel’s object model is a method that enables the conversion of a worksheet into a fixed-layout format, such as PDF or XPS, directly from an Excel file. In xlwings, this functionality is exposed through the api property, which provides direct access to the underlying Excel object model. This method is particularly useful for automating report generation, distributing documents in a non-editable format, or archiving sheets with precise formatting preserved.

Functionality:
The primary purpose of ExportAsFixedFormat is to export a worksheet to a fixed-format file. It supports formats including PDF and XPS, ensuring that the layout, fonts, and graphics are maintained as they appear in Excel. This is essential for creating professional documents that require consistent presentation across different devices and platforms.

Syntax in xlwings:
In xlwings, you call this method via the api property of a Worksheet object. The basic syntax is:

worksheet.api.ExportAsFixedFormat(Type, Filename, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish, FixedFormatExtClassPtr)

Here, worksheet refers to the xlwings Worksheet object. The parameters are as follows:

• Type: Specifies the format type. Use 0 for PDF or 1 for XPS.
• Filename: A string representing the full path and name of the output file (e.g., r'C:\Reports\output.pdf'). If omitted, Excel uses the default name.
• Quality: Optional. Sets the quality of the output. Use 0 for Standard or 1 for Minimum size.
• IncludeDocProperties: Optional. True to include document properties, False otherwise.
• IgnorePrintAreas: Optional. True to ignore any set print areas, False to use them.
• From and To: Optional. Integers specifying the page range to export (e.g., From=1, To=3). If not specified, all pages are exported.
• OpenAfterPublish: Optional. True to open the file after export, False otherwise.
• FixedFormatExtClassPtr: Optional. A pointer for extended format classes, typically left as None.

Most parameters are optional; in practice, only Type and Filename are commonly required. For example, to export as PDF with default settings, you might specify just these two.

Code Example:
Below is an xlwings code instance that demonstrates exporting the active worksheet to a PDF file. This example assumes Excel is already open with a workbook, and it sets a few optional parameters for clarity.

import xlwings as xw

# Connect to the active Excel instance
app = xw.apps.active
wb = app.books.active
ws = wb.sheets.active

# Export the active worksheet to PDF
output_path = r'C:\Users\Public\Documents\Monthly_Report.pdf'
ws.api.ExportAsFixedFormat(
Type=0, # 0 for PDF
Filename=output_path,
Quality=0, # Standard quality
IncludeDocProperties=True,
IgnorePrintAreas=False,
OpenAfterPublish=False
)

print(f"Worksheet exported to {output_path}")

How to use Worksheet.Evaluate in the xlwings API way

The Evaluate method of the Worksheet object in Excel is a powerful tool that allows you to evaluate a Microsoft Excel expression or a name and return the resulting value. In xlwings, this functionality is exposed through the api property, which provides direct access to the underlying Excel object model. This method is particularly useful for calculating formulas or expressions that are provided as strings, without the need to write them into a cell first. It can handle complex expressions, including those with functions and references, and return the computed result directly to your Python code.

Functionality:
The primary function of Evaluate is to compute the result of an Excel formula or expression given as a string. This is equivalent to typing the formula into the Excel formula bar and pressing Enter, but it is done programmatically. It can evaluate simple arithmetic, Excel functions, named ranges, and cell references. This is efficient for one-off calculations where you do not want to modify the worksheet.

Syntax in xlwings:
In xlwings, you access the Evaluate method through the api property of a Sheet object (which corresponds to a Worksheet). The general syntax is:

result = sheet.api.Evaluate(expression)

• sheet: This is an xlwings Sheet object representing the worksheet where the evaluation context is considered (important for relative references).
• expression (required): A string that contains the Excel formula or expression to be evaluated. This can be any valid Excel formula, such as "SUM(A1:A10)", "2+2", or "A1*B1". The string must be formatted exactly as it would be in Excel, using English function names and comma separators by default (depending on the system’s locale settings).

Parameters:
The Evaluate method takes a single parameter:

• Expression: A string that is a valid Excel formula. It can include:
• Arithmetic operators (e.g., "5*3").
• Excel functions (e.g., "AVERAGE(1,2,3)").
• Cell references (e.g., "A1", "Sheet2!B5"). Relative references are evaluated in the context of the worksheet object used.
• Named ranges (e.g., "MyRange").
• R1C1-style references are also supported if provided as strings (e.g., "R1C1").

Code Examples:
Here are practical examples using xlwings to demonstrate the Evaluate method:

1. Evaluating a simple arithmetic expression:

import xlwings as xw
# Connect to the active workbook and sheet
wb = xw.books.active
sheet = wb.sheets['Sheet1']
# Evaluate 10 + 20
result = sheet.api.Evaluate("10+20")
print(result) # Output: 30

2. Using an Excel function to calculate an average:

import xlwings as xw
wb = xw.books.active
sheet = wb.sheets[0]
# Assume cells A1:A5 contain numbers 1, 2, 3, 4, 5
# Evaluate the AVERAGE function on that range
avg_result = sheet.api.Evaluate("AVERAGE(A1:A5)")
print(avg_result) # Output: 3.0

3. Referencing a cell and performing a calculation:

import xlwings as xw
wb = xlwings.Book('example.xlsx')
sheet = wb.sheets['Data']
# Suppose cell B2 contains the value 100 and C2 contains 0.2
# Calculate B2 * C2
computed = sheet.api.Evaluate("B2*C2")
print(computed) # Output: 20.0

4. Evaluating a more complex formula with a named range:

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.add()
sheet = wb.sheets[0]
# Define a named range for cells D1:D3
wb.api.Names.Add(Name="SalesData", RefersTo="=Sheet1!$D$1:$D$3")
# Assign values to D1:D3
sheet.range('D1').value = [200, 300, 400]
# Use SUM on the named range
total_sales = sheet.api.Evaluate("SUM(SalesData)")
print(total_sales) # Output: 900
wb.close()
app.quit()