CODEWITHSUNDEEP
pythonopenpyxl
Peter Jung
Peter Jung

Reputation: 13

how to find cell containing specific text using python?

I am trying to iterate xlsx file and find the cell that contains our company's name using python. The file consists of 2 or more sheets, and each sheet has 6 company's information. Each cell I am looking for has formation as below:

Cell F6 = 1ST(Company_A+Company_B)

Cell G6 = 2ND(Company_C+Company_D)

Cell H6 = 3RD(Company_E+Company_F) and so on.

I'd like to find the cell that contains Company_A. I have done some coding, but I got some problem.

The coding I can do is as following:

import openpyxl
bid = openpyxl.load_workbook('C:/Users/User/Desktop/bidding.xlsx', data_only=True)

for sheet in bid.worksheets:
    for row in sheet.iter_rows():
        for entry in row:
            if entry.value == '1ST(Company_A+Company_B)':
                print(entry.offset(row=1).value)
                print(round(entry.offset(row=8).value/100,5))

I can find the value I want, but I want to find the cell without entering everything

Upvotes: 1

Views: 11561

Answers (1)

Dror Av.
Dror Av.

Reputation: 1214

As you're using == the script is checking for the string in the cell to match exactly that. Instead use in.

Your code should be:

import openpyxl
bid = openpyxl.load_workbook('C:/Users/User/Desktop/bidding.xlsx', data_only=True)

for sheet in bid.worksheets:
    for row in sheet.iter_rows():
        for entry in row:
            try:
                if 'Company_A' in entry.value:
                    print(entry.offset(row=1).value)
                    print(round(entry.offset(row=8).value/100,5))

             except (AttributeError, TypeError):
                 continue

Upvotes: 3

Related Questions