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