Archive

How to use Worksheet.ProtectContents in the xlwings API way

In the Excel object model, the ProtectContents property of a Worksheet object is a read-only Boolean property that indicates whether the contents (cells) of a worksheet are currently protected. When a worksheet is protected, users are typically restricted from modifying locked cells, and the ProtectContents property returns True. Conversely, if the worksheet is unprotected, it returns False. This property is useful for programmatically checking the protection status of a worksheet, allowing for conditional logic in automation scripts, such as only performing certain operations if the sheet is unprotected or alerting the user if protection is active.

The ProtectContents property corresponds to the ProtectContents attribute in the Excel object model. In xlwings, you can access this property through the api property of a sheet object, which provides direct access to the underlying Excel object model. The syntax for accessing ProtectContents in xlwings is as follows:

sheet.api.ProtectContents

Here, sheet is an xlwings Sheet object representing the worksheet. The api property exposes the native Excel VBA object model, so ProtectContents is called as a property without any parameters. It returns a Boolean value (True or False). Note that this property is read-only; to change the protection status, you would use methods like Protect or Unprotect on the worksheet object.

For example, to check if the active sheet is protected, you can use the following xlwings code:

import xlwings as xw

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

# Check the ProtectContents property
is_protected = sheet.api.ProtectContents

if is_protected:
    print("The worksheet is protected. Contents cannot be modified.")
else:
    print("The worksheet is unprotected. Contents can be modified.")

In this example, sheet.api.ProtectContents retrieves the protection status, and the script prints a message based on the result. This can be integrated into larger automation tasks, such as ensuring data integrity by preventing modifications on protected sheets or temporarily unprotecting a sheet to perform updates before re-protecting it.

Another practical use case is to loop through all worksheets in a workbook and report their protection status:

import xlwings as xw

# Open a workbook
wb = xw.books.active

for sheet in wb.sheets:
    status = "Protected" if sheet.api.ProtectContents else "Unprotected"
    print(f"Sheet '{sheet.name}': {status}")

How to use Worksheet.PrintedCommentPages in the xlwings API way

In the realm of Excel automation with Python, the xlwings library provides a powerful and Pythonic interface to interact with the Excel Object Model. One of the more specialized members within the Worksheet object is PrintedCommentPages. This property is particularly useful when dealing with document formatting and print management, as it allows developers to programmatically control how comments are handled during printing operations.

Functionality:
The PrintedCommentPages property of a Worksheet object in Excel determines the location where cell comments are printed. In Excel, comments (or notes) can be printed either at the end of the worksheet or as displayed on the sheet. This property is essential for generating reports or documents where the inclusion and placement of annotations are critical for clarity and reference. By accessing this property via xlwings, you can both retrieve the current setting and modify it to suit specific printing requirements, ensuring that printed outputs meet desired standards.

Syntax and Parameters:
In xlwings, the PrintedCommentPages property is accessed through a Worksheet object. The property corresponds to the Excel constant XlPrintLocation, which defines where comments are printed. The syntax is straightforward:

worksheet.api.PrintedCommentPages

This property is read-write, meaning you can both get its current value and set it to a new value. The value is an integer that corresponds to one of the following Excel enumeration constants:

Constant Name (Excel)	Value	Description
xlPrintNoComments	-4142	Comments are not printed.
xlPrintInPlace	16	Comments are printed as displayed on the sheet.
xlPrintSheetEnd	1	Comments are printed at the end of the sheet.

To use these constants in xlwings, you typically import them from the win32com.client.constants module if you are on Windows, or use their numeric values directly for cross-platform compatibility. However, xlwings often abstracts these constants, so you might use the numeric values or predefined constants if available in your environment.

Code Examples:
Below are practical examples demonstrating how to use the PrintedCommentPages property with xlwings.

1. Retrieving the Current Print Setting for Comments:
   This example shows how to get the current setting to understand where comments will be printed.

import xlwings as xw

# Open an existing workbook and select a worksheet
app = xw.App(visible=False)
workbook = app.books.open('example.xlsx')
sheet = workbook.sheets['Sheet1']

# Get the current PrintedCommentPages setting
current_setting = sheet.api.PrintedCommentPages
print(f"Current comment print setting: {current_setting}")

# Interpret the value
if current_setting == -4142:
    print("Comments will not be printed.")
elif current_setting == 16:
    print("Comments will be printed in place.")
elif current_setting == 1:
    print("Comments will be printed at the end of the sheet.")

workbook.close()
app.quit()

2. Setting the Print Location for Comments:
   This example changes the setting to print comments at the end of the sheet, which is useful for keeping the main content uncluttered.

import xlwings as xw

# Start Excel and open a workbook
app = xw.App(visible=False)
workbook = app.books.open('report.xlsx')
sheet = workbook.sheets['Data']

# Set to print comments at the end of the sheet
sheet.api.PrintedCommentPages = 1 # xlPrintSheetEnd

# Save and print preview (optional)
workbook.save()
# To see the effect, you might trigger a print preview or print directly
# app.api.ActiveSheet.PrintPreview()

print("Comment print setting updated to print at sheet end.")

workbook.close()
app.quit()

3. Dynamically Configuring Based on User Input:
   Here, the setting is adjusted based on a configuration parameter, making it adaptable to different reporting needs.

import xlwings as xw

def set_comment_printing(workbook_path, sheet_name, print_location):
"""
Set the comment printing location for a specified worksheet.

Parameters:
workbook_path (str): Path to the Excel workbook.
sheet_name (str): Name of the worksheet.
print_location (str): Desired print location ('none', 'inplace', 'end').
"""
    app = xw.App(visible=False)
    workbook = app.books.open(workbook_path)
    sheet = workbook.sheets[sheet_name]

    # Map user-friendly input to Excel constants
    location_map = {
    'none': -4142, # xlPrintNoComments
    'inplace': 16, # xlPrintInPlace
    'end': 1 # xlPrintSheetEnd
    }

    if print_location in location_map:
        sheet.api.PrintedCommentPages = location_map[print_location]
        print(f"Set comment printing to '{print_location}'.")
    else:
        print("Invalid print location. Using default.")

    workbook.save()
    workbook.close()
    app.quit()

# Example usage
set_comment_printing('financial_report.xlsx', 'Summary', 'end')

How to use Worksheet.Previous in the xlwings API way

The Previous property of a Worksheet object in Excel’s object model provides a convenient way to navigate to the preceding worksheet within the workbook, based on the physical tab order. 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 tasks that require sequential processing of sheets or for creating navigation macros.

Functionality:
The Previous property returns a Worksheet object representing the sheet immediately before the active or specified worksheet. If the current sheet is the first one, accessing Previous will return None. It is a read-only property.

Syntax:
In xlwings, you access this property via the api object. The general syntax is:

previous_sheet = ws.api.Previous

Where ws is an xlwings Sheet object (which corresponds to a Worksheet). The returned value is a COM object representing the previous worksheet. To integrate it seamlessly with xlwings, you typically wrap it with xlwings.Sheet.

Example:
Consider a workbook with three worksheets named “Data”, “Analysis”, and “Summary” in that order. The following xlwings code demonstrates how to use the Previous property.

import xlwings as xw

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

# Get the "Analysis" sheet
ws_analysis = wb.sheets['Analysis']

# Access the Previous property via the Excel object model
previous_excel_ws = ws_analysis.api.Previous

# Convert the Excel worksheet object to an xlwings Sheet object
if previous_excel_ws is not None:
    previous_sheet = xw.Sheet(previous_excel_ws)
    print(f"The previous sheet is: {previous_sheet.name}") # Output: The previous sheet is: Data
else:
    print("This is the first sheet.")

# Example: A practical loop to process sheets backwards from a given point
current_sheet = wb.sheets['Summary']
while True:
    prev_excel_ws = current_sheet.api.Previous
    if prev_excel_ws is None:
        break
    prev_sheet = xw.Sheet(prev_excel_ws)
    print(f"Processing: {prev_sheet.name}")
    # Perform your operations on prev_sheet here, e.g., read values
    data_range = prev_sheet.range('A1').expand('table')
    print(f"Data range size: {data_range.shape}")
    # Move to the previous sheet for the next iteration
    current_sheet = prev_sheet

How to use Worksheet.Parent in the xlwings API way

The Parent property of a Worksheet object in the Excel object model is a fundamental attribute that provides a reference to the workbook containing the worksheet. In xlwings, this property is accessible through the api property, which exposes the underlying Excel object model, allowing for direct interaction with Excel’s native features. Understanding and utilizing the Parent property is essential for navigating between worksheets and workbooks programmatically, especially when dealing with multiple workbooks or when you need to reference the parent workbook for operations such as saving, closing, or accessing other worksheets.

Functionality:
The primary function of the Parent property is to return the parent object of a worksheet, which is always a Workbook object in Excel. This enables developers to access workbook-level properties and methods from a worksheet instance, facilitating tasks like workbook management, cross-sheet references, and dynamic operations that depend on the workbook context.

Syntax:
In xlwings, you access the Parent property through the api property of a Worksheet object. The syntax is straightforward:

worksheet.api.Parent

Here, worksheet is an instance of a xlwings Worksheet object. The Parent property does not take any parameters and returns a COM object representing the parent workbook. You can then use this object to call workbook-specific methods or access properties.

Example Usage:
Consider a scenario where you have an Excel workbook with multiple worksheets, and you want to retrieve the name of the parent workbook from a specific worksheet. Using xlwings, you can achieve this as follows:

import xlwings as xw

# Connect to the active workbook or open a specific one
app = xw.App(visible=False) # Run Excel in the background
workbook = app.books.open('example.xlsx') # Open a workbook
worksheet = workbook.sheets['Sheet1'] # Access a specific worksheet

# Access the Parent property to get the workbook object
parent_workbook = worksheet.api.Parent

# Retrieve the name of the parent workbook
workbook_name = parent_workbook.Name
print(f"The parent workbook name is: {workbook_name}")

# You can also perform workbook-level operations, such as saving
parent_workbook.Save()

# Close the workbook and quit Excel
workbook.close()
app.quit()

How to use Worksheet.PageSetup in the xlwings API way

The PageSetup member of the Worksheet object in xlwings provides comprehensive control over printing settings, allowing users to configure page layout, margins, orientation, scaling, headers, footers, and other print-related properties programmatically. This is essential for generating professional reports and ensuring printed documents match specific formatting requirements. Through xlwings, you can access these settings using the api property to interact with the underlying Excel object model, enabling automation of print preparation tasks directly from Python.

Functionality:
The PageSetup object is used to manage all aspects of page setup for printing a worksheet. Key functionalities include setting page orientation (portrait or landscape), adjusting margins (top, bottom, left, right, header, footer), defining paper size, scaling printouts (by percentage or to fit a certain number of pages), and configuring headers and footers with text, page numbers, dates, or other elements. It also allows control over print titles, gridlines, and comments.

Syntax:
In xlwings, you access the PageSetup member via the worksheet object’s api property. The general syntax is:

worksheet.api.PageSetup.PropertyName
worksheet.api.PageSetup.MethodName(Arguments)

Where worksheet is an xlwings Worksheet object. Properties can be get or set, while methods perform actions. Common properties and methods include:

• Properties: Orientation, Zoom, FitToPagesTall, FitToPagesWide, TopMargin, BottomMargin, LeftMargin, RightMargin, HeaderMargin, FooterMargin, PaperSize, PrintTitleRows, PrintTitleColumns, PrintGridlines, PrintHeadings, LeftHeader, CenterHeader, RightHeader, LeftFooter, CenterFooter, RightFooter.
• Methods: PrintPreview(), PrintOut().

Parameters for methods like PrintOut() typically include: From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName. These can be specified as keyword arguments. Property values are often integers or strings; for example, Orientation can be set to 1 for portrait or 2 for landscape, and margins are in points.

Examples:
Here are xlwings API code instances demonstrating the use of PageSetup:

1. Setting Page Orientation and Scaling:

import xlwings as xw
wb = xw.Book('example.xlsx')
sheet = wb.sheets['Sheet1']

# Set landscape orientation
sheet.api.PageSetup.Orientation = 2 # 2 for landscape, 1 for portrait

# Set zoom to 80%
sheet.api.PageSetup.Zoom = 80

# Scale to fit to 1 page wide and 1 page tall
sheet.api.PageSetup.FitToPagesTall = 1
sheet.api.PageSetup.FitToPagesWide = 1

2. Adjusting Margins:

# Set margins in points (1 point = 1/72 inch)
sheet.api.PageSetup.TopMargin = 50
sheet.api.PageSetup.BottomMargin = 50
sheet.api.PageSetup.LeftMargin = 70
sheet.api.PageSetup.RightMargin = 70
sheet.api.PageSetup.HeaderMargin = 30
sheet.api.PageSetup.FooterMargin = 30

3. Configuring Headers and Footers:

# Set header and footer text
sheet.api.PageSetup.LeftHeader = "&LReport Date: &D"
sheet.api.PageSetup.CenterHeader = "&CSales Data"
sheet.api.PageSetup.RightHeader = "&RPage &P of &N"
sheet.api.PageSetup.LeftFooter = "&LConfidential"
sheet.api.PageSetup.CenterFooter = "&C&F"
sheet.api.PageSetup.RightFooter = "&RPrinted at &T"

Here, &L, &C, &R align left, center, right; &D is current date, &T is current time, &P is page number, &N is total pages, &F is file name.

4. Setting Print Titles and Gridlines:

# Set rows 1:1 as print titles
sheet.api.PageSetup.PrintTitleRows = "$1:$1"

# Print gridlines
sheet.api.PageSetup.PrintGridlines = True

# Print row and column headings
sheet.api.PageSetup.PrintHeadings = True

5. Printing the Worksheet:

# Preview print
sheet.api.PageSetup.PrintPreview()

# Print directly (optional parameters)
sheet.api.PageSetup.PrintOut(From=1, To=1, Copies=2, Preview=False, Collate=True)

How to use Worksheet.Outline in the xlwings API way

The Outline property of a Worksheet object in Excel provides access to the outlining (grouping and ungrouping) features for rows and columns on a sheet. Outlining allows you to collapse or expand sections of data, making it easier to manage and view large datasets by hiding detail rows or columns while showing summary information. In xlwings, you can control these outlining features programmatically through the api property, which exposes the underlying Excel object model.

Syntax and Parameters

In xlwings, you access the Outline property via the api property of a Worksheet object. The general syntax is:

worksheet.api.Outline

This returns an Outline object, which has several key methods and properties for managing outlines. The most commonly used methods include:

• ShowLevels(row_levels, column_levels): This method sets the outline levels to display.
• row_levels (optional): An integer specifying the row outline level to show. If omitted, the current row level is unchanged. Levels typically range from 1 (highest summary) to 8 (most detailed).
• column_levels (optional): An integer specifying the column outline level to show. If omitted, the current column level is unchanged.
• AutomaticStyles: A property that, when set to True, allows Excel to apply automatic styles to summary rows and columns. You can set it using worksheet.api.Outline.AutomaticStyles = True.
• SummaryRow and SummaryColumn: Properties that control the placement of summary rows and columns relative to the detail data. For example, xlAbove (or -4162 as a constant) places summaries above details, while xlBelow (or -4167) places them below. In xlwings, you can use constants from the xlwings.constants module or their numeric equivalents.

Code Examples

Here are practical examples using xlwings to manipulate worksheet outlines:

1. Setting Outline Levels: To collapse all rows to show only the top-level summary (level 1) and all columns to show full detail (level 8), you can use:

import xlwings as xw
wb = xw.Book("example.xlsx")
ws = wb.sheets["Sheet1"]
ws.api.Outline.ShowLevels(row_levels=1, column_levels=8)

2. Enabling Automatic Styles: To apply Excel’s automatic outlining styles for better visual distinction:

ws.api.Outline.AutomaticStyles = True

3. Configuring Summary Row Placement: To set summary rows to appear below the detail data (commonly used for subtotals), you can set the SummaryRow property. Using xlwings constants:

from xlwings.constants import xlBelow
ws.api.Outline.SummaryRow = xlBelow

Alternatively, with a numeric value:

ws.api.Outline.SummaryRow = -4167 # Equivalent to xlBelow

4. Grouping Rows Programmatically: While the Outline property itself doesn’t group data directly, you can use it in conjunction with Excel’s Range objects. For instance, to group rows 5 through 10 and apply outlining:

ws.range("5:10").api.Group()
# After grouping, you can control the outline level
ws.api.Outline.ShowLevels(row_levels=1)

How to use Worksheet.Next in the xlwings API way

In Excel’s object model, the Next property of a Worksheet object is a property that returns a Worksheet object representing the next sheet in the workbook. This is useful for programmatically navigating through worksheets in sequence without relying on specific sheet names. The Next property is part of the Excel interop and is accessible through xlwings, a Python library that allows you to automate Excel from Python. It is often used in loops or when you need to process multiple sheets in order.

The Next property is read-only and returns None if the current worksheet is the last sheet in the workbook. In xlwings, you can access this property via the api attribute, which provides direct access to the underlying Excel object model. This allows for seamless integration with Excel’s native functionality.

Functionality:
The primary function of the Next property is to retrieve the worksheet that immediately follows the current one in the workbook’s tab order. This can be helpful for tasks such as iterating through all sheets, comparing data between consecutive sheets, or performing batch operations across multiple worksheets. It simplifies navigation by avoiding the need to hardcode sheet indices or names.

Syntax:
In xlwings, the syntax for accessing the Next property is as follows:

next_worksheet = current_worksheet.api.Next

Here, current_worksheet is an xlwings Sheet object representing the active worksheet. The .api attribute exposes the native Excel VBA object model, allowing you to call properties like Next. The return value is an Excel Worksheet object, which can be wrapped in an xlwings Sheet object if needed for further operations. Note that if there is no next worksheet, the property returns None.

Parameters:
The Next property does not take any parameters. It is a simple property that relies on the workbook’s sheet order. The sheet order is determined by the position of the tabs in Excel, which can be changed manually by the user or programmatically via other methods.

Example Usage:
Below is a code example demonstrating how to use the Next property with xlwings to iterate through worksheets and print their names. This example assumes you have an Excel workbook open with multiple sheets.

import xlwings as xw

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

# Start with the first worksheet
current_sheet = wb.sheets[0]

# Loop through worksheets using the Next property
while current_sheet is not None:
print(f"Current sheet name: {current_sheet.name}")

# Get the next worksheet using the Excel object model
next_sheet = current_sheet.api.Next

if next_sheet is not None:
    # Wrap the Excel Worksheet object in an xlwings Sheet object
    current_sheet = xw.Sheet(next_sheet)
else:
    current_sheet = None

print("Finished iterating through all sheets.")

In this example, we start from the first sheet (index 0) and use a while loop to navigate through each subsequent sheet via the Next property. The loop continues until Next returns None, indicating the last sheet has been reached. This approach is efficient for sequential processing and ensures compatibility with Excel’s native behavior.

Another practical use case is to compare data between consecutive sheets. For instance, you might want to check if the values in cell A1 are the same across all sheets:

import xlwings as xw

wb = xw.books.active
current_sheet = wb.sheets[0]
reference_value = current_sheet.range('A1').value

while current_sheet is not None:
if current_sheet.range('A1').value != reference_value:
print(f"Mismatch found in sheet: {current_sheet.name}")

next_sheet = current_sheet.api.Next
if next_sheet is not None:
current_sheet = xw.Sheet(next_sheet)
else:
break

How to use Worksheet.Names in the xlwings API way

In Excel object model, the Names collection refers to all defined names within a workbook, including workbook-level and worksheet-level names. However, in xlwings, the Worksheet object does not have a direct Names property. Instead, you can access defined names via the Book (or Workbook) object. Specifically, you can use Book.names to retrieve all defined names in the workbook. To get or manage names that are scoped to a particular worksheet, you can filter the Book.names collection based on the name’s scope.

The primary functionality of the Names collection in xlwings is to create, read, update, or delete defined names, which are useful for referencing specific ranges, constants, or formulas in a workbook. This can simplify formulas, improve readability, and make your code more maintainable.

Syntax and Usage:
In xlwings, you interact with defined names through the Book.names property. Here’s the basic syntax:

• book.names: Returns a collection of all defined names in the workbook. Each item in the collection is a Name object.
• To access a specific name, you can use indexing or the get method: book.names['MyName'] or book.names.get('MyName').
• To create a new name, use book.names.add(name, refers_to), where name is the string identifier for the name, and refers_to is the formula or range it references (e.g., “=Sheet1!$A$1:$B$10”). You can specify the scope by including the worksheet name in the refers_to parameter or by setting properties after creation.

For worksheet-level names, you can filter by checking the name.scope property. For example, to get all names scoped to a specific worksheet, you can iterate through book.names and compare the scope. However, note that xlwings does not provide a direct Worksheet.names property, so this filtering is done manually.

Example Code:
Here’s a practical example using xlwings to work with defined names, focusing on a worksheet context:

import xlwings as xw

# Connect to an existing workbook or create a new one
wb = xw.Book('example.xlsx') # or xw.Book() for a new workbook
ws = wb.sheets['Sheet1']

# Add a worksheet-level defined name for a range in Sheet1
# The refers_to string includes the worksheet name to scope it
wb.names.add(name='MyRange', refers_to=f"={ws.name}!$A$1:$D$10")

# Access the defined name and print its details
my_name = wb.names['MyRange']
print(f"Name: {my_name.name}")
print(f"Refers to: {my_name.refers_to}")
print(f"Scope: {my_name.scope}") # This might return the workbook or worksheet, depending on setup

# List all defined names scoped to the specific worksheet (Sheet1)
worksheet_names = []
for name in wb.names:
    # Check if the name's scope matches the worksheet; note: scope may be a string or object
    if hasattr(name.scope, 'name') and name.scope.name == ws.name:
        worksheet_names.append(name.name)
    elif isinstance(name.scope, str) and name.scope == ws.name:
        worksheet_names.append(name.name)
        print(f"Names in {ws.name}: {worksheet_names}")

# Use the defined name in a formula or operation
# For example, set a value in the named range
ws.range('MyRange').value = [[1, 2, 3, 4] for _ in range(10)] # Fills the range with data

# Delete a defined name if needed
wb.names['MyRange'].delete()

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

How to use Worksheet.Name in the xlwings API way

The Name property of a Worksheet object in the Excel object model is a fundamental attribute used to get or set the name of a worksheet. In xlwings, this property is accessed directly through the Worksheet object, allowing for straightforward retrieval and modification of sheet names within a workbook. This capability is essential for tasks such as dynamically referencing sheets, organizing data across multiple sheets, or automating sheet management processes.

Functionality:
The primary function is to read or change the name of a worksheet. This is useful in scenarios where sheet names need to be updated based on data content, user input, or automated workflows. For example, you might rename sheets after importing data to reflect the dataset’s source or date.

Syntax in xlwings:
In xlwings, the Name property is accessed as an attribute of a Worksheet object. The syntax is simple:

• To get the current name: sheet_name = ws.name
• To set a new name: ws.name = "NewSheetName"
  Here, ws represents a Worksheet object obtained via wb.sheets['Sheet1'] or similar. The name must be a string and adhere to Excel’s naming conventions (e.g., no more than 31 characters, and characters like :, \, /, ?, *, [, ] are not allowed). If an invalid name is provided, xlwings will raise an error.

Code Examples:
Below are practical examples demonstrating the use of the Name property with xlwings.

1. Retrieving a Worksheet Name:
   This example opens an existing workbook and prints the name of the first worksheet.

import xlwings as xw
# Open an existing workbook
wb = xw.Book('example.xlsx')
# Access the first worksheet
ws = wb.sheets[0]
# Get and print the worksheet name
current_name = ws.name
print(f"The worksheet name is: {current_name}")

2. Renaming a Worksheet:
   This example renames a specific worksheet to “DataSummary”.

import xlwings as xw
# Open an existing workbook
wb = xw.Book('example.xlsx')
# Access a worksheet by its current name
ws = wb.sheets['Sheet1']
# Change the worksheet name
ws.name = "DataSummary"
# Save the workbook to persist changes
wb.save()

3. Dynamic Renaming Based on Content:
   This example renames all worksheets in a workbook based on a list of new names, demonstrating batch processing.

import xlwings as xw
# Open an existing workbook
wb = xw.Book('data.xlsx')
# Define new names for each worksheet
new_names = ["January", "February", "March"]
# Iterate through worksheets and rename them
for i, ws in enumerate(wb.sheets):
    if i < len(new_names):
        ws.name = new_names[i]
# Save the workbook
wb.save()

How to use Worksheet.MailEnvelope in the xlwings API way

The MailEnvelope property of a Worksheet object in Excel VBA is used to control email-related features when sending a worksheet via email. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model. The MailEnvelope object allows you to customize the email subject, recipients, and message body when using Excel’s built-in email integration, typically via the SendMail method. It is particularly useful for automating email reports directly from an Excel workbook.

Syntax and Parameters
In xlwings, you access the MailEnvelope property via the api property of a Worksheet object. The basic syntax is:

worksheet.api.MailEnvelope

This returns a MailEnvelope object, which has several key properties and methods. The most commonly used include:

• Subject: Sets or gets the email subject line as a string.
• To: Sets or gets the primary recipients as a string (multiple addresses can be separated by semicolons).
• CC: Sets or gets the carbon copy recipients as a string.
• BCC: Sets or gets the blind carbon copy recipients as a string.
• Introduction: Sets or gets the introductory text in the email body as a string. This text appears above the worksheet in the email.
• Item.Send(): Sends the email. Note that this method may require an email client (like Outlook) to be configured and running.

These properties are straightforward to set by assigning string values. For example, worksheet.api.MailEnvelope.Subject = "Monthly Report" sets the subject. The Introduction property is especially useful for adding descriptive text.

Code Example
Below is a practical xlwings example that sets up and sends a worksheet via email. This assumes you have an active workbook and an email client set up.

import xlwings as xw

# Connect to the active workbook
wb = xw.books.active
# Access the first worksheet
ws = wb.sheets[0]

# Access the MailEnvelope property via api
envelope = ws.api.MailEnvelope

# Set email properties
envelope.Subject = "Q4 Sales Data"
envelope.To = "manager@example.com; team@example.com"
envelope.CC = "supervisor@example.com"
envelope.Introduction = "Please find the attached Q4 sales report. Key highlights include a 15% increase in revenue."

# Optional: Save or update the workbook before sending
wb.save()

# Send the email
envelope.Item.Send()