ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
Workbook.php
Go to the documentation of this file.
1<?php
2
4
8use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
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 <xnoguer@rezebra.com>
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// * <jmcnamara@cpan.org>
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 xnoguer@rezebra.com
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// */
48class Workbook extends BIFFwriter
49{
55 private $parser;
56
64 private $biffSize;
65
71 private $xfWriters = [];
72
78 private $palette;
79
85 private $codepage;
86
92 private $countryCode;
93
99 private $spreadsheet;
100
106 private $fontWriters = [];
107
113 private $addedFonts = [];
114
120 private $numberFormats = [];
121
128
134 private $worksheetSizes = [];
135
141 private $worksheetOffsets = [];
142
149
156
163
167 private $colors;
168
174 private $escher;
175
186 public function __construct(Spreadsheet $spreadsheet, &$str_total, &$str_unique, &$str_table, &$colors, Parser $parser)
187 {
188 // It needs to call its parent's constructor explicitly
189 parent::__construct();
190
191 $this->parser = $parser;
192 $this->biffSize = 0;
193 $this->palette = [];
194 $this->countryCode = -1;
195
196 $this->stringTotal = &$str_total;
197 $this->stringUnique = &$str_unique;
198 $this->stringTable = &$str_table;
199 $this->colors = &$colors;
200 $this->setPaletteXl97();
201
202 $this->spreadsheet = $spreadsheet;
203
204 $this->codepage = 0x04B0;
205
206 // Add empty sheets and Build color cache
207 $countSheets = $spreadsheet->getSheetCount();
208 for ($i = 0; $i < $countSheets; ++$i) {
209 $phpSheet = $spreadsheet->getSheet($i);
210
211 $this->parser->setExtSheet($phpSheet->getTitle(), $i); // Register worksheet name with parser
212
213 $supbook_index = 0x00;
214 $ref = pack('vvv', $supbook_index, $i, $i);
215 $this->parser->references[] = $ref; // Register reference with parser
216
217 // Sheet tab colors?
218 if ($phpSheet->isTabColorSet()) {
219 $this->addColor($phpSheet->getTabColor()->getRGB());
220 }
221 }
222 }
223
231 public function addXfWriter(Style $style, $isStyleXf = false)
232 {
233 $xfWriter = new Xf($style);
234 $xfWriter->setIsStyleXf($isStyleXf);
235
236 // Add the font if not already added
237 $fontIndex = $this->addFont($style->getFont());
238
239 // Assign the font index to the xf record
240 $xfWriter->setFontIndex($fontIndex);
241
242 // Background colors, best to treat these after the font so black will come after white in custom palette
243 $xfWriter->setFgColor($this->addColor($style->getFill()->getStartColor()->getRGB()));
244 $xfWriter->setBgColor($this->addColor($style->getFill()->getEndColor()->getRGB()));
245 $xfWriter->setBottomColor($this->addColor($style->getBorders()->getBottom()->getColor()->getRGB()));
246 $xfWriter->setTopColor($this->addColor($style->getBorders()->getTop()->getColor()->getRGB()));
247 $xfWriter->setRightColor($this->addColor($style->getBorders()->getRight()->getColor()->getRGB()));
248 $xfWriter->setLeftColor($this->addColor($style->getBorders()->getLeft()->getColor()->getRGB()));
249 $xfWriter->setDiagColor($this->addColor($style->getBorders()->getDiagonal()->getColor()->getRGB()));
250
251 // Add the number format if it is not a built-in one and not already added
252 if ($style->getNumberFormat()->getBuiltInFormatCode() === false) {
253 $numberFormatHashCode = $style->getNumberFormat()->getHashCode();
254
255 if (isset($this->addedNumberFormats[$numberFormatHashCode])) {
256 $numberFormatIndex = $this->addedNumberFormats[$numberFormatHashCode];
257 } else {
258 $numberFormatIndex = 164 + count($this->numberFormats);
259 $this->numberFormats[$numberFormatIndex] = $style->getNumberFormat();
260 $this->addedNumberFormats[$numberFormatHashCode] = $numberFormatIndex;
261 }
262 } else {
263 $numberFormatIndex = (int) $style->getNumberFormat()->getBuiltInFormatCode();
264 }
265
266 // Assign the number format index to xf record
267 $xfWriter->setNumberFormatIndex($numberFormatIndex);
268
269 $this->xfWriters[] = $xfWriter;
270
271 return count($this->xfWriters) - 1;
272 }
273
279 public function addFont(\PhpOffice\PhpSpreadsheet\Style\Font $font)
280 {
281 $fontHashCode = $font->getHashCode();
282 if (isset($this->addedFonts[$fontHashCode])) {
283 $fontIndex = $this->addedFonts[$fontHashCode];
284 } else {
285 $countFonts = count($this->fontWriters);
286 $fontIndex = ($countFonts < 4) ? $countFonts : $countFonts + 1;
287
288 $fontWriter = new Font($font);
289 $fontWriter->setColorIndex($this->addColor($font->getColor()->getRGB()));
290 $this->fontWriters[] = $fontWriter;
291
292 $this->addedFonts[$fontHashCode] = $fontIndex;
293 }
294
295 return $fontIndex;
296 }
297
305 private function addColor($rgb)
306 {
307 if (!isset($this->colors[$rgb])) {
308 $color =
309 [
310 hexdec(substr($rgb, 0, 2)),
311 hexdec(substr($rgb, 2, 2)),
312 hexdec(substr($rgb, 4)),
313 0,
314 ];
315 $colorIndex = array_search($color, $this->palette);
316 if ($colorIndex) {
317 $this->colors[$rgb] = $colorIndex;
318 } else {
319 if (count($this->colors) === 0) {
320 $lastColor = 7;
321 } else {
322 $lastColor = end($this->colors);
323 }
324 if ($lastColor < 57) {
325 // then we add a custom color altering the palette
326 $colorIndex = $lastColor + 1;
327 $this->palette[$colorIndex] = $color;
328 $this->colors[$rgb] = $colorIndex;
329 } else {
330 // no room for more custom colors, just map to black
331 $colorIndex = 0;
332 }
333 }
334 } else {
335 // fetch already added custom color
336 $colorIndex = $this->colors[$rgb];
337 }
338
339 return $colorIndex;
340 }
341
345 private function setPaletteXl97(): void
346 {
347 $this->palette = [
348 0x08 => [0x00, 0x00, 0x00, 0x00],
349 0x09 => [0xff, 0xff, 0xff, 0x00],
350 0x0A => [0xff, 0x00, 0x00, 0x00],
351 0x0B => [0x00, 0xff, 0x00, 0x00],
352 0x0C => [0x00, 0x00, 0xff, 0x00],
353 0x0D => [0xff, 0xff, 0x00, 0x00],
354 0x0E => [0xff, 0x00, 0xff, 0x00],
355 0x0F => [0x00, 0xff, 0xff, 0x00],
356 0x10 => [0x80, 0x00, 0x00, 0x00],
357 0x11 => [0x00, 0x80, 0x00, 0x00],
358 0x12 => [0x00, 0x00, 0x80, 0x00],
359 0x13 => [0x80, 0x80, 0x00, 0x00],
360 0x14 => [0x80, 0x00, 0x80, 0x00],
361 0x15 => [0x00, 0x80, 0x80, 0x00],
362 0x16 => [0xc0, 0xc0, 0xc0, 0x00],
363 0x17 => [0x80, 0x80, 0x80, 0x00],
364 0x18 => [0x99, 0x99, 0xff, 0x00],
365 0x19 => [0x99, 0x33, 0x66, 0x00],
366 0x1A => [0xff, 0xff, 0xcc, 0x00],
367 0x1B => [0xcc, 0xff, 0xff, 0x00],
368 0x1C => [0x66, 0x00, 0x66, 0x00],
369 0x1D => [0xff, 0x80, 0x80, 0x00],
370 0x1E => [0x00, 0x66, 0xcc, 0x00],
371 0x1F => [0xcc, 0xcc, 0xff, 0x00],
372 0x20 => [0x00, 0x00, 0x80, 0x00],
373 0x21 => [0xff, 0x00, 0xff, 0x00],
374 0x22 => [0xff, 0xff, 0x00, 0x00],
375 0x23 => [0x00, 0xff, 0xff, 0x00],
376 0x24 => [0x80, 0x00, 0x80, 0x00],
377 0x25 => [0x80, 0x00, 0x00, 0x00],
378 0x26 => [0x00, 0x80, 0x80, 0x00],
379 0x27 => [0x00, 0x00, 0xff, 0x00],
380 0x28 => [0x00, 0xcc, 0xff, 0x00],
381 0x29 => [0xcc, 0xff, 0xff, 0x00],
382 0x2A => [0xcc, 0xff, 0xcc, 0x00],
383 0x2B => [0xff, 0xff, 0x99, 0x00],
384 0x2C => [0x99, 0xcc, 0xff, 0x00],
385 0x2D => [0xff, 0x99, 0xcc, 0x00],
386 0x2E => [0xcc, 0x99, 0xff, 0x00],
387 0x2F => [0xff, 0xcc, 0x99, 0x00],
388 0x30 => [0x33, 0x66, 0xff, 0x00],
389 0x31 => [0x33, 0xcc, 0xcc, 0x00],
390 0x32 => [0x99, 0xcc, 0x00, 0x00],
391 0x33 => [0xff, 0xcc, 0x00, 0x00],
392 0x34 => [0xff, 0x99, 0x00, 0x00],
393 0x35 => [0xff, 0x66, 0x00, 0x00],
394 0x36 => [0x66, 0x66, 0x99, 0x00],
395 0x37 => [0x96, 0x96, 0x96, 0x00],
396 0x38 => [0x00, 0x33, 0x66, 0x00],
397 0x39 => [0x33, 0x99, 0x66, 0x00],
398 0x3A => [0x00, 0x33, 0x00, 0x00],
399 0x3B => [0x33, 0x33, 0x00, 0x00],
400 0x3C => [0x99, 0x33, 0x00, 0x00],
401 0x3D => [0x99, 0x33, 0x66, 0x00],
402 0x3E => [0x33, 0x33, 0x99, 0x00],
403 0x3F => [0x33, 0x33, 0x33, 0x00],
404 ];
405 }
406
415 public function writeWorkbook(array $pWorksheetSizes)
416 {
417 $this->worksheetSizes = $pWorksheetSizes;
418
419 // Calculate the number of selected worksheet tabs and call the finalization
420 // methods for each worksheet
421 $total_worksheets = $this->spreadsheet->getSheetCount();
422
423 // Add part 1 of the Workbook globals, what goes before the SHEET records
424 $this->storeBof(0x0005);
425 $this->writeCodepage();
426 $this->writeWindow1();
427
428 $this->writeDateMode();
429 $this->writeAllFonts();
430 $this->writeAllNumberFormats();
431 $this->writeAllXfs();
432 $this->writeAllStyles();
433 $this->writePalette();
434
435 // Prepare part 3 of the workbook global stream, what goes after the SHEET records
436 $part3 = '';
437 if ($this->countryCode !== -1) {
438 $part3 .= $this->writeCountry();
439 }
440 $part3 .= $this->writeRecalcId();
441
442 $part3 .= $this->writeSupbookInternal();
443 /* TODO: store external SUPBOOK records and XCT and CRN records
444 in case of external references for BIFF8 */
445 $part3 .= $this->writeExternalsheetBiff8();
446 $part3 .= $this->writeAllDefinedNamesBiff8();
447 $part3 .= $this->writeMsoDrawingGroup();
448 $part3 .= $this->writeSharedStringsTable();
449
450 $part3 .= $this->writeEof();
451
452 // Add part 2 of the Workbook globals, the SHEET records
453 $this->calcSheetOffsets();
454 for ($i = 0; $i < $total_worksheets; ++$i) {
455 $this->writeBoundSheet($this->spreadsheet->getSheet($i), $this->worksheetOffsets[$i]);
456 }
457
458 // Add part 3 of the Workbook globals
459 $this->_data .= $part3;
460
461 return $this->_data;
462 }
463
467 private function calcSheetOffsets(): void
468 {
469 $boundsheet_length = 10; // fixed length for a BOUNDSHEET record
470
471 // size of Workbook globals part 1 + 3
472 $offset = $this->_datasize;
473
474 // add size of Workbook globals part 2, the length of the SHEET records
475 $total_worksheets = count($this->spreadsheet->getAllSheets());
476 foreach ($this->spreadsheet->getWorksheetIterator() as $sheet) {
477 $offset += $boundsheet_length + strlen(StringHelper::UTF8toBIFF8UnicodeShort($sheet->getTitle()));
478 }
479
480 // add the sizes of each of the Sheet substreams, respectively
481 for ($i = 0; $i < $total_worksheets; ++$i) {
482 $this->worksheetOffsets[$i] = $offset;
483 $offset += $this->worksheetSizes[$i];
484 }
485 $this->biffSize = $offset;
486 }
487
491 private function writeAllFonts(): void
492 {
493 foreach ($this->fontWriters as $fontWriter) {
494 $this->append($fontWriter->writeFont());
495 }
496 }
497
501 private function writeAllNumberFormats(): void
502 {
503 foreach ($this->numberFormats as $numberFormatIndex => $numberFormat) {
504 $this->writeNumberFormat($numberFormat->getFormatCode(), $numberFormatIndex);
505 }
506 }
507
511 private function writeAllXfs(): void
512 {
513 foreach ($this->xfWriters as $xfWriter) {
514 $this->append($xfWriter->writeXf());
515 }
516 }
517
521 private function writeAllStyles(): void
522 {
523 $this->writeStyle();
524 }
525
526 private function parseDefinedNameValue(DefinedName $pDefinedName): string
527 {
528 $definedRange = $pDefinedName->getValue();
529 $splitCount = preg_match_all(
531 $definedRange,
532 $splitRanges,
533 PREG_OFFSET_CAPTURE
534 );
535
536 $lengths = array_map('strlen', array_column($splitRanges[0], 0));
537 $offsets = array_column($splitRanges[0], 1);
538
539 $worksheets = $splitRanges[2];
540 $columns = $splitRanges[6];
541 $rows = $splitRanges[7];
542
543 while ($splitCount > 0) {
544 --$splitCount;
545 $length = $lengths[$splitCount];
546 $offset = $offsets[$splitCount];
547 $worksheet = $worksheets[$splitCount][0];
548 $column = $columns[$splitCount][0];
549 $row = $rows[$splitCount][0];
550
551 $newRange = '';
552 if (empty($worksheet)) {
553 if (($offset === 0) || ($definedRange[$offset - 1] !== ':')) {
554 // We should have a worksheet
555 $worksheet = $pDefinedName->getWorksheet() ? $pDefinedName->getWorksheet()->getTitle() : null;
556 }
557 } else {
558 $worksheet = str_replace("''", "'", trim($worksheet, "'"));
559 }
560 if (!empty($worksheet)) {
561 $newRange = "'" . str_replace("'", "''", $worksheet) . "'!";
562 }
563
564 if (!empty($column)) {
565 $newRange .= "\${$column}";
566 }
567 if (!empty($row)) {
568 $newRange .= "\${$row}";
569 }
570
571 $definedRange = substr($definedRange, 0, $offset) . $newRange . substr($definedRange, $offset + $length);
572 }
573
574 return $definedRange;
575 }
576
581 private function writeAllDefinedNamesBiff8()
582 {
583 $chunk = '';
584
585 // Named ranges
586 $definedNames = $this->spreadsheet->getDefinedNames();
587 if (count($definedNames) > 0) {
588 // Loop named ranges
589 foreach ($definedNames as $definedName) {
590 $range = $this->parseDefinedNameValue($definedName);
591
592 // parse formula
593 try {
594 $error = $this->parser->parse($range);
595 $formulaData = $this->parser->toReversePolish();
596
597 // make sure tRef3d is of type tRef3dR (0x3A)
598 if (isset($formulaData[0]) && ($formulaData[0] == "\x7A" || $formulaData[0] == "\x5A")) {
599 $formulaData = "\x3A" . substr($formulaData, 1);
600 }
601
602 if ($definedName->getLocalOnly()) {
603 // local scope
604 $scope = $this->spreadsheet->getIndex($definedName->getScope()) + 1;
605 } else {
606 // global scope
607 $scope = 0;
608 }
609 $chunk .= $this->writeData($this->writeDefinedNameBiff8($definedName->getName(), $formulaData, $scope, false));
610 } catch (PhpSpreadsheetException $e) {
611 // do nothing
612 }
613 }
614 }
615
616 // total number of sheets
617 $total_worksheets = $this->spreadsheet->getSheetCount();
618
619 // write the print titles (repeating rows, columns), if any
620 for ($i = 0; $i < $total_worksheets; ++$i) {
621 $sheetSetup = $this->spreadsheet->getSheet($i)->getPageSetup();
622 // simultaneous repeatColumns repeatRows
623 if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) {
624 $repeat = $sheetSetup->getColumnsToRepeatAtLeft();
627
628 $repeat = $sheetSetup->getRowsToRepeatAtTop();
629 $rowmin = $repeat[0] - 1;
630 $rowmax = $repeat[1] - 1;
631
632 // construct formula data manually
633 $formulaData = pack('Cv', 0x29, 0x17); // tMemFunc
634 $formulaData .= pack('Cvvvvv', 0x3B, $i, 0, 65535, $colmin, $colmax); // tArea3d
635 $formulaData .= pack('Cvvvvv', 0x3B, $i, $rowmin, $rowmax, 0, 255); // tArea3d
636 $formulaData .= pack('C', 0x10); // tList
637
638 // store the DEFINEDNAME record
639 $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x07), $formulaData, $i + 1, true));
640
641 // (exclusive) either repeatColumns or repeatRows
642 } elseif ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) {
643 // Columns to repeat
644 if ($sheetSetup->isColumnsToRepeatAtLeftSet()) {
645 $repeat = $sheetSetup->getColumnsToRepeatAtLeft();
648 } else {
649 $colmin = 0;
650 $colmax = 255;
651 }
652 // Rows to repeat
653 if ($sheetSetup->isRowsToRepeatAtTopSet()) {
654 $repeat = $sheetSetup->getRowsToRepeatAtTop();
655 $rowmin = $repeat[0] - 1;
656 $rowmax = $repeat[1] - 1;
657 } else {
658 $rowmin = 0;
659 $rowmax = 65535;
660 }
661
662 // construct formula data manually because parser does not recognize absolute 3d cell references
663 $formulaData = pack('Cvvvvv', 0x3B, $i, $rowmin, $rowmax, $colmin, $colmax);
664
665 // store the DEFINEDNAME record
666 $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x07), $formulaData, $i + 1, true));
667 }
668 }
669
670 // write the print areas, if any
671 for ($i = 0; $i < $total_worksheets; ++$i) {
672 $sheetSetup = $this->spreadsheet->getSheet($i)->getPageSetup();
673 if ($sheetSetup->isPrintAreaSet()) {
674 // Print area, e.g. A3:J6,H1:X20
675 $printArea = Coordinate::splitRange($sheetSetup->getPrintArea());
676 $countPrintArea = count($printArea);
677
678 $formulaData = '';
679 for ($j = 0; $j < $countPrintArea; ++$j) {
680 $printAreaRect = $printArea[$j]; // e.g. A3:J6
681 $printAreaRect[0] = Coordinate::indexesFromString($printAreaRect[0]);
682 $printAreaRect[1] = Coordinate::indexesFromString($printAreaRect[1]);
683
684 $print_rowmin = $printAreaRect[0][1] - 1;
685 $print_rowmax = $printAreaRect[1][1] - 1;
686 $print_colmin = $printAreaRect[0][0] - 1;
687 $print_colmax = $printAreaRect[1][0] - 1;
688
689 // construct formula data manually because parser does not recognize absolute 3d cell references
690 $formulaData .= pack('Cvvvvv', 0x3B, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax);
691
692 if ($j > 0) {
693 $formulaData .= pack('C', 0x10); // list operator token ','
694 }
695 }
696
697 // store the DEFINEDNAME record
698 $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x06), $formulaData, $i + 1, true));
699 }
700 }
701
702 // write autofilters, if any
703 for ($i = 0; $i < $total_worksheets; ++$i) {
704 $sheetAutoFilter = $this->spreadsheet->getSheet($i)->getAutoFilter();
705 $autoFilterRange = $sheetAutoFilter->getRange();
706 if (!empty($autoFilterRange)) {
707 $rangeBounds = Coordinate::rangeBoundaries($autoFilterRange);
708
709 //Autofilter built in name
710 $name = pack('C', 0x0D);
711
712 $chunk .= $this->writeData($this->writeShortNameBiff8($name, $i + 1, $rangeBounds, true));
713 }
714 }
715
716 return $chunk;
717 }
718
729 private function writeDefinedNameBiff8($name, $formulaData, $sheetIndex = 0, $isBuiltIn = false)
730 {
731 $record = 0x0018;
732
733 // option flags
734 $options = $isBuiltIn ? 0x20 : 0x00;
735
736 // length of the name, character count
738
739 // name with stripped length field
741
742 // size of the formula (in bytes)
743 $sz = strlen($formulaData);
744
745 // combine the parts
746 $data = pack('vCCvvvCCCC', $options, 0, $nlen, $sz, 0, $sheetIndex, 0, 0, 0, 0)
747 . $name . $formulaData;
748 $length = strlen($data);
749
750 $header = pack('vv', $record, $length);
751
752 return $header . $data;
753 }
754
765 private function writeShortNameBiff8($name, $sheetIndex, $rangeBounds, $isHidden = false)
766 {
767 $record = 0x0018;
768
769 // option flags
770 $options = ($isHidden ? 0x21 : 0x00);
771
772 $extra = pack(
773 'Cvvvvv',
774 0x3B,
775 $sheetIndex - 1,
776 $rangeBounds[0][1] - 1,
777 $rangeBounds[1][1] - 1,
778 $rangeBounds[0][0] - 1,
779 $rangeBounds[1][0] - 1
780 );
781
782 // size of the formula (in bytes)
783 $sz = strlen($extra);
784
785 // combine the parts
786 $data = pack('vCCvvvCCCCC', $options, 0, 1, $sz, 0, $sheetIndex, 0, 0, 0, 0, 0)
787 . $name . $extra;
788 $length = strlen($data);
789
790 $header = pack('vv', $record, $length);
791
792 return $header . $data;
793 }
794
798 private function writeCodepage(): void
799 {
800 $record = 0x0042; // Record identifier
801 $length = 0x0002; // Number of bytes to follow
802 $cv = $this->codepage; // The code page
803
804 $header = pack('vv', $record, $length);
805 $data = pack('v', $cv);
806
807 $this->append($header . $data);
808 }
809
813 private function writeWindow1(): void
814 {
815 $record = 0x003D; // Record identifier
816 $length = 0x0012; // Number of bytes to follow
817
818 $xWn = 0x0000; // Horizontal position of window
819 $yWn = 0x0000; // Vertical position of window
820 $dxWn = 0x25BC; // Width of window
821 $dyWn = 0x1572; // Height of window
822
823 $grbit = 0x0038; // Option flags
824
825 // not supported by PhpSpreadsheet, so there is only one selected sheet, the active
826 $ctabsel = 1; // Number of workbook tabs selected
827
828 $wTabRatio = 0x0258; // Tab to scrollbar ratio
829
830 // not supported by PhpSpreadsheet, set to 0
831 $itabFirst = 0; // 1st displayed worksheet
832 $itabCur = $this->spreadsheet->getActiveSheetIndex(); // Active worksheet
833
834 $header = pack('vv', $record, $length);
835 $data = pack('vvvvvvvvv', $xWn, $yWn, $dxWn, $dyWn, $grbit, $itabCur, $itabFirst, $ctabsel, $wTabRatio);
836 $this->append($header . $data);
837 }
838
844 private function writeBoundSheet(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet, $offset): void
845 {
846 $sheetname = $sheet->getTitle();
847 $record = 0x0085; // Record identifier
848
849 // sheet state
850 switch ($sheet->getSheetState()) {
851 case \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_VISIBLE:
852 $ss = 0x00;
853
854 break;
855 case \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_HIDDEN:
856 $ss = 0x01;
857
858 break;
859 case \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_VERYHIDDEN:
860 $ss = 0x02;
861
862 break;
863 default:
864 $ss = 0x00;
865
866 break;
867 }
868
869 // sheet type
870 $st = 0x00;
871
872 $grbit = 0x0000; // Visibility and sheet type
873
874 $data = pack('VCC', $offset, $ss, $st);
876
877 $length = strlen($data);
878 $header = pack('vv', $record, $length);
879 $this->append($header . $data);
880 }
881
885 private function writeSupbookInternal()
886 {
887 $record = 0x01AE; // Record identifier
888 $length = 0x0004; // Bytes to follow
889
890 $header = pack('vv', $record, $length);
891 $data = pack('vv', $this->spreadsheet->getSheetCount(), 0x0401);
892
893 return $this->writeData($header . $data);
894 }
895
900 private function writeExternalsheetBiff8()
901 {
902 $totalReferences = count($this->parser->references);
903 $record = 0x0017; // Record identifier
904 $length = 2 + 6 * $totalReferences; // Number of bytes to follow
905
906 $supbook_index = 0; // FIXME: only using internal SUPBOOK record
907 $header = pack('vv', $record, $length);
908 $data = pack('v', $totalReferences);
909 for ($i = 0; $i < $totalReferences; ++$i) {
910 $data .= $this->parser->references[$i];
911 }
912
913 return $this->writeData($header . $data);
914 }
915
919 private function writeStyle(): void
920 {
921 $record = 0x0293; // Record identifier
922 $length = 0x0004; // Bytes to follow
923
924 $ixfe = 0x8000; // Index to cell style XF
925 $BuiltIn = 0x00; // Built-in style
926 $iLevel = 0xff; // Outline style level
927
928 $header = pack('vv', $record, $length);
929 $data = pack('vCC', $ixfe, $BuiltIn, $iLevel);
930 $this->append($header . $data);
931 }
932
939 private function writeNumberFormat($format, $ifmt): void
940 {
941 $record = 0x041E; // Record identifier
942
943 $numberFormatString = StringHelper::UTF8toBIFF8UnicodeLong($format);
944 $length = 2 + strlen($numberFormatString); // Number of bytes to follow
945
946 $header = pack('vv', $record, $length);
947 $data = pack('v', $ifmt) . $numberFormatString;
948 $this->append($header . $data);
949 }
950
954 private function writeDateMode(): void
955 {
956 $record = 0x0022; // Record identifier
957 $length = 0x0002; // Bytes to follow
958
960 ? 1
961 : 0; // Flag for 1904 date system
962
963 $header = pack('vv', $record, $length);
964 $data = pack('v', $f1904);
965 $this->append($header . $data);
966 }
967
973 private function writeCountry()
974 {
975 $record = 0x008C; // Record identifier
976 $length = 4; // Number of bytes to follow
977
978 $header = pack('vv', $record, $length);
979 // using the same country code always for simplicity
980 $data = pack('vv', $this->countryCode, $this->countryCode);
981
982 return $this->writeData($header . $data);
983 }
984
990 private function writeRecalcId()
991 {
992 $record = 0x01C1; // Record identifier
993 $length = 8; // Number of bytes to follow
994
995 $header = pack('vv', $record, $length);
996
997 // by inspection of real Excel files, MS Office Excel 2007 writes this
998 $data = pack('VV', 0x000001C1, 0x00001E667);
999
1000 return $this->writeData($header . $data);
1001 }
1002
1006 private function writePalette(): void
1007 {
1008 $aref = $this->palette;
1009
1010 $record = 0x0092; // Record identifier
1011 $length = 2 + 4 * count($aref); // Number of bytes to follow
1012 $ccv = count($aref); // Number of RGB values to follow
1013 $data = ''; // The RGB data
1014
1015 // Pack the RGB data
1016 foreach ($aref as $color) {
1017 foreach ($color as $byte) {
1018 $data .= pack('C', $byte);
1019 }
1020 }
1021
1022 $header = pack('vvv', $record, $length, $ccv);
1023 $this->append($header . $data);
1024 }
1025
1040 private function writeSharedStringsTable()
1041 {
1042 // maximum size of record data (excluding record header)
1043 $continue_limit = 8224;
1044
1045 // initialize array of record data blocks
1046 $recordDatas = [];
1047
1048 // start SST record data block with total number of strings, total number of unique strings
1049 $recordData = pack('VV', $this->stringTotal, $this->stringUnique);
1050
1051 // loop through all (unique) strings in shared strings table
1052 foreach (array_keys($this->stringTable) as $string) {
1053 // here $string is a BIFF8 encoded string
1054
1055 // length = character count
1056 $headerinfo = unpack('vlength/Cencoding', $string);
1057
1058 // currently, this is always 1 = uncompressed
1059 $encoding = $headerinfo['encoding'];
1060
1061 // initialize finished writing current $string
1062 $finished = false;
1063
1064 while ($finished === false) {
1065 // normally, there will be only one cycle, but if string cannot immediately be written as is
1066 // there will be need for more than one cylcle, if string longer than one record data block, there
1067 // may be need for even more cycles
1068
1069 if (strlen($recordData) + strlen($string) <= $continue_limit) {
1070 // then we can write the string (or remainder of string) without any problems
1071 $recordData .= $string;
1072
1073 if (strlen($recordData) + strlen($string) == $continue_limit) {
1074 // we close the record data block, and initialize a new one
1075 $recordDatas[] = $recordData;
1076 $recordData = '';
1077 }
1078
1079 // we are finished writing this string
1080 $finished = true;
1081 } else {
1082 // special treatment writing the string (or remainder of the string)
1083 // If the string is very long it may need to be written in more than one CONTINUE record.
1084
1085 // check how many bytes more there is room for in the current record
1086 $space_remaining = $continue_limit - strlen($recordData);
1087
1088 // minimum space needed
1089 // uncompressed: 2 byte string length length field + 1 byte option flags + 2 byte character
1090 // compressed: 2 byte string length length field + 1 byte option flags + 1 byte character
1091 $min_space_needed = ($encoding == 1) ? 5 : 4;
1092
1093 // We have two cases
1094 // 1. space remaining is less than minimum space needed
1095 // here we must waste the space remaining and move to next record data block
1096 // 2. space remaining is greater than or equal to minimum space needed
1097 // here we write as much as we can in the current block, then move to next record data block
1098
1099 // 1. space remaining is less than minimum space needed
1100 if ($space_remaining < $min_space_needed) {
1101 // we close the block, store the block data
1102 $recordDatas[] = $recordData;
1103
1104 // and start new record data block where we start writing the string
1105 $recordData = '';
1106
1107 // 2. space remaining is greater than or equal to minimum space needed
1108 } else {
1109 // initialize effective remaining space, for Unicode strings this may need to be reduced by 1, see below
1110 $effective_space_remaining = $space_remaining;
1111
1112 // for uncompressed strings, sometimes effective space remaining is reduced by 1
1113 if ($encoding == 1 && (strlen($string) - $space_remaining) % 2 == 1) {
1114 --$effective_space_remaining;
1115 }
1116
1117 // one block fininshed, store the block data
1118 $recordData .= substr($string, 0, $effective_space_remaining);
1119
1120 $string = substr($string, $effective_space_remaining); // for next cycle in while loop
1121 $recordDatas[] = $recordData;
1122
1123 // start new record data block with the repeated option flags
1124 $recordData = pack('C', $encoding);
1125 }
1126 }
1127 }
1128 }
1129
1130 // Store the last record data block unless it is empty
1131 // if there was no need for any continue records, this will be the for SST record data block itself
1132 if (strlen($recordData) > 0) {
1133 $recordDatas[] = $recordData;
1134 }
1135
1136 // combine into one chunk with all the blocks SST, CONTINUE,...
1137 $chunk = '';
1138 foreach ($recordDatas as $i => $recordData) {
1139 // first block should have the SST record header, remaing should have CONTINUE header
1140 $record = ($i == 0) ? 0x00FC : 0x003C;
1141
1142 $header = pack('vv', $record, strlen($recordData));
1143 $data = $header . $recordData;
1144
1145 $chunk .= $this->writeData($data);
1146 }
1147
1148 return $chunk;
1149 }
1150
1154 private function writeMsoDrawingGroup()
1155 {
1156 // write the Escher stream if necessary
1157 if (isset($this->escher)) {
1158 $writer = new Escher($this->escher);
1159 $data = $writer->close();
1160
1161 $record = 0x00EB;
1162 $length = strlen($data);
1163 $header = pack('vv', $record, $length);
1164
1165 return $this->writeData($header . $data);
1166 }
1167
1168 return '';
1169 }
1170
1176 public function getEscher()
1177 {
1178 return $this->escher;
1179 }
1180
1186 public function setEscher(?\PhpOffice\PhpSpreadsheet\Shared\Escher $pValue = null): void
1187 {
1188 $this->escher = $pValue;
1189 }
1190}
if(! $in) $columns
Definition: Utf8Test.php:45
An exception for terminatinating execution or to throw for unit testing.
Helper class to manipulate cell coordinates.
Definition: Coordinate.php:15
static indexesFromString(string $coordinates)
Get indexes from a string coordinates.
Definition: Coordinate.php:52
static columnIndexFromString($pString)
Column index from string.
Definition: Coordinate.php:265
static splitRange($pRange)
Split range into coordinate strings.
Definition: Coordinate.php:140
static rangeBoundaries($pRange)
Calculate range boundaries.
Definition: Coordinate.php:187
getValue()
Get range or formula value.
static getExcelCalendar()
Return the Excel calendar (Windows 1900 or Mac 1904).
Definition: Date.php:92
static countCharacters($value, $enc='UTF-8')
Get character count.
static UTF8toBIFF8UnicodeShort($value, $arrcRuns=[])
Converts a UTF-8 string into BIFF8 Unicode string data (8-bit string length) Writes the string using ...
static UTF8toBIFF8UnicodeLong($value)
Converts a UTF-8 string into BIFF8 Unicode string data (16-bit string length) Writes the string using...
storeBof($type)
Writes Excel BOF record to indicate the beginning of a stream or sub-stream in the BIFF file.
Definition: BIFFwriter.php:145
writeEof()
Writes Excel EOF record to indicate the end of a BIFF stream.
Definition: BIFFwriter.php:178
append($data)
General storage function.
Definition: BIFFwriter.php:112
writeData($data)
General storage function like append, but returns string instead of modifying $this->_data.
Definition: BIFFwriter.php:128
writeCodepage()
Stores the CODEPAGE biff record.
Definition: Workbook.php:798
addColor($rgb)
Alter color palette adding a custom color.
Definition: Workbook.php:305
addXfWriter(Style $style, $isStyleXf=false)
Add a new XF writer.
Definition: Workbook.php:231
writeAllNumberFormats()
Store user defined numerical formats i.e.
Definition: Workbook.php:501
writeMsoDrawingGroup()
Writes the MSODRAWINGGROUP record if needed.
Definition: Workbook.php:1154
writeAllFonts()
Store the Excel FONT records.
Definition: Workbook.php:491
writeAllStyles()
Write all STYLE records.
Definition: Workbook.php:521
writeRecalcId()
Write the RECALCID record.
Definition: Workbook.php:990
writeBoundSheet(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet, $offset)
Writes Excel BIFF BOUNDSHEET record.
Definition: Workbook.php:844
calcSheetOffsets()
Calculate offsets for Worksheet BOF records.
Definition: Workbook.php:467
writeAllDefinedNamesBiff8()
Writes all the DEFINEDNAME records (BIFF8).
Definition: Workbook.php:581
parseDefinedNameValue(DefinedName $pDefinedName)
Definition: Workbook.php:526
writeExternalsheetBiff8()
Writes the Excel BIFF EXTERNSHEET record.
Definition: Workbook.php:900
writeNumberFormat($format, $ifmt)
Writes Excel FORMAT record for non "built-in" numerical formats.
Definition: Workbook.php:939
writeDefinedNameBiff8($name, $formulaData, $sheetIndex=0, $isBuiltIn=false)
Write a DEFINEDNAME record for BIFF8 using explicit binary formula data.
Definition: Workbook.php:729
setPaletteXl97()
Sets the colour palette to the Excel 97+ default.
Definition: Workbook.php:345
addFont(\PhpOffice\PhpSpreadsheet\Style\Font $font)
Add a font to added fonts.
Definition: Workbook.php:279
writeCountry()
Stores the COUNTRY record for localization.
Definition: Workbook.php:973
writeDateMode()
Write DATEMODE record to indicate the date system in use (1904 or 1900).
Definition: Workbook.php:954
writeWorkbook(array $pWorksheetSizes)
Assemble worksheets into a workbook and send the BIFF data to an OLE storage.
Definition: Workbook.php:415
writeWindow1()
Write Excel BIFF WINDOW1 record.
Definition: Workbook.php:813
writeSharedStringsTable()
Handling of the SST continue blocks is complicated by the need to include an additional continuation ...
Definition: Workbook.php:1040
writePalette()
Stores the PALETTE biff record.
Definition: Workbook.php:1006
writeStyle()
Write Excel BIFF STYLE records.
Definition: Workbook.php:919
writeShortNameBiff8($name, $sheetIndex, $rangeBounds, $isHidden=false)
Write a short NAME record.
Definition: Workbook.php:765
__construct(Spreadsheet $spreadsheet, &$str_total, &$str_unique, &$str_table, &$colors, Parser $parser)
Class constructor.
Definition: Workbook.php:186
setEscher(?\PhpOffice\PhpSpreadsheet\Shared\Escher $pValue=null)
Set Escher object.
Definition: Workbook.php:1186
writeSupbookInternal()
Write Internal SUPBOOK record.
Definition: Workbook.php:885
$i
Definition: disco.tpl.php:19
$style
Definition: example_012.php:70
$format
Definition: metadata.php:141
$row
$data
Definition: bench.php:6
$rows
Definition: xhr_table.php:10