在使用openpyxl的时候,希望添加一个超链接,从一个worksheet跳转到另一个worksheet中。在stack overflow中找到一个很好的方法,保存下来。
原文链接:
原文如下:
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'
.
补充:
实际上,link中不用添加文件名,默认就是当前文件。
link = "#sheet2!E5" #默认就是当前文件