Openpyxl how to colour word, not whole cell

Question

import openpyxl as xl
from openpyxl.styles import Font, colors

wb = xl.load_workbook('schedule.xlsx')
sheet = wb['Schedule']

for row in range(2, sheet.max_row + 1):
    for column in range(1, 7):
        cell = sheet.cell(column, row)
        word = str(cell.value)

        if '-' in word:
            index = word.index('-')
            part = word[index:]
            print(part)

            # ???????
            cell.font = Font(color=colors.RED)

wb.save('new_schedule.xlsx')

Hello, I want this program to pick cells that contain '-'. Then it should colour only the part after '-' with red colour. Code I made makes whole cell red, I haven't found anything useful in documentation of this package.

Would really appreciate some suggestions

Answer 1

I looked through the documetation, didn't find anything useful. After looking here I found these two: This user tried doing the same thing openpyxl change one word's color in the same cell

The user moved to this thread at last - The ability to apply multiple formats to cell with xlwt / openpyxl

Answer 2

Yes we can do it with:

from openpyxl.cell.rich_text import CellRichText,TextBlock,InlineFont
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows

and the logic is:

wb  = Workbook()
ws = wb.active

for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

red = InlineFont(color='00FF0000')
black = InlineFont(color='00000000')

for row in ws.iter_rows(min_row=2):

    for cell in row:

        rich_text = CellRichText()

        wordidx = cell.value.index('-')  
     
        rich_text.append(TextBlock(black,cell.value[:wordidx]))
           
        rich_text.append(TextBlock(red,cell.value[wordidx:]))
        
        cell.value = rich_text

wb.save("test.xlsx")
wb.close()

Answer 3

Support for CellRichText() is only from openpyxl version 3.1.0.
You can read the documentation.