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.