Subversion Repositories eFlore/Applications.cel

<?php

/**

 * PHPExcel

 *

 * Copyright (c) 2006 - 2013 PHPExcel

 *

 * This library is free software; you can redistribute it and/or

 * modify it under the terms of the GNU Lesser General Public

 * License as published by the Free Software Foundation; either

 * version 2.1 of the License, or (at your option) any later version.

 *

 * This library is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU

 * Lesser General Public License for more details.

 *

 * You should have received a copy of the GNU Lesser General Public

 * License along with this library; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA

 *

 * @category   PHPExcel

 * @package    PHPExcel

 * @copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)

 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL

 * @version    ##VERSION##, ##DATE##

 */

/** PHPExcel root directory */

if (!defined('PHPEXCEL_ROOT')) {

    define('PHPEXCEL_ROOT', dirname(__FILE__) . '/');

    require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');

}

/**

 * PHPExcel

 *

 * @category   PHPExcel

 * @package    PHPExcel

 * @copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)

 */

class PHPExcel

{

    /**

     * Unique ID

     *

     * @var string

     */

    private $_uniqueID;

    /**

     * Document properties

     *

     * @var PHPExcel_DocumentProperties

     */

    private $_properties;

    /**

     * Document security

     *

     * @var PHPExcel_DocumentSecurity

     */

    private $_security;

    /**

     * Collection of Worksheet objects

     *

     * @var PHPExcel_Worksheet[]

     */

    private $_workSheetCollection = array();

    /**

	 * Calculation Engine

	 *

	 * @var PHPExcel_Calculation

	 */

	private $_calculationEngine = NULL;

    /**

     * Active sheet index

     *

     * @var int

     */

    private $_activeSheetIndex = 0;

    /**

     * Named ranges

     *

     * @var PHPExcel_NamedRange[]

     */

    private $_namedRanges = array();

    /**

     * CellXf supervisor

     *

     * @var PHPExcel_Style

     */

    private $_cellXfSupervisor;

    /**

     * CellXf collection

     *

     * @var PHPExcel_Style[]

     */

    private $_cellXfCollection = array();

    /**

     * CellStyleXf collection

     *

     * @var PHPExcel_Style[]

     */

    private $_cellStyleXfCollection = array();

    /**

     * Create a new PHPExcel with one Worksheet

     */

    public function __construct()

    {

		$this->_uniqueID = uniqid();

		$this->_calculationEngine	= PHPExcel_Calculation::getInstance($this);

        // Initialise worksheet collection and add one worksheet

        $this->_workSheetCollection = array();

        $this->_workSheetCollection[] = new PHPExcel_Worksheet($this);

        $this->_activeSheetIndex = 0;

        // Create document properties

        $this->_properties = new PHPExcel_DocumentProperties();

        // Create document security

        $this->_security = new PHPExcel_DocumentSecurity();

        // Set named ranges

        $this->_namedRanges = array();

        // Create the cellXf supervisor

        $this->_cellXfSupervisor = new PHPExcel_Style(true);

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

        // Create the default style

        $this->addCellXf(new PHPExcel_Style);

        $this->addCellStyleXf(new PHPExcel_Style);

    }

    /**

     * Code to execute when this worksheet is unset()

     *

     */

    public function __destruct() {

        PHPExcel_Calculation::unsetInstance($this);

        $this->disconnectWorksheets();

    }    //    function __destruct()

    /**

     * 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();

    }

	/**

	 * Return the calculation engine for this worksheet

	 *

	 * @return PHPExcel_Calculation

	 */

	public function getCalculationEngine()

	{

		return $this->_calculationEngine;

	}	//	function getCellCacheController()

    /**

     * Get properties

     *

     * @return PHPExcel_DocumentProperties

     */

    public function getProperties()

    {

        return $this->_properties;

    }

    /**

     * Set properties

     *

     * @param PHPExcel_DocumentProperties    $pValue

     */

    public function setProperties(PHPExcel_DocumentProperties $pValue)

    {

        $this->_properties = $pValue;

    }

    /**

     * Get security

     *

     * @return PHPExcel_DocumentSecurity

     */

    public function getSecurity()

    {

        return $this->_security;

    }

    /**

     * Set security

     *

     * @param PHPExcel_DocumentSecurity    $pValue

     */

    public function setSecurity(PHPExcel_DocumentSecurity $pValue)

    {

        $this->_security = $pValue;

    }

    /**

     * Get active sheet

     *

     * @return PHPExcel_Worksheet

     */

    public function getActiveSheet()

    {

        return $this->_workSheetCollection[$this->_activeSheetIndex];

    }

    /**

     * 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 PHPExcel_Worksheet($this);

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

        return $newSheet;

    }

    /**

     * 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);

    }

    /**

     * Add sheet

     *

     * @param  PHPExcel_Worksheet $pSheet

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

     * @return PHPExcel_Worksheet

     * @throws PHPExcel_Exception

     */

    public function addSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = NULL)

    {

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

            throw new PHPExcel_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;

            }

        }

        return $pSheet;

    }

    /**

     * Remove sheet by index

     *

     * @param  int $pIndex Active sheet index

     * @throws PHPExcel_Exception

     */

    public function removeSheetByIndex($pIndex = 0)

    {

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

        if ($pIndex > $numSheets - 1) {

            throw new PHPExcel_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;

        }

    }

    /**

     * Get sheet by index

     *

     * @param  int $pIndex Sheet index

     * @return PHPExcel_Worksheet

     * @throws PHPExcel_Exception

     */

    public function getSheet($pIndex = 0)

    {

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

        if ($pIndex > $numSheets - 1) {

            throw new PHPExcel_Exception(

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

           	);

        } else {

            return $this->_workSheetCollection[$pIndex];

        }

    }

    /**

     * Get all sheets

     *

     * @return PHPExcel_Worksheet[]

     */

    public function getAllSheets()

    {

        return $this->_workSheetCollection;

    }

    /**

     * 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;

    }

    /**

     * Get index for sheet

     *

     * @param  PHPExcel_Worksheet $pSheet

     * @return Sheet index

     * @throws PHPExcel_Exception

     */

    public function getIndex(PHPExcel_Worksheet $pSheet)

    {

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

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

                return $key;

            }

        }

        throw new PHPExcel_Exception("Sheet does not exist.");

    }

    /**

     * Set index for sheet by sheet name.

     *

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

     * @param  int $newIndex New index for the sheet

     * @return New sheet index

     * @throws PHPExcel_Exception

     */

    public function setIndexByName($sheetName, $newIndex)

    {

        $oldIndex = $this->getIndex($this->getSheetByName($sheetName));

        $pSheet = array_splice(

            $this->_workSheetCollection,

            $oldIndex,

            1

        );

        array_splice(

            $this->_workSheetCollection,

            $newIndex,

            0,

            $pSheet

        );

        return $newIndex;

    }

    /**

     * 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 $pIndex Active sheet index

     * @throws PHPExcel_Exception

     * @return PHPExcel_Worksheet

     */

    public function setActiveSheetIndex($pIndex = 0)

    {

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

        if ($pIndex > $numSheets - 1) {

            throw new PHPExcel_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();

    }

    /**

     * Set active sheet index by name

     *

     * @param  string $pValue Sheet title

     * @return PHPExcel_Worksheet

     * @throws PHPExcel_Exception

     */

    public function setActiveSheetIndexByName($pValue = '')

    {

        if (($worksheet = $this->getSheetByName($pValue)) instanceof PHPExcel_Worksheet) {

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

            return $worksheet;

        }

        throw new PHPExcel_Exception('Workbook does not contain sheet:' . $pValue);

    }

    /**

     * Get sheet names

     *

     * @return string[]

     */

    public function getSheetNames()

    {

        $returnValue = array();

        $worksheetCount = $this->getSheetCount();

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

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

        }

        return $returnValue;

    }

    /**

     * Add external sheet

     *

     * @param  PHPExcel_Worksheet $pSheet External sheet to add

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

     * @throws PHPExcel_Exception

     * @return PHPExcel_Worksheet

     */

    public function addExternalSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = null) {

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

            throw new PHPExcel_Exception("Workbook already contains a worksheet named '{$pSheet->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 ($pSheet->getParent()->getCellXfCollection() as $cellXf) {

            $this->addCellXf(clone $cellXf);

        }

        // move sheet to this workbook

        $pSheet->rebindParent($this);

        // update the cellXfs

        foreach ($pSheet->getCellCollection(false) as $cellID) {

            $cell = $pSheet->getCell($cellID);

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

        }

        return $this->addSheet($pSheet, $iSheetIndex);

    }

    /**

     * Get named ranges

     *

     * @return PHPExcel_NamedRange[]

     */

    public function getNamedRanges() {

        return $this->_namedRanges;

    }

    /**

     * Add named range

     *

     * @param  PHPExcel_NamedRange $namedRange

     * @return PHPExcel

     */

    public function addNamedRange(PHPExcel_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 named range

     *

     * @param  string $namedRange

     * @param  PHPExcel_Worksheet|null $pSheet Scope. Use null for global scope

     * @return PHPExcel_NamedRange|null

     */

    public function getNamedRange($namedRange, PHPExcel_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;

    }

    /**

     * Remove named range

     *

     * @param  string  $namedRange

     * @param  PHPExcel_Worksheet|null  $pSheet  Scope: use null for global scope.

     * @return PHPExcel

     */

    public function removeNamedRange($namedRange, PHPExcel_Worksheet $pSheet = null) {

        if ($pSheet === NULL) {

            if (isset($this->_namedRanges[$namedRange])) {

                unset($this->_namedRanges[$namedRange]);

            }

        } else {

            if (isset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {

                unset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange]);

            }

        }

        return $this;

    }

    /**

     * Get worksheet iterator

     *

     * @return PHPExcel_WorksheetIterator

     */

    public function getWorksheetIterator() {

        return new PHPExcel_WorksheetIterator($this);

    }

    /**

     * Copy workbook (!= clone!)

     *

     * @return PHPExcel

     */

    public function copy() {

        $copied = clone $this;

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

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

            $this->_workSheetCollection[$i] = $this->_workSheetCollection[$i]->copy();

            $this->_workSheetCollection[$i]->rebindParent($this);

        }

        return $copied;

    }

    /**

     * Implement PHP __clone to create a deep clone, not just a shallow copy.

     */

    public function __clone() {

        foreach($this as $key => $val) {

            if (is_object($val) || (is_array($val))) {

                $this->{$key} = unserialize(serialize($val));

            }

        }

    }

    /**

     * Get the workbook collection of cellXfs

     *

     * @return PHPExcel_Style[]

     */

    public function getCellXfCollection()

    {

        return $this->_cellXfCollection;

    }

    /**

     * Get cellXf by index

     *

     * @param  int $pIndex

     * @return PHPExcel_Style

     */

    public function getCellXfByIndex($pIndex = 0)

    {

        return $this->_cellXfCollection[$pIndex];

    }

    /**

     * Get cellXf by hash code

     *

     * @param  string $pValue

     * @return PHPExcel_Style|false

     */

    public function getCellXfByHashCode($pValue = '')

    {

        foreach ($this->_cellXfCollection as $cellXf) {

            if ($cellXf->getHashCode() == $pValue) {

                return $cellXf;

            }

        }

        return false;

    }

    /**

     * Check if style exists in style collection

     *

     * @param  PHPExcel_Style $pCellStyle

     * @return boolean

     */

    public function cellXfExists($pCellStyle = null)

    {

        return in_array($pCellStyle, $this->_cellXfCollection, true);

    }

    /**

     * Get default style

     *

     * @return PHPExcel_Style

     * @throws PHPExcel_Exception

     */

    public function getDefaultStyle()

    {

        if (isset($this->_cellXfCollection[0])) {

            return $this->_cellXfCollection[0];

        }

        throw new PHPExcel_Exception('No default style found for this workbook');

    }

    /**

     * Add a cellXf to the workbook

     *

     * @param PHPExcel_Style $style

     */

    public function addCellXf(PHPExcel_Style $style)

    {

        $this->_cellXfCollection[] = $style;

        $style->setIndex(count($this->_cellXfCollection) - 1);

    }

    /**

     * Remove cellXf by index. It is ensured that all cells get their xf index updated.

     *

     * @param  int $pIndex Index to cellXf

     * @throws PHPExcel_Exception

     */

    public function removeCellXfByIndex($pIndex = 0)

    {

        if ($pIndex > count($this->_cellXfCollection) - 1) {

            throw new PHPExcel_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);

                    } else if ($xfIndex == $pIndex) {

                        // set to default xf index 0

                        $cell->setXfIndex(0);

                    }

                }

            }

        }

    }

    /**

     * Get the cellXf supervisor

     *

     * @return PHPExcel_Style

     */

    public function getCellXfSupervisor()

    {

        return $this->_cellXfSupervisor;

    }

    /**

     * Get the workbook collection of cellStyleXfs

     *

     * @return PHPExcel_Style[]

     */

    public function getCellStyleXfCollection()

    {

        return $this->_cellStyleXfCollection;

    }

    /**

     * Get cellStyleXf by index

     *

     * @param  int $pIndex

     * @return PHPExcel_Style

     */

    public function getCellStyleXfByIndex($pIndex = 0)

    {

        return $this->_cellStyleXfCollection[$pIndex];

    }

    /**

     * Get cellStyleXf by hash code

     *

     * @param  string $pValue

     * @return PHPExcel_Style|false

     */

    public function getCellStyleXfByHashCode($pValue = '')

    {

        foreach ($this->_cellXfStyleCollection as $cellStyleXf) {

            if ($cellStyleXf->getHashCode() == $pValue) {

                return $cellStyleXf;

            }

        }

        return false;

    }

    /**

     * Add a cellStyleXf to the workbook

     *

     * @param PHPExcel_Style $pStyle

     */

    public function addCellStyleXf(PHPExcel_Style $pStyle)

    {

        $this->_cellStyleXfCollection[] = $pStyle;

        $pStyle->setIndex(count($this->_cellStyleXfCollection) - 1);

    }

    /**

     * Remove cellStyleXf by index

     *

     * @param int $pIndex

     * @throws PHPExcel_Exception

     */

    public function removeCellStyleXfByIndex($pIndex = 0)

    {

        if ($pIndex > count($this->_cellStyleXfCollection) - 1) {

            throw new PHPExcel_Exception("CellStyleXf index is out of bounds.");

        } else {

            array_splice($this->_cellStyleXfCollection, $pIndex, 1);

        }

    }

    /**

     * Eliminate all unneeded cellXf and afterwards update the xfIndex for all cells

     * and columns in the workbook

     */

    public function garbageCollect()

    {

        // how many references are there to each cellXf ?

        $countReferencesCellXf = array();

        foreach ($this->_cellXfCollection as $index => $cellXf) {

            $countReferencesCellXf[$index] = 0;

        }

        foreach ($this->getWorksheetIterator() as $sheet) {

            // from cells

            foreach ($sheet->getCellCollection(false) as $cellID) {

                $cell = $sheet->getCell($cellID);

                ++$countReferencesCellXf[$cell->getXfIndex()];

            }

            // from row dimensions

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

                if ($rowDimension->getXfIndex() !== null) {

                    ++$countReferencesCellXf[$rowDimension->getXfIndex()];

                }

            }

            // from column dimensions

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

                ++$countReferencesCellXf[$columnDimension->getXfIndex()];

            }

        }

        // remove cellXfs without references and create mapping so we can update xfIndex

        // for all cells and columns

        $countNeededCellXfs = 0;

        foreach ($this->_cellXfCollection as $index => $cellXf) {

            if ($countReferencesCellXf[$index] > 0 || $index == 0) { // we must never remove the first cellXf

                ++$countNeededCellXfs;

            } else {

                unset($this->_cellXfCollection[$index]);

            }

            $map[$index] = $countNeededCellXfs - 1;

        }

        $this->_cellXfCollection = array_values($this->_cellXfCollection);

        // update the index for all cellXfs

        foreach ($this->_cellXfCollection as $i => $cellXf) {

            $cellXf->setIndex($i);

        }

        // make sure there is always at least one cellXf (there should be)

        if (empty($this->_cellXfCollection)) {

            $this->_cellXfCollection[] = new PHPExcel_Style();

        }

        // update the xfIndex for all cells, row dimensions, column dimensions

        foreach ($this->getWorksheetIterator() as $sheet) {

            // for all cells

            foreach ($sheet->getCellCollection(false) as $cellID) {

                $cell = $sheet->getCell($cellID);

                $cell->setXfIndex( $map[$cell->getXfIndex()] );

            }

            // for all row dimensions

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

                if ($rowDimension->getXfIndex() !== null) {

                    $rowDimension->setXfIndex( $map[$rowDimension->getXfIndex()] );

                }

            }

            // for all column dimensions

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

                $columnDimension->setXfIndex( $map[$columnDimension->getXfIndex()] );

            }

			// also do garbage collection for all the sheets

            $sheet->garbageCollect();

        }

    }

    /**

     * Return the unique ID value assigned to this spreadsheet workbook

     *

     * @return string

     */

    public function getID() {

        return $this->_uniqueID;

    }

}