Using openpyxl to find rows that contain cell with

问题:

I am completely new to openpyxl so, as you can imagine, I am having pretyy hard times when I try to make use of it.

I have an excel report that contains only one sheet (called Sheet 1). I'd like to search all cells for those that contain specific string (product name ABC in this case).

Then I would like to copy contents of every cell in the rows that contain cell with ABC product name. And assign every cell to a variable.

To give you better idea of what I am trying to achieve I'll give you an example:

So in this case I would only copy cells from rows: 2, 4, 6 (as only they contain ABC product).

I have already looked up similar questions and answers to them but I don't understand them (never have used excel before).

回答1:

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:

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

回答2:

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

回答3:

from openpyxl import load_workbook

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

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