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.
Add a comment
|
1 Answer
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")
