如何使用Xlsxwriter更改字符串中的特定字体颜色？

Question

我想使用xlsxwriter更改字符串中的特定文本颜色。 我的想法是用彩色文本代替非彩色文本。 但是失败了。。。你知道吗

结果显示“TypeError:'Format'对象不能解释为整数”

似乎f{wrong}，cell（格式）是一个整数。你知道吗

这很奇怪，因为如果不能使用replace()来更改字符串中的单个字体颜色，我们还能做什么呢？你知道吗

我的输出是：

应该是：

我的代码：

    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

所以我在工作中遇到了一个类似的情况，我认为不可能部分格式化字符串，更不用说根据一些特定的条件，比如你的情况。我看到了你的帖子和令人惊叹的John Mcnamara的回复，我决定尝试使用rich string方法（我真的怀疑是否还有其他方法）。你知道吗

首先让我提到，我能够实现它使用熊猫和xlsxwriter。其次，使用pandas和xlsxwriter应该避免for循环（因为文件行越多，程序完成所需的时间就越长），但我无法以不同的方式实现它。您需要在那里应用一些错误处理，因为如果索引值不存在，它将引发一个值错误。最后，我没有包括一个单元格包含多个错误单词的情况，我们需要格式化所有这些单词。你知道吗

我会这样做：

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()

最终输出与所需输出相同：

我希望这有帮助。你知道吗