1<?php
2
3namespace PhpOffice\PhpSpreadsheet\Writer\Xls;
4
5use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
6use PhpOffice\PhpSpreadsheet\Cell\DataType;
7use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
8use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
9use PhpOffice\PhpSpreadsheet\RichText\RichText;
10use PhpOffice\PhpSpreadsheet\RichText\Run;
11use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
12use PhpOffice\PhpSpreadsheet\Shared\Xls;
13use PhpOffice\PhpSpreadsheet\Style\Alignment;
14use PhpOffice\PhpSpreadsheet\Style\Border;
15use PhpOffice\PhpSpreadsheet\Style\Color;
16use PhpOffice\PhpSpreadsheet\Style\Conditional;
17use PhpOffice\PhpSpreadsheet\Style\Fill;
18use PhpOffice\PhpSpreadsheet\Style\Protection;
19use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
20use PhpOffice\PhpSpreadsheet\Worksheet\SheetView;
21use PhpOffice\PhpSpreadsheet\Writer\Exception as WriterException;
22
23// Original file header of PEAR::Spreadsheet_Excel_Writer_Worksheet (used as the base for this class):
24// -----------------------------------------------------------------------------------------
25// /*
26// *  Module written/ported by Xavier Noguer <xnoguer@rezebra.com>
27// *
28// *  The majority of this is _NOT_ my code.  I simply ported it from the
29// *  PERL Spreadsheet::WriteExcel module.
30// *
31// *  The author of the Spreadsheet::WriteExcel module is John McNamara
32// *  <jmcnamara@cpan.org>
33// *
34// *  I _DO_ maintain this code, and John McNamara has nothing to do with the
35// *  porting of this code to PHP.  Any questions directly related to this
36// *  class library should be directed to me.
37// *
38// *  License Information:
39// *
40// *    Spreadsheet_Excel_Writer:  A library for generating Excel Spreadsheets
41// *    Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com
42// *
43// *    This library is free software; you can redistribute it and/or
44// *    modify it under the terms of the GNU Lesser General Public
45// *    License as published by the Free Software Foundation; either
46// *    version 2.1 of the License, or (at your option) any later version.
47// *
48// *    This library is distributed in the hope that it will be useful,
49// *    but WITHOUT ANY WARRANTY; without even the implied warranty of
50// *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
51// *    Lesser General Public License for more details.
52// *
53// *    You should have received a copy of the GNU Lesser General Public
54// *    License along with this library; if not, write to the Free Software
55// *    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
56// */
57class Worksheet extends BIFFwriter
58{
59    /**
60     * Formula parser.
61     *
62     * @var \PhpOffice\PhpSpreadsheet\Writer\Xls\Parser
63     */
64    private $parser;
65
66    /**
67     * Maximum number of characters for a string (LABEL record in BIFF5).
68     *
69     * @var int
70     */
71    private $xlsStringMaxLength;
72
73    /**
74     * Array containing format information for columns.
75     *
76     * @var array
77     */
78    private $columnInfo;
79
80    /**
81     * Array containing the selected area for the worksheet.
82     *
83     * @var array
84     */
85    private $selection;
86
87    /**
88     * The active pane for the worksheet.
89     *
90     * @var int
91     */
92    private $activePane;
93
94    /**
95     * Whether to use outline.
96     *
97     * @var int
98     */
99    private $outlineOn;
100
101    /**
102     * Auto outline styles.
103     *
104     * @var bool
105     */
106    private $outlineStyle;
107
108    /**
109     * Whether to have outline summary below.
110     *
111     * @var bool
112     */
113    private $outlineBelow;
114
115    /**
116     * Whether to have outline summary at the right.
117     *
118     * @var bool
119     */
120    private $outlineRight;
121
122    /**
123     * Reference to the total number of strings in the workbook.
124     *
125     * @var int
126     */
127    private $stringTotal;
128
129    /**
130     * Reference to the number of unique strings in the workbook.
131     *
132     * @var int
133     */
134    private $stringUnique;
135
136    /**
137     * Reference to the array containing all the unique strings in the workbook.
138     *
139     * @var array
140     */
141    private $stringTable;
142
143    /**
144     * Color cache.
145     */
146    private $colors;
147
148    /**
149     * Index of first used row (at least 0).
150     *
151     * @var int
152     */
153    private $firstRowIndex;
154
155    /**
156     * Index of last used row. (no used rows means -1).
157     *
158     * @var int
159     */
160    private $lastRowIndex;
161
162    /**
163     * Index of first used column (at least 0).
164     *
165     * @var int
166     */
167    private $firstColumnIndex;
168
169    /**
170     * Index of last used column (no used columns means -1).
171     *
172     * @var int
173     */
174    private $lastColumnIndex;
175
176    /**
177     * Sheet object.
178     *
179     * @var \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet
180     */
181    public $phpSheet;
182
183    /**
184     * Count cell style Xfs.
185     *
186     * @var int
187     */
188    private $countCellStyleXfs;
189
190    /**
191     * Escher object corresponding to MSODRAWING.
192     *
193     * @var \PhpOffice\PhpSpreadsheet\Shared\Escher
194     */
195    private $escher;
196
197    /**
198     * Array of font hashes associated to FONT records index.
199     *
200     * @var array
201     */
202    public $fontHashIndex;
203
204    /**
205     * @var bool
206     */
207    private $preCalculateFormulas;
208
209    /**
210     * @var int
211     */
212    private $printHeaders;
213
214    /**
215     * Constructor.
216     *
217     * @param int $str_total Total number of strings
218     * @param int $str_unique Total number of unique strings
219     * @param array &$str_table String Table
220     * @param array &$colors Colour Table
221     * @param Parser $parser The formula parser created for the Workbook
222     * @param bool $preCalculateFormulas Flag indicating whether formulas should be calculated or just written
223     * @param \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $phpSheet The worksheet to write
224     */
225    public function __construct(&$str_total, &$str_unique, &$str_table, &$colors, Parser $parser, $preCalculateFormulas, \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $phpSheet)
226    {
227        // It needs to call its parent's constructor explicitly
228        parent::__construct();
229
230        $this->preCalculateFormulas = $preCalculateFormulas;
231        $this->stringTotal = &$str_total;
232        $this->stringUnique = &$str_unique;
233        $this->stringTable = &$str_table;
234        $this->colors = &$colors;
235        $this->parser = $parser;
236
237        $this->phpSheet = $phpSheet;
238
239        $this->xlsStringMaxLength = 255;
240        $this->columnInfo = [];
241        $this->selection = [0, 0, 0, 0];
242        $this->activePane = 3;
243
244        $this->printHeaders = 0;
245
246        $this->outlineStyle = 0;
247        $this->outlineBelow = 1;
248        $this->outlineRight = 1;
249        $this->outlineOn = 1;
250
251        $this->fontHashIndex = [];
252
253        // calculate values for DIMENSIONS record
254        $minR = 1;
255        $minC = 'A';
256
257        $maxR = $this->phpSheet->getHighestRow();
258        $maxC = $this->phpSheet->getHighestColumn();
259
260        // Determine lowest and highest column and row
261        $this->lastRowIndex = ($maxR > 65535) ? 65535 : $maxR;
262
263        $this->firstColumnIndex = Coordinate::columnIndexFromString($minC);
264        $this->lastColumnIndex = Coordinate::columnIndexFromString($maxC);
265
266//        if ($this->firstColumnIndex > 255) $this->firstColumnIndex = 255;
267        if ($this->lastColumnIndex > 255) {
268            $this->lastColumnIndex = 255;
269        }
270
271        $this->countCellStyleXfs = count($phpSheet->getParent()->getCellStyleXfCollection());
272    }
273
274    /**
275     * Add data to the beginning of the workbook (note the reverse order)
276     * and to the end of the workbook.
277     *
278     * @see \PhpOffice\PhpSpreadsheet\Writer\Xls\Workbook::storeWorkbook()
279     */
280    public function close()
281    {
282        $phpSheet = $this->phpSheet;
283
284        // Storing selected cells and active sheet because it changes while parsing cells with formulas.
285        $selectedCells = $this->phpSheet->getSelectedCells();
286        $activeSheetIndex = $this->phpSheet->getParent()->getActiveSheetIndex();
287
288        // Write BOF record
289        $this->storeBof(0x0010);
290
291        // Write PRINTHEADERS
292        $this->writePrintHeaders();
293
294        // Write PRINTGRIDLINES
295        $this->writePrintGridlines();
296
297        // Write GRIDSET
298        $this->writeGridset();
299
300        // Calculate column widths
301        $phpSheet->calculateColumnWidths();
302
303        // Column dimensions
304        if (($defaultWidth = $phpSheet->getDefaultColumnDimension()->getWidth()) < 0) {
305            $defaultWidth = \PhpOffice\PhpSpreadsheet\Shared\Font::getDefaultColumnWidthByFont($phpSheet->getParent()->getDefaultStyle()->getFont());
306        }
307
308        $columnDimensions = $phpSheet->getColumnDimensions();
309        $maxCol = $this->lastColumnIndex - 1;
310        for ($i = 0; $i <= $maxCol; ++$i) {
311            $hidden = 0;
312            $level = 0;
313            $xfIndex = 15; // there are 15 cell style Xfs
314
315            $width = $defaultWidth;
316
317            $columnLetter = Coordinate::stringFromColumnIndex($i + 1);
318            if (isset($columnDimensions[$columnLetter])) {
319                $columnDimension = $columnDimensions[$columnLetter];
320                if ($columnDimension->getWidth() >= 0) {
321                    $width = $columnDimension->getWidth();
322                }
323                $hidden = $columnDimension->getVisible() ? 0 : 1;
324                $level = $columnDimension->getOutlineLevel();
325                $xfIndex = $columnDimension->getXfIndex() + 15; // there are 15 cell style Xfs
326            }
327
328            // Components of columnInfo:
329            // $firstcol first column on the range
330            // $lastcol  last column on the range
331            // $width    width to set
332            // $xfIndex  The optional cell style Xf index to apply to the columns
333            // $hidden   The optional hidden atribute
334            // $level    The optional outline level
335            $this->columnInfo[] = [$i, $i, $width, $xfIndex, $hidden, $level];
336        }
337
338        // Write GUTS
339        $this->writeGuts();
340
341        // Write DEFAULTROWHEIGHT
342        $this->writeDefaultRowHeight();
343        // Write WSBOOL
344        $this->writeWsbool();
345        // Write horizontal and vertical page breaks
346        $this->writeBreaks();
347        // Write page header
348        $this->writeHeader();
349        // Write page footer
350        $this->writeFooter();
351        // Write page horizontal centering
352        $this->writeHcenter();
353        // Write page vertical centering
354        $this->writeVcenter();
355        // Write left margin
356        $this->writeMarginLeft();
357        // Write right margin
358        $this->writeMarginRight();
359        // Write top margin
360        $this->writeMarginTop();
361        // Write bottom margin
362        $this->writeMarginBottom();
363        // Write page setup
364        $this->writeSetup();
365        // Write sheet protection
366        $this->writeProtect();
367        // Write SCENPROTECT
368        $this->writeScenProtect();
369        // Write OBJECTPROTECT
370        $this->writeObjectProtect();
371        // Write sheet password
372        $this->writePassword();
373        // Write DEFCOLWIDTH record
374        $this->writeDefcol();
375
376        // Write the COLINFO records if they exist
377        if (!empty($this->columnInfo)) {
378            $colcount = count($this->columnInfo);
379            for ($i = 0; $i < $colcount; ++$i) {
380                $this->writeColinfo($this->columnInfo[$i]);
381            }
382        }
383        $autoFilterRange = $phpSheet->getAutoFilter()->getRange();
384        if (!empty($autoFilterRange)) {
385            // Write AUTOFILTERINFO
386            $this->writeAutoFilterInfo();
387        }
388
389        // Write sheet dimensions
390        $this->writeDimensions();
391
392        // Row dimensions
393        foreach ($phpSheet->getRowDimensions() as $rowDimension) {
394            $xfIndex = $rowDimension->getXfIndex() + 15; // there are 15 cellXfs
395            $this->writeRow($rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), $xfIndex, ($rowDimension->getVisible() ? '0' : '1'), $rowDimension->getOutlineLevel());
396        }
397
398        // Write Cells
399        foreach ($phpSheet->getCoordinates() as $coordinate) {
400            $cell = $phpSheet->getCell($coordinate);
401            $row = $cell->getRow() - 1;
402            $column = Coordinate::columnIndexFromString($cell->getColumn()) - 1;
403
404            // Don't break Excel break the code!
405            if ($row > 65535 || $column > 255) {
406                throw new WriterException('Rows or columns overflow! Excel5 has limit to 65535 rows and 255 columns. Use XLSX instead.');
407            }
408
409            // Write cell value
410            $xfIndex = $cell->getXfIndex() + 15; // there are 15 cell style Xfs
411
412            $cVal = $cell->getValue();
413            if ($cVal instanceof RichText) {
414                $arrcRun = [];
415                $str_len = StringHelper::countCharacters($cVal->getPlainText(), 'UTF-8');
416                $str_pos = 0;
417                $elements = $cVal->getRichTextElements();
418                foreach ($elements as $element) {
419                    // FONT Index
420                    if ($element instanceof Run) {
421                        $str_fontidx = $this->fontHashIndex[$element->getFont()->getHashCode()];
422                    } else {
423                        $str_fontidx = 0;
424                    }
425                    $arrcRun[] = ['strlen' => $str_pos, 'fontidx' => $str_fontidx];
426                    // Position FROM
427                    $str_pos += StringHelper::countCharacters($element->getText(), 'UTF-8');
428                }
429                $this->writeRichTextString($row, $column, $cVal->getPlainText(), $xfIndex, $arrcRun);
430            } else {
431                switch ($cell->getDatatype()) {
432                    case DataType::TYPE_STRING:
433                    case DataType::TYPE_NULL:
434                        if ($cVal === '' || $cVal === null) {
435                            $this->writeBlank($row, $column, $xfIndex);
436                        } else {
437                            $this->writeString($row, $column, $cVal, $xfIndex);
438                        }
439
440                        break;
441                    case DataType::TYPE_NUMERIC:
442                        $this->writeNumber($row, $column, $cVal, $xfIndex);
443
444                        break;
445                    case DataType::TYPE_FORMULA:
446                        $calculatedValue = $this->preCalculateFormulas ?
447                            $cell->getCalculatedValue() : null;
448                        if (self::WRITE_FORMULA_EXCEPTION == $this->writeFormula($row, $column, $cVal, $xfIndex, $calculatedValue)) {
449                            if ($calculatedValue === null) {
450                                $calculatedValue = $cell->getCalculatedValue();
451                            }
452                            $calctype = gettype($calculatedValue);
453                            switch ($calctype) {
454                                case 'integer':
455                                case 'double':
456                                    $this->writeNumber($row, $column, $calculatedValue, $xfIndex);
457
458                                    break;
459                                case 'string':
460                                    $this->writeString($row, $column, $calculatedValue, $xfIndex);
461
462                                    break;
463                                case 'boolean':
464                                    $this->writeBoolErr($row, $column, $calculatedValue, 0, $xfIndex);
465
466                                    break;
467                                default:
468                                    $this->writeString($row, $column, $cVal, $xfIndex);
469                            }
470                        }
471
472                        break;
473                    case DataType::TYPE_BOOL:
474                        $this->writeBoolErr($row, $column, $cVal, 0, $xfIndex);
475
476                        break;
477                    case DataType::TYPE_ERROR:
478                        $this->writeBoolErr($row, $column, self::mapErrorCode($cVal), 1, $xfIndex);
479
480                        break;
481                }
482            }
483        }
484
485        // Append
486        $this->writeMsoDrawing();
487
488        // Restoring active sheet.
489        $this->phpSheet->getParent()->setActiveSheetIndex($activeSheetIndex);
490
491        // Write WINDOW2 record
492        $this->writeWindow2();
493
494        // Write PLV record
495        $this->writePageLayoutView();
496
497        // Write ZOOM record
498        $this->writeZoom();
499        if ($phpSheet->getFreezePane()) {
500            $this->writePanes();
501        }
502
503        // Restoring selected cells.
504        $this->phpSheet->setSelectedCells($selectedCells);
505
506        // Write SELECTION record
507        $this->writeSelection();
508
509        // Write MergedCellsTable Record
510        $this->writeMergedCells();
511
512        // Hyperlinks
513        foreach ($phpSheet->getHyperLinkCollection() as $coordinate => $hyperlink) {
514            [$column, $row] = Coordinate::coordinateFromString($coordinate);
515
516            $url = $hyperlink->getUrl();
517
518            if (strpos($url, 'sheet://') !== false) {
519                // internal to current workbook
520                $url = str_replace('sheet://', 'internal:', $url);
521            } elseif (preg_match('/^(http:|https:|ftp:|mailto:)/', $url)) {
522                // URL
523            } else {
524                // external (local file)
525                $url = 'external:' . $url;
526            }
527
528            $this->writeUrl($row - 1, Coordinate::columnIndexFromString($column) - 1, $url);
529        }
530
531        $this->writeDataValidity();
532        $this->writeSheetLayout();
533
534        // Write SHEETPROTECTION record
535        $this->writeSheetProtection();
536        $this->writeRangeProtection();
537
538        $arrConditionalStyles = $phpSheet->getConditionalStylesCollection();
539        if (!empty($arrConditionalStyles)) {
540            $arrConditional = [];
541            // @todo CFRule & CFHeader
542            // Write CFHEADER record
543            $this->writeCFHeader();
544            // Write ConditionalFormattingTable records
545            foreach ($arrConditionalStyles as $cellCoordinate => $conditionalStyles) {
546                foreach ($conditionalStyles as $conditional) {
547                    if ($conditional->getConditionType() == Conditional::CONDITION_EXPRESSION
548                        || $conditional->getConditionType() == Conditional::CONDITION_CELLIS) {
549                        if (!isset($arrConditional[$conditional->getHashCode()])) {
550                            // This hash code has been handled
551                            $arrConditional[$conditional->getHashCode()] = true;
552
553                            // Write CFRULE record
554                            $this->writeCFRule($conditional);
555                        }
556                    }
557                }
558            }
559        }
560
561        $this->storeEof();
562    }
563
564    /**
565     * Write a cell range address in BIFF8
566     * always fixed range
567     * See section 2.5.14 in OpenOffice.org's Documentation of the Microsoft Excel File Format.
568     *
569     * @param string $range E.g. 'A1' or 'A1:B6'
570     *
571     * @return string Binary data
572     */
573    private function writeBIFF8CellRangeAddressFixed($range)
574    {
575        $explodes = explode(':', $range);
576
577        // extract first cell, e.g. 'A1'
578        $firstCell = $explodes[0];
579
580        // extract last cell, e.g. 'B6'
581        if (count($explodes) == 1) {
582            $lastCell = $firstCell;
583        } else {
584            $lastCell = $explodes[1];
585        }
586
587        $firstCellCoordinates = Coordinate::coordinateFromString($firstCell); // e.g. [0, 1]
588        $lastCellCoordinates = Coordinate::coordinateFromString($lastCell); // e.g. [1, 6]
589
590        return pack('vvvv', $firstCellCoordinates[1] - 1, $lastCellCoordinates[1] - 1, Coordinate::columnIndexFromString($firstCellCoordinates[0]) - 1, Coordinate::columnIndexFromString($lastCellCoordinates[0]) - 1);
591    }
592
593    /**
594     * Retrieves data from memory in one chunk, or from disk in $buffer
595     * sized chunks.
596     *
597     * @return string The data
598     */
599    public function getData()
600    {
601        $buffer = 4096;
602
603        // Return data stored in memory
604        if (isset($this->_data)) {
605            $tmp = $this->_data;
606            unset($this->_data);
607
608            return $tmp;
609        }
610        // No data to return
611        return false;
612    }
613
614    /**
615     * Set the option to print the row and column headers on the printed page.
616     *
617     * @param int $print Whether to print the headers or not. Defaults to 1 (print).
618     */
619    public function printRowColHeaders($print = 1)
620    {
621        $this->printHeaders = $print;
622    }
623
624    /**
625     * This method sets the properties for outlining and grouping. The defaults
626     * correspond to Excel's defaults.
627     *
628     * @param bool $visible
629     * @param bool $symbols_below
630     * @param bool $symbols_right
631     * @param bool $auto_style
632     */
633    public function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false)
634    {
635        $this->outlineOn = $visible;
636        $this->outlineBelow = $symbols_below;
637        $this->outlineRight = $symbols_right;
638        $this->outlineStyle = $auto_style;
639
640        // Ensure this is a boolean vale for Window2
641        if ($this->outlineOn) {
642            $this->outlineOn = 1;
643        }
644    }
645
646    /**
647     * Write a double to the specified row and column (zero indexed).
648     * An integer can be written as a double. Excel will display an
649     * integer. $format is optional.
650     *
651     * Returns  0 : normal termination
652     *         -2 : row or column out of range
653     *
654     * @param int $row Zero indexed row
655     * @param int $col Zero indexed column
656     * @param float $num The number to write
657     * @param mixed $xfIndex The optional XF format
658     *
659     * @return int
660     */
661    private function writeNumber($row, $col, $num, $xfIndex)
662    {
663        $record = 0x0203; // Record identifier
664        $length = 0x000E; // Number of bytes to follow
665
666        $header = pack('vv', $record, $length);
667        $data = pack('vvv', $row, $col, $xfIndex);
668        $xl_double = pack('d', $num);
669        if (self::getByteOrder()) { // if it's Big Endian
670            $xl_double = strrev($xl_double);
671        }
672
673        $this->append($header . $data . $xl_double);
674
675        return 0;
676    }
677
678    /**
679     * Write a LABELSST record or a LABEL record. Which one depends on BIFF version.
680     *
681     * @param int $row Row index (0-based)
682     * @param int $col Column index (0-based)
683     * @param string $str The string
684     * @param int $xfIndex Index to XF record
685     */
686    private function writeString($row, $col, $str, $xfIndex)
687    {
688        $this->writeLabelSst($row, $col, $str, $xfIndex);
689    }
690
691    /**
692     * Write a LABELSST record or a LABEL record. Which one depends on BIFF version
693     * It differs from writeString by the writing of rich text strings.
694     *
695     * @param int $row Row index (0-based)
696     * @param int $col Column index (0-based)
697     * @param string $str The string
698     * @param int $xfIndex The XF format index for the cell
699     * @param array $arrcRun Index to Font record and characters beginning
700     */
701    private function writeRichTextString($row, $col, $str, $xfIndex, $arrcRun)
702    {
703        $record = 0x00FD; // Record identifier
704        $length = 0x000A; // Bytes to follow
705        $str = StringHelper::UTF8toBIFF8UnicodeShort($str, $arrcRun);
706
707        // check if string is already present
708        if (!isset($this->stringTable[$str])) {
709            $this->stringTable[$str] = $this->stringUnique++;
710        }
711        ++$this->stringTotal;
712
713        $header = pack('vv', $record, $length);
714        $data = pack('vvvV', $row, $col, $xfIndex, $this->stringTable[$str]);
715        $this->append($header . $data);
716    }
717
718    /**
719     * Write a string to the specified row and column (zero indexed).
720     * This is the BIFF8 version (no 255 chars limit).
721     * $format is optional.
722     *
723     * @param int $row Zero indexed row
724     * @param int $col Zero indexed column
725     * @param string $str The string to write
726     * @param mixed $xfIndex The XF format index for the cell
727     */
728    private function writeLabelSst($row, $col, $str, $xfIndex)
729    {
730        $record = 0x00FD; // Record identifier
731        $length = 0x000A; // Bytes to follow
732
733        $str = StringHelper::UTF8toBIFF8UnicodeLong($str);
734
735        // check if string is already present
736        if (!isset($this->stringTable[$str])) {
737            $this->stringTable[$str] = $this->stringUnique++;
738        }
739        ++$this->stringTotal;
740
741        $header = pack('vv', $record, $length);
742        $data = pack('vvvV', $row, $col, $xfIndex, $this->stringTable[$str]);
743        $this->append($header . $data);
744    }
745
746    /**
747     * Write a blank cell to the specified row and column (zero indexed).
748     * A blank cell is used to specify formatting without adding a string
749     * or a number.
750     *
751     * A blank cell without a format serves no purpose. Therefore, we don't write
752     * a BLANK record unless a format is specified.
753     *
754     * Returns  0 : normal termination (including no format)
755     *         -1 : insufficient number of arguments
756     *         -2 : row or column out of range
757     *
758     * @param int $row Zero indexed row
759     * @param int $col Zero indexed column
760     * @param mixed $xfIndex The XF format index
761     *
762     * @return int
763     */
764    public function writeBlank($row, $col, $xfIndex)
765    {
766        $record = 0x0201; // Record identifier
767        $length = 0x0006; // Number of bytes to follow
768
769        $header = pack('vv', $record, $length);
770        $data = pack('vvv', $row, $col, $xfIndex);
771        $this->append($header . $data);
772
773        return 0;
774    }
775
776    /**
777     * Write a boolean or an error type to the specified row and column (zero indexed).
778     *
779     * @param int $row Row index (0-based)
780     * @param int $col Column index (0-based)
781     * @param int $value
782     * @param bool $isError Error or Boolean?
783     * @param int $xfIndex
784     *
785     * @return int
786     */
787    private function writeBoolErr($row, $col, $value, $isError, $xfIndex)
788    {
789        $record = 0x0205;
790        $length = 8;
791
792        $header = pack('vv', $record, $length);
793        $data = pack('vvvCC', $row, $col, $xfIndex, $value, $isError);
794        $this->append($header . $data);
795
796        return 0;
797    }
798
799    const WRITE_FORMULA_NORMAL = 0;
800    const WRITE_FORMULA_ERRORS = -1;
801    const WRITE_FORMULA_RANGE = -2;
802    const WRITE_FORMULA_EXCEPTION = -3;
803
804    /**
805     * Write a formula to the specified row and column (zero indexed).
806     * The textual representation of the formula is passed to the parser in
807     * Parser.php which returns a packed binary string.
808     *
809     * Returns  0 : WRITE_FORMULA_NORMAL  normal termination
810     *         -1 : WRITE_FORMULA_ERRORS formula errors (bad formula)
811     *         -2 : WRITE_FORMULA_RANGE  row or column out of range
812     *         -3 : WRITE_FORMULA_EXCEPTION parse raised exception, probably due to definedname
813     *
814     * @param int $row Zero indexed row
815     * @param int $col Zero indexed column
816     * @param string $formula The formula text string
817     * @param mixed $xfIndex The XF format index
818     * @param mixed $calculatedValue Calculated value
819     *
820     * @return int
821     */
822    private function writeFormula($row, $col, $formula, $xfIndex, $calculatedValue)
823    {
824        $record = 0x0006; // Record identifier
825
826        // Initialize possible additional value for STRING record that should be written after the FORMULA record?
827        $stringValue = null;
828
829        // calculated value
830        if (isset($calculatedValue)) {
831            // Since we can't yet get the data type of the calculated value,
832            // we use best effort to determine data type
833            if (is_bool($calculatedValue)) {
834                // Boolean value
835                $num = pack('CCCvCv', 0x01, 0x00, (int) $calculatedValue, 0x00, 0x00, 0xFFFF);
836            } elseif (is_int($calculatedValue) || is_float($calculatedValue)) {
837                // Numeric value
838                $num = pack('d', $calculatedValue);
839            } elseif (is_string($calculatedValue)) {
840                $errorCodes = DataType::getErrorCodes();
841                if (isset($errorCodes[$calculatedValue])) {
842                    // Error value
843                    $num = pack('CCCvCv', 0x02, 0x00, self::mapErrorCode($calculatedValue), 0x00, 0x00, 0xFFFF);
844                } elseif ($calculatedValue === '') {
845                    // Empty string (and BIFF8)
846                    $num = pack('CCCvCv', 0x03, 0x00, 0x00, 0x00, 0x00, 0xFFFF);
847                } else {
848                    // Non-empty string value (or empty string BIFF5)
849                    $stringValue = $calculatedValue;
850                    $num = pack('CCCvCv', 0x00, 0x00, 0x00, 0x00, 0x00, 0xFFFF);
851                }
852            } else {
853                // We are really not supposed to reach here
854                $num = pack('d', 0x00);
855            }
856        } else {
857            $num = pack('d', 0x00);
858        }
859
860        $grbit = 0x03; // Option flags
861        $unknown = 0x0000; // Must be zero
862
863        // Strip the '=' or '@' sign at the beginning of the formula string
864        if ($formula[0] == '=') {
865            $formula = substr($formula, 1);
866        } else {
867            // Error handling
868            $this->writeString($row, $col, 'Unrecognised character for formula', 0);
869
870            return self::WRITE_FORMULA_ERRORS;
871        }
872
873        // Parse the formula using the parser in Parser.php
874        try {
875            $error = $this->parser->parse($formula);
876            $formula = $this->parser->toReversePolish();
877
878            $formlen = strlen($formula); // Length of the binary string
879            $length = 0x16 + $formlen; // Length of the record data
880
881            $header = pack('vv', $record, $length);
882
883            $data = pack('vvv', $row, $col, $xfIndex)
884                        . $num
885                        . pack('vVv', $grbit, $unknown, $formlen);
886            $this->append($header . $data . $formula);
887
888            // Append also a STRING record if necessary
889            if ($stringValue !== null) {
890                $this->writeStringRecord($stringValue);
891            }
892
893            return self::WRITE_FORMULA_NORMAL;
894        } catch (PhpSpreadsheetException $e) {
895            return self::WRITE_FORMULA_EXCEPTION;
896        }
897    }
898
899    /**
900     * Write a STRING record. This.
901     *
902     * @param string $stringValue
903     */
904    private function writeStringRecord($stringValue)
905    {
906        $record = 0x0207; // Record identifier
907        $data = StringHelper::UTF8toBIFF8UnicodeLong($stringValue);
908
909        $length = strlen($data);
910        $header = pack('vv', $record, $length);
911
912        $this->append($header . $data);
913    }
914
915    /**
916     * Write a hyperlink.
917     * This is comprised of two elements: the visible label and
918     * the invisible link. The visible label is the same as the link unless an
919     * alternative string is specified. The label is written using the
920     * writeString() method. Therefore the 255 characters string limit applies.
921     * $string and $format are optional.
922     *
923     * The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external
924     * directory url.
925     *
926     * Returns  0 : normal termination
927     *         -2 : row or column out of range
928     *         -3 : long string truncated to 255 chars
929     *
930     * @param int $row Row
931     * @param int $col Column
932     * @param string $url URL string
933     *
934     * @return int
935     */
936    private function writeUrl($row, $col, $url)
937    {
938        // Add start row and col to arg list
939        return $this->writeUrlRange($row, $col, $row, $col, $url);
940    }
941
942    /**
943     * This is the more general form of writeUrl(). It allows a hyperlink to be
944     * written to a range of cells. This function also decides the type of hyperlink
945     * to be written. These are either, Web (http, ftp, mailto), Internal
946     * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
947     *
948     * @see writeUrl()
949     *
950     * @param int $row1 Start row
951     * @param int $col1 Start column
952     * @param int $row2 End row
953     * @param int $col2 End column
954     * @param string $url URL string
955     *
956     * @return int
957     */
958    public function writeUrlRange($row1, $col1, $row2, $col2, $url)
959    {
960        // Check for internal/external sheet links or default to web link
961        if (preg_match('[^internal:]', $url)) {
962            return $this->writeUrlInternal($row1, $col1, $row2, $col2, $url);
963        }
964        if (preg_match('[^external:]', $url)) {
965            return $this->writeUrlExternal($row1, $col1, $row2, $col2, $url);
966        }
967
968        return $this->writeUrlWeb($row1, $col1, $row2, $col2, $url);
969    }
970
971    /**
972     * Used to write http, ftp and mailto hyperlinks.
973     * The link type ($options) is 0x03 is the same as absolute dir ref without
974     * sheet. However it is differentiated by the $unknown2 data stream.
975     *
976     * @see writeUrl()
977     *
978     * @param int $row1 Start row
979     * @param int $col1 Start column
980     * @param int $row2 End row
981     * @param int $col2 End column
982     * @param string $url URL string
983     *
984     * @return int
985     */
986    public function writeUrlWeb($row1, $col1, $row2, $col2, $url)
987    {
988        $record = 0x01B8; // Record identifier
989        $length = 0x00000; // Bytes to follow
990
991        // Pack the undocumented parts of the hyperlink stream
992        $unknown1 = pack('H*', 'D0C9EA79F9BACE118C8200AA004BA90B02000000');
993        $unknown2 = pack('H*', 'E0C9EA79F9BACE118C8200AA004BA90B');
994
995        // Pack the option flags
996        $options = pack('V', 0x03);
997
998        // Convert URL to a null terminated wchar string
999        $url = implode("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
1000        $url = $url . "\0\0\0";
1001
1002        // Pack the length of the URL
1003        $url_len = pack('V', strlen($url));
1004
1005        // Calculate the data length
1006        $length = 0x34 + strlen($url);
1007
1008        // Pack the header data
1009        $header = pack('vv', $record, $length);
1010        $data = pack('vvvv', $row1, $row2, $col1, $col2);
1011
1012        // Write the packed data
1013        $this->append($header . $data . $unknown1 . $options . $unknown2 . $url_len . $url);
1014
1015        return 0;
1016    }
1017
1018    /**
1019     * Used to write internal reference hyperlinks such as "Sheet1!A1".
1020     *
1021     * @see writeUrl()
1022     *
1023     * @param int $row1 Start row
1024     * @param int $col1 Start column
1025     * @param int $row2 End row
1026     * @param int $col2 End column
1027     * @param string $url URL string
1028     *
1029     * @return int
1030     */
1031    public function writeUrlInternal($row1, $col1, $row2, $col2, $url)
1032    {
1033        $record = 0x01B8; // Record identifier
1034        $length = 0x00000; // Bytes to follow
1035
1036        // Strip URL type
1037        $url = preg_replace('/^internal:/', '', $url);
1038
1039        // Pack the undocumented parts of the hyperlink stream
1040        $unknown1 = pack('H*', 'D0C9EA79F9BACE118C8200AA004BA90B02000000');
1041
1042        // Pack the option flags
1043        $options = pack('V', 0x08);
1044
1045        // Convert the URL type and to a null terminated wchar string
1046        $url .= "\0";
1047
1048        // character count
1049        $url_len = StringHelper::countCharacters($url);
1050        $url_len = pack('V', $url_len);
1051
1052        $url = StringHelper::convertEncoding($url, 'UTF-16LE', 'UTF-8');
1053
1054        // Calculate the data length
1055        $length = 0x24 + strlen($url);
1056
1057        // Pack the header data
1058        $header = pack('vv', $record, $length);
1059        $data = pack('vvvv', $row1, $row2, $col1, $col2);
1060
1061        // Write the packed data
1062        $this->append($header . $data . $unknown1 . $options . $url_len . $url);
1063
1064        return 0;
1065    }
1066
1067    /**
1068     * Write links to external directory names such as 'c:\foo.xls',
1069     * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
1070     *
1071     * Note: Excel writes some relative links with the $dir_long string. We ignore
1072     * these cases for the sake of simpler code.
1073     *
1074     * @see writeUrl()
1075     *
1076     * @param int $row1 Start row
1077     * @param int $col1 Start column
1078     * @param int $row2 End row
1079     * @param int $col2 End column
1080     * @param string $url URL string
1081     *
1082     * @return int
1083     */
1084    public function writeUrlExternal($row1, $col1, $row2, $col2, $url)
1085    {
1086        // Network drives are different. We will handle them separately
1087        // MS/Novell network drives and shares start with \\
1088        if (preg_match('[^external:\\\\]', $url)) {
1089            return; //($this->writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format));
1090        }
1091
1092        $record = 0x01B8; // Record identifier
1093        $length = 0x00000; // Bytes to follow
1094
1095        // Strip URL type and change Unix dir separator to Dos style (if needed)
1096        //
1097        $url = preg_replace('/^external:/', '', $url);
1098        $url = preg_replace('/\//', '\\', $url);
1099
1100        // Determine if the link is relative or absolute:
1101        //   relative if link contains no dir separator, "somefile.xls"
1102        //   relative if link starts with up-dir, "..\..\somefile.xls"
1103        //   otherwise, absolute
1104
1105        $absolute = 0x00; // relative path
1106        if (preg_match('/^[A-Z]:/', $url)) {
1107            $absolute = 0x02; // absolute path on Windows, e.g. C:\...
1108        }
1109        $link_type = 0x01 | $absolute;
1110
1111        // Determine if the link contains a sheet reference and change some of the
1112        // parameters accordingly.
1113        // Split the dir name and sheet name (if it exists)
1114        $dir_long = $url;
1115        if (preg_match('/\\#/', $url)) {
1116            $link_type |= 0x08;
1117        }
1118
1119        // Pack the link type
1120        $link_type = pack('V', $link_type);
1121
1122        // Calculate the up-level dir count e.g.. (..\..\..\ == 3)
1123        $up_count = preg_match_all('/\\.\\.\\\\/', $dir_long, $useless);
1124        $up_count = pack('v', $up_count);
1125
1126        // Store the short dos dir name (null terminated)
1127        $dir_short = preg_replace('/\\.\\.\\\\/', '', $dir_long) . "\0";
1128
1129        // Store the long dir name as a wchar string (non-null terminated)
1130        $dir_long = $dir_long . "\0";
1131
1132        // Pack the lengths of the dir strings
1133        $dir_short_len = pack('V', strlen($dir_short));
1134        $dir_long_len = pack('V', strlen($dir_long));
1135        $stream_len = pack('V', 0); //strlen($dir_long) + 0x06);
1136
1137        // Pack the undocumented parts of the hyperlink stream
1138        $unknown1 = pack('H*', 'D0C9EA79F9BACE118C8200AA004BA90B02000000');
1139        $unknown2 = pack('H*', '0303000000000000C000000000000046');
1140        $unknown3 = pack('H*', 'FFFFADDE000000000000000000000000000000000000000');
1141        $unknown4 = pack('v', 0x03);
1142
1143        // Pack the main data stream
1144        $data = pack('vvvv', $row1, $row2, $col1, $col2) .
1145                            $unknown1 .
1146                            $link_type .
1147                            $unknown2 .
1148                            $up_count .
1149                            $dir_short_len .
1150                            $dir_short .
1151                            $unknown3 .
1152                            $stream_len; /*.
1153                          $dir_long_len .
1154                          $unknown4     .
1155                          $dir_long     .
1156                          $sheet_len    .
1157                          $sheet        ;*/
1158
1159        // Pack the header data
1160        $length = strlen($data);
1161        $header = pack('vv', $record, $length);
1162
1163        // Write the packed data
1164        $this->append($header . $data);
1165
1166        return 0;
1167    }
1168
1169    /**
1170     * This method is used to set the height and format for a row.
1171     *
1172     * @param int $row The row to set
1173     * @param int $height Height we are giving to the row.
1174     *                        Use null to set XF without setting height
1175     * @param int $xfIndex The optional cell style Xf index to apply to the columns
1176     * @param bool $hidden The optional hidden attribute
1177     * @param int $level The optional outline level for row, in range [0,7]
1178     */
1179    private function writeRow($row, $height, $xfIndex, $hidden = false, $level = 0)
1180    {
1181        $record = 0x0208; // Record identifier
1182        $length = 0x0010; // Number of bytes to follow
1183
1184        $colMic = 0x0000; // First defined column
1185        $colMac = 0x0000; // Last defined column
1186        $irwMac = 0x0000; // Used by Excel to optimise loading
1187        $reserved = 0x0000; // Reserved
1188        $grbit = 0x0000; // Option flags
1189        $ixfe = $xfIndex;
1190
1191        if ($height < 0) {
1192            $height = null;
1193        }
1194
1195        // Use writeRow($row, null, $XF) to set XF format without setting height
1196        if ($height != null) {
1197            $miyRw = $height * 20; // row height
1198        } else {
1199            $miyRw = 0xff; // default row height is 256
1200        }
1201
1202        // Set the options flags. fUnsynced is used to show that the font and row
1203        // heights are not compatible. This is usually the case for WriteExcel.
1204        // The collapsed flag 0x10 doesn't seem to be used to indicate that a row
1205        // is collapsed. Instead it is used to indicate that the previous row is
1206        // collapsed. The zero height flag, 0x20, is used to collapse a row.
1207
1208        $grbit |= $level;
1209        if ($hidden) {
1210            $grbit |= 0x0030;
1211        }
1212        if ($height !== null) {
1213            $grbit |= 0x0040; // fUnsynced
1214        }
1215        if ($xfIndex !== 0xF) {
1216            $grbit |= 0x0080;
1217        }
1218        $grbit |= 0x0100;
1219
1220        $header = pack('vv', $record, $length);
1221        $data = pack('vvvvvvvv', $row, $colMic, $colMac, $miyRw, $irwMac, $reserved, $grbit, $ixfe);
1222        $this->append($header . $data);
1223    }
1224
1225    /**
1226     * Writes Excel DIMENSIONS to define the area in which there is data.
1227     */
1228    private function writeDimensions()
1229    {
1230        $record = 0x0200; // Record identifier
1231
1232        $length = 0x000E;
1233        $data = pack('VVvvv', $this->firstRowIndex, $this->lastRowIndex + 1, $this->firstColumnIndex, $this->lastColumnIndex + 1, 0x0000); // reserved
1234
1235        $header = pack('vv', $record, $length);
1236        $this->append($header . $data);
1237    }
1238
1239    /**
1240     * Write BIFF record Window2.
1241     */
1242    private function writeWindow2()
1243    {
1244        $record = 0x023E; // Record identifier
1245        $length = 0x0012;
1246
1247        $grbit = 0x00B6; // Option flags
1248        $rwTop = 0x0000; // Top row visible in window
1249        $colLeft = 0x0000; // Leftmost column visible in window
1250
1251        // The options flags that comprise $grbit
1252        $fDspFmla = 0; // 0 - bit
1253        $fDspGrid = $this->phpSheet->getShowGridlines() ? 1 : 0; // 1
1254        $fDspRwCol = $this->phpSheet->getShowRowColHeaders() ? 1 : 0; // 2
1255        $fFrozen = $this->phpSheet->getFreezePane() ? 1 : 0; // 3
1256        $fDspZeros = 1; // 4
1257        $fDefaultHdr = 1; // 5
1258        $fArabic = $this->phpSheet->getRightToLeft() ? 1 : 0; // 6
1259        $fDspGuts = $this->outlineOn; // 7
1260        $fFrozenNoSplit = 0; // 0 - bit
1261        // no support in PhpSpreadsheet for selected sheet, therefore sheet is only selected if it is the active sheet
1262        $fSelected = ($this->phpSheet === $this->phpSheet->getParent()->getActiveSheet()) ? 1 : 0;
1263        $fPageBreakPreview = $this->phpSheet->getSheetView()->getView() === SheetView::SHEETVIEW_PAGE_BREAK_PREVIEW;
1264
1265        $grbit = $fDspFmla;
1266        $grbit |= $fDspGrid << 1;
1267        $grbit |= $fDspRwCol << 2;
1268        $grbit |= $fFrozen << 3;
1269        $grbit |= $fDspZeros << 4;
1270        $grbit |= $fDefaultHdr << 5;
1271        $grbit |= $fArabic << 6;
1272        $grbit |= $fDspGuts << 7;
1273        $grbit |= $fFrozenNoSplit << 8;
1274        $grbit |= $fSelected << 9; // Selected sheets.
1275        $grbit |= $fSelected << 10; // Active sheet.
1276        $grbit |= $fPageBreakPreview << 11;
1277
1278        $header = pack('vv', $record, $length);
1279        $data = pack('vvv', $grbit, $rwTop, $colLeft);
1280
1281        // FIXME !!!
1282        $rgbHdr = 0x0040; // Row/column heading and gridline color index
1283        $zoom_factor_page_break = ($fPageBreakPreview ? $this->phpSheet->getSheetView()->getZoomScale() : 0x0000);
1284        $zoom_factor_normal = $this->phpSheet->getSheetView()->getZoomScaleNormal();
1285
1286        $data .= pack('vvvvV', $rgbHdr, 0x0000, $zoom_factor_page_break, $zoom_factor_normal, 0x00000000);
1287
1288        $this->append($header . $data);
1289    }
1290
1291    /**
1292     * Write BIFF record DEFAULTROWHEIGHT.
1293     */
1294    private function writeDefaultRowHeight()
1295    {
1296        $defaultRowHeight = $this->phpSheet->getDefaultRowDimension()->getRowHeight();
1297
1298        if ($defaultRowHeight < 0) {
1299            return;
1300        }
1301
1302        // convert to twips
1303        $defaultRowHeight = (int) 20 * $defaultRowHeight;
1304
1305        $record = 0x0225; // Record identifier
1306        $length = 0x0004; // Number of bytes to follow
1307
1308        $header = pack('vv', $record, $length);
1309        $data = pack('vv', 1, $defaultRowHeight);
1310        $this->append($header . $data);
1311    }
1312
1313    /**
1314     * Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
1315     */
1316    private function writeDefcol()
1317    {
1318        $defaultColWidth = 8;
1319
1320        $record = 0x0055; // Record identifier
1321        $length = 0x0002; // Number of bytes to follow
1322
1323        $header = pack('vv', $record, $length);
1324        $data = pack('v', $defaultColWidth);
1325        $this->append($header . $data);
1326    }
1327
1328    /**
1329     * Write BIFF record COLINFO to define column widths.
1330     *
1331     * Note: The SDK says the record length is 0x0B but Excel writes a 0x0C
1332     * length record.
1333     *
1334     * @param array $col_array This is the only parameter received and is composed of the following:
1335     *                0 => First formatted column,
1336     *                1 => Last formatted column,
1337     *                2 => Col width (8.43 is Excel default),
1338     *                3 => The optional XF format of the column,
1339     *                4 => Option flags.
1340     *                5 => Optional outline level
1341     */
1342    private function writeColinfo($col_array)
1343    {
1344        if (isset($col_array[0])) {
1345            $colFirst = $col_array[0];
1346        }
1347        if (isset($col_array[1])) {
1348            $colLast = $col_array[1];
1349        }
1350        if (isset($col_array[2])) {
1351            $coldx = $col_array[2];
1352        } else {
1353            $coldx = 8.43;
1354        }
1355        if (isset($col_array[3])) {
1356            $xfIndex = $col_array[3];
1357        } else {
1358            $xfIndex = 15;
1359        }
1360        if (isset($col_array[4])) {
1361            $grbit = $col_array[4];
1362        } else {
1363            $grbit = 0;
1364        }
1365        if (isset($col_array[5])) {
1366            $level = $col_array[5];
1367        } else {
1368            $level = 0;
1369        }
1370        $record = 0x007D; // Record identifier
1371        $length = 0x000C; // Number of bytes to follow
1372
1373        $coldx *= 256; // Convert to units of 1/256 of a char
1374
1375        $ixfe = $xfIndex;
1376        $reserved = 0x0000; // Reserved
1377
1378        $level = max(0, min($level, 7));
1379        $grbit |= $level << 8;
1380
1381        $header = pack('vv', $record, $length);
1382        $data = pack('vvvvvv', $colFirst, $colLast, $coldx, $ixfe, $grbit, $reserved);
1383        $this->append($header . $data);
1384    }
1385
1386    /**
1387     * Write BIFF record SELECTION.
1388     */
1389    private function writeSelection()
1390    {
1391        // look up the selected cell range
1392        $selectedCells = Coordinate::splitRange($this->phpSheet->getSelectedCells());
1393        $selectedCells = $selectedCells[0];
1394        if (count($selectedCells) == 2) {
1395            [$first, $last] = $selectedCells;
1396        } else {
1397            $first = $selectedCells[0];
1398            $last = $selectedCells[0];
1399        }
1400
1401        [$colFirst, $rwFirst] = Coordinate::coordinateFromString($first);
1402        $colFirst = Coordinate::columnIndexFromString($colFirst) - 1; // base 0 column index
1403        --$rwFirst; // base 0 row index
1404
1405        [$colLast, $rwLast] = Coordinate::coordinateFromString($last);
1406        $colLast = Coordinate::columnIndexFromString($colLast) - 1; // base 0 column index
1407        --$rwLast; // base 0 row index
1408
1409        // make sure we are not out of bounds
1410        $colFirst = min($colFirst, 255);
1411        $colLast = min($colLast, 255);
1412
1413        $rwFirst = min($rwFirst, 65535);
1414        $rwLast = min($rwLast, 65535);
1415
1416        $record = 0x001D; // Record identifier
1417        $length = 0x000F; // Number of bytes to follow
1418
1419        $pnn = $this->activePane; // Pane position
1420        $rwAct = $rwFirst; // Active row
1421        $colAct = $colFirst; // Active column
1422        $irefAct = 0; // Active cell ref
1423        $cref = 1; // Number of refs
1424
1425        if (!isset($rwLast)) {
1426            $rwLast = $rwFirst; // Last  row in reference
1427        }
1428        if (!isset($colLast)) {
1429            $colLast = $colFirst; // Last  col in reference
1430        }
1431
1432        // Swap last row/col for first row/col as necessary
1433        if ($rwFirst > $rwLast) {
1434            [$rwFirst, $rwLast] = [$rwLast, $rwFirst];
1435        }
1436
1437        if ($colFirst > $colLast) {
1438            [$colFirst, $colLast] = [$colLast, $colFirst];
1439        }
1440
1441        $header = pack('vv', $record, $length);
1442        $data = pack('CvvvvvvCC', $pnn, $rwAct, $colAct, $irefAct, $cref, $rwFirst, $rwLast, $colFirst, $colLast);
1443        $this->append($header . $data);
1444    }
1445
1446    /**
1447     * Store the MERGEDCELLS records for all ranges of merged cells.
1448     */
1449    private function writeMergedCells()
1450    {
1451        $mergeCells = $this->phpSheet->getMergeCells();
1452        $countMergeCells = count($mergeCells);
1453
1454        if ($countMergeCells == 0) {
1455            return;
1456        }
1457
1458        // maximum allowed number of merged cells per record
1459        $maxCountMergeCellsPerRecord = 1027;
1460
1461        // record identifier
1462        $record = 0x00E5;
1463
1464        // counter for total number of merged cells treated so far by the writer
1465        $i = 0;
1466
1467        // counter for number of merged cells written in record currently being written
1468        $j = 0;
1469
1470        // initialize record data
1471        $recordData = '';
1472
1473        // loop through the merged cells
1474        foreach ($mergeCells as $mergeCell) {
1475            ++$i;
1476            ++$j;
1477
1478            // extract the row and column indexes
1479            $range = Coordinate::splitRange($mergeCell);
1480            [$first, $last] = $range[0];
1481            [$firstColumn, $firstRow] = Coordinate::coordinateFromString($first);
1482            [$lastColumn, $lastRow] = Coordinate::coordinateFromString($last);
1483
1484            $recordData .= pack('vvvv', $firstRow - 1, $lastRow - 1, Coordinate::columnIndexFromString($firstColumn) - 1, Coordinate::columnIndexFromString($lastColumn) - 1);
1485
1486            // flush record if we have reached limit for number of merged cells, or reached final merged cell
1487            if ($j == $maxCountMergeCellsPerRecord or $i == $countMergeCells) {
1488                $recordData = pack('v', $j) . $recordData;
1489                $length = strlen($recordData);
1490                $header = pack('vv', $record, $length);
1491                $this->append($header . $recordData);
1492
1493                // initialize for next record, if any
1494                $recordData = '';
1495                $j = 0;
1496            }
1497        }
1498    }
1499
1500    /**
1501     * Write SHEETLAYOUT record.
1502     */
1503    private function writeSheetLayout()
1504    {
1505        if (!$this->phpSheet->isTabColorSet()) {
1506            return;
1507        }
1508
1509        $recordData = pack(
1510            'vvVVVvv',
1511            0x0862,
1512            0x0000, // unused
1513            0x00000000, // unused
1514            0x00000000, // unused
1515            0x00000014, // size of record data
1516            $this->colors[$this->phpSheet->getTabColor()->getRGB()], // color index
1517            0x0000        // unused
1518        );
1519
1520        $length = strlen($recordData);
1521
1522        $record = 0x0862; // Record identifier
1523        $header = pack('vv', $record, $length);
1524        $this->append($header . $recordData);
1525    }
1526
1527    /**
1528     * Write SHEETPROTECTION.
1529     */
1530    private function writeSheetProtection()
1531    {
1532        // record identifier
1533        $record = 0x0867;
1534
1535        // prepare options
1536        $options = (int) !$this->phpSheet->getProtection()->getObjects()
1537                    | (int) !$this->phpSheet->getProtection()->getScenarios() << 1
1538                    | (int) !$this->phpSheet->getProtection()->getFormatCells() << 2
1539                    | (int) !$this->phpSheet->getProtection()->getFormatColumns() << 3
1540                    | (int) !$this->phpSheet->getProtection()->getFormatRows() << 4
1541                    | (int) !$this->phpSheet->getProtection()->getInsertColumns() << 5
1542                    | (int) !$this->phpSheet->getProtection()->getInsertRows() << 6
1543                    | (int) !$this->phpSheet->getProtection()->getInsertHyperlinks() << 7
1544                    | (int) !$this->phpSheet->getProtection()->getDeleteColumns() << 8
1545                    | (int) !$this->phpSheet->getProtection()->getDeleteRows() << 9
1546                    | (int) !$this->phpSheet->getProtection()->getSelectLockedCells() << 10
1547                    | (int) !$this->phpSheet->getProtection()->getSort() << 11
1548                    | (int) !$this->phpSheet->getProtection()->getAutoFilter() << 12
1549                    | (int) !$this->phpSheet->getProtection()->getPivotTables() << 13
1550                    | (int) !$this->phpSheet->getProtection()->getSelectUnlockedCells() << 14;
1551
1552        // record data
1553        $recordData = pack(
1554            'vVVCVVvv',
1555            0x0867, // repeated record identifier
1556            0x0000, // not used
1557            0x0000, // not used
1558            0x00, // not used
1559            0x01000200, // unknown data
1560            0xFFFFFFFF, // unknown data
1561            $options, // options
1562            0x0000 // not used
1563        );
1564
1565        $length = strlen($recordData);
1566        $header = pack('vv', $record, $length);
1567
1568        $this->append($header . $recordData);
1569    }
1570
1571    /**
1572     * Write BIFF record RANGEPROTECTION.
1573     *
1574     * Openoffice.org's Documentaion of the Microsoft Excel File Format uses term RANGEPROTECTION for these records
1575     * Microsoft Office Excel 97-2007 Binary File Format Specification uses term FEAT for these records
1576     */
1577    private function writeRangeProtection()
1578    {
1579        foreach ($this->phpSheet->getProtectedCells() as $range => $password) {
1580            // number of ranges, e.g. 'A1:B3 C20:D25'
1581            $cellRanges = explode(' ', $range);
1582            $cref = count($cellRanges);
1583
1584            $recordData = pack(
1585                'vvVVvCVvVv',
1586                0x0868,
1587                0x00,
1588                0x0000,
1589                0x0000,
1590                0x02,
1591                0x0,
1592                0x0000,
1593                $cref,
1594                0x0000,
1595                0x00
1596            );
1597
1598            foreach ($cellRanges as $cellRange) {
1599                $recordData .= $this->writeBIFF8CellRangeAddressFixed($cellRange);
1600            }
1601
1602            // the rgbFeat structure
1603            $recordData .= pack(
1604                'VV',
1605                0x0000,
1606                hexdec($password)
1607            );
1608
1609            $recordData .= StringHelper::UTF8toBIFF8UnicodeLong('p' . md5($recordData));
1610
1611            $length = strlen($recordData);
1612
1613            $record = 0x0868; // Record identifier
1614            $header = pack('vv', $record, $length);
1615            $this->append($header . $recordData);
1616        }
1617    }
1618
1619    /**
1620     * Writes the Excel BIFF PANE record.
1621     * The panes can either be frozen or thawed (unfrozen).
1622     * Frozen panes are specified in terms of an integer number of rows and columns.
1623     * Thawed panes are specified in terms of Excel's units for rows and columns.
1624     */
1625    private function writePanes()
1626    {
1627        $panes = [];
1628        if ($this->phpSheet->getFreezePane()) {
1629            [$column, $row] = Coordinate::coordinateFromString($this->phpSheet->getFreezePane());
1630            $panes[0] = Coordinate::columnIndexFromString($column) - 1;
1631            $panes[1] = $row - 1;
1632
1633            [$leftMostColumn, $topRow] = Coordinate::coordinateFromString($this->phpSheet->getTopLeftCell());
1634            //Coordinates are zero-based in xls files
1635            $panes[2] = $topRow - 1;
1636            $panes[3] = Coordinate::columnIndexFromString($leftMostColumn) - 1;
1637        } else {
1638            // thaw panes
1639            return;
1640        }
1641
1642        $x = $panes[0] ?? null;
1643        $y = $panes[1] ?? null;
1644        $rwTop = $panes[2] ?? null;
1645        $colLeft = $panes[3] ?? null;
1646        if (count($panes) > 4) { // if Active pane was received
1647            $pnnAct = $panes[4];
1648        } else {
1649            $pnnAct = null;
1650        }
1651        $record = 0x0041; // Record identifier
1652        $length = 0x000A; // Number of bytes to follow
1653
1654        // Code specific to frozen or thawed panes.
1655        if ($this->phpSheet->getFreezePane()) {
1656            // Set default values for $rwTop and $colLeft
1657            if (!isset($rwTop)) {
1658                $rwTop = $y;
1659            }
1660            if (!isset($colLeft)) {
1661                $colLeft = $x;
1662            }
1663        } else {
1664            // Set default values for $rwTop and $colLeft
1665            if (!isset($rwTop)) {
1666                $rwTop = 0;
1667            }
1668            if (!isset($colLeft)) {
1669                $colLeft = 0;
1670            }
1671
1672            // Convert Excel's row and column units to the internal units.
1673            // The default row height is 12.75
1674            // The default column width is 8.43
1675            // The following slope and intersection values were interpolated.
1676            //
1677            $y = 20 * $y + 255;
1678            $x = 113.879 * $x + 390;
1679        }
1680
1681        // Determine which pane should be active. There is also the undocumented
1682        // option to override this should it be necessary: may be removed later.
1683        //
1684        if (!isset($pnnAct)) {
1685            if ($x != 0 && $y != 0) {
1686                $pnnAct = 0; // Bottom right
1687            }
1688            if ($x != 0 && $y == 0) {
1689                $pnnAct = 1; // Top right
1690            }
1691            if ($x == 0 && $y != 0) {
1692                $pnnAct = 2; // Bottom left
1693            }
1694            if ($x == 0 && $y == 0) {
1695                $pnnAct = 3; // Top left
1696            }
1697        }
1698
1699        $this->activePane = $pnnAct; // Used in writeSelection
1700
1701        $header = pack('vv', $record, $length);
1702        $data = pack('vvvvv', $x, $y, $rwTop, $colLeft, $pnnAct);
1703        $this->append($header . $data);
1704    }
1705
1706    /**
1707     * Store the page setup SETUP BIFF record.
1708     */
1709    private function writeSetup()
1710    {
1711        $record = 0x00A1; // Record identifier
1712        $length = 0x0022; // Number of bytes to follow
1713
1714        $iPaperSize = $this->phpSheet->getPageSetup()->getPaperSize(); // Paper size
1715
1716        $iScale = $this->phpSheet->getPageSetup()->getScale() ?
1717            $this->phpSheet->getPageSetup()->getScale() : 100; // Print scaling factor
1718
1719        $iPageStart = 0x01; // Starting page number
1720        $iFitWidth = (int) $this->phpSheet->getPageSetup()->getFitToWidth(); // Fit to number of pages wide
1721        $iFitHeight = (int) $this->phpSheet->getPageSetup()->getFitToHeight(); // Fit to number of pages high
1722        $grbit = 0x00; // Option flags
1723        $iRes = 0x0258; // Print resolution
1724        $iVRes = 0x0258; // Vertical print resolution
1725
1726        $numHdr = $this->phpSheet->getPageMargins()->getHeader(); // Header Margin
1727
1728        $numFtr = $this->phpSheet->getPageMargins()->getFooter(); // Footer Margin
1729        $iCopies = 0x01; // Number of copies
1730
1731        $fLeftToRight = 0x0; // Print over then down
1732
1733        // Page orientation
1734        $fLandscape = ($this->phpSheet->getPageSetup()->getOrientation() == PageSetup::ORIENTATION_LANDSCAPE) ?
1735            0x0 : 0x1;
1736
1737        $fNoPls = 0x0; // Setup not read from printer
1738        $fNoColor = 0x0; // Print black and white
1739        $fDraft = 0x0; // Print draft quality
1740        $fNotes = 0x0; // Print notes
1741        $fNoOrient = 0x0; // Orientation not set
1742        $fUsePage = 0x0; // Use custom starting page
1743
1744        $grbit = $fLeftToRight;
1745        $grbit |= $fLandscape << 1;
1746        $grbit |= $fNoPls << 2;
1747        $grbit |= $fNoColor << 3;
1748        $grbit |= $fDraft << 4;
1749        $grbit |= $fNotes << 5;
1750        $grbit |= $fNoOrient << 6;
1751        $grbit |= $fUsePage << 7;
1752
1753        $numHdr = pack('d', $numHdr);
1754        $numFtr = pack('d', $numFtr);
1755        if (self::getByteOrder()) { // if it's Big Endian
1756            $numHdr = strrev($numHdr);
1757            $numFtr = strrev($numFtr);
1758        }
1759
1760        $header = pack('vv', $record, $length);
1761        $data1 = pack('vvvvvvvv', $iPaperSize, $iScale, $iPageStart, $iFitWidth, $iFitHeight, $grbit, $iRes, $iVRes);
1762        $data2 = $numHdr . $numFtr;
1763        $data3 = pack('v', $iCopies);
1764        $this->append($header . $data1 . $data2 . $data3);
1765    }
1766
1767    /**
1768     * Store the header caption BIFF record.
1769     */
1770    private function writeHeader()
1771    {
1772        $record = 0x0014; // Record identifier
1773
1774        /* removing for now
1775        // need to fix character count (multibyte!)
1776        if (strlen($this->phpSheet->getHeaderFooter()->getOddHeader()) <= 255) {
1777            $str      = $this->phpSheet->getHeaderFooter()->getOddHeader();       // header string
1778        } else {
1779            $str = '';
1780        }
1781        */
1782
1783        $recordData = StringHelper::UTF8toBIFF8UnicodeLong($this->phpSheet->getHeaderFooter()->getOddHeader());
1784        $length = strlen($recordData);
1785
1786        $header = pack('vv', $record, $length);
1787
1788        $this->append($header . $recordData);
1789    }
1790
1791    /**
1792     * Store the footer caption BIFF record.
1793     */
1794    private function writeFooter()
1795    {
1796        $record = 0x0015; // Record identifier
1797
1798        /* removing for now
1799        // need to fix character count (multibyte!)
1800        if (strlen($this->phpSheet->getHeaderFooter()->getOddFooter()) <= 255) {
1801            $str = $this->phpSheet->getHeaderFooter()->getOddFooter();
1802        } else {
1803            $str = '';
1804        }
1805        */
1806
1807        $recordData = StringHelper::UTF8toBIFF8UnicodeLong($this->phpSheet->getHeaderFooter()->getOddFooter());
1808        $length = strlen($recordData);
1809
1810        $header = pack('vv', $record, $length);
1811
1812        $this->append($header . $recordData);
1813    }
1814
1815    /**
1816     * Store the horizontal centering HCENTER BIFF record.
1817     */
1818    private function writeHcenter()
1819    {
1820        $record = 0x0083; // Record identifier
1821        $length = 0x0002; // Bytes to follow
1822
1823        $fHCenter = $this->phpSheet->getPageSetup()->getHorizontalCentered() ? 1 : 0; // Horizontal centering
1824
1825        $header = pack('vv', $record, $length);
1826        $data = pack('v', $fHCenter);
1827
1828        $this->append($header . $data);
1829    }
1830
1831    /**
1832     * Store the vertical centering VCENTER BIFF record.
1833     */
1834    private function writeVcenter()
1835    {
1836        $record = 0x0084; // Record identifier
1837        $length = 0x0002; // Bytes to follow
1838
1839        $fVCenter = $this->phpSheet->getPageSetup()->getVerticalCentered() ? 1 : 0; // Horizontal centering
1840
1841        $header = pack('vv', $record, $length);
1842        $data = pack('v', $fVCenter);
1843        $this->append($header . $data);
1844    }
1845
1846    /**
1847     * Store the LEFTMARGIN BIFF record.
1848     */
1849    private function writeMarginLeft()
1850    {
1851        $record = 0x0026; // Record identifier
1852        $length = 0x0008; // Bytes to follow
1853
1854        $margin = $this->phpSheet->getPageMargins()->getLeft(); // Margin in inches
1855
1856        $header = pack('vv', $record, $length);
1857        $data = pack('d', $margin);
1858        if (self::getByteOrder()) { // if it's Big Endian
1859            $data = strrev($data);
1860        }
1861
1862        $this->append($header . $data);
1863    }
1864
1865    /**
1866     * Store the RIGHTMARGIN BIFF record.
1867     */
1868    private function writeMarginRight()
1869    {
1870        $record = 0x0027; // Record identifier
1871        $length = 0x0008; // Bytes to follow
1872
1873        $margin = $this->phpSheet->getPageMargins()->getRight(); // Margin in inches
1874
1875        $header = pack('vv', $record, $length);
1876        $data = pack('d', $margin);
1877        if (self::getByteOrder()) { // if it's Big Endian
1878            $data = strrev($data);
1879        }
1880
1881        $this->append($header . $data);
1882    }
1883
1884    /**
1885     * Store the TOPMARGIN BIFF record.
1886     */
1887    private function writeMarginTop()
1888    {
1889        $record = 0x0028; // Record identifier
1890        $length = 0x0008; // Bytes to follow
1891
1892        $margin = $this->phpSheet->getPageMargins()->getTop(); // Margin in inches
1893
1894        $header = pack('vv', $record, $length);
1895        $data = pack('d', $margin);
1896        if (self::getByteOrder()) { // if it's Big Endian
1897            $data = strrev($data);
1898        }
1899
1900        $this->append($header . $data);
1901    }
1902
1903    /**
1904     * Store the BOTTOMMARGIN BIFF record.
1905     */
1906    private function writeMarginBottom()
1907    {
1908        $record = 0x0029; // Record identifier
1909        $length = 0x0008; // Bytes to follow
1910
1911        $margin = $this->phpSheet->getPageMargins()->getBottom(); // Margin in inches
1912
1913        $header = pack('vv', $record, $length);
1914        $data = pack('d', $margin);
1915        if (self::getByteOrder()) { // if it's Big Endian
1916            $data = strrev($data);
1917        }
1918
1919        $this->append($header . $data);
1920    }
1921
1922    /**
1923     * Write the PRINTHEADERS BIFF record.
1924     */
1925    private function writePrintHeaders()
1926    {
1927        $record = 0x002a; // Record identifier
1928        $length = 0x0002; // Bytes to follow
1929
1930        $fPrintRwCol = $this->printHeaders; // Boolean flag
1931
1932        $header = pack('vv', $record, $length);
1933        $data = pack('v', $fPrintRwCol);
1934        $this->append($header . $data);
1935    }
1936
1937    /**
1938     * Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the
1939     * GRIDSET record.
1940     */
1941    private function writePrintGridlines()
1942    {
1943        $record = 0x002b; // Record identifier
1944        $length = 0x0002; // Bytes to follow
1945
1946        $fPrintGrid = $this->phpSheet->getPrintGridlines() ? 1 : 0; // Boolean flag
1947
1948        $header = pack('vv', $record, $length);
1949        $data = pack('v', $fPrintGrid);
1950        $this->append($header . $data);
1951    }
1952
1953    /**
1954     * Write the GRIDSET BIFF record. Must be used in conjunction with the
1955     * PRINTGRIDLINES record.
1956     */
1957    private function writeGridset()
1958    {
1959        $record = 0x0082; // Record identifier
1960        $length = 0x0002; // Bytes to follow
1961
1962        $fGridSet = !$this->phpSheet->getPrintGridlines(); // Boolean flag
1963
1964        $header = pack('vv', $record, $length);
1965        $data = pack('v', $fGridSet);
1966        $this->append($header . $data);
1967    }
1968
1969    /**
1970     * Write the AUTOFILTERINFO BIFF record. This is used to configure the number of autofilter select used in the sheet.
1971     */
1972    private function writeAutoFilterInfo()
1973    {
1974        $record = 0x009D; // Record identifier
1975        $length = 0x0002; // Bytes to follow
1976
1977        $rangeBounds = Coordinate::rangeBoundaries($this->phpSheet->getAutoFilter()->getRange());
1978        $iNumFilters = 1 + $rangeBounds[1][0] - $rangeBounds[0][0];
1979
1980        $header = pack('vv', $record, $length);
1981        $data = pack('v', $iNumFilters);
1982        $this->append($header . $data);
1983    }
1984
1985    /**
1986     * Write the GUTS BIFF record. This is used to configure the gutter margins
1987     * where Excel outline symbols are displayed. The visibility of the gutters is
1988     * controlled by a flag in WSBOOL.
1989     *
1990     * @see writeWsbool()
1991     */
1992    private function writeGuts()
1993    {
1994        $record = 0x0080; // Record identifier
1995        $length = 0x0008; // Bytes to follow
1996
1997        $dxRwGut = 0x0000; // Size of row gutter
1998        $dxColGut = 0x0000; // Size of col gutter
1999
2000        // determine maximum row outline level
2001        $maxRowOutlineLevel = 0;
2002        foreach ($this->phpSheet->getRowDimensions() as $rowDimension) {
2003            $maxRowOutlineLevel = max($maxRowOutlineLevel, $rowDimension->getOutlineLevel());
2004        }
2005
2006        $col_level = 0;
2007
2008        // Calculate the maximum column outline level. The equivalent calculation
2009        // for the row outline level is carried out in writeRow().
2010        $colcount = count($this->columnInfo);
2011        for ($i = 0; $i < $colcount; ++$i) {
2012            $col_level = max($this->columnInfo[$i][5], $col_level);
2013        }
2014
2015        // Set the limits for the outline levels (0 <= x <= 7).
2016        $col_level = max(0, min($col_level, 7));
2017
2018        // The displayed level is one greater than the max outline levels
2019        if ($maxRowOutlineLevel) {
2020            ++$maxRowOutlineLevel;
2021        }
2022        if ($col_level) {
2023            ++$col_level;
2024        }
2025
2026        $header = pack('vv', $record, $length);
2027        $data = pack('vvvv', $dxRwGut, $dxColGut, $maxRowOutlineLevel, $col_level);
2028
2029        $this->append($header . $data);
2030    }
2031
2032    /**
2033     * Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction
2034     * with the SETUP record.
2035     */
2036    private function writeWsbool()
2037    {
2038        $record = 0x0081; // Record identifier
2039        $length = 0x0002; // Bytes to follow
2040        $grbit = 0x0000;
2041
2042        // The only option that is of interest is the flag for fit to page. So we
2043        // set all the options in one go.
2044        //
2045        // Set the option flags
2046        $grbit |= 0x0001; // Auto page breaks visible
2047        if ($this->outlineStyle) {
2048            $grbit |= 0x0020; // Auto outline styles
2049        }
2050        if ($this->phpSheet->getShowSummaryBelow()) {
2051            $grbit |= 0x0040; // Outline summary below
2052        }
2053        if ($this->phpSheet->getShowSummaryRight()) {
2054            $grbit |= 0x0080; // Outline summary right
2055        }
2056        if ($this->phpSheet->getPageSetup()->getFitToPage()) {
2057            $grbit |= 0x0100; // Page setup fit to page
2058        }
2059        if ($this->outlineOn) {
2060            $grbit |= 0x0400; // Outline symbols displayed
2061        }
2062
2063        $header = pack('vv', $record, $length);
2064        $data = pack('v', $grbit);
2065        $this->append($header . $data);
2066    }
2067
2068    /**
2069     * Write the HORIZONTALPAGEBREAKS and VERTICALPAGEBREAKS BIFF records.
2070     */
2071    private function writeBreaks()
2072    {
2073        // initialize
2074        $vbreaks = [];
2075        $hbreaks = [];
2076
2077        foreach ($this->phpSheet->getBreaks() as $cell => $breakType) {
2078            // Fetch coordinates
2079            $coordinates = Coordinate::coordinateFromString($cell);
2080
2081            // Decide what to do by the type of break
2082            switch ($breakType) {
2083                case \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_COLUMN:
2084                    // Add to list of vertical breaks
2085                    $vbreaks[] = Coordinate::columnIndexFromString($coordinates[0]) - 1;
2086
2087                    break;
2088                case \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW:
2089                    // Add to list of horizontal breaks
2090                    $hbreaks[] = $coordinates[1];
2091
2092                    break;
2093                case \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_NONE:
2094                default:
2095                    // Nothing to do
2096                    break;
2097            }
2098        }
2099
2100        //horizontal page breaks
2101        if (!empty($hbreaks)) {
2102            // Sort and filter array of page breaks
2103            sort($hbreaks, SORT_NUMERIC);
2104            if ($hbreaks[0] == 0) { // don't use first break if it's 0
2105                array_shift($hbreaks);
2106            }
2107
2108            $record = 0x001b; // Record identifier
2109            $cbrk = count($hbreaks); // Number of page breaks
2110            $length = 2 + 6 * $cbrk; // Bytes to follow
2111
2112            $header = pack('vv', $record, $length);
2113            $data = pack('v', $cbrk);
2114
2115            // Append each page break
2116            foreach ($hbreaks as $hbreak) {
2117                $data .= pack('vvv', $hbreak, 0x0000, 0x00ff);
2118            }
2119
2120            $this->append($header . $data);
2121        }
2122
2123        // vertical page breaks
2124        if (!empty($vbreaks)) {
2125            // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
2126            // It is slightly higher in Excel 97/200, approx. 1026
2127            $vbreaks = array_slice($vbreaks, 0, 1000);
2128
2129            // Sort and filter array of page breaks
2130            sort($vbreaks, SORT_NUMERIC);
2131            if ($vbreaks[0] == 0) { // don't use first break if it's 0
2132                array_shift($vbreaks);
2133            }
2134
2135            $record = 0x001a; // Record identifier
2136            $cbrk = count($vbreaks); // Number of page breaks
2137            $length = 2 + 6 * $cbrk; // Bytes to follow
2138
2139            $header = pack('vv', $record, $length);
2140            $data = pack('v', $cbrk);
2141
2142            // Append each page break
2143            foreach ($vbreaks as $vbreak) {
2144                $data .= pack('vvv', $vbreak, 0x0000, 0xffff);
2145            }
2146
2147            $this->append($header . $data);
2148        }
2149    }
2150
2151    /**
2152     * Set the Biff PROTECT record to indicate that the worksheet is protected.
2153     */
2154    private function writeProtect()
2155    {
2156        // Exit unless sheet protection has been specified
2157        if (!$this->phpSheet->getProtection()->getSheet()) {
2158            return;
2159        }
2160
2161        $record = 0x0012; // Record identifier
2162        $length = 0x0002; // Bytes to follow
2163
2164        $fLock = 1; // Worksheet is protected
2165
2166        $header = pack('vv', $record, $length);
2167        $data = pack('v', $fLock);
2168
2169        $this->append($header . $data);
2170    }
2171
2172    /**
2173     * Write SCENPROTECT.
2174     */
2175    private function writeScenProtect()
2176    {
2177        // Exit if sheet protection is not active
2178        if (!$this->phpSheet->getProtection()->getSheet()) {
2179            return;
2180        }
2181
2182        // Exit if scenarios are not protected
2183        if (!$this->phpSheet->getProtection()->getScenarios()) {
2184            return;
2185        }
2186
2187        $record = 0x00DD; // Record identifier
2188        $length = 0x0002; // Bytes to follow
2189
2190        $header = pack('vv', $record, $length);
2191        $data = pack('v', 1);
2192
2193        $this->append($header . $data);
2194    }
2195
2196    /**
2197     * Write OBJECTPROTECT.
2198     */
2199    private function writeObjectProtect()
2200    {
2201        // Exit if sheet protection is not active
2202        if (!$this->phpSheet->getProtection()->getSheet()) {
2203            return;
2204        }
2205
2206        // Exit if objects are not protected
2207        if (!$this->phpSheet->getProtection()->getObjects()) {
2208            return;
2209        }
2210
2211        $record = 0x0063; // Record identifier
2212        $length = 0x0002; // Bytes to follow
2213
2214        $header = pack('vv', $record, $length);
2215        $data = pack('v', 1);
2216
2217        $this->append($header . $data);
2218    }
2219
2220    /**
2221     * Write the worksheet PASSWORD record.
2222     */
2223    private function writePassword()
2224    {
2225        // Exit unless sheet protection and password have been specified
2226        if (!$this->phpSheet->getProtection()->getSheet() || !$this->phpSheet->getProtection()->getPassword()) {
2227            return;
2228        }
2229
2230        $record = 0x0013; // Record identifier
2231        $length = 0x0002; // Bytes to follow
2232
2233        $wPassword = hexdec($this->phpSheet->getProtection()->getPassword()); // Encoded password
2234
2235        $header = pack('vv', $record, $length);
2236        $data = pack('v', $wPassword);
2237
2238        $this->append($header . $data);
2239    }
2240
2241    /**
2242     * Insert a 24bit bitmap image in a worksheet.
2243     *
2244     * @param int $row The row we are going to insert the bitmap into
2245     * @param int $col The column we are going to insert the bitmap into
2246     * @param mixed $bitmap The bitmap filename or GD-image resource
2247     * @param int $x the horizontal position (offset) of the image inside the cell
2248     * @param int $y the vertical position (offset) of the image inside the cell
2249     * @param float $scale_x The horizontal scale
2250     * @param float $scale_y The vertical scale
2251     */
2252    public function insertBitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1)
2253    {
2254        $bitmap_array = (is_resource($bitmap) ? $this->processBitmapGd($bitmap) : $this->processBitmap($bitmap));
2255        [$width, $height, $size, $data] = $bitmap_array;
2256
2257        // Scale the frame of the image.
2258        $width *= $scale_x;
2259        $height *= $scale_y;
2260
2261        // Calculate the vertices of the image and write the OBJ record
2262        $this->positionImage($col, $row, $x, $y, $width, $height);
2263
2264        // Write the IMDATA record to store the bitmap data
2265        $record = 0x007f;
2266        $length = 8 + $size;
2267        $cf = 0x09;
2268        $env = 0x01;
2269        $lcb = $size;
2270
2271        $header = pack('vvvvV', $record, $length, $cf, $env, $lcb);
2272        $this->append($header . $data);
2273    }
2274
2275    /**
2276     * Calculate the vertices that define the position of the image as required by
2277     * the OBJ record.
2278     *
2279     *         +------------+------------+
2280     *         |     A      |      B     |
2281     *   +-----+------------+------------+
2282     *   |     |(x1,y1)     |            |
2283     *   |  1  |(A1)._______|______      |
2284     *   |     |    |              |     |
2285     *   |     |    |              |     |
2286     *   +-----+----|    BITMAP    |-----+
2287     *   |     |    |              |     |
2288     *   |  2  |    |______________.     |
2289     *   |     |            |        (B2)|
2290     *   |     |            |     (x2,y2)|
2291     *   +---- +------------+------------+
2292     *
2293     * Example of a bitmap that covers some of the area from cell A1 to cell B2.
2294     *
2295     * Based on the width and height of the bitmap we need to calculate 8 vars:
2296     *     $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
2297     * The width and height of the cells are also variable and have to be taken into
2298     * account.
2299     * The values of $col_start and $row_start are passed in from the calling
2300     * function. The values of $col_end and $row_end are calculated by subtracting
2301     * the width and height of the bitmap from the width and height of the
2302     * underlying cells.
2303     * The vertices are expressed as a percentage of the underlying cell width as
2304     * follows (rhs values are in pixels):
2305     *
2306     *       x1 = X / W *1024
2307     *       y1 = Y / H *256
2308     *       x2 = (X-1) / W *1024
2309     *       y2 = (Y-1) / H *256
2310     *
2311     *       Where:  X is distance from the left side of the underlying cell
2312     *               Y is distance from the top of the underlying cell
2313     *               W is the width of the cell
2314     *               H is the height of the cell
2315     * The SDK incorrectly states that the height should be expressed as a
2316     *        percentage of 1024.
2317     *
2318     * @param int $col_start Col containing upper left corner of object
2319     * @param int $row_start Row containing top left corner of object
2320     * @param int $x1 Distance to left side of object
2321     * @param int $y1 Distance to top of object
2322     * @param int $width Width of image frame
2323     * @param int $height Height of image frame
2324     */
2325    public function positionImage($col_start, $row_start, $x1, $y1, $width, $height)
2326    {
2327        // Initialise end cell to the same as the start cell
2328        $col_end = $col_start; // Col containing lower right corner of object
2329        $row_end = $row_start; // Row containing bottom right corner of object
2330
2331        // Zero the specified offset if greater than the cell dimensions
2332        if ($x1 >= Xls::sizeCol($this->phpSheet, Coordinate::stringFromColumnIndex($col_start + 1))) {
2333            $x1 = 0;
2334        }
2335        if ($y1 >= Xls::sizeRow($this->phpSheet, $row_start + 1)) {
2336            $y1 = 0;
2337        }
2338
2339        $width = $width + $x1 - 1;
2340        $height = $height + $y1 - 1;
2341
2342        // Subtract the underlying cell widths to find the end cell of the image
2343        while ($width >= Xls::sizeCol($this->phpSheet, Coordinate::stringFromColumnIndex($col_end + 1))) {
2344            $width -= Xls::sizeCol($this->phpSheet, Coordinate::stringFromColumnIndex($col_end + 1));
2345            ++$col_end;
2346        }
2347
2348        // Subtract the underlying cell heights to find the end cell of the image
2349        while ($height >= Xls::sizeRow($this->phpSheet, $row_end + 1)) {
2350            $height -= Xls::sizeRow($this->phpSheet, $row_end + 1);
2351            ++$row_end;
2352        }
2353
2354        // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
2355        // with zero eight or width.
2356        //
2357        if (Xls::sizeCol($this->phpSheet, Coordinate::stringFromColumnIndex($col_start + 1)) == 0) {
2358            return;
2359        }
2360        if (Xls::sizeCol($this->phpSheet, Coordinate::stringFromColumnIndex($col_end + 1)) == 0) {
2361            return;
2362        }
2363        if (Xls::sizeRow($this->phpSheet, $row_start + 1) == 0) {
2364            return;
2365        }
2366        if (Xls::sizeRow($this->phpSheet, $row_end + 1) == 0) {
2367            return;
2368        }
2369
2370        // Convert the pixel values to the percentage value expected by Excel
2371        $x1 = $x1 / Xls::sizeCol($this->phpSheet, Coordinate::stringFromColumnIndex($col_start + 1)) * 1024;
2372        $y1 = $y1 / Xls::sizeRow($this->phpSheet, $row_start + 1) * 256;
2373        $x2 = $width / Xls::sizeCol($this->phpSheet, Coordinate::stringFromColumnIndex($col_end + 1)) * 1024; // Distance to right side of object
2374        $y2 = $height / Xls::sizeRow($this->phpSheet, $row_end + 1) * 256; // Distance to bottom of object
2375
2376        $this->writeObjPicture($col_start, $x1, $row_start, $y1, $col_end, $x2, $row_end, $y2);
2377    }
2378
2379    /**
2380     * Store the OBJ record that precedes an IMDATA record. This could be generalise
2381     * to support other Excel objects.
2382     *
2383     * @param int $colL Column containing upper left corner of object
2384     * @param int $dxL Distance from left side of cell
2385     * @param int $rwT Row containing top left corner of object
2386     * @param int $dyT Distance from top of cell
2387     * @param int $colR Column containing lower right corner of object
2388     * @param int $dxR Distance from right of cell
2389     * @param int $rwB Row containing bottom right corner of object
2390     * @param int $dyB Distance from bottom of cell
2391     */
2392    private function writeObjPicture($colL, $dxL, $rwT, $dyT, $colR, $dxR, $rwB, $dyB)
2393    {
2394        $record = 0x005d; // Record identifier
2395        $length = 0x003c; // Bytes to follow
2396
2397        $cObj = 0x0001; // Count of objects in file (set to 1)
2398        $OT = 0x0008; // Object type. 8 = Picture
2399        $id = 0x0001; // Object ID
2400        $grbit = 0x0614; // Option flags
2401
2402        $cbMacro = 0x0000; // Length of FMLA structure
2403        $Reserved1 = 0x0000; // Reserved
2404        $Reserved2 = 0x0000; // Reserved
2405
2406        $icvBack = 0x09; // Background colour
2407        $icvFore = 0x09; // Foreground colour
2408        $fls = 0x00; // Fill pattern
2409        $fAuto = 0x00; // Automatic fill
2410        $icv = 0x08; // Line colour
2411        $lns = 0xff; // Line style
2412        $lnw = 0x01; // Line weight
2413        $fAutoB = 0x00; // Automatic border
2414        $frs = 0x0000; // Frame style
2415        $cf = 0x0009; // Image format, 9 = bitmap
2416        $Reserved3 = 0x0000; // Reserved
2417        $cbPictFmla = 0x0000; // Length of FMLA structure
2418        $Reserved4 = 0x0000; // Reserved
2419        $grbit2 = 0x0001; // Option flags
2420        $Reserved5 = 0x0000; // Reserved
2421
2422        $header = pack('vv', $record, $length);
2423        $data = pack('V', $cObj);
2424        $data .= pack('v', $OT);
2425        $data .= pack('v', $id);
2426        $data .= pack('v', $grbit);
2427        $data .= pack('v', $colL);
2428        $data .= pack('v', $dxL);
2429        $data .= pack('v', $rwT);
2430        $data .= pack('v', $dyT);
2431        $data .= pack('v', $colR);
2432        $data .= pack('v', $dxR);
2433        $data .= pack('v', $rwB);
2434        $data .= pack('v', $dyB);
2435        $data .= pack('v', $cbMacro);
2436        $data .= pack('V', $Reserved1);
2437        $data .= pack('v', $Reserved2);
2438        $data .= pack('C', $icvBack);
2439        $data .= pack('C', $icvFore);
2440        $data .= pack('C', $fls);
2441        $data .= pack('C', $fAuto);
2442        $data .= pack('C', $icv);
2443        $data .= pack('C', $lns);
2444        $data .= pack('C', $lnw);
2445        $data .= pack('C', $fAutoB);
2446        $data .= pack('v', $frs);
2447        $data .= pack('V', $cf);
2448        $data .= pack('v', $Reserved3);
2449        $data .= pack('v', $cbPictFmla);
2450        $data .= pack('v', $Reserved4);
2451        $data .= pack('v', $grbit2);
2452        $data .= pack('V', $Reserved5);
2453
2454        $this->append($header . $data);
2455    }
2456
2457    /**
2458     * Convert a GD-image into the internal format.
2459     *
2460     * @param resource $image The image to process
2461     *
2462     * @return array Array with data and properties of the bitmap
2463     */
2464    public function processBitmapGd($image)
2465    {
2466        $width = imagesx($image);
2467        $height = imagesy($image);
2468
2469        $data = pack('Vvvvv', 0x000c, $width, $height, 0x01, 0x18);
2470        for ($j = $height; --$j;) {
2471            for ($i = 0; $i < $width; ++$i) {
2472                $color = imagecolorsforindex($image, imagecolorat($image, $i, $j));
2473                foreach (['red', 'green', 'blue'] as $key) {
2474                    $color[$key] = $color[$key] + round((255 - $color[$key]) * $color['alpha'] / 127);
2475                }
2476                $data .= chr($color['blue']) . chr($color['green']) . chr($color['red']);
2477            }
2478            if (3 * $width % 4) {
2479                $data .= str_repeat("\x00", 4 - 3 * $width % 4);
2480            }
2481        }
2482
2483        return [$width, $height, strlen($data), $data];
2484    }
2485
2486    /**
2487     * Convert a 24 bit bitmap into the modified internal format used by Windows.
2488     * This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the
2489     * MSDN library.
2490     *
2491     * @param string $bitmap The bitmap to process
2492     *
2493     * @return array Array with data and properties of the bitmap
2494     */
2495    public function processBitmap($bitmap)
2496    {
2497        // Open file.
2498        $bmp_fd = @fopen($bitmap, 'rb');
2499        if (!$bmp_fd) {
2500            throw new WriterException("Couldn't import $bitmap");
2501        }
2502
2503        // Slurp the file into a string.
2504        $data = fread($bmp_fd, filesize($bitmap));
2505
2506        // Check that the file is big enough to be a bitmap.
2507        if (strlen($data) <= 0x36) {
2508            throw new WriterException("$bitmap doesn't contain enough data.\n");
2509        }
2510
2511        // The first 2 bytes are used to identify the bitmap.
2512        $identity = unpack('A2ident', $data);
2513        if ($identity['ident'] != 'BM') {
2514            throw new WriterException("$bitmap doesn't appear to be a valid bitmap image.\n");
2515        }
2516
2517        // Remove bitmap data: ID.
2518        $data = substr($data, 2);
2519
2520        // Read and remove the bitmap size. This is more reliable than reading
2521        // the data size at offset 0x22.
2522        //
2523        $size_array = unpack('Vsa', substr($data, 0, 4));
2524        $size = $size_array['sa'];
2525        $data = substr($data, 4);
2526        $size -= 0x36; // Subtract size of bitmap header.
2527        $size += 0x0C; // Add size of BIFF header.
2528
2529        // Remove bitmap data: reserved, offset, header length.
2530        $data = substr($data, 12);
2531
2532        // Read and remove the bitmap width and height. Verify the sizes.
2533        $width_and_height = unpack('V2', substr($data, 0, 8));
2534        $width = $width_and_height[1];
2535        $height = $width_and_height[2];
2536        $data = substr($data, 8);
2537        if ($width > 0xFFFF) {
2538            throw new WriterException("$bitmap: largest image width supported is 65k.\n");
2539        }
2540        if ($height > 0xFFFF) {
2541            throw new WriterException("$bitmap: largest image height supported is 65k.\n");
2542        }
2543
2544        // Read and remove the bitmap planes and bpp data. Verify them.
2545        $planes_and_bitcount = unpack('v2', substr($data, 0, 4));
2546        $data = substr($data, 4);
2547        if ($planes_and_bitcount[2] != 24) { // Bitcount
2548            throw new WriterException("$bitmap isn't a 24bit true color bitmap.\n");
2549        }
2550        if ($planes_and_bitcount[1] != 1) {
2551            throw new WriterException("$bitmap: only 1 plane supported in bitmap image.\n");
2552        }
2553
2554        // Read and remove the bitmap compression. Verify compression.
2555        $compression = unpack('Vcomp', substr($data, 0, 4));
2556        $data = substr($data, 4);
2557
2558        if ($compression['comp'] != 0) {
2559            throw new WriterException("$bitmap: compression not supported in bitmap image.\n");
2560        }
2561
2562        // Remove bitmap data: data size, hres, vres, colours, imp. colours.
2563        $data = substr($data, 20);
2564
2565        // Add the BITMAPCOREHEADER data
2566        $header = pack('Vvvvv', 0x000c, $width, $height, 0x01, 0x18);
2567        $data = $header . $data;
2568
2569        return [$width, $height, $size, $data];
2570    }
2571
2572    /**
2573     * Store the window zoom factor. This should be a reduced fraction but for
2574     * simplicity we will store all fractions with a numerator of 100.
2575     */
2576    private function writeZoom()
2577    {
2578        // If scale is 100 we don't need to write a record
2579        if ($this->phpSheet->getSheetView()->getZoomScale() == 100) {
2580            return;
2581        }
2582
2583        $record = 0x00A0; // Record identifier
2584        $length = 0x0004; // Bytes to follow
2585
2586        $header = pack('vv', $record, $length);
2587        $data = pack('vv', $this->phpSheet->getSheetView()->getZoomScale(), 100);
2588        $this->append($header . $data);
2589    }
2590
2591    /**
2592     * Get Escher object.
2593     *
2594     * @return \PhpOffice\PhpSpreadsheet\Shared\Escher
2595     */
2596    public function getEscher()
2597    {
2598        return $this->escher;
2599    }
2600
2601    /**
2602     * Set Escher object.
2603     *
2604     * @param \PhpOffice\PhpSpreadsheet\Shared\Escher $pValue
2605     */
2606    public function setEscher(\PhpOffice\PhpSpreadsheet\Shared\Escher $pValue = null)
2607    {
2608        $this->escher = $pValue;
2609    }
2610
2611    /**
2612     * Write MSODRAWING record.
2613     */
2614    private function writeMsoDrawing()
2615    {
2616        // write the Escher stream if necessary
2617        if (isset($this->escher)) {
2618            $writer = new Escher($this->escher);
2619            $data = $writer->close();
2620            $spOffsets = $writer->getSpOffsets();
2621            $spTypes = $writer->getSpTypes();
2622            // write the neccesary MSODRAWING, OBJ records
2623
2624            // split the Escher stream
2625            $spOffsets[0] = 0;
2626            $nm = count($spOffsets) - 1; // number of shapes excluding first shape
2627            for ($i = 1; $i <= $nm; ++$i) {
2628                // MSODRAWING record
2629                $record = 0x00EC; // Record identifier
2630
2631                // chunk of Escher stream for one shape
2632                $dataChunk = substr($data, $spOffsets[$i - 1], $spOffsets[$i] - $spOffsets[$i - 1]);
2633
2634                $length = strlen($dataChunk);
2635                $header = pack('vv', $record, $length);
2636
2637                $this->append($header . $dataChunk);
2638
2639                // OBJ record
2640                $record = 0x005D; // record identifier
2641                $objData = '';
2642
2643                // ftCmo
2644                if ($spTypes[$i] == 0x00C9) {
2645                    // Add ftCmo (common object data) subobject
2646                    $objData .=
2647                        pack(
2648                            'vvvvvVVV',
2649                            0x0015, // 0x0015 = ftCmo
2650                            0x0012, // length of ftCmo data
2651                            0x0014, // object type, 0x0014 = filter
2652                            $i, // object id number, Excel seems to use 1-based index, local for the sheet
2653                            0x2101, // option flags, 0x2001 is what OpenOffice.org uses
2654                            0, // reserved
2655                            0, // reserved
2656                            0  // reserved
2657                        );
2658
2659                    // Add ftSbs Scroll bar subobject
2660                    $objData .= pack('vv', 0x00C, 0x0014);
2661                    $objData .= pack('H*', '0000000000000000640001000A00000010000100');
2662                    // Add ftLbsData (List box data) subobject
2663                    $objData .= pack('vv', 0x0013, 0x1FEE);
2664                    $objData .= pack('H*', '00000000010001030000020008005700');
2665                } else {
2666                    // Add ftCmo (common object data) subobject
2667                    $objData .=
2668                        pack(
2669                            'vvvvvVVV',
2670                            0x0015, // 0x0015 = ftCmo
2671                            0x0012, // length of ftCmo data
2672                            0x0008, // object type, 0x0008 = picture
2673                            $i, // object id number, Excel seems to use 1-based index, local for the sheet
2674                            0x6011, // option flags, 0x6011 is what OpenOffice.org uses
2675                            0, // reserved
2676                            0, // reserved
2677                            0  // reserved
2678                        );
2679                }
2680
2681                // ftEnd
2682                $objData .=
2683                    pack(
2684                        'vv',
2685                        0x0000, // 0x0000 = ftEnd
2686                        0x0000  // length of ftEnd data
2687                    );
2688
2689                $length = strlen($objData);
2690                $header = pack('vv', $record, $length);
2691                $this->append($header . $objData);
2692            }
2693        }
2694    }
2695
2696    /**
2697     * Store the DATAVALIDATIONS and DATAVALIDATION records.
2698     */
2699    private function writeDataValidity()
2700    {
2701        // Datavalidation collection
2702        $dataValidationCollection = $this->phpSheet->getDataValidationCollection();
2703
2704        // Write data validations?
2705        if (!empty($dataValidationCollection)) {
2706            // DATAVALIDATIONS record
2707            $record = 0x01B2; // Record identifier
2708            $length = 0x0012; // Bytes to follow
2709
2710            $grbit = 0x0000; // Prompt box at cell, no cached validity data at DV records
2711            $horPos = 0x00000000; // Horizontal position of prompt box, if fixed position
2712            $verPos = 0x00000000; // Vertical position of prompt box, if fixed position
2713            $objId = 0xFFFFFFFF; // Object identifier of drop down arrow object, or -1 if not visible
2714
2715            $header = pack('vv', $record, $length);
2716            $data = pack('vVVVV', $grbit, $horPos, $verPos, $objId, count($dataValidationCollection));
2717            $this->append($header . $data);
2718
2719            // DATAVALIDATION records
2720            $record = 0x01BE; // Record identifier
2721
2722            foreach ($dataValidationCollection as $cellCoordinate => $dataValidation) {
2723                // initialize record data
2724                $data = '';
2725
2726                // options
2727                $options = 0x00000000;
2728
2729                // data type
2730                $type = 0x00;
2731                switch ($dataValidation->getType()) {
2732                    case DataValidation::TYPE_NONE:
2733                        $type = 0x00;
2734
2735                        break;
2736                    case DataValidation::TYPE_WHOLE:
2737                        $type = 0x01;
2738
2739                        break;
2740                    case DataValidation::TYPE_DECIMAL:
2741                        $type = 0x02;
2742
2743                        break;
2744                    case DataValidation::TYPE_LIST:
2745                        $type = 0x03;
2746
2747                        break;
2748                    case DataValidation::TYPE_DATE:
2749                        $type = 0x04;
2750
2751                        break;
2752                    case DataValidation::TYPE_TIME:
2753                        $type = 0x05;
2754
2755                        break;
2756                    case DataValidation::TYPE_TEXTLENGTH:
2757                        $type = 0x06;
2758
2759                        break;
2760                    case DataValidation::TYPE_CUSTOM:
2761                        $type = 0x07;
2762
2763                        break;
2764                }
2765
2766                $options |= $type << 0;
2767
2768                // error style
2769                $errorStyle = 0x00;
2770                switch ($dataValidation->getErrorStyle()) {
2771                    case DataValidation::STYLE_STOP:
2772                        $errorStyle = 0x00;
2773
2774                        break;
2775                    case DataValidation::STYLE_WARNING:
2776                        $errorStyle = 0x01;
2777
2778                        break;
2779                    case DataValidation::STYLE_INFORMATION:
2780                        $errorStyle = 0x02;
2781
2782                        break;
2783                }
2784
2785                $options |= $errorStyle << 4;
2786
2787                // explicit formula?
2788                if ($type == 0x03 && preg_match('/^\".*\"$/', $dataValidation->getFormula1())) {
2789                    $options |= 0x01 << 7;
2790                }
2791
2792                // empty cells allowed
2793                $options |= $dataValidation->getAllowBlank() << 8;
2794
2795                // show drop down
2796                $options |= (!$dataValidation->getShowDropDown()) << 9;
2797
2798                // show input message
2799                $options |= $dataValidation->getShowInputMessage() << 18;
2800
2801                // show error message
2802                $options |= $dataValidation->getShowErrorMessage() << 19;
2803
2804                // condition operator
2805                $operator = 0x00;
2806                switch ($dataValidation->getOperator()) {
2807                    case DataValidation::OPERATOR_BETWEEN:
2808                        $operator = 0x00;
2809
2810                        break;
2811                    case DataValidation::OPERATOR_NOTBETWEEN:
2812                        $operator = 0x01;
2813
2814                        break;
2815                    case DataValidation::OPERATOR_EQUAL:
2816                        $operator = 0x02;
2817
2818                        break;
2819                    case DataValidation::OPERATOR_NOTEQUAL:
2820                        $operator = 0x03;
2821
2822                        break;
2823                    case DataValidation::OPERATOR_GREATERTHAN:
2824                        $operator = 0x04;
2825
2826                        break;
2827                    case DataValidation::OPERATOR_LESSTHAN:
2828                        $operator = 0x05;
2829
2830                        break;
2831                    case DataValidation::OPERATOR_GREATERTHANOREQUAL:
2832                        $operator = 0x06;
2833
2834                        break;
2835                    case DataValidation::OPERATOR_LESSTHANOREQUAL:
2836                        $operator = 0x07;
2837
2838                        break;
2839                }
2840
2841                $options |= $operator << 20;
2842
2843                $data = pack('V', $options);
2844
2845                // prompt title
2846                $promptTitle = $dataValidation->getPromptTitle() !== '' ?
2847                    $dataValidation->getPromptTitle() : chr(0);
2848                $data .= StringHelper::UTF8toBIFF8UnicodeLong($promptTitle);
2849
2850                // error title
2851                $errorTitle = $dataValidation->getErrorTitle() !== '' ?
2852                    $dataValidation->getErrorTitle() : chr(0);
2853                $data .= StringHelper::UTF8toBIFF8UnicodeLong($errorTitle);
2854
2855                // prompt text
2856                $prompt = $dataValidation->getPrompt() !== '' ?
2857                    $dataValidation->getPrompt() : chr(0);
2858                $data .= StringHelper::UTF8toBIFF8UnicodeLong($prompt);
2859
2860                // error text
2861                $error = $dataValidation->getError() !== '' ?
2862                    $dataValidation->getError() : chr(0);
2863                $data .= StringHelper::UTF8toBIFF8UnicodeLong($error);
2864
2865                // formula 1
2866                try {
2867                    $formula1 = $dataValidation->getFormula1();
2868                    if ($type == 0x03) { // list type
2869                        $formula1 = str_replace(',', chr(0), $formula1);
2870                    }
2871                    $this->parser->parse($formula1);
2872                    $formula1 = $this->parser->toReversePolish();
2873                    $sz1 = strlen($formula1);
2874                } catch (PhpSpreadsheetException $e) {
2875                    $sz1 = 0;
2876                    $formula1 = '';
2877                }
2878                $data .= pack('vv', $sz1, 0x0000);
2879                $data .= $formula1;
2880
2881                // formula 2
2882                try {
2883                    $formula2 = $dataValidation->getFormula2();
2884                    if ($formula2 === '') {
2885                        throw new WriterException('No formula2');
2886                    }
2887                    $this->parser->parse($formula2);
2888                    $formula2 = $this->parser->toReversePolish();
2889                    $sz2 = strlen($formula2);
2890                } catch (PhpSpreadsheetException $e) {
2891                    $sz2 = 0;
2892                    $formula2 = '';
2893                }
2894                $data .= pack('vv', $sz2, 0x0000);
2895                $data .= $formula2;
2896
2897                // cell range address list
2898                $data .= pack('v', 0x0001);
2899                $data .= $this->writeBIFF8CellRangeAddressFixed($cellCoordinate);
2900
2901                $length = strlen($data);
2902                $header = pack('vv', $record, $length);
2903
2904                $this->append($header . $data);
2905            }
2906        }
2907    }
2908
2909    /**
2910     * Map Error code.
2911     *
2912     * @param string $errorCode
2913     *
2914     * @return int
2915     */
2916    private static function mapErrorCode($errorCode)
2917    {
2918        switch ($errorCode) {
2919            case '#NULL!':
2920                return 0x00;
2921            case '#DIV/0!':
2922                return 0x07;
2923            case '#VALUE!':
2924                return 0x0F;
2925            case '#REF!':
2926                return 0x17;
2927            case '#NAME?':
2928                return 0x1D;
2929            case '#NUM!':
2930                return 0x24;
2931            case '#N/A':
2932                return 0x2A;
2933        }
2934
2935        return 0;
2936    }
2937
2938    /**
2939     * Write PLV Record.
2940     */
2941    private function writePageLayoutView()
2942    {
2943        $record = 0x088B; // Record identifier
2944        $length = 0x0010; // Bytes to follow
2945
2946        $rt = 0x088B; // 2
2947        $grbitFrt = 0x0000; // 2
2948        $reserved = 0x0000000000000000; // 8
2949        $wScalvePLV = $this->phpSheet->getSheetView()->getZoomScale(); // 2
2950
2951        // The options flags that comprise $grbit
2952        if ($this->phpSheet->getSheetView()->getView() == SheetView::SHEETVIEW_PAGE_LAYOUT) {
2953            $fPageLayoutView = 1;
2954        } else {
2955            $fPageLayoutView = 0;
2956        }
2957        $fRulerVisible = 0;
2958        $fWhitespaceHidden = 0;
2959
2960        $grbit = $fPageLayoutView; // 2
2961        $grbit |= $fRulerVisible << 1;
2962        $grbit |= $fWhitespaceHidden << 3;
2963
2964        $header = pack('vv', $record, $length);
2965        $data = pack('vvVVvv', $rt, $grbitFrt, 0x00000000, 0x00000000, $wScalvePLV, $grbit);
2966        $this->append($header . $data);
2967    }
2968
2969    /**
2970     * Write CFRule Record.
2971     *
2972     * @param Conditional $conditional
2973     */
2974    private function writeCFRule(Conditional $conditional)
2975    {
2976        $record = 0x01B1; // Record identifier
2977
2978        // $type : Type of the CF
2979        // $operatorType : Comparison operator
2980        if ($conditional->getConditionType() == Conditional::CONDITION_EXPRESSION) {
2981            $type = 0x02;
2982            $operatorType = 0x00;
2983        } elseif ($conditional->getConditionType() == Conditional::CONDITION_CELLIS) {
2984            $type = 0x01;
2985
2986            switch ($conditional->getOperatorType()) {
2987                case Conditional::OPERATOR_NONE:
2988                    $operatorType = 0x00;
2989
2990                    break;
2991                case Conditional::OPERATOR_EQUAL:
2992                    $operatorType = 0x03;
2993
2994                    break;
2995                case Conditional::OPERATOR_GREATERTHAN:
2996                    $operatorType = 0x05;
2997
2998                    break;
2999                case Conditional::OPERATOR_GREATERTHANOREQUAL:
3000                    $operatorType = 0x07;
3001
3002                    break;
3003                case Conditional::OPERATOR_LESSTHAN:
3004                    $operatorType = 0x06;
3005
3006                    break;
3007                case Conditional::OPERATOR_LESSTHANOREQUAL:
3008                    $operatorType = 0x08;
3009
3010                    break;
3011                case Conditional::OPERATOR_NOTEQUAL:
3012                    $operatorType = 0x04;
3013
3014                    break;
3015                case Conditional::OPERATOR_BETWEEN:
3016                    $operatorType = 0x01;
3017
3018                    break;
3019                    // not OPERATOR_NOTBETWEEN 0x02
3020            }
3021        }
3022
3023        // $szValue1 : size of the formula data for first value or formula
3024        // $szValue2 : size of the formula data for second value or formula
3025        $arrConditions = $conditional->getConditions();
3026        $numConditions = count($arrConditions);
3027        if ($numConditions == 1) {
3028            $szValue1 = ($arrConditions[0] <= 65535 ? 3 : 0x0000);
3029            $szValue2 = 0x0000;
3030            $operand1 = pack('Cv', 0x1E, $arrConditions[0]);
3031            $operand2 = null;
3032        } elseif ($numConditions == 2 && ($conditional->getOperatorType() == Conditional::OPERATOR_BETWEEN)) {
3033            $szValue1 = ($arrConditions[0] <= 65535 ? 3 : 0x0000);
3034            $szValue2 = ($arrConditions[1] <= 65535 ? 3 : 0x0000);
3035            $operand1 = pack('Cv', 0x1E, $arrConditions[0]);
3036            $operand2 = pack('Cv', 0x1E, $arrConditions[1]);
3037        } else {
3038            $szValue1 = 0x0000;
3039            $szValue2 = 0x0000;
3040            $operand1 = null;
3041            $operand2 = null;
3042        }
3043
3044        // $flags : Option flags
3045        // Alignment
3046        $bAlignHz = ($conditional->getStyle()->getAlignment()->getHorizontal() == null ? 1 : 0);
3047        $bAlignVt = ($conditional->getStyle()->getAlignment()->getVertical() == null ? 1 : 0);
3048        $bAlignWrapTx = ($conditional->getStyle()->getAlignment()->getWrapText() == false ? 1 : 0);
3049        $bTxRotation = ($conditional->getStyle()->getAlignment()->getTextRotation() == null ? 1 : 0);
3050        $bIndent = ($conditional->getStyle()->getAlignment()->getIndent() == 0 ? 1 : 0);
3051        $bShrinkToFit = ($conditional->getStyle()->getAlignment()->getShrinkToFit() == false ? 1 : 0);
3052        if ($bAlignHz == 0 || $bAlignVt == 0 || $bAlignWrapTx == 0 || $bTxRotation == 0 || $bIndent == 0 || $bShrinkToFit == 0) {
3053            $bFormatAlign = 1;
3054        } else {
3055            $bFormatAlign = 0;
3056        }
3057        // Protection
3058        $bProtLocked = ($conditional->getStyle()->getProtection()->getLocked() == null ? 1 : 0);
3059        $bProtHidden = ($conditional->getStyle()->getProtection()->getHidden() == null ? 1 : 0);
3060        if ($bProtLocked == 0 || $bProtHidden == 0) {
3061            $bFormatProt = 1;
3062        } else {
3063            $bFormatProt = 0;
3064        }
3065        // Border
3066        $bBorderLeft = ($conditional->getStyle()->getBorders()->getLeft()->getColor()->getARGB() == Color::COLOR_BLACK
3067                        && $conditional->getStyle()->getBorders()->getLeft()->getBorderStyle() == Border::BORDER_NONE ? 1 : 0);
3068        $bBorderRight = ($conditional->getStyle()->getBorders()->getRight()->getColor()->getARGB() == Color::COLOR_BLACK
3069                        && $conditional->getStyle()->getBorders()->getRight()->getBorderStyle() == Border::BORDER_NONE ? 1 : 0);
3070        $bBorderTop = ($conditional->getStyle()->getBorders()->getTop()->getColor()->getARGB() == Color::COLOR_BLACK
3071                        && $conditional->getStyle()->getBorders()->getTop()->getBorderStyle() == Border::BORDER_NONE ? 1 : 0);
3072        $bBorderBottom = ($conditional->getStyle()->getBorders()->getBottom()->getColor()->getARGB() == Color::COLOR_BLACK
3073                        && $conditional->getStyle()->getBorders()->getBottom()->getBorderStyle() == Border::BORDER_NONE ? 1 : 0);
3074        if ($bBorderLeft == 0 || $bBorderRight == 0 || $bBorderTop == 0 || $bBorderBottom == 0) {
3075            $bFormatBorder = 1;
3076        } else {
3077            $bFormatBorder = 0;
3078        }
3079        // Pattern
3080        $bFillStyle = ($conditional->getStyle()->getFill()->getFillType() == null ? 0 : 1);
3081        $bFillColor = ($conditional->getStyle()->getFill()->getStartColor()->getARGB() == null ? 0 : 1);
3082        $bFillColorBg = ($conditional->getStyle()->getFill()->getEndColor()->getARGB() == null ? 0 : 1);
3083        if ($bFillStyle == 0 || $bFillColor == 0 || $bFillColorBg == 0) {
3084            $bFormatFill = 1;
3085        } else {
3086            $bFormatFill = 0;
3087        }
3088        // Font
3089        if ($conditional->getStyle()->getFont()->getName() != null
3090            || $conditional->getStyle()->getFont()->getSize() != null
3091            || $conditional->getStyle()->getFont()->getBold() != null
3092            || $conditional->getStyle()->getFont()->getItalic() != null
3093            || $conditional->getStyle()->getFont()->getSuperscript() != null
3094            || $conditional->getStyle()->getFont()->getSubscript() != null
3095            || $conditional->getStyle()->getFont()->getUnderline() != null
3096            || $conditional->getStyle()->getFont()->getStrikethrough() != null
3097            || $conditional->getStyle()->getFont()->getColor()->getARGB() != null) {
3098            $bFormatFont = 1;
3099        } else {
3100            $bFormatFont = 0;
3101        }
3102        // Alignment
3103        $flags = 0;
3104        $flags |= (1 == $bAlignHz ? 0x00000001 : 0);
3105        $flags |= (1 == $bAlignVt ? 0x00000002 : 0);
3106        $flags |= (1 == $bAlignWrapTx ? 0x00000004 : 0);
3107        $flags |= (1 == $bTxRotation ? 0x00000008 : 0);
3108        // Justify last line flag
3109        $flags |= (1 == 1 ? 0x00000010 : 0);
3110        $flags |= (1 == $bIndent ? 0x00000020 : 0);
3111        $flags |= (1 == $bShrinkToFit ? 0x00000040 : 0);
3112        // Default
3113        $flags |= (1 == 1 ? 0x00000080 : 0);
3114        // Protection
3115        $flags |= (1 == $bProtLocked ? 0x00000100 : 0);
3116        $flags |= (1 == $bProtHidden ? 0x00000200 : 0);
3117        // Border
3118        $flags |= (1 == $bBorderLeft ? 0x00000400 : 0);
3119        $flags |= (1 == $bBorderRight ? 0x00000800 : 0);
3120        $flags |= (1 == $bBorderTop ? 0x00001000 : 0);
3121        $flags |= (1 == $bBorderBottom ? 0x00002000 : 0);
3122        $flags |= (1 == 1 ? 0x00004000 : 0); // Top left to Bottom right border
3123        $flags |= (1 == 1 ? 0x00008000 : 0); // Bottom left to Top right border
3124        // Pattern
3125        $flags |= (1 == $bFillStyle ? 0x00010000 : 0);
3126        $flags |= (1 == $bFillColor ? 0x00020000 : 0);
3127        $flags |= (1 == $bFillColorBg ? 0x00040000 : 0);
3128        $flags |= (1 == 1 ? 0x00380000 : 0);
3129        // Font
3130        $flags |= (1 == $bFormatFont ? 0x04000000 : 0);
3131        // Alignment:
3132        $flags |= (1 == $bFormatAlign ? 0x08000000 : 0);
3133        // Border
3134        $flags |= (1 == $bFormatBorder ? 0x10000000 : 0);
3135        // Pattern
3136        $flags |= (1 == $bFormatFill ? 0x20000000 : 0);
3137        // Protection
3138        $flags |= (1 == $bFormatProt ? 0x40000000 : 0);
3139        // Text direction
3140        $flags |= (1 == 0 ? 0x80000000 : 0);
3141
3142        // Data Blocks
3143        if ($bFormatFont == 1) {
3144            // Font Name
3145            if ($conditional->getStyle()->getFont()->getName() == null) {
3146                $dataBlockFont = pack('VVVVVVVV', 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000);
3147                $dataBlockFont .= pack('VVVVVVVV', 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000);
3148            } else {
3149                $dataBlockFont = StringHelper::UTF8toBIFF8UnicodeLong($conditional->getStyle()->getFont()->getName());
3150            }
3151            // Font Size
3152            if ($conditional->getStyle()->getFont()->getSize() == null) {
3153                $dataBlockFont .= pack('V', 20 * 11);
3154            } else {
3155                $dataBlockFont .= pack('V', 20 * $conditional->getStyle()->getFont()->getSize());
3156            }
3157            // Font Options
3158            $dataBlockFont .= pack('V', 0);
3159            // Font weight
3160            if ($conditional->getStyle()->getFont()->getBold() == true) {
3161                $dataBlockFont .= pack('v', 0x02BC);
3162            } else {
3163                $dataBlockFont .= pack('v', 0x0190);
3164            }
3165            // Escapement type
3166            if ($conditional->getStyle()->getFont()->getSubscript() == true) {
3167                $dataBlockFont .= pack('v', 0x02);
3168                $fontEscapement = 0;
3169            } elseif ($conditional->getStyle()->getFont()->getSuperscript() == true) {
3170                $dataBlockFont .= pack('v', 0x01);
3171                $fontEscapement = 0;
3172            } else {
3173                $dataBlockFont .= pack('v', 0x00);
3174                $fontEscapement = 1;
3175            }
3176            // Underline type
3177            switch ($conditional->getStyle()->getFont()->getUnderline()) {
3178                case \PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_NONE:
3179                    $dataBlockFont .= pack('C', 0x00);
3180                    $fontUnderline = 0;
3181
3182                    break;
3183                case \PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_DOUBLE:
3184                    $dataBlockFont .= pack('C', 0x02);
3185                    $fontUnderline = 0;
3186
3187                    break;
3188                case \PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_DOUBLEACCOUNTING:
3189                    $dataBlockFont .= pack('C', 0x22);
3190                    $fontUnderline = 0;
3191
3192                    break;
3193                case \PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_SINGLE:
3194                    $dataBlockFont .= pack('C', 0x01);
3195                    $fontUnderline = 0;
3196
3197                    break;
3198                case \PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_SINGLEACCOUNTING:
3199                    $dataBlockFont .= pack('C', 0x21);
3200                    $fontUnderline = 0;
3201
3202                    break;
3203                default:
3204                    $dataBlockFont .= pack('C', 0x00);
3205                    $fontUnderline = 1;
3206
3207                    break;
3208            }
3209            // Not used (3)
3210            $dataBlockFont .= pack('vC', 0x0000, 0x00);
3211            // Font color index
3212            switch ($conditional->getStyle()->getFont()->getColor()->getRGB()) {
3213                case '000000':
3214                    $colorIdx = 0x08;
3215
3216                    break;
3217                case 'FFFFFF':
3218                    $colorIdx = 0x09;
3219
3220                    break;
3221                case 'FF0000':
3222                    $colorIdx = 0x0A;
3223
3224                    break;
3225                case '00FF00':
3226                    $colorIdx = 0x0B;
3227
3228                    break;
3229                case '0000FF':
3230                    $colorIdx = 0x0C;
3231
3232                    break;
3233                case 'FFFF00':
3234                    $colorIdx = 0x0D;
3235
3236                    break;
3237                case 'FF00FF':
3238                    $colorIdx = 0x0E;
3239
3240                    break;
3241                case '00FFFF':
3242                    $colorIdx = 0x0F;
3243
3244                    break;
3245                case '800000':
3246                    $colorIdx = 0x10;
3247
3248                    break;
3249                case '008000':
3250                    $colorIdx = 0x11;
3251
3252                    break;
3253                case '000080':
3254                    $colorIdx = 0x12;
3255
3256                    break;
3257                case '808000':
3258                    $colorIdx = 0x13;
3259
3260                    break;
3261                case '800080':
3262                    $colorIdx = 0x14;
3263
3264                    break;
3265                case '008080':
3266                    $colorIdx = 0x15;
3267
3268                    break;
3269                case 'C0C0C0':
3270                    $colorIdx = 0x16;
3271
3272                    break;
3273                case '808080':
3274                    $colorIdx = 0x17;
3275
3276                    break;
3277                case '9999FF':
3278                    $colorIdx = 0x18;
3279
3280                    break;
3281                case '993366':
3282                    $colorIdx = 0x19;
3283
3284                    break;
3285                case 'FFFFCC':
3286                    $colorIdx = 0x1A;
3287
3288                    break;
3289                case 'CCFFFF':
3290                    $colorIdx = 0x1B;
3291
3292                    break;
3293                case '660066':
3294                    $colorIdx = 0x1C;
3295
3296                    break;
3297                case 'FF8080':
3298                    $colorIdx = 0x1D;
3299
3300                    break;
3301                case '0066CC':
3302                    $colorIdx = 0x1E;
3303
3304                    break;
3305                case 'CCCCFF':
3306                    $colorIdx = 0x1F;
3307
3308                    break;
3309                case '000080':
3310                    $colorIdx = 0x20;
3311
3312                    break;
3313                case 'FF00FF':
3314                    $colorIdx = 0x21;
3315
3316                    break;
3317                case 'FFFF00':
3318                    $colorIdx = 0x22;
3319
3320                    break;
3321                case '00FFFF':
3322                    $colorIdx = 0x23;
3323
3324                    break;
3325                case '800080':
3326                    $colorIdx = 0x24;
3327
3328                    break;
3329                case '800000':
3330                    $colorIdx = 0x25;
3331
3332                    break;
3333                case '008080':
3334                    $colorIdx = 0x26;
3335
3336                    break;
3337                case '0000FF':
3338                    $colorIdx = 0x27;
3339
3340                    break;
3341                case '00CCFF':
3342                    $colorIdx = 0x28;
3343
3344                    break;
3345                case 'CCFFFF':
3346                    $colorIdx = 0x29;
3347
3348                    break;
3349                case 'CCFFCC':
3350                    $colorIdx = 0x2A;
3351
3352                    break;
3353                case 'FFFF99':
3354                    $colorIdx = 0x2B;
3355
3356                    break;
3357                case '99CCFF':
3358                    $colorIdx = 0x2C;
3359
3360                    break;
3361                case 'FF99CC':
3362                    $colorIdx = 0x2D;
3363
3364                    break;
3365                case 'CC99FF':
3366                    $colorIdx = 0x2E;
3367
3368                    break;
3369                case 'FFCC99':
3370                    $colorIdx = 0x2F;
3371
3372                    break;
3373                case '3366FF':
3374                    $colorIdx = 0x30;
3375
3376                    break;
3377                case '33CCCC':
3378                    $colorIdx = 0x31;
3379
3380                    break;
3381                case '99CC00':
3382                    $colorIdx = 0x32;
3383
3384                    break;
3385                case 'FFCC00':
3386                    $colorIdx = 0x33;
3387
3388                    break;
3389                case 'FF9900':
3390                    $colorIdx = 0x34;
3391
3392                    break;
3393                case 'FF6600':
3394                    $colorIdx = 0x35;
3395
3396                    break;
3397                case '666699':
3398                    $colorIdx = 0x36;
3399
3400                    break;
3401                case '969696':
3402                    $colorIdx = 0x37;
3403
3404                    break;
3405                case '003366':
3406                    $colorIdx = 0x38;
3407
3408                    break;
3409                case '339966':
3410                    $colorIdx = 0x39;
3411
3412                    break;
3413                case '003300':
3414                    $colorIdx = 0x3A;
3415
3416                    break;
3417                case '333300':
3418                    $colorIdx = 0x3B;
3419
3420                    break;
3421                case '993300':
3422                    $colorIdx = 0x3C;
3423
3424                    break;
3425                case '993366':
3426                    $colorIdx = 0x3D;
3427
3428                    break;
3429                case '333399':
3430                    $colorIdx = 0x3E;
3431
3432                    break;
3433                case '333333':
3434                    $colorIdx = 0x3F;
3435
3436                    break;
3437                default:
3438                    $colorIdx = 0x00;
3439
3440                    break;
3441            }
3442            $dataBlockFont .= pack('V', $colorIdx);
3443            // Not used (4)
3444            $dataBlockFont .= pack('V', 0x00000000);
3445            // Options flags for modified font attributes
3446            $optionsFlags = 0;
3447            $optionsFlagsBold = ($conditional->getStyle()->getFont()->getBold() == null ? 1 : 0);
3448            $optionsFlags |= (1 == $optionsFlagsBold ? 0x00000002 : 0);
3449            $optionsFlags |= (1 == 1 ? 0x00000008 : 0);
3450            $optionsFlags |= (1 == 1 ? 0x00000010 : 0);
3451            $optionsFlags |= (1 == 0 ? 0x00000020 : 0);
3452            $optionsFlags |= (1 == 1 ? 0x00000080 : 0);
3453            $dataBlockFont .= pack('V', $optionsFlags);
3454            // Escapement type
3455            $dataBlockFont .= pack('V', $fontEscapement);
3456            // Underline type
3457            $dataBlockFont .= pack('V', $fontUnderline);
3458            // Always
3459            $dataBlockFont .= pack('V', 0x00000000);
3460            // Always
3461            $dataBlockFont .= pack('V', 0x00000000);
3462            // Not used (8)
3463            $dataBlockFont .= pack('VV', 0x00000000, 0x00000000);
3464            // Always
3465            $dataBlockFont .= pack('v', 0x0001);
3466        }
3467        if ($bFormatAlign == 1) {
3468            $blockAlign = 0;
3469            // Alignment and text break
3470            switch ($conditional->getStyle()->getAlignment()->getHorizontal()) {
3471                case Alignment::HORIZONTAL_GENERAL:
3472                    $blockAlign = 0;
3473
3474                    break;
3475                case Alignment::HORIZONTAL_LEFT:
3476                    $blockAlign = 1;
3477
3478                    break;
3479                case Alignment::HORIZONTAL_RIGHT:
3480                    $blockAlign = 3;
3481
3482                    break;
3483                case Alignment::HORIZONTAL_CENTER:
3484                    $blockAlign = 2;
3485
3486                    break;
3487                case Alignment::HORIZONTAL_CENTER_CONTINUOUS:
3488                    $blockAlign = 6;
3489
3490                    break;
3491                case Alignment::HORIZONTAL_JUSTIFY:
3492                    $blockAlign = 5;
3493
3494                    break;
3495            }
3496            if ($conditional->getStyle()->getAlignment()->getWrapText() == true) {
3497                $blockAlign |= 1 << 3;
3498            } else {
3499                $blockAlign |= 0 << 3;
3500            }
3501            switch ($conditional->getStyle()->getAlignment()->getVertical()) {
3502                case Alignment::VERTICAL_BOTTOM:
3503                    $blockAlign = 2 << 4;
3504
3505                    break;
3506                case Alignment::VERTICAL_TOP:
3507                    $blockAlign = 0 << 4;
3508
3509                    break;
3510                case Alignment::VERTICAL_CENTER:
3511                    $blockAlign = 1 << 4;
3512
3513                    break;
3514                case Alignment::VERTICAL_JUSTIFY:
3515                    $blockAlign = 3 << 4;
3516
3517                    break;
3518            }
3519            $blockAlign |= 0 << 7;
3520
3521            // Text rotation angle
3522            $blockRotation = $conditional->getStyle()->getAlignment()->getTextRotation();
3523
3524            // Indentation
3525            $blockIndent = $conditional->getStyle()->getAlignment()->getIndent();
3526            if ($conditional->getStyle()->getAlignment()->getShrinkToFit() == true) {
3527                $blockIndent |= 1 << 4;
3528            } else {
3529                $blockIndent |= 0 << 4;
3530            }
3531            $blockIndent |= 0 << 6;
3532
3533            // Relative indentation
3534            $blockIndentRelative = 255;
3535
3536            $dataBlockAlign = pack('CCvvv', $blockAlign, $blockRotation, $blockIndent, $blockIndentRelative, 0x0000);
3537        }
3538        if ($bFormatBorder == 1) {
3539            $blockLineStyle = 0;
3540            switch ($conditional->getStyle()->getBorders()->getLeft()->getBorderStyle()) {
3541                case Border::BORDER_NONE:
3542                    $blockLineStyle |= 0x00;
3543
3544                    break;
3545                case Border::BORDER_THIN:
3546                    $blockLineStyle |= 0x01;
3547
3548                    break;
3549                case Border::BORDER_MEDIUM:
3550                    $blockLineStyle |= 0x02;
3551
3552                    break;
3553                case Border::BORDER_DASHED:
3554                    $blockLineStyle |= 0x03;
3555
3556                    break;
3557                case Border::BORDER_DOTTED:
3558                    $blockLineStyle |= 0x04;
3559
3560                    break;
3561                case Border::BORDER_THICK:
3562                    $blockLineStyle |= 0x05;
3563
3564                    break;
3565                case Border::BORDER_DOUBLE:
3566                    $blockLineStyle |= 0x06;
3567
3568                    break;
3569                case Border::BORDER_HAIR:
3570                    $blockLineStyle |= 0x07;
3571
3572                    break;
3573                case Border::BORDER_MEDIUMDASHED:
3574                    $blockLineStyle |= 0x08;
3575
3576                    break;
3577                case Border::BORDER_DASHDOT:
3578                    $blockLineStyle |= 0x09;
3579
3580                    break;
3581                case Border::BORDER_MEDIUMDASHDOT:
3582                    $blockLineStyle |= 0x0A;
3583
3584                    break;
3585                case Border::BORDER_DASHDOTDOT:
3586                    $blockLineStyle |= 0x0B;
3587
3588                    break;
3589                case Border::BORDER_MEDIUMDASHDOTDOT:
3590                    $blockLineStyle |= 0x0C;
3591
3592                    break;
3593                case Border::BORDER_SLANTDASHDOT:
3594                    $blockLineStyle |= 0x0D;
3595
3596                    break;
3597            }
3598            switch ($conditional->getStyle()->getBorders()->getRight()->getBorderStyle()) {
3599                case Border::BORDER_NONE:
3600                    $blockLineStyle |= 0x00 << 4;
3601
3602                    break;
3603                case Border::BORDER_THIN:
3604                    $blockLineStyle |= 0x01 << 4;
3605
3606                    break;
3607                case Border::BORDER_MEDIUM:
3608                    $blockLineStyle |= 0x02 << 4;
3609
3610                    break;
3611                case Border::BORDER_DASHED:
3612                    $blockLineStyle |= 0x03 << 4;
3613
3614                    break;
3615                case Border::BORDER_DOTTED:
3616                    $blockLineStyle |= 0x04 << 4;
3617
3618                    break;
3619                case Border::BORDER_THICK:
3620                    $blockLineStyle |= 0x05 << 4;
3621
3622                    break;
3623                case Border::BORDER_DOUBLE:
3624                    $blockLineStyle |= 0x06 << 4;
3625
3626                    break;
3627                case Border::BORDER_HAIR:
3628                    $blockLineStyle |= 0x07 << 4;
3629
3630                    break;
3631                case Border::BORDER_MEDIUMDASHED:
3632                    $blockLineStyle |= 0x08 << 4;
3633
3634                    break;
3635                case Border::BORDER_DASHDOT:
3636                    $blockLineStyle |= 0x09 << 4;
3637
3638                    break;
3639                case Border::BORDER_MEDIUMDASHDOT:
3640                    $blockLineStyle |= 0x0A << 4;
3641
3642                    break;
3643                case Border::BORDER_DASHDOTDOT:
3644                    $blockLineStyle |= 0x0B << 4;
3645
3646                    break;
3647                case Border::BORDER_MEDIUMDASHDOTDOT:
3648                    $blockLineStyle |= 0x0C << 4;
3649
3650                    break;
3651                case Border::BORDER_SLANTDASHDOT:
3652                    $blockLineStyle |= 0x0D << 4;
3653
3654                    break;
3655            }
3656            switch ($conditional->getStyle()->getBorders()->getTop()->getBorderStyle()) {
3657                case Border::BORDER_NONE:
3658                    $blockLineStyle |= 0x00 << 8;
3659
3660                    break;
3661                case Border::BORDER_THIN:
3662                    $blockLineStyle |= 0x01 << 8;
3663
3664                    break;
3665                case Border::BORDER_MEDIUM:
3666                    $blockLineStyle |= 0x02 << 8;
3667
3668                    break;
3669                case Border::BORDER_DASHED:
3670                    $blockLineStyle |= 0x03 << 8;
3671
3672                    break;
3673                case Border::BORDER_DOTTED:
3674                    $blockLineStyle |= 0x04 << 8;
3675
3676                    break;
3677                case Border::BORDER_THICK:
3678                    $blockLineStyle |= 0x05 << 8;
3679
3680                    break;
3681                case Border::BORDER_DOUBLE:
3682                    $blockLineStyle |= 0x06 << 8;
3683
3684                    break;
3685                case Border::BORDER_HAIR:
3686                    $blockLineStyle |= 0x07 << 8;
3687
3688                    break;
3689                case Border::BORDER_MEDIUMDASHED:
3690                    $blockLineStyle |= 0x08 << 8;
3691
3692                    break;
3693                case Border::BORDER_DASHDOT:
3694                    $blockLineStyle |= 0x09 << 8;
3695
3696                    break;
3697                case Border::BORDER_MEDIUMDASHDOT:
3698                    $blockLineStyle |= 0x0A << 8;
3699
3700                    break;
3701                case Border::BORDER_DASHDOTDOT:
3702                    $blockLineStyle |= 0x0B << 8;
3703
3704                    break;
3705                case Border::BORDER_MEDIUMDASHDOTDOT:
3706                    $blockLineStyle |= 0x0C << 8;
3707
3708                    break;
3709                case Border::BORDER_SLANTDASHDOT:
3710                    $blockLineStyle |= 0x0D << 8;
3711
3712                    break;
3713            }
3714            switch ($conditional->getStyle()->getBorders()->getBottom()->getBorderStyle()) {
3715                case Border::BORDER_NONE:
3716                    $blockLineStyle |= 0x00 << 12;
3717
3718                    break;
3719                case Border::BORDER_THIN:
3720                    $blockLineStyle |= 0x01 << 12;
3721
3722                    break;
3723                case Border::BORDER_MEDIUM:
3724                    $blockLineStyle |= 0x02 << 12;
3725
3726                    break;
3727                case Border::BORDER_DASHED:
3728                    $blockLineStyle |= 0x03 << 12;
3729
3730                    break;
3731                case Border::BORDER_DOTTED:
3732                    $blockLineStyle |= 0x04 << 12;
3733
3734                    break;
3735                case Border::BORDER_THICK:
3736                    $blockLineStyle |= 0x05 << 12;
3737
3738                    break;
3739                case Border::BORDER_DOUBLE:
3740                    $blockLineStyle |= 0x06 << 12;
3741
3742                    break;
3743                case Border::BORDER_HAIR:
3744                    $blockLineStyle |= 0x07 << 12;
3745
3746                    break;
3747                case Border::BORDER_MEDIUMDASHED:
3748                    $blockLineStyle |= 0x08 << 12;
3749
3750                    break;
3751                case Border::BORDER_DASHDOT:
3752                    $blockLineStyle |= 0x09 << 12;
3753
3754                    break;
3755                case Border::BORDER_MEDIUMDASHDOT:
3756                    $blockLineStyle |= 0x0A << 12;
3757
3758                    break;
3759                case Border::BORDER_DASHDOTDOT:
3760                    $blockLineStyle |= 0x0B << 12;
3761
3762                    break;
3763                case Border::BORDER_MEDIUMDASHDOTDOT:
3764                    $blockLineStyle |= 0x0C << 12;
3765
3766                    break;
3767                case Border::BORDER_SLANTDASHDOT:
3768                    $blockLineStyle |= 0x0D << 12;
3769
3770                    break;
3771            }
3772            //@todo writeCFRule() => $blockLineStyle => Index Color for left line
3773            //@todo writeCFRule() => $blockLineStyle => Index Color for right line
3774            //@todo writeCFRule() => $blockLineStyle => Top-left to bottom-right on/off
3775            //@todo writeCFRule() => $blockLineStyle => Bottom-left to top-right on/off
3776            $blockColor = 0;
3777            //@todo writeCFRule() => $blockColor => Index Color for top line
3778            //@todo writeCFRule() => $blockColor => Index Color for bottom line
3779            //@todo writeCFRule() => $blockColor => Index Color for diagonal line
3780            switch ($conditional->getStyle()->getBorders()->getDiagonal()->getBorderStyle()) {
3781                case Border::BORDER_NONE:
3782                    $blockColor |= 0x00 << 21;
3783
3784                    break;
3785                case Border::BORDER_THIN:
3786                    $blockColor |= 0x01 << 21;
3787
3788                    break;
3789                case Border::BORDER_MEDIUM:
3790                    $blockColor |= 0x02 << 21;
3791
3792                    break;
3793                case Border::BORDER_DASHED:
3794                    $blockColor |= 0x03 << 21;
3795
3796                    break;
3797                case Border::BORDER_DOTTED:
3798                    $blockColor |= 0x04 << 21;
3799
3800                    break;
3801                case Border::BORDER_THICK:
3802                    $blockColor |= 0x05 << 21;
3803
3804                    break;
3805                case Border::BORDER_DOUBLE:
3806                    $blockColor |= 0x06 << 21;
3807
3808                    break;
3809                case Border::BORDER_HAIR:
3810                    $blockColor |= 0x07 << 21;
3811
3812                    break;
3813                case Border::BORDER_MEDIUMDASHED:
3814                    $blockColor |= 0x08 << 21;
3815
3816                    break;
3817                case Border::BORDER_DASHDOT:
3818                    $blockColor |= 0x09 << 21;
3819
3820                    break;
3821                case Border::BORDER_MEDIUMDASHDOT:
3822                    $blockColor |= 0x0A << 21;
3823
3824                    break;
3825                case Border::BORDER_DASHDOTDOT:
3826                    $blockColor |= 0x0B << 21;
3827
3828                    break;
3829                case Border::BORDER_MEDIUMDASHDOTDOT:
3830                    $blockColor |= 0x0C << 21;
3831
3832                    break;
3833                case Border::BORDER_SLANTDASHDOT:
3834                    $blockColor |= 0x0D << 21;
3835
3836                    break;
3837            }
3838            $dataBlockBorder = pack('vv', $blockLineStyle, $blockColor);
3839        }
3840        if ($bFormatFill == 1) {
3841            // Fill Patern Style
3842            $blockFillPatternStyle = 0;
3843            switch ($conditional->getStyle()->getFill()->getFillType()) {
3844                case Fill::FILL_NONE:
3845                    $blockFillPatternStyle = 0x00;
3846
3847                    break;
3848                case Fill::FILL_SOLID:
3849                    $blockFillPatternStyle = 0x01;
3850
3851                    break;
3852                case Fill::FILL_PATTERN_MEDIUMGRAY:
3853                    $blockFillPatternStyle = 0x02;
3854
3855                    break;
3856                case Fill::FILL_PATTERN_DARKGRAY:
3857                    $blockFillPatternStyle = 0x03;
3858
3859                    break;
3860                case Fill::FILL_PATTERN_LIGHTGRAY:
3861                    $blockFillPatternStyle = 0x04;
3862
3863                    break;
3864                case Fill::FILL_PATTERN_DARKHORIZONTAL:
3865                    $blockFillPatternStyle = 0x05;
3866
3867                    break;
3868                case Fill::FILL_PATTERN_DARKVERTICAL:
3869                    $blockFillPatternStyle = 0x06;
3870
3871                    break;
3872                case Fill::FILL_PATTERN_DARKDOWN:
3873                    $blockFillPatternStyle = 0x07;
3874
3875                    break;
3876                case Fill::FILL_PATTERN_DARKUP:
3877                    $blockFillPatternStyle = 0x08;
3878
3879                    break;
3880                case Fill::FILL_PATTERN_DARKGRID:
3881                    $blockFillPatternStyle = 0x09;
3882
3883                    break;
3884                case Fill::FILL_PATTERN_DARKTRELLIS:
3885                    $blockFillPatternStyle = 0x0A;
3886
3887                    break;
3888                case Fill::FILL_PATTERN_LIGHTHORIZONTAL:
3889                    $blockFillPatternStyle = 0x0B;
3890
3891                    break;
3892                case Fill::FILL_PATTERN_LIGHTVERTICAL:
3893                    $blockFillPatternStyle = 0x0C;
3894
3895                    break;
3896                case Fill::FILL_PATTERN_LIGHTDOWN:
3897                    $blockFillPatternStyle = 0x0D;
3898
3899                    break;
3900                case Fill::FILL_PATTERN_LIGHTUP:
3901                    $blockFillPatternStyle = 0x0E;
3902
3903                    break;
3904                case Fill::FILL_PATTERN_LIGHTGRID:
3905                    $blockFillPatternStyle = 0x0F;
3906
3907                    break;
3908                case Fill::FILL_PATTERN_LIGHTTRELLIS:
3909                    $blockFillPatternStyle = 0x10;
3910
3911                    break;
3912                case Fill::FILL_PATTERN_GRAY125:
3913                    $blockFillPatternStyle = 0x11;
3914
3915                    break;
3916                case Fill::FILL_PATTERN_GRAY0625:
3917                    $blockFillPatternStyle = 0x12;
3918
3919                    break;
3920                case Fill::FILL_GRADIENT_LINEAR:
3921                    $blockFillPatternStyle = 0x00;
3922
3923                    break; // does not exist in BIFF8
3924                case Fill::FILL_GRADIENT_PATH:
3925                    $blockFillPatternStyle = 0x00;
3926
3927                    break; // does not exist in BIFF8
3928                default:
3929                    $blockFillPatternStyle = 0x00;
3930
3931                    break;
3932            }
3933            // Color
3934            switch ($conditional->getStyle()->getFill()->getStartColor()->getRGB()) {
3935                case '000000':
3936                    $colorIdxBg = 0x08;
3937
3938                    break;
3939                case 'FFFFFF':
3940                    $colorIdxBg = 0x09;
3941
3942                    break;
3943                case 'FF0000':
3944                    $colorIdxBg = 0x0A;
3945
3946                    break;
3947                case '00FF00':
3948                    $colorIdxBg = 0x0B;
3949
3950                    break;
3951                case '0000FF':
3952                    $colorIdxBg = 0x0C;
3953
3954                    break;
3955                case 'FFFF00':
3956                    $colorIdxBg = 0x0D;
3957
3958                    break;
3959                case 'FF00FF':
3960                    $colorIdxBg = 0x0E;
3961
3962                    break;
3963                case '00FFFF':
3964                    $colorIdxBg = 0x0F;
3965
3966                    break;
3967                case '800000':
3968                    $colorIdxBg = 0x10;
3969
3970                    break;
3971                case '008000':
3972                    $colorIdxBg = 0x11;
3973
3974                    break;
3975                case '000080':
3976                    $colorIdxBg = 0x12;
3977
3978                    break;
3979                case '808000':
3980                    $colorIdxBg = 0x13;
3981
3982                    break;
3983                case '800080':
3984                    $colorIdxBg = 0x14;
3985
3986                    break;
3987                case '008080':
3988                    $colorIdxBg = 0x15;
3989
3990                    break;
3991                case 'C0C0C0':
3992                    $colorIdxBg = 0x16;
3993
3994                    break;
3995                case '808080':
3996                    $colorIdxBg = 0x17;
3997
3998                    break;
3999                case '9999FF':
4000                    $colorIdxBg = 0x18;
4001
4002                    break;
4003                case '993366':
4004                    $colorIdxBg = 0x19;
4005
4006                    break;
4007                case 'FFFFCC':
4008                    $colorIdxBg = 0x1A;
4009
4010                    break;
4011                case 'CCFFFF':
4012                    $colorIdxBg = 0x1B;
4013
4014                    break;
4015                case '660066':
4016                    $colorIdxBg = 0x1C;
4017
4018                    break;
4019                case 'FF8080':
4020                    $colorIdxBg = 0x1D;
4021
4022                    break;
4023                case '0066CC':
4024                    $colorIdxBg = 0x1E;
4025
4026                    break;
4027                case 'CCCCFF':
4028                    $colorIdxBg = 0x1F;
4029
4030                    break;
4031                case '000080':
4032                    $colorIdxBg = 0x20;
4033
4034                    break;
4035                case 'FF00FF':
4036                    $colorIdxBg = 0x21;
4037
4038                    break;
4039                case 'FFFF00':
4040                    $colorIdxBg = 0x22;
4041
4042                    break;
4043                case '00FFFF':
4044                    $colorIdxBg = 0x23;
4045
4046                    break;
4047                case '800080':
4048                    $colorIdxBg = 0x24;
4049
4050                    break;
4051                case '800000':
4052                    $colorIdxBg = 0x25;
4053
4054                    break;
4055                case '008080':
4056                    $colorIdxBg = 0x26;
4057
4058                    break;
4059                case '0000FF':
4060                    $colorIdxBg = 0x27;
4061
4062                    break;
4063                case '00CCFF':
4064                    $colorIdxBg = 0x28;
4065
4066                    break;
4067                case 'CCFFFF':
4068                    $colorIdxBg = 0x29;
4069
4070                    break;
4071                case 'CCFFCC':
4072                    $colorIdxBg = 0x2A;
4073
4074                    break;
4075                case 'FFFF99':
4076                    $colorIdxBg = 0x2B;
4077
4078                    break;
4079                case '99CCFF':
4080                    $colorIdxBg = 0x2C;
4081
4082                    break;
4083                case 'FF99CC':
4084                    $colorIdxBg = 0x2D;
4085
4086                    break;
4087                case 'CC99FF':
4088                    $colorIdxBg = 0x2E;
4089
4090                    break;
4091                case 'FFCC99':
4092                    $colorIdxBg = 0x2F;
4093
4094                    break;
4095                case '3366FF':
4096                    $colorIdxBg = 0x30;
4097
4098                    break;
4099                case '33CCCC':
4100                    $colorIdxBg = 0x31;
4101
4102                    break;
4103                case '99CC00':
4104                    $colorIdxBg = 0x32;
4105
4106                    break;
4107                case 'FFCC00':
4108                    $colorIdxBg = 0x33;
4109
4110                    break;
4111                case 'FF9900':
4112                    $colorIdxBg = 0x34;
4113
4114                    break;
4115                case 'FF6600':
4116                    $colorIdxBg = 0x35;
4117
4118                    break;
4119                case '666699':
4120                    $colorIdxBg = 0x36;
4121
4122                    break;
4123                case '969696':
4124                    $colorIdxBg = 0x37;
4125
4126                    break;
4127                case '003366':
4128                    $colorIdxBg = 0x38;
4129
4130                    break;
4131                case '339966':
4132                    $colorIdxBg = 0x39;
4133
4134                    break;
4135                case '003300':
4136                    $colorIdxBg = 0x3A;
4137
4138                    break;
4139                case '333300':
4140                    $colorIdxBg = 0x3B;
4141
4142                    break;
4143                case '993300':
4144                    $colorIdxBg = 0x3C;
4145
4146                    break;
4147                case '993366':
4148                    $colorIdxBg = 0x3D;
4149
4150                    break;
4151                case '333399':
4152                    $colorIdxBg = 0x3E;
4153
4154                    break;
4155                case '333333':
4156                    $colorIdxBg = 0x3F;
4157
4158                    break;
4159                default:
4160                          $colorIdxBg = 0x41;
4161
4162                    break;
4163            }
4164            // Fg Color
4165            switch ($conditional->getStyle()->getFill()->getEndColor()->getRGB()) {
4166                case '000000':
4167                    $colorIdxFg = 0x08;
4168
4169                    break;
4170                case 'FFFFFF':
4171                    $colorIdxFg = 0x09;
4172
4173                    break;
4174                case 'FF0000':
4175                    $colorIdxFg = 0x0A;
4176
4177                    break;
4178                case '00FF00':
4179                    $colorIdxFg = 0x0B;
4180
4181                    break;
4182                case '0000FF':
4183                    $colorIdxFg = 0x0C;
4184
4185                    break;
4186                case 'FFFF00':
4187                    $colorIdxFg = 0x0D;
4188
4189                    break;
4190                case 'FF00FF':
4191                    $colorIdxFg = 0x0E;
4192
4193                    break;
4194                case '00FFFF':
4195                    $colorIdxFg = 0x0F;
4196
4197                    break;
4198                case '800000':
4199                    $colorIdxFg = 0x10;
4200
4201                    break;
4202                case '008000':
4203                    $colorIdxFg = 0x11;
4204
4205                    break;
4206                case '000080':
4207                    $colorIdxFg = 0x12;
4208
4209                    break;
4210                case '808000':
4211                    $colorIdxFg = 0x13;
4212
4213                    break;
4214                case '800080':
4215                    $colorIdxFg = 0x14;
4216
4217                    break;
4218                case '008080':
4219                    $colorIdxFg = 0x15;
4220
4221                    break;
4222                case 'C0C0C0':
4223                    $colorIdxFg = 0x16;
4224
4225                    break;
4226                case '808080':
4227                    $colorIdxFg = 0x17;
4228
4229                    break;
4230                case '9999FF':
4231                    $colorIdxFg = 0x18;
4232
4233                    break;
4234                case '993366':
4235                    $colorIdxFg = 0x19;
4236
4237                    break;
4238                case 'FFFFCC':
4239                    $colorIdxFg = 0x1A;
4240
4241                    break;
4242                case 'CCFFFF':
4243                    $colorIdxFg = 0x1B;
4244
4245                    break;
4246                case '660066':
4247                    $colorIdxFg = 0x1C;
4248
4249                    break;
4250                case 'FF8080':
4251                    $colorIdxFg = 0x1D;
4252
4253                    break;
4254                case '0066CC':
4255                    $colorIdxFg = 0x1E;
4256
4257                    break;
4258                case 'CCCCFF':
4259                    $colorIdxFg = 0x1F;
4260
4261                    break;
4262                case '000080':
4263                    $colorIdxFg = 0x20;
4264
4265                    break;
4266                case 'FF00FF':
4267                    $colorIdxFg = 0x21;
4268
4269                    break;
4270                case 'FFFF00':
4271                    $colorIdxFg = 0x22;
4272
4273                    break;
4274                case '00FFFF':
4275                    $colorIdxFg = 0x23;
4276
4277                    break;
4278                case '800080':
4279                    $colorIdxFg = 0x24;
4280
4281                    break;
4282                case '800000':
4283                    $colorIdxFg = 0x25;
4284
4285                    break;
4286                case '008080':
4287                    $colorIdxFg = 0x26;
4288
4289                    break;
4290                case '0000FF':
4291                    $colorIdxFg = 0x27;
4292
4293                    break;
4294                case '00CCFF':
4295                    $colorIdxFg = 0x28;
4296
4297                    break;
4298                case 'CCFFFF':
4299                    $colorIdxFg = 0x29;
4300
4301                    break;
4302                case 'CCFFCC':
4303                    $colorIdxFg = 0x2A;
4304
4305                    break;
4306                case 'FFFF99':
4307                    $colorIdxFg = 0x2B;
4308
4309                    break;
4310                case '99CCFF':
4311                    $colorIdxFg = 0x2C;
4312
4313                    break;
4314                case 'FF99CC':
4315                    $colorIdxFg = 0x2D;
4316
4317                    break;
4318                case 'CC99FF':
4319                    $colorIdxFg = 0x2E;
4320
4321                    break;
4322                case 'FFCC99':
4323                    $colorIdxFg = 0x2F;
4324
4325                    break;
4326                case '3366FF':
4327                    $colorIdxFg = 0x30;
4328
4329                    break;
4330                case '33CCCC':
4331                    $colorIdxFg = 0x31;
4332
4333                    break;
4334                case '99CC00':
4335                    $colorIdxFg = 0x32;
4336
4337                    break;
4338                case 'FFCC00':
4339                    $colorIdxFg = 0x33;
4340
4341                    break;
4342                case 'FF9900':
4343                    $colorIdxFg = 0x34;
4344
4345                    break;
4346                case 'FF6600':
4347                    $colorIdxFg = 0x35;
4348
4349                    break;
4350                case '666699':
4351                    $colorIdxFg = 0x36;
4352
4353                    break;
4354                case '969696':
4355                    $colorIdxFg = 0x37;
4356
4357                    break;
4358                case '003366':
4359                    $colorIdxFg = 0x38;
4360
4361                    break;
4362                case '339966':
4363                    $colorIdxFg = 0x39;
4364
4365                    break;
4366                case '003300':
4367                    $colorIdxFg = 0x3A;
4368
4369                    break;
4370                case '333300':
4371                    $colorIdxFg = 0x3B;
4372
4373                    break;
4374                case '993300':
4375                    $colorIdxFg = 0x3C;
4376
4377                    break;
4378                case '993366':
4379                    $colorIdxFg = 0x3D;
4380
4381                    break;
4382                case '333399':
4383                    $colorIdxFg = 0x3E;
4384
4385                    break;
4386                case '333333':
4387                    $colorIdxFg = 0x3F;
4388
4389                    break;
4390                default:
4391                          $colorIdxFg = 0x40;
4392
4393                    break;
4394            }
4395            $dataBlockFill = pack('v', $blockFillPatternStyle);
4396            $dataBlockFill .= pack('v', $colorIdxFg | ($colorIdxBg << 7));
4397        }
4398        if ($bFormatProt == 1) {
4399            $dataBlockProtection = 0;
4400            if ($conditional->getStyle()->getProtection()->getLocked() == Protection::PROTECTION_PROTECTED) {
4401                $dataBlockProtection = 1;
4402            }
4403            if ($conditional->getStyle()->getProtection()->getHidden() == Protection::PROTECTION_PROTECTED) {
4404                $dataBlockProtection = 1 << 1;
4405            }
4406        }
4407
4408        $data = pack('CCvvVv', $type, $operatorType, $szValue1, $szValue2, $flags, 0x0000);
4409        if ($bFormatFont == 1) { // Block Formatting : OK
4410            $data .= $dataBlockFont;
4411        }
4412        if ($bFormatAlign == 1) {
4413            $data .= $dataBlockAlign;
4414        }
4415        if ($bFormatBorder == 1) {
4416            $data .= $dataBlockBorder;
4417        }
4418        if ($bFormatFill == 1) { // Block Formatting : OK
4419            $data .= $dataBlockFill;
4420        }
4421        if ($bFormatProt == 1) {
4422            $data .= $dataBlockProtection;
4423        }
4424        if ($operand1 !== null) {
4425            $data .= $operand1;
4426        }
4427        if ($operand2 !== null) {
4428            $data .= $operand2;
4429        }
4430        $header = pack('vv', $record, strlen($data));
4431        $this->append($header . $data);
4432    }
4433
4434    /**
4435     * Write CFHeader record.
4436     */
4437    private function writeCFHeader()
4438    {
4439        $record = 0x01B0; // Record identifier
4440        $length = 0x0016; // Bytes to follow
4441
4442        $numColumnMin = null;
4443        $numColumnMax = null;
4444        $numRowMin = null;
4445        $numRowMax = null;
4446        $arrConditional = [];
4447        foreach ($this->phpSheet->getConditionalStylesCollection() as $cellCoordinate => $conditionalStyles) {
4448            foreach ($conditionalStyles as $conditional) {
4449                if ($conditional->getConditionType() == Conditional::CONDITION_EXPRESSION
4450                        || $conditional->getConditionType() == Conditional::CONDITION_CELLIS) {
4451                    if (!in_array($conditional->getHashCode(), $arrConditional)) {
4452                        $arrConditional[] = $conditional->getHashCode();
4453                    }
4454                    // Cells
4455                    $arrCoord = Coordinate::coordinateFromString($cellCoordinate);
4456                    if (!is_numeric($arrCoord[0])) {
4457                        $arrCoord[0] = Coordinate::columnIndexFromString($arrCoord[0]);
4458                    }
4459                    if ($numColumnMin === null || ($numColumnMin > $arrCoord[0])) {
4460                        $numColumnMin = $arrCoord[0];
4461                    }
4462                    if ($numColumnMax === null || ($numColumnMax < $arrCoord[0])) {
4463                        $numColumnMax = $arrCoord[0];
4464                    }
4465                    if ($numRowMin === null || ($numRowMin > $arrCoord[1])) {
4466                        $numRowMin = $arrCoord[1];
4467                    }
4468                    if ($numRowMax === null || ($numRowMax < $arrCoord[1])) {
4469                        $numRowMax = $arrCoord[1];
4470                    }
4471                }
4472            }
4473        }
4474        $needRedraw = 1;
4475        $cellRange = pack('vvvv', $numRowMin - 1, $numRowMax - 1, $numColumnMin - 1, $numColumnMax - 1);
4476
4477        $header = pack('vv', $record, $length);
4478        $data = pack('vv', count($arrConditional), $needRedraw);
4479        $data .= $cellRange;
4480        $data .= pack('v', 0x0001);
4481        $data .= $cellRange;
4482        $this->append($header . $data);
4483    }
4484}
4485