229 $this->macrosCode = $macroCode;
260 return $this->macrosCertificate !==
null;
279 $this->macrosCode =
null;
280 $this->macrosCertificate =
null;
291 if (
$target !==
null && $xmlData !==
null) {
292 $this->ribbonXMLData = [
'target' =>
$target,
'data' => $xmlData];
294 $this->ribbonXMLData =
null;
308 $what = strtolower($what);
316 if (is_array($this->ribbonXMLData) && isset($this->ribbonXMLData[$what])) {
317 $returnData = $this->ribbonXMLData[$what];
334 if ($BinObjectsNames !==
null && $BinObjectsData !==
null) {
335 $this->ribbonBinObjects = [
'names' => $BinObjectsNames,
'data' => $BinObjectsData];
337 $this->ribbonBinObjects =
null;
374 $extension = pathinfo(
$path, PATHINFO_EXTENSION);
376 return is_array($extension) ?
'' : $extension;
389 $what = strtolower($what);
397 if (is_array($this->ribbonBinObjects) && isset($this->ribbonBinObjects[$what])) {
398 $ReturnData = $this->ribbonBinObjects[$what];
404 is_array($this->ribbonBinObjects) &&
405 isset($this->ribbonBinObjects[
'data']) && is_array($this->ribbonBinObjects[
'data'])
407 $tmpTypes = array_keys($this->ribbonBinObjects[
'data']);
408 $ReturnData = array_unique(array_map([$this,
'getExtensionOnly'], $tmpTypes));
426 return $this->ribbonXMLData !==
null;
436 return $this->ribbonBinObjects !==
null;
460 $worksheetCount = count($this->workSheetCollection);
461 for (
$i = 0;
$i < $worksheetCount; ++
$i) {
462 if ($this->workSheetCollection[
$i]->getCodeName() == $pName) {
463 return $this->workSheetCollection[
$i];
475 $this->uniqueID = uniqid(
'',
true);
479 $this->workSheetCollection = [];
480 $this->workSheetCollection[] =
new Worksheet($this);
481 $this->activeSheetIndex = 0;
490 $this->definedNames = [];
493 $this->cellXfSupervisor =
new Style(
true);
494 $this->cellXfSupervisor->bindParent($this);
507 $this->calculationEngine =
null;
508 $this->cellXfCollection = [];
509 $this->cellStyleXfCollection = [];
518 foreach ($this->workSheetCollection as $worksheet) {
519 $worksheet->disconnectCells();
522 $this->workSheetCollection = [];
550 $this->properties = $pValue;
568 $this->security = $pValue;
578 return $this->
getSheet($this->activeSheetIndex);
591 $this->
addSheet($newSheet, $sheetIndex);
619 "Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename this worksheet first."
623 if ($iSheetIndex ===
null) {
624 if ($this->activeSheetIndex < 0) {
625 $this->activeSheetIndex = 0;
627 $this->workSheetCollection[] = $pSheet;
631 $this->workSheetCollection,
638 if ($this->activeSheetIndex >= $iSheetIndex) {
643 if ($pSheet->getParent() ===
null) {
644 $pSheet->rebindParent($this);
657 $numSheets = count($this->workSheetCollection);
658 if ($pIndex > $numSheets - 1) {
660 "You tried to remove a sheet by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
663 array_splice($this->workSheetCollection, $pIndex, 1);
667 ($this->activeSheetIndex >= $pIndex) &&
668 ($this->activeSheetIndex > 0 || $numSheets <= 1)
683 if (!isset($this->workSheetCollection[$pIndex])) {
687 "Your requested sheet index: {$pIndex} is out of bounds. The actual number of sheets is {$numSheets}."
691 return $this->workSheetCollection[$pIndex];
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];
730 foreach ($this->workSheetCollection as
$key => $value) {
731 if ($value->getHashCode() === $pSheet->
getHashCode()) {
736 throw new Exception(
'Sheet does not exist.');
750 $pSheet = array_splice(
751 $this->workSheetCollection,
756 $this->workSheetCollection,
772 return count($this->workSheetCollection);
794 $numSheets = count($this->workSheetCollection);
796 if ($pIndex > $numSheets - 1) {
798 "You tried to set a sheet active by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
801 $this->activeSheetIndex = $pIndex;
821 throw new Exception(
'Workbook does not contain sheet:' . $pValue);
833 for (
$i = 0;
$i < $worksheetCount; ++
$i) {
834 $returnValue[] = $this->
getSheet(
$i)->getTitle();
851 throw new Exception(
"Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
855 $countCellXfs = count($this->cellXfCollection);
858 foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) {
863 $pSheet->rebindParent($this);
866 foreach ($pSheet->getCoordinates(
false) as $coordinate) {
867 $cell = $pSheet->getCell($coordinate);
868 $cell->setXfIndex($cell->getXfIndex() + $countCellXfs);
871 return $this->
addSheet($pSheet, $iSheetIndex);
884 return $definedName->
isFormula() === self::DEFINED_NAME_IS_RANGE;
899 return $definedName->
isFormula() === self::DEFINED_NAME_IS_FORMULA;
939 if ($definedName->
getScope() ==
null) {
941 $this->definedNames[$upperCaseName] = $definedName;
944 $this->definedNames[$definedName->
getScope()->getTitle() .
'!' . $upperCaseName] = $definedName;
957 if ($namedRange !==
'') {
965 return $returnValue instanceof
NamedRange ? $returnValue :
null;
977 if ($namedFormula !==
'') {
985 return $returnValue instanceof
NamedFormula ? $returnValue :
null;
990 if (isset($this->definedNames[
$name]) && $this->definedNames[
$name]->isFormula() ===
$type) {
991 return $this->definedNames[
$name];
1000 ($pSheet !==
null) && isset($this->definedNames[$pSheet->getTitle() .
'!' .
$name])
1001 && $this->definedNames[$pSheet->getTitle() .
'!' .
$name]->isFormula() ===
$type
1003 return $this->definedNames[$pSheet->getTitle() .
'!' .
$name];
1016 $returnValue =
null;
1018 if ($definedName !==
'') {
1021 if (isset($this->definedNames[$definedName])) {
1022 $returnValue = $this->definedNames[$definedName];
1026 if (($pSheet !==
null) && isset($this->definedNames[$pSheet->getTitle() .
'!' . $definedName])) {
1027 $returnValue = $this->definedNames[$pSheet->getTitle() .
'!' . $definedName];
1031 return $returnValue;
1077 if ($pSheet ===
null) {
1078 if (isset($this->definedNames[$definedName])) {
1079 unset($this->definedNames[$definedName]);
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]);
1109 $copied = clone $this;
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);
1126 foreach ($this as
$key => $val) {
1127 if (is_object($val) || (is_array($val))) {
1128 $this->{
$key} = unserialize(serialize($val));
1152 return $this->cellXfCollection[$pIndex];
1164 foreach ($this->cellXfCollection as $cellXf) {
1165 if ($cellXf->getHashCode() === $pValue) {
1182 return in_array($pCellStyle, $this->cellXfCollection,
true);
1192 if (isset($this->cellXfCollection[0])) {
1193 return $this->cellXfCollection[0];
1196 throw new Exception(
'No default style found for this workbook');
1204 $this->cellXfCollection[] =
$style;
1205 $style->setIndex(count($this->cellXfCollection) - 1);
1215 if ($pIndex > count($this->cellXfCollection) - 1) {
1216 throw new Exception(
'CellXf index is out of bounds.');
1220 array_splice($this->cellXfCollection, $pIndex, 1);
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) {
1229 $cell->setXfIndex($xfIndex - 1);
1230 } elseif ($xfIndex == $pIndex) {
1232 $cell->setXfIndex(0);
1267 return $this->cellStyleXfCollection[$pIndex];
1279 foreach ($this->cellStyleXfCollection as $cellStyleXf) {
1280 if ($cellStyleXf->getHashCode() === $pValue) {
1281 return $cellStyleXf;
1293 $this->cellStyleXfCollection[] = $pStyle;
1294 $pStyle->
setIndex(count($this->cellStyleXfCollection) - 1);
1304 if ($pIndex > count($this->cellStyleXfCollection) - 1) {
1305 throw new Exception(
'CellStyleXf index is out of bounds.');
1307 array_splice($this->cellStyleXfCollection, $pIndex, 1);
1317 $countReferencesCellXf = [];
1318 foreach ($this->cellXfCollection as
$index => $cellXf) {
1319 $countReferencesCellXf[
$index] = 0;
1324 foreach ($sheet->getCoordinates(
false) as $coordinate) {
1325 $cell = $sheet->getCell($coordinate);
1326 ++$countReferencesCellXf[$cell->getXfIndex()];
1330 foreach ($sheet->getRowDimensions() as $rowDimension) {
1331 if ($rowDimension->getXfIndex() !==
null) {
1332 ++$countReferencesCellXf[$rowDimension->getXfIndex()];
1337 foreach ($sheet->getColumnDimensions() as $columnDimension) {
1338 ++$countReferencesCellXf[$columnDimension->getXfIndex()];
1344 $countNeededCellXfs = 0;
1346 foreach ($this->cellXfCollection as
$index => $cellXf) {
1347 if ($countReferencesCellXf[
$index] > 0 ||
$index == 0) {
1348 ++$countNeededCellXfs;
1350 unset($this->cellXfCollection[
$index]);
1354 $this->cellXfCollection = array_values($this->cellXfCollection);
1357 foreach ($this->cellXfCollection as
$i => $cellXf) {
1358 $cellXf->setIndex(
$i);
1362 if (empty($this->cellXfCollection)) {
1363 $this->cellXfCollection[] =
new Style();
1369 foreach ($sheet->getCoordinates(
false) as $coordinate) {
1370 $cell = $sheet->getCell($coordinate);
1371 $cell->setXfIndex(
$map[$cell->getXfIndex()]);
1375 foreach ($sheet->getRowDimensions() as $rowDimension) {
1376 if ($rowDimension->getXfIndex() !==
null) {
1377 $rowDimension->setXfIndex(
$map[$rowDimension->getXfIndex()]);
1382 foreach ($sheet->getColumnDimensions() as $columnDimension) {
1383 $columnDimension->setXfIndex(
$map[$columnDimension->getXfIndex()]);
1387 $sheet->garbageCollect();
1523 throw new Exception(
'First sheet index must be a positive integer.');
1561 if (in_array(
$visibility, self::$workbookViewVisibilityValues)) {
1564 throw new Exception(
'Invalid visibility value.');
1590 throw new Exception(
'Tab ratio must be between 0 and 1000.');
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.
getDefaultStyle()
Get default style.
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.
const DEFINED_NAME_IS_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.
const VISIBILITY_VERY_HIDDEN
const DEFINED_NAME_IS_FORMULA
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.
getSheetNames()
Get sheet names.
getSecurity()
Get security.
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...
static $workbookViewVisibilityValues
cellXfExists($pCellStyle)
Check if style exists in style collection.
getProperties()
Get properties.
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.
getSheetCount()
Get sheet count.
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.
getActiveSheet()
Get active sheet.
getWorksheetIterator()
Get worksheet iterator.
getAllSheets()
Get all sheets.
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.
if(@file_exists(dirname(__FILE__).'/lang/eng.php')) $certificate
getHashCode()
Get hash code.