CODEWITHSUNDEEP
pythonexcelpandashyperlink
wright17
wright17

Reputation: 341

add hyperlink to excel sheet created by pandas dataframe to_excel method

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

Upvotes: 23

Views: 38713

Answers (5)

Dannid
Dannid

Reputation: 1697

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

Upvotes: 36

Abhishek Roy
Abhishek Roy

Reputation: 89

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.

The dataframe

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)

Upvotes: 1

Akshit Gupta
Akshit Gupta

Reputation: 11

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

Upvotes: 1

Guillaume Jacquenot
Guillaume Jacquenot

Reputation: 11717

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)

Upvotes: 3

maxymoo
maxymoo

Reputation: 36545

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

Upvotes: 26

Related Questions