Using Openpyxl To Find Rows That Contain Cell With Specific Value

I am completely new to openpyxl so, as you can imagine, I am having pretty hard times when I try to make use of it. I have an Excel report that contains only one sheet (called Shee

Solution 1:

There's no need to use the pandas for this.

from openpyxl import Workbook
import openpyxl

file = "enter_path_to_file_here"
wb = openpyxl.load_workbook(file, read_only=True)
ws = wb.active

for row in ws.iter_rows("E"):
    for cell in row:
        if cell.value == "ABC":
            print(ws.cell(row=cell.row, column=2).value) #change column number for any cell value you want

Solution 2:

is it important for you to use openpyxl to do this? i would suggest using pandas if not.

    import pandas as pd

    df = pd.read_excel("path_to_excel_file")
    df_abc = df[df["Products"] == "ABC"] # this will only contain 2,4,6 rows

then:

forrowin df_abc.iterrows():
        # do what you want with the row

Solution 3:

from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
ws = wb.active

forrowin ws.rows:
if row[4].value== "ABC":
    for cell inrow:
        print(cell.value, end=" ")
    print()

Solution 4:

wb = load_workbook("report.xlsx")
ws = wb.active

abc_rows=[]

forrowinrange(2,ws.max_row+1):
    if(ws[row][4]=='ABC'):
       abc_rows.append(row)

To access the selected rows separately, further add this code

i=1
abc_dict={}
for row in ws.values:
    if(i in abc_rows):
        (a,b,c,d,e,f,g,h,i,j,k,l)=row
         abc_dict[i]=row
    i=i+1  

To access selected rows seperately, use

abc_dict[2] gives entire second row as tuples and abc_dict[2][0] gives first cell value. you can parse till abc_dict[2][11] to get each cell of selected rows seperately.