6

I am using openpyxl to write some data to excel sheet. In my script, I need to append data to the same cell and highlight with different color for the new added data. Currently, I tried below but it turns out all the data's color will change at once. Is there any way to change one word's color in the same cell?

from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl.styles import colors
book = Workbook()
sheet = book.active
sheet.cell(row=1, column=2).value = "11111"
sheet.cell(row=1, column=2).font = Font(color=colors.GREEN)
sheet.cell(row=1, column=2).value += " 12345"
sheet.cell(row=1, column=2).font = Font(color=colors.RED)
sheet.cell(row=1, column=2).value += " 22222"
Kitty
  • 73
  • 1
  • 4
  • Possible duplicate of [The ability to apply multiple formats to cell with xlwt / openpyxl](https://stackoverflow.com/questions/8429014/the-ability-to-apply-multiple-formats-to-cell-with-xlwt-openpyxl) – Charlie Clark Mar 28 '18 at 07:51
  • I look at the solution in The ability to apply multiple formats to cell with xlwt / openpyxl. It looks like openpyxl is not able to achieve what I want.I will try xlwt. – Kitty Mar 29 '18 at 11:10
  • Here's the bug in openpyxl: https://bitbucket.org/openpyxl/openpyxl/issues/9/add-support-for-rich-text – Bruno Duyé Jul 25 '18 at 11:51

3 Answers3

3

xlswriter may help you.

import xlsxwriter

workbook = xlsxwriter.Workbook('rich_text.xlsx')
worksheet = workbook.add_worksheet()
bold_red = workbook.add_format({'bold': True, 'color': 'red'})
worksheet.write_rich_string('A1',
                            'hello ',
                            bold_red, 'this is bold_red', # style
                            ' and default string')
workbook.close()

enter image description here

jef
  • 3,890
  • 10
  • 42
  • 76
2

This is unfortunately not possible with openpyxl. You'll have to use xlwt (for the legacy Excel format) or xlsxwriter (for the current Excel format).

See https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1503 for confirmation that openpyxl does not (and likely will never) support this functionality.

For more information on xlwt see https://pypi.org/project/xlwt/.

For more information on xlsxwriter see https://pypi.org/project/XlsxWriter/.

Bob Kline
  • 304
  • 1
  • 14
  • And dont forget Xlwings has also been able to do this using its character class. Openpyxl introduced support for this in 3.1 – moken Mar 30 '23 at 12:35
0

This is an older question, but I had a similar issue and was able to find a way to accomplish my goal using examples from the openpyxl documentation at https://openpyxl.readthedocs.io/en/stable/rich_text.html. Note for the code to work, you might need to update to the newest version of openpyxl.

import openpyxl 
from openpyxl.cell.text import InlineFont 
from openpyxl.cell.rich_text import TextBlock, CellRichText

red = InlineFont(color='00FF0000')
green = InlineFont(color='00008000')
book = openpyxl.load_workbook("rich_text.xlsx")
sheet = book.active
rich_text_cell = CellRichText()
rich_text_cell.append('11111')
rich_text_cell.append(TextBlock(green, '12345'))
rich_text_cell.append(TextBlock(red, '22222'))
sheet.cell(row=1, column=2).value = rich_text_cell
book.save('rich_text.xlsx')
TheInfamousWook
  • 1
  • 1