Can Pandas to_excel support hyperlink style now?

Question

I can't find an answer (or one I know how to implement) when it comes to using the excel "hyperlink" style for a column when exporting using pd.to_excel.

I can find plenty of (OLD) answers on using xlsxwriter or openpyxl. But none using the current pandas functionality.

I think it might be possible now with the updates to the .style function? But I don't know how to implement the CSS2.2 rules to emulate the hyperlink style.

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

The desired output is for the link column, to be the standard blue underlined text that then turns purple once you have clicked the link.

Is there a way to use the built in excel styling? Or would you have to pass various css properties througha dictionary using .style?

user_10238 · Answer

Here is one way to do it using xlsxwriter as the Excel engine:

import pandas as pd

df = pd.DataFrame({'ID': [1, 2],
                   'link':['=HYPERLINK("http://www.python.org", "some website")',
                           '=HYPERLINK("http://www.python.org", "some website")']})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')


# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Get the default URL format.
url_format = workbook.get_default_url_format()

# Apply it to the appropriate column, and widen the column.
worksheet.set_column(2, 2, 40, url_format)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output, note that the second link has been clicked and is a different color:

Note, it would be preferable to use the xlsxwriter worksheet.write_url() method since that will look like a native Excel url to the end user and also doesn't need the above trick of getting and applying the url format. However, that method can't be used directly from a pandas dataframe (unlike the formula) so you would need to iterate through the link column of the dataframe and overwrite the formulas programatically with actual links.

Something like this:

import pandas as pd

df = pd.DataFrame({'ID': [1, 2],
                   'link':['=HYPERLINK("http://www.python.org", "some website")',
                           '=HYPERLINK("http://www.python.org", "some website")']})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('test2.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the worksheet handle.
worksheet = writer.sheets['Sheet1']

# Widen the colum for clarity
worksheet.set_column(2, 2, 40)

# Overwrite the urls
worksheet.write_url(1, 2, "http://www.python.org", None, "some website")
worksheet.write_url(2, 2, "http://www.python.org", None, "some website")

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output:

Can Pandas to_excel support hyperlink style now?

1 Answers