Assign a fill pattern to worksheet cell based on column title and value in another column using openpyxl

Laucrimus 0
Viewed 32

I have a large excel data file, with gapped rows at the top, a mix of data types and various blank cells. I want to pattern fill blank cells based on a value in one cell in the row and the column heading being found in a list.

I have:

enter image description here

I would like:

enter image description here

So far I have been able to access each cell and pattern fill on the basis on the cell contents (a random string):

import openpyxl
from openpyxl.styles.colors import Color
from openpyxl.styles import PatternFill

wbInPanel = openpyxl.load_workbook("file\path.xlsx")
shInPanel = wbInPanel['SheetName']

c = Color(indexed=55)
fmtFillPattern = PatternFill(start_color=c,fill_type='lightTrellis')

for row in shInPanel.iter_rows(shInPanel.min_row,shInPanel.max_row):
    for cell in row:
        if cell.value == '*?!*':
            cell.fill = fmtFillPattern
            cell.value = ''


wbInPanel.save("file\path.xlsx")
wbInPanel.close()

I also have my list of headings:

headingsList = ['Heading3','Heading5']

I know I now need to include a couple of if loops, one for if Heading2 == 'A', which needs to be changeable as I may also wish to search if Heading4 > 5, and one for if cell is in col with heading in list.

  1. Where/How do I put the if loops in?
  2. How do I search the heading list whilst I am in the cell in the loop?
formatting openpyxl loops python
1 Answers

I have managed to brute force an answer to my question that gives the desired result. However, I suspect there is a more elegant way of doing this.

The key: when looping over rows and columns using worksheet.iter_rows, the cell locations can be found using:

  • Column number: cell.col_idx
  • Column letter: cell.column_letter
  • Row number: cell.row
  • A1 coordinate: cell.coordinate

My solution:

  1. loop over the heading row and get the column number of the heading in my list.
  2. loop over column with search condition to get list of rows satisfying condition.
  3. loop over all rows and columns, searching if row or column match in the relative list, then applying the fill.

Code:

import pandas as pd
import openpyxl
from openpyxl.styles.colors import Color
from openpyxl.styles import PatternFill

wbInPanel = openpyxl.load_workbook("file\path.xlsx")
shInPanel = wbInPanel['Sheet1']

c = Color(indexed=55)
fmtFillPattern = PatternFill(start_color=c,fill_type='lightTrellis')

# Find column numbers for headings in list
## Setup df
headingList = ['Heading3','Heading5']
colNumList = ['','']
dfHeadings = pd.DataFrame(
    {'Heading': headingList,
     'ColNum': colNumList
     })
##  search for col nums
for row in shInPanel.iter_rows(shInPanel.min_row,shInPanel.min_row):
    for cell in row:
        first_row=cell.row
        x_count=-1
        for x in dfHeadings['Heading']:
            x_count+=1
            if x == cell.value:
                dfHeadings['ColNum'][x_count] = cell.col_idx

# Find row numbers to be changed
## initialise list
rowsToFill = []

## search for rows to fill
row_count=0
for row in shInPanel.iter_rows(shInPanel.min_row,shInPanel.max_row):
    row_count+=1
    for cell in row:
        #print(str(cell.col_idx) + " " + cell.column_letter + " " + str(cell.row)+ " " + str(cell.coordinate))
        if row_count==1:
            pass
        if cell.col_idx==2:
            if cell.value == 'A':
                rowsToFill.append(cell.row)

# Apply fill
for row in shInPanel.iter_rows(shInPanel.min_row+1,shInPanel.max_row):
    for cell in row:
        for y in rowsToFill:
            if cell.row==y:
                for z in dfHeadings['ColNum']:
                    if cell.col_idx==z:
                        if pd.isna(cell.value) == True:
                            cell.fill = fmtFillPattern

wbInPanel.save("file\path.xlsx")
wbInPanel.close()
Laucrimus0
Related