Proyectos de Subversion Moodle

<?php

namespace PhpOffice\PhpSpreadsheet;

use JsonSerializable;

use PhpOffice\PhpSpreadsheet\Calculation\Calculation;

use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;

use PhpOffice\PhpSpreadsheet\Shared\File;

use PhpOffice\PhpSpreadsheet\Shared\StringHelper;

use PhpOffice\PhpSpreadsheet\Style\Style;

use PhpOffice\PhpSpreadsheet\Worksheet\Iterator;

use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;

class Spreadsheet implements JsonSerializable

{

    // Allowable values for workbook window visilbity

    const VISIBILITY_VISIBLE = 'visible';

    const VISIBILITY_HIDDEN = 'hidden';

    const VISIBILITY_VERY_HIDDEN = 'veryHidden';

    private const DEFINED_NAME_IS_RANGE = false;

    private const DEFINED_NAME_IS_FORMULA = true;

    private const WORKBOOK_VIEW_VISIBILITY_VALUES = [

        self::VISIBILITY_VISIBLE,

        self::VISIBILITY_HIDDEN,

        self::VISIBILITY_VERY_HIDDEN,

    ];

    /**

     * Unique ID.

     *

     * @var string

     */

    private $uniqueID;

    /**

     * Document properties.

     *

     * @var Document\Properties

     */

    private $properties;

    /**

     * Document security.

     *

     * @var Document\Security

     */

    private $security;

    /**

     * Collection of Worksheet objects.

     *

     * @var Worksheet[]

     */

    private $workSheetCollection = [];

    /**

     * Calculation Engine.

     *

     * @var null|Calculation

     */

    private $calculationEngine;

    /**

     * Active sheet index.

     *

     * @var int

     */

    private $activeSheetIndex = 0;

    /**

     * Named ranges.

     *

     * @var DefinedName[]

     */

    private $definedNames = [];

    /**

     * CellXf supervisor.

     *

     * @var Style

     */

    private $cellXfSupervisor;

    /**

     * CellXf collection.

     *

     * @var Style[]

     */

    private $cellXfCollection = [];

    /**

     * CellStyleXf collection.

     *

     * @var Style[]

     */

    private $cellStyleXfCollection = [];

    /**

     * hasMacros : this workbook have macros ?

     *

     * @var bool

     */

    private $hasMacros = false;

    /**

     * macrosCode : all macros code as binary data (the vbaProject.bin file, this include form, code,  etc.), null if no macro.

     *

     * @var null|string

     */

    private $macrosCode;

    /**

     * macrosCertificate : if macros are signed, contains binary data vbaProjectSignature.bin file, null if not signed.

     *

     * @var null|string

     */

    private $macrosCertificate;

    /**

     * ribbonXMLData : null if workbook is'nt Excel 2007 or not contain a customized UI.

     *

     * @var null|array{target: string, data: string}

     */

    private $ribbonXMLData;

    /**

     * ribbonBinObjects : null if workbook is'nt Excel 2007 or not contain embedded objects (picture(s)) for Ribbon Elements

     * ignored if $ribbonXMLData is null.

     *

     * @var null|array

     */

    private $ribbonBinObjects;

    /**

     * List of unparsed loaded data for export to same format with better compatibility.

     * It has to be minimized when the library start to support currently unparsed data.

     *

     * @var array

     */

    private $unparsedLoadedData = [];

    /**

     * Controls visibility of the horizonal scroll bar in the application.

     *

     * @var bool

     */

    private $showHorizontalScroll = true;

    /**

     * Controls visibility of the horizonal scroll bar in the application.

     *

     * @var bool

     */

    private $showVerticalScroll = true;

    /**

     * Controls visibility of the sheet tabs in the application.

     *

     * @var bool

     */

    private $showSheetTabs = true;

    /**

     * Specifies a boolean value that indicates whether the workbook window

     * is minimized.

     *

     * @var bool

     */

    private $minimized = false;

    /**

     * Specifies a boolean value that indicates whether to group dates

     * when presenting the user with filtering optiomd in the user

     * interface.

     *

     * @var bool

     */

    private $autoFilterDateGrouping = true;

    /**

     * Specifies the index to the first sheet in the book view.

     *

     * @var int

     */

    private $firstSheetIndex = 0;

    /**

     * Specifies the visible status of the workbook.

     *

     * @var string

     */

    private $visibility = self::VISIBILITY_VISIBLE;

    /**

     * Specifies the ratio between the workbook tabs bar and the horizontal

     * scroll bar.  TabRatio is assumed to be out of 1000 of the horizontal

     * window width.

     *

     * @var int

     */

    private $tabRatio = 600;

    /** @var Theme */

    private $theme;

    public function getTheme(): Theme

    {

        return $this->theme;

    }

    /**

     * The workbook has macros ?

     *

     * @return bool

     */

    public function hasMacros()

    {

        return $this->hasMacros;

    }

    /**

     * Define if a workbook has macros.

     *

     * @param bool $hasMacros true|false

     */

    public function setHasMacros($hasMacros): void

    {

        $this->hasMacros = (bool) $hasMacros;

    }

    /**

     * Set the macros code.

     *

     * @param string $macroCode string|null

     */

    public function setMacrosCode($macroCode): void

    {

        $this->macrosCode = $macroCode;

        $this->setHasMacros($macroCode !== null);

    }

    /**

     * Return the macros code.

     *

     * @return null|string

     */

    public function getMacrosCode()

    {

        return $this->macrosCode;

    }

    /**

     * Set the macros certificate.

     *

     * @param null|string $certificate

     */

    public function setMacrosCertificate($certificate): void

    {

        $this->macrosCertificate = $certificate;

    }

    /**

     * Is the project signed ?

     *

     * @return bool true|false

     */

    public function hasMacrosCertificate()

    {

        return $this->macrosCertificate !== null;

    }

    /**

     * Return the macros certificate.

     *

     * @return null|string

     */

    public function getMacrosCertificate()

    {

        return $this->macrosCertificate;

    }

    /**

     * Remove all macros, certificate from spreadsheet.

     */

    public function discardMacros(): void

    {

        $this->hasMacros = false;

        $this->macrosCode = null;

        $this->macrosCertificate = null;

    }

    /**

     * set ribbon XML data.

     *

     * @param null|mixed $target

     * @param null|mixed $xmlData

     */

    public function setRibbonXMLData($target, $xmlData): void

    {

        if ($target !== null && $xmlData !== null) {

            $this->ribbonXMLData = ['target' => $target, 'data' => $xmlData];

        } else {

            $this->ribbonXMLData = null;

        }

    }

    /**

     * retrieve ribbon XML Data.

     *

     * @param string $what

     *

     * @return null|array|string

     */

    public function getRibbonXMLData($what = 'all') //we need some constants here...

    {

        $returnData = null;

        $what = strtolower($what);

        switch ($what) {

            case 'all':

                $returnData = $this->ribbonXMLData;

                break;

            case 'target':

            case 'data':

                if (is_array($this->ribbonXMLData)) {

                    $returnData = $this->ribbonXMLData[$what];

                }

                break;

        }

        return $returnData;

    }

    /**

     * store binaries ribbon objects (pictures).

     *

     * @param null|mixed $BinObjectsNames

     * @param null|mixed $BinObjectsData

     */

    public function setRibbonBinObjects($BinObjectsNames, $BinObjectsData): void

    {

        if ($BinObjectsNames !== null && $BinObjectsData !== null) {

            $this->ribbonBinObjects = ['names' => $BinObjectsNames, 'data' => $BinObjectsData];

        } else {

            $this->ribbonBinObjects = null;

        }

    }

    /**

     * List of unparsed loaded data for export to same format with better compatibility.

     * It has to be minimized when the library start to support currently unparsed data.

     *

     * @internal

     *

     * @return array

     */

    public function getUnparsedLoadedData()

    {

        return $this->unparsedLoadedData;

    }

    /**

     * List of unparsed loaded data for export to same format with better compatibility.

     * It has to be minimized when the library start to support currently unparsed data.

     *

     * @internal

     */

    public function setUnparsedLoadedData(array $unparsedLoadedData): void

    {

        $this->unparsedLoadedData = $unparsedLoadedData;

    }

    /**

     * return the extension of a filename. Internal use for a array_map callback (php<5.3 don't like lambda function).

     *

     * @param mixed $path

     *

     * @return string

     */

    private function getExtensionOnly($path)

    {

        $extension = pathinfo($path, PATHINFO_EXTENSION);

        return substr(/** @scrutinizer ignore-type */$extension, 0);

    }

    /**

     * retrieve Binaries Ribbon Objects.

     *

     * @param string $what

     *

     * @return null|array

     */

    public function getRibbonBinObjects($what = 'all')

    {

        $ReturnData = null;

        $what = strtolower($what);

        switch ($what) {

            case 'all':

                return $this->ribbonBinObjects;

            case 'names':

            case 'data':

                if (is_array($this->ribbonBinObjects) && isset($this->ribbonBinObjects[$what])) {

                    $ReturnData = $this->ribbonBinObjects[$what];

                }

                break;

            case 'types':

                if (

                    is_array($this->ribbonBinObjects) &&

                    isset($this->ribbonBinObjects['data']) && is_array($this->ribbonBinObjects['data'])

                ) {

                    $tmpTypes = array_keys($this->ribbonBinObjects['data']);

                    $ReturnData = array_unique(array_map([$this, 'getExtensionOnly'], $tmpTypes));

                } else {

                    $ReturnData = []; // the caller want an array... not null if empty

                }

                break;

        }

        return $ReturnData;

    }

    /**

     * This workbook have a custom UI ?

     *

     * @return bool

     */

    public function hasRibbon()

    {

        return $this->ribbonXMLData !== null;

    }

    /**

     * This workbook have additionnal object for the ribbon ?

     *

     * @return bool

     */

    public function hasRibbonBinObjects()

    {

        return $this->ribbonBinObjects !== null;

    }

    /**

     * Check if a sheet with a specified code name already exists.

     *

     * @param string $codeName Name of the worksheet to check

     *

     * @return bool

     */

    public function sheetCodeNameExists($codeName)

    {

        return $this->getSheetByCodeName($codeName) !== null;

    }

    /**

     * Get sheet by code name. Warning : sheet don't have always a code name !

     *

     * @param string $codeName Sheet name

     *

     * @return null|Worksheet

     */

    public function getSheetByCodeName($codeName)

    {

        $worksheetCount = count($this->workSheetCollection);

        for ($i = 0; $i < $worksheetCount; ++$i) {

            if ($this->workSheetCollection[$i]->getCodeName() == $codeName) {

                return $this->workSheetCollection[$i];

            }

        }

        return null;

    }

    /**

     * Create a new PhpSpreadsheet with one Worksheet.

     */

    public function __construct()

    {

        $this->uniqueID = uniqid('', true);

        $this->calculationEngine = new Calculation($this);

        $this->theme = new Theme();

        // Initialise worksheet collection and add one worksheet

        $this->workSheetCollection = [];

        $this->workSheetCollection[] = new Worksheet($this);

        $this->activeSheetIndex = 0;

        // Create document properties

        $this->properties = new Document\Properties();

        // Create document security

        $this->security = new Document\Security();

        // Set defined names

        $this->definedNames = [];

        // Create the cellXf supervisor

        $this->cellXfSupervisor = new Style(true);

        $this->cellXfSupervisor->bindParent($this);

        // Create the default style

        $this->addCellXf(new Style());

        $this->addCellStyleXf(new Style());

    }

    /**

     * Code to execute when this worksheet is unset().

     */

    public function __destruct()

    {

        $this->disconnectWorksheets();

        $this->calculationEngine = null;

        $this->cellXfCollection = [];

        $this->cellStyleXfCollection = [];

    }

    /**

     * Disconnect all worksheets from this PhpSpreadsheet workbook object,

     * typically so that the PhpSpreadsheet object can be unset.

     */

    public function disconnectWorksheets(): void

    {

        foreach ($this->workSheetCollection as $worksheet) {

            $worksheet->disconnectCells();

            unset($worksheet);

        }

        $this->workSheetCollection = [];

    }

    /**

     * Return the calculation engine for this worksheet.

     *

     * @return null|Calculation

     */

    public function getCalculationEngine()

    {

        return $this->calculationEngine;

    }

    /**

     * Get properties.

     *

     * @return Document\Properties

     */

    public function getProperties()

    {

        return $this->properties;

    }

    /**

     * Set properties.

     */

    public function setProperties(Document\Properties $documentProperties): void

    {

        $this->properties = $documentProperties;

    }

    /**

     * Get security.

     *

     * @return Document\Security

     */

    public function getSecurity()

    {

        return $this->security;

    }

    /**

     * Set security.

     */

    public function setSecurity(Document\Security $documentSecurity): void

    {

        $this->security = $documentSecurity;

    }

    /**

     * Get active sheet.

     *

     * @return Worksheet

     */

    public function getActiveSheet()

    {

        return $this->getSheet($this->activeSheetIndex);

    }

    /**

     * Create sheet and add it to this workbook.

     *

     * @param null|int $sheetIndex Index where sheet should go (0,1,..., or null for last)

     *

     * @return Worksheet

     */

    public function createSheet($sheetIndex = null)

    {

        $newSheet = new Worksheet($this);

        $this->addSheet($newSheet, $sheetIndex);

        return $newSheet;

    }

    /**

     * Check if a sheet with a specified name already exists.

     *

     * @param string $worksheetName Name of the worksheet to check

     *

     * @return bool

     */

    public function sheetNameExists($worksheetName)

    {

        return $this->getSheetByName($worksheetName) !== null;

    }

    /**

     * Add sheet.

     *

     * @param Worksheet $worksheet The worksheet to add

     * @param null|int $sheetIndex Index where sheet should go (0,1,..., or null for last)

     *

     * @return Worksheet

     */

    public function addSheet(Worksheet $worksheet, $sheetIndex = null)

    {

        if ($this->sheetNameExists($worksheet->getTitle())) {

            throw new Exception(

                "Workbook already contains a worksheet named '{$worksheet->getTitle()}'. Rename this worksheet first."

            );

        }

        if ($sheetIndex === null) {

            if ($this->activeSheetIndex < 0) {

                $this->activeSheetIndex = 0;

            }

            $this->workSheetCollection[] = $worksheet;

        } else {

            // Insert the sheet at the requested index

            array_splice(

                $this->workSheetCollection,

                $sheetIndex,

                0,

                [$worksheet]

            );

            // Adjust active sheet index if necessary

            if ($this->activeSheetIndex >= $sheetIndex) {

                ++$this->activeSheetIndex;

            }

        }

        if ($worksheet->getParent() === null) {

            $worksheet->rebindParent($this);

        }

        return $worksheet;

    }

    /**

     * Remove sheet by index.

     *

     * @param int $sheetIndex Index position of the worksheet to remove

     */

    public function removeSheetByIndex($sheetIndex): void

    {

        $numSheets = count($this->workSheetCollection);

        if ($sheetIndex > $numSheets - 1) {

            throw new Exception(

                "You tried to remove a sheet by the out of bounds index: {$sheetIndex}. The actual number of sheets is {$numSheets}."

            );

        }

        array_splice($this->workSheetCollection, $sheetIndex, 1);

        // Adjust active sheet index if necessary

        if (

            ($this->activeSheetIndex >= $sheetIndex) &&

            ($this->activeSheetIndex > 0 || $numSheets <= 1)

        ) {

            --$this->activeSheetIndex;

        }

    }

    /**

     * Get sheet by index.

     *

     * @param int $sheetIndex Sheet index

     *

     * @return Worksheet

     */

    public function getSheet($sheetIndex)

    {

        if (!isset($this->workSheetCollection[$sheetIndex])) {

            $numSheets = $this->getSheetCount();

            throw new Exception(

                "Your requested sheet index: {$sheetIndex} is out of bounds. The actual number of sheets is {$numSheets}."

            );

        }

        return $this->workSheetCollection[$sheetIndex];

    }

    /**

     * Get all sheets.

     *

     * @return Worksheet[]

     */

    public function getAllSheets()

    {

        return $this->workSheetCollection;

    }

    /**

     * Get sheet by name.

     *

     * @param string $worksheetName Sheet name

     *

     * @return null|Worksheet

     */

    public function getSheetByName($worksheetName)

    {

        $worksheetCount = count($this->workSheetCollection);

        for ($i = 0; $i < $worksheetCount; ++$i) {

            if ($this->workSheetCollection[$i]->getTitle() === trim($worksheetName, "'")) {

                return $this->workSheetCollection[$i];

            }

        }

        return null;

    }

    /**

     * Get sheet by name, throwing exception if not found.

     */

    public function getSheetByNameOrThrow(string $worksheetName): Worksheet

    {

        $worksheet = $this->getSheetByName($worksheetName);

        if ($worksheet === null) {

            throw new Exception("Sheet $worksheetName does not exist.");

        }

        return $worksheet;

    }

    /**

     * Get index for sheet.

     *

     * @return int index

     */

    public function getIndex(Worksheet $worksheet)

    {

        foreach ($this->workSheetCollection as $key => $value) {

            if ($value->getHashCode() === $worksheet->getHashCode()) {

                return $key;

            }

        }

        throw new Exception('Sheet does not exist.');

    }

    /**

     * Set index for sheet by sheet name.

     *

     * @param string $worksheetName Sheet name to modify index for

     * @param int $newIndexPosition New index for the sheet

     *

     * @return int New sheet index

     */

    public function setIndexByName($worksheetName, $newIndexPosition)

    {

        $oldIndex = $this->getIndex($this->getSheetByNameOrThrow($worksheetName));

        $worksheet = array_splice(

            $this->workSheetCollection,

            $oldIndex,

            1

        );

        array_splice(

            $this->workSheetCollection,

            $newIndexPosition,

            0,

            $worksheet

        );

        return $newIndexPosition;

    }

    /**

     * Get sheet count.

     *

     * @return int

     */

    public function getSheetCount()

    {

        return count($this->workSheetCollection);

    }

    /**

     * Get active sheet index.

     *

     * @return int Active sheet index

     */

    public function getActiveSheetIndex()

    {

        return $this->activeSheetIndex;

    }

    /**

     * Set active sheet index.

     *

     * @param int $worksheetIndex Active sheet index

     *

     * @return Worksheet

     */

    public function setActiveSheetIndex($worksheetIndex)

    {

        $numSheets = count($this->workSheetCollection);

        if ($worksheetIndex > $numSheets - 1) {

            throw new Exception(

                "You tried to set a sheet active by the out of bounds index: {$worksheetIndex}. The actual number of sheets is {$numSheets}."

            );

        }

        $this->activeSheetIndex = $worksheetIndex;

        return $this->getActiveSheet();

    }

    /**

     * Set active sheet index by name.

     *

     * @param string $worksheetName Sheet title

     *

     * @return Worksheet

     */

    public function setActiveSheetIndexByName($worksheetName)

    {

        if (($worksheet = $this->getSheetByName($worksheetName)) instanceof Worksheet) {

            $this->setActiveSheetIndex($this->getIndex($worksheet));

            return $worksheet;

        }

        throw new Exception('Workbook does not contain sheet:' . $worksheetName);

    }

    /**

     * Get sheet names.

     *

     * @return string[]

     */

    public function getSheetNames()

    {

        $returnValue = [];

        $worksheetCount = $this->getSheetCount();

        for ($i = 0; $i < $worksheetCount; ++$i) {

            $returnValue[] = $this->getSheet($i)->getTitle();

        }

        return $returnValue;

    }

    /**

     * Add external sheet.

     *

     * @param Worksheet $worksheet External sheet to add

     * @param null|int $sheetIndex Index where sheet should go (0,1,..., or null for last)

     *

     * @return Worksheet

     */

    public function addExternalSheet(Worksheet $worksheet, $sheetIndex = null)

    {

        if ($this->sheetNameExists($worksheet->getTitle())) {

            throw new Exception("Workbook already contains a worksheet named '{$worksheet->getTitle()}'. Rename the external sheet first.");

        }

        // count how many cellXfs there are in this workbook currently, we will need this below

        $countCellXfs = count($this->cellXfCollection);

        // copy all the shared cellXfs from the external workbook and append them to the current

        foreach ($worksheet->getParentOrThrow()->getCellXfCollection() as $cellXf) {

            $this->addCellXf(clone $cellXf);

        }

        // move sheet to this workbook

        $worksheet->rebindParent($this);

        // update the cellXfs

        foreach ($worksheet->getCoordinates(false) as $coordinate) {

            $cell = $worksheet->getCell($coordinate);

            $cell->setXfIndex($cell->getXfIndex() + $countCellXfs);

        }

        // update the column dimensions Xfs

        foreach ($worksheet->getColumnDimensions() as $columnDimension) {

            $columnDimension->setXfIndex($columnDimension->getXfIndex() + $countCellXfs);

        }

        // update the row dimensions Xfs

        foreach ($worksheet->getRowDimensions() as $rowDimension) {

            $xfIndex = $rowDimension->getXfIndex();

            if ($xfIndex !== null) {

                $rowDimension->setXfIndex($xfIndex + $countCellXfs);

            }

        }

        return $this->addSheet($worksheet, $sheetIndex);

    }

    /**

     * Get an array of all Named Ranges.

     *

     * @return DefinedName[]

     */

    public function getNamedRanges(): array

    {

        return array_filter(

            $this->definedNames,

            function (DefinedName $definedName) {

                return $definedName->isFormula() === self::DEFINED_NAME_IS_RANGE;

            }

        );

    }

    /**

     * Get an array of all Named Formulae.

     *

     * @return DefinedName[]

     */

    public function getNamedFormulae(): array

    {

        return array_filter(

            $this->definedNames,

            function (DefinedName $definedName) {

                return $definedName->isFormula() === self::DEFINED_NAME_IS_FORMULA;

            }

        );

    }

    /**

     * Get an array of all Defined Names (both named ranges and named formulae).

     *

     * @return DefinedName[]

     */

    public function getDefinedNames(): array

    {

        return $this->definedNames;

    }

    /**

     * Add a named range.

     * If a named range with this name already exists, then this will replace the existing value.

     */

    public function addNamedRange(NamedRange $namedRange): void

    {

        $this->addDefinedName($namedRange);

    }

    /**

     * Add a named formula.

     * If a named formula with this name already exists, then this will replace the existing value.

     */

    public function addNamedFormula(NamedFormula $namedFormula): void

    {

        $this->addDefinedName($namedFormula);

    }

    /**

     * Add a defined name (either a named range or a named formula).

     * If a defined named with this name already exists, then this will replace the existing value.

     */

    public function addDefinedName(DefinedName $definedName): void

    {

        $upperCaseName = StringHelper::strToUpper($definedName->getName());

        if ($definedName->getScope() == null) {

            // global scope

            $this->definedNames[$upperCaseName] = $definedName;

        } else {

            // local scope

            $this->definedNames[$definedName->getScope()->getTitle() . '!' . $upperCaseName] = $definedName;

        }

    }

    /**

     * Get named range.

     *

     * @param null|Worksheet $worksheet Scope. Use null for global scope

     */

    public function getNamedRange(string $namedRange, ?Worksheet $worksheet = null): ?NamedRange

    {

        $returnValue = null;

        if ($namedRange !== '') {

            $namedRange = StringHelper::strToUpper($namedRange);

            // first look for global named range

            $returnValue = $this->getGlobalDefinedNameByType($namedRange, self::DEFINED_NAME_IS_RANGE);

            // then look for local named range (has priority over global named range if both names exist)

            $returnValue = $this->getLocalDefinedNameByType($namedRange, self::DEFINED_NAME_IS_RANGE, $worksheet) ?: $returnValue;

        }