ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
Spreadsheet.php
Go to the documentation of this file.
1<?php
2
4
10
12{
13 // Allowable values for workbook window visilbity
14 const VISIBILITY_VISIBLE = 'visible';
15 const VISIBILITY_HIDDEN = 'hidden';
16 const VISIBILITY_VERY_HIDDEN = 'veryHidden';
17
18 private const DEFINED_NAME_IS_RANGE = false;
19 private const DEFINED_NAME_IS_FORMULA = true;
20
25 ];
26
32 private $uniqueID;
33
39 private $properties;
40
46 private $security;
47
54
61
67 private $activeSheetIndex = 0;
68
74 private $definedNames = [];
75
82
88 private $cellXfCollection = [];
89
96
102 private $hasMacros = false;
103
109 private $macrosCode;
110
117
124
132
140
146 private $showHorizontalScroll = true;
147
153 private $showVerticalScroll = true;
154
160 private $showSheetTabs = true;
161
168 private $minimized = false;
169
178
184 private $firstSheetIndex = 0;
185
192
200 private $tabRatio = 600;
201
207 public function hasMacros()
208 {
209 return $this->hasMacros;
210 }
211
217 public function setHasMacros($hasMacros): void
218 {
219 $this->hasMacros = (bool) $hasMacros;
220 }
221
227 public function setMacrosCode($macroCode): void
228 {
229 $this->macrosCode = $macroCode;
230 $this->setHasMacros($macroCode !== null);
231 }
232
238 public function getMacrosCode()
239 {
240 return $this->macrosCode;
241 }
242
248 public function setMacrosCertificate($certificate): void
249 {
250 $this->macrosCertificate = $certificate;
251 }
252
258 public function hasMacrosCertificate()
259 {
260 return $this->macrosCertificate !== null;
261 }
262
268 public function getMacrosCertificate()
269 {
271 }
272
276 public function discardMacros(): void
277 {
278 $this->hasMacros = false;
279 $this->macrosCode = null;
280 $this->macrosCertificate = null;
281 }
282
289 public function setRibbonXMLData($target, $xmlData): void
290 {
291 if ($target !== null && $xmlData !== null) {
292 $this->ribbonXMLData = ['target' => $target, 'data' => $xmlData];
293 } else {
294 $this->ribbonXMLData = null;
295 }
296 }
297
305 public function getRibbonXMLData($what = 'all') //we need some constants here...
306 {
307 $returnData = null;
308 $what = strtolower($what);
309 switch ($what) {
310 case 'all':
311 $returnData = $this->ribbonXMLData;
312
313 break;
314 case 'target':
315 case 'data':
316 if (is_array($this->ribbonXMLData) && isset($this->ribbonXMLData[$what])) {
317 $returnData = $this->ribbonXMLData[$what];
318 }
319
320 break;
321 }
322
323 return $returnData;
324 }
325
332 public function setRibbonBinObjects($BinObjectsNames, $BinObjectsData): void
333 {
334 if ($BinObjectsNames !== null && $BinObjectsData !== null) {
335 $this->ribbonBinObjects = ['names' => $BinObjectsNames, 'data' => $BinObjectsData];
336 } else {
337 $this->ribbonBinObjects = null;
338 }
339 }
340
349 public function getUnparsedLoadedData()
350 {
352 }
353
360 public function setUnparsedLoadedData(array $unparsedLoadedData): void
361 {
362 $this->unparsedLoadedData = $unparsedLoadedData;
363 }
364
372 private function getExtensionOnly($path)
373 {
374 $extension = pathinfo($path, PATHINFO_EXTENSION);
375
376 return is_array($extension) ? '' : $extension;
377 }
378
386 public function getRibbonBinObjects($what = 'all')
387 {
388 $ReturnData = null;
389 $what = strtolower($what);
390 switch ($what) {
391 case 'all':
393
394 break;
395 case 'names':
396 case 'data':
397 if (is_array($this->ribbonBinObjects) && isset($this->ribbonBinObjects[$what])) {
398 $ReturnData = $this->ribbonBinObjects[$what];
399 }
400
401 break;
402 case 'types':
403 if (
404 is_array($this->ribbonBinObjects) &&
405 isset($this->ribbonBinObjects['data']) && is_array($this->ribbonBinObjects['data'])
406 ) {
407 $tmpTypes = array_keys($this->ribbonBinObjects['data']);
408 $ReturnData = array_unique(array_map([$this, 'getExtensionOnly'], $tmpTypes));
409 } else {
410 $ReturnData = []; // the caller want an array... not null if empty
411 }
412
413 break;
414 }
415
416 return $ReturnData;
417 }
418
424 public function hasRibbon()
425 {
426 return $this->ribbonXMLData !== null;
427 }
428
434 public function hasRibbonBinObjects()
435 {
436 return $this->ribbonBinObjects !== null;
437 }
438
446 public function sheetCodeNameExists($pSheetCodeName)
447 {
448 return $this->getSheetByCodeName($pSheetCodeName) !== null;
449 }
450
458 public function getSheetByCodeName($pName)
459 {
460 $worksheetCount = count($this->workSheetCollection);
461 for ($i = 0; $i < $worksheetCount; ++$i) {
462 if ($this->workSheetCollection[$i]->getCodeName() == $pName) {
463 return $this->workSheetCollection[$i];
464 }
465 }
466
467 return null;
468 }
469
473 public function __construct()
474 {
475 $this->uniqueID = uniqid('', true);
476 $this->calculationEngine = new Calculation($this);
477
478 // Initialise worksheet collection and add one worksheet
479 $this->workSheetCollection = [];
480 $this->workSheetCollection[] = new Worksheet($this);
481 $this->activeSheetIndex = 0;
482
483 // Create document properties
484 $this->properties = new Document\Properties();
485
486 // Create document security
487 $this->security = new Document\Security();
488
489 // Set defined names
490 $this->definedNames = [];
491
492 // Create the cellXf supervisor
493 $this->cellXfSupervisor = new Style(true);
494 $this->cellXfSupervisor->bindParent($this);
495
496 // Create the default style
497 $this->addCellXf(new Style());
498 $this->addCellStyleXf(new Style());
499 }
500
504 public function __destruct()
505 {
506 $this->disconnectWorksheets();
507 $this->calculationEngine = null;
508 $this->cellXfCollection = [];
509 $this->cellStyleXfCollection = [];
510 }
511
516 public function disconnectWorksheets(): void
517 {
518 foreach ($this->workSheetCollection as $worksheet) {
519 $worksheet->disconnectCells();
520 unset($worksheet);
521 }
522 $this->workSheetCollection = [];
523 }
524
530 public function getCalculationEngine()
531 {
533 }
534
540 public function getProperties()
541 {
542 return $this->properties;
543 }
544
548 public function setProperties(Document\Properties $pValue): void
549 {
550 $this->properties = $pValue;
551 }
552
558 public function getSecurity()
559 {
560 return $this->security;
561 }
562
566 public function setSecurity(Document\Security $pValue): void
567 {
568 $this->security = $pValue;
569 }
570
576 public function getActiveSheet()
577 {
578 return $this->getSheet($this->activeSheetIndex);
579 }
580
588 public function createSheet($sheetIndex = null)
589 {
590 $newSheet = new Worksheet($this);
591 $this->addSheet($newSheet, $sheetIndex);
592
593 return $newSheet;
594 }
595
603 public function sheetNameExists($pSheetName)
604 {
605 return $this->getSheetByName($pSheetName) !== null;
606 }
607
615 public function addSheet(Worksheet $pSheet, $iSheetIndex = null)
616 {
617 if ($this->sheetNameExists($pSheet->getTitle())) {
618 throw new Exception(
619 "Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename this worksheet first."
620 );
621 }
622
623 if ($iSheetIndex === null) {
624 if ($this->activeSheetIndex < 0) {
625 $this->activeSheetIndex = 0;
626 }
627 $this->workSheetCollection[] = $pSheet;
628 } else {
629 // Insert the sheet at the requested index
630 array_splice(
631 $this->workSheetCollection,
632 $iSheetIndex,
633 0,
634 [$pSheet]
635 );
636
637 // Adjust active sheet index if necessary
638 if ($this->activeSheetIndex >= $iSheetIndex) {
640 }
641 }
642
643 if ($pSheet->getParent() === null) {
644 $pSheet->rebindParent($this);
645 }
646
647 return $pSheet;
648 }
649
655 public function removeSheetByIndex($pIndex): void
656 {
657 $numSheets = count($this->workSheetCollection);
658 if ($pIndex > $numSheets - 1) {
659 throw new Exception(
660 "You tried to remove a sheet by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
661 );
662 }
663 array_splice($this->workSheetCollection, $pIndex, 1);
664
665 // Adjust active sheet index if necessary
666 if (
667 ($this->activeSheetIndex >= $pIndex) &&
668 ($this->activeSheetIndex > 0 || $numSheets <= 1)
669 ) {
671 }
672 }
673
681 public function getSheet($pIndex)
682 {
683 if (!isset($this->workSheetCollection[$pIndex])) {
684 $numSheets = $this->getSheetCount();
685
686 throw new Exception(
687 "Your requested sheet index: {$pIndex} is out of bounds. The actual number of sheets is {$numSheets}."
688 );
689 }
690
691 return $this->workSheetCollection[$pIndex];
692 }
693
699 public function getAllSheets()
700 {
702 }
703
711 public function getSheetByName($pName)
712 {
713 $worksheetCount = count($this->workSheetCollection);
714 for ($i = 0; $i < $worksheetCount; ++$i) {
715 if ($this->workSheetCollection[$i]->getTitle() === trim($pName, "'")) {
716 return $this->workSheetCollection[$i];
717 }
718 }
719
720 return null;
721 }
722
728 public function getIndex(Worksheet $pSheet)
729 {
730 foreach ($this->workSheetCollection as $key => $value) {
731 if ($value->getHashCode() === $pSheet->getHashCode()) {
732 return $key;
733 }
734 }
735
736 throw new Exception('Sheet does not exist.');
737 }
738
747 public function setIndexByName($sheetName, $newIndex)
748 {
749 $oldIndex = $this->getIndex($this->getSheetByName($sheetName));
750 $pSheet = array_splice(
751 $this->workSheetCollection,
752 $oldIndex,
753 1
754 );
755 array_splice(
756 $this->workSheetCollection,
757 $newIndex,
758 0,
759 $pSheet
760 );
761
762 return $newIndex;
763 }
764
770 public function getSheetCount()
771 {
772 return count($this->workSheetCollection);
773 }
774
780 public function getActiveSheetIndex()
781 {
783 }
784
792 public function setActiveSheetIndex($pIndex)
793 {
794 $numSheets = count($this->workSheetCollection);
795
796 if ($pIndex > $numSheets - 1) {
797 throw new Exception(
798 "You tried to set a sheet active by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
799 );
800 }
801 $this->activeSheetIndex = $pIndex;
802
803 return $this->getActiveSheet();
804 }
805
813 public function setActiveSheetIndexByName($pValue)
814 {
815 if (($worksheet = $this->getSheetByName($pValue)) instanceof Worksheet) {
816 $this->setActiveSheetIndex($this->getIndex($worksheet));
817
818 return $worksheet;
819 }
820
821 throw new Exception('Workbook does not contain sheet:' . $pValue);
822 }
823
829 public function getSheetNames()
830 {
831 $returnValue = [];
832 $worksheetCount = $this->getSheetCount();
833 for ($i = 0; $i < $worksheetCount; ++$i) {
834 $returnValue[] = $this->getSheet($i)->getTitle();
835 }
836
837 return $returnValue;
838 }
839
848 public function addExternalSheet(Worksheet $pSheet, $iSheetIndex = null)
849 {
850 if ($this->sheetNameExists($pSheet->getTitle())) {
851 throw new Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
852 }
853
854 // count how many cellXfs there are in this workbook currently, we will need this below
855 $countCellXfs = count($this->cellXfCollection);
856
857 // copy all the shared cellXfs from the external workbook and append them to the current
858 foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) {
859 $this->addCellXf(clone $cellXf);
860 }
861
862 // move sheet to this workbook
863 $pSheet->rebindParent($this);
864
865 // update the cellXfs
866 foreach ($pSheet->getCoordinates(false) as $coordinate) {
867 $cell = $pSheet->getCell($coordinate);
868 $cell->setXfIndex($cell->getXfIndex() + $countCellXfs);
869 }
870
871 return $this->addSheet($pSheet, $iSheetIndex);
872 }
873
879 public function getNamedRanges(): array
880 {
881 return array_filter(
882 $this->definedNames,
883 function (DefinedName $definedName) {
884 return $definedName->isFormula() === self::DEFINED_NAME_IS_RANGE;
885 }
886 );
887 }
888
894 public function getNamedFormulae(): array
895 {
896 return array_filter(
897 $this->definedNames,
898 function (DefinedName $definedName) {
899 return $definedName->isFormula() === self::DEFINED_NAME_IS_FORMULA;
900 }
901 );
902 }
903
909 public function getDefinedNames(): array
910 {
911 return $this->definedNames;
912 }
913
918 public function addNamedRange(NamedRange $namedRange): void
919 {
920 $this->addDefinedName($namedRange);
921 }
922
927 public function addNamedFormula(NamedFormula $namedFormula): void
928 {
929 $this->addDefinedName($namedFormula);
930 }
931
936 public function addDefinedName(DefinedName $definedName): void
937 {
938 $upperCaseName = StringHelper::strToUpper($definedName->getName());
939 if ($definedName->getScope() == null) {
940 // global scope
941 $this->definedNames[$upperCaseName] = $definedName;
942 } else {
943 // local scope
944 $this->definedNames[$definedName->getScope()->getTitle() . '!' . $upperCaseName] = $definedName;
945 }
946 }
947
953 public function getNamedRange(string $namedRange, ?Worksheet $pSheet = null): ?NamedRange
954 {
955 $returnValue = null;
956
957 if ($namedRange !== '') {
958 $namedRange = StringHelper::strToUpper($namedRange);
959 // first look for global named range
960 $returnValue = $this->getGlobalDefinedNameByType($namedRange, self::DEFINED_NAME_IS_RANGE);
961 // then look for local named range (has priority over global named range if both names exist)
962 $returnValue = $this->getLocalDefinedNameByType($namedRange, self::DEFINED_NAME_IS_RANGE, $pSheet) ?: $returnValue;
963 }
964
965 return $returnValue instanceof NamedRange ? $returnValue : null;
966 }
967
973 public function getNamedFormula(string $namedFormula, ?Worksheet $pSheet = null): ?NamedFormula
974 {
975 $returnValue = null;
976
977 if ($namedFormula !== '') {
978 $namedFormula = StringHelper::strToUpper($namedFormula);
979 // first look for global named formula
980 $returnValue = $this->getGlobalDefinedNameByType($namedFormula, self::DEFINED_NAME_IS_FORMULA);
981 // then look for local named formula (has priority over global named formula if both names exist)
982 $returnValue = $this->getLocalDefinedNameByType($namedFormula, self::DEFINED_NAME_IS_FORMULA, $pSheet) ?: $returnValue;
983 }
984
985 return $returnValue instanceof NamedFormula ? $returnValue : null;
986 }
987
988 private function getGlobalDefinedNameByType(string $name, bool $type): ?DefinedName
989 {
990 if (isset($this->definedNames[$name]) && $this->definedNames[$name]->isFormula() === $type) {
991 return $this->definedNames[$name];
992 }
993
994 return null;
995 }
996
997 private function getLocalDefinedNameByType(string $name, bool $type, ?Worksheet $pSheet = null): ?DefinedName
998 {
999 if (
1000 ($pSheet !== null) && isset($this->definedNames[$pSheet->getTitle() . '!' . $name])
1001 && $this->definedNames[$pSheet->getTitle() . '!' . $name]->isFormula() === $type
1002 ) {
1003 return $this->definedNames[$pSheet->getTitle() . '!' . $name];
1004 }
1005
1006 return null;
1007 }
1008
1014 public function getDefinedName(string $definedName, ?Worksheet $pSheet = null): ?DefinedName
1015 {
1016 $returnValue = null;
1017
1018 if ($definedName !== '') {
1019 $definedName = StringHelper::strToUpper($definedName);
1020 // first look for global defined name
1021 if (isset($this->definedNames[$definedName])) {
1022 $returnValue = $this->definedNames[$definedName];
1023 }
1024
1025 // then look for local defined name (has priority over global defined name if both names exist)
1026 if (($pSheet !== null) && isset($this->definedNames[$pSheet->getTitle() . '!' . $definedName])) {
1027 $returnValue = $this->definedNames[$pSheet->getTitle() . '!' . $definedName];
1028 }
1029 }
1030
1031 return $returnValue;
1032 }
1033
1041 public function removeNamedRange(string $namedRange, ?Worksheet $pSheet = null): self
1042 {
1043 if ($this->getNamedRange($namedRange, $pSheet) === null) {
1044 return $this;
1045 }
1046
1047 return $this->removeDefinedName($namedRange, $pSheet);
1048 }
1049
1057 public function removeNamedFormula(string $namedFormula, ?Worksheet $pSheet = null): self
1058 {
1059 if ($this->getNamedFormula($namedFormula, $pSheet) === null) {
1060 return $this;
1061 }
1062
1063 return $this->removeDefinedName($namedFormula, $pSheet);
1064 }
1065
1073 public function removeDefinedName(string $definedName, ?Worksheet $pSheet = null): self
1074 {
1075 $definedName = StringHelper::strToUpper($definedName);
1076
1077 if ($pSheet === null) {
1078 if (isset($this->definedNames[$definedName])) {
1079 unset($this->definedNames[$definedName]);
1080 }
1081 } else {
1082 if (isset($this->definedNames[$pSheet->getTitle() . '!' . $definedName])) {
1083 unset($this->definedNames[$pSheet->getTitle() . '!' . $definedName]);
1084 } elseif (isset($this->definedNames[$definedName])) {
1085 unset($this->definedNames[$definedName]);
1086 }
1087 }
1088
1089 return $this;
1090 }
1091
1097 public function getWorksheetIterator()
1098 {
1099 return new Iterator($this);
1100 }
1101
1107 public function copy()
1108 {
1109 $copied = clone $this;
1110
1111 $worksheetCount = count($this->workSheetCollection);
1112 for ($i = 0; $i < $worksheetCount; ++$i) {
1113 $this->workSheetCollection[$i] = $this->workSheetCollection[$i]->copy();
1114 $this->workSheetCollection[$i]->rebindParent($this);
1115 }
1116
1117 return $copied;
1118 }
1119
1123 public function __clone()
1124 {
1125 // @phpstan-ignore-next-line
1126 foreach ($this as $key => $val) {
1127 if (is_object($val) || (is_array($val))) {
1128 $this->{$key} = unserialize(serialize($val));
1129 }
1130 }
1131 }
1132
1138 public function getCellXfCollection()
1139 {
1141 }
1142
1150 public function getCellXfByIndex($pIndex)
1151 {
1152 return $this->cellXfCollection[$pIndex];
1153 }
1154
1162 public function getCellXfByHashCode($pValue)
1163 {
1164 foreach ($this->cellXfCollection as $cellXf) {
1165 if ($cellXf->getHashCode() === $pValue) {
1166 return $cellXf;
1167 }
1168 }
1169
1170 return false;
1171 }
1172
1180 public function cellXfExists($pCellStyle)
1181 {
1182 return in_array($pCellStyle, $this->cellXfCollection, true);
1183 }
1184
1190 public function getDefaultStyle()
1191 {
1192 if (isset($this->cellXfCollection[0])) {
1193 return $this->cellXfCollection[0];
1194 }
1195
1196 throw new Exception('No default style found for this workbook');
1197 }
1198
1202 public function addCellXf(Style $style): void
1203 {
1204 $this->cellXfCollection[] = $style;
1205 $style->setIndex(count($this->cellXfCollection) - 1);
1206 }
1207
1213 public function removeCellXfByIndex($pIndex): void
1214 {
1215 if ($pIndex > count($this->cellXfCollection) - 1) {
1216 throw new Exception('CellXf index is out of bounds.');
1217 }
1218
1219 // first remove the cellXf
1220 array_splice($this->cellXfCollection, $pIndex, 1);
1221
1222 // then update cellXf indexes for cells
1223 foreach ($this->workSheetCollection as $worksheet) {
1224 foreach ($worksheet->getCoordinates(false) as $coordinate) {
1225 $cell = $worksheet->getCell($coordinate);
1226 $xfIndex = $cell->getXfIndex();
1227 if ($xfIndex > $pIndex) {
1228 // decrease xf index by 1
1229 $cell->setXfIndex($xfIndex - 1);
1230 } elseif ($xfIndex == $pIndex) {
1231 // set to default xf index 0
1232 $cell->setXfIndex(0);
1233 }
1234 }
1235 }
1236 }
1237
1243 public function getCellXfSupervisor()
1244 {
1246 }
1247
1254 {
1256 }
1257
1265 public function getCellStyleXfByIndex($pIndex)
1266 {
1267 return $this->cellStyleXfCollection[$pIndex];
1268 }
1269
1277 public function getCellStyleXfByHashCode($pValue)
1278 {
1279 foreach ($this->cellStyleXfCollection as $cellStyleXf) {
1280 if ($cellStyleXf->getHashCode() === $pValue) {
1281 return $cellStyleXf;
1282 }
1283 }
1284
1285 return false;
1286 }
1287
1291 public function addCellStyleXf(Style $pStyle): void
1292 {
1293 $this->cellStyleXfCollection[] = $pStyle;
1294 $pStyle->setIndex(count($this->cellStyleXfCollection) - 1);
1295 }
1296
1302 public function removeCellStyleXfByIndex($pIndex): void
1303 {
1304 if ($pIndex > count($this->cellStyleXfCollection) - 1) {
1305 throw new Exception('CellStyleXf index is out of bounds.');
1306 }
1307 array_splice($this->cellStyleXfCollection, $pIndex, 1);
1308 }
1309
1314 public function garbageCollect(): void
1315 {
1316 // how many references are there to each cellXf ?
1317 $countReferencesCellXf = [];
1318 foreach ($this->cellXfCollection as $index => $cellXf) {
1319 $countReferencesCellXf[$index] = 0;
1320 }
1321
1322 foreach ($this->getWorksheetIterator() as $sheet) {
1323 // from cells
1324 foreach ($sheet->getCoordinates(false) as $coordinate) {
1325 $cell = $sheet->getCell($coordinate);
1326 ++$countReferencesCellXf[$cell->getXfIndex()];
1327 }
1328
1329 // from row dimensions
1330 foreach ($sheet->getRowDimensions() as $rowDimension) {
1331 if ($rowDimension->getXfIndex() !== null) {
1332 ++$countReferencesCellXf[$rowDimension->getXfIndex()];
1333 }
1334 }
1335
1336 // from column dimensions
1337 foreach ($sheet->getColumnDimensions() as $columnDimension) {
1338 ++$countReferencesCellXf[$columnDimension->getXfIndex()];
1339 }
1340 }
1341
1342 // remove cellXfs without references and create mapping so we can update xfIndex
1343 // for all cells and columns
1344 $countNeededCellXfs = 0;
1345 $map = [];
1346 foreach ($this->cellXfCollection as $index => $cellXf) {
1347 if ($countReferencesCellXf[$index] > 0 || $index == 0) { // we must never remove the first cellXf
1348 ++$countNeededCellXfs;
1349 } else {
1350 unset($this->cellXfCollection[$index]);
1351 }
1352 $map[$index] = $countNeededCellXfs - 1;
1353 }
1354 $this->cellXfCollection = array_values($this->cellXfCollection);
1355
1356 // update the index for all cellXfs
1357 foreach ($this->cellXfCollection as $i => $cellXf) {
1358 $cellXf->setIndex($i);
1359 }
1360
1361 // make sure there is always at least one cellXf (there should be)
1362 if (empty($this->cellXfCollection)) {
1363 $this->cellXfCollection[] = new Style();
1364 }
1365
1366 // update the xfIndex for all cells, row dimensions, column dimensions
1367 foreach ($this->getWorksheetIterator() as $sheet) {
1368 // for all cells
1369 foreach ($sheet->getCoordinates(false) as $coordinate) {
1370 $cell = $sheet->getCell($coordinate);
1371 $cell->setXfIndex($map[$cell->getXfIndex()]);
1372 }
1373
1374 // for all row dimensions
1375 foreach ($sheet->getRowDimensions() as $rowDimension) {
1376 if ($rowDimension->getXfIndex() !== null) {
1377 $rowDimension->setXfIndex($map[$rowDimension->getXfIndex()]);
1378 }
1379 }
1380
1381 // for all column dimensions
1382 foreach ($sheet->getColumnDimensions() as $columnDimension) {
1383 $columnDimension->setXfIndex($map[$columnDimension->getXfIndex()]);
1384 }
1385
1386 // also do garbage collection for all the sheets
1387 $sheet->garbageCollect();
1388 }
1389 }
1390
1396 public function getID()
1397 {
1398 return $this->uniqueID;
1399 }
1400
1406 public function getShowHorizontalScroll()
1407 {
1409 }
1410
1417 {
1418 $this->showHorizontalScroll = (bool) $showHorizontalScroll;
1419 }
1420
1426 public function getShowVerticalScroll()
1427 {
1429 }
1430
1437 {
1438 $this->showVerticalScroll = (bool) $showVerticalScroll;
1439 }
1440
1446 public function getShowSheetTabs()
1447 {
1448 return $this->showSheetTabs;
1449 }
1450
1456 public function setShowSheetTabs($showSheetTabs): void
1457 {
1458 $this->showSheetTabs = (bool) $showSheetTabs;
1459 }
1460
1466 public function getMinimized()
1467 {
1468 return $this->minimized;
1469 }
1470
1476 public function setMinimized($minimized): void
1477 {
1478 $this->minimized = (bool) $minimized;
1479 }
1480
1488 {
1490 }
1491
1499 {
1500 $this->autoFilterDateGrouping = (bool) $autoFilterDateGrouping;
1501 }
1502
1508 public function getFirstSheetIndex()
1509 {
1511 }
1512
1519 {
1520 if ($firstSheetIndex >= 0) {
1521 $this->firstSheetIndex = (int) $firstSheetIndex;
1522 } else {
1523 throw new Exception('First sheet index must be a positive integer.');
1524 }
1525 }
1526
1535 public function getVisibility()
1536 {
1537 return $this->visibility;
1538 }
1539
1555 public function setVisibility($visibility): void
1556 {
1557 if ($visibility === null) {
1559 }
1560
1561 if (in_array($visibility, self::$workbookViewVisibilityValues)) {
1562 $this->visibility = $visibility;
1563 } else {
1564 throw new Exception('Invalid visibility value.');
1565 }
1566 }
1567
1574 public function getTabRatio()
1575 {
1576 return $this->tabRatio;
1577 }
1578
1585 public function setTabRatio($tabRatio): void
1586 {
1587 if ($tabRatio >= 0 || $tabRatio <= 1000) {
1588 $this->tabRatio = (int) $tabRatio;
1589 } else {
1590 throw new Exception('Tab ratio must be between 0 and 1000.');
1591 }
1592 }
1593}
$path
Definition: aliased.php:25
An exception for terminatinating execution or to throw for unit testing.
isFormula()
Identify whether this is a named range or a named formula.
static strToUpper($pValue)
Convert a UTF-8 encoded string to upper case.
setRibbonXMLData($target, $xmlData)
set ribbon XML data.
removeNamedFormula(string $namedFormula, ?Worksheet $pSheet=null)
Remove named formula.
getShowHorizontalScroll()
Get the visibility of the horizonal scroll bar in the application.
getCellStyleXfByIndex($pIndex)
Get cellStyleXf by index.
addNamedFormula(NamedFormula $namedFormula)
Add a named formula.
getNamedFormulae()
Get an array of all Named Formulae.
getCellXfByHashCode($pValue)
Get cellXf by hash code.
setRibbonBinObjects($BinObjectsNames, $BinObjectsData)
store binaries ribbon objects (pictures).
getFirstSheetIndex()
Return the first sheet in the book view.
setVisibility($visibility)
Set the visibility status of the workbook.
setMacrosCertificate($certificate)
Set the macros certificate.
createSheet($sheetIndex=null)
Create sheet and add it to this workbook.
setUnparsedLoadedData(array $unparsedLoadedData)
List of unparsed loaded data for export to same format with better compatibility.
getMacrosCode()
Return the macros code.
setSecurity(Document\Security $pValue)
Set security.
getCellStyleXfCollection()
Get the workbook collection of cellStyleXfs.
setProperties(Document\Properties $pValue)
Set properties.
getDefinedName(string $definedName, ?Worksheet $pSheet=null)
Get named range.
getCellXfSupervisor()
Get the cellXf supervisor.
getCellXfCollection()
Get the workbook collection of cellXfs.
getCellXfByIndex($pIndex)
Get cellXf by index.
setActiveSheetIndex($pIndex)
Set active sheet index.
setShowSheetTabs($showSheetTabs)
Set the visibility of the sheet tabs in the application.
getSheet($pIndex)
Get sheet by index.
getRibbonXMLData($what='all')
retrieve ribbon XML Data.
setMinimized($minimized)
Set whether the workbook window is minimized.
hasRibbonBinObjects()
This workbook have additionnal object for the ribbon ?
removeCellStyleXfByIndex($pIndex)
Remove cellStyleXf by index.
getExtensionOnly($path)
return the extension of a filename.
getCellStyleXfByHashCode($pValue)
Get cellStyleXf by hash code.
removeNamedRange(string $namedRange, ?Worksheet $pSheet=null)
Remove named range.
hasMacrosCertificate()
Is the project signed ?
getID()
Return the unique ID value assigned to this spreadsheet workbook.
getNamedRange(string $namedRange, ?Worksheet $pSheet=null)
Get named range.
setShowHorizontalScroll($showHorizontalScroll)
Set the visibility of the horizonal scroll bar in the application.
removeSheetByIndex($pIndex)
Remove sheet by index.
addSheet(Worksheet $pSheet, $iSheetIndex=null)
Add sheet.
removeDefinedName(string $definedName, ?Worksheet $pSheet=null)
Remove defined name.
setHasMacros($hasMacros)
Define if a workbook has macros.
discardMacros()
Remove all macros, certificate from spreadsheet.
sheetNameExists($pSheetName)
Check if a sheet with a specified name already exists.
disconnectWorksheets()
Disconnect all worksheets from this PhpSpreadsheet workbook object, typically so that the PhpSpreadsh...
cellXfExists($pCellStyle)
Check if style exists in style collection.
getSheetByName($pName)
Get sheet by name.
getShowSheetTabs()
Get the visibility of the sheet tabs in the application.
addDefinedName(DefinedName $definedName)
Add a defined name (either a named range or a named formula).
getNamedFormula(string $namedFormula, ?Worksheet $pSheet=null)
Get named formula.
getLocalDefinedNameByType(string $name, bool $type, ?Worksheet $pSheet=null)
addCellXf(Style $style)
Add a cellXf to the workbook.
setMacrosCode($macroCode)
Set the macros code.
setFirstSheetIndex($firstSheetIndex)
Set the first sheet in the book view.
getSheetByCodeName($pName)
Get sheet by code name.
getShowVerticalScroll()
Get the visibility of the vertical scroll bar in the application.
addCellStyleXf(Style $pStyle)
Add a cellStyleXf to the workbook.
setShowVerticalScroll($showVerticalScroll)
Set the visibility of the vertical scroll bar in the application.
getDefinedNames()
Get an array of all Defined Names (both named ranges and named formulae).
hasMacros()
The workbook has macros ?
__destruct()
Code to execute when this worksheet is unset().
removeCellXfByIndex($pIndex)
Remove cellXf by index.
getWorksheetIterator()
Get worksheet iterator.
setIndexByName($sheetName, $newIndex)
Set index for sheet by sheet name.
getMinimized()
Return whether the workbook window is minimized.
getAutoFilterDateGrouping()
Return whether to group dates when presenting the user with filtering optiomd in the user interface.
sheetCodeNameExists($pSheetCodeName)
Check if a sheet with a specified code name already exists.
getMacrosCertificate()
Return the macros certificate.
getIndex(Worksheet $pSheet)
Get index for sheet.
hasRibbon()
This workbook have a custom UI ?
__clone()
Implement PHP __clone to create a deep clone, not just a shallow copy.
getTabRatio()
Get the ratio between the workbook tabs bar and the horizontal scroll bar.
setAutoFilterDateGrouping($autoFilterDateGrouping)
Set whether to group dates when presenting the user with filtering optiomd in the user interface.
getCalculationEngine()
Return the calculation engine for this worksheet.
copy()
Copy workbook (!= clone!).
getRibbonBinObjects($what='all')
retrieve Binaries Ribbon Objects.
addNamedRange(NamedRange $namedRange)
Add a named range.
getUnparsedLoadedData()
List of unparsed loaded data for export to same format with better compatibility.
__construct()
Create a new PhpSpreadsheet with one Worksheet.
addExternalSheet(Worksheet $pSheet, $iSheetIndex=null)
Add external sheet.
getNamedRanges()
Get an array of all Named Ranges.
getActiveSheetIndex()
Get active sheet index.
getVisibility()
Return the visibility status of the workbook.
setActiveSheetIndexByName($pValue)
Set active sheet index by name.
garbageCollect()
Eliminate all unneeded cellXf and afterwards update the xfIndex for all cells and columns in the work...
getGlobalDefinedNameByType(string $name, bool $type)
setTabRatio($tabRatio)
Set the ratio between the workbook tabs bar and the horizontal scroll bar TabRatio is assumed to be o...
setIndex($pValue)
Set own index in style collection.
Definition: Style.php:646
$key
Definition: croninfo.php:18
$i
Definition: disco.tpl.php:19
$style
Definition: example_012.php:70
if(@file_exists(dirname(__FILE__).'/lang/eng.php')) $certificate
Definition: example_052.php:77
$index
Definition: metadata.php:60
$target
Definition: test.php:19
$type