跳转至

大数据处理

本页面介绍如何处理大型数据集。

分块处理

基本分块

from symphra_excel.utils import MemoryOptimizedWorkbook

with MemoryOptimizedWorkbook() as wb:
    sheet = wb.create_worksheet("大数据")

    chunk_size = 10000
    total_rows = 1000000

    for start in range(1, total_rows, chunk_size):
        end = min(start + chunk_size, total_rows)

        for row in range(start, end):
            sheet.set_cell_value(f"A{row}", f"Data {row}")

    wb.save("large_data.xlsx")

数据流处理

从数据库流式导出

from symphra_excel.utils import MemoryOptimizedWorkbook, LargeDataProcessor

processor = LargeDataProcessor()

def fetch_data_from_db(offset, limit):
    return [...]

with MemoryOptimizedWorkbook() as wb:
    sheet = wb.create_worksheet("导出数据")

    offset = 0
    batch_size = 5000
    row = 1

    while True:
        data = fetch_data_from_db(offset, batch_size)
        if not data:
            break

        for record in data:
            sheet.set_cell_value(f"A{row}", record["id"])
            sheet.set_cell_value(f"B{row}", record["name"])
            sheet.set_cell_value(f"C{row}", record["value"])
            row += 1

        processor.flush()
        offset += batch_size

    wb.save("database_export.xlsx")

完整示例

示例: CSV 转 Excel

import csv
from symphra_excel.utils import MemoryOptimizedWorkbook, LargeDataProcessor

processor = LargeDataProcessor()

with MemoryOptimizedWorkbook() as wb:
    sheet = wb.create_worksheet("CSV数据")

    row = 1
    batch_size = 5000
    batch_count = 0

    with open("large_data.csv", "r", encoding="utf-8") as csvfile:
        reader = csv.reader(csvfile)

        for csv_row in reader:
            for col_idx, value in enumerate(csv_row, start=1):
                sheet.set_cell_value(f"{chr(64+col_idx)}{row}", value)

            row += 1
            batch_count += 1

            if batch_count >= batch_size:
                processor.flush()
                batch_count = 0

    wb.save("converted_from_csv.xlsx")

性能优化

批量设置值

from symphra_excel.utils import MemoryOptimizedWorkbook

with MemoryOptimizedWorkbook() as wb:
    sheet = wb.create_worksheet("批量数据")

    data = []
    for i in range(10000):
        data.append([i, f"Item {i}", i * 100])

    sheet.set_values_batch("A1", data)

    wb.save("batch_data.xlsx")

最佳实践

✅ 推荐做法

from symphra_excel.utils import MemoryOptimizedWorkbook, LargeDataProcessor

processor = LargeDataProcessor()

with MemoryOptimizedWorkbook() as wb:
    sheet = wb.create_worksheet("数据")

    chunk_size = 10000
    for chunk in get_data_chunks(chunk_size):
        for row_data in chunk:
            sheet.set_cell_value(f"A{row}", row_data)

        processor.flush()

processor.clear_cache()

❌ 避免的做法

data = load_all_data()

for row in data:
    sheet.set_cell_value(f"A{row}", row)

下一步