Proyectos de Subversion Moodle

<?php

namespace PhpOffice\PhpSpreadsheet\Worksheet;

use ArrayObject;

use PhpOffice\PhpSpreadsheet\Calculation\Calculation;

use PhpOffice\PhpSpreadsheet\Calculation\Functions;

use PhpOffice\PhpSpreadsheet\Cell\AddressRange;

use PhpOffice\PhpSpreadsheet\Cell\Cell;

use PhpOffice\PhpSpreadsheet\Cell\CellAddress;

use PhpOffice\PhpSpreadsheet\Cell\CellRange;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

use PhpOffice\PhpSpreadsheet\Cell\DataType;

use PhpOffice\PhpSpreadsheet\Cell\DataValidation;

use PhpOffice\PhpSpreadsheet\Cell\Hyperlink;

use PhpOffice\PhpSpreadsheet\Cell\IValueBinder;

use PhpOffice\PhpSpreadsheet\Chart\Chart;

use PhpOffice\PhpSpreadsheet\Collection\Cells;

use PhpOffice\PhpSpreadsheet\Collection\CellsFactory;

use PhpOffice\PhpSpreadsheet\Comment;

use PhpOffice\PhpSpreadsheet\DefinedName;

use PhpOffice\PhpSpreadsheet\Exception;

use PhpOffice\PhpSpreadsheet\IComparable;

use PhpOffice\PhpSpreadsheet\ReferenceHelper;

use PhpOffice\PhpSpreadsheet\RichText\RichText;

use PhpOffice\PhpSpreadsheet\Shared;

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Style\Alignment;

use PhpOffice\PhpSpreadsheet\Style\Color;

use PhpOffice\PhpSpreadsheet\Style\Conditional;

use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

use PhpOffice\PhpSpreadsheet\Style\Style;

class Worksheet implements IComparable

{

    // Break types

    public const BREAK_NONE = 0;

    public const BREAK_ROW = 1;

    public const BREAK_COLUMN = 2;

    // Maximum column for row break

    public const BREAK_ROW_MAX_COLUMN = 16383;

    // Sheet state

    public const SHEETSTATE_VISIBLE = 'visible';

    public const SHEETSTATE_HIDDEN = 'hidden';

    public const SHEETSTATE_VERYHIDDEN = 'veryHidden';

    public const MERGE_CELL_CONTENT_EMPTY = 'empty';

    public const MERGE_CELL_CONTENT_HIDE = 'hide';

    public const MERGE_CELL_CONTENT_MERGE = 'merge';

    protected const SHEET_NAME_REQUIRES_NO_QUOTES = '/^[_\p{L}][_\p{L}\p{N}]*$/mui';

    /**

     * Maximum 31 characters allowed for sheet title.

     *

     * @var int

     */

    const SHEET_TITLE_MAXIMUM_LENGTH = 31;

    /**

     * Invalid characters in sheet title.

     *

     * @var array

     */

    private static $invalidCharacters = ['*', ':', '/', '\\', '?', '[', ']'];

    /**

     * Parent spreadsheet.

     *

     * @var ?Spreadsheet

     */

    private $parent;

    /**

     * Collection of cells.

     *

     * @var Cells

     */

    private $cellCollection;

    /**

     * Collection of row dimensions.

     *

     * @var RowDimension[]

     */

    private $rowDimensions = [];

    /**

     * Default row dimension.

     *

     * @var RowDimension

     */

    private $defaultRowDimension;

    /**

     * Collection of column dimensions.

     *

     * @var ColumnDimension[]

     */

    private $columnDimensions = [];

    /**

     * Default column dimension.

     *

     * @var ColumnDimension

     */

    private $defaultColumnDimension;

    /**

     * Collection of drawings.

     *

     * @var ArrayObject<int, BaseDrawing>

     */

    private $drawingCollection;

    /**

     * Collection of Chart objects.

     *

     * @var ArrayObject<int, Chart>

     */

    private $chartCollection;

    /**

     * Collection of Table objects.

     *

     * @var ArrayObject<int, Table>

     */

    private $tableCollection;

    /**

     * Worksheet title.

     *

     * @var string

     */

    private $title;

    /**

     * Sheet state.

     *

     * @var string

     */

    private $sheetState;

    /**

     * Page setup.

     *

     * @var PageSetup

     */

    private $pageSetup;

    /**

     * Page margins.

     *

     * @var PageMargins

     */

    private $pageMargins;

    /**

     * Page header/footer.

     *

     * @var HeaderFooter

     */

    private $headerFooter;

    /**

     * Sheet view.

     *

     * @var SheetView

     */

    private $sheetView;

    /**

     * Protection.

     *

     * @var Protection

     */

    private $protection;

    /**

     * Collection of styles.

     *

     * @var Style[]

     */

    private $styles = [];

    /**

     * Conditional styles. Indexed by cell coordinate, e.g. 'A1'.

     *

     * @var array

     */

    private $conditionalStylesCollection = [];

    /**

     * Collection of row breaks.

     *

     * @var PageBreak[]

     */

    private $rowBreaks = [];

    /**

     * Collection of column breaks.

     *

     * @var PageBreak[]

     */

    private $columnBreaks = [];

    /**

     * Collection of merged cell ranges.

     *

     * @var string[]

     */

    private $mergeCells = [];

    /**

     * Collection of protected cell ranges.

     *

     * @var string[]

     */

    private $protectedCells = [];

    /**

     * Autofilter Range and selection.

     *

     * @var AutoFilter

     */

    private $autoFilter;

    /**

     * Freeze pane.

     *

     * @var null|string

     */

    private $freezePane;

    /**

     * Default position of the right bottom pane.

     *

     * @var null|string

     */

    private $topLeftCell;

    /**

     * Show gridlines?

     *

     * @var bool

     */

    private $showGridlines = true;

    /**

     * Print gridlines?

     *

     * @var bool

     */

    private $printGridlines = false;

    /**

     * Show row and column headers?

     *

     * @var bool

     */

    private $showRowColHeaders = true;

    /**

     * Show summary below? (Row/Column outline).

     *

     * @var bool

     */

    private $showSummaryBelow = true;

    /**

     * Show summary right? (Row/Column outline).

     *

     * @var bool

     */

    private $showSummaryRight = true;

    /**

     * Collection of comments.

     *

     * @var Comment[]

     */

    private $comments = [];

    /**

     * Active cell. (Only one!).

     *

     * @var string

     */

    private $activeCell = 'A1';

    /**

     * Selected cells.

     *

     * @var string

     */

    private $selectedCells = 'A1';

    /**

     * Cached highest column.

     *

     * @var int

     */

    private $cachedHighestColumn = 1;

    /**

     * Cached highest row.

     *

     * @var int

     */

    private $cachedHighestRow = 1;

    /**

     * Right-to-left?

     *

     * @var bool

     */

    private $rightToLeft = false;

    /**

     * Hyperlinks. Indexed by cell coordinate, e.g. 'A1'.

     *

     * @var array

     */

    private $hyperlinkCollection = [];

    /**

     * Data validation objects. Indexed by cell coordinate, e.g. 'A1'.

     *

     * @var array

     */

    private $dataValidationCollection = [];

    /**

     * Tab color.

     *

     * @var null|Color

     */

    private $tabColor;

    /**

     * Dirty flag.

     *

     * @var bool

     */

    private $dirty = true;

    /**

     * Hash.

     *

     * @var string

     */

    private $hash;

    /**

     * CodeName.

     *

     * @var string

     */

    private $codeName;

    /**

     * Create a new worksheet.

     *

     * @param string $title

     */

    public function __construct(?Spreadsheet $parent = null, $title = 'Worksheet')

    {

        // Set parent and title

        $this->parent = $parent;

        $this->setTitle($title, false);

        // setTitle can change $pTitle

        $this->setCodeName($this->getTitle());

        $this->setSheetState(self::SHEETSTATE_VISIBLE);

        $this->cellCollection = CellsFactory::getInstance($this);

        // Set page setup

        $this->pageSetup = new PageSetup();

        // Set page margins

        $this->pageMargins = new PageMargins();

        // Set page header/footer

        $this->headerFooter = new HeaderFooter();

        // Set sheet view

        $this->sheetView = new SheetView();

        // Drawing collection

        $this->drawingCollection = new ArrayObject();

        // Chart collection

        $this->chartCollection = new ArrayObject();

        // Protection

        $this->protection = new Protection();

        // Default row dimension

        $this->defaultRowDimension = new RowDimension(null);

        // Default column dimension

        $this->defaultColumnDimension = new ColumnDimension(null);

        // AutoFilter

        $this->autoFilter = new AutoFilter('', $this);

        // Table collection

        $this->tableCollection = new ArrayObject();

    }

    /**

     * Disconnect all cells from this Worksheet object,

     * typically so that the worksheet object can be unset.

     */

    public function disconnectCells(): void

    {

        if ($this->cellCollection !== null) {

            $this->cellCollection->unsetWorksheetCells();

            // @phpstan-ignore-next-line

            $this->cellCollection = null;

        }

        //    detach ourself from the workbook, so that it can then delete this worksheet successfully

        $this->parent = null;

    }

    /**

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

     */

    public function __destruct()

    {

        Calculation::getInstance($this->parent)->clearCalculationCacheForWorksheet($this->title);

        $this->disconnectCells();

        $this->rowDimensions = [];

    }

    /**

     * Return the cell collection.

     *

     * @return Cells

     */

    public function getCellCollection()

    {

        return $this->cellCollection;

    }

    /**

     * Get array of invalid characters for sheet title.

     *

     * @return array

     */

    public static function getInvalidCharacters()

    {

        return self::$invalidCharacters;

    }

    /**

     * Check sheet code name for valid Excel syntax.

     *

     * @param string $sheetCodeName The string to check

     *

     * @return string The valid string

     */

    private static function checkSheetCodeName($sheetCodeName)

    {

        $charCount = Shared\StringHelper::countCharacters($sheetCodeName);

        if ($charCount == 0) {

            throw new Exception('Sheet code name cannot be empty.');

        }

        // Some of the printable ASCII characters are invalid:  * : / \ ? [ ] and  first and last characters cannot be a "'"

        if (

            (str_replace(self::$invalidCharacters, '', $sheetCodeName) !== $sheetCodeName) ||

            (Shared\StringHelper::substring($sheetCodeName, -1, 1) == '\'') ||

            (Shared\StringHelper::substring($sheetCodeName, 0, 1) == '\'')

        ) {

            throw new Exception('Invalid character found in sheet code name');

        }

        // Enforce maximum characters allowed for sheet title

        if ($charCount > self::SHEET_TITLE_MAXIMUM_LENGTH) {

            throw new Exception('Maximum ' . self::SHEET_TITLE_MAXIMUM_LENGTH . ' characters allowed in sheet code name.');

        }

        return $sheetCodeName;

    }

    /**

     * Check sheet title for valid Excel syntax.

     *

     * @param string $sheetTitle The string to check

     *

     * @return string The valid string

     */

    private static function checkSheetTitle($sheetTitle)

    {

        // Some of the printable ASCII characters are invalid:  * : / \ ? [ ]

        if (str_replace(self::$invalidCharacters, '', $sheetTitle) !== $sheetTitle) {

            throw new Exception('Invalid character found in sheet title');

        }

        // Enforce maximum characters allowed for sheet title

        if (Shared\StringHelper::countCharacters($sheetTitle) > self::SHEET_TITLE_MAXIMUM_LENGTH) {

            throw new Exception('Maximum ' . self::SHEET_TITLE_MAXIMUM_LENGTH . ' characters allowed in sheet title.');

        }

        return $sheetTitle;

    }

    /**

     * Get a sorted list of all cell coordinates currently held in the collection by row and column.

     *

     * @param bool $sorted Also sort the cell collection?

     *

     * @return string[]

     */

    public function getCoordinates($sorted = true)

    {

        if ($this->cellCollection == null) {

            return [];

        }

        if ($sorted) {

            return $this->cellCollection->getSortedCoordinates();

        }

        return $this->cellCollection->getCoordinates();

    }

    /**

     * Get collection of row dimensions.

     *

     * @return RowDimension[]

     */

    public function getRowDimensions()

    {

        return $this->rowDimensions;

    }

    /**

     * Get default row dimension.

     *

     * @return RowDimension

     */

    public function getDefaultRowDimension()

    {

        return $this->defaultRowDimension;

    }

    /**

     * Get collection of column dimensions.

     *

     * @return ColumnDimension[]

     */

    public function getColumnDimensions()

    {

        /** @var callable */

        $callable = [self::class, 'columnDimensionCompare'];

        uasort($this->columnDimensions, $callable);

        return $this->columnDimensions;

    }

    private static function columnDimensionCompare(ColumnDimension $a, ColumnDimension $b): int

    {

        return $a->getColumnNumeric() - $b->getColumnNumeric();

    }

    /**

     * Get default column dimension.

     *

     * @return ColumnDimension

     */

    public function getDefaultColumnDimension()

    {

        return $this->defaultColumnDimension;

    }

    /**

     * Get collection of drawings.

     *

     * @return ArrayObject<int, BaseDrawing>

     */

    public function getDrawingCollection()

    {

        return $this->drawingCollection;

    }

    /**

     * Get collection of charts.

     *

     * @return ArrayObject<int, Chart>

     */

    public function getChartCollection()

    {

        return $this->chartCollection;

    }

    /**

     * Add chart.

     *

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

     *

     * @return Chart

     */

    public function addChart(Chart $chart, $chartIndex = null)

    {

        $chart->setWorksheet($this);

        if ($chartIndex === null) {

            $this->chartCollection[] = $chart;

        } else {

            // Insert the chart at the requested index

            // @phpstan-ignore-next-line

            array_splice(/** @scrutinizer ignore-type */ $this->chartCollection, $chartIndex, 0, [$chart]);

        }

        return $chart;

    }

    /**

     * Return the count of charts on this worksheet.

     *

     * @return int The number of charts

     */

    public function getChartCount()

    {

        return count($this->chartCollection);

    }

    /**

     * Get a chart by its index position.

     *

     * @param ?string $index Chart index position

     *

     * @return Chart|false

     */

    public function getChartByIndex($index)

    {

        $chartCount = count($this->chartCollection);

        if ($chartCount == 0) {

            return false;

        }

        if ($index === null) {

            $index = --$chartCount;

        }

        if (!isset($this->chartCollection[$index])) {

            return false;

        }

        return $this->chartCollection[$index];

    }

    /**

     * Return an array of the names of charts on this worksheet.

     *

     * @return string[] The names of charts

     */

    public function getChartNames()

    {

        $chartNames = [];

        foreach ($this->chartCollection as $chart) {

            $chartNames[] = $chart->getName();

        }

        return $chartNames;

    }

    /**

     * Get a chart by name.

     *

     * @param string $chartName Chart name

     *

     * @return Chart|false

     */

    public function getChartByName($chartName)

    {

        foreach ($this->chartCollection as $index => $chart) {

            if ($chart->getName() == $chartName) {

                return $chart;

            }

        }

        return false;

    }

    /**

     * Refresh column dimensions.

     *

     * @return $this

     */

    public function refreshColumnDimensions()

    {

        $newColumnDimensions = [];

        foreach ($this->getColumnDimensions() as $objColumnDimension) {

            $newColumnDimensions[$objColumnDimension->getColumnIndex()] = $objColumnDimension;

        }

        $this->columnDimensions = $newColumnDimensions;

        return $this;

    }

    /**

     * Refresh row dimensions.

     *

     * @return $this

     */

    public function refreshRowDimensions()

    {

        $newRowDimensions = [];

        foreach ($this->getRowDimensions() as $objRowDimension) {

            $newRowDimensions[$objRowDimension->getRowIndex()] = $objRowDimension;

        }

        $this->rowDimensions = $newRowDimensions;

        return $this;

    }

    /**

     * Calculate worksheet dimension.

     *

     * @return string String containing the dimension of this worksheet

     */

    public function calculateWorksheetDimension()

    {

        // Return

        return 'A1:' . $this->getHighestColumn() . $this->getHighestRow();

    }

    /**

     * Calculate worksheet data dimension.

     *

     * @return string String containing the dimension of this worksheet that actually contain data

     */

    public function calculateWorksheetDataDimension()

    {

        // Return

        return 'A1:' . $this->getHighestDataColumn() . $this->getHighestDataRow();

    }

    /**

     * Calculate widths for auto-size columns.

     *

     * @return $this

     */

    public function calculateColumnWidths()

    {

        // initialize $autoSizes array

        $autoSizes = [];

        foreach ($this->getColumnDimensions() as $colDimension) {

            if ($colDimension->getAutoSize()) {

                $autoSizes[$colDimension->getColumnIndex()] = -1;

            }

        }

        // There is only something to do if there are some auto-size columns

        if (!empty($autoSizes)) {

            // build list of cells references that participate in a merge

            $isMergeCell = [];

            foreach ($this->getMergeCells() as $cells) {

                foreach (Coordinate::extractAllCellReferencesInRange($cells) as $cellReference) {

                    $isMergeCell[$cellReference] = true;

                }

            }

            $autoFilterIndentRanges = (new AutoFit($this))->getAutoFilterIndentRanges();

            // loop through all cells in the worksheet

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

                $cell = $this->getCellOrNull($coordinate);

                if ($cell !== null && isset($autoSizes[$this->cellCollection->getCurrentColumn()])) {

                    //Determine if cell is in merge range

                    $isMerged = isset($isMergeCell[$this->cellCollection->getCurrentCoordinate()]);

                    //By default merged cells should be ignored

                    $isMergedButProceed = false;

                    //The only exception is if it's a merge range value cell of a 'vertical' range (1 column wide)

                    if ($isMerged && $cell->isMergeRangeValueCell()) {

                        $range = (string) $cell->getMergeRange();

                        $rangeBoundaries = Coordinate::rangeDimension($range);

                        if ($rangeBoundaries[0] === 1) {

                            $isMergedButProceed = true;

                        }

                    }

                    // Determine width if cell is not part of a merge or does and is a value cell of 1-column wide range

                    if (!$isMerged || $isMergedButProceed) {

                        // Determine if we need to make an adjustment for the first row in an AutoFilter range that

                        //    has a column filter dropdown

                        $filterAdjustment = false;

                        if (!empty($autoFilterIndentRanges)) {

                            foreach ($autoFilterIndentRanges as $autoFilterFirstRowRange) {

                                if ($cell->isInRange($autoFilterFirstRowRange)) {

                                    $filterAdjustment = true;

                                    break;

                                }

                            }

                        }

                        $indentAdjustment = $cell->getStyle()->getAlignment()->getIndent();

                        $indentAdjustment += (int) ($cell->getStyle()->getAlignment()->getHorizontal() === Alignment::HORIZONTAL_CENTER);

                        // Calculated value

                        // To formatted string

                        $cellValue = NumberFormat::toFormattedString(

                            $cell->getCalculatedValue(),

                            (string) $this->getParentOrThrow()->getCellXfByIndex($cell->getXfIndex())

                                ->getNumberFormat()->getFormatCode()

                        );

                        if ($cellValue !== null && $cellValue !== '') {

                            $autoSizes[$this->cellCollection->getCurrentColumn()] = max(

                                $autoSizes[$this->cellCollection->getCurrentColumn()],

                                round(

                                    Shared\Font::calculateColumnWidth(

                                        $this->getParentOrThrow()->getCellXfByIndex($cell->getXfIndex())->getFont(),

                                        $cellValue,

                                        (int) $this->getParentOrThrow()->getCellXfByIndex($cell->getXfIndex())

                                            ->getAlignment()->getTextRotation(),

                                        $this->getParentOrThrow()->getDefaultStyle()->getFont(),

                                        $filterAdjustment,

                                        $indentAdjustment

                                    ),

                                    3

                                )

                            );

                        }

                    }

                }

            }

            // adjust column widths

            foreach ($autoSizes as $columnIndex => $width) {

                if ($width == -1) {

                    $width = $this->getDefaultColumnDimension()->getWidth();

                }

                $this->getColumnDimension($columnIndex)->setWidth($width);

            }

        }

        return $this;

    }

    /**

     * Get parent or null.

     */

    public function getParent(): ?Spreadsheet

    {

        return $this->parent;

    }

    /**

     * Get parent, throw exception if null.

     */

    public function getParentOrThrow(): Spreadsheet

    {

        if ($this->parent !== null) {

            return $this->parent;

        }

        throw new Exception('Sheet does not have a parent.');

    }

    /**

     * Re-bind parent.

     *

     * @return $this

     */

    public function rebindParent(Spreadsheet $parent)

    {

        if ($this->parent !== null) {

            $definedNames = $this->parent->getDefinedNames();

            foreach ($definedNames as $definedName) {

                $parent->addDefinedName($definedName);

            }

            $this->parent->removeSheetByIndex(

                $this->parent->getIndex($this)

            );

        }

        $this->parent = $parent;

        return $this;

    }

    /**

     * Get title.

     *

     * @return string

     */

    public function getTitle()

    {

        return $this->title;

    }

    /**

     * Set title.

     *

     * @param string $title String containing the dimension of this worksheet

     * @param bool $updateFormulaCellReferences Flag indicating whether cell references in formulae should

     *            be updated to reflect the new sheet name.

     *          This should be left as the default true, unless you are

     *          certain that no formula cells on any worksheet contain

     *          references to this worksheet

     * @param bool $validate False to skip validation of new title. WARNING: This should only be set

     *                       at parse time (by Readers), where titles can be assumed to be valid.

     *

     * @return $this

     */

    public function setTitle($title, $updateFormulaCellReferences = true, $validate = true)

    {

        // Is this a 'rename' or not?

        if ($this->getTitle() == $title) {

            return $this;

        }

        // Old title

        $oldTitle = $this->getTitle();

        if ($validate) {

            // Syntax check

            self::checkSheetTitle($title);

            if ($this->parent) {

                // Is there already such sheet name?

                if ($this->parent->sheetNameExists($title)) {

                    // Use name, but append with lowest possible integer

                    if (Shared\StringHelper::countCharacters($title) > 29) {

                        $title = Shared\StringHelper::substring($title, 0, 29);

                    }

                    $i = 1;

                    while ($this->parent->sheetNameExists($title . ' ' . $i)) {

                        ++$i;

                        if ($i == 10) {

                            if (Shared\StringHelper::countCharacters($title) > 28) {

                                $title = Shared\StringHelper::substring($title, 0, 28);

                            }

                        } elseif ($i == 100) {

                            if (Shared\StringHelper::countCharacters($title) > 27) {

                                $title = Shared\StringHelper::substring($title, 0, 27);

                            }

                        }

                    }

                    $title .= " $i";

                }

            }

        }

        // Set title

        $this->title = $title;

        $this->dirty = true;

        if ($this->parent && $this->parent->getCalculationEngine()) {

            // New title

            $newTitle = $this->getTitle();

            $this->parent->getCalculationEngine()

                ->renameCalculationCacheForWorksheet($oldTitle, $newTitle);

            if ($updateFormulaCellReferences) {

                ReferenceHelper::getInstance()->updateNamedFormulae($this->parent, $oldTitle, $newTitle);

            }

        }

        return $this;

    }

    /**

     * Get sheet state.

     *

     * @return string Sheet state (visible, hidden, veryHidden)

     */

    public function getSheetState()

    {

        return $this->sheetState;

    }

    /**

     * Set sheet state.

     *

     * @param string $value Sheet state (visible, hidden, veryHidden)

     *

     * @return $this

     */

    public function setSheetState($value)

    {

        $this->sheetState = $value;

        return $this;

    }

    /**

     * Get page setup.

     *

     * @return PageSetup

     */

    public function getPageSetup()

    {

        return $this->pageSetup;

    }

    /**

     * Set page setup.

     *

     * @return $this

     */

    public function setPageSetup(PageSetup $pageSetup)

    {

        $this->pageSetup = $pageSetup;