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