Add Hyperlink to Excel Using Python Pandas: A Step Guide- Python Pandas Tutorial

By | April 14, 2022

If you plan to insert a video in an excel file, you have to insert a hyperlink. In this tutorial, we will introduce you how to do using python pandas.

Save rows to excel using python pandas

It is easy to save rows to excel file. For example:

import  pandas  as pd
def save_data_to_excel(excel_name, sheet_name, data):
    columns = []
    for k, v in data.items():
        columns.append(k)
    with pd.ExcelWriter(excel_name) as writer:
        df = pd.DataFrame(data, index=None)
        df.to_excel(writer, sheet_name=sheet_name, index=False, columns=columns)

Here data is rows we plan to save to excel file, it should be a python dictionary as follows:

data = {'Name':['Tom', 'Jack', 'Steve', 'Ricky'],'Age':[28,34,29,42]}

Then, we can save data to excel file as follows:

save_data_to_excel(excel_name="text.xlsx", sheet_name='test', data = data)

Add Hyperlink to Excel

From above, we can find we have not add any hyperlinks to excel file, in order to implement it, we can do as follows:

data = {'File':['=HYPERLINK("1.wav","1.wav")', '=HYPERLINK("https://www.tutorialexample.com","www.tutorialexample.com")'],'Size':[100, 120]}

In this example, we will use =HYPERLINK(“url or file path”,”text”) to add a hyperlink to excel file.

save_data_to_excel(excel_name="text.xlsx", sheet_name='test', data = data)

Run this code, we will see:

Add Hyperlink to Excel Using Python Pandas - A Step Guide- Python Pandas Tutorial

We will click 1.wav or www.tutorialexample.com to open a wav file or our blog.

However, we should notice: we save hyperlink to .xlsx file, how about we save it to .xls file?

save_data_to_excel(excel_name="text.xls", sheet_name='test', data = data)

Run this code, we will see:

Add Hyperlink to XLS Excel Using Python Pandas - A Step Guide- Python Pandas Tutorial

It shows hyperlinks will display incorrectly.

Leave a Reply

Your email address will not be published. Required fields are marked *