如何分块读取大型.XLS文件？已实现CSV、XLSX分块读取方案

Great question! Handling the older binary .xls format for chunked reading is trickier than text-based .csv or XML-backed .xlsx files, but there are reliable approaches to avoid loading the entire file into memory. Here are three practical solutions tailored to different use cases:

1. Cross-Platform Approach with xlrd #

The xlrd library natively supports the binary .xls format, and you can use its on-demand loading mode to read rows incrementally and simulate chunked processing:

import xlrd
import pandas as pd

def read_xls_in_chunks(file_path, chunksize=1000):
    # Enable on-demand loading to avoid loading the entire workbook into memory
    workbook = xlrd.open_workbook(file_path, on_demand=True)
    sheet = workbook.sheet_by_index(0)  # Target the first worksheet; adjust index as needed
    
    # Iterate through rows in chunks
    for start_row in range(0, sheet.nrows, chunksize):
        end_row = min(start_row + chunksize, sheet.nrows)
        # Extract row values for the current chunk
        chunk_rows = [sheet.row_values(row_idx) for row_idx in range(start_row, end_row)]
        
        # Optional: Convert chunk to a pandas DataFrame for easier processing
        if start_row == 0:
            # Treat first chunk's first row as headers
            df = pd.DataFrame(chunk_rows[1:], columns=chunk_rows[0])
        else:
            df = pd.DataFrame(chunk_rows)
        
        yield df
    
    # Clean up resources
    workbook.release_resources()

• Pros: Works on Windows, macOS, and Linux; no dependency on Microsoft Excel.
• Cons: Requires manual handling of headers and row iteration, but converting to a DataFrame simplifies downstream work.

2. Windows-Only Approach with pywin32 #

If you're working on Windows and have Microsoft Excel installed, you can use the pywin32 library to interact directly with the Excel application, which is great for handling complex .xls formats with macros or advanced styling:

import win32com.client as win32
import pandas as pd

def read_xls_with_win32_chunks(file_path, chunksize=1000):
    # Initialize Excel in background mode
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    excel.Visible = False
    workbook = excel.Workbooks.Open(file_path)
    sheet = workbook.Worksheets(1)  # Access first worksheet
    
    total_rows = sheet.UsedRange.Rows.Count
    
    for start_row in range(1, total_rows + 1, chunksize):
        end_row = min(start_row + chunksize - 1, total_rows)
        # Read the specified range of cells
        range_data = sheet.Range(f"A{start_row}:XFD{end_row}").Value
        
        # Convert to DataFrame (first row is headers if start_row == 1)
        if start_row == 1:
            df = pd.DataFrame(range_data[1:], columns=range_data[0])
        else:
            df = pd.DataFrame(range_data)
        
        yield df
    
    # Clean up Excel instance
    workbook.Close(SaveChanges=False)
    excel.Quit()

• Pros: Leverages Excel's native parsing engine, which handles edge cases (like merged cells or formulas) better than pure Python libraries.
• Cons: Windows-only; requires Excel to be installed; slower than pure Python approaches.

3. Pandas-Based Manual Chunking #

If you prefer working with pandas DataFrames directly, you can combine pd.ExcelFile with skiprows and nrows parameters to load chunks incrementally:

import pandas as pd

def read_xls_pandas_chunks(file_path, chunksize=1000):
    # Get total number of rows without loading the entire workbook
    with pd.ExcelFile(file_path) as xls:
        # Access the underlying xlrd workbook to get row count
        total_rows = xls.book.sheet_by_index(0).nrows
    
    # Iterate through chunks
    for skip_rows in range(0, total_rows, chunksize):
        # Skip rows and load only the current chunk
        df = pd.read_excel(
            file_path,
            skiprows=skip_rows,
            nrows=chunksize,
            header=0 if skip_rows == 0 else None  # Use header only for first chunk
        )
        yield df

• Pros: Seamlessly integrates with pandas workflows; no extra libraries beyond pandas (since pandas uses xlrd for .xls parsing).
• Cons: Requires careful handling of headers across chunks; less efficient than the raw xlrd approach for very large files.

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