add hyperlink to excel sheet with pandas, which references to a another sheet

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 another sheet on the excel document (i dont want to bring up a website !!)

Answer 1

The fully reproducible example provided below demonstrates how you would create a workbook, in which you are able to click on a particular cell and bring up another sheet in the same excel document.

In this example we use Xlswriter as the engine= parameter in pandas.DataFrame.to_excel(). This done to make use of some methods in the worksheet class of Xlsxwriter library, including worksheet.write_url()(link to docs).

import pandas as pd
import numpy as np

# Creating a dataframe 
df = pd.DataFrame(np.random.randn(10, 2), columns=list('AB'))
writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')

df.to_excel(writer, index=False, sheet_name='data')

workbook  = writer.book
worksheet = workbook.add_worksheet('sheetWithLink')

worksheet.write_url('A1',  'internal:data!A1')
worksheet.write('A1',  "A link to another sheet")


writer.save()

Expected Output: