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: }
1451: