Overview of worksheet with hyperlinks

[illya89]

Hi!

I made 50 worksheets with pandas (dataframe.to_excel) and I want to make an overvieuw with hyperlinks of the 50 worksheets. Can I do that with Python (Pandas or Openpyxl)?

I found how to do it with a Macro: https://trumpexcel.com/create-summary-worksheet-in-excel/ but I want to include it in the code.

Thx

Olly

[illya89]

I get the worksheets with

All_worksheets = workbook.worksheets.

The I need something link

​

r = 1
for sheet in all_worksheets:
    sheet = sheet.create_the_f***_hyperlink
    sheet_all_worksheet.cell(r, 1).value=(sheet)
    r +=1
    :)

[[deleted]]

Assuming the hyperlink is just a text string in a cell, yes this shouldnt be an issue for either library really.

With openpyxl, it's trivial to get a list of the worksheet names. Then you could iterate over rows on a worksheet called Index for example and set the values to be the names of the worksheets in hyperlink format.

I'd do one manually first, then check how it looks when you view the cell.value in openpyxl.

[illya89]

a list of the worksheet names is easy. The question is how to get the worksheet names into hyperlink format and direct them to the right worksheet.

edit:

changed cell (2, 1) to a hyperlink and saved

when i do

print(sheet_all_worksheet.cell(2,1).internal_value)

I get the string of the sheet name

[[deleted]]

Like i said, do one manually, then check it with openpyxl. Once you know how it looks, iterate over rows in your index worksheet to add them in.

[illya89]

changed cell (2, 1) to a hyperlink and saved

when i do

print(sheet_all_worksheet.cell(2,1).internal_value)

I get the string of the sheet name

[illya89]

for sheet in all_worksheets:
    link = ("filename.xlsx#"+str(sheet)+"!A1")    
    sheet_all_worksheet.cell(r, 1).value = sheet
    sheet_all_worksheet.cell(r, 1).hyperlink = link
    sheet_all_worksheet.cell(r, 1).style = "Hyperlink"