CODEWITHSUNDEEP
pythonhtmlpandas
zadrozny
zadrozny

Reputation: 1731

Ouputting (clickable) hyperlinks to a spreadsheet with Python pandas.to_excel()

I have a dataframe in which one column is all hyperlinks (eg, http://example.com). I have managed to make these links render as html inside Jupyter Notebook:

from IPython.display import HTML
df["URL"] = df["URL"].apply(lambda x: '<a href="{}">{}</a>'.format(x,x))
df = HTML(df.to_html(escape=False))
df

But I cannot get these links written to an Excel (or ODS) spreadsheet as html rather than text. In the above, df become a IPython.core.display.HTML object, without pandas's to_excel().

When I convert the individual cells to IPython.core.display.HTML with lambda x: HTML('<a href="{}">{}</a>'.format(x,x)) and then call to_excel on the DataFrame, I get the Exception:

Unexpected data type <class 'IPython.core.display.HTML'>

Upvotes: 1

Views: 3995

Answers (1)

Mohammad Ali
Mohammad Ali

Reputation: 922

You could use the HYPERLINK function built into pandas 

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

Upvotes: 3

Related Questions