New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Feature request: change color of urls after they have been clicked #455

Closed
gseastream opened this Issue Jul 17, 2017 · 13 comments

Comments

4 participants
@gseastream

gseastream commented Jul 17, 2017

Hello

Is it possible to add a font color option for urls/links such that the color changes after the url has been clicked? For example, from blue to purple, as usual with links. Openpyxl supports this so I was hoping xlsxwriter could too :)

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Jul 18, 2017

Owner

I'll look into it.

Owner

jmcnamara commented Jul 18, 2017

I'll look into it.

@Themanwithoutaplan

This comment has been minimized.

Show comment
Hide comment
@Themanwithoutaplan

Themanwithoutaplan Jul 18, 2017

openpyxl just relies on the relevant named style.

Themanwithoutaplan commented Jul 18, 2017

openpyxl just relies on the relevant named style.

@jdarrah

This comment has been minimized.

Show comment
Hide comment
@jdarrah

jdarrah commented Oct 12, 2017

+1

jmcnamara added a commit that referenced this issue Oct 13, 2017

Change default hyperlink format to a style.
Change the inbuilt hyperlink format to an Excel style so that it
changes with the theme and so that it shows up as clicked.

Issue #455

jmcnamara added a commit that referenced this issue Oct 14, 2017

Change default hyperlink format to a style.
Change the inbuilt hyperlink format to an Excel style so that it
changes with the theme and so that it shows up as clicked.

Issue #455
@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 14, 2017

Owner

I've added this feature to the feature to the master branch if you'd like to try it out.

Owner

jmcnamara commented Oct 14, 2017

I've added this feature to the feature to the master branch if you'd like to try it out.

@gseastream

This comment has been minimized.

Show comment
Hide comment
@gseastream

gseastream Oct 14, 2017

I cannot seem to get the feature to work properly. Here is what I tried:

To make sure I was updated, I ran the following:

pip uninstall xlsxwriter
pip install git+https://github.com/jmcnamara/XlsxWriter

Then, none of these pieces of code produced a link that changed color (they all stayed blue post-click):

format1 = sheet.add_format({'hyperlink': 1})
sheet.write_url('F20', 'http://www.python.org/', format1)
format1 = sheet.add_format({'hyperlink': 1})
sheet.write_url(row=row, col=0, url="internal:'%s'!A1" % sheetname, cell_format=format1, string=name)
sheet.write_url(row=row, col=0, url="internal:'%s'!A1" % sheetname, string=name)
sheet.write_url(row=row, col=0, url="internal:'%s'!A1" % sheetname)
sheet.write_url(row=row, col=0, url="internal:'%s'!A1" % sheetname, cell_format=sheet.default_url_format, string=name)

Is there something I'm missing? Is there a way you got it to work? Thanks!

gseastream commented Oct 14, 2017

I cannot seem to get the feature to work properly. Here is what I tried:

To make sure I was updated, I ran the following:

pip uninstall xlsxwriter
pip install git+https://github.com/jmcnamara/XlsxWriter

Then, none of these pieces of code produced a link that changed color (they all stayed blue post-click):

format1 = sheet.add_format({'hyperlink': 1})
sheet.write_url('F20', 'http://www.python.org/', format1)
format1 = sheet.add_format({'hyperlink': 1})
sheet.write_url(row=row, col=0, url="internal:'%s'!A1" % sheetname, cell_format=format1, string=name)
sheet.write_url(row=row, col=0, url="internal:'%s'!A1" % sheetname, string=name)
sheet.write_url(row=row, col=0, url="internal:'%s'!A1" % sheetname)
sheet.write_url(row=row, col=0, url="internal:'%s'!A1" % sheetname, cell_format=sheet.default_url_format, string=name)

Is there something I'm missing? Is there a way you got it to work? Thanks!

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 14, 2017

Owner

I've pushed the changes to PyPI in version 1.0.1 so you should be able to install it directly.

If you run the hyperlink.py example from the distro you should get a file like this:

screenshot1

After clicking on a link it looks like this:

screenshot2

Note, you don't need to (and probably shouldn't since it isn't a public property) create the format directly with 'hyperlink'.

Double check the installed version of the module, try again, and let me know how you get on.

Owner

jmcnamara commented Oct 14, 2017

I've pushed the changes to PyPI in version 1.0.1 so you should be able to install it directly.

If you run the hyperlink.py example from the distro you should get a file like this:

screenshot1

After clicking on a link it looks like this:

screenshot2

Note, you don't need to (and probably shouldn't since it isn't a public property) create the format directly with 'hyperlink'.

Double check the installed version of the module, try again, and let me know how you get on.

@gseastream

This comment has been minimized.

Show comment
Hide comment
@gseastream

gseastream Oct 14, 2017

Ok, so the example you referenced above does work properly for me. However, my problem persists and I think it has to do with pandas somehow.

The examples I was trying above in my comment were all using a worksheet created and retrieved from a pandas ExcelWriter object. For some reason, urls written onto those sheets did not color change correctly. I ran the following simple example that I modified from this page:

##############################################################################
#
# An example of converting a Pandas dataframe to an xlsx file
# with a user defined header format.
#
# Copyright 2013-2017, John McNamara, jmcnamara@cpan.org
#

import pandas as pd

# Create a Pandas dataframe from some data.
data = [10, 20, 30, 40, 50, 60]
df = pd.DataFrame({'Heading': data,
                   'Longer heading that should be wrapped' : data})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter("pandas_header_format.xlsx", engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object. Note that we turn off
# the default header and skip one row to allow us to insert a user defined
# header.
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add a header format.
header_format = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#D7E4BC',
    'border': 1})

# Write the column headers with the defined format.
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num + 1, value, header_format)

##### ADDITION: Try writing a url
worksheet.write_url('F10', 'http://www.python.org/')

# Close the Pandas Excel writer and output the Excel file.
writer.save()

The url in the file produced by this did not change colors after I clicked it. Would you know what might be causing this issue?

gseastream commented Oct 14, 2017

Ok, so the example you referenced above does work properly for me. However, my problem persists and I think it has to do with pandas somehow.

The examples I was trying above in my comment were all using a worksheet created and retrieved from a pandas ExcelWriter object. For some reason, urls written onto those sheets did not color change correctly. I ran the following simple example that I modified from this page:

##############################################################################
#
# An example of converting a Pandas dataframe to an xlsx file
# with a user defined header format.
#
# Copyright 2013-2017, John McNamara, jmcnamara@cpan.org
#

import pandas as pd

# Create a Pandas dataframe from some data.
data = [10, 20, 30, 40, 50, 60]
df = pd.DataFrame({'Heading': data,
                   'Longer heading that should be wrapped' : data})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter("pandas_header_format.xlsx", engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object. Note that we turn off
# the default header and skip one row to allow us to insert a user defined
# header.
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add a header format.
header_format = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#D7E4BC',
    'border': 1})

# Write the column headers with the defined format.
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num + 1, value, header_format)

##### ADDITION: Try writing a url
worksheet.write_url('F10', 'http://www.python.org/')

# Close the Pandas Excel writer and output the Excel file.
writer.save()

The url in the file produced by this did not change colors after I clicked it. Would you know what might be causing this issue?

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 15, 2017

Owner

Ok. I can see the issue but it isn't pandas related. The following simpler case doesn't work either:

import xlsxwriter

workbook = xlsxwriter.Workbook('hyperlink.xlsx')
worksheet = workbook.add_worksheet('Hyperlinks')

header_format = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#D7E4BC',
    'border': 1})

worksheet.write('A1', 'Test', header_format)

worksheet.write_url('A3', 'http://www.python.org/')

workbook.close()

I think I know what the issue might be. I'll look into it.

Owner

jmcnamara commented Oct 15, 2017

Ok. I can see the issue but it isn't pandas related. The following simpler case doesn't work either:

import xlsxwriter

workbook = xlsxwriter.Workbook('hyperlink.xlsx')
worksheet = workbook.add_worksheet('Hyperlinks')

header_format = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#D7E4BC',
    'border': 1})

worksheet.write('A1', 'Test', header_format)

worksheet.write_url('A3', 'http://www.python.org/')

workbook.close()

I think I know what the issue might be. I'll look into it.

jmcnamara added a commit that referenced this issue Oct 15, 2017

Fix for bug in hyplerlink style
Fix issue where the hyperlink style could have an incorrect font
reference which stopped the link style from working.

Issue #455
@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 15, 2017

Owner

Okay, I've fixed the issue on master and added another test case. The previous test cases didn't catch this unfortunately. :-(

Owner

jmcnamara commented Oct 15, 2017

Okay, I've fixed the issue on master and added another test case. The previous test cases didn't catch this unfortunately. :-(

@gseastream

This comment has been minimized.

Show comment
Hide comment
@gseastream

gseastream Oct 15, 2017

Awesome, works for me now too. Thanks!

gseastream commented Oct 15, 2017

Awesome, works for me now too. Thanks!

@gseastream gseastream closed this Oct 15, 2017

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 15, 2017

Owner

Fixed in release 1.0.2 on PyPI.

Owner

jmcnamara commented Oct 15, 2017

Fixed in release 1.0.2 on PyPI.

@gseastream

This comment has been minimized.

Show comment
Hide comment
@gseastream

gseastream Oct 15, 2017

I know I already closed the issue, but I have a quick question regarding the new feature. I want to try to change the font of some (not all) of the links in my workbook to be bolded but still maintain the hyperlink style. What would be the best way to do this? I tried doing

sheet.default_url_format.set_bold()

Which I thought might have worked for me since I just want all the links on one of my sheets to be bold, but this affected all the links in my workbook. Essentially, what is the best way to access and modify this new hyperlink format to change the format of a select number of cells? Is there a way to copy the format or something? I would try

format1 = sheet.add_format({'hyperlink': 1})

But I know you said this isn't a public property.

gseastream commented Oct 15, 2017

I know I already closed the issue, but I have a quick question regarding the new feature. I want to try to change the font of some (not all) of the links in my workbook to be bolded but still maintain the hyperlink style. What would be the best way to do this? I tried doing

sheet.default_url_format.set_bold()

Which I thought might have worked for me since I just want all the links on one of my sheets to be bold, but this affected all the links in my workbook. Essentially, what is the best way to access and modify this new hyperlink format to change the format of a select number of cells? Is there a way to copy the format or something? I would try

format1 = sheet.add_format({'hyperlink': 1})

But I know you said this isn't a public property.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 15, 2017

Owner

You should be able to do what you want as follows:

import xlsxwriter

workbook    = xlsxwriter.Workbook('hyperlink.xlsx')
worksheet   = workbook.add_worksheet()

url_format2 = workbook.add_format({'bold': True, 
                                   'underline': 1, 
                                   'theme': 10, 
                                   'hyperlink': True})

worksheet.write_url('A1', 'http://www.python.org/')
worksheet.write_url('A3', 'http://www.python.org/', url_format2)

workbook.close()

Note, that you will need to set underline, theme and hyperlink (even if the last 2 aren't public).

Owner

jmcnamara commented Oct 15, 2017

You should be able to do what you want as follows:

import xlsxwriter

workbook    = xlsxwriter.Workbook('hyperlink.xlsx')
worksheet   = workbook.add_worksheet()

url_format2 = workbook.add_format({'bold': True, 
                                   'underline': 1, 
                                   'theme': 10, 
                                   'hyperlink': True})

worksheet.write_url('A1', 'http://www.python.org/')
worksheet.write_url('A3', 'http://www.python.org/', url_format2)

workbook.close()

Note, that you will need to set underline, theme and hyperlink (even if the last 2 aren't public).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment