Workbook::writeCalcPr() - Code Metrics - Inspection of "Sheet title can contain exclamation mark (in formu..." - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — develop ( 57404f...50a9bc )

by Adrien

created 2018-10-03 03:52 UTC

Workbook::writeCalcPr() A

↳ Parent: Workbook

Complexity

Conditions	4
Paths	1

Size

Total Lines	15
Code Lines	7

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	8
CRAP Score	4

Importance

Changes

Metric	Value
cc	4
eloc	7
nc	1
nop	2
dl	0
loc	15
ccs	8
cts	8
cp	1
crap	4
rs	10
c	0
b	0
f	0

<?php

namespace PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Shared\XMLWriter;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
.
|-- OtherDir
|   |-- Bar.php
|   `-- Foo.php
`-- SomeDir
    `-- Foo.php
use PhpOffice\PhpSpreadsheet\Writer\Exception as WriterException;

class Workbook extends WriterPart
{
    /**
     * Write workbook to XML format.
     *
     * @param Spreadsheet $spreadsheet
     * @param bool $recalcRequired Indicate whether formulas should be recalculated before writing
     *
     * @throws WriterException
     *
     * @return string XML Output
     */
    public function writeWorkbook(Spreadsheet $spreadsheet, $recalcRequired = false)
    {
        // Create XML writer
        if ($this->getParentWriter()->getUseDiskCaching()) {
            $objWriter = new XMLWriter(XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory());
        } else {
            $objWriter = new XMLWriter(XMLWriter::STORAGE_MEMORY);
        }

        // XML header
        $objWriter->startDocument('1.0', 'UTF-8', 'yes');

        // workbook
        $objWriter->startElement('workbook');
        $objWriter->writeAttribute('xml:space', 'preserve');
        $objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
        $objWriter->writeAttribute('xmlns:r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships');

        // fileVersion
        $this->writeFileVersion($objWriter);

        // workbookPr
        $this->writeWorkbookPr($objWriter);

        // workbookProtection
        $this->writeWorkbookProtection($objWriter, $spreadsheet);

        // bookViews
        if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
            $this->writeBookViews($objWriter, $spreadsheet);
        }

        // sheets
        $this->writeSheets($objWriter, $spreadsheet);

        // definedNames
        $this->writeDefinedNames($objWriter, $spreadsheet);

        // calcPr
        $this->writeCalcPr($objWriter, $recalcRequired);

        $objWriter->endElement();

        // Return
        return $objWriter->getData();
    }

    /**
     * Write file version.
     *
     * @param XMLWriter $objWriter XML Writer
     */
    private function writeFileVersion(XMLWriter $objWriter)
    {
        $objWriter->startElement('fileVersion');
        $objWriter->writeAttribute('appName', 'xl');
        $objWriter->writeAttribute('lastEdited', '4');
        $objWriter->writeAttribute('lowestEdited', '4');
        $objWriter->writeAttribute('rupBuild', '4505');
        $objWriter->endElement();
    }

    /**
     * Write WorkbookPr.
     *
     * @param XMLWriter $objWriter XML Writer
     */
    private function writeWorkbookPr(XMLWriter $objWriter)
    {
        $objWriter->startElement('workbookPr');

        if (Date::getExcelCalendar() == Date::CALENDAR_MAC_1904) {
            $objWriter->writeAttribute('date1904', '1');
        }

        $objWriter->writeAttribute('codeName', 'ThisWorkbook');

        $objWriter->endElement();
    }

    /**
     * Write BookViews.
     *
     * @param XMLWriter $objWriter XML Writer
     * @param Spreadsheet $spreadsheet
     */
    private function writeBookViews(XMLWriter $objWriter, Spreadsheet $spreadsheet)
    {
        // bookViews
        $objWriter->startElement('bookViews');

        // workbookView
        $objWriter->startElement('workbookView');

        $objWriter->writeAttribute('activeTab', $spreadsheet->getActiveSheetIndex());
        $objWriter->writeAttribute('autoFilterDateGrouping', ($spreadsheet->getAutoFilterDateGrouping() ? 'true' : 'false'));
        $objWriter->writeAttribute('firstSheet', $spreadsheet->getFirstSheetIndex());
        $objWriter->writeAttribute('minimized', ($spreadsheet->getMinimized() ? 'true' : 'false'));
        $objWriter->writeAttribute('showHorizontalScroll', ($spreadsheet->getShowHorizontalScroll() ? 'true' : 'false'));
        $objWriter->writeAttribute('showSheetTabs', ($spreadsheet->getShowSheetTabs() ? 'true' : 'false'));
        $objWriter->writeAttribute('showVerticalScroll', ($spreadsheet->getShowVerticalScroll() ? 'true' : 'false'));
        $objWriter->writeAttribute('tabRatio', $spreadsheet->getTabRatio());
        $objWriter->writeAttribute('visibility', $spreadsheet->getVisibility());

        $objWriter->endElement();

        $objWriter->endElement();
    }

    /**
     * Write WorkbookProtection.
     *
     * @param XMLWriter $objWriter XML Writer
     * @param Spreadsheet $spreadsheet
     */
    private function writeWorkbookProtection(XMLWriter $objWriter, Spreadsheet $spreadsheet)
    {
        if ($spreadsheet->getSecurity()->isSecurityEnabled()) {
            $objWriter->startElement('workbookProtection');
            $objWriter->writeAttribute('lockRevision', ($spreadsheet->getSecurity()->getLockRevision() ? 'true' : 'false'));
            $objWriter->writeAttribute('lockStructure', ($spreadsheet->getSecurity()->getLockStructure() ? 'true' : 'false'));
            $objWriter->writeAttribute('lockWindows', ($spreadsheet->getSecurity()->getLockWindows() ? 'true' : 'false'));

            if ($spreadsheet->getSecurity()->getRevisionsPassword() != '') {
                $objWriter->writeAttribute('revisionsPassword', $spreadsheet->getSecurity()->getRevisionsPassword());
            }

            if ($spreadsheet->getSecurity()->getWorkbookPassword() != '') {
                $objWriter->writeAttribute('workbookPassword', $spreadsheet->getSecurity()->getWorkbookPassword());
            }

            $objWriter->endElement();
        }
    }

    /**
     * Write calcPr.
     *
     * @param XMLWriter $objWriter XML Writer
     * @param bool $recalcRequired Indicate whether formulas should be recalculated before writing
     */
    private function writeCalcPr(XMLWriter $objWriter, $recalcRequired = true)
    {
        $objWriter->startElement('calcPr');

        //    Set the calcid to a higher value than Excel itself will use, otherwise Excel will always recalc
        //  If MS Excel does do a recalc, then users opening a file in MS Excel will be prompted to save on exit
        //     because the file has changed
        $objWriter->writeAttribute('calcId', '999999');
        $objWriter->writeAttribute('calcMode', 'auto');
        //    fullCalcOnLoad isn't needed if we've recalculating for the save
        $objWriter->writeAttribute('calcCompleted', ($recalcRequired) ? 1 : 0);
        $objWriter->writeAttribute('fullCalcOnLoad', ($recalcRequired) ? 0 : 1);
        $objWriter->writeAttribute('forceFullCalc', ($recalcRequired) ? 0 : 1);

        $objWriter->endElement();
    }

    /**
     * Write sheets.
     *
     * @param XMLWriter $objWriter XML Writer
     * @param Spreadsheet $spreadsheet
     *
     * @throws WriterException
     */
    private function writeSheets(XMLWriter $objWriter, Spreadsheet $spreadsheet)
    {
        // Write sheets
        $objWriter->startElement('sheets');
        $sheetCount = $spreadsheet->getSheetCount();
        for ($i = 0; $i < $sheetCount; ++$i) {
            // sheet
            $this->writeSheet(
                $objWriter,
                $spreadsheet->getSheet($i)->getTitle(),
                ($i + 1),
                ($i + 1 + 3),
                $spreadsheet->getSheet($i)->getSheetState()
            );
        }

        $objWriter->endElement();
    }

    /**
     * Write sheet.
     *
     * @param XMLWriter $objWriter XML Writer
     * @param string $pSheetname Sheet name
     * @param int $pSheetId Sheet id
     * @param int $pRelId Relationship ID
     * @param string $sheetState Sheet state (visible, hidden, veryHidden)
     *
     * @throws WriterException
     */
    private function writeSheet(XMLWriter $objWriter, $pSheetname, $pSheetId = 1, $pRelId = 1, $sheetState = 'visible')
    {
        if ($pSheetname != '') {
            // Write sheet
            $objWriter->startElement('sheet');
            $objWriter->writeAttribute('name', $pSheetname);
            $objWriter->writeAttribute('sheetId', $pSheetId);
            if ($sheetState != 'visible' && $sheetState != '') {
                $objWriter->writeAttribute('state', $sheetState);
            }
            $objWriter->writeAttribute('r:id', 'rId' . $pRelId);
            $objWriter->endElement();
        } else {
            throw new WriterException('Invalid parameters passed.');
        }
    }

    /**
     * Write Defined Names.
     *
     * @param XMLWriter $objWriter XML Writer
     * @param Spreadsheet $spreadsheet
     *
     * @throws WriterException
     */
    private function writeDefinedNames(XMLWriter $objWriter, Spreadsheet $spreadsheet)
    {
        // Write defined names
        $objWriter->startElement('definedNames');

        // Named ranges
        if (count($spreadsheet->getNamedRanges()) > 0) {
            // Named ranges
            $this->writeNamedRanges($objWriter, $spreadsheet);
        }

        // Other defined names
        $sheetCount = $spreadsheet->getSheetCount();
        for ($i = 0; $i < $sheetCount; ++$i) {
            // definedName for autoFilter
            $this->writeDefinedNameForAutofilter($objWriter, $spreadsheet->getSheet($i), $i);

            // definedName for Print_Titles
            $this->writeDefinedNameForPrintTitles($objWriter, $spreadsheet->getSheet($i), $i);

            // definedName for Print_Area
            $this->writeDefinedNameForPrintArea($objWriter, $spreadsheet->getSheet($i), $i);
        }

        $objWriter->endElement();
    }

    /**
     * Write named ranges.
     *
     * @param XMLWriter $objWriter XML Writer
     * @param Spreadsheet $spreadsheet
     *
     * @throws WriterException
     */
    private function writeNamedRanges(XMLWriter $objWriter, Spreadsheet $spreadsheet)
    {
        // Loop named ranges
        $namedRanges = $spreadsheet->getNamedRanges();
        foreach ($namedRanges as $namedRange) {
            $this->writeDefinedNameForNamedRange($objWriter, $namedRange);
        }
    }

    /**
     * Write Defined Name for named range.
     *
     * @param XMLWriter $objWriter XML Writer
     * @param NamedRange $pNamedRange
     */
    private function writeDefinedNameForNamedRange(XMLWriter $objWriter, NamedRange $pNamedRange)
    {
        // definedName for named range
        $objWriter->startElement('definedName');
        $objWriter->writeAttribute('name', $pNamedRange->getName());
        if ($pNamedRange->getLocalOnly()) {
            $objWriter->writeAttribute('localSheetId', $pNamedRange->getScope()->getParent()->getIndex($pNamedRange->getScope()));
        }

        // Create absolute coordinate and write as raw text
        $range = Coordinate::splitRange($pNamedRange->getRange());
        $iMax = count($range);
        for ($i = 0; $i < $iMax; ++$i) {
            $range[$i][0] = '\'' . str_replace("'", "''", $pNamedRange->getWorksheet()->getTitle()) . '\'!' . Coordinate::absoluteReference($range[$i][0]);
            if (isset($range[$i][1])) {
                $range[$i][1] = Coordinate::absoluteReference($range[$i][1]);
            }
        }
        $range = Coordinate::buildRange($range);

        $objWriter->writeRawData($range);

        $objWriter->endElement();
    }

    /**
     * Write Defined Name for autoFilter.
     *
     * @param XMLWriter $objWriter XML Writer
     * @param Worksheet $pSheet
     * @param int $pSheetId
     */
    private function writeDefinedNameForAutofilter(XMLWriter $objWriter, Worksheet $pSheet, $pSheetId = 0)
    {
        // definedName for autoFilter
        $autoFilterRange = $pSheet->getAutoFilter()->getRange();
        if (!empty($autoFilterRange)) {
            $objWriter->startElement('definedName');
            $objWriter->writeAttribute('name', '_xlnm._FilterDatabase');
            $objWriter->writeAttribute('localSheetId', $pSheetId);
            $objWriter->writeAttribute('hidden', '1');

            // Create absolute coordinate and write as raw text
            $range = Coordinate::splitRange($autoFilterRange);
            $range = $range[0];
            //    Strip any worksheet ref so we can make the cell ref absolute
            list($ws, $range[0]) = Worksheet::extractSheetTitle($range[0], true);

            $range[0] = Coordinate::absoluteCoordinate($range[0]);
            $range[1] = Coordinate::absoluteCoordinate($range[1]);
            $range = implode(':', $range);

            $objWriter->writeRawData('\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . $range);

            $objWriter->endElement();
        }
    }

    /**
     * Write Defined Name for PrintTitles.
     *
     * @param XMLWriter $objWriter XML Writer
     * @param Worksheet $pSheet
     * @param int $pSheetId
     */
    private function writeDefinedNameForPrintTitles(XMLWriter $objWriter, Worksheet $pSheet, $pSheetId = 0)
    {
        // definedName for PrintTitles
        if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet() || $pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
            $objWriter->startElement('definedName');
            $objWriter->writeAttribute('name', '_xlnm.Print_Titles');
            $objWriter->writeAttribute('localSheetId', $pSheetId);

            // Setting string
            $settingString = '';

            // Columns to repeat
            if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
                $repeat = $pSheet->getPageSetup()->getColumnsToRepeatAtLeft();

                $settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
            }

            // Rows to repeat
            if ($pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
                if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
                    $settingString .= ',';
                }

                $repeat = $pSheet->getPageSetup()->getRowsToRepeatAtTop();

                $settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
            }

            $objWriter->writeRawData($settingString);

            $objWriter->endElement();
        }
    }

    /**
     * Write Defined Name for PrintTitles.
     *
     * @param XMLWriter $objWriter XML Writer
     * @param Worksheet $pSheet
     * @param int $pSheetId
     */
    private function writeDefinedNameForPrintArea(XMLWriter $objWriter, Worksheet $pSheet, $pSheetId = 0)
    {
        // definedName for PrintArea
        if ($pSheet->getPageSetup()->isPrintAreaSet()) {
            $objWriter->startElement('definedName');
            $objWriter->writeAttribute('name', '_xlnm.Print_Area');
            $objWriter->writeAttribute('localSheetId', $pSheetId);

            // Print area
            $printArea = Coordinate::splitRange($pSheet->getPageSetup()->getPrintArea());

            $chunks = [];
            foreach ($printArea as $printAreaRect) {
                $printAreaRect[0] = Coordinate::absoluteReference($printAreaRect[0]);
                $printAreaRect[1] = Coordinate::absoluteReference($printAreaRect[1]);
                $chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
            }

            $objWriter->writeRawData(implode(',', $chunks));

            $objWriter->endElement();
        }
    }
}


1		<?php
2
3		namespace PhpOffice\PhpSpreadsheet\Writer\Xlsx;
4
5		use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
6		use PhpOffice\PhpSpreadsheet\NamedRange;
7		use PhpOffice\PhpSpreadsheet\Shared\Date;
8		use PhpOffice\PhpSpreadsheet\Shared\XMLWriter;
9		use PhpOffice\PhpSpreadsheet\Spreadsheet;
10		use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
		0 ignored issues – show Bug introduced 2017-12-03 09:09 UTC by Report Bug Copy Issue Report This use statement conflicts with another class in this namespace, `PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet`. Consider defining an alias. Let?s assume that you have a directory layout like this: . \|-- OtherDir \| \|-- Bar.php \| `-- Foo.php `-- SomeDir `-- Foo.php and let?s assume the following content of `Bar.php`: // Bar.php namespace OtherDir; use SomeDir\Foo; // This now conflicts the class OtherDir\Foo If both files `OtherDir/Foo.php` and `SomeDir/Foo.php` are loaded in the same runtime, you will see a PHP error such as the following: PHP Fatal error: Cannot use SomeDir\Foo as Foo because the name is already in use in OtherDir/Foo.php However, as `OtherDir/Foo.php` does not necessarily have to be loaded and the error is only triggered if it is loaded before `OtherDir/Bar.php`, this problem might go unnoticed for a while. In order to prevent this error from surfacing, you must import the namespace with a different alias: // Bar.php namespace OtherDir; use SomeDir\Foo as SomeDirFoo; // There is no conflict anymore. Loading history...
11		use PhpOffice\PhpSpreadsheet\Writer\Exception as WriterException;
12
13		class Workbook extends WriterPart
14		{
15		/**
16		* Write workbook to XML format.
17		*
18		* @param Spreadsheet $spreadsheet
19		* @param bool $recalcRequired Indicate whether formulas should be recalculated before writing
20		*
21		* @throws WriterException
22		*
23		* @return string XML Output
24		*/
25	82	public function writeWorkbook(Spreadsheet $spreadsheet, $recalcRequired = false)
26		{
27		// Create XML writer
28	82	if ($this->getParentWriter()->getUseDiskCaching()) {
29		$objWriter = new XMLWriter(XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory());
30		} else {
31	82	$objWriter = new XMLWriter(XMLWriter::STORAGE_MEMORY);
32		}
33
34		// XML header
35	82	$objWriter->startDocument('1.0', 'UTF-8', 'yes');
36
37		// workbook
38	82	$objWriter->startElement('workbook');
39	82	$objWriter->writeAttribute('xml:space', 'preserve');
40	82	$objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
41	82	$objWriter->writeAttribute('xmlns:r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships');
42
43		// fileVersion
44	82	$this->writeFileVersion($objWriter);
45
46		// workbookPr
47	82	$this->writeWorkbookPr($objWriter);
48
49		// workbookProtection
50	82	$this->writeWorkbookProtection($objWriter, $spreadsheet);
51
52		// bookViews
53	82	if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
54	82	$this->writeBookViews($objWriter, $spreadsheet);
55		}
56
57		// sheets
58	82	$this->writeSheets($objWriter, $spreadsheet);
59
60		// definedNames
61	82	$this->writeDefinedNames($objWriter, $spreadsheet);
62
63		// calcPr
64	82	$this->writeCalcPr($objWriter, $recalcRequired);
65
66	82	$objWriter->endElement();
67
68		// Return
69	82	return $objWriter->getData();
70		}
71
72		/**
73		* Write file version.
74		*
75		* @param XMLWriter $objWriter XML Writer
76		*/
77	82	private function writeFileVersion(XMLWriter $objWriter)
78		{
79	82	$objWriter->startElement('fileVersion');
80	82	$objWriter->writeAttribute('appName', 'xl');
81	82	$objWriter->writeAttribute('lastEdited', '4');
82	82	$objWriter->writeAttribute('lowestEdited', '4');
83	82	$objWriter->writeAttribute('rupBuild', '4505');
84	82	$objWriter->endElement();
85	82	}
86
87		/**
88		* Write WorkbookPr.
89		*
90		* @param XMLWriter $objWriter XML Writer
91		*/
92	82	private function writeWorkbookPr(XMLWriter $objWriter)
93		{
94	82	$objWriter->startElement('workbookPr');
95
96	82	if (Date::getExcelCalendar() == Date::CALENDAR_MAC_1904) {
97		$objWriter->writeAttribute('date1904', '1');
98		}
99
100	82	$objWriter->writeAttribute('codeName', 'ThisWorkbook');
101
102	82	$objWriter->endElement();
103	82	}
104
105		/**
106		* Write BookViews.
107		*
108		* @param XMLWriter $objWriter XML Writer
109		* @param Spreadsheet $spreadsheet
110		*/
111	82	private function writeBookViews(XMLWriter $objWriter, Spreadsheet $spreadsheet)
112		{
113		// bookViews
114	82	$objWriter->startElement('bookViews');
115
116		// workbookView
117	82	$objWriter->startElement('workbookView');
118
119	82	$objWriter->writeAttribute('activeTab', $spreadsheet->getActiveSheetIndex());
120	82	$objWriter->writeAttribute('autoFilterDateGrouping', ($spreadsheet->getAutoFilterDateGrouping() ? 'true' : 'false'));
121	82	$objWriter->writeAttribute('firstSheet', $spreadsheet->getFirstSheetIndex());
122	82	$objWriter->writeAttribute('minimized', ($spreadsheet->getMinimized() ? 'true' : 'false'));
123	82	$objWriter->writeAttribute('showHorizontalScroll', ($spreadsheet->getShowHorizontalScroll() ? 'true' : 'false'));
124	82	$objWriter->writeAttribute('showSheetTabs', ($spreadsheet->getShowSheetTabs() ? 'true' : 'false'));
125	82	$objWriter->writeAttribute('showVerticalScroll', ($spreadsheet->getShowVerticalScroll() ? 'true' : 'false'));
126	82	$objWriter->writeAttribute('tabRatio', $spreadsheet->getTabRatio());
127	82	$objWriter->writeAttribute('visibility', $spreadsheet->getVisibility());
128
129	82	$objWriter->endElement();
130
131	82	$objWriter->endElement();
132	82	}
133
134		/**
135		* Write WorkbookProtection.
136		*
137		* @param XMLWriter $objWriter XML Writer
138		* @param Spreadsheet $spreadsheet
139		*/
140	82	private function writeWorkbookProtection(XMLWriter $objWriter, Spreadsheet $spreadsheet)
141		{
142	82	if ($spreadsheet->getSecurity()->isSecurityEnabled()) {
143	3	$objWriter->startElement('workbookProtection');
144	3	$objWriter->writeAttribute('lockRevision', ($spreadsheet->getSecurity()->getLockRevision() ? 'true' : 'false'));
145	3	$objWriter->writeAttribute('lockStructure', ($spreadsheet->getSecurity()->getLockStructure() ? 'true' : 'false'));
146	3	$objWriter->writeAttribute('lockWindows', ($spreadsheet->getSecurity()->getLockWindows() ? 'true' : 'false'));
147
148	3	if ($spreadsheet->getSecurity()->getRevisionsPassword() != '') {
149		$objWriter->writeAttribute('revisionsPassword', $spreadsheet->getSecurity()->getRevisionsPassword());
150		}
151
152	3	if ($spreadsheet->getSecurity()->getWorkbookPassword() != '') {
153	3	$objWriter->writeAttribute('workbookPassword', $spreadsheet->getSecurity()->getWorkbookPassword());
154		}
155
156	3	$objWriter->endElement();
157		}
158	82	}
159
160		/**
161		* Write calcPr.
162		*
163		* @param XMLWriter $objWriter XML Writer
164		* @param bool $recalcRequired Indicate whether formulas should be recalculated before writing
165		*/
166	82	private function writeCalcPr(XMLWriter $objWriter, $recalcRequired = true)
167		{
168	82	$objWriter->startElement('calcPr');
169
170		// Set the calcid to a higher value than Excel itself will use, otherwise Excel will always recalc
171		// If MS Excel does do a recalc, then users opening a file in MS Excel will be prompted to save on exit
172		// because the file has changed
173	82	$objWriter->writeAttribute('calcId', '999999');
174	82	$objWriter->writeAttribute('calcMode', 'auto');
175		// fullCalcOnLoad isn't needed if we've recalculating for the save
176	82	$objWriter->writeAttribute('calcCompleted', ($recalcRequired) ? 1 : 0);
177	82	$objWriter->writeAttribute('fullCalcOnLoad', ($recalcRequired) ? 0 : 1);
178	82	$objWriter->writeAttribute('forceFullCalc', ($recalcRequired) ? 0 : 1);
179
180	82	$objWriter->endElement();
181	82	}
182
183		/**
184		* Write sheets.
185		*
186		* @param XMLWriter $objWriter XML Writer
187		* @param Spreadsheet $spreadsheet
188		*
189		* @throws WriterException
190		*/
191	82	private function writeSheets(XMLWriter $objWriter, Spreadsheet $spreadsheet)
192		{
193		// Write sheets
194	82	$objWriter->startElement('sheets');
195	82	$sheetCount = $spreadsheet->getSheetCount();
196	82	for ($i = 0; $i < $sheetCount; ++$i) {
197		// sheet
198	82	$this->writeSheet(
199	82	$objWriter,
200	82	$spreadsheet->getSheet($i)->getTitle(),
201	82	($i + 1),
202	82	($i + 1 + 3),
203	82	$spreadsheet->getSheet($i)->getSheetState()
204		);
205		}
206
207	82	$objWriter->endElement();
208	82	}
209
210		/**
211		* Write sheet.
212		*
213		* @param XMLWriter $objWriter XML Writer
214		* @param string $pSheetname Sheet name
215		* @param int $pSheetId Sheet id
216		* @param int $pRelId Relationship ID
217		* @param string $sheetState Sheet state (visible, hidden, veryHidden)
218		*
219		* @throws WriterException
220		*/
221	82	private function writeSheet(XMLWriter $objWriter, $pSheetname, $pSheetId = 1, $pRelId = 1, $sheetState = 'visible')
222		{
223	82	if ($pSheetname != '') {
224		// Write sheet
225	82	$objWriter->startElement('sheet');
226	82	$objWriter->writeAttribute('name', $pSheetname);
227	82	$objWriter->writeAttribute('sheetId', $pSheetId);
228	82	if ($sheetState != 'visible' && $sheetState != '') {
229	1	$objWriter->writeAttribute('state', $sheetState);
230		}
231	82	$objWriter->writeAttribute('r:id', 'rId' . $pRelId);
232	82	$objWriter->endElement();
233		} else {
234		throw new WriterException('Invalid parameters passed.');
235		}
236	82	}
237
238		/**
239		* Write Defined Names.
240		*
241		* @param XMLWriter $objWriter XML Writer
242		* @param Spreadsheet $spreadsheet
243		*
244		* @throws WriterException
245		*/
246	82	private function writeDefinedNames(XMLWriter $objWriter, Spreadsheet $spreadsheet)
247		{
248		// Write defined names
249	82	$objWriter->startElement('definedNames');
250
251		// Named ranges
252	82	if (count($spreadsheet->getNamedRanges()) > 0) {
253		// Named ranges
254	4	$this->writeNamedRanges($objWriter, $spreadsheet);
255		}
256
257		// Other defined names
258	82	$sheetCount = $spreadsheet->getSheetCount();
259	82	for ($i = 0; $i < $sheetCount; ++$i) {
260		// definedName for autoFilter
261	82	$this->writeDefinedNameForAutofilter($objWriter, $spreadsheet->getSheet($i), $i);
262
263		// definedName for Print_Titles
264	82	$this->writeDefinedNameForPrintTitles($objWriter, $spreadsheet->getSheet($i), $i);
265
266		// definedName for Print_Area
267	82	$this->writeDefinedNameForPrintArea($objWriter, $spreadsheet->getSheet($i), $i);
268		}
269
270	82	$objWriter->endElement();
271	82	}
272
273		/**
274		* Write named ranges.
275		*
276		* @param XMLWriter $objWriter XML Writer
277		* @param Spreadsheet $spreadsheet
278		*
279		* @throws WriterException
280		*/
281	4	private function writeNamedRanges(XMLWriter $objWriter, Spreadsheet $spreadsheet)
282		{
283		// Loop named ranges
284	4	$namedRanges = $spreadsheet->getNamedRanges();
285	4	foreach ($namedRanges as $namedRange) {
286	4	$this->writeDefinedNameForNamedRange($objWriter, $namedRange);
287		}
288	4	}
289
290		/**
291		* Write Defined Name for named range.
292		*
293		* @param XMLWriter $objWriter XML Writer
294		* @param NamedRange $pNamedRange
295		*/
296	4	private function writeDefinedNameForNamedRange(XMLWriter $objWriter, NamedRange $pNamedRange)
297		{
298		// definedName for named range
299	4	$objWriter->startElement('definedName');
300	4	$objWriter->writeAttribute('name', $pNamedRange->getName());
301	4	if ($pNamedRange->getLocalOnly()) {
302		$objWriter->writeAttribute('localSheetId', $pNamedRange->getScope()->getParent()->getIndex($pNamedRange->getScope()));
303		}
304
305		// Create absolute coordinate and write as raw text
306	4	$range = Coordinate::splitRange($pNamedRange->getRange());
307	4	$iMax = count($range);
308	4	for ($i = 0; $i < $iMax; ++$i) {
309	4	$range[$i][0] = '\'' . str_replace("'", "''", $pNamedRange->getWorksheet()->getTitle()) . '\'!' . Coordinate::absoluteReference($range[$i][0]);
310	4	if (isset($range[$i][1])) {
311	3	$range[$i][1] = Coordinate::absoluteReference($range[$i][1]);
312		}
313		}
314	4	$range = Coordinate::buildRange($range);
315
316	4	$objWriter->writeRawData($range);
317
318	4	$objWriter->endElement();
319	4	}
320
321		/**
322		* Write Defined Name for autoFilter.
323		*
324		* @param XMLWriter $objWriter XML Writer
325		* @param Worksheet $pSheet
326		* @param int $pSheetId
327		*/
328	82	private function writeDefinedNameForAutofilter(XMLWriter $objWriter, Worksheet $pSheet, $pSheetId = 0)
329		{
330		// definedName for autoFilter
331	82	$autoFilterRange = $pSheet->getAutoFilter()->getRange();
332	82	if (!empty($autoFilterRange)) {
333	3	$objWriter->startElement('definedName');
334	3	$objWriter->writeAttribute('name', '_xlnm._FilterDatabase');
335	3	$objWriter->writeAttribute('localSheetId', $pSheetId);
336	3	$objWriter->writeAttribute('hidden', '1');
337
338		// Create absolute coordinate and write as raw text
339	3	$range = Coordinate::splitRange($autoFilterRange);
340	3	$range = $range[0];
341		// Strip any worksheet ref so we can make the cell ref absolute
342	3	list($ws, $range[0]) = Worksheet::extractSheetTitle($range[0], true);
343
344	3	$range[0] = Coordinate::absoluteCoordinate($range[0]);
345	3	$range[1] = Coordinate::absoluteCoordinate($range[1]);
346	3	$range = implode(':', $range);
347
348	3	$objWriter->writeRawData('\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . $range);
349
350	3	$objWriter->endElement();
351		}
352	82	}
353
354		/**
355		* Write Defined Name for PrintTitles.
356		*
357		* @param XMLWriter $objWriter XML Writer
358		* @param Worksheet $pSheet
359		* @param int $pSheetId
360		*/
361	82	private function writeDefinedNameForPrintTitles(XMLWriter $objWriter, Worksheet $pSheet, $pSheetId = 0)
362		{
363		// definedName for PrintTitles
364	82	if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet() \|\| $pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
365	1	$objWriter->startElement('definedName');
366	1	$objWriter->writeAttribute('name', '_xlnm.Print_Titles');
367	1	$objWriter->writeAttribute('localSheetId', $pSheetId);
368
369		// Setting string
370	1	$settingString = '';
371
372		// Columns to repeat
373	1	if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
374		$repeat = $pSheet->getPageSetup()->getColumnsToRepeatAtLeft();
375
376		$settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
377		}
378
379		// Rows to repeat
380	1	if ($pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
381	1	if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
382		$settingString .= ',';
383		}
384
385	1	$repeat = $pSheet->getPageSetup()->getRowsToRepeatAtTop();
386
387	1	$settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
388		}
389
390	1	$objWriter->writeRawData($settingString);
391
392	1	$objWriter->endElement();
393		}
394	82	}
395
396		/**
397		* Write Defined Name for PrintTitles.
398		*
399		* @param XMLWriter $objWriter XML Writer
400		* @param Worksheet $pSheet
401		* @param int $pSheetId
402		*/
403	82	private function writeDefinedNameForPrintArea(XMLWriter $objWriter, Worksheet $pSheet, $pSheetId = 0)
404		{
405		// definedName for PrintArea
406	82	if ($pSheet->getPageSetup()->isPrintAreaSet()) {
407	1	$objWriter->startElement('definedName');
408	1	$objWriter->writeAttribute('name', '_xlnm.Print_Area');
409	1	$objWriter->writeAttribute('localSheetId', $pSheetId);
410
411		// Print area
412	1	$printArea = Coordinate::splitRange($pSheet->getPageSetup()->getPrintArea());
413
414	1	$chunks = [];
415	1	foreach ($printArea as $printAreaRect) {
416	1	$printAreaRect[0] = Coordinate::absoluteReference($printAreaRect[0]);
417	1	$printAreaRect[1] = Coordinate::absoluteReference($printAreaRect[1]);
418	1	$chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
419		}
420
421	1	$objWriter->writeRawData(implode(',', $chunks));
422
423	1	$objWriter->endElement();
424		}
425	82	}
426		}
427

PHPOffice / PhpSpreadsheet

Push — develop ( 57404f...50a9bc )

Workbook::writeCalcPr() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like