3 namespace PhpOffice\PhpSpreadsheet
;
5 use PhpOffice\PhpSpreadsheet\Calculation\Calculation
;
6 use PhpOffice\PhpSpreadsheet\Style\Style
;
7 use PhpOffice\PhpSpreadsheet\Worksheet\Iterator
;
8 use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet
;
20 * Document properties.
22 * @var Document\Properties
29 * @var Document\Security
34 * Collection of Worksheet objects.
38 private $workSheetCollection = [];
45 private $calculationEngine;
52 private $activeSheetIndex = 0;
59 private $namedRanges = [];
66 private $cellXfSupervisor;
73 private $cellXfCollection = [];
76 * CellStyleXf collection.
80 private $cellStyleXfCollection = [];
83 * hasMacros : this workbook have macros ?
87 private $hasMacros = false;
90 * macrosCode : all macros code as binary data (the vbaProject.bin file, this include form, code, etc.), null if no macro.
97 * macrosCertificate : if macros are signed, contains binary data vbaProjectSignature.bin file, null if not signed.
101 private $macrosCertificate;
104 * ribbonXMLData : null if workbook is'nt Excel 2007 or not contain a customized UI.
108 private $ribbonXMLData;
111 * ribbonBinObjects : null if workbook is'nt Excel 2007 or not contain embedded objects (picture(s)) for Ribbon Elements
112 * ignored if $ribbonXMLData is null.
116 private $ribbonBinObjects;
119 * The workbook has macros ?
123 public function hasMacros()
125 return $this->hasMacros
;
129 * Define if a workbook has macros.
131 * @param bool $hasMacros true|false
133 public function setHasMacros($hasMacros)
135 $this->hasMacros
= (bool) $hasMacros;
139 * Set the macros code.
141 * @param string $macroCode string|null
143 public function setMacrosCode($macroCode)
145 $this->macrosCode
= $macroCode;
146 $this->setHasMacros($macroCode !== null);
150 * Return the macros code.
152 * @return null|string
154 public function getMacrosCode()
156 return $this->macrosCode
;
160 * Set the macros certificate.
162 * @param null|string $certificate
164 public function setMacrosCertificate($certificate)
166 $this->macrosCertificate
= $certificate;
170 * Is the project signed ?
172 * @return bool true|false
174 public function hasMacrosCertificate()
176 return $this->macrosCertificate
!== null;
180 * Return the macros certificate.
182 * @return null|string
184 public function getMacrosCertificate()
186 return $this->macrosCertificate
;
190 * Remove all macros, certificate from spreadsheet.
192 public function discardMacros()
194 $this->hasMacros
= false;
195 $this->macrosCode
= null;
196 $this->macrosCertificate
= null;
200 * set ribbon XML data.
202 * @param null|mixed $target
203 * @param null|mixed $xmlData
205 public function setRibbonXMLData($target, $xmlData)
207 if ($target !== null && $xmlData !== null) {
208 $this->ribbonXMLData
= ['target' => $target, 'data' => $xmlData];
210 $this->ribbonXMLData
= null;
215 * retrieve ribbon XML Data.
217 * return string|null|array
219 * @param string $what
223 public function getRibbonXMLData($what = 'all') //we need some constants here...
226 $what = strtolower($what);
229 $returnData = $this->ribbonXMLData
;
234 if (is_array($this->ribbonXMLData
) && isset($this->ribbonXMLData
[$what])) {
235 $returnData = $this->ribbonXMLData
[$what];
245 * store binaries ribbon objects (pictures).
247 * @param null|mixed $BinObjectsNames
248 * @param null|mixed $BinObjectsData
250 public function setRibbonBinObjects($BinObjectsNames, $BinObjectsData)
252 if ($BinObjectsNames !== null && $BinObjectsData !== null) {
253 $this->ribbonBinObjects
= ['names' => $BinObjectsNames, 'data' => $BinObjectsData];
255 $this->ribbonBinObjects
= null;
260 * return the extension of a filename. Internal use for a array_map callback (php<5.3 don't like lambda function).
266 private function getExtensionOnly($path)
268 return pathinfo($path, PATHINFO_EXTENSION
);
272 * retrieve Binaries Ribbon Objects.
274 * @param string $what
278 public function getRibbonBinObjects($what = 'all')
281 $what = strtolower($what);
284 return $this->ribbonBinObjects
;
289 if (is_array($this->ribbonBinObjects
) && isset($this->ribbonBinObjects
[$what])) {
290 $ReturnData = $this->ribbonBinObjects
[$what];
295 if (is_array($this->ribbonBinObjects
) &&
296 isset($this->ribbonBinObjects
['data']) && is_array($this->ribbonBinObjects
['data'])) {
297 $tmpTypes = array_keys($this->ribbonBinObjects
['data']);
298 $ReturnData = array_unique(array_map([$this, 'getExtensionOnly'], $tmpTypes));
300 $ReturnData = []; // the caller want an array... not null if empty
310 * This workbook have a custom UI ?
314 public function hasRibbon()
316 return $this->ribbonXMLData
!== null;
320 * This workbook have additionnal object for the ribbon ?
324 public function hasRibbonBinObjects()
326 return $this->ribbonBinObjects
!== null;
330 * Check if a sheet with a specified code name already exists.
332 * @param string $pSheetCodeName Name of the worksheet to check
336 public function sheetCodeNameExists($pSheetCodeName)
338 return $this->getSheetByCodeName($pSheetCodeName) !== null;
342 * Get sheet by code name. Warning : sheet don't have always a code name !
344 * @param string $pName Sheet name
348 public function getSheetByCodeName($pName)
350 $worksheetCount = count($this->workSheetCollection
);
351 for ($i = 0; $i < $worksheetCount; ++
$i) {
352 if ($this->workSheetCollection
[$i]->getCodeName() == $pName) {
353 return $this->workSheetCollection
[$i];
361 * Create a new PhpSpreadsheet with one Worksheet.
363 public function __construct()
365 $this->uniqueID
= uniqid('', true);
366 $this->calculationEngine
= new Calculation($this);
368 // Initialise worksheet collection and add one worksheet
369 $this->workSheetCollection
= [];
370 $this->workSheetCollection
[] = new Worksheet($this);
371 $this->activeSheetIndex
= 0;
373 // Create document properties
374 $this->properties
= new Document\
Properties();
376 // Create document security
377 $this->security
= new Document\
Security();
380 $this->namedRanges
= [];
382 // Create the cellXf supervisor
383 $this->cellXfSupervisor
= new Style(true);
384 $this->cellXfSupervisor
->bindParent($this);
386 // Create the default style
387 $this->addCellXf(new Style());
388 $this->addCellStyleXf(new Style());
392 * Code to execute when this worksheet is unset().
394 public function __destruct()
396 $this->calculationEngine
= null;
397 $this->disconnectWorksheets();
401 * Disconnect all worksheets from this PhpSpreadsheet workbook object,
402 * typically so that the PhpSpreadsheet object can be unset.
404 public function disconnectWorksheets()
407 foreach ($this->workSheetCollection
as $k => &$worksheet) {
408 $worksheet->disconnectCells();
409 $this->workSheetCollection
[$k] = null;
412 $this->workSheetCollection
= [];
416 * Return the calculation engine for this worksheet.
418 * @return Calculation
420 public function getCalculationEngine()
422 return $this->calculationEngine
;
428 * @return Document\Properties
430 public function getProperties()
432 return $this->properties
;
438 * @param Document\Properties $pValue
440 public function setProperties(Document\Properties
$pValue)
442 $this->properties
= $pValue;
448 * @return Document\Security
450 public function getSecurity()
452 return $this->security
;
458 * @param Document\Security $pValue
460 public function setSecurity(Document\Security
$pValue)
462 $this->security
= $pValue;
472 public function getActiveSheet()
474 return $this->getSheet($this->activeSheetIndex
);
478 * Create sheet and add it to this workbook.
480 * @param null|int $sheetIndex Index where sheet should go (0,1,..., or null for last)
486 public function createSheet($sheetIndex = null)
488 $newSheet = new Worksheet($this);
489 $this->addSheet($newSheet, $sheetIndex);
495 * Check if a sheet with a specified name already exists.
497 * @param string $pSheetName Name of the worksheet to check
501 public function sheetNameExists($pSheetName)
503 return $this->getSheetByName($pSheetName) !== null;
509 * @param Worksheet $pSheet
510 * @param null|int $iSheetIndex Index where sheet should go (0,1,..., or null for last)
516 public function addSheet(Worksheet
$pSheet, $iSheetIndex = null)
518 if ($this->sheetNameExists($pSheet->getTitle())) {
520 "Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename this worksheet first."
524 if ($iSheetIndex === null) {
525 if ($this->activeSheetIndex
< 0) {
526 $this->activeSheetIndex
= 0;
528 $this->workSheetCollection
[] = $pSheet;
530 // Insert the sheet at the requested index
532 $this->workSheetCollection
,
538 // Adjust active sheet index if necessary
539 if ($this->activeSheetIndex
>= $iSheetIndex) {
540 ++
$this->activeSheetIndex
;
544 if ($pSheet->getParent() === null) {
545 $pSheet->rebindParent($this);
552 * Remove sheet by index.
554 * @param int $pIndex Active sheet index
558 public function removeSheetByIndex($pIndex)
560 $numSheets = count($this->workSheetCollection
);
561 if ($pIndex > $numSheets - 1) {
563 "You tried to remove a sheet by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
566 array_splice($this->workSheetCollection
, $pIndex, 1);
568 // Adjust active sheet index if necessary
569 if (($this->activeSheetIndex
>= $pIndex) &&
570 ($pIndex > count($this->workSheetCollection
) - 1)) {
571 --$this->activeSheetIndex
;
576 * Get sheet by index.
578 * @param int $pIndex Sheet index
584 public function getSheet($pIndex)
586 if (!isset($this->workSheetCollection
[$pIndex])) {
587 $numSheets = $this->getSheetCount();
590 "Your requested sheet index: {$pIndex} is out of bounds. The actual number of sheets is {$numSheets}."
594 return $this->workSheetCollection
[$pIndex];
600 * @return Worksheet[]
602 public function getAllSheets()
604 return $this->workSheetCollection
;
610 * @param string $pName Sheet name
614 public function getSheetByName($pName)
616 $worksheetCount = count($this->workSheetCollection
);
617 for ($i = 0; $i < $worksheetCount; ++
$i) {
618 if ($this->workSheetCollection
[$i]->getTitle() === $pName) {
619 return $this->workSheetCollection
[$i];
627 * Get index for sheet.
629 * @param Worksheet $pSheet
635 public function getIndex(Worksheet
$pSheet)
637 foreach ($this->workSheetCollection
as $key => $value) {
638 if ($value->getHashCode() == $pSheet->getHashCode()) {
643 throw new Exception('Sheet does not exist.');
647 * Set index for sheet by sheet name.
649 * @param string $sheetName Sheet name to modify index for
650 * @param int $newIndex New index for the sheet
654 * @return int New sheet index
656 public function setIndexByName($sheetName, $newIndex)
658 $oldIndex = $this->getIndex($this->getSheetByName($sheetName));
659 $pSheet = array_splice(
660 $this->workSheetCollection
,
665 $this->workSheetCollection
,
679 public function getSheetCount()
681 return count($this->workSheetCollection
);
685 * Get active sheet index.
687 * @return int Active sheet index
689 public function getActiveSheetIndex()
691 return $this->activeSheetIndex
;
695 * Set active sheet index.
697 * @param int $pIndex Active sheet index
703 public function setActiveSheetIndex($pIndex)
705 $numSheets = count($this->workSheetCollection
);
707 if ($pIndex > $numSheets - 1) {
709 "You tried to set a sheet active by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
712 $this->activeSheetIndex
= $pIndex;
714 return $this->getActiveSheet();
718 * Set active sheet index by name.
720 * @param string $pValue Sheet title
726 public function setActiveSheetIndexByName($pValue)
728 if (($worksheet = $this->getSheetByName($pValue)) instanceof Worksheet
) {
729 $this->setActiveSheetIndex($this->getIndex($worksheet));
734 throw new Exception('Workbook does not contain sheet:' . $pValue);
742 public function getSheetNames()
745 $worksheetCount = $this->getSheetCount();
746 for ($i = 0; $i < $worksheetCount; ++
$i) {
747 $returnValue[] = $this->getSheet($i)->getTitle();
754 * Add external sheet.
756 * @param Worksheet $pSheet External sheet to add
757 * @param null|int $iSheetIndex Index where sheet should go (0,1,..., or null for last)
763 public function addExternalSheet(Worksheet
$pSheet, $iSheetIndex = null)
765 if ($this->sheetNameExists($pSheet->getTitle())) {
766 throw new Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
769 // count how many cellXfs there are in this workbook currently, we will need this below
770 $countCellXfs = count($this->cellXfCollection
);
772 // copy all the shared cellXfs from the external workbook and append them to the current
773 foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) {
774 $this->addCellXf(clone $cellXf);
777 // move sheet to this workbook
778 $pSheet->rebindParent($this);
780 // update the cellXfs
781 foreach ($pSheet->getCoordinates(false) as $coordinate) {
782 $cell = $pSheet->getCell($coordinate);
783 $cell->setXfIndex($cell->getXfIndex() +
$countCellXfs);
786 return $this->addSheet($pSheet, $iSheetIndex);
792 * @return NamedRange[]
794 public function getNamedRanges()
796 return $this->namedRanges
;
802 * @param NamedRange $namedRange
806 public function addNamedRange(NamedRange
$namedRange)
808 if ($namedRange->getScope() == null) {
810 $this->namedRanges
[$namedRange->getName()] = $namedRange;
813 $this->namedRanges
[$namedRange->getScope()->getTitle() . '!' . $namedRange->getName()] = $namedRange;
822 * @param string $namedRange
823 * @param null|Worksheet $pSheet Scope. Use null for global scope
825 * @return null|NamedRange
827 public function getNamedRange($namedRange, Worksheet
$pSheet = null)
831 if ($namedRange != '' && ($namedRange !== null)) {
832 // first look for global defined name
833 if (isset($this->namedRanges
[$namedRange])) {
834 $returnValue = $this->namedRanges
[$namedRange];
837 // then look for local defined name (has priority over global defined name if both names exist)
838 if (($pSheet !== null) && isset($this->namedRanges
[$pSheet->getTitle() . '!' . $namedRange])) {
839 $returnValue = $this->namedRanges
[$pSheet->getTitle() . '!' . $namedRange];
847 * Remove named range.
849 * @param string $namedRange
850 * @param null|Worksheet $pSheet scope: use null for global scope
852 * @return Spreadsheet
854 public function removeNamedRange($namedRange, Worksheet
$pSheet = null)
856 if ($pSheet === null) {
857 if (isset($this->namedRanges
[$namedRange])) {
858 unset($this->namedRanges
[$namedRange]);
861 if (isset($this->namedRanges
[$pSheet->getTitle() . '!' . $namedRange])) {
862 unset($this->namedRanges
[$pSheet->getTitle() . '!' . $namedRange]);
870 * Get worksheet iterator.
874 public function getWorksheetIterator()
876 return new Iterator($this);
880 * Copy workbook (!= clone!).
882 * @return Spreadsheet
884 public function copy()
886 $copied = clone $this;
888 $worksheetCount = count($this->workSheetCollection
);
889 for ($i = 0; $i < $worksheetCount; ++
$i) {
890 $this->workSheetCollection
[$i] = $this->workSheetCollection
[$i]->copy();
891 $this->workSheetCollection
[$i]->rebindParent($this);
898 * Implement PHP __clone to create a deep clone, not just a shallow copy.
900 public function __clone()
902 foreach ($this as $key => $val) {
903 if (is_object($val) ||
(is_array($val))) {
904 $this->{$key} = unserialize(serialize($val));
910 * Get the workbook collection of cellXfs.
914 public function getCellXfCollection()
916 return $this->cellXfCollection
;
920 * Get cellXf by index.
926 public function getCellXfByIndex($pIndex)
928 return $this->cellXfCollection
[$pIndex];
932 * Get cellXf by hash code.
934 * @param string $pValue
936 * @return false|Style
938 public function getCellXfByHashCode($pValue)
940 foreach ($this->cellXfCollection
as $cellXf) {
941 if ($cellXf->getHashCode() == $pValue) {
950 * Check if style exists in style collection.
952 * @param Style $pCellStyle
956 public function cellXfExists($pCellStyle)
958 return in_array($pCellStyle, $this->cellXfCollection
, true);
968 public function getDefaultStyle()
970 if (isset($this->cellXfCollection
[0])) {
971 return $this->cellXfCollection
[0];
974 throw new Exception('No default style found for this workbook');
978 * Add a cellXf to the workbook.
980 * @param Style $style
982 public function addCellXf(Style
$style)
984 $this->cellXfCollection
[] = $style;
985 $style->setIndex(count($this->cellXfCollection
) - 1);
989 * Remove cellXf by index. It is ensured that all cells get their xf index updated.
991 * @param int $pIndex Index to cellXf
995 public function removeCellXfByIndex($pIndex)
997 if ($pIndex > count($this->cellXfCollection
) - 1) {
998 throw new Exception('CellXf index is out of bounds.');
1001 // first remove the cellXf
1002 array_splice($this->cellXfCollection
, $pIndex, 1);
1004 // then update cellXf indexes for cells
1005 foreach ($this->workSheetCollection
as $worksheet) {
1006 foreach ($worksheet->getCoordinates(false) as $coordinate) {
1007 $cell = $worksheet->getCell($coordinate);
1008 $xfIndex = $cell->getXfIndex();
1009 if ($xfIndex > $pIndex) {
1010 // decrease xf index by 1
1011 $cell->setXfIndex($xfIndex - 1);
1012 } elseif ($xfIndex == $pIndex) {
1013 // set to default xf index 0
1014 $cell->setXfIndex(0);
1021 * Get the cellXf supervisor.
1025 public function getCellXfSupervisor()
1027 return $this->cellXfSupervisor
;
1031 * Get the workbook collection of cellStyleXfs.
1035 public function getCellStyleXfCollection()
1037 return $this->cellStyleXfCollection
;
1041 * Get cellStyleXf by index.
1043 * @param int $pIndex Index to cellXf
1047 public function getCellStyleXfByIndex($pIndex)
1049 return $this->cellStyleXfCollection
[$pIndex];
1053 * Get cellStyleXf by hash code.
1055 * @param string $pValue
1057 * @return false|Style
1059 public function getCellStyleXfByHashCode($pValue)
1061 foreach ($this->cellStyleXfCollection
as $cellStyleXf) {
1062 if ($cellStyleXf->getHashCode() == $pValue) {
1063 return $cellStyleXf;
1071 * Add a cellStyleXf to the workbook.
1073 * @param Style $pStyle
1075 public function addCellStyleXf(Style
$pStyle)
1077 $this->cellStyleXfCollection
[] = $pStyle;
1078 $pStyle->setIndex(count($this->cellStyleXfCollection
) - 1);
1082 * Remove cellStyleXf by index.
1084 * @param int $pIndex Index to cellXf
1088 public function removeCellStyleXfByIndex($pIndex)
1090 if ($pIndex > count($this->cellStyleXfCollection
) - 1) {
1091 throw new Exception('CellStyleXf index is out of bounds.');
1093 array_splice($this->cellStyleXfCollection
, $pIndex, 1);
1097 * Eliminate all unneeded cellXf and afterwards update the xfIndex for all cells
1098 * and columns in the workbook.
1100 public function garbageCollect()
1102 // how many references are there to each cellXf ?
1103 $countReferencesCellXf = [];
1104 foreach ($this->cellXfCollection
as $index => $cellXf) {
1105 $countReferencesCellXf[$index] = 0;
1108 foreach ($this->getWorksheetIterator() as $sheet) {
1110 foreach ($sheet->getCoordinates(false) as $coordinate) {
1111 $cell = $sheet->getCell($coordinate);
1112 ++
$countReferencesCellXf[$cell->getXfIndex()];
1115 // from row dimensions
1116 foreach ($sheet->getRowDimensions() as $rowDimension) {
1117 if ($rowDimension->getXfIndex() !== null) {
1118 ++
$countReferencesCellXf[$rowDimension->getXfIndex()];
1122 // from column dimensions
1123 foreach ($sheet->getColumnDimensions() as $columnDimension) {
1124 ++
$countReferencesCellXf[$columnDimension->getXfIndex()];
1128 // remove cellXfs without references and create mapping so we can update xfIndex
1129 // for all cells and columns
1130 $countNeededCellXfs = 0;
1131 foreach ($this->cellXfCollection
as $index => $cellXf) {
1132 if ($countReferencesCellXf[$index] > 0 ||
$index == 0) { // we must never remove the first cellXf
1133 ++
$countNeededCellXfs;
1135 unset($this->cellXfCollection
[$index]);
1137 $map[$index] = $countNeededCellXfs - 1;
1139 $this->cellXfCollection
= array_values($this->cellXfCollection
);
1141 // update the index for all cellXfs
1142 foreach ($this->cellXfCollection
as $i => $cellXf) {
1143 $cellXf->setIndex($i);
1146 // make sure there is always at least one cellXf (there should be)
1147 if (empty($this->cellXfCollection
)) {
1148 $this->cellXfCollection
[] = new Style();
1151 // update the xfIndex for all cells, row dimensions, column dimensions
1152 foreach ($this->getWorksheetIterator() as $sheet) {
1154 foreach ($sheet->getCoordinates(false) as $coordinate) {
1155 $cell = $sheet->getCell($coordinate);
1156 $cell->setXfIndex($map[$cell->getXfIndex()]);
1159 // for all row dimensions
1160 foreach ($sheet->getRowDimensions() as $rowDimension) {
1161 if ($rowDimension->getXfIndex() !== null) {
1162 $rowDimension->setXfIndex($map[$rowDimension->getXfIndex()]);
1166 // for all column dimensions
1167 foreach ($sheet->getColumnDimensions() as $columnDimension) {
1168 $columnDimension->setXfIndex($map[$columnDimension->getXfIndex()]);
1171 // also do garbage collection for all the sheets
1172 $sheet->garbageCollect();
1177 * Return the unique ID value assigned to this spreadsheet workbook.
1181 public function getID()
1183 return $this->uniqueID
;