servicio-autos-api/modules/excel/controllers/ObraController.php

<?php

namespace app\modules\excel\controllers;
use v1\models\ObraEmpleado;
use v1\models\Usuario;
use DateTime;
use DateTimeZone;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use v1\models\Obra;
use Yii;
use yii\web\Controller;

class ObraController extends Controller {

    public function actionExcelObra($idObra) {
      $obra = Obra::findOne($idObra);
        $request = Yii::$app->request;
       
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Empleados');

        $BASEPATH = \Yii::getAlias('@app') . "/web";
        $logo = $BASEPATH . '/img/logos/edesarrollos-unicolor-azul.png';
        $fechaInicio = (new DateTime($obra->fechaInicio))->format('d-m-Y');
        $fechaFin = (new DateTime($obra->fechaFinal))->format('d-m-Y');
        $drawing = new Drawing();
        $drawing->setName('Logotipo');
        $drawing->setDescription('Logotipo de la empresa');
        $drawing->setPath($logo);
        $drawing->setHeight(70);
        $drawing->setCoordinates('A1');
        $drawing->setOffsetX(10);
        $drawing->setOffsetY(10);
        $drawing->setWorksheet($sheet);

        $sheet->mergeCells('A5:N5');
        $sheet->setCellValue('A5','Obra: ' . $obra->nombre);
        $sheet->mergeCells('A6:G6');
        $sheet->setCellValue('A6','Fecha Inicial: ' .$fechaInicio);
        $sheet->mergeCells('H6:N6');
        $sheet->setCellValue('H6','Fecha Final: ' .$fechaFin);
        $sheet->getStyle('A5')->getFont()->setBold(true);
        $sheet->getStyle('A5')->getFont()->setSize(16);

        $headerStyle = [
            'font' => [
                'bold' => true,
                'color' => ['argb' => 'FFFFFFFF'] // Letras en blanco
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true
            ],
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                'startColor' => ['argb' => 'FF625FF5'] // Fondo azul (con opacidad FF)
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => 'FF020073'], // Bordes azul oscuro (con opacidad FF)
                ],
            ],
        ];
        
        
        
        // Aplicar el borde a las celdas combinadas
        $sheet->getStyle('A5:N5')->applyFromArray( $headerStyle);
        $sheet->getStyle('A6:G6')->applyFromArray( $headerStyle);
        $sheet->getStyle('H6:N6')->applyFromArray( $headerStyle);
      
        //Empleados
      $empleados= ObraEmpleado::find()	
      ->joinWith('empleado')
      ->andWhere(['{{ObraEmpleado}}.[[eliminado]]'=>null])->andWhere(['{{ObraEmpleado}}.[[idObra]]'=>$idObra]);
      $empleados->orderBy(['creado' => SORT_DESC]);
      
      $sheet->mergeCells('A8:G8');
      $sheet->setCellValue('A8', 'Empleados');
      $sheet->getStyle('A8:G8')->applyFromArray( $headerStyle);
      $sheet->mergeCells('A9:D9');
      $sheet->setCellValue('A9', 'Nombre');
      $sheet->getStyle('A9:D9')->applyFromArray( $headerStyle);
      $sheet->mergeCells('E9:G9');
      $sheet->setCellValue('E9', 'Fecha Alta');
      $sheet->getStyle('E9:G9')->applyFromArray( $headerStyle);
      $cellBorderStyle = [
        'borders' => [
            'allBorders' => [
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                'color' => ['argb' => 'FF000000'], // Color negro
            ],
        ],
    ];
      $row = 10;

        foreach ($empleados->each() as $obraEmpleado) {
            $empleado = $obraEmpleado->empleado; 
            $sheet->mergeCells('A' . $row . ':D' . $row);
            $sheet->setCellValue('A' . $row, $empleado->nombre); 
            $sheet->getStyle('A' . $row . ':D' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY);
            $sheet->getStyle('A' . $row . ':D' . $row)->applyFromArray($cellBorderStyle);

          
            $sheet->mergeCells('E' . $row . ':G' . $row);
            $sheet->setCellValue('E' . $row, $empleado->fechaAlta); 
            $sheet->getStyle('E' . $row . ':G' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY);
            $sheet->getStyle('E' . $row . ':G' . $row)->applyFromArray($cellBorderStyle);
            $row++;
        }

   //Herramientas



/*
      // Aplicar el borde a las celdas combinadas
      $sheet->getStyle('A5:J5')->applyFromArray( $headerStyle);
      $sheet->getStyle('A6:E6')->applyFromArray( $headerStyle);
      $sheet->getStyle('F6:J6')->applyFromArray( $headerStyle);
       
      $sheet->mergeCells('A8:B8');
      $sheet->getStyle('A8:B8')->applyFromArray( $headerStyle);
      $sheet->setCellValue('A8', 'Gasto total');
      
      $sheet->mergeCells('C8:D8');
      $sheet->getStyle('C8:D8')->applyFromArray( $headerStyle);
      $sheet->setCellValue('C8', 'Pago total');
       
      $sheet->mergeCells('E8:G8');
      $sheet->getStyle('E8:G8')->applyFromArray( $headerStyle);
      $sheet->setCellValue('E8', 'Pago total');

      $sheet->mergeCells('H8:J8');
      $sheet->getStyle('E8:F8')->applyFromArray( $headerStyle);
      $sheet->setCellValue('E8', 'Pago total');
*/
        $sheet->getStyle('A5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        try {
          ob_start();
          $writer->save("php://output");
          $documento = ob_get_contents();
          ob_clean();
          Yii::$app->getResponse()->sendContentAsFile($documento, "Obra.xlsx");
        } catch (\Exception $exception) {
          return null;
        }
    }
}