sgi/apsa/export/xls_profesorarea.php

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
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\Style\Color;
use PhpOffice\PhpSpreadsheet\Shared\Date;

$pag = "../reporte_horarioprofesores.php";

function comparaMaterias(&$usuariosArr, $fileArr, $electivas = false ){
    foreach($usuariosArr as $index => $usuario){
        //$row = $usuario["Carrera_id"].",".$usuario["PlanEstudio_id"].",".$usuario["Grupo_id"].",".$usuario["Materia_id"].",".$usuario["Submateria_id"].",".$usuario["Usuario_id"]."\n";
        if(!$electivas)
            $row = $usuario["Grupo_desc"].$usuario["Carrera_prefijo"].",".$usuario["Materia_id"].",".$usuario["Usuario_id"]."\n";
        else
            $row = "Electivas,".$usuario["Submateria_id"].",".$usuario["Usuario_id"]."\n";

        //echo $row;
        if(!in_array($row, $fileArr)){
            $usuariosArr[$index]["modificado"] = 1;

            //echo $index.") No está";
        }else{
            $usuariosArr[$index]["modificado"] = 0;
        }
        //echo "<br>";
    }
}


//--- Objeto para validar usuario. El id de usuario lo lee desde sesión
$objSesion = new ValidaSesion($pdo, 71, APSA);
if(!$objSesion->tieneAcceso()){
    header("Location: ".$pag."?error=3");
    exit();
}

unset($objValida);

$query ="";
if(isset($_POST["area"]) && is_numeric($_POST["area"])){
    $query .= ":area, ";
    $filter_area = filter_input(INPUT_POST, "area", FILTER_SANITIZE_NUMBER_INT);//limpia texto
}else{
    $query .= "NULL, ";
}
if(isset($_POST["desc"])){
    $query .= ":nom, ";
    $filter_desc = trim(filter_input(INPUT_POST, "desc", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto
}else{
    $query .= "'', ";
}
if(isset($_POST["clave"])){
    $query .= ":clave, ";
    $filter_clave = filter_input(INPUT_POST, "clave", FILTER_SANITIZE_NUMBER_INT);//limpia texto
}else{
    $query .= "'', ";
}

$stmt = $pdo->prepare('Select * from fs_profesoresarea('.$query.' :periodo, 1, 0, NULL)');
if(isset($filter_area)) $stmt->bindParam(":area", $filter_area);
if(isset($filter_desc)) $stmt->bindParam(":nom", $filter_desc);
if(isset($filter_clave)) $stmt->bindParam(":clave", $filter_clave);
$stmt->bindParam(":periodo", $_SESSION["periodo_id"]);

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

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

// Set document properties
$spreadsheet->getProperties()->setCreator('Ingeniería La Salle')
    ->setLastModifiedBy('Ingeniería La Salle')
    ->setTitle('Profesores por área')
    ->setDescription('Profesores por área en el periodo.');

$row_base = 6;
//crea imagen
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setName('La Salle');
$drawing->setDescription('La Salle');
$drawing->setPath('../../img/logopdf.png'); // put your path and image here
$drawing->setCoordinates('A1');
$drawing->setHeight(100);
$drawing->setOffsetX(10);
//agrega imagen
$drawing->setWorksheet($spreadsheet->getActiveSheet());

//crea imagen
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setName('Facultad de Ingenería');
$drawing->setDescription('Facultad de Ingenería');
$drawing->setPath('../../img/logoIngenieria.png'); // put your path and image here
$drawing->setCoordinates('F1');
$drawing->setHeight(100);
$drawing->setOffsetX(-20);
//agrega imagen
$drawing->setWorksheet($spreadsheet->getActiveSheet());

// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle($_SESSION["periodo_desc"]);

$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'],
        ]
    ]
);


// Add some data
$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A'.$row_base, 'Clave ULSA')
    ->setCellValue('B'.$row_base, 'Grado')
    ->setCellValue('C'.$row_base, 'Apellidos')
    ->setCellValue('D'.$row_base, 'Nombre')
    ->setCellValue('E'.$row_base, 'Correo')
    ->setCellValue('F'.$row_base, 'Área');
    

$spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A'.$row_base.':F'.$row_base);
$spreadsheet->getActiveSheet()->setAutoFilter('A'.$row_base.':F'.$row_base);
//$highestRow = count($usuario_rs)+1;

$row = $row_base+1;
if(isset($usuario_rs)){
    foreach($usuario_rs as $usuario){
        $col = 1;
        
        $spreadsheet->getActiveSheet()
            ->setCellValueByColumnAndRow($col++, $row, $usuario["Usuario_claveULSA"])
            ->setCellValueByColumnAndRow($col++, $row, $usuario["Usuario_grado"])
            ->setCellValueByColumnAndRow($col++, $row, $usuario["Usuario_apellidos"])
            ->setCellValueByColumnAndRow($col++, $row, $usuario["Usuario_nombre"])
            ->setCellValueByColumnAndRow($col++, $row, $usuario["Correo_valor"])
            ->setCellValueByColumnAndRow($col++, $row, $usuario["Area_desc"]);
        $color = 'ff'.substr($usuario["Area_color"], 1);
        /*$spreadsheet->getActiveSheet()->getStyle('C'.$row)
            ->getFont()->getColor()->setARGB($color);*/
        $spreadsheet->getActiveSheet()->getStyle('F'.$row)
            ->getFill()->setFillType(Fill::FILL_SOLID)->setStartColor(new Color($color));

        $row++;
    }
}

$otro = 5;

//Auto ajustar anchos
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(3);

// 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="profesoresArea_'.date("y-m-d").'.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;