/include/PHPExcel/PHPExcel/Reader/Excel2007.php
PHP | 1998 lines | 1433 code | 267 blank | 298 comment | 512 complexity | 1514ac137d78307a96031cf596ab8425 MD5 | raw file
Possible License(s): Apache-2.0, LGPL-3.0, LGPL-2.1, GPL-2.0, GPL-3.0
Large files files are truncated, but you can click here to view the full file
- <?php
- /**
- * PHPExcel
- *
- * Copyright (c) 2006 - 2012 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_Reader
- * @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
- * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
- * @version 1.7.7, 2012-05-19
- */
- /** PHPExcel root directory */
- if (!defined('PHPEXCEL_ROOT')) {
- /**
- * @ignore
- */
- define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
- require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
- }
- /**
- * PHPExcel_Reader_Excel2007
- *
- * @category PHPExcel
- * @package PHPExcel_Reader
- * @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
- */
- class PHPExcel_Reader_Excel2007 implements PHPExcel_Reader_IReader
- {
- /**
- * Read data only?
- * Identifies whether the Reader should only read data values for cells, and ignore any formatting information;
- * or whether it should read both data and formatting
- *
- * @var boolean
- */
- private $_readDataOnly = false;
- /**
- * Read charts that are defined in the workbook?
- * Identifies whether the Reader should read the definitions for any charts that exist in the workbook;
- *
- * @var boolean
- */
- private $_includeCharts = false;
- /**
- * Restrict which sheets should be loaded?
- * This property holds an array of worksheet names to be loaded. If null, then all worksheets will be loaded.
- *
- * @var array of string
- */
- private $_loadSheetsOnly = null;
- /**
- * PHPExcel_Reader_IReadFilter instance
- *
- * @var PHPExcel_Reader_IReadFilter
- */
- private $_readFilter = null;
- /**
- * PHPExcel_ReferenceHelper instance
- *
- * @var PHPExcel_ReferenceHelper
- */
- private $_referenceHelper = null;
- /**
- * PHPExcel_Reader_Excel2007_Theme instance
- *
- * @var PHPExcel_Reader_Excel2007_Theme
- */
- private static $_theme = null;
- /**
- * Create a new PHPExcel_Reader_Excel2007 instance
- */
- public function __construct() {
- $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
- $this->_referenceHelper = PHPExcel_ReferenceHelper::getInstance();
- }
- /**
- * Read data only?
- * If this is true, then the Reader will only read data values for cells, it will not read any formatting information.
- * If false (the default) it will read data and formatting.
- *
- * @return boolean
- */
- public function getReadDataOnly() {
- return $this->_readDataOnly;
- }
- /**
- * Set read data only
- * Set to true, to advise the Reader only to read data values for cells, and to ignore any formatting information.
- * Set to false (the default) to advise the Reader to read both data and formatting for cells.
- *
- * @param boolean $pValue
- *
- * @return PHPExcel_Reader_Excel2007
- */
- public function setReadDataOnly($pValue = false) {
- $this->_readDataOnly = $pValue;
- return $this;
- }
- /**
- * Read charts in workbook?
- * If this is true, then the Reader will include any charts that exist in the workbook.
- * Note that a ReadDataOnly value of false overrides, and charts won't be read regardless of the IncludeCharts value.
- * If false (the default) it will ignore any charts defined in the workbook file.
- *
- * @return boolean
- */
- public function getIncludeCharts() {
- return $this->_includeCharts;
- }
- /**
- * Set read charts in workbook
- * Set to true, to advise the Reader to include any charts that exist in the workbook.
- * Note that a ReadDataOnly value of false overrides, and charts won't be read regardless of the IncludeCharts value.
- * Set to false (the default) to discard charts.
- *
- * @param boolean $pValue
- *
- * @return PHPExcel_Reader_Excel2007
- */
- public function setIncludeCharts($pValue = false) {
- $this->_includeCharts = (boolean) $pValue;
- return $this;
- }
- /**
- * Get which sheets to load
- * Returns either an array of worksheet names (the list of worksheets that should be loaded), or a null
- * indicating that all worksheets in the workbook should be loaded.
- *
- * @return mixed
- */
- public function getLoadSheetsOnly()
- {
- return $this->_loadSheetsOnly;
- }
- /**
- * Set which sheets to load
- *
- * @param mixed $value
- * This should be either an array of worksheet names to be loaded, or a string containing a single worksheet name.
- * If NULL, then it tells the Reader to read all worksheets in the workbook
- *
- * @return PHPExcel_Reader_Excel2007
- */
- public function setLoadSheetsOnly($value = null)
- {
- $this->_loadSheetsOnly = is_array($value) ?
- $value : array($value);
- return $this;
- }
- /**
- * Set all sheets to load
- * Tells the Reader to load all worksheets from the workbook.
- *
- * @return PHPExcel_Reader_Excel2007
- */
- public function setLoadAllSheets()
- {
- $this->_loadSheetsOnly = null;
- return $this;
- }
- /**
- * Read filter
- *
- * @return PHPExcel_Reader_IReadFilter
- */
- public function getReadFilter() {
- return $this->_readFilter;
- }
- /**
- * Set read filter
- *
- * @param PHPExcel_Reader_IReadFilter $pValue
- * @return PHPExcel_Reader_Excel2007
- */
- public function setReadFilter(PHPExcel_Reader_IReadFilter $pValue) {
- $this->_readFilter = $pValue;
- return $this;
- }
- /**
- * Can the current PHPExcel_Reader_IReader read the file?
- *
- * @param string $pFileName
- * @return boolean
- * @throws Exception
- */
- public function canRead($pFilename)
- {
- // Check if file exists
- if (!file_exists($pFilename)) {
- throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
- }
- // Check if zip class exists
- if (!class_exists('ZipArchive')) {
- throw new Exception("ZipArchive library is not enabled");
- }
- $xl = false;
- // Load file
- $zip = new ZipArchive;
- if ($zip->open($pFilename) === true) {
- // check if it is an OOXML archive
- $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels"));
- if ($rels !== false) {
- foreach ($rels->Relationship as $rel) {
- switch ($rel["Type"]) {
- case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
- if (basename($rel["Target"]) == 'workbook.xml') {
- $xl = true;
- }
- break;
- }
- }
- }
- $zip->close();
- }
- return $xl;
- }
- /**
- * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns)
- *
- * @param string $pFilename
- * @throws Exception
- */
- public function listWorksheetInfo($pFilename)
- {
- // Check if file exists
- if (!file_exists($pFilename)) {
- throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
- }
- $worksheetInfo = array();
- $zip = new ZipArchive;
- $zip->open($pFilename);
- $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
- foreach ($rels->Relationship as $rel) {
- if ($rel["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument") {
- $dir = dirname($rel["Target"]);
- $relsWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/_rels/" . basename($rel["Target"]) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
- $relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
- $worksheets = array();
- foreach ($relsWorkbook->Relationship as $ele) {
- if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {
- $worksheets[(string) $ele["Id"]] = $ele["Target"];
- }
- }
- $xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
- if ($xmlWorkbook->sheets) {
- $dir = dirname($rel["Target"]);
- foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
- $tmpInfo = array();
- $tmpInfo['worksheetName'] = (string) $eleSheet["name"];
- $tmpInfo['lastColumnLetter'] = 'A';
- $tmpInfo['lastColumnIndex'] = 0;
- $tmpInfo['totalRows'] = 0;
- $tmpInfo['totalColumns'] = 0;
- $fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
- $xmlSheet = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$fileWorksheet")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
- if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
- foreach ($xmlSheet->sheetData->row as $row) {
- foreach ($row->c as $c) {
- $r = (string) $c["r"];
- $coordinates = PHPExcel_Cell::coordinateFromString($r);
- $rowIndex = $coordinates[1];
- $columnIndex = PHPExcel_Cell::columnIndexFromString($coordinates[0]) - 1;
- $tmpInfo['totalRows'] = max($tmpInfo['totalRows'], $rowIndex);
- $tmpInfo['lastColumnIndex'] = max($tmpInfo['lastColumnIndex'], $columnIndex);
- }
- }
- }
- $tmpInfo['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($tmpInfo['lastColumnIndex']);
- $tmpInfo['totalColumns'] = $tmpInfo['lastColumnIndex'] + 1;
- $worksheetInfo[] = $tmpInfo;
- }
- }
- }
- }
- $zip->close();
- return $worksheetInfo;
- }
- private static function _castToBool($c) {
- // echo 'Initial Cast to Boolean<br />';
- $value = isset($c->v) ? (string) $c->v : null;
- if ($value == '0') {
- return false;
- } elseif ($value == '1') {
- return true;
- } else {
- return (bool)$c->v;
- }
- return $value;
- } // function _castToBool()
- private static function _castToError($c) {
- // echo 'Initial Cast to Error<br />';
- return isset($c->v) ? (string) $c->v : null;;
- } // function _castToError()
- private static function _castToString($c) {
- // echo 'Initial Cast to String<br />';
- return isset($c->v) ? (string) $c->v : null;;
- } // function _castToString()
- private function _castToFormula($c,$r,&$cellDataType,&$value,&$calculatedValue,&$sharedFormulas,$castBaseType) {
- // echo '<font color="darkgreen">Formula</font><br />';
- // echo '$c->f is '.$c->f.'<br />';
- $cellDataType = 'f';
- $value = "={$c->f}";
- $calculatedValue = self::$castBaseType($c);
- // Shared formula?
- if (isset($c->f['t']) && strtolower((string)$c->f['t']) == 'shared') {
- // echo '<font color="darkgreen">SHARED FORMULA</font><br />';
- $instance = (string)$c->f['si'];
- // echo 'Instance ID = '.$instance.'<br />';
- //
- // echo 'Shared Formula Array:<pre>';
- // print_r($sharedFormulas);
- // echo '</pre>';
- if (!isset($sharedFormulas[(string)$c->f['si']])) {
- // echo '<font color="darkgreen">SETTING NEW SHARED FORMULA</font><br />';
- // echo 'Master is '.$r.'<br />';
- // echo 'Formula is '.$value.'<br />';
- $sharedFormulas[$instance] = array( 'master' => $r,
- 'formula' => $value
- );
- // echo 'New Shared Formula Array:<pre>';
- // print_r($sharedFormulas);
- // echo '</pre>';
- } else {
- // echo '<font color="darkgreen">GETTING SHARED FORMULA</font><br />';
- // echo 'Master is '.$sharedFormulas[$instance]['master'].'<br />';
- // echo 'Formula is '.$sharedFormulas[$instance]['formula'].'<br />';
- $master = PHPExcel_Cell::coordinateFromString($sharedFormulas[$instance]['master']);
- $current = PHPExcel_Cell::coordinateFromString($r);
- $difference = array(0, 0);
- $difference[0] = PHPExcel_Cell::columnIndexFromString($current[0]) - PHPExcel_Cell::columnIndexFromString($master[0]);
- $difference[1] = $current[1] - $master[1];
- $value = $this->_referenceHelper->updateFormulaReferences( $sharedFormulas[$instance]['formula'],
- 'A1',
- $difference[0],
- $difference[1]
- );
- // echo 'Adjusted Formula is '.$value.'<br />';
- }
- }
- }
- public function _getFromZipArchive(ZipArchive $archive, $fileName = '')
- {
- // Root-relative paths
- if (strpos($fileName, '//') !== false)
- {
- $fileName = substr($fileName, strpos($fileName, '//') + 1);
- }
- $fileName = PHPExcel_Shared_File::realpath($fileName);
- // Apache POI fixes
- $contents = $archive->getFromName($fileName);
- if ($contents === false)
- {
- $contents = $archive->getFromName(substr($fileName, 1));
- }
- return $contents;
- }
- /**
- * Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object
- *
- * @param string $pFilename
- * @throws Exception
- */
- public function listWorksheetNames($pFilename)
- {
- // Check if file exists
- if (!file_exists($pFilename)) {
- throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
- }
- $worksheetNames = array();
- $zip = new ZipArchive;
- $zip->open($pFilename);
- $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
- foreach ($rels->Relationship as $rel) {
- switch ($rel["Type"]) {
- case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
- $xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
- if ($xmlWorkbook->sheets) {
- foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
- // Check if sheet should be skipped
- $worksheetNames[] = (string) $eleSheet["name"];
- }
- }
- }
- }
- $zip->close();
- return $worksheetNames;
- }
- /**
- * Loads PHPExcel from file
- *
- * @param string $pFilename
- * @throws Exception
- */
- public function load($pFilename)
- {
- // Check if file exists
- if (!file_exists($pFilename)) {
- throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
- }
- // Initialisations
- $excel = new PHPExcel;
- $excel->removeSheetByIndex(0);
- if (!$this->_readDataOnly) {
- $excel->removeCellStyleXfByIndex(0); // remove the default style
- $excel->removeCellXfByIndex(0); // remove the default style
- }
- $zip = new ZipArchive;
- $zip->open($pFilename);
- // Read the theme first, because we need the colour scheme when reading the styles
- $wbRels = simplexml_load_string($this->_getFromZipArchive($zip, "xl/_rels/workbook.xml.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
- foreach ($wbRels->Relationship as $rel) {
- switch ($rel["Type"]) {
- case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme":
- $themeOrderArray = array('lt1','dk1','lt2','dk2');
- $themeOrderAdditional = count($themeOrderArray);
- $xmlTheme = simplexml_load_string($this->_getFromZipArchive($zip, "xl/{$rel['Target']}"));
- if (is_object($xmlTheme)) {
- $xmlThemeName = $xmlTheme->attributes();
- $xmlTheme = $xmlTheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
- $themeName = (string)$xmlThemeName['name'];
- $colourScheme = $xmlTheme->themeElements->clrScheme->attributes();
- $colourSchemeName = (string)$colourScheme['name'];
- $colourScheme = $xmlTheme->themeElements->clrScheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
- $themeColours = array();
- foreach ($colourScheme as $k => $xmlColour) {
- $themePos = array_search($k,$themeOrderArray);
- if ($themePos === false) {
- $themePos = $themeOrderAdditional++;
- }
- if (isset($xmlColour->sysClr)) {
- $xmlColourData = $xmlColour->sysClr->attributes();
- $themeColours[$themePos] = $xmlColourData['lastClr'];
- } elseif (isset($xmlColour->srgbClr)) {
- $xmlColourData = $xmlColour->srgbClr->attributes();
- $themeColours[$themePos] = $xmlColourData['val'];
- }
- }
- self::$_theme = new PHPExcel_Reader_Excel2007_Theme($themeName,$colourSchemeName,$themeColours);
- }
- break;
- }
- }
- $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
- foreach ($rels->Relationship as $rel) {
- switch ($rel["Type"]) {
- case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties":
- $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
- if (is_object($xmlCore)) {
- $xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/");
- $xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/");
- $xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties");
- $docProps = $excel->getProperties();
- $docProps->setCreator((string) self::array_item($xmlCore->xpath("dc:creator")));
- $docProps->setLastModifiedBy((string) self::array_item($xmlCore->xpath("cp:lastModifiedBy")));
- $docProps->setCreated(strtotime(self::array_item($xmlCore->xpath("dcterms:created")))); //! respect xsi:type
- $docProps->setModified(strtotime(self::array_item($xmlCore->xpath("dcterms:modified")))); //! respect xsi:type
- $docProps->setTitle((string) self::array_item($xmlCore->xpath("dc:title")));
- $docProps->setDescription((string) self::array_item($xmlCore->xpath("dc:description")));
- $docProps->setSubject((string) self::array_item($xmlCore->xpath("dc:subject")));
- $docProps->setKeywords((string) self::array_item($xmlCore->xpath("cp:keywords")));
- $docProps->setCategory((string) self::array_item($xmlCore->xpath("cp:category")));
- }
- break;
- case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties":
- $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
- if (is_object($xmlCore)) {
- $docProps = $excel->getProperties();
- if (isset($xmlCore->Company))
- $docProps->setCompany((string) $xmlCore->Company);
- if (isset($xmlCore->Manager))
- $docProps->setManager((string) $xmlCore->Manager);
- }
- break;
- case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties":
- $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
- if (is_object($xmlCore)) {
- $docProps = $excel->getProperties();
- foreach ($xmlCore as $xmlProperty) {
- $cellDataOfficeAttributes = $xmlProperty->attributes();
- if (isset($cellDataOfficeAttributes['name'])) {
- $propertyName = (string) $cellDataOfficeAttributes['name'];
- $cellDataOfficeChildren = $xmlProperty->children('http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes');
- $attributeType = $cellDataOfficeChildren->getName();
- $attributeValue = (string) $cellDataOfficeChildren->{$attributeType};
- $attributeValue = PHPExcel_DocumentProperties::convertProperty($attributeValue,$attributeType);
- $attributeType = PHPExcel_DocumentProperties::convertPropertyType($attributeType);
- $docProps->setCustomProperty($propertyName,$attributeValue,$attributeType);
- }
- }
- }
- break;
- case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
- $dir = dirname($rel["Target"]);
- $relsWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/_rels/" . basename($rel["Target"]) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
- $relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
- $sharedStrings = array();
- $xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']"));
- $xmlStrings = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$xpath[Target]")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
- if (isset($xmlStrings) && isset($xmlStrings->si)) {
- foreach ($xmlStrings->si as $val) {
- if (isset($val->t)) {
- $sharedStrings[] = PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $val->t );
- } elseif (isset($val->r)) {
- $sharedStrings[] = $this->_parseRichText($val);
- }
- }
- }
- $worksheets = array();
- foreach ($relsWorkbook->Relationship as $ele) {
- if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {
- $worksheets[(string) $ele["Id"]] = $ele["Target"];
- }
- }
- $styles = array();
- $cellStyles = array();
- $xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']"));
- $xmlStyles = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$xpath[Target]")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
- $numFmts = null;
- if ($xmlStyles && $xmlStyles->numFmts[0]) {
- $numFmts = $xmlStyles->numFmts[0];
- }
- if (isset($numFmts) && ($numFmts !== NULL)) {
- $numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
- }
- if (!$this->_readDataOnly && $xmlStyles) {
- foreach ($xmlStyles->cellXfs->xf as $xf) {
- $numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
- if ($xf["numFmtId"]) {
- if (isset($numFmts)) {
- $tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
- if (isset($tmpNumFmt["formatCode"])) {
- $numFmt = (string) $tmpNumFmt["formatCode"];
- }
- }
- if ((int)$xf["numFmtId"] < 164) {
- $numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int)$xf["numFmtId"]);
- }
- }
- //$numFmt = str_replace('mm', 'i', $numFmt);
- //$numFmt = str_replace('h', 'H', $numFmt);
- $style = (object) array(
- "numFmt" => $numFmt,
- "font" => $xmlStyles->fonts->font[intval($xf["fontId"])],
- "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])],
- "border" => $xmlStyles->borders->border[intval($xf["borderId"])],
- "alignment" => $xf->alignment,
- "protection" => $xf->protection,
- );
- $styles[] = $style;
- // add style to cellXf collection
- $objStyle = new PHPExcel_Style;
- self::_readStyle($objStyle, $style);
- $excel->addCellXf($objStyle);
- }
- foreach ($xmlStyles->cellStyleXfs->xf as $xf) {
- $numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
- if ($numFmts && $xf["numFmtId"]) {
- $tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
- if (isset($tmpNumFmt["formatCode"])) {
- $numFmt = (string) $tmpNumFmt["formatCode"];
- } else if ((int)$xf["numFmtId"] < 165) {
- $numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int)$xf["numFmtId"]);
- }
- }
- $cellStyle = (object) array(
- "numFmt" => $numFmt,
- "font" => $xmlStyles->fonts->font[intval($xf["fontId"])],
- "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])],
- "border" => $xmlStyles->borders->border[intval($xf["borderId"])],
- "alignment" => $xf->alignment,
- "protection" => $xf->protection,
- );
- $cellStyles[] = $cellStyle;
- // add style to cellStyleXf collection
- $objStyle = new PHPExcel_Style;
- self::_readStyle($objStyle, $cellStyle);
- $excel->addCellStyleXf($objStyle);
- }
- }
- $dxfs = array();
- if (!$this->_readDataOnly && $xmlStyles) {
- if ($xmlStyles->dxfs) {
- foreach ($xmlStyles->dxfs->dxf as $dxf) {
- $style = new PHPExcel_Style;
- self::_readStyle($style, $dxf);
- $dxfs[] = $style;
- }
- }
- if ($xmlStyles->cellStyles)
- {
- foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) {
- if (intval($cellStyle['builtinId']) == 0) {
- if (isset($cellStyles[intval($cellStyle['xfId'])])) {
- // Set default style
- $style = new PHPExcel_Style;
- self::_readStyle($style, $cellStyles[intval($cellStyle['xfId'])]);
- // normal style, currently not using it for anything
- }
- }
- }
- }
- }
- $xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
- // Set base date
- if ($xmlWorkbook->workbookPr) {
- PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
- if (isset($xmlWorkbook->workbookPr['date1904'])) {
- $date1904 = (string)$xmlWorkbook->workbookPr['date1904'];
- if ($date1904 == "true" || $date1904 == "1") {
- PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
- }
- }
- }
- $sheetId = 0; // keep track of new sheet id in final workbook
- $oldSheetId = -1; // keep track of old sheet id in final workbook
- $countSkippedSheets = 0; // keep track of number of skipped sheets
- $mapSheetId = array(); // mapping of sheet ids from old to new
- $charts = $chartDetails = array();
- if ($xmlWorkbook->sheets) {
- foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
- ++$oldSheetId;
- // Check if sheet should be skipped
- if (isset($this->_loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) {
- ++$countSkippedSheets;
- $mapSheetId[$oldSheetId] = null;
- continue;
- }
- // Map old sheet id in original workbook to new sheet id.
- // They will differ if loadSheetsOnly() is being used
- $mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets;
- // Load sheet
- $docSheet = $excel->createSheet();
- // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet
- // references in formula cells... during the load, all formulae should be correct,
- // and we're simply bringing the worksheet name in line with the formula, not the
- // reverse
- $docSheet->setTitle((string) $eleSheet["name"],false);
- $fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
- $xmlSheet = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$fileWorksheet")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
- $sharedFormulas = array();
- if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') {
- $docSheet->setSheetState( (string) $eleSheet["state"] );
- }
- if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) {
- if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) {
- $docSheet->getSheetView()->setZoomScale( intval($xmlSheet->sheetViews->sheetView['zoomScale']) );
- }
- if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) {
- $docSheet->getSheetView()->setZoomScaleNormal( intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal']) );
- }
- if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) {
- $docSheet->setShowGridLines((string)$xmlSheet->sheetViews->sheetView['showGridLines'] ? true : false);
- }
- if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) {
- $docSheet->setShowRowColHeaders((string)$xmlSheet->sheetViews->sheetView['showRowColHeaders'] ? true : false);
- }
- if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) {
- $docSheet->setRightToLeft((string)$xmlSheet->sheetViews->sheetView['rightToLeft'] ? true : false);
- }
- if (isset($xmlSheet->sheetViews->sheetView->pane)) {
- if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) {
- $docSheet->freezePane( (string)$xmlSheet->sheetViews->sheetView->pane['topLeftCell'] );
- } else {
- $xSplit = 0;
- $ySplit = 0;
- if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) {
- $xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']);
- }
- if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) {
- $ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']);
- }
- $docSheet->freezePaneByColumnAndRow($xSplit, $ySplit);
- }
- }
- if (isset($xmlSheet->sheetViews->sheetView->selection)) {
- if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) {
- $sqref = (string)$xmlSheet->sheetViews->sheetView->selection['sqref'];
- $sqref = explode(' ', $sqref);
- $sqref = $sqref[0];
- $docSheet->setSelectedCells($sqref);
- }
- }
- }
- if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) {
- if (isset($xmlSheet->sheetPr->tabColor['rgb'])) {
- $docSheet->getTabColor()->setARGB( (string)$xmlSheet->sheetPr->tabColor['rgb'] );
- }
- }
- if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) {
- if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && $xmlSheet->sheetPr->outlinePr['summaryRight'] == false) {
- $docSheet->setShowSummaryRight(false);
- } else {
- $docSheet->setShowSummaryRight(true);
- }
- if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && $xmlSheet->sheetPr->outlinePr['summaryBelow'] == false) {
- $docSheet->setShowSummaryBelow(false);
- } else {
- $docSheet->setShowSummaryBelow(true);
- }
- }
- if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) {
- if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) && $xmlSheet->sheetPr->pageSetUpPr['fitToPage'] == false) {
- $docSheet->getPageSetup()->setFitToPage(false);
- } else {
- $docSheet->getPageSetup()->setFitToPage(true);
- }
- }
- if (isset($xmlSheet->sheetFormatPr)) {
- if (isset($xmlSheet->sheetFormatPr['customHeight']) && ((string)$xmlSheet->sheetFormatPr['customHeight'] == '1' || strtolower((string)$xmlSheet->sheetFormatPr['customHeight']) == 'true') && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
- $docSheet->getDefaultRowDimension()->setRowHeight( (float)$xmlSheet->sheetFormatPr['defaultRowHeight'] );
- }
- if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
- $docSheet->getDefaultColumnDimension()->setWidth( (float)$xmlSheet->sheetFormatPr['defaultColWidth'] );
- }
- }
- if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
- foreach ($xmlSheet->cols->col as $col) {
- for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
- if ($col["style"] && !$this->_readDataOnly) {
- $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"]));
- }
- if ($col["bestFit"]) {
- //$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(true);
- }
- if ($col["hidden"]) {
- $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setVisible(false);
- }
- if ($col["collapsed"]) {
- $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setCollapsed(true);
- }
- if ($col["outlineLevel"] > 0) {
- $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
- }
- $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"]));
- if (intval($col["max"]) == 16384) {
- break;
- }
- }
- }
- }
- if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) {
- if ($xmlSheet->printOptions['gridLinesSet'] == 'true' && $xmlSheet->printOptions['gridLinesSet'] == '1') {
- $docSheet->setShowGridlines(true);
- }
- if ($xmlSheet->printOptions['gridLines'] == 'true' || $xmlSheet->printOptions['gridLines'] == '1') {
- $docSheet->setPrintGridlines(true);
- }
- if ($xmlSheet->printOptions['horizontalCentered']) {
- $docSheet->getPageSetup()->setHorizontalCentered(true);
- }
- if ($xmlSheet->printOptions['verticalCentered']) {
- $docSheet->getPageSetup()->setVerticalCentered(true);
- }
- }
- if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
- foreach ($xmlSheet->sheetData->row as $row) {
- if ($row["ht"] && !$this->_readDataOnly) {
- $docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
- }
- if ($row["hidden"] && !$this->_readDataOnly) {
- $docSheet->getRowDimension(intval($row["r"]))->setVisible(false);
- }
- if ($row["collapsed"]) {
- $docSheet->getRowDimension(intval($row["r"]))->setCollapsed(true);
- }
- if ($row["outlineLevel"] > 0) {
- $docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
- }
- if ($row["s"] && !$this->_readDataOnly) {
- $docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"]));
- }
- foreach ($row->c as $c) {
- $r = (string) $c["r"];
- $cellDataType = (string) $c["t"];
- $value = null;
- $calculatedValue = null;
- // Read cell?
- if ($this->getReadFilter() !== NULL) {
- $coordinates = PHPExcel_Cell::coordinateFromString($r);
- if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
- continue;
- }
- }
- // echo '<b>Reading cell '.$coordinates[0].$coordinates[1].'</b><br />';
- // print_r($c);
- // echo '<br />';
- // echo 'Cell Data Type is '.$cellDataType.': ';
- //
- // Read cell!
- switch ($cellDataType) {
- case "s":
- // echo 'String<br />';
- if ((string)$c->v != '') {
- $value = $sharedStrings[intval($c->v)];
- if ($value instanceof PHPExcel_RichText) {
- $value = clone $value;
- }
- } else {
- $value = '';
- }
- break;
- case "b":
- // echo 'Boolean<br />';
- if (!isset($c->f)) {
- $value = self::_castToBool($c);
- } else {
- // Formula
- $this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToBool');
- if (isset($c->f['t'])) {
- $att = array();
- $att = $c->f;
- $docSheet->getCell($r)->setFormulaAttributes($att);
- }
- // echo '$calculatedValue = '.$calculatedValue.'<br />';
- }
- break;
- case "inlineStr":
- // echo 'Inline String<br />';
- $value = $this->_parseRichText($c->is);
- break;
- case "e":
- // echo 'Error<br />';
- if (!isset($c->f)) {
- $value = self::_castToError($c);
- } else {
- // Formula
- $this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToError');
- // echo '$calculatedValue = '.$calculatedValue.'<br />';
- }
- break;
- default:
- // echo 'Default<br />';
- if (!isset($c->f)) {
- // echo 'Not a Formula<br />';
- $value = self::_castToString($c);
- } else {
- // echo 'Treat as Formula<br />';
- // Formula
- $this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToString');
- // echo '$calculatedValue = '.$calculatedValue.'<br />';
- }
- break;
- }
- // echo 'Value is '.$value.'<br />';
- // Check for numeric values
- if (is_numeric($value) && $cellDataType != 's') {
- if ($value == (int)$value) $value = (int)$value;
- elseif ($value == (float)$value) $value = (float)$value;
- elseif ($value == (double)$value) $value = (double)$value;
- }
- // Rich text?
- if ($value instanceof PHPExcel_RichText && $this->_readDataOnly) {
- $value = $value->getPlainText();
- }
- $cell = $docSheet->getCell($r);
- // Assign value
- if ($cellDataType != '') {
- $cell->setValueExplicit($value, $cellDataType);
- } else {
- $cell->setValue($value);
- }
- if ($calculatedValue !== NULL) {
- $cell->setCalculatedValue($calculatedValue);
- }
- // Style information?
- if ($c["s"] && !$this->_readDataOnly) {
- // no style index means 0, it seems
- $cell->setXfIndex(isset($styles[intval($c["s"])]) ?
- intval($c["s"]) : 0);
- }
- }
- }
- }
- $conditionals = array();
- if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) {
- foreach ($xmlSheet->conditionalFormatting as $conditional) {
- foreach ($conditional->cfRule as $cfRule) {
- if (
- (
- (string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_NONE ||
- (string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CELLIS ||
- (string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT ||
- (string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_EXPRESSION
- ) && isset($dxfs[intval($cfRule["dxfId"])])
- ) {
- $conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule;
- }
- }
- }
- foreach ($conditionals as $ref => $cfRules) {
- ksort($cfRules);
- $conditionalStyles = array();
- foreach ($cfRules as $cfRule) {
- $objConditional = new PHPExcel_Style_Conditional();
- $objConditional->setConditionType((string)$cfRule["type"]);
- $objConditional->setOperatorType((string)$cfRule["operator"]);
- if ((string)$cfRule["text"] != '') {
- $objConditional->setText((string)$cfRule["text"]);
- }
- if (count($cfRule->formula) > 1) {
- foreach ($cfRule->formula as $formula) {
- $objConditional->addCondition((string)$formula);
- }
- } else {
- $objConditional->addCondition((string)$cfRule->formula);
- }
- $objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]);
- $conditionalStyles[] = $objConditional;
- }
- // Extract all cell references in $ref
- $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($ref);
- foreach ($aReferences as $reference) {
- $docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles);
- }
- }
- }
- $aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells");
- if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
- foreach ($aKeys as $key) {
- $method = "set" . ucfirst($key);
- $docSheet->getProtection()->$method($xmlSheet->sheetProtection[$key] == "true");
- }
- }
- if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
- $docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], true);
- if ($xmlSheet->protectedRanges->protectedRange) {
- foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
- $docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true);
- }
- }
- }
- if ($xmlSheet && $xmlSheet->autoFilter && !$this->_readDataOnly) {
- $docSheet->setAutoFilter((string) $xmlSheet->autoFilter["ref"]);
- }
- if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) {
- foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
- $docSheet->mergeCells((string) $mergeCell["ref"]);
- }
- }
- if ($xmlSheet && $xmlSheet->pageMargins && !$this->_readDataOnly) {
- $docPageMargins = $docSheet->getPageMargins();
- $docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"]));
- $docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"]));
- $docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"]));
- $docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"]));
- $docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"]));
- $docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"]));
- }
- if ($xmlSheet && $xmlSheet->pageSetup && !$this->_readDataOnly) {
- $docPageSetup = $docSheet->getPageSetup();
- if (isset($xmlSheet->pageSetup["orientation"])) {
- $docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]);
- }
- if (isset($xmlSheet->pageSetup["paperSize"])) {
- $docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"]));
- }
- if (isset($xmlSheet->pageSetup["scale"])) {
- $docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]), false);
- }
- if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) {
- $docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]), false);
- }
- if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) {
- $docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]), false);
- }
- if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) &&
- ((string)$xmlSheet->pageSetup["useFirstPageNumber"] == 'true' || (string)$xmlSheet->pageSetup["useFirstPageNumber"] == '1')) {
- $docPageSetup->setFirstPageNumber(intval($xmlSheet->pageSetup["firstPageNumber"]));
- }
- }
- if ($xmlSheet && $xmlSheet->headerFooter && !$this->_readDataOnly) {
- $docHeaderFooter = $docSheet->getHeaderFooter();
- if (isset($xmlSheet->headerFooter["differentOddEven"]) &&
- ((string)$xmlSheet->headerFooter["differentOddEven"] == 'true' || (string)$xmlSheet->headerFooter["differentOddEven"] == '1')) {
- $docHeaderFooter->setDifferentOddEven(true);
- } else {
- $docHeaderFooter->setDifferentOddEven(false);
- }
- if (isset($xmlSheet->headerFooter["differentFirst"]) &&
- ((string)$xmlSheet->headerFooter["differentFirst"] == 'true' || (string)$xmlSheet->headerFooter["differentFirst"] == '1')) {
- $docHeaderFooter->setDifferentFirst(true);
- } else {
- $docHeaderFooter->setDifferentFirst(false);
- }
- if (isset($xmlSheet->headerFooter["scaleWithDoc"]) &&
- ((string)$xmlSheet->headerFooter["scaleWithDoc"] == 'false' || (string)$xmlSheet->headerFooter["scaleWithDoc"] == '0')) {
- $docHeaderFooter->setScaleWithDocument(false);
- } else {
- $docHeaderFooter->setScaleWithDocument(true);
- }
- if (isset($xmlSheet->headerFooter["alignWithMargins"]) &&
- ((string)$xmlSheet->headerFooter["alignWithMargins"] == 'false' || (string)$xmlSheet->headerFooter["alignWithMargins"] == '0')) {
- $docHeaderFooter->setAlignWithMargins(false);
- } else {
- $docHeaderFooter->setAlignWithMargins(true);
- }
- $docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader);
- $docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter);
- $docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader);
- $docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter);
- $docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader);
- $docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter);
- }
- if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->_readDataOnly) {
- foreach ($xmlSheet->rowBreaks->brk as $brk) {
- if ($brk["man"]) {
- $docSheet->setBreak("A$brk[id]", PHPExcel_Worksheet::BREAK_ROW);
- }
- }
- }
- if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->_readDataOnly) {
- foreach ($xmlSheet->colBreaks->brk as $brk) {
- if ($brk["man"]) {
- $docSheet->setBreak(PHPExcel_Cell::stringFromColumnIndex($brk["id"]) . "1", PHPExcel_Worksheet::BREAK_COLUMN);
- }
- }
- }
- if ($xmlSheet && $xmlSheet->dataValidations && !$this->_readDataOnly) {
- foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) {
- // Uppercase coordinate
- $range = strtoupper($dataValidation["sqref"]);
- $rangeSet = explode(' ',$range);
- foreach($rangeSet as $range) {
- $stRange = $docSheet->shrinkRangeToFit($range);
- // Extract all cell references in $range
- $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($stRange);
- foreach ($aReferences as $reference) {
- // Create validation
- $docValidation = $docSheet->getCell($reference)->getDataValidation();
- $docValidation->setType((string) $dataValidation["type"]);
- $docValidation->setErrorStyle((string) $dataValidation["errorStyle"]);
- $docValidation->setOperator((string) $dataValidation["operator"]);
- $docValidation->setAllowBlank($dataValidation["allowBlank"] != 0);
- $docValidation->setShowDropDown($dataValidation["showDropDown"] == 0);
- $docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0);
- $docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0);
- $docValidation->setErrorTitle((string) $dataValidation["errorTitle"]);
- $docValidation->setError((string) $dataValidation["error"]);
- $docValidation->setPromptTitle((string) $dataValidation["promptTitle"]);
- $docValidation->setPrompt((string) $dataValidation["prompt"]);
- $docValidation->setFormula1((string) $dataValidation->formula1);
- $docValidation->setFormula2((string) $dataValidation->formula2);
- }
- }
- }
- }
- // Add hyperlinks
- $hyperlinks = array();
- if (!$this->_readDataOnly) {
- // Locate hyperlink relations
- if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
- $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
- foreach ($relsWorksheet->Relationship as $ele) {
- if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") {
- $hyperlinks[(string)$ele["Id"]] = (string)$ele["Target"];
- }
- }
- }
- // Loop through hyperlinks
- if ($xmlSheet && $xmlSheet->hyperlinks) {
- foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) {
- // Link url
- $linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
- foreach (PHPExcel_Cell::extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) {
- $cell = $docSheet->getCell( $cellReference );
- if (isset($linkRel['id'])) {
- $cell->getHyperlink()->setUrl( $hyperlinks[ (string)$linkRel['id'] ] );
- }
- if (isset($hyperlink['location'])) {
- $cell->getHyperlink()->setUrl( 'sheet://' . (string)$hyperlink['location'] );
- }
- // Tooltip
- if (isset($hyperlink['tooltip'])) {
- $cell->getHyperlink()->setTooltip( (string)$hyperlink['tooltip'] );
- }
- }
- }
- }
- }
- // Add comments
- $comments = array();
- $vmlComments = array();
- if (!$this->_readDataOnly) {
- // Locate comment relations
- if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
- $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org…
Large files files are truncated, but you can click here to view the full file