Hyperlinks are a commonly used tool in Excel that facilitates navigation between different sheets, workbooks, websites, or even specific cells within a worksheet. There are instances where you may need to manage hyperlinks in Excel files, such as extracting hyperlinks for further analysis, modifying existing links, or removing them entirely. In this article, we will introduce how to extract, modify, and remove hyperlinks in Excel in Python using Spire.XLS for Python.

• Extract Hyperlinks from Excel in Python
• Modify Hyperlinks in Excel in Python
• Remove Hyperlinks from Excel in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Extract Hyperlinks from Excel in Python

Extracting hyperlinks from an Excel worksheet can be beneficial when you need to analyze or export the link data for further processing.

The following steps demonstrate how to extract hyperlinks from an Excel worksheet in Python using Spire.XLS for Python:

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[] property.
• Get the collection of all hyperlinks in the worksheet using Worksheet.HyperLinks property.
• Create an empty list to store the extracted hyperlink information.
• Loop through the hyperlinks in the hyperlink collection.
• Get the address of each hyperlink using XlsHyperlink.Address property and append the address to the list.
• Write the addresses in the list into a text file.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Hyperlinks.xlsx")

# Get the first worksheet of the file
sheet = workbook.Worksheets[0]

# Get the hyperlink collection of the worksheet
links = sheet.HyperLinks

# Create an empty list to store the extracted hyperlinks
list = []

# Loop through the hyperlinks in the hyperlink collection
for link in links:
    # Get the address of each hyperlink
    address = link.Address
    # Append the address to the list
    list.append(address)

# Write the extracted hyperlink addresses to a text file
with open("ExtractHyperlinks.txt", "w", encoding = "utf-8") as file:
    for item in list:
        file.write(item + "\n")

workbook.Dispose()

Modify Hyperlinks in Excel in Python

Modifying hyperlinks allows you to update URLs or alter the display text to suit your needs.

The following steps demonstrate how to modify an existing hyperlink in an Excel worksheet in Python using Spire.XLS for Python:

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[] property.
• Get a specific hyperlink in the worksheet using Worksheet.HyperLinks[] property.
• Modify the display text and address of the hyperlink using XlsHyperlink.TextToDisplay and XlsHyperlink.Address properties.
• Save the resulting file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Hyperlinks.xlsx")

# Get the first worksheet of the file
sheet = workbook.Worksheets[0]

# Get the first hyperlink in the worksheet
link = sheet.HyperLinks[0]

# Change the display text of the hyperlink
link.TextToDisplay = "Spire.XLS for .NET"
# Change the address of the hyperlink
link.Address = "http://www.e-iceblue.com"

# Save the resulting file
workbook.SaveToFile("ModifyHyperlink.xlsx", ExcelVersion.Version2016)

workbook.Dispose()

Remove Hyperlinks from Excel in Python

Removing hyperlinks can help eliminate unnecessary links and clean up your spreadsheet.

The following steps demonstrate how to remove a specific hyperlink from an Excel worksheet in Python using Spire.XLS for Python:

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[] property.
• Remove a specific hyperlink from the worksheet using Worksheet.Hyperlinks.RemoveAt() method.
• Save the resulting file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Hyperlinks.xlsx")

# Get the first worksheet of the file
sheet = workbook.Worksheets[0]

# Remove the first hyperlink and keep its display text
sheet.HyperLinks.RemoveAt(0)

# Save the resulting file
workbook.SaveToFile("RemoveHyperlink.xlsx", ExcelVersion.Version2016)

workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Hyperlinks are a useful tool in Microsoft Excel that allows users to create clickable links within their spreadsheets. By adding hyperlinks, you can conveniently navigate between different sheets, workbooks, websites, or even specific cells within the same workbook. Whether you need to reference external resources, connect related data, or create interactive reports, hyperlinks can help you achieve your purpose with ease. In this article, we will demonstrate how to add hyperlinks to Excel in Python using Spire.XLS for Python.

• Add Text Hyperlinks to Excel in Python
• Add Image Hyperlinks to Excel in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Add Text Hyperlinks to Excel in Python

Text hyperlinks in Excel are clickable words or phrases that can direct users to different parts of the Excel file, external resources, or email addresses. The following steps explain how to add a text hyperlink to an Excel file using Spire.XLS for Python:

• Create a Workbook object.
• Get the desired worksheet using Workbook.Worksheets[] property.
• Access the specific cell that you want to add a hyperlink to using Worksheet.Range[] property.
• Add a hyperlink to the cell using Worksheet.HyperLinks.Add() method.
• Set the type, display text and address of the hyperlink using XlsHyperLink.Type, XlsHyperLink.TextToDisplay and XlsHyperLink.Address properties.
• Save the resulting file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add a text hyperlink that leads to a webpage
cell1 = sheet.Range["B3"]
urlLink = sheet.HyperLinks.Add(cell1)
urlLink.Type = HyperLinkType.Url
urlLink.TextToDisplay = "Link to a website"
urlLink.Address = "https://www.e-iceblue.com/"

# Add a text hyperlink that leads to an email address
cell2 = sheet.Range["E3"]
mailLink = sheet.HyperLinks.Add(cell2)
mailLink.Type = HyperLinkType.Url
mailLink.TextToDisplay = "Link to an email address"
mailLink.Address = "mailto:example@outlook.com"

# Add a text hyperlink that leads to an external file
cell3 = sheet.Range["B7"]
fileLink = sheet.HyperLinks.Add(cell3)
fileLink.Type = HyperLinkType.File
fileLink.TextToDisplay = "Link to an external file"
fileLink.Address = "C:\\Users\\Administrator\\Desktop\\Report.xlsx"

# Add a text hyperlink that leads to a cell in another sheet
cell4 = sheet.Range["E7"]
linkToSheet = sheet.HyperLinks.Add(cell4)
linkToSheet.Type = HyperLinkType.Workbook
linkToSheet.TextToDisplay = "Link to a cell in sheet2"
linkToSheet.Address = "Sheet2!B5"

# Add a text hyperlink that leads to a UNC address
cell5 = sheet.Range["B11"]
uncLink = sheet.HyperLinks.Add(cell5)
uncLink.Type = HyperLinkType.Unc
uncLink.TextToDisplay = "Link to a UNC address"
uncLink.Address = "\\\\192.168.0.121"

# Autofit column widths
sheet.AutoFitColumn(2)
sheet.AutoFitColumn(5)

# Save the resulting file
workbook.SaveToFile("AddTextHyperlinks.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Add Image Hyperlinks to Excel in Python

Image hyperlinks in Excel work similarly to text hyperlinks but use images as clickable elements instead of words or phrases. They provide a visually appealing and intuitive way to navigate within the spreadsheet or to external resources. The following steps explain how to add an image hyperlink to an Excel file using Spire.XLS for Python:

• Create a Workbook object.
• Get the desired worksheet using Workbook.Worksheets[] property.
• Insert an image into the worksheet using Worksheet.Pictures.Add() method.
• Add a hyperlink to the image using XlsBitmapShape.SetHyperLink() method.
• Save the result file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add text to the worksheet
sheet.Range["B2"].Text = "Image Hyperlink"
# Set the width of the second column
sheet.Columns[1].ColumnWidth = 15

# Insert an image into the worksheet
picture = sheet.Pictures.Add(3, 2, "logo2.png")

# Add a hyperlink to the image 
picture.SetHyperLink("https://www.e-iceblue.com", True)
            
# Save the resulting file
workbook.SaveToFile("AddImageHyperlink.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.