Failed Conditions
Push — master ( bf4629...7712d5 )
by Adrien
27:59 queued 18:08
created

Workbook::writeWorkbook()   A

Complexity

Conditions 3
Paths 4

Size

Total Lines 47
Code Lines 26

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 26
CRAP Score 3.0004

Importance

Changes 0
Metric Value
cc 3
eloc 26
nc 4
nop 1
dl 0
loc 47
ccs 26
cts 27
cp 0.963
crap 3.0004
rs 9.504
c 0
b 0
f 0
1
<?php
2
3
namespace PhpOffice\PhpSpreadsheet\Writer\Xls;
4
5
use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
7
use PhpOffice\PhpSpreadsheet\DefinedName;
8
use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
9
use PhpOffice\PhpSpreadsheet\Shared\Date;
10
use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
11
use PhpOffice\PhpSpreadsheet\Spreadsheet;
12
use PhpOffice\PhpSpreadsheet\Style\Style;
13
14
// Original file header of PEAR::Spreadsheet_Excel_Writer_Workbook (used as the base for this class):
15
// -----------------------------------------------------------------------------------------
16
// /*
17
// *  Module written/ported by Xavier Noguer <[email protected]>
18
// *
19
// *  The majority of this is _NOT_ my code.  I simply ported it from the
20
// *  PERL Spreadsheet::WriteExcel module.
21
// *
22
// *  The author of the Spreadsheet::WriteExcel module is John McNamara
23
// *  <[email protected]>
24
// *
25
// *  I _DO_ maintain this code, and John McNamara has nothing to do with the
26
// *  porting of this code to PHP.  Any questions directly related to this
27
// *  class library should be directed to me.
28
// *
29
// *  License Information:
30
// *
31
// *    Spreadsheet_Excel_Writer:  A library for generating Excel Spreadsheets
32
// *    Copyright (c) 2002-2003 Xavier Noguer [email protected]
33
// *
34
// *    This library is free software; you can redistribute it and/or
35
// *    modify it under the terms of the GNU Lesser General Public
36
// *    License as published by the Free Software Foundation; either
37
// *    version 2.1 of the License, or (at your option) any later version.
38
// *
39
// *    This library is distributed in the hope that it will be useful,
40
// *    but WITHOUT ANY WARRANTY; without even the implied warranty of
41
// *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
42
// *    Lesser General Public License for more details.
43
// *
44
// *    You should have received a copy of the GNU Lesser General Public
45
// *    License along with this library; if not, write to the Free Software
46
// *    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
47
// */
48
class Workbook extends BIFFwriter
49
{
50
    /**
51
     * Formula parser.
52
     */
53
    private Parser $parser;
54
55
    /**
56
     * The BIFF file size for the workbook. Not currently used.
57
     *
58
     * @see calcSheetOffsets()
59
     */
60
    private int $biffSize; // @phpstan-ignore-line
61
62
    /**
63
     * XF Writers.
64
     *
65
     * @var Xf[]
66
     */
67
    private $xfWriters = [];
68
69
    /**
70
     * Array containing the colour palette.
71
     *
72
     * @var array
73
     */
74
    private $palette;
75
76
    /**
77
     * The codepage indicates the text encoding used for strings.
78
     */
79
    private int $codepage;
80
81
    /**
82
     * The country code used for localization.
83
     */
84
    private int $countryCode;
85
86
    /**
87
     * Workbook.
88
     */
89
    private Spreadsheet $spreadsheet;
90
91
    /**
92
     * Fonts writers.
93
     *
94
     * @var Font[]
95
     */
96
    private $fontWriters = [];
97
98
    /**
99
     * Added fonts. Maps from font's hash => index in workbook.
100
     *
101
     * @var array
102
     */
103
    private $addedFonts = [];
104
105
    /**
106
     * Shared number formats.
107
     *
108
     * @var array
109
     */
110
    private $numberFormats = [];
111
112
    /**
113
     * Added number formats. Maps from numberFormat's hash => index in workbook.
114
     *
115
     * @var array
116
     */
117
    private $addedNumberFormats = [];
118
119
    /**
120
     * Sizes of the binary worksheet streams.
121
     *
122
     * @var array
123
     */
124
    private $worksheetSizes = [];
125
126
    /**
127
     * Offsets of the binary worksheet streams relative to the start of the global workbook stream.
128
     *
129
     * @var array
130
     */
131
    private $worksheetOffsets = [];
132
133
    /**
134
     * Total number of shared strings in workbook.
135
     *
136
     * @var int
137
     */
138
    private $stringTotal;
139
140
    /**
141
     * Number of unique shared strings in workbook.
142
     *
143
     * @var int
144
     */
145
    private $stringUnique;
146
147
    /**
148
     * Array of unique shared strings in workbook.
149
     *
150
     * @var array
151
     */
152
    private $stringTable;
153
154
    /**
155
     * Color cache.
156
     *
157
     * @var array
158
     */
159
    private $colors;
160
161
    /**
162
     * Escher object corresponding to MSODRAWINGGROUP.
163
     */
164
    private ?\PhpOffice\PhpSpreadsheet\Shared\Escher $escher = null;
165
166
    /** @var mixed */
167
    private static $scrutinizerFalse = false;
168
169
    /**
170
     * Class constructor.
171
     *
172
     * @param Spreadsheet $spreadsheet The Workbook
173
     * @param int $str_total Total number of strings
174
     * @param int $str_unique Total number of unique strings
175
     * @param array $str_table String Table
176
     * @param array $colors Colour Table
177
     * @param Parser $parser The formula parser created for the Workbook
178
     */
179 97
    public function __construct(Spreadsheet $spreadsheet, &$str_total, &$str_unique, &$str_table, &$colors, Parser $parser)
180
    {
181
        // It needs to call its parent's constructor explicitly
182 97
        parent::__construct();
183
184 97
        $this->parser = $parser;
185 97
        $this->biffSize = 0;
186 97
        $this->palette = [];
187 97
        $this->countryCode = -1;
188
189 97
        $this->stringTotal = &$str_total;
190 97
        $this->stringUnique = &$str_unique;
191 97
        $this->stringTable = &$str_table;
192 97
        $this->colors = &$colors;
193 97
        $this->setPaletteXl97();
194
195 97
        $this->spreadsheet = $spreadsheet;
196
197 97
        $this->codepage = 0x04B0;
198
199
        // Add empty sheets and Build color cache
200 97
        $countSheets = $spreadsheet->getSheetCount();
201 97
        for ($i = 0; $i < $countSheets; ++$i) {
202 97
            $phpSheet = $spreadsheet->getSheet($i);
203
204 97
            $this->parser->setExtSheet($phpSheet->getTitle(), $i); // Register worksheet name with parser
205
206 97
            $supbook_index = 0x00;
207 97
            $ref = pack('vvv', $supbook_index, $i, $i);
208 97
            $this->parser->references[] = $ref; // Register reference with parser
209
210
            // Sheet tab colors?
211 97
            if ($phpSheet->isTabColorSet()) {
212 6
                $this->addColor($phpSheet->getTabColor()->getRGB());
213
            }
214
        }
215
    }
216
217
    /**
218
     * Add a new XF writer.
219
     *
220
     * @param bool $isStyleXf Is it a style XF?
221
     *
222
     * @return int Index to XF record
223
     */
224 96
    public function addXfWriter(Style $style, $isStyleXf = false): int
225
    {
226 96
        $xfWriter = new Xf($style);
227 96
        $xfWriter->setIsStyleXf($isStyleXf);
228
229
        // Add the font if not already added
230 96
        $fontIndex = $this->addFont($style->getFont());
231
232
        // Assign the font index to the xf record
233 96
        $xfWriter->setFontIndex($fontIndex);
234
235
        // Background colors, best to treat these after the font so black will come after white in custom palette
236 96
        $xfWriter->setFgColor($this->addColor($style->getFill()->getStartColor()->getRGB()));
237 96
        $xfWriter->setBgColor($this->addColor($style->getFill()->getEndColor()->getRGB()));
238 96
        $xfWriter->setBottomColor($this->addColor($style->getBorders()->getBottom()->getColor()->getRGB()));
239 96
        $xfWriter->setTopColor($this->addColor($style->getBorders()->getTop()->getColor()->getRGB()));
240 96
        $xfWriter->setRightColor($this->addColor($style->getBorders()->getRight()->getColor()->getRGB()));
241 96
        $xfWriter->setLeftColor($this->addColor($style->getBorders()->getLeft()->getColor()->getRGB()));
242 96
        $xfWriter->setDiagColor($this->addColor($style->getBorders()->getDiagonal()->getColor()->getRGB()));
243
244
        // Add the number format if it is not a built-in one and not already added
245 96
        if ($style->getNumberFormat()->getBuiltInFormatCode() === self::$scrutinizerFalse) {
246 21
            $numberFormatHashCode = $style->getNumberFormat()->getHashCode();
247
248 21
            if (isset($this->addedNumberFormats[$numberFormatHashCode])) {
249 10
                $numberFormatIndex = $this->addedNumberFormats[$numberFormatHashCode];
250
            } else {
251 21
                $numberFormatIndex = 164 + count($this->numberFormats);
252 21
                $this->numberFormats[$numberFormatIndex] = $style->getNumberFormat();
253 21
                $this->addedNumberFormats[$numberFormatHashCode] = $numberFormatIndex;
254
            }
255
        } else {
256 96
            $numberFormatIndex = (int) $style->getNumberFormat()->getBuiltInFormatCode();
257
        }
258
259
        // Assign the number format index to xf record
260 96
        $xfWriter->setNumberFormatIndex($numberFormatIndex);
261
262 96
        $this->xfWriters[] = $xfWriter;
263
264 96
        return count($this->xfWriters) - 1;
265
    }
266
267
    /**
268
     * Add a font to added fonts.
269
     *
270
     * @return int Index to FONT record
271
     */
272 96
    public function addFont(\PhpOffice\PhpSpreadsheet\Style\Font $font)
273
    {
274 96
        $fontHashCode = $font->getHashCode();
275 96
        if (isset($this->addedFonts[$fontHashCode])) {
276 96
            $fontIndex = $this->addedFonts[$fontHashCode];
277
        } else {
278 96
            $countFonts = count($this->fontWriters);
279 96
            $fontIndex = ($countFonts < 4) ? $countFonts : $countFonts + 1;
280
281 96
            $fontWriter = new Font($font);
282 96
            $fontWriter->setColorIndex($this->addColor($font->getColor()->getRGB()));
283 96
            $this->fontWriters[] = $fontWriter;
284
285 96
            $this->addedFonts[$fontHashCode] = $fontIndex;
286
        }
287
288 96
        return $fontIndex;
289
    }
290
291
    /**
292
     * Alter color palette adding a custom color.
293
     *
294
     * @param string $rgb E.g. 'FF00AA'
295
     *
296
     * @return int Color index
297
     */
298 97
    private function addColor($rgb)
299
    {
300 97
        if (!isset($this->colors[$rgb])) {
301 97
            $color =
302 97
                [
303 97
                    hexdec(substr($rgb, 0, 2)),
304 97
                    hexdec(substr($rgb, 2, 2)),
305 97
                    hexdec(substr($rgb, 4)),
306 97
                    0,
307 97
                ];
308 97
            $colorIndex = array_search($color, $this->palette);
309 97
            if ($colorIndex) {
310 97
                $this->colors[$rgb] = $colorIndex;
311
            } else {
312 16
                if (count($this->colors) === 0) {
313 7
                    $lastColor = 7;
314
                } else {
315 16
                    $lastColor = end($this->colors);
316
                }
317 16
                if ($lastColor < 57) {
318
                    // then we add a custom color altering the palette
319 16
                    $colorIndex = $lastColor + 1;
320 16
                    $this->palette[$colorIndex] = $color;
321 16
                    $this->colors[$rgb] = $colorIndex;
322
                } else {
323
                    // no room for more custom colors, just map to black
324 97
                    $colorIndex = 0;
325
                }
326
            }
327
        } else {
328
            // fetch already added custom color
329 97
            $colorIndex = $this->colors[$rgb];
330
        }
331
332 97
        return $colorIndex;
333
    }
334
335
    /**
336
     * Sets the colour palette to the Excel 97+ default.
337
     */
338 97
    private function setPaletteXl97(): void
339
    {
340 97
        $this->palette = [
341 97
            0x08 => [0x00, 0x00, 0x00, 0x00],
342 97
            0x09 => [0xff, 0xff, 0xff, 0x00],
343 97
            0x0A => [0xff, 0x00, 0x00, 0x00],
344 97
            0x0B => [0x00, 0xff, 0x00, 0x00],
345 97
            0x0C => [0x00, 0x00, 0xff, 0x00],
346 97
            0x0D => [0xff, 0xff, 0x00, 0x00],
347 97
            0x0E => [0xff, 0x00, 0xff, 0x00],
348 97
            0x0F => [0x00, 0xff, 0xff, 0x00],
349 97
            0x10 => [0x80, 0x00, 0x00, 0x00],
350 97
            0x11 => [0x00, 0x80, 0x00, 0x00],
351 97
            0x12 => [0x00, 0x00, 0x80, 0x00],
352 97
            0x13 => [0x80, 0x80, 0x00, 0x00],
353 97
            0x14 => [0x80, 0x00, 0x80, 0x00],
354 97
            0x15 => [0x00, 0x80, 0x80, 0x00],
355 97
            0x16 => [0xc0, 0xc0, 0xc0, 0x00],
356 97
            0x17 => [0x80, 0x80, 0x80, 0x00],
357 97
            0x18 => [0x99, 0x99, 0xff, 0x00],
358 97
            0x19 => [0x99, 0x33, 0x66, 0x00],
359 97
            0x1A => [0xff, 0xff, 0xcc, 0x00],
360 97
            0x1B => [0xcc, 0xff, 0xff, 0x00],
361 97
            0x1C => [0x66, 0x00, 0x66, 0x00],
362 97
            0x1D => [0xff, 0x80, 0x80, 0x00],
363 97
            0x1E => [0x00, 0x66, 0xcc, 0x00],
364 97
            0x1F => [0xcc, 0xcc, 0xff, 0x00],
365 97
            0x20 => [0x00, 0x00, 0x80, 0x00],
366 97
            0x21 => [0xff, 0x00, 0xff, 0x00],
367 97
            0x22 => [0xff, 0xff, 0x00, 0x00],
368 97
            0x23 => [0x00, 0xff, 0xff, 0x00],
369 97
            0x24 => [0x80, 0x00, 0x80, 0x00],
370 97
            0x25 => [0x80, 0x00, 0x00, 0x00],
371 97
            0x26 => [0x00, 0x80, 0x80, 0x00],
372 97
            0x27 => [0x00, 0x00, 0xff, 0x00],
373 97
            0x28 => [0x00, 0xcc, 0xff, 0x00],
374 97
            0x29 => [0xcc, 0xff, 0xff, 0x00],
375 97
            0x2A => [0xcc, 0xff, 0xcc, 0x00],
376 97
            0x2B => [0xff, 0xff, 0x99, 0x00],
377 97
            0x2C => [0x99, 0xcc, 0xff, 0x00],
378 97
            0x2D => [0xff, 0x99, 0xcc, 0x00],
379 97
            0x2E => [0xcc, 0x99, 0xff, 0x00],
380 97
            0x2F => [0xff, 0xcc, 0x99, 0x00],
381 97
            0x30 => [0x33, 0x66, 0xff, 0x00],
382 97
            0x31 => [0x33, 0xcc, 0xcc, 0x00],
383 97
            0x32 => [0x99, 0xcc, 0x00, 0x00],
384 97
            0x33 => [0xff, 0xcc, 0x00, 0x00],
385 97
            0x34 => [0xff, 0x99, 0x00, 0x00],
386 97
            0x35 => [0xff, 0x66, 0x00, 0x00],
387 97
            0x36 => [0x66, 0x66, 0x99, 0x00],
388 97
            0x37 => [0x96, 0x96, 0x96, 0x00],
389 97
            0x38 => [0x00, 0x33, 0x66, 0x00],
390 97
            0x39 => [0x33, 0x99, 0x66, 0x00],
391 97
            0x3A => [0x00, 0x33, 0x00, 0x00],
392 97
            0x3B => [0x33, 0x33, 0x00, 0x00],
393 97
            0x3C => [0x99, 0x33, 0x00, 0x00],
394 97
            0x3D => [0x99, 0x33, 0x66, 0x00],
395 97
            0x3E => [0x33, 0x33, 0x99, 0x00],
396 97
            0x3F => [0x33, 0x33, 0x33, 0x00],
397 97
        ];
398
    }
399
400
    /**
401
     * Assemble worksheets into a workbook and send the BIFF data to an OLE
402
     * storage.
403
     *
404
     * @param array $worksheetSizes The sizes in bytes of the binary worksheet streams
405
     *
406
     * @return string Binary data for workbook stream
407
     */
408 95
    public function writeWorkbook(array $worksheetSizes): string
409
    {
410 95
        $this->worksheetSizes = $worksheetSizes;
411
412
        // Calculate the number of selected worksheet tabs and call the finalization
413
        // methods for each worksheet
414 95
        $total_worksheets = $this->spreadsheet->getSheetCount();
415
416
        // Add part 1 of the Workbook globals, what goes before the SHEET records
417 95
        $this->storeBof(0x0005);
418 95
        $this->writeCodepage();
419 95
        $this->writeWindow1();
420
421 95
        $this->writeDateMode();
422 95
        $this->writeAllFonts();
423 95
        $this->writeAllNumberFormats();
424 95
        $this->writeAllXfs();
425 95
        $this->writeAllStyles();
426 95
        $this->writePalette();
427
428
        // Prepare part 3 of the workbook global stream, what goes after the SHEET records
429 95
        $part3 = '';
430 95
        if ($this->countryCode !== -1) {
431
            $part3 .= $this->writeCountry();
432
        }
433 95
        $part3 .= $this->writeRecalcId();
434
435 95
        $part3 .= $this->writeSupbookInternal();
436
        /* TODO: store external SUPBOOK records and XCT and CRN records
437
        in case of external references for BIFF8 */
438 95
        $part3 .= $this->writeExternalsheetBiff8();
439 95
        $part3 .= $this->writeAllDefinedNamesBiff8();
440 95
        $part3 .= $this->writeMsoDrawingGroup();
441 95
        $part3 .= $this->writeSharedStringsTable();
442
443 95
        $part3 .= $this->writeEof();
444
445
        // Add part 2 of the Workbook globals, the SHEET records
446 95
        $this->calcSheetOffsets();
447 95
        for ($i = 0; $i < $total_worksheets; ++$i) {
448 95
            $this->writeBoundSheet($this->spreadsheet->getSheet($i), $this->worksheetOffsets[$i]);
449
        }
450
451
        // Add part 3 of the Workbook globals
452 95
        $this->_data .= $part3;
453
454 95
        return $this->_data;
455
    }
456
457
    /**
458
     * Calculate offsets for Worksheet BOF records.
459
     */
460 95
    private function calcSheetOffsets(): void
461
    {
462 95
        $boundsheet_length = 10; // fixed length for a BOUNDSHEET record
463
464
        // size of Workbook globals part 1 + 3
465 95
        $offset = $this->_datasize;
466
467
        // add size of Workbook globals part 2, the length of the SHEET records
468 95
        $total_worksheets = count($this->spreadsheet->getAllSheets());
469 95
        foreach ($this->spreadsheet->getWorksheetIterator() as $sheet) {
470 95
            $offset += $boundsheet_length + strlen(StringHelper::UTF8toBIFF8UnicodeShort($sheet->getTitle()));
471
        }
472
473
        // add the sizes of each of the Sheet substreams, respectively
474 95
        for ($i = 0; $i < $total_worksheets; ++$i) {
475 95
            $this->worksheetOffsets[$i] = $offset;
476 95
            $offset += $this->worksheetSizes[$i];
477
        }
478 95
        $this->biffSize = $offset;
479
    }
480
481
    /**
482
     * Store the Excel FONT records.
483
     */
484 95
    private function writeAllFonts(): void
485
    {
486 95
        foreach ($this->fontWriters as $fontWriter) {
487 95
            $this->append($fontWriter->writeFont());
488
        }
489
    }
490
491
    /**
492
     * Store user defined numerical formats i.e. FORMAT records.
493
     */
494 95
    private function writeAllNumberFormats(): void
495
    {
496 95
        foreach ($this->numberFormats as $numberFormatIndex => $numberFormat) {
497 21
            $this->writeNumberFormat($numberFormat->getFormatCode(), $numberFormatIndex);
498
        }
499
    }
500
501
    /**
502
     * Write all XF records.
503
     */
504 95
    private function writeAllXfs(): void
505
    {
506 95
        foreach ($this->xfWriters as $xfWriter) {
507 95
            $this->append($xfWriter->writeXf());
508
        }
509
    }
510
511
    /**
512
     * Write all STYLE records.
513
     */
514 95
    private function writeAllStyles(): void
515
    {
516 95
        $this->writeStyle();
517
    }
518
519 7
    private function parseDefinedNameValue(DefinedName $definedName): string
520
    {
521 7
        $definedRange = $definedName->getValue();
522 7
        $splitCount = preg_match_all(
523 7
            '/' . Calculation::CALCULATION_REGEXP_CELLREF . '/mui',
524 7
            $definedRange,
525 7
            $splitRanges,
526 7
            PREG_OFFSET_CAPTURE
527 7
        );
528
529 7
        $lengths = array_map('strlen', array_column($splitRanges[0], 0));
530 7
        $offsets = array_column($splitRanges[0], 1);
531
532 7
        $worksheets = $splitRanges[2];
533 7
        $columns = $splitRanges[6];
534 7
        $rows = $splitRanges[7];
535
536 7
        while ($splitCount > 0) {
537 7
            --$splitCount;
538 7
            $length = $lengths[$splitCount];
539 7
            $offset = $offsets[$splitCount];
540 7
            $worksheet = $worksheets[$splitCount][0];
541 7
            $column = $columns[$splitCount][0];
542 7
            $row = $rows[$splitCount][0];
543
544 7
            $newRange = '';
545 7
            if (empty($worksheet)) {
546 6
                if (($offset === 0) || ($definedRange[$offset - 1] !== ':')) {
547
                    // We should have a worksheet
548 6
                    $worksheet = $definedName->getWorksheet() ? $definedName->getWorksheet()->getTitle() : null;
549
                }
550
            } else {
551 4
                $worksheet = str_replace("''", "'", trim($worksheet, "'"));
552
            }
553 7
            if (!empty($worksheet)) {
554 7
                $newRange = "'" . str_replace("'", "''", $worksheet) . "'!";
555
            }
556
557 7
            if (!empty($column)) {
558 7
                $newRange .= "\${$column}";
559
            }
560 7
            if (!empty($row)) {
561 7
                $newRange .= "\${$row}";
562
            }
563
564 7
            $definedRange = substr($definedRange, 0, $offset) . $newRange . substr($definedRange, $offset + $length);
565
        }
566
567 7
        return $definedRange;
568
    }
569
570
    /**
571
     * Writes all the DEFINEDNAME records (BIFF8).
572
     * So far this is only used for repeating rows/columns (print titles) and print areas.
573
     */
574 95
    private function writeAllDefinedNamesBiff8(): string
575
    {
576 95
        $chunk = '';
577
578
        // Named ranges
579 95
        $definedNames = $this->spreadsheet->getDefinedNames();
580 95
        if (count($definedNames) > 0) {
581
            // Loop named ranges
582 7
            foreach ($definedNames as $definedName) {
583 7
                $range = $this->parseDefinedNameValue($definedName);
584
585
                // parse formula
586
                try {
587 7
                    $this->parser->parse($range);
588 7
                    $formulaData = $this->parser->toReversePolish();
589
590
                    // make sure tRef3d is of type tRef3dR (0x3A)
591 6
                    if (isset($formulaData[0]) && ($formulaData[0] == "\x7A" || $formulaData[0] == "\x5A")) {
592 5
                        $formulaData = "\x3A" . substr($formulaData, 1);
593
                    }
594
595 6
                    if ($definedName->getLocalOnly()) {
596
                        // local scope
597 1
                        $scopeWs = $definedName->getScope();
598 1
                        $scope = ($scopeWs === null) ? 0 : ($this->spreadsheet->getIndex($scopeWs) + 1);
599
                    } else {
600
                        // global scope
601 6
                        $scope = 0;
602
                    }
603 6
                    $chunk .= $this->writeData($this->writeDefinedNameBiff8($definedName->getName(), $formulaData, $scope, false));
604 1
                } catch (PhpSpreadsheetException) {
605
                    // do nothing
606
                }
607
            }
608
        }
609
610
        // total number of sheets
611 95
        $total_worksheets = $this->spreadsheet->getSheetCount();
612
613
        // write the print titles (repeating rows, columns), if any
614 95
        for ($i = 0; $i < $total_worksheets; ++$i) {
615 95
            $sheetSetup = $this->spreadsheet->getSheet($i)->getPageSetup();
616
            // simultaneous repeatColumns repeatRows
617 95
            if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) {
618
                $repeat = $sheetSetup->getColumnsToRepeatAtLeft();
619
                $colmin = Coordinate::columnIndexFromString($repeat[0]) - 1;
620
                $colmax = Coordinate::columnIndexFromString($repeat[1]) - 1;
621
622
                $repeat = $sheetSetup->getRowsToRepeatAtTop();
623
                $rowmin = $repeat[0] - 1;
624
                $rowmax = $repeat[1] - 1;
625
626
                // construct formula data manually
627
                $formulaData = pack('Cv', 0x29, 0x17); // tMemFunc
628
                $formulaData .= pack('Cvvvvv', 0x3B, $i, 0, 65535, $colmin, $colmax); // tArea3d
629
                $formulaData .= pack('Cvvvvv', 0x3B, $i, $rowmin, $rowmax, 0, 255); // tArea3d
630
                $formulaData .= pack('C', 0x10); // tList
631
632
                // store the DEFINEDNAME record
633
                $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x07), $formulaData, $i + 1, true));
634 95
            } elseif ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) {
635
                // (exclusive) either repeatColumns or repeatRows.
636
                // Columns to repeat
637 2
                if ($sheetSetup->isColumnsToRepeatAtLeftSet()) {
638
                    $repeat = $sheetSetup->getColumnsToRepeatAtLeft();
639
                    $colmin = Coordinate::columnIndexFromString($repeat[0]) - 1;
640
                    $colmax = Coordinate::columnIndexFromString($repeat[1]) - 1;
641
                } else {
642 2
                    $colmin = 0;
643 2
                    $colmax = 255;
644
                }
645
                // Rows to repeat
646 2
                if ($sheetSetup->isRowsToRepeatAtTopSet()) {
647 2
                    $repeat = $sheetSetup->getRowsToRepeatAtTop();
648 2
                    $rowmin = $repeat[0] - 1;
649 2
                    $rowmax = $repeat[1] - 1;
650
                } else {
651
                    $rowmin = 0;
652
                    $rowmax = 65535;
653
                }
654
655
                // construct formula data manually because parser does not recognize absolute 3d cell references
656 2
                $formulaData = pack('Cvvvvv', 0x3B, $i, $rowmin, $rowmax, $colmin, $colmax);
657
658
                // store the DEFINEDNAME record
659 2
                $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x07), $formulaData, $i + 1, true));
660
            }
661
        }
662
663
        // write the print areas, if any
664 95
        for ($i = 0; $i < $total_worksheets; ++$i) {
665 95
            $sheetSetup = $this->spreadsheet->getSheet($i)->getPageSetup();
666 95
            if ($sheetSetup->isPrintAreaSet()) {
667
                // Print area, e.g. A3:J6,H1:X20
668 4
                $printArea = Coordinate::splitRange($sheetSetup->getPrintArea());
669 4
                $countPrintArea = count($printArea);
670
671 4
                $formulaData = '';
672 4
                for ($j = 0; $j < $countPrintArea; ++$j) {
673 4
                    $printAreaRect = $printArea[$j]; // e.g. A3:J6
674 4
                    $printAreaRect[0] = Coordinate::indexesFromString($printAreaRect[0]);
675 4
                    $printAreaRect[1] = Coordinate::indexesFromString($printAreaRect[1]);
676
677 4
                    $print_rowmin = $printAreaRect[0][1] - 1;
678 4
                    $print_rowmax = $printAreaRect[1][1] - 1;
679 4
                    $print_colmin = $printAreaRect[0][0] - 1;
680 4
                    $print_colmax = $printAreaRect[1][0] - 1;
681
682
                    // construct formula data manually because parser does not recognize absolute 3d cell references
683 4
                    $formulaData .= pack('Cvvvvv', 0x3B, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax);
684
685 4
                    if ($j > 0) {
686 1
                        $formulaData .= pack('C', 0x10); // list operator token ','
687
                    }
688
                }
689
690
                // store the DEFINEDNAME record
691 4
                $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x06), $formulaData, $i + 1, true));
692
            }
693
        }
694
695
        // write autofilters, if any
696 95
        for ($i = 0; $i < $total_worksheets; ++$i) {
697 95
            $sheetAutoFilter = $this->spreadsheet->getSheet($i)->getAutoFilter();
698 95
            $autoFilterRange = $sheetAutoFilter->getRange();
699 95
            if (!empty($autoFilterRange)) {
700 3
                $rangeBounds = Coordinate::rangeBoundaries($autoFilterRange);
701
702
                //Autofilter built in name
703 3
                $name = pack('C', 0x0D);
704
705 3
                $chunk .= $this->writeData($this->writeShortNameBiff8($name, $i + 1, $rangeBounds, true));
706
            }
707
        }
708
709 95
        return $chunk;
710
    }
711
712
    /**
713
     * Write a DEFINEDNAME record for BIFF8 using explicit binary formula data.
714
     *
715
     * @param string $name The name in UTF-8
716
     * @param string $formulaData The binary formula data
717
     * @param int $sheetIndex 1-based sheet index the defined name applies to. 0 = global
718
     * @param bool $isBuiltIn Built-in name?
719
     *
720
     * @return string Complete binary record data
721
     */
722 10
    private function writeDefinedNameBiff8($name, string $formulaData, int $sheetIndex = 0, bool $isBuiltIn = false): string
723
    {
724 10
        $record = 0x0018;
725
726
        // option flags
727 10
        $options = $isBuiltIn ? 0x20 : 0x00;
728
729
        // length of the name, character count
730 10
        $nlen = StringHelper::countCharacters($name);
731
732
        // name with stripped length field
733 10
        $name = substr(StringHelper::UTF8toBIFF8UnicodeLong($name), 2);
734
735
        // size of the formula (in bytes)
736 10
        $sz = strlen($formulaData);
737
738
        // combine the parts
739 10
        $data = pack('vCCvvvCCCC', $options, 0, $nlen, $sz, 0, $sheetIndex, 0, 0, 0, 0)
740 10
            . $name . $formulaData;
741 10
        $length = strlen($data);
742
743 10
        $header = pack('vv', $record, $length);
744
745 10
        return $header . $data;
746
    }
747
748
    /**
749
     * Write a short NAME record.
750
     *
751
     * @param int $sheetIndex 1-based sheet index the defined name applies to. 0 = global
752
     * @param int[][] $rangeBounds range boundaries
753
     *
754
     * @return string Complete binary record data
755
     * */
756 3
    private function writeShortNameBiff8(string $name, int $sheetIndex, array $rangeBounds, bool $isHidden = false): string
757
    {
758 3
        $record = 0x0018;
759
760
        // option flags
761 3
        $options = ($isHidden ? 0x21 : 0x00);
762
763 3
        $extra = pack(
764 3
            'Cvvvvv',
765 3
            0x3B,
766 3
            $sheetIndex - 1,
767 3
            $rangeBounds[0][1] - 1,
768 3
            $rangeBounds[1][1] - 1,
769 3
            $rangeBounds[0][0] - 1,
770 3
            $rangeBounds[1][0] - 1
771 3
        );
772
773
        // size of the formula (in bytes)
774 3
        $sz = strlen($extra);
775
776
        // combine the parts
777 3
        $data = pack('vCCvvvCCCCC', $options, 0, 1, $sz, 0, $sheetIndex, 0, 0, 0, 0, 0)
778 3
            . $name . $extra;
779 3
        $length = strlen($data);
780
781 3
        $header = pack('vv', $record, $length);
782
783 3
        return $header . $data;
784
    }
785
786
    /**
787
     * Stores the CODEPAGE biff record.
788
     */
789 95
    private function writeCodepage(): void
790
    {
791 95
        $record = 0x0042; // Record identifier
792 95
        $length = 0x0002; // Number of bytes to follow
793 95
        $cv = $this->codepage; // The code page
794
795 95
        $header = pack('vv', $record, $length);
796 95
        $data = pack('v', $cv);
797
798 95
        $this->append($header . $data);
799
    }
800
801
    /**
802
     * Write Excel BIFF WINDOW1 record.
803
     */
804 95
    private function writeWindow1(): void
805
    {
806 95
        $record = 0x003D; // Record identifier
807 95
        $length = 0x0012; // Number of bytes to follow
808
809 95
        $xWn = 0x0000; // Horizontal position of window
810 95
        $yWn = 0x0000; // Vertical position of window
811 95
        $dxWn = 0x25BC; // Width of window
812 95
        $dyWn = 0x1572; // Height of window
813
814 95
        $grbit = 0x0038; // Option flags
815
816
        // not supported by PhpSpreadsheet, so there is only one selected sheet, the active
817 95
        $ctabsel = 1; // Number of workbook tabs selected
818
819 95
        $wTabRatio = 0x0258; // Tab to scrollbar ratio
820
821
        // not supported by PhpSpreadsheet, set to 0
822 95
        $itabFirst = 0; // 1st displayed worksheet
823 95
        $itabCur = $this->spreadsheet->getActiveSheetIndex(); // Active worksheet
824
825 95
        $header = pack('vv', $record, $length);
826 95
        $data = pack('vvvvvvvvv', $xWn, $yWn, $dxWn, $dyWn, $grbit, $itabCur, $itabFirst, $ctabsel, $wTabRatio);
827 95
        $this->append($header . $data);
828
    }
829
830
    /**
831
     * Writes Excel BIFF BOUNDSHEET record.
832
     *
833
     * @param int $offset Location of worksheet BOF
834
     */
835 95
    private function writeBoundSheet(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet, $offset): void
836
    {
837 95
        $sheetname = $sheet->getTitle();
838 95
        $record = 0x0085; // Record identifier
839 95
        $ss = match ($sheet->getSheetState()) {
840 95
            \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_VISIBLE => 0x00,
841 95
            \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_HIDDEN => 0x01,
842 95
            \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_VERYHIDDEN => 0x02,
843 95
            default => 0x00,
844 95
        };
845
846
        // sheet type
847 95
        $st = 0x00;
848
849
        //$grbit = 0x0000; // Visibility and sheet type
850
851 95
        $data = pack('VCC', $offset, $ss, $st);
852 95
        $data .= StringHelper::UTF8toBIFF8UnicodeShort($sheetname);
853
854 95
        $length = strlen($data);
855 95
        $header = pack('vv', $record, $length);
856 95
        $this->append($header . $data);
857
    }
858
859
    /**
860
     * Write Internal SUPBOOK record.
861
     */
862 95
    private function writeSupbookInternal(): string
863
    {
864 95
        $record = 0x01AE; // Record identifier
865 95
        $length = 0x0004; // Bytes to follow
866
867 95
        $header = pack('vv', $record, $length);
868 95
        $data = pack('vv', $this->spreadsheet->getSheetCount(), 0x0401);
869
870 95
        return $this->writeData($header . $data);
871
    }
872
873
    /**
874
     * Writes the Excel BIFF EXTERNSHEET record. These references are used by
875
     * formulas.
876
     */
877 95
    private function writeExternalsheetBiff8(): string
878
    {
879 95
        $totalReferences = count($this->parser->references);
880 95
        $record = 0x0017; // Record identifier
881 95
        $length = 2 + 6 * $totalReferences; // Number of bytes to follow
882
883
        //$supbook_index = 0; // FIXME: only using internal SUPBOOK record
884 95
        $header = pack('vv', $record, $length);
885 95
        $data = pack('v', $totalReferences);
886 95
        for ($i = 0; $i < $totalReferences; ++$i) {
887 95
            $data .= $this->parser->references[$i];
888
        }
889
890 95
        return $this->writeData($header . $data);
891
    }
892
893
    /**
894
     * Write Excel BIFF STYLE records.
895
     */
896 95
    private function writeStyle(): void
897
    {
898 95
        $record = 0x0293; // Record identifier
899 95
        $length = 0x0004; // Bytes to follow
900
901 95
        $ixfe = 0x8000; // Index to cell style XF
902 95
        $BuiltIn = 0x00; // Built-in style
903 95
        $iLevel = 0xff; // Outline style level
904
905 95
        $header = pack('vv', $record, $length);
906 95
        $data = pack('vCC', $ixfe, $BuiltIn, $iLevel);
907 95
        $this->append($header . $data);
908
    }
909
910
    /**
911
     * Writes Excel FORMAT record for non "built-in" numerical formats.
912
     *
913
     * @param string $format Custom format string
914
     * @param int $ifmt Format index code
915
     */
916 21
    private function writeNumberFormat(string $format, $ifmt): void
917
    {
918 21
        $record = 0x041E; // Record identifier
919
920 21
        $numberFormatString = StringHelper::UTF8toBIFF8UnicodeLong($format);
921 21
        $length = 2 + strlen($numberFormatString); // Number of bytes to follow
922
923 21
        $header = pack('vv', $record, $length);
924 21
        $data = pack('v', $ifmt) . $numberFormatString;
925 21
        $this->append($header . $data);
926
    }
927
928
    /**
929
     * Write DATEMODE record to indicate the date system in use (1904 or 1900).
930
     */
931 95
    private function writeDateMode(): void
932
    {
933 95
        $record = 0x0022; // Record identifier
934 95
        $length = 0x0002; // Bytes to follow
935
936 95
        $f1904 = (Date::getExcelCalendar() === Date::CALENDAR_MAC_1904)
937
            ? 1
938 95
            : 0; // Flag for 1904 date system
939
940 95
        $header = pack('vv', $record, $length);
941 95
        $data = pack('v', $f1904);
942 95
        $this->append($header . $data);
943
    }
944
945
    /**
946
     * Stores the COUNTRY record for localization.
947
     *
948
     * @return string
949
     */
950
    private function writeCountry()
951
    {
952
        $record = 0x008C; // Record identifier
953
        $length = 4; // Number of bytes to follow
954
955
        $header = pack('vv', $record, $length);
956
        // using the same country code always for simplicity
957
        $data = pack('vv', $this->countryCode, $this->countryCode);
958
959
        return $this->writeData($header . $data);
960
    }
961
962
    /**
963
     * Write the RECALCID record.
964
     *
965
     * @return string
966
     */
967 95
    private function writeRecalcId()
968
    {
969 95
        $record = 0x01C1; // Record identifier
970 95
        $length = 8; // Number of bytes to follow
971
972 95
        $header = pack('vv', $record, $length);
973
974
        // by inspection of real Excel files, MS Office Excel 2007 writes this
975 95
        $data = pack('VV', 0x000001C1, 0x00001E667);
976
977 95
        return $this->writeData($header . $data);
978
    }
979
980
    /**
981
     * Stores the PALETTE biff record.
982
     */
983 95
    private function writePalette(): void
984
    {
985 95
        $aref = $this->palette;
986
987 95
        $record = 0x0092; // Record identifier
988 95
        $length = 2 + 4 * count($aref); // Number of bytes to follow
989 95
        $ccv = count($aref); // Number of RGB values to follow
990 95
        $data = ''; // The RGB data
991
992
        // Pack the RGB data
993 95
        foreach ($aref as $color) {
994 95
            foreach ($color as $byte) {
995 95
                $data .= pack('C', $byte);
996
            }
997
        }
998
999 95
        $header = pack('vvv', $record, $length, $ccv);
1000 95
        $this->append($header . $data);
1001
    }
1002
1003
    /**
1004
     * Handling of the SST continue blocks is complicated by the need to include an
1005
     * additional continuation byte depending on whether the string is split between
1006
     * blocks or whether it starts at the beginning of the block. (There are also
1007
     * additional complications that will arise later when/if Rich Strings are
1008
     * supported).
1009
     *
1010
     * The Excel documentation says that the SST record should be followed by an
1011
     * EXTSST record. The EXTSST record is a hash table that is used to optimise
1012
     * access to SST. However, despite the documentation it doesn't seem to be
1013
     * required so we will ignore it.
1014
     *
1015
     * @return string Binary data
1016
     */
1017 95
    private function writeSharedStringsTable(): string
1018
    {
1019
        // maximum size of record data (excluding record header)
1020 95
        $continue_limit = 8224;
1021
1022
        // initialize array of record data blocks
1023 95
        $recordDatas = [];
1024
1025
        // start SST record data block with total number of strings, total number of unique strings
1026 95
        $recordData = pack('VV', $this->stringTotal, $this->stringUnique);
1027
1028
        // loop through all (unique) strings in shared strings table
1029 95
        foreach (array_keys($this->stringTable) as $string) {
1030
            // here $string is a BIFF8 encoded string
1031
1032
            // length = character count
1033 68
            $headerinfo = unpack('vlength/Cencoding', $string);
1034
1035
            // currently, this is always 1 = uncompressed
1036 68
            $encoding = $headerinfo['encoding'] ?? 1;
1037
1038
            // initialize finished writing current $string
1039 68
            $finished = false;
1040
1041 68
            while ($finished === false) {
1042
                // normally, there will be only one cycle, but if string cannot immediately be written as is
1043
                // there will be need for more than one cylcle, if string longer than one record data block, there
1044
                // may be need for even more cycles
1045
1046 68
                if (strlen($recordData) + strlen($string) <= $continue_limit) {
1047
                    // then we can write the string (or remainder of string) without any problems
1048 68
                    $recordData .= $string;
1049
1050 68
                    if (strlen($recordData) + strlen($string) == $continue_limit) {
1051
                        // we close the record data block, and initialize a new one
1052
                        $recordDatas[] = $recordData;
1053
                        $recordData = '';
1054
                    }
1055
1056
                    // we are finished writing this string
1057 68
                    $finished = true;
1058
                } else {
1059
                    // special treatment writing the string (or remainder of the string)
1060
                    // If the string is very long it may need to be written in more than one CONTINUE record.
1061
1062
                    // check how many bytes more there is room for in the current record
1063 1
                    $space_remaining = $continue_limit - strlen($recordData);
1064
1065
                    // minimum space needed
1066
                    // uncompressed: 2 byte string length length field + 1 byte option flags + 2 byte character
1067
                    // compressed:   2 byte string length length field + 1 byte option flags + 1 byte character
1068 1
                    $min_space_needed = ($encoding == 1) ? 5 : 4;
1069
1070
                    // We have two cases
1071
                    // 1. space remaining is less than minimum space needed
1072
                    //        here we must waste the space remaining and move to next record data block
1073
                    // 2. space remaining is greater than or equal to minimum space needed
1074
                    //        here we write as much as we can in the current block, then move to next record data block
1075
1076 1
                    if ($space_remaining < $min_space_needed) {
1077
                        // 1. space remaining is less than minimum space needed.
1078
                        // we close the block, store the block data
1079
                        $recordDatas[] = $recordData;
1080
1081
                        // and start new record data block where we start writing the string
1082
                        $recordData = '';
1083
                    } else {
1084
                        // 2. space remaining is greater than or equal to minimum space needed.
1085
                        // initialize effective remaining space, for Unicode strings this may need to be reduced by 1, see below
1086 1
                        $effective_space_remaining = $space_remaining;
1087
1088
                        // for uncompressed strings, sometimes effective space remaining is reduced by 1
1089 1
                        if ($encoding == 1 && (strlen($string) - $space_remaining) % 2 == 1) {
1090 1
                            --$effective_space_remaining;
1091
                        }
1092
1093
                        // one block fininshed, store the block data
1094 1
                        $recordData .= substr($string, 0, $effective_space_remaining);
1095
1096 1
                        $string = substr($string, $effective_space_remaining); // for next cycle in while loop
1097 1
                        $recordDatas[] = $recordData;
1098
1099
                        // start new record data block with the repeated option flags
1100 1
                        $recordData = pack('C', $encoding);
1101
                    }
1102
                }
1103
            }
1104
        }
1105
1106
        // Store the last record data block unless it is empty
1107
        // if there was no need for any continue records, this will be the for SST record data block itself
1108 95
        if (strlen($recordData) > 0) {
1109 95
            $recordDatas[] = $recordData;
1110
        }
1111
1112
        // combine into one chunk with all the blocks SST, CONTINUE,...
1113 95
        $chunk = '';
1114 95
        foreach ($recordDatas as $i => $recordData) {
1115
            // first block should have the SST record header, remaing should have CONTINUE header
1116 95
            $record = ($i == 0) ? 0x00FC : 0x003C;
1117
1118 95
            $header = pack('vv', $record, strlen($recordData));
1119 95
            $data = $header . $recordData;
1120
1121 95
            $chunk .= $this->writeData($data);
1122
        }
1123
1124 95
        return $chunk;
1125
    }
1126
1127
    /**
1128
     * Writes the MSODRAWINGGROUP record if needed. Possibly split using CONTINUE records.
1129
     */
1130 95
    private function writeMsoDrawingGroup(): string
1131
    {
1132
        // write the Escher stream if necessary
1133 95
        if (isset($this->escher)) {
1134 14
            $writer = new Escher($this->escher);
1135 14
            $data = $writer->close();
1136
1137 14
            $record = 0x00EB;
1138 14
            $length = strlen($data);
1139 14
            $header = pack('vv', $record, $length);
1140
1141 14
            return $this->writeData($header . $data);
1142
        }
1143
1144 81
        return '';
1145
    }
1146
1147
    /**
1148
     * Get Escher object.
1149
     */
1150
    public function getEscher(): ?\PhpOffice\PhpSpreadsheet\Shared\Escher
1151
    {
1152
        return $this->escher;
1153
    }
1154
1155
    /**
1156
     * Set Escher object.
1157
     */
1158 14
    public function setEscher(?\PhpOffice\PhpSpreadsheet\Shared\Escher $escher): void
1159
    {
1160 14
        $this->escher = $escher;
1161
    }
1162
}
1163