servicio-autos-api/modules/excel/web/Controller.php

<?php

namespace excel\web;

use common\rest\JsonController;
use yii\web\Controller as WebController;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Conditional;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use yii\filters\auth\CompositeAuth;
use yii\filters\auth\QueryParamAuth;
use yii\web\Response;
use Yii;

class Controller extends WebController {

  public $app = null;
  public $req = null;
  public $res = null;

  const MIMETYPE_EXCEL = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

  const TIPO_EXCEL = "excel";
  const TIPO_PDF = "pdf";

  const COLOR_NEGRO = "FF000000";
  const COLOR_AZUL = "FF1A95E8";
  const COLOR_GRIS = "FF959595";

  public $renglonActual = 1;
  public $renglonPrevio = 0;
  protected $spreadsheet;
  protected $activeSheet;

  public static $estiloCeldaNormal = [
    'alignment' => [
      'horizontal' => Alignment::HORIZONTAL_LEFT,
    ],
  ];

  public static $estiloCeldaCentrada = [
    'font' => [
      'bold' => true,
      'size' => 16,
      'color' => ['argb' => 'FF000000'],
    ],
    'alignment' => [
      'horizontal' => Alignment::HORIZONTAL_CENTER,
      'vertical' => Alignment::VERTICAL_CENTER,
    ],
  ];

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

  public static $bordes = [
    'borders' => [
      'left' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FF000000'],
      ],
      'right' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FF000000'],
      ],
      'top' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FF000000'],
      ],
      'bottom' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FF000000'],
      ]
    ],
  ];

  public static $bordesPrimario = [
    'borders' => [
      'left' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FF750d44'],
      ],
      'right' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FF750d44'],
      ],
      'top' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FF750d44'],
      ],
      'bottom' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FF750d44'],
      ]
    ],
  ];

  public static $bordesWhite = [
    'borders' => [
      'left' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FFFFFFFF'],
      ],
      'right' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FFFFFFFF'],
      ],
      'top' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FFFFFFFF'],
      ],
      'bottom' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FFFFFFFF'],
      ]
    ],
  ];

  public static $bordesGray = [
    'borders' => [
      'left' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FFE2E2E2'],
      ],
      'right' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FFE2E2E2'],
      ],
      'top' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FFE2E2E2'],
      ],
      'bottom' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FFE2E2E2'],
      ]
    ],
  ];

  public static $estiloEncabezado = [
    'font' => [
      'bold' => true,
      'size' => 9
    ],
    'alignment' => [
      'horizontal' => Alignment::HORIZONTAL_CENTER,
      'vertical' => Alignment::VERTICAL_CENTER,
    ],
    'fill' => [
      'fillType' => Fill::FILL_SOLID,
      'startColor' => ['argb' => self::COLOR_GRIS]
    ],
    'font' => [
      'bold'  => true,
      'color' => ['argb' => "AAFFFFFF"],
    ],
    'borders' => [
      'left' => [
        'borderStyle' => Border::BORDER_THICK,
        'color' => ['argb' => 'FFFFFFFF'],
      ],
      'right' => [
        'borderStyle' => Border::BORDER_THICK,
        'color' => ['argb' => 'FFFFFFFF'],
      ]
    ],
  ];

  public static $estiloTitulo = [
    'font' => [
      'bold' => true,
      'size' => 13,
      'color' => ['argb' => self::COLOR_AZUL],
    ],
    'alignment' => [
      'horizontal' => Alignment::HORIZONTAL_CENTER,
      'vertical' => Alignment::VERTICAL_CENTER,
    ],
  ];

  public static $CELDA_VERDE = [
    'alignment' => [
      'horizontal' => Alignment::HORIZONTAL_CENTER,
    ],
    'fill' => [
      'fillType' => Fill::FILL_SOLID,
      'startColor' => ['argb' => 'FFD8E4BD']
    ],
    'borders' => [
      'left' => [
        'borderStyle' => Border::BORDER_THICK,
        'color' => ['argb' => 'FFFFFFFF'],
      ],
      'right' => [
        'borderStyle' => Border::BORDER_THICK,
        'color' => ['argb' => 'FFFFFFFF'],
      ],
      'bottom' => [
        'borderStyle' => Border::BORDER_DASHED,
      ],
    ],
  ];

  public static $CELDA_AMARILLA = [
    'alignment' => [
      'horizontal' => Alignment::HORIZONTAL_CENTER,
    ],
    'fill' => [
      'fillType' => Fill::FILL_SOLID,
      'startColor' => ['argb' => 'FFFFE597']
    ],
    'borders' => [
      'left' => [
        'borderStyle' => Border::BORDER_THICK,
        'color' => ['argb' => 'FFFFFFFF'],
      ],
      'right' => [
        'borderStyle' => Border::BORDER_THICK,
        'color' => ['argb' => 'FFFFFFFF'],
      ],
      'bottom' => [
        'borderStyle' => Border::BORDER_DASHED,
      ],
    ],
  ];

  public static $CELDA_ROJA = [
    'alignment' => [
      'horizontal' => Alignment::HORIZONTAL_CENTER,
    ],
    'fill' => [
      'fillType' => Fill::FILL_SOLID,
      'startColor' => ['argb' => 'FFE6B8B7']
    ],
    'borders' => [
      'left' => [
        'borderStyle' => Border::BORDER_THICK,
        'color' => ['argb' => 'FFFFFFFF'],
      ],
      'right' => [
        'borderStyle' => Border::BORDER_THICK,
        'color' => ['argb' => 'FFFFFFFF'],
      ],
      'bottom' => [
        'borderStyle' => Border::BORDER_DASHED,
      ],
    ],
  ];

  public static $CELDA_PRIMARIA = [
    'font' => [
      'bold' => true,
      'size' => 16,
      'color' => ['argb' => 'FFFFFFFF'],
    ],
    'alignment' => [
      'horizontal' => Alignment::HORIZONTAL_CENTER,
    ],
    'fill' => [
      'fillType' => Fill::FILL_SOLID,
      'startColor' => ['argb' => 'FF660034']
    ],
  ];

  public static $CELDA_SECUNDARIA = [
    'font' => [
      'bold' => true,
      'size' => 16,
      'color' => ['argb' => 'FFFFFFFF'],
    ],
    'alignment' => [
      'horizontal' => Alignment::HORIZONTAL_CENTER,
    ],
    'fill' => [
      'fillType' => Fill::FILL_SOLID,
      'startColor' => ['argb' => 'FF990033']
    ],
  ];

  public static $TITULO_01 = [
    'font' => [
      'bold' => true,
      'size' => 14,
      'color' => ['argb' => 'FFFFFFFF'],
    ],
  ];

  public static $TITULO_02 = [
    'font' => [
      'bold' => true,
      'size' => 14,
      'color' => ['argb' => 'FF000000'],
    ],
  ];

  public static $DESCR_01 = [
    'alignment' => [
      'horizontal' => Alignment::HORIZONTAL_LEFT,
    ],
    'font' => [
      'bold' => false,
      'size' => 12,
      'color' => ['argb' => 'FF000000'],
    ],
  ];

  public static $CELDA_GRIS_01 = [
    'font' => [
      'bold' => true,
      'size' => 14,
      'color' => ['argb' => 'FFFFFFFF'],
    ],
    'fill' => [
      'fillType' => Fill::FILL_SOLID,
      'startColor' => ['argb' => 'FF808080']
    ],
  ];

  public static $CELDA_GRIS_02 = [
    'fill' => [
      'fillType' => Fill::FILL_SOLID,
      'startColor' => ['argb' => 'FFA6A6A6']
    ],
  ];

  public static $CELDA_GRIS_03 = [
    'fill' => [
      'fillType' => Fill::FILL_SOLID,
      'startColor' => ['argb' => 'FFD9D9D9']
    ],
  ];

  /* public function behaviors() {
    $behavior = parent::behaviors();
    $behavior["authenticator"] = [
      "class" => CompositeAuth::className(),
      "authMethods" => [
        QueryParamAuth::className(),
      ]
    ];
    return $behavior;
  } */

  public function beforeAction($action) {

    parent::beforeAction($action);
    $this->app = Yii::$app;
    $this->req = $this->app->getRequest();
    $this->res = $this->app->getResponse();

    $this->spreadsheet = new Spreadsheet();
    $this->activeSheet = $this->spreadsheet->getActiveSheet();
    return true;
  }

  public function obtenerHojaDeCalculo() {
    return $this->spreadsheet;
  }

  public function nuevaHoja($indice = null, $titulo = null) {
    $this->activeSheet = $this->spreadsheet->createSheet($indice);
    if ($titulo !== null) {
      $this->activeSheet->setTitle($titulo);
    }
  }

  public function logo($logo, $coordenada = "I1", $nombre = 'Logo', $descripcion = 'Logo', $x = 27, $y = 8, $heigth = 75) {
    $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
    $drawing->setName($nombre);
    $drawing->setDescription($descripcion);
    $drawing->setPath($logo);
    $drawing->setCoordinates($coordenada);
    $drawing->setOffsetX($x);
    $drawing->setOffsetY($y);
    $drawing->setHeight($heigth);
    $drawing->setWorksheet($this->activeSheet);
  }

  public function titulo($inicio = 1, $titulo, $columnaInicio = "A", $columnaFinal = "E") {
    if ($inicio < $this->renglonActual) {
      $inicio = $this->renglonActual + 1;
    }
    $renglones = [
      "{$columnaInicio}{$inicio}" => [
        "valor" => "{$titulo}",
        "combinar" => "{$columnaFinal}{$inicio}",
        "estilo" => [
          'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
          ],
          'font' => [
            'bold'  => true,
            'color' => ['argb' => self::COLOR_AZUL],
            // 'size'  => 15,
            // 'name'  => 'Verdana'
          ]
        ]
      ]
    ];

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

    $this->renglonActual = $inicio + 1;
    return $this;
  }

  public function agregarRenglones($renglones) {
    foreach ($renglones as $coordenada => $valor) {
      $this->agregarCelda($coordenada, $valor);
    }
    return $this;
  }

  public function agregarEncabezado($titulos) {
    if (count($titulos) <= 0) {
      return $this;
    }

    foreach ($titulos as $k => $v) {
      $this->agregarCelda($k, [
        "valor" => "$v",
        "estilo" => self::$estiloEncabezado
      ]);
    }

    return $this;
  }

  public function agregarCelda($coordenada, $valor) {
    if (isset($valor["valor"])) {
      $this->activeSheet
        ->setCellValue($coordenada, $valor["valor"]);
      if (isset($valor["combinar"])) {
        $coordenada = "{$coordenada}:{$valor["combinar"]}";
        $this->activeSheet
          ->mergeCells($coordenada);
      }
    }
    if (isset($valor["estilo"])) {
      $this->activeSheet
        ->getStyle($coordenada)
        ->applyFromArray($valor["estilo"]);
    }
    if (isset($valor["formato"])) {
      $this->activeSheet
        ->getStyle($coordenada)
        ->getNumberFormat()
        ->setFormatCode($valor["formato"]);
    }
    if (isset($valor["wrap"])) {
      $this->activeSheet
        ->getStyle($coordenada)
        ->getAlignment()
        ->setWrapText($valor["wrap"] || false);
    }
  }

  # Después de agregar toda la información elegir el ancho de las columnas
  public function anchoColumnas($columnas) {
    foreach ($columnas as $columna => $c) {
      if (isset($c["auto"]) && $c["auto"]) {
        $this->activeSheet
          ->getColumnDimension($columna)
          ->setAutoSize(true);
      } elseif (isset($c["ancho"]) && $c["ancho"] > 0) {
        $this->activeSheet->getColumnDimension($columna)->setWidth($c["ancho"]);
      }
    }
    return $this;
  }

  # Generar el archivo excel
  public function generar($filename = null) {
    $writer = IOFactory::createWriter($this->spreadsheet, 'Xlsx');
    try {
      ob_start();
      $writer->save("php://output");
      $documento = ob_get_contents();
      ob_clean();
      return $documento;
    } catch (\Exception $exception) {
      return null;
    }
  }

  # Descarga el archivo en formato excel o pdf
  public function crear($hojaCalculo, $archivo = null, $tipo = self::TIPO_EXCEL) {
    if (!$tipo) {
      $tipo = \Yii::$app->getRequest()->get("tipo", "excel");
    }
    $tipo_writer = 'Xlsx';
    $extension = '.xlsx';
    if ($tipo === self::TIPO_PDF) {
      $tipo_writer = 'Mpdf';
      $extension = '.pdf';
    }
    $writer = IOFactory::createWriter($hojaCalculo, $tipo_writer);
    $archivo .= $extension;
    try {
      ob_start();
      $writer->save("php://output");
      $content = ob_get_contents();
      ob_clean();
      \Yii::$app->getResponse()->sendContentAsFile($content, $archivo);
    } catch (\Exception $e) {
      echo $e->getMessage();
    }
  }
  public function estiloCondicional() {
    $condicionalRojo = new Conditional();
    $condicionalRojo->setConditionType(Conditional::CONDITION_CELLIS)
      ->setOperatorType(Conditional::OPERATOR_LESSTHAN)
      ->addCondition('0');
    $colorRojo = "FFFEC7CF";
    $condicionalRojo->getStyle()->getFont()->setBold(true)->getColor()->setARGB(Color::COLOR_BLACK);
    $condicionalRojo->getStyle()->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
    $condicionalRojo->getStyle()->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
    $condicionalRojo->getStyle()->getFill()->setFillType(Fill::FILL_SOLID);
    $condicionalRojo->getStyle()->getFill()->getStartColor()->setARGB($colorRojo);
    $condicionalRojo->getStyle()->getFill()->getEndColor()->setARGB($colorRojo);
    $condicionalRojo->getStyle()->getBorders()->getTop()->setBorderStyle(Border::BORDER_THIN)->getColor()->setARGB('FF000000');
    $condicionalRojo->getStyle()->getBorders()->getBottom()->setBorderStyle(Border::BORDER_THIN)->getColor()->setARGB('FF000000');
    $condicionalRojo->getStyle()->getBorders()->getLeft()->setBorderStyle(Border::BORDER_THIN)->getColor()->setARGB('FF000000');
    $condicionalRojo->getStyle()->getBorders()->getRight()->setBorderStyle(Border::BORDER_THIN)->getColor()->setARGB('FF000000');

    $condicionalVerde = new Conditional();
    $condicionalVerde->setConditionType(Conditional::CONDITION_CELLIS)
      ->setOperatorType(Conditional::OPERATOR_GREATERTHAN)
      ->addCondition('0');
    $colorVerde = "FFC6EFCD";
    $condicionalVerde->getStyle()->getFont()->setBold(true)->getColor()->setARGB(Color::COLOR_BLACK);
    $condicionalVerde->getStyle()->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
    $condicionalVerde->getStyle()->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
    $condicionalVerde->getStyle()->getFill()->setFillType(Fill::FILL_SOLID);
    $condicionalVerde->getStyle()->getFill()->getStartColor()->setARGB($colorVerde);
    $condicionalVerde->getStyle()->getFill()->getEndColor()->setARGB($colorVerde);
    $condicionalVerde->getStyle()->getBorders()->getTop()->setBorderStyle(Border::BORDER_THIN)->getColor()->setARGB('FF000000');
    $condicionalVerde->getStyle()->getBorders()->getBottom()->setBorderStyle(Border::BORDER_THIN)->getColor()->setARGB('FF000000');
    $condicionalVerde->getStyle()->getBorders()->getLeft()->setBorderStyle(Border::BORDER_THIN)->getColor()->setARGB('FF000000');
    $condicionalVerde->getStyle()->getBorders()->getRight()->setBorderStyle(Border::BORDER_THIN)->getColor()->setARGB('FF000000');

    return [$condicionalRojo, $condicionalVerde];
  }
}