Subversion Repositories eFlore/Applications.cel

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
2388 jpm 1
<?php
2
/**
3
 * PHPExcel
4
 *
5
 * Copyright (c) 2006 - 2013 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 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
24
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
25
 * @version    ##VERSION##, ##DATE##
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 - 2013 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
}