<?php
ini_set('display_errors', 1);

require_once $base_path_to_learning . "vendor/autoload.php";
require_once $base_path_to_learning_resources . "marksheet_template/function.php";

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

function marksheet_gazette_report_template22($data)
{
    usort($data, function ($a, $b) {
        // First sort by section
        $sectionCompare = strcmp($a['section_name'], $b['section_name']);
        if ($sectionCompare !== 0) {
            return $sectionCompare;
        }

        // Then by student_name
        $nameCompare = strcmp($a['student_name'], $b['student_name']);
        if ($nameCompare !== 0) {
            return $nameCompare;
        }

        // Finally by admission_number
        return strcmp($a['admission_number'], $b['admission_number']);
    });

    ob_start();
    $spreadsheet = new Spreadsheet();

    // Remove the default sheet
    $spreadsheet->removeSheetByIndex(0);

    // Create first sheet (Marksheet)
    $sheet1 = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'MARKSHEET');
    $spreadsheet->addSheet($sheet1, 0);
    $spreadsheet->setActiveSheetIndex(0);

    // Generate content for first sheet
    generateMarksheet($spreadsheet->getActiveSheet(), $data);

    // Create second sheet (Competencies)
    $sheet2 = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'COMPETENCIES');
    $spreadsheet->addSheet($sheet2, 1);
    $spreadsheet->setActiveSheetIndex(1);

    // Generate content for second sheet
    generateCompetenciesSheet($spreadsheet->getActiveSheet(), $data);

    $spreadsheet->setActiveSheetIndex(0);

    $writer = new Xlsx($spreadsheet);

    // Set appropriate headers for download
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="gazette_report.xlsx"');
    header('Cache-Control: max-age=0');
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Pragma: public');

    // Clear any previous output
    ob_end_clean();

    // Output the Excel file directly
    $writer->save('php://output');

    // Ensure no further output
    exit;
}

function generateCompetenciesSheet($sheet, $data)
{
    // Document props
    $sheet->getParent()->getProperties()
        ->setCreator("Rejoice Learning")
        ->setTitle("Competencies Assessment - NURSERY")
        ->setSubject("Student Competencies Data");

    // Styles
    $titleStyle = [
        'font' => ['bold' => true, 'size' => 14],
        'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]
    ];
    $headerStyle = [
        'font' => ['bold' => true, 'size' => 10],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical' => Alignment::VERTICAL_CENTER,
            'wrapText' => true
        ],
        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
        'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['argb' => 'FFE0E0E0']]
    ];
    $categoryHeaderStyle = [
        'font' => ['bold' => true, 'size' => 11],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_LEFT,
            'vertical' => Alignment::VERTICAL_CENTER,
            'wrapText' => true
        ],
        'fill' => ['fillType' => Fill::FILL_SOLID],
        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]
    ];
    $subCategoryStyle = [
        'font' => ['bold' => true, 'italic' => true, 'size' => 10],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_LEFT,
            'vertical' => Alignment::VERTICAL_CENTER,
            'wrapText' => true
        ],
        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]
    ];
    $competencyStyle = [
        'font' => ['size' => 9],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_LEFT,
            'vertical' => Alignment::VERTICAL_CENTER,
            'wrapText' => true
        ],
        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]
    ];

    // Set up the header rows to match the template
    $row = 1;
    $sheet->setCellValue('A' . $row, $data[0]['institution_info']['institution_name'] . ' , ' . $data[0]['branch_info']['name']);
    $sheet->mergeCells('A' . $row . ':P' . $row);
    $sheet->getStyle('A' . $row)->applyFromArray($titleStyle);

    $row++;
    $classInfo = 'CLASS: ' . $data[0]['class_name'] . ' - ' . $data[0]['examination_name'] . ' (' . $data[0]['academic_session'] . ')';
    $sheet->setCellValue('A' . $row, $classInfo);
    $sheet->mergeCells('A' . $row . ':P' . $row);
    $sheet->getStyle('A' . $row)->applyFromArray($titleStyle);

    $row++;
    $sheet->setCellValue('A' . $row, 'ADM.NO.');
    $studentCount = count($data);
    $colIndex = 2; // start from B

    if ($studentCount === 0) {
        // ensure at least one student column exists to avoid merge errors
        $sheet->setCellValue('B' . $row, '');
        $colIndex = 3; // next free column index
    } else {
        foreach ($data as $i => $student) {
            $colLetter = Coordinate::stringFromColumnIndex($colIndex);
            $sheet->setCellValue($colLetter . $row, $student['admission_number'] ?? '');
            $colIndex++;
        }
    }

    $row++;
    $sheet->setCellValue('A' . $row, 'COMPETENCIES /ROLL NO.');
    $colIndex = 2; // start from B

    if ($studentCount === 0) {
        $sheet->setCellValue('B' . $row, '');
        $colIndex = 3;
    } else {
        foreach ($data as $i => $student) {
            $colLetter = Coordinate::stringFromColumnIndex($colIndex);
            $sheet->setCellValue($colLetter . $row, $i + 1);
            $colIndex++;
        }
    }

    $lastColLetter = Coordinate::stringFromColumnIndex($colIndex - 1);
    $row++;

    // Competencies text & keys (ordered)
    $competencies = [
        '18' => "Shows a liking for and understanding of\nnutritious food and does not waste food",
        '19' => "Practices basic self- care and hygiene",
        '20' => "Differentiates between shapes ,colours and their\nshades",
        '21' => "Differentiates multiple smells and tastes",
        '22' => "Developes discriminations in the sense of touch",
        '23' => "Shows balance , coordination and flexibility in\nvarious physical activities",
        '24' => "Shows precision and control in working with\ntheir hands and fingers",
        '25' => "Shows strength and endurance in carrying,\nwalking and running",
        '26' => "Recognises  'self 'as an individual belong to a\nfamily and community",
        '27' => "Recognises different emotions and makes deliberate efforts to regulate them",
        '28' => "Inteacts comfortably with other children and\nadults",
        '29' => "Shows kindness and helfulness to others"
    ];

    // --- PHYSICAL DEVELOPMENT (category header) ---
    $sheet->setCellValue('A' . $row, 'PHYSICAL DEVELOPMENT');
    $sheet->mergeCells('A' . $row . ':' . $lastColLetter . $row);
    $sheet->getStyle('A' . $row . ':' . $lastColLetter . $row)->applyFromArray($categoryHeaderStyle);
    $row++;

    // PART - I (HEALTHY & SAFE HABITS)
    $sheet->setCellValue('A' . $row, 'PART - I (HEALTHY & SAFE HABITS)');
    $sheet->mergeCells('A' . $row . ':' . $lastColLetter . $row);
    $sheet->getStyle('A' . $row . ':' . $lastColLetter . $row)->applyFromArray($subCategoryStyle);
    $row++;

    // First competency rows for PART-I
    $partI_keys = ['18', '19'];
    foreach ($partI_keys as $key) {
        $sheet->setCellValue('A' . $row, $competencies[$key]);
        $sheet->getRowDimension($row)->setRowHeight(30);
        $sheet->getStyle('A' . $row)->getAlignment()->setWrapText(true);

        // fill each student's grade
        $studentCol = 2;
        foreach ($data as $student) {
            $jsonData = json_decode($student['data'], true);
            $grade = $jsonData[$key] ?? 'A'; // Default to 'A' as in template
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($studentCol) . $row, $grade);
            $studentCol++;
        }

        $sheet->getStyle('A' . $row . ':' . $lastColLetter . $row)->applyFromArray($competencyStyle);
        $row++;
    }

    // --- PART - II (SENSORIAL PERCEPTIONS) ---
    $sheet->setCellValue('A' . $row, 'PART - II (SENSORIAL PERCEPTIONS)');
    $sheet->mergeCells('A' . $row . ':' . $lastColLetter . $row);
    $sheet->getStyle('A' . $row . ':' . $lastColLetter . $row)->applyFromArray($subCategoryStyle);
    $row++;

    // PART-II competencies
    $partII_keys = ['20', '21', '22'];
    foreach ($partII_keys as $key) {
        $sheet->setCellValue('A' . $row, $competencies[$key]);
        $sheet->getRowDimension($row)->setRowHeight(30);
        $sheet->getStyle('A' . $row)->getAlignment()->setWrapText(true);

        $studentCol = 2;
        foreach ($data as $student) {
            $jsonData = json_decode($student['data'], true);
            $grade = $jsonData[$key] ?? 'A'; // Default to 'A' as in template
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($studentCol) . $row, $grade);
            $studentCol++;
        }

        $sheet->getStyle('A' . $row . ':' . $lastColLetter . $row)->applyFromArray($competencyStyle);
        $row++;
    }

    // --- PART III (PHYSICAL FITNESS) ---
    $sheet->setCellValue('A' . $row, 'PART III (PHYSICAL FITNESS)');
    $sheet->mergeCells('A' . $row . ':' . $lastColLetter . $row);
    $sheet->getStyle('A' . $row . ':' . $lastColLetter . $row)->applyFromArray($subCategoryStyle);
    $row++;

    // PART-III competencies
    $partIII_keys = ['23', '24', '25'];
    foreach ($partIII_keys as $key) {
        $sheet->setCellValue('A' . $row, $competencies[$key]);
        $sheet->getRowDimension($row)->setRowHeight(30);
        $sheet->getStyle('A' . $row)->getAlignment()->setWrapText(true);

        $studentCol = 2;
        foreach ($data as $student) {
            $jsonData = json_decode($student['data'], true);
            $grade = $jsonData[$key] ?? 'A'; // Default to 'A' as in template
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($studentCol) . $row, $grade);
            $studentCol++;
        }

        $sheet->getStyle('A' . $row . ':' . $lastColLetter . $row)->applyFromArray($competencyStyle);
        $row++;
    }

    // --- SOCIO-EMOTIONAL & ETHICAL DEVELOPMENT (category header)
    $sheet->setCellValue('A' . $row, 'SOCIO - EMOTIONAL & ETHICAL DEVELOPMENT');
    $sheet->mergeCells('A' . $row . ':' . $lastColLetter . $row);
    $sheet->getStyle('A' . $row . ':' . $lastColLetter . $row)->applyFromArray($categoryHeaderStyle);
    $row++;

    // Socio-emotional competencies (no subcategory in template)
    $socio_keys = ['26', '27', '28', '29'];
    foreach ($socio_keys as $key) {
        $sheet->setCellValue('A' . $row, $competencies[$key]);
        $sheet->getRowDimension($row)->setRowHeight(30);
        $sheet->getStyle('A' . $row)->getAlignment()->setWrapText(true);

        $studentCol = 2;
        foreach ($data as $student) {
            $jsonData = json_decode($student['data'], true);
            $grade = $jsonData[$key] ?? 'A'; // Default to 'A' as in template
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($studentCol) . $row, $grade);
            $studentCol++;
        }

        $sheet->getStyle('A' . $row . ':' . $lastColLetter . $row)->applyFromArray($competencyStyle);
        $row++;
    }

    // Add empty rows as in template
    for ($i = 0; $i < 3; $i++) {
        $row++;
    }

    // Signatures at the bottom
    $sheet->setCellValue('A' . $row, 'Sign. of C.I:');
    $sheet->setCellValue('H' . $row, 'Sign. of W.I:');
    $sheet->setCellValue('P' . $row, 'Sign. of Principal:');

    // Formatting: headers, widths, freeze, borders
    $sheet->getStyle('A3:' . $lastColLetter . '4')->applyFromArray($headerStyle);
    $sheet->getColumnDimension('A')->setWidth(50);
    for ($i = 2; $i <= Coordinate::columnIndexFromString($lastColLetter); $i++) {
        $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($i))->setWidth(5);
    }

    // Freeze panes so columns A and top header rows remain visible
    // $sheet->freezePane('B5');
}


function generateMarksheet($sheet, $data)
{
    // ===== Document properties =====
    $sheet->getParent()->getProperties()
        ->setCreator("Rejoice Learning")
        ->setTitle("Marksheet Export - Kindergarten Template")
        ->setSubject("Student Marksheet Data");

    // ===== Styles =====
    $titleStyle = [
        'font' => ['bold' => true, 'size' => 14],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical'   => Alignment::VERTICAL_CENTER
        ]
    ];

    $headerStyle = [
        'font' => ['bold' => true, 'size' => 11],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical'   => Alignment::VERTICAL_CENTER,
            'wrapText'   => true
        ],
        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
        'fill'    => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['argb' => 'FFE0E0E0']]
    ];

    $cellStyle = [
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical'   => Alignment::VERTICAL_CENTER,
            'wrapText'   => true
        ],
        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]
    ];

    $signatureStyle = [
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_LEFT,
            'vertical'   => Alignment::VERTICAL_BOTTOM
        ]
    ];

    $sheet->setCellValue('A1', $data[0]['institution_info']['institution_name'] . ' , ' . $data[0]['branch_info']['name']);
    $sheet->setCellValue('A2', 'RESULT OF ' . $data[0]['examination_name'] . ' (' . $data[0]['academic_session'] . ')');
    $sheet->setCellValue('A3', 'CLASS: ' . $data[0]['class_name']);

    // ===== Subjects and headers =====
    $subjects = [
        'ENGLISH' => ['W (80)', 'O (20)', 'TOTAL (100)', 'G'],
        'MATH.' => ['W (80)', 'O (20)', 'TOTAL (100)', 'G'],
        'PUNJABI' => ['W (80)', 'O (20)', 'TOTAL (100)', 'G'],
        'E.V.S OR GK' => ['W (80)', 'O (20)', 'TOTAL (100)', 'G'],
        'RHYMES' => ['O (20)', 'G'],
        'CONV.' => ['O (20)', 'G'],
        'STORY' => ['O (20)', 'G'],
        'ART & CRAFT' => ['G']
    ];

    // Fixed student info columns
    $colIndex = 1;
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex++) . '5', 'ROLL NO.');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex++) . '5', 'ADM. NO');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex++) . '5', 'NAME');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex++) . '5', 'SECTION');

    // Subjects with subheaders
    foreach ($subjects as $subject => $subHeaders) {
        $startCol = $colIndex;
        foreach ($subHeaders as $sub) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex++) . '6', $sub);
        }
        $endCol = $colIndex - 1;
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startCol) . '5:' . Coordinate::stringFromColumnIndex($endCol) . '5');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startCol) . '5', $subject);
    }

    // Overall headers after subjects
    $overallHeaders = ['GRAND TOTAL', '%', 'RANK', 'ATTENDANCE'];
    foreach ($overallHeaders as $head) {
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex++) . '5', $head);
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($colIndex - 1) . '5:' . Coordinate::stringFromColumnIndex($colIndex - 1) . '6');
    }

    // Merge title rows across all columns
    $lastColLetter = Coordinate::stringFromColumnIndex($colIndex - 1);
    $sheet->mergeCells("A1:{$lastColLetter}1");
    $sheet->mergeCells("A2:{$lastColLetter}2");
    $sheet->mergeCells("A3:{$lastColLetter}3");

    // Apply styles
    $sheet->getStyle("A1:A3")->applyFromArray($titleStyle);
    $sheet->getStyle("A5:{$lastColLetter}6")->applyFromArray($headerStyle);

    // ===== Fill student data =====
    $row = 7;
    foreach ($data as $index => $studentData) {

    $jsonData = json_decode($studentData['data'], true);
    if (!is_array($jsonData)) {
        $jsonData = [];
    }

    // Helper function for safe numeric conversion
    $num = function ($key) use ($jsonData) {
        return isset($jsonData[$key]) && is_numeric($jsonData[$key]) ? (float)$jsonData[$key] : 0;
    };

    $col = 1;

    // Student info
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $index + 1);
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $studentData['admission_number']);
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $studentData['student_name']);
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $studentData['section_name']);

    // ===== ENGLISH =====
    $eng_w = $num('33');
    $eng_o = $num('34');
    $eng_total = $eng_w + $eng_o;

    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['33'] ?? '');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['34'] ?? '');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $eng_total);
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, get_grade($eng_total, 100));

    // ===== MATHS =====
    $math_w = $num('35');
    $math_o = $num('36');
    $math_total = $math_w + $math_o;

    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['35'] ?? '');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['36'] ?? '');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $math_total);
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, get_grade($math_total, 100));

    // ===== PUNJABI =====
    $pun_w = $num('37');
    $pun_o = $num('38');
    $pun_total = $pun_w + $pun_o;

    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['37'] ?? '');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['38'] ?? '');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $pun_total);
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, get_grade($pun_total, 100));

    // ===== GK =====
    $gk_w = $num('39');
    $gk_o = $num('40');
    $gk_total = $gk_w + $gk_o;

    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['39'] ?? '');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['40'] ?? '');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $gk_total);
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, get_grade($gk_total, 100));

    // ===== OTHER SUBJECTS =====
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['42'] ?? '');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['41'] ?? '');

    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['46'] ?? '');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['45'] ?? '');

    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['44'] ?? '');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['43'] ?? '');

    // ART & CRAFT (fix: removed wrong key)
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, '');

    // ===== GRAND TOTAL =====
    $fields = ['33','34','35','36','37','38','39', '40', '42','46','44'];
    $totalMarks = 0;

    foreach ($fields as $f) {
        $totalMarks += $num($f);
    }

    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $totalMarks);

    // ===== PERCENTAGE =====
    $percentage = $totalMarks > 0 ? round(($totalMarks / 460) * 100, 2) : 0;
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $percentage . ' %');

    // ===== RANK & ATTENDANCE =====
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, ''); // Rank (fix empty key)
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['17'] ?? '');

    // Style
    $sheet->getStyle("A{$row}:{$lastColLetter}{$row}")->applyFromArray($cellStyle);

    $row++;
}

    // ===== Signatures =====
    $signatureRow = $row + 2;
    $sheet->setCellValue('A' . $signatureRow, 'Sign. of C.L :');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex(10) . $signatureRow, 'Sign. of W.L :');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex(20) . $signatureRow, 'Sign. of PRINCIPAL :');

    $signatureRange = 'A' . $signatureRow . ':' . $lastColLetter . $signatureRow;
    $sheet->getStyle($signatureRange)->applyFromArray($signatureStyle);

    // ===== Column widths =====
    $sheet->getColumnDimension('A')->setWidth(8);
    $sheet->getColumnDimension('B')->setWidth(10);
    $sheet->getColumnDimension('C')->setWidth(25);

    // Set subject column widths (narrower for grade columns)
    for ($i = 4; $i <= Coordinate::columnIndexFromString($lastColLetter); $i++) {
        $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($i))->setWidth(6);
    }

    // Set specific widths for overall columns
    $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($colIndex - 3))->setWidth(12); // GRAND TOTAL
    $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($colIndex - 2))->setWidth(8);  // RANK
    $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($colIndex - 1))->setWidth(12); // ATTENDANCE
}
