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 excel\web\Controller;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use yii\web\Response;

class ObraHerramientaController extends Controller {
    public static $estiloCeldaIzquierda12 = [
        'font' => [
            'bold' => true,
            'size' => 12,
            'color' => ['argb' => 'FF000000'],
        ],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_LEFT,
            'vertical' => Alignment::VERTICAL_CENTER,
        ],
    ];

    public static $celdaVertical = [
        'font' => [
            'bold' => true,
            'size' => 14,
            'color' => ['argb' => 'FFFFFFFF'],
        ],
        'alignment' => [
            // 'textRotation' => 90,
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical' => Alignment::VERTICAL_CENTER
        ],
        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'startColor' => ['argb' => "FF625FF5"]
        ],
    ];

    public static $celdaHorizontal = [
        'font' => [
            'bold' => true,
            'size' => 14,
            'color' => ['argb' => 'FF000000'],
        ],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical' => Alignment::VERTICAL_CENTER
        ],
        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'startColor' => ['argb' => "FFE7E7E7"]
        ],
    ];

    public static $celdaFuente12 = [
        'font' => [
            'bold' => true,
            'size' => 10,
            'color' => ['argb' => 'FF000000'],
        ],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_LEFT,
            'vertical' => Alignment::VERTICAL_CENTER
        ],
    ];

    public static $celdaVerticalF12 = [
        'font' => [
            'bold' => true,
            'size' => 10,
            'color' => ['argb' => 'FF000000'],
        ],
        'alignment' => [
            'textRotation' => 90,
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical' => Alignment::VERTICAL_CENTER
        ],
        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'startColor' => ['argb' => "FFE7E7E7"]
        ],
    ];
    public function encabezadoHoja($logo)
    {

        $celdas = [
            "A1" => [
                "valor" => $logo,
                "combinar" => "G4",
            ],
        ];

        foreach ($celdas as $coordenada => $valor) {
            $this->agregarCelda($coordenada, $valor);
        }

        $this->renglonActual = 6;

        return $this;
    }

    public function actionExcelObraHerramienta($idObra) {
        $json = intval($this->req->get("json", ""));

        $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]);
        if ($json) {
            \Yii::$app->getResponse()->format = Response::FORMAT_JSON;
            return $json;
        }
        $BASEPATH = \Yii::getAlias('@app') . "/web";
        $logo = $BASEPATH . '/img/logos/edesarrollos-unicolor-azul.png';
        $this->encabezadoHoja($this->logo($logo, 'A1'));
        $fechaInicio = (new DateTime($obra->fechaInicio))->format('d-m-Y');
        $fechaFin = (new DateTime($obra->fechaFinal))->format('d-m-Y');
        $estiloEncabezado = array_merge(self::$celdaVertical, self::$bordes);
        $encabezadoTabla = [
            "A6" => [
                "valor" => "Herramientas relacionadas a la obra: $obra->nombre",
                "combinar" => "D6",
                "estilo" => $estiloEncabezado
            ],
            "A7" => [
                "valor" => "Fecha Inicial: $fechaInicio ",
                "combinar" => "B7",
                "estilo" => $estiloEncabezado
            ],
            "C7" => [
                "valor" => "Fecha Final: $fechaFin",
                "combinar" => "D7",
                "estilo" => $estiloEncabezado
            ]
        ];
        $this->agregarRenglones($encabezadoTabla);
        $encabezadoTabla = [
            "A9" => [
                "valor" => "Nombre",
                "estilo" => $estiloEncabezado,
                "combinar" => "B9",

            ],
            "C9" => [
                "valor" => "Cantidad",
                "estilo" => $estiloEncabezado,
                "combinar" => "D9",

            ],

        ];
        $r = 10;
        $this->agregarRenglones($encabezadoTabla);
        $estiloCeldaIzquierda = array_merge(self::$bordes, self::$celdaFuente12);
        $estiloCondicional = $this->estiloCondicional();
        
        foreach ($query->each() as $obraHerramienta) {
            $herramienta = $obraHerramienta->herramienta; 
            $renglones = [
                "A{$r}" => [
                    "valor" => $herramienta->nombre,
                    "estilo" => $estiloCeldaIzquierda,
                    "combinar" => "B{$r}",

                ],
                "C{$r}" => [
                    "valor" => $obraHerramienta->cantidad,
                    "estilo" => $estiloCeldaIzquierda,
                    "combinar" => "D{$r}",
                    "formato" => "#,##0",
                    "estiloCondicional" => $estiloCondicional

                ],
            ];
            $this->agregarRenglones($renglones);
            $r += 1;
        }
        $anchoColumnas = [
            "A" => ["ancho" => 20],
            "B" => ["ancho" => 20],
            "C" => ["ancho" => 20],
            "D" => ["ancho" => 20],
        ];
        $this->anchoColumnas($anchoColumnas);
        $documento = $this->obtenerHojaDeCalculo();
        $this->crear($documento,  "Herramienta_" . date("YmdHis"), self::TIPO_EXCEL);
        \Yii::$app->end();
    }


  
}