sgi/gema/export/xls_alumnossemestre.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, 331, GEMA);
if(!$objSesion->tieneAcceso()){
    echo "No tienes permiso de ver esta página. Revisa que tu sesión siga activa.";
    exit();
}
unset($objValida);


if(!isset($_POST["sem_ini"]) || !is_numeric($_POST["sem_ini"]) || !isset($_POST["sem_fin"]) || !is_numeric($_POST["sem_fin"])){
    echo "No se recibieron los datos necesarios.";
    exit();
}

$filter_sem_ini = filter_input(INPUT_POST, "sem_ini", FILTER_SANITIZE_NUMBER_INT);//limpia texto
$filter_sem_fin = filter_input(INPUT_POST, "sem_fin", FILTER_SANITIZE_NUMBER_INT);//limpia texto
if(isset($_POST["carrera"]) && $_POST["carrera"]!=""){
    $stmt = $pdo->prepare('Select * from fs_alumnossemestre(:per, :sem_ini, :sem_fin, :carr)');
    $filter_carrera = filter_input(INPUT_POST, "carrera", FILTER_SANITIZE_NUMBER_INT);//limpia texto
    $stmt->bindParam(":carr", $filter_carrera);
}else{
    $stmt = $pdo->prepare('Select * from fs_alumnossemestre(:per, :sem_ini, :sem_fin, NULL)');
}
$stmt->bindParam(":per", $_SESSION["periodo_id"]);
$stmt->bindParam(":sem_ini", $filter_sem_ini);
$stmt->bindParam(":sem_fin", $filter_sem_fin);

if(!$stmt->execute()){
    $errorDesc = "Ocurrió un error al cargar los alumnos.";
    //print_r($stmt->errorInfo());
}else{
    $alumnos_rs = $stmt->fetchAll();
}
$stmt->closeCursor();
$stmt = null;


$prefijo = $filter_sem_ini." a ".$filter_sem_fin;
//--------
//
// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Set document properties
$spreadsheet->getProperties()->setCreator('Ingeniería La Salle')
    ->setLastModifiedBy('Ingeniería La Salle')
    ->setTitle('Alumnos por seemstre')
    ->setDescription('Alumnos por semestre.');


$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'],
        ]
    ]
);
$titleStyle = new Style();
$titleStyle->applyFromArray(
    [
        'font' => [
            'bold'  =>  true,
            'size' => 14,
        ]
    ]
);


//--------
$sheet = 0;
$spreadsheet->setActiveSheetIndex($sheet)->setTitle('Alumnos '.$prefijo.' semestre');


$spreadsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 1, "Alumnos ".$prefijo.' semestre');
$spreadsheet->getActiveSheet()->mergeCells('A1:D1');
$spreadsheet->getActiveSheet()->duplicateStyle($titleStyle, 'A1:D1');

$row = 3;
$col = 1;
$spreadsheet->getActiveSheet()
                ->setCellValueByColumnAndRow($col++, $row, "Clave")
                ->setCellValueByColumnAndRow($col++, $row, "Apellidos")
                ->setCellValueByColumnAndRow($col++, $row, "Nombre")
                ->setCellValueByColumnAndRow($col++, $row, "Correo institucional")
                ->setCellValueByColumnAndRow($col++, $row, "Generación")
                ->setCellValueByColumnAndRow($col++, $row, "Carrera")
                ->setCellValueByColumnAndRow($col++, $row, "Plan");
$colMax = $col;

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

$row++;
$startRow = $row;
foreach($alumnos_rs as $alumno){
    $col = 1;
    //$spreadsheet->setActiveSheetIndex($sheet)

    $correo = "";
    $stmt = $pdo->prepare('SELECT "Contacto_valor" from fs_contacto(:id, 3, NULL) WHERE "Contacto_valor" ILIKE \'%\' || \'lasallistas.org.mx\'');
    $stmt->bindParam(":id", $alumno["Usuario_id"]);
    if($stmt->execute()){
        $contacto_rs = $stmt->fetch();
        $correo = $contacto_rs["Contacto_valor"];
    }
    $stmt->closeCursor();

    $spreadsheet->getActiveSheet()
        ->setCellValueByColumnAndRow($col++, $row, $alumno["Usuario_claveULSA"])
        ->setCellValueByColumnAndRow($col++, $row, $alumno["Usuario_apellidos"])
        ->setCellValueByColumnAndRow($col++, $row, $alumno["Usuario_nombre"])
        ->setCellValueByColumnAndRow($col++, $row, $correo)
        ->setCellValueByColumnAndRow($col++, $row, fechaMonthPicker($alumno["Alumno_generacion"]))
        ->setCellValueByColumnAndRow($col++, $row, $alumno["Carrera_desc"])
        ->setCellValueByColumnAndRow($col++, $row, $alumno["PlanEstudio_desc"]);

    $spreadsheet->getActiveSheet()->getStyle(numberToLetter(1).$row)->getNumberFormat()->setFormatCode('000000');
    $row++;
}

$row--;


//Auto ajustar anchos
for($col = 1; $col < $colMax; $col++)
    $spreadsheet->getActiveSheet()->getColumnDimension(numberToLetter($col))->setAutoSize(true);


//---------
// 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="alumnos_'.str_replace(' ','_', $prefijo).'.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;