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.
- Python
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.
- Python
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.
- Python
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.
