How to create a hyperlink to another excel sheet in the same workbook

Question

I am using the openpyxl module for Python and trying to create a hyperlink that will lead me to another tab in the same Excel workbook. Doing something similar to the following creates a hyperlink; however, when I click on it, it tells me that it cannot open the file.

from openpyxl import Workbook wb = Workbook() first_sheet = wb.create_sheet(title='first') second_sheet = wb.create_sheet(title='second') first_sheet['A1'] = "hello" second_sheet['B2'] = "goodbye" link_from = first_sheet['A1'] link_to = second_sheet['B2'].value link_from.hyperlink = link_to wb.save("C:/somepath/workbook.xlsx") 

I assume the problem is the value of 'link_to'; however, I do not know what needs to be changed or which way I would have to write.

I am using Python 2.7.6 and Excel 2013.

Answer 1

Openpyxl's hyperlink support is currently extremely rudimentary and is largely limited to reading links in existing files.

Answer 2

I found a way to do this.

Suppose one .xlsx file named " workbookEx.xlsx " with two sheets named " sheet1 " and " sheet2 " and needs a link from one cell ( A1 ) " sheet1 " to another cell ( E5 ) sheet2 '

 from openpyxl import load_workbook wb = load_workbook(workbookEx.xlsx) ws = wb.get_sheet_by_name("sheet1") link = "workbookEx.xlsx#sheet2!E5" ws.cell(row=1, column=1).hyperlink = (link) 

The secret was "#", Excel does not show you, but it uses "#" for the same file links, I just had to copy the same link to the file created in Excel into the Word document to see the "#",

It is also possible to skip the file name, that is, associate it with the sheet of the active document, just use: _cell.hyperlink = '#sheetName!A1' .

To name the link you just created, simply set the cell value to the desired line: _cell.value = 'Linkname' .

Answer 3

Another working solution is to use excels built into the HYPERLINK function. It does not make the value in the cell a hyperlink, but enters a formula in the cell and acts as a hyperlink.

 ws.cell('A1').value = '=HYPERLINK("#sheet2!E5","Link name")' 

Answer 4

As an addition to Marcus.Luck's answer, if you want to use the Excel hyperlink function directly, you may need to format it as:

'=HYPERLINK("{}", "{}")'.format(link, "Link Name")

Without this formatting, the file cannot be opened for me without the need for repair, which removes the cell values ​​when clicking links.

eg. ws.cell(row=1, column=1).value = '=HYPERLINK("{}", "{}")'.format(link, "Link Name")