Python Forum
Openpyxl, format color of cells (cols) based on condition.
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Openpyxl, format color of cells (cols) based on condition.
#1
Hello,
I just need format cells based on condition like this.

[Image: ex.png]

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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  unable to remove all elements from list based on a condition sg_python 3 371 Jan-27-2024, 04:03 PM
Last Post: deanhystad
  Turtle Star Fill Color Yellow-White Interchanging Color Effect codelab 9 900 Oct-25-2023, 09:09 AM
Last Post: codelab
  Color a table cell based on specific text Creepy 11 1,822 Jul-27-2023, 02:48 PM
Last Post: deanhystad
  Sent email based on if condition stewietopg 1 802 Mar-15-2023, 08:54 AM
Last Post: menator01
  create new column based on condition arvin 12 2,130 Dec-13-2022, 04:53 PM
Last Post: jefsummers
  How to assign a value to pandas dataframe column rows based on a condition klllmmm 0 797 Sep-08-2022, 06:32 AM
Last Post: klllmmm
  Converting cells in excel to JSON format desmondtay 4 1,679 May-23-2022, 10:31 AM
Last Post: Larz60+
  select Eof extension files based on text list of filenames with if condition RolanRoll 1 1,474 Apr-04-2022, 09:29 PM
Last Post: Larz60+
  Openpyxl-change value of cells in column based on value that currently occupies cells phillipaj1391 5 9,568 Mar-30-2022, 11:05 PM
Last Post: Pedroski55
  How to map two data frames based on multiple condition SriRajesh 0 1,448 Oct-27-2021, 02:43 PM
Last Post: SriRajesh

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020