Python Openpyxl/Pandas change font color and bold font based on condition in Excel and save-CodePudding

Even though this question seems to have been answered, I fear some of the posts might be outdated, or openpyxl has deprecated this functionality.

I need to change some font formatting within an Excel file using Python based upon a certain condition. If in columns 'MIC' and 'B5BA' the cell == '2 Person Entry Error' change cell font color to Red and Bold the font. I followed the tutorial and other examples to a tee, but the font is not changing. I iterate through columns and rows to obtain cell coordinates, I compare the value within the cell, if cell value meets the condition change the font. The cell font is not changing in my saved file.

I posted code that will automatically write the excel workbook from a df, then read the Excel workbook. Has openpyxl deprecated this capability? Help is appreciated! Code is below.

import pandas as pd
from openpyxl.styles import Font, Color, colors
from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter

data = [
    [1, '2022-10-10', '2022-10-10', 'Monday', 'Lunch', '2 Person Entry Error', '2 Person Entry Error', None],
    [1, '2022-10-10', '2022-10-10', 'Monday', 'Dinner', '2 Person Entry Error', '2 Person Entry Error', None],
    [2, '2022-10-10', '2022-10-10', 'Monday', 'Dinner', 'John', None, None],
    [2, '2022-10-10', '2022-10-11', 'Tuesday', 'Lunch', 'Bob', 'Mackenzie ', None],
    [3, '2022-10-10', '2022-10-11', 'Tuesday', 'Dinner', '2 Person Entry Error', '2 Person Entry Error', None],
    [3, '2022-10-10', '2022-10-12', 'Wednesday', 'Lunch', '2 Person Entry Error', '2 Person Entry Error', None]
]
cols = ['Store', 'Start Date', 'Date', 'Day of Week', 'Shift', 'MIC', 'B5BA', 'Task']
df = pd.DataFrame(data, columns=cols)
df.to_excel('input.xlsx', index=False)

input_file_path = 'input.xlsx'
output_file_path = 'output.xlsx'

wb = load_workbook(input_file_path)
ws = wb.active

col_start = 6
col_end = 7
row_start = 2
row_end = ws.max_row

for col_num in range(col_start, col_end   1):
    col_letter = get_column_letter(col_num)
    for row_num in range(2, row_end   1):
        # cell_coordinate = ws.cell(row=row_num, column=col_num)
        cell_coordinate = ws[f'{col_letter}{row_num}']
        print(cell_coordinate, f'{col_letter}{row_num}')
        if cell_coordinate.value == '2 Person Entry Error':
            cell_coordinate = Font(color='FF0000', bold=True)

wb.save(output_file_path)
wb.close()

CodePudding user response：

Without any links to "examples" mentioned, it is impossible to judge if you are doing the exact same thing as they advertise. Either way, the fix should be as follows. Change:

            cell_coordinate = Font(color='FF0000', bold=True)

Into:

            cell_coordinate.font = Font(color='FF0000', bold=True)

See the