111 lines
4.7 KiB
Python
111 lines
4.7 KiB
Python
"""
|
|
utils/export.py — CSV and Excel export helpers for report data.
|
|
"""
|
|
|
|
import csv
|
|
import logging
|
|
import os
|
|
from datetime import datetime
|
|
|
|
logger = logging.getLogger("export")
|
|
|
|
|
|
def _timestamp() -> str:
|
|
return datetime.now().strftime("%Y%m%d_%H%M%S")
|
|
|
|
|
|
def export_csv(rows: list, columns: list, base_filename: str, save_dir: str) -> str:
|
|
"""
|
|
Write rows (list of dicts) to a CSV file.
|
|
Returns the full path of the written file.
|
|
"""
|
|
filename = f"{base_filename}_{_timestamp()}.csv"
|
|
filepath = os.path.join(save_dir, filename)
|
|
|
|
try:
|
|
with open(filepath, "w", newline="", encoding="utf-8-sig") as fh:
|
|
writer = csv.DictWriter(fh, fieldnames=columns, extrasaction="ignore")
|
|
writer.writeheader()
|
|
for row in rows:
|
|
# Convert non-string types (date, datetime) to string
|
|
clean = {k: (str(v) if v is not None else "") for k, v in row.items()}
|
|
writer.writerow(clean)
|
|
logger.info(f"[EXPORT] CSV written: {filepath} ({len(rows)} rows)")
|
|
return filepath
|
|
except Exception as e:
|
|
logger.error(f"CSV export failed: {e}")
|
|
raise
|
|
|
|
|
|
def export_excel(rows: list, columns: list, base_filename: str,
|
|
save_dir: str, sheet_title: str = "Report") -> str:
|
|
"""
|
|
Write rows (list of dicts) to an .xlsx file with basic formatting.
|
|
Returns the full path of the written file.
|
|
"""
|
|
try:
|
|
import openpyxl
|
|
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
|
|
except ImportError:
|
|
raise RuntimeError(
|
|
"openpyxl is required for Excel export.\n"
|
|
"Install it with: pip install openpyxl"
|
|
)
|
|
|
|
filename = f"{base_filename}_{_timestamp()}.xlsx"
|
|
filepath = os.path.join(save_dir, filename)
|
|
|
|
wb = openpyxl.Workbook()
|
|
ws = wb.active
|
|
ws.title = sheet_title[:31] # Excel sheet name limit
|
|
|
|
# ── Header style ──────────────────────────────────────────────────────────
|
|
header_fill = PatternFill("solid", fgColor="7C6AF7") # accent purple
|
|
header_font = Font(bold=True, color="FFFFFF", name="Calibri", size=11)
|
|
header_align = Alignment(horizontal="center", vertical="center", wrap_text=True)
|
|
thin_border = Border(
|
|
bottom=Side(style="thin", color="45475A"),
|
|
right=Side(style="thin", color="45475A"),
|
|
)
|
|
|
|
# ── Write header row ──────────────────────────────────────────────────────
|
|
for col_idx, col_name in enumerate(columns, start=1):
|
|
cell = ws.cell(row=1, column=col_idx, value=col_name.replace("_", " ").title())
|
|
cell.font = header_font
|
|
cell.fill = header_fill
|
|
cell.alignment = header_align
|
|
cell.border = thin_border
|
|
|
|
ws.row_dimensions[1].height = 22
|
|
|
|
# ── Write data rows ───────────────────────────────────────────────────────
|
|
alt_fill = PatternFill("solid", fgColor="2A2A3E")
|
|
for row_idx, row in enumerate(rows, start=2):
|
|
fill = alt_fill if row_idx % 2 == 0 else PatternFill("solid", fgColor="1E1E2E")
|
|
for col_idx, col_name in enumerate(columns, start=1):
|
|
val = row.get(col_name)
|
|
if val is None:
|
|
val = ""
|
|
cell = ws.cell(row=row_idx, column=col_idx, value=str(val))
|
|
cell.font = Font(name="Calibri", size=10, color="CDD6F4")
|
|
cell.fill = fill
|
|
cell.alignment = Alignment(vertical="center", wrap_text=False)
|
|
cell.border = thin_border
|
|
|
|
# ── Auto-width columns (capped at 60) ─────────────────────────────────────
|
|
for col_idx, col_name in enumerate(columns, start=1):
|
|
col_letter = openpyxl.utils.get_column_letter(col_idx)
|
|
header_len = len(col_name.replace("_", " ").title())
|
|
max_data_len = max(
|
|
(len(str(row.get(col_name) or "")) for row in rows),
|
|
default=0
|
|
)
|
|
ws.column_dimensions[col_letter].width = min(max(header_len, max_data_len) + 4, 60)
|
|
|
|
# ── Freeze top row ────────────────────────────────────────────────────────
|
|
ws.freeze_panes = "A2"
|
|
|
|
wb.save(filepath)
|
|
logger.info(f"[EXPORT] Excel written: {filepath} ({len(rows)} rows)")
|
|
return filepath
|