Python Forum
OpenPyxl: How to iterate through each Column (in 1 row) to find a value?
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
OpenPyxl: How to iterate through each Column (in 1 row) to find a value?
#1
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
Reply
#2
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.
Reply
#3
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'])]
buran write Apr-06-2021, 06:03 AM:
URL removed.
Necroposting with sole purpose to promote your site via clickbite is unacceptable and will not be tolerated.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Openpyxl-change value of cells in column based on value that currently occupies cells phillipaj1391 5 1,363 Mar-30-2022, 11:05 PM
Last Post: Pedroski55
  Find last filled column in openpyxl Irv1n 2 3,499 Jan-16-2022, 11:05 AM
Last Post: Pedroski55
  pandas pivot table: How to find count for each group in Index and Column JaneTan 0 1,550 Oct-23-2021, 04:35 AM
Last Post: JaneTan
  How can I iterate through all cells in a column (with merge cells) with openpyxl? aquerci 1 5,014 Feb-11-2021, 09:31 PM
Last Post: nilamo
  Openpyxl tkinter search a value in Excel column Heathcliff_1 0 2,068 Dec-02-2020, 04:35 PM
Last Post: Heathcliff_1
  Python Openpyxl is unable to check from Column 6 onwards Skye 0 929 Oct-13-2020, 06:11 AM
Last Post: Skye
  Using OpenPyXL How To Read Entire Column Into Dictionary jo15765 1 1,799 Jun-08-2020, 04:10 AM
Last Post: buran
  Need to copy column of cell values from one workbook to another with openpyxl curranjohn46 3 8,941 Oct-12-2019, 10:57 PM
Last Post: curranjohn46
  Openpyxl -Coming up with a single column from a multi column data set Givan007 1 1,987 Sep-07-2018, 04:29 AM
Last Post: Prabakaran141
  find empty cells in a column Pedroski55 2 22,093 Sep-18-2017, 01:27 PM
Last Post: Pedroski55

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020