class.ilExcel.php

Go to the documentation of this file.

<?php
 
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use ILIAS\FileUpload\MimeType;
 
/*
 * Wrapper for Microsoft Excel Import/Export (based on PHPSpreadsheet, formerPHPExcel which is deprecated)
 * @author Jörg Lützenkirchen <luetzenkirchen@leifos.com>
 */
class ilExcel
{
    public const FORMAT_XML = 'Xlsx';
    public const FORMAT_BIFF = 'Xls';
    protected string $format;
 
    private array $noncharacters = [
        '\x{FFFE}-\x{FFFF}',
        '\x{1FFFE}-\x{1FFFF}',
        '\x{2FFFE}-\x{2FFFF}',
        '\x{3FFFE}-\x{3FFFF}',
        '\x{4FFFE}-\x{4FFFF}',
        '\x{5FFFE}-\x{5FFFF}',
        '\x{6FFFE}-\x{6FFFF}',
        '\x{7FFFE}-\x{7FFFF}',
        '\x{8FFFE}-\x{8FFFF}',
        '\x{9FFFE}-\x{9FFFF}',
        '\x{AFFFE}-\x{AFFFF}',
        '\x{BFFFE}-\x{BFFFF}',
        '\x{CFFFE}-\x{CFFFF}',
        '\x{DFFFE}-\x{DFFFF}',
        '\x{EFFFE}-\x{EFFFF}',
        '\x{FFFFE}-\x{FFFFF}',
        '\x{10FFFE}-\x{10FFFF}',
        '\x{FDD0}-\x{FDEF}'
    ];
 
    protected ilLanguage $lng;
    protected Spreadsheet $workbook;
    protected string $type;
 
    public function __construct()
    {
        global $DIC;
 
        $this->lng = $DIC->language();
        $this->setFormat(self::FORMAT_XML);
        $this->workbook = new Spreadsheet();
        $this->workbook->removeSheetByIndex(0);
    }
 
    //
    // loading files
    //
 
    public function loadFromFile(string $filename): void
    {
        $this->workbook = IOFactory::load($filename);
    }
 
    //
    // type/format
    //
 
    public function getValidFormats(): array
    {
        return [self::FORMAT_XML, self::FORMAT_BIFF];
    }
 
    public function setFormat(string $a_format): void
    {
        if (in_array($a_format, $this->getValidFormats())) {
            $this->format = $a_format;
        }
    }
 
 
    //
    // sheets
    //
 
    public function addSheet(
        string $a_name,
        bool $a_activate = true
    ): int {
        #20749
        // see Worksheet::$_invalidCharacters;
        $invalid = ['*', ':', '/', '\\', '?', '[', ']', '\'-', '\''];
 
        $a_name = str_replace($invalid, '', $a_name);
 
        // #19056 - phpExcel only allows 31 chars
        // see https://github.com/PHPOffice/PHPExcel/issues/79
        $a_name = ilStr::shortenTextExtended($a_name, 31);
 
        $sheet = new Worksheet($this->workbook, $a_name);
        $this->workbook->addSheet($sheet);
        $new_index = $this->workbook->getSheetCount() - 1;
 
        if ($a_activate) {
            $this->setActiveSheet($new_index);
        }
 
        return $new_index;
    }
 
    public function setActiveSheet(int $a_index): void
    {
        $this->workbook->setActiveSheetIndex($a_index);
    }
 
 
    public function getSheetCount(): int
    {
        return $this->workbook->getSheetCount();
    }
 
 
    public function getSheetTitle(): string
    {
        return $this->workbook->getActiveSheet()->getTitle();
    }
 
 
    //
    // cells
    //
 
    protected function prepareValue($value)
    {
        if (is_bool($value)) {
            return $this->prepareBooleanValue($value);
        }
 
        if ($value instanceof ilDateTime) {
            return $this->prepareDateValue($value);
        }
 
        if (is_string($value)) {
            return $this->prepareString($value);
        }
 
        return $value;
    }
 
    protected function prepareDateValue(ilDateTime $a_value)
    {
        switch (true) {
            case $a_value instanceof ilDate:
                $a_value = Date::stringToExcel($a_value->get(IL_CAL_DATE));
                break;
 
            default:
                $a_value = Date::stringToExcel($a_value->get(IL_CAL_DATETIME));
                break;
        }
 
        return $a_value;
    }
 
    protected function prepareBooleanValue(bool $a_value): string
    {
        $lng = $this->lng;
 
        return $a_value ? $lng->txt('yes') : $lng->txt('no');
    }
 
    protected function prepareString(
        string $value,
    ): string {
        if (!mb_check_encoding($value, 'UTF-8')) {
            throw new InvalidArgumentException('Invalid UTF-8 passed.');
        }
 
        return $this->cleanupNonCharachters(strip_tags($value));
    }
 
    protected function setDateFormat(Cell $a_cell, $a_value): void
    {
        if ($a_value instanceof ilDate) {
            $a_cell->getStyle()->getNumberFormat()->setFormatCode('dd.mm.yyyy');
        } elseif ($a_value instanceof ilDateTime) {
            $a_cell->getStyle()->getNumberFormat()->setFormatCode('dd.mm.yyyy hh:mm:ss');
        }
    }
 
    public function setCellByCoordinates($a_coords, $a_value): void
    {
        if ($a_value instanceof ilDateTime) {
            $wb = $this->workbook->getActiveSheet()->setCellValue(
                $a_coords,
                $this->prepareValue($a_value)
            );
            $cell = $wb->getCell($a_coords);
            $this->setDateFormat($cell, $a_value);
        } elseif (is_numeric($a_value)) {
            $this->workbook->getActiveSheet()->setCellValueExplicit(
                $a_coords,
                $this->prepareValue($a_value),
                DataType::TYPE_NUMERIC
            );
        } else {
            $this->workbook->getActiveSheet()->setCellValueExplicit(
                $a_coords,
                $this->prepareValue($a_value),
                DataType::TYPE_STRING
            );
        }
    }
 
    public function setCell(
        int $a_row,
        int $col,
        $value,
        ?string $datatype = null
    ): void {
        $coordinate = $this->getCoordByColumnAndRow($col, $a_row);
 
        if ($datatype === DataType::TYPE_STRING) {
            $this->workbook->getActiveSheet()->setCellValueExplicit(
                $coordinate,
                $this->prepareString($value),
                $datatype
            );
        } elseif ($datatype !== null) {
            $this->workbook->getActiveSheet()->setCellValueExplicit(
                $coordinate,
                $this->prepareValue($value),
                $datatype
            );
        } elseif ($value instanceof ilDateTime) {
            $wb = $this->workbook->getActiveSheet()->setCellValue(
                $coordinate,
                $this->prepareValue($value)
            );
            $this->setDateFormat($wb->getCell($coordinate), $value);
        } elseif (is_numeric($value)) {
            $this->workbook->getActiveSheet()->setCellValueExplicit(
                $coordinate,
                $this->prepareValue($value),
                DataType::TYPE_NUMERIC
            );
        } else {
            $this->workbook->getActiveSheet()->setCellValueExplicit(
                $coordinate,
                $this->prepareValue($value),
                DataType::TYPE_STRING
            );
        }
    }
 
    public function setCellArray(
        array $a_values,
        string $a_top_left = 'A1',
        $a_null_value = null
    ): void {
        foreach ($a_values as $row_idx => $cols) {
            if (is_array($cols)) {
                foreach ($cols as $col_idx => $col_value) {
                    $a_values[$row_idx][$col_idx] = $this->prepareValue($col_value);
                }
            } else {
                $a_values[$row_idx] = $this->prepareValue($cols);
            }
        }
 
        $this->workbook->getActiveSheet()->fromArray($a_values, $a_null_value, $a_top_left);
    }
 
 
    public function getCell(
        int $a_row,
        int $a_col
    ) {
        $coordinate = $this->getCoordByColumnAndRow($a_col, $a_row);
        return $this->workbook->getActiveSheet()->getCell($coordinate)->getValue();
    }
 
    public function getSheetAsArray(): array
    {
        return $this->workbook->getActiveSheet()->toArray();
    }
 
    public function getColumnCount(): int
    {
        return Coordinate::columnIndexFromString($this->workbook->getActiveSheet()->getHighestDataColumn());
    }
 
    public function getColumnCoord(int $a_col): string
    {
        $col = $this->columnIndexAdjustment($a_col);
        return Coordinate::stringFromColumnIndex($col);
    }
 
    protected function setGlobalAutoSize(): void
    {
        // this may change the active sheet
        foreach ($this->workbook->getWorksheetIterator() as $worksheet) {
            $this->workbook->setActiveSheetIndex($this->workbook->getIndex($worksheet));
            $sheet = $this->workbook->getActiveSheet();
            $cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(true);
            foreach ($cellIterator as $cell) {
                $sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
            }
        }
    }
 
    //
    // deliver/save
    //
 
    protected function prepareStorage(string $a_file_name): string
    {
        $this->setGlobalAutoSize();
        $this->workbook->setActiveSheetIndex(0);
 
        switch ($this->format) {
            case self::FORMAT_BIFF:
                if (stripos($a_file_name, '.xls') === false) {
                    $a_file_name .= '.xls';
                }
                break;
 
            case self::FORMAT_XML:
                if (stripos($a_file_name, '.xlsx') === false) {
                    $a_file_name .= '.xlsx';
                }
                break;
        }
 
        return $a_file_name;
    }
 
    public function sendToClient(string $a_file_name): void
    {
        $a_file_name = $this->prepareStorage($a_file_name);
        switch ($this->format) {
            case self::FORMAT_BIFF:
                $a_mime_type = MimeType::APPLICATION__VND_MS_EXCEL;
                break;
 
            case self::FORMAT_XML:
                $a_mime_type = MimeType::APPLICATION__VND_OPENXMLFORMATS_OFFICEDOCUMENT_SPREADSHEETML_SHEET;
                break;
            default:
                $a_mime_type = MimeType::APPLICATION__OCTET_STREAM;
                break;
        }
        $tmp_name = ilFileUtils::ilTempnam();
 
        $writer = IOFactory::createWriter($this->workbook, $this->format);
        $writer->save($tmp_name);
 
        ilFileDelivery::deliverFileAttached($tmp_name, $a_file_name, $a_mime_type, true);
    }
 
    public function writeToFile(string $a_file): void
    {
        $a_file = $this->prepareStorage($a_file);
 
        $writer = IOFactory::createWriter($this->workbook, $this->format);
        $writer->save($a_file);
    }
 
    public function writeToTmpFile(): string
    {
        $writer = IOFactory::createWriter($this->workbook, $this->format);
        $filename = ilFileUtils::ilTempnam();
        $writer->save($filename);
 
        return $filename;
    }
 
    public function setBold(string $a_coords): void
    {
        $this->workbook->getActiveSheet()->getStyle($a_coords)->getFont()->setBold(true);
    }
 
    public function setColors(
        string $a_coords,
        string $a_background,
        ?string $a_font = null
    ): void {
        $opts = [
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'color' => ['rgb' => $a_background]
            ]
        ];
 
        if ($a_font) {
            $opts['font'] = [
                'color' => ['rgb' => $a_font]
            ];
        }
 
        $this->workbook->getActiveSheet()->getStyle($a_coords)->applyFromArray($opts);
    }
 
    public function setBorders(
        string $a_coords,
        bool $a_top,
        bool $a_right = false,
        bool $a_bottom = false,
        bool $a_left = false
    ): void {
        $style = $this->workbook->getActiveSheet()->getStyle($a_coords);
 
        // :TODO: border styles?
        if ($a_top) {
            $style->getBorders()->getTop()->setBorderStyle(Border::BORDER_THIN);
        }
        if ($a_right) {
            $style->getBorders()->getRight()->setBorderStyle(Border::BORDER_THIN);
        }
        if ($a_bottom) {
            $style->getBorders()->getBottom()->setBorderStyle(Border::BORDER_THIN);
        }
        if ($a_left) {
            $style->getBorders()->getLeft()->setBorderStyle(Border::BORDER_THIN);
        }
    }
 
    public function getCoordByColumnAndRow(
        int $pColumn = 1,
        int $pRow = 1
    ): string {
        $col = $this->columnIndexAdjustment($pColumn);
        $columnLetter = Coordinate::stringFromColumnIndex($col);
 
        return $columnLetter . $pRow;
    }
 
    public function addLink(
        int $a_row,
        int $a_column,
        string $a_path
    ): void {
        $coordinate = $this->getCoordByColumnAndRow($a_column, $a_row);
        $this->workbook->getActiveSheet()->getCell($coordinate)->getHyperlink()->setUrl($a_path);
    }
 
    public function columnIndexAdjustment(int $column): int
    {
        return ++$column;
    }
 
    public function mergeCells(string $coordinatesRange): void
    {
        $this->workbook->getActiveSheet()->mergeCells($coordinatesRange);
    }
 
    private function cleanupNonCharachters(string $string): string
    {
        return mb_ereg_replace('[' . implode('', $this->noncharacters) . ']', '', $string);
    }
}