Python openpyxl lose hyperlink when modifying existed files

Question

Strangely, when load an existed excel with openpyxl and save it again, the hyperlinks in the file disappears.

Either openpyxl 1.7.2 or the newest 1.8.5 has this problem.

Anyone can help with this problem?

Or is there any better choice than openpyxl?

I know xlrd/xlwt and XlsxWriter, but xlwt doesn't support .xlsx files, and XlsxWriter can't read existed files. I need modify a file many times in my application.

[UPDATED]: Look here. Seems this is bug not yet fixed?

The following code may be helpfull for your test.

#-*- coding: utf-8 -*-
import openpyxl

def create():
    wb = openpyxl.Workbook()
    ws = wb.worksheets[0]

    ws.cell('A1').value = 'Click Me'
    ws.cell('A1').hyperlink = 'http://www.google.com'

    wb.save('test1.xlsx')

def rewrite():
    wb = openpyxl.load_workbook('test1.xlsx')
    ws = wb.worksheets[0]

    wb.save('test2.xlsx')

if __name__ == '__main__':
    create()
    rewrite()

[2017-03-07 UPDATED]: The bug has been fixed, and the problem does not exist any more.

Answer 1

Try to use the HYPERLINK function in Excel. That results in a formula and not a value in that cell, but from a user's standpoint it most probably makes no difference:

ws.cell('A1').value = '=HYPERLINK("http://www.google.com","Click Me")'

Answer 2

As an addendum to Cedric's answer, if wanting to use excel's built in hyperlink function directly, you can use the following to format as a link:

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

Without this formatting, the file didn't open for me without needing repair, which removed the cell values with the attempted hyperlinks.

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