CODEWITHSUNDEEP
pythonopenpyxl
Jeffrey Magedanz
Jeffrey Magedanz

Reputation: 159

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?

Upvotes: 11

Views: 10311

Answers (6)

jtt
jtt

Reputation: 1

As far as I can ascertain, cell.style = "Hyperlink" applies the hyperlink style that is defined in Excel. If you overwrite the cell style, the "link" properties are lost.

This worked for me (to link to a different sheet):

  • Change link style in Excel: Insert a link in a cell in spreadsheet. With that cell selected, on the Home tab, right-click the Hyperlink style and pick Modify. This will apply new style to ALL links in workbook.

In openpyxl:

cell.value = f'=HYPERLINK("#\'TOC\'!A{target_toc_cell}", "{table_name} (Return to TOC)")'
cell.style = "Hyperlink"

Upvotes: 0

LivingDust
LivingDust

Reputation: 307

This works for me:

cell.value = '=HYPERLINK("{}", "{}")'.format('/my/unix/path/data.html', 'theData')
cell.style='Hyperlink'

Where 'theData' is the display value people see in the cell.

Upvotes: 0

Humberto Márquez Blanco
Humberto Márquez Blanco

Reputation: 121

You have to change style attribute

cell.style = "Hyperlink"

Upvotes: 8

HHK
HHK

Reputation: 31

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

Upvotes: 3

Jean-Francois T.
Jean-Francois T.

Reputation: 12940

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...

Upvotes: 1

alen
alen

Reputation: 149

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

Upvotes: 0

Related Questions