<?php

namespace Modules\Consultation\Service;

use Entity\Model\AoEntet;
use Entity\Model\AoExcEnte;
use Entity\Model\AoExCenv;
use Entity\Model\Fourniss;
use Helpers\ChampsSupplementaire\ChampSupp;
use Helpers\Outil\Date;
use Helpers\Outil\Sql;
use Helpers\Trad\ITrad;
use Helpers\Trad\Trad;
use PhpOffice\PhpSpreadsheet\Style\Protection;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use Romans\Filter\IntToRoman;
use Entity\Model\AoExcFofo;
use Entity\Model\AoQstR;
use Helpers\Log\LogSysteme;
use Helpers\Phpspreadsheet\Block;
use Helpers\Phpspreadsheet\SpreadsheetHelper;
use Modules\Consultation\Repository\ExportExcel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use Helpers\Session\ParseBase1;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\ReferenceHelper;

class ExportExcelService
{
    
    /**
     * @var \PDO $pdo
     */
    private $pdo;

    private ITrad $trad;

    /**
     *
     * @var string $idAo
     */
    private $idAo;

    /**
     *
     * @var string $codeForm
     */
    private $codeForm;

    /**
     *
     * @var string $versionForm
     */
    private $versionForm;

    /**
     *
     * @var int $rand
     */
    private $rand;

    /**
     *
     * @var string $codeFou
     */
    private $codeFou;

    /**
     *
     * @var string $idForm
     */
    private $idForm;

    /**
     * @var Spreadsheet $spreadsheet
     */
    private $spreadsheet;

    /**
     * @var ExportExcel $exportExcelRepo
     */
    private $exportExcelRepo;

    public function __construct(\PDO $pdo, string $idForm, ITrad $trad)
    {
        $this->pdo = $pdo;

        $aoExCenv = new AoExCenv($this->pdo);
        $infoGenerate = $aoExCenv->find($idForm);

        if (empty($infoGenerate)) {
            LogSysteme::prefixErrorLog(new \Exception('Invalid form #'.$idForm));
        }
        
        $fourniss = new Fourniss($this->pdo);
        $infoFrs = $fourniss->find($infoGenerate['CODE_FOU']);

        $this->idAo = $infoGenerate['NOAO'];

        $this->codeForm = $infoGenerate['FORMULAIR'];
        $this->versionForm = $infoGenerate['VERSI'];
        $this->codeFou = $infoGenerate['CODE_FOU'];
        $this->idForm = $idForm;

        $this->rand = rand(10000000, 99999999);

        $this->trad = $trad->copy($infoFrs['COD_LNG']??'');
    }

    /**
     * @return string
     */
    public function generateExcel()
    {
        $aoExCente = new AoExcEnte($this->pdo);
        $aoExFofo = new AoExcFofo($this->pdo);
        
        $infoForm = $aoExFofo->getForm(
            (new AoEntet($this->pdo))->find($this->idAo),
            $this->codeForm,
            intval($this->versionForm)
        );

        if (!$infoForm) {
            LogSysteme::prefixErrorLog(new \Exception('Invalid form #'.$this->codeForm. ' v'.$this->versionForm));
            return '';
        }
        
        /** On charge le template récuperé et on le place dans TMP pour travailler dessus */
        $explode = explode('|', $infoForm['CHEMFORM']);
        $infoFile = pathinfo($explode[0]);
        $infoFile['extension'] = empty($infoFile['extension']) ? '' : $infoFile['extension'];
        $fileSource = (new ParseBase1())->getRep()->pj.$explode[1].'.'.$infoFile['extension'];

        $pathInfo = pathinfo($fileSource);
        $pathInfo['extension'] = empty($pathInfo['extension']) ? '' : $pathInfo['extension'];

        if (!file_exists($fileSource)) {
            LogSysteme::prefixErrorLog('Missing file "'.$fileSource.'"');
            return '';
        }

        $fileName = 'AO_'.str_pad((string)$this->idAo, 9, '0', STR_PAD_LEFT).$this->codeFou.'_'.$this->rand.'.'.$pathInfo['extension'];
        $newName =  (new ParseBase1())->getRep()->tmp.$fileName;
        
        copy($fileSource, $newName);

        $this->spreadsheet = IOFactory::load($newName);
        $feuilleReponse = $this->spreadsheet->getSheet(0);

        // ecrire version fichier A1 et on bloc la cellule en siasie et on cache toute la ligne
        $feuilleReponse->setCellValue('A1', $this->idForm);
        $feuilleReponse->getStyle('A1')->getProtection()->setLocked('protected');
        $feuilleReponse->getRowDimension(1)->setVisible(false);
        
        // remplire tout les champs d'entête
        $this->exportExcelRepo = new ExportExcel($this->pdo);
        
        $list = $this->exportExcelRepo->getInfoExport($this->codeForm, $this->versionForm);
        $listeChampsEntete = array_filter($list, fn($item) => Sql::toBool($item['ENTET']));
        $listeChampsLigne = array_filter($list, fn($item) => ! Sql::toBool($item['ENTET']));
        $infoEnte = $this->exportExcelRepo->getInfoAo((string)$this->idAo, $this->codeFou);
        $infoLigne = $this->exportExcelRepo->getInfoLigne((string)$this->idAo, $this->codeFou);

        foreach ($listeChampsEntete as $champs) {
            $valueForCell = $this->generateValueForExcel(true, $this->pdo, (string)$this->idAo, $champs, $infoEnte);
            $cell = Coordinate::stringFromColumnIndex($champs['NOCOL']).$champs['NOLIG'];
            $feuilleReponse->setCellValue($cell, $valueForCell);
            $feuilleReponse->getStyle($cell)->getProtection()->setLocked(
                Sql::boolSql($champs['LUE'])
                ? Protection::PROTECTION_UNPROTECTED
                : Protection::PROTECTION_PROTECTED
            );
        }

        $parametreEntete = $aoExCente->find(['CODEFORM' => $this->codeForm, 'VERSFORM' => $this->versionForm]);

        /* ligne titre exemple */
        $titleBlock = [
            'xStart' => (int) $parametreEntete['NLIG_ST'],
            'yStart' => (int) $parametreEntete['NCOL_ST'],
            'xEnd' => (int) $parametreEntete['NLIG_ST'],
            'yEnd' => (int) $parametreEntete['DERCOLAO'],
        ];

        $startNextBloc = $parametreEntete['LIGUNAO'];
        $countLigneBloc = $parametreEntete['NBLIGAO'];

        /* Ligne d'exemple 1 */
        $ligneBlock = [
            'xStart' => (int) $parametreEntete['LIGUNAO'],
            'yStart' => (int) $parametreEntete['COLUNAO'],
            'xEnd' => $parametreEntete['LIGUNAO'] + $parametreEntete['NBLIGAO'] - 1,
            'yEnd' => (int) $parametreEntete['DERCOLAO'],
        ];

        $colInit = $parametreEntete['COLUNAO'];
        $ligInit = $parametreEntete['LIGUNAO'];

        /** On remplis et on duplique la ligne 1 pour chaque ligne trouvé */
        foreach ($infoLigne as $index => $ligne) {
            if ($index != 0) {
                $startNextBloc += $countLigneBloc;

                /* Si sous titre, alors on duplique le modele de sous-titre  */
                if (sql::boolSql($ligne['ST_APLIG'])) {
                    $this->copyRows(
                        $feuilleReponse,
                        $titleBlock['xStart'],
                        $titleBlock['yStart'],
                        $titleBlock['xEnd'],
                        $titleBlock['yEnd'],
                        $startNextBloc,
                        (int) $parametreEntete['COLUNAO']
                    );

                    $startNextBloc += 1;
                }

                $this->copyRows(
                    $feuilleReponse,
                    $ligneBlock['xStart'],
                    $ligneBlock['yStart'],
                    $ligneBlock['xEnd'],
                    $ligneBlock['yEnd'],
                    $startNextBloc,
                    (int) $parametreEntete['COLUNAO']
                );

                $ligInit = $startNextBloc;
            }

            foreach ($listeChampsLigne as $champs) {
                $col = $colInit+$champs['NOCOL']-1;
                $lig = $ligInit+$champs['NOLIG']-1;

                $valueForCell = $this->generateValueForExcel(false, $this->pdo, (string)$this->idAo, $champs, $ligne);
                $cell = Coordinate::stringFromColumnIndex($col).$lig;
                
                $feuilleReponse->setCellValue($cell, $valueForCell);
                $feuilleReponse->getStyle($cell)->getProtection()->setLocked(
                    Sql::boolSql($champs['LUE'])
                    ? Protection::PROTECTION_UNPROTECTED
                    : Protection::PROTECTION_PROTECTED
                );
            }
        }

        // Si AO variante autorisé, alors rename feuille par Conforme et dupliquer la feuille en 2 puis rename en Variante
        if (Sql::boolSql($infoEnte['VARIANTE']) == true) {
            $feuilleReponse->setTitle($this->trad->t('Conforme'));
            $sheet = $this->spreadsheet->getSheet(0)->copy();
            $sheet->setTitle($this->trad->t('Variante'));

            $this->spreadsheet->addSheet($sheet, 1);
        }

        // on protège la feuille conforme
        $feuilleReponse->getProtection()->setSheet(true);
        $feuilleReponse->getProtection()->setSort(true);
        $feuilleReponse->getProtection()->setInsertRows(true);
        $feuilleReponse->getProtection()->setFormatCells(true);

        // mot de passe si besoin
        // $feuilleReponse->getProtection()->setPassword('password');

        /***** PARTIE QUESTIONNAIRE */
        $infoQuest = $this->exportExcelRepo->getQuestionnaire((string)$this->idAo);

        if ($infoQuest) {
            $this->generateQuestionnaire();
        }

        $writer = IOFactory::createWriter($this->spreadsheet, 'Xlsx');

        $writer->save($newName);

        return $fileName;
    }

    private function generateQuestionnaire() : void
    {
        $aoQstR = new AoQstR($this->pdo);

        $libDico = $this->trad->t('Dictionnaire');
        $dictionnaire = $this->spreadsheet->createSheet(3);
        $dictionnaire->setTitle($libDico);
        $dictionnaire->setSheetState('hidden');
        $dictionnaire->getProtection()
            ->setSheet(true)
            ->setSort(true)
            ->setInsertRows(true)
            ->setFormatCells(true)
        ;

        $this->setDico($dictionnaire, 1, $this->trad->t('Oui'), 'L|'.$this->trad->t('Oui'), 'O');
        $this->setDico($dictionnaire, 2, $this->trad->t('Non'), 'L|'.$this->trad->t('Non'), 'N');
        $posDico = 3;
        
        $templateQuest = IOFactory::load(
            (new ParseBase1())->getRep()->etc.'ModeleQuestionnaireAO.xlsx'
        );
        $sheetQuest = $templateQuest->getSheet(0);

        // TODO : use $sheetQuest as a template and create a new working sheet instead of modifying $sheetQuest
        // this should allow the use of copyRows everywhere
        
        $sheetQuest->setTitle($this->trad->t('Questionnaire'));
        $sheetQuest->setShowGridlines(false);

        $listeTheme = $this->exportExcelRepo->getQuestionnaire((string)$this->idAo);

        $posRow = 7;
        $arraySaveTheme = [];
        $saveIdTheme = [];
        foreach ($listeTheme as $theme) {
            $arraySaveTheme[$theme['IdPere']] = isset($arraySaveTheme[$theme['IdPere']])
                ? $arraySaveTheme[$theme['IdPere']] + 1
                : 1;
            $saveIdTheme[$theme['IdTheme']] = $this->getRefTheme($theme['Niveau']+1, $arraySaveTheme[$theme['IdPere']]);

            if ($posRow != 7) {
                // TODO: this break the file, why ?
                // $this->copyRows(
                //     $sheetQuest,
                //     2, 7, // B7
                //     13, 7, // M7

                //     2, $posRow
                // );

                $style = $sheetQuest->getStyle('B7');
                $sheetQuest->duplicateStyle($style, 'B'.$posRow);

                $style = $sheetQuest->getStyle('C7');
                $sheetQuest->duplicateStyle($style, 'C'.$posRow.':G'.$posRow);
                $sheetQuest->mergeCells('C'.$posRow.':G'.$posRow);

                $style = $sheetQuest->getStyle('H7');
                $sheetQuest->duplicateStyle($style, 'H'.$posRow.':M'.$posRow);
                $sheetQuest->mergeCells('H'.$posRow.':M'.$posRow);
            }

            $explode = explode(',', $theme['ConcatId']);
            $a = [];
            foreach ($explode as $val) {
                $a[] = $saveIdTheme[$val];
            }
            $idTheme = implode('.', $a);




            $sheetQuest->setCellValue('A'.$posRow, 'T|'.$theme['IdTheme']);
            $sheetQuest->setCellValue('B'.$posRow, $idTheme);
            $sheetQuest->setCellValue('C'.$posRow, $theme['Libelle']);
            $sheetQuest->setCellValue('H'.$posRow, $theme['CONTMEMO']);
            $sheetQuest->getStyle('H'.$posRow)->getFont()->setItalic(true);

            $posRow++;

            $listeQuestion = $this->exportExcelRepo->getListeQuestion((string)$this->idAo, $theme['IdTheme']);
            
            $countQuest = 1;
            foreach ($listeQuestion as $quest) {
                $sheetQuest->setCellValue('A'.$posRow, 'Q|'.$theme['IdTheme'].'|'.$quest['ID_QUEST']);

                $sheetQuest->setCellValue('B'.$posRow, $countQuest);
                
                $sheetQuest->mergeCells('C'.$posRow.':G'.$posRow);
                $sheetQuest->mergeCells('H'.$posRow.':M'.$posRow);

                $sheetQuest->setCellValue('C'.$posRow, $quest['LIB_QUEST']);

                $cellInput = $sheetQuest->getCell('H'.$posRow);
                $cellInput->getStyle()
                    ->getProtection()
                    ->setLocked(Protection::PROTECTION_UNPROTECTED);
                $validation = $cellInput->getDataValidation();
                
                if ($quest['TYPE_REP'] == 'T') {
                    $listeReponse = $aoQstR->findAll([
                        'NO_AO' => $this->idAo,
                        'ID_THEME' => $theme['IdTheme'],
                        'ID_QUEST' => $quest['ID_QUEST']
                    ], [], ['tri_ID_REP' => 'asc']);

                    $posDicoStart = $posDico;
                    foreach ($listeReponse as $rep) {
                        $this->setDico($dictionnaire, $posDico, $rep['TXT_REP'], $rep['ID_REP'].'|'.$rep['TXT_REP'], $rep['ID_REP']);
                        /** @phpstan-ignore-next-line */
                        $posDicoEnd = $posDico;
                        $posDico++;
                    }

                    $validation->setType(DataValidation::TYPE_LIST);
                    $validation->setErrorStyle(DataValidation::STYLE_STOP);
                    $validation->setAllowBlank(false);
                    $validation->setShowInputMessage(true);
                    $validation->setShowErrorMessage(true);
                    $validation->setShowDropDown(true);
                    $validation->setErrorTitle($this->trad->t('Erreur'));
                    $validation->setError($this->trad->t('La valeur saisie ne fait pas partie des valeurs possibles.'));
                    /** @phpstan-ignore-next-line */
                    $validation->setFormula1('\''.$libDico.'\'!$A$'.$posDicoStart.':$A$'.$posDicoEnd.'');
                }

                if ($quest['TYPE_REP'] == 'L') {
                    $validation->setType(DataValidation::TYPE_LIST);
                    $validation->setErrorStyle(DataValidation::STYLE_STOP);
                    $validation->setAllowBlank(false);
                    $validation->setShowInputMessage(true);
                    $validation->setShowErrorMessage(true);
                    $validation->setShowDropDown(true);
                    $validation->setErrorTitle($this->trad->t('Erreur'));
                    $validation->setError($this->trad->t('La valeur saisie ne fait pas partie des valeurs possibles.'));
                    $validation->setFormula1('\''.$libDico.'\'!$A$1:$A$2');
                }

                if ($quest['TYPE_REP'] == 'N'/* Numérique */) {
                    $validation->setType(DataValidation::TYPE_DECIMAL);
                    $validation->setErrorStyle(DataValidation::STYLE_STOP);
                    $validation->setAllowBlank(true);
                    $validation->setShowInputMessage(true);
                    $validation->setShowErrorMessage(true);
                    $validation->setErrorTitle($this->trad->t('Erreur'));
                    $validation->setError(trad::strBuild($this->trad->t('La valeur doit être un décimal compris entre @1@ et @2@.'), $quest['NUM_MIN'], $quest['NUM_MAX']));
                    $validation->setFormula1($quest['NUM_MIN']);
                    $validation->setFormula2($quest['NUM_MAX']);

                    $cellInput->getStyle()->getNumberFormat()->setFormatCode('0.00000');
                }
                
                $posRow++;

                if ($quest['CONTMEMO']) {
                    $sheetQuest->mergeCells('C'.$posRow.':G'.$posRow);
                    $sheetQuest->mergeCells('H'.$posRow.':M'.$posRow);
                    $sheetQuest->setCellValue('C'.$posRow, $quest['CONTMEMO']);
                    $sheetQuest->getStyle('C'.$posRow)->getFont()->setItalic(true);
                    
                    $posRow++;
                }

                $countQuest++;
            }
        }
        $sheetQuest->getColumnDimension('A')->setVisible(false);

        $sheetQuest->getProtection()->setSheet(true);
        $sheetQuest->getProtection()->setSort(true);
        $sheetQuest->getProtection()->setInsertRows(true);
        $sheetQuest->getProtection()->setFormatCells(true);

        $this->spreadsheet->addExternalSheet($sheetQuest, 2);
    }

    /**
     * @param array<mixed> $champs
     * @param array<mixed> $info
     * @return mixed
     */
    private function generateValueForExcel(bool $entete, \PDO $pdo, string $idAO, $champs, $info)
    {
        if (Sql::boolSql($champs['CSPECIF']) == true) {
            $numFix = (int) substr($champs['CODEINTZ'], 3);

            if ($entete) {
                $idCS = ChampSupp::autoCompletCodeEnr($idAO, '', 'AO_ENTET');
                return ChampSupp::getValueCS('AO_ENTET', $idCS, (string)$numFix, $pdo);
            }
            
            $idLigne = $champs['POSTE'];
            $idCS = ChampSupp::autoCompletCodeEnr($idAO, $idLigne, 'AO_LIGNE');
            return ChampSupp::getValueCS('AO_LIGNE', $idCS, (string)$numFix, $pdo);
        }
        $valueForCell = $info[$champs['CODEINTZ']]??null;
        if ($champs['CODEINTZ'] == 'DEVOBLIG') {
            $valueForCell = sql::toBool($valueForCell)? $this->trad->t('Oui'):$this->trad->t('Non');
        } elseif (!$entete && $champs['CODEINTZ'] == 'DELAILIVR') {
            switch ($info['TYPEDEL']) {
                case 'D':
                    $valueForCell = Date::dateConverte($valueForCell);
                    break;
                case 'J':
                    $valueForCell = Trad::strBuild(
                        $this->trad->t('@1@ jour(s)'),
                        intval($valueForCell)
                    );
                    break;
            }
        } elseif (in_array($champs['CODEINTZ'], ['DATLIMREP', 'DATLIMVAL','DATREP','DELAILIVR']) && $valueForCell) {
            $valueForCell = Date::dateConverte($valueForCell);
        }

        return $valueForCell;
    }

    private function copyRows(
        Worksheet $sheet,
        int $srcRowStart,
        int $srcColumnStart,
        int $srcRowEnd,
        int $srcColumnEnd,
        int $destRowStart,
        int $destColumnStart,
        ?Worksheet $destSheet = null
    ): void {
        if (!isset($destSheet)) {
            $destSheet = $sheet;
        }

        
        

        $rowCount = 0;
        for ($row = $srcRowStart; $row <= $srcRowEnd; $row++) {
            $colCount = 0;
            for ($col = $srcColumnStart; $col <= $srcColumnEnd; $col++) {
                $cell = $sheet->getCell(Coordinate::stringFromColumnIndex($col).$row);
                /** @var \stdClass */
                $cell = SpreadsheetHelper::normalizeCell($cell);
                $dstCellCoord = Coordinate::stringFromColumnIndex($destColumnStart + $colCount)
                    . (string)($destRowStart + $rowCount);
                
                // Insert this cell at its new location
                $helper = ReferenceHelper::getInstance();
                $destCell = $destSheet->getCell($dstCellCoord);
                if ($cell->type == DataType::TYPE_FORMULA) {
                    // Formula should be adjusted
                    $destCell->setValue(
                        $helper->updateFormulaReferences(
                            $cell->value,
                            $cell->coordinate,
                            Coordinate::columnIndexFromString($destCell->getColumn())
                                - $cell->col,
                            $destCell->getRow() - $cell->row,
                            $destSheet->getTitle()
                        )
                    );
                } else {
                    // Formula should not be adjusted
                    $destCell->setValue($cell->value);
                }
                
                $destSheet->duplicateStyle(
                    $sheet->getStyle(Coordinate::stringFromColumnIndex($col).$row),
                    $dstCellCoord
                );
                
                // Set width of column, but only once per column
                if ($rowCount === 0) {
                    $w = $sheet->getColumnDimensionByColumn($col)->getWidth();
                    $destSheet->getColumnDimensionByColumn($destColumnStart + $colCount)
                        ->setAutoSize(false)
                        ->setWidth($w);
                }

                $colCount++;
            }

            $h = $sheet->getRowDimension($row)->getRowHeight();
            $destSheet->getRowDimension($destRowStart + $rowCount)->setRowHeight($h);

            $rowCount++;
        }


        // (new Block(
        //     $sheet,
        //     $srcRowStart,
        //     $srcColumnStart,
        //     $srcRowEnd,
        //     $srcColumnEnd,
        // ))->walkCell(function(Cell $cell) use($destSheet) {
        //     $mergeRange = $cell->getMergeRange();
        //     if (! $mergeRange) {
        //         return;
        //     }
        //     // TODO : move merge range $mergeRange
        //     $destSheet->mergeCells($mergeRange);
        // });

        foreach ($sheet->getMergeCells() as $mergeCell) {
            $mc = explode(":", $mergeCell);
            $mergeColSrcStart = Coordinate::columnIndexFromString(preg_replace("/[0-9]*/", "", $mc[0]));
            $mergeColSrcEnd = Coordinate::columnIndexFromString(preg_replace("/[0-9]*/", "", $mc[1]));
            $mergeRowSrcStart = ((int)preg_replace("/[A-Z]*/", "", $mc[0]));
            $mergeRowSrcEnd = ((int)preg_replace("/[A-Z]*/", "", $mc[1]));

            $relativeColStart = $mergeColSrcStart - $srcColumnStart;
            $relativeColEnd = $mergeColSrcEnd - $srcColumnStart;
            $relativeRowStart = $mergeRowSrcStart - $srcRowStart;
            $relativeRowEnd = $mergeRowSrcEnd - $srcRowStart;

            if (0 <= $mergeRowSrcStart && $mergeRowSrcStart >= $srcRowStart && $mergeRowSrcEnd <= $srcRowEnd) {
                $targetColStart = Coordinate::stringFromColumnIndex($destColumnStart + $relativeColStart);
                $targetColEnd = Coordinate::stringFromColumnIndex($destColumnStart + $relativeColEnd);
                $targetRowStart = $destRowStart + $relativeRowStart;
                $targetRowEnd = $destRowStart + $relativeRowEnd;

                $merge = (string)$targetColStart . (string)($targetRowStart) . ":" . (string)$targetColEnd . (string)($targetRowEnd);
                //Merge target cells
                $destSheet->mergeCells($merge);
            }
        }
    }

    /**
     * getRefTheme
     *
     * @param  int|string $niveau
     * @param  int $number
     * @return string
     */
    private function getRefTheme($niveau, int $number) : string
    {
        $palier = '';
        /** @phpstan-ignore-next-line */
        $convertToRomain = new IntToRoman();
        
        if ($niveau == 1) {
            /** @phpstan-ignore-next-line */
            $palier = $convertToRomain->filter($number);
        } elseif ((float)$niveau % 2 == 0) {
            $palier = $this->numberToAlphabet($number);
        } else {
            if ($niveau>5) {
                /** @phpstan-ignore-next-line */
                $palier = $convertToRomain->filter($number);
            } else {
                $palier = (string)$number;
            }
        }

        if ($niveau < 3) {
            $palier = strtoupper($palier);
        } else {
            $palier = strtolower($palier);
        }

        return $palier;
    }

    private function numberToAlphabet(int $number) : string
    {
        $number = intval($number);
        if ($number <= 0) {
            return '';
        }
        $alphabet = '';
        while ($number != 0) {
            $p = ($number - 1) % 26;
            $number = intval(($number - $p) / 26);
            $alphabet = chr(65 + $p) . $alphabet;
        }
        return $alphabet;
    }
    
    /**
     * setDico
     *
     * @param  Worksheet $sheet
     * @param  string|int $position
     * @param  string $text
     * @param  string $middle
     * @param  string|int $id
     * @return void
     */
    private function setDico(Worksheet $sheet, $position, $text, $middle, $id)
    {
        $sheet->setCellValue('A'.$position, $text);
        $sheet->setCellValue('B'.$position, $middle);
        $sheet->setCellValue('C'.$position, $id);
    }
}
