<?php

function generateExcel($rs, $file_name) {    
    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();        
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);

    $list = $rs->getResult();
    $array = $rs->getResultArray();

    $style_col = [
        'font' => ['bold' => true]
    ];

    foreach ($list as $row) {
        $i = 1;
        foreach($row as $field=>$val) {
            if($field != "row_id" && $field != "line_id" && $field != "is_deleted" && $field != "created_date"
                && $field != "created_by" && $field != "modified_date" && $field != "modified_by"
                && $field != "deleted_date" && $field != "deleted_by") {
                $a = getAlphabetColumn($i);
                $spreadsheet->setActiveSheetIndex(0)->setCellValue($a.'1', strtoupper($field));
                $spreadsheet->getActiveSheet()->getStyle($a.'1')->applyFromArray($style_col);
                $i++;
            }
        }
        break;
    }

    $no = 1;
    $numrow = 2;
    foreach($list as $line){
        foreach ($array as $row) {
            $i = 1;
            foreach($row as $field=>$val) {
                if($field != "row_id" && $field != "line_id" && $field != "is_deleted" && $field != "created_date"
                    && $field != "created_by" && $field != "modified_date" && $field != "modified_by"
                    && $field != "deleted_date" && $field != "deleted_by") {
                    $a = getAlphabetColumn($i);
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue($a.$numrow, $line->$field);
                    $i++;
                }
            }
            break;
        }

        $no++;
        $numrow++;
    }

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename='.$file_name.'.xlsx');
    header('Cache-Control: max-age=0');	
    $writer->save('php://output');
    
}

function getAlphabetColumn($num) {
    $alphabet = "";
    if($num == 1) $alphabet = 'A';
    if($num == 2) $alphabet = 'B';
    if($num == 3) $alphabet = 'C';
    if($num == 4) $alphabet = 'D';
    if($num == 5) $alphabet = 'E';
    if($num == 6) $alphabet = 'F';
    if($num == 7) $alphabet = 'G';
    if($num == 8) $alphabet = 'H';
    if($num == 9) $alphabet = 'I';
    if($num == 10) $alphabet = 'J';
    if($num == 11) $alphabet = 'K';
    if($num == 12) $alphabet = 'L';
    if($num == 13) $alphabet = 'M';
    if($num == 14) $alphabet = 'N';
    if($num == 15) $alphabet = 'O';
    if($num == 16) $alphabet = 'P';
    if($num == 17) $alphabet = 'Q';
    if($num == 18) $alphabet = 'R';
    if($num == 19) $alphabet = 'S';
    if($num == 20) $alphabet = 'T';
    if($num == 21) $alphabet = 'U';
    if($num == 22) $alphabet = 'V';
    if($num == 23) $alphabet = 'W';
    if($num == 24) $alphabet = 'X';
    if($num == 25) $alphabet = 'Y';
    if($num == 26) $alphabet = 'Z';
    if($num == 27) $alphabet = 'AA';
    if($num == 28) $alphabet = 'AB';
    if($num == 29) $alphabet = 'AC';
    if($num == 30) $alphabet = 'AD';
    if($num == 31) $alphabet = 'AE';
    if($num == 32) $alphabet = 'AF';
    if($num == 33) $alphabet = 'AG';
    if($num == 34) $alphabet = 'AH';
    if($num == 35) $alphabet = 'AI';
    if($num == 36) $alphabet = 'AJ';
    if($num == 37) $alphabet = 'AK';
    if($num == 38) $alphabet = 'AL';
    if($num == 39) $alphabet = 'AM';
    if($num == 40) $alphabet = 'AN';
    if($num == 41) $alphabet = 'AO';
    if($num == 42) $alphabet = 'AP';
    if($num == 43) $alphabet = 'AQ';
    if($num == 44) $alphabet = 'AR';
    if($num == 45) $alphabet = 'AS';
    if($num == 46) $alphabet = 'AT';
    if($num == 47) $alphabet = 'AU';
    if($num == 48) $alphabet = 'AV';
    if($num == 49) $alphabet = 'AW';
    if($num == 50) $alphabet = 'AX';
    if($num == 51) $alphabet = 'AY';
    if($num == 52) $alphabet = 'AZ';
    if($num == 53) $alphabet = 'BA';
    if($num == 54) $alphabet = 'BB';
    if($num == 55) $alphabet = 'BC';
    if($num == 56) $alphabet = 'BD';
    if($num == 57) $alphabet = 'BE';
    if($num == 58) $alphabet = 'BF';
    if($num == 59) $alphabet = 'BG';
    if($num == 60) $alphabet = 'BH';
    if($num == 61) $alphabet = 'BI';
    if($num == 62) $alphabet = 'BJ';
    if($num == 63) $alphabet = 'BK';
    if($num == 64) $alphabet = 'BL';
    if($num == 65) $alphabet = 'BM';
    if($num == 66) $alphabet = 'BN';
    if($num == 67) $alphabet = 'BO';
    if($num == 68) $alphabet = 'BP';
    if($num == 69) $alphabet = 'BQ';
    if($num == 70) $alphabet = 'BR';
    if($num == 71) $alphabet = 'BS';
    if($num == 72) $alphabet = 'BT';
    if($num == 73) $alphabet = 'BU';
    if($num == 74) $alphabet = 'BV';
    if($num == 75) $alphabet = 'BW';
    if($num == 76) $alphabet = 'BX';
    if($num == 77) $alphabet = 'BY';
    if($num == 78) $alphabet = 'BZ';
    if($num == 79) $alphabet = 'CA';
    if($num == 80) $alphabet = 'CB';
    if($num == 81) $alphabet = 'CC';
    if($num == 82) $alphabet = 'CD';
    if($num == 83) $alphabet = 'CE';
    if($num == 84) $alphabet = 'CF';
    if($num == 85) $alphabet = 'CG';
    if($num == 86) $alphabet = 'CH';
    if($num == 87) $alphabet = 'CI';
    if($num == 88) $alphabet = 'CJ';
    if($num == 89) $alphabet = 'CK';
    if($num == 90) $alphabet = 'CL';
    if($num == 91) $alphabet = 'CM';
    if($num == 92) $alphabet = 'CN';
    if($num == 93) $alphabet = 'CO';
    if($num == 94) $alphabet = 'CP';
    if($num == 95) $alphabet = 'CQ';
    if($num == 96) $alphabet = 'CR';
    if($num == 97) $alphabet = 'CS';
    if($num == 98) $alphabet = 'CT';
    if($num == 99) $alphabet = 'CU';
    if($num == 100) $alphabet = 'CV';
    if($num == 101) $alphabet = 'CW';
    if($num == 102) $alphabet = 'CX';
    if($num == 103) $alphabet = 'CY';
    if($num == 104) $alphabet = 'CZ';

    return $alphabet;
}

?>