Great question—dealing with those massive empty rows in Excel sheets is such a common pain point, and openpyxl absolutely has ways to handle this without wasting cycles on billions of empty cells. Here's how to do it right:

Core Strategy: Iterate & Filter Rows #

Instead of looping through ws.max_row and ws.max_column directly (which would be catastrophic for your "3. Access Control" sheet), use openpyxl's lazy row iterators to process rows one at a time, keeping only those with at least one non-empty cell. This avoids loading the entire sheet into memory and cuts processing time drastically.

Method 1: Explicit Iteration with iter_rows() #

This approach is easy to read and gives you flexibility to work with cell objects or just their values:

from openpyxl import load_workbook

# Use read_only=True for huge files—optimizes openpyxl for reading
wb = load_workbook("your_file.xlsx", read_only=True)

for ws in wb.worksheets:
    print(f"Processing sheet: {ws.title}")
    non_empty_rows = []
    
    # Iterate through rows, fetching only cell values
    for row in ws.iter_rows(values_only=True):
        # Check if any cell in the row has meaningful content
        if any(cell is not None and str(cell).strip() != "" for cell in row):
            non_empty_rows.append(row)
    
    print(f"Found {len(non_empty_rows)} non-empty rows (vs. {ws.max_row} total rows)")
    
    # Now you can analyze, transform, or export non_empty_rows as needed
wb.close()

Method 2: Concise List Comprehension with ws.values #

If you prefer shorter code, ws.values returns a generator of row value tuples that you can filter directly:

from openpyxl import load_workbook

wb = load_workbook("your_file.xlsx", read_only=True)

for ws in wb.worksheets:
    non_empty_rows = [
        row for row in ws.values 
        if any(cell is not None and str(cell).strip() != "" for cell in row)
    ]
    print(f"{ws.title}: {len(non_empty_rows)} non-empty rows")

wb.close()

Why This Works #

• Lazy Loading: Both iter_rows() and ws.values generate rows on-the-fly, so you never load all 1 million+ empty rows into memory at once.
• No Missed Content: The any() check ensures you capture every row with even a single non-empty cell—no risk of skipping late-occurring rows like your hypothetical row 10000.
• Performance Boost: Using read_only=True is critical for large files, as it tells openpyxl to prioritize reading speed over write capabilities.

Bonus: Tweak the Filter for Your Needs #

The check cell is not None and str(cell).strip() != "" handles cases where cells might have hidden whitespace (like empty strings or spaces). Adjust this if you need to treat specific values (like 0 or False) as non-empty.

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