visualizer / vendor / phpoffice / phpexcel / Classes / PHPExcel / Writer / Excel5 / Worksheet.php

Worksheet.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.0.3, at vendor/phpoffice/phpexcel/Classes/PHPExcel/Writer/Excel5/Worksheet.php

3,681 lines 121.1 KB

Raw Download Zip

1	<?php
2	/**
3	* PHPExcel
4	*
5	* Copyright (c) 2006 - 2014 PHPExcel
6	*
7	* This library is free software; you can redistribute it and/or
8	* modify it under the terms of the GNU Lesser General Public
9	* License as published by the Free Software Foundation; either
10	* version 2.1 of the License, or (at your option) any later version.
11	*
12	* This library is distributed in the hope that it will be useful,
13	* but WITHOUT ANY WARRANTY; without even the implied warranty of
14	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15	* Lesser General Public License for more details.
16	*
17	* You should have received a copy of the GNU Lesser General Public
18	* License along with this library; if not, write to the Free Software
19	* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
20	*
21	* @category PHPExcel
22	* @package PHPExcel_Writer_Excel5
23	* @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
24	* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25	* @version ##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 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
70	*/
71	class PHPExcel_Writer_Excel5_Worksheet extends PHPExcel_Writer_Excel5_BIFFwriter
72	{
73	/**
74	* Formula parser
75	*
76	* @var PHPExcel_Writer_Excel5_Parser
77	*/
78	private $_parser;
79
80	/**
81	* Maximum number of characters for a string (LABEL record in BIFF5)
82	* @var integer
83	*/
84	public $_xls_strmax;
85
86	/**
87	* Array containing format information for columns
88	* @var array
89	*/
90	public $_colinfo;
91
92	/**
93	* Array containing the selected area for the worksheet
94	* @var array
95	*/
96	public $_selection;
97
98	/**
99	* The active pane for the worksheet
100	* @var integer
101	*/
102	public $_active_pane;
103
104	/**
105	* Whether to use outline.
106	* @var integer
107	*/
108	public $_outline_on;
109
110	/**
111	* Auto outline styles.
112	* @var bool
113	*/
114	public $_outline_style;
115
116	/**
117	* Whether to have outline summary below.
118	* @var bool
119	*/
120	public $_outline_below;
121
122	/**
123	* Whether to have outline summary at the right.
124	* @var bool
125	*/
126	public $_outline_right;
127
128	/**
129	* Reference to the total number of strings in the workbook
130	* @var integer
131	*/
132	public $_str_total;
133
134	/**
135	* Reference to the number of unique strings in the workbook
136	* @var integer
137	*/
138	public $_str_unique;
139
140	/**
141	* Reference to the array containing all the unique strings in the workbook
142	* @var array
143	*/
144	public $_str_table;
145
146	/**
147	* Color cache
148	*/
149	private $_colors;
150
151	/**
152	* Index of first used row (at least 0)
153	* @var int
154	*/
155	private $_firstRowIndex;
156
157	/**
158	* Index of last used row. (no used rows means -1)
159	* @var int
160	*/
161	private $_lastRowIndex;
162
163	/**
164	* Index of first used column (at least 0)
165	* @var int
166	*/
167	private $_firstColumnIndex;
168
169	/**
170	* Index of last used column (no used columns means -1)
171	* @var int
172	*/
173	private $_lastColumnIndex;
174
175	/**
176	* Sheet object
177	* @var PHPExcel_Worksheet
178	*/
179	public $_phpSheet;
180
181	/**
182	* Count cell style Xfs
183	*
184	* @var int
185	*/
186	private $_countCellStyleXfs;
187
188	/**
189	* Escher object corresponding to MSODRAWING
190	*
191	* @var PHPExcel_Shared_Escher
192	*/
193	private $_escher;
194
195	/**
196	* Array of font hashes associated to FONT records index
197	*
198	* @var array
199	*/
200	public $_fntHashIndex;
201
202	/**
203	* Constructor
204	*
205	* @param int &$str_total Total number of strings
206	* @param int &$str_unique Total number of unique strings
207	* @param array &$str_table String Table
208	* @param array &$colors Colour Table
209	* @param mixed $parser The formula parser created for the Workbook
210	* @param boolean $preCalculateFormulas Flag indicating whether formulas should be calculated or just written
211	* @param string $phpSheet The worksheet to write
212	* @param PHPExcel_Worksheet $phpSheet
213	*/
214	public function __construct(&$str_total, &$str_unique, &$str_table, &$colors,
215	$parser, $preCalculateFormulas, $phpSheet)
216	{
217	// It needs to call its parent's constructor explicitly
218	parent::__construct();
219
220	// change BIFFwriter limit for CONTINUE records
221	// $this->_limit = 8224;
222
223
224	$this->_preCalculateFormulas = $preCalculateFormulas;
225	$this->_str_total = &$str_total;
226	$this->_str_unique = &$str_unique;
227	$this->_str_table = &$str_table;
228	$this->_colors = &$colors;
229	$this->_parser = $parser;
230
231	$this->_phpSheet = $phpSheet;
232
233	//$this->ext_sheets = array();
234	//$this->offset = 0;
235	$this->_xls_strmax = 255;
236	$this->_colinfo = array();
237	$this->_selection = array(0,0,0,0);
238	$this->_active_pane = 3;
239
240	$this->_print_headers = 0;
241
242	$this->_outline_style = 0;
243	$this->_outline_below = 1;
244	$this->_outline_right = 1;
245	$this->_outline_on = 1;
246
247	$this->_fntHashIndex = array();
248
249	// calculate values for DIMENSIONS record
250	$minR = 1;
251	$minC = 'A';
252
253	$maxR = $this->_phpSheet->getHighestRow();
254	$maxC = $this->_phpSheet->getHighestColumn();
255
256	// Determine lowest and highest column and row
257	// $this->_firstRowIndex = ($minR > 65535) ? 65535 : $minR;
258	$this->_lastRowIndex = ($maxR > 65535) ? 65535 : $maxR ;
259
260	$this->_firstColumnIndex = PHPExcel_Cell::columnIndexFromString($minC);
261	$this->_lastColumnIndex = PHPExcel_Cell::columnIndexFromString($maxC);
262
263	// if ($this->_firstColumnIndex > 255) $this->_firstColumnIndex = 255;
264	if ($this->_lastColumnIndex > 255) $this->_lastColumnIndex = 255;
265
266	$this->_countCellStyleXfs = count($phpSheet->getParent()->getCellStyleXfCollection());
267	}
268
269	/**
270	* Add data to the beginning of the workbook (note the reverse order)
271	* and to the end of the workbook.
272	*
273	* @access public
274	* @see PHPExcel_Writer_Excel5_Workbook::storeWorkbook()
275	*/
276	function close()
277	{
278	$_phpSheet = $this->_phpSheet;
279
280	$num_sheets = $_phpSheet->getParent()->getSheetCount();
281
282	// Write BOF record
283	$this->_storeBof(0x0010);
284
285	// Write PRINTHEADERS
286	$this->_writePrintHeaders();
287
288	// Write PRINTGRIDLINES
289	$this->_writePrintGridlines();
290
291	// Write GRIDSET
292	$this->_writeGridset();
293
294	// Calculate column widths
295	$_phpSheet->calculateColumnWidths();
296
297	// Column dimensions
298	if (($defaultWidth = $_phpSheet->getDefaultColumnDimension()->getWidth()) < 0) {
299	$defaultWidth = PHPExcel_Shared_Font::getDefaultColumnWidthByFont($_phpSheet->getParent()->getDefaultStyle()->getFont());
300	}
301
302	$columnDimensions = $_phpSheet->getColumnDimensions();
303	$maxCol = $this->_lastColumnIndex -1;
304	for ($i = 0; $i <= $maxCol; ++$i) {
305	$hidden = 0;
306	$level = 0;
307	$xfIndex = 15; // there are 15 cell style Xfs
308
309	$width = $defaultWidth;
310
311	$columnLetter = PHPExcel_Cell::stringFromColumnIndex($i);
312	if (isset($columnDimensions[$columnLetter])) {
313	$columnDimension = $columnDimensions[$columnLetter];
314	if ($columnDimension->getWidth() >= 0) {
315	$width = $columnDimension->getWidth();
316	}
317	$hidden = $columnDimension->getVisible() ? 0 : 1;
318	$level = $columnDimension->getOutlineLevel();
319	$xfIndex = $columnDimension->getXfIndex() + 15; // there are 15 cell style Xfs
320	}
321
322	// Components of _colinfo:
323	// $firstcol first column on the range
324	// $lastcol last column on the range
325	// $width width to set
326	// $xfIndex The optional cell style Xf index to apply to the columns
327	// $hidden The optional hidden atribute
328	// $level The optional outline level
329	$this->_colinfo[] = array($i, $i, $width, $xfIndex, $hidden, $level);
330	}
331
332	// Write GUTS
333	$this->_writeGuts();
334
335	// Write DEFAULTROWHEIGHT
336	$this->_writeDefaultRowHeight();
337
338	// Write WSBOOL
339	$this->_writeWsbool();
340
341	// Write horizontal and vertical page breaks
342	$this->_writeBreaks();
343
344	// Write page header
345	$this->_writeHeader();
346
347	// Write page footer
348	$this->_writeFooter();
349
350	// Write page horizontal centering
351	$this->_writeHcenter();
352
353	// Write page vertical centering
354	$this->_writeVcenter();
355
356	// Write left margin
357	$this->_writeMarginLeft();
358
359	// Write right margin
360	$this->_writeMarginRight();
361
362	// Write top margin
363	$this->_writeMarginTop();
364
365	// Write bottom margin
366	$this->_writeMarginBottom();
367
368	// Write page setup
369	$this->_writeSetup();
370
371	// Write sheet protection
372	$this->_writeProtect();
373
374	// Write SCENPROTECT
375	$this->_writeScenProtect();
376
377	// Write OBJECTPROTECT
378	$this->_writeObjectProtect();
379
380	// Write sheet password
381	$this->_writePassword();
382
383	// Write DEFCOLWIDTH record
384	$this->_writeDefcol();
385
386	// Write the COLINFO records if they exist
387	if (!empty($this->_colinfo)) {
388	$colcount = count($this->_colinfo);
389	for ($i = 0; $i < $colcount; ++$i) {
390	$this->_writeColinfo($this->_colinfo[$i]);
391	}
392	}
393	$autoFilterRange = $_phpSheet->getAutoFilter()->getRange();
394	if (!empty($autoFilterRange)) {
395	// Write AUTOFILTERINFO
396	$this->_writeAutoFilterInfo();
397	}
398
399	// Write sheet dimensions
400	$this->_writeDimensions();
401
402	// Row dimensions
403	foreach ($_phpSheet->getRowDimensions() as $rowDimension) {
404	$xfIndex = $rowDimension->getXfIndex() + 15; // there are 15 cellXfs
405	$this->_writeRow( $rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), $xfIndex, ($rowDimension->getVisible() ? '0' : '1'), $rowDimension->getOutlineLevel() );
406	}
407
408	// Write Cells
409	foreach ($_phpSheet->getCellCollection() as $cellID) {
410	$cell = $_phpSheet->getCell($cellID);
411	$row = $cell->getRow() - 1;
412	$column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
413
414	// Don't break Excel!
415	// if ($row + 1 > 65536 or $column + 1 > 256) {
416	if ($row > 65535 \|\| $column > 255) {
417	break;
418	}
419
420	// Write cell value
421	$xfIndex = $cell->getXfIndex() + 15; // there are 15 cell style Xfs
422
423	$cVal = $cell->getValue();
424	if ($cVal instanceof PHPExcel_RichText) {
425	// $this->_writeString($row, $column, $cVal->getPlainText(), $xfIndex);
426	$arrcRun = array();
427	$str_len = PHPExcel_Shared_String::CountCharacters($cVal->getPlainText(), 'UTF-8');
428	$str_pos = 0;
429	$elements = $cVal->getRichTextElements();
430	foreach ($elements as $element) {
431	// FONT Index
432	if ($element instanceof PHPExcel_RichText_Run) {
433	$str_fontidx = $this->_fntHashIndex[$element->getFont()->getHashCode()];
434	}
435	else {
436	$str_fontidx = 0;
437	}
438	$arrcRun[] = array('strlen' => $str_pos, 'fontidx' => $str_fontidx);
439	// Position FROM
440	$str_pos += PHPExcel_Shared_String::CountCharacters($element->getText(), 'UTF-8');
441	}
442	$this->_writeRichTextString($row, $column, $cVal->getPlainText(), $xfIndex, $arrcRun);
443	} else {
444	switch ($cell->getDatatype()) {
445	case PHPExcel_Cell_DataType::TYPE_STRING:
446	case PHPExcel_Cell_DataType::TYPE_NULL:
447	if ($cVal === '' \|\| $cVal === null) {
448	$this->_writeBlank($row, $column, $xfIndex);
449	} else {
450	$this->_writeString($row, $column, $cVal, $xfIndex);
451	}
452	break;
453
454	case PHPExcel_Cell_DataType::TYPE_NUMERIC:
455	$this->_writeNumber($row, $column, $cVal, $xfIndex);
456	break;
457
458	case PHPExcel_Cell_DataType::TYPE_FORMULA:
459	$calculatedValue = $this->_preCalculateFormulas ?
460	$cell->getCalculatedValue() : null;
461	$this->_writeFormula($row, $column, $cVal, $xfIndex, $calculatedValue);
462	break;
463
464	case PHPExcel_Cell_DataType::TYPE_BOOL:
465	$this->_writeBoolErr($row, $column, $cVal, 0, $xfIndex);
466	break;
467
468	case PHPExcel_Cell_DataType::TYPE_ERROR:
469	$this->_writeBoolErr($row, $column, self::_mapErrorCode($cVal), 1, $xfIndex);
470	break;
471
472	}
473	}
474	}
475
476	// Append
477	$this->_writeMsoDrawing();
478
479	// Write WINDOW2 record
480	$this->_writeWindow2();
481
482	// Write PLV record
483	$this->_writePageLayoutView();
484
485	// Write ZOOM record
486	$this->_writeZoom();
487	if ($_phpSheet->getFreezePane()) {
488	$this->_writePanes();
489	}
490
491	// Write SELECTION record
492	$this->_writeSelection();
493
494	// Write MergedCellsTable Record
495	$this->_writeMergedCells();
496
497	// Hyperlinks
498	foreach ($_phpSheet->getHyperLinkCollection() as $coordinate => $hyperlink) {
499	list($column, $row) = PHPExcel_Cell::coordinateFromString($coordinate);
500
501	$url = $hyperlink->getUrl();
502
503	if ( strpos($url, 'sheet://') !== false ) {
504	// internal to current workbook
505	$url = str_replace('sheet://', 'internal:', $url);
506
507	} else if ( preg_match('/^(http:\|https:\|ftp:\|mailto:)/', $url) ) {
508	// URL
509	// $url = $url;
510
511	} else {
512	// external (local file)
513	$url = 'external:' . $url;
514	}
515
516	$this->_writeUrl($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1, $url);
517	}
518
519	$this->_writeDataValidity();
520	$this->_writeSheetLayout();
521
522	// Write SHEETPROTECTION record
523	$this->_writeSheetProtection();
524	$this->_writeRangeProtection();
525
526	$arrConditionalStyles = $_phpSheet->getConditionalStylesCollection();
527	if(!empty($arrConditionalStyles)){
528	$arrConditional = array();
529	// @todo CFRule & CFHeader
530	// Write CFHEADER record
531	$this->_writeCFHeader();
532	// Write ConditionalFormattingTable records
533	foreach ($arrConditionalStyles as $cellCoordinate => $conditionalStyles) {
534	foreach ($conditionalStyles as $conditional) {
535	if($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_EXPRESSION
536	\|\| $conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CELLIS){
537	if(!in_array($conditional->getHashCode(), $arrConditional)){
538	$arrConditional[] = $conditional->getHashCode();
539	// Write CFRULE record
540	$this->_writeCFRule($conditional);
541	}
542	}
543	}
544	}
545	}
546
547	$this->_storeEof();
548	}
549
550	/**
551	* Write a cell range address in BIFF8
552	* always fixed range
553	* See section 2.5.14 in OpenOffice.org's Documentation of the Microsoft Excel File Format
554	*
555	* @param string $range E.g. 'A1' or 'A1:B6'
556	* @return string Binary data
557	*/
558	private function _writeBIFF8CellRangeAddressFixed($range = 'A1')
559	{
560	$explodes = explode(':', $range);
561
562	// extract first cell, e.g. 'A1'
563	$firstCell = $explodes[0];
564
565	// extract last cell, e.g. 'B6'
566	if (count($explodes) == 1) {
567	$lastCell = $firstCell;
568	} else {
569	$lastCell = $explodes[1];
570	}
571
572	$firstCellCoordinates = PHPExcel_Cell::coordinateFromString($firstCell); // e.g. array(0, 1)
573	$lastCellCoordinates = PHPExcel_Cell::coordinateFromString($lastCell); // e.g. array(1, 6)
574
575	return(pack('vvvv',
576	$firstCellCoordinates[1] - 1,
577	$lastCellCoordinates[1] - 1,
578	PHPExcel_Cell::columnIndexFromString($firstCellCoordinates[0]) - 1,
579	PHPExcel_Cell::columnIndexFromString($lastCellCoordinates[0]) - 1
580	));
581	}
582
583	/**
584	* Retrieves data from memory in one chunk, or from disk in $buffer
585	* sized chunks.
586	*
587	* @return string The data
588	*/
589	function getData()
590	{
591	$buffer = 4096;
592
593	// Return data stored in memory
594	if (isset($this->_data)) {
595	$tmp = $this->_data;
596	unset($this->_data);
597	return $tmp;
598	}
599	// No data to return
600	return false;
601	}
602
603	/**
604	* Set the option to print the row and column headers on the printed page.
605	*
606	* @access public
607	* @param integer $print Whether to print the headers or not. Defaults to 1 (print).
608	*/
609	function printRowColHeaders($print = 1)
610	{
611	$this->_print_headers = $print;
612	}
613
614	/**
615	* This method sets the properties for outlining and grouping. The defaults
616	* correspond to Excel's defaults.
617	*
618	* @param bool $visible
619	* @param bool $symbols_below
620	* @param bool $symbols_right
621	* @param bool $auto_style
622	*/
623	function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false)
624	{
625	$this->_outline_on = $visible;
626	$this->_outline_below = $symbols_below;
627	$this->_outline_right = $symbols_right;
628	$this->_outline_style = $auto_style;
629
630	// Ensure this is a boolean vale for Window2
631	if ($this->_outline_on) {
632	$this->_outline_on = 1;
633	}
634	}
635
636	/**
637	* Write a double to the specified row and column (zero indexed).
638	* An integer can be written as a double. Excel will display an
639	* integer. $format is optional.
640	*
641	* Returns 0 : normal termination
642	* -2 : row or column out of range
643	*
644	* @param integer $row Zero indexed row
645	* @param integer $col Zero indexed column
646	* @param float $num The number to write
647	* @param mixed $xfIndex The optional XF format
648	* @return integer
649	*/
650	private function _writeNumber($row, $col, $num, $xfIndex)
651	{
652	$record = 0x0203; // Record identifier
653	$length = 0x000E; // Number of bytes to follow
654
655	$header = pack("vv", $record, $length);
656	$data = pack("vvv", $row, $col, $xfIndex);
657	$xl_double = pack("d", $num);
658	if (self::getByteOrder()) { // if it's Big Endian
659	$xl_double = strrev($xl_double);
660	}
661
662	$this->_append($header.$data.$xl_double);
663	return(0);
664	}
665
666	/**
667	* Write a LABELSST record or a LABEL record. Which one depends on BIFF version
668	*
669	* @param int $row Row index (0-based)
670	* @param int $col Column index (0-based)
671	* @param string $str The string
672	* @param int $xfIndex Index to XF record
673	*/
674	private function _writeString($row, $col, $str, $xfIndex)
675	{
676	$this->_writeLabelSst($row, $col, $str, $xfIndex);
677	}
678
679	/**
680	* Write a LABELSST record or a LABEL record. Which one depends on BIFF version
681	* It differs from _writeString by the writing of rich text strings.
682	* @param int $row Row index (0-based)
683	* @param int $col Column index (0-based)
684	* @param string $str The string
685	* @param mixed $xfIndex The XF format index for the cell
686	* @param array $arrcRun Index to Font record and characters beginning
687	*/
688	private function _writeRichTextString($row, $col, $str, $xfIndex, $arrcRun){
689	$record = 0x00FD; // Record identifier
690	$length = 0x000A; // Bytes to follow
691	$str = PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($str, $arrcRun);
692
693	/* check if string is already present */
694	if (!isset($this->_str_table[$str])) {
695	$this->_str_table[$str] = $this->_str_unique++;
696	}
697	$this->_str_total++;
698
699	$header = pack('vv', $record, $length);
700	$data = pack('vvvV', $row, $col, $xfIndex, $this->_str_table[$str]);
701	$this->_append($header.$data);
702	}
703
704	/**
705	* Write a string to the specified row and column (zero indexed).
706	* NOTE: there is an Excel 5 defined limit of 255 characters.
707	* $format is optional.
708	* Returns 0 : normal termination
709	* -2 : row or column out of range
710	* -3 : long string truncated to 255 chars
711	*
712	* @access public
713	* @param integer $row Zero indexed row
714	* @param integer $col Zero indexed column
715	* @param string $str The string to write
716	* @param mixed $xfIndex The XF format index for the cell
717	* @return integer
718	*/
719	private function _writeLabel($row, $col, $str, $xfIndex)
720	{
721	$strlen = strlen($str);
722	$record = 0x0204; // Record identifier
723	$length = 0x0008 + $strlen; // Bytes to follow
724
725	$str_error = 0;
726
727	if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars
728	$str = substr($str, 0, $this->_xls_strmax);
729	$length = 0x0008 + $this->_xls_strmax;
730	$strlen = $this->_xls_strmax;
731	$str_error = -3;
732	}
733
734	$header = pack("vv", $record, $length);
735	$data = pack("vvvv", $row, $col, $xfIndex, $strlen);
736	$this->_append($header . $data . $str);
737	return($str_error);
738	}
739
740	/**
741	* Write a string to the specified row and column (zero indexed).
742	* This is the BIFF8 version (no 255 chars limit).
743	* $format is optional.
744	* Returns 0 : normal termination
745	* -2 : row or column out of range
746	* -3 : long string truncated to 255 chars
747	*
748	* @access public
749	* @param integer $row Zero indexed row
750	* @param integer $col Zero indexed column
751	* @param string $str The string to write
752	* @param mixed $xfIndex The XF format index for the cell
753	* @return integer
754	*/
755	private function _writeLabelSst($row, $col, $str, $xfIndex)
756	{
757	$record = 0x00FD; // Record identifier
758	$length = 0x000A; // Bytes to follow
759
760	$str = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($str);
761
762	/* check if string is already present */
763	if (!isset($this->_str_table[$str])) {
764	$this->_str_table[$str] = $this->_str_unique++;
765	}
766	$this->_str_total++;
767
768	$header = pack('vv', $record, $length);
769	$data = pack('vvvV', $row, $col, $xfIndex, $this->_str_table[$str]);
770	$this->_append($header.$data);
771	}
772
773	/**
774	* Writes a note associated with the cell given by the row and column.
775	* NOTE records don't have a length limit.
776	*
777	* @param integer $row Zero indexed row
778	* @param integer $col Zero indexed column
779	* @param string $note The note to write
780	*/
781	private function _writeNote($row, $col, $note)
782	{
783	$note_length = strlen($note);
784	$record = 0x001C; // Record identifier
785	$max_length = 2048; // Maximun length for a NOTE record
786
787	// Length for this record is no more than 2048 + 6
788	$length = 0x0006 + min($note_length, 2048);
789	$header = pack("vv", $record, $length);
790	$data = pack("vvv", $row, $col, $note_length);
791	$this->_append($header . $data . substr($note, 0, 2048));
792
793	for ($i = $max_length; $i < $note_length; $i += $max_length) {
794	$chunk = substr($note, $i, $max_length);
795	$length = 0x0006 + strlen($chunk);
796	$header = pack("vv", $record, $length);
797	$data = pack("vvv", -1, 0, strlen($chunk));
798	$this->_append($header.$data.$chunk);
799	}
800	return(0);
801	}
802
803	/**
804	* Write a blank cell to the specified row and column (zero indexed).
805	* A blank cell is used to specify formatting without adding a string
806	* or a number.
807	*
808	* A blank cell without a format serves no purpose. Therefore, we don't write
809	* a BLANK record unless a format is specified.
810	*
811	* Returns 0 : normal termination (including no format)
812	* -1 : insufficient number of arguments
813	* -2 : row or column out of range
814	*
815	* @param integer $row Zero indexed row
816	* @param integer $col Zero indexed column
817	* @param mixed $xfIndex The XF format index
818	*/
819	function _writeBlank($row, $col, $xfIndex)
820	{
821	$record = 0x0201; // Record identifier
822	$length = 0x0006; // Number of bytes to follow
823
824	$header = pack("vv", $record, $length);
825	$data = pack("vvv", $row, $col, $xfIndex);
826	$this->_append($header . $data);
827	return 0;
828	}
829
830	/**
831	* Write a boolean or an error type to the specified row and column (zero indexed)
832	*
833	* @param int $row Row index (0-based)
834	* @param int $col Column index (0-based)
835	* @param int $value
836	* @param boolean $isError Error or Boolean?
837	* @param int $xfIndex
838	*/
839	private function _writeBoolErr($row, $col, $value, $isError, $xfIndex)
840	{
841	$record = 0x0205;
842	$length = 8;
843
844	$header = pack("vv", $record, $length);
845	$data = pack("vvvCC", $row, $col, $xfIndex, $value, $isError);
846	$this->_append($header . $data);
847	return 0;
848	}
849
850	/**
851	* Write a formula to the specified row and column (zero indexed).
852	* The textual representation of the formula is passed to the parser in
853	* Parser.php which returns a packed binary string.
854	*
855	* Returns 0 : normal termination
856	* -1 : formula errors (bad formula)
857	* -2 : row or column out of range
858	*
859	* @param integer $row Zero indexed row
860	* @param integer $col Zero indexed column
861	* @param string $formula The formula text string
862	* @param mixed $xfIndex The XF format index
863	* @param mixed $calculatedValue Calculated value
864	* @return integer
865	*/
866	private function _writeFormula($row, $col, $formula, $xfIndex, $calculatedValue)
867	{
868	$record = 0x0006; // Record identifier
869
870	// Initialize possible additional value for STRING record that should be written after the FORMULA record?
871	$stringValue = null;
872
873	// calculated value
874	if (isset($calculatedValue)) {
875	// Since we can't yet get the data type of the calculated value,
876	// we use best effort to determine data type
877	if (is_bool($calculatedValue)) {
878	// Boolean value
879	$num = pack('CCCvCv', 0x01, 0x00, (int)$calculatedValue, 0x00, 0x00, 0xFFFF);
880	} elseif (is_int($calculatedValue) \|\| is_float($calculatedValue)) {
881	// Numeric value
882	$num = pack('d', $calculatedValue);
883	} elseif (is_string($calculatedValue)) {
884	if (array_key_exists($calculatedValue, PHPExcel_Cell_DataType::getErrorCodes())) {
885	// Error value
886	$num = pack('CCCvCv', 0x02, 0x00, self::_mapErrorCode($calculatedValue), 0x00, 0x00, 0xFFFF);
887	} elseif ($calculatedValue === '') {
888	// Empty string (and BIFF8)
889	$num = pack('CCCvCv', 0x03, 0x00, 0x00, 0x00, 0x00, 0xFFFF);
890	} else {
891	// Non-empty string value (or empty string BIFF5)
892	$stringValue = $calculatedValue;
893	$num = pack('CCCvCv', 0x00, 0x00, 0x00, 0x00, 0x00, 0xFFFF);
894	}
895	} else {
896	// We are really not supposed to reach here
897	$num = pack('d', 0x00);
898	}
899	} else {
900	$num = pack('d', 0x00);
901	}
902
903	$grbit = 0x03; // Option flags
904	$unknown = 0x0000; // Must be zero
905
906	// Strip the '=' or '@' sign at the beginning of the formula string
907	if ($formula{0} == '=') {
908	$formula = substr($formula,1);
909	} else {
910	// Error handling
911	$this->_writeString($row, $col, 'Unrecognised character for formula');
912	return -1;
913	}
914
915	// Parse the formula using the parser in Parser.php
916	try {
917	$error = $this->_parser->parse($formula);
918	$formula = $this->_parser->toReversePolish();
919
920	$formlen = strlen($formula); // Length of the binary string
921	$length = 0x16 + $formlen; // Length of the record data
922
923	$header = pack("vv", $record, $length);
924
925	$data = pack("vvv", $row, $col, $xfIndex)
926	. $num
927	. pack("vVv", $grbit, $unknown, $formlen);
928	$this->_append($header . $data . $formula);
929
930	// Append also a STRING record if necessary
931	if ($stringValue !== null) {
932	$this->_writeStringRecord($stringValue);
933	}
934
935	return 0;
936
937	} catch (PHPExcel_Exception $e) {
938	// do nothing
939	}
940
941	}
942
943	/**
944	* Write a STRING record. This
945	*
946	* @param string $stringValue
947	*/
948	private function _writeStringRecord($stringValue)
949	{
950	$record = 0x0207; // Record identifier
951	$data = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($stringValue);
952
953	$length = strlen($data);
954	$header = pack('vv', $record, $length);
955
956	$this->_append($header . $data);
957	}
958
959	/**
960	* Write a hyperlink.
961	* This is comprised of two elements: the visible label and
962	* the invisible link. The visible label is the same as the link unless an
963	* alternative string is specified. The label is written using the
964	* _writeString() method. Therefore the 255 characters string limit applies.
965	* $string and $format are optional.
966	*
967	* The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external
968	* directory url.
969	*
970	* Returns 0 : normal termination
971	* -2 : row or column out of range
972	* -3 : long string truncated to 255 chars
973	*
974	* @param integer $row Row
975	* @param integer $col Column
976	* @param string $url URL string
977	* @return integer
978	*/
979	private function _writeUrl($row, $col, $url)
980	{
981	// Add start row and col to arg list
982	return($this->_writeUrlRange($row, $col, $row, $col, $url));
983	}
984
985	/**
986	* This is the more general form of _writeUrl(). It allows a hyperlink to be
987	* written to a range of cells. This function also decides the type of hyperlink
988	* to be written. These are either, Web (http, ftp, mailto), Internal
989	* (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
990	*
991	* @access private
992	* @see _writeUrl()
993	* @param integer $row1 Start row
994	* @param integer $col1 Start column
995	* @param integer $row2 End row
996	* @param integer $col2 End column
997	* @param string $url URL string
998	* @return integer
999	*/
1000	function _writeUrlRange($row1, $col1, $row2, $col2, $url)
1001	{
1002	// Check for internal/external sheet links or default to web link
1003	if (preg_match('[^internal:]', $url)) {
1004	return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url));
1005	}
1006	if (preg_match('[^external:]', $url)) {
1007	return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url));
1008	}
1009	return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url));
1010	}
1011
1012	/**
1013	* Used to write http, ftp and mailto hyperlinks.
1014	* The link type ($options) is 0x03 is the same as absolute dir ref without
1015	* sheet. However it is differentiated by the $unknown2 data stream.
1016	*
1017	* @access private
1018	* @see _writeUrl()
1019	* @param integer $row1 Start row
1020	* @param integer $col1 Start column
1021	* @param integer $row2 End row
1022	* @param integer $col2 End column
1023	* @param string $url URL string
1024	* @return integer
1025	*/
1026	function _writeUrlWeb($row1, $col1, $row2, $col2, $url)
1027	{
1028	$record = 0x01B8; // Record identifier
1029	$length = 0x00000; // Bytes to follow
1030
1031	// Pack the undocumented parts of the hyperlink stream
1032	$unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
1033	$unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");
1034
1035	// Pack the option flags
1036	$options = pack("V", 0x03);
1037
1038	// Convert URL to a null terminated wchar string
1039	$url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
1040	$url = $url . "\0\0\0";
1041
1042	// Pack the length of the URL
1043	$url_len = pack("V", strlen($url));
1044
1045	// Calculate the data length
1046	$length = 0x34 + strlen($url);
1047
1048	// Pack the header data
1049	$header = pack("vv", $record, $length);
1050	$data = pack("vvvv", $row1, $row2, $col1, $col2);
1051
1052	// Write the packed data
1053	$this->_append($header . $data .
1054	$unknown1 . $options .
1055	$unknown2 . $url_len . $url);
1056	return 0;
1057	}
1058
1059	/**
1060	* Used to write internal reference hyperlinks such as "Sheet1!A1".
1061	*
1062	* @access private
1063	* @see _writeUrl()
1064	* @param integer $row1 Start row
1065	* @param integer $col1 Start column
1066	* @param integer $row2 End row
1067	* @param integer $col2 End column
1068	* @param string $url URL string
1069	* @return integer
1070	*/
1071	function _writeUrlInternal($row1, $col1, $row2, $col2, $url)
1072	{
1073	$record = 0x01B8; // Record identifier
1074	$length = 0x00000; // Bytes to follow
1075
1076	// Strip URL type
1077	$url = preg_replace('/^internal:/', '', $url);
1078
1079	// Pack the undocumented parts of the hyperlink stream
1080	$unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
1081
1082	// Pack the option flags
1083	$options = pack("V", 0x08);
1084
1085	// Convert the URL type and to a null terminated wchar string
1086	$url .= "\0";
1087
1088	// character count
1089	$url_len = PHPExcel_Shared_String::CountCharacters($url);
1090	$url_len = pack('V', $url_len);
1091
1092	$url = PHPExcel_Shared_String::ConvertEncoding($url, 'UTF-16LE', 'UTF-8');
1093
1094	// Calculate the data length
1095	$length = 0x24 + strlen($url);
1096
1097	// Pack the header data
1098	$header = pack("vv", $record, $length);
1099	$data = pack("vvvv", $row1, $row2, $col1, $col2);
1100
1101	// Write the packed data
1102	$this->_append($header . $data .
1103	$unknown1 . $options .
1104	$url_len . $url);
1105	return 0;
1106	}
1107
1108	/**
1109	* Write links to external directory names such as 'c:\foo.xls',
1110	* c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
1111	*
1112	* Note: Excel writes some relative links with the $dir_long string. We ignore
1113	* these cases for the sake of simpler code.
1114	*
1115	* @access private
1116	* @see _writeUrl()
1117	* @param integer $row1 Start row
1118	* @param integer $col1 Start column
1119	* @param integer $row2 End row
1120	* @param integer $col2 End column
1121	* @param string $url URL string
1122	* @return integer
1123	*/
1124	function _writeUrlExternal($row1, $col1, $row2, $col2, $url)
1125	{
1126	// Network drives are different. We will handle them separately
1127	// MS/Novell network drives and shares start with \\
1128	if (preg_match('[^external:\\\\]', $url)) {
1129	return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format));
1130	}
1131
1132	$record = 0x01B8; // Record identifier
1133	$length = 0x00000; // Bytes to follow
1134
1135	// Strip URL type and change Unix dir separator to Dos style (if needed)
1136	//
1137	$url = preg_replace('/^external:/', '', $url);
1138	$url = preg_replace('/\//', "\\", $url);
1139
1140	// Determine if the link is relative or absolute:
1141	// relative if link contains no dir separator, "somefile.xls"
1142	// relative if link starts with up-dir, "..\..\somefile.xls"
1143	// otherwise, absolute
1144
1145	$absolute = 0x00; // relative path
1146	if ( preg_match('/^[A-Z]:/', $url) ) {
1147	$absolute = 0x02; // absolute path on Windows, e.g. C:\...
1148	}
1149	$link_type = 0x01 \| $absolute;
1150
1151	// Determine if the link contains a sheet reference and change some of the
1152	// parameters accordingly.
1153	// Split the dir name and sheet name (if it exists)
1154	$dir_long = $url;
1155	if (preg_match("/\#/", $url)) {
1156	$link_type \|= 0x08;
1157	}
1158
1159
1160	// Pack the link type
1161	$link_type = pack("V", $link_type);
1162
1163	// Calculate the up-level dir count e.g.. (..\..\..\ == 3)
1164	$up_count = preg_match_all("/\.\.\\\/", $dir_long, $useless);
1165	$up_count = pack("v", $up_count);
1166
1167	// Store the short dos dir name (null terminated)
1168	$dir_short = preg_replace("/\.\.\\\/", '', $dir_long) . "\0";
1169
1170	// Store the long dir name as a wchar string (non-null terminated)
1171	$dir_long = $dir_long . "\0";
1172
1173	// Pack the lengths of the dir strings
1174	$dir_short_len = pack("V", strlen($dir_short) );
1175	$dir_long_len = pack("V", strlen($dir_long) );
1176	$stream_len = pack("V", 0);//strlen($dir_long) + 0x06);
1177
1178	// Pack the undocumented parts of the hyperlink stream
1179	$unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000' );
1180	$unknown2 = pack("H*",'0303000000000000C000000000000046' );
1181	$unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000');
1182	$unknown4 = pack("v", 0x03 );
1183
1184	// Pack the main data stream
1185	$data = pack("vvvv", $row1, $row2, $col1, $col2) .
1186	$unknown1 .
1187	$link_type .
1188	$unknown2 .
1189	$up_count .
1190	$dir_short_len.
1191	$dir_short .
1192	$unknown3 .
1193	$stream_len ;/*.
1194	$dir_long_len .
1195	$unknown4 .
1196	$dir_long .
1197	$sheet_len .
1198	$sheet ;*/
1199
1200	// Pack the header data
1201	$length = strlen($data);
1202	$header = pack("vv", $record, $length);
1203
1204	// Write the packed data
1205	$this->_append($header. $data);
1206	return 0;
1207	}
1208
1209	/**
1210	* This method is used to set the height and format for a row.
1211	*
1212	* @param integer $row The row to set
1213	* @param integer $height Height we are giving to the row.
1214	* Use null to set XF without setting height
1215	* @param integer $xfIndex The optional cell style Xf index to apply to the columns
1216	* @param bool $hidden The optional hidden attribute
1217	* @param integer $level The optional outline level for row, in range [0,7]
1218	*/
1219	private function _writeRow($row, $height, $xfIndex, $hidden = false, $level = 0)
1220	{
1221	$record = 0x0208; // Record identifier
1222	$length = 0x0010; // Number of bytes to follow
1223
1224	$colMic = 0x0000; // First defined column
1225	$colMac = 0x0000; // Last defined column
1226	$irwMac = 0x0000; // Used by Excel to optimise loading
1227	$reserved = 0x0000; // Reserved
1228	$grbit = 0x0000; // Option flags
1229	$ixfe = $xfIndex;
1230
1231	if ( $height < 0 ){
1232	$height = null;
1233	}
1234
1235	// Use _writeRow($row, null, $XF) to set XF format without setting height
1236	if ($height != null) {
1237	$miyRw = $height * 20; // row height
1238	} else {
1239	$miyRw = 0xff; // default row height is 256
1240	}
1241
1242	// Set the options flags. fUnsynced is used to show that the font and row
1243	// heights are not compatible. This is usually the case for WriteExcel.
1244	// The collapsed flag 0x10 doesn't seem to be used to indicate that a row
1245	// is collapsed. Instead it is used to indicate that the previous row is
1246	// collapsed. The zero height flag, 0x20, is used to collapse a row.
1247
1248	$grbit \|= $level;
1249	if ($hidden) {
1250	$grbit \|= 0x0030;
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	}