1<?php
2
3namespace PhpOffice\PhpSpreadsheet\Writer\Xlsx;
4
5use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
6use PhpOffice\PhpSpreadsheet\NamedRange;
7use PhpOffice\PhpSpreadsheet\Shared\Date;
8use PhpOffice\PhpSpreadsheet\Shared\XMLWriter;
9use PhpOffice\PhpSpreadsheet\Spreadsheet;
10use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
11use PhpOffice\PhpSpreadsheet\Writer\Exception as WriterException;
12
13class 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    public function writeWorkbook(Spreadsheet $spreadsheet, $recalcRequired = false)
26    {
27        // Create XML writer
28        if ($this->getParentWriter()->getUseDiskCaching()) {
29            $objWriter = new XMLWriter(XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory());
30        } else {
31            $objWriter = new XMLWriter(XMLWriter::STORAGE_MEMORY);
32        }
33
34        // XML header
35        $objWriter->startDocument('1.0', 'UTF-8', 'yes');
36
37        // workbook
38        $objWriter->startElement('workbook');
39        $objWriter->writeAttribute('xml:space', 'preserve');
40        $objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
41        $objWriter->writeAttribute('xmlns:r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships');
42
43        // fileVersion
44        $this->writeFileVersion($objWriter);
45
46        // workbookPr
47        $this->writeWorkbookPr($objWriter);
48
49        // workbookProtection
50        $this->writeWorkbookProtection($objWriter, $spreadsheet);
51
52        // bookViews
53        if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
54            $this->writeBookViews($objWriter, $spreadsheet);
55        }
56
57        // sheets
58        $this->writeSheets($objWriter, $spreadsheet);
59
60        // definedNames
61        $this->writeDefinedNames($objWriter, $spreadsheet);
62
63        // calcPr
64        $this->writeCalcPr($objWriter, $recalcRequired);
65
66        $objWriter->endElement();
67
68        // Return
69        return $objWriter->getData();
70    }
71
72    /**
73     * Write file version.
74     *
75     * @param XMLWriter $objWriter XML Writer
76     */
77    private function writeFileVersion(XMLWriter $objWriter)
78    {
79        $objWriter->startElement('fileVersion');
80        $objWriter->writeAttribute('appName', 'xl');
81        $objWriter->writeAttribute('lastEdited', '4');
82        $objWriter->writeAttribute('lowestEdited', '4');
83        $objWriter->writeAttribute('rupBuild', '4505');
84        $objWriter->endElement();
85    }
86
87    /**
88     * Write WorkbookPr.
89     *
90     * @param XMLWriter $objWriter XML Writer
91     */
92    private function writeWorkbookPr(XMLWriter $objWriter)
93    {
94        $objWriter->startElement('workbookPr');
95
96        if (Date::getExcelCalendar() === Date::CALENDAR_MAC_1904) {
97            $objWriter->writeAttribute('date1904', '1');
98        }
99
100        $objWriter->writeAttribute('codeName', 'ThisWorkbook');
101
102        $objWriter->endElement();
103    }
104
105    /**
106     * Write BookViews.
107     *
108     * @param XMLWriter $objWriter XML Writer
109     * @param Spreadsheet $spreadsheet
110     */
111    private function writeBookViews(XMLWriter $objWriter, Spreadsheet $spreadsheet)
112    {
113        // bookViews
114        $objWriter->startElement('bookViews');
115
116        // workbookView
117        $objWriter->startElement('workbookView');
118
119        $objWriter->writeAttribute('activeTab', $spreadsheet->getActiveSheetIndex());
120        $objWriter->writeAttribute('autoFilterDateGrouping', ($spreadsheet->getAutoFilterDateGrouping() ? 'true' : 'false'));
121        $objWriter->writeAttribute('firstSheet', $spreadsheet->getFirstSheetIndex());
122        $objWriter->writeAttribute('minimized', ($spreadsheet->getMinimized() ? 'true' : 'false'));
123        $objWriter->writeAttribute('showHorizontalScroll', ($spreadsheet->getShowHorizontalScroll() ? 'true' : 'false'));
124        $objWriter->writeAttribute('showSheetTabs', ($spreadsheet->getShowSheetTabs() ? 'true' : 'false'));
125        $objWriter->writeAttribute('showVerticalScroll', ($spreadsheet->getShowVerticalScroll() ? 'true' : 'false'));
126        $objWriter->writeAttribute('tabRatio', $spreadsheet->getTabRatio());
127        $objWriter->writeAttribute('visibility', $spreadsheet->getVisibility());
128
129        $objWriter->endElement();
130
131        $objWriter->endElement();
132    }
133
134    /**
135     * Write WorkbookProtection.
136     *
137     * @param XMLWriter $objWriter XML Writer
138     * @param Spreadsheet $spreadsheet
139     */
140    private function writeWorkbookProtection(XMLWriter $objWriter, Spreadsheet $spreadsheet)
141    {
142        if ($spreadsheet->getSecurity()->isSecurityEnabled()) {
143            $objWriter->startElement('workbookProtection');
144            $objWriter->writeAttribute('lockRevision', ($spreadsheet->getSecurity()->getLockRevision() ? 'true' : 'false'));
145            $objWriter->writeAttribute('lockStructure', ($spreadsheet->getSecurity()->getLockStructure() ? 'true' : 'false'));
146            $objWriter->writeAttribute('lockWindows', ($spreadsheet->getSecurity()->getLockWindows() ? 'true' : 'false'));
147
148            if ($spreadsheet->getSecurity()->getRevisionsPassword() != '') {
149                $objWriter->writeAttribute('revisionsPassword', $spreadsheet->getSecurity()->getRevisionsPassword());
150            }
151
152            if ($spreadsheet->getSecurity()->getWorkbookPassword() != '') {
153                $objWriter->writeAttribute('workbookPassword', $spreadsheet->getSecurity()->getWorkbookPassword());
154            }
155
156            $objWriter->endElement();
157        }
158    }
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    private function writeCalcPr(XMLWriter $objWriter, $recalcRequired = true)
167    {
168        $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        $objWriter->writeAttribute('calcId', '999999');
174        $objWriter->writeAttribute('calcMode', 'auto');
175        //    fullCalcOnLoad isn't needed if we've recalculating for the save
176        $objWriter->writeAttribute('calcCompleted', ($recalcRequired) ? 1 : 0);
177        $objWriter->writeAttribute('fullCalcOnLoad', ($recalcRequired) ? 0 : 1);
178        $objWriter->writeAttribute('forceFullCalc', ($recalcRequired) ? 0 : 1);
179
180        $objWriter->endElement();
181    }
182
183    /**
184     * Write sheets.
185     *
186     * @param XMLWriter $objWriter XML Writer
187     * @param Spreadsheet $spreadsheet
188     *
189     * @throws WriterException
190     */
191    private function writeSheets(XMLWriter $objWriter, Spreadsheet $spreadsheet)
192    {
193        // Write sheets
194        $objWriter->startElement('sheets');
195        $sheetCount = $spreadsheet->getSheetCount();
196        for ($i = 0; $i < $sheetCount; ++$i) {
197            // sheet
198            $this->writeSheet(
199                $objWriter,
200                $spreadsheet->getSheet($i)->getTitle(),
201                ($i + 1),
202                ($i + 1 + 3),
203                $spreadsheet->getSheet($i)->getSheetState()
204            );
205        }
206
207        $objWriter->endElement();
208    }
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    private function writeSheet(XMLWriter $objWriter, $pSheetname, $pSheetId = 1, $pRelId = 1, $sheetState = 'visible')
222    {
223        if ($pSheetname != '') {
224            // Write sheet
225            $objWriter->startElement('sheet');
226            $objWriter->writeAttribute('name', $pSheetname);
227            $objWriter->writeAttribute('sheetId', $pSheetId);
228            if ($sheetState !== 'visible' && $sheetState != '') {
229                $objWriter->writeAttribute('state', $sheetState);
230            }
231            $objWriter->writeAttribute('r:id', 'rId' . $pRelId);
232            $objWriter->endElement();
233        } else {
234            throw new WriterException('Invalid parameters passed.');
235        }
236    }
237
238    /**
239     * Write Defined Names.
240     *
241     * @param XMLWriter $objWriter XML Writer
242     * @param Spreadsheet $spreadsheet
243     *
244     * @throws WriterException
245     */
246    private function writeDefinedNames(XMLWriter $objWriter, Spreadsheet $spreadsheet)
247    {
248        // Write defined names
249        $objWriter->startElement('definedNames');
250
251        // Named ranges
252        if (count($spreadsheet->getNamedRanges()) > 0) {
253            // Named ranges
254            $this->writeNamedRanges($objWriter, $spreadsheet);
255        }
256
257        // Other defined names
258        $sheetCount = $spreadsheet->getSheetCount();
259        for ($i = 0; $i < $sheetCount; ++$i) {
260            // definedName for autoFilter
261            $this->writeDefinedNameForAutofilter($objWriter, $spreadsheet->getSheet($i), $i);
262
263            // definedName for Print_Titles
264            $this->writeDefinedNameForPrintTitles($objWriter, $spreadsheet->getSheet($i), $i);
265
266            // definedName for Print_Area
267            $this->writeDefinedNameForPrintArea($objWriter, $spreadsheet->getSheet($i), $i);
268        }
269
270        $objWriter->endElement();
271    }
272
273    /**
274     * Write named ranges.
275     *
276     * @param XMLWriter $objWriter XML Writer
277     * @param Spreadsheet $spreadsheet
278     *
279     * @throws WriterException
280     */
281    private function writeNamedRanges(XMLWriter $objWriter, Spreadsheet $spreadsheet)
282    {
283        // Loop named ranges
284        $namedRanges = $spreadsheet->getNamedRanges();
285        foreach ($namedRanges as $namedRange) {
286            $this->writeDefinedNameForNamedRange($objWriter, $namedRange);
287        }
288    }
289
290    /**
291     * Write Defined Name for named range.
292     *
293     * @param XMLWriter $objWriter XML Writer
294     * @param NamedRange $pNamedRange
295     */
296    private function writeDefinedNameForNamedRange(XMLWriter $objWriter, NamedRange $pNamedRange)
297    {
298        // definedName for named range
299        $objWriter->startElement('definedName');
300        $objWriter->writeAttribute('name', $pNamedRange->getName());
301        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        $range = Coordinate::splitRange($pNamedRange->getRange());
307        $iMax = count($range);
308        for ($i = 0; $i < $iMax; ++$i) {
309            $range[$i][0] = '\'' . str_replace("'", "''", $pNamedRange->getWorksheet()->getTitle()) . '\'!' . Coordinate::absoluteReference($range[$i][0]);
310            if (isset($range[$i][1])) {
311                $range[$i][1] = Coordinate::absoluteReference($range[$i][1]);
312            }
313        }
314        $range = Coordinate::buildRange($range);
315
316        $objWriter->writeRawData($range);
317
318        $objWriter->endElement();
319    }
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    private function writeDefinedNameForAutofilter(XMLWriter $objWriter, Worksheet $pSheet, $pSheetId = 0)
329    {
330        // definedName for autoFilter
331        $autoFilterRange = $pSheet->getAutoFilter()->getRange();
332        if (!empty($autoFilterRange)) {
333            $objWriter->startElement('definedName');
334            $objWriter->writeAttribute('name', '_xlnm._FilterDatabase');
335            $objWriter->writeAttribute('localSheetId', $pSheetId);
336            $objWriter->writeAttribute('hidden', '1');
337
338            // Create absolute coordinate and write as raw text
339            $range = Coordinate::splitRange($autoFilterRange);
340            $range = $range[0];
341            //    Strip any worksheet ref so we can make the cell ref absolute
342            [$ws, $range[0]] = Worksheet::extractSheetTitle($range[0], true);
343
344            $range[0] = Coordinate::absoluteCoordinate($range[0]);
345            $range[1] = Coordinate::absoluteCoordinate($range[1]);
346            $range = implode(':', $range);
347
348            $objWriter->writeRawData('\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . $range);
349
350            $objWriter->endElement();
351        }
352    }
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    private function writeDefinedNameForPrintTitles(XMLWriter $objWriter, Worksheet $pSheet, $pSheetId = 0)
362    {
363        // definedName for PrintTitles
364        if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet() || $pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
365            $objWriter->startElement('definedName');
366            $objWriter->writeAttribute('name', '_xlnm.Print_Titles');
367            $objWriter->writeAttribute('localSheetId', $pSheetId);
368
369            // Setting string
370            $settingString = '';
371
372            // Columns to repeat
373            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            if ($pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
381                if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
382                    $settingString .= ',';
383                }
384
385                $repeat = $pSheet->getPageSetup()->getRowsToRepeatAtTop();
386
387                $settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
388            }
389
390            $objWriter->writeRawData($settingString);
391
392            $objWriter->endElement();
393        }
394    }
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    private function writeDefinedNameForPrintArea(XMLWriter $objWriter, Worksheet $pSheet, $pSheetId = 0)
404    {
405        // definedName for PrintArea
406        if ($pSheet->getPageSetup()->isPrintAreaSet()) {
407            $objWriter->startElement('definedName');
408            $objWriter->writeAttribute('name', '_xlnm.Print_Area');
409            $objWriter->writeAttribute('localSheetId', $pSheetId);
410
411            // Print area
412            $printArea = Coordinate::splitRange($pSheet->getPageSetup()->getPrintArea());
413
414            $chunks = [];
415            foreach ($printArea as $printAreaRect) {
416                $printAreaRect[0] = Coordinate::absoluteReference($printAreaRect[0]);
417                $printAreaRect[1] = Coordinate::absoluteReference($printAreaRect[1]);
418                $chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
419            }
420
421            $objWriter->writeRawData(implode(',', $chunks));
422
423            $objWriter->endElement();
424        }
425    }
426}
427