Context Navigation

← Previous Revision
Latest Revision
Next Revision →
Blame
Revision Log

Workbook.php @ 1

Last change on this file since 1 was 1, checked in by dungnv, 11 years ago

File size: 45.5 KB

Line
1	<?php
2	/**
3	* PHPExcel
4	*
5	* Copyright (c) 2006 - 2014 PHPExcel
6	*
7	* This library is free software; you can redistribute it and/or
8	* modify it under the terms of the GNU Lesser General Public
9	* License as published by the Free Software Foundation; either
10	* version 2.1 of the License, or (at your option) any later version.
11	*
12	* This library is distributed in the hope that it will be useful,
13	* but WITHOUT ANY WARRANTY; without even the implied warranty of
14	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15	* Lesser General Public License for more details.
16	*
17	* You should have received a copy of the GNU Lesser General Public
18	* License along with this library; if not, write to the Free Software
19	* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
20	*
21	* @category PHPExcel
22	* @package PHPExcel_Writer_Excel5
23	* @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
24	* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25	* @version 1.8.0, 2014-03-02
26	*/
27
28	// Original file header of PEAR::Spreadsheet_Excel_Writer_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 - 2014 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	0
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	}

Note: See TracBrowser for help on using the repository browser.

Context Navigation

source: sourcecode/application/libraries/PHPExcel/Writer/Excel5/Workbook.php @ 1

Download in other formats: