| """
|
| Custom Company-Format XLSX & CSV Exporter.
|
| Renders candidate shortlists and interview schedules according to company-specific templates.
|
| """
|
|
|
| from __future__ import annotations
|
|
|
| import csv
|
| import io
|
| from typing import Any
|
|
|
| import openpyxl
|
| from openpyxl.styles import Alignment, Font, PatternFill
|
| from openpyxl.utils import get_column_letter
|
|
|
| from backend.app.api.export.mapping import CompanyExportMapping
|
| from backend.app.domain.models import Student
|
| from backend.app.scheduler.models import ScheduledItem
|
|
|
|
|
| def export_to_company_xlsx(
|
| students: list[Student],
|
| schedule_items: list[ScheduledItem],
|
| mapping: CompanyExportMapping,
|
| ) -> bytes:
|
| """Exports candidates to an XLSX matching the company's exact layout and column widths."""
|
| wb = openpyxl.Workbook()
|
| ws = wb.active
|
| assert ws is not None
|
| ws.title = mapping.sheet_name or "Candidates"
|
|
|
|
|
| header_font = Font(name="Calibri", size=11, bold=True, color="000000")
|
| header_fill = PatternFill(start_color="E0E0E0", end_color="E0E0E0", fill_type="solid")
|
| center_align = Alignment(horizontal="center", vertical="center")
|
|
|
| headers = mapping.column_order
|
| ws.append(headers)
|
|
|
| for col_num in range(1, len(headers) + 1):
|
| cell = ws.cell(row=1, column=col_num)
|
| cell.font = header_font
|
| cell.fill = header_fill
|
| cell.alignment = center_align
|
|
|
|
|
| sched_map: dict[str, ScheduledItem] = {}
|
| for item in schedule_items:
|
| for sid in item.student_ids:
|
| sched_map[sid] = item
|
|
|
|
|
| for s in students:
|
| slot = sched_map.get(s.id)
|
| row_vals = []
|
| for h in headers:
|
| canonical_key = mapping.canonical_mapping.get(h)
|
| val: Any = ""
|
| if canonical_key == "roll_no":
|
| val = s.roll_no
|
| elif canonical_key == "name":
|
| val = s.name
|
| elif canonical_key == "email":
|
| val = s.email
|
| elif canonical_key == "branch":
|
| val = s.branch
|
| elif canonical_key == "cgpa":
|
| val = s.cgpa
|
| elif canonical_key == "tenth_percentage":
|
| val = s.tenth_percentage
|
| elif canonical_key == "twelfth_percentage":
|
| val = s.twelfth_percentage or ""
|
| elif canonical_key == "round_name":
|
| val = slot.round_name if slot else ""
|
| elif canonical_key == "slot_time":
|
| val = slot.start_time.strftime("%H:%M") if slot else ""
|
| elif canonical_key == "room_name":
|
| val = slot.room_name if slot else ""
|
| elif canonical_key == "panel_name":
|
| val = slot.panel_name or "" if slot else ""
|
|
|
| row_vals.append(val)
|
|
|
| ws.append(row_vals)
|
|
|
|
|
| for idx, h in enumerate(headers, start=1):
|
| col_letter = get_column_letter(idx)
|
| if h in mapping.column_widths:
|
| ws.column_dimensions[col_letter].width = mapping.column_widths[h]
|
| else:
|
|
|
| max_len = max(len(str(h)), 12)
|
| ws.column_dimensions[col_letter].width = max_len + 3
|
|
|
| buffer = io.BytesIO()
|
| wb.save(buffer)
|
| buffer.seek(0)
|
| return buffer.getvalue()
|
|
|
|
|
| def export_to_company_csv(
|
| students: list[Student],
|
| schedule_items: list[ScheduledItem],
|
| mapping: CompanyExportMapping,
|
| ) -> str:
|
| """Exports candidates to CSV matching the company's exact header ordering."""
|
| output = io.StringIO()
|
| writer = csv.writer(output)
|
| headers = mapping.column_order
|
| writer.writerow(headers)
|
|
|
| sched_map: dict[str, ScheduledItem] = {}
|
| for item in schedule_items:
|
| for sid in item.student_ids:
|
| sched_map[sid] = item
|
|
|
| for s in students:
|
| slot = sched_map.get(s.id)
|
| row_vals = []
|
| for h in headers:
|
| canonical_key = mapping.canonical_mapping.get(h)
|
| val: Any = ""
|
| if canonical_key == "roll_no":
|
| val = s.roll_no
|
| elif canonical_key == "name":
|
| val = s.name
|
| elif canonical_key == "email":
|
| val = s.email
|
| elif canonical_key == "branch":
|
| val = s.branch
|
| elif canonical_key == "cgpa":
|
| val = s.cgpa
|
| elif canonical_key == "tenth_percentage":
|
| val = s.tenth_percentage
|
| elif canonical_key == "twelfth_percentage":
|
| val = s.twelfth_percentage or ""
|
| elif canonical_key == "round_name":
|
| val = slot.round_name if slot else ""
|
| elif canonical_key == "slot_time":
|
| val = slot.start_time.strftime("%H:%M") if slot else ""
|
| elif canonical_key == "room_name":
|
| val = slot.room_name if slot else ""
|
| elif canonical_key == "panel_name":
|
| val = slot.panel_name or "" if slot else ""
|
|
|
| row_vals.append(val)
|
| writer.writerow(row_vals)
|
|
|
| return output.getvalue()
|
|
|
