Create hyperlink for accessing Multiple excel sheets by python

Question

I have multiple txt files which I am able to merge and write into different excel sheets by the below code:

for file in list(file_lists):
    filename = os.path.join(path, file)
    df = pd.read_table(filename,sep="|",encoding = "ISO-8859-1")
    df.to_excel(writer, sheet_name=file[:-4], index=False,header=True)
writer.save()

I want to now create another sheet (Name = "Homepage") which will contain different sheet names as hyperlinks. I should be able to click on that hyperlink which will take me to the respective sheets. Is there any way I can do that? Any sample codes would be helpful, the similar codes available in SO are not helping me.

iDrwish · Accepted Answer · 2018-06-17 10:09:25Z

This is not really a pandas question, as the pandas.to_excel function has very little to do with the formatting and markup used in the excel sheet.

You can look at xlsxwriter which specifically formats xlsx files.

This is a sample code for formatting hyperlinks, it is adapted from the documentation where you can read more.

###############################################################################
#
# Example of how to use the XlsxWriter module to write hyperlinks
#
# Copyright 2013-2018, John McNamara, [email protected]
#
import xlsxwriter

# Create a new workbook and add a worksheet
workbook = xlsxwriter.Workbook('hyperlink.xlsx')
worksheet = workbook.add_worksheet('Hyperlinks')

# Format the first column
worksheet.set_column('A:A', 30)

# Add a sample alternative link format.
red_format = workbook.add_format({
    'font_color': 'red',
    'bold':       1,
    'underline':  1,
    'font_size':  12,
})

# Write some hyperlinks
worksheet.write_url('A1', 'http://www.python.org/')  # Implicit format.
worksheet.write_url('A3', 'http://www.python.org/', string='Python Home')
worksheet.write_url('A5', 'http://www.python.org/', tip='Click here')
worksheet.write_url('A7', 'http://www.python.org/', red_format)
worksheet.write_url('A9', 'mailto:[email protected]', string='Mail me')

# Write a URL that isn't a hyperlink
worksheet.write_string('A11', 'http://www.python.org/')

workbook.close()

Collectives™ on Stack Overflow

Create hyperlink for accessing Multiple excel sheets by python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related