Openpyxl, format color of cells (cols) based on condition. - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: Openpyxl, format color of cells (cols) based on condition. (/thread-21041.html) |
Openpyxl, format color of cells (cols) based on condition. - genderbee - Sep-11-2019 Hello, I just need format cells based on condition like this. So different colors for different values of cells, 1, 2, 3, etc. I tried something like this. from openpyxl import Workbook, load_workbook from openpyxl.styles import Color, Font, Border from openpyxl.styles.differential import DifferentialStyle from openpyxl.formatting.rule import ColorScale, ColorScaleRule, CellIsRule, FormulaRule # New workbook #new workbook #wb = Workbook() import sys filename = sys.argv[1] # Load from file wb = load_workbook(filename,data_only = True) #sheet_ranges = wb['table1'] #print(sheet_ranges['b18'].value) # grab the active worksheet ws = wb.active first = FormatObject(type='min') last = FormatObject(type='max') colors match the format objects: colors = [Color('AA0000'), Color('00AA00')] cs2 = ColorScale(cfvo=[first, last], color=colors) # a three color scale would extend the sequences mid = FormatObject(type='num', val=40) colors.insert(1, Color('00AA00')) cs3 = ColorScale(cfvo=[first, mid, last], color=colors) # create a rule with the color scale from openpyxl.formatting.rule import Rule rule = Rule(type='colorScale', colorScale=cs3) OR ws.conditional_formatting.add('A1:A10',ColorScaleRule(start_type='min', start_color='AA0000',end_type='max', end_color='00AA00')) red_fill = PatternFill(bgColor="FFC7CE") dxf = DifferentialStyle(fill=red_fill) r = Rule(type="expression", dxf=dxf, stopIfTrue=True) r.formula = ['$A2="DDD"'] ws.conditional_formatting.add("A1:C10", r) ws['a1'].formula = ['a3+a4'] ws['B3'].fill = fill = GradientFill(stop=("000000", "FFFFFF")) # save to file wb.save("pdf-to-xlsx.xlsx")But it is not working. And second problem, when I open xlsx, it shows me, that Undefined style record '0' . Why?Thank you very much for reactions. BR G. |