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