Archive

How to use Worksheet.Protection in the xlwings API way

The Protection member of a Worksheet object in xlwings provides a way to control and query the protection settings of a worksheet. This is essential for securing data by preventing unauthorized users from modifying cells, formatting, or other elements. Through xlwings, you can access the protection properties to check the current protection status, apply protection with specific options, or unprotect the sheet if needed. It’s a powerful feature for automating the security aspects of Excel workbooks in Python.

Functionality
The Protection object allows you to:

• Protect a worksheet to restrict editing.
• Unprotect a worksheet to allow edits.
• Check if a worksheet is currently protected.
• Customize protection settings, such as allowing users to select locked cells, format cells, insert rows, etc.

Syntax
In xlwings, you access the Protection member via the api property to interact with the underlying Excel object model. The typical syntax is:

sheet.protection # This returns the Protection object

To protect a worksheet:

sheet.api.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

To unprotect a worksheet:

sheet.api.Unprotect(Password)

To check protection status:

sheet.api.ProtectContents # Returns True if the worksheet is protected

Parameters and Values
The Protect method has multiple optional parameters that control what users can do. Here are key parameters and their meanings:

Parameter	Type	Description	Typical Values
Password	String	A password to protect the sheet (optional).	Any string, e.g., “mypass123”
DrawingObjects	Boolean	Protects drawing objects (shapes).	True or False
Contents	Boolean	Protects cell contents (locked cells).	True or False
UserInterfaceOnly	Boolean	If True, protection applies only via UI, not via code.	True or False
AllowFormattingCells	Boolean	Allows formatting of cells.	True or False
AllowInsertingRows	Boolean	Allows inserting rows.	True or False
AllowSorting	Boolean	Allows sorting.	True or False
AllowFiltering	Boolean	Allows filtering.	True or False

For a full list, refer to the Excel VBA documentation, as xlwings passes these directly to Excel.

Code Examples
Here are practical examples using xlwings to work with worksheet protection:

1. Protecting a worksheet with a password and specific allowances:

import xlwings as xw

# Connect to an existing workbook and sheet
wb = xw.Book('example.xlsx')
sheet = wb.sheets['Sheet1']

# Protect the sheet with a password, allowing formatting and sorting
sheet.api.Protect(Password="secret123", AllowFormattingCells=True, AllowSorting=True)
print("Worksheet protected.")

2. Unprotecting a worksheet:

# Unprotect the sheet (if no password, omit the argument)
sheet.api.Unprotect("secret123")
print("Worksheet unprotected.")

3. Checking if a worksheet is protected:

# Check protection status
if sheet.api.ProtectContents:
    print("The worksheet is protected.")
else:
    print("The worksheet is not protected.")

4. Applying protection with multiple options:

# Protect without a password but allow various actions
sheet.api.Protect(
Password=None,
DrawingObjects=True,
Contents=True,
AllowInsertingRows=True,
AllowFiltering=True,
UserInterfaceOnly=False
)
print("Protection applied with custom settings.")

How to use Worksheet.ProtectDrawingObjects in the xlwings API way

The ProtectDrawingObjects property of a Worksheet object in Excel is a Boolean value that indicates whether the shapes (drawing objects) on the worksheet are protected. When a worksheet is protected using the Protect method, this property can be set to True to prevent users from modifying, moving, or deleting shapes such as charts, text boxes, and other drawing objects. It is particularly useful when you want to lock down the visual elements of a worksheet while still allowing data entry in cells, depending on the overall protection settings.

In xlwings, you can access this property through the api property of a Worksheet object, which provides direct access to the underlying Excel object model. The property is read/write, meaning you can both retrieve its current value and set it to a new value.

Syntax in xlwings:

worksheet.api.ProtectDrawingObjects

• worksheet: An xlwings Worksheet object representing the target worksheet.
• ProtectDrawingObjects: Returns or sets a Boolean value (True or False).
• True: Drawing objects are protected (locked) when the worksheet is protected.
• False: Drawing objects are not protected, even if the worksheet is protected.

Note that this property only takes effect when the worksheet is protected. If the worksheet is not protected, drawing objects remain editable regardless of this setting. To protect the worksheet, use the Protect method, such as worksheet.api.Protect().

Example Code:
Here is a practical example demonstrating how to use the ProtectDrawingObjects property with xlwings. This example assumes you have an Excel workbook open and a worksheet named “Sheet1”. It checks the current protection status for drawing objects, sets it to True, protects the worksheet, and then verifies the changes.

import xlwings as xw

# Connect to the active Excel application and workbook
app = xw.apps.active
wb = app.books.active
ws = wb.sheets['Sheet1']

# Check the current ProtectDrawingObjects setting
current_setting = ws.api.ProtectDrawingObjects
print(f"Current ProtectDrawingObjects setting: {current_setting}")

# Set ProtectDrawingObjects to True to protect shapes when the sheet is protected
ws.api.ProtectDrawingObjects = True

# Protect the worksheet to activate the drawing object protection
# You can specify a password and other options; here, we use default settings
ws.api.Protect(Password=None, DrawingObjects=True, Contents=True, Scenarios=True)
# Note: The 'DrawingObjects' parameter in the Protect method corresponds to ProtectDrawingObjects.

# Verify the protection status
if ws.api.ProtectDrawingObjects:
    print("Drawing objects are now protected on this worksheet.")
else:
    print("Drawing objects are not protected.")

# Optionally, unprotect the worksheet to make changes
ws.api.Unprotect()
ws.api.ProtectDrawingObjects = False
print("Worksheet unprotected and ProtectDrawingObjects set to False.")

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