Python Forum

Full Version: OpenPyxl: How to iterate through each Column (in 1 row) to find a value?
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
When using Python OpenPyxl, how would I iterate through each Column (in only one row) to find a value?

Here's my current (failing) attempt -- (There appears to be an error in (at least) my 2nd for loop.)

book = openpyxl.load_workbook(excelFile)
for sheet in book.worksheets: #For each worksheet
    for colidx in sheet.iter_cols(sheet.min_col,sheet.max_col): #For each Column in a worksheet
        if sheet.cell(1,colidx).value == "ValueImLookingFor": #Search each Column in only Row #1 for value
            print ("Found ValueImLookingFor in COLUMN " + colidx)
ps: Xposted elsewhere last week, but no working answers yet. I'll post the answer to both forums once found.
Thanks so much in advance,
CG
Can you please share what error message(s) you are getting?

That aside, it looks like iter_cols takes three arguments, min_row, max_col, max_row. I think you may have passed the wrong arguments here. Doesn't look like you can pass a min_col either. See https://openpyxl.readthedocs.io/en/stable/tutorial.html.

Try (let me know if it works!) :
book = openpyxl.load_workbook(excelFile)
for sheet in book.worksheets:
     col_range = sheet[sheet.min_column : sheet.max_column]
     for colidx in col_range:
         if sheet.cell(1,colidx).value == "ValueImLookingFor": #Search each Column in only Row #1 for value
            print ("Found ValueImLookingFor in COLUMN " + colidx)
I prefer to use pandas for complicated logic operations as I find it much more flexible.
Iterating through pandas dataFrame objects is generally slow. Pandas iteration beats the whole purpose of using DataFrame. It is an anti-pattern and is something you should only do when you have exhausted every other option. It is better look for a List Comprehensions , vectorized solution or DataFrame.apply() method.

Pandas DataFrame loop using list comprehension

result = [(x, y,z) for x, y,z in zip(df['Name'], df['Promoted'],df['Grade'])]