使用OpenPyXL匹配单元格字符串时遇TypeError错误求助

Hey there! Let's break down this error you're hitting and get your code working to find those target strings, whether you need to print specific cells or entire rows.

What's Causing the Error? #

This TypeError almost always happens when you try to check if your target string exists in a blank cell—because blank cells in OpenPyXL return None when you access cell.value, and you can't use the in operator on None (you can't search for a string in nothing!).

Step 1: Fix Basic Cell Value Search (Your Current Goal) #

First, let's adjust your code to handle blank cells properly. We'll add a check to make sure the cell value isn't None before we look for your target string, and use values_only=True to make traversing your 3000+ rows more efficient.

from openpyxl import load_workbook

# Replace with your file path and sheet name if needed
wb = load_workbook(filename="your_excel_file.xlsx")
ws = wb.active  # Or use ws = wb["SheetName"] to target a specific sheet

target_string = "your_target_here"

# Iterate through rows, fetching only cell values (faster for large files)
for row in ws.iter_rows(values_only=True):
    for cell_value in row:
        # First check if cell isn't blank, then check for target string
        # Convert cell value to string to handle numbers/dates safely
        if cell_value is not None and target_string in str(cell_value):
            print(f"Found target in cell: {cell_value}")

Step 2: Expand to Print Entire Rows or Specific Cells #

Once the basic search works, we can tweak the code to match your final goals:

Print Entire Rows Containing the Target #

from openpyxl import load_workbook

wb = load_workbook(filename="your_excel_file.xlsx")
ws = wb.active
target_string = "your_target_here"

for row in ws.iter_rows(values_only=True):
    # Check if any cell in the row contains the target (skip blanks)
    has_target = any(
        val is not None and target_string in str(val) 
        for val in row
    )
    if has_target:
        print(f"Full row with target: {row}")

Print a Specific Cell from Matching Rows #

Say you want to pull the value from the 2nd column (index 1, since Python uses 0-based indexing) whenever a row has your target:

from openpyxl import load_workbook

wb = load_workbook(filename="your_excel_file.xlsx")
ws = wb.active
target_string = "your_target_here"
target_column_index = 1  # Adjust this to your desired column

for row in ws.iter_rows(values_only=True):
    for idx, cell_val in enumerate(row):
        if cell_val is not None and target_string in str(cell_val):
            print(f"Target column value: {row[target_column_index]}")
            break  # Remove this if you want to catch multiple matches in one row

Key Tips for Large Files #

• iter_rows(values_only=True) is way more efficient than loading full cell objects for 3000+ rows—it keeps memory usage low.
• Converting cell values to str() ensures you don't hit errors if cells contain numbers, dates, or other non-string data types.
• If you have merged cells, OpenPyXL only stores the value in the top-left cell of the merge—all other merged cells will return None, so your is not None check will handle that automatically.

内容的提问来源于stack exchange，提问作者Heidrake