Automating font color selection of specifc text in spreadsheets?

Question

I have extracted data from a website and formatted it into a CSV, all with Python. My job requires me to check this data and to make it easier I'd like to have the string in column A be used as a conditional to format the font color of the equivalent text in the string in column B in the same row.

For example, if "P90374" is in column A row 1, then "P90374" from the string "C801273 A12313 P90374 J1823132 B12313" contained in column B row 1 should have the font color set to red.

I was originally planning on processing the data in Google Sheets but am open to any possible solutions (though I don't currently have access to Excel).

Hope that makes sense. Many thanks.

Answer 1

You can do it using with "xlsxwriter" library. You can format parts of a string with XlsxWriter using write_rich_string().

I am assuming you have already converted csv to dict. And you have a problem with how to compare and fill color to partially fill red color to text.

Here is the code:

import xlsxwriter

workbook = xlsxwriter.Workbook('sample_sezer_scraped_data.xlsx')
worksheet = workbook.add_worksheet()
red_color = workbook.add_format({'bold': True, 'color': 'red'})

sample_a_row_1 = "P90374"
sample_b_row_1 = "C801273 A12313 P90374 J1823132 B12313 C801273 A12313 P90374 J1823132 B12313"

if sample_a_row_1 in sample_b_row_1:
    parts_of_row = sample_b_row_1.split(sample_a_row_1)
    colored_row = []
    for order, part in enumerate(parts_of_row):
        if order != len(parts_of_row) - 1:
            colored_row.append(part)
            colored_row.append(red_color)
            colored_row.append(sample_a_row_1)
        else:
            colored_row.append(part)

    worksheet.write_rich_string("A1", *colored_row)

workbook.close()