Cách đọc hàng trăm nghìn hàng trong Excel mà không làm đầy bộ nhớ một lúc

Làm thế nào để đọc một bảng lớn mà không sử dụng hết bộ nhớ? Bài viết này dành cho các nhà phát triển và nhóm dữ liệu cần sử dụng Python để xử lý hàng trăm nghìn hàng Excel. Sau khi đọc, bạn sẽ biết lý do gây ra hiện tượng nổ bộ nhớ tệp lớn và cắt bớt dữ liệu cũng như cách sử dụng chế độ chỉ đọc, sửa chữa kích thước và giải phóng tài nguyên của openpyxl để ngăn chặn những sự cố này.

Các ví dụ trong bài viết này sử dụng mẫu tổng hợp. Mã đã được kiểm tra bằng các ví dụ trong môi trường biệt lập, nhưng điều này không đảm bảo cho tất cả các tệp thực hoặc hiệu suất sản phẩm. Trước khi xử lý các văn bản quan trọng, vui lòng giữ lại bản chính và xem xét theo ranh giới và các mục chấp nhận trong văn bản.

Làm thế nào để đọc một bảng lớn mà không bị tràn bộ nhớ? Một câu kết luận

Sử dụng read_only=True để đọc trực tiếp các tệp lớn và đóng sổ làm việc sau khi đọc; nếu số dòng đọc rõ ràng là quá nhỏ, trước tiên hãy kiểm tra thông tin kích thước của tệp, sau đó kết hợp với so sánh thủ công để xác nhận rằng dữ liệu đã hoàn chỉnh.

Nguyên nhân trực tiếp gây nổ bộ nhớ hoặc cắt bớt dữ liệu khi đọc hàng trăm nghìn hàng trong Excel

Khi bạn sử dụng load_workbook('huge.xlsx') để mở một bảng có nửa triệu hàng, chương trình có thể sử dụng hết 8 GB bộ nhớ trong vài giây hoặc có thể chấm dứt sau khi chỉ đọc 1.000 hàng đầu tiên.

Lý do trước đây là chế độ mặc định tải tất cả các đối tượng ô cũng như kiểu, nhận xét và siêu dữ liệu khác của chúng vào bộ nhớ cùng một lúc. Điều thứ hai là do ở chế độ read_only=True, openpyxl hoàn toàn tin tưởng thuộc tính dimensions được khai báo bên trong tệp để theo đuổi hiệu suất.

Nếu hệ thống tạo tệp (chẳng hạn như một số mô-đun xuất ERP cũ hoặc thư viện Python không chuẩn) ghi siêu dữ liệu này không chính xác, trình vòng lặp sẽ chấm dứt sớm mà không đưa ra bất kỳ ngoại lệ nào. Đây không phải là lỗi mã mà là khoảng cách giữa thông số kỹ thuật định dạng và dữ liệu trong thế giới thực cần được khắc phục thông qua lập trình phòng thủ.

Đúng cách mở và giải phóng tài nguyên của chế độ read_only

read_only=True là đường dẫn chính để xử lý các tệp lớn, nhưng nó thay đổi cách quản lý vòng đời của các đối tượng. Sổ làm việc thông thường sẽ tự động giải phóng tài nguyên trong quá trình thu thập rác, trong khi sổ làm việc chỉ đọc duy trì các tham chiếu đến luồng tệp ZIP và trình phân tích cú pháp XML ở phía dưới và phải gọi rõ ràng wb.close(). Để biết hướng dẫn chính thức về chế độ chỉ đọc, vui lòng tham khảo Tài liệu chính thức về Chế độ tối ưu hóa openpyxl.

Việc quên bước này trong tập lệnh ETL lặp qua nhiều tệp có thể dẫn đến rò rỉ phần xử lý tệp, cuối cùng kích hoạt OSError: Too many open files. Loại lỗi này thường xuất hiện sau khi chạy vài giờ và cực kỳ khó khắc phục.

Ngoài ra, chế độ chỉ đọc trả về ReadOnlyCell thay vì đối tượng Cell tiêu chuẩn. Nó không hỗ trợ truy cập vào các thuộc tính như kiểu và chú thích, cũng như không cho phép tra cứu ngược hoặc sửa đổi giá trị thông qua tọa độ. Nếu bạn nhận được thông tin vùng đã hợp nhất hoặc đặt màu ô trong quá trình lặp, bạn sẽ nhận được AttributeError. Giới hạn này được thiết kế để duy trì mức sử dụng bộ nhớ gần như không đổi.

📊 So sánh cốt lõi

Ví dụ tổng hợp: Sửa chữa chiều kích thước và kiểm tra bảo mật lặp đi lặp lại

Đoạn mã sau minh họa luồng đọc phòng thủ điển hình. Lưu ý rằng đây là một ví dụ tổng hợp dùng để minh họa cấu trúc logic, không phải kết quả xử lý thực tế của dữ liệu sản xuất. Bạn cần tự chuẩn bị một tệp tên input_large.xlsx để chạy script này, và nó nên được thực thi trong môi trường cô lập với các phiên bản phụ thuộc bị khóa. Ví dụ này không bao gồm các thao tác mạng, lệnh shell hoặc thông tin xác thực nhạy cảm.

from openpyxl import load_workbook

# 合成示例：演示read_only模式下的安全读取与维度校验
# 依赖：openpyxl (需在项目requirements中锁定具体版本)
# 注意：需读者自备 input_large.xlsx，本示例不包含真实数据

def safe_read_large_excel(filepath, expected_min_rows=1000):
    """
    安全读取大Excel文件，包含维度修复与完整性校验。
    :param filepath: Excel文件路径
    :param expected_min_rows: 业务预期的最小行数阈值
    :return: 实际读取的行数
    """
    wb = load_workbook(filename=filepath, read_only=True)
    try:
        ws = wb.active
        # 检查并修复可能错误的维度声明
        # calculate_dimension() 可能返回 'A1:A1' 或其他不准确范围
        dim = ws.calculate_dimension()
        if dim == "A1:A1" or not dim:
            # 重置维度以强制扫描实际数据区域
            # 警告：若文件完全无维度信息，此操作可能遍历全表(104万行)
            ws.reset_dimensions()

        row_count = 0
        header = None
        for row in ws.iter_rows(values_only=True):
            if row_count == 0:
                header = row
                assert header is not None and any(v is not None for v in header), \
                    "首行不应为空或全None"
            row_count += 1

            # 模拟业务处理，避免全量加载到内存
            if row_count % 10000 == 0:
                print(f"已处理 {row_count} 行")

        # 校验读取完整性
        assert row_count >= expected_min_rows, \
            f"行数不足: 期望>={expected_min_rows}, 实际={row_count}。请检查源文件或维度设置。"
        return row_count
    finally:
        # 必须显式关闭，防止句柄泄漏
        wb.close()

# 使用示例（需替换为真实文件路径）
# total = safe_read_large_excel("input_large.xlsx", expected_min_rows=50000)

Chìa khóa của mã này không phải là việc đọc, mà là ba lớp bảo vệ:

• Đặt lại kích thước: Đã sửa lỗi khai báo tệp dimensions để ngăn iterator kết thúc quá sớm.
• Đếm luồng: Đếm số dòng khi đọc, giúp so sánh với kỳ vọng dễ dàng hơn.
• Đóng ép buộc trong finally: Giải phóng tài nguyên tệp bất kể lỗi đọc hoặc lỗi trung gian thành công.

expected_min_rows không phải là ngưỡng tiêu chuẩn ngành, mà là kết quả kinh doanh dựa trên kiến thức trước đó của bạn về dữ liệu nguồn. Nếu số dòng thực tế thấp hơn giá trị này, điều đó cho thấy tệp có thể bị hỏng hoặc sửa chữa kích thước đã thất bại. Trong trường hợp này, nên thực hiện ngắt thay vì lặng lẽ chấp nhận dữ liệu không đầy đủ.

Tiêu chí chấp nhận và ranh giới kiểm tra thủ công

Chạy mã tốt không có nghĩa là dữ liệu đúng. Bảng dưới đây liệt kê các bất thường điển hình có thể gặp phải trong các dự án thực tế và cách xử lý chúng, phân biệt giữa phạm vi bao phủ tự động hóa và các kịch bản cần can thiệp thủ công.

Lưu ý đặc biệt: reset_dimensions() không phải là giải pháp toàn diện. Nếu tệp không ghi bất kỳ thông tin kích thước nào, phương pháp này có thể mở rộng phạm vi đến giới hạn bảng tính (1048576 hàng), dẫn đến việc duyệt không hợp lệ hoặc thậm chí hết thời gian. Ở thời điểm này, bạn nên tự thiết lập giới hạn trên hợp lý kết hợp với max_row, hoặc chủ động kết thúc khi gặp N dòng trống liên tiếp. Những đánh giá này không thể chỉ dựa trên mã; chúng đòi hỏi kiến thức chuyên ngành để hỗ trợ.

🎯 Phương pháp nào phù hợp với bạn?

Nếu bạn cần xử lý hàng trăm nghìn hàng của một bảng cùng lúc,

Sử dụng read_only=True streaming read để tránh đột biến bộ nhớ ở chế độ mặc định.

Nếu script của bạn xử lý nhiều tệp trong một vòng lặp,

Gọi rõ ràng wb.close() trong khối finally để ngăn rò rỉ tay cầm.

Nếu số dòng đọc ít hơn đáng kể so với dữ liệu nguồn,

Đầu tiên, kiểm tra xem dimensions có viết sai không, sau đó đọc lại reset_dimensions() và so sánh với nhật ký xuất của hệ thống nguồn.

Nếu bạn cần đọc phong cách hoặc bình luận

Chế độ chỉ đọc không hỗ trợ các thuộc tính này; chế độ mặc định nên được sử dụng hoặc xử lý riêng biệt trong các quy trình làm việc khác.

Điểm vào để chạy các mẫu bộ nhớ

Đoạn mã sau chạy sau hàm trên, chỉ sử dụng dữ liệu tổng hợp và không đọc các tệp khách.

from io import BytesIO
from openpyxl import Workbook
source = Workbook()
source.active.append(["id", "value"])
source.active.append(["A", 1])
source.active.append(["B", 2])
out = BytesIO()
source.save(out)
out.seek(0)
assert safe_read_large_excel(out, expected_min_rows=3) == 3

Sử dụng danh sách kiểm tra này để dịch tài liệu

Đầu tiên, quyết định nội dung nào là văn bản, nội dung nào là công thức và mã kinh doanh, sau đó dịch bảng. Kiểm tra sau dịch không chỉ kiểm tra xem tiếng Trung có trở thành ngôn ngữ đích hay không, mà còn kiểm tra xem công thức, định danh và mối quan hệ trích dẫn có bị thay đổi hay không.

OmniDoc (Mục sản phẩm) cung cấp các cổng dịch tài liệu đa định dạng, và trang web chính thức của họ giới thiệu OCR, bảng thuật ngữ và các tính năng tham khảo song ngữ. Đối với các nhóm muốn bắt đầu trực tiếp từ file, đây là con đường đáng để đánh giá bằng các mẫu thực tế. Vui lòng sử dụng tệp ẩn danh mà bạn đã cho phép tải lên và xác minh kết quả với danh sách kiểm tra trong bài viết này. Bài viết này không khẳng định rằng OmniDoc sử dụng các thư viện mã nguồn mở hoặc thuật toán nội bộ được đề cập trong bài viết, cũng không thay thế kiểm thử hiệu suất sản phẩm bằng các ví dụ.

❓ Câu hỏi thường gặp (FAQ)

1. Tại sao nó kết thúc giữa chừng khi đọc mà vẫn không nhận lỗi?

Bởi vì chế độ chỉ đọc tin tưởng vào dimensions đã khai báo của tệp. Nếu hệ thống tạo ghi sai siêu dữ liệu này, iterator sẽ kết thúc sớm và không ném bất kỳ ngoại lệ nào.

2. Tất cả các tệp lớn có thể xử lý bằng reset_dimensions()?

Không. Nếu tệp không có thông tin về kích thước, phương pháp này có thể mở rộng phạm vi lên 1048576 dòng, gây ra thời gian chờ, và yêu cầu kết hợp max_row hoặc các chiến lược kết thúc dòng trống liên tiếp.

3. Tại sao bạn không thể đọc kiểu hoặc chỉnh sửa ô ở chế độ chỉ đọc?

Chế độ chỉ đọc trả về ReadOnlyCell, không hỗ trợ quyền truy cập kiểu hoặc nhận xét, cũng không cho phép sửa đổi giá trị. Điều này nhằm duy trì mức sử dụng bộ nhớ gần như không đổi.

4. Những gì cần kiểm tra sau khi dịch biểu mẫu?

Ngoài việc đảm bảo tính chính xác ngôn ngữ, hãy kiểm tra xem công thức, định danh và tham chiếu có bị thay đổi hay không.

📝 Tóm tắt

Chìa khóa của việc đọc bảng lớn nằm ở việc đọc luồng, kiểm tra kích thước và giải phóng tài nguyên kịp thời. Mã chỉ là điểm khởi đầu; việc dữ liệu có đầy đủ hay không vẫn phụ thuộc vào so sánh và xác minh thủ công.

Trải nghiệm ngay: Tải lên tài liệu của bạn để bắt đầu dịch, giữ nguyên định dạng gốc