Ok so basically what I'm trying to do is a search through all of 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.
I apologize if my code is hard to read, still very much a beginner.
So the below will run fine and prints the dictionary "dictIPExcel" with no issue:
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')
However, if I move the "dictIPExcel" to within the second for loop, it throws a 'TypeError: Strings must be integers.' Now from what I understand this means that it is basically not ok with having the 'A50' for example be the index in the dictionary. My question is why does this work outside of that loop? The data index in the dictionary isn't changing outside of the loop vs. inside, yet the error is only thrown inside. Code with loop inside:
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
Below is the output:
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
Is there anything I can do to allow this to run within the loop? Or perhaps there is a better way of going about what I am trying to do in going through every sheet and highlighting matching data?