"""XLSX export — Gantt data table, RACI matrix, and Risk Register in one workbook.""" import asyncio from datetime import date, timedelta from openpyxl import Workbook from openpyxl.styles import PatternFill, Font, Alignment from core.planner import ProjectPlan, RACIEntry RACI_FILLS = { "R": "4ade80", "A": "86efac", "C": "a0a0a0", "I": "606060", } RISK_FILLS = { "High": "f87171", "Medium": "fbbf24", "Low": "4ade80", } _IMPACT_ORDER = {"High": 0, "Medium": 1, "Low": 2} _HEADER_FILL = PatternFill("solid", fgColor="1a1a2e") _CRITICAL_FILL = PatternFill("solid", fgColor="14532d") # dark green for critical path rows _HEADER_FONT = Font(bold=True, color="4ade80") _BODY_FONT = Font(color="e8e8e8") def _build_gantt_sheet(ws, plan: ProjectPlan) -> None: """Write Gantt phase data as a structured table (no image — kaleido needs Chrome).""" base_date = ( date.fromisoformat(plan.estimated_start_date) if plan.estimated_start_date else date.today() ) headers = ["Phase ID", "Phase Name", "Owner Team", "Start Date", "End Date", "Duration (days)", "Critical Path", "Slack Days", "Tasks"] ws.append(headers) for cell in ws[1]: cell.fill = _HEADER_FILL cell.font = _HEADER_FONT cell.alignment = Alignment(horizontal="center", vertical="center") ws.row_dimensions[1].height = 20 for phase in plan.phases: start = base_date + timedelta(days=phase.start_offset_days) end = base_date + timedelta(days=phase.start_offset_days + phase.duration_days) tasks = "; ".join(phase.tasks) if phase.tasks else "" row = [ phase.id, phase.name, phase.owner_team or "", start.isoformat(), end.isoformat(), phase.duration_days, "Yes" if phase.is_critical_path else "No", phase.slack_days, tasks, ] ws.append(row) fill = _CRITICAL_FILL if phase.is_critical_path else PatternFill("solid", fgColor="111111") for cell in ws[ws.max_row]: cell.fill = fill cell.font = _BODY_FONT cell.alignment = Alignment(vertical="center", wrap_text=True) col_widths = [10, 28, 18, 14, 14, 16, 14, 12, 50] for i, width in enumerate(col_widths, start=1): ws.column_dimensions[ws.cell(1, i).column_letter].width = width def _get_all_teams(raci: list) -> list: seen: list[str] = [] for entry in raci: for team in (entry.responsible + [entry.accountable] + entry.consulted + entry.informed): if team and team not in seen: seen.append(team) return seen def _cell_letter(entry: RACIEntry, team: str) -> str: roles: list[str] = [] if team in entry.responsible: roles.append("R") if team == entry.accountable: roles.append("A") if team in entry.consulted: roles.append("C") if team in entry.informed: roles.append("I") return "/".join(roles) def _build_raci_sheet(ws, plan: ProjectPlan) -> None: teams = _get_all_teams(plan.raci) ws.append(["Task"] + teams) for cell in ws[1]: cell.fill = _HEADER_FILL cell.font = _HEADER_FONT cell.alignment = Alignment(horizontal="center") for entry in plan.raci: row = [entry.task] + [_cell_letter(entry, t) for t in teams] ws.append(row) for col_idx, team in enumerate(teams, start=2): letter = _cell_letter(entry, team) cell = ws.cell(row=ws.max_row, column=col_idx) first_role = letter.split("/")[0] if letter else "" if first_role in RACI_FILLS: cell.fill = PatternFill("solid", fgColor=RACI_FILLS[first_role]) cell.alignment = Alignment(horizontal="center") for col in ws.columns: max_len = max((len(str(c.value or "")) for c in col), default=8) ws.column_dimensions[col[0].column_letter].width = min(max_len + 4, 40) def _build_risks_sheet(ws, plan: ProjectPlan) -> None: ws.append(["Phase", "Description", "Likelihood", "Impact", "Mitigation"]) for cell in ws[1]: cell.fill = _HEADER_FILL cell.font = _HEADER_FONT cell.alignment = Alignment(horizontal="center") for risk in sorted(plan.risks, key=lambda r: _IMPACT_ORDER.get(r.impact, 99)): ws.append([risk.phase_id, risk.description, risk.likelihood, risk.impact, risk.mitigation]) fill_color = RISK_FILLS.get(risk.impact) if fill_color: for cell in ws[ws.max_row]: cell.fill = PatternFill("solid", fgColor=fill_color) for col in ws.columns: max_len = max((len(str(c.value or "")) for c in col), default=8) ws.column_dimensions[col[0].column_letter].width = min(max_len + 4, 60) def _write_workbook(plan: ProjectPlan, path: str) -> None: wb = Workbook() gantt_ws = wb.active gantt_ws.title = "Gantt Chart" _build_gantt_sheet(gantt_ws, plan) raci_ws = wb.create_sheet(title="RACI Matrix") _build_raci_sheet(raci_ws, plan) risks_ws = wb.create_sheet(title="Risk Register") _build_risks_sheet(risks_ws, plan) wb.save(path) async def export_xlsx(plan: ProjectPlan, gantt_fig=None, filename: str = "projectcraft_plan") -> str: """Export Gantt data table, RACI matrix, and Risk Register to a single .xlsx workbook.""" path = f"/tmp/{filename}.xlsx" try: await asyncio.to_thread(_write_workbook, plan, path) return path except Exception as exc: print(f"[export_xlsx] {exc}") return ""