Hyperlink style in Openpyxl

问题:

In openpyxl, you can set a hyperlink like this:

cell.hyperlink = r'..\somedir\somefile.txt'

However, this does not apply the hyperlink style that you would get when setting a hyperlink in Excel. You can apply a style with blue text and underlining, but when you open the document, visited hyperlinks do not change color.

Is there any way with openpyxl to specify a hyperlink style that works like a hyperlink set within Excel?

回答1:

import openpyxl
from openpyxl.styles import Font, Color, colors
#...

# alternative 1: set hyperlink property to cell
def link_1(cell, link, display=None):
    cell.hyperlink = link
    cell.font = Font(u='single', color=colors.BLUE)
    if display is not None:
        cell.value = display

# alternative 2: use Excel formula HYPERLINK
def link_2(cell, link, display='link'):
    cell.value = '=HYPERLINK("%s", "%s")' % (link, display)
    cell.font = Font(u='single', color=colors.BLUE)

# examples
link_1(ws['B2'], '#sheet3!A1', 'link_text') # internal link
link_2(ws['B3'], '#sheet3!A1', 'link_text') # internal link
link_1(ws['B4'], 'https://www.google.com/', 'Google') # web link

回答2:

You have to change style attribute

cell.style = "Hyperlink"

回答3:

I used a Font and it worked.

from openpyxl.styles import Font
hyperlink = Font(underline='single', color='0563C1')
# ...
cell.font = hyperlink

There is supposed to be a builtin sytle named Hyperlink but I have not managed to make it work...

回答4:

Try to add the hyperlink style like this

ft = Font()
ft.underline = 'single'    # add single underline
ft.color = Color(rgb='000000FF')  # add blue color
cell.font = ft