[Sommaire] [Imprimer]

   1  <?php
   2  
   3  /*
   4   * Copyleft 2002 Johann Hanne
   5   *
   6   * This is free software; you can redistribute it and/or
   7   * modify it under the terms of the GNU Lesser General Public
   8   * License as published by the Free Software Foundation; either
   9   * version 2.1 of the License, or (at your option) any later version.
  10   *
  11   * This software is distributed in the hope that it will be useful,
  12   * but WITHOUT ANY WARRANTY; without even the implied warranty of
  13   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  14   * Lesser General Public License for more details.
  15   *
  16   * You should have received a copy of the GNU Lesser General Public
  17   * License along with this software; if not, write to the
  18   * Free Software Foundation, Inc., 59 Temple Place,
  19   * Suite 330, Boston, MA  02111-1307 USA
  20   */
  21  
  22  /*
  23   * This is the Spreadsheet::WriteExcel Perl package ported to PHP
  24   * Spreadsheet::WriteExcel was written by John McNamara, jmcnamara@cpan.org
  25   */
  26  
  27  require_once  "class.writeexcel_biffwriter.inc.php";
  28  require_once  "class.writeexcel_format.inc.php";
  29  require_once  "class.writeexcel_formula.inc.php";
  30  require_once  "class.writeexcel_olewriter.inc.php";
  31  
  32  class writeexcel_workbook extends writeexcel_biffwriter {
  33  
  34      var $_filename;
  35      var $_tmpfilename;
  36      var $_parser;
  37      var $_tempdir;
  38      var $_1904;
  39      var $_activesheet;
  40      var $_firstsheet;
  41      var $_selected;
  42      var $_xf_index;
  43      var $_fileclosed;
  44      var $_biffsize;
  45      var $_sheetname;
  46      var $_tmp_format;
  47      var $_url_format;
  48      var $_worksheets;
  49      var $_sheetnames;
  50      var $_formats;
  51      var $_palette;
  52  
  53  ###############################################################################
  54  #
  55  # new()
  56  #
  57  # Constructor. Creates a new Workbook object from a BIFFwriter object.
  58  #
  59  function writeexcel_workbook($filename) {
  60  
  61      $this->writeexcel_biffwriter();
  62  
  63      $tmp_format  = new writeexcel_format();
  64      $byte_order  = $this->_byte_order;
  65      $parser      = new writeexcel_formula($byte_order);
  66  
  67      $this->_filename          = $filename;
  68      $this->_parser            = $parser;
  69  //?    $this->_tempdir           = undef;
  70      $this->_1904              = 0;
  71      $this->_activesheet       = 0;
  72      $this->_firstsheet        = 0;
  73      $this->_selected          = 0;
  74      $this->_xf_index          = 16; # 15 style XF's and 1 cell XF.
  75      $this->_fileclosed        = 0;
  76      $this->_biffsize          = 0;
  77      $this->_sheetname         = "Sheet";
  78      $this->_tmp_format        = $tmp_format;
  79      $this->_url_format        = false;
  80      $this->_worksheets        = array();
  81      $this->_sheetnames        = array();
  82      $this->_formats           = array();
  83      $this->_palette           = array();
  84  
  85      # Add the default format for hyperlinks
  86      $this->_url_format =& $this->addformat(array('color' => 'blue', 'underline' => 1));
  87  
  88      # Check for a filename
  89      if ($this->_filename == '') {
  90  //todo: print error
  91          return;
  92      }
  93  
  94      # Try to open the named file and see if it throws any errors.
  95      # If the filename is a reference it is assumed that it is a valid
  96      # filehandle and ignore
  97      #
  98  //todo
  99  
 100      # Set colour palette.
 101      $this->set_palette_xl97();
 102  }
 103  
 104  ###############################################################################
 105  #
 106  # close()
 107  #
 108  # Calls finalization methods and explicitly close the OLEwriter file
 109  # handle.
 110  #
 111  function close() {
 112      # Prevent close() from being called twice.
 113      if ($this->_fileclosed) {
 114          return;
 115      }
 116  
 117      $this->_store_workbook();
 118      $this->_fileclosed = 1;
 119  }
 120  
 121  //PHPport: method DESTROY deleted
 122  
 123  ###############################################################################
 124  #
 125  # sheets()
 126  #
 127  # An accessor for the _worksheets[] array
 128  #
 129  # Returns: a list of the worksheet objects in a workbook
 130  #
 131  function &sheets() {
 132      return $this->_worksheets;
 133  }
 134  
 135  //PHPport: method worksheets deleted:
 136  # This method is now deprecated. Use the sheets() method instead.
 137  
 138  ###############################################################################
 139  #
 140  # addworksheet($name)
 141  #
 142  # Add a new worksheet to the Excel workbook.
 143  # TODO: Add accessor for $self->{_sheetname} for international Excel versions.
 144  #
 145  # Returns: reference to a worksheet object
 146  #
 147  function &addworksheet($name="") {
 148  
 149      # Check that sheetname is <= 31 chars (Excel limit).
 150      if (strlen($name) > 31) {
 151          trigger_error("Sheetname $name must be <= 31 chars", E_USER_ERROR);
 152      }
 153  
 154      $index     = sizeof($this->_worksheets);
 155      $sheetname = $this->_sheetname;
 156  
 157      if ($name == "") {
 158          $name = $sheetname . ($index+1);
 159      }
 160  
 161      # Check that the worksheet name doesn't already exist: a fatal Excel error.
 162      foreach ($this->_worksheets as $tmp) {
 163          if ($name == $tmp->get_name()) {
 164              trigger_error("Worksheet '$name' already exists", E_USER_ERROR);
 165          }
 166      }
 167  
 168      $worksheet =& new writeexcel_worksheet($name, $index, $this->_activesheet,
 169                                            $this->_firstsheet,
 170                                            $this->_url_format, $this->_parser,
 171                                            $this->_tempdir);
 172  
 173      $this->_worksheets[$index] = &$worksheet;    # Store ref for iterator
 174      $this->_sheetnames[$index] = $name;         # Store EXTERNSHEET names
 175      $this->_parser->set_ext_sheet($name, $index); # Store names in Formula.pm
 176      return $worksheet;
 177  }
 178  
 179  ###############################################################################
 180  #
 181  # addformat(%properties)
 182  #
 183  # Add a new format to the Excel workbook. This adds an XF record and
 184  # a FONT record. Also, pass any properties to the Format::new().
 185  #
 186  function &addformat($para=false) {
 187      if($para===false) {
 188          $format =& new writeexcel_format($this->_xf_index);
 189      } else {
 190          $format =& new writeexcel_format($this->_xf_index, $para);
 191      }
 192  
 193      $this->_xf_index += 1;
 194      # Store format reference
 195      $this->_formats[]=&$format;
 196  
 197      return $format;
 198  }
 199  
 200  ###############################################################################
 201  #
 202  # set_1904()
 203  #
 204  # Set the date system: 0 = 1900 (the default), 1 = 1904
 205  #
 206  function set_1904($_1904) {
 207      $this->_1904 = $_1904;
 208  }
 209  
 210  ###############################################################################
 211  #
 212  # get_1904()
 213  #
 214  # Return the date system: 0 = 1900, 1 = 1904
 215  #
 216  function get_1904() {
 217      return $this->_1904;
 218  }
 219  
 220  ###############################################################################
 221  #
 222  # set_custom_color()
 223  #
 224  # Change the RGB components of the elements in the colour palette.
 225  #
 226  function set_custom_color($index, $red, $green, $blue) {
 227  // todo
 228  /*
 229      # Match a HTML #xxyyzz style parameter
 230      if (defined $_[1] and $_[1] =~ /^#(\w\w)(\w\w)(\w\w)/ ) {
 231          @_ = ($_[0], hex $1, hex $2, hex $3);
 232      }
 233  */
 234  
 235      $aref    = &$this->_palette;
 236  
 237      # Check that the colour index is the right range
 238      if ($index < 8 or $index > 64) {
 239  //todo        carp "Color index $index outside range: 8 <= index <= 64";
 240          return;
 241      }
 242  
 243      # Check that the colour components are in the right range
 244      if ( ($red   < 0 || $red   > 255) ||
 245           ($green < 0 || $green > 255) ||
 246           ($blue  < 0 || $blue  > 255) )
 247      {
 248  //todo        carp "Color component outside range: 0 <= color <= 255";
 249          return;
 250      }
 251  
 252      $index -=8; # Adjust colour index (wingless dragonfly)
 253  
 254      # Set the RGB value
 255      $aref[$index] = array($red, $green, $blue, 0);
 256  
 257      return $index +8;
 258  }
 259  
 260  ###############################################################################
 261  #
 262  # set_palette_xl97()
 263  #
 264  # Sets the colour palette to the Excel 97+ default.
 265  #
 266  function set_palette_xl97() {
 267      $this->_palette = array(
 268                              array(0x00, 0x00, 0x00, 0x00),   # 8
 269                              array(0xff, 0xff, 0xff, 0x00),   # 9
 270                              array(0xff, 0x00, 0x00, 0x00),   # 10
 271                              array(0x00, 0xff, 0x00, 0x00),   # 11
 272                              array(0x00, 0x00, 0xff, 0x00),   # 12
 273                              array(0xff, 0xff, 0x00, 0x00),   # 13
 274                              array(0xff, 0x00, 0xff, 0x00),   # 14
 275                              array(0x00, 0xff, 0xff, 0x00),   # 15
 276                              array(0x80, 0x00, 0x00, 0x00),   # 16
 277                              array(0x00, 0x80, 0x00, 0x00),   # 17
 278                              array(0x00, 0x00, 0x80, 0x00),   # 18
 279                              array(0x80, 0x80, 0x00, 0x00),   # 19
 280                              array(0x80, 0x00, 0x80, 0x00),   # 20
 281                              array(0x00, 0x80, 0x80, 0x00),   # 21
 282                              array(0xc0, 0xc0, 0xc0, 0x00),   # 22
 283                              array(0x80, 0x80, 0x80, 0x00),   # 23
 284                              array(0x99, 0x99, 0xff, 0x00),   # 24
 285                              array(0x99, 0x33, 0x66, 0x00),   # 25
 286                              array(0xff, 0xff, 0xcc, 0x00),   # 26
 287                              array(0xcc, 0xff, 0xff, 0x00),   # 27
 288                              array(0x66, 0x00, 0x66, 0x00),   # 28
 289                              array(0xff, 0x80, 0x80, 0x00),   # 29
 290                              array(0x00, 0x66, 0xcc, 0x00),   # 30
 291                              array(0xcc, 0xcc, 0xff, 0x00),   # 31
 292                              array(0x00, 0x00, 0x80, 0x00),   # 32
 293                              array(0xff, 0x00, 0xff, 0x00),   # 33
 294                              array(0xff, 0xff, 0x00, 0x00),   # 34
 295                              array(0x00, 0xff, 0xff, 0x00),   # 35
 296                              array(0x80, 0x00, 0x80, 0x00),   # 36
 297                              array(0x80, 0x00, 0x00, 0x00),   # 37
 298                              array(0x00, 0x80, 0x80, 0x00),   # 38
 299                              array(0x00, 0x00, 0xff, 0x00),   # 39
 300                              array(0x00, 0xcc, 0xff, 0x00),   # 40
 301                              array(0xcc, 0xff, 0xff, 0x00),   # 41
 302                              array(0xcc, 0xff, 0xcc, 0x00),   # 42
 303                              array(0xff, 0xff, 0x99, 0x00),   # 43
 304                              array(0x99, 0xcc, 0xff, 0x00),   # 44
 305                              array(0xff, 0x99, 0xcc, 0x00),   # 45
 306                              array(0xcc, 0x99, 0xff, 0x00),   # 46
 307                              array(0xff, 0xcc, 0x99, 0x00),   # 47
 308                              array(0x33, 0x66, 0xff, 0x00),   # 48
 309                              array(0x33, 0xcc, 0xcc, 0x00),   # 49
 310                              array(0x99, 0xcc, 0x00, 0x00),   # 50
 311                              array(0xff, 0xcc, 0x00, 0x00),   # 51
 312                              array(0xff, 0x99, 0x00, 0x00),   # 52
 313                              array(0xff, 0x66, 0x00, 0x00),   # 53
 314                              array(0x66, 0x66, 0x99, 0x00),   # 54
 315                              array(0x96, 0x96, 0x96, 0x00),   # 55
 316                              array(0x00, 0x33, 0x66, 0x00),   # 56
 317                              array(0x33, 0x99, 0x66, 0x00),   # 57
 318                              array(0x00, 0x33, 0x00, 0x00),   # 58
 319                              array(0x33, 0x33, 0x00, 0x00),   # 59
 320                              array(0x99, 0x33, 0x00, 0x00),   # 60
 321                              array(0x99, 0x33, 0x66, 0x00),   # 61
 322                              array(0x33, 0x33, 0x99, 0x00),   # 62
 323                              array(0x33, 0x33, 0x33, 0x00),   # 63
 324                          );
 325  
 326      return 0;
 327  }
 328  
 329  ###############################################################################
 330  #
 331  # set_palette_xl5()
 332  #
 333  # Sets the colour palette to the Excel 5 default.
 334  #
 335  function set_palette_xl5() {
 336      $this->_palette = array(
 337                              array(0x00, 0x00, 0x00, 0x00),   # 8
 338                              array(0xff, 0xff, 0xff, 0x00),   # 9
 339                              array(0xff, 0x00, 0x00, 0x00),   # 10
 340                              array(0x00, 0xff, 0x00, 0x00),   # 11
 341                              array(0x00, 0x00, 0xff, 0x00),   # 12
 342                              array(0xff, 0xff, 0x00, 0x00),   # 13
 343                              array(0xff, 0x00, 0xff, 0x00),   # 14
 344                              array(0x00, 0xff, 0xff, 0x00),   # 15
 345                              array(0x80, 0x00, 0x00, 0x00),   # 16
 346                              array(0x00, 0x80, 0x00, 0x00),   # 17
 347                              array(0x00, 0x00, 0x80, 0x00),   # 18
 348                              array(0x80, 0x80, 0x00, 0x00),   # 19
 349                              array(0x80, 0x00, 0x80, 0x00),   # 20
 350                              array(0x00, 0x80, 0x80, 0x00),   # 21
 351                              array(0xc0, 0xc0, 0xc0, 0x00),   # 22
 352                              array(0x80, 0x80, 0x80, 0x00),   # 23
 353                              array(0x80, 0x80, 0xff, 0x00),   # 24
 354                              array(0x80, 0x20, 0x60, 0x00),   # 25
 355                              array(0xff, 0xff, 0xc0, 0x00),   # 26
 356                              array(0xa0, 0xe0, 0xe0, 0x00),   # 27
 357                              array(0x60, 0x00, 0x80, 0x00),   # 28
 358                              array(0xff, 0x80, 0x80, 0x00),   # 29
 359                              array(0x00, 0x80, 0xc0, 0x00),   # 30
 360                              array(0xc0, 0xc0, 0xff, 0x00),   # 31
 361                              array(0x00, 0x00, 0x80, 0x00),   # 32
 362                              array(0xff, 0x00, 0xff, 0x00),   # 33
 363                              array(0xff, 0xff, 0x00, 0x00),   # 34
 364                              array(0x00, 0xff, 0xff, 0x00),   # 35
 365                              array(0x80, 0x00, 0x80, 0x00),   # 36
 366                              array(0x80, 0x00, 0x00, 0x00),   # 37
 367                              array(0x00, 0x80, 0x80, 0x00),   # 38
 368                              array(0x00, 0x00, 0xff, 0x00),   # 39
 369                              array(0x00, 0xcf, 0xff, 0x00),   # 40
 370                              array(0x69, 0xff, 0xff, 0x00),   # 41
 371                              array(0xe0, 0xff, 0xe0, 0x00),   # 42
 372                              array(0xff, 0xff, 0x80, 0x00),   # 43
 373                              array(0xa6, 0xca, 0xf0, 0x00),   # 44
 374                              array(0xdd, 0x9c, 0xb3, 0x00),   # 45
 375                              array(0xb3, 0x8f, 0xee, 0x00),   # 46
 376                              array(0xe3, 0xe3, 0xe3, 0x00),   # 47
 377                              array(0x2a, 0x6f, 0xf9, 0x00),   # 48
 378                              array(0x3f, 0xb8, 0xcd, 0x00),   # 49
 379                              array(0x48, 0x84, 0x36, 0x00),   # 50
 380                              array(0x95, 0x8c, 0x41, 0x00),   # 51
 381                              array(0x8e, 0x5e, 0x42, 0x00),   # 52
 382                              array(0xa0, 0x62, 0x7a, 0x00),   # 53
 383                              array(0x62, 0x4f, 0xac, 0x00),   # 54
 384                              array(0x96, 0x96, 0x96, 0x00),   # 55
 385                              array(0x1d, 0x2f, 0xbe, 0x00),   # 56
 386                              array(0x28, 0x66, 0x76, 0x00),   # 57
 387                              array(0x00, 0x45, 0x00, 0x00),   # 58
 388                              array(0x45, 0x3e, 0x01, 0x00),   # 59
 389                              array(0x6a, 0x28, 0x13, 0x00),   # 60
 390                              array(0x85, 0x39, 0x6a, 0x00),   # 61
 391                              array(0x4a, 0x32, 0x85, 0x00),   # 62
 392                              array(0x42, 0x42, 0x42, 0x00),   # 63
 393                          );
 394  
 395      return 0;
 396  }
 397  
 398  ###############################################################################
 399  #
 400  # set_tempdir()
 401  #
 402  # Change the default temp directory used by _initialize() in Worksheet.pm.
 403  #
 404  function set_tempdir($tempdir) {
 405  //todo
 406  /*
 407      croak "$_[0] is not a valid directory"                 unless -d $_[0];
 408      croak "set_tempdir must be called before addworksheet" if $self->sheets();
 409  */
 410  
 411      $this->_tempdir = $tempdir;
 412  }
 413  
 414  ###############################################################################
 415  #
 416  # _store_workbook()
 417  #
 418  # Assemble worksheets into a workbook and send the BIFF data to an OLE
 419  # storage.
 420  #
 421  function _store_workbook() {
 422  
 423      # Ensure that at least one worksheet has been selected.
 424      if ($this->_activesheet == 0) {
 425          $this->_worksheets[0]->_selected = 1;
 426      }
 427  
 428      # Calculate the number of selected worksheet tabs and call the finalization
 429      # methods for each worksheet
 430      for ($c=0;$c<sizeof($this->_worksheets);$c++) {
 431          $sheet=&$this->_worksheets[$c];
 432          if ($sheet->_selected) {
 433              $this->_selected++;
 434          }
 435          $sheet->_close($this->_sheetnames);
 436      }
 437  
 438      # Add Workbook globals
 439      $this->_store_bof(0x0005);
 440  
 441      $this->_store_externs();    # For print area and repeat rows
 442  
 443      $this->_store_names();      # For print area and repeat rows
 444  
 445      $this->_store_window1();
 446  
 447      $this->_store_1904();
 448  
 449      $this->_store_all_fonts();
 450  
 451      $this->_store_all_num_formats();
 452  
 453      $this->_store_all_xfs();
 454  
 455      $this->_store_all_styles();
 456  
 457      $this->_store_palette();
 458  
 459      $this->_calc_sheet_offsets();
 460  
 461      # Add BOUNDSHEET records
 462      for ($c=0;$c<sizeof($this->_worksheets);$c++) {
 463         $sheet=&$this->_worksheets[$c];
 464          $this->_store_boundsheet($sheet->_name, $sheet->_offset);
 465      }
 466  
 467      # End Workbook globals
 468      $this->_store_eof();
 469  
 470      # Store the workbook in an OLE container
 471      $this->_store_OLE_file();
 472  }
 473  
 474  ###############################################################################
 475  #
 476  # _store_OLE_file()
 477  #
 478  # Store the workbook in an OLE container if the total size of the workbook data
 479  # is less than ~ 7MB.
 480  #
 481  function _store_OLE_file() {
 482  ## ABR
 483      if ($this->_tmpfilename != '') {
 484          $OLE  = new writeexcel_olewriter('/tmp/'.$this->_tmpfilename);
 485          $OLE->_OLEtmpfilename = '/tmp/'.$this->_tmpfilename;
 486      } else {
 487          $OLE  = new writeexcel_olewriter($this->_filename);
 488          $OLE->_OLEtmpfilename = '';
 489      };
 490  ## END ABR
 491                                  
 492      # Write Worksheet data if data <~ 7MB
 493      if ($OLE->set_size($this->_biffsize)) {
 494          $OLE->write_header();
 495          $OLE->write($this->_data);
 496  
 497          for ($c=0;$c<sizeof($this->_worksheets);$c++) {
 498              $sheet=&$this->_worksheets[$c];
 499              while ($tmp = $sheet->get_data()) {
 500                  $OLE->write($tmp);
 501              }
 502          }
 503      }
 504  
 505      $OLE->close();
 506  }
 507  
 508  ###############################################################################
 509  #
 510  # _calc_sheet_offsets()
 511  #
 512  # Calculate offsets for Worksheet BOF records.
 513  #
 514  function _calc_sheet_offsets() {
 515  
 516      $BOF     = 11;
 517      $EOF     = 4;
 518      $offset  = $this->_datasize;
 519  
 520      foreach ($this->_worksheets as $sheet) {
 521          $offset += $BOF + strlen($sheet->_name);
 522      }
 523  
 524      $offset += $EOF;
 525  
 526      for ($c=0;$c<sizeof($this->_worksheets);$c++) {
 527          $sheet=&$this->_worksheets[$c];
 528          $sheet->_offset = $offset;
 529          $offset += $sheet->_datasize;
 530      }
 531  
 532      $this->_biffsize = $offset;
 533  }
 534  
 535  ###############################################################################
 536  #
 537  # _store_all_fonts()
 538  #
 539  # Store the Excel FONT records.
 540  #
 541  function _store_all_fonts() {
 542      # _tmp_format is added by new(). We use this to write the default XF's
 543      $format = $this->_tmp_format;
 544      $font   = $format->get_font();
 545  
 546      # Note: Fonts are 0-indexed. According to the SDK there is no index 4,
 547      # so the following fonts are 0, 1, 2, 3, 5
 548      #
 549      for ($c=0;$c<5;$c++) {
 550          $this->_append($font);
 551      }
 552  
 553      # Iterate through the XF objects and write a FONT record if it isn't the
 554      # same as the default FONT and if it hasn't already been used.
 555      #
 556      $index = 6;                  # The first user defined FONT
 557  
 558      $key = $format->get_font_key(); # The default font from _tmp_format
 559      $fonts[$key] = 0;               # Index of the default font
 560  
 561      for ($c=0;$c<sizeof($this->_formats);$c++) {
 562          $format=&$this->_formats[$c];
 563  
 564          $key = $format->get_font_key();
 565  
 566          if (isset($fonts[$key])) {
 567              # FONT has already been used
 568              $format->_font_index = $fonts[$key];
 569          } else {
 570              # Add a new FONT record
 571              $fonts[$key]           = $index;
 572              $format->_font_index = $index;
 573              $index++;
 574              $font = $format->get_font();
 575              $this->_append($font);
 576          }
 577      }
 578  }
 579  
 580  ###############################################################################
 581  #
 582  # _store_all_num_formats()
 583  #
 584  # Store user defined numerical formats i.e. FORMAT records
 585  #
 586  function _store_all_num_formats() {
 587  
 588      # Leaning num_format syndrome
 589      $num_formats_list=array();
 590      $index = 164;
 591  
 592      # Iterate through the XF objects and write a FORMAT record if it isn't a
 593      # built-in format type and if the FORMAT string hasn't already been used.
 594      #
 595  
 596      for ($c=0;$c<sizeof($this->_formats);$c++) {
 597          $format=&$this->_formats[$c];
 598  
 599          $num_format = $format->_num_format;
 600  
 601          # Check if $num_format is an index to a built-in format.
 602          # Also check for a string of zeros, which is a valid format string
 603          # but would evaluate to zero.
 604          #
 605          if (!preg_match('/^0+\d/', $num_format)) {
 606              if (preg_match('/^\d+$/', $num_format)) {
 607                  # built-in
 608                  continue;
 609              }
 610          }
 611  
 612          if (isset($num_formats[$num_format])) {
 613              # FORMAT has already been used
 614              $format->_num_format = $num_formats[$num_format];
 615          } else {
 616              # Add a new FORMAT
 617              $num_formats[$num_format] = $index;
 618              $format->_num_format    = $index;
 619              array_push($num_formats_list, $num_format);
 620              $index++;
 621          }
 622      }
 623  
 624      # Write the new FORMAT records starting from 0xA4
 625      $index = 164;
 626      foreach ($num_formats_list as $num_format) {
 627          $this->_store_num_format($num_format, $index);
 628          $index++;
 629      }
 630  }
 631  
 632  ###############################################################################
 633  #
 634  # _store_all_xfs()
 635  #
 636  # Write all XF records.
 637  #
 638  function _store_all_xfs() {
 639      # _tmp_format is added by new(). We use this to write the default XF's
 640      # The default font index is 0
 641      #
 642      $format = $this->_tmp_format;
 643      $xf;
 644  
 645      for ($c=0;$c<15;$c++) {
 646          $xf = $format->get_xf('style'); # Style XF
 647          $this->_append($xf);
 648      }
 649  
 650      $xf = $format->get_xf('cell');      # Cell XF
 651      $this->_append($xf);
 652  
 653      # User defined XFs
 654      foreach ($this->_formats as $format) {
 655          $xf = $format->get_xf('cell');
 656          $this->_append($xf);
 657      }
 658  }
 659  
 660  ###############################################################################
 661  #
 662  # _store_all_styles()
 663  #
 664  # Write all STYLE records.
 665  #
 666  function _store_all_styles() {
 667      $this->_store_style();
 668  }
 669  
 670  ###############################################################################
 671  #
 672  # _store_externs()
 673  #
 674  # Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for
 675  # the NAME records.
 676  #
 677  function _store_externs() {
 678  
 679      # Create EXTERNCOUNT with number of worksheets
 680      $this->_store_externcount(sizeof($this->_worksheets));
 681  
 682      # Create EXTERNSHEET for each worksheet
 683      foreach ($this->_sheetnames as $sheetname) {
 684          $this->_store_externsheet($sheetname);
 685      }
 686  }
 687  
 688  ###############################################################################
 689  #
 690  # _store_names()
 691  #
 692  # Write the NAME record to define the print area and the repeat rows and cols.
 693  #
 694  function _store_names() {
 695  
 696      # Create the print area NAME records
 697      foreach ($this->_worksheets as $worksheet) {
 698          # Write a Name record if the print area has been defined
 699          if ($worksheet->_print_rowmin!==false) {
 700              $this->_store_name_short(
 701                  $worksheet->_index,
 702                  0x06, # NAME type
 703                  $worksheet->_print_rowmin,
 704                  $worksheet->_print_rowmax,
 705                  $worksheet->_print_colmin,
 706                  $worksheet->_print_colmax
 707              );
 708          }
 709      }
 710  
 711      # Create the print title NAME records
 712      foreach ($this->_worksheets as $worksheet) {
 713  
 714          $rowmin = $worksheet->_title_rowmin;
 715          $rowmax = $worksheet->_title_rowmax;
 716          $colmin = $worksheet->_title_colmin;
 717          $colmax = $worksheet->_title_colmax;
 718  
 719          # Determine if row + col, row, col or nothing has been defined
 720          # and write the appropriate record
 721          #
 722          if ($rowmin!==false && $colmin!==false) {
 723              # Row and column titles have been defined.
 724              # Row title has been defined.
 725              $this->_store_name_long(
 726                  $worksheet->_index,
 727                  0x07, # NAME type
 728                  $rowmin,
 729                  $rowmax,
 730                  $colmin,
 731                  $colmax
 732             );
 733          } elseif ($rowmin!==false) {
 734              # Row title has been defined.
 735              $this->_store_name_short(
 736                  $worksheet->_index,
 737                  0x07, # NAME type
 738                  $rowmin,
 739                  $rowmax,
 740                  0x00,
 741                  0xff
 742              );
 743          } elseif ($colmin!==false) {
 744              # Column title has been defined.
 745              $this->_store_name_short(
 746                  $worksheet->_index,
 747                  0x07, # NAME type
 748                  0x0000,
 749                  0x3fff,
 750                  $colmin,
 751                  $colmax
 752              );
 753          } else {
 754              # Print title hasn't been defined.
 755          }
 756      }
 757  }
 758  
 759  ###############################################################################
 760  ###############################################################################
 761  #
 762  # BIFF RECORDS
 763  #
 764  
 765  ###############################################################################
 766  #
 767  # _store_window1()
 768  #
 769  # Write Excel BIFF WINDOW1 record.
 770  #
 771  function _store_window1() {
 772  
 773      $record    = 0x003D;                 # Record identifier
 774      $length    = 0x0012;                 # Number of bytes to follow
 775  
 776      $xWn       = 0x0000;                 # Horizontal position of window
 777      $yWn       = 0x0000;                 # Vertical position of window
 778      $dxWn      = 0x25BC;                 # Width of window
 779      $dyWn      = 0x1572;                 # Height of window
 780  
 781      $grbit     = 0x0038;                 # Option flags
 782      $ctabsel   = $this->_selected;     # Number of workbook tabs selected
 783      $wTabRatio = 0x0258;                 # Tab to scrollbar ratio
 784  
 785      $itabFirst = $this->_firstsheet;   # 1st displayed worksheet
 786      $itabCur   = $this->_activesheet;  # Active worksheet
 787  
 788      $header    = pack("vv",        $record, $length);
 789      $data      = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,
 790                                     $grbit,
 791                                     $itabCur, $itabFirst,
 792                                     $ctabsel, $wTabRatio);
 793  
 794      $this->_append($header . $data);
 795  }
 796  
 797  ###############################################################################
 798  #
 799  # _store_boundsheet()
 800  #
 801  # Writes Excel BIFF BOUNDSHEET record.
 802  #
 803  function _store_boundsheet($sheetname, $offset) {
 804      $record    = 0x0085;               # Record identifier
 805      $length    = 0x07 + strlen($sheetname); # Number of bytes to follow
 806  
 807      //$sheetname = $_[0];                # Worksheet name
 808      //$offset    = $_[1];                # Location of worksheet BOF
 809      $grbit     = 0x0000;               # Sheet identifier
 810      $cch       = strlen($sheetname);   # Length of sheet name
 811  
 812      $header    = pack("vv",  $record, $length);
 813      $data      = pack("VvC", $offset, $grbit, $cch);
 814  
 815      $this->_append($header . $data . $sheetname);
 816  }
 817  
 818  ###############################################################################
 819  #
 820  # _store_style()
 821  #
 822  # Write Excel BIFF STYLE records.
 823  #
 824  function _store_style() {
 825      $record    = 0x0293; # Record identifier
 826      $length    = 0x0004; # Bytes to follow
 827  
 828      $ixfe      = 0x8000; # Index to style XF
 829      $BuiltIn   = 0x00;   # Built-in style
 830      $iLevel    = 0xff;   # Outline style level
 831  
 832      $header    = pack("vv",  $record, $length);
 833      $data      = pack("vCC", $ixfe, $BuiltIn, $iLevel);
 834  
 835      $this->_append($header . $data);
 836  }
 837  
 838  ###############################################################################
 839  #
 840  # _store_num_format()
 841  #
 842  # Writes Excel FORMAT record for non "built-in" numerical formats.
 843  #
 844  function _store_num_format($num_format, $index) {
 845      $record    = 0x041E;                 # Record identifier
 846      $length    = 0x03 + strlen($num_format);   # Number of bytes to follow
 847  
 848      $format    = $num_format;                  # Custom format string
 849      $ifmt      = $index;                  # Format index code
 850      $cch       = strlen($format);        # Length of format string
 851  
 852      $header    = pack("vv", $record, $length);
 853      $data      = pack("vC", $ifmt, $cch);
 854  
 855      $this->_append($header . $data . $format);
 856  }
 857  
 858  ###############################################################################
 859  #
 860  # _store_1904()
 861  #
 862  # Write Excel 1904 record to indicate the date system in use.
 863  #
 864  function _store_1904() {
 865      $record    = 0x0022;         # Record identifier
 866      $length    = 0x0002;         # Bytes to follow
 867  
 868      $f1904     = $this->_1904; # Flag for 1904 date system
 869  
 870      $header    = pack("vv",  $record, $length);
 871      $data      = pack("v", $f1904);
 872  
 873      $this->_append($header . $data);
 874  }
 875  
 876  ###############################################################################
 877  #
 878  # _store_externcount($count)
 879  #
 880  # Write BIFF record EXTERNCOUNT to indicate the number of external sheet
 881  # references in the workbook.
 882  #
 883  # Excel only stores references to external sheets that are used in NAME.
 884  # The workbook NAME record is required to define the print area and the repeat
 885  # rows and columns.
 886  #
 887  # A similar method is used in Worksheet.pm for a slightly different purpose.
 888  #
 889  function _store_externcount($par0) {
 890      $record   = 0x0016;          # Record identifier
 891      $length   = 0x0002;          # Number of bytes to follow
 892  
 893      $cxals    = $par0;           # Number of external references
 894  
 895      $header   = pack("vv", $record, $length);
 896      $data     = pack("v",  $cxals);
 897  
 898      $this->_append($header . $data);
 899  }
 900  
 901  ###############################################################################
 902  #
 903  # _store_externsheet($sheetname)
 904  #
 905  #
 906  # Writes the Excel BIFF EXTERNSHEET record. These references are used by
 907  # formulas. NAME record is required to define the print area and the repeat
 908  # rows and columns.
 909  #
 910  # A similar method is used in Worksheet.pm for a slightly different purpose.
 911  #
 912  function _store_externsheet($par0) {
 913      $record      = 0x0017;               # Record identifier
 914      $length      = 0x02 + strlen($par0); # Number of bytes to follow
 915  
 916      $sheetname   = $par0;                # Worksheet name
 917      $cch         = strlen($sheetname);   # Length of sheet name
 918      $rgch        = 0x03;                 # Filename encoding
 919  
 920      $header      = pack("vv",  $record, $length);
 921      $data        = pack("CC", $cch, $rgch);
 922  
 923      $this->_append($header . $data . $sheetname);
 924  }
 925  
 926  ###############################################################################
 927  #
 928  # _store_name_short()
 929  #
 930  #
 931  # Store the NAME record in the short format that is used for storing the print
 932  # area, repeat rows only and repeat columns only.
 933  #
 934  function _store_name_short($par0, $par1, $par2, $par3, $par4, $par5) {
 935      $record          = 0x0018;       # Record identifier
 936      $length          = 0x0024;       # Number of bytes to follow
 937  
 938      $index           = $par0;        # Sheet index
 939      $type            = $par1;
 940  
 941      $grbit           = 0x0020;       # Option flags
 942      $chKey           = 0x00;         # Keyboard shortcut
 943      $cch             = 0x01;         # Length of text name
 944      $cce             = 0x0015;       # Length of text definition
 945      $ixals           = $index +1;    # Sheet index
 946      $itab            = $ixals;       # Equal to ixals
 947      $cchCustMenu     = 0x00;         # Length of cust menu text
 948      $cchDescription  = 0x00;         # Length of description text
 949      $cchHelptopic    = 0x00;         # Length of help topic text
 950      $cchStatustext   = 0x00;         # Length of status bar text
 951      $rgch            = $type;        # Built-in name type
 952  
 953      $unknown03       = 0x3b;
 954      $unknown04       = 0xffff-$index;
 955      $unknown05       = 0x0000;
 956      $unknown06       = 0x0000;
 957      $unknown07       = 0x1087;
 958      $unknown08       = 0x8005;
 959  
 960      $rowmin          = $par2;        # Start row
 961      $rowmax          = $par3;        # End row
 962      $colmin          = $par4;        # Start column
 963      $colmax          = $par5;        # end column
 964  
 965      $header          = pack("vv",  $record, $length);
 966      $data            = pack("v", $grbit);
 967      $data              .= pack("C", $chKey);
 968      $data              .= pack("C", $cch);
 969      $data              .= pack("v", $cce);
 970      $data              .= pack("v", $ixals);
 971      $data              .= pack("v", $itab);
 972      $data              .= pack("C", $cchCustMenu);
 973      $data              .= pack("C", $cchDescription);
 974      $data              .= pack("C", $cchHelptopic);
 975      $data              .= pack("C", $cchStatustext);
 976      $data              .= pack("C", $rgch);
 977      $data              .= pack("C", $unknown03);
 978      $data              .= pack("v", $unknown04);
 979      $data              .= pack("v", $unknown05);
 980      $data              .= pack("v", $unknown06);
 981      $data              .= pack("v", $unknown07);
 982      $data              .= pack("v", $unknown08);
 983      $data              .= pack("v", $index);
 984      $data              .= pack("v", $index);
 985      $data              .= pack("v", $rowmin);
 986      $data              .= pack("v", $rowmax);
 987      $data              .= pack("C", $colmin);
 988      $data              .= pack("C", $colmax);
 989  
 990      $this->_append($header . $data);
 991  }
 992  
 993  ###############################################################################
 994  #
 995  # _store_name_long()
 996  #
 997  #
 998  # Store the NAME record in the long format that is used for storing the repeat
 999  # rows and columns when both are specified. This share a lot of code with
1000  # _store_name_short() but we use a separate method to keep the code clean.
1001  # Code abstraction for reuse can be carried too far, and I should know. ;-)
1002  #
1003  function _store_name_long($par0, $par1, $par2, $par3, $par4, $par5) {
1004      $record          = 0x0018;       # Record identifier
1005      $length          = 0x003d;       # Number of bytes to follow
1006  
1007      $index           = $par0;        # Sheet index
1008      $type            = $par1;
1009  
1010      $grbit           = 0x0020;       # Option flags
1011      $chKey           = 0x00;         # Keyboard shortcut
1012      $cch             = 0x01;         # Length of text name
1013      $cce             = 0x002e;       # Length of text definition
1014      $ixals           = $index +1;    # Sheet index
1015      $itab            = $ixals;       # Equal to ixals
1016      $cchCustMenu     = 0x00;         # Length of cust menu text
1017      $cchDescription  = 0x00;         # Length of description text
1018      $cchHelptopic    = 0x00;         # Length of help topic text
1019      $cchStatustext   = 0x00;         # Length of status bar text
1020      $rgch            = $type;        # Built-in name type
1021  
1022      $unknown01       = 0x29;
1023      $unknown02       = 0x002b;
1024      $unknown03       = 0x3b;
1025      $unknown04       = 0xffff-$index;
1026      $unknown05       = 0x0000;
1027      $unknown06       = 0x0000;
1028      $unknown07       = 0x1087;
1029      $unknown08       = 0x8008;
1030  
1031      $rowmin          = $par2;        # Start row
1032      $rowmax          = $par3;        # End row
1033      $colmin          = $par4;        # Start column
1034      $colmax          = $par5;        # end column
1035  
1036      $header          = pack("vv",  $record, $length);
1037      $data            = pack("v", $grbit);
1038      $data              .= pack("C", $chKey);
1039      $data              .= pack("C", $cch);
1040      $data              .= pack("v", $cce);
1041      $data              .= pack("v", $ixals);
1042      $data              .= pack("v", $itab);
1043      $data              .= pack("C", $cchCustMenu);
1044      $data              .= pack("C", $cchDescription);
1045      $data              .= pack("C", $cchHelptopic);
1046      $data              .= pack("C", $cchStatustext);
1047      $data              .= pack("C", $rgch);
1048      $data              .= pack("C", $unknown01);
1049      $data              .= pack("v", $unknown02);
1050      # Column definition
1051      $data              .= pack("C", $unknown03);
1052      $data              .= pack("v", $unknown04);
1053      $data              .= pack("v", $unknown05);
1054      $data              .= pack("v", $unknown06);
1055      $data              .= pack("v", $unknown07);
1056      $data              .= pack("v", $unknown08);
1057      $data              .= pack("v", $index);
1058      $data              .= pack("v", $index);
1059      $data              .= pack("v", 0x0000);
1060      $data              .= pack("v", 0x3fff);
1061      $data              .= pack("C", $colmin);
1062      $data              .= pack("C", $colmax);
1063      # Row definition
1064      $data              .= pack("C", $unknown03);
1065      $data              .= pack("v", $unknown04);
1066      $data              .= pack("v", $unknown05);
1067      $data              .= pack("v", $unknown06);
1068      $data              .= pack("v", $unknown07);
1069      $data              .= pack("v", $unknown08);
1070      $data              .= pack("v", $index);
1071      $data              .= pack("v", $index);
1072      $data              .= pack("v", $rowmin);
1073      $data              .= pack("v", $rowmax);
1074      $data              .= pack("C", 0x00);
1075      $data              .= pack("C", 0xff);
1076      # End of data
1077      $data              .= pack("C", 0x10);
1078  
1079      $this->_append($header . $data);
1080  }
1081  
1082  ###############################################################################
1083  #
1084  # _store_palette()
1085  #
1086  # Stores the PALETTE biff record.
1087  #
1088  function _store_palette() {
1089      $aref            = &$this->_palette;
1090  
1091      $record          = 0x0092;                  # Record identifier
1092      $length          = 2 + 4 * sizeof($aref);   # Number of bytes to follow
1093      $ccv             =         sizeof($aref);   # Number of RGB values to follow
1094      //$data;                                      # The RGB data
1095  
1096      # Pack the RGB data
1097      foreach($aref as $dat) {
1098          $data .= call_user_func_array('pack', array_merge(array("CCCC"), $dat));
1099      }
1100  
1101      $header = pack("vvv",  $record, $length, $ccv);
1102  
1103      $this->_append($header . $data);
1104  }
1105  
1106  }
1107  
1108  ?>