英文:
Use xlsxwriter write_url with excel file opened through openpyxl
问题
def linkCells():
wb = load_workbook(filename='output1.xlsx')
ws = wb['Summary']
cells = []
for row in ws.iter_rows():
for cell in row:
temp = re.compile("([a-zA-Z]+)([0-9]+)")
res = temp.match(cell.coordinate).groups()
val1 = res[0]
val2 = int(res[1])
if val1 == "B" and val2 >= 25:
cells.append(cell)
for val in cells:
cellCordinate = val.coordinate
temp = re.compile("([a-zA-Z]+)([0-9]+)")
res = temp.match(cellCordinate).groups()
val2 = int(res[1])
cellValue = val.value[:30]
link = "output1.xlsx#" + cellValue + "!A1"
ws.cell(2, val2).hyperlink = link
ws.cell(2, val2).style = "Hyperlink"
wb.save(filename='output1.xlsx')
英文:
I want to add links to a sheet in the same workbook. The sheet with the link is "Summary" and would contain multiple links pointing to different sheets in the same workbook.
Here is the complete code:
def linkCells():
wb = load_workbook(filename = 'output1.xlsx')
ws = wb['Summary']
cells = []
for row in ws.iter_rows():
for cell in row:
temp = re.compile("([a-zA-Z]+)([0-9]+)")
res = temp.match(cell.coordinate).groups()
val1 = res[0]
val2 = int(res[1])
if val1 == "B" and val2 >= 25:
cells.append(cell)
for val in cells:
cellCordinate = val.coordinate
temp = re.compile("([a-zA-Z]+)([0-9]+)")
res = temp.match(cellCordinate).groups()
val2 = int(res[1])
cellValue = val.value[:30]
link = "output1.xlsx#"+cellValue+"!A1"
ws.cell(2,val2).hyperlink = link
ws.cell(2,val2).style = "Hyperlink"
wb.save(filename = 'output1.xlsx')
But based on the following link:
Support for hyperlinks in openpyxl is currently extremely rudimentary and largely limited to reading the links in existing files.
I also know that xlsxwriter has write_url function which could help to create links, but from my understanding, there is no way to open an existing file in xlsxwriter.
Is there any way my given code could work in openpyxl or how can I open the file using openpyxl and then create links using xlsxwriter(I don't think the second option is feasible as far as I know)?
答案1
得分: 1
从我的角度看，你只想要做这个：
```python
from openpyxl import load_workbook
from openpyxl.worksheet.hyperlink import Hyperlink
xlfile = "output1.xlsx"
summary_sheet = 'Summary'
wb = load_workbook(xlfile)
ws = wb[summary_sheet]
cell_coord = 'A1'
for row, sh in enumerate(wb.worksheets):
if sh.title == summary_sheet:
continue
hyperlink = Hyperlink(target=xlfile,
ref=cell_coord,
location=f'{sh.title}!{cell_coord}'
)
ws.cell(row, 1).value = f"{sh.title}"
ws.cell(row, 1).hyperlink = hyperlink
wb.save(xlfile)
输出文件在每个工作表1-4的A列中都有4个链接。如果单击链接，它会将焦点移动到工作簿中所选工作表的单元格'A1'。
<details>
<summary>英文:</summary>
Seems to me all you want to do is this
from openpyxl import load_workbook
from openpyxl.worksheet.hyperlink import Hyperlink
xlfile = "output1.xlsx"
summary_sheet = 'Summary'
wb = load_workbook(xlfile)
ws = wb[summary_sheet]
cell_coord = 'A1'
for row, sh in enumerate(wb.worksheets):
if sh.title == summary_sheet:
continue
hyperlink = Hyperlink(target=xlfile,
ref=cell_coord,
location=f'{sh.title}!{cell_coord}'
)
ws.cell(row, 1).value = f"{sh.title}"
ws.cell(row, 1).hyperlink = hyperlink
wb.save(xlfile)
The output file has 4 links in column A for each of the Sheets 1-4. If you click a link it will move the focus to cell 'A1' of the selected sheet in the workbook.<br>
[![Example output Excel file][1]][1]
[1]: https://i.stack.imgur.com/L2N1B.png
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论