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