change the text color with python when write the excel file-CodePudding

How can I change the font color with some row when write the dataframe to excel file?

This is the sample dataframe.

If the amount<0,then the text color with whole row change to red except the name.

CodePudding user response：

Here is a sample dataframe that you can insert into excel with the correct color coding in account column

import openpyxl
# Sample dataframe
data = {'name': ['A', 'B', 'C', 'D', 'E'],
        'Amount':[7, -2, 5, -1, 0], 
        'unit_price': [600, 500, 440, 510, 350], 
        'total_price': [4200, -1000, -2200, -510, 0]}
df=pd.DataFrame(data)

wb = openpyxl.Workbook()
ws = wb.active

#Write data
from openpyxl.utils.dataframe import dataframe_to_rows
rows = dataframe_to_rows(df, index=False)
for r_idx, row in enumerate(rows, 1):
    for c_idx, value in enumerate(row, 1):
         ws.cell(row=r_idx, column=c_idx, value=value)

#Check and set format to RED
for row in range(0, df.shape[0]):
    if df.iloc[row,1] < 0:
        for col in range(0,df.shape[1]):
            ws.cell(row 2, col 1).font = openpyxl.styles.Font(color='FF0000')

wb.save("output_file.xlsx")