sgi/gema/export/xls_insigniasgeneral.php

<?php

setlocale(LC_TIME, 'es_MX.UTF-8');
require_once("../../include/nocache.php");
require_once("../../include/constantes.php");
require_once("../../include/bd_pdo.php");
require_once("../../include/util.php");
require_once("../../classes/ValidaSesion.php");
require "../../include/phpSpreadsheet/autoload.php";

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Style;
use PhpOffice\PhpSpreadsheet\Shared\Date;


function numberToLetter ($number){
    $temp = ($number-1) % 26;
    return chr($temp + 65);
};

//--- Objeto para validar usuario. El id de usuario lo lee desde sesión
$objSesion = new ValidaSesion($pdo, 113, GEMA);
if(!$objSesion->tieneAcceso()){
    echo "No tienes permiso de ver esta página. Revisa que tu sesión siga activa.";
    exit();
}
unset($objValida);

$filter_periodo = $_SESSION["periodo_id"];
$query = ":periodo, NULL, ";
if(isset($_POST["nombre"]) && trim($_POST["nombre"]) != ""){
    $query .= ":nombre,";
    $filter_nombre = trim(filter_input(INPUT_POST, "nombre", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto
}else{
    $query .= "NULL,";
}

if(isset($_POST["tipo"]) && $_POST["tipo"]!=""){
    $filter_tipo = filter_input(INPUT_POST, "tipo", FILTER_SANITIZE_NUMBER_INT);
    $query.= ":tipo, ";
}else{
    $query.= "NULL, ";
}
$query .= "true, true, 0, NULL";
$stmt = $pdo->prepare('Select * from fs_insignia('.$query.')');
$stmt->bindParam(":periodo", $filter_periodo);
if(isset($filter_nombre)) $stmt->bindParam(":nombre", $filter_nombre);
if(isset($filter_tipo)) $stmt->bindParam(":tipo", $filter_tipo);
if($stmt->execute()){
    $insignias_rs = $stmt->fetchAll();
    $stmt->closeCursor();
    $insignias_arr = array();
    //Revisa fechas para validar si el evento está en el rango
}

//--------
//
// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Set document properties
$spreadsheet->getProperties()->setCreator('Ingeniería La Salle')
    ->setLastModifiedBy('Ingeniería La Salle')
    ->setTitle('Insignias Generales')
    ->setDescription('Insignias generales del periodo.');


$headerStyle = new Style();
$headerStyle->applyFromArray(
    [
        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'color' => ['argb' => 'FF001d68'],
        ],
        'borders' => [
            'bottom' => ['borderStyle' => Border::BORDER_THIN],
            'right' => ['borderStyle' => Border::BORDER_MEDIUM],
        ],
        'font' => [
            'bold'  =>  true,
            'color' => ['argb' => 'FFFFFFFF'],
        ]
    ]
);


//--------
$sheet = 0;
// Rename worksheet

$spreadsheet->setActiveSheetIndex($sheet)->setTitle('Insignias generales');

$row = 1;
$col = 1;
$spreadsheet->getActiveSheet()
                ->setCellValueByColumnAndRow($col++, $row, "Insignia")
                ->setCellValueByColumnAndRow($col++, $row, "Descripción")
                ->setCellValueByColumnAndRow($col++, $row, "Tipo")
                ->setCellValueByColumnAndRow($col++, $row, "Fecha inicio")
                ->setCellValueByColumnAndRow($col++, $row, "Alumnos");
$colMax = $col;

$colLetter = numberToLetter($colMax-1);
$spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A1:'.$colLetter.'1');

$row = 2;
$startRow = $row;
foreach($insignias_rs as $insignia){
    $col = 1;
    //$spreadsheet->setActiveSheetIndex($sheet)
    $spreadsheet->getActiveSheet()
        ->setCellValueByColumnAndRow($col++, $row, $insignia["Insignia_titulo"])
        ->setCellValueByColumnAndRow($col++, $row, $insignia["Insignia_desc"])
        ->setCellValueByColumnAndRow($col++, $row, $insignia["InsigniaTipo_desc"])
        ->setCellValueByColumnAndRow($col++, $row, $insignia["CalendarioEvento_fecha"])
        ->setCellValueByColumnAndRow($col++, $row, $insignia["Alumnos_total"]);
    
    $spreadsheet->getActiveSheet()->getStyle(numberToLetter(4).$row)->getNumberFormat()
        ->setFormatCode('dd/mm/yyyy');
    $row++;
}

$row--;


//Auto ajustar anchos
$spreadsheet->getActiveSheet()->getColumnDimension(numberToLetter(1))->setWidth(50);
$spreadsheet->getActiveSheet()->getColumnDimension(numberToLetter(2))->setWidth(75);
$spreadsheet->getActiveSheet()->getColumnDimension(numberToLetter(3))->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension(numberToLetter(4))->setAutoSize(true);

$spreadsheet->getActiveSheet()->getStyle('A2:B'.$row)->getAlignment()->setWrapText(true);
$spreadsheet->getActiveSheet()->getStyle('A2:'.$colLetter.$row)
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);

//$spreadsheet->getActiveSheet()->getStyle('A1:H1')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->setAutoFilter($spreadsheet->getActiveSheet()->calculateWorksheetDimension());


//---------
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$spreadsheet->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="insignias_general.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;