lib/excel/Workbook.php

   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::WriteExcel:  A library for generating Excel Spreadsheets
  18 *    Copyright (C) 2002 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("$CFG->libdir/excel/Format.php");
  37 require_once('OLEwriter.php');
  38 require_once('BIFFwriter.php');
  39
  40 /**
  41 * Class for generating Excel Spreadsheets
  42 *
  43 * @author Xavier Noguer <xnoguer@rezebra.com>
  44 * @package Spreadsheet_WriteExcel
  45 */
  46
  47 class Workbook extends BIFFwriter
  48 {
  49     /**
  50     * Class constructor
  51     *
  52     * @param string filename for storing the workbook. "-" for writing to stdout.
  53     */
  54     function Workbook($filename)
  55     {
  56         $this->BIFFwriter(); // It needs to call its parent's constructor explicitly
  57
  58         $this->_filename         = $filename;
  59         $this->parser            = new Parser($this->_byte_order);
  60         $this->_1904             = 0;
  61         $this->activesheet       = 0;
  62         $this->firstsheet        = 0;
  63         $this->selected          = 0;
  64         $this->xf_index          = 16; // 15 style XF's and 1 cell XF.
  65         $this->_fileclosed       = 0;
  66         $this->_biffsize         = 0;
  67         $this->sheetname         = "Sheet";
  68         $this->tmp_format        = new Format();
  69         $this->worksheets        = array();
  70         $this->sheetnames        = array();
  71         $this->formats           = array();
  72         $this->palette           = array();
  73
  74         // Add the default format for hyperlinks
  75         $this->url_format =& $this->add_format(array('color' => 'blue', 'underline' => 1));
  76
  77         // Check for a filename
  78         //if ($this->_filename == '') {
  79         //    die('Filename required by Spreadsheet::WriteExcel->new()');
  80         //}
  81
  82         # Warn if tmpfiles can't be used.
  83         //$this->tmpfile_warning();
  84         $this->_set_palette_xl97();
  85     }
  86
  87     /**
  88     * Calls finalization methods and explicitly close the OLEwriter file
  89     * handle.
  90     */
  91     function close()
  92     {
  93         if ($this->_fileclosed) { // Prevent close() from being called twice.
  94             return;
  95         }
  96         $this->store_workbook();
  97         $this->_fileclosed = 1;
  98     }
  99
 100
 101     /**
 102     * An accessor for the _worksheets[] array
 103     * Returns an array of the worksheet objects in a workbook
 104     *
 105     * @return array
 106     */
 107     function sheets()
 108     {
 109         return($this->worksheets());
 110     }
 111
 112     /**
 113     * An accessor for the _worksheets[] array.
 114     *
 115     * @return array
 116     */
 117     function worksheets()
 118     {
 119         return($this->worksheets);
 120     }
 121
 122     /**
 123     * Add a new worksheet to the Excel workbook.
 124     * TODO: Add accessor for $this->{_sheetname} for international Excel versions.
 125     *
 126     * @access public
 127     * @param string $name the optional name of the worksheet
 128     * @return &object reference to a worksheet object
 129     */
 130     function &add_worksheet($name = '')
 131     {
 132         $index     = count($this->worksheets);
 133         $sheetname = $this->sheetname;
 134
 135         if($name == '') {
 136             $name = $sheetname.($index+1);
 137         }
 138
 139         // Check that sheetname is <= 31 chars (Excel limit).
 140         if(strlen($name) > 31) {
 141             die("Sheetname $name must be <= 31 chars");
 142         }
 143
 144         // Check that the worksheet name doesn't already exist: a fatal Excel error.
 145         for($i=0; $i < count($this->worksheets); $i++)
 146         {
 147             if($name == $this->worksheets[$i]->get_name()) {
 148                 die("Worksheet '$name' already exists");
 149             }
 150         }
 151
 152         $worksheet = new Worksheet($name,$index,$this->activesheet,
 153                                    $this->firstsheet,$this->url_format,
 154                                    $this->parser);
 155         $this->worksheets[$index] = &$worksheet;      // Store ref for iterator
 156         $this->sheetnames[$index] = $name;            // Store EXTERNSHEET names
 157         //$this->parser->set_ext_sheet($name,$index); // Store names in Formula.php
 158         return($worksheet);
 159     }
 160
 161     /**
 162     * DEPRECATED!! Use add_worksheet instead
 163     *
 164     * @access public
 165     * @deprecated Use add_worksheet instead
 166     * @param string $name the optional name of the worksheet
 167     * @return &object reference to a worksheet object
 168     */
 169     function &addworksheet($name = '')
 170     {
 171         return($this->add_worksheet($name));
 172     }
 173
 174     /**
 175     * Add a new format to the Excel workbook. This adds an XF record and
 176     * a FONT record. Also, pass any properties to the Format constructor.
 177     *
 178     * @access public
 179     * @param array $properties array with properties for initializing the format (see Format.php)
 180     * @return &object reference to an XF format
 181     */
 182     function &add_format($properties = array())
 183     {
 184         $format = new Format($this->xf_index,$properties);
 185         $this->xf_index += 1;
 186         $this->formats[] = &$format;
 187         return($format);
 188     }
 189
 190     /**
 191     * DEPRECATED!! Use add_format instead
 192     *
 193     * @access public
 194     * @deprecated Use add_format instead
 195     * @param array $properties array with properties for initializing the format (see Format.php)
 196     * @return &object reference to an XF format
 197     */
 198     function &addformat($properties = array())
 199     {
 200          return($this->add_format($properties));
 201     }
 202
 203
 204     /**
 205     * Change the RGB components of the elements in the colour palette.
 206     *
 207     * @access public
 208     * @param integer $index colour index
 209     * @param integer $red   red RGB value [0-255]
 210     * @param integer $green green RGB value [0-255]
 211     * @param integer $blue  blue RGB value [0-255]
 212     * @return integer The palette index for the custom color
 213     */
 214     function set_custom_color($index,$red,$green,$blue)
 215     {
 216         // Match a HTML #xxyyzz style parameter
 217         /*if (defined $_[1] and $_[1] =~ /^#(\w\w)(\w\w)(\w\w)/ ) {
 218             @_ = ($_[0], hex $1, hex $2, hex $3);
 219         }*/
 220
 221         // Check that the colour index is the right range
 222         if ($index < 8 or $index > 64) {
 223             die("Color index $index outside range: 8 <= index <= 64");
 224         }
 225
 226         // Check that the colour components are in the right range
 227         if ( ($red   < 0 or $red   > 255) ||
 228              ($green < 0 or $green > 255) ||
 229              ($blue  < 0 or $blue  > 255) )
 230         {
 231             die("Color component outside range: 0 <= color <= 255");
 232         }
 233
 234         $index -= 8; // Adjust colour index (wingless dragonfly)
 235
 236         // Set the RGB value
 237         $this->palette[$index] = array($red, $green, $blue, 0);
 238         return($index + 8);
 239     }
 240
 241     /**
 242     * Sets the colour palette to the Excel 97+ default.
 243     */
 244     function _set_palette_xl97()
 245     {
 246         $this->palette = array(
 247                            array(0x00, 0x00, 0x00, 0x00),   // 8
 248                            array(0xff, 0xff, 0xff, 0x00),   // 9
 249                            array(0xff, 0x00, 0x00, 0x00),   // 10
 250                            array(0x00, 0xff, 0x00, 0x00),   // 11
 251                            array(0x00, 0x00, 0xff, 0x00),   // 12
 252                            array(0xff, 0xff, 0x00, 0x00),   // 13
 253                            array(0xff, 0x00, 0xff, 0x00),   // 14
 254                            array(0x00, 0xff, 0xff, 0x00),   // 15
 255                            array(0x80, 0x00, 0x00, 0x00),   // 16
 256                            array(0x00, 0x80, 0x00, 0x00),   // 17
 257                            array(0x00, 0x00, 0x80, 0x00),   // 18
 258                            array(0x80, 0x80, 0x00, 0x00),   // 19
 259                            array(0x80, 0x00, 0x80, 0x00),   // 20
 260                            array(0x00, 0x80, 0x80, 0x00),   // 21
 261                            array(0xc0, 0xc0, 0xc0, 0x00),   // 22
 262                            array(0x80, 0x80, 0x80, 0x00),   // 23
 263                            array(0x99, 0x99, 0xff, 0x00),   // 24
 264                            array(0x99, 0x33, 0x66, 0x00),   // 25
 265                            array(0xff, 0xff, 0xcc, 0x00),   // 26
 266                            array(0xcc, 0xff, 0xff, 0x00),   // 27
 267                            array(0x66, 0x00, 0x66, 0x00),   // 28
 268                            array(0xff, 0x80, 0x80, 0x00),   // 29
 269                            array(0x00, 0x66, 0xcc, 0x00),   // 30
 270                            array(0xcc, 0xcc, 0xff, 0x00),   // 31
 271                            array(0x00, 0x00, 0x80, 0x00),   // 32
 272                            array(0xff, 0x00, 0xff, 0x00),   // 33
 273                            array(0xff, 0xff, 0x00, 0x00),   // 34
 274                            array(0x00, 0xff, 0xff, 0x00),   // 35
 275                            array(0x80, 0x00, 0x80, 0x00),   // 36
 276                            array(0x80, 0x00, 0x00, 0x00),   // 37
 277                            array(0x00, 0x80, 0x80, 0x00),   // 38
 278                            array(0x00, 0x00, 0xff, 0x00),   // 39
 279                            array(0x00, 0xcc, 0xff, 0x00),   // 40
 280                            array(0xcc, 0xff, 0xff, 0x00),   // 41
 281                            array(0xcc, 0xff, 0xcc, 0x00),   // 42
 282                            array(0xff, 0xff, 0x99, 0x00),   // 43
 283                            array(0x99, 0xcc, 0xff, 0x00),   // 44
 284                            array(0xff, 0x99, 0xcc, 0x00),   // 45
 285                            array(0xcc, 0x99, 0xff, 0x00),   // 46
 286                            array(0xff, 0xcc, 0x99, 0x00),   // 47
 287                            array(0x33, 0x66, 0xff, 0x00),   // 48
 288                            array(0x33, 0xcc, 0xcc, 0x00),   // 49
 289                            array(0x99, 0xcc, 0x00, 0x00),   // 50
 290                            array(0xff, 0xcc, 0x00, 0x00),   // 51
 291                            array(0xff, 0x99, 0x00, 0x00),   // 52
 292                            array(0xff, 0x66, 0x00, 0x00),   // 53
 293                            array(0x66, 0x66, 0x99, 0x00),   // 54
 294                            array(0x96, 0x96, 0x96, 0x00),   // 55
 295                            array(0x00, 0x33, 0x66, 0x00),   // 56
 296                            array(0x33, 0x99, 0x66, 0x00),   // 57
 297                            array(0x00, 0x33, 0x00, 0x00),   // 58
 298                            array(0x33, 0x33, 0x00, 0x00),   // 59
 299                            array(0x99, 0x33, 0x00, 0x00),   // 60
 300                            array(0x99, 0x33, 0x66, 0x00),   // 61
 301                            array(0x33, 0x33, 0x99, 0x00),   // 62
 302                            array(0x33, 0x33, 0x33, 0x00),   // 63
 303                          );
 304     }
 305
 306
 307     ###############################################################################
 308     #
 309     # _tmpfile_warning()
 310     #
 311     # Check that tmp files can be created for use in Worksheet.pm. A CGI, mod_perl
 312     # or IIS might not have permission to create tmp files. The test is here rather
 313     # than in Worksheet.pm so that only one warning is given.
 314     #
 315     /*sub _tmpfile_warning {
 316
 317         my $fh = IO::File->new_tmpfile();
 318
 319         if ((not defined $fh) && ($^W)) {
 320             carp("Unable to create tmp files via IO::File->new_tmpfile(). " .
 321                  "Storing data in memory")
 322     }
 323     }*/
 324
 325     /**
 326     * Assemble worksheets into a workbook and send the BIFF data to an OLE
 327     * storage.
 328     */
 329     function store_workbook()
 330     {
 331         // Ensure that at least one worksheet has been selected.
 332         if ($this->activesheet == 0) {
 333             $this->worksheets[0]->selected = 1;
 334         }
 335
 336         // Calculate the number of selected worksheet tabs and call the finalization
 337         // methods for each worksheet
 338         for($i=0; $i < count($this->worksheets); $i++)
 339         {
 340             if($this->worksheets[$i]->selected)
 341               $this->selected++;
 342             $this->worksheets[$i]->close($this->sheetnames);
 343         }
 344
 345         // Add Workbook globals
 346         $this->_store_bof(0x0005);
 347         $this->_store_externs();    // For print area and repeat rows
 348         $this->_store_names();      // For print area and repeat rows
 349         $this->_store_window1();
 350         $this->_store_1904();
 351         $this->_store_all_fonts();
 352         $this->_store_all_num_formats();
 353         $this->_store_all_xfs();
 354         $this->_store_all_styles();
 355         $this->_store_palette();
 356         $this->_calc_sheet_offsets();
 357
 358         // Add BOUNDSHEET records
 359         for($i=0; $i < count($this->worksheets); $i++) {
 360             $this->_store_boundsheet($this->worksheets[$i]->name,$this->worksheets[$i]->offset);
 361         }
 362
 363         // End Workbook globals
 364         $this->_store_eof();
 365
 366         // Store the workbook in an OLE container
 367         $this->_store_OLE_file();
 368     }
 369
 370     /**
 371     * Store the workbook in an OLE container if the total size of the workbook data
 372     * is less than ~ 7MB.
 373     */
 374     function _store_OLE_file()
 375     {
 376         $OLE  = new OLEwriter($this->_filename);
 377         // Write Worksheet data if data <~ 7MB
 378         if ($OLE->set_size($this->_biffsize))
 379         {
 380             $OLE->write_header();
 381             $OLE->write($this->_data);
 382             foreach($this->worksheets as $sheet)
 383             {
 384                 while ($tmp = $sheet->get_data()) {
 385                     $OLE->write($tmp);
 386                 }
 387             }
 388         }
 389         $OLE->close();
 390     }
 391
 392     /**
 393     * Calculate offsets for Worksheet BOF records.
 394     */
 395     function _calc_sheet_offsets()
 396     {
 397         $BOF     = 11;
 398         $EOF     = 4;
 399         $offset  = $this->_datasize;
 400         for($i=0; $i < count($this->worksheets); $i++) {
 401             $offset += $BOF + strlen($this->worksheets[$i]->name);
 402         }
 403         $offset += $EOF;
 404         for($i=0; $i < count($this->worksheets); $i++) {
 405             $this->worksheets[$i]->offset = $offset;
 406             $offset += $this->worksheets[$i]->_datasize;
 407         }
 408         $this->_biffsize = $offset;
 409     }
 410
 411     /**
 412     * Store the Excel FONT records.
 413     */
 414     function _store_all_fonts()
 415     {
 416         // tmp_format is added by new(). We use this to write the default XF's
 417         $format = $this->tmp_format;
 418         $font   = $format->get_font();
 419
 420         // Note: Fonts are 0-indexed. According to the SDK there is no index 4,
 421         // so the following fonts are 0, 1, 2, 3, 5
 422         //
 423         for($i=1; $i <= 5; $i++){
 424             $this->_append($font);
 425         }
 426
 427         // Iterate through the XF objects and write a FONT record if it isn't the
 428         // same as the default FONT and if it hasn't already been used.
 429         //
 430         $fonts = array();
 431         $index = 6;                  // The first user defined FONT
 432
 433         $key = $format->get_font_key(); // The default font from _tmp_format
 434         $fonts[$key] = 0;               // Index of the default font
 435
 436         for($i=0; $i < count($this->formats); $i++) {
 437             $key = $this->formats[$i]->get_font_key();
 438             if (isset($fonts[$key])) {
 439                 // FONT has already been used
 440                 $this->formats[$i]->font_index = $fonts[$key];
 441             }
 442             else {
 443                 // Add a new FONT record
 444                 $fonts[$key]        = $index;
 445                 $this->formats[$i]->font_index = $index;
 446                 $index++;
 447                 $font = $this->formats[$i]->get_font();
 448                 $this->_append($font);
 449             }
 450         }
 451     }
 452
 453     /**
 454     * Store user defined numerical formats i.e. FORMAT records
 455     */
 456     function _store_all_num_formats()
 457     {
 458         // Leaning num_format syndrome
 459         $hash_num_formats = array();
 460         $num_formats      = array();
 461         $index = 164;
 462
 463         // Iterate through the XF objects and write a FORMAT record if it isn't a
 464         // built-in format type and if the FORMAT string hasn't already been used.
 465         //
 466         for($i=0; $i < count($this->formats); $i++)
 467         {
 468             $num_format = $this->formats[$i]->_num_format;
 469
 470             // Check if $num_format is an index to a built-in format.
 471             // Also check for a string of zeros, which is a valid format string
 472             // but would evaluate to zero.
 473             //
 474             if (!preg_match("/^0+\d/",$num_format))
 475             {
 476                 if (preg_match("/^\d+$/",$num_format)) { // built-in format
 477                     continue;
 478                 }
 479             }
 480
 481             if (isset($hash_num_formats[$num_format])) {
 482                 // FORMAT has already been used
 483                 $this->formats[$i]->_num_format = $hash_num_formats[$num_format];
 484             }
 485             else
 486             {
 487                 // Add a new FORMAT
 488                 $hash_num_formats[$num_format]  = $index;
 489                 $this->formats[$i]->_num_format = $index;
 490                 array_push($num_formats,$num_format);
 491                 $index++;
 492             }
 493         }
 494
 495         // Write the new FORMAT records starting from 0xA4
 496         $index = 164;
 497         foreach ($num_formats as $num_format)
 498         {
 499             $this->_store_num_format($num_format,$index);
 500             $index++;
 501         }
 502     }
 503
 504     /**
 505     * Write all XF records.
 506     */
 507     function _store_all_xfs()
 508     {
 509         // tmp_format is added by the constructor. We use this to write the default XF's
 510         // The default font index is 0
 511         //
 512         $format = $this->tmp_format;
 513         for ($i=0; $i <= 14; $i++)
 514         {
 515             $xf = $format->get_xf('style'); // Style XF
 516             $this->_append($xf);
 517         }
 518
 519         $xf = $format->get_xf('cell');      // Cell XF
 520         $this->_append($xf);
 521
 522         // User defined XFs
 523         for($i=0; $i < count($this->formats); $i++)
 524         {
 525             $xf = $this->formats[$i]->get_xf('cell');
 526             $this->_append($xf);
 527         }
 528     }
 529
 530     /**
 531     * Write all STYLE records.
 532     */
 533     function _store_all_styles()
 534     {
 535         $this->_store_style();
 536     }
 537
 538     /**
 539     * Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for
 540     * the NAME records.
 541     */
 542     function _store_externs()
 543     {
 544         // Create EXTERNCOUNT with number of worksheets
 545         $this->_store_externcount(count($this->worksheets));
 546
 547         // Create EXTERNSHEET for each worksheet
 548         foreach ($this->sheetnames as $sheetname) {
 549             $this->_store_externsheet($sheetname);
 550         }
 551     }
 552
 553     /**
 554     * Write the NAME record to define the print area and the repeat rows and cols.
 555     */
 556     function _store_names()
 557     {
 558         // Create the print area NAME records
 559         foreach ($this->worksheets as $worksheet)
 560         {
 561             // Write a Name record if the print area has been defined
 562             if (isset($worksheet->_print_rowmin))
 563             {
 564                 $this->store_name_short(
 565                     $worksheet->index,
 566                     0x06, // NAME type
 567                     $worksheet->_print_rowmin,
 568                     $worksheet->_print_rowmax,
 569                     $worksheet->_print_colmin,
 570                     $worksheet->_print_colmax
 571                     );
 572             }
 573         }
 574
 575         // Create the print title NAME records
 576         foreach ($this->worksheets as $worksheet)
 577         {
 578             $rowmin = $worksheet->_title_rowmin;
 579             $rowmax = $worksheet->_title_rowmax;
 580             $colmin = $worksheet->_title_colmin;
 581             $colmax = $worksheet->_title_colmax;
 582
 583             // Determine if row + col, row, col or nothing has been defined
 584             // and write the appropriate record
 585             //
 586             if (isset($rowmin) && isset($colmin))
 587             {
 588                 // Row and column titles have been defined.
 589                 // Row title has been defined.
 590                 $this->store_name_long(
 591                     $worksheet->index,
 592                     0x07, // NAME type
 593                     $rowmin,
 594                     $rowmax,
 595                     $colmin,
 596                     $colmax
 597                     );
 598             }
 599             elseif (isset($rowmin))
 600             {
 601                 // Row title has been defined.
 602                 $this->store_name_short(
 603                     $worksheet->index,
 604                     0x07, // NAME type
 605                     $rowmin,
 606                     $rowmax,
 607                     0x00,
 608                     0xff
 609                     );
 610             }
 611             elseif (isset($colmin))
 612             {
 613                 // Column title has been defined.
 614                 $this->store_name_short(
 615                     $worksheet->index,
 616                     0x07, // NAME type
 617                     0x0000,
 618                     0x3fff,
 619                     $colmin,
 620                     $colmax
 621                     );
 622             }
 623             else {
 624                 // Print title hasn't been defined.
 625             }
 626         }
 627     }
 628
 629
 630
 631
 632     /******************************************************************************
 633     *
 634     * BIFF RECORDS
 635     *
 636     */
 637
 638     /**
 639     * Write Excel BIFF WINDOW1 record.
 640     */
 641     function _store_window1()
 642     {
 643         $record    = 0x003D;                 // Record identifier
 644         $length    = 0x0012;                 // Number of bytes to follow
 645
 646         $xWn       = 0x0000;                 // Horizontal position of window
 647         $yWn       = 0x0000;                 // Vertical position of window
 648         $dxWn      = 0x25BC;                 // Width of window
 649         $dyWn      = 0x1572;                 // Height of window
 650
 651         $grbit     = 0x0038;                 // Option flags
 652         $ctabsel   = $this->selected;        // Number of workbook tabs selected
 653         $wTabRatio = 0x0258;                 // Tab to scrollbar ratio
 654
 655         $itabFirst = $this->firstsheet;   // 1st displayed worksheet
 656         $itabCur   = $this->activesheet;  // Active worksheet
 657
 658         $header    = pack("vv",        $record, $length);
 659         $data      = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,
 660                                        $grbit,
 661                                        $itabCur, $itabFirst,
 662                                        $ctabsel, $wTabRatio);
 663         $this->_append($header.$data);
 664     }
 665
 666     /**
 667     * Writes Excel BIFF BOUNDSHEET record.
 668     *
 669     * @param string  $sheetname Worksheet name
 670     * @param integer $offset    Location of worksheet BOF
 671     */
 672     function _store_boundsheet($sheetname,$offset)
 673     {
 674         $record    = 0x0085;                    // Record identifier
 675         $length    = 0x07 + strlen($sheetname); // Number of bytes to follow
 676
 677         $grbit     = 0x0000;                    // Sheet identifier
 678         $cch       = strlen($sheetname);        // Length of sheet name
 679
 680         $header    = pack("vv",  $record, $length);
 681         $data      = pack("VvC", $offset, $grbit, $cch);
 682         $this->_append($header.$data.$sheetname);
 683     }
 684
 685     /**
 686     * Write Excel BIFF STYLE records.
 687     */
 688     function _store_style()
 689     {
 690         $record    = 0x0293;   // Record identifier
 691         $length    = 0x0004;   // Bytes to follow
 692
 693         $ixfe      = 0x8000;   // Index to style XF
 694         $BuiltIn   = 0x00;     // Built-in style
 695         $iLevel    = 0xff;     // Outline style level
 696
 697         $header    = pack("vv",  $record, $length);
 698         $data      = pack("vCC", $ixfe, $BuiltIn, $iLevel);
 699         $this->_append($header.$data);
 700     }
 701
 702
 703     /**
 704     * Writes Excel FORMAT record for non "built-in" numerical formats.
 705     *
 706     * @param string  $format Custom format string
 707     * @param integer $ifmt   Format index code
 708     */
 709     function _store_num_format($format,$ifmt)
 710     {
 711         $record    = 0x041E;                      // Record identifier
 712         $length    = 0x03 + strlen($format);      // Number of bytes to follow
 713
 714         $cch       = strlen($format);             // Length of format string
 715
 716         $header    = pack("vv", $record, $length);
 717         $data      = pack("vC", $ifmt, $cch);
 718         $this->_append($header.$data.$format);
 719     }
 720
 721     /**
 722     * Write Excel 1904 record to indicate the date system in use.
 723     */
 724     function _store_1904()
 725     {
 726         $record    = 0x0022;         // Record identifier
 727         $length    = 0x0002;         // Bytes to follow
 728
 729         $f1904     = $this->_1904;   // Flag for 1904 date system
 730
 731         $header    = pack("vv", $record, $length);
 732         $data      = pack("v", $f1904);
 733         $this->_append($header.$data);
 734     }
 735
 736
 737     /**
 738     * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
 739     * references in the workbook.
 740     *
 741     * Excel only stores references to external sheets that are used in NAME.
 742     * The workbook NAME record is required to define the print area and the repeat
 743     * rows and columns.
 744     *
 745     * A similar method is used in Worksheet.php for a slightly different purpose.
 746     *
 747     * @param integer $cxals Number of external references
 748     */
 749     function _store_externcount($cxals)
 750     {
 751         $record   = 0x0016;          // Record identifier
 752         $length   = 0x0002;          // Number of bytes to follow
 753
 754         $header   = pack("vv", $record, $length);
 755         $data     = pack("v",  $cxals);
 756         $this->_append($header.$data);
 757     }
 758
 759
 760     /**
 761     * Writes the Excel BIFF EXTERNSHEET record. These references are used by
 762     * formulas. NAME record is required to define the print area and the repeat
 763     * rows and columns.
 764     *
 765     * A similar method is used in Worksheet.php for a slightly different purpose.
 766     *
 767     * @param string $sheetname Worksheet name
 768     */
 769     function _store_externsheet($sheetname)
 770     {
 771         $record      = 0x0017;                     // Record identifier
 772         $length      = 0x02 + strlen($sheetname);  // Number of bytes to follow
 773
 774         $cch         = strlen($sheetname);         // Length of sheet name
 775         $rgch        = 0x03;                       // Filename encoding
 776
 777         $header      = pack("vv",  $record, $length);
 778         $data        = pack("CC", $cch, $rgch);
 779         $this->_append($header.$data.$sheetname);
 780     }
 781
 782
 783     /**
 784     * Store the NAME record in the short format that is used for storing the print
 785     * area, repeat rows only and repeat columns only.
 786     *
 787     * @param integer $index  Sheet index
 788     * @param integer $type   Built-in name type
 789     * @param integer $rowmin Start row
 790     * @param integer $rowmax End row
 791     * @param integer $colmin Start colum
 792     * @param integer $colmax End column
 793     */
 794     function store_name_short($index,$type,$rowmin,$rowmax,$colmin,$colmax)
 795     {
 796         $record          = 0x0018;       // Record identifier
 797         $length          = 0x0024;       // Number of bytes to follow
 798
 799         $grbit           = 0x0020;       // Option flags
 800         $chKey           = 0x00;         // Keyboard shortcut
 801         $cch             = 0x01;         // Length of text name
 802         $cce             = 0x0015;       // Length of text definition
 803         $ixals           = $index + 1;   // Sheet index
 804         $itab            = $ixals;       // Equal to ixals
 805         $cchCustMenu     = 0x00;         // Length of cust menu text
 806         $cchDescription  = 0x00;         // Length of description text
 807         $cchHelptopic    = 0x00;         // Length of help topic text
 808         $cchStatustext   = 0x00;         // Length of status bar text
 809         $rgch            = $type;        // Built-in name type
 810
 811         $unknown03       = 0x3b;
 812         $unknown04       = 0xffff-$index;
 813         $unknown05       = 0x0000;
 814         $unknown06       = 0x0000;
 815         $unknown07       = 0x1087;
 816         $unknown08       = 0x8005;
 817
 818         $header             = pack("vv", $record, $length);
 819         $data               = pack("v", $grbit);
 820         $data              .= pack("C", $chKey);
 821         $data              .= pack("C", $cch);
 822         $data              .= pack("v", $cce);
 823         $data              .= pack("v", $ixals);
 824         $data              .= pack("v", $itab);
 825         $data              .= pack("C", $cchCustMenu);
 826         $data              .= pack("C", $cchDescription);
 827         $data              .= pack("C", $cchHelptopic);
 828         $data              .= pack("C", $cchStatustext);
 829         $data              .= pack("C", $rgch);
 830         $data              .= pack("C", $unknown03);
 831         $data              .= pack("v", $unknown04);
 832         $data              .= pack("v", $unknown05);
 833         $data              .= pack("v", $unknown06);
 834         $data              .= pack("v", $unknown07);
 835         $data              .= pack("v", $unknown08);
 836         $data              .= pack("v", $index);
 837         $data              .= pack("v", $index);
 838         $data              .= pack("v", $rowmin);
 839         $data              .= pack("v", $rowmax);
 840         $data              .= pack("C", $colmin);
 841         $data              .= pack("C", $colmax);
 842         $this->_append($header.$data);
 843     }
 844
 845
 846     /**
 847     * Store the NAME record in the long format that is used for storing the repeat
 848     * rows and columns when both are specified. This share a lot of code with
 849     * _store_name_short() but we use a separate method to keep the code clean.
 850     * Code abstraction for reuse can be carried too far, and I should know. ;-)
 851     *
 852     * @param integer $index Sheet index
 853     * @param integer $type  Built-in name type
 854     * @param integer $rowmin Start row
 855     * @param integer $rowmax End row
 856     * @param integer $colmin Start colum
 857     * @param integer $colmax End column
 858     */
 859     function store_name_long($index,$type,$rowmin,$rowmax,$colmin,$colmax)
 860     {
 861         $record          = 0x0018;       // Record identifier
 862         $length          = 0x003d;       // Number of bytes to follow
 863         $grbit           = 0x0020;       // Option flags
 864         $chKey           = 0x00;         // Keyboard shortcut
 865         $cch             = 0x01;         // Length of text name
 866         $cce             = 0x002e;       // Length of text definition
 867         $ixals           = $index + 1;   // Sheet index
 868         $itab            = $ixals;       // Equal to ixals
 869         $cchCustMenu     = 0x00;         // Length of cust menu text
 870         $cchDescription  = 0x00;         // Length of description text
 871         $cchHelptopic    = 0x00;         // Length of help topic text
 872         $cchStatustext   = 0x00;         // Length of status bar text
 873         $rgch            = $type;        // Built-in name type
 874
 875         $unknown01       = 0x29;
 876         $unknown02       = 0x002b;
 877         $unknown03       = 0x3b;
 878         $unknown04       = 0xffff-$index;
 879         $unknown05       = 0x0000;
 880         $unknown06       = 0x0000;
 881         $unknown07       = 0x1087;
 882         $unknown08       = 0x8008;
 883
 884         $header             = pack("vv",  $record, $length);
 885         $data               = pack("v", $grbit);
 886         $data              .= pack("C", $chKey);
 887         $data              .= pack("C", $cch);
 888         $data              .= pack("v", $cce);
 889         $data              .= pack("v", $ixals);
 890         $data              .= pack("v", $itab);
 891         $data              .= pack("C", $cchCustMenu);
 892         $data              .= pack("C", $cchDescription);
 893         $data              .= pack("C", $cchHelptopic);
 894         $data              .= pack("C", $cchStatustext);
 895         $data              .= pack("C", $rgch);
 896         $data              .= pack("C", $unknown01);
 897         $data              .= pack("v", $unknown02);
 898         // Column definition
 899         $data              .= pack("C", $unknown03);
 900         $data              .= pack("v", $unknown04);
 901         $data              .= pack("v", $unknown05);
 902         $data              .= pack("v", $unknown06);
 903         $data              .= pack("v", $unknown07);
 904         $data              .= pack("v", $unknown08);
 905         $data              .= pack("v", $index);
 906         $data              .= pack("v", $index);
 907         $data              .= pack("v", 0x0000);
 908         $data              .= pack("v", 0x3fff);
 909         $data              .= pack("C", $colmin);
 910         $data              .= pack("C", $colmax);
 911         // Row definition
 912         $data              .= pack("C", $unknown03);
 913         $data              .= pack("v", $unknown04);
 914         $data              .= pack("v", $unknown05);
 915         $data              .= pack("v", $unknown06);
 916         $data              .= pack("v", $unknown07);
 917         $data              .= pack("v", $unknown08);
 918         $data              .= pack("v", $index);
 919         $data              .= pack("v", $index);
 920         $data              .= pack("v", $rowmin);
 921         $data              .= pack("v", $rowmax);
 922         $data              .= pack("C", 0x00);
 923         $data              .= pack("C", 0xff);
 924         // End of data
 925         $data              .= pack("C", 0x10);
 926         $this->_append($header.$data);
 927     }
 928
 929
 930     /**
 931     * Stores the PALETTE biff record.
 932     */
 933     function _store_palette()
 934     {
 935         $aref            = $this->palette;
 936
 937         $record          = 0x0092;                 // Record identifier
 938         $length          = 2 + 4 * count($aref);   // Number of bytes to follow
 939         $ccv             =         count($aref);   // Number of RGB values to follow
 940         $data = '';                                // The RGB data
 941
 942         // Pack the RGB data
 943         foreach($aref as $color)
 944         {
 945             foreach($color as $byte) {
 946                 $data .= pack("C",$byte);
 947             }
 948         }
 949
 950         $header = pack("vvv",  $record, $length, $ccv);
 951         $this->_append($header.$data);
 952     }
 953 }
 954 ?>