ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
Workbook.php
Go to the documentation of this file.
1<?php
2/*
3* Module written/ported by Xavier Noguer <xnoguer@rezebra.com>
4*
5* The majority of this is _NOT_ my code. I simply ported it from the
6* PERL Spreadsheet::WriteExcel module.
7*
8* The author of the Spreadsheet::WriteExcel module is John McNamara
9* <jmcnamara@cpan.org>
10*
11* I _DO_ maintain this code, and John McNamara has nothing to do with the
12* porting of this code to PHP. Any questions directly related to this
13* class library should be directed to me.
14*
15* License Information:
16*
17* Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets
18* Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com
19*
20* This library is free software; you can redistribute it and/or
21* modify it under the terms of the GNU Lesser General Public
22* License as published by the Free Software Foundation; either
23* version 2.1 of the License, or (at your option) any later version.
24*
25* This library is distributed in the hope that it will be useful,
26* but WITHOUT ANY WARRANTY; without even the implied warranty of
27* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
28* Lesser General Public License for more details.
29*
30* You should have received a copy of the GNU Lesser General Public
31* License along with this library; if not, write to the Free Software
32* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
33*/
34
35require_once 'Spreadsheet/Excel/Writer/Format.php';
36require_once 'Spreadsheet/Excel/Writer/BIFFwriter.php';
37require_once 'Spreadsheet/Excel/Writer/Worksheet.php';
38require_once 'Spreadsheet/Excel/Writer/Parser.php';
39require_once 'OLE/PPS/Root.php';
40require_once 'OLE/PPS/File.php';
41
51{
57
63
68 var $_1904;
69
75
81
87
93
100
107
113
119
125
131
137
143
149
155
161
167
175 {
176 // It needs to call its parent's constructor explicitly
178
179 $this->_filename = $filename;
180 $this->_parser =& new Spreadsheet_Excel_Writer_Parser($this->_byte_order, $this->_BIFF_version);
181 $this->_1904 = 0;
182 $this->_activesheet = 0;
183 $this->_firstsheet = 0;
184 $this->_selected = 0;
185 $this->_xf_index = 16; // 15 style XF's and 1 cell XF.
186 $this->_fileclosed = 0;
187 $this->_biffsize = 0;
188 $this->_sheetname = 'Sheet';
189 $this->_tmp_format =& new Spreadsheet_Excel_Writer_Format($this->_BIFF_version);
190 $this->_worksheets = array();
191 $this->_sheetnames = array();
192 $this->_formats = array();
193 $this->_palette = array();
194 $this->_codepage = 0x04E4; // FIXME: should change for BIFF8
195 $this->_country_code = -1;
196 $this->_string_sizeinfo = 3;
197
198 // Add the default format for hyperlinks
199 $this->_url_format =& $this->addFormat(array('color' => 'blue', 'underline' => 1));
200 $this->_str_total = 0;
201 $this->_str_unique = 0;
202 $this->_str_table = array();
203 $this->_setPaletteXl97();
204 }
205
213 function close()
214 {
215 if ($this->_fileclosed) { // Prevent close() from being called twice.
216 return true;
217 }
218 $res = $this->_storeWorkbook();
219 if ($this->isError($res)) {
220 return $this->raiseError($res->getMessage());
221 }
222 $this->_fileclosed = 1;
223 return true;
224 }
225
235 function sheets()
236 {
237 return $this->worksheets();
238 }
239
247 function worksheets()
248 {
249 return $this->_worksheets;
250 }
251
262 function setVersion($version)
263 {
264 if ($version == 8) { // only accept version 8
265 $version = 0x0600;
266 $this->_BIFF_version = $version;
267 // change BIFFwriter limit for CONTINUE records
268 $this->_limit = 8228;
269 $this->_tmp_format->_BIFF_version = $version;
270 $this->_url_format->_BIFF_version = $version;
271 $this->_parser->_BIFF_version = $version;
272 $this->_codepage = 0x04B0;
273
274 $total_worksheets = count($this->_worksheets);
275 // change version for all worksheets too
276 for ($i = 0; $i < $total_worksheets; $i++) {
277 $this->_worksheets[$i]->_BIFF_version = $version;
278 }
279
280 $total_formats = count($this->_formats);
281 // change version for all formats too
282 for ($i = 0; $i < $total_formats; $i++) {
283 $this->_formats[$i]->_BIFF_version = $version;
284 }
285 }
286 }
287
296 {
297 $this->_country_code = $code;
298 }
299
310 function &addWorksheet($name = '')
311 {
312 $index = count($this->_worksheets);
313 $sheetname = $this->_sheetname;
314
315 if ($name == '') {
316 $name = $sheetname.($index+1);
317 }
318
319 // Check that sheetname is <= 31 chars (Excel limit before BIFF8).
320 if ($this->_BIFF_version != 0x0600)
321 {
322 if (strlen($name) > 31) {
323 return $this->raiseError("Sheetname $name must be <= 31 chars");
324 }
325 }
326
327 // Check that the worksheet name doesn't already exist: a fatal Excel error.
328 $total_worksheets = count($this->_worksheets);
329 for ($i = 0; $i < $total_worksheets; $i++) {
330 if ($this->_worksheets[$i]->getName() == $name) {
331 return $this->raiseError("Worksheet '$name' already exists");
332 }
333 }
334
335 $worksheet = new Spreadsheet_Excel_Writer_Worksheet($this->_BIFF_version,
336 $name, $index,
337 $this->_activesheet, $this->_firstsheet,
338 $this->_str_total, $this->_str_unique,
339 $this->_str_table, $this->_url_format,
340 $this->_parser, $this->_tmp_dir);
341
342 $this->_worksheets[$index] = &$worksheet; // Store ref for iterator
343 $this->_sheetnames[$index] = $name; // Store EXTERNSHEET names
344 $this->_parser->setExtSheet($name, $index); // Register worksheet name with parser
345 return $worksheet;
346 }
347
356 function &addFormat($properties = array())
357 {
358 $format = new Spreadsheet_Excel_Writer_Format($this->_BIFF_version, $this->_xf_index, $properties);
359 $this->_xf_index += 1;
360 $this->_formats[] = &$format;
361 return $format;
362 }
363
370 function &addValidator()
371 {
372 include_once 'Spreadsheet/Excel/Writer/Validator.php';
373 /* FIXME: check for successful inclusion*/
374 $valid = new Spreadsheet_Excel_Writer_Validator($this->_parser);
375 return $valid;
376 }
377
388 function setCustomColor($index, $red, $green, $blue)
389 {
390 // Match a HTML #xxyyzz style parameter
391 /*if (defined $_[1] and $_[1] =~ /^#(\w\w)(\w\w)(\w\w)/ ) {
392 @_ = ($_[0], hex $1, hex $2, hex $3);
393 }*/
394
395 // Check that the colour index is the right range
396 if ($index < 8 or $index > 64) {
397 // TODO: assign real error codes
398 return $this->raiseError("Color index $index outside range: 8 <= index <= 64");
399 }
400
401 // Check that the colour components are in the right range
402 if (($red < 0 or $red > 255) ||
403 ($green < 0 or $green > 255) ||
404 ($blue < 0 or $blue > 255))
405 {
406 return $this->raiseError("Color component outside range: 0 <= color <= 255");
407 }
408
409 $index -= 8; // Adjust colour index (wingless dragonfly)
410
411 // Set the RGB value
412 $this->_palette[$index] = array($red, $green, $blue, 0);
413 return($index + 8);
414 }
415
422 {
423 $this->_palette = array(
424 array(0x00, 0x00, 0x00, 0x00), // 8
425 array(0xff, 0xff, 0xff, 0x00), // 9
426 array(0xff, 0x00, 0x00, 0x00), // 10
427 array(0x00, 0xff, 0x00, 0x00), // 11
428 array(0x00, 0x00, 0xff, 0x00), // 12
429 array(0xff, 0xff, 0x00, 0x00), // 13
430 array(0xff, 0x00, 0xff, 0x00), // 14
431 array(0x00, 0xff, 0xff, 0x00), // 15
432 array(0x80, 0x00, 0x00, 0x00), // 16
433 array(0x00, 0x80, 0x00, 0x00), // 17
434 array(0x00, 0x00, 0x80, 0x00), // 18
435 array(0x80, 0x80, 0x00, 0x00), // 19
436 array(0x80, 0x00, 0x80, 0x00), // 20
437 array(0x00, 0x80, 0x80, 0x00), // 21
438 array(0xc0, 0xc0, 0xc0, 0x00), // 22
439 array(0x80, 0x80, 0x80, 0x00), // 23
440 array(0x99, 0x99, 0xff, 0x00), // 24
441 array(0x99, 0x33, 0x66, 0x00), // 25
442 array(0xff, 0xff, 0xcc, 0x00), // 26
443 array(0xcc, 0xff, 0xff, 0x00), // 27
444 array(0x66, 0x00, 0x66, 0x00), // 28
445 array(0xff, 0x80, 0x80, 0x00), // 29
446 array(0x00, 0x66, 0xcc, 0x00), // 30
447 array(0xcc, 0xcc, 0xff, 0x00), // 31
448 array(0x00, 0x00, 0x80, 0x00), // 32
449 array(0xff, 0x00, 0xff, 0x00), // 33
450 array(0xff, 0xff, 0x00, 0x00), // 34
451 array(0x00, 0xff, 0xff, 0x00), // 35
452 array(0x80, 0x00, 0x80, 0x00), // 36
453 array(0x80, 0x00, 0x00, 0x00), // 37
454 array(0x00, 0x80, 0x80, 0x00), // 38
455 array(0x00, 0x00, 0xff, 0x00), // 39
456 array(0x00, 0xcc, 0xff, 0x00), // 40
457 array(0xcc, 0xff, 0xff, 0x00), // 41
458 array(0xcc, 0xff, 0xcc, 0x00), // 42
459 array(0xff, 0xff, 0x99, 0x00), // 43
460 array(0x99, 0xcc, 0xff, 0x00), // 44
461 array(0xff, 0x99, 0xcc, 0x00), // 45
462 array(0xcc, 0x99, 0xff, 0x00), // 46
463 array(0xff, 0xcc, 0x99, 0x00), // 47
464 array(0x33, 0x66, 0xff, 0x00), // 48
465 array(0x33, 0xcc, 0xcc, 0x00), // 49
466 array(0x99, 0xcc, 0x00, 0x00), // 50
467 array(0xff, 0xcc, 0x00, 0x00), // 51
468 array(0xff, 0x99, 0x00, 0x00), // 52
469 array(0xff, 0x66, 0x00, 0x00), // 53
470 array(0x66, 0x66, 0x99, 0x00), // 54
471 array(0x96, 0x96, 0x96, 0x00), // 55
472 array(0x00, 0x33, 0x66, 0x00), // 56
473 array(0x33, 0x99, 0x66, 0x00), // 57
474 array(0x00, 0x33, 0x00, 0x00), // 58
475 array(0x33, 0x33, 0x00, 0x00), // 59
476 array(0x99, 0x33, 0x00, 0x00), // 60
477 array(0x99, 0x33, 0x66, 0x00), // 61
478 array(0x33, 0x33, 0x99, 0x00), // 62
479 array(0x33, 0x33, 0x33, 0x00), // 63
480 );
481 }
482
490 function _storeWorkbook()
491 {
492 if (count($this->_worksheets) == 0) {
493 return true;
494 }
495
496 // Ensure that at least one worksheet has been selected.
497 if ($this->_activesheet == 0) {
498 $this->_worksheets[0]->selected = 1;
499 }
500
501 // Calculate the number of selected worksheet tabs and call the finalization
502 // methods for each worksheet
503 $total_worksheets = count($this->_worksheets);
504 for ($i = 0; $i < $total_worksheets; $i++) {
505 if ($this->_worksheets[$i]->selected) {
506 $this->_selected++;
507 }
508 $this->_worksheets[$i]->close($this->_sheetnames);
509 }
510
511 // Add Workbook globals
512 $this->_storeBof(0x0005);
513 $this->_storeCodepage();
514 if ($this->_BIFF_version == 0x0600) {
515 $this->_storeWindow1();
516 }
517 if ($this->_BIFF_version == 0x0500) {
518 $this->_storeExterns(); // For print area and repeat rows
519 }
520 $this->_storeNames(); // For print area and repeat rows
521 if ($this->_BIFF_version == 0x0500) {
522 $this->_storeWindow1();
523 }
524 $this->_storeDatemode();
525 $this->_storeAllFonts();
526 $this->_storeAllNumFormats();
527 $this->_storeAllXfs();
528 $this->_storeAllStyles();
529 $this->_storePalette();
530 $this->_calcSheetOffsets();
531
532 // Add BOUNDSHEET records
533 for ($i = 0; $i < $total_worksheets; $i++) {
534 $this->_storeBoundsheet($this->_worksheets[$i]->name,$this->_worksheets[$i]->offset);
535 }
536
537 if ($this->_country_code != -1) {
538 $this->_storeCountry();
539 }
540
541 if ($this->_BIFF_version == 0x0600) {
542 //$this->_storeSupbookInternal();
543 /* TODO: store external SUPBOOK records and XCT and CRN records
544 in case of external references for BIFF8 */
545 //$this->_storeExternsheetBiff8();
547 }
548
549 // End Workbook globals
550 $this->_storeEof();
551
552 // Store the workbook in an OLE container
553 $res = $this->_storeOLEFile();
554 if ($this->isError($res)) {
555 return $this->raiseError($res->getMessage());
556 }
557 return true;
558 }
559
566 function _storeOLEFile()
567 {
568 if($this->_BIFF_version == 0x0600) {
569 $OLE = new OLE_PPS_File(OLE::Asc2Ucs('Workbook'));
570 } else {
571 $OLE = new OLE_PPS_File(OLE::Asc2Ucs('Book'));
572 }
573 if ($this->_tmp_dir != '') {
574 $OLE->setTempDir($this->_tmp_dir);
575 }
576 $res = $OLE->init();
577 if ($this->isError($res)) {
578 return $this->raiseError("OLE Error: ".$res->getMessage());
579 }
580 $OLE->append($this->_data);
581
582 $total_worksheets = count($this->_worksheets);
583 for ($i = 0; $i < $total_worksheets; $i++) {
584 while ($tmp = $this->_worksheets[$i]->getData()) {
585 $OLE->append($tmp);
586 }
587 }
588
589 $root = new OLE_PPS_Root(time(), time(), array($OLE));
590 if ($this->_tmp_dir != '') {
591 $root->setTempDir($this->_tmp_dir);
592 }
593
594 $res = $root->save($this->_filename);
595 if ($this->isError($res)) {
596 return $this->raiseError("OLE Error: ".$res->getMessage());
597 }
598 return true;
599 }
600
607 {
608 if ($this->_BIFF_version == 0x0600) {
609 $boundsheet_length = 12; // fixed length for a BOUNDSHEET record
610 } else {
611 $boundsheet_length = 11;
612 }
613 $EOF = 4;
614 $offset = $this->_datasize;
615
616 if ($this->_BIFF_version == 0x0600) {
617 // add the length of the SST
618 /* TODO: check this works for a lot of strings (> 8224 bytes) */
619 $offset += $this->_calculateSharedStringsSizes();
620 if ($this->_country_code != -1) {
621 $offset += 8; // adding COUNTRY record
622 }
623 // add the lenght of SUPBOOK, EXTERNSHEET and NAME records
624 //$offset += 8; // FIXME: calculate real value when storing the records
625 }
626 $total_worksheets = count($this->_worksheets);
627 // add the length of the BOUNDSHEET records
628 for ($i = 0; $i < $total_worksheets; $i++) {
629 $offset += $boundsheet_length + strlen($this->_worksheets[$i]->name);
630 }
631 $offset += $EOF;
632
633 for ($i = 0; $i < $total_worksheets; $i++) {
634 $this->_worksheets[$i]->offset = $offset;
635 $offset += $this->_worksheets[$i]->_datasize;
636 }
637 $this->_biffsize = $offset;
638 }
639
645 function _storeAllFonts()
646 {
647 // tmp_format is added by the constructor. We use this to write the default XF's
648 $format = $this->_tmp_format;
649 $font = $format->getFont();
650
651 // Note: Fonts are 0-indexed. According to the SDK there is no index 4,
652 // so the following fonts are 0, 1, 2, 3, 5
653 //
654 for ($i = 1; $i <= 5; $i++){
655 $this->_append($font);
656 }
657
658 // Iterate through the XF objects and write a FONT record if it isn't the
659 // same as the default FONT and if it hasn't already been used.
660 //
661 $fonts = array();
662 $index = 6; // The first user defined FONT
663
664 $key = $format->getFontKey(); // The default font from _tmp_format
665 $fonts[$key] = 0; // Index of the default font
666
667 $total_formats = count($this->_formats);
668 for ($i = 0; $i < $total_formats; $i++) {
669 $key = $this->_formats[$i]->getFontKey();
670 if (isset($fonts[$key])) {
671 // FONT has already been used
672 $this->_formats[$i]->font_index = $fonts[$key];
673 } else {
674 // Add a new FONT record
675 $fonts[$key] = $index;
676 $this->_formats[$i]->font_index = $index;
677 $index++;
678 $font = $this->_formats[$i]->getFont();
679 $this->_append($font);
680 }
681 }
682 }
683
690 {
691 // Leaning num_format syndrome
692 $hash_num_formats = array();
693 $num_formats = array();
694 $index = 164;
695
696 // Iterate through the XF objects and write a FORMAT record if it isn't a
697 // built-in format type and if the FORMAT string hasn't already been used.
698 $total_formats = count($this->_formats);
699 for ($i = 0; $i < $total_formats; $i++) {
700 $num_format = $this->_formats[$i]->_num_format;
701
702 // Check if $num_format is an index to a built-in format.
703 // Also check for a string of zeros, which is a valid format string
704 // but would evaluate to zero.
705 //
706 if (!preg_match("/^0+\d/", $num_format)) {
707 if (preg_match("/^\d+$/", $num_format)) { // built-in format
708 continue;
709 }
710 }
711
712 if (isset($hash_num_formats[$num_format])) {
713 // FORMAT has already been used
714 $this->_formats[$i]->_num_format = $hash_num_formats[$num_format];
715 } else{
716 // Add a new FORMAT
717 $hash_num_formats[$num_format] = $index;
718 $this->_formats[$i]->_num_format = $index;
719 array_push($num_formats,$num_format);
720 $index++;
721 }
722 }
723
724 // Write the new FORMAT records starting from 0xA4
725 $index = 164;
726 foreach ($num_formats as $num_format) {
727 $this->_storeNumFormat($num_format,$index);
728 $index++;
729 }
730 }
731
737 function _storeAllXfs()
738 {
739 // _tmp_format is added by the constructor. We use this to write the default XF's
740 // The default font index is 0
741 //
742 $format = $this->_tmp_format;
743 for ($i = 0; $i <= 14; $i++) {
744 $xf = $format->getXf('style'); // Style XF
745 $this->_append($xf);
746 }
747
748 $xf = $format->getXf('cell'); // Cell XF
749 $this->_append($xf);
750
751 // User defined XFs
752 $total_formats = count($this->_formats);
753 for ($i = 0; $i < $total_formats; $i++) {
754 $xf = $this->_formats[$i]->getXf('cell');
755 $this->_append($xf);
756 }
757 }
758
765 {
766 $this->_storeStyle();
767 }
768
775 function _storeExterns()
776 {
777 // Create EXTERNCOUNT with number of worksheets
778 $this->_storeExterncount(count($this->_worksheets));
779
780 // Create EXTERNSHEET for each worksheet
781 foreach ($this->_sheetnames as $sheetname) {
782 $this->_storeExternsheet($sheetname);
783 }
784 }
785
791 function _storeNames()
792 {
793 // Create the print area NAME records
794 $total_worksheets = count($this->_worksheets);
795 for ($i = 0; $i < $total_worksheets; $i++) {
796 // Write a Name record if the print area has been defined
797 if (isset($this->_worksheets[$i]->print_rowmin)) {
798 $this->_storeNameShort(
799 $this->_worksheets[$i]->index,
800 0x06, // NAME type
801 $this->_worksheets[$i]->print_rowmin,
802 $this->_worksheets[$i]->print_rowmax,
803 $this->_worksheets[$i]->print_colmin,
804 $this->_worksheets[$i]->print_colmax
805 );
806 }
807 }
808
809 // Create the print title NAME records
810 $total_worksheets = count($this->_worksheets);
811 for ($i = 0; $i < $total_worksheets; $i++) {
812 $rowmin = $this->_worksheets[$i]->title_rowmin;
813 $rowmax = $this->_worksheets[$i]->title_rowmax;
814 $colmin = $this->_worksheets[$i]->title_colmin;
815 $colmax = $this->_worksheets[$i]->title_colmax;
816
817 // Determine if row + col, row, col or nothing has been defined
818 // and write the appropriate record
819 //
820 if (isset($rowmin) && isset($colmin)) {
821 // Row and column titles have been defined.
822 // Row title has been defined.
823 $this->_storeNameLong(
824 $this->_worksheets[$i]->index,
825 0x07, // NAME type
826 $rowmin,
827 $rowmax,
828 $colmin,
829 $colmax
830 );
831 } elseif (isset($rowmin)) {
832 // Row title has been defined.
833 $this->_storeNameShort(
834 $this->_worksheets[$i]->index,
835 0x07, // NAME type
836 $rowmin,
837 $rowmax,
838 0x00,
839 0xff
840 );
841 } elseif (isset($colmin)) {
842 // Column title has been defined.
843 $this->_storeNameShort(
844 $this->_worksheets[$i]->index,
845 0x07, // NAME type
846 0x0000,
847 0x3fff,
848 $colmin,
849 $colmax
850 );
851 } else {
852 // Print title hasn't been defined.
853 }
854 }
855 }
856
857
858
859
860 /******************************************************************************
861 *
862 * BIFF RECORDS
863 *
864 */
865
871 function _storeCodepage()
872 {
873 $record = 0x0042; // Record identifier
874 $length = 0x0002; // Number of bytes to follow
875 $cv = $this->_codepage; // The code page
876
877 $header = pack('vv', $record, $length);
878 $data = pack('v', $cv);
879
880 $this->_append($header . $data);
881 }
882
888 function _storeWindow1()
889 {
890 $record = 0x003D; // Record identifier
891 $length = 0x0012; // Number of bytes to follow
892
893 $xWn = 0x0000; // Horizontal position of window
894 $yWn = 0x0000; // Vertical position of window
895 $dxWn = 0x25BC; // Width of window
896 $dyWn = 0x1572; // Height of window
897
898 $grbit = 0x0038; // Option flags
899 $ctabsel = $this->_selected; // Number of workbook tabs selected
900 $wTabRatio = 0x0258; // Tab to scrollbar ratio
901
902 $itabFirst = $this->_firstsheet; // 1st displayed worksheet
903 $itabCur = $this->_activesheet; // Active worksheet
904
905 $header = pack("vv", $record, $length);
906 $data = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,
907 $grbit,
908 $itabCur, $itabFirst,
909 $ctabsel, $wTabRatio);
910 $this->_append($header . $data);
911 }
912
921 function _storeBoundsheet($sheetname,$offset)
922 {
923 $record = 0x0085; // Record identifier
924 if ($this->_BIFF_version == 0x0600) {
925 $length = 0x08 + strlen($sheetname); // Number of bytes to follow
926 } else {
927 $length = 0x07 + strlen($sheetname); // Number of bytes to follow
928 }
929
930 $grbit = 0x0000; // Visibility and sheet type
931 $cch = strlen($sheetname); // Length of sheet name
932
933 $header = pack("vv", $record, $length);
934 if ($this->_BIFF_version == 0x0600) {
935 $data = pack("Vvv", $offset, $grbit, $cch);
936 } else {
937 $data = pack("VvC", $offset, $grbit, $cch);
938 }
939 $this->_append($header.$data.$sheetname);
940 }
941
948 {
949 $record = 0x01AE; // Record identifier
950 $length = 0x0004; // Bytes to follow
951
952 $header = pack("vv", $record, $length);
953 $data = pack("vv", count($this->_worksheets), 0x0104);
954 $this->_append($header . $data);
955 }
956
965 {
966 $total_references = count($this->_parser->_references);
967 $record = 0x0017; // Record identifier
968 $length = 2 + 6 * $total_references; // Number of bytes to follow
969
970 $supbook_index = 0; // FIXME: only using internal SUPBOOK record
971 $header = pack("vv", $record, $length);
972 $data = pack('v', $total_references);
973 for ($i = 0; $i < $total_references; $i++) {
974 $data .= $this->_parser->_references[$i];
975 }
976 $this->_append($header . $data);
977 }
978
984 function _storeStyle()
985 {
986 $record = 0x0293; // Record identifier
987 $length = 0x0004; // Bytes to follow
988
989 $ixfe = 0x8000; // Index to style XF
990 $BuiltIn = 0x00; // Built-in style
991 $iLevel = 0xff; // Outline style level
992
993 $header = pack("vv", $record, $length);
994 $data = pack("vCC", $ixfe, $BuiltIn, $iLevel);
995 $this->_append($header . $data);
996 }
997
998
1006 function _storeNumFormat($format, $ifmt)
1007 {
1008 $record = 0x041E; // Record identifier
1009
1010 if ($this->_BIFF_version == 0x0600) {
1011 $length = 5 + strlen($format); // Number of bytes to follow
1012 $encoding = 0x0;
1013 } elseif ($this->_BIFF_version == 0x0500) {
1014 $length = 3 + strlen($format); // Number of bytes to follow
1015 }
1016
1017 $cch = strlen($format); // Length of format string
1018
1019 $header = pack("vv", $record, $length);
1020 if ($this->_BIFF_version == 0x0600) {
1021 $data = pack("vvC", $ifmt, $cch, $encoding);
1022 } elseif ($this->_BIFF_version == 0x0500) {
1023 $data = pack("vC", $ifmt, $cch);
1024 }
1025 $this->_append($header . $data . $format);
1026 }
1027
1034 {
1035 $record = 0x0022; // Record identifier
1036 $length = 0x0002; // Bytes to follow
1037
1038 $f1904 = $this->_1904; // Flag for 1904 date system
1039
1040 $header = pack("vv", $record, $length);
1041 $data = pack("v", $f1904);
1042 $this->_append($header . $data);
1043 }
1044
1045
1059 function _storeExterncount($cxals)
1060 {
1061 $record = 0x0016; // Record identifier
1062 $length = 0x0002; // Number of bytes to follow
1063
1064 $header = pack("vv", $record, $length);
1065 $data = pack("v", $cxals);
1066 $this->_append($header . $data);
1067 }
1068
1069
1080 function _storeExternsheet($sheetname)
1081 {
1082 $record = 0x0017; // Record identifier
1083 $length = 0x02 + strlen($sheetname); // Number of bytes to follow
1084
1085 $cch = strlen($sheetname); // Length of sheet name
1086 $rgch = 0x03; // Filename encoding
1087
1088 $header = pack("vv", $record, $length);
1089 $data = pack("CC", $cch, $rgch);
1090 $this->_append($header . $data . $sheetname);
1091 }
1092
1093
1106 function _storeNameShort($index, $type, $rowmin, $rowmax, $colmin, $colmax)
1107 {
1108 $record = 0x0018; // Record identifier
1109 $length = 0x0024; // Number of bytes to follow
1110
1111 $grbit = 0x0020; // Option flags
1112 $chKey = 0x00; // Keyboard shortcut
1113 $cch = 0x01; // Length of text name
1114 $cce = 0x0015; // Length of text definition
1115 $ixals = $index + 1; // Sheet index
1116 $itab = $ixals; // Equal to ixals
1117 $cchCustMenu = 0x00; // Length of cust menu text
1118 $cchDescription = 0x00; // Length of description text
1119 $cchHelptopic = 0x00; // Length of help topic text
1120 $cchStatustext = 0x00; // Length of status bar text
1121 $rgch = $type; // Built-in name type
1122
1123 $unknown03 = 0x3b;
1124 $unknown04 = 0xffff-$index;
1125 $unknown05 = 0x0000;
1126 $unknown06 = 0x0000;
1127 $unknown07 = 0x1087;
1128 $unknown08 = 0x8005;
1129
1130 $header = pack("vv", $record, $length);
1131 $data = pack("v", $grbit);
1132 $data .= pack("C", $chKey);
1133 $data .= pack("C", $cch);
1134 $data .= pack("v", $cce);
1135 $data .= pack("v", $ixals);
1136 $data .= pack("v", $itab);
1137 $data .= pack("C", $cchCustMenu);
1138 $data .= pack("C", $cchDescription);
1139 $data .= pack("C", $cchHelptopic);
1140 $data .= pack("C", $cchStatustext);
1141 $data .= pack("C", $rgch);
1142 $data .= pack("C", $unknown03);
1143 $data .= pack("v", $unknown04);
1144 $data .= pack("v", $unknown05);
1145 $data .= pack("v", $unknown06);
1146 $data .= pack("v", $unknown07);
1147 $data .= pack("v", $unknown08);
1148 $data .= pack("v", $index);
1149 $data .= pack("v", $index);
1150 $data .= pack("v", $rowmin);
1151 $data .= pack("v", $rowmax);
1152 $data .= pack("C", $colmin);
1153 $data .= pack("C", $colmax);
1154 $this->_append($header . $data);
1155 }
1156
1157
1172 function _storeNameLong($index, $type, $rowmin, $rowmax, $colmin, $colmax)
1173 {
1174 $record = 0x0018; // Record identifier
1175 $length = 0x003d; // Number of bytes to follow
1176 $grbit = 0x0020; // Option flags
1177 $chKey = 0x00; // Keyboard shortcut
1178 $cch = 0x01; // Length of text name
1179 $cce = 0x002e; // Length of text definition
1180 $ixals = $index + 1; // Sheet index
1181 $itab = $ixals; // Equal to ixals
1182 $cchCustMenu = 0x00; // Length of cust menu text
1183 $cchDescription = 0x00; // Length of description text
1184 $cchHelptopic = 0x00; // Length of help topic text
1185 $cchStatustext = 0x00; // Length of status bar text
1186 $rgch = $type; // Built-in name type
1187
1188 $unknown01 = 0x29;
1189 $unknown02 = 0x002b;
1190 $unknown03 = 0x3b;
1191 $unknown04 = 0xffff-$index;
1192 $unknown05 = 0x0000;
1193 $unknown06 = 0x0000;
1194 $unknown07 = 0x1087;
1195 $unknown08 = 0x8008;
1196
1197 $header = pack("vv", $record, $length);
1198 $data = pack("v", $grbit);
1199 $data .= pack("C", $chKey);
1200 $data .= pack("C", $cch);
1201 $data .= pack("v", $cce);
1202 $data .= pack("v", $ixals);
1203 $data .= pack("v", $itab);
1204 $data .= pack("C", $cchCustMenu);
1205 $data .= pack("C", $cchDescription);
1206 $data .= pack("C", $cchHelptopic);
1207 $data .= pack("C", $cchStatustext);
1208 $data .= pack("C", $rgch);
1209 $data .= pack("C", $unknown01);
1210 $data .= pack("v", $unknown02);
1211 // Column definition
1212 $data .= pack("C", $unknown03);
1213 $data .= pack("v", $unknown04);
1214 $data .= pack("v", $unknown05);
1215 $data .= pack("v", $unknown06);
1216 $data .= pack("v", $unknown07);
1217 $data .= pack("v", $unknown08);
1218 $data .= pack("v", $index);
1219 $data .= pack("v", $index);
1220 $data .= pack("v", 0x0000);
1221 $data .= pack("v", 0x3fff);
1222 $data .= pack("C", $colmin);
1223 $data .= pack("C", $colmax);
1224 // Row definition
1225 $data .= pack("C", $unknown03);
1226 $data .= pack("v", $unknown04);
1227 $data .= pack("v", $unknown05);
1228 $data .= pack("v", $unknown06);
1229 $data .= pack("v", $unknown07);
1230 $data .= pack("v", $unknown08);
1231 $data .= pack("v", $index);
1232 $data .= pack("v", $index);
1233 $data .= pack("v", $rowmin);
1234 $data .= pack("v", $rowmax);
1235 $data .= pack("C", 0x00);
1236 $data .= pack("C", 0xff);
1237 // End of data
1238 $data .= pack("C", 0x10);
1239 $this->_append($header . $data);
1240 }
1241
1247 function _storeCountry()
1248 {
1249 $record = 0x008C; // Record identifier
1250 $length = 4; // Number of bytes to follow
1251
1252 $header = pack('vv', $record, $length);
1253 /* using the same country code always for simplicity */
1254 $data = pack('vv', $this->_country_code, $this->_country_code);
1255 $this->_append($header . $data);
1256 }
1257
1263 function _storePalette()
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
1294 {
1295 /* Iterate through the strings to calculate the CONTINUE block sizes.
1296 For simplicity we use the same size for the SST and CONTINUE records:
1297 8228 : Maximum Excel97 block size
1298 -4 : Length of block header
1299 -8 : Length of additional SST header information
1300 -8 : Arbitrary number to keep within _add_continue() limit = 8208
1301 */
1302 $continue_limit = 8208;
1303 $block_length = 0;
1304 $written = 0;
1305 $this->_block_sizes = array();
1306 $continue = 0;
1307
1308 foreach (array_keys($this->_str_table) as $string) {
1309 $string_length = strlen($string);
1310 $headerinfo = unpack("vlength/Cencoding", $string);
1311 $encoding = $headerinfo["encoding"];
1312 $split_string = 0;
1313
1314 // Block length is the total length of the strings that will be
1315 // written out in a single SST or CONTINUE block.
1316 $block_length += $string_length;
1317
1318 // We can write the string if it doesn't cross a CONTINUE boundary
1319 if ($block_length < $continue_limit) {
1320 $written += $string_length;
1321 continue;
1322 }
1323
1324 // Deal with the cases where the next string to be written will exceed
1325 // the CONTINUE boundary. If the string is very long it may need to be
1326 // written in more than one CONTINUE record.
1327 while ($block_length >= $continue_limit) {
1328
1329 // We need to avoid the case where a string is continued in the first
1330 // n bytes that contain the string header information.
1331 $header_length = 3; // Min string + header size -1
1332 $space_remaining = $continue_limit - $written - $continue;
1333
1334
1335 /* TODO: Unicode data should only be split on char (2 byte)
1336 boundaries. Therefore, in some cases we need to reduce the
1337 amount of available
1338 */
1339 $align = 0;
1340
1341 // Only applies to Unicode strings
1342 if ($encoding == 1) {
1343 // Min string + header size -1
1344 $header_length = 4;
1345
1346 if ($space_remaining > $header_length) {
1347 // String contains 3 byte header => split on odd boundary
1348 if (!$split_string && $space_remaining % 2 != 1) {
1349 $space_remaining--;
1350 $align = 1;
1351 }
1352 // Split section without header => split on even boundary
1353 else if ($split_string && $space_remaining % 2 == 1) {
1354 $space_remaining--;
1355 $align = 1;
1356 }
1357
1358 $split_string = 1;
1359 }
1360 }
1361
1362
1363 if ($space_remaining > $header_length) {
1364 // Write as much as possible of the string in the current block
1365 $written += $space_remaining;
1366
1367 // Reduce the current block length by the amount written
1368 $block_length -= $continue_limit - $continue - $align;
1369
1370 // Store the max size for this block
1371 $this->_block_sizes[] = $continue_limit - $align;
1372
1373 // If the current string was split then the next CONTINUE block
1374 // should have the string continue flag (grbit) set unless the
1375 // split string fits exactly into the remaining space.
1376 if ($block_length > 0) {
1377 $continue = 1;
1378 } else {
1379 $continue = 0;
1380 }
1381 } else {
1382 // Store the max size for this block
1383 $this->_block_sizes[] = $written + $continue;
1384
1385 // Not enough space to start the string in the current block
1386 $block_length -= $continue_limit - $space_remaining - $continue;
1387 $continue = 0;
1388
1389 }
1390
1391 // If the string (or substr) is small enough we can write it in the
1392 // new CONTINUE block. Else, go through the loop again to write it in
1393 // one or more CONTINUE blocks
1394 if ($block_length < $continue_limit) {
1395 $written = $block_length;
1396 } else {
1397 $written = 0;
1398 }
1399 }
1400 }
1401
1402 // Store the max size for the last block unless it is empty
1403 if ($written + $continue) {
1404 $this->_block_sizes[] = $written + $continue;
1405 }
1406
1407
1408 /* Calculate the total length of the SST and associated CONTINUEs (if any).
1409 The SST record will have a length even if it contains no strings.
1410 This length is required to set the offsets in the BOUNDSHEET records since
1411 they must be written before the SST records
1412 */
1413
1414 $tmp_block_sizes = array();
1415 $tmp_block_sizes = $this->_block_sizes;
1416
1417 $length = 12;
1418 if (!empty($tmp_block_sizes)) {
1419 $length += array_shift($tmp_block_sizes); // SST
1420 }
1421 while (!empty($tmp_block_sizes)) {
1422 $length += 4 + array_shift($tmp_block_sizes); // CONTINUEs
1423 }
1424
1425 return $length;
1426 }
1427
1440 {
1441 $record = 0x00fc; // Record identifier
1442 $length = 0x0008; // Number of bytes to follow
1443 $total = 0x0000;
1444
1445 // Iterate through the strings to calculate the CONTINUE block sizes
1446 $continue_limit = 8208;
1447 $block_length = 0;
1448 $written = 0;
1449 $continue = 0;
1450
1451 // sizes are upside down
1452 $tmp_block_sizes = $this->_block_sizes;
1453 // $tmp_block_sizes = array_reverse($this->_block_sizes);
1454
1455 // The SST record is required even if it contains no strings. Thus we will
1456 // always have a length
1457 //
1458 if (!empty($tmp_block_sizes)) {
1459 $length = 8 + array_shift($tmp_block_sizes);
1460 }
1461 else {
1462 // No strings
1463 $length = 8;
1464 }
1465
1466
1467
1468 // Write the SST block header information
1469 $header = pack("vv", $record, $length);
1470 $data = pack("VV", $this->_str_total, $this->_str_unique);
1471 $this->_append($header . $data);
1472
1473
1474
1475
1476 /* TODO: not good for performance */
1477 foreach (array_keys($this->_str_table) as $string) {
1478
1479 $string_length = strlen($string);
1480 $headerinfo = unpack("vlength/Cencoding", $string);
1481 $encoding = $headerinfo["encoding"];
1482 $split_string = 0;
1483
1484 // Block length is the total length of the strings that will be
1485 // written out in a single SST or CONTINUE block.
1486 //
1487 $block_length += $string_length;
1488
1489
1490 // We can write the string if it doesn't cross a CONTINUE boundary
1491 if ($block_length < $continue_limit) {
1492 $this->_append($string);
1493 $written += $string_length;
1494 continue;
1495 }
1496
1497 // Deal with the cases where the next string to be written will exceed
1498 // the CONTINUE boundary. If the string is very long it may need to be
1499 // written in more than one CONTINUE record.
1500 //
1501 while ($block_length >= $continue_limit) {
1502
1503 // We need to avoid the case where a string is continued in the first
1504 // n bytes that contain the string header information.
1505 //
1506 $header_length = 3; // Min string + header size -1
1507 $space_remaining = $continue_limit - $written - $continue;
1508
1509
1510 // Unicode data should only be split on char (2 byte) boundaries.
1511 // Therefore, in some cases we need to reduce the amount of available
1512 // space by 1 byte to ensure the correct alignment.
1513 $align = 0;
1514
1515 // Only applies to Unicode strings
1516 if ($encoding == 1) {
1517 // Min string + header size -1
1518 $header_length = 4;
1519
1520 if ($space_remaining > $header_length) {
1521 // String contains 3 byte header => split on odd boundary
1522 if (!$split_string && $space_remaining % 2 != 1) {
1523 $space_remaining--;
1524 $align = 1;
1525 }
1526 // Split section without header => split on even boundary
1527 else if ($split_string && $space_remaining % 2 == 1) {
1528 $space_remaining--;
1529 $align = 1;
1530 }
1531
1532 $split_string = 1;
1533 }
1534 }
1535
1536
1537 if ($space_remaining > $header_length) {
1538 // Write as much as possible of the string in the current block
1539 $tmp = substr($string, 0, $space_remaining);
1540 $this->_append($tmp);
1541
1542 // The remainder will be written in the next block(s)
1543 $string = substr($string, $space_remaining);
1544
1545 // Reduce the current block length by the amount written
1546 $block_length -= $continue_limit - $continue - $align;
1547
1548 // If the current string was split then the next CONTINUE block
1549 // should have the string continue flag (grbit) set unless the
1550 // split string fits exactly into the remaining space.
1551 //
1552 if ($block_length > 0) {
1553 $continue = 1;
1554 } else {
1555 $continue = 0;
1556 }
1557 } else {
1558 // Not enough space to start the string in the current block
1559 $block_length -= $continue_limit - $space_remaining - $continue;
1560 $continue = 0;
1561 }
1562
1563 // Write the CONTINUE block header
1564 if (!empty($this->_block_sizes)) {
1565 $record = 0x003C;
1566 $length = array_shift($tmp_block_sizes);
1567
1568 $header = pack('vv', $record, $length);
1569 if ($continue) {
1570 $header .= pack('C', $encoding);
1571 }
1572 $this->_append($header);
1573 }
1574
1575 // If the string (or substr) is small enough we can write it in the
1576 // new CONTINUE block. Else, go through the loop again to write it in
1577 // one or more CONTINUE blocks
1578 //
1579 if ($block_length < $continue_limit) {
1580 $this->_append($string);
1581 $written = $block_length;
1582 } else {
1583 $written = 0;
1584 }
1585 }
1586 }
1587 }
1588
1589
1590}
1591
$filename
Definition: buildRTE.php:89
Asc2Ucs($ascii)
Utility function to transform ASCII text to Unicode.
Definition: OLE.php:473
isError($data, $code=null)
Tell whether a value is a PEAR error.
Definition: PEAR.php:279
& raiseError($message=null, $code=null, $mode=null, $options=null, $userinfo=null, $error_class=null, $skipmsg=false)
This method is a wrapper that returns an instance of the configured error class with this object's de...
Definition: PEAR.php:524
_storeBof($type)
Writes Excel BOF record to indicate the beginning of a stream or sub-stream in the BIFF file.
Definition: BIFFwriter.php:170
Spreadsheet_Excel_Writer_BIFFwriter()
Constructor.
Definition: BIFFwriter.php:98
_storeEof()
Writes Excel EOF record to indicate the end of a BIFF stream.
Definition: BIFFwriter.php:199
_append($data)
General storage function.
Definition: BIFFwriter.php:153
_setPaletteXl97()
Sets the colour palette to the Excel 97+ default.
Definition: Workbook.php:421
_storeExternsheetBiff8()
Writes the Excel BIFF EXTERNSHEET record.
Definition: Workbook.php:964
setCustomColor($index, $red, $green, $blue)
Change the RGB components of the elements in the colour palette.
Definition: Workbook.php:388
& addFormat($properties=array())
Add a new format to the Excel workbook.
Definition: Workbook.php:356
_storeAllFonts()
Store the Excel FONT records.
Definition: Workbook.php:645
_storeNameShort($index, $type, $rowmin, $rowmax, $colmin, $colmax)
Store the NAME record in the short format that is used for storing the print area,...
Definition: Workbook.php:1106
_storeSharedStringsTable()
Write all of the workbooks strings into an indexed array.
Definition: Workbook.php:1439
_storeNames()
Write the NAME record to define the print area and the repeat rows and cols.
Definition: Workbook.php:791
_storePalette()
Stores the PALETTE biff record.
Definition: Workbook.php:1263
_storeExterncount($cxals)
Write BIFF record EXTERNCOUNT to indicate the number of external sheet references in the workbook.
Definition: Workbook.php:1059
_storeSupbookInternal()
Write Internal SUPBOOK record.
Definition: Workbook.php:947
_storeNameLong($index, $type, $rowmin, $rowmax, $colmin, $colmax)
Store the NAME record in the long format that is used for storing the repeat rows and columns when bo...
Definition: Workbook.php:1172
_calcSheetOffsets()
Calculate offsets for Worksheet BOF records.
Definition: Workbook.php:606
worksheets()
An accessor for the _worksheets[] array.
Definition: Workbook.php:247
_storeDatemode()
Write DATEMODE record to indicate the date system in use (1904 or 1900).
Definition: Workbook.php:1033
_storeAllXfs()
Write all XF records.
Definition: Workbook.php:737
_storeCountry()
Stores the COUNTRY record for localization.
Definition: Workbook.php:1247
_calculateSharedStringsSizes()
Calculate Handling of the SST continue blocks is complicated by the need to include an additional con...
Definition: Workbook.php:1293
_storeWorkbook()
Assemble worksheets into a workbook and send the BIFF data to an OLE storage.
Definition: Workbook.php:490
_storeExternsheet($sheetname)
Writes the Excel BIFF EXTERNSHEET record.
Definition: Workbook.php:1080
_storeOLEFile()
Store the workbook in an OLE container.
Definition: Workbook.php:566
_storeNumFormat($format, $ifmt)
Writes Excel FORMAT record for non "built-in" numerical formats.
Definition: Workbook.php:1006
& addWorksheet($name='')
Add a new worksheet to the Excel workbook.
Definition: Workbook.php:310
setVersion($version)
Sets the BIFF version.
Definition: Workbook.php:262
Spreadsheet_Excel_Writer_Workbook($filename)
Class constructor.
Definition: Workbook.php:174
_storeCodepage()
Stores the CODEPAGE biff record.
Definition: Workbook.php:871
sheets()
An accessor for the _worksheets[] array Returns an array of the worksheet objects in a workbook It ac...
Definition: Workbook.php:235
_storeWindow1()
Write Excel BIFF WINDOW1 record.
Definition: Workbook.php:888
_storeBoundsheet($sheetname, $offset)
Writes Excel BIFF BOUNDSHEET record.
Definition: Workbook.php:921
_storeStyle()
Write Excel BIFF STYLE records.
Definition: Workbook.php:984
& addValidator()
Create new validator.
Definition: Workbook.php:370
_storeAllNumFormats()
Store user defined numerical formats i.e.
Definition: Workbook.php:689
setCountry($code)
Set the country identifier for the workbook.
Definition: Workbook.php:295
close()
Calls finalization methods.
Definition: Workbook.php:213
_storeExterns()
Write the EXTERNCOUNT and EXTERNSHEET records.
Definition: Workbook.php:775
_storeAllStyles()
Write all STYLE records.
Definition: Workbook.php:764
$valid
$header
$data
$red
Definition: example_030.php:80
$green
Definition: example_030.php:83
$blue
Definition: example_030.php:81
$code
Definition: example_050.php:99
$fonts
Definition: example_063.php:80