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