0

I'm writing a program that searches through the first row of a sheet for a specific value ("Filenames"). Once found, it iterates through that column and returns the values underneath it (rows 2 through x).

I've figured out how to iterate through the first row in the sheet, and get the cell which contains the specific value, but now I need to iterate over that column and print out those values. How do I do so?

import os
import sys
from openpyxl import load_workbook

def main():

    column_value = 'Filenames'

    wb = load_workbook('test.xlsx')
    script = wb["Script"]

# Find "Filenames"
for col in script.iter_rows(min_row=1, max_row=1):
    for name in col:
        if (name.value == column_value):
            print("Found it!")
            filenameColumn = name
            print(filenameColumn)
    
# Now that we have that column, iterate over the rows in that specific column to get the filenames
for row in filenameColumn: # THIS DOES NOT WORK
    print(row.value)

main()
deadtrick
  • 5
  • 1
  • 2
  • I presume this is homework otherwise you could just use a Pandas Dataframe.. it is more straight forward – orangepixel Feb 19 '21 at 21:54
  • Does this answer your question? [iterate through all rows in specific column openpyxl](https://stackoverflow.com/questions/38619471/iterate-through-all-rows-in-specific-column-openpyxl) – Tomerikoo Feb 21 '21 at 12:47

1 Answers1

0

You're actually iterating over rows and cells, not columns and names here:

for col in script.iter_rows(min_row=1, max_row=1):
    for name in col:

if you rewrite it that way, you can see you get a cell, like this:

for row in script.iter_rows(min_row=1, max_row=1):
    for cell in row:
        if (cell.value == column_value):
            print("Found it!")
            filenameCell = cell
            print(filenameCell)

So you have a cell. You need to get the column, which you can do with cell.column which returns a column index.

Better though, than iterating over just the first row (which iter_rows with min and max row set to 1 does) would be to just use iter_cols - built for this. So:

for col in script.iter_cols():
      # see if the value of the first cell matches
      if col[0].value == column_value:
         # this is the column we want, this col is an iterable of cells:
         for cell in col:
            # do something with the cell in this column here
Charlie Clark
  • 18,477
  • 4
  • 49
  • 55
Cameron McFee
  • 386
  • 1
  • 4
  • Worth noting that `iter_cols` is not available in `read_only` mode – Tomerikoo Feb 21 '21 at 12:46
  • This will loop through **all** cells of **all** columns before it gets to the header. Probably better to search the first row and then loop over the rows/columns using min_col & max_col = cell.column. – Charlie Clark Feb 21 '21 at 12:46
  • It won't loop through any cells in a column for which the first cell (col[0].value) isn't the header we're looking for – Cameron McFee Feb 22 '21 at 23:10