Python / Openpyxl - Find Strings in column and return row number

Question

I'm a fairly new Python user and I have an excel sheet that contains multiple unformatted tables. I am trying to iterate through column B with Python and openpyxl in order to find the headers of the respective table. When I find the header, I would like to save the row number in a variable. Unfortunately, the code is not running as intended and I am not receiving any error message. Below you can find a screenshot of a sample excel sheet as well as my code. Thank you for your help!

start = 1
end = 14
sheet = wb[('Positioning')]

for col in sheet.iter_cols(min_col=2,max_col=2, min_row = start, max_row=end):
    for cell in col:
        if cell.value == 'Table 1':
            table1 = cell.row
        elif cell.value == 'Table 2':
            table2 = cell.row

Screenshot - Excel Example

Are the headers always be named 'Table 1','Table 2', etc.. (incremental)? — HaR
– HaR, Commented May 25, 2018 at 12:16

bonney · Accepted Answer · 2019-11-02 17:52:19Z

Here are ways to search for a string in column or row. You may use column or col_idx which are terms inherent to openpyxl to denote alphabets and number of an Excel sheet respectively.

wb = load_workbook()
ws = wb.active

col_idx, row = search_value_in_col_index(ws, "_my_search_string_")

def search_value_in_column(ws, search_string, column="A"):
    for row in range(1, ws.max_row + 1):
        coordinate = "{}{}".format(column, row)
        if ws[coordinate].value == search_string:
            return column, row
    return column, None


def search_value_in_col_idx(ws, search_string, col_idx=1):
    for row in range(1, ws.max_row + 1):
        if ws[row][col_idx].value == search_string:
            return col_idx, row
    return col_idx, None


def search_value_in_row_index(ws, search_string, row=1):
    for cell in ws[row]:
        if cell.value == search_string:
            return cell.column, row
    return None, row

HaR · Accepted Answer · 2018-05-25 12:27:31Z

2

This can be done in multiple ways , here's one way: Assuming that 'Table' won't show up in the table anywhere but the header of said table.

from openpyxl import Workbook
from openpyxl import load_workbook,styles

wb = load_workbook('Test.xlsx') #Load the workbook
ws = wb['Sheet1'] #Load the worksheet

#ws['B'] will return all cells on the B column until the last one (similar to max_row but it's only for the B column)
for cell in ws['B']:
    if(cell.value is not None): #We need to check that the cell is not empty.
        if 'Table' in cell.value: #Check if the value of the cell contains the text 'Table'
            print('Found header with name: {} at row: {} and column: {}. In cell {}'.format(cell.value,cell.row,cell.column,cell))

Which prints:

Found header with name: Table 1 at row: 2 and column: B. In cell <Cell 'Sheet1'.B2>
Found header with name: Table 2 at row: 10 and column: B. In cell <Cell 'Sheet1'.B10>

answered May 25, 2018 at 12:27

HaR

1,0779 silver badges24 bronze badges

1 Comment

LearneR Over a year ago

How to not hardcode the column 'B' but loop through the current column where a string match was found?

Collectives™ on Stack Overflow

Python / Openpyxl - Find Strings in column and return row number

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related