what's wrong with my iteration code

Question

Please vote up for my question I just got banned from posting questions at a crucial time for me. I'm a beginner programmer. Please guys.

I have two lists, I need to iterate over each item in both lists (in order) to change Excel cell fill color using openpyxl. I get a syntax error in the line that starts with ws.cell(row = int(items), column

Here is the code:

color_lst= ['FF333399', 'FFFFFF00', 'FFC0C0C0', 'FF00FF00', 'FF008080', 'FF00FFFF', 'FF000080', 'FF800000', 'FF969696', 'FFFF99CC', 'FFFFFF99', 'FF800080', 'FF99CC00', 'FF0000FF']

label_list_for_col_header= ['Energy', 'Green Buildings', 'High Performance Buildings', 'Computing', 'Design', 'Infrared', 'Laser scanning', 'Outdoor Thermal Comfort', 'Urban Desgin', 'Daylighting', 'Thermal Comfort', 'Parametric', 'Simulation Tools', 'N']

gerenral_lst= list()

m= 0    
for row in ws.iter_rows('D2:D11'):
    for cell in row:
        if label_list_for_col_header[m] in cell.value : 
            general_lst.append(cell.row)
        for items in range(len(general_lst)):

            ws.cell(row = int(items), column = int(label_list_for_col_header.index(label_list_for_col_header[0])).fill = PatternFill(start_color=str(color_lst[m]), end_color=str(color_lst[m]) , fill_type='solid')
    general_lst = []
    m +=1

I fixed the code as per the suggestions, however the loop executed only once. Not sure why

m= 0    
for row in ws.iter_rows('D2:D11'):
    for cell in row:
        if label_list_for_col_header[m] in cell.value : 
            general_lst.append(cell.row)
        for items in range(len(general_lst)):

            ws.cell(row = general_lst[items], column = int(m+5)).fill = PatternFill(start_color=str(color_lst[m]), end_color=str(color_lst[m]) , fill_type='solid')
    general_lst = []
    m +=1

I finally got the code to work in case someone else is having the same problem, here is the working code:

m= 0    
for lbls in label_list_for_col_header: 
    j= int(label_list_for_col_header.index(lbls))+5
    for row in ws.iter_rows('D2:D11'):
        for cell in  row:

            if lbls in cell.value : 
                general_lst.append(cell.row)
                for items in range(len(general_lst)):

                    ws.cell(row = general_lst[items], column = j).fill = PatternFill(start_color=str(color_lst[m]), end_color=str(color_lst[m]) , fill_type='solid')
    general_lst = []
    m +=1

I just went over all parenthesis, I believe I'm not missing any. However, the color should be input with quotes like this 'FFEE1111' . Could the problem be extracting a string from list FFEE111 ( without quotes) — Julia_arch, Dec 10 '15 at 08:10
I have another code that's working that has almost the same structure as the one above, the line looks like this : ws.cell(row = 2, column = 3).fill = PatternFill(start_color='FF993366', end_color='FF993366', fill_type='solid') — Julia_arch, Dec 10 '15 at 08:16
`ws.cell(row = int(items), column = int(label_list_for_col_header.index(label_list_for_col_header[0])).fill` Please count paranthesis. You need a closing one before `.fill` — Lafexlos, Dec 10 '15 at 08:19
You're absolutely right. I fixed it however I got another error saying " Row or column values must be at least 1" . Right now I'm trying to figure out what the problem is — Julia_arch, Dec 10 '15 at 08:21
Noel, Thanks for pointing out the problem. I fixed that part and I'm going to update the code above. my loop executed only once though, not sure why — Julia_arch, Dec 10 '15 at 08:31
It looks like you are adding to `general_lst` while trying to loop over it. This will cause all kinds of problem due to the internal counter Python is using. I see no need for `general_lst` just set the fill on the cell if the condition is met. — Charlie Clark, Dec 10 '15 at 09:47
Is this question essentially the same as http://stackoverflow.com/questions/34186335/search-a-word-in-a-text-string-in-excel-sheet-using-openpyxl/34186722 ? If so they should probably be merged. — Charlie Clark, Dec 10 '15 at 10:11

score 1 · Answer 1 · answered Dec 10 '15 at 08:46

1

In reference to the error of the edit:

You can iterate over a list in this way:

for items in general_lst:

No need for range(len())

answered Dec 10 '15 at 08:46

k4ppa

4,122
8
26
36

Please vote up for my question I just got banned from posting questions at a crucial time for me. I'm a beginner programmer. Please guys. – Julia_arch Dec 12 '15 at 08:48

score 1 · Accepted Answer · answered Dec 10 '15 at 09:56

It's important to try keep as much procedural code out of the loops as possible. This makes it easier to understand and will probably be faster. So, for example, you can make a list of all the possible fills in advance.

Guessing at what you want to do I think the following code may work or be close enough. It's important to provide as much context as possible, ie. what ws is.

from openpyxl import load_workbook
wb = load_workbook("List of books about buildings.xlsx")
ws = wb.active

color_lst= ['FF333399', 'FFFFFF00', 'FFC0C0C0', 'FF00FF00', 'FF008080', 'FF00FFFF', 'FF000080', 'FF800000', 'FF969696', 'FFFF99CC', 'FFFFFF99', 'FF800080', 'FF99CC00', 'FF0000FF']

label_list_for_col_header= ['Energy', 'Green Buildings', 'High Performance Buildings', 'Computing', 'Design', 'Infrared', 'Laser scanning', 'Outdoor Thermal Comfort', 'Urban Desgin', 'Daylighting', 'Thermal Comfort', 'Parametric', 'Simulation Tools', 'N']

fills = [PatternFill(fgColor=color), bgColor=color, fill_type="solid") for color in color_lst]


for idx, row in enumerate(ws.iter_rows('D2:D11')):
    for cell in row:
        if label_list_for_col_header[idx] in cell.value : 
            cell.fill = fills[idx]

what's wrong with my iteration code

2 Answers2