<?php

namespace Modules\Consultation\Service;

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 phpDocumentor\Reflection\PseudoTypes\LowercaseString;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use Romans\Filter\IntToRoman;

use Entity\Model\AoExCentre;
use Entity\Model\AoExcFofo;
use Entity\Model\AoQstQ;
use Entity\Model\AoQstR;
use Helpers\Log\LogSysteme;
use Modules\Consultation\Repository\ExportExcel;
use PHPExcel_Style_Protection;
use PHPExcel_Worksheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;

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 Spreadsheet $spreadsheetQuest
     */
    private $spreadsheetQuest;

    /**
     * @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 AoExCentre($this->pdo);
        $aoQstQ = new AoQstQ($this->pdo);
        $aoExFofo = new AoExcFofo($this->pdo);
        
        $infoForm = $aoExFofo->find([
            'SOCIETE' => '',
            'CTRE' => '',
            'CODE_FOU' => '',
            'CODEFORM' => $this->codeForm,
            'VERSFORM' => $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 = $_SESSION['chemin_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, (string)STR_PAD_RIGHT).$this->codeFou.'_'.$this->rand.'.'.$pathInfo['extension'];
        $newName =  $_SESSION['dir_tmp'].$fileName;
        
        copy($fileSource, $newName);

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

        /** On recupère le template des questionnaire et on le met dans TMP pour plus tard */
        $fileSourceQuest = $_SESSION['_etc'].'ModeleQuestionnaireAO.xlsx';
        $pathInfoQuest = pathinfo($fileSourceQuest);
        $pathInfoQuest['extension'] = empty($pathInfoQuest['extension']) ? '' : $pathInfoQuest['extension'];

        $newNameQuest =  $_SESSION['dir_tmp'].'AO_QUEST'.'_'.$this->rand.'.'.$pathInfoQuest['extension'];
        
        copy($fileSourceQuest, $newNameQuest);

        $this->spreadsheetQuest = IOFactory::load($newNameQuest);
        unlink($newNameQuest);
        /** */

        // 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(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED);
        $feuilleReponse->getRowDimension(1)->setVisible(false);
        
        // remplire tout les champs d'entête
        $this->exportExcelRepo = new ExportExcel($this->pdo);
        $listeChampsEntete = $this->exportExcelRepo->getInfoExport($this->codeForm, $this->versionForm, true);
        $listeChampsLigne = $this->exportExcelRepo->getInfoExport($this->codeForm, $this->versionForm, false);
        $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((string)(Sql::boolSql($champs['LUE']) == 0));
        }

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

        /* ligne titre exemple */
        $cellTitleStart = Coordinate::stringFromColumnIndex($parametreEntete['NCOL_ST']).$parametreEntete['NLIG_ST'];
        $cellTitleEnd = Coordinate::stringFromColumnIndex($parametreEntete['DERCOLAO']).$parametreEntete['NLIG_ST'];
        $rangeTitle = $cellTitleStart.':'.$cellTitleEnd;

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

        /* Ligne d'exemple 1 */
        $cellLigneStart = Coordinate::stringFromColumnIndex($parametreEntete['COLUNAO']).$parametreEntete['LIGUNAO'];
        $cellLigneEnd = Coordinate::stringFromColumnIndex($parametreEntete['DERCOLAO']).($parametreEntete['LIGUNAO']+$parametreEntete['NBLIGAO']-1);
        $rangeSaveLigne = $cellLigneStart.':'.$cellLigneEnd;

        $colInit = $parametreEntete['COLUNAO'];
        $ligInit = $parametreEntete['LIGUNAO'];
        $count = 0;
        
        /** On remplis et on duplique la ligne 1 pour chaque ligne trouvé */
        foreach ($infoLigne as $ligne) {
            if ($count != 0) {
                $startNextBloc += $countLigneBloc;

                /* Si sous titre, alors on duplique le modele de sous-titre  */
                if (sql::boolSql($ligne['ST_APLIG'])) {
                    $cellNextStart = Coordinate::stringFromColumnIndex($parametreEntete['COLUNAO']).($startNextBloc);
                    $this->copyRows($feuilleReponse, $rangeTitle, $cellNextStart);

                    $startNextBloc += 1;
                }

                $cellNextStart = Coordinate::stringFromColumnIndex($parametreEntete['COLUNAO']).($startNextBloc);
                $this->copyRows($feuilleReponse, $rangeSaveLigne, $cellNextStart);

                $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);
                if (Sql::boolSql($champs['LUE'])) {
                    $feuilleReponse->getStyle($cell)->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
                } else {
                    $feuilleReponse->getStyle($cell)->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_PROTECTED);
                }
            }
            $count++;
        }

        // 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(false);
        $feuilleReponse->getProtection()->setSort(false);
        $feuilleReponse->getProtection()->setInsertRows(false);
        $feuilleReponse->getProtection()->setFormatCells(false);

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

        /***** PARTIE QUESTIONNAIRE */
        $infoQuest = $aoQstQ->find(['NO_AO' => $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(PHPExcel_Worksheet::SHEETSTATE_HIDDEN);
        $dictionnaire->getProtection()->setSheet(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;

        /** @var Worksheet $sheetQuest */
        $sheetQuest = '';
        foreach ($this->spreadsheetQuest->getSheetNames() as $sheetName) {
            $sheetQuest = $this->spreadsheetQuest->getSheetByName($sheetName);
            break;
        }

        $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) {
                $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']);

            $idTheme = '';
            foreach ($explode as $val) {
                if ($idTheme) {
                    $idTheme .= '.'.$saveIdTheme[$val];
                } else {
                    $idTheme = $saveIdTheme[$val];
                }
            }

            $valueCell = 'T|'.$theme['IdTheme'];
            $sheetQuest->setCellValue('A'.$posRow, $valueCell);
            $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++;

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

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

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

                // TODO  merge cell

                $validation = $sheetQuest->getCell('H'.$posRow)->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') {
                    $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']);

                    $sheetQuest->getStyle('H'.$posRow)->getNumberFormat()->setFormatCode('0.00000');
                }
                
                $posRow++;

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

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

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

        $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');
                $valueForCell = ChampSupp::getValueCS('AO_ENTET', $idCS, (string)$numFix, $pdo);
            } else {
                $idLigne = $champs['POSTE'];
                $idCS = ChampSupp::autoCompletCodeEnr($idAO, $idLigne, 'AO_LIGNE');
                $valueForCell = ChampSupp::getValueCS('AO_LIGNE', $idCS, (string)$numFix, $pdo);
            }
        } else {
            $valueForCell = $info[$champs['CODEINTZ']]??null;
            if (!$entete && $champs['CODEINTZ'] == 'DELAILIVR' && $info['TYPEDEL'] == 'D' && $valueForCell) {
                $valueForCell = Date::dateConverte($valueForCell);
            } elseif (in_array($champs['CODEINTZ'], ['DATLIMREP', 'DATLIMVAL','DATREP','DELAILIVR']) && $valueForCell) {
                $valueForCell = Date::dateConverte($valueForCell);
            }
        }

        return $valueForCell;
    }

    private function copyRows(Worksheet $sheet, string $srcRange, string $dstCell, Worksheet $destSheet = null): void
    {
        if (!isset($destSheet)) {
            $destSheet = $sheet;
        }

        if (!preg_match('/^([A-Z]+)(\d+):([A-Z]+)(\d+)$/', $srcRange, $srcRangeMatch)) {
            // Invalid src range
            return;
        }

        if (!preg_match('/^([A-Z]+)(\d+)$/', $dstCell, $destCellMatch)) {
            // Invalid dest cell
            return;
        }

        $srcColumnStart = $srcRangeMatch[1];
        $srcRowStart = $srcRangeMatch[2];
        $srcColumnEnd = $srcRangeMatch[3];
        $srcRowEnd = $srcRangeMatch[4];

        $destColumnStart = $destCellMatch[1];
        $destRowStart = $destCellMatch[2];

        $srcColumnStart = Coordinate::columnIndexFromString($srcColumnStart);
        $srcColumnEnd = Coordinate::columnIndexFromString($srcColumnEnd);
        $destColumnStart = Coordinate::columnIndexFromString($destColumnStart);

        $rowCount = 0;
        for ($row = $srcRowStart; $row <= $srcRowEnd; $row++) {
            $colCount = 0;
            for ($col = $srcColumnStart; $col <= $srcColumnEnd; $col++) {
                //$cell = $sheet->getCellByColumnAndRow($col, $row);
                //$style = $sheet->getStyleByColumnAndRow($col, $row);
                $cell = $sheet->getCell(Coordinate::stringFromColumnIndex($col).$row);
                $style = $sheet->getStyle(Coordinate::stringFromColumnIndex($col).$row);
                $dstCell = Coordinate::stringFromColumnIndex($destColumnStart + $colCount) . (string)($destRowStart + $rowCount);
                $destSheet->setCellValue($dstCell, $cell->getValue());
                $destSheet->duplicateStyle($style, $dstCell);
                
                // Set width of column, but only once per column
                if ($rowCount === 0) {
                    $w = $sheet->getColumnDimensionByColumn($col)->getWidth();
                    $destSheet->getColumnDimensionByColumn($destColumnStart + $colCount)->setAutoSize(false);
                    $destSheet->getColumnDimensionByColumn($destColumnStart + $colCount)->setWidth($w);
                }

                $colCount++;
            }

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

            $rowCount++;
        }

        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 ($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);
    }
}
