Python Forum

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.