demand_server/app/utils/excel_export.py

"""将行数据序列化为 Excel 字节流。"""

from __future__ import annotations

import json
from io import BytesIO
from typing import Iterable
from urllib.parse import quote

from openpyxl import Workbook
from openpyxl.utils import get_column_letter


def _cell_value(raw: object) -> object:
    if raw is None:
        return ""
    if isinstance(raw, (list, dict)):
        return json.dumps(raw, ensure_ascii=False)
    return raw


def rows_to_excel_bytes(
    rows: Iterable[dict[str, object]],
    columns: list[tuple[str, str]],
    *,
    sheet_name: str = "Sheet1",
) -> bytes:
    workbook = Workbook()
    worksheet = workbook.active
    worksheet.title = sheet_name[:31]

    headers = [header for header, _ in columns]
    worksheet.append(headers)

    for row in rows:
        worksheet.append([_cell_value(row.get(field)) for _, field in columns])

    for index, (header, _) in enumerate(columns, start=1):
        column_letter = get_column_letter(index)
        max_len = len(header)
        for cell in worksheet[column_letter]:
            if cell.value is not None:
                max_len = max(max_len, len(str(cell.value)))
        worksheet.column_dimensions[column_letter].width = min(max_len + 2, 60)

    buffer = BytesIO()
    workbook.save(buffer)
    return buffer.getvalue()


def build_content_disposition(filename: str) -> str:
    ascii_fallback = "export.xlsx"
    encoded = quote(filename, safe="")
    return f"attachment; filename=\"{ascii_fallback}\"; filename*=UTF-8''{encoded}"