A dictionary inside a for loop leads to a TypeError, outside a for loop works fine

Question

Okay, so basically I'm trying to search all the values ​​in a column in an excel file and then mark them green if they match any of the data values ​​in a separate text file.

Sorry if my code is hard to read, but still very new to it.

So, the following will work fine and print the dictIPExcel dictionary without any problems:

dictIPExcel = {}
##############IMPORTANT CHANGE HERE, FILE NAME########################
#Change file name here to suit your file
wb = openpyxl.load_workbook('file.xlsx')
#Copy sheet name here
sheet = wb.active

#What font color to change to below

greenFill = PatternFill(start_color='0000B200', end_color='0000B200', fill_type='solid')

#This will create a list containing every line in the below txt file
IPlist = open('No Config or Denied devices.txt').read().split('\n')

#Loops through A19-A100, gathering the value in each row into dictIPExcel
for t in range(19,100):
    dictIPExcel["A{0}".format(t)] = sheet['A'+ str(t)].value
print dictIPExcel
sheetz = wb.sheetnames

for a in sheetz:
    sheet = a
    dictIPExcel = {}

    for l in range(19,100):
        for x,y in dictIPExcel.items():
            for z in IPlist:
                if y == z:
                    print "match"
                    sheet[x].fill = greenFill
wb.save('textCopy4.xlsx')

, "dictIPExcel" , "TypeError: ". , , , , "A50", , . , ? , . :

for a in sheetz:
    sheet = a
    dictIPExcel = {}
    for t in range(19,100):
        dictIPExcel["A{0}".format(t)] = sheet['A'+ str(t)].value
    for l in range(19,100):
        for x,y in dictIPExcel.items():
            for z in IPlist:
                if y == z:
                    print "match"
                    sheet[x].fill = greenFill

:

Traceback (most recent call last):
  File "C:..\testing.py", line 49, in <module>
    dictIPExcel["A{0}".format(t)] = sheet['A'+ str(t)].value
TypeError: string indices must be integers

-, , ? , , , , ?

Answer 1

, , , , . , , , .get_sheet_by_name():

for a in sheetz:
    sheet2 = wb.get_sheet_by_name(a)
    dictIPExcel = {}
    for t in range(19,100):
        dictIPExcel["A{0}".format(t)] = sheet2['A'+ str(t)].value
    for l in range(19,100):
        for x,y in dictIPExcel.items():
            for z in IPlist:
                if y == z:
                    print "match"
                    sheet2[x].fill = greenFill
wb.save('Copy.xlsx')

script, .:)

Answer 2

, - w/ dictIPExcel. , sheet str, dict, .

:

#Copy sheet name here
sheet = wb.active

:

for a in sheetz:
    sheet = a

, , str sheetz = wb.sheetnames.

python. , . :

>>> for i in range(5): pass
... 
>>> print i
4
>>> 

(sheet_name?), .

!

Answer 3

In the first case (correct)

sheet = wb.active

therefore sheetis a dictionary.

In the second (wrong) case

sheet = a   # for a in sheetz, where  sheetz = wb.sheetnames

sheetis a list (so it needs integer indexes).

Answer 4

sheetz is a list of sheet names that are strings. You need to get a handle usingwb.get_sheet_by_name

for a in sheetz:
    sheet=wb.get_sheet_by_name(a)
    dictIPExcel = {}
    for t in range(19,100):
        dictIPExcel["A{0}".format(t)] = sheet['A' + str(t)].value