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: 31: 32: 33: 34: 35:
36: class PHPExcel_Worksheet implements PHPExcel_IComparable
37: {
38:
39: const BREAK_NONE = 0;
40: const BREAK_ROW = 1;
41: const BREAK_COLUMN = 2;
42:
43:
44: const SHEETSTATE_VISIBLE = 'visible';
45: const SHEETSTATE_HIDDEN = 'hidden';
46: const SHEETSTATE_VERYHIDDEN = 'veryHidden';
47:
48: 49: 50: 51: 52:
53: private static $_invalidCharacters = array('*', ':', '/', '\\', '?', '[', ']');
54:
55: 56: 57: 58: 59:
60: private $_parent;
61:
62: 63: 64: 65: 66:
67: private $_cellCollection = null;
68:
69: 70: 71: 72: 73:
74: private $_rowDimensions = array();
75:
76: 77: 78: 79: 80:
81: private $_defaultRowDimension = null;
82:
83: 84: 85: 86: 87:
88: private $_columnDimensions = array();
89:
90: 91: 92: 93: 94:
95: private $_defaultColumnDimension = null;
96:
97: 98: 99: 100: 101:
102: private $_drawingCollection = null;
103:
104: 105: 106: 107: 108:
109: private $_chartCollection = array();
110:
111: 112: 113: 114: 115:
116: private $_title;
117:
118: 119: 120: 121: 122:
123: private $_sheetState;
124:
125: 126: 127: 128: 129:
130: private $_pageSetup;
131:
132: 133: 134: 135: 136:
137: private $_pageMargins;
138:
139: 140: 141: 142: 143:
144: private $_headerFooter;
145:
146: 147: 148: 149: 150:
151: private $_sheetView;
152:
153: 154: 155: 156: 157:
158: private $_protection;
159:
160: 161: 162: 163: 164:
165: private $_styles = array();
166:
167: 168: 169: 170: 171:
172: private $_conditionalStylesCollection = array();
173:
174: 175: 176: 177: 178:
179: private $_cellCollectionIsSorted = false;
180:
181: 182: 183: 184: 185:
186: private $_breaks = array();
187:
188: 189: 190: 191: 192:
193: private $_mergeCells = array();
194:
195: 196: 197: 198: 199:
200: private $_protectedCells = array();
201:
202: 203: 204: 205: 206:
207: private $_autoFilter = NULL;
208:
209: 210: 211: 212: 213:
214: private $_freezePane = '';
215:
216: 217: 218: 219: 220:
221: private $_showGridlines = true;
222:
223: 224: 225: 226: 227:
228: private $_printGridlines = false;
229:
230: 231: 232: 233: 234:
235: private $_showRowColHeaders = true;
236:
237: 238: 239: 240: 241:
242: private $_showSummaryBelow = true;
243:
244: 245: 246: 247: 248:
249: private $_showSummaryRight = true;
250:
251: 252: 253: 254: 255:
256: private $_comments = array();
257:
258: 259: 260: 261: 262:
263: private $_activeCell = 'A1';
264:
265: 266: 267: 268: 269:
270: private $_selectedCells = 'A1';
271:
272: 273: 274: 275: 276:
277: private $_cachedHighestColumn = 'A';
278:
279: 280: 281: 282: 283:
284: private $_cachedHighestRow = 1;
285:
286: 287: 288: 289: 290:
291: private $_rightToLeft = false;
292:
293: 294: 295: 296: 297:
298: private $_hyperlinkCollection = array();
299:
300: 301: 302: 303: 304:
305: private $_dataValidationCollection = array();
306:
307: 308: 309: 310: 311:
312: private $_tabColor;
313:
314: 315: 316: 317: 318:
319: private $_dirty = true;
320:
321: 322: 323: 324: 325:
326: private $_hash = null;
327:
328: 329: 330: 331: 332:
333: private $_codeName = null;
334:
335: 336: 337: 338: 339: 340:
341: public function __construct(PHPExcel $pParent = null, $pTitle = 'Worksheet')
342: {
343:
344: $this->_parent = $pParent;
345: $this->setTitle($pTitle, FALSE);
346:
347: $this->setCodeName($this->getTitle());
348: $this->setSheetState(PHPExcel_Worksheet::SHEETSTATE_VISIBLE);
349:
350: $this->_cellCollection = PHPExcel_CachedObjectStorageFactory::getInstance($this);
351:
352:
353: $this->_pageSetup = new PHPExcel_Worksheet_PageSetup();
354:
355:
356: $this->_pageMargins = new PHPExcel_Worksheet_PageMargins();
357:
358:
359: $this->_headerFooter = new PHPExcel_Worksheet_HeaderFooter();
360:
361:
362: $this->_sheetView = new PHPExcel_Worksheet_SheetView();
363:
364:
365: $this->_drawingCollection = new ArrayObject();
366:
367:
368: $this->_chartCollection = new ArrayObject();
369:
370:
371: $this->_protection = new PHPExcel_Worksheet_Protection();
372:
373:
374: $this->_defaultRowDimension = new PHPExcel_Worksheet_RowDimension(NULL);
375:
376:
377: $this->_defaultColumnDimension = new PHPExcel_Worksheet_ColumnDimension(NULL);
378:
379: $this->_autoFilter = new PHPExcel_Worksheet_AutoFilter(NULL, $this);
380: }
381:
382:
383: 384: 385: 386: 387:
388: public function disconnectCells() {
389: if ( $this->_cellCollection !== NULL){
390: $this->_cellCollection->unsetWorksheetCells();
391: $this->_cellCollection = NULL;
392: }
393:
394: $this->_parent = null;
395: }
396:
397: 398: 399: 400:
401: function __destruct() {
402: PHPExcel_Calculation::getInstance($this->_parent)
403: ->clearCalculationCacheForWorksheet($this->_title);
404:
405: $this->disconnectCells();
406: }
407:
408: 409: 410: 411: 412:
413: public function getCellCacheController() {
414: return $this->_cellCollection;
415: }
416:
417:
418: 419: 420: 421: 422:
423: public static function getInvalidCharacters()
424: {
425: return self::$_invalidCharacters;
426: }
427:
428: 429: 430: 431: 432: 433: 434:
435: private static function _checkSheetCodeName($pValue)
436: {
437: $CharCount = PHPExcel_Shared_String::CountCharacters($pValue);
438: if ($CharCount == 0) {
439: throw new PHPExcel_Exception('Sheet code name cannot be empty.');
440: }
441:
442: if ((str_replace(self::$_invalidCharacters, '', $pValue) !== $pValue) ||
443: (PHPExcel_Shared_String::Substring($pValue,-1,1)=='\'') ||
444: (PHPExcel_Shared_String::Substring($pValue,0,1)=='\'')) {
445: throw new PHPExcel_Exception('Invalid character found in sheet code name');
446: }
447:
448:
449: if ($CharCount > 31) {
450: throw new PHPExcel_Exception('Maximum 31 characters allowed in sheet code name.');
451: }
452:
453: return $pValue;
454: }
455:
456: 457: 458: 459: 460: 461: 462:
463: private static function _checkSheetTitle($pValue)
464: {
465:
466: if (str_replace(self::$_invalidCharacters, '', $pValue) !== $pValue) {
467: throw new PHPExcel_Exception('Invalid character found in sheet title');
468: }
469:
470:
471: if (PHPExcel_Shared_String::CountCharacters($pValue) > 31) {
472: throw new PHPExcel_Exception('Maximum 31 characters allowed in sheet title.');
473: }
474:
475: return $pValue;
476: }
477:
478: 479: 480: 481: 482: 483:
484: public function getCellCollection($pSorted = true)
485: {
486: if ($pSorted) {
487:
488: return $this->sortCellCollection();
489: }
490: if ($this->_cellCollection !== NULL) {
491: return $this->_cellCollection->getCellList();
492: }
493: return array();
494: }
495:
496: 497: 498: 499: 500:
501: public function sortCellCollection()
502: {
503: if ($this->_cellCollection !== NULL) {
504: return $this->_cellCollection->getSortedCellList();
505: }
506: return array();
507: }
508:
509: 510: 511: 512: 513:
514: public function getRowDimensions()
515: {
516: return $this->_rowDimensions;
517: }
518:
519: 520: 521: 522: 523:
524: public function getDefaultRowDimension()
525: {
526: return $this->_defaultRowDimension;
527: }
528:
529: 530: 531: 532: 533:
534: public function getColumnDimensions()
535: {
536: return $this->_columnDimensions;
537: }
538:
539: 540: 541: 542: 543:
544: public function getDefaultColumnDimension()
545: {
546: return $this->_defaultColumnDimension;
547: }
548:
549: 550: 551: 552: 553:
554: public function getDrawingCollection()
555: {
556: return $this->_drawingCollection;
557: }
558:
559: 560: 561: 562: 563:
564: public function getChartCollection()
565: {
566: return $this->_chartCollection;
567: }
568:
569: 570: 571: 572: 573: 574: 575:
576: public function addChart(PHPExcel_Chart $pChart = null, $iChartIndex = null)
577: {
578: $pChart->setWorksheet($this);
579: if (is_null($iChartIndex)) {
580: $this->_chartCollection[] = $pChart;
581: } else {
582:
583: array_splice($this->_chartCollection, $iChartIndex, 0, array($pChart));
584: }
585:
586: return $pChart;
587: }
588:
589: 590: 591: 592: 593:
594: public function getChartCount()
595: {
596: return count($this->_chartCollection);
597: }
598:
599: 600: 601: 602: 603: 604: 605:
606: public function getChartByIndex($index = null)
607: {
608: $chartCount = count($this->_chartCollection);
609: if ($chartCount == 0) {
610: return false;
611: }
612: if (is_null($index)) {
613: $index = --$chartCount;
614: }
615: if (!isset($this->_chartCollection[$index])) {
616: return false;
617: }
618:
619: return $this->_chartCollection[$index];
620: }
621:
622: 623: 624: 625: 626: 627:
628: public function getChartNames()
629: {
630: $chartNames = array();
631: foreach($this->_chartCollection as $chart) {
632: $chartNames[] = $chart->getName();
633: }
634: return $chartNames;
635: }
636:
637: 638: 639: 640: 641: 642: 643:
644: public function getChartByName($chartName = '')
645: {
646: $chartCount = count($this->_chartCollection);
647: if ($chartCount == 0) {
648: return false;
649: }
650: foreach($this->_chartCollection as $index => $chart) {
651: if ($chart->getName() == $chartName) {
652: return $this->_chartCollection[$index];
653: }
654: }
655: return false;
656: }
657:
658: 659: 660: 661: 662:
663: public function refreshColumnDimensions()
664: {
665: $currentColumnDimensions = $this->getColumnDimensions();
666: $newColumnDimensions = array();
667:
668: foreach ($currentColumnDimensions as $objColumnDimension) {
669: $newColumnDimensions[$objColumnDimension->getColumnIndex()] = $objColumnDimension;
670: }
671:
672: $this->_columnDimensions = $newColumnDimensions;
673:
674: return $this;
675: }
676:
677: 678: 679: 680: 681:
682: public function refreshRowDimensions()
683: {
684: $currentRowDimensions = $this->getRowDimensions();
685: $newRowDimensions = array();
686:
687: foreach ($currentRowDimensions as $objRowDimension) {
688: $newRowDimensions[$objRowDimension->getRowIndex()] = $objRowDimension;
689: }
690:
691: $this->_rowDimensions = $newRowDimensions;
692:
693: return $this;
694: }
695:
696: 697: 698: 699: 700:
701: public function calculateWorksheetDimension()
702: {
703:
704: return 'A1' . ':' . $this->getHighestColumn() . $this->getHighestRow();
705: }
706:
707: 708: 709: 710: 711:
712: public function calculateWorksheetDataDimension()
713: {
714:
715: return 'A1' . ':' . $this->getHighestDataColumn() . $this->getHighestDataRow();
716: }
717:
718: 719: 720: 721: 722: 723:
724: public function calculateColumnWidths($calculateMergeCells = false)
725: {
726:
727: $autoSizes = array();
728: foreach ($this->getColumnDimensions() as $colDimension) {
729: if ($colDimension->getAutoSize()) {
730: $autoSizes[$colDimension->getColumnIndex()] = -1;
731: }
732: }
733:
734:
735: if (!empty($autoSizes)) {
736:
737:
738: $isMergeCell = array();
739: foreach ($this->getMergeCells() as $cells) {
740: foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
741: $isMergeCell[$cellReference] = true;
742: }
743: }
744:
745:
746: foreach ($this->getCellCollection(false) as $cellID) {
747: $cell = $this->getCell($cellID);
748: if (isset($autoSizes[$this->_cellCollection->getCurrentColumn()])) {
749:
750: if (!isset($isMergeCell[$this->_cellCollection->getCurrentAddress()])) {
751:
752:
753: $cellValue = PHPExcel_Style_NumberFormat::toFormattedString(
754: $cell->getCalculatedValue(),
755: $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode()
756: );
757:
758: $autoSizes[$this->_cellCollection->getCurrentColumn()] = max(
759: (float) $autoSizes[$this->_cellCollection->getCurrentColumn()],
760: (float)PHPExcel_Shared_Font::calculateColumnWidth(
761: $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont(),
762: $cellValue,
763: $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getAlignment()->getTextRotation(),
764: $this->getDefaultStyle()->getFont()
765: )
766: );
767: }
768: }
769: }
770:
771:
772: foreach ($autoSizes as $columnIndex => $width) {
773: if ($width == -1) $width = $this->getDefaultColumnDimension()->getWidth();
774: $this->getColumnDimension($columnIndex)->setWidth($width);
775: }
776: }
777:
778: return $this;
779: }
780:
781: 782: 783: 784: 785:
786: public function getParent() {
787: return $this->_parent;
788: }
789:
790: 791: 792: 793: 794: 795:
796: public function rebindParent(PHPExcel $parent) {
797: if ($this->_parent !== null) {
798: $namedRanges = $this->_parent->getNamedRanges();
799: foreach ($namedRanges as $namedRange) {
800: $parent->addNamedRange($namedRange);
801: }
802:
803: $this->_parent->removeSheetByIndex(
804: $this->_parent->getIndex($this)
805: );
806: }
807: $this->_parent = $parent;
808:
809: return $this;
810: }
811:
812: 813: 814: 815: 816:
817: public function getTitle()
818: {
819: return $this->_title;
820: }
821:
822: 823: 824: 825: 826: 827: 828: 829: 830: 831: 832:
833: public function setTitle($pValue = 'Worksheet', $updateFormulaCellReferences = true)
834: {
835:
836: if ($this->getTitle() == $pValue) {
837: return $this;
838: }
839:
840:
841: self::_checkSheetTitle($pValue);
842:
843:
844: $oldTitle = $this->getTitle();
845:
846: if ($this->_parent) {
847:
848: if ($this->_parent->sheetNameExists($pValue)) {
849:
850:
851: if (PHPExcel_Shared_String::CountCharacters($pValue) > 29) {
852: $pValue = PHPExcel_Shared_String::Substring($pValue,0,29);
853: }
854: $i = 1;
855: while ($this->_parent->sheetNameExists($pValue . ' ' . $i)) {
856: ++$i;
857: if ($i == 10) {
858: if (PHPExcel_Shared_String::CountCharacters($pValue) > 28) {
859: $pValue = PHPExcel_Shared_String::Substring($pValue,0,28);
860: }
861: } elseif ($i == 100) {
862: if (PHPExcel_Shared_String::CountCharacters($pValue) > 27) {
863: $pValue = PHPExcel_Shared_String::Substring($pValue,0,27);
864: }
865: }
866: }
867:
868: $altTitle = $pValue . ' ' . $i;
869: return $this->setTitle($altTitle,$updateFormulaCellReferences);
870: }
871: }
872:
873:
874: $this->_title = $pValue;
875: $this->_dirty = true;
876:
877: if ($this->_parent) {
878:
879: $newTitle = $this->getTitle();
880: PHPExcel_Calculation::getInstance($this->_parent)
881: ->renameCalculationCacheForWorksheet($oldTitle, $newTitle);
882: if ($updateFormulaCellReferences)
883: PHPExcel_ReferenceHelper::getInstance()->updateNamedFormulas($this->_parent, $oldTitle, $newTitle);
884: }
885:
886: return $this;
887: }
888:
889: 890: 891: 892: 893:
894: public function getSheetState() {
895: return $this->_sheetState;
896: }
897:
898: 899: 900: 901: 902: 903:
904: public function setSheetState($value = PHPExcel_Worksheet::SHEETSTATE_VISIBLE) {
905: $this->_sheetState = $value;
906: return $this;
907: }
908:
909: 910: 911: 912: 913:
914: public function getPageSetup()
915: {
916: return $this->_pageSetup;
917: }
918:
919: 920: 921: 922: 923: 924:
925: public function setPageSetup(PHPExcel_Worksheet_PageSetup $pValue)
926: {
927: $this->_pageSetup = $pValue;
928: return $this;
929: }
930:
931: 932: 933: 934: 935:
936: public function getPageMargins()
937: {
938: return $this->_pageMargins;
939: }
940:
941: 942: 943: 944: 945: 946:
947: public function setPageMargins(PHPExcel_Worksheet_PageMargins $pValue)
948: {
949: $this->_pageMargins = $pValue;
950: return $this;
951: }
952:
953: 954: 955: 956: 957:
958: public function getHeaderFooter()
959: {
960: return $this->_headerFooter;
961: }
962:
963: 964: 965: 966: 967: 968:
969: public function setHeaderFooter(PHPExcel_Worksheet_HeaderFooter $pValue)
970: {
971: $this->_headerFooter = $pValue;
972: return $this;
973: }
974:
975: 976: 977: 978: 979:
980: public function getSheetView()
981: {
982: return $this->_sheetView;
983: }
984:
985: 986: 987: 988: 989: 990:
991: public function setSheetView(PHPExcel_Worksheet_SheetView $pValue)
992: {
993: $this->_sheetView = $pValue;
994: return $this;
995: }
996:
997: 998: 999: 1000: 1001:
1002: public function getProtection()
1003: {
1004: return $this->_protection;
1005: }
1006:
1007: 1008: 1009: 1010: 1011: 1012:
1013: public function setProtection(PHPExcel_Worksheet_Protection $pValue)
1014: {
1015: $this->_protection = $pValue;
1016: $this->_dirty = true;
1017:
1018: return $this;
1019: }
1020:
1021: 1022: 1023: 1024: 1025: 1026: 1027:
1028: public function getHighestColumn($row = null)
1029: {
1030: if ($row == null) {
1031: return $this->_cachedHighestColumn;
1032: }
1033: return $this->getHighestDataColumn($row);
1034: }
1035:
1036: 1037: 1038: 1039: 1040: 1041: 1042:
1043: public function getHighestDataColumn($row = null)
1044: {
1045: return $this->_cellCollection->getHighestColumn($row);
1046: }
1047:
1048: 1049: 1050: 1051: 1052: 1053: 1054:
1055: public function getHighestRow($column = null)
1056: {
1057: if ($column == null) {
1058: return $this->_cachedHighestRow;
1059: }
1060: return $this->getHighestDataRow($column);
1061: }
1062:
1063: 1064: 1065: 1066: 1067: 1068: 1069:
1070: public function getHighestDataRow($column = null)
1071: {
1072: return $this->_cellCollection->getHighestRow($column);
1073: }
1074:
1075: 1076: 1077: 1078: 1079:
1080: public function getHighestRowAndColumn()
1081: {
1082: return $this->_cellCollection->getHighestRowAndColumn();
1083: }
1084:
1085: 1086: 1087: 1088: 1089: 1090: 1091: 1092:
1093: public function setCellValue($pCoordinate = 'A1', $pValue = null, $returnCell = false)
1094: {
1095: $cell = $this->getCell($pCoordinate)->setValue($pValue);
1096: return ($returnCell) ? $cell : $this;
1097: }
1098:
1099: 1100: 1101: 1102: 1103: 1104: 1105: 1106: 1107:
1108: public function setCellValueByColumnAndRow($pColumn = 0, $pRow = 1, $pValue = null, $returnCell = false)
1109: {
1110: $cell = $this->getCellByColumnAndRow($pColumn, $pRow)->setValue($pValue);
1111: return ($returnCell) ? $cell : $this;
1112: }
1113:
1114: 1115: 1116: 1117: 1118: 1119: 1120: 1121: 1122:
1123: public function setCellValueExplicit($pCoordinate = 'A1', $pValue = null, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING, $returnCell = false)
1124: {
1125:
1126: $cell = $this->getCell($pCoordinate)->setValueExplicit($pValue, $pDataType);
1127: return ($returnCell) ? $cell : $this;
1128: }
1129:
1130: 1131: 1132: 1133: 1134: 1135: 1136: 1137: 1138: 1139:
1140: public function setCellValueExplicitByColumnAndRow($pColumn = 0, $pRow = 1, $pValue = null, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING, $returnCell = false)
1141: {
1142: $cell = $this->getCellByColumnAndRow($pColumn, $pRow)->setValueExplicit($pValue, $pDataType);
1143: return ($returnCell) ? $cell : $this;
1144: }
1145:
1146: 1147: 1148: 1149: 1150: 1151: 1152:
1153: public function getCell($pCoordinate = 'A1')
1154: {
1155:
1156: if ($this->_cellCollection->isDataSet($pCoordinate)) {
1157: return $this->_cellCollection->getCacheData($pCoordinate);
1158: }
1159:
1160:
1161: if (strpos($pCoordinate, '!') !== false) {
1162: $worksheetReference = PHPExcel_Worksheet::extractSheetTitle($pCoordinate, true);
1163: return $this->_parent->getSheetByName($worksheetReference[0])->getCell($worksheetReference[1]);
1164: }
1165:
1166:
1167: if ((!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $pCoordinate, $matches)) &&
1168: (preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_NAMEDRANGE.'$/i', $pCoordinate, $matches))) {
1169: $namedRange = PHPExcel_NamedRange::resolveRange($pCoordinate, $this);
1170: if ($namedRange !== NULL) {
1171: $pCoordinate = $namedRange->getRange();
1172: return $namedRange->getWorksheet()->getCell($pCoordinate);
1173: }
1174: }
1175:
1176:
1177: $pCoordinate = strtoupper($pCoordinate);
1178:
1179: if (strpos($pCoordinate, ':') !== false || strpos($pCoordinate, ',') !== false) {
1180: throw new PHPExcel_Exception('Cell coordinate can not be a range of cells.');
1181: } elseif (strpos($pCoordinate, '$') !== false) {
1182: throw new PHPExcel_Exception('Cell coordinate must not be absolute.');
1183: }
1184:
1185:
1186: return $this->_createNewCell($pCoordinate);
1187: }
1188:
1189: 1190: 1191: 1192: 1193: 1194: 1195:
1196: public function getCellByColumnAndRow($pColumn = 0, $pRow = 1)
1197: {
1198: $columnLetter = PHPExcel_Cell::stringFromColumnIndex($pColumn);
1199: $coordinate = $columnLetter . $pRow;
1200:
1201: if ($this->_cellCollection->isDataSet($coordinate)) {
1202: return $this->_cellCollection->getCacheData($coordinate);
1203: }
1204:
1205: return $this->_createNewCell($coordinate);
1206: }
1207:
1208: 1209: 1210: 1211: 1212: 1213:
1214: private function _createNewCell($pCoordinate)
1215: {
1216: $cell = $this->_cellCollection->addCacheData(
1217: $pCoordinate,
1218: new PHPExcel_Cell(
1219: NULL,
1220: PHPExcel_Cell_DataType::TYPE_NULL,
1221: $this
1222: )
1223: );
1224: $this->_cellCollectionIsSorted = false;
1225:
1226:
1227: $aCoordinates = PHPExcel_Cell::coordinateFromString($pCoordinate);
1228: if (PHPExcel_Cell::columnIndexFromString($this->_cachedHighestColumn) < PHPExcel_Cell::columnIndexFromString($aCoordinates[0]))
1229: $this->_cachedHighestColumn = $aCoordinates[0];
1230: $this->_cachedHighestRow = max($this->_cachedHighestRow, $aCoordinates[1]);
1231:
1232:
1233:
1234: $rowDimension = $this->getRowDimension($aCoordinates[1], FALSE);
1235: $columnDimension = $this->getColumnDimension($aCoordinates[0], FALSE);
1236:
1237: if ($rowDimension !== NULL && $rowDimension->getXfIndex() > 0) {
1238:
1239: $cell->setXfIndex($rowDimension->getXfIndex());
1240: } elseif ($columnDimension !== NULL && $columnDimension->getXfIndex() > 0) {
1241:
1242: $cell->setXfIndex($columnDimension->getXfIndex());
1243: }
1244:
1245: return $cell;
1246: }
1247:
1248: 1249: 1250: 1251: 1252: 1253: 1254:
1255: public function cellExists($pCoordinate = 'A1')
1256: {
1257:
1258: if (strpos($pCoordinate, '!') !== false) {
1259: $worksheetReference = PHPExcel_Worksheet::extractSheetTitle($pCoordinate, true);
1260: return $this->_parent->getSheetByName($worksheetReference[0])->cellExists($worksheetReference[1]);
1261: }
1262:
1263:
1264: if ((!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $pCoordinate, $matches)) &&
1265: (preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_NAMEDRANGE.'$/i', $pCoordinate, $matches))) {
1266: $namedRange = PHPExcel_NamedRange::resolveRange($pCoordinate, $this);
1267: if ($namedRange !== NULL) {
1268: $pCoordinate = $namedRange->getRange();
1269: if ($this->getHashCode() != $namedRange->getWorksheet()->getHashCode()) {
1270: if (!$namedRange->getLocalOnly()) {
1271: return $namedRange->getWorksheet()->cellExists($pCoordinate);
1272: } else {
1273: throw new PHPExcel_Exception('Named range ' . $namedRange->getName() . ' is not accessible from within sheet ' . $this->getTitle());
1274: }
1275: }
1276: }
1277: else { return false; }
1278: }
1279:
1280:
1281: $pCoordinate = strtoupper($pCoordinate);
1282:
1283: if (strpos($pCoordinate,':') !== false || strpos($pCoordinate,',') !== false) {
1284: throw new PHPExcel_Exception('Cell coordinate can not be a range of cells.');
1285: } elseif (strpos($pCoordinate,'$') !== false) {
1286: throw new PHPExcel_Exception('Cell coordinate must not be absolute.');
1287: } else {
1288:
1289: $aCoordinates = PHPExcel_Cell::coordinateFromString($pCoordinate);
1290:
1291:
1292: return $this->_cellCollection->isDataSet($pCoordinate);
1293: }
1294: }
1295:
1296: 1297: 1298: 1299: 1300: 1301: 1302:
1303: public function cellExistsByColumnAndRow($pColumn = 0, $pRow = 1)
1304: {
1305: return $this->cellExists(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow);
1306: }
1307:
1308: 1309: 1310: 1311: 1312: 1313:
1314: public function getRowDimension($pRow = 1, $create = TRUE)
1315: {
1316:
1317: $found = null;
1318:
1319:
1320: if (!isset($this->_rowDimensions[$pRow])) {
1321: if (!$create)
1322: return NULL;
1323: $this->_rowDimensions[$pRow] = new PHPExcel_Worksheet_RowDimension($pRow);
1324:
1325: $this->_cachedHighestRow = max($this->_cachedHighestRow,$pRow);
1326: }
1327: return $this->_rowDimensions[$pRow];
1328: }
1329:
1330: 1331: 1332: 1333: 1334: 1335:
1336: public function getColumnDimension($pColumn = 'A', $create = TRUE)
1337: {
1338:
1339: $pColumn = strtoupper($pColumn);
1340:
1341:
1342: if (!isset($this->_columnDimensions[$pColumn])) {
1343: if (!$create)
1344: return NULL;
1345: $this->_columnDimensions[$pColumn] = new PHPExcel_Worksheet_ColumnDimension($pColumn);
1346:
1347: if (PHPExcel_Cell::columnIndexFromString($this->_cachedHighestColumn) < PHPExcel_Cell::columnIndexFromString($pColumn))
1348: $this->_cachedHighestColumn = $pColumn;
1349: }
1350: return $this->_columnDimensions[$pColumn];
1351: }
1352:
1353: 1354: 1355: 1356: 1357: 1358:
1359: public function getColumnDimensionByColumn($pColumn = 0)
1360: {
1361: return $this->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($pColumn));
1362: }
1363:
1364: 1365: 1366: 1367: 1368:
1369: public function getStyles()
1370: {
1371: return $this->_styles;
1372: }
1373:
1374: 1375: 1376: 1377: 1378: 1379: 1380:
1381: public function getDefaultStyle()
1382: {
1383: return $this->_parent->getDefaultStyle();
1384: }
1385:
1386: 1387: 1388: 1389: 1390: 1391: 1392: 1393:
1394: public function setDefaultStyle(PHPExcel_Style $pValue)
1395: {
1396: $this->_parent->getDefaultStyle()->applyFromArray(array(
1397: 'font' => array(
1398: 'name' => $pValue->getFont()->getName(),
1399: 'size' => $pValue->getFont()->getSize(),
1400: ),
1401: ));
1402: return $this;
1403: }
1404:
1405: 1406: 1407: 1408: 1409: 1410: 1411:
1412: public function getStyle($pCellCoordinate = 'A1')
1413: {
1414:
1415: $this->_parent->setActiveSheetIndex($this->_parent->getIndex($this));
1416:
1417:
1418: $this->setSelectedCells($pCellCoordinate);
1419:
1420: return $this->_parent->getCellXfSupervisor();
1421: }
1422:
1423: 1424: 1425: 1426: 1427: 1428:
1429: public function getConditionalStyles($pCoordinate = 'A1')
1430: {
1431: if (!isset($this->_conditionalStylesCollection[$pCoordinate])) {
1432: $this->_conditionalStylesCollection[$pCoordinate] = array();
1433: }
1434: return $this->_conditionalStylesCollection[$pCoordinate];
1435: }
1436:
1437: 1438: 1439: 1440: 1441: 1442:
1443: public function conditionalStylesExists($pCoordinate = 'A1')
1444: {
1445: if (isset($this->_conditionalStylesCollection[$pCoordinate])) {
1446: return true;
1447: }
1448: return false;
1449: }
1450:
1451: 1452: 1453: 1454: 1455: 1456:
1457: public function removeConditionalStyles($pCoordinate = 'A1')
1458: {
1459: unset($this->_conditionalStylesCollection[$pCoordinate]);
1460: return $this;
1461: }
1462:
1463: 1464: 1465: 1466: 1467:
1468: public function getConditionalStylesCollection()
1469: {
1470: return $this->_conditionalStylesCollection;
1471: }
1472:
1473: 1474: 1475: 1476: 1477: 1478: 1479:
1480: public function setConditionalStyles($pCoordinate = 'A1', $pValue)
1481: {
1482: $this->_conditionalStylesCollection[$pCoordinate] = $pValue;
1483: return $this;
1484: }
1485:
1486: 1487: 1488: 1489: 1490: 1491: 1492:
1493: public function getStyleByColumnAndRow($pColumn = 0, $pRow = 1)
1494: {
1495: return $this->getStyle(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow);
1496: }
1497:
1498: 1499: 1500: 1501: 1502: 1503: 1504: 1505: 1506: 1507: 1508:
1509: public function setSharedStyle(PHPExcel_Style $pSharedCellStyle = null, $pRange = '')
1510: {
1511: $this->duplicateStyle($pSharedCellStyle, $pRange);
1512: return $this;
1513: }
1514:
1515: 1516: 1517: 1518: 1519: 1520: 1521: 1522: 1523: 1524:
1525: public function duplicateStyle(PHPExcel_Style $pCellStyle = null, $pRange = '')
1526: {
1527:
1528: $style = $pCellStyle->getIsSupervisor() ? $pCellStyle->getSharedComponent() : $pCellStyle;
1529:
1530:
1531: $workbook = $this->_parent;
1532: if ($existingStyle = $this->_parent->getCellXfByHashCode($pCellStyle->getHashCode())) {
1533:
1534: $xfIndex = $existingStyle->getIndex();
1535: } else {
1536:
1537: $workbook->addCellXf($pCellStyle);
1538: $xfIndex = $pCellStyle->getIndex();
1539: }
1540:
1541:
1542: list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($pRange . ':' . $pRange);
1543:
1544:
1545: if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
1546: $tmp = $rangeStart;
1547: $rangeStart = $rangeEnd;
1548: $rangeEnd = $tmp;
1549: }
1550:
1551:
1552: for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
1553: for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
1554: $this->getCell(PHPExcel_Cell::stringFromColumnIndex($col - 1) . $row)->setXfIndex($xfIndex);
1555: }
1556: }
1557:
1558: return $this;
1559: }
1560:
1561: 1562: 1563: 1564: 1565: 1566: 1567: 1568: 1569: 1570:
1571: public function duplicateConditionalStyle(array $pCellStyle = null, $pRange = '')
1572: {
1573: foreach($pCellStyle as $cellStyle) {
1574: if (!($cellStyle instanceof PHPExcel_Style_Conditional)) {
1575: throw new PHPExcel_Exception('Style is not a conditional style');
1576: }
1577: }
1578:
1579:
1580: list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($pRange . ':' . $pRange);
1581:
1582:
1583: if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
1584: $tmp = $rangeStart;
1585: $rangeStart = $rangeEnd;
1586: $rangeEnd = $tmp;
1587: }
1588:
1589:
1590: for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
1591: for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
1592: $this->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($col - 1) . $row, $pCellStyle);
1593: }
1594: }
1595:
1596: return $this;
1597: }
1598:
1599: 1600: 1601: 1602: 1603: 1604: 1605: 1606: 1607: 1608: 1609: 1610: 1611: 1612:
1613: public function duplicateStyleArray($pStyles = null, $pRange = '', $pAdvanced = true)
1614: {
1615: $this->getStyle($pRange)->applyFromArray($pStyles, $pAdvanced);
1616: return $this;
1617: }
1618:
1619: 1620: 1621: 1622: 1623: 1624: 1625: 1626:
1627: public function setBreak($pCell = 'A1', $pBreak = PHPExcel_Worksheet::BREAK_NONE)
1628: {
1629:
1630: $pCell = strtoupper($pCell);
1631:
1632: if ($pCell != '') {
1633: if ($pBreak == PHPExcel_Worksheet::BREAK_NONE) {
1634: if (isset($this->_breaks[$pCell])) {
1635: unset($this->_breaks[$pCell]);
1636: }
1637: } else {
1638: $this->_breaks[$pCell] = $pBreak;
1639: }
1640: } else {
1641: throw new PHPExcel_Exception('No cell coordinate specified.');
1642: }
1643:
1644: return $this;
1645: }
1646:
1647: 1648: 1649: 1650: 1651: 1652: 1653: 1654:
1655: public function setBreakByColumnAndRow($pColumn = 0, $pRow = 1, $pBreak = PHPExcel_Worksheet::BREAK_NONE)
1656: {
1657: return $this->setBreak(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow, $pBreak);
1658: }
1659:
1660: 1661: 1662: 1663: 1664:
1665: public function getBreaks()
1666: {
1667: return $this->_breaks;
1668: }
1669:
1670: 1671: 1672: 1673: 1674: 1675: 1676:
1677: public function mergeCells($pRange = 'A1:A1')
1678: {
1679:
1680: $pRange = strtoupper($pRange);
1681:
1682: if (strpos($pRange,':') !== false) {
1683: $this->_mergeCells[$pRange] = $pRange;
1684:
1685:
1686:
1687:
1688: $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
1689:
1690:
1691: $upperLeft = $aReferences[0];
1692: if (!$this->cellExists($upperLeft)) {
1693: $this->getCell($upperLeft)->setValueExplicit(null, PHPExcel_Cell_DataType::TYPE_NULL);
1694: }
1695:
1696:
1697: $count = count($aReferences);
1698: for ($i = 1; $i < $count; $i++) {
1699: $this->getCell($aReferences[$i])->setValueExplicit(null, PHPExcel_Cell_DataType::TYPE_NULL);
1700: }
1701:
1702: } else {
1703: throw new PHPExcel_Exception('Merge must be set on a range of cells.');
1704: }
1705:
1706: return $this;
1707: }
1708:
1709: 1710: 1711: 1712: 1713: 1714: 1715: 1716: 1717: 1718:
1719: public function mergeCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1)
1720: {
1721: $cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2;
1722: return $this->mergeCells($cellRange);
1723: }
1724:
1725: 1726: 1727: 1728: 1729: 1730: 1731:
1732: public function unmergeCells($pRange = 'A1:A1')
1733: {
1734:
1735: $pRange = strtoupper($pRange);
1736:
1737: if (strpos($pRange,':') !== false) {
1738: if (isset($this->_mergeCells[$pRange])) {
1739: unset($this->_mergeCells[$pRange]);
1740: } else {
1741: throw new PHPExcel_Exception('Cell range ' . $pRange . ' not known as merged.');
1742: }
1743: } else {
1744: throw new PHPExcel_Exception('Merge can only be removed from a range of cells.');
1745: }
1746:
1747: return $this;
1748: }
1749:
1750: 1751: 1752: 1753: 1754: 1755: 1756: 1757: 1758: 1759:
1760: public function unmergeCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1)
1761: {
1762: $cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2;
1763: return $this->unmergeCells($cellRange);
1764: }
1765:
1766: 1767: 1768: 1769: 1770:
1771: public function getMergeCells()
1772: {
1773: return $this->_mergeCells;
1774: }
1775:
1776: 1777: 1778: 1779: 1780: 1781:
1782: public function setMergeCells($pValue = array())
1783: {
1784: $this->_mergeCells = $pValue;
1785:
1786: return $this;
1787: }
1788:
1789: 1790: 1791: 1792: 1793: 1794: 1795: 1796: 1797:
1798: public function protectCells($pRange = 'A1', $pPassword = '', $pAlreadyHashed = false)
1799: {
1800:
1801: $pRange = strtoupper($pRange);
1802:
1803: if (!$pAlreadyHashed) {
1804: $pPassword = PHPExcel_Shared_PasswordHasher::hashPassword($pPassword);
1805: }
1806: $this->_protectedCells[$pRange] = $pPassword;
1807:
1808: return $this;
1809: }
1810:
1811: 1812: 1813: 1814: 1815: 1816: 1817: 1818: 1819: 1820: 1821: 1822:
1823: public function protectCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1, $pPassword = '', $pAlreadyHashed = false)
1824: {
1825: $cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2;
1826: return $this->protectCells($cellRange, $pPassword, $pAlreadyHashed);
1827: }
1828:
1829: 1830: 1831: 1832: 1833: 1834: 1835:
1836: public function unprotectCells($pRange = 'A1')
1837: {
1838:
1839: $pRange = strtoupper($pRange);
1840:
1841: if (isset($this->_protectedCells[$pRange])) {
1842: unset($this->_protectedCells[$pRange]);
1843: } else {
1844: throw new PHPExcel_Exception('Cell range ' . $pRange . ' not known as protected.');
1845: }
1846: return $this;
1847: }
1848:
1849: 1850: 1851: 1852: 1853: 1854: 1855: 1856: 1857: 1858: 1859: 1860:
1861: public function unprotectCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1, $pPassword = '', $pAlreadyHashed = false)
1862: {
1863: $cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2;
1864: return $this->unprotectCells($cellRange, $pPassword, $pAlreadyHashed);
1865: }
1866:
1867: 1868: 1869: 1870: 1871:
1872: public function getProtectedCells()
1873: {
1874: return $this->_protectedCells;
1875: }
1876:
1877: 1878: 1879: 1880: 1881:
1882: public function getAutoFilter()
1883: {
1884: return $this->_autoFilter;
1885: }
1886:
1887: 1888: 1889: 1890: 1891: 1892: 1893: 1894:
1895: public function setAutoFilter($pValue)
1896: {
1897: if (is_string($pValue)) {
1898: $this->_autoFilter->setRange($pValue);
1899: } elseif(is_object($pValue) && ($pValue instanceof PHPExcel_Worksheet_AutoFilter)) {
1900: $this->_autoFilter = $pValue;
1901: }
1902: return $this;
1903: }
1904:
1905: 1906: 1907: 1908: 1909: 1910: 1911: 1912: 1913: 1914:
1915: public function setAutoFilterByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1)
1916: {
1917: return $this->setAutoFilter(
1918: PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1
1919: . ':' .
1920: PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2
1921: );
1922: }
1923:
1924: 1925: 1926: 1927: 1928:
1929: public function removeAutoFilter()
1930: {
1931: $this->_autoFilter->setRange(NULL);
1932: return $this;
1933: }
1934:
1935: 1936: 1937: 1938: 1939:
1940: public function getFreezePane()
1941: {
1942: return $this->_freezePane;
1943: }
1944:
1945: 1946: 1947: 1948: 1949: 1950: 1951: 1952: 1953: 1954: 1955: 1956:
1957: public function freezePane($pCell = '')
1958: {
1959:
1960: $pCell = strtoupper($pCell);
1961:
1962: if (strpos($pCell,':') === false && strpos($pCell,',') === false) {
1963: $this->_freezePane = $pCell;
1964: } else {
1965: throw new PHPExcel_Exception('Freeze pane can not be set on a range of cells.');
1966: }
1967: return $this;
1968: }
1969:
1970: 1971: 1972: 1973: 1974: 1975: 1976: 1977:
1978: public function freezePaneByColumnAndRow($pColumn = 0, $pRow = 1)
1979: {
1980: return $this->freezePane(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow);
1981: }
1982:
1983: 1984: 1985: 1986: 1987:
1988: public function unfreezePane()
1989: {
1990: return $this->freezePane('');
1991: }
1992:
1993: 1994: 1995: 1996: 1997: 1998: 1999: 2000:
2001: public function insertNewRowBefore($pBefore = 1, $pNumRows = 1) {
2002: if ($pBefore >= 1) {
2003: $objReferenceHelper = PHPExcel_ReferenceHelper::getInstance();
2004: $objReferenceHelper->insertNewBefore('A' . $pBefore, 0, $pNumRows, $this);
2005: } else {
2006: throw new PHPExcel_Exception("Rows can only be inserted before at least row 1.");
2007: }
2008: return $this;
2009: }
2010:
2011: 2012: 2013: 2014: 2015: 2016: 2017: 2018:
2019: public function insertNewColumnBefore($pBefore = 'A', $pNumCols = 1) {
2020: if (!is_numeric($pBefore)) {
2021: $objReferenceHelper = PHPExcel_ReferenceHelper::getInstance();
2022: $objReferenceHelper->insertNewBefore($pBefore . '1', $pNumCols, 0, $this);
2023: } else {
2024: throw new PHPExcel_Exception("Column references should not be numeric.");
2025: }
2026: return $this;
2027: }
2028:
2029: 2030: 2031: 2032: 2033: 2034: 2035: 2036:
2037: public function insertNewColumnBeforeByIndex($pBefore = 0, $pNumCols = 1) {
2038: if ($pBefore >= 0) {
2039: return $this->insertNewColumnBefore(PHPExcel_Cell::stringFromColumnIndex($pBefore), $pNumCols);
2040: } else {
2041: throw new PHPExcel_Exception("Columns can only be inserted before at least column A (0).");
2042: }
2043: }
2044:
2045: 2046: 2047: 2048: 2049: 2050: 2051: 2052:
2053: public function removeRow($pRow = 1, $pNumRows = 1) {
2054: if ($pRow >= 1) {
2055: $objReferenceHelper = PHPExcel_ReferenceHelper::getInstance();
2056: $objReferenceHelper->insertNewBefore('A' . ($pRow + $pNumRows), 0, -$pNumRows, $this);
2057: } else {
2058: throw new PHPExcel_Exception("Rows to be deleted should at least start from row 1.");
2059: }
2060: return $this;
2061: }
2062:
2063: 2064: 2065: 2066: 2067: 2068: 2069: 2070:
2071: public function removeColumn($pColumn = 'A', $pNumCols = 1) {
2072: if (!is_numeric($pColumn)) {
2073: $pColumn = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($pColumn) - 1 + $pNumCols);
2074: $objReferenceHelper = PHPExcel_ReferenceHelper::getInstance();
2075: $objReferenceHelper->insertNewBefore($pColumn . '1', -$pNumCols, 0, $this);
2076: } else {
2077: throw new PHPExcel_Exception("Column references should not be numeric.");
2078: }
2079: return $this;
2080: }
2081:
2082: 2083: 2084: 2085: 2086: 2087: 2088: 2089:
2090: public function removeColumnByIndex($pColumn = 0, $pNumCols = 1) {
2091: if ($pColumn >= 0) {
2092: return $this->removeColumn(PHPExcel_Cell::stringFromColumnIndex($pColumn), $pNumCols);
2093: } else {
2094: throw new PHPExcel_Exception("Columns to be deleted should at least start from column 0");
2095: }
2096: }
2097:
2098: 2099: 2100: 2101: 2102:
2103: public function getShowGridlines() {
2104: return $this->_showGridlines;
2105: }
2106:
2107: 2108: 2109: 2110: 2111: 2112:
2113: public function setShowGridlines($pValue = false) {
2114: $this->_showGridlines = $pValue;
2115: return $this;
2116: }
2117:
2118: 2119: 2120: 2121: 2122:
2123: public function getPrintGridlines() {
2124: return $this->_printGridlines;
2125: }
2126:
2127: 2128: 2129: 2130: 2131: 2132:
2133: public function setPrintGridlines($pValue = false) {
2134: $this->_printGridlines = $pValue;
2135: return $this;
2136: }
2137:
2138: 2139: 2140: 2141: 2142:
2143: public function getShowRowColHeaders() {
2144: return $this->_showRowColHeaders;
2145: }
2146:
2147: 2148: 2149: 2150: 2151: 2152:
2153: public function setShowRowColHeaders($pValue = false) {
2154: $this->_showRowColHeaders = $pValue;
2155: return $this;
2156: }
2157:
2158: 2159: 2160: 2161: 2162:
2163: public function getShowSummaryBelow() {
2164: return $this->_showSummaryBelow;
2165: }
2166:
2167: 2168: 2169: 2170: 2171: 2172:
2173: public function setShowSummaryBelow($pValue = true) {
2174: $this->_showSummaryBelow = $pValue;
2175: return $this;
2176: }
2177:
2178: 2179: 2180: 2181: 2182:
2183: public function getShowSummaryRight() {
2184: return $this->_showSummaryRight;
2185: }
2186:
2187: 2188: 2189: 2190: 2191: 2192:
2193: public function setShowSummaryRight($pValue = true) {
2194: $this->_showSummaryRight = $pValue;
2195: return $this;
2196: }
2197:
2198: 2199: 2200: 2201: 2202:
2203: public function getComments()
2204: {
2205: return $this->_comments;
2206: }
2207:
2208: 2209: 2210: 2211: 2212: 2213:
2214: public function setComments($pValue = array())
2215: {
2216: $this->_comments = $pValue;
2217:
2218: return $this;
2219: }
2220:
2221: 2222: 2223: 2224: 2225: 2226: 2227:
2228: public function getComment($pCellCoordinate = 'A1')
2229: {
2230:
2231: $pCellCoordinate = strtoupper($pCellCoordinate);
2232:
2233: if (strpos($pCellCoordinate,':') !== false || strpos($pCellCoordinate,',') !== false) {
2234: throw new PHPExcel_Exception('Cell coordinate string can not be a range of cells.');
2235: } else if (strpos($pCellCoordinate,'$') !== false) {
2236: throw new PHPExcel_Exception('Cell coordinate string must not be absolute.');
2237: } else if ($pCellCoordinate == '') {
2238: throw new PHPExcel_Exception('Cell coordinate can not be zero-length string.');
2239: } else {
2240:
2241:
2242: if (isset($this->_comments[$pCellCoordinate])) {
2243: return $this->_comments[$pCellCoordinate];
2244: } else {
2245: $newComment = new PHPExcel_Comment();
2246: $this->_comments[$pCellCoordinate] = $newComment;
2247: return $newComment;
2248: }
2249: }
2250: }
2251:
2252: 2253: 2254: 2255: 2256: 2257: 2258:
2259: public function getCommentByColumnAndRow($pColumn = 0, $pRow = 1)
2260: {
2261: return $this->getComment(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow);
2262: }
2263:
2264: 2265: 2266: 2267: 2268: 2269:
2270: public function getSelectedCell()
2271: {
2272: return $this->getSelectedCells();
2273: }
2274:
2275: 2276: 2277: 2278: 2279:
2280: public function getActiveCell()
2281: {
2282: return $this->_activeCell;
2283: }
2284:
2285: 2286: 2287: 2288: 2289:
2290: public function getSelectedCells()
2291: {
2292: return $this->_selectedCells;
2293: }
2294:
2295: 2296: 2297: 2298: 2299: 2300:
2301: public function setSelectedCell($pCoordinate = 'A1')
2302: {
2303: return $this->setSelectedCells($pCoordinate);
2304: }
2305:
2306: 2307: 2308: 2309: 2310: 2311: 2312:
2313: public function setSelectedCells($pCoordinate = 'A1')
2314: {
2315:
2316: $pCoordinate = strtoupper($pCoordinate);
2317:
2318:
2319: $pCoordinate = preg_replace('/^([A-Z]+)$/', '${1}:${1}', $pCoordinate);
2320:
2321:
2322: $pCoordinate = preg_replace('/^([0-9]+)$/', '${1}:${1}', $pCoordinate);
2323:
2324:
2325: $pCoordinate = preg_replace('/^([A-Z]+):([A-Z]+)$/', '${1}1:${2}1048576', $pCoordinate);
2326:
2327:
2328: $pCoordinate = preg_replace('/^([0-9]+):([0-9]+)$/', 'A${1}:XFD${2}', $pCoordinate);
2329:
2330: if (strpos($pCoordinate,':') !== false || strpos($pCoordinate,',') !== false) {
2331: list($first, ) = PHPExcel_Cell::splitRange($pCoordinate);
2332: $this->_activeCell = $first[0];
2333: } else {
2334: $this->_activeCell = $pCoordinate;
2335: }
2336: $this->_selectedCells = $pCoordinate;
2337: return $this;
2338: }
2339:
2340: 2341: 2342: 2343: 2344: 2345: 2346: 2347:
2348: public function setSelectedCellByColumnAndRow($pColumn = 0, $pRow = 1)
2349: {
2350: return $this->setSelectedCells(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow);
2351: }
2352:
2353: 2354: 2355: 2356: 2357:
2358: public function getRightToLeft() {
2359: return $this->_rightToLeft;
2360: }
2361:
2362: 2363: 2364: 2365: 2366: 2367:
2368: public function setRightToLeft($value = false) {
2369: $this->_rightToLeft = $value;
2370: return $this;
2371: }
2372:
2373: 2374: 2375: 2376: 2377: 2378: 2379: 2380: 2381: 2382:
2383: public function fromArray($source = null, $nullValue = null, $startCell = 'A1', $strictNullComparison = false) {
2384: if (is_array($source)) {
2385:
2386: if (!is_array(end($source))) {
2387: $source = array($source);
2388: }
2389:
2390:
2391: list ($startColumn, $startRow) = PHPExcel_Cell::coordinateFromString($startCell);
2392:
2393:
2394: foreach ($source as $rowData) {
2395: $currentColumn = $startColumn;
2396: foreach($rowData as $cellValue) {
2397: if ($strictNullComparison) {
2398: if ($cellValue !== $nullValue) {
2399:
2400: $this->getCell($currentColumn . $startRow)->setValue($cellValue);
2401: }
2402: } else {
2403: if ($cellValue != $nullValue) {
2404:
2405: $this->getCell($currentColumn . $startRow)->setValue($cellValue);
2406: }
2407: }
2408: ++$currentColumn;
2409: }
2410: ++$startRow;
2411: }
2412: } else {
2413: throw new PHPExcel_Exception("Parameter \$source should be an array.");
2414: }
2415: return $this;
2416: }
2417:
2418: 2419: 2420: 2421: 2422: 2423: 2424: 2425: 2426: 2427: 2428:
2429: public function rangeToArray($pRange = 'A1', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) {
2430:
2431: $returnValue = array();
2432:
2433: list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($pRange);
2434: $minCol = PHPExcel_Cell::stringFromColumnIndex($rangeStart[0] -1);
2435: $minRow = $rangeStart[1];
2436: $maxCol = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0] -1);
2437: $maxRow = $rangeEnd[1];
2438:
2439: $maxCol++;
2440:
2441: $r = -1;
2442: for ($row = $minRow; $row <= $maxRow; ++$row) {
2443: $rRef = ($returnCellRef) ? $row : ++$r;
2444: $c = -1;
2445:
2446: for ($col = $minCol; $col != $maxCol; ++$col) {
2447: $cRef = ($returnCellRef) ? $col : ++$c;
2448:
2449:
2450: if ($this->_cellCollection->isDataSet($col.$row)) {
2451:
2452: $cell = $this->_cellCollection->getCacheData($col.$row);
2453: if ($cell->getValue() !== null) {
2454: if ($cell->getValue() instanceof PHPExcel_RichText) {
2455: $returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText();
2456: } else {
2457: if ($calculateFormulas) {
2458: $returnValue[$rRef][$cRef] = $cell->getCalculatedValue();
2459: } else {
2460: $returnValue[$rRef][$cRef] = $cell->getValue();
2461: }
2462: }
2463:
2464: if ($formatData) {
2465: $style = $this->_parent->getCellXfByIndex($cell->getXfIndex());
2466: $returnValue[$rRef][$cRef] = PHPExcel_Style_NumberFormat::toFormattedString(
2467: $returnValue[$rRef][$cRef],
2468: ($style && $style->getNumberFormat()) ?
2469: $style->getNumberFormat()->getFormatCode() :
2470: PHPExcel_Style_NumberFormat::FORMAT_GENERAL
2471: );
2472: }
2473: } else {
2474:
2475: $returnValue[$rRef][$cRef] = $nullValue;
2476: }
2477: } else {
2478:
2479: $returnValue[$rRef][$cRef] = $nullValue;
2480: }
2481: }
2482: }
2483:
2484:
2485: return $returnValue;
2486: }
2487:
2488:
2489: 2490: 2491: 2492: 2493: 2494: 2495: 2496: 2497: 2498: 2499: 2500:
2501: public function namedRangeToArray($pNamedRange = '', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) {
2502: $namedRange = PHPExcel_NamedRange::resolveRange($pNamedRange, $this);
2503: if ($namedRange !== NULL) {
2504: $pWorkSheet = $namedRange->getWorksheet();
2505: $pCellRange = $namedRange->getRange();
2506:
2507: return $pWorkSheet->rangeToArray( $pCellRange,
2508: $nullValue, $calculateFormulas, $formatData, $returnCellRef);
2509: }
2510:
2511: throw new PHPExcel_Exception('Named Range '.$pNamedRange.' does not exist.');
2512: }
2513:
2514:
2515: 2516: 2517: 2518: 2519: 2520: 2521: 2522: 2523: 2524:
2525: public function toArray($nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) {
2526:
2527: $this->garbageCollect();
2528:
2529:
2530: $maxCol = $this->getHighestColumn();
2531: $maxRow = $this->getHighestRow();
2532:
2533: return $this->rangeToArray( 'A1:'.$maxCol.$maxRow,
2534: $nullValue, $calculateFormulas, $formatData, $returnCellRef);
2535: }
2536:
2537: 2538: 2539: 2540: 2541: 2542:
2543: public function getRowIterator($startRow = 1) {
2544: return new PHPExcel_Worksheet_RowIterator($this,$startRow);
2545: }
2546:
2547: 2548: 2549: 2550: 2551:
2552: public function garbageCollect() {
2553:
2554: $this->_cellCollection->getCacheData('A1');
2555:
2556:
2557:
2558:
2559:
2560:
2561:
2562:
2563:
2564:
2565: $colRow = $this->_cellCollection->getHighestRowAndColumn();
2566: $highestRow = $colRow['row'];
2567: $highestColumn = PHPExcel_Cell::columnIndexFromString($colRow['column']);
2568:
2569:
2570: foreach ($this->_columnDimensions as $dimension) {
2571: $highestColumn = max($highestColumn,PHPExcel_Cell::columnIndexFromString($dimension->getColumnIndex()));
2572: }
2573:
2574:
2575: foreach ($this->_rowDimensions as $dimension) {
2576: $highestRow = max($highestRow,$dimension->getRowIndex());
2577: }
2578:
2579:
2580: if ($highestColumn < 0) {
2581: $this->_cachedHighestColumn = 'A';
2582: } else {
2583: $this->_cachedHighestColumn = PHPExcel_Cell::stringFromColumnIndex(--$highestColumn);
2584: }
2585: $this->_cachedHighestRow = $highestRow;
2586:
2587:
2588: return $this;
2589: }
2590:
2591: 2592: 2593: 2594: 2595:
2596: public function getHashCode() {
2597: if ($this->_dirty) {
2598: $this->_hash = md5( $this->_title .
2599: $this->_autoFilter .
2600: ($this->_protection->isProtectionEnabled() ? 't' : 'f') .
2601: __CLASS__
2602: );
2603: $this->_dirty = false;
2604: }
2605: return $this->_hash;
2606: }
2607:
2608: 2609: 2610: 2611: 2612: 2613: 2614: 2615: 2616: 2617:
2618: public static function extractSheetTitle($pRange, $returnRange = false) {
2619:
2620: if (($sep = strpos($pRange, '!')) === false) {
2621: return '';
2622: }
2623:
2624: if ($returnRange) {
2625: return array( trim(substr($pRange, 0, $sep),"'"),
2626: substr($pRange, $sep + 1)
2627: );
2628: }
2629:
2630: return substr($pRange, $sep + 1);
2631: }
2632:
2633: 2634: 2635: 2636: 2637:
2638: public function getHyperlink($pCellCoordinate = 'A1')
2639: {
2640:
2641: if (isset($this->_hyperlinkCollection[$pCellCoordinate])) {
2642: return $this->_hyperlinkCollection[$pCellCoordinate];
2643: }
2644:
2645:
2646: $this->_hyperlinkCollection[$pCellCoordinate] = new PHPExcel_Cell_Hyperlink();
2647: return $this->_hyperlinkCollection[$pCellCoordinate];
2648: }
2649:
2650: 2651: 2652: 2653: 2654: 2655: 2656:
2657: public function setHyperlink($pCellCoordinate = 'A1', PHPExcel_Cell_Hyperlink $pHyperlink = null)
2658: {
2659: if ($pHyperlink === null) {
2660: unset($this->_hyperlinkCollection[$pCellCoordinate]);
2661: } else {
2662: $this->_hyperlinkCollection[$pCellCoordinate] = $pHyperlink;
2663: }
2664: return $this;
2665: }
2666:
2667: 2668: 2669: 2670: 2671: 2672:
2673: public function hyperlinkExists($pCoordinate = 'A1')
2674: {
2675: return isset($this->_hyperlinkCollection[$pCoordinate]);
2676: }
2677:
2678: 2679: 2680: 2681: 2682:
2683: public function getHyperlinkCollection()
2684: {
2685: return $this->_hyperlinkCollection;
2686: }
2687:
2688: 2689: 2690: 2691: 2692:
2693: public function getDataValidation($pCellCoordinate = 'A1')
2694: {
2695:
2696: if (isset($this->_dataValidationCollection[$pCellCoordinate])) {
2697: return $this->_dataValidationCollection[$pCellCoordinate];
2698: }
2699:
2700:
2701: $this->_dataValidationCollection[$pCellCoordinate] = new PHPExcel_Cell_DataValidation();
2702: return $this->_dataValidationCollection[$pCellCoordinate];
2703: }
2704:
2705: 2706: 2707: 2708: 2709: 2710: 2711:
2712: public function setDataValidation($pCellCoordinate = 'A1', PHPExcel_Cell_DataValidation $pDataValidation = null)
2713: {
2714: if ($pDataValidation === null) {
2715: unset($this->_dataValidationCollection[$pCellCoordinate]);
2716: } else {
2717: $this->_dataValidationCollection[$pCellCoordinate] = $pDataValidation;
2718: }
2719: return $this;
2720: }
2721:
2722: 2723: 2724: 2725: 2726: 2727:
2728: public function dataValidationExists($pCoordinate = 'A1')
2729: {
2730: return isset($this->_dataValidationCollection[$pCoordinate]);
2731: }
2732:
2733: 2734: 2735: 2736: 2737:
2738: public function getDataValidationCollection()
2739: {
2740: return $this->_dataValidationCollection;
2741: }
2742:
2743: 2744: 2745: 2746: 2747: 2748:
2749: public function shrinkRangeToFit($range) {
2750: $maxCol = $this->getHighestColumn();
2751: $maxRow = $this->getHighestRow();
2752: $maxCol = PHPExcel_Cell::columnIndexFromString($maxCol);
2753:
2754: $rangeBlocks = explode(' ',$range);
2755: foreach ($rangeBlocks as &$rangeSet) {
2756: $rangeBoundaries = PHPExcel_Cell::getRangeBoundaries($rangeSet);
2757:
2758: if (PHPExcel_Cell::columnIndexFromString($rangeBoundaries[0][0]) > $maxCol) { $rangeBoundaries[0][0] = PHPExcel_Cell::stringFromColumnIndex($maxCol); }
2759: if ($rangeBoundaries[0][1] > $maxRow) { $rangeBoundaries[0][1] = $maxRow; }
2760: if (PHPExcel_Cell::columnIndexFromString($rangeBoundaries[1][0]) > $maxCol) { $rangeBoundaries[1][0] = PHPExcel_Cell::stringFromColumnIndex($maxCol); }
2761: if ($rangeBoundaries[1][1] > $maxRow) { $rangeBoundaries[1][1] = $maxRow; }
2762: $rangeSet = $rangeBoundaries[0][0].$rangeBoundaries[0][1].':'.$rangeBoundaries[1][0].$rangeBoundaries[1][1];
2763: }
2764: unset($rangeSet);
2765: $stRange = implode(' ',$rangeBlocks);
2766:
2767: return $stRange;
2768: }
2769:
2770: 2771: 2772: 2773: 2774:
2775: public function getTabColor()
2776: {
2777: if ($this->_tabColor === NULL)
2778: $this->_tabColor = new PHPExcel_Style_Color();
2779:
2780: return $this->_tabColor;
2781: }
2782:
2783: 2784: 2785: 2786: 2787:
2788: public function resetTabColor()
2789: {
2790: $this->_tabColor = null;
2791: unset($this->_tabColor);
2792:
2793: return $this;
2794: }
2795:
2796: 2797: 2798: 2799: 2800:
2801: public function isTabColorSet()
2802: {
2803: return ($this->_tabColor !== NULL);
2804: }
2805:
2806: 2807: 2808: 2809: 2810:
2811: public function copy() {
2812: $copied = clone $this;
2813:
2814: return $copied;
2815: }
2816:
2817: 2818: 2819:
2820: public function __clone() {
2821: foreach ($this as $key => $val) {
2822: if ($key == '_parent') {
2823: continue;
2824: }
2825:
2826: if (is_object($val) || (is_array($val))) {
2827: if ($key == '_cellCollection') {
2828: $newCollection = clone $this->_cellCollection;
2829: $newCollection->copyCellCollection($this);
2830: $this->_cellCollection = $newCollection;
2831: } elseif ($key == '_drawingCollection') {
2832: $newCollection = clone $this->_drawingCollection;
2833: $this->_drawingCollection = $newCollection;
2834: } elseif (($key == '_autoFilter') && ($this->_autoFilter instanceof PHPExcel_Worksheet_AutoFilter)) {
2835: $newAutoFilter = clone $this->_autoFilter;
2836: $this->_autoFilter = $newAutoFilter;
2837: $this->_autoFilter->setParent($this);
2838: } else {
2839: $this->{$key} = unserialize(serialize($val));
2840: }
2841: }
2842: }
2843: }
2844: 2845: 2846: 2847: 2848: 2849: 2850:
2851: public function setCodeName($pValue=null){
2852:
2853: if ($this->getCodeName() == $pValue) {
2854: return $this;
2855: }
2856: $pValue = str_replace(' ', '_', $pValue);
2857:
2858:
2859: self::_checkSheetCodeName($pValue);
2860:
2861:
2862:
2863: if ($this->getParent()) {
2864:
2865: if ($this->getParent()->sheetCodeNameExists($pValue)) {
2866:
2867:
2868: if (PHPExcel_Shared_String::CountCharacters($pValue) > 29) {
2869: $pValue = PHPExcel_Shared_String::Substring($pValue,0,29);
2870: }
2871: $i = 1;
2872: while ($this->getParent()->sheetCodeNameExists($pValue . '_' . $i)) {
2873: ++$i;
2874: if ($i == 10) {
2875: if (PHPExcel_Shared_String::CountCharacters($pValue) > 28) {
2876: $pValue = PHPExcel_Shared_String::Substring($pValue,0,28);
2877: }
2878: } elseif ($i == 100) {
2879: if (PHPExcel_Shared_String::CountCharacters($pValue) > 27) {
2880: $pValue = PHPExcel_Shared_String::Substring($pValue,0,27);
2881: }
2882: }
2883: }
2884:
2885: $pValue = $pValue . '_' . $i;
2886:
2887:
2888: }
2889: }
2890:
2891: $this->_codeName=$pValue;
2892: return $this;
2893: }
2894: 2895: 2896: 2897: 2898:
2899: public function getCodeName(){
2900: return $this->_codeName;
2901: }
2902: 2903: 2904: 2905:
2906: public function hasCodeName(){
2907: return !(is_null($this->_codeName));
2908: }
2909: }
2910: