I am using pyopenxl to output some excel spreadsheets, I encountered a problem with font conditional formatting. I want to highlight the cells lesser than 0 with red color and here's what I've done:
from pyopenxl import formatting, styles
red_font = styles.Font(size=self.font_size, bold=bold, color=self.red_color_font)
red_fill = styles.PatternFill(start_color=self.red_color, end_color=self.red_color, fill_type='solid')
self.ws.conditional_formatting.add(
cell.coordinate,
formatting.CellIsRule(operator='lessThan', formula=['0'], fill=red_fill, font=red_font)
)
So I simply created styles for font and fill and applied them for my cell. The bad thing is that it doesn't work. As soon as I remove the font formatting from the CellIsRule()
everything goes back to normal and I'm having my cell filled with red. But the thing is that I need to change the color as well, does anyone has any idea what's wrong with my code? Or maybe with openpyxl?
To highlight cells that are less than zero, you can use the following code when using openpyxl
version 2.2.6
:
from openpyxl import formatting, styles
wb = Workbook()
ws = wb.active
red_color = 'ffc7ce'
red_color_font = '9c0103'
red_font = styles.Font(size=14, bold=True, color=red_color_font)
red_fill = styles.PatternFill(start_color=red_color, end_color=red_color, fill_type='solid')
for row in range(1,10):
ws.cell(row=row, column=1, value=row-5)
ws.cell(row=row, column=2, value=row-5)
ws.conditional_formatting.add('A1:A10', formatting.CellIsRule(operator='lessThan', formula=['0'], fill=red_fill, font=red_font))
ws.conditional_formatting.add('B1:B10', formatting.CellIsRule(operator='lessThan', formula=['0'], fill=red_fill))
wb.save("test.xlsx")
This displays as follows:
For openpyxl version 2.5.1,CellIsRule
is now inside rule
as follows:
from openpyxl import formatting, styles, Workbook
wb = Workbook()
ws = wb.active
red_color = 'ffc7ce'
red_color_font = '9c0103'
red_font = styles.Font(size=14, bold=True, color=red_color_font)
red_fill = styles.PatternFill(start_color=red_color, end_color=red_color, fill_type='solid')
for row in range(1,10):
ws.cell(row=row, column=1, value=row-5)
ws.cell(row=row, column=2, value=row-5)
ws.conditional_formatting.add('A1:A10', formatting.rule.CellIsRule(operator='lessThan', formula=['0'], fill=red_fill, font=red_font))
ws.conditional_formatting.add('B1:B10', formatting.rule.CellIsRule(operator='lessThan', formula=['0'], fill=red_fill))
wb.save("test.xlsx")