add hyperlink to excel sheet created by pandas dataframe to_excel method

Question

I have converted a pandas DataFrame to an Excel sheet using df.to_excel.

Now, I want to add hyperlinks to the values in one column. In other words, when a customer sees my excel sheet, he would be able to click on a cell and bring up a webpage (depending on the value in this cell).

score 29 · Answer 1 · edited Apr 20 '23 at 15:13

Building on the approach by @guillaume-jacquenot we can use apply to apply this to an entire Series.

df = pd.DataFrame({'Year': [2000, 2001, 2002 , 2003]})

For cleanliness, I wrote a helper method.

def make_hyperlink(value):
    url = "https://custom.url/{}"
    return '=HYPERLINK("%s", "%s")' % (url.format(value), value)

Then, apply it to the Series:

df['hyperlink'] = df['Year'].apply(make_hyperlink)

    Year    hyperlink
0   2000    =HYPERLINK("https://custom.url/2000", "2000")
1   2001    =HYPERLINK("https://custom.url/2001", "2001")
2   2002    =HYPERLINK("https://custom.url/2002", "2002")
3   2003    =HYPERLINK("https://custom.url/2003", "2003")

As an improvement, you could just pass `make_hyperlink` function directly into `.apply` method like `df['Year'].apply(make_hyperlink)`. — Guven Degirmenci, Mar 24 '22 at 12:30

score 23 · Answer 2 · edited Nov 14 '16 at 10:27

23

You could use the HYPERLINK function

import pandas as pd
df = pd.DataFrame({'link':['=HYPERLINK("http://www.someurl.com", "some website")']})
df.to_excel('test.xlsx')

edited Nov 14 '16 at 10:27

Guillaume Jacquenot

11,217
6
43
49

answered Aug 05 '15 at 01:01

maxymoo

35,286
11
92
119

2

Is there a way to get the hyperlink when reading the file into a Pandas DataFrame? – slaw Feb 10 '16 at 21:01
1

Could you a give an example where you add a hyperlink to a specific cell? Also, have you succeed in passing anchor in the URL? It does not seem possible from what I have seen – Guillaume Jacquenot Nov 10 '16 at 15:38
1

just run `df.to_excel('test.xlsx')` on the above code and try it out, i just tried an anchor link and it worked for me – maxymoo Nov 10 '16 at 22:31
How can I write a link containing `"` in it? Thanks – ayalaall Jan 14 '21 at 18:57
Found the answer so I write it here in case it will help other people. To write a url with a `"` in it you need to replace the `"` with `%7B`. One can use `str.replace()` for that. – ayalaall Jan 17 '21 at 07:04

score 2 · Answer 3 · answered Nov 14 '16 at 10:38

From @maxymoo's answer, here is a full example

import pandas as pd
df = pd.DataFrame({'Year': [2000, 2001, 2002 , 2003]})
df['link'] = '-'
df.set_value(0, 'link', '=HYPERLINK("https://en.wikipedia.org/wiki/2000", 2000)')
df.set_value(1, 'link', '=HYPERLINK("https://en.wikipedia.org/wiki/2001", 2001)')
df.set_value(2, 'link', '=HYPERLINK("https://en.wikipedia.org/wiki/2002", 2002)')
df.set_value(3, 'link', '=HYPERLINK("https://en.wikipedia.org/wiki/2003", 2003)')
df.to_excel('test.xlsx', index = False)

score 1 · Answer 4 · edited Jun 20 '17 at 08:03

1

You can use:

df = pd.DataFrame(list(range(5)), columns=['a'])

df['a'] = df['a'].apply(lambda x: '<a href="http://youtube.com/{0}">link</a>'.format(x))

HTML(df.to_html(escape=False))

edited Jun 20 '17 at 08:03

Bugs

4,491
9
32
41

answered Jun 20 '17 at 07:12

Akshit Gupta

11
2

This doesn't create a hyperlink in the dataframe; it creates an HTML version of the dataframe. The other answers that use `to_excel` have the right approach. – Dannid Dec 13 '17 at 23:04
you also need `from IPython.display import HTML` – Tim Jul 10 '19 at 18:37

score 1 · Answer 5 · answered Oct 26 '18 at 08:55

I was generating text files from a excel file and wanted to link the names of the generated .txt files to a particular existing column in the Dataframe.

I was trying to push the local drive directory where the generated .txt files are stored, to the corresponding "File Name". So that on clicking the file name, it will open the .txt file.

rancheck_DF = pd.read_excel(excel_file, delim_whitespace = True, encoding = 'utf-8')

for index_df in range(len(rancheck_DF)):

    Desc = rancheck_DF.loc[index_df,'Description Text']

    MainFile = rancheck_DF.loc[index_df,'File Name']

    fileName = r'.\Documents\TestF\TestF_{}.txt'.format(index_df)

    with open(fileName, 'w', encoding='utf-8') as txtfile:
        txtfile.write(Desc)

    rancheck_DF.loc[index_df,'File Name'] = '=HYPERLINK("{}","{}")'.format(fileName,MainFile)

rancheck_DF.to_excel('./damn.xlsx', index=None)

add hyperlink to excel sheet created by pandas dataframe to_excel method

5 Answers5

Linked