Blog

How to use Workbooks.OpenXML in the xlwings API way

The OpenXML member of the Workbooks collection in Excel’s object model is a method that allows developers to open an Excel workbook from an XML file format, specifically targeting files in the Office Open XML format (such as .xlsx, .xlsm). This method is particularly useful when you need to programmatically load workbooks that are stored in this modern, XML-based format, ensuring compatibility and efficient handling of Excel 2007 and later file types. In xlwings, which provides a Pythonic interface to Excel’s COM automation, this functionality is accessed through the app.books.open() method, as xlwings abstracts the underlying COM methods like OpenXML into a more unified open function. However, understanding the original OpenXML method’s parameters helps in utilizing the xlwings equivalent effectively.

Functionality: The primary purpose is to open an Excel workbook from an Office Open XML file. It enables automation scenarios where workbooks are generated or stored as .xlsx files, and you need to manipulate them via Python scripts. This method ensures that the workbook is loaded correctly with all its components, such as worksheets, charts, and defined names, from the XML structure.

Syntax in xlwings: While xlwings does not expose a direct OpenXML method, it uses the open() method of the Books collection, which internally handles various file formats, including Open XML. The syntax is:
app.books.open(fullname)
Here, app is an instance of the xlwings App class representing an Excel application. The parameter fullname is a string specifying the full path and filename of the workbook to open (e.g., “C:\Data\report.xlsx”). This method corresponds to the VBA Workbooks.OpenXML method but simplifies it by not requiring explicit format parameters—xlwings automatically detects the file type based on the extension.

In the native Excel object model, OpenXML has additional parameters like LoadOption to control how the XML is loaded, but xlwings’ open() method abstracts these details. For advanced usage, you can pass other optional arguments supported by xlwings’ open() to mimic OpenXML behavior, such as update_links or read_only, though these are not XML-specific. For example, to open a workbook in read-only mode similar to using OpenXML with caution, you can set read_only=True.

Code Example:
Below is an xlwings API code instance that demonstrates opening an Open XML workbook using the open() method, which effectively utilizes the underlying OpenXML functionality. This example assumes Excel is running or will be launched.

import xlwings as xw

# Start or connect to an Excel application
app = xw.App(visible=True) # Set visible=False for background operation

# Open an Open XML file (.xlsx) using the books.open method
# This internally uses the OpenXML mechanism for .xlsx files
workbook_path = r"C:\Users\Example\Documents\budget.xlsx"
wb = app.books.open(workbook_path)

# Perform operations: e.g., read data from a specific cell
data = wb.sheets['Sheet1'].range('A1').value
print(f"Data from A1: {data}")

# Save any changes (if needed) and close the workbook
wb.save() # Optional: save changes
wb.close()

# Quit the Excel application
app.quit()

How to use Workbooks.OpenText in the xlwings API way

The OpenText member of the Workbooks object in the Excel object model is a method used to import and parse a text file into a new Excel workbook. This is particularly useful for automating the loading of data from delimited text files (like CSV or TSV) or fixed-width text files directly into Excel without manual intervention. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model, allowing precise control over the import process.

Syntax in xlwings:
The xlwings API call follows the pattern: xlwings.Book.api.OpenText(...). However, since OpenText is a method of the Workbooks collection, it is typically used to create a new workbook. In xlwings, you can access it via the Excel application object. The general syntax is:

app = xw.App(visible=False) # Create an invisible Excel instance
app.api.Workbooks.OpenText(Filename, ...)

The OpenText method has numerous parameters to customize the import. Key parameters include:

• Filename (required, String): The full path and name of the text file to import.
• Origin: Specifies the file origin (e.g., xlWindows for Windows or xlMacintosh for Mac). Often set to xlWindows (value 437) by default.
• StartRow (Long): The starting row for parsing (default is 1).
• DataType (XlTextParsingType): Sets how columns are parsed. Use xlDelimited (value 1) for delimited files (like CSV) or xlFixedWidth (value 2) for fixed-width files.
• TextQualifier (XlTextQualifier): Specifies the text qualifier character, such as xlTextQualifierDoubleQuote (value 1) for double quotes.
• ConsecutiveDelimiter (Boolean): True to treat consecutive delimiters as one.
• Tab, Semicolon, Comma, Space, Other, OtherChar: Boolean parameters to set delimiters. For example, set Comma=True for CSV files. If Other=True, specify the character in OtherChar.
• FieldInfo (Array): An array of arrays specifying the data type and width for each column. For delimited files, it often uses xlGeneralFormat (value 1). Example: [[1, 1], [2, 1]] sets the first two columns to general format.

Example:
Here is an xlwings API code example that imports a comma-delimited CSV file, treating consecutive commas as one delimiter, and starting from the first row:

import xlwings as xw

# Start Excel in the background
app = xw.App(visible=False)

# Define the text file path
file_path = r'C:\Data\sales.csv'

# Open the text file using OpenText
# Parameters: Filename, StartRow=1, DataType=xlDelimited, Comma=True, ConsecutiveDelimiter=True
workbook = app.api.Workbooks.OpenText(
Filename=file_path,
Origin=437, # xlWindows
StartRow=1,
DataType=1, # xlDelimited
TextQualifier=1, # xlTextQualifierDoubleQuote
ConsecutiveDelimiter=True,
Comma=True,
FieldInfo=[[1, 1], [2, 1], [3, 1]] # Set first three columns to general format
)

# Save the workbook as an Excel file
workbook.SaveAs(r'C:\Data\sales_imported.xlsx')
workbook.Close()
app.quit()

How to use Workbooks.OpenDatabase in the xlwings API way

The OpenDatabase member of the Workbooks object in the Excel object model is a method used to connect to and import data from an external database directly into Excel. This functionality is particularly valuable for automating data retrieval from sources like Microsoft Access, SQL Server, or other ODBC-compliant databases, enabling dynamic report generation and data analysis without manual copy-paste operations. In xlwings, this method provides a programmatic way to execute such database queries through Excel’s engine, leveraging its native data connection capabilities.

Syntax and Parameters

In xlwings, the method is accessed via the api property of an Excel App or Book object. The typical call pattern is:

workbook.api.OpenDatabase(Connection, CommandText, CommandType, BackgroundQuery, ImportDataAs)

The parameters map closely to the VBA Workbooks.OpenDatabase method. Below is a detailed breakdown:

Parameter	Description	Typical Values / How to Specify
Connection	A string that defines the connection to the database. This includes the data source and any necessary credentials.	For an Access database: "DSN=MS Access Database;DBQ=C:\\path\\database.accdb;". For SQL Server: "ODBC;DSN=MyServerDSN;UID=user;PWD=password;".
CommandText	The SQL query string or the name of a table, query, or stored procedure to run.	"SELECT * FROM SalesData" or "TableName".
CommandType	Specifies the type of command in CommandText.	Use Excel constants: xlwings.constants.xlCmdTable (default for table names), xlwings.constants.xlCmdSql (for SQL strings).
BackgroundQuery	A boolean that determines if the query runs asynchronously.	True for background (asynchronous) query, False (default) for foreground.
ImportDataAs	Defines how the returned data is placed.	A Workbook object or xlwings.constants.xlPTTable. Often set as the workbook itself: wb.api for a new workbook, or a specific Range object like sheet.range('A1').api to import to a specific location.

Important Notes on xlwings Usage:

• The method is called on the api property because OpenDatabase is a method of the underlying COM object (Excel’s VBA object model).
• You often need to import xlwings.constants to use the Excel constants for parameters like CommandType.
• The Connection string must be correctly formatted for your specific database provider (ODBC, OLE DB). Incorrect strings are a common source of errors.

Code Examples

1. Basic Example: Importing a Table from Microsoft Access into a New Workbook
   This example opens a connection to an Access database and imports an entire table named “Customers”.

import xlwings as xw
import xlwings.constants as xl

# Launch Excel app
app = xw.App(visible=True)
# Create a new, empty workbook
wb = app.books.add()

# Define the connection string (adjust the DBQ path)
connection_str = "DSN=MS Access Database;DBQ=C:\\Data\\MyDatabase.accdb;"

# Use the OpenDatabase method on the workbook's API
# This imports the 'Customers' table into the active sheet starting at cell A1
wb.api.OpenDatabase(
Connection=connection_str,
CommandText="Customers",
CommandType=xl.xlCmdTable,
BackgroundQuery=False,
ImportDataAs=wb.sheets.active.range('A1').api
)

2. Example with SQL Query and Importing to a Specific Location
   This example runs a custom SQL query on a SQL Server database via an ODBC DSN and places the results into a specific range on a sheet named “Report”.

import xlwings as xw
import xlwings.constants as xl

# Connect to an existing workbook
wb = xw.Book("Monthly_Report.xlsx")
report_sheet = wb.sheets["Report"]
target_range = report_sheet.range("B5")

# Define ODBC connection string (DSN must be pre-configured on the system)
connection_str = "ODBC;DSN=MyCompanySQLServer;UID=analyst;PWD=secure_pwd;"

# Define the SQL command
sql_query = """
SELECT Region, Product, SUM(Sales) AS TotalSales
FROM SalesTransactions
WHERE TransactionDate >= '2024-01-01'
GROUP BY Region, Product
ORDER BY Region, TotalSales DESC
"""

# Execute the query
wb.api.OpenDatabase(
Connection=connection_str,
CommandText=sql_query,
CommandType=xl.xlCmdSql, # Explicitly state it's a SQL command
BackgroundQuery=True, # Run in the background to not block Excel
ImportDataAs=target_range.api
)

# Optional: Wait for the background query to complete if needed
# while report_sheet.api.QueryTables(1).Refreshing:
# xw.time.sleep(0.1)

How to use Workbooks.Open in the xlwings API way

The Open member of the Workbooks object in xlwings is a fundamental method for automating Excel file operations. It allows you to programmatically open an existing Excel workbook, making it available for further manipulation, such as reading data, writing values, or applying formatting. This is the primary way to interact with workbooks that are not created within the current script session.

Functionality
The primary function is to load an Excel workbook file from disk into the Excel application (whether running visibly or in the background). Once opened, the workbook becomes part of the Workbooks collection, and you can reference it to access its worksheets, ranges, and other properties. This is essential for any automation task that starts with an existing template or data file.

Syntax
In xlwings, the Open method is accessed through the main App instance, which represents the Excel application. The syntax is:

app.books.open(fullpath, ...)

Where app is your xlwings App object (e.g., xw.App() or xw.apps.active). The method returns a Book object representing the opened workbook.

Key Parameters
While xlwings abstracts many of the underlying Excel object model details, the open method provides access to several important parameters from the native Excel Workbooks.Open method. The most commonly used ones in xlwings are:

• fullpath (str, required): The complete file path to the Excel workbook you want to open.
• update_links (bool or int, optional): Specifies how links in the workbook are updated. You can pass True to update external references (links), False to not update them, or use integer constants (like 0, 1, 2, 3) for more control as defined in the Excel object model (e.g., 0 = xlUpdateLinksNever).
• read_only (bool, optional): Opens the workbook in read-only mode if set to True.
• password (str, optional): The password required to open a protected workbook.
• write_res_password (str, optional): The password required for write access to a write-reserved workbook.

For a complete list, consult the xlwings documentation which mirrors the VBA object model parameters.

Code Examples

1. Basic Open:
   Opens a workbook from a specified path.

import xlwings as xw
app = xw.App(visible=True) # Start Excel
wb = app.books.open(r'C:\Reports\Q1_Data.xlsx')
print(f"Opened: {wb.name}")
# ... perform operations ...
wb.close()
app.quit()

2. Open with Read-Only and Password:
   Opens a protected workbook in read-only mode.

import xlwings as xw
app = xw.App(visible=False) # Excel runs in background
wb = app.books.open(r'C:\Secure\Budget.xlsx', read_only=True, password='mypass123')
data = wb.sheets['Summary'].range('A1').value
print(data)
wb.close()
app.quit()

3. Open Without Updating Links:
   Useful when the workbook contains links to external sources that are unavailable or should not be refreshed.

import xlwings as xw
# Attach to an already running instance of Excel
app = xw.apps.active
wb = app.books.open(r'\\Server\Archive\MasterFile.xlsx', update_links=False)
# Process data without attempting to update broken links
wb.save()
# No need to close the app if it was already open

4. Open and Assign to a Variable for Manipulation:
   Demonstrates a common pattern for data processing.

import xlwings as xw
with xw.App(visible=False) as app:
source_wb = app.books.open(r'C:\Data\Source.xlsx')
source_sheet = source_wb.sheets[0]
raw_data = source_sheet.range('A1:D100').value

# Process data (e.g., clean, filter)
processed_data = [row for row in raw_data if row[0] is not None]

# Write to a new workbook or another sheet
output_wb = app.books.add()
output_wb.sheets[0].range('A1').value = processed_data
output_wb.save(r'C:\Data\Output.xlsx')
# Workbooks are automatically closed when the 'with' block exits and the app quits.

How to use Workbooks.Close in the xlwings API way

The Close member of the Workbooks object in Excel’s object model is used to close one or all open workbooks. In xlwings, this functionality is accessed through the books collection, which corresponds to the Workbooks object. The Close operation is essential for managing resources, ensuring data is saved properly before exiting, and automating workbook lifecycle tasks in scripts. It allows for closing a specific workbook or all workbooks with options to save changes or discard them.

Syntax and Parameters:
In xlwings, the Close method is called on a workbook instance or the books collection. The basic syntax is:

• For a specific workbook: workbook.close()
• For all workbooks: xlwings.books.close()

The method can accept parameters to control saving behavior, though xlwings often handles this implicitly. In the underlying Excel object model, the Close method for Workbook objects has parameters like SaveChanges, FileName, and RouteWorkbook. In xlwings, these are typically managed through context or by setting workbook properties before closing. For example, you can save a workbook before closing with workbook.save() or close without saving by setting workbook.saved = True to mark it as saved. The Close method in xlwings does not directly expose all Excel parameters but integrates with Python’s workflow.

Key considerations:

• If changes exist and no save action is taken, Excel may prompt the user (in interactive mode), which can disrupt automation. To avoid this, ensure workbooks are saved or marked as saved before closing.
• When closing all workbooks via xlwings.books.close(), xlwings will iterate through open workbooks and close them, applying save logic based on each workbook’s state.

Code Examples:
Here are practical examples using xlwings to demonstrate the Close member:

1. Closing a specific workbook after saving:

import xlwings as xw
# Open an existing workbook
wb = xw.Book('example.xlsx')
# Perform operations, such as writing data
wb.sheets[0].range('A1').value = 'Test Data'
# Save and close the workbook
wb.save()
wb.close()

2. Closing a workbook without saving changes:

import xlwings as xw
wb = xw.Book('example.xlsx')
wb.sheets[0].range('A1').value = 'Temporary Data'
# Mark the workbook as saved to prevent save prompts
wb.saved = True
wb.close() # Closes without saving the changes

3. Closing all open workbooks with a loop, handling save based on condition:

import xlwings as xw
# Open multiple workbooks
wb1 = xw.Book('file1.xlsx')
wb2 = xw.Book('file2.xlsx')
# Process data...
# Close all workbooks, saving only if needed
for book in xw.books:
    if book.name == 'file1.xlsx':
        book.save() # Save specific workbook
        book.close()

4. Using a context manager to automatically close workbooks (recommended for resource management):

import xlwings as xw
with xw.Book('example.xlsx') as wb:
wb.sheets[0].range('A1').value = 'Data inside context'
# Workbook is automatically closed upon exiting the context

How to use Workbooks.CheckOut in the xlwings API way

The CheckOut member of the Workbooks object in Excel’s object model is accessible through the xlwings library, enabling Python scripts to programmatically check out a workbook from a SharePoint server or other document management server. This functionality is crucial in collaborative environments where files are stored on servers that support check-in/check-out mechanisms, allowing users to lock a file for editing, preventing conflicts.

Functionality:
The CheckOut method is used to open a workbook from a server in exclusive mode. When you check out a workbook, it is typically downloaded to your local machine, and other users are prevented from editing it until it is checked back in. This ensures data integrity and avoids version conflicts in team settings. In xlwings, this operation is performed via the underlying Excel application object, leveraging the full capabilities of Excel’s COM automation.

Syntax:
In xlwings, you access the CheckOut method through the app.books collection (which represents the Workbooks object). The syntax is as follows:

app.books.checkout(filename)

• filename (string, required): This parameter specifies the full path or URL of the workbook to check out. It must be a string that points to the workbook’s location on the server. For example, it could be a SharePoint URL like "https://sharepoint.example.com/sites/team/Shared Documents/report.xlsx" or a network path.

The method does not return a value but will raise an error if the checkout fails (e.g., if the file is already checked out, the path is invalid, or there are network issues).

Example:
Below is a practical xlwings code example that demonstrates how to use the CheckOut method to check out an Excel workbook from a SharePoint server, open it, make modifications, and then check it back in (note that checking in is done via Excel’s SaveAs or similar methods, often combined with server-specific commands, but xlwings primarily handles the checkout step).

import xlwings as xw

# Start or connect to an Excel application
app = xw.App(visible=True) # Set visible=False for background operation

# Define the server path or URL of the workbook
server_path = "https://sharepoint.example.com/sites/team/Shared Documents/budget.xlsx"

try:
    # Check out the workbook from the server
    app.books.checkout(server_path)
    print(f"Workbook checked out successfully from: {server_path}")

    # Open the checked-out workbook (it may open automatically in some cases, but    explicitly open it for safety)
    wb = app.books.open(server_path) # This opens the local checked-out version
    sheet = wb.sheets[0]

    # Perform data operations: for instance, update a cell with new data
    sheet.range("A1").value = "Updated Budget Data"
    sheet.range("B2").value = 15000

    # Save changes to the local checked-out workbook
    wb.save()

    # Optionally, check in the workbook back to the server using Excel's SaveAs or other methods
    # Note: xlwings does not have a direct CheckIn method; this often requires server integration or Excel's built-in features.
    # For demonstration, we simply close the workbook without checking in (leaving it checked out).
    wb.close()

except Exception as e:
    print(f"An error occurred: {e}")

finally:
    # Quit the Excel application
    app.quit()

How to use Workbooks.CanCheckOut in the xlwings API way

The CanCheckOut member of the Workbooks object in Excel is a property that determines whether a specified workbook can be checked out from a server, such as SharePoint or a network location, where document management features like check-in and check-out are enabled. This is particularly useful in collaborative environments to verify if a workbook is available for editing before attempting to check it out programmatically, helping to avoid errors or conflicts when multiple users might be accessing the file.

In the Excel Object Model, CanCheckOut is a property of the Workbook object, but it is accessed through the Workbooks collection when referring to a specific workbook. In xlwings, which provides a Pythonic way to interact with Excel via its API, this property can be used to check the check-out status. The property returns a Boolean value: True if the workbook can be checked out, and False otherwise. This is read-only, meaning you cannot set it directly; it depends on the workbook’s current state on the server.

The syntax for accessing CanCheckOut in xlwings involves using the api property to tap into the underlying Excel object model. Here’s the general format:

workbook_object.api.CanCheckOut

In this syntax:

• workbook_object refers to an xlwings Book object, which represents an Excel workbook. You typically obtain this by opening a workbook with xw.Book() or by referencing an active one.
• .api is used to access the native Excel object model, allowing you to call properties and methods like CanCheckOut directly.
• CanCheckOut is the property being accessed, with no parameters required. It returns a Boolean (True or False).

For example, to check if a workbook named “example.xlsx” located on a SharePoint server can be checked out, you would use the following xlwings code:

import xlwings as xw

# Open the workbook from a server path (e.g., SharePoint URL)
wb = xw.Book(r'https://your-sharepoint-site/example.xlsx')

# Check if the workbook can be checked out
can_checkout = wb.api.CanCheckOut

if can_checkout:
    print("The workbook can be checked out for editing.")
else:
    print("The workbook cannot be checked out, possibly because it is already checked out by another user or there are permissions issues.")

# Optionally, you can proceed to check out the workbook if allowed
if can_checkout:
    wb.api.CheckOut() # This checks out the workbook for editing
    print("Workbook has been checked out successfully.")
else:
    print("Skipping check-out due to unavailability.")

# Close the workbook if needed
wb.close()

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}")