servicio-autos-api/modules/excel/controllers/ObraHerramientaController.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 v1\models\ObraHerramienta;
use Yii;
use yii\web\Controller;

class ObraHerramientaController extends Controller {

    public function actionExcelObraHerramienta($idObra) {
      $obra = Obra::findOne($idObra);
        $request = Yii::$app->request;
        $query= ObraHerramienta::find()	
        ->joinWith('herramienta')
        ->andWhere(['{{ObraHerramienta}}.[[eliminado]]'=>null])->andWhere(['{{ObraHerramienta}}.[[idObra]]'=>$idObra]);
        $query->orderBy(['creado' => SORT_DESC]);
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Herramientas de la obra');

        $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:J5');
        $sheet->setCellValue('A5','Herramientas relacionadas a la obra: ' . $obra->nombre);
        $sheet->mergeCells('A6:E6');
        $sheet->setCellValue('A6','Fecha Inicial: ' .$fechaInicio);
        $sheet->mergeCells('F6:J6');
        $sheet->setCellValue('F6','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:J5')->applyFromArray( $headerStyle);
      $sheet->getStyle('A6:E6')->applyFromArray( $headerStyle);
      $sheet->getStyle('F6:J6')->applyFromArray( $headerStyle);
       
      $sheet->mergeCells('A8:D8');
      $sheet->setCellValue('A8', 'Herramienta');
      $sheet->getStyle('A8:D8')->applyFromArray( $headerStyle);
      $sheet->mergeCells('E8:J8');
      $sheet->setCellValue('E8', 'Cantidad');
      $sheet->getStyle('E8:J8')->applyFromArray( $headerStyle);
      $cellBorderStyle = [
        'borders' => [
            'allBorders' => [
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                'color' => ['argb' => 'FF000000'], // Color negro
            ],
        ],
    ];
      $row = 9;

        foreach ($query->each() as $obraHerramienta) {
            $herramienta = $obraHerramienta->herramienta; 
            $sheet->mergeCells('A' . $row . ':D' . $row);
            $sheet->setCellValue('A' . $row, $herramienta->nombre); // Nombre del empleado
            $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 . ':J' . $row);
            $sheet->setCellValue('E' . $row, $obraHerramienta->cantidad); // Nombre del empleado
            $sheet->getStyle('E' . $row . ':J' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY);
            $sheet->getStyle('E' . $row . ':J' . $row)->applyFromArray($cellBorderStyle);

            $row++;
        }
       
       
        $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, "Herramientas.xlsx");
        } catch (\Exception $exception) {
          return null;
        }
    }
}