seaBlueApi/Util/PHPExcel/PHPExcel.Class.php

<?php
/**
 * Created by PhpStorm.
 * User: phperstar
 * Date: 2020/8/10
 * Time: 5:10 PM
 */
namespace Util\PHPExcel;

use Util\PHPExcel\Style;
use Util\PHPExcel\Worksheet;
use Util\PHPExcel\Calculation;
use Util\PHPExcel\NamedRange;
use Util\PHPExcel\DocumentProperties;

class PHPExcel
{
    /**
     * Unique ID
     *
     * @var string
     */
    private $uniqueID;

    /**
     * Collection of Worksheet objects
     *
     * @var Worksheet[]
     */
    private $workSheetCollection = array();

    /**
     * CellStyleXf collection
     *
     * @var Style[]
     */
    private $cellStyleXfCollection = array();

    /**
     * CellXf collection
     *
     * @var Style[]
     */
    private $cellXfCollection = array();

    /**
     * Active sheet index
     *
     * @var integer
     */
    private $activeSheetIndex = 0;

    /**
     * Calculation Engine
     *
     * @var Calculation
     */
    private $calculationEngine;

    /**
     * Named ranges
     *
     * @var NamedRanges[]
     */
    private $namedRanges = array();

    /**
     * Document properties
     *
     * @var DocumentProperties
     */
    private $properties;

    /**
     * Create a new PHPExcel with one Worksheet
     */
    public function __construct()
    {
        $this->uniqueID = uniqid();
        $this->calculationEngine = new Calculation($this);

        // Initialise worksheet collection and add one worksheet
        $this->workSheetCollection = array();
        $this->workSheetCollection[] = new Worksheet($this);
        $this->activeSheetIndex = 0;

        // Set named ranges
        $this->namedRanges = array();


        // Create document properties
        $this->properties = new DocumentProperties();

        /*
              // Create document security
              $this->security = new PHPExcel_DocumentSecurity();


              // Create the cellXf supervisor
              $this->cellXfSupervisor = new PHPExcel_Style(true);
              $this->cellXfSupervisor->bindParent($this); */

        // Create the default style
        $this->addCellXf(new Style);
        $this->addCellStyleXf(new Style);
    }

    /**
     * Add a cellXf to the workbook
     *
     * @param PHPExcel_Style $style
     */
    public function addCellXf(Style $style)
    {
        $this->cellXfCollection[] = $style;
        $style->setIndex(count($this->cellXfCollection) - 1);
    }

    /**
     * Add a cellStyleXf to the workbook
     *
     * @param PHPExcel_Style $pStyle
     */
    public function addCellStyleXf(Style $pStyle)
    {
        $this->cellStyleXfCollection[] = $pStyle;
        $pStyle->setIndex(count($this->cellStyleXfCollection) - 1);
    }

    /**
     * Remove sheet by index
     *
     * @param  int $pIndex Active sheet index
     * @throws Exception
     */
    public function removeSheetByIndex($pIndex = 0)
    {

        $numSheets = count($this->workSheetCollection);
        if ($pIndex > $numSheets - 1) {
            throw new \Exception(
                "You tried to remove a sheet by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
            );
        } else {
            array_splice($this->workSheetCollection, $pIndex, 1);
        }
        // Adjust active sheet index if necessary
        if (($this->activeSheetIndex >= $pIndex) &&
            ($pIndex > count($this->workSheetCollection) - 1)) {
            --$this->activeSheetIndex;
        }

    }

    /**
     * Remove cellStyleXf by index
     *
     * @param integer $pIndex Index to cellXf
     * @throws Exception
     */
    public function removeCellStyleXfByIndex($pIndex = 0)
    {
        if ($pIndex > count($this->cellStyleXfCollection) - 1) {
            throw new \Exception("CellStyleXf index is out of bounds.");
        } else {
            array_splice($this->cellStyleXfCollection, $pIndex, 1);
        }
    }

    /**
     * Remove cellXf by index. It is ensured that all cells get their xf index updated.
     *
     * @param integer $pIndex Index to cellXf
     */
    public function removeCellXfByIndex($pIndex = 0)
    {
        if ($pIndex > count($this->cellXfCollection) - 1) {
            throw new \Exception("CellXf index is out of bounds.");
        } else {
            // first remove the cellXf
            array_splice($this->cellXfCollection, $pIndex, 1);

            // then update cellXf indexes for cells
            foreach ($this->workSheetCollection as $worksheet) {
                foreach ($worksheet->getCellCollection(false) as $cellID) {
                    $cell = $worksheet->getCell($cellID);
                    $xfIndex = $cell->getXfIndex();
                    if ($xfIndex > $pIndex) {
                        // decrease xf index by 1
                        $cell->setXfIndex($xfIndex - 1);
                    } elseif ($xfIndex == $pIndex) {
                        // set to default xf index 0
                        $cell->setXfIndex(0);
                    }
                }
            }
        }
    }

    /**
     * Create sheet and add it to this workbook
     *
     * @param  int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
     * @return PHPExcel_Worksheet
     * @throws PHPExcel_Exception
     */
    public function createSheet($iSheetIndex = null)
    {
        $newSheet = new Worksheet($this);
        $this->addSheet($newSheet, $iSheetIndex);
        return $newSheet;
    }

    /**
     * Add sheet
     *
     * @param  Worksheet $pSheet
     * @param  int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
     * @return Worksheet
     * @throws
     */
    public function addSheet(Worksheet $pSheet, $iSheetIndex = null)
    {
        if ($this->sheetNameExists($pSheet->getTitle())) {
            throw new \Exception(
                "Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename this worksheet first."
            );
        }

        if ($iSheetIndex === null) {
            if ($this->activeSheetIndex < 0) {
                $this->activeSheetIndex = 0;
            }
            $this->workSheetCollection[] = $pSheet;
        } else {
            // Insert the sheet at the requested index
            array_splice(
                $this->workSheetCollection,
                $iSheetIndex,
                0,
                array($pSheet)
            );

            // Adjust active sheet index if necessary
            if ($this->activeSheetIndex >= $iSheetIndex) {
                ++$this->activeSheetIndex;
            }
        }

        if ($pSheet->getParent() === null) {
            $pSheet->rebindParent($this);
        }

        return $pSheet;
    }

    /**
     * Check if a sheet with a specified name already exists
     *
     * @param  string $pSheetName  Name of the worksheet to check
     * @return boolean
     */
    public function sheetNameExists($pSheetName)
    {
        return ($this->getSheetByName($pSheetName) !== null);
    }

    /**
     * Get sheet by name
     *
     * @param  string $pName Sheet name
     * @return PHPExcel_Worksheet
     */
    public function getSheetByName($pName = '')
    {
        $worksheetCount = count($this->workSheetCollection);
        for ($i = 0; $i < $worksheetCount; ++$i) {
            if ($this->workSheetCollection[$i]->getTitle() === $pName) {
                return $this->workSheetCollection[$i];
            }
        }

        return null;
    }

    /**
     * Return the calculation engine for this worksheet
     *
     * @return PHPExcel_Calculation
     */
    public function getCalculationEngine()
    {
        return $this->calculationEngine;
    }

    /**
     * Code to execute when this worksheet is unset()
     *
     */
    public function __destruct()
    {
        $this->calculationEngine = null;
        $this->disconnectWorksheets();
    }

    /**
     * Disconnect all worksheets from this PHPExcel workbook object,
     *    typically so that the PHPExcel object can be unset
     *
     */
    public function disconnectWorksheets()
    {
        $worksheet = null;
        foreach ($this->workSheetCollection as $k => &$worksheet) {
            $worksheet->disconnectCells();
            $this->workSheetCollection[$k] = null;
        }
        unset($worksheet);
        $this->workSheetCollection = array();
    }

    /**
     * Get named ranges
     *
     * @return PHPExcel_NamedRange[]
     */
    public function getNamedRanges()
    {
        return $this->namedRanges;
    }

    /**
     * Get named range
     *
     * @param  string $namedRange
     * @param  Worksheet|null $pSheet Scope. Use null for global scope
     * @return NamedRange|null
     */
    public function getNamedRange($namedRange, Worksheet $pSheet = null)
    {
        $returnValue = null;

        if ($namedRange != '' && ($namedRange !== null)) {
            // first look for global defined name
            if (isset($this->namedRanges[$namedRange])) {
                $returnValue = $this->namedRanges[$namedRange];
            }

            // then look for local defined name (has priority over global defined name if both names exist)
            if (($pSheet !== null) && isset($this->namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
                $returnValue = $this->namedRanges[$pSheet->getTitle() . '!' . $namedRange];
            }
        }

        return $returnValue;
    }

    /**
     * Add named range
     *
     * @param  PHPExcel_NamedRange $namedRange
     * @return boolean
     */
    public function addNamedRange(NamedRange $namedRange)
    {
        if ($namedRange->getScope() == null) {
            // global scope
            $this->namedRanges[$namedRange->getName()] = $namedRange;
        } else {
            // local scope
            $this->namedRanges[$namedRange->getScope()->getTitle().'!'.$namedRange->getName()] = $namedRange;
        }
        return true;
    }

    /**
     * Get index for sheet
     *
     * @param  Worksheet $pSheet
     * @return int Sheet index
     * @throws
     */
    public function getIndex(Worksheet $pSheet)
    {
        foreach ($this->workSheetCollection as $key => $value) {
            if ($value->getHashCode() == $pSheet->getHashCode()) {
                return $key;
            }
        }

        throw new \Exception("Sheet does not exist.");
    }

    /**
     * Check if a sheet with a specified code name already exists
     *
     * @param string $pSheetCodeName  Name of the worksheet to check
     * @return boolean
     */
    public function sheetCodeNameExists($pSheetCodeName)
    {
        return ($this->getSheetByCodeName($pSheetCodeName) !== null);
    }

    /**
     * Get sheet by code name. Warning : sheet don't have always a code name !
     *
     * @param string $pName Sheet name
     * @return PHPExcel_Worksheet
     */
    public function getSheetByCodeName($pName = '')
    {
        $worksheetCount = count($this->workSheetCollection);
        for ($i = 0; $i < $worksheetCount; ++$i) {
            if ($this->workSheetCollection[$i]->getCodeName() == $pName) {
                return $this->workSheetCollection[$i];
            }
        }

        return null;
    }

    /**
     * Get properties
     *
     * @return PHPExcel_DocumentProperties
     */
    public function getProperties()
    {
        return $this->properties;
    }

    /**
     * Set properties
     *
     * @param PHPExcel_DocumentProperties    $pValue
     */
    public function setProperties(DocumentProperties $pValue)
    {
        $this->properties = $pValue;
    }

    /**
     * 获取指定工作表
     *
     * @param  int $pIndex Sheet index
     * @return Worksheet
     * @throws
     */
    public function getSheet($pIndex = 0)
    {
        if (!isset($this->workSheetCollection[$pIndex])) {
            $numSheets = $this->getSheetCount();
            throw new \Exception(
                "Your requested sheet index: {$pIndex} is out of bounds. The actual number of sheets is {$numSheets}."
            );
        }

        return $this->workSheetCollection[$pIndex];
    }

    /**
     * Get sheet count
     *
     * @return int
     */
    public function getSheetCount()
    {
        return count($this->workSheetCollection);
    }

    /**
     * 指定激活状态的工作表
     *
     * @param  int $pIndex Active sheet index
     * @throws
     * @return PHPExcel_Worksheet
     */
    public function setActiveSheetIndex($pIndex = 0)
    {
        $numSheets = count($this->workSheetCollection);

        if ($pIndex > $numSheets - 1) {
            throw new \Exception(
                "You tried to set a sheet active by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
            );
        } else {
            $this->activeSheetIndex = $pIndex;
        }
        return $this->getActiveSheet();
    }

    /**
     * 获取激活状态工作表
     *
     * @return Worksheet
     *
     * @throws
     */
    public function getActiveSheet()
    {
        return $this->getSheet($this->activeSheetIndex);
    }

}