Reputation: 201
I have an excel file with data stored as strings in different columns. I want to apply the condition >= and change the text color to red if the condition is met in that cell of the column without changing the header background color.
Data: https://1drv.ms/x/s!ArCp0UbnlDouglYGkM83sQtckagM?e=e5nUgn
Condition: If any value in column Qt._For_1s is greater than or equal to 900.0 then the font color of that specific value should turn into Red (without messing with the header colors) else can either be green or stay the same black with white background and saved as xls file.
Note: These column values are saved as strings because they are needed like that. I have tried several conditional formats but could not solve my specific problem.
def highlight_Qt(row):
ret=["" for _ in row.index]
if (row.Qt._For_1s) >= str(900.0): # also tried without str() no results
ret[row.index.get_loc("Qt._For_1s")]="background-color: Red"
return ret
data.style.apply(highlight_Qt, axis=1).to_excel("Data.xlsx", engine='openpyxl')
This messes up the header color and not giving the required results. I am looking this up in StackOverflow posts over the last 5 days but could not find any post related to this specific condition.
Upvotes: 0
Views: 1139
Reputation: 21
One way to do it would be to open your file with openpyxl and format it
from openpyxl.styles import Font
from openpyxl import Workbook
# Open the workbook
from openpyxl import load_workbook
wb = load_workbook('file_name.xlsx')
ws = wb.active
# Get the columns name
ColNames = {}
Current = 0
for COL in ws.iter_cols(1, ws.max_column):
ColNames[COL[0].value] = Current
Current += 1
## Set the font color
ft = Font(color="00FF0000")
## Acces the cells
for row in ws.iter_rows():
cell = row[ColNames['Qt._For_1s']]
try:
if int(cell.value) > 900:
cell.font = ft
except:
pass
wb.save("styled.xlsx")
Upvotes: 1