FacturesSpreadsheet.php

<?php

namespace App\Spreadsheet;

use App\Model\Entity\Facture;
use Cake\Http\Response;
use Cake\Http\Session;
use Cake\View\View;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Html;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;

/**
 * Class FacturesSpreadsheet
 *
 * @package App\Spreadsheet
 */
class FacturesSpreadsheet
{
    /**
     * @var \App\Model\Entity\Facture
     */
    private $facture;
    /**
     * @var \PhpOffice\PhpSpreadsheet\Spreadsheet
     */
    private $spreadsheet;
    /**
     * @var string
     */
    private $username;

    /**
     * FacturesSpreadsheet constructor.
     *
     * @param \App\Model\Entity\Facture $facture
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function __construct(Facture $facture)
    {
        $this->setUsername((new Session())->read('Auth.User.username'));
        $this->setFacture($facture);
        $this->_initializeSpreadsheetFromHtml($this->getFacture());
    }

    /**
     * @param string $username
     * @return void
     */
    private function setUsername(string $username)
    {
        $this->username = $username;
    }

    /**
     * @param \App\Model\Entity\Facture $facture
     */
    private function setFacture(\App\Model\Entity\Facture $facture)
    {
        $this->facture = $facture;
    }

    /**
     * @param \App\Model\Entity\Facture $facture
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    private function _initializeSpreadsheetFromHtml(Facture $facture)
    {
        $vue = (new \Cake\View\View());
        $vue->set(compact('facture'));
        $this->setSpreadsheet((new Html())->loadFromString($vue->render('Factures/xlsx/view_by_hole', false)));
        $this->_initializeExecl($facture);
        $this->_addHeader($facture);

        $styleArray = [
            'borders' => [
                'left' => [
                    'borderStyle' => Border::BORDER_THICK,
                    'color' => ['argb' => 'FFFF0000'],
                ],
                'right' => [
                    'borderStyle' => Border::BORDER_THICK,
                    'color' => ['argb' => 'FFFF0000'],
                ],
                'top' => [
                    'borderStyle' => Border::BORDER_THICK,
                    'color' => ['argb' => 'FFFF0000'],
                ],
                'bottom' => [
                    'borderStyle' => Border::BORDER_THICK,
                    'color' => ['argb' => 'FFFF0000'],
                ],
            ],
        ];
        $this->getSpreadsheet()->getDefaultStyle()
            ->applyFromArray($styleArray);
        $this->getSpreadsheet()->getActiveSheet()
            ->getRowDimension('1')
            ->setRowHeight(200);
    }

    /**
     * @param \PhpOffice\PhpSpreadsheet\Spreadsheet $spreadsheet
     * @return void
     */
    private function setSpreadsheet(Spreadsheet $spreadsheet)
    {
        $this->spreadsheet = $spreadsheet;
    }

    /**
     * @param \App\Model\Entity\Facture $facture
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    private function _initializeExecl(Facture $facture)
    {
        $this->getSpreadsheet()->getActiveSheet()->setAutoFilter(
            $this->getSpreadsheet()->getActiveSheet()
                ->calculateWorksheetDimension()
        );
        $this->getSpreadsheet()->getProperties()
            ->setCreator($this->getUsername())
            ->setLastModifiedBy($this->getUsername())
            ->setTitle($facture->title);
    }

    /**
     * @return \PhpOffice\PhpSpreadsheet\Spreadsheet
     */
    private function getSpreadsheet(): \PhpOffice\PhpSpreadsheet\Spreadsheet
    {
        return $this->spreadsheet;
    }

    /**
     * @return string
     */
    public function getUsername(): string
    {
        return $this->username;
    }

    /**
     * @param \App\Model\Entity\Facture $facture
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    private function _addHeader(Facture $facture)
    {
        $activeSheet = $this->getSpreadsheet()->setActiveSheetIndex(0);
        $activeSheet
            ->insertNewRowBefore(1, 2)
            ->setMergeCells(['A1:I1'])
            ->getStyle('A1:I1')
            ->getAlignment()
            ->setVertical(Alignment::VERTICAL_TOP)
            ->setWrapText(true);
        $activeSheet
            ->setCellValue('A1', "Invoice date: {$facture->date_fin}")
            ->setMergeCells(['A2:I2'])
            ->getStyle('A2:I2')
            ->getAlignment()
            ->setVertical(Alignment::VERTICAL_TOP)
            ->setWrapText(true);
        $activeSheet
            ->setCellValue('A2', $this->_headerExcel($facture));
    }

    /**
     * @param Facture $facture facture
     * @return null|string
     */
    private function _headerExcel($facture)
    {
        $vue = new View();
        $vue->set(compact('facture'));

        return (new \PhpOffice\PhpSpreadsheet\Helper\Html)->toRichTextObject(trim($vue->render('Factures/xlsx/header', false)));
    }

    /**
     * @return \App\Model\Entity\Facture
     */
    private function getFacture(): \App\Model\Entity\Facture
    {
        return $this->facture;
    }

    /**
     * @param bool $debug
     * @return \Cake\Http\Response
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function render(bool $debug = false)
    {
        if ($debug) {
            die(
            IOFactory::createWriter($this->getSpreadsheet(), 'Html')
                ->save('php://output')
            );
        } else {
            if (file_exists(WWW_ROOT . 'write.xlsx')) {
                unlink(WWW_ROOT . 'write.xlsx');
            }
            IOFactory::createWriter($this->getSpreadsheet(), 'Xlsx')
                ->save(WWW_ROOT . 'write.xlsx');

            return (new Response())->withFile(WWW_ROOT . 'write.xlsx');
        }
    }
}