Casie's blog

Master How to Add Hyperlink in Excel Using Python Like an Expert

Casie Liu's photo
Casie Liu
·

5 min read

Table of contents

Hyperlinks, though not as common in Excel sheets, can be incredibly useful for various purposes. For instance, you might link to a product's webpage in a product catalog, provide a clickable email address in a customer info sheet, or even link to other files and cells within your workbook. With Python, you can easily add hyperlinks in bulk, automating the process and saving time. In this article, we will show you how to add hyperlinks in Excel with Python quickly and efficiently, covering both text and image links, and guiding you through the process of creating links in Excel without repetitive manual work.

To quickly and easily add hyperlinks in Excel, a reliable Python library is essential. While popular open-source libraries like openpyxl and pandas can handle some of these tasks, openpyxl doesn’t support xls files, and pandas requires openpyxl to add hyperlinks. xlwings can do the job, but it depends on Microsoft Excel. For a more flexible and independent solution, I recommend using Spire.XLS. This third-party library doesn’t require MS Excel and offers much more than just hyperlink creation. With Spire.XLS, you can also perform complex tasks like text replacement, formatting, and converting Excel files to PDF or HTML. You can install it using the pip command: pip install Spire.XLS.

How to Add Text Hyperlink in Excel with Python

Text hyperlinks are a great way to connect key information in Excel to external resources like web pages, documents, or email addresses, significantly enhancing the interactivity and utility of your spreadsheets. For example, if your Excel worksheet only contains key data, you can link keywords to detailed external documents to provide additional context or support. In this section, we’ll walk through the specific steps to add text hyperlinks in Excel, helping you make your documents more dynamic and informative.

Steps to add text hyperlinks in Excel files:

  • Create an object of the Workbook class.

  • Get a worksheet using the Workbook.Worksheets[] property.

  • Access the specific cell where you want to add a hyperlink with the Worksheet.Range[] property.

  • Add hyperlinks to cells through the Worksheet.HyperLinks.Add() method.

  • Customize types, display text, and address of the hyperlink using the XlsHyperLink.Type, XlsHyperLink.TextToDisplay and XlsHyperLink.Address properties.

  • Save the resulting Excel workbook through the Workbook.SaveToFile() method.

Here is a Python example of adding links to a website, an email address, an external file, a cell, and a UNC address in Excel:

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

Python Add Text Hyperlinks to Excel Worksheet

Similar to text hyperlinks, image hyperlinks can also connect to URLs and other resources. However, they offer a more visually engaging and interactive way to enhance your Excel sheets. By linking logos, charts, or product images to web pages—such as a company website or a product purchase page—you can improve navigation and user experience. In this section, we'll explore how to automate the process of adding image hyperlinks in Excel using Python.

Steps to add image hyperlinks in Excel workbooks:

  • Create an instance of the Workbook class.

  • Retrieve a certain worksheet through the Workbook.Worksheets[] property.

  • Insert an image into the worksheet using the Worksheet.Pictures.Add() method.

  • Add a hyperlink attached to the image with the XlsBitmapShape.SetHyperLink() method.

  • Save the resulting Excel file using the Workbook.SaveToFile() method.

Below is the code example of adding an image hyperlink in the first worksheet in an Excel file:

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, "/Logo1.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()

Python Add Image Hyperlinks to Excel Worksheet

To Wrap Up

This article explores how to add hyperlinks in Excel using Python, covering both text and image hyperlinks. To help you understand the process better, we provide step-by-step instructions along with code examples. By the end of this guide, you'll see how easy and efficient it is to insert hyperlinks in Excel in bulk!

Pythonexcel#Excel-tutorial