Spout library读取文件时获取行/单元格样式的技术问询

Hey there! I totally get your frustration—setting cell styles is straightforward, but reading them back (especially for huge files) can feel like hitting a wall. Let's break this down and find a solution that works for your large Excel file with those yellow total rows at the end.

First, the key here is to efficiently read cell background colors without loading your entire 100k+ row file into memory (which would crash most systems). We'll use common Python Excel libraries, since they're the most practical for this task.

Option 1: Using openpyxl (for .xlsx/.xlsm files) #

openpyxl is great for reading/writing modern Excel files and supports style detection. Crucially, it has a read-only mode that's perfect for large datasets.

Step-by-Step Approach: #

1. Load the file in read-only mode to save memory.
2. Traverse rows from the end backwards (since your totals are at the bottom—this saves you from looping through every single row!).
3. Check if a cell (or entire row) has a yellow background.
4. Once found, stop processing or remove those rows.

Example Code: #

from openpyxl import load_workbook

# Load the file in read-only mode (critical for large files)
wb = load_workbook("your_large_file.xlsx", read_only=True)
ws = wb.active

# First, confirm your yellow's RGB value (test this with a known yellow cell first!)
# To find it: print(cell.fill.start_color.rgb) for a yellow cell in your file
TARGET_YELLOW_RGB = "FFFFFF00"  # Common yellow with alpha channel; adjust if needed

stop_row = None

# Iterate rows from the bottom up to find the first yellow total row
for row in reversed(list(ws.iter_rows(min_row=1, values_only=False))):
    # Check the first cell of the row (adjust column index if your total marker is elsewhere)
    first_cell = row[0]
    if first_cell.fill.start_color.rgb == TARGET_YELLOW_RGB:
        stop_row = first_cell.row
        break

if stop_row:
    print(f"Found total rows starting at row {stop_row}")
    # Process only rows BEFORE the total row
    processed_data = []
    for row in ws.iter_rows(min_row=1, max_row=stop_row - 1, values_only=True):
        processed_data.append(row)
    # Now you can convert processed_data to a DataFrame, save to a new file, etc.
else:
    print("No yellow total rows found")

wb.close()

If You Need to Delete the Yellow Rows: #

Read-only mode doesn't let you edit the file, so create a new workbook and copy only the rows before stop_row:

from openpyxl import Workbook, load_workbook

# Load original file in read-only
original_wb = load_workbook("your_large_file.xlsx", read_only=True)
original_ws = original_wb.active

# Create new workbook to save cleaned data
new_wb = Workbook()
new_ws = new_wb.active

# Copy rows up to stop_row - 1
for row in original_ws.iter_rows(min_row=1, max_row=stop_row - 1, values_only=True):
    new_ws.append(row)

new_wb.save("cleaned_file.xlsx")
original_wb.close()

Option 2: Using xlrd (for .xls files) #

If you're dealing with older .xls files, xlrd works (note: xlrd 2.0+ no longer supports .xlsx). You'll need to enable formatting_info=True to read styles.

Example Code: #

import xlrd

wb = xlrd.open_workbook("your_large_file.xls", formatting_info=True)
ws = wb.sheet_by_index(0)

# Get the formatting info for cells
xf_list = wb.xf_list
stop_row = None

# Again, iterate from the bottom up
for row_idx in reversed(range(ws.nrows)):
    # Check first cell of the row
    cell = ws.cell(row_idx, 0)
    xf_idx = cell.xf_index
    xf = xf_list[xf_idx]
    # Yellow's color index is often 6 (test with your file to confirm!)
    if xf.background.pattern_colour_index == 6:
        stop_row = row_idx + 1  # Convert to 1-based Excel row number
        break

if stop_row:
    print(f"Total rows start at row {stop_row}")
    # Process rows before the total row
    processed_data = [ws.row_values(idx) for idx in range(stop_row - 1)]
else:
    print("No yellow total rows detected")

Critical Tips: #

• Confirm the Yellow Color Code: Excel can store colors as RGB values or indexed colors. Always test with a known yellow cell from your file to get the correct code (print the RGB or index as shown in the examples).
• Memory Efficiency: Always use read-only mode (openpyxl) or avoid loading the entire file into memory. Traversing from the end saves tons of time compared to looping from row 1 to 100k+.

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