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

class ObraGastoController extends Controller {

    public function actionExcelObraGasto($idObra) {
      $obra = Obra::findOne($idObra);
        $request = Yii::$app->request;
        $query= Gasto::find()	
        ->joinWith('conceptosObra')
        ->andWhere(['{{Gasto}}.[[eliminado]]'=>null])->andWhere(['{{Gasto}}.[[idObra]]'=>$idObra]);
        $query->orderBy(['creado' => SORT_DESC]);
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Gastos 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','Gastos relacionado 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:C8');
      $sheet->setCellValue('A8', 'Concepto Obra');
      $sheet->getStyle('A8:C8')->applyFromArray( $headerStyle);
      $sheet->mergeCells('D8:E8');
      $sheet->setCellValue('D8', 'Cantidad');
      $sheet->getStyle('D8:E8')->applyFromArray( $headerStyle);
      $sheet->mergeCells('F8:G8');
      $sheet->setCellValue('F8', 'Fecha Compra');
      $sheet->getStyle('F8:G8')->applyFromArray( $headerStyle);
      $sheet->mergeCells('H8:J8');
      $sheet->setCellValue('H8', 'Descripción');
      $sheet->getStyle('H8:J8')->applyFromArray( $headerStyle);
      $cellBorderStyle = [
        'borders' => [
            'allBorders' => [
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                'color' => ['argb' => 'FF000000'], // Color negro
            ],
        ],
    ];
      $row = 9;

        foreach ($query->each() as $obraGasto) {
            $fecha = (new DateTime($obraGasto->fechaCompra))->format('d-m-Y');

            $gasto = $obraGasto->conceptosObra; 
            $sheet->mergeCells('A' . $row . ':C' . $row);
           $sheet->setCellValue('A' . $row, $gasto->concepto); 
            $sheet->getStyle('A' . $row . ':C' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY);
            $sheet->getStyle('A' . $row . ':C' . $row)->applyFromArray($cellBorderStyle);

            $sheet->mergeCells('D' . $row . ':E' . $row);
            $sheet->setCellValue('D' . $row, $obraGasto->cantidad); 
            $sheet->getStyle('D' . $row . ':E' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY);
            $sheet->getStyle('D' . $row . ':E' . $row)->applyFromArray($cellBorderStyle);
            $sheet->getStyle('D' . $row)
            ->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
            $sheet->mergeCells('F' . $row . ':G' . $row);
            $sheet->setCellValue('F' . $row, $fecha); 
            $sheet->getStyle('F' . $row . ':G' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY);
            $sheet->getStyle('F' . $row . ':G' . $row)->applyFromArray($cellBorderStyle);
            
            $sheet->mergeCells('H' . $row . ':J' . $row);
            $sheet->setCellValue('H' . $row, $obraGasto->descripcion);
            $sheet->getStyle('H' . $row . ':J' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY);
            $sheet->getStyle('H' . $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, "Gasto.xlsx");
        } catch (\Exception $exception) {
          return null;
        }
    }
}