ILIAS  Release_3_10_x_branch Revision 61812
 All Data Structures Namespaces Files Functions Variables Groups Pages
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 
35 require_once('Format.php');
36 require_once('classes/Spreadsheet/Excel/Writer/BIFFwriter.php');
37 require_once('classes/Spreadsheet/Excel/Writer/Worksheet.php');
38 require_once('classes/Spreadsheet/Excel/Writer/Parser.php');
39 require_once('classes/OLE/PPS/Root.php');
40 require_once('classes/OLE/PPS/File.php');
41 
50 class Spreadsheet_Excel_Writer_Workbook extends Spreadsheet_Excel_Writer_BIFFwriter
51 {
56  var $_filename;
57 
62  var $_parser;
63 
68  var $_1904;
69 
74  var $_activesheet;
75 
80  var $_firstsheet;
81 
86  var $_selected;
87 
92  var $_xf_index;
93 
99  var $_fileclosed;
100 
106  var $_biffsize;
107 
112  var $_sheetname;
113 
118  var $_tmp_format;
119 
124  var $_worksheets;
125 
130  var $_sheetnames;
131 
136  var $_formats;
137 
142  var $_palette;
143 
148  var $_url_format;
149 
154  var $_codepage;
155 
160  var $_country_code;
161 
166  var $_tmp_dir;
167 
174  function Spreadsheet_Excel_Writer_Workbook($filename)
175  {
176  // It needs to call its parent's constructor explicitly
177  $this->Spreadsheet_Excel_Writer_BIFFwriter();
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 
197  // Add the default format for hyperlinks
198  $this->_url_format =& $this->addFormat(array('color' => 'blue', 'underline' => 1));
199  $this->_str_total = 0;
200  $this->_str_unique = 0;
201  $this->_str_table = array();
202  $this->_setPaletteXl97();
203  $this->_tmp_dir = '';
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 = 8224;
269  $this->_tmp_format->_BIFF_version = $version;
270  $this->_url_format->_BIFF_version = $version;
271  $this->_parser->_BIFF_version = $version;
272  $total_worksheets = count($this->_worksheets);
273  // change version for all worksheets too
274  for ($i = 0; $i < $total_worksheets; $i++) {
275  $this->_worksheets[$i]->_BIFF_version = $version;
276  }
277  $total_formats = count($this->_formats);
278  // change version for all formats too
279  for ($i = 0; $i < $total_formats; $i++) {
280  $this->_formats[$i]->_BIFF_version = $version;
281  }
282  }
283  }
284 
292  function setCountry($code)
293  {
294  $this->_country_code = $code;
295  }
296 
307  function &addWorksheet($name = '')
308  {
309  $index = count($this->_worksheets);
310  $sheetname = $this->_sheetname;
311 
312  if ($name == '') {
313  $name = $sheetname.($index+1);
314  }
315 
316  // Check that sheetname is <= 31 chars (Excel limit).
317  if (strlen($name) > 31) {
318  return $this->raiseError("Sheetname $name must be <= 31 chars");
319  }
320 
321  // Check that the worksheet name doesn't already exist: a fatal Excel error.
322  $total_worksheets = count($this->_worksheets);
323  for ($i=0; $i < $total_worksheets; $i++)
324  {
325  if ($name == $this->_worksheets[$i]->getName()) {
326  return $this->raiseError("Worksheet '$name' already exists");
327  }
328  }
329 
330  $worksheet = new Spreadsheet_Excel_Writer_Worksheet($this->_BIFF_version,
331  $name, $index,
332  $this->_activesheet, $this->_firstsheet,
333  $this->_str_total, $this->_str_unique,
334  $this->_str_table, $this->_url_format,
335  $this->_parser);
336 
337  $this->_worksheets[$index] = &$worksheet; // Store ref for iterator
338  $this->_sheetnames[$index] = $name; // Store EXTERNSHEET names
339  $this->_parser->setExtSheet($name, $index); // Register worksheet name with parser
340  return $worksheet;
341  }
342 
351  function &addFormat($properties = array())
352  {
353  $format = new Spreadsheet_Excel_Writer_Format($this->_BIFF_version, $this->_xf_index,$properties);
354  $this->_xf_index += 1;
355  $this->_formats[] = &$format;
356  return $format;
357  }
358 
365  function &addValidator()
366  {
367  include_once('Validator.php');
368  /* FIXME: check for successful inclusion*/
369  $valid = new Spreadsheet_Excel_Writer_Validator($this->_parser);
370  return $valid;
371  }
372 
383  function setCustomColor($index,$red,$green,$blue)
384  {
385  // Match a HTML #xxyyzz style parameter
386  /*if (defined $_[1] and $_[1] =~ /^#(\w\w)(\w\w)(\w\w)/ ) {
387  @_ = ($_[0], hex $1, hex $2, hex $3);
388  }*/
389 
390  // Check that the colour index is the right range
391  if ($index < 8 or $index > 64) {
392  // TODO: assign real error codes
393  return $this->raiseError("Color index $index outside range: 8 <= index <= 64");
394  }
395 
396  // Check that the colour components are in the right range
397  if ( ($red < 0 or $red > 255) or
398  ($green < 0 or $green > 255) or
399  ($blue < 0 or $blue > 255) )
400  {
401  return $this->raiseError("Color component outside range: 0 <= color <= 255");
402  }
403 
404  $index -= 8; // Adjust colour index (wingless dragonfly)
405 
406  // Set the RGB value
407  $this->_palette[$index] = array($red, $green, $blue, 0);
408  return($index + 8);
409  }
410 
416  function _setPaletteXl97()
417  {
418  $this->_palette = array(
419  array(0x00, 0x00, 0x00, 0x00), // 8
420  array(0xff, 0xff, 0xff, 0x00), // 9
421  array(0xff, 0x00, 0x00, 0x00), // 10
422  array(0x00, 0xff, 0x00, 0x00), // 11
423  array(0x00, 0x00, 0xff, 0x00), // 12
424  array(0xff, 0xff, 0x00, 0x00), // 13
425  array(0xff, 0x00, 0xff, 0x00), // 14
426  array(0x00, 0xff, 0xff, 0x00), // 15
427  array(0x80, 0x00, 0x00, 0x00), // 16
428  array(0x00, 0x80, 0x00, 0x00), // 17
429  array(0x00, 0x00, 0x80, 0x00), // 18
430  array(0x80, 0x80, 0x00, 0x00), // 19
431  array(0x80, 0x00, 0x80, 0x00), // 20
432  array(0x00, 0x80, 0x80, 0x00), // 21
433  array(0xc0, 0xc0, 0xc0, 0x00), // 22
434  array(0x80, 0x80, 0x80, 0x00), // 23
435  array(0x99, 0x99, 0xff, 0x00), // 24
436  array(0x99, 0x33, 0x66, 0x00), // 25
437  array(0xff, 0xff, 0xcc, 0x00), // 26
438  array(0xcc, 0xff, 0xff, 0x00), // 27
439  array(0x66, 0x00, 0x66, 0x00), // 28
440  array(0xff, 0x80, 0x80, 0x00), // 29
441  array(0x00, 0x66, 0xcc, 0x00), // 30
442  array(0xcc, 0xcc, 0xff, 0x00), // 31
443  array(0x00, 0x00, 0x80, 0x00), // 32
444  array(0xff, 0x00, 0xff, 0x00), // 33
445  array(0xff, 0xff, 0x00, 0x00), // 34
446  array(0x00, 0xff, 0xff, 0x00), // 35
447  array(0x80, 0x00, 0x80, 0x00), // 36
448  array(0x80, 0x00, 0x00, 0x00), // 37
449  array(0x00, 0x80, 0x80, 0x00), // 38
450  array(0x00, 0x00, 0xff, 0x00), // 39
451  array(0x00, 0xcc, 0xff, 0x00), // 40
452  array(0xcc, 0xff, 0xff, 0x00), // 41
453  array(0xcc, 0xff, 0xcc, 0x00), // 42
454  array(0xff, 0xff, 0x99, 0x00), // 43
455  array(0x99, 0xcc, 0xff, 0x00), // 44
456  array(0xff, 0x99, 0xcc, 0x00), // 45
457  array(0xcc, 0x99, 0xff, 0x00), // 46
458  array(0xff, 0xcc, 0x99, 0x00), // 47
459  array(0x33, 0x66, 0xff, 0x00), // 48
460  array(0x33, 0xcc, 0xcc, 0x00), // 49
461  array(0x99, 0xcc, 0x00, 0x00), // 50
462  array(0xff, 0xcc, 0x00, 0x00), // 51
463  array(0xff, 0x99, 0x00, 0x00), // 52
464  array(0xff, 0x66, 0x00, 0x00), // 53
465  array(0x66, 0x66, 0x99, 0x00), // 54
466  array(0x96, 0x96, 0x96, 0x00), // 55
467  array(0x00, 0x33, 0x66, 0x00), // 56
468  array(0x33, 0x99, 0x66, 0x00), // 57
469  array(0x00, 0x33, 0x00, 0x00), // 58
470  array(0x33, 0x33, 0x00, 0x00), // 59
471  array(0x99, 0x33, 0x00, 0x00), // 60
472  array(0x99, 0x33, 0x66, 0x00), // 61
473  array(0x33, 0x33, 0x99, 0x00), // 62
474  array(0x33, 0x33, 0x33, 0x00), // 63
475  );
476  }
477 
485  function _storeWorkbook()
486  {
487  // Ensure that at least one worksheet has been selected.
488  if ($this->_activesheet == 0) {
489  $this->_worksheets[0]->selected = 1;
490  }
491 
492  // Calculate the number of selected worksheet tabs and call the finalization
493  // methods for each worksheet
494  $total_worksheets = count($this->_worksheets);
495  for ($i=0; $i < $total_worksheets; $i++) {
496  if ($this->_worksheets[$i]->selected) {
497  $this->_selected++;
498  }
499  $this->_worksheets[$i]->close($this->_sheetnames);
500  }
501 
502  // Add Workbook globals
503  $this->_storeBof(0x0005);
504  if ($this->_BIFF_version == 0x0600) {
505  $this->_storeCodepage();
506  $this->_storeWindow1();
507  }
508  if ($this->_BIFF_version == 0x0500) {
509  $this->_storeExterns(); // For print area and repeat rows
510  }
511  $this->_storeNames(); // For print area and repeat rows
512  if ($this->_BIFF_version == 0x0500) {
513  $this->_storeWindow1();
514  }
515  $this->_storeDatemode();
516  $this->_storeAllFonts();
517  $this->_storeAllNumFormats();
518  $this->_storeAllXfs();
519  $this->_storeAllStyles();
520  $this->_storePalette();
521  $this->_calcSheetOffsets();
522 
523  // Add BOUNDSHEET records
524  for ($i=0; $i < $total_worksheets; $i++) {
525  $this->_storeBoundsheet($this->_worksheets[$i]->name,$this->_worksheets[$i]->offset);
526  }
527 
528  if ($this->_country_code != -1) {
529  $this->_storeCountry();
530  }
531 
532  if ($this->_BIFF_version == 0x0600) {
533  //$this->_storeSupbookInternal();
534  /* TODO: store external SUPBOOK records and XCT and CRN records
535  in case of external references for BIFF8 */
536  //$this->_storeExternsheetBiff8();
537  $this->_storeSharedStringsTable();
538  }
539 
540  // End Workbook globals
541  $this->_storeEof();
542 
543  // Store the workbook in an OLE container
544  $res = $this->_storeOLEFile();
545  if ($this->isError($res)) {
546  return $this->raiseError($res->getMessage());
547  }
548  return true;
549  }
550 
558  function setTempDir($dir)
559  {
560  if (is_dir($dir)) {
561  $this->_tmp_dir = $dir;
562  return true;
563  }
564  return false;
565  }
566 
573  function _storeOLEFile()
574  {
575  $OLE = new OLE_PPS_File(OLE::Asc2Ucs('Book'));
576  if ($this->_tmp_dir != '') {
577  $OLE->setTempDir($this->_tmp_dir);
578  }
579  $res = $OLE->init();
580  if ($this->isError($res)) {
581  return $this->raiseError("OLE Error: ".$res->getMessage());
582  }
583  $OLE->append($this->_data);
584  $total_worksheets = count($this->_worksheets);
585  for ($i = 0; $i < $total_worksheets; $i++)
586  {
587  while ($tmp = $this->_worksheets[$i]->getData()) {
588  $OLE->append($tmp);
589  }
590  }
591  $root = new OLE_PPS_Root(time(), time(), array($OLE));
592  if ($this->_tmp_dir != '') {
593  $root->setTempDir($this->_tmp_dir);
594  }
595  $res = $root->save($this->_filename);
596  if ($this->isError($res)) {
597  return $this->raiseError("OLE Error: ".$res->getMessage());
598  }
599  return true;
600  }
601 
607  function _calcSheetOffsets()
608  {
609  if ($this->_BIFF_version == 0x0600) {
610  $boundsheet_length = 12; // fixed length for a BOUNDSHEET record
611  }
612  else {
613  $boundsheet_length = 11;
614  }
615  $EOF = 4;
616  $offset = $this->_datasize;
617 
618  if ($this->_BIFF_version == 0x0600) {
619  // add the length of the SST
620  /* TODO: check this works for a lot of strings (> 8224 bytes) */
621  $offset += $this->_calculateSharedStringsSizes();
622  if ($this->_country_code != -1) {
623  $offset += 8; // adding COUNTRY record
624  }
625  // add the lenght of SUPBOOK, EXTERNSHEET and NAME records
626  //$offset += 8; // FIXME: calculate real value when storing the records
627  }
628  $total_worksheets = count($this->_worksheets);
629  // add the length of the BOUNDSHEET records
630  for ($i=0; $i < $total_worksheets; $i++) {
631  $offset += $boundsheet_length + strlen($this->_worksheets[$i]->name);
632  }
633  $offset += $EOF;
634 
635  for ($i=0; $i < $total_worksheets; $i++) {
636  $this->_worksheets[$i]->offset = $offset;
637  $offset += $this->_worksheets[$i]->_datasize;
638  }
639  $this->_biffsize = $offset;
640  }
641 
647  function _storeAllFonts()
648  {
649  // tmp_format is added by the constructor. We use this to write the default XF's
650  $format = $this->_tmp_format;
651  $font = $format->getFont();
652 
653  // Note: Fonts are 0-indexed. According to the SDK there is no index 4,
654  // so the following fonts are 0, 1, 2, 3, 5
655  //
656  for ($i=1; $i <= 5; $i++){
657  $this->_append($font);
658  }
659 
660  // Iterate through the XF objects and write a FONT record if it isn't the
661  // same as the default FONT and if it hasn't already been used.
662  //
663  $fonts = array();
664  $index = 6; // The first user defined FONT
665 
666  $key = $format->getFontKey(); // The default font from _tmp_format
667  $fonts[$key] = 0; // Index of the default font
668 
669  $total_formats = count($this->_formats);
670  for ($i=0; $i < $total_formats; $i++)
671  {
672  $key = $this->_formats[$i]->getFontKey();
673  if (isset($fonts[$key])) {
674  // FONT has already been used
675  $this->_formats[$i]->font_index = $fonts[$key];
676  }
677  else {
678  // Add a new FONT record
679  $fonts[$key] = $index;
680  $this->_formats[$i]->font_index = $index;
681  $index++;
682  $font = $this->_formats[$i]->getFont();
683  $this->_append($font);
684  }
685  }
686  }
687 
693  function _storeAllNumFormats()
694  {
695  // Leaning num_format syndrome
696  $hash_num_formats = array();
697  $num_formats = array();
698  $index = 164;
699 
700  // Iterate through the XF objects and write a FORMAT record if it isn't a
701  // built-in format type and if the FORMAT string hasn't already been used.
702  $total_formats = count($this->_formats);
703  for ($i=0; $i < $total_formats; $i++)
704  {
705  $num_format = $this->_formats[$i]->_num_format;
706 
707  // Check if $num_format is an index to a built-in format.
708  // Also check for a string of zeros, which is a valid format string
709  // but would evaluate to zero.
710  //
711  if (!preg_match("/^0+\d/",$num_format))
712  {
713  if (preg_match("/^\d+$/",$num_format)) { // built-in format
714  continue;
715  }
716  }
717 
718  if (isset($hash_num_formats[$num_format])) {
719  // FORMAT has already been used
720  $this->_formats[$i]->_num_format = $hash_num_formats[$num_format];
721  }
722  else{
723  // Add a new FORMAT
724  $hash_num_formats[$num_format] = $index;
725  $this->_formats[$i]->_num_format = $index;
726  array_push($num_formats,$num_format);
727  $index++;
728  }
729  }
730 
731  // Write the new FORMAT records starting from 0xA4
732  $index = 164;
733  foreach ($num_formats as $num_format) {
734  $this->_storeNumFormat($num_format,$index);
735  $index++;
736  }
737  }
738 
744  function _storeAllXfs()
745  {
746  // _tmp_format is added by the constructor. We use this to write the default XF's
747  // The default font index is 0
748  //
749  $format = $this->_tmp_format;
750  for ($i=0; $i <= 14; $i++) {
751  $xf = $format->getXf('style'); // Style XF
752  $this->_append($xf);
753  }
754 
755  $xf = $format->getXf('cell'); // Cell XF
756  $this->_append($xf);
757 
758  // User defined XFs
759  $total_formats = count($this->_formats);
760  for ($i=0; $i < $total_formats; $i++) {
761  $xf = $this->_formats[$i]->getXf('cell');
762  $this->_append($xf);
763  }
764  }
765 
771  function _storeAllStyles()
772  {
773  $this->_storeStyle();
774  }
775 
782  function _storeExterns()
783  {
784  // Create EXTERNCOUNT with number of worksheets
785  $this->_storeExterncount(count($this->_worksheets));
786 
787  // Create EXTERNSHEET for each worksheet
788  foreach ($this->_sheetnames as $sheetname) {
789  $this->_storeExternsheet($sheetname);
790  }
791  }
792 
798  function _storeNames()
799  {
800  // Create the print area NAME records
801  $total_worksheets = count($this->_worksheets);
802  for ($i = 0; $i < $total_worksheets; $i++) {
803  // Write a Name record if the print area has been defined
804  if (isset($this->_worksheets[$i]->print_rowmin))
805  {
806  $this->_storeNameShort(
807  $this->_worksheets[$i]->index,
808  0x06, // NAME type
809  $this->_worksheets[$i]->print_rowmin,
810  $this->_worksheets[$i]->print_rowmax,
811  $this->_worksheets[$i]->print_colmin,
812  $this->_worksheets[$i]->print_colmax
813  );
814  }
815  }
816 
817  // Create the print title NAME records
818  $total_worksheets = count($this->_worksheets);
819  for ($i = 0; $i < $total_worksheets; $i++) {
820  $rowmin = $this->_worksheets[$i]->title_rowmin;
821  $rowmax = $this->_worksheets[$i]->title_rowmax;
822  $colmin = $this->_worksheets[$i]->title_colmin;
823  $colmax = $this->_worksheets[$i]->title_colmax;
824 
825  // Determine if row + col, row, col or nothing has been defined
826  // and write the appropriate record
827  //
828  if (isset($rowmin) and isset($colmin)) {
829  // Row and column titles have been defined.
830  // Row title has been defined.
831  $this->_storeNameLong(
832  $this->_worksheets[$i]->index,
833  0x07, // NAME type
834  $rowmin,
835  $rowmax,
836  $colmin,
837  $colmax
838  );
839  }
840  elseif (isset($rowmin)) {
841  // Row title has been defined.
842  $this->_storeNameShort(
843  $this->_worksheets[$i]->index,
844  0x07, // NAME type
845  $rowmin,
846  $rowmax,
847  0x00,
848  0xff
849  );
850  }
851  elseif (isset($colmin)) {
852  // Column title has been defined.
853  $this->_storeNameShort(
854  $this->_worksheets[$i]->index,
855  0x07, // NAME type
856  0x0000,
857  0x3fff,
858  $colmin,
859  $colmax
860  );
861  }
862  else {
863  // Print title hasn't been defined.
864  }
865  }
866  }
867 
868 
869 
870 
871  /******************************************************************************
872  *
873  * BIFF RECORDS
874  *
875  */
876 
882  function _storeCodepage()
883  {
884  $record = 0x0042; // Record identifier
885  $length = 0x0002; // Number of bytes to follow
886  $cv = $this->_codepage; // The code page
887 
888  $header = pack('vv', $record, $length);
889  $data = pack('v', $cv);
890 
891  $this->_append($header.$data);
892  }
893 
899  function _storeWindow1()
900  {
901  $record = 0x003D; // Record identifier
902  $length = 0x0012; // Number of bytes to follow
903 
904  $xWn = 0x0000; // Horizontal position of window
905  $yWn = 0x0000; // Vertical position of window
906  $dxWn = 0x25BC; // Width of window
907  $dyWn = 0x1572; // Height of window
908 
909  $grbit = 0x0038; // Option flags
910  $ctabsel = $this->_selected; // Number of workbook tabs selected
911  $wTabRatio = 0x0258; // Tab to scrollbar ratio
912 
913  $itabFirst = $this->_firstsheet; // 1st displayed worksheet
914  $itabCur = $this->_activesheet; // Active worksheet
915 
916  $header = pack("vv", $record, $length);
917  $data = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,
918  $grbit,
919  $itabCur, $itabFirst,
920  $ctabsel, $wTabRatio);
921  $this->_append($header.$data);
922  }
923 
932  function _storeBoundsheet($sheetname,$offset)
933  {
934  $record = 0x0085; // Record identifier
935  if ($this->_BIFF_version == 0x0600) {
936  $length = 0x08 + strlen($sheetname); // Number of bytes to follow
937  }
938  else {
939  $length = 0x07 + strlen($sheetname); // Number of bytes to follow
940  }
941 
942  $grbit = 0x0000; // Visibility and sheet type
943  $cch = strlen($sheetname); // Length of sheet name
944 
945  $header = pack("vv", $record, $length);
946  if ($this->_BIFF_version == 0x0600) {
947  $data = pack("Vvv", $offset, $grbit, $cch);
948  }
949  else {
950  $data = pack("VvC", $offset, $grbit, $cch);
951  }
952  $this->_append($header.$data.$sheetname);
953  }
954 
960  function _storeSupbookInternal()
961  {
962  $record = 0x01AE; // Record identifier
963  $length = 0x0004; // Bytes to follow
964 
965  $header = pack("vv", $record, $length);
966  $data = pack("vv", count($this->_worksheets), 0x0104);
967  $this->_append($header.$data);
968  }
969 
977  function _storeExternsheetBiff8()
978  {
979  $total_references = count($this->_parser->_references);
980  $record = 0x0017; // Record identifier
981  $length = 2 + 6 * $total_references; // Number of bytes to follow
982 
983  $supbook_index = 0; // FIXME: only using internal SUPBOOK record
984  $header = pack("vv", $record, $length);
985  $data = pack('v', $total_references);
986  for ($i = 0; $i < $total_references; $i++) {
987  $data .= $this->_parser->_references[$i];
988  }
989  $this->_append($header.$data);
990  }
991 
997  function _storeStyle()
998  {
999  $record = 0x0293; // Record identifier
1000  $length = 0x0004; // Bytes to follow
1001 
1002  $ixfe = 0x8000; // Index to style XF
1003  $BuiltIn = 0x00; // Built-in style
1004  $iLevel = 0xff; // Outline style level
1005 
1006  $header = pack("vv", $record, $length);
1007  $data = pack("vCC", $ixfe, $BuiltIn, $iLevel);
1008  $this->_append($header.$data);
1009  }
1010 
1011 
1019  function _storeNumFormat($format,$ifmt)
1020  {
1021  $record = 0x041E; // Record identifier
1022 
1023  if ($this->_BIFF_version == 0x0600) {
1024  $length = 5 + strlen($format); // Number of bytes to follow
1025  $encoding = 0x0;
1026  }
1027  elseif ($this->_BIFF_version == 0x0500) {
1028  $length = 3 + strlen($format); // Number of bytes to follow
1029  }
1030 
1031  $cch = strlen($format); // Length of format string
1032 
1033  $header = pack("vv", $record, $length);
1034  if ($this->_BIFF_version == 0x0600) {
1035  $data = pack("vvC", $ifmt, $cch, $encoding);
1036  }
1037  elseif ($this->_BIFF_version == 0x0500) {
1038  $data = pack("vC", $ifmt, $cch);
1039  }
1040  $this->_append($header.$data.$format);
1041  }
1042 
1048  function _storeDatemode()
1049  {
1050  $record = 0x0022; // Record identifier
1051  $length = 0x0002; // Bytes to follow
1052 
1053  $f1904 = $this->_1904; // Flag for 1904 date system
1054 
1055  $header = pack("vv", $record, $length);
1056  $data = pack("v", $f1904);
1057  $this->_append($header.$data);
1058  }
1059 
1060 
1074  function _storeExterncount($cxals)
1075  {
1076  $record = 0x0016; // Record identifier
1077  $length = 0x0002; // Number of bytes to follow
1078 
1079  $header = pack("vv", $record, $length);
1080  $data = pack("v", $cxals);
1081  $this->_append($header.$data);
1082  }
1083 
1084 
1095  function _storeExternsheet($sheetname)
1096  {
1097  $record = 0x0017; // Record identifier
1098  $length = 0x02 + strlen($sheetname); // Number of bytes to follow
1099 
1100  $cch = strlen($sheetname); // Length of sheet name
1101  $rgch = 0x03; // Filename encoding
1102 
1103  $header = pack("vv", $record, $length);
1104  $data = pack("CC", $cch, $rgch);
1105  $this->_append($header.$data.$sheetname);
1106  }
1107 
1108 
1121  function _storeNameShort($index,$type,$rowmin,$rowmax,$colmin,$colmax)
1122  {
1123  $record = 0x0018; // Record identifier
1124  $length = 0x0024; // Number of bytes to follow
1125 
1126  $grbit = 0x0020; // Option flags
1127  $chKey = 0x00; // Keyboard shortcut
1128  $cch = 0x01; // Length of text name
1129  $cce = 0x0015; // Length of text definition
1130  $ixals = $index + 1; // Sheet index
1131  $itab = $ixals; // Equal to ixals
1132  $cchCustMenu = 0x00; // Length of cust menu text
1133  $cchDescription = 0x00; // Length of description text
1134  $cchHelptopic = 0x00; // Length of help topic text
1135  $cchStatustext = 0x00; // Length of status bar text
1136  $rgch = $type; // Built-in name type
1137 
1138  $unknown03 = 0x3b;
1139  $unknown04 = 0xffff-$index;
1140  $unknown05 = 0x0000;
1141  $unknown06 = 0x0000;
1142  $unknown07 = 0x1087;
1143  $unknown08 = 0x8005;
1144 
1145  $header = pack("vv", $record, $length);
1146  $data = pack("v", $grbit);
1147  $data .= pack("C", $chKey);
1148  $data .= pack("C", $cch);
1149  $data .= pack("v", $cce);
1150  $data .= pack("v", $ixals);
1151  $data .= pack("v", $itab);
1152  $data .= pack("C", $cchCustMenu);
1153  $data .= pack("C", $cchDescription);
1154  $data .= pack("C", $cchHelptopic);
1155  $data .= pack("C", $cchStatustext);
1156  $data .= pack("C", $rgch);
1157  $data .= pack("C", $unknown03);
1158  $data .= pack("v", $unknown04);
1159  $data .= pack("v", $unknown05);
1160  $data .= pack("v", $unknown06);
1161  $data .= pack("v", $unknown07);
1162  $data .= pack("v", $unknown08);
1163  $data .= pack("v", $index);
1164  $data .= pack("v", $index);
1165  $data .= pack("v", $rowmin);
1166  $data .= pack("v", $rowmax);
1167  $data .= pack("C", $colmin);
1168  $data .= pack("C", $colmax);
1169  $this->_append($header.$data);
1170  }
1171 
1172 
1187  function _storeNameLong($index,$type,$rowmin,$rowmax,$colmin,$colmax)
1188  {
1189  $record = 0x0018; // Record identifier
1190  $length = 0x003d; // Number of bytes to follow
1191  $grbit = 0x0020; // Option flags
1192  $chKey = 0x00; // Keyboard shortcut
1193  $cch = 0x01; // Length of text name
1194  $cce = 0x002e; // Length of text definition
1195  $ixals = $index + 1; // Sheet index
1196  $itab = $ixals; // Equal to ixals
1197  $cchCustMenu = 0x00; // Length of cust menu text
1198  $cchDescription = 0x00; // Length of description text
1199  $cchHelptopic = 0x00; // Length of help topic text
1200  $cchStatustext = 0x00; // Length of status bar text
1201  $rgch = $type; // Built-in name type
1202 
1203  $unknown01 = 0x29;
1204  $unknown02 = 0x002b;
1205  $unknown03 = 0x3b;
1206  $unknown04 = 0xffff-$index;
1207  $unknown05 = 0x0000;
1208  $unknown06 = 0x0000;
1209  $unknown07 = 0x1087;
1210  $unknown08 = 0x8008;
1211 
1212  $header = pack("vv", $record, $length);
1213  $data = pack("v", $grbit);
1214  $data .= pack("C", $chKey);
1215  $data .= pack("C", $cch);
1216  $data .= pack("v", $cce);
1217  $data .= pack("v", $ixals);
1218  $data .= pack("v", $itab);
1219  $data .= pack("C", $cchCustMenu);
1220  $data .= pack("C", $cchDescription);
1221  $data .= pack("C", $cchHelptopic);
1222  $data .= pack("C", $cchStatustext);
1223  $data .= pack("C", $rgch);
1224  $data .= pack("C", $unknown01);
1225  $data .= pack("v", $unknown02);
1226  // Column definition
1227  $data .= pack("C", $unknown03);
1228  $data .= pack("v", $unknown04);
1229  $data .= pack("v", $unknown05);
1230  $data .= pack("v", $unknown06);
1231  $data .= pack("v", $unknown07);
1232  $data .= pack("v", $unknown08);
1233  $data .= pack("v", $index);
1234  $data .= pack("v", $index);
1235  $data .= pack("v", 0x0000);
1236  $data .= pack("v", 0x3fff);
1237  $data .= pack("C", $colmin);
1238  $data .= pack("C", $colmax);
1239  // Row definition
1240  $data .= pack("C", $unknown03);
1241  $data .= pack("v", $unknown04);
1242  $data .= pack("v", $unknown05);
1243  $data .= pack("v", $unknown06);
1244  $data .= pack("v", $unknown07);
1245  $data .= pack("v", $unknown08);
1246  $data .= pack("v", $index);
1247  $data .= pack("v", $index);
1248  $data .= pack("v", $rowmin);
1249  $data .= pack("v", $rowmax);
1250  $data .= pack("C", 0x00);
1251  $data .= pack("C", 0xff);
1252  // End of data
1253  $data .= pack("C", 0x10);
1254  $this->_append($header.$data);
1255  }
1256 
1262  function _storeCountry()
1263  {
1264  $record = 0x008C; // Record identifier
1265  $length = 4; // Number of bytes to follow
1266 
1267  $header = pack('vv', $record, $length);
1268  /* using the same country code always for simplicity */
1269  $data = pack('vv', $this->_country_code, $this->_country_code);
1270  $this->_append($header.$data);
1271  }
1272 
1278  function _storePalette()
1279  {
1280  $aref = $this->_palette;
1281 
1282  $record = 0x0092; // Record identifier
1283  $length = 2 + 4 * count($aref); // Number of bytes to follow
1284  $ccv = count($aref); // Number of RGB values to follow
1285  $data = ''; // The RGB data
1286 
1287  // Pack the RGB data
1288  foreach($aref as $color)
1289  {
1290  foreach($color as $byte) {
1291  $data .= pack("C",$byte);
1292  }
1293  }
1294 
1295  $header = pack("vvv", $record, $length, $ccv);
1296  $this->_append($header.$data);
1297  }
1298 
1309  function _calculateSharedStringsSizes()
1310  {
1311  /* Iterate through the strings to calculate the CONTINUE block sizes.
1312  The SST blocks requires a specialised CONTINUE block, so we have to
1313  ensure that the maximum data block size is less than the limit used by
1314  _add_continue() in BIFFwriter.pm. For simplicity we use the same size
1315  for the SST and CONTINUE records:
1316  8228 : Maximum Excel97 block size
1317  -4 : Length of block header
1318  -8 : Length of additional SST header information
1319  -8 : Arbitrary number to keep within _add_continue() limit
1320  = 8208
1321  */
1322  $total_offset = 12;
1323  $continue_limit = 8208;
1324  $block_length = 0;
1325  $written = 0;
1326  $this->_block_sizes = array();
1327  $continue = 0;
1328 
1329  foreach (array_keys($this->_str_table) as $string) {
1330  $string_length = strlen($string);
1331 
1332  // Block length is the total length of the strings that will be
1333  // written out in a single SST or CONTINUE block.
1334  $block_length += $string_length;
1335 
1336  // We can write the string if it doesn't cross a CONTINUE boundary
1337  if ($block_length < $continue_limit) {
1338  $written += $string_length;
1339  $total_offset += $string_length;
1340  continue;
1341  }
1342 
1343  // Deal with the cases where the next string to be written will exceed
1344  // the CONTINUE boundary. If the string is very long it may need to be
1345  // written in more than one CONTINUE record.
1346  while ($block_length >= $continue_limit) {
1347 
1348  // We need to avoid the case where a string is continued in the first
1349  // n bytes that contain the string header information.
1350  $header_length = 3; // Min string + header size -1
1351  $space_remaining = $continue_limit - $written - $continue;
1352 
1353 
1354  /* TODO: Unicode data should only be split on char (2 byte)
1355  boundaries. Therefore, in some cases we need to reduce the
1356  amount of available
1357  */
1358 
1359  if ($space_remaining > $header_length) {
1360  // Write as much as possible of the string in the current block
1361  $written += $space_remaining;
1362 
1363  // Reduce the current block length by the amount written
1364  $block_length -= $continue_limit + $continue;
1365 
1366  // Store the max size for this block
1367  $this->_block_sizes[] = $continue_limit;
1368 
1369  // If the current string was split then the next CONTINUE block
1370  // should have the string continue flag (grbit) set unless the
1371  // split string fits exactly into the remaining space.
1372  if ($block_length > 0) {
1373  $continue = 1;
1374  }
1375  else {
1376  $continue = 0;
1377  }
1378 
1379  }
1380  else {
1381  // Store the max size for this block
1382  $this->_block_sizes[] = $written + $continue;
1383 
1384  // Not enough space to start the string in the current block
1385  $block_length -= $continue_limit - $space_remaining - $continue;
1386  $continue = 0;
1387 
1388  }
1389 
1390  // If the string (or substr) is small enough we can write it in the
1391  // new CONTINUE block. Else, go through the loop again to write it in
1392  // one or more CONTINUE blocks
1393  if ($block_length < $continue_limit) {
1394  $written = $block_length;
1395  }
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  if (!empty($this->_block_sizes)) {
1414  $total_offset += (count($this->_block_sizes) - 1) * 4; // add CONTINUE headers
1415  }
1416  return $total_offset;
1417  }
1418 
1430  /* FIXME: update _calcSheetOffsets() when updating this method */
1431  function _storeSharedStringsTable()
1432  {
1433  $record = 0x00fc; // Record identifier
1434  $length = 8 + array_sum($this->_block_sizes); // Number of bytes to follow
1435 
1436  // Write the SST block header information
1437  $header = pack("vv", $record, $length);
1438  $data = pack("VV", $this->_str_total, $this->_str_unique);
1439  $this->_append($header.$data);
1440 
1441 
1442  // Iterate through the strings to calculate the CONTINUE block sizes
1443  $continue_limit = 8208;
1444  $block_length = 0;
1445  $written = 0;
1446  $continue = 0;
1447 
1448 
1449  /* TODO: not good for performance */
1450  foreach (array_keys($this->_str_table) as $string) {
1451 
1452  $string_length = strlen($string);
1453  $encoding = 0; // assume there are no Unicode strings
1454  $split_string = 0;
1455 
1456  // Block length is the total length of the strings that will be
1457  // written out in a single SST or CONTINUE block.
1458  //
1459  $block_length += $string_length;
1460 
1461 
1462  // We can write the string if it doesn't cross a CONTINUE boundary
1463  if ($block_length < $continue_limit) {
1464  $this->_append($string);
1465  $written += $string_length;
1466  continue;
1467  }
1468 
1469  // Deal with the cases where the next string to be written will exceed
1470  // the CONTINUE boundary. If the string is very long it may need to be
1471  // written in more than one CONTINUE record.
1472  //
1473  while ($block_length >= $continue_limit) {
1474 
1475  // We need to avoid the case where a string is continued in the first
1476  // n bytes that contain the string header information.
1477  //
1478  $header_length = 3; // Min string + header size -1
1479  $space_remaining = $continue_limit - $written - $continue;
1480 
1481 
1482  // Unicode data should only be split on char (2 byte) boundaries.
1483  // Therefore, in some cases we need to reduce the amount of available
1484 
1485  if ($space_remaining > $header_length) {
1486  // Write as much as possible of the string in the current block
1487  $tmp = substr($string, 0, $space_remaining);
1488  $this->_append($tmp);
1489 
1490  // The remainder will be written in the next block(s)
1491  $string = substr($string, $space_remaining);
1492 
1493  // Reduce the current block length by the amount written
1494  $block_length -= $continue_limit - $continue;
1495 
1496  // If the current string was split then the next CONTINUE block
1497  // should have the string continue flag (grbit) set unless the
1498  // split string fits exactly into the remaining space.
1499  //
1500  if ($block_length > 0) {
1501  $continue = 1;
1502  }
1503  else {
1504  $continue = 0;
1505  }
1506  }
1507  else {
1508  // Not enough space to start the string in the current block
1509  $block_length -= $continue_limit - $space_remaining - $continue;
1510  $continue = 0;
1511  }
1512 
1513  // Write the CONTINUE block header
1514  if (!empty($this->_block_sizes)) {
1515  $record = 0x003C;
1516  $length = array_pop($this->_block_sizes);
1517 
1518  $header = pack('vv', $record, $length);
1519  if ($continue) {
1520  $header .= pack('C', $encoding);
1521  }
1522  $this->_append($header);
1523  }
1524 
1525  // If the string (or substr) is small enough we can write it in the
1526  // new CONTINUE block. Else, go through the loop again to write it in
1527  // one or more CONTINUE blocks
1528  //
1529  if ($block_length < $continue_limit) {
1530  $this->_append($string);
1531 
1532  $written = $block_length;
1533  }
1534  else {
1535  $written = 0;
1536  }
1537  }
1538  }
1539  }
1540 }
1541 ?>