Python Forum
coloring cells in a row with openpyxl
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
coloring cells in a row with openpyxl
#1
Hi All,

I am struggling with something and looking everywhere for a clue. The scenario is this: I am searching column 2 of a spreadsheet for a value in a list called subid. See code below.

cells_in_row_with_subid_match = []
for row in ws2.iter_rows(min_col=2, max_col=2):
    for cell in row:
        if cell.value in subID:
            #this next line appends the list of rows in a list
            cells_in_row_with_subid_match.append(cell.row)
That above code produces a list of row with the subid value: [10, 18, 529, 657]

I now want to patternfill each of the rows in the list using:

PatternFill(start_color='c9e1f8',end_color='c9e1f8',fill_type='solid')

I can do that one row at a time like this:

for rows in ws2.iter_rows(min_row=13, max_row=13, min_col=1):
    for cell in rows:
      cell.fill = PatternFill(start_color='c9e1f8',end_color='c9e1f8',fill_type='solid')
But how do I loop through the list of rows [10, 18, 529, 657] and apply the color?
Reply
#2
I have a list:

[10, 18, 529, 657]

I want to loop through the above list and replace XX in the first line below with 10 then 18 then 529 and 657 :

for rows in ws2.iter_rows(min_row=XX, max_row=XX, min_col=1):
    for cell in rows:
      cell.fill = PatternFill(start_color='c9e1f8',end_color='c9e1f8',fill_type='solid')
so it would look below for example:

for rows in ws2.iter_rows(min_row=10, max_row=10, min_col=1):
    for cell in rows:
      cell.fill = PatternFill(start_color='c9e1f8',end_color='c9e1f8',fill_type='solid')
I am trying to grasp how to approach this. Any suggestions?
Reply
#3
why do you need to create list of row numbers and then iterate again over rows?
for cell in ws2['B']: # iterate over cells in column B
    if cell.value in subID: # check cell value
        for row_cell in ws2[cell.row]: #iterate over cell in the row
            row_cell.fill = PatternFill(start_color='c9e1f8',end_color='c9e1f8',fill_type='solid')
not tested but it should work
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#4
That worked. Thank you so much!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Openpyxl-change value of cells in column based on value that currently occupies cells phillipaj1391 5 9,572 Mar-30-2022, 11:05 PM
Last Post: Pedroski55
  How can I iterate through all cells in a column (with merge cells) with openpyxl? aquerci 1 7,445 Feb-11-2021, 09:31 PM
Last Post: nilamo
  Can you help me to merge the cells with OpenPyXL? TurboC 1 2,166 Feb-01-2021, 12:54 AM
Last Post: Larz60+
  [openpyxl] Increment cells being pasted into Template Kristenl2784 4 3,507 Jul-16-2020, 10:00 PM
Last Post: Kristenl2784
  Copy certain cells into new workbook certain cells Kristenl2784 4 2,446 Jul-14-2020, 07:59 PM
Last Post: Kristenl2784
  openpyxl Pasting data to different cells Kristenl2784 3 7,558 Jun-15-2020, 08:50 PM
Last Post: Yoriz
  Openpyxl, format color of cells (cols) based on condition. genderbee 0 8,039 Sep-11-2019, 01:05 PM
Last Post: genderbee
  Graph coloring problem jackrus1010 0 4,361 Oct-20-2018, 10:19 AM
Last Post: jackrus1010

Forum Jump:

User Panel Messages

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