Subversion Repositories eFlore/Applications.cel

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
2390 jpm 1
<?php
2
/**
3
 * PHPExcel
4
 *
5
 * Copyright (c) 2006 - 2013 PHPExcel
6
 *
7
 * This library is free software; you can redistribute it and/or
8
 * modify it under the terms of the GNU Lesser General Public
9
 * License as published by the Free Software Foundation; either
10
 * version 2.1 of the License, or (at your option) any later version.
11
 *
12
 * This library is distributed in the hope that it will be useful,
13
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
15
 * Lesser General Public License for more details.
16
 *
17
 * You should have received a copy of the GNU Lesser General Public
18
 * License along with this library; if not, write to the Free Software
19
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
20
 *
21
 * @category   PHPExcel
22
 * @package    PHPExcel_Writer_Excel5
23
 * @copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
24
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
25
 * @version    ##VERSION##, ##DATE##
26
 */
27
 
28
// Original file header of PEAR::Spreadsheet_Excel_Writer_Workbook (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_Workbook
66
 *
67
 * @category   PHPExcel
68
 * @package    PHPExcel_Writer_Excel5
69
 * @copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
70
 */
71
class PHPExcel_Writer_Excel5_Workbook extends PHPExcel_Writer_Excel5_BIFFwriter
72
{
73
	/**
74
	 * Formula parser
75
	 *
76
	 * @var PHPExcel_Writer_Excel5_Parser
77
	 */
78
	private $_parser;
79
 
80
	/**
81
	 * The BIFF file size for the workbook.
82
	 * @var integer
83
	 * @see _calcSheetOffsets()
84
	 */
85
	public $_biffsize;
86
 
87
	/**
88
	 * XF Writers
89
	 * @var PHPExcel_Writer_Excel5_Xf[]
90
	 */
91
	private $_xfWriters = array();
92
 
93
	/**
94
	 * Array containing the colour palette
95
	 * @var array
96
	 */
97
	public $_palette;
98
 
99
	/**
100
	 * The codepage indicates the text encoding used for strings
101
	 * @var integer
102
	 */
103
	public $_codepage;
104
 
105
	/**
106
	 * The country code used for localization
107
	 * @var integer
108
	 */
109
	public $_country_code;
110
 
111
	/**
112
	 * Workbook
113
	 * @var PHPExcel
114
	 */
115
	private $_phpExcel;
116
 
117
	/**
118
	 * Fonts writers
119
	 *
120
	 * @var PHPExcel_Writer_Excel5_Font[]
121
	 */
122
	private $_fontWriters = array();
123
 
124
	/**
125
	 * Added fonts. Maps from font's hash => index in workbook
126
	 *
127
	 * @var array
128
	 */
129
	private $_addedFonts = array();
130
 
131
	/**
132
	 * Shared number formats
133
	 *
134
	 * @var array
135
	 */
136
	private $_numberFormats = array();
137
 
138
	/**
139
	 * Added number formats. Maps from numberFormat's hash => index in workbook
140
	 *
141
	 * @var array
142
	 */
143
	private $_addedNumberFormats = array();
144
 
145
	/**
146
	 * Sizes of the binary worksheet streams
147
	 *
148
	 * @var array
149
	 */
150
	private $_worksheetSizes = array();
151
 
152
	/**
153
	 * Offsets of the binary worksheet streams relative to the start of the global workbook stream
154
	 *
155
	 * @var array
156
	 */
157
	private $_worksheetOffsets = array();
158
 
159
	/**
160
	 * Total number of shared strings in workbook
161
	 *
162
	 * @var int
163
	 */
164
	private $_str_total;
165
 
166
	/**
167
	 * Number of unique shared strings in workbook
168
	 *
169
	 * @var int
170
	 */
171
	private $_str_unique;
172
 
173
	/**
174
	 * Array of unique shared strings in workbook
175
	 *
176
	 * @var array
177
	 */
178
	private $_str_table;
179
 
180
	/**
181
	 * Color cache
182
	 */
183
	private $_colors;
184
 
185
	/**
186
	 * Escher object corresponding to MSODRAWINGGROUP
187
	 *
188
	 * @var PHPExcel_Shared_Escher
189
	 */
190
	private $_escher;
191
 
192
 
193
	/**
194
	 * Class constructor
195
	 *
196
	 * @param PHPExcel	$phpExcel		The Workbook
197
	 * @param int		&$str_total		Total number of strings
198
	 * @param int		&$str_unique	Total number of unique strings
199
	 * @param array		&$str_table		String Table
200
	 * @param array		&$colors		Colour Table
201
	 * @param mixed		$parser			The formula parser created for the Workbook
202
	 */
203
	public function __construct(PHPExcel $phpExcel = null,
204
								&$str_total, &$str_unique, &$str_table, &$colors,
205
								$parser )
206
	{
207
		// It needs to call its parent's constructor explicitly
208
		parent::__construct();
209
 
210
		$this->_parser           = $parser;
211
		$this->_biffsize         = 0;
212
		$this->_palette          = array();
213
		$this->_country_code     = -1;
214
 
215
		$this->_str_total       = &$str_total;
216
		$this->_str_unique      = &$str_unique;
217
		$this->_str_table       = &$str_table;
218
		$this->_colors          = &$colors;
219
		$this->_setPaletteXl97();
220
 
221
		$this->_phpExcel = $phpExcel;
222
 
223
		// set BIFFwriter limit for CONTINUE records
224
		//		$this->_limit = 8224;
225
		$this->_codepage = 0x04B0;
226
 
227
		// Add empty sheets and Build color cache
228
		$countSheets = $phpExcel->getSheetCount();
229
		for ($i = 0; $i < $countSheets; ++$i) {
230
			$phpSheet = $phpExcel->getSheet($i);
231
 
232
			$this->_parser->setExtSheet($phpSheet->getTitle(), $i);  // Register worksheet name with parser
233
 
234
			$supbook_index = 0x00;
235
			$ref = pack('vvv', $supbook_index, $i, $i);
236
			$this->_parser->_references[] = $ref;  // Register reference with parser
237
 
238
			// Sheet tab colors?
239
			if ($phpSheet->isTabColorSet()) {
240
				$this->_addColor($phpSheet->getTabColor()->getRGB());
241
			}
242
		}
243
 
244
	}
245
 
246
	/**
247
	 * Add a new XF writer
248
	 *
249
	 * @param PHPExcel_Style
250
	 * @param boolean Is it a style XF?
251
	 * @return int Index to XF record
252
	 */
253
	public function addXfWriter($style, $isStyleXf = false)
254
	{
255
		$xfWriter = new PHPExcel_Writer_Excel5_Xf($style);
256
		$xfWriter->setIsStyleXf($isStyleXf);
257
 
258
		// Add the font if not already added
259
		$fontIndex = $this->_addFont($style->getFont());
260
 
261
		// Assign the font index to the xf record
262
		$xfWriter->setFontIndex($fontIndex);
263
 
264
		// Background colors, best to treat these after the font so black will come after white in custom palette
265
		$xfWriter->setFgColor($this->_addColor($style->getFill()->getStartColor()->getRGB()));
266
		$xfWriter->setBgColor($this->_addColor($style->getFill()->getEndColor()->getRGB()));
267
		$xfWriter->setBottomColor($this->_addColor($style->getBorders()->getBottom()->getColor()->getRGB()));
268
		$xfWriter->setTopColor($this->_addColor($style->getBorders()->getTop()->getColor()->getRGB()));
269
		$xfWriter->setRightColor($this->_addColor($style->getBorders()->getRight()->getColor()->getRGB()));
270
		$xfWriter->setLeftColor($this->_addColor($style->getBorders()->getLeft()->getColor()->getRGB()));
271
		$xfWriter->setDiagColor($this->_addColor($style->getBorders()->getDiagonal()->getColor()->getRGB()));
272
 
273
		// Add the number format if it is not a built-in one and not already added
274
		if ($style->getNumberFormat()->getBuiltInFormatCode() === false) {
275
			$numberFormatHashCode = $style->getNumberFormat()->getHashCode();
276
 
277
			if (isset($this->_addedNumberFormats[$numberFormatHashCode])) {
278
				$numberFormatIndex = $this->_addedNumberFormats[$numberFormatHashCode];
279
			} else {
280
				$numberFormatIndex = 164 + count($this->_numberFormats);
281
				$this->_numberFormats[$numberFormatIndex] = $style->getNumberFormat();
282
				$this->_addedNumberFormats[$numberFormatHashCode] = $numberFormatIndex;
283
			}
284
		} else {
285
			$numberFormatIndex = (int) $style->getNumberFormat()->getBuiltInFormatCode();
286
		}
287
 
288
		// Assign the number format index to xf record
289
		$xfWriter->setNumberFormatIndex($numberFormatIndex);
290
 
291
		$this->_xfWriters[] = $xfWriter;
292
 
293
		$xfIndex = count($this->_xfWriters) - 1;
294
		return $xfIndex;
295
	}
296
 
297
	/**
298
	 * Add a font to added fonts
299
	 *
300
	 * @param PHPExcel_Style_Font $font
301
	 * @return int Index to FONT record
302
	 */
303
	public function _addFont(PHPExcel_Style_Font $font)
304
	{
305
		$fontHashCode = $font->getHashCode();
306
		if(isset($this->_addedFonts[$fontHashCode])){
307
			$fontIndex = $this->_addedFonts[$fontHashCode];
308
		} else {
309
			$countFonts = count($this->_fontWriters);
310
			$fontIndex = ($countFonts < 4) ? $countFonts : $countFonts + 1;
311
 
312
			$fontWriter = new PHPExcel_Writer_Excel5_Font($font);
313
			$fontWriter->setColorIndex($this->_addColor($font->getColor()->getRGB()));
314
			$this->_fontWriters[] = $fontWriter;
315
 
316
			$this->_addedFonts[$fontHashCode] = $fontIndex;
317
		}
318
		return $fontIndex;
319
	}
320
 
321
	/**
322
	 * Alter color palette adding a custom color
323
	 *
324
	 * @param string $rgb E.g. 'FF00AA'
325
	 * @return int Color index
326
	 */
327
	private function _addColor($rgb) {
328
		if (!isset($this->_colors[$rgb])) {
329
			if (count($this->_colors) < 57) {
330
				// then we add a custom color altering the palette
331
				$colorIndex = 8 + count($this->_colors);
332
				$this->_palette[$colorIndex] =
333
					array(
334
						hexdec(substr($rgb, 0, 2)),
335
						hexdec(substr($rgb, 2, 2)),
336
						hexdec(substr($rgb, 4)),
337
 
338
					);
339
				$this->_colors[$rgb] = $colorIndex;
340
			} else {
341
				// no room for more custom colors, just map to black
342
				$colorIndex = 0;
343
			}
344
		} else {
345
			// fetch already added custom color
346
			$colorIndex = $this->_colors[$rgb];
347
		}
348
 
349
		return $colorIndex;
350
	}
351
 
352
	/**
353
	 * Sets the colour palette to the Excel 97+ default.
354
	 *
355
	 * @access private
356
	 */
357
	function _setPaletteXl97()
358
	{
359
		$this->_palette = array(
360
			0x08 => array(0x00, 0x00, 0x00, 0x00),
361
			0x09 => array(0xff, 0xff, 0xff, 0x00),
362
			0x0A => array(0xff, 0x00, 0x00, 0x00),
363
			0x0B => array(0x00, 0xff, 0x00, 0x00),
364
			0x0C => array(0x00, 0x00, 0xff, 0x00),
365
			0x0D => array(0xff, 0xff, 0x00, 0x00),
366
			0x0E => array(0xff, 0x00, 0xff, 0x00),
367
			0x0F => array(0x00, 0xff, 0xff, 0x00),
368
			0x10 => array(0x80, 0x00, 0x00, 0x00),
369
			0x11 => array(0x00, 0x80, 0x00, 0x00),
370
			0x12 => array(0x00, 0x00, 0x80, 0x00),
371
			0x13 => array(0x80, 0x80, 0x00, 0x00),
372
			0x14 => array(0x80, 0x00, 0x80, 0x00),
373
			0x15 => array(0x00, 0x80, 0x80, 0x00),
374
			0x16 => array(0xc0, 0xc0, 0xc0, 0x00),
375
			0x17 => array(0x80, 0x80, 0x80, 0x00),
376
			0x18 => array(0x99, 0x99, 0xff, 0x00),
377
			0x19 => array(0x99, 0x33, 0x66, 0x00),
378
			0x1A => array(0xff, 0xff, 0xcc, 0x00),
379
			0x1B => array(0xcc, 0xff, 0xff, 0x00),
380
			0x1C => array(0x66, 0x00, 0x66, 0x00),
381
			0x1D => array(0xff, 0x80, 0x80, 0x00),
382
			0x1E => array(0x00, 0x66, 0xcc, 0x00),
383
			0x1F => array(0xcc, 0xcc, 0xff, 0x00),
384
			0x20 => array(0x00, 0x00, 0x80, 0x00),
385
			0x21 => array(0xff, 0x00, 0xff, 0x00),
386
			0x22 => array(0xff, 0xff, 0x00, 0x00),
387
			0x23 => array(0x00, 0xff, 0xff, 0x00),
388
			0x24 => array(0x80, 0x00, 0x80, 0x00),
389
			0x25 => array(0x80, 0x00, 0x00, 0x00),
390
			0x26 => array(0x00, 0x80, 0x80, 0x00),
391
			0x27 => array(0x00, 0x00, 0xff, 0x00),
392
			0x28 => array(0x00, 0xcc, 0xff, 0x00),
393
			0x29 => array(0xcc, 0xff, 0xff, 0x00),
394
			0x2A => array(0xcc, 0xff, 0xcc, 0x00),
395
			0x2B => array(0xff, 0xff, 0x99, 0x00),
396
			0x2C => array(0x99, 0xcc, 0xff, 0x00),
397
			0x2D => array(0xff, 0x99, 0xcc, 0x00),
398
			0x2E => array(0xcc, 0x99, 0xff, 0x00),
399
			0x2F => array(0xff, 0xcc, 0x99, 0x00),
400
			0x30 => array(0x33, 0x66, 0xff, 0x00),
401
			0x31 => array(0x33, 0xcc, 0xcc, 0x00),
402
			0x32 => array(0x99, 0xcc, 0x00, 0x00),
403
			0x33 => array(0xff, 0xcc, 0x00, 0x00),
404
			0x34 => array(0xff, 0x99, 0x00, 0x00),
405
			0x35 => array(0xff, 0x66, 0x00, 0x00),
406
			0x36 => array(0x66, 0x66, 0x99, 0x00),
407
			0x37 => array(0x96, 0x96, 0x96, 0x00),
408
			0x38 => array(0x00, 0x33, 0x66, 0x00),
409
			0x39 => array(0x33, 0x99, 0x66, 0x00),
410
			0x3A => array(0x00, 0x33, 0x00, 0x00),
411
			0x3B => array(0x33, 0x33, 0x00, 0x00),
412
			0x3C => array(0x99, 0x33, 0x00, 0x00),
413
			0x3D => array(0x99, 0x33, 0x66, 0x00),
414
			0x3E => array(0x33, 0x33, 0x99, 0x00),
415
			0x3F => array(0x33, 0x33, 0x33, 0x00),
416
		);
417
	}
418
 
419
	/**
420
	 * Assemble worksheets into a workbook and send the BIFF data to an OLE
421
	 * storage.
422
	 *
423
	 * @param	array	$pWorksheetSizes	The sizes in bytes of the binary worksheet streams
424
	 * @return	string	Binary data for workbook stream
425
	 */
426
	public function writeWorkbook($pWorksheetSizes = null)
427
	{
428
		$this->_worksheetSizes = $pWorksheetSizes;
429
 
430
		// Calculate the number of selected worksheet tabs and call the finalization
431
		// methods for each worksheet
432
		$total_worksheets = $this->_phpExcel->getSheetCount();
433
 
434
		// Add part 1 of the Workbook globals, what goes before the SHEET records
435
		$this->_storeBof(0x0005);
436
		$this->_writeCodepage();
437
		$this->_writeWindow1();
438
 
439
		$this->_writeDatemode();
440
		$this->_writeAllFonts();
441
		$this->_writeAllNumFormats();
442
		$this->_writeAllXfs();
443
		$this->_writeAllStyles();
444
		$this->_writePalette();
445
 
446
		// Prepare part 3 of the workbook global stream, what goes after the SHEET records
447
		$part3 = '';
448
		if ($this->_country_code != -1) {
449
			$part3 .= $this->_writeCountry();
450
		}
451
		$part3 .= $this->_writeRecalcId();
452
 
453
		$part3 .= $this->_writeSupbookInternal();
454
		/* TODO: store external SUPBOOK records and XCT and CRN records
455
		in case of external references for BIFF8 */
456
		$part3 .= $this->_writeExternsheetBiff8();
457
		$part3 .= $this->_writeAllDefinedNamesBiff8();
458
		$part3 .= $this->_writeMsoDrawingGroup();
459
		$part3 .= $this->_writeSharedStringsTable();
460
 
461
		$part3 .= $this->writeEof();
462
 
463
		// Add part 2 of the Workbook globals, the SHEET records
464
		$this->_calcSheetOffsets();
465
		for ($i = 0; $i < $total_worksheets; ++$i) {
466
			$this->_writeBoundsheet($this->_phpExcel->getSheet($i), $this->_worksheetOffsets[$i]);
467
		}
468
 
469
		// Add part 3 of the Workbook globals
470
		$this->_data .= $part3;
471
 
472
		return $this->_data;
473
	}
474
 
475
	/**
476
	 * Calculate offsets for Worksheet BOF records.
477
	 *
478
	 * @access private
479
	 */
480
	function _calcSheetOffsets()
481
	{
482
		$boundsheet_length = 10;  // fixed length for a BOUNDSHEET record
483
 
484
		// size of Workbook globals part 1 + 3
485
		$offset            = $this->_datasize;
486
 
487
		// add size of Workbook globals part 2, the length of the SHEET records
488
		$total_worksheets = count($this->_phpExcel->getAllSheets());
489
		foreach ($this->_phpExcel->getWorksheetIterator() as $sheet) {
490
			$offset += $boundsheet_length + strlen(PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($sheet->getTitle()));
491
		}
492
 
493
		// add the sizes of each of the Sheet substreams, respectively
494
		for ($i = 0; $i < $total_worksheets; ++$i) {
495
			$this->_worksheetOffsets[$i] = $offset;
496
			$offset += $this->_worksheetSizes[$i];
497
		}
498
		$this->_biffsize = $offset;
499
	}
500
 
501
	/**
502
	 * Store the Excel FONT records.
503
	 */
504
	private function _writeAllFonts()
505
	{
506
		foreach ($this->_fontWriters as $fontWriter) {
507
			$this->_append($fontWriter->writeFont());
508
		}
509
	}
510
 
511
	/**
512
	 * Store user defined numerical formats i.e. FORMAT records
513
	 */
514
	private function _writeAllNumFormats()
515
	{
516
		foreach ($this->_numberFormats as $numberFormatIndex => $numberFormat) {
517
			$this->_writeNumFormat($numberFormat->getFormatCode(), $numberFormatIndex);
518
		}
519
	}
520
 
521
	/**
522
	 * Write all XF records.
523
	 */
524
	private function _writeAllXfs()
525
	{
526
		foreach ($this->_xfWriters as $xfWriter) {
527
			$this->_append($xfWriter->writeXf());
528
		}
529
	}
530
 
531
	/**
532
	 * Write all STYLE records.
533
	 */
534
	private function _writeAllStyles()
535
	{
536
		$this->_writeStyle();
537
	}
538
 
539
	/**
540
	 * Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for
541
	 * the NAME records.
542
	 */
543
	private function _writeExterns()
544
	{
545
		$countSheets = $this->_phpExcel->getSheetCount();
546
		// Create EXTERNCOUNT with number of worksheets
547
		$this->_writeExterncount($countSheets);
548
 
549
		// Create EXTERNSHEET for each worksheet
550
		for ($i = 0; $i < $countSheets; ++$i) {
551
			$this->_writeExternsheet($this->_phpExcel->getSheet($i)->getTitle());
552
		}
553
	}
554
 
555
	/**
556
	 * Write the NAME record to define the print area and the repeat rows and cols.
557
	 */
558
	private function _writeNames()
559
	{
560
		// total number of sheets
561
		$total_worksheets = $this->_phpExcel->getSheetCount();
562
 
563
		// Create the print area NAME records
564
		for ($i = 0; $i < $total_worksheets; ++$i) {
565
			$sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
566
			// Write a Name record if the print area has been defined
567
			if ($sheetSetup->isPrintAreaSet()) {
568
				// Print area
569
				$printArea = PHPExcel_Cell::splitRange($sheetSetup->getPrintArea());
570
				$printArea = $printArea[0];
571
				$printArea[0] = PHPExcel_Cell::coordinateFromString($printArea[0]);
572
				$printArea[1] = PHPExcel_Cell::coordinateFromString($printArea[1]);
573
 
574
				$print_rowmin = $printArea[0][1] - 1;
575
				$print_rowmax = $printArea[1][1] - 1;
576
				$print_colmin = PHPExcel_Cell::columnIndexFromString($printArea[0][0]) - 1;
577
				$print_colmax = PHPExcel_Cell::columnIndexFromString($printArea[1][0]) - 1;
578
 
579
				$this->_writeNameShort(
580
					$i, // sheet index
581
					0x06, // NAME type
582
					$print_rowmin,
583
					$print_rowmax,
584
					$print_colmin,
585
					$print_colmax
586
					);
587
			}
588
		}
589
 
590
		// Create the print title NAME records
591
		for ($i = 0; $i < $total_worksheets; ++$i) {
592
			$sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
593
 
594
			// simultaneous repeatColumns repeatRows
595
			if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) {
596
				$repeat = $sheetSetup->getColumnsToRepeatAtLeft();
597
				$colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
598
				$colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
599
 
600
				$repeat = $sheetSetup->getRowsToRepeatAtTop();
601
				$rowmin = $repeat[0] - 1;
602
				$rowmax = $repeat[1] - 1;
603
 
604
				$this->_writeNameLong(
605
					$i, // sheet index
606
					0x07, // NAME type
607
					$rowmin,
608
					$rowmax,
609
					$colmin,
610
					$colmax
611
					);
612
 
613
			// (exclusive) either repeatColumns or repeatRows
614
			} else if ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) {
615
 
616
				// Columns to repeat
617
				if ($sheetSetup->isColumnsToRepeatAtLeftSet()) {
618
					$repeat = $sheetSetup->getColumnsToRepeatAtLeft();
619
					$colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
620
					$colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
621
				} else {
622
					$colmin = 0;
623
					$colmax = 255;
624
				}
625
 
626
				// Rows to repeat
627
				if ($sheetSetup->isRowsToRepeatAtTopSet()) {
628
					$repeat = $sheetSetup->getRowsToRepeatAtTop();
629
					$rowmin = $repeat[0] - 1;
630
					$rowmax = $repeat[1] - 1;
631
				} else {
632
					$rowmin = 0;
633
					$rowmax = 65535;
634
				}
635
 
636
				$this->_writeNameShort(
637
					$i, // sheet index
638
					0x07, // NAME type
639
					$rowmin,
640
					$rowmax,
641
					$colmin,
642
					$colmax
643
					);
644
			}
645
		}
646
	}
647
 
648
	/**
649
	 * Writes all the DEFINEDNAME records (BIFF8).
650
	 * So far this is only used for repeating rows/columns (print titles) and print areas
651
	 */
652
	private function _writeAllDefinedNamesBiff8()
653
	{
654
		$chunk = '';
655
 
656
		// Named ranges
657
		if (count($this->_phpExcel->getNamedRanges()) > 0) {
658
			// Loop named ranges
659
			$namedRanges = $this->_phpExcel->getNamedRanges();
660
			foreach ($namedRanges as $namedRange) {
661
 
662
				// Create absolute coordinate
663
				$range = PHPExcel_Cell::splitRange($namedRange->getRange());
664
				for ($i = 0; $i < count($range); $i++) {
665
					$range[$i][0] = '\'' . str_replace("'", "''", $namedRange->getWorksheet()->getTitle()) . '\'!' . PHPExcel_Cell::absoluteCoordinate($range[$i][0]);
666
					if (isset($range[$i][1])) {
667
						$range[$i][1] = PHPExcel_Cell::absoluteCoordinate($range[$i][1]);
668
					}
669
				}
670
				$range = PHPExcel_Cell::buildRange($range); // e.g. Sheet1!$A$1:$B$2
671
 
672
				// parse formula
673
				try {
674
					$error = $this->_parser->parse($range);
675
					$formulaData = $this->_parser->toReversePolish();
676
 
677
					// make sure tRef3d is of type tRef3dR (0x3A)
678
					if (isset($formulaData{0}) and ($formulaData{0} == "\x7A" or $formulaData{0} == "\x5A")) {
679
						$formulaData = "\x3A" . substr($formulaData, 1);
680
					}
681
 
682
					if ($namedRange->getLocalOnly()) {
683
						// local scope
684
						$scope = $this->_phpExcel->getIndex($namedRange->getScope()) + 1;
685
					} else {
686
						// global scope
687
						$scope = 0;
688
					}
689
					$chunk .= $this->writeData($this->_writeDefinedNameBiff8($namedRange->getName(), $formulaData, $scope, false));
690
 
691
				} catch(PHPExcel_Exception $e) {
692
					// do nothing
693
				}
694
			}
695
		}
696
 
697
		// total number of sheets
698
		$total_worksheets = $this->_phpExcel->getSheetCount();
699
 
700
		// write the print titles (repeating rows, columns), if any
701
		for ($i = 0; $i < $total_worksheets; ++$i) {
702
			$sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
703
			// simultaneous repeatColumns repeatRows
704
			if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) {
705
				$repeat = $sheetSetup->getColumnsToRepeatAtLeft();
706
				$colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
707
				$colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
708
 
709
				$repeat = $sheetSetup->getRowsToRepeatAtTop();
710
				$rowmin = $repeat[0] - 1;
711
				$rowmax = $repeat[1] - 1;
712
 
713
				// construct formula data manually
714
				$formulaData = pack('Cv', 0x29, 0x17); // tMemFunc
715
				$formulaData .= pack('Cvvvvv', 0x3B, $i, 0, 65535, $colmin, $colmax); // tArea3d
716
				$formulaData .= pack('Cvvvvv', 0x3B, $i, $rowmin, $rowmax, 0, 255); // tArea3d
717
				$formulaData .= pack('C', 0x10); // tList
718
 
719
				// store the DEFINEDNAME record
720
				$chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x07), $formulaData, $i + 1, true));
721
 
722
			// (exclusive) either repeatColumns or repeatRows
723
			} else if ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) {
724
 
725
				// Columns to repeat
726
				if ($sheetSetup->isColumnsToRepeatAtLeftSet()) {
727
					$repeat = $sheetSetup->getColumnsToRepeatAtLeft();
728
					$colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
729
					$colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
730
				} else {
731
					$colmin = 0;
732
					$colmax = 255;
733
				}
734
				// Rows to repeat
735
				if ($sheetSetup->isRowsToRepeatAtTopSet()) {
736
					$repeat = $sheetSetup->getRowsToRepeatAtTop();
737
					$rowmin = $repeat[0] - 1;
738
					$rowmax = $repeat[1] - 1;
739
				} else {
740
					$rowmin = 0;
741
					$rowmax = 65535;
742
				}
743
 
744
				// construct formula data manually because parser does not recognize absolute 3d cell references
745
				$formulaData = pack('Cvvvvv', 0x3B, $i, $rowmin, $rowmax, $colmin, $colmax);
746
 
747
				// store the DEFINEDNAME record
748
				$chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x07), $formulaData, $i + 1, true));
749
			}
750
		}
751
 
752
		// write the print areas, if any
753
		for ($i = 0; $i < $total_worksheets; ++$i) {
754
			$sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
755
			if ($sheetSetup->isPrintAreaSet()) {
756
				// Print area, e.g. A3:J6,H1:X20
757
				$printArea = PHPExcel_Cell::splitRange($sheetSetup->getPrintArea());
758
				$countPrintArea = count($printArea);
759
 
760
				$formulaData = '';
761
				for ($j = 0; $j < $countPrintArea; ++$j) {
762
					$printAreaRect = $printArea[$j]; // e.g. A3:J6
763
					$printAreaRect[0] = PHPExcel_Cell::coordinateFromString($printAreaRect[0]);
764
					$printAreaRect[1] = PHPExcel_Cell::coordinateFromString($printAreaRect[1]);
765
 
766
					$print_rowmin = $printAreaRect[0][1] - 1;
767
					$print_rowmax = $printAreaRect[1][1] - 1;
768
					$print_colmin = PHPExcel_Cell::columnIndexFromString($printAreaRect[0][0]) - 1;
769
					$print_colmax = PHPExcel_Cell::columnIndexFromString($printAreaRect[1][0]) - 1;
770
 
771
					// construct formula data manually because parser does not recognize absolute 3d cell references
772
					$formulaData .= pack('Cvvvvv', 0x3B, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax);
773
 
774
					if ($j > 0) {
775
						$formulaData .= pack('C', 0x10); // list operator token ','
776
					}
777
				}
778
 
779
				// store the DEFINEDNAME record
780
				$chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x06), $formulaData, $i + 1, true));
781
			}
782
		}
783
 
784
		// write autofilters, if any
785
		for ($i = 0; $i < $total_worksheets; ++$i) {
786
			$sheetAutoFilter = $this->_phpExcel->getSheet($i)->getAutoFilter();
787
			$autoFilterRange = $sheetAutoFilter->getRange();
788
			if(!empty($autoFilterRange)) {
789
				$rangeBounds = PHPExcel_Cell::rangeBoundaries($autoFilterRange);
790
 
791
				//Autofilter built in name
792
				$name = pack('C', 0x0D);
793
 
794
				$chunk .= $this->writeData($this->_writeShortNameBiff8($name, $i + 1, $rangeBounds, true));
795
			}
796
		}
797
 
798
		return $chunk;
799
	}
800
 
801
	/**
802
	 * Write a DEFINEDNAME record for BIFF8 using explicit binary formula data
803
	 *
804
	 * @param	string		$name			The name in UTF-8
805
	 * @param	string		$formulaData	The binary formula data
806
	 * @param	string		$sheetIndex		1-based sheet index the defined name applies to. 0 = global
807
	 * @param	boolean		$isBuiltIn		Built-in name?
808
	 * @return	string	Complete binary record data
809
	 */
810
	private function _writeDefinedNameBiff8($name, $formulaData, $sheetIndex = 0, $isBuiltIn = false)
811
	{
812
		$record = 0x0018;
813
 
814
		// option flags
815
		$options = $isBuiltIn ? 0x20 : 0x00;
816
 
817
		// length of the name, character count
818
		$nlen = PHPExcel_Shared_String::CountCharacters($name);
819
 
820
		// name with stripped length field
821
		$name = substr(PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($name), 2);
822
 
823
		// size of the formula (in bytes)
824
		$sz = strlen($formulaData);
825
 
826
		// combine the parts
827
		$data = pack('vCCvvvCCCC', $options, 0, $nlen, $sz, 0, $sheetIndex, 0, 0, 0, 0)
828
			. $name . $formulaData;
829
		$length = strlen($data);
830
 
831
		$header = pack('vv', $record, $length);
832
 
833
		return $header . $data;
834
	}
835
 
836
	/**
837
	 * Write a short NAME record
838
	 *
839
	 * @param	string		 $name
840
	 * @param	string		 $sheetIndex		1-based sheet index the defined name applies to. 0 = global
841
	 * @param	integer[][]  $rangeBounds    range boundaries
842
	 * @param	boolean      $isHidden
843
	 * @return	string	Complete binary record data
844
	 * */
845
	private function _writeShortNameBiff8($name, $sheetIndex = 0, $rangeBounds, $isHidden = false){
846
		$record = 0x0018;
847
 
848
		// option flags
849
		$options = ($isHidden  ? 0x21 : 0x00);
850
 
851
		$extra  = pack('Cvvvvv',
852
				0x3B,
853
				$sheetIndex - 1,
854
				$rangeBounds[0][1] - 1,
855
				$rangeBounds[1][1] - 1,
856
				$rangeBounds[0][0] - 1,
857
				$rangeBounds[1][0] - 1);
858
 
859
		// size of the formula (in bytes)
860
		$sz = strlen($extra);
861
 
862
		// combine the parts
863
		$data = pack('vCCvvvCCCCC', $options, 0, 1, $sz, 0, $sheetIndex, 0, 0, 0, 0, 0)
864
			. $name . $extra;
865
		$length = strlen($data);
866
 
867
		$header = pack('vv', $record, $length);
868
 
869
		return $header . $data;
870
	}
871
 
872
	/**
873
	 * Stores the CODEPAGE biff record.
874
	 */
875
	private function _writeCodepage()
876
	{
877
		$record          = 0x0042;             // Record identifier
878
		$length          = 0x0002;             // Number of bytes to follow
879
		$cv              = $this->_codepage;   // The code page
880
 
881
		$header          = pack('vv', $record, $length);
882
		$data            = pack('v',  $cv);
883
 
884
		$this->_append($header . $data);
885
	}
886
 
887
	/**
888
	 * Write Excel BIFF WINDOW1 record.
889
	 */
890
	private function _writeWindow1()
891
	{
892
		$record    = 0x003D;                 // Record identifier
893
		$length    = 0x0012;                 // Number of bytes to follow
894
 
895
		$xWn       = 0x0000;                 // Horizontal position of window
896
		$yWn       = 0x0000;                 // Vertical position of window
897
		$dxWn      = 0x25BC;                 // Width of window
898
		$dyWn      = 0x1572;                 // Height of window
899
 
900
		$grbit     = 0x0038;                 // Option flags
901
 
902
		// not supported by PHPExcel, so there is only one selected sheet, the active
903
		$ctabsel   = 1;       // Number of workbook tabs selected
904
 
905
		$wTabRatio = 0x0258;                 // Tab to scrollbar ratio
906
 
907
		// not supported by PHPExcel, set to 0
908
		$itabFirst = 0;     // 1st displayed worksheet
909
		$itabCur   = $this->_phpExcel->getActiveSheetIndex();    // Active worksheet
910
 
911
		$header    = pack("vv",        $record, $length);
912
		$data      = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,
913
									   $grbit,
914
									   $itabCur, $itabFirst,
915
									   $ctabsel, $wTabRatio);
916
		$this->_append($header . $data);
917
	}
918
 
919
	/**
920
	 * Writes Excel BIFF BOUNDSHEET record.
921
	 *
922
	 * @param PHPExcel_Worksheet  $sheet Worksheet name
923
	 * @param integer $offset    Location of worksheet BOF
924
	 */
925
	private function _writeBoundsheet($sheet, $offset)
926
	{
927
		$sheetname = $sheet->getTitle();
928
		$record    = 0x0085;                    // Record identifier
929
 
930
		// sheet state
931
		switch ($sheet->getSheetState()) {
932
			case PHPExcel_Worksheet::SHEETSTATE_VISIBLE:	$ss = 0x00; break;
933
			case PHPExcel_Worksheet::SHEETSTATE_HIDDEN:		$ss = 0x01; break;
934
			case PHPExcel_Worksheet::SHEETSTATE_VERYHIDDEN:	$ss = 0x02; break;
935
			default: $ss = 0x00; break;
936
		}
937
 
938
		// sheet type
939
		$st = 0x00;
940
 
941
		$grbit     = 0x0000;                    // Visibility and sheet type
942
 
943
		$data      = pack("VCC", $offset, $ss, $st);
944
		$data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($sheetname);
945
 
946
		$length = strlen($data);
947
		$header = pack("vv",  $record, $length);
948
		$this->_append($header . $data);
949
	}
950
 
951
	/**
952
	 * Write Internal SUPBOOK record
953
	 */
954
	private function _writeSupbookInternal()
955
	{
956
		$record    = 0x01AE;   // Record identifier
957
		$length    = 0x0004;   // Bytes to follow
958
 
959
		$header    = pack("vv", $record, $length);
960
		$data      = pack("vv", $this->_phpExcel->getSheetCount(), 0x0401);
961
		return $this->writeData($header . $data);
962
	}
963
 
964
	/**
965
	 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
966
	 * formulas.
967
	 *
968
	 */
969
	private function _writeExternsheetBiff8()
970
	{
971
		$total_references = count($this->_parser->_references);
972
		$record   = 0x0017;                     // Record identifier
973
		$length   = 2 + 6 * $total_references;  // Number of bytes to follow
974
 
975
		$supbook_index = 0;           // FIXME: only using internal SUPBOOK record
976
		$header           = pack("vv",  $record, $length);
977
		$data             = pack('v', $total_references);
978
		for ($i = 0; $i < $total_references; ++$i) {
979
			$data .= $this->_parser->_references[$i];
980
		}
981
		return $this->writeData($header . $data);
982
	}
983
 
984
	/**
985
	 * Write Excel BIFF STYLE records.
986
	 */
987
	private function _writeStyle()
988
	{
989
		$record    = 0x0293;   // Record identifier
990
		$length    = 0x0004;   // Bytes to follow
991
 
992
		$ixfe      = 0x8000;  // Index to cell style XF
993
		$BuiltIn   = 0x00;     // Built-in style
994
		$iLevel    = 0xff;     // Outline style level
995
 
996
		$header    = pack("vv",  $record, $length);
997
		$data      = pack("vCC", $ixfe, $BuiltIn, $iLevel);
998
		$this->_append($header . $data);
999
	}
1000
 
1001
	/**
1002
	 * Writes Excel FORMAT record for non "built-in" numerical formats.
1003
	 *
1004
	 * @param string  $format Custom format string
1005
	 * @param integer $ifmt   Format index code
1006
	 */
1007
	private function _writeNumFormat($format, $ifmt)
1008
	{
1009
		$record    = 0x041E;                      // Record identifier
1010
 
1011
		$numberFormatString = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($format);
1012
		$length    = 2 + strlen($numberFormatString);      // Number of bytes to follow
1013
 
1014
 
1015
		$header    = pack("vv", $record, $length);
1016
		$data      = pack("v", $ifmt) .  $numberFormatString;
1017
		$this->_append($header . $data);
1018
	}
1019
 
1020
	/**
1021
	 * Write DATEMODE record to indicate the date system in use (1904 or 1900).
1022
	 */
1023
	private function _writeDatemode()
1024
	{
1025
		$record    = 0x0022;         // Record identifier
1026
		$length    = 0x0002;         // Bytes to follow
1027
 
1028
		$f1904     = (PHPExcel_Shared_Date::getExcelCalendar() == PHPExcel_Shared_Date::CALENDAR_MAC_1904) ?
1029
			1 : 0;   // Flag for 1904 date system
1030
 
1031
		$header    = pack("vv", $record, $length);
1032
		$data      = pack("v", $f1904);
1033
		$this->_append($header . $data);
1034
	}
1035
 
1036
	/**
1037
	 * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
1038
	 * references in the workbook.
1039
	 *
1040
	 * Excel only stores references to external sheets that are used in NAME.
1041
	 * The workbook NAME record is required to define the print area and the repeat
1042
	 * rows and columns.
1043
	 *
1044
	 * A similar method is used in Worksheet.php for a slightly different purpose.
1045
	 *
1046
	 * @param integer $cxals Number of external references
1047
	 */
1048
	private function _writeExterncount($cxals)
1049
	{
1050
		$record   = 0x0016;          // Record identifier
1051
		$length   = 0x0002;          // Number of bytes to follow
1052
 
1053
		$header   = pack("vv", $record, $length);
1054
		$data     = pack("v",  $cxals);
1055
		$this->_append($header . $data);
1056
	}
1057
 
1058
	/**
1059
	 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
1060
	 * formulas. NAME record is required to define the print area and the repeat
1061
	 * rows and columns.
1062
	 *
1063
	 * A similar method is used in Worksheet.php for a slightly different purpose.
1064
	 *
1065
	 * @param string $sheetname Worksheet name
1066
	 */
1067
	private function _writeExternsheet($sheetname)
1068
	{
1069
		$record      = 0x0017;                     // Record identifier
1070
		$length      = 0x02 + strlen($sheetname);  // Number of bytes to follow
1071
 
1072
		$cch         = strlen($sheetname);         // Length of sheet name
1073
		$rgch        = 0x03;                       // Filename encoding
1074
 
1075
		$header      = pack("vv",  $record, $length);
1076
		$data        = pack("CC", $cch, $rgch);
1077
		$this->_append($header . $data . $sheetname);
1078
	}
1079
 
1080
	/**
1081
	 * Store the NAME record in the short format that is used for storing the print
1082
	 * area, repeat rows only and repeat columns only.
1083
	 *
1084
	 * @param integer $index  Sheet index
1085
	 * @param integer $type   Built-in name type
1086
	 * @param integer $rowmin Start row
1087
	 * @param integer $rowmax End row
1088
	 * @param integer $colmin Start colum
1089
	 * @param integer $colmax End column
1090
	 */
1091
	private function _writeNameShort($index, $type, $rowmin, $rowmax, $colmin, $colmax)
1092
	{
1093
		$record          = 0x0018;       // Record identifier
1094
		$length          = 0x0024;       // Number of bytes to follow
1095
 
1096
		$grbit           = 0x0020;       // Option flags
1097
		$chKey           = 0x00;         // Keyboard shortcut
1098
		$cch             = 0x01;         // Length of text name
1099
		$cce             = 0x0015;       // Length of text definition
1100
		$ixals           = $index + 1;   // Sheet index
1101
		$itab            = $ixals;       // Equal to ixals
1102
		$cchCustMenu     = 0x00;         // Length of cust menu text
1103
		$cchDescription  = 0x00;         // Length of description text
1104
		$cchHelptopic    = 0x00;         // Length of help topic text
1105
		$cchStatustext   = 0x00;         // Length of status bar text
1106
		$rgch            = $type;        // Built-in name type
1107
 
1108
		$unknown03       = 0x3b;
1109
		$unknown04       = 0xffff-$index;
1110
		$unknown05       = 0x0000;
1111
		$unknown06       = 0x0000;
1112
		$unknown07       = 0x1087;
1113
		$unknown08       = 0x8005;
1114
 
1115
		$header             = pack("vv", $record, $length);
1116
		$data               = pack("v", $grbit);
1117
		$data              .= pack("C", $chKey);
1118
		$data              .= pack("C", $cch);
1119
		$data              .= pack("v", $cce);
1120
		$data              .= pack("v", $ixals);
1121
		$data              .= pack("v", $itab);
1122
		$data              .= pack("C", $cchCustMenu);
1123
		$data              .= pack("C", $cchDescription);
1124
		$data              .= pack("C", $cchHelptopic);
1125
		$data              .= pack("C", $cchStatustext);
1126
		$data              .= pack("C", $rgch);
1127
		$data              .= pack("C", $unknown03);
1128
		$data              .= pack("v", $unknown04);
1129
		$data              .= pack("v", $unknown05);
1130
		$data              .= pack("v", $unknown06);
1131
		$data              .= pack("v", $unknown07);
1132
		$data              .= pack("v", $unknown08);
1133
		$data              .= pack("v", $index);
1134
		$data              .= pack("v", $index);
1135
		$data              .= pack("v", $rowmin);
1136
		$data              .= pack("v", $rowmax);
1137
		$data              .= pack("C", $colmin);
1138
		$data              .= pack("C", $colmax);
1139
		$this->_append($header . $data);
1140
	}
1141
 
1142
	/**
1143
	 * Store the NAME record in the long format that is used for storing the repeat
1144
	 * rows and columns when both are specified. This shares a lot of code with
1145
	 * _writeNameShort() but we use a separate method to keep the code clean.
1146
	 * Code abstraction for reuse can be carried too far, and I should know. ;-)
1147
	 *
1148
	 * @param integer $index Sheet index
1149
	 * @param integer $type  Built-in name type
1150
	 * @param integer $rowmin Start row
1151
	 * @param integer $rowmax End row
1152
	 * @param integer $colmin Start colum
1153
	 * @param integer $colmax End column
1154
	 */
1155
	private function _writeNameLong($index, $type, $rowmin, $rowmax, $colmin, $colmax)
1156
	{
1157
		$record          = 0x0018;       // Record identifier
1158
		$length          = 0x003d;       // Number of bytes to follow
1159
		$grbit           = 0x0020;       // Option flags
1160
		$chKey           = 0x00;         // Keyboard shortcut
1161
		$cch             = 0x01;         // Length of text name
1162
		$cce             = 0x002e;       // Length of text definition
1163
		$ixals           = $index + 1;   // Sheet index
1164
		$itab            = $ixals;       // Equal to ixals
1165
		$cchCustMenu     = 0x00;         // Length of cust menu text
1166
		$cchDescription  = 0x00;         // Length of description text
1167
		$cchHelptopic    = 0x00;         // Length of help topic text
1168
		$cchStatustext   = 0x00;         // Length of status bar text
1169
		$rgch            = $type;        // Built-in name type
1170
 
1171
		$unknown01       = 0x29;
1172
		$unknown02       = 0x002b;
1173
		$unknown03       = 0x3b;
1174
		$unknown04       = 0xffff-$index;
1175
		$unknown05       = 0x0000;
1176
		$unknown06       = 0x0000;
1177
		$unknown07       = 0x1087;
1178
		$unknown08       = 0x8008;
1179
 
1180
		$header             = pack("vv",  $record, $length);
1181
		$data               = pack("v", $grbit);
1182
		$data              .= pack("C", $chKey);
1183
		$data              .= pack("C", $cch);
1184
		$data              .= pack("v", $cce);
1185
		$data              .= pack("v", $ixals);
1186
		$data              .= pack("v", $itab);
1187
		$data              .= pack("C", $cchCustMenu);
1188
		$data              .= pack("C", $cchDescription);
1189
		$data              .= pack("C", $cchHelptopic);
1190
		$data              .= pack("C", $cchStatustext);
1191
		$data              .= pack("C", $rgch);
1192
		$data              .= pack("C", $unknown01);
1193
		$data              .= pack("v", $unknown02);
1194
		// Column definition
1195
		$data              .= pack("C", $unknown03);
1196
		$data              .= pack("v", $unknown04);
1197
		$data              .= pack("v", $unknown05);
1198
		$data              .= pack("v", $unknown06);
1199
		$data              .= pack("v", $unknown07);
1200
		$data              .= pack("v", $unknown08);
1201
		$data              .= pack("v", $index);
1202
		$data              .= pack("v", $index);
1203
		$data              .= pack("v", 0x0000);
1204
		$data              .= pack("v", 0x3fff);
1205
		$data              .= pack("C", $colmin);
1206
		$data              .= pack("C", $colmax);
1207
		// Row definition
1208
		$data              .= pack("C", $unknown03);
1209
		$data              .= pack("v", $unknown04);
1210
		$data              .= pack("v", $unknown05);
1211
		$data              .= pack("v", $unknown06);
1212
		$data              .= pack("v", $unknown07);
1213
		$data              .= pack("v", $unknown08);
1214
		$data              .= pack("v", $index);
1215
		$data              .= pack("v", $index);
1216
		$data              .= pack("v", $rowmin);
1217
		$data              .= pack("v", $rowmax);
1218
		$data              .= pack("C", 0x00);
1219
		$data              .= pack("C", 0xff);
1220
		// End of data
1221
		$data              .= pack("C", 0x10);
1222
		$this->_append($header . $data);
1223
	}
1224
 
1225
	/**
1226
	 * Stores the COUNTRY record for localization
1227
	 *
1228
	 * @return string
1229
	 */
1230
	private function _writeCountry()
1231
	{
1232
		$record          = 0x008C;    // Record identifier
1233
		$length          = 4;         // Number of bytes to follow
1234
 
1235
		$header = pack('vv',  $record, $length);
1236
		/* using the same country code always for simplicity */
1237
		$data = pack('vv', $this->_country_code, $this->_country_code);
1238
		//$this->_append($header . $data);
1239
		return $this->writeData($header . $data);
1240
	}
1241
 
1242
	/**
1243
	 * Write the RECALCID record
1244
	 *
1245
	 * @return string
1246
	 */
1247
	private function _writeRecalcId()
1248
	{
1249
		$record = 0x01C1;    // Record identifier
1250
		$length = 8;         // Number of bytes to follow
1251
 
1252
		$header = pack('vv',  $record, $length);
1253
 
1254
		// by inspection of real Excel files, MS Office Excel 2007 writes this
1255
		$data = pack('VV', 0x000001C1, 0x00001E667);
1256
 
1257
		return $this->writeData($header . $data);
1258
	}
1259
 
1260
	/**
1261
	 * Stores the PALETTE biff record.
1262
	 */
1263
	private function _writePalette()
1264
	{
1265
		$aref            = $this->_palette;
1266
 
1267
		$record          = 0x0092;                 // Record identifier
1268
		$length          = 2 + 4 * count($aref);   // Number of bytes to follow
1269
		$ccv             =         count($aref);   // Number of RGB values to follow
1270
		$data = '';                                // The RGB data
1271
 
1272
		// Pack the RGB data
1273
		foreach ($aref as $color) {
1274
			foreach ($color as $byte) {
1275
				$data .= pack("C",$byte);
1276
			}
1277
		}
1278
 
1279
		$header = pack("vvv",  $record, $length, $ccv);
1280
		$this->_append($header . $data);
1281
	}
1282
 
1283
	/**
1284
	 * Handling of the SST continue blocks is complicated by the need to include an
1285
	 * additional continuation byte depending on whether the string is split between
1286
	 * blocks or whether it starts at the beginning of the block. (There are also
1287
	 * additional complications that will arise later when/if Rich Strings are
1288
	 * supported).
1289
	 *
1290
	 * The Excel documentation says that the SST record should be followed by an
1291
	 * EXTSST record. The EXTSST record is a hash table that is used to optimise
1292
	 * access to SST. However, despite the documentation it doesn't seem to be
1293
	 * required so we will ignore it.
1294
	 *
1295
	 * @return string Binary data
1296
	 */
1297
	private function _writeSharedStringsTable()
1298
	{
1299
		// maximum size of record data (excluding record header)
1300
		$continue_limit = 8224;
1301
 
1302
		// initialize array of record data blocks
1303
		$recordDatas = array();
1304
 
1305
		// start SST record data block with total number of strings, total number of unique strings
1306
		$recordData = pack("VV", $this->_str_total, $this->_str_unique);
1307
 
1308
		// loop through all (unique) strings in shared strings table
1309
		foreach (array_keys($this->_str_table) as $string) {
1310
 
1311
			// here $string is a BIFF8 encoded string
1312
 
1313
			// length = character count
1314
			$headerinfo = unpack("vlength/Cencoding", $string);
1315
 
1316
			// currently, this is always 1 = uncompressed
1317
			$encoding = $headerinfo["encoding"];
1318
 
1319
			// initialize finished writing current $string
1320
			$finished = false;
1321
 
1322
			while ($finished === false) {
1323
 
1324
				// normally, there will be only one cycle, but if string cannot immediately be written as is
1325
				// there will be need for more than one cylcle, if string longer than one record data block, there
1326
				// may be need for even more cycles
1327
 
1328
				if (strlen($recordData) + strlen($string) <= $continue_limit) {
1329
					// then we can write the string (or remainder of string) without any problems
1330
					$recordData .= $string;
1331
 
1332
					if (strlen($recordData) + strlen($string) == $continue_limit) {
1333
						// we close the record data block, and initialize a new one
1334
						$recordDatas[] = $recordData;
1335
						$recordData = '';
1336
					}
1337
 
1338
					// we are finished writing this string
1339
					$finished = true;
1340
				} else {
1341
					// special treatment writing the string (or remainder of the string)
1342
					// If the string is very long it may need to be written in more than one CONTINUE record.
1343
 
1344
					// check how many bytes more there is room for in the current record
1345
					$space_remaining = $continue_limit - strlen($recordData);
1346
 
1347
					// minimum space needed
1348
					// uncompressed: 2 byte string length length field + 1 byte option flags + 2 byte character
1349
					// compressed:   2 byte string length length field + 1 byte option flags + 1 byte character
1350
					$min_space_needed = ($encoding == 1) ? 5 : 4;
1351
 
1352
					// We have two cases
1353
					// 1. space remaining is less than minimum space needed
1354
					//		here we must waste the space remaining and move to next record data block
1355
					// 2. space remaining is greater than or equal to minimum space needed
1356
					//		here we write as much as we can in the current block, then move to next record data block
1357
 
1358
					// 1. space remaining is less than minimum space needed
1359
					if ($space_remaining < $min_space_needed) {
1360
						// we close the block, store the block data
1361
						$recordDatas[] = $recordData;
1362
 
1363
						// and start new record data block where we start writing the string
1364
						$recordData = '';
1365
 
1366
					// 2. space remaining is greater than or equal to minimum space needed
1367
					} else {
1368
						// initialize effective remaining space, for Unicode strings this may need to be reduced by 1, see below
1369
						$effective_space_remaining = $space_remaining;
1370
 
1371
						// for uncompressed strings, sometimes effective space remaining is reduced by 1
1372
						if ( $encoding == 1 && (strlen($string) - $space_remaining) % 2 == 1 ) {
1373
							--$effective_space_remaining;
1374
						}
1375
 
1376
						// one block fininshed, store the block data
1377
						$recordData .= substr($string, 0, $effective_space_remaining);
1378
 
1379
						$string = substr($string, $effective_space_remaining); // for next cycle in while loop
1380
						$recordDatas[] = $recordData;
1381
 
1382
						// start new record data block with the repeated option flags
1383
						$recordData = pack('C', $encoding);
1384
					}
1385
				}
1386
			}
1387
		}
1388
 
1389
		// Store the last record data block unless it is empty
1390
		// if there was no need for any continue records, this will be the for SST record data block itself
1391
		if (strlen($recordData) > 0) {
1392
			$recordDatas[] = $recordData;
1393
		}
1394
 
1395
		// combine into one chunk with all the blocks SST, CONTINUE,...
1396
		$chunk = '';
1397
		foreach ($recordDatas as $i => $recordData) {
1398
			// first block should have the SST record header, remaing should have CONTINUE header
1399
			$record = ($i == 0) ? 0x00FC : 0x003C;
1400
 
1401
			$header = pack("vv", $record, strlen($recordData));
1402
			$data = $header . $recordData;
1403
 
1404
			$chunk .= $this->writeData($data);
1405
		}
1406
 
1407
		return $chunk;
1408
	}
1409
 
1410
	/**
1411
	 * Writes the MSODRAWINGGROUP record if needed. Possibly split using CONTINUE records.
1412
	 */
1413
	private function _writeMsoDrawingGroup()
1414
	{
1415
		// write the Escher stream if necessary
1416
		if (isset($this->_escher)) {
1417
			$writer = new PHPExcel_Writer_Excel5_Escher($this->_escher);
1418
			$data = $writer->close();
1419
 
1420
			$record = 0x00EB;
1421
			$length = strlen($data);
1422
			$header = pack("vv",  $record, $length);
1423
 
1424
			return $this->writeData($header . $data);
1425
 
1426
		} else {
1427
			return '';
1428
		}
1429
	}
1430
 
1431
	/**
1432
	 * Get Escher object
1433
	 *
1434
	 * @return PHPExcel_Shared_Escher
1435
	 */
1436
	public function getEscher()
1437
	{
1438
		return $this->_escher;
1439
	}
1440
 
1441
	/**
1442
	 * Set Escher object
1443
	 *
1444
	 * @param PHPExcel_Shared_Escher $pValue
1445
	 */
1446
	public function setEscher(PHPExcel_Shared_Escher $pValue = null)
1447
	{
1448
		$this->_escher = $pValue;
1449
	}
1450
}