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

Answer 1

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(lambda x: make_hyperlink(x))

>

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

Answer 2

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)

Answer 3

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