f09576425233b21502f63db79dc3b2d4fa4c7b40.svn-base 30 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139
  1. <?php
  2. /**
  3. * PHPExcel
  4. *
  5. * Copyright (c) 2006 - 2014 PHPExcel
  6. *
  7. * This library is free software; you can redistribute it and/or
  8. * modify it under the terms of the GNU Lesser General Public
  9. * License as published by the Free Software Foundation; either
  10. * version 2.1 of the License, or (at your option) any later version.
  11. *
  12. * This library is distributed in the hope that it will be useful,
  13. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  15. * Lesser General Public License for more details.
  16. *
  17. * You should have received a copy of the GNU Lesser General Public
  18. * License along with this library; if not, write to the Free Software
  19. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
  20. *
  21. * @category PHPExcel
  22. * @package PHPExcel
  23. * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  25. * @version ##VERSION##, ##DATE##
  26. */
  27. /** PHPExcel root directory */
  28. if (!defined('PHPEXCEL_ROOT')) {
  29. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/');
  30. require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
  31. }
  32. /**
  33. * PHPExcel
  34. *
  35. * @category PHPExcel
  36. * @package PHPExcel
  37. * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  38. */
  39. class PHPExcel
  40. {
  41. /**
  42. * Unique ID
  43. *
  44. * @var string
  45. */
  46. private $_uniqueID;
  47. /**
  48. * Document properties
  49. *
  50. * @var PHPExcel_DocumentProperties
  51. */
  52. private $_properties;
  53. /**
  54. * Document security
  55. *
  56. * @var PHPExcel_DocumentSecurity
  57. */
  58. private $_security;
  59. /**
  60. * Collection of Worksheet objects
  61. *
  62. * @var PHPExcel_Worksheet[]
  63. */
  64. private $_workSheetCollection = array();
  65. /**
  66. * Calculation Engine
  67. *
  68. * @var PHPExcel_Calculation
  69. */
  70. private $_calculationEngine = NULL;
  71. /**
  72. * Active sheet index
  73. *
  74. * @var int
  75. */
  76. private $_activeSheetIndex = 0;
  77. /**
  78. * Named ranges
  79. *
  80. * @var PHPExcel_NamedRange[]
  81. */
  82. private $_namedRanges = array();
  83. /**
  84. * CellXf supervisor
  85. *
  86. * @var PHPExcel_Style
  87. */
  88. private $_cellXfSupervisor;
  89. /**
  90. * CellXf collection
  91. *
  92. * @var PHPExcel_Style[]
  93. */
  94. private $_cellXfCollection = array();
  95. /**
  96. * CellStyleXf collection
  97. *
  98. * @var PHPExcel_Style[]
  99. */
  100. private $_cellStyleXfCollection = array();
  101. /**
  102. * _hasMacros : this workbook have macros ?
  103. *
  104. * @var bool
  105. */
  106. private $_hasMacros = FALSE;
  107. /**
  108. * _macrosCode : all macros code (the vbaProject.bin file, this include form, code, etc.), NULL if no macro
  109. *
  110. * @var binary
  111. */
  112. private $_macrosCode=NULL;
  113. /**
  114. * _macrosCertificate : if macros are signed, contains vbaProjectSignature.bin file, NULL if not signed
  115. *
  116. * @var binary
  117. */
  118. private $_macrosCertificate=NULL;
  119. /**
  120. * _ribbonXMLData : NULL if workbook is'nt Excel 2007 or not contain a customized UI
  121. *
  122. * @var NULL|string
  123. */
  124. private $_ribbonXMLData=NULL;
  125. /**
  126. * _ribbonBinObjects : NULL if workbook is'nt Excel 2007 or not contain embedded objects (picture(s)) for Ribbon Elements
  127. * ignored if $_ribbonXMLData is null
  128. *
  129. * @var NULL|array
  130. */
  131. private $_ribbonBinObjects=NULL;
  132. /**
  133. * The workbook has macros ?
  134. *
  135. * @return true if workbook has macros, false if not
  136. */
  137. public function hasMacros(){
  138. return $this->_hasMacros;
  139. }
  140. /**
  141. * Define if a workbook has macros
  142. *
  143. * @param true|false
  144. */
  145. public function setHasMacros($hasMacros=false){
  146. $this->_hasMacros=(bool)$hasMacros;
  147. }
  148. /**
  149. * Set the macros code
  150. *
  151. * @param binary string|null
  152. */
  153. public function setMacrosCode($MacrosCode){
  154. $this->_macrosCode=$MacrosCode;
  155. $this->setHasMacros(!is_null($MacrosCode));
  156. }
  157. /**
  158. * Return the macros code
  159. *
  160. * @return binary|null
  161. */
  162. public function getMacrosCode(){
  163. return $this->_macrosCode;
  164. }
  165. /**
  166. * Set the macros certificate
  167. *
  168. * @param binary|null
  169. */
  170. public function setMacrosCertificate($Certificate=NULL){
  171. $this->_macrosCertificate=$Certificate;
  172. }
  173. /**
  174. * Is the project signed ?
  175. *
  176. * @return true|false
  177. */
  178. public function hasMacrosCertificate(){
  179. return !is_null($this->_macrosCertificate);
  180. }
  181. /**
  182. * Return the macros certificate
  183. *
  184. * @return binary|null
  185. */
  186. public function getMacrosCertificate(){
  187. return $this->_macrosCertificate;
  188. }
  189. /**
  190. * Remove all macros, certificate from spreadsheet
  191. *
  192. * @param none
  193. * @return void
  194. */
  195. public function discardMacros(){
  196. $this->_hasMacros=false;
  197. $this->_macrosCode=NULL;
  198. $this->_macrosCertificate=NULL;
  199. }
  200. /**
  201. * set ribbon XML data
  202. *
  203. */
  204. public function setRibbonXMLData($Target=NULL, $XMLData=NULL){
  205. if(!is_null($Target) && !is_null($XMLData)){
  206. $this->_ribbonXMLData=array('target'=>$Target, 'data'=>$XMLData);
  207. }else{
  208. $this->_ribbonXMLData=NULL;
  209. }
  210. }
  211. /**
  212. * retrieve ribbon XML Data
  213. *
  214. * return string|null|array
  215. */
  216. public function getRibbonXMLData($What='all'){//we need some constants here...
  217. $ReturnData=NULL;
  218. $What=strtolower($What);
  219. switch($What){
  220. case 'all':
  221. $ReturnData=$this->_ribbonXMLData;
  222. break;
  223. case 'target':
  224. case 'data':
  225. if(is_array($this->_ribbonXMLData) && array_key_exists($What,$this->_ribbonXMLData)){
  226. $ReturnData=$this->_ribbonXMLData[$What];
  227. }//else $ReturnData stay at null
  228. break;
  229. }//default: $ReturnData at null
  230. return $ReturnData;
  231. }
  232. /**
  233. * store binaries ribbon objects (pictures)
  234. *
  235. */
  236. public function setRibbonBinObjects($BinObjectsNames=NULL, $BinObjectsData=NULL){
  237. if(!is_null($BinObjectsNames) && !is_null($BinObjectsData)){
  238. $this->_ribbonBinObjects=array('names'=>$BinObjectsNames, 'data'=>$BinObjectsData);
  239. }else{
  240. $this->_ribbonBinObjects=NULL;
  241. }
  242. }
  243. /**
  244. * return the extension of a filename. Internal use for a array_map callback (php<5.3 don't like lambda function)
  245. *
  246. */
  247. private function _getExtensionOnly($ThePath){
  248. return pathinfo($ThePath, PATHINFO_EXTENSION);
  249. }
  250. /**
  251. * retrieve Binaries Ribbon Objects
  252. *
  253. */
  254. public function getRibbonBinObjects($What='all'){
  255. $ReturnData=NULL;
  256. $What=strtolower($What);
  257. switch($What){
  258. case 'all':
  259. return $this->_ribbonBinObjects;
  260. break;
  261. case 'names':
  262. case 'data':
  263. if(is_array($this->_ribbonBinObjects) && array_key_exists($What, $this->_ribbonBinObjects)){
  264. $ReturnData=$this->_ribbonBinObjects[$What];
  265. }
  266. break;
  267. case 'types':
  268. if(is_array($this->_ribbonBinObjects) && array_key_exists('data', $this->_ribbonBinObjects) && is_array($this->_ribbonBinObjects['data'])){
  269. $tmpTypes=array_keys($this->_ribbonBinObjects['data']);
  270. $ReturnData=array_unique(array_map(array($this,'_getExtensionOnly'), $tmpTypes));
  271. }else
  272. $ReturnData=array();//the caller want an array... not null if empty
  273. break;
  274. }
  275. return $ReturnData;
  276. }
  277. /**
  278. * This workbook have a custom UI ?
  279. *
  280. * @return true|false
  281. */
  282. public function hasRibbon(){
  283. return !is_null($this->_ribbonXMLData);
  284. }
  285. /**
  286. * This workbook have additionnal object for the ribbon ?
  287. *
  288. * @return true|false
  289. */
  290. public function hasRibbonBinObjects(){
  291. return !is_null($this->_ribbonBinObjects);
  292. }
  293. /**
  294. * Check if a sheet with a specified code name already exists
  295. *
  296. * @param string $pSheetCodeName Name of the worksheet to check
  297. * @return boolean
  298. */
  299. public function sheetCodeNameExists($pSheetCodeName)
  300. {
  301. return ($this->getSheetByCodeName($pSheetCodeName) !== NULL);
  302. }
  303. /**
  304. * Get sheet by code name. Warning : sheet don't have always a code name !
  305. *
  306. * @param string $pName Sheet name
  307. * @return PHPExcel_Worksheet
  308. */
  309. public function getSheetByCodeName($pName = '')
  310. {
  311. $worksheetCount = count($this->_workSheetCollection);
  312. for ($i = 0; $i < $worksheetCount; ++$i) {
  313. if ($this->_workSheetCollection[$i]->getCodeName() == $pName) {
  314. return $this->_workSheetCollection[$i];
  315. }
  316. }
  317. return null;
  318. }
  319. /**
  320. * Create a new PHPExcel with one Worksheet
  321. */
  322. public function __construct()
  323. {
  324. $this->_uniqueID = uniqid();
  325. $this->_calculationEngine = PHPExcel_Calculation::getInstance($this);
  326. // Initialise worksheet collection and add one worksheet
  327. $this->_workSheetCollection = array();
  328. $this->_workSheetCollection[] = new PHPExcel_Worksheet($this);
  329. $this->_activeSheetIndex = 0;
  330. // Create document properties
  331. $this->_properties = new PHPExcel_DocumentProperties();
  332. // Create document security
  333. $this->_security = new PHPExcel_DocumentSecurity();
  334. // Set named ranges
  335. $this->_namedRanges = array();
  336. // Create the cellXf supervisor
  337. $this->_cellXfSupervisor = new PHPExcel_Style(true);
  338. $this->_cellXfSupervisor->bindParent($this);
  339. // Create the default style
  340. $this->addCellXf(new PHPExcel_Style);
  341. $this->addCellStyleXf(new PHPExcel_Style);
  342. }
  343. /**
  344. * Code to execute when this worksheet is unset()
  345. *
  346. */
  347. public function __destruct() {
  348. PHPExcel_Calculation::unsetInstance($this);
  349. $this->disconnectWorksheets();
  350. } // function __destruct()
  351. /**
  352. * Disconnect all worksheets from this PHPExcel workbook object,
  353. * typically so that the PHPExcel object can be unset
  354. *
  355. */
  356. public function disconnectWorksheets()
  357. {
  358. $worksheet = NULL;
  359. foreach($this->_workSheetCollection as $k => &$worksheet) {
  360. $worksheet->disconnectCells();
  361. $this->_workSheetCollection[$k] = null;
  362. }
  363. unset($worksheet);
  364. $this->_workSheetCollection = array();
  365. }
  366. /**
  367. * Return the calculation engine for this worksheet
  368. *
  369. * @return PHPExcel_Calculation
  370. */
  371. public function getCalculationEngine()
  372. {
  373. return $this->_calculationEngine;
  374. } // function getCellCacheController()
  375. /**
  376. * Get properties
  377. *
  378. * @return PHPExcel_DocumentProperties
  379. */
  380. public function getProperties()
  381. {
  382. return $this->_properties;
  383. }
  384. /**
  385. * Set properties
  386. *
  387. * @param PHPExcel_DocumentProperties $pValue
  388. */
  389. public function setProperties(PHPExcel_DocumentProperties $pValue)
  390. {
  391. $this->_properties = $pValue;
  392. }
  393. /**
  394. * Get security
  395. *
  396. * @return PHPExcel_DocumentSecurity
  397. */
  398. public function getSecurity()
  399. {
  400. return $this->_security;
  401. }
  402. /**
  403. * Set security
  404. *
  405. * @param PHPExcel_DocumentSecurity $pValue
  406. */
  407. public function setSecurity(PHPExcel_DocumentSecurity $pValue)
  408. {
  409. $this->_security = $pValue;
  410. }
  411. /**
  412. * Get active sheet
  413. *
  414. * @return PHPExcel_Worksheet
  415. *
  416. * @throws PHPExcel_Exception
  417. */
  418. public function getActiveSheet()
  419. {
  420. return $this->getSheet($this->_activeSheetIndex);
  421. }
  422. /**
  423. * Create sheet and add it to this workbook
  424. *
  425. * @param int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
  426. * @return PHPExcel_Worksheet
  427. * @throws PHPExcel_Exception
  428. */
  429. public function createSheet($iSheetIndex = NULL)
  430. {
  431. $newSheet = new PHPExcel_Worksheet($this);
  432. $this->addSheet($newSheet, $iSheetIndex);
  433. return $newSheet;
  434. }
  435. /**
  436. * Check if a sheet with a specified name already exists
  437. *
  438. * @param string $pSheetName Name of the worksheet to check
  439. * @return boolean
  440. */
  441. public function sheetNameExists($pSheetName)
  442. {
  443. return ($this->getSheetByName($pSheetName) !== NULL);
  444. }
  445. /**
  446. * Add sheet
  447. *
  448. * @param PHPExcel_Worksheet $pSheet
  449. * @param int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
  450. * @return PHPExcel_Worksheet
  451. * @throws PHPExcel_Exception
  452. */
  453. public function addSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = NULL)
  454. {
  455. if ($this->sheetNameExists($pSheet->getTitle())) {
  456. throw new PHPExcel_Exception(
  457. "Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename this worksheet first."
  458. );
  459. }
  460. if($iSheetIndex === NULL) {
  461. if ($this->_activeSheetIndex < 0) {
  462. $this->_activeSheetIndex = 0;
  463. }
  464. $this->_workSheetCollection[] = $pSheet;
  465. } else {
  466. // Insert the sheet at the requested index
  467. array_splice(
  468. $this->_workSheetCollection,
  469. $iSheetIndex,
  470. 0,
  471. array($pSheet)
  472. );
  473. // Adjust active sheet index if necessary
  474. if ($this->_activeSheetIndex >= $iSheetIndex) {
  475. ++$this->_activeSheetIndex;
  476. }
  477. }
  478. if ($pSheet->getParent() === null) {
  479. $pSheet->rebindParent($this);
  480. }
  481. return $pSheet;
  482. }
  483. /**
  484. * Remove sheet by index
  485. *
  486. * @param int $pIndex Active sheet index
  487. * @throws PHPExcel_Exception
  488. */
  489. public function removeSheetByIndex($pIndex = 0)
  490. {
  491. $numSheets = count($this->_workSheetCollection);
  492. if ($pIndex > $numSheets - 1) {
  493. throw new PHPExcel_Exception(
  494. "You tried to remove a sheet by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
  495. );
  496. } else {
  497. array_splice($this->_workSheetCollection, $pIndex, 1);
  498. }
  499. // Adjust active sheet index if necessary
  500. if (($this->_activeSheetIndex >= $pIndex) &&
  501. ($pIndex > count($this->_workSheetCollection) - 1)) {
  502. --$this->_activeSheetIndex;
  503. }
  504. }
  505. /**
  506. * Get sheet by index
  507. *
  508. * @param int $pIndex Sheet index
  509. * @return PHPExcel_Worksheet
  510. * @throws PHPExcel_Exception
  511. */
  512. public function getSheet($pIndex = 0)
  513. {
  514. if (!isset($this->_workSheetCollection[$pIndex])) {
  515. $numSheets = $this->getSheetCount();
  516. throw new PHPExcel_Exception(
  517. "Your requested sheet index: {$pIndex} is out of bounds. The actual number of sheets is {$numSheets}."
  518. );
  519. }
  520. return $this->_workSheetCollection[$pIndex];
  521. }
  522. /**
  523. * Get all sheets
  524. *
  525. * @return PHPExcel_Worksheet[]
  526. */
  527. public function getAllSheets()
  528. {
  529. return $this->_workSheetCollection;
  530. }
  531. /**
  532. * Get sheet by name
  533. *
  534. * @param string $pName Sheet name
  535. * @return PHPExcel_Worksheet
  536. */
  537. public function getSheetByName($pName = '')
  538. {
  539. $worksheetCount = count($this->_workSheetCollection);
  540. for ($i = 0; $i < $worksheetCount; ++$i) {
  541. if ($this->_workSheetCollection[$i]->getTitle() === $pName) {
  542. return $this->_workSheetCollection[$i];
  543. }
  544. }
  545. return NULL;
  546. }
  547. /**
  548. * Get index for sheet
  549. *
  550. * @param PHPExcel_Worksheet $pSheet
  551. * @return Sheet index
  552. * @throws PHPExcel_Exception
  553. */
  554. public function getIndex(PHPExcel_Worksheet $pSheet)
  555. {
  556. foreach ($this->_workSheetCollection as $key => $value) {
  557. if ($value->getHashCode() == $pSheet->getHashCode()) {
  558. return $key;
  559. }
  560. }
  561. throw new PHPExcel_Exception("Sheet does not exist.");
  562. }
  563. /**
  564. * Set index for sheet by sheet name.
  565. *
  566. * @param string $sheetName Sheet name to modify index for
  567. * @param int $newIndex New index for the sheet
  568. * @return New sheet index
  569. * @throws PHPExcel_Exception
  570. */
  571. public function setIndexByName($sheetName, $newIndex)
  572. {
  573. $oldIndex = $this->getIndex($this->getSheetByName($sheetName));
  574. $pSheet = array_splice(
  575. $this->_workSheetCollection,
  576. $oldIndex,
  577. 1
  578. );
  579. array_splice(
  580. $this->_workSheetCollection,
  581. $newIndex,
  582. 0,
  583. $pSheet
  584. );
  585. return $newIndex;
  586. }
  587. /**
  588. * Get sheet count
  589. *
  590. * @return int
  591. */
  592. public function getSheetCount()
  593. {
  594. return count($this->_workSheetCollection);
  595. }
  596. /**
  597. * Get active sheet index
  598. *
  599. * @return int Active sheet index
  600. */
  601. public function getActiveSheetIndex()
  602. {
  603. return $this->_activeSheetIndex;
  604. }
  605. /**
  606. * Set active sheet index
  607. *
  608. * @param int $pIndex Active sheet index
  609. * @throws PHPExcel_Exception
  610. * @return PHPExcel_Worksheet
  611. */
  612. public function setActiveSheetIndex($pIndex = 0)
  613. {
  614. $numSheets = count($this->_workSheetCollection);
  615. if ($pIndex > $numSheets - 1) {
  616. throw new PHPExcel_Exception(
  617. "You tried to set a sheet active by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
  618. );
  619. } else {
  620. $this->_activeSheetIndex = $pIndex;
  621. }
  622. return $this->getActiveSheet();
  623. }
  624. /**
  625. * Set active sheet index by name
  626. *
  627. * @param string $pValue Sheet title
  628. * @return PHPExcel_Worksheet
  629. * @throws PHPExcel_Exception
  630. */
  631. public function setActiveSheetIndexByName($pValue = '')
  632. {
  633. if (($worksheet = $this->getSheetByName($pValue)) instanceof PHPExcel_Worksheet) {
  634. $this->setActiveSheetIndex($this->getIndex($worksheet));
  635. return $worksheet;
  636. }
  637. throw new PHPExcel_Exception('Workbook does not contain sheet:' . $pValue);
  638. }
  639. /**
  640. * Get sheet names
  641. *
  642. * @return string[]
  643. */
  644. public function getSheetNames()
  645. {
  646. $returnValue = array();
  647. $worksheetCount = $this->getSheetCount();
  648. for ($i = 0; $i < $worksheetCount; ++$i) {
  649. $returnValue[] = $this->getSheet($i)->getTitle();
  650. }
  651. return $returnValue;
  652. }
  653. /**
  654. * Add external sheet
  655. *
  656. * @param PHPExcel_Worksheet $pSheet External sheet to add
  657. * @param int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
  658. * @throws PHPExcel_Exception
  659. * @return PHPExcel_Worksheet
  660. */
  661. public function addExternalSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = null) {
  662. if ($this->sheetNameExists($pSheet->getTitle())) {
  663. throw new PHPExcel_Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
  664. }
  665. // count how many cellXfs there are in this workbook currently, we will need this below
  666. $countCellXfs = count($this->_cellXfCollection);
  667. // copy all the shared cellXfs from the external workbook and append them to the current
  668. foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) {
  669. $this->addCellXf(clone $cellXf);
  670. }
  671. // move sheet to this workbook
  672. $pSheet->rebindParent($this);
  673. // update the cellXfs
  674. foreach ($pSheet->getCellCollection(false) as $cellID) {
  675. $cell = $pSheet->getCell($cellID);
  676. $cell->setXfIndex( $cell->getXfIndex() + $countCellXfs );
  677. }
  678. return $this->addSheet($pSheet, $iSheetIndex);
  679. }
  680. /**
  681. * Get named ranges
  682. *
  683. * @return PHPExcel_NamedRange[]
  684. */
  685. public function getNamedRanges() {
  686. return $this->_namedRanges;
  687. }
  688. /**
  689. * Add named range
  690. *
  691. * @param PHPExcel_NamedRange $namedRange
  692. * @return PHPExcel
  693. */
  694. public function addNamedRange(PHPExcel_NamedRange $namedRange) {
  695. if ($namedRange->getScope() == null) {
  696. // global scope
  697. $this->_namedRanges[$namedRange->getName()] = $namedRange;
  698. } else {
  699. // local scope
  700. $this->_namedRanges[$namedRange->getScope()->getTitle().'!'.$namedRange->getName()] = $namedRange;
  701. }
  702. return true;
  703. }
  704. /**
  705. * Get named range
  706. *
  707. * @param string $namedRange
  708. * @param PHPExcel_Worksheet|null $pSheet Scope. Use null for global scope
  709. * @return PHPExcel_NamedRange|null
  710. */
  711. public function getNamedRange($namedRange, PHPExcel_Worksheet $pSheet = null) {
  712. $returnValue = null;
  713. if ($namedRange != '' && ($namedRange !== NULL)) {
  714. // first look for global defined name
  715. if (isset($this->_namedRanges[$namedRange])) {
  716. $returnValue = $this->_namedRanges[$namedRange];
  717. }
  718. // then look for local defined name (has priority over global defined name if both names exist)
  719. if (($pSheet !== NULL) && isset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
  720. $returnValue = $this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange];
  721. }
  722. }
  723. return $returnValue;
  724. }
  725. /**
  726. * Remove named range
  727. *
  728. * @param string $namedRange
  729. * @param PHPExcel_Worksheet|null $pSheet Scope: use null for global scope.
  730. * @return PHPExcel
  731. */
  732. public function removeNamedRange($namedRange, PHPExcel_Worksheet $pSheet = null) {
  733. if ($pSheet === NULL) {
  734. if (isset($this->_namedRanges[$namedRange])) {
  735. unset($this->_namedRanges[$namedRange]);
  736. }
  737. } else {
  738. if (isset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
  739. unset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange]);
  740. }
  741. }
  742. return $this;
  743. }
  744. /**
  745. * Get worksheet iterator
  746. *
  747. * @return PHPExcel_WorksheetIterator
  748. */
  749. public function getWorksheetIterator() {
  750. return new PHPExcel_WorksheetIterator($this);
  751. }
  752. /**
  753. * Copy workbook (!= clone!)
  754. *
  755. * @return PHPExcel
  756. */
  757. public function copy() {
  758. $copied = clone $this;
  759. $worksheetCount = count($this->_workSheetCollection);
  760. for ($i = 0; $i < $worksheetCount; ++$i) {
  761. $this->_workSheetCollection[$i] = $this->_workSheetCollection[$i]->copy();
  762. $this->_workSheetCollection[$i]->rebindParent($this);
  763. }
  764. return $copied;
  765. }
  766. /**
  767. * Implement PHP __clone to create a deep clone, not just a shallow copy.
  768. */
  769. public function __clone() {
  770. foreach($this as $key => $val) {
  771. if (is_object($val) || (is_array($val))) {
  772. $this->{$key} = unserialize(serialize($val));
  773. }
  774. }
  775. }
  776. /**
  777. * Get the workbook collection of cellXfs
  778. *
  779. * @return PHPExcel_Style[]
  780. */
  781. public function getCellXfCollection()
  782. {
  783. return $this->_cellXfCollection;
  784. }
  785. /**
  786. * Get cellXf by index
  787. *
  788. * @param int $pIndex
  789. * @return PHPExcel_Style
  790. */
  791. public function getCellXfByIndex($pIndex = 0)
  792. {
  793. return $this->_cellXfCollection[$pIndex];
  794. }
  795. /**
  796. * Get cellXf by hash code
  797. *
  798. * @param string $pValue
  799. * @return PHPExcel_Style|false
  800. */
  801. public function getCellXfByHashCode($pValue = '')
  802. {
  803. foreach ($this->_cellXfCollection as $cellXf) {
  804. if ($cellXf->getHashCode() == $pValue) {
  805. return $cellXf;
  806. }
  807. }
  808. return false;
  809. }
  810. /**
  811. * Check if style exists in style collection
  812. *
  813. * @param PHPExcel_Style $pCellStyle
  814. * @return boolean
  815. */
  816. public function cellXfExists($pCellStyle = null)
  817. {
  818. return in_array($pCellStyle, $this->_cellXfCollection, true);
  819. }
  820. /**
  821. * Get default style
  822. *
  823. * @return PHPExcel_Style
  824. * @throws PHPExcel_Exception
  825. */
  826. public function getDefaultStyle()
  827. {
  828. if (isset($this->_cellXfCollection[0])) {
  829. return $this->_cellXfCollection[0];
  830. }
  831. throw new PHPExcel_Exception('No default style found for this workbook');
  832. }
  833. /**
  834. * Add a cellXf to the workbook
  835. *
  836. * @param PHPExcel_Style $style
  837. */
  838. public function addCellXf(PHPExcel_Style $style)
  839. {
  840. $this->_cellXfCollection[] = $style;
  841. $style->setIndex(count($this->_cellXfCollection) - 1);
  842. }
  843. /**
  844. * Remove cellXf by index. It is ensured that all cells get their xf index updated.
  845. *
  846. * @param int $pIndex Index to cellXf
  847. * @throws PHPExcel_Exception
  848. */
  849. public function removeCellXfByIndex($pIndex = 0)
  850. {
  851. if ($pIndex > count($this->_cellXfCollection) - 1) {
  852. throw new PHPExcel_Exception("CellXf index is out of bounds.");
  853. } else {
  854. // first remove the cellXf
  855. array_splice($this->_cellXfCollection, $pIndex, 1);
  856. // then update cellXf indexes for cells
  857. foreach ($this->_workSheetCollection as $worksheet) {
  858. foreach ($worksheet->getCellCollection(false) as $cellID) {
  859. $cell = $worksheet->getCell($cellID);
  860. $xfIndex = $cell->getXfIndex();
  861. if ($xfIndex > $pIndex ) {
  862. // decrease xf index by 1
  863. $cell->setXfIndex($xfIndex - 1);
  864. } else if ($xfIndex == $pIndex) {
  865. // set to default xf index 0
  866. $cell->setXfIndex(0);
  867. }
  868. }
  869. }
  870. }
  871. }
  872. /**
  873. * Get the cellXf supervisor
  874. *
  875. * @return PHPExcel_Style
  876. */
  877. public function getCellXfSupervisor()
  878. {
  879. return $this->_cellXfSupervisor;
  880. }
  881. /**
  882. * Get the workbook collection of cellStyleXfs
  883. *
  884. * @return PHPExcel_Style[]
  885. */
  886. public function getCellStyleXfCollection()
  887. {
  888. return $this->_cellStyleXfCollection;
  889. }
  890. /**
  891. * Get cellStyleXf by index
  892. *
  893. * @param int $pIndex
  894. * @return PHPExcel_Style
  895. */
  896. public function getCellStyleXfByIndex($pIndex = 0)
  897. {
  898. return $this->_cellStyleXfCollection[$pIndex];
  899. }
  900. /**
  901. * Get cellStyleXf by hash code
  902. *
  903. * @param string $pValue
  904. * @return PHPExcel_Style|false
  905. */
  906. public function getCellStyleXfByHashCode($pValue = '')
  907. {
  908. foreach ($this->_cellStyleXfCollection as $cellStyleXf) {
  909. if ($cellStyleXf->getHashCode() == $pValue) {
  910. return $cellStyleXf;
  911. }
  912. }
  913. return false;
  914. }
  915. /**
  916. * Add a cellStyleXf to the workbook
  917. *
  918. * @param PHPExcel_Style $pStyle
  919. */
  920. public function addCellStyleXf(PHPExcel_Style $pStyle)
  921. {
  922. $this->_cellStyleXfCollection[] = $pStyle;
  923. $pStyle->setIndex(count($this->_cellStyleXfCollection) - 1);
  924. }
  925. /**
  926. * Remove cellStyleXf by index
  927. *
  928. * @param int $pIndex
  929. * @throws PHPExcel_Exception
  930. */
  931. public function removeCellStyleXfByIndex($pIndex = 0)
  932. {
  933. if ($pIndex > count($this->_cellStyleXfCollection) - 1) {
  934. throw new PHPExcel_Exception("CellStyleXf index is out of bounds.");
  935. } else {
  936. array_splice($this->_cellStyleXfCollection, $pIndex, 1);
  937. }
  938. }
  939. /**
  940. * Eliminate all unneeded cellXf and afterwards update the xfIndex for all cells
  941. * and columns in the workbook
  942. */
  943. public function garbageCollect()
  944. {
  945. // how many references are there to each cellXf ?
  946. $countReferencesCellXf = array();
  947. foreach ($this->_cellXfCollection as $index => $cellXf) {
  948. $countReferencesCellXf[$index] = 0;
  949. }
  950. foreach ($this->getWorksheetIterator() as $sheet) {
  951. // from cells
  952. foreach ($sheet->getCellCollection(false) as $cellID) {
  953. $cell = $sheet->getCell($cellID);
  954. ++$countReferencesCellXf[$cell->getXfIndex()];
  955. }
  956. // from row dimensions
  957. foreach ($sheet->getRowDimensions() as $rowDimension) {
  958. if ($rowDimension->getXfIndex() !== null) {
  959. ++$countReferencesCellXf[$rowDimension->getXfIndex()];
  960. }
  961. }
  962. // from column dimensions
  963. foreach ($sheet->getColumnDimensions() as $columnDimension) {
  964. ++$countReferencesCellXf[$columnDimension->getXfIndex()];
  965. }
  966. }
  967. // remove cellXfs without references and create mapping so we can update xfIndex
  968. // for all cells and columns
  969. $countNeededCellXfs = 0;
  970. foreach ($this->_cellXfCollection as $index => $cellXf) {
  971. if ($countReferencesCellXf[$index] > 0 || $index == 0) { // we must never remove the first cellXf
  972. ++$countNeededCellXfs;
  973. } else {
  974. unset($this->_cellXfCollection[$index]);
  975. }
  976. $map[$index] = $countNeededCellXfs - 1;
  977. }
  978. $this->_cellXfCollection = array_values($this->_cellXfCollection);
  979. // update the index for all cellXfs
  980. foreach ($this->_cellXfCollection as $i => $cellXf) {
  981. $cellXf->setIndex($i);
  982. }
  983. // make sure there is always at least one cellXf (there should be)
  984. if (empty($this->_cellXfCollection)) {
  985. $this->_cellXfCollection[] = new PHPExcel_Style();
  986. }
  987. // update the xfIndex for all cells, row dimensions, column dimensions
  988. foreach ($this->getWorksheetIterator() as $sheet) {
  989. // for all cells
  990. foreach ($sheet->getCellCollection(false) as $cellID) {
  991. $cell = $sheet->getCell($cellID);
  992. $cell->setXfIndex( $map[$cell->getXfIndex()] );
  993. }
  994. // for all row dimensions
  995. foreach ($sheet->getRowDimensions() as $rowDimension) {
  996. if ($rowDimension->getXfIndex() !== null) {
  997. $rowDimension->setXfIndex( $map[$rowDimension->getXfIndex()] );
  998. }
  999. }
  1000. // for all column dimensions
  1001. foreach ($sheet->getColumnDimensions() as $columnDimension) {
  1002. $columnDimension->setXfIndex( $map[$columnDimension->getXfIndex()] );
  1003. }
  1004. // also do garbage collection for all the sheets
  1005. $sheet->garbageCollect();
  1006. }
  1007. }
  1008. /**
  1009. * Return the unique ID value assigned to this spreadsheet workbook
  1010. *
  1011. * @return string
  1012. */
  1013. public function getID() {
  1014. return $this->_uniqueID;
  1015. }
  1016. }