How to create a hyperlink to a different Excel she

Question

I'm using the module openpyxl for Python and am trying to create a hyperlink that will take me to a different tab in the same Excel workbook. Doing something similar to the following creates the hyperlink; however, when I click on it, it tells me it can't 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'm assuming the issue lies in the value of 'link_to'; however, I don't know what would need changed or what kind of path I would have to write.

I'm using Python 2.7.6 and Excel 2013.

Answer 1

As an addendum to Marcus.Luck's answer, if wanting to use Excel's built-in hyperlink function directly, you may need to format as:

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

Without this formatting, the file didn't open for me without needing repair, which removed the cell values when clicking the links.

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

Answer 2

I found a way to do it.

Assuming one .xlsx file named 'workbookEx.xlsx' with two sheets named 'sheet1' and 'sheet2' and needing a link from one cell(A1) of the 'sheet1' to another cell(E5) of the '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 the "#", Excel do not shows you but it uses the '#' for same file links, I just had to copy a same file link created in Excel to a Word document to see the '#'.

It is also possible to omit the filename, i.e. to link against a sheet of the active document just use: _cell.hyperlink = '#sheetName!A1'.

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

Answer 3

In addition to the previous answers, it is also useful to format the cell to make it look like the hyperlinks created within Excel. To do this use the Excel style named "Hyperlink", as per example below which also includes using single quotes around sheet names in case they include spaces (as mentioned by Neofish).

cell(row, col).value = '=HYPERLINK("#\'{}\'!A1", "{}")'.format(sheet_name_with_spaces, "Link Name")
cell(row, col).style = 'Hyperlink'

Answer 4

import openpyxl as opxl
import pandas as pd
def hyperlinking(New_file_path):
    xls = pd.ExcelFile(New_file_path)
    sheets = xls.sheet_names
    wb = opxl.load_workbook(New_file_path)
    ws = wb.create_sheet("Consolitated_Sheet")
    ws['A1'] = "Sheet_Name"; ws['B1'] = "Active_link"
    for i, j in enumerate(sheets):
        # print('A'+str(i+2) + ' value is: ' + j)
        ws['A' + str(i + 2)] = j
        ws['B' + str(i + 2)].value = '=HYPERLINK("%s", "%s")' % ('#' + str(j) + '!A1', 'Clickhere')
    wb.save(New_file_path)
    wb.close()

Answer 5

Support for hyperlinks in openpyxl is currently extremely rudimentary and largely limited to reading the links in existing files.

Answer 6

Another working solution is to use excels built in function HYPERLINK. It doesn't make the value in the cell into a hyperlink but put a formula in the cell and act like a hyperlink.

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