How to use Worksheet.Hyperlinks in the xlwings API way

The Hyperlinks member of the Worksheet object in the Excel object model provides access to a collection of all hyperlinks on a specific worksheet. Through xlwings, this collection can be manipulated to add, modify, or retrieve hyperlinks programmatically, enabling dynamic linking to web pages, documents, email addresses, or other cells within the workbook. This functionality is essential for creating interactive and navigable Excel reports.

In xlwings, the Hyperlinks collection is accessed via the api property of a worksheet object. The syntax is ws.api.Hyperlinks, where ws is an xlwings Sheet object representing the worksheet. The Hyperlinks collection has several key methods, most notably Add. The Add method creates a new hyperlink and has the following parameters:

• Anchor: A required parameter specifying the range where the hyperlink will be placed. This is typically a Range object.
• Address: The target address of the hyperlink (e.g., a URL like “https://www.example.com”).
• SubAddress: An optional parameter for linking to a specific location within a file, such as a named range or a cell reference (e.g., “Sheet2!A1”).
• ScreenTip: Optional text that appears when the user hovers over the hyperlink.
• TextToDisplay: The optional visible text for the hyperlink. If omitted, the Address is displayed.

To retrieve an existing hyperlink, you can iterate through ws.api.Hyperlinks or access a specific one by index. Each hyperlink object in the collection has properties like Address, SubAddress, ScreenTip, and Range, which can be read or modified.

Below are practical xlwings code examples demonstrating the use of the Hyperlinks member:

import xlwings as xw

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

# Example 1: Add a hyperlink to a website in cell A1
link_range = ws.range('A1')
ws.api.Hyperlinks.Add(Anchor=link_range.api,
Address='https://www.python.org',
ScreenTip='Visit Python Website',
TextToDisplay='Python.org')

# Example 2: Add a hyperlink to another cell in the same workbook
ws.api.Hyperlinks.Add(Anchor=ws.range('B2').api,
Address='',
SubAddress='Sheet2!C5',
TextToDisplay='Go to Sheet2')

# Example 3: Loop through all hyperlinks on the worksheet and print their addresses
for link in ws.api.Hyperlinks:
    print(f"Hyperlink at {link.Range.Address}: {link.Address}")

# Example 4: Modify an existing hyperlink (e.g., the first one)
if ws.api.Hyperlinks.Count > 0:
    first_link = ws.api.Hyperlinks(1) # Index is 1-based
    first_link.Address = 'https://www.xlwings.org'
    first_link.TextToDisplay = 'xlwings Docs'

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