CODEWITHSUNDEEP
pythonexcelopenpyxlpywin32xlsxwriter
iMath
iMath

Reputation: 2478

insert partial colored text and a hyperlink behind it into a excel cell

How to add the following hyperlink

<a href="http://www.bing.com">elon musk <em style="color : red;">starlink</em></a>

into a excel cell?

What I want is : enter image description here

I have searched web and found that it is not difficult to add text with a link to excel , the problem here is the text is partially colored, so how to partially color the text in an Excel cell?

Upvotes: 0

Views: 648

Answers (2)

jmcnamara
jmcnamara

Reputation: 41644

Here is an example using XlsxWriter where you write a url and then overwrite the cell text with a rich string containing multiple formats:

import xlsxwriter

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

# Adjust the column width for clarity
worksheet.set_column('A:A', 20)

# Add the required url.
worksheet.write_url('A1', 'http://www.bing.com')

# Add for formats for the rich string.
red = workbook.add_format({'color': 'red', 'underline': 1})
url_format = workbook.get_default_url_format()

# Overwrite the text for the url with a rich string.
worksheet.write_rich_string('A1',
                            'elon musk ',
                            red, 'starlink',
                            url_format)

workbook.close()

Output:

enter image description here

See the XlsxWriter docs on write_url() and write_rich_string().

Upvotes: 1

jizhihaoSAMA
jizhihaoSAMA

Reputation: 12672

openpyxl couldn't do it.(partially colored).By test,it seems that openpyxl won't get the color of the second part.

This is the example.xlsx:

enter image description here

If you read it by openpyxl,like:

import openpyxl
from openpyxl.styles import Font, Color

wb = openpyxl.load_workbook('example.xlsx')
sheet = wb.active
print(sheet.cell(row=1, column=1).font.color)

Result:

<openpyxl.styles.colors.Color object>
Parameters:
rgb='FFFFFF00', indexed=None, auto=None, theme=None, tint=0.0, type='rgb'

There isn't the second color in the font.

Also, I tried to copy the style of this cell to a new file,like:

import openpyxl, copy
from openpyxl.styles import Font, Color


wb = openpyxl.load_workbook('example.xlsx')
sheet = wb.active
sheet.cell(row=1, column=2, value="elon musk starlink").font = copy.copy(sheet.cell(row=1, column=1).font)

wb.save('output.xlsx')

still missed the color of the second part. enter image description here

Upvotes: 1

Related Questions