Python: Extract, Modify or Remove Hyperlinks in Excel
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.
Python: Add Hyperlinks to Excel
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.
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.
- Python
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.
- Python
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.