File size: 5,271 Bytes
788eab4 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 | """
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 styling
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
# Index schedule by student_id
sched_map: dict[str, ScheduledItem] = {}
for item in schedule_items:
for sid in item.student_ids:
sched_map[sid] = item
# Populate candidate rows
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)
# Set column widths
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:
# Default auto-width
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()
|