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