Archive

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()

How to use Worksheet.Delete in the xlwings API way

The Delete member of the Worksheet object in the Excel object model, accessible via the xlwings API in Python, provides a programmatic way to remove a specific worksheet from a workbook. This operation is irreversible through the API itself (akin to manually deleting a sheet and not saving the workbook), so it should be used with caution, especially on unsaved workbooks where data loss can occur.

Functionality
The primary function of the Delete method is to permanently delete the worksheet object on which it is called. After deletion, the worksheet is removed from the workbook’s Worksheets collection. If the deleted sheet was the only sheet in the workbook, xlwings and Excel will typically prevent its deletion to maintain at least one visible sheet, as an Excel workbook must contain at least one visible worksheet.

Syntax & Parameters
In xlwings, the method is called directly on a Sheet object (which represents a worksheet or chart sheet). The xlwings API abstracts the underlying COM calls into a simple Python method call.

sheet.delete()

There are no parameters for this method in the standard xlwings API. The action is applied to the sheet object referenced. It’s important to note that the Sheet object in xlwings corresponds to what the Excel Object Model calls a Worksheet (for standard worksheets) or a Chart object (for chart sheets). The delete() method works on both.

Code Examples

1. Basic Deletion of a Specific Sheet:
   This example opens a workbook and deletes a sheet named “SheetToRemove”.

import xlwings as xw

# Open the workbook (use full path if needed)
wb = xw.Book("example.xlsx")

# Access the specific worksheet
sheet_to_delete = wb.sheets["SheetToRemove"]

# Delete the worksheet
sheet_to_delete.delete()

# Save the workbook to persist the change
wb.save()
wb.close()

2. Deleting the Active Sheet:
   This example deletes whichever sheet is currently active in the open workbook.

import xlwings as xw

app = xw.App(visible=False) # Start Excel in the background
wb = app.books.open("data.xlsx")

# Delete the active sheet
wb.sheets.active.delete()

wb.save("data_modified.xlsx")
wb.close()
app.quit()

3. Conditional Deletion Based on Content:
   A more practical example involves checking sheet names or content before deletion. This script deletes all sheets whose name contains the word “Temp”.

import xlwings as xw

wb = xw.Book("report.xlsx")

# Create a list of sheets to delete first to avoid iteration issues
sheets_to_delete = [sht for sht in wb.sheets if "Temp" in sht.name]

for sht in sheets_to_delete:
    print(f"Deleting sheet: {sht.name}")
    sht.delete()

wb.save()

How to use Worksheet.Copy in the xlwings API way

The Copy method of the Worksheet object in xlwings is a powerful tool for duplicating worksheets within or across workbooks. This functionality is essential for tasks such as creating templates, backing up data, or reorganizing workbook structures without manual copying and pasting. By leveraging the Excel object model through xlwings, users can automate these processes efficiently in Python.

In xlwings, the Copy method is accessed via the api property, which provides direct access to the underlying Excel object model. The syntax follows the pattern of the Excel VBA Copy method, where you specify the location for the copied sheet. The method signature is Copy(Before, After), with both parameters being optional. The Before parameter accepts a Worksheet object indicating the sheet before which the copy should be placed, while After specifies the sheet after which the copy should be inserted. If neither Before nor After is provided, Excel creates a new workbook to hold the copied worksheet. It’s important to note that you cannot use both Before and After simultaneously; specifying one excludes the other. These parameters allow precise control over the placement of the duplicated sheet within the workbook’s tab order.

For example, to copy a worksheet named “DataSheet” and place it before an existing sheet called “SummarySheet” in the same workbook, you can use the following xlwings code:

import xlwings as xw
wb = xw.Book("example.xlsx")
data_sheet = wb.sheets["DataSheet"]
summary_sheet = wb.sheets["SummarySheet"]
data_sheet.api.Copy(Before=summary_sheet.api)

This code snippet opens a workbook, references the “DataSheet” and “SummarySheet”, and copies “DataSheet” to appear directly before “SummarySheet”. The copied sheet will automatically be named “DataSheet (2)” by Excel to avoid naming conflicts.

Another common use case is copying a worksheet to a new workbook. By omitting both Before and After parameters, Excel generates a new workbook containing only the copied worksheet. For instance:

import xlwings as xw
wb = xw.Book("source.xlsx")
source_sheet = wb.sheets["SourceSheet"]
source_sheet.api.Copy()

After executing this, a new Excel workbook will open with a worksheet named “SourceSheet” that is a duplicate of the original. This is particularly useful for exporting specific sheets to separate files for distribution or analysis.

When copying between different workbooks, you need to reference the target workbook’s worksheets for the Before or After parameters. For example, to copy “Sheet1” from one workbook and place it after “SheetA” in another workbook:

import xlwings as xw
wb1 = xw.Book("workbook1.xlsx")
wb2 = xw.Book("workbook2.xlsx")
sheet1 = wb1.sheets["Sheet1"]
sheeta = wb2.sheets["SheetA"]
sheet1.api.Copy(After=sheeta.api)

How to use Worksheet.ClearCircles in the xlwings API way

The ClearCircles method of the Worksheet object in Excel is used to remove all circles that have been applied to cells via data validation error alert circles. These circles typically appear when data validation rules are violated, visually indicating invalid entries in a worksheet. The method is particularly useful for cleaning up the visual interface after data validation errors have been corrected or when preparing a sheet for presentation or further processing. It operates on the entire worksheet, affecting all cells within it.

In xlwings, the ClearCircles method can be accessed through a Worksheet object. The syntax is straightforward, as it does not require any parameters. The method is called directly on the worksheet instance.

Syntax:

worksheet.api.ClearCircles()

Here, worksheet is an xlwings Worksheet object representing the target sheet. The .api attribute provides access to the underlying Excel object model, allowing direct invocation of the native ClearCircles method. No arguments are needed, as the action applies to all circles from data validation errors on that specific worksheet.

Example:
Consider a scenario where a worksheet contains data validation rules, such as restricting input in column A to whole numbers between 1 and 10. If a user enters text like “abc” in cell A5, Excel may display a red circle around that cell (depending on error alert settings). To programmatically remove all such circles after reviewing and correcting the data, you can use the following xlwings code:

import xlwings as xw

# Connect to the active Excel instance or open a workbook
app = xw.App(visible=False) # Set to True if you want to see Excel
wb = app.books.open('example.xlsx')
sheet = wb.sheets['Sheet1']

# Assume data validation errors exist and circles are visible
# Clear all circles from data validation errors
sheet.api.ClearCircles()

# Save and close
wb.save()
wb.close()
app.quit()

How to use Worksheet.ClearArrows in the xlwings API way

The ClearArrows member of the Worksheet object in the Excel object model is accessible through the xlwings library, a powerful tool for automating Excel with Python. This method specifically targets the removal of tracer arrows within a worksheet. Tracer arrows are visual aids in Excel that help users understand formula dependencies and precedents by drawing arrows from cells that provide data to the cells that use that data (dependents) or from cells that are referenced by a formula (precedents). The ClearArrows method is essential for cleaning up the worksheet view, removing these arrows to declutter the interface, especially after auditing formulas or during the preparation of a final report.

In the xlwings API, the ClearArrows method is called directly on a Worksheet object. The syntax is straightforward, as the method does not require any parameters. It corresponds to the ClearArrows method in the Excel VBA object model, which clears all tracer arrows on the specified worksheet.

Syntax:

worksheet.api.ClearArrows()

Here, worksheet is an xlwings Worksheet object. The .api property provides direct access to the underlying Excel object model, allowing you to call native Excel methods like ClearArrows. This method clears all tracer arrows—both precedent and dependent arrows—from the active sheet. There are no parameters to specify, making its usage simple and direct.

Code Example:
The following xlwings code demonstrates how to use the ClearArrows method. It assumes you have an Excel workbook open and a specific worksheet selected.

import xlwings as xw

# Connect to the active Excel instance
app = xw.apps.active

# Specify the workbook (use the active workbook or open by name)
wb = app.books.active # or app.books['YourWorkbook.xlsx']

# Specify the worksheet by name or index
ws = wb.sheets['Sheet1'] # or wb.sheets[0]

# Clear all tracer arrows on the worksheet
ws.api.ClearArrows()

print("All tracer arrows have been cleared from the worksheet.")

How to use Worksheet.CircleInvalid in the xlwings API way

The CircleInvalid method in the Worksheet object is a useful feature for data validation and error checking in Excel. When applied, it draws red circles around cells that contain data failing any validation rules set for those cells. This visual cue helps users quickly identify and correct invalid entries, enhancing data integrity. In xlwings, this method can be accessed through the api property, which provides direct access to the underlying Excel object model, allowing for seamless integration of Excel’s native functionalities into Python scripts.

The syntax for using CircleInvalid in xlwings is straightforward: worksheet.api.CircleInvalid(). This method does not take any parameters, as it simply applies the circling effect to all cells in the worksheet that currently violate validation rules. It is important to note that this method is a member of the Excel VBA Worksheet object, and xlwings bridges this by exposing it via the api attribute. Before calling CircleInvalid, ensure that data validation rules are properly set in the Excel worksheet, as the method relies on these rules to determine invalid cells. The circles are drawn based on the active validation criteria, and they can be removed by using the ClearCircles method if needed.

For example, consider a scenario where you have an Excel worksheet with a column for age entries, and you’ve set a data validation rule to only allow values between 0 and 120. If some cells contain ages outside this range, you can use xlwings to circle those invalid entries. Here’s a code instance:

import xlwings as xw

# Connect to the active Excel instance or open a workbook
app = xw.App(visible=True) # Set visible=False for background operation
wb = app.books.open('example.xlsx') # Replace with your file path
ws = wb.sheets['Sheet1'] # Specify the worksheet name

# Apply data validation rule (if not already set in Excel)
# Note: xlwings does not directly set validation; ensure it's pre-configured in Excel.
# For demonstration, assume validation is already applied in the worksheet.

# Circle invalid cells based on existing validation rules
ws.api.CircleInvalid()

# To remove the circles after correction, you could use:
# ws.api.ClearCircles()

# Save and close if needed
wb.save()
wb.close()
app.quit()

How to use Worksheet.CheckSpelling in the xlwings API way

The CheckSpelling member of a Worksheet object in Excel provides a programmatic way to initiate a spell check on the text within that specific worksheet. In xlwings, this functionality is exposed through the api property, which grants direct access to the underlying Excel object model. This is particularly useful for automating document review processes or for integrating spell-checking into larger data validation and reporting workflows. The method checks the spelling of words in the worksheet’s cells, leveraging the same dictionaries and custom dictionaries used by Excel’s native spell-check feature.

Syntax in xlwings:
The method is accessed via the worksheet’s api object. Its full syntax in the Excel object model is complex, but the xlwings call typically uses the most common parameters.

worksheet.api.CheckSpelling(CustomDictionary, IgnoreUppercase, AlwaysSuggest, SpellLang)

• CustomDictionary (Optional, String): The file name of the custom dictionary to be used if the word is not found in the main dictionary. If omitted, the currently specified dictionary is used.
• IgnoreUppercase (Optional, Boolean): True to have Excel ignore words in all uppercase letters (e.g., “USA”). False to check them. If omitted, the current application setting is used.
• AlwaysSuggest (Optional, Boolean): True to have Excel display a list of suggestions for misspelled words. False to just check spelling without suggestions. If omitted, the current application setting is used.
• SpellLang (Optional, Variant): The language of the dictionary to use. This can be a language ID (LCID). It’s often omitted to use the application’s default language.

In practice, when called without arguments, it starts the interactive spell-check dialog, just like pressing F7 in Excel.

Code Examples:

1. Basic Spell Check (Interactive Dialog):
   This code opens the specified workbook, activates the first worksheet, and starts the standard Excel spell-check dialog, pausing the script until the user closes it.

import xlwings as xw
# Connect to an open workbook or open a new one
app = xw.App(visible=True)
wb = app.books.open('report.xlsx')
ws = wb.sheets[0]
# Start the interactive spell check
ws.api.CheckSpelling()
# ... other operations can follow after the dialog is closed
wb.save()
wb.close()
app.quit()

2. Spell Check with Specific Parameters:
   This example performs a spell check that ignores words in all caps and uses a specific custom dictionary file. Note that the AlwaysSuggest parameter might not prevent the dialog from appearing if misspellings are found, depending on Excel’s version and settings.

import xlwings as xw
from pathlib import Path

custom_dict_path = str(Path.home() / 'custom.dic')
with xw.App(visible=False) as app:
wb = app.books.add()
ws = wb.sheets[0]
# Populate some cells with text for testing
ws.range('A1').value = "This is a testt for spellling."
ws.range('A2').value = "NASA and HTML are acronyms."

# Check spelling, ignoring uppercase words, using a custom dict
# The method likely returns True if no errors were found, False otherwise.
check_passed = ws.api.CheckSpelling(CustomDictionary=custom_dict_path,
IgnoreUppercase=True,
AlwaysSuggest=False)
print(f"Spell check passed without errors: {check_passed}")
# In a non-visible app, the dialog may not appear; the return value is key.