1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26:
27:
28:
29:
30: if (!defined('PHPEXCEL_ROOT')) {
31: define('PHPEXCEL_ROOT', dirname(__FILE__) . '/');
32: require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
33: }
34:
35:
36: 37: 38: 39: 40: 41: 42:
43: class PHPExcel
44: {
45: 46: 47: 48: 49:
50: private $_uniqueID;
51:
52: 53: 54: 55: 56:
57: private $_properties;
58:
59: 60: 61: 62: 63:
64: private $_security;
65:
66: 67: 68: 69: 70:
71: private $_workSheetCollection = array();
72:
73: 74: 75: 76: 77:
78: private $_calculationEngine = NULL;
79:
80: 81: 82: 83: 84:
85: private $_activeSheetIndex = 0;
86:
87: 88: 89: 90: 91:
92: private $_namedRanges = array();
93:
94: 95: 96: 97: 98:
99: private $_cellXfSupervisor;
100:
101: 102: 103: 104: 105:
106: private $_cellXfCollection = array();
107:
108: 109: 110: 111: 112:
113: private $_cellStyleXfCollection = array();
114:
115: 116: 117: 118: 119:
120: private $_hasMacros = FALSE;
121:
122: 123: 124: 125: 126:
127: private $_macrosCode=NULL;
128: 129: 130: 131: 132:
133: private $_macrosCertificate=NULL;
134:
135: 136: 137: 138: 139:
140: private $_ribbonXMLData=NULL;
141:
142: 143: 144: 145: 146: 147:
148: private $_ribbonBinObjects=NULL;
149:
150: 151: 152: 153: 154:
155: public function hasMacros(){
156: return $this->_hasMacros;
157: }
158:
159: 160: 161: 162: 163:
164: public function setHasMacros($hasMacros=false){
165: $this->_hasMacros=(bool)$hasMacros;
166: }
167:
168: 169: 170: 171: 172:
173: public function setMacrosCode($MacrosCode){
174: $this->_macrosCode=$MacrosCode;
175: $this->setHasMacros(!is_null($MacrosCode));
176: }
177:
178: 179: 180: 181: 182:
183: public function getMacrosCode(){
184: return $this->_macrosCode;
185: }
186:
187: 188: 189: 190: 191:
192: public function setMacrosCertificate($Certificate=NULL){
193: $this->_macrosCertificate=$Certificate;
194: }
195:
196: 197: 198: 199: 200:
201: public function hasMacrosCertificate(){
202: return !is_null($this->_macrosCertificate);
203: }
204:
205: 206: 207: 208: 209:
210: public function getMacrosCertificate(){
211: return $this->_macrosCertificate;
212: }
213:
214: 215: 216: 217: 218: 219:
220: public function discardMacros(){
221: $this->_hasMacros=false;
222: $this->_macrosCode=NULL;
223: $this->_macrosCertificate=NULL;
224: }
225:
226: 227: 228: 229:
230: public function setRibbonXMLData($Target=NULL, $XMLData=NULL){
231: if(!is_null($Target) && !is_null($XMLData)){
232: $this->_ribbonXMLData=array('target'=>$Target, 'data'=>$XMLData);
233: }else{
234: $this->_ribbonXMLData=NULL;
235: }
236: }
237:
238: 239: 240: 241: 242:
243: public function getRibbonXMLData($What='all'){
244: $ReturnData=NULL;
245: $What=strtolower($What);
246: switch($What){
247: case 'all':
248: $ReturnData=$this->_ribbonXMLData;
249: break;
250: case 'target':
251: case 'data':
252: if(is_array($this->_ribbonXMLData) && array_key_exists($What,$this->_ribbonXMLData)){
253: $ReturnData=$this->_ribbonXMLData[$What];
254: }
255: break;
256: }
257: return $ReturnData;
258: }
259:
260: 261: 262: 263:
264: public function setRibbonBinObjects($BinObjectsNames=NULL, $BinObjectsData=NULL){
265: if(!is_null($BinObjectsNames) && !is_null($BinObjectsData)){
266: $this->_ribbonBinObjects=array('names'=>$BinObjectsNames, 'data'=>$BinObjectsData);
267: }else{
268: $this->_ribbonBinObjects=NULL;
269: }
270: }
271: 272: 273: 274:
275: private function _getExtensionOnly($ThePath){
276: return pathinfo($ThePath, PATHINFO_EXTENSION);
277: }
278:
279: 280: 281: 282:
283: public function getRibbonBinObjects($What='all'){
284: $ReturnData=NULL;
285: $What=strtolower($What);
286: switch($What){
287: case 'all':
288: return $this->_ribbonBinObjects;
289: break;
290: case 'names':
291: case 'data':
292: if(is_array($this->_ribbonBinObjects) && array_key_exists($What, $this->_ribbonBinObjects)){
293: $ReturnData=$this->_ribbonBinObjects[$What];
294: }
295: break;
296: case 'types':
297: if(is_array($this->_ribbonBinObjects) && array_key_exists('data', $this->_ribbonBinObjects) && is_array($this->_ribbonBinObjects['data'])){
298: $tmpTypes=array_keys($this->_ribbonBinObjects['data']);
299: $ReturnData=array_unique(array_map(array($this,'_getExtensionOnly'), $tmpTypes));
300: }else
301: $ReturnData=array();
302: break;
303: }
304: return $ReturnData;
305: }
306:
307: 308: 309: 310: 311:
312: public function hasRibbon(){
313: return !is_null($this->_ribbonXMLData);
314: }
315:
316: 317: 318: 319: 320:
321: public function hasRibbonBinObjects(){
322: return !is_null($this->_ribbonBinObjects);
323: }
324:
325: 326: 327: 328: 329: 330:
331: public function sheetCodeNameExists($pSheetCodeName)
332: {
333: return ($this->getSheetByCodeName($pSheetCodeName) !== NULL);
334: }
335:
336: 337: 338: 339: 340: 341:
342: public function getSheetByCodeName($pName = '')
343: {
344: $worksheetCount = count($this->_workSheetCollection);
345: for ($i = 0; $i < $worksheetCount; ++$i) {
346: if ($this->_workSheetCollection[$i]->getCodeName() == $pName) {
347: return $this->_workSheetCollection[$i];
348: }
349: }
350:
351: return null;
352: }
353:
354: 355: 356:
357: public function __construct()
358: {
359: $this->_uniqueID = uniqid();
360: $this->_calculationEngine = PHPExcel_Calculation::getInstance($this);
361:
362:
363: $this->_workSheetCollection = array();
364: $this->_workSheetCollection[] = new PHPExcel_Worksheet($this);
365: $this->_activeSheetIndex = 0;
366:
367:
368: $this->_properties = new PHPExcel_DocumentProperties();
369:
370:
371: $this->_security = new PHPExcel_DocumentSecurity();
372:
373:
374: $this->_namedRanges = array();
375:
376:
377: $this->_cellXfSupervisor = new PHPExcel_Style(true);
378: $this->_cellXfSupervisor->bindParent($this);
379:
380:
381: $this->addCellXf(new PHPExcel_Style);
382: $this->addCellStyleXf(new PHPExcel_Style);
383: }
384:
385: 386: 387: 388:
389: public function __destruct() {
390: PHPExcel_Calculation::unsetInstance($this);
391: $this->disconnectWorksheets();
392: }
393:
394: 395: 396: 397: 398:
399: public function disconnectWorksheets()
400: {
401: $worksheet = NULL;
402: foreach($this->_workSheetCollection as $k => &$worksheet) {
403: $worksheet->disconnectCells();
404: $this->_workSheetCollection[$k] = null;
405: }
406: unset($worksheet);
407: $this->_workSheetCollection = array();
408: }
409:
410: 411: 412: 413: 414:
415: public function getCalculationEngine()
416: {
417: return $this->_calculationEngine;
418: }
419:
420: 421: 422: 423: 424:
425: public function getProperties()
426: {
427: return $this->_properties;
428: }
429:
430: 431: 432: 433: 434:
435: public function setProperties(PHPExcel_DocumentProperties $pValue)
436: {
437: $this->_properties = $pValue;
438: }
439:
440: 441: 442: 443: 444:
445: public function getSecurity()
446: {
447: return $this->_security;
448: }
449:
450: 451: 452: 453: 454:
455: public function setSecurity(PHPExcel_DocumentSecurity $pValue)
456: {
457: $this->_security = $pValue;
458: }
459:
460: 461: 462: 463: 464:
465: public function getActiveSheet()
466: {
467: return $this->_workSheetCollection[$this->_activeSheetIndex];
468: }
469:
470: 471: 472: 473: 474: 475: 476:
477: public function createSheet($iSheetIndex = NULL)
478: {
479: $newSheet = new PHPExcel_Worksheet($this);
480: $this->addSheet($newSheet, $iSheetIndex);
481: return $newSheet;
482: }
483:
484: 485: 486: 487: 488: 489:
490: public function sheetNameExists($pSheetName)
491: {
492: return ($this->getSheetByName($pSheetName) !== NULL);
493: }
494:
495: 496: 497: 498: 499: 500: 501: 502:
503: public function addSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = NULL)
504: {
505: if ($this->sheetNameExists($pSheet->getTitle())) {
506: throw new PHPExcel_Exception(
507: "Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename this worksheet first."
508: );
509: }
510:
511: if($iSheetIndex === NULL) {
512: if ($this->_activeSheetIndex < 0) {
513: $this->_activeSheetIndex = 0;
514: }
515: $this->_workSheetCollection[] = $pSheet;
516: } else {
517:
518: array_splice(
519: $this->_workSheetCollection,
520: $iSheetIndex,
521: 0,
522: array($pSheet)
523: );
524:
525:
526: if ($this->_activeSheetIndex >= $iSheetIndex) {
527: ++$this->_activeSheetIndex;
528: }
529: }
530:
531: if ($pSheet->getParent() === null) {
532: $pSheet->rebindParent($this);
533: }
534:
535: return $pSheet;
536: }
537:
538: 539: 540: 541: 542: 543:
544: public function removeSheetByIndex($pIndex = 0)
545: {
546:
547: $numSheets = count($this->_workSheetCollection);
548:
549: if ($pIndex > $numSheets - 1) {
550: throw new PHPExcel_Exception(
551: "You tried to remove a sheet by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
552: );
553: } else {
554: array_splice($this->_workSheetCollection, $pIndex, 1);
555: }
556:
557: if (($this->_activeSheetIndex >= $pIndex) &&
558: ($pIndex > count($this->_workSheetCollection) - 1)) {
559: --$this->_activeSheetIndex;
560: }
561:
562: }
563:
564: 565: 566: 567: 568: 569: 570:
571: public function getSheet($pIndex = 0)
572: {
573:
574: $numSheets = count($this->_workSheetCollection);
575:
576: if ($pIndex > $numSheets - 1) {
577: throw new PHPExcel_Exception(
578: "Your requested sheet index: {$pIndex} is out of bounds. The actual number of sheets is {$numSheets}."
579: );
580: } else {
581: return $this->_workSheetCollection[$pIndex];
582: }
583: }
584:
585: 586: 587: 588: 589:
590: public function getAllSheets()
591: {
592: return $this->_workSheetCollection;
593: }
594:
595: 596: 597: 598: 599: 600:
601: public function getSheetByName($pName = '')
602: {
603: $worksheetCount = count($this->_workSheetCollection);
604: for ($i = 0; $i < $worksheetCount; ++$i) {
605: if ($this->_workSheetCollection[$i]->getTitle() === $pName) {
606: return $this->_workSheetCollection[$i];
607: }
608: }
609:
610: return NULL;
611: }
612:
613: 614: 615: 616: 617: 618: 619:
620: public function getIndex(PHPExcel_Worksheet $pSheet)
621: {
622: foreach ($this->_workSheetCollection as $key => $value) {
623: if ($value->getHashCode() == $pSheet->getHashCode()) {
624: return $key;
625: }
626: }
627:
628: throw new PHPExcel_Exception("Sheet does not exist.");
629: }
630:
631: 632: 633: 634: 635: 636: 637: 638:
639: public function setIndexByName($sheetName, $newIndex)
640: {
641: $oldIndex = $this->getIndex($this->getSheetByName($sheetName));
642: $pSheet = array_splice(
643: $this->_workSheetCollection,
644: $oldIndex,
645: 1
646: );
647: array_splice(
648: $this->_workSheetCollection,
649: $newIndex,
650: 0,
651: $pSheet
652: );
653: return $newIndex;
654: }
655:
656: 657: 658: 659: 660:
661: public function getSheetCount()
662: {
663: return count($this->_workSheetCollection);
664: }
665:
666: 667: 668: 669: 670:
671: public function getActiveSheetIndex()
672: {
673: return $this->_activeSheetIndex;
674: }
675:
676: 677: 678: 679: 680: 681: 682:
683: public function setActiveSheetIndex($pIndex = 0)
684: {
685: $numSheets = count($this->_workSheetCollection);
686:
687: if ($pIndex > $numSheets - 1) {
688: throw new PHPExcel_Exception(
689: "You tried to set a sheet active by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
690: );
691: } else {
692: $this->_activeSheetIndex = $pIndex;
693: }
694: return $this->getActiveSheet();
695: }
696:
697: 698: 699: 700: 701: 702: 703:
704: public function setActiveSheetIndexByName($pValue = '')
705: {
706: if (($worksheet = $this->getSheetByName($pValue)) instanceof PHPExcel_Worksheet) {
707: $this->setActiveSheetIndex($this->getIndex($worksheet));
708: return $worksheet;
709: }
710:
711: throw new PHPExcel_Exception('Workbook does not contain sheet:' . $pValue);
712: }
713:
714: 715: 716: 717: 718:
719: public function getSheetNames()
720: {
721: $returnValue = array();
722: $worksheetCount = $this->getSheetCount();
723: for ($i = 0; $i < $worksheetCount; ++$i) {
724: $returnValue[] = $this->getSheet($i)->getTitle();
725: }
726:
727: return $returnValue;
728: }
729:
730: 731: 732: 733: 734: 735: 736: 737:
738: public function addExternalSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = null) {
739: if ($this->sheetNameExists($pSheet->getTitle())) {
740: throw new PHPExcel_Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
741: }
742:
743:
744: $countCellXfs = count($this->_cellXfCollection);
745:
746:
747: foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) {
748: $this->addCellXf(clone $cellXf);
749: }
750:
751:
752: $pSheet->rebindParent($this);
753:
754:
755: foreach ($pSheet->getCellCollection(false) as $cellID) {
756: $cell = $pSheet->getCell($cellID);
757: $cell->setXfIndex( $cell->getXfIndex() + $countCellXfs );
758: }
759:
760: return $this->addSheet($pSheet, $iSheetIndex);
761: }
762:
763: 764: 765: 766: 767:
768: public function getNamedRanges() {
769: return $this->_namedRanges;
770: }
771:
772: 773: 774: 775: 776: 777:
778: public function addNamedRange(PHPExcel_NamedRange $namedRange) {
779: if ($namedRange->getScope() == null) {
780:
781: $this->_namedRanges[$namedRange->getName()] = $namedRange;
782: } else {
783:
784: $this->_namedRanges[$namedRange->getScope()->getTitle().'!'.$namedRange->getName()] = $namedRange;
785: }
786: return true;
787: }
788:
789: 790: 791: 792: 793: 794: 795:
796: public function getNamedRange($namedRange, PHPExcel_Worksheet $pSheet = null) {
797: $returnValue = null;
798:
799: if ($namedRange != '' && ($namedRange !== NULL)) {
800:
801: if (isset($this->_namedRanges[$namedRange])) {
802: $returnValue = $this->_namedRanges[$namedRange];
803: }
804:
805:
806: if (($pSheet !== NULL) && isset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
807: $returnValue = $this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange];
808: }
809: }
810:
811: return $returnValue;
812: }
813:
814: 815: 816: 817: 818: 819: 820:
821: public function removeNamedRange($namedRange, PHPExcel_Worksheet $pSheet = null) {
822: if ($pSheet === NULL) {
823: if (isset($this->_namedRanges[$namedRange])) {
824: unset($this->_namedRanges[$namedRange]);
825: }
826: } else {
827: if (isset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
828: unset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange]);
829: }
830: }
831: return $this;
832: }
833:
834: 835: 836: 837: 838:
839: public function getWorksheetIterator() {
840: return new PHPExcel_WorksheetIterator($this);
841: }
842:
843: 844: 845: 846: 847:
848: public function copy() {
849: $copied = clone $this;
850:
851: $worksheetCount = count($this->_workSheetCollection);
852: for ($i = 0; $i < $worksheetCount; ++$i) {
853: $this->_workSheetCollection[$i] = $this->_workSheetCollection[$i]->copy();
854: $this->_workSheetCollection[$i]->rebindParent($this);
855: }
856:
857: return $copied;
858: }
859:
860: 861: 862:
863: public function __clone() {
864: foreach($this as $key => $val) {
865: if (is_object($val) || (is_array($val))) {
866: $this->{$key} = unserialize(serialize($val));
867: }
868: }
869: }
870:
871: 872: 873: 874: 875:
876: public function getCellXfCollection()
877: {
878: return $this->_cellXfCollection;
879: }
880:
881: 882: 883: 884: 885: 886:
887: public function getCellXfByIndex($pIndex = 0)
888: {
889: return $this->_cellXfCollection[$pIndex];
890: }
891:
892: 893: 894: 895: 896: 897:
898: public function getCellXfByHashCode($pValue = '')
899: {
900: foreach ($this->_cellXfCollection as $cellXf) {
901: if ($cellXf->getHashCode() == $pValue) {
902: return $cellXf;
903: }
904: }
905: return false;
906: }
907:
908: 909: 910: 911: 912: 913:
914: public function cellXfExists($pCellStyle = null)
915: {
916: return in_array($pCellStyle, $this->_cellXfCollection, true);
917: }
918:
919: 920: 921: 922: 923: 924:
925: public function getDefaultStyle()
926: {
927: if (isset($this->_cellXfCollection[0])) {
928: return $this->_cellXfCollection[0];
929: }
930: throw new PHPExcel_Exception('No default style found for this workbook');
931: }
932:
933: 934: 935: 936: 937:
938: public function addCellXf(PHPExcel_Style $style)
939: {
940: $this->_cellXfCollection[] = $style;
941: $style->setIndex(count($this->_cellXfCollection) - 1);
942: }
943:
944: 945: 946: 947: 948: 949:
950: public function removeCellXfByIndex($pIndex = 0)
951: {
952: if ($pIndex > count($this->_cellXfCollection) - 1) {
953: throw new PHPExcel_Exception("CellXf index is out of bounds.");
954: } else {
955:
956: array_splice($this->_cellXfCollection, $pIndex, 1);
957:
958:
959: foreach ($this->_workSheetCollection as $worksheet) {
960: foreach ($worksheet->getCellCollection(false) as $cellID) {
961: $cell = $worksheet->getCell($cellID);
962: $xfIndex = $cell->getXfIndex();
963: if ($xfIndex > $pIndex ) {
964:
965: $cell->setXfIndex($xfIndex - 1);
966: } else if ($xfIndex == $pIndex) {
967:
968: $cell->setXfIndex(0);
969: }
970: }
971: }
972: }
973: }
974:
975: 976: 977: 978: 979:
980: public function getCellXfSupervisor()
981: {
982: return $this->_cellXfSupervisor;
983: }
984:
985: 986: 987: 988: 989:
990: public function getCellStyleXfCollection()
991: {
992: return $this->_cellStyleXfCollection;
993: }
994:
995: 996: 997: 998: 999: 1000:
1001: public function getCellStyleXfByIndex($pIndex = 0)
1002: {
1003: return $this->_cellStyleXfCollection[$pIndex];
1004: }
1005:
1006: 1007: 1008: 1009: 1010: 1011:
1012: public function getCellStyleXfByHashCode($pValue = '')
1013: {
1014: foreach ($this->_cellXfStyleCollection as $cellStyleXf) {
1015: if ($cellStyleXf->getHashCode() == $pValue) {
1016: return $cellStyleXf;
1017: }
1018: }
1019: return false;
1020: }
1021:
1022: 1023: 1024: 1025: 1026:
1027: public function addCellStyleXf(PHPExcel_Style $pStyle)
1028: {
1029: $this->_cellStyleXfCollection[] = $pStyle;
1030: $pStyle->setIndex(count($this->_cellStyleXfCollection) - 1);
1031: }
1032:
1033: 1034: 1035: 1036: 1037: 1038:
1039: public function removeCellStyleXfByIndex($pIndex = 0)
1040: {
1041: if ($pIndex > count($this->_cellStyleXfCollection) - 1) {
1042: throw new PHPExcel_Exception("CellStyleXf index is out of bounds.");
1043: } else {
1044: array_splice($this->_cellStyleXfCollection, $pIndex, 1);
1045: }
1046: }
1047:
1048: 1049: 1050: 1051:
1052: public function garbageCollect()
1053: {
1054:
1055: $countReferencesCellXf = array();
1056: foreach ($this->_cellXfCollection as $index => $cellXf) {
1057: $countReferencesCellXf[$index] = 0;
1058: }
1059:
1060: foreach ($this->getWorksheetIterator() as $sheet) {
1061:
1062:
1063: foreach ($sheet->getCellCollection(false) as $cellID) {
1064: $cell = $sheet->getCell($cellID);
1065: ++$countReferencesCellXf[$cell->getXfIndex()];
1066: }
1067:
1068:
1069: foreach ($sheet->getRowDimensions() as $rowDimension) {
1070: if ($rowDimension->getXfIndex() !== null) {
1071: ++$countReferencesCellXf[$rowDimension->getXfIndex()];
1072: }
1073: }
1074:
1075:
1076: foreach ($sheet->getColumnDimensions() as $columnDimension) {
1077: ++$countReferencesCellXf[$columnDimension->getXfIndex()];
1078: }
1079: }
1080:
1081:
1082:
1083: $countNeededCellXfs = 0;
1084: foreach ($this->_cellXfCollection as $index => $cellXf) {
1085: if ($countReferencesCellXf[$index] > 0 || $index == 0) {
1086: ++$countNeededCellXfs;
1087: } else {
1088: unset($this->_cellXfCollection[$index]);
1089: }
1090: $map[$index] = $countNeededCellXfs - 1;
1091: }
1092: $this->_cellXfCollection = array_values($this->_cellXfCollection);
1093:
1094:
1095: foreach ($this->_cellXfCollection as $i => $cellXf) {
1096: $cellXf->setIndex($i);
1097: }
1098:
1099:
1100: if (empty($this->_cellXfCollection)) {
1101: $this->_cellXfCollection[] = new PHPExcel_Style();
1102: }
1103:
1104:
1105: foreach ($this->getWorksheetIterator() as $sheet) {
1106:
1107:
1108: foreach ($sheet->getCellCollection(false) as $cellID) {
1109: $cell = $sheet->getCell($cellID);
1110: $cell->setXfIndex( $map[$cell->getXfIndex()] );
1111: }
1112:
1113:
1114: foreach ($sheet->getRowDimensions() as $rowDimension) {
1115: if ($rowDimension->getXfIndex() !== null) {
1116: $rowDimension->setXfIndex( $map[$rowDimension->getXfIndex()] );
1117: }
1118: }
1119:
1120:
1121: foreach ($sheet->getColumnDimensions() as $columnDimension) {
1122: $columnDimension->setXfIndex( $map[$columnDimension->getXfIndex()] );
1123: }
1124:
1125:
1126: $sheet->garbageCollect();
1127: }
1128: }
1129:
1130: 1131: 1132: 1133: 1134:
1135: public function getID() {
1136: return $this->_uniqueID;
1137: }
1138:
1139: }
1140: