时间戳

首页 > 解决方案 > 如何在超链接中使用变量名？

python - 如何在超链接中使用变量名？

问题描述

我尝试使用以下 Python 代码在 Python 中使用 openpyxl 创建超链接。我尝试使用变量名，但收到错误“引用无效”。我还尝试使用带有空格的 Excel 工作表名称，这在 Excel 中有效，但 openpyxl 超链接命令似乎不适用于它们。为 openpyxl 查找代码示例也具有挑战性。这是代码：

from openpyxl import load_workbook

xlsFile = 'e:\\exceltest1.xlsx'
wbook = load_workbook(xlsFile)
Sheet7Name = "Sheet7"
worksheet7 = wbook.create_sheet(title=Sheet7Name)
worksheet9 = wbook.create_sheet(title="Sheet9")
worksheet10 = wbook.create_sheet(title="Sheet 10")
worksheet7.cell(row=3, column=4, value="Go to A1 s9 from D3 s7").hyperlink = '#Sheet9!A1'
worksheet7.cell(row=1, column=1, value="Go to D3 s9 from A1 s7").hyperlink = '#Sheet9!D3'
worksheet7.cell(row=2, column=1, value="Go to A3 s9 from A2 s7").hyperlink = '#Sheet9!A3'
worksheet7.cell(row=4, column=5, value="Go to E6 s7  from E4 s7").hyperlink = '#Sheet7Name!E6'
worksheet7.cell(row=6, column=5, value="Go to E4 s7 from E6 s7").hyperlink = '#Sheet7Name!E4'
worksheet9.cell(row=4, column=1, value="Go to A7 s7 from A4 s9").hyperlink = '#Sheet7Name!A7'
worksheet9.cell(row=3, column=4, value="Go to A1 s9 from D3 s9").hyperlink = '#Sheet9!A1'
worksheet9.cell(row=1, column=1, value="Go to D3 s9 from A1 s9").hyperlink = '#Sheet9!D3'
worksheet9.cell(row=2, column=1, value="Go to A3 s9 from A2 s9").hyperlink = '#Sheet9!A3'
worksheet10.cell(row=2, column=1, value="Go to A3 s10 from A2 s10").hyperlink = '#Sheet 10!A3'
wbook.save(xlsFile)

标签： pythonpython-3.xopenpyxl

解决方案

你可以试试这个 -链接到cell 1in ：Sheet1A1 cellSheet9

file_name = "your-excel-file.xlsx"
wb = load_workbook(file_name) 
ws1 = wb['Sheet1']

# Create hyperlink to relevant cell
link = file_name+"#Sheet9!A1"

ws1.cell(row=1, column=1).hyperlink = link
ws1.cell(row=1, column=1).value = "Refer to Sheet 9 - Cell A1"
ws1.cell(row=1, column=1).style = "Hyperlink"

wb.save(file_name)

并为您想要超链接的其余单元格执行此操作。

编辑：替换wb.get_sheet_by_name('Sheet1')为ws1 = wb['Sheet1']因为 DeprecationWarning。

推荐阅读