How to change a certain font color in a string using Xlsxwriter?

Question

I want to change a certain text color in the string using xlsxwriter. My thought was to replace non-colored text with colored text. But it failed...

The result shows "TypeError: 'Format' object cannot be interpreted as an integer"

It seems like f"{wrong}",cell_format) is a integer.

It's odd, because what else can we change single font color among string if we cannot use replace() to do so?

My output is :

It should be :

My code:

    import xlsxwriter

    from functools import partial

    def x_in_y(word, inner):
        return inner in word

    workbook = xlsxwriter.Workbook('C:\\Users\\Claude\\Desktop\\hello.xlsx')
    worksheet = workbook.add_worksheet()
    cell_format = workbook.add_format()

    cell_format.set_font_color('red')
    words = [
        ('pasport','passport'),
        ('limmit','limit'),
        ('putt','put')
    ]

    sentence =['putt dweqrerwr','dfsdf putt','limmit','pasport']
    row = 0

    for wrong,correct in words:
        filtered_names = filter(partial(x_in_y, inner=wrong), sentence)
        next_elem = next(filtered_names, None)

        if next_elem:
            worksheet.write(row,0, f"Typo: {wrong} 'should be {correct}'")
            worksheet.write(row+1,0,next_elem.replace(wrong, f"{wrong}",cell_format))

        for name in filtered_names:
            worksheet.write(row+2,0,name)
        row += 2
    workbook.close()

Answer 1

So i had a similar case in my work and i thought that it was not possible to partially format a string, let alone according to some specific conditions like in your case. I saw your post and the reply by the amazing John Mcnamara and i decided to give it a try using the rich string method (i really doubt if there is another way).

Firstly let me mention that i was able to achieve it using pandas and xlsxwriter. Secondly, for loops should be avoided with pandas and xlsxwriter (because the more rows a file has the longer it takes for the program to finish) but i was not able to achieve it differently. You need to apply some error handling there because if the index value does not exist it will raise a value error. Finally i did not include the case where a cell contains more than one wrong word and we need to format all of them.

This is how i would do it:

import pandas as pd

# Create your dataframe
df = pd.DataFrame(data={'A': ["Typo: pasport 'should be passport'", 'pasport',
                                "Typo: limmit 'should be limit'", 'limmit',
                                "Typo: putt 'should be put'", 'putt dweqrerwr',
                                'dfsdf putt']})

# Create a list with the words that are wrong
wrong_words = ['pasport', 'limmit', 'putt']

# Kickstart the xlsxwriter
writer = pd.ExcelWriter('Testing rich strings.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', header=False, index=False)
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Define the red format and a default format
cell_format_red = workbook.add_format({'font_color': 'red'})
cell_format_default = workbook.add_format({'bold': False})

# Start iterating through the rows and through all of the words in the list
for row in range(0,df.shape[0]):
    for word in wrong_words:
        try:
            # 1st case, wrong word is at the start and there is additional text
            if (df.iloc[row,0].index(word) == 0) \
            and (len(df.iloc[row,0]) != len(word)):
                worksheet.write_rich_string(row, 0, cell_format_red, word,
                                            cell_format_default,
                                            df.iloc[row,0][len(word):])

            # 2nd case, wrong word is at the middle of the string
            elif (df.iloc[row,0].index(word) > 0) \
            and (df.iloc[row,0].index(word) != len(df.iloc[row,0])-len(word)) \
            and ('Typo:' not in df.iloc[row,0]):
                starting_point = df.iloc[row,0].index(word)
                worksheet.write_rich_string(row, 0, cell_format_default,
                                    df.iloc[row,0][0:starting_point],
                                    cell_format_red, word, cell_format_default,
                                    df.iloc[row,0][starting_point+len(word):])

            # 3rd case, wrong word is at the end of the string
            elif (df.iloc[row,0].index(word) > 0) \
            and (df.iloc[row,0].index(word) == len(df.iloc[row,0])-len(word)):
                starting_point = df.iloc[row,0].index(word)
                worksheet.write_rich_string(row, 0, cell_format_default,
                                            df.iloc[row,0][0:starting_point],
                                            cell_format_red, word)

            # 4th case, wrong word is the only one in the string
            elif (df.iloc[row,0].index(word) == 0) \
            and (len(df.iloc[row,0]) == len(word)):
                worksheet.write(row, 0, word, cell_format_red)

        except ValueError:
            continue

writer.save()

Final output identical to your desired output:

I hope that this helps.