Blog

How to use Worksheets.Add2 in the xlwings API way

The Worksheets.Add2 method in Excel’s object model is a powerful feature for creating new worksheets within a workbook. In xlwings, this method is accessible through the api property, which provides direct access to the underlying Excel object model. The Add2 method is an enhanced version of the traditional Add method, offering additional parameters for more control over the sheet creation process, such as specifying the sheet type. It is particularly useful for automating the generation of reports, dashboards, or data logs in Excel workbooks.

Syntax in xlwings:
The xlwings API call for the Add2 method follows this general format:
workbook.api.Worksheets.Add2(Before, After, Count, Type)

• Before (optional): A Worksheet object that specifies the sheet before which the new sheet will be added. If omitted, the new sheet is added after all existing sheets.
• After (optional): A Worksheet object that specifies the sheet after which the new sheet will be added. If both Before and After are omitted, the new sheet is added as the last sheet.
• Count (optional): An Integer that specifies the number of sheets to add. The default is 1.
• Type (optional): An XlSheetType constant that specifies the sheet type. Common values include:
• xlWorksheet (default): A standard worksheet.
• xlChart: A chart sheet.
• xlExcel4MacroSheet: A macro sheet (for compatibility).
• xlExcel4IntlMacroSheet: An international macro sheet.

Example Usage:
Here are several xlwings API code examples demonstrating the use of the Worksheets.Add2 member:

1. Adding a single worksheet at the end of the workbook:

import xlwings as xw
wb = xw.Book() # Open a new workbook
new_sheet = wb.api.Worksheets.Add2()
new_sheet.Name = "DataSummary"

2. Adding a worksheet before a specific sheet:

import xlwings as xw
wb = xw.Book('report.xlsx')
target_sheet = wb.sheets['Sheet1']
new_sheet = wb.api.Worksheets.Add2(Before=target_sheet.api)
new_sheet.Name = "Introduction"

3. Adding multiple chart sheets after a specific sheet:

import xlwings as xw
wb = xw.Book('data.xlsx')
after_sheet = wb.sheets['RawData']
# Add two chart sheets
chart_sheets = wb.api.Worksheets.Add2(After=after_sheet.api, Count=2, Type=-4109) # -4109 is xlChart
chart_sheets.Item(1).Name = "Chart1"
chart_sheets.Item(2).Name = "Chart2"

4. Using constants for sheet types (requires importing win32com.client or similar):

import xlwings as xw
from win32com.client import constants
wb = xw.Book()
new_chart_sheet = wb.api.Worksheets.Add2(Type=constants.xlChart)
new_chart_sheet.Name = "AnalysisChart"

How to use Worksheets.Add in the xlwings API way

The Add member of the Worksheets object in the Excel object model is a method used to create a new worksheet. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model (via pywin32 on Windows or appscript on macOS). This allows you to programmatically add sheets to a workbook, offering control over the sheet’s position and name.

Functionality
The primary purpose of the Add method is to insert a new worksheet into a workbook. You can specify where the new sheet should be placed relative to existing sheets and what its name should be. This is essential for automating report generation, data organization, or creating dynamic dashboards where the number of sheets may vary based on the data.

Syntax in xlwings
The general syntax using xlwings is:
workbook.api.Worksheets.Add(Before, After, Count, Type)

The parameters are:

• Before (Optional, Variant): A worksheet object that specifies the sheet before which the new sheet will be added. You cannot use both Before and After.
• After (Optional, Variant): A worksheet object that specifies the sheet after which the new sheet will be added. You cannot use both Before and After.
• Count (Optional, Variant): The number of new worksheets to add. The default value is 1.
• Type (Optional, Variant): The type of sheet to add. Can be xlWorksheet (value -4167) for a standard worksheet or xlChart (value -4109) for a chart sheet. The default is xlWorksheet.

To use a parameter, you typically pass a worksheet object (e.g., wb.sheets['Sheet1'].api) for Before or After, or an integer for Count. If both Before and After are omitted, the new sheet is added before the active sheet.

Code Examples

1. Add a single worksheet with a default name (e.g., “Sheet4”):

import xlwings as xw
wb = xw.Book() # Opens a new workbook
new_sheet = wb.api.Worksheets.Add()
# The new worksheet object is now in 'new_sheet'

2. Add a worksheet after a specific sheet and rename it:

import xlwings as xw
wb = xw.Book('Report.xlsx')
# Add new sheet after the sheet named "Data"
new_sheet = wb.api.Worksheets.Add(After=wb.sheets['Data'].api)
new_sheet.Name = "Summary" # Rename the new sheet

3. Add multiple worksheets at the beginning of the workbook:

import xlwings as xw
wb = xw.Book()
first_sheet = wb.sheets[0].api # Get the API object of the first sheet
# Add 3 new sheets before the first sheet
wb.api.Worksheets.Add(Before=first_sheet, Count=3)

4. Add a chart sheet at the end of the workbook:

import xlwings as xw
from xlwings.constants import ChartType
wb = xw.Book()
last_sheet = wb.sheets[-1].api # Get the API object of the last sheet
# Add a chart sheet after the last worksheet
chart_sheet = wb.api.Worksheets.Add(After=last_sheet, Type=ChartType.xlChart)
# Note: Chart sheets are a different object type than worksheets in the Excel model.

How to use Workbook.Parent in the xlwings API way

The Parent property of a Workbook object in the xlwings API serves a fundamental role in navigating the Excel object hierarchy. It returns the parent object of the current workbook, which is typically the Application object representing the entire Excel instance. This property is read-only and is primarily used for object model traversal, allowing you to access higher-level application settings or other workbooks within the same Excel instance.

Functionality:
The main purpose of the Parent property is to provide a reference to the application that contains the workbook. This is useful when you need to perform operations at the application level, such as modifying Excel-wide settings (e.g., Application.ScreenUpdating), accessing other open workbooks via Application.Workbooks, or retrieving application properties like the version. It establishes a clear parent-child relationship where the workbook is a child of the application.

Syntax:
In xlwings, the syntax for accessing the Parent property is straightforward, as it follows the standard attribute access pattern in Python. The property does not accept any parameters.

workbook_parent = workbook_object.parent

• workbook_object: This is a required variable representing an instance of a workbook, typically obtained by using xw.Book() or through the books collection.
• The return value is an xlwings.main.App object, which is xlwings’ wrapper for the Excel Application object.

Code Examples:

1. Basic Access and Type Verification:
   This example demonstrates how to get the parent of an active workbook and check its type.

import xlwings as xw

# Connect to the active Excel instance and workbook
app = xw.apps.active
wb = app.books.active

# Get the parent of the workbook
parent_app = wb.parent

# Verify it is the same application object
print(f"Workbook's parent is the same as 'app': {parent_app is app}")
# Output: Workbook's parent is the same as 'app': True
print(f"Parent object type: {type(parent_app)}")
# Output: Parent object type: <class 'xlwings.main.App'>

2. Using the Parent to Control Application Settings:
   A common use case is to use the workbook’s parent to toggle application-level properties for performance optimization during a script.

import xlwings as xw

wb = xw.Book("Financial_Model.xlsx")
excel_app = wb.parent # Get the Application object

# Disable screen updating and alerts for faster execution
excel_app.screen_updating = False
excel_app.display_alerts = False

# ... Perform data processing or formatting operations on the workbook ...

# Re-enable screen updating and alerts
excel_app.screen_updating = True
excel_app.display_alerts = True
wb.save()

3. Accessing Other Workbooks via the Parent:
   This example shows how you can use the parent application to iterate through or access other workbooks that are currently open.

import xlwings as xw

# Start with a specific workbook
current_wb = xw.Book("Report_Q1.xlsx")
app = current_wb.parent

# Print the names of all other open workbooks
print("Other open workbooks in the same Excel instance:")
for other_wb in app.books:
    if other_wb is not current_wb: # Avoid listing the current workbook
    print(f" - {other_wb.name}")

# You can now activate or manipulate another workbook, e.g.:
# target_wb = app.books["Data_Source.xlsx"]

How to use Workbook.Item in the xlwings API way

The Item member of the Workbook object in the Excel object model is a property that provides access to individual worksheets or charts within a workbook by their index number or name. In xlwings, this functionality is not exposed through an explicit Item property as in VBA, but is instead accessed directly through the workbook’s indexing or via methods like .sheets[]. This design offers a more Pythonic and intuitive way to retrieve specific sheets, aligning with common Python container behaviors.

Functionality
The primary function is to return a single Sheet object (which can be a Worksheet or Chart object) from the Sheets collection of a workbook. This allows for targeted operations on a specific sheet, such as reading data, writing values, or formatting cells, without needing to activate or select it first.

Syntax & Parameters
In xlwings, you access a sheet by its index or name using the sheets property of a Book object (xlwings’ equivalent of a Workbook). The syntax is:

sheet = wb.sheets[index_or_name]

• wb: The xlwings Book object instance.
• index_or_name: This parameter can be:
• An int representing the sheet’s position (1-based index). For example, 1 refers to the first sheet tab from the left.
• A str representing the exact name of the sheet as it appears on its tab.

Unlike the VBA Item property, xlwings does not use a separate property call; the indexing is performed directly on the sheets collection.

Code Examples
Here are practical examples demonstrating how to use this functionality in xlwings:

1. Accessing a sheet by its index:

import xlwings as xw
# Connect to an existing workbook (ensure Excel is open or use app.books.open)
app = xw.App(visible=False)
wb = app.books.open(r'C:\path\to\your\workbook.xlsx')
# Access the first worksheet in the workbook
first_sheet = wb.sheets[1]
# Read a value from cell A1 of the first sheet
value_a1 = first_sheet.range('A1').value
print(value_a1)
wb.close()
app.quit()

2. Accessing a sheet by its name:

import xlwings as xw
# Start a new instance of Excel and create a new workbook
app = xw.App()
wb = app.books.add()
# Rename the first sheet for demonstration
wb.sheets[0].name = 'SalesData'
# Access the sheet by its exact name
sales_sheet = wb.sheets['SalesData']
# Write a value to cell B5
sales_sheet.range('B5').value = 'Quarterly Revenue'
# Save and close
wb.save('report.xlsx')
wb.close()
app.quit()

3. Iterating through all sheets using the collection:
   While the Item concept is for single access, you can loop through the sheets collection, which is the parent of the Item.

import xlwings as xw
wb = xw.Book('data_workbook.xlsx')
# Print the name of every sheet in the workbook
for sheet in wb.sheets:
    print(sheet.name)
    # You could perform operations on each 'sheet' object here
wb.close()

How to use Workbook.Creator in the xlwings API way

The Creator property of the Workbook object in Excel’s object model is a read-only attribute that returns a 32-bit integer representing the application that originally created the workbook. This value is a unique identifier, often used to distinguish between workbooks created by different versions of Excel or other applications that can generate Excel files, such as older Mac versions or third-party software. In xlwings, this property can be accessed directly from a Workbook instance, providing compatibility information that can be useful for debugging, version control, or conditional logic in automation scripts.

Syntax in xlwings:
workbook_instance.creator
This property does not accept any parameters. It returns an integer value. The meaning of specific integer values is not publicly documented by Microsoft in a comprehensive list, but common values include:

• 1480803660 (hex: 0x5843454C): Typically indicates the workbook was created by a version of Excel for Windows.
• 1480803660 (hex: 0x5843434D): Often associated with Excel for Mac.
  Other values may correspond to different creation sources.

Example Usage:
Suppose you have an Excel workbook and you want to check its origin before performing specific operations. You can use xlwings to retrieve the Creator value and act accordingly. Here’s a practical code example:

import xlwings as xw

# Open an existing workbook
wb = xw.Book('example.xlsx')

# Access the Creator property
creator_value = wb.creator

# Display the result
print(f"The workbook's creator code is: {creator_value}")

# Conditional logic based on the creator
if creator_value == 1480803660: # Common code for Excel Windows
    print("This workbook was likely created by Excel for Windows.")
elif creator_value == 1480803660: # Note: This is an example; actual Mac codes may vary
    print("This workbook may have been created by Excel for Mac.")
else:
    print("The creator application is unknown or from a different source.")

# You can also use it in automation, e.g., to log workbook origins
with open('workbook_log.txt', 'a') as log_file:
log_file.write(f"Workbook: {wb.name}, Creator Code: {creator_value}\n")

# Close the workbook if needed
wb.close()

How to use Workbook.Count in the xlwings API way

The Count property of the Workbook object in Excel’s object model is accessible through the xlwings library in Python, providing a straightforward way to retrieve the number of open workbooks in the current Excel application instance. This property is particularly useful for automation scripts that need to monitor or manage multiple workbooks dynamically, such as in scenarios involving batch processing, data consolidation, or application state checks. By using Count, developers can programmatically determine how many workbooks are active, enabling conditional logic based on this count—for example, to ensure that a specific number of workbooks are open before proceeding with operations or to iterate through all open workbooks for uniform modifications.

In xlwings, the Count property is accessed through the books collection of the App object, which represents the Excel application. The syntax for retrieving the count is as follows:

import xlwings as xw

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

# Get the number of open workbooks
workbook_count = app.books.count

Here, app refers to an instance of the Excel application (either retrieved via xw.apps.active or created anew), and app.books represents the collection of all open workbooks within that application. The count property is a read-only integer that returns the total number of workbooks in the collection. It does not accept any parameters, and its value is dynamically updated as workbooks are opened or closed during the session. This property is essential for scripts that require awareness of the workbook environment, ensuring robust error handling and efficient resource management.

For example, consider a scenario where you need to close all workbooks except the first one. The Count property can be used to determine how many workbooks are open and to loop through them appropriately:

import xlwings as xw

# Connect to Excel
app = xw.apps.active

# Check the number of open workbooks
if app.books.count > 1:
    # Keep the first workbook open and close the rest
    for i in range(app.books.count - 1, 0, -1):
    app.books[i].close()
    print(f"Closed {app.books.count - 1} workbooks.")
else:
    print("Only one workbook is open.")

In this code, app.books.count is used to verify if multiple workbooks are open. If so, it iterates backward through the workbooks collection to close all but the first one, preventing index errors that might occur if iterating forward while removing items. Another common use case is to log the count for auditing purposes, such as in automated reporting systems:

import xlwings as xw
import logging

logging.basicConfig(level=logging.INFO)
app = xw.apps.active
workbook_count = app.books.count
logging.info(f"Number of open workbooks: {workbook_count}")

# Proceed only if at least one workbook is open
if workbook_count == 0:
    raise ValueError("No workbooks are open. Please open a workbook and try again.")

How to use Workbook.Application in the xlwings API way

The Application member of a Workbook object in xlwings provides a gateway to the overarching Excel application instance. This is a powerful property because it allows you to control Excel-wide settings, access other open workbooks, and interact with application-level features directly from a specific workbook context. Essentially, Workbook.app (or the full property Workbook.application) returns the main App object to which the workbook belongs, enabling you to scale your automation from a single file to the entire Excel environment.

Functionality:
The primary function is to retrieve the parent App object. Through this App object, you can:

• Control Excel application settings (e.g., DisplayAlerts, ScreenUpdating, Calculation).
• Access all open workbooks via the App.books collection.
• Create new workbooks or open existing ones at the application level.
• Quit the Excel application entirely.

Syntax:
The access is straightforward as it is a property.

app_instance = my_workbook.app
# or equivalently
app_instance = my_workbook.application

• my_workbook: A xlwings Book object (the Python representation of an Excel Workbook).
• app_instance: The returned xlwings App object. This object has its own set of properties and methods.

Code Examples:

1. Accessing Application Properties from a Workbook:
   This example shows how to disable screen updating and alerts via the App object retrieved from a specific workbook, perform an operation, and then restore the settings.

import xlwings as xw

# Connect to an existing workbook
wb = xw.Book("Report.xlsx")

# Get the parent Excel Application
excel_app = wb.app

# Configure application-wide settings
excel_app.screen_updating = False
excel_app.display_alerts = False

# Perform operations (e.g., add a new sheet)
new_sheet = wb.sheets.add("NewData")

# Restore settings
excel_app.screen_updating = True
excel_app.display_alerts = True

2. Listing All Open Workbooks:
   Using the App object from one workbook to interact with others.

import xlwings as xw

wb1 = xw.Book("Financials.xlsx")
app = wb1.app

print("Workbooks currently open in this Excel instance:")
for open_wb in app.books:
    print(f" - {open_wb.name}")

3. Creating a New Workbook in the Same Instance:
   Ensures a new workbook is created in the same Excel application window as your current workbook.

import xlwings as xw

source_wb = xw.Book("SourceData.xlsx")
app = source_wb.app

# Create a new workbook in the same Excel instance
new_wb = app.books.add()
new_wb.sheets[0].range("A1").value = "Report Generated from " + source_wb.name

How to use Workbook.OpenXML in the xlwings API way

In Excel object model, the OpenXML property of the Workbook object provides a way to access the underlying Open XML representation of a workbook. This is particularly useful for developers who need to perform custom XML manipulations, such as reading or modifying specific parts of the workbook’s structure that are not directly exposed through the standard Excel object model. The OpenXML property returns a string that contains the raw XML data of the workbook in the Office Open XML format, enabling advanced automation and integration scenarios. In xlwings, this functionality can be accessed via the api property, which exposes the native Excel object model.

The syntax for accessing the OpenXML property in xlwings is straightforward. Since xlwings uses the underlying COM object model, you can call it directly on a workbook object. The property does not take any parameters and returns a string. Here’s the basic format:

workbook.openxml

In this syntax, workbook refers to an xlwings Book object that represents an open workbook. The openxml property is accessed through the api attribute to get the native Excel Workbook object’s OpenXML property. Note that this property is read-only in the context of xlwings, meaning you can retrieve the XML data but not set it directly through this property. To modify the XML, you would typically use additional libraries like openpyxl or lxml to parse and edit the string, then save it back if needed.

For example, consider a scenario where you need to extract custom XML data from an Excel workbook to analyze metadata or embedded schemas. Using xlwings, you can open a workbook and retrieve its Open XML representation. Here’s a code instance:

import xlwings as xw

# Open an existing workbook
wb = xw.Book('example.xlsx')

# Access the OpenXML property via the api
openxml_data = wb.api.OpenXML

# Print or process the XML data (first 500 characters for brevity)
print(openxml_data[:500])

# You can also save the XML to a file for further analysis
with open('workbook_openxml.xml', 'w', encoding='utf-8') as f:
    f.write(openxml_data)

# Close the workbook if needed
wb.close()

How to use Workbook.OpenText in the xlwings API way

The OpenText method in the Workbook object is a powerful feature for importing and parsing text files directly into Excel using xlwings. It allows for automated data ingestion from various delimited text formats, such as CSV or tab-separated files, into a structured Excel workbook. This method is particularly useful for data analysts and developers who need to streamline workflows by eliminating manual import steps. By leveraging xlwings, users can programmatically control the import process, specifying parameters like delimiters, data types, and starting cell positions to ensure data is correctly formatted upon entry.

Syntax and Parameters:
In xlwings, the OpenText method is accessed through a Workbook object. The basic API call follows this format:
workbook.api.OpenText(Filename, ...)
Here, workbook refers to an xlwings Book object, and .api provides access to the underlying Excel object model. The method requires the Filename parameter, which is a string specifying the path to the text file. Additional optional parameters can be set to customize the import. Key parameters include:

• Origin: Specifies the file origin (e.g., xlWindows for Windows).
• StartRow: The row number at which to start importing data (default is 1).
• DataType: Sets column data types, using constants like xlGeneralFormat for general data.
• TextQualifier: Defines the text qualifier character, such as xlTextQualifierDoubleQuote.
• ConsecutiveDelimiter: A Boolean indicating whether consecutive delimiters should be treated as one.
• Tab, Semicolon, Comma, Space, Other: Boolean parameters to set the delimiter type, with Other allowing a custom delimiter via OtherChar.
• FieldInfo: An array specifying detailed parsing for each column, including data types and delimiters.

For example, to import a comma-delimited file with specific settings, you might set Comma=True and DataType=xlTextFormat for text columns. The FieldInfo parameter is often provided as a list of tuples, where each tuple corresponds to a column and includes a column number and data type constant.

Code Example:
Below is an xlwings Python code snippet demonstrating the use of OpenText to import a CSV file. This example assumes Excel is running and a workbook is active:

import xlwings as xw

# Connect to the active Excel instance and workbook
app = xw.apps.active
wb = app.books.active

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

# Use OpenText to import the file with custom settings
wb.api.OpenText(Filename=file_path,
Origin=xw.constants.xlWindows,
StartRow=1,
DataType=xw.constants.xlDelimited,
TextQualifier=xw.constants.xlTextQualifierDoubleQuote,
ConsecutiveDelimiter=False,
Comma=True,
FieldInfo=[(1, xw.constants.xlGeneralFormat),
(2, xw.constants.xlTextFormat),
(3, xw.constants.xlMDYFormat)])

# Save the workbook with the imported data
wb.save(r'C:\Data\output.xlsx')

How to use Workbook.OpenDatabase in the xlwings API way

The OpenDatabase method of the Workbook object in Excel is a powerful feature for connecting to and retrieving data from external databases directly into an Excel workbook. This method enables you to establish a connection to a database source (such as Microsoft Access, SQL Server, or other ODBC-compliant databases) and execute SQL queries to import data. In xlwings, this functionality is accessible through the Workbook object’s API, allowing you to automate database queries and data integration within your Python scripts. It is particularly useful for automating reports, dashboards, and data analysis tasks that require fresh data from corporate databases.

Syntax in xlwings:
The OpenDatabase method can be called on a Workbook object. The basic syntax in xlwings is:

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

Parameters:

• Connection: A string specifying the connection string to the database. This includes details like the data source provider, server name, database name, and authentication credentials.
• CommandText: A string that defines the SQL query or command to execute, such as “SELECT * FROM SalesData”.
• CommandType: An optional parameter that specifies the command type. Common values are xlCmdSQL (default for SQL queries) or xlCmdTable (for direct table access). In xlwings, you can use Excel constants like win32c.xlCmdSQL (on Windows) or their numeric equivalents (e.g., 1 for xlCmdSQL).
• BackgroundQuery: An optional boolean parameter (True or False) that determines if the query runs in the background. If True, Excel allows other operations while the query executes.
• ImportDataAs: An optional parameter that specifies how to import the data. For example, you can use win32c.xlPivotTableReport to create a PivotTable or win32c.xlTable for a standard table. The default is to import as a simple range.

Example:
Below is an xlwings code example that demonstrates using OpenDatabase to import data from a Microsoft Access database into an Excel workbook. This example assumes you have an Excel file open via xlwings and a valid Access database file.

import xlwings as xw
import win32com.client

# Connect to the active Excel workbook
wb = xw.books.active

# Define the connection string for an Access database (adjust the path as needed)
connection_str = "ODBC;DSN=MS Access Database;DBQ=C:\\Path\\To\\Your\\Database.accdb;"

# Define the SQL query
sql_query = "SELECT * FROM Orders WHERE OrderDate >= #2023-01-01#"

# Call OpenDatabase via the Excel API
# Note: We use wb.api to access the underlying Workbook object from Excel's object model
wb.api.OpenDatabase(
Connection=connection_str,
CommandText=sql_query,
CommandType=win32com.client.constants.xlCmdSQL, # Use constant for SQL command
BackgroundQuery=False, # Run query in foreground
ImportDataAs=win32com.client.constants.xlTable # Import as a table
)

# Optional: Save the workbook to persist the data
wb.save()