from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter NAVY_FILL = PatternFill(start_color="005696", end_color="005696", fill_type="solid") BLUE_FILL = PatternFill(start_color="E0F2FE", end_color="E0F2FE", fill_type="solid") GREEN_FILL = PatternFill(start_color="DCFCE7", end_color="DCFCE7", fill_type="solid") LIGHT_FILL = PatternFill(start_color="F8FAFC", end_color="F8FAFC", fill_type="solid") WHITE_FONT = Font(color="FFFFFF", bold=True, size=11) HEADER_FONT = Font(color="FFFFFF", bold=True, size=12) TITLE_FONT = Font(color="005696", bold=True, size=14) SECTION_FONT = Font(color="005696", bold=True, size=11) BODY_FONT = Font(size=10) BOLD_FONT = Font(bold=True, size=10) THIN_BORDER = Border( left=Side(style="thin", color="E2E8F0"), right=Side(style="thin", color="E2E8F0"), top=Side(style="thin", color="E2E8F0"), bottom=Side(style="thin", color="E2E8F0"), ) WRAP_ALIGN = Alignment(wrap_text=True, vertical="top") CENTER_ALIGN = Alignment(horizontal="center", vertical="center") def _write_header_row(ws, row, headers, fill=None, font=None): col = 1 for h in headers: cell = ws.cell(row=row, column=col, value=h) cell.font = font or WHITE_FONT cell.fill = fill or NAVY_FILL cell.alignment = CENTER_ALIGN cell.border = THIN_BORDER col += 1 def _write_row(ws, row, values, fill=None, font=None): col = 1 for v in values: cell = ws.cell(row=row, column=col, value=v) cell.font = font or BODY_FONT cell.fill = fill or PatternFill() cell.alignment = WRAP_ALIGN cell.border = THIN_BORDER col += 1 def export_project_excel(project): wb = Workbook() # --- Sheet 1: Project Overview --- ws_overview = wb.active ws_overview.title = "Project Overview" ws_overview.merge_cells("A1:D1") title_cell = ws_overview.cell(row=1, column=1, value=f"QI Project: {project.name}") title_cell.font = TITLE_FONT info_data = [ ("Description", project.description), ("Hospital", project.hospital.name if project.hospital else ""), ("Department", project.department.name if project.department else ""), ("Project Lead", project.project_lead.get_full_name() if project.project_lead else ""), ("Created By", project.created_by.get_full_name() if project.created_by else ""), ("Status", project.get_status_display()), ("Start Date", str(project.start_date) if project.start_date else ""), ("Target Completion", str(project.target_completion_date) if project.target_completion_date else ""), ("Actual Completion", str(project.actual_completion_date) if project.actual_completion_date else ""), ("Outcome", project.outcome_description or ""), ] _write_header_row(ws_overview, 3, ["Field", "Value"]) for i, (field, value) in enumerate(info_data, start=4): fill = LIGHT_FILL if i % 2 == 0 else None _write_row(ws_overview, i, [field, value], fill=fill) ws_overview.cell(row=i, column=1).font = BOLD_FONT ws_overview.column_dimensions["A"].width = 25 ws_overview.column_dimensions["B"].width = 60 # Team Members row = 4 + len(info_data) + 1 ws_overview.merge_cells(f"A{row}:D{row}") ws_overview.cell(row=row, column=1, value="Team Members").font = SECTION_FONT row += 1 _write_header_row(ws_overview, row, ["Name", "Email", "Role(s)"]) row += 1 for member in project.team_members.all(): roles = ", ".join(member.get_role_names()) if hasattr(member, "get_role_names") else "" _write_row(ws_overview, row, [member.get_full_name(), member.email, roles]) row += 1 # --- Sheet 2: PDCA Phases --- ws_pdca = wb.create_sheet("PDCA Cycle") ws_pdca.merge_cells("A1:F1") ws_pdca.cell(row=1, column=1, value="PDCA Cycle").font = TITLE_FONT _write_header_row(ws_pdca, 3, ["Phase", "Title", "Status", "Owner", "Start Date", "Due Date"]) row = 4 phase_fills = { "plan": PatternFill(start_color="FEF3C7", end_color="FEF3C7", fill_type="solid"), "do": PatternFill(start_color="DBEAFE", end_color="DBEAFE", fill_type="solid"), "check": PatternFill(start_color="F3E8FF", end_color="F3E8FF", fill_type="solid"), "act": PatternFill(start_color="DCFCE7", end_color="DCFCE7", fill_type="solid"), } for phase_key, phase_label in [("plan", "Plan"), ("do", "Do"), ("check", "Check"), ("act", "Act")]: pdca_phase = None for p in project.pdca_phases.all(): if p.phase == phase_key: pdca_phase = p break fill = phase_fills.get(phase_key, LIGHT_FILL) if pdca_phase: _write_row( ws_pdca, row, [ phase_label, pdca_phase.title, pdca_phase.get_status_display(), pdca_phase.owner.get_full_name() if pdca_phase.owner else "", str(pdca_phase.start_date) if pdca_phase.start_date else "", str(pdca_phase.due_date) if pdca_phase.due_date else "", ], fill=fill, ) row += 1 if pdca_phase.description: ws_pdca.merge_cells(f"B{row}:F{row}") desc_cell = ws_pdca.cell(row=row, column=2, value=f"Description: {pdca_phase.description}") desc_cell.font = Font(size=9, italic=True, color="64748B") desc_cell.alignment = WRAP_ALIGN row += 1 if pdca_phase.findings: ws_pdca.merge_cells(f"B{row}:F{row}") find_cell = ws_pdca.cell(row=row, column=2, value=f"Findings: {pdca_phase.findings}") find_cell.font = Font(size=9, italic=True, color="64748B") find_cell.alignment = WRAP_ALIGN row += 1 else: _write_row(ws_pdca, row, [phase_label, "(Not started)", "", "", "", ""], fill=fill) row += 1 row += 1 ws_pdca.column_dimensions["A"].width = 12 ws_pdca.column_dimensions["B"].width = 40 ws_pdca.column_dimensions["C"].width = 15 ws_pdca.column_dimensions["D"].width = 20 ws_pdca.column_dimensions["E"].width = 15 ws_pdca.column_dimensions["F"].width = 15 # --- Sheet 3: Phase Tasks --- ws_tasks = wb.create_sheet("Phase Tasks") ws_tasks.merge_cells("A1:G1") ws_tasks.cell(row=1, column=1, value="Tasks by PDCA Phase").font = TITLE_FONT row = 3 phase_order = ["plan", "do", "check", "act"] for phase_key in phase_order: phase_label = dict(PDCAPhaseChoices.choices).get(phase_key, phase_key) phase_obj = None for p in project.pdca_phases.all(): if p.phase == phase_key: phase_obj = p break # Phase header ws_tasks.merge_cells(f"A{row}:G{row}") phase_header = ws_tasks.cell(row=row, column=1, value=f"{phase_label} Phase") phase_header.font = SECTION_FONT phase_header.fill = LIGHT_FILL phase_header.alignment = CENTER_ALIGN phase_header.border = THIN_BORDER row += 1 _write_header_row(ws_tasks, row, ["Title", "Description", "Assigned To", "Status", "Due Date", "Completed"]) row += 1 if phase_obj: phase_tasks = phase_obj.tasks.all().order_by("order") if phase_tasks: for task in phase_tasks: fill = GREEN_FILL if task.status == "completed" else None _write_row( ws_tasks, row, [ task.title, task.description, task.assigned_to.get_full_name() if task.assigned_to else "", task.get_status_display(), str(task.due_date) if task.due_date else "", str(task.completed_date) if task.completed_date else "", ], fill=fill, ) row += 1 else: ws_tasks.merge_cells(f"A{row}:G{row}") no_tasks = ws_tasks.cell(row=row, column=1, value="No tasks in this phase") no_tasks.font = Font(size=10, italic=True, color="94A3B8") no_tasks.alignment = CENTER_ALIGN row += 1 else: ws_tasks.merge_cells(f"A{row}:G{row}") no_phase = ws_tasks.cell(row=row, column=1, value="Phase not started") no_phase.font = Font(size=10, italic=True, color="94A3B8") no_phase.alignment = CENTER_ALIGN row += 1 row += 1 # spacing between phases ws_tasks.column_dimensions["A"].width = 30 ws_tasks.column_dimensions["B"].width = 40 ws_tasks.column_dimensions["C"].width = 20 ws_tasks.column_dimensions["D"].width = 15 ws_tasks.column_dimensions["E"].width = 15 ws_tasks.column_dimensions["F"].width = 15 return wb