Archive

How to use Workbooks.Add in the xlwings API way

The Add member of the Workbooks object in the Excel object model is a method used to create a new, empty workbook. In xlwings, this functionality is accessed through the xlwings.Book() constructor, which internally leverages the Add method when creating a new workbook without opening an existing file. This is a fundamental operation for automating report generation, data processing workflows, or any task that requires starting with a fresh Excel file programmatically.

Functionality
The primary purpose is to instantiate a new Excel workbook. This new workbook becomes the active workbook in the Excel application and contains a default number of worksheets (typically one, depending on Excel’s default settings). It provides a clean slate for subsequent operations like data entry, formatting, or chart creation.

Syntax and Parameters
In xlwings, you do not call Add directly on a Workbooks collection. Instead, you create a new Book object. The equivalent action is performed with the following syntax:

import xlwings as xw
new_workbook = xw.Book()

This constructor corresponds to the VBA Workbooks.Add() method. The xlwings Book() constructor can also accept a template argument to create a workbook based on an existing template file.

• template (optional, string): The full path to an Excel template file (.xltx, .xltm). If provided, the new workbook is created as a copy of this template. If omitted, a new blank workbook is created based on the default workbook template.

Code Examples

1. Creating a Blank Workbook:
   This is the most straightforward use case. The code below starts Excel (if not already running), creates a new workbook, and returns a Book object linked to it.

import xlwings as xw

# Create a new blank workbook
wb = xw.Book()
print(f"New workbook created: {wb.name}")

# Add data to the first worksheet
wb.sheets[0].range('A1').value = "Sample Data"
wb.sheets[0].range('A2').value = 100

# Save the workbook
wb.save(r'C:\path\to\NewReport.xlsx')
wb.close()

2. Creating a Workbook from a Template:
   This is useful for standardized reports where formatting, headers, or specific sheet structures are pre-defined in a template file.

import xlwings as xw

# Path to your template file
template_path = r'C:\templates\Monthly_Report_Template.xltx'

# Create a new workbook based on the template
wb = xw.Book(template_path)
print(f"Workbook created from template: {wb.name}")

# The new workbook inherits all sheets and formatting from the template.
# You can now populate it with data.
wb.sheets['Data'].range('B5').value = "Q4 Results"
# ... additional data processing ...

# Save it as a regular workbook
wb.save(r'C:\reports\Monthly_Report_November.xlsx')

How to use Application.Worksheets in the xlwings API way

In the Excel object model, the Worksheets collection is a crucial component under the Application object, representing all worksheets within a workbook. Through xlwings, a powerful Python library for Excel automation, developers can programmatically access and manipulate these worksheets, enabling dynamic data analysis, reporting, and visualization. The Worksheets member provides methods to add, delete, reference, and iterate over sheets, making it essential for tasks like batch processing or creating dashboards.

Functionality: The Worksheets collection allows you to manage worksheets in an Excel workbook. You can retrieve a specific sheet by name or index, add new sheets, count the total number of sheets, and perform operations across multiple sheets. This is particularly useful for automating repetitive tasks, such as consolidating data from multiple sources or generating standardized reports.

Syntax: In xlwings, the Worksheets collection is accessed via the api property of a workbook or application object. The general syntax is:

• app.worksheets: Returns a collection of all worksheets in the active workbook.
• app.worksheets[index]: Accesses a worksheet by its index (1-based).
• app.worksheets["SheetName"]: Accesses a worksheet by its name.
• app.worksheets.add(): Adds a new worksheet to the workbook.
• app.worksheets.count: Returns the number of worksheets.

Parameters for methods like add() include:

• before: Specifies the sheet before which the new sheet is added (can be a sheet object or index).
• after: Specifies the sheet after which the new sheet is added.
• count: The number of sheets to add (default is 1).
  If not specified, the new sheet is added after all existing sheets.

Example Code: Below are practical xlwings API examples demonstrating the use of the Worksheets member:

1. Accessing Worksheets by Index and Name:

import xlwings as xw
app = xw.App(visible=False) # Start Excel in background
wb = app.books.open("example.xlsx")
# Access first worksheet
ws1 = wb.worksheets[0]
# Access worksheet by name
ws2 = wb.worksheets["DataSheet"]
print(f"First sheet name: {ws1.name}, DataSheet range A1: {ws2.range('A1').value}")
wb.close()
app.quit()

2. Adding and Counting Worksheets:

import xlwings as xw
app = xw.App(visible=True)
wb = app.books.add()
# Add a new sheet after the first one
new_sheet = wb.worksheets.add(after=wb.worksheets[0])
new_sheet.name = "Analysis"
# Count total worksheets
sheet_count = wb.worksheets.count
print(f"Total sheets: {sheet_count}")
# Save and close
wb.save("new_workbook.xlsx")
app.quit()

3. Iterating Over All Worksheets:

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open("data.xlsx")
# Loop through each worksheet and print names
for ws in wb.worksheets:
    print(f"Processing sheet: {ws.name}")
    # Example: Clear content from column A
ws.range("A:A").clear()
wb.save()
app.quit()

4. Deleting a Worksheet:

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open("report.xlsx")
# Delete a sheet by name
if "TempSheet" in [sheet.name for sheet in wb.worksheets]:
    wb.worksheets["TempSheet"].delete()
wb.save()
app.quit()

How to use Application.WorksheetFunction in the xlwings API way

The Application object in Excel’s object model provides access to a wide range of application-level settings and operations. One of its most powerful members is the WorksheetFunction property, which grants access to numerous Excel worksheet functions directly through code. In xlwings, this is exposed via the api property, allowing Python scripts to leverage Excel’s built-in functions programmatically. This capability is invaluable for performing complex calculations, statistical analysis, financial modeling, and more, without needing to reimplement these functions in Python. It bridges the gap between Excel’s robust functionality and Python’s scripting flexibility, enabling automation of sophisticated data processing tasks.

Functionality:
The WorksheetFunction member provides methods that correspond to Excel’s worksheet functions (e.g., VLookup, SumIf, NormDist, IRR). These methods can be used to execute calculations that are already optimized and tested within Excel. This is particularly useful when you need to ensure consistency with Excel’s calculations or when dealing with functions that are complex to implement from scratch.

Syntax:
In xlwings, you access WorksheetFunction through the api property of an app or workbook object. The general syntax is:

app.api.WorksheetFunction.FunctionName(arg1, arg2, ...)

• app: An instance of xlwings App (representing the Excel application).
• FunctionName: The name of the Excel function (e.g., VLookup, Average). Note that method names in WorksheetFunction may differ slightly from Excel’s function names (e.g., use NormDist instead of NORM.DIST). It’s advisable to check the Excel Object Model documentation for exact names.
• arg1, arg2, ...: Arguments for the function. These can be provided as values, cell references (as Range objects), or arrays. The number and type of arguments depend on the specific Excel function.

For many functions, arguments can be passed similarly to how they are in Excel. For example, for VLookup, the arguments are: lookup_value, table_array, col_index_num, range_lookup. In xlwings, table_array would typically be a Range object.

Example:
Here is a practical example using WorksheetFunction in xlwings to perform a VLookup and calculate the average of a range:

import xlwings as xw

# Connect to the active Excel instance or open a new one
app = xw.apps.active

# Access a specific workbook and sheet
wb = app.books['DataWorkbook.xlsx']
sheet = wb.sheets['Sheet1']

# Use WorksheetFunction.VLookup to find a value
lookup_value = "ProductA"
table_array = sheet.range("A2:B10") # Assuming column A has keys, column B has values
col_index = 2
range_lookup = False # Exact match
result_vlookup = app.api.WorksheetFunction.VLookup(lookup_value, table_array, col_index, range_lookup)
print(f"VLookup result: {result_vlookup}")

# Use WorksheetFunction.Average to compute the mean of a range
data_range = sheet.range("C2:C20")
average_value = app.api.WorksheetFunction.Average(data_range)
print(f"Average: {average_value}")

# Use a statistical function like NormDist (equivalent to NORM.DIST in Excel)
x = 1.5
mean = 1
standard_dev = 0.5
cumulative = True
norm_dist_result = app.api.WorksheetFunction.NormDist(x, mean, standard_dev, cumulative)
print(f"NormDist result: {norm_dist_result}")

How to use Application.Workbooks in the xlwings API way

The Workbooks member of the Application object in xlwings is a crucial property for managing Excel workbooks programmatically. It represents the collection of all open workbooks in an Excel session and provides methods to create, open, and access these workbooks. This allows for automation of tasks such as data consolidation, report generation, and batch processing across multiple files. Through xlwings, you can interact with this collection in a Pythonic way, leveraging the full power of Excel’s object model while maintaining clean and readable code.

In xlwings, the Application object is typically accessed via the app instance when you start an Excel application. The Workbooks property is then used to get the collection. The basic syntax to reference the Workbooks collection is:

import xlwings as xw

# Start or connect to an Excel application
app = xw.App(visible=True) # or xw.App() for a new instance, or xw.apps to access existing ones
workbooks_collection = app.books

Note: In xlwings, the Workbooks collection is accessed through app.books rather than app.Workbooks, following xlwings’ naming conventions for simplicity. The books property returns a Books collection object that you can use to perform operations.

Key methods and properties of the Books collection in xlwings include:

• add(): Creates a new workbook. You can optionally specify a template.
• open(): Opens an existing workbook from a file path.
• count: Returns the number of open workbooks (as an integer).
• active: Returns the active workbook (the one that is currently in focus).

Parameters for methods:

• For add(): The template parameter (optional) can be a string path to an Excel template file (e.g., .xltx). If omitted, a blank workbook is created.
• For open(): The fullname parameter (required) is the full path to the Excel file (e.g., 'C:/data/report.xlsx'). Additional optional parameters like read_only or password can be passed as keyword arguments for advanced control.

Here are practical examples using xlwings API to demonstrate the Workbooks member:

1. Creating a new workbook:

import xlwings as xw

app = xw.App(visible=True)
new_workbook = app.books.add() # Adds a blank workbook
new_workbook.save('C:/temp/new_file.xlsx') # Save it to a location
print(f"New workbook created with {new_workbook.sheets.count} sheets.")

2. Opening an existing workbook:

import xlwings as xw

app = xw.App(visible=False) # Run in background
existing_workbook = app.books.open('C:/data/sales_data.xlsx')
print(f"Opened workbook: {existing_workbook.name}")
# Perform operations, like reading data
data = existing_workbook.sheets[0].range('A1').value
print(f"Data from A1: {data}")
existing_workbook.close()
app.quit()

3. Iterating through all open workbooks:

import xlwings as xw

app = xw.App(visible=True)
# Open multiple workbooks for demonstration
wb1 = app.books.open('C:/data/file1.xlsx')
wb2 = app.books.open('C:/data/file2.xlsx')

print(f"Total open workbooks: {app.books.count}")
for wb in app.books:
    print(f" - {wb.name} (active: {wb is app.books.active})")
# Close all workbooks and quit
app.quit()

4. Using the active workbook:

import xlwings as xw

app = xw.App(visible=True)
app.books.open('C:/data/analysis.xlsx')
active_wb = app.books.active # Get the currently active workbook
if active_wb:
    active_wb.sheets[0].range('A1').value = "Updated via xlwings"
    active_wb.save()
    print(f"Active workbook saved: {active_wb.name}")
app.quit()

How to use Application.WindowState in the xlwings API way

The Application.WindowState property in Excel’s object model is a crucial feature for controlling the visual state of the Excel application window through xlwings. This property allows developers to programmatically maximize, minimize, or restore the main Excel window, which can enhance user experience by ensuring the application is presented in a desired state during automation tasks. In xlwings, this is accessed via the App object that represents the Excel application instance.

Functionality:
The WindowState property sets or returns the state of the Excel application window. It can be used to adjust the window to fit the screen, minimize it to the taskbar, or keep it in a normal restored state. This is particularly useful in scenarios where you want to hide the Excel interface during background processing or ensure it’s fully visible when presenting data.

Syntax in xlwings:
In xlwings, the property is accessed through the App object. The syntax is:

app.window_state

Where app is an instance of xlwings.App. This property can be both read and written. When setting the property, you assign it a string value that corresponds to the desired state. The possible values are:

• 'normal': The window is in a restored state (not maximized or minimized).
• 'maximized': The window is maximized to fill the screen.
• 'minimized': The window is minimized to the taskbar.

Examples:
Here are practical xlwings API code examples demonstrating the use of WindowState:

1. Maximizing the Excel Application Window:
   This example maximizes the Excel window to ensure it occupies the entire screen, which is helpful when displaying large datasets or dashboards.

import xlwings as xw
# Connect to an existing Excel instance or start a new one
app = xw.apps.active
# Maximize the application window
app.window_state = 'maximized'

2. Minimizing and Restoring the Window:
   This example minimizes the Excel window to the taskbar and then restores it to its normal state. This can be used to temporarily hide the interface during calculations.

import xlwings as xw
app = xw.App() # Start a new Excel application
# Minimize the window
app.window_state = 'minimized'
# Perform some background operations (e.g., data processing)
# Restore the window to normal state
app.window_state = 'normal'

3. Checking the Current Window State:
   You can also read the current state of the window to make conditional decisions in your script.

import xlwings as xw
app = xw.apps[0] # Access the first open Excel application
current_state = app.window_state
print(f"The current window state is: {current_state}")
if current_state == 'minimized':
    app.window_state = 'maximized' # Maximize if it was minimized

How to use Application.WindowsForPens in the xlwings API way

The Application.WindowsForPens property in the Excel object model is a legacy property primarily used to indicate whether the system is configured for pen-based input, such as with a stylus or tablet. In the context of xlwings, a powerful Python library for automating Excel, this property can be accessed to check the pen input settings of the Excel application. While its practical use in modern automation scripts is limited, it can be relevant for applications that need to adapt their interface or behavior based on the input device type.

Functionality:
This read-only property returns a Boolean value (True or False). A return value of True signifies that the Excel application is running on a system that is set up for pen input (e.g., Windows is configured to use a tablet or touch screen with pen support). A value of False indicates the system is not configured for such input. It can be used to conditionally enable or disable certain features in a macro or script that are optimized for pen interaction.

Syntax in xlwings:
In xlwings, you access this property through the app object, which represents the Excel Application. The syntax is straightforward:

app.api.WindowsForPens

Here, app is your xlwings App instance. The .api attribute provides direct access to the underlying Excel object model, allowing you to call the native WindowsForPens property. This property does not take any parameters.

Code Example:
The following xlwings code demonstrates how to check the WindowsForPens property and print the result. This example assumes you have an Excel instance running.

import xlwings as xw

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

# Access the WindowsForPens property via the .api attribute
is_pen_enabled = app.api.WindowsForPens

# Output the result
if is_pen_enabled:
    print("The system is configured for pen input.")
else:
    print("The system is not configured for pen input.")

# Alternatively, you can directly print the Boolean value
print(f"WindowsForPens value: {is_pen_enabled}")

How to use Application.Windows in the xlwings API way

The Application.Windows property in Excel’s object model provides a collection of all open workbook windows. In xlwings, this property is accessible through the app object, which represents the Excel application instance. It is particularly useful for programmatically managing and interacting with multiple workbook windows, such as iterating through them to perform actions like arranging, resizing, or closing windows based on specific conditions. This property is read-only and returns a Windows collection object, enabling developers to handle window-level operations efficiently within their automation scripts.

Syntax in xlwings:
app.api.Windows
Here, app is an instance of the xlwings App class, which connects to the Excel application. The .api attribute provides direct access to the underlying Excel object model, allowing you to use the Windows property. The returned collection can be indexed or iterated over, with each item representing a Window object corresponding to an open workbook window. For example, app.api.Windows[0] refers to the first window in the collection, typically the most recently activated window. Note that the order of windows in this collection may vary based on user interactions, so it’s advisable to reference windows by their Caption property (the window title) for more reliable access.

Example Usage:
Below is a practical xlwings code example that demonstrates how to use the Application.Windows property to list all open workbook windows and perform a simple action, such as arranging them in a tiled layout. This example assumes Excel is already running with multiple workbooks open.

import xlwings as xw

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

# Access the Windows collection via the .api attribute
windows = app.api.Windows

# Print the Caption (title) of each open window
print("Open workbook windows:")
for window in windows:
    print(f" - {window.Caption}")

# Arrange all windows in a tiled layout (Excel constant xlTiled = 1)
# This organizes windows side-by-side without overlapping
windows.Arrange(Style=1) # Style 1 corresponds to xlTiled

# Optionally, you can close a specific window by its Caption
# For instance, close a window titled "SalesData.xlsx"
for window in windows:
    if window.Caption == "SalesData.xlsx":
    window.Close()
    print("Closed SalesData.xlsx window.")
    break

# Note: The Arrange method affects only visible windows. Hidden or minimized windows may not be rearranged.

How to use Application.Width in the xlwings API way

The Width property of the Application object in the Excel object model controls the overall width of the Excel application window. In xlwings, this property is accessible through the api property, which provides direct access to the underlying Excel object model. This allows you to programmatically adjust the width of the Excel window, which can be useful for creating a consistent user interface, optimizing screen space during automated tasks, or ensuring that the application window fits specific display requirements.

Syntax in xlwings:

app.api.Width

• Get: The property is read-write, so you can retrieve the current width by simply accessing it.
• Set: Assign a new numeric value (in points) to change the width. The value must be a positive number.

The width is measured in points, where one point equals 1/72 of an inch. The maximum and minimum values depend on the user’s screen resolution and system settings, but Excel typically enforces practical limits to keep the window within the visible desktop area.

Example:
Here are practical examples of using the Width property with xlwings:

1. Getting the Current Application Window Width:

import xlwings as xw
app = xw.App(visible=True) # Start Excel application
current_width = app.api.Width
print(f"The current Excel window width is {current_width} points.")
app.quit() # Close the application

This code snippet starts Excel, reads the window width, prints it, and then closes Excel.

2. Setting the Application Window Width:

import xlwings as xw
app = xw.App(visible=True)
app.api.Width = 800 # Set the width to 800 points
print("Excel window width has been set to 800 points.")
# Keep the application open for observation
input("Press Enter to close Excel...")
app.quit()

Here, the width is explicitly set to 800 points, which can help standardize the window size for presentations or automated reports.

3. Adjusting Width Based on Screen Resolution:

import xlwings as xw
import tkinter as tk
app = xw.App(visible=True)
# Use tkinter to get screen width in pixels
root = tk.Tk()
screen_width_pixels = root.winfo_screenwidth()
root.destroy()
# Convert pixels to points (assuming 96 DPI: 1 pixel = 0.75 points)
width_in_points = screen_width_pixels * 0.75
app.api.Width = width_in_points # Set to half of screen width
print(f"Excel window width set to {width_in_points:.2f} points (half of screen width).")
app.quit()

This example demonstrates dynamic width adjustment by calculating half of the screen width in points, ensuring the Excel window adapts to different monitors.

4. Combining with Height for Full Window Control:

import xlwings as xw
app = xw.App(visible=True)
app.api.Width = 600
app.api.Height = 400 # Set height as well
print("Excel window resized to 600 points wide and 400 points tall.")
app.quit()

How to use Application.Watches in the xlwings API way

The Watches member of the Excel Application object in xlwings provides a programmatic way to manage and interact with the Watch Window feature in Excel. The Watch Window is a debugging and monitoring tool that allows users to track the values of specific cells or formulas across different worksheets and workbooks, updating in real-time as changes occur. Through the Watches collection in xlwings, developers can add, delete, or modify watches dynamically, enabling automation of data validation, error checking, or performance monitoring in complex Excel models.

In xlwings, the Watches collection is accessed via the Application object. The syntax for referencing it is straightforward: app.api.Watches, where app is an instance of the xlwings App class representing the Excel application. This returns a COM object that mirrors the VBA Watches collection, allowing access to its methods and properties. Key methods include Add, which creates a new watch, and Delete, which removes an existing one. The Add method requires parameters such as the source (a Range object) and optional arguments like the sheet name or workbook, which can be specified using xlwings range objects or Excel range addresses as strings. For example, to add a watch for cell A1 on the active sheet, you would use app.api.Watches.Add(app.range('A1').api). Properties like Count can be used to iterate through existing watches, and each watch item in the collection has properties such as Formula (the cell reference or formula being watched) and Value (the current value).

Here is a code example demonstrating the use of the Watches member in xlwings:

import xlwings as xw

# Connect to the active Excel instance or start a new one
app = xw.apps.active

# Add a watch for cell B5 on the first sheet of the active workbook
sheet = app.books.active.sheets[0]
watch_range = sheet.range('B5')
app.api.Watches.Add(watch_range.api)

# Check the number of watches currently in the Watch Window
watch_count = app.api.Watches.Count
print(f"Number of watches: {watch_count}")

# List all watches and their details
for i in range(1, watch_count + 1):
    watch = app.api.Watches.Item(i)
    print(f"Watch {i}: Formula = {watch.Formula}, Value = {watch.Value}")

# Delete a specific watch by index (e.g., the first watch)
if watch_count > 0:
    app.api.Watches.Item(1).Delete()

# Alternatively, delete all watches
app.api.Watches.Delete()

How to use Application.WarnOnFunctionNameConflict in the xlwings API way

The WarnOnFunctionNameConflict property of the Excel Application object is a setting that controls whether Excel displays a warning message when a user-defined function (UDF) in an add-in has the same name as a built-in Excel function. This is particularly relevant when working with custom functions created via VBA or other add-ins, as name conflicts can cause confusion or unexpected behavior. In xlwings, you can access and modify this property to manage how Excel handles such conflicts, ensuring a smoother integration of custom functionality.

Functionality:
When set to True, Excel will show a warning dialog if a function name conflict is detected. This alert informs the user that a custom function may override or be confused with a built-in one, allowing them to decide how to proceed. When set to False, no warning is issued, which can be useful in controlled environments where conflicts are intentional or managed. This property helps maintain clarity and prevent errors in spreadsheet calculations.

Syntax in xlwings:
In xlwings, you interact with this property through the app object, which represents the Excel application. The property is accessed as follows:

import xlwings as xw

app = xw.apps.active # Or xw.App() for a new instance
# Get the current value
current_setting = app.api.WarnOnFunctionNameConflict
# Set the value
app.api.WarnOnFunctionNameConflict = True # or False

The app.api provides direct access to the underlying Excel object model. The WarnOnFunctionNameConflict property is a Boolean value:

• True: Enables warnings for function name conflicts.
• False: Disables warnings.

Example Usage:
Suppose you are developing an add-in with custom functions and want to ensure users are alerted to potential conflicts. You can use xlwings to enable warnings dynamically. Below is a code example that checks the current setting, changes it to enable warnings, and then restores the original state after performing tasks.

import xlwings as xw

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

# Store the original setting
original_setting = app.api.WarnOnFunctionNameConflict
print(f"Original WarnOnFunctionNameConflict setting: {original_setting}")

# Enable warnings for function name conflicts
app.api.WarnOnFunctionNameConflict = True
print("Warnings enabled for function name conflicts.")

# Perform tasks that might involve custom functions, e.g., running a macro or adding an add-in
# For demonstration, we just wait a moment
import time
time.sleep(2)

# Restore the original setting
app.api.WarnOnFunctionNameConflict = original_setting
print(f"Restored WarnOnFunctionNameConflict to: {app.api.WarnOnFunctionNameConflict}")