Use xlsxwriter write_url with excel file opened through openpyxl

Question

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:

Create a hyperlink to a different Excel sheet in the same workbook

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)?

Answer 1

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.