[Summary view] [Print] [Text view]

   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 'Spreadsheet/Excel/Writer/Format.php';
  36  require_once 'Spreadsheet/Excel/Writer/BIFFwriter.php';
  37  require_once 'Spreadsheet/Excel/Writer/Worksheet.php';
  38  require_once 'Spreadsheet/Excel/Writer/Parser.php';
  39  require_once 'OLE/PPS/Root.php';
  40  require_once 'OLE/PPS/File.php';
  41  
  42  /**
  43  * Class for generating Excel Spreadsheets
  44  *
  45  * @author   Xavier Noguer <xnoguer@rezebra.com>
  46  * @category FileFormats
  47  * @package  Spreadsheet_Excel_Writer
  48  */
  49  
  50  class Spreadsheet_Excel_Writer_Workbook extends Spreadsheet_Excel_Writer_BIFFwriter
  51  {
  52      /**
  53      * Filename for the Workbook
  54      * @var string
  55      */
  56      var $_filename;
  57  
  58      /**
  59      * Formula parser
  60      * @var object Parser
  61      */
  62      var $_parser;
  63  
  64      /**
  65      * Flag for 1904 date system (0 => base date is 1900, 1 => base date is 1904)
  66      * @var integer
  67      */
  68      var $_1904;
  69  
  70      /**
  71      * The active worksheet of the workbook (0 indexed)
  72      * @var integer
  73      */
  74      var $_activesheet;
  75  
  76      /**
  77      * 1st displayed worksheet in the workbook (0 indexed)
  78      * @var integer
  79      */
  80      var $_firstsheet;
  81  
  82      /**
  83      * Number of workbook tabs selected
  84      * @var integer
  85      */
  86      var $_selected;
  87  
  88      /**
  89      * Index for creating adding new formats to the workbook
  90      * @var integer
  91      */
  92      var $_xf_index;
  93  
  94      /**
  95      * Flag for preventing close from being called twice.
  96      * @var integer
  97      * @see close()
  98      */
  99      var $_fileclosed;
 100  
 101      /**
 102      * The BIFF file size for the workbook.
 103      * @var integer
 104      * @see _calcSheetOffsets()
 105      */
 106      var $_biffsize;
 107  
 108      /**
 109      * The default sheetname for all sheets created.
 110      * @var string
 111      */
 112      var $_sheetname;
 113  
 114      /**
 115      * The default XF format.
 116      * @var object Format
 117      */
 118      var $_tmp_format;
 119  
 120      /**
 121      * Array containing references to all of this workbook's worksheets
 122      * @var array
 123      */
 124      var $_worksheets;
 125  
 126      /**
 127      * Array of sheetnames for creating the EXTERNSHEET records
 128      * @var array
 129      */
 130      var $_sheetnames;
 131  
 132      /**
 133      * Array containing references to all of this workbook's formats
 134      * @var array
 135      */
 136      var $_formats;
 137  
 138      /**
 139      * Array containing the colour palette
 140      * @var array
 141      */
 142      var $_palette;
 143  
 144      /**
 145      * The default format for URLs.
 146      * @var object Format
 147      */
 148      var $_url_format;
 149  
 150      /**
 151      * The codepage indicates the text encoding used for strings
 152      * @var integer
 153      */
 154      var $_codepage;
 155  
 156      /**
 157      * The country code used for localization
 158      * @var integer
 159      */
 160      var $_country_code;
 161  
 162      /**
 163      * The temporary dir for storing the OLE file
 164      * @var string
 165      */
 166      var $_tmp_dir;
 167  
 168      /**
 169      * number of bytes for sizeinfo of strings
 170      * @var integer
 171      */
 172      var $_string_sizeinfo_size;
 173  
 174      /**
 175      * Class constructor
 176      *
 177      * @param string filename for storing the workbook. "-" for writing to stdout.
 178      * @access public
 179      */
 180      function Spreadsheet_Excel_Writer_Workbook($filename)
 181      {
 182          // It needs to call its parent's constructor explicitly
 183          $this->Spreadsheet_Excel_Writer_BIFFwriter();
 184  
 185          $this->_filename         = $filename;
 186          $this->_parser           =& new Spreadsheet_Excel_Writer_Parser($this->_byte_order, $this->_BIFF_version);
 187          $this->_1904             = 0;
 188          $this->_activesheet      = 0;
 189          $this->_firstsheet       = 0;
 190          $this->_selected         = 0;
 191          $this->_xf_index         = 16; // 15 style XF's and 1 cell XF.
 192          $this->_fileclosed       = 0;
 193          $this->_biffsize         = 0;
 194          $this->_sheetname        = 'Sheet';
 195          $this->_tmp_format       =& new Spreadsheet_Excel_Writer_Format($this->_BIFF_version);
 196          $this->_worksheets       = array();
 197          $this->_sheetnames       = array();
 198          $this->_formats          = array();
 199          $this->_palette          = array();
 200          $this->_codepage         = 0x04E4; // FIXME: should change for BIFF8
 201          $this->_country_code     = -1;
 202          $this->_string_sizeinfo  = 3;
 203  
 204          // Add the default format for hyperlinks
 205          $this->_url_format =& $this->addFormat(array('color' => 'blue', 'underline' => 1));
 206          $this->_str_total       = 0;
 207          $this->_str_unique      = 0;
 208          $this->_str_table       = array();
 209          $this->_setPaletteXl97();
 210          $this->_tmp_dir         = '';
 211      }
 212  
 213      /**
 214      * Calls finalization methods.
 215      * This method should always be the last one to be called on every workbook
 216      *
 217      * @access public
 218      * @return mixed true on success. PEAR_Error on failure
 219      */
 220      function close()
 221      {
 222          if ($this->_fileclosed) { // Prevent close() from being called twice.
 223              return true;
 224          }
 225          $res = $this->_storeWorkbook();
 226          if ($this->isError($res)) {
 227              return $this->raiseError($res->getMessage());
 228          }
 229          $this->_fileclosed = 1;
 230          return true;
 231      }
 232  
 233      /**
 234      * An accessor for the _worksheets[] array
 235      * Returns an array of the worksheet objects in a workbook
 236      * It actually calls to worksheets()
 237      *
 238      * @access public
 239      * @see worksheets()
 240      * @return array
 241      */
 242      function sheets()
 243      {
 244          return $this->worksheets();
 245      }
 246  
 247      /**
 248      * An accessor for the _worksheets[] array.
 249      * Returns an array of the worksheet objects in a workbook
 250      *
 251      * @access public
 252      * @return array
 253      */
 254      function worksheets()
 255      {
 256          return $this->_worksheets;
 257      }
 258  
 259      /**
 260      * Sets the BIFF version.
 261      * This method exists just to access experimental functionality
 262      * from BIFF8. It will be deprecated !
 263      * Only possible value is 8 (Excel 97/2000).
 264      * For any other value it fails silently.
 265      *
 266      * @access public
 267      * @param integer $version The BIFF version
 268      */
 269      function setVersion($version)
 270      {
 271          if ($version == 8) { // only accept version 8
 272              $version = 0x0600;
 273              $this->_BIFF_version = $version;
 274              // change BIFFwriter limit for CONTINUE records
 275              $this->_limit = 8228;
 276              $this->_tmp_format->_BIFF_version = $version;
 277              $this->_url_format->_BIFF_version = $version;
 278              $this->_parser->_BIFF_version = $version;
 279  
 280              $total_worksheets = count($this->_worksheets);
 281              // change version for all worksheets too
 282              for ($i = 0; $i < $total_worksheets; $i++) {
 283                  $this->_worksheets[$i]->_BIFF_version = $version;
 284              }
 285  
 286              $total_formats = count($this->_formats);
 287              // change version for all formats too
 288              for ($i = 0; $i < $total_formats; $i++) {
 289                  $this->_formats[$i]->_BIFF_version = $version;
 290              }
 291          }
 292      }
 293  
 294      /**
 295      * Set the country identifier for the workbook
 296      *
 297      * @access public
 298      * @param integer $code Is the international calling country code for the
 299      *                      chosen country.
 300      */
 301      function setCountry($code)
 302      {
 303          $this->_country_code = $code;
 304      }
 305  
 306      /**
 307      * Add a new worksheet to the Excel workbook.
 308      * If no name is given the name of the worksheet will be Sheeti$i, with
 309      * $i in [1..].
 310      *
 311      * @access public
 312      * @param string $name the optional name of the worksheet
 313      * @return mixed reference to a worksheet object on success, PEAR_Error
 314      *               on failure
 315      */
 316      function &addWorksheet($name = '')
 317      {
 318          $index     = count($this->_worksheets);
 319          $sheetname = $this->_sheetname;
 320  
 321          if ($name == '') {
 322              $name = $sheetname.($index+1);
 323          }
 324  
 325          // Check that sheetname is <= 31 chars (Excel limit before BIFF8).
 326          if ($this->_BIFF_version != 0x0600)
 327          {
 328              if (strlen($name) > 31) {
 329                  return $this->raiseError("Sheetname $name must be <= 31 chars");
 330              }
 331          }
 332  
 333          // Check that the worksheet name doesn't already exist: a fatal Excel error.
 334          $total_worksheets = count($this->_worksheets);
 335          for ($i = 0; $i < $total_worksheets; $i++) {
 336              if ($this->_worksheets[$i]->getName() == $name) {
 337                  return $this->raiseError("Worksheet '$name' already exists");
 338              }
 339          }
 340  
 341          $worksheet = new Spreadsheet_Excel_Writer_Worksheet($this->_BIFF_version,
 342                                     $name, $index,
 343                                     $this->_activesheet, $this->_firstsheet,
 344                                     $this->_str_total, $this->_str_unique,
 345                                     $this->_str_table, $this->_url_format,
 346                                     $this->_parser);
 347  
 348          $this->_worksheets[$index] = &$worksheet;    // Store ref for iterator
 349          $this->_sheetnames[$index] = $name;          // Store EXTERNSHEET names
 350          $this->_parser->setExtSheet($name, $index);  // Register worksheet name with parser
 351          return $worksheet;
 352      }
 353  
 354      /**
 355      * Add a new format to the Excel workbook.
 356      * Also, pass any properties to the Format constructor.
 357      *
 358      * @access public
 359      * @param array $properties array with properties for initializing the format.
 360      * @return &Spreadsheet_Excel_Writer_Format reference to an Excel Format
 361      */
 362      function &addFormat($properties = array())
 363      {
 364          $format = new Spreadsheet_Excel_Writer_Format($this->_BIFF_version, $this->_xf_index, $properties);
 365          $this->_xf_index += 1;
 366          $this->_formats[] = &$format;
 367          return $format;
 368      }
 369  
 370      /**
 371       * Create new validator.
 372       *
 373       * @access public
 374       * @return &Spreadsheet_Excel_Writer_Validator reference to a Validator
 375       */
 376      function &addValidator()
 377      {
 378          include_once 'Spreadsheet/Excel/Writer/Validator.php';
 379          /* FIXME: check for successful inclusion*/
 380          $valid = new Spreadsheet_Excel_Writer_Validator($this->_parser);
 381          return $valid;
 382      }
 383  
 384      /**
 385      * Change the RGB components of the elements in the colour palette.
 386      *
 387      * @access public
 388      * @param integer $index colour index
 389      * @param integer $red   red RGB value [0-255]
 390      * @param integer $green green RGB value [0-255]
 391      * @param integer $blue  blue RGB value [0-255]
 392      * @return integer The palette index for the custom color
 393      */
 394      function setCustomColor($index, $red, $green, $blue)
 395      {
 396          // Match a HTML #xxyyzz style parameter
 397          /*if (defined $_[1] and $_[1] =~ /^#(\w\w)(\w\w)(\w\w)/ ) {
 398              @_ = ($_[0], hex $1, hex $2, hex $3);
 399          }*/
 400  
 401          // Check that the colour index is the right range
 402          if ($index < 8 or $index > 64) {
 403              // TODO: assign real error codes
 404              return $this->raiseError("Color index $index outside range: 8 <= index <= 64");
 405          }
 406  
 407          // Check that the colour components are in the right range
 408          if (($red   < 0 or $red   > 255) ||
 409              ($green < 0 or $green > 255) ||
 410              ($blue  < 0 or $blue  > 255))
 411          {
 412              return $this->raiseError("Color component outside range: 0 <= color <= 255");
 413          }
 414  
 415          $index -= 8; // Adjust colour index (wingless dragonfly)
 416  
 417          // Set the RGB value
 418          $this->_palette[$index] = array($red, $green, $blue, 0);
 419          return($index + 8);
 420      }
 421  
 422      /**
 423      * Sets the colour palette to the Excel 97+ default.
 424      *
 425      * @access private
 426      */
 427      function _setPaletteXl97()
 428      {
 429          $this->_palette = array(
 430                             array(0x00, 0x00, 0x00, 0x00),   // 8
 431                             array(0xff, 0xff, 0xff, 0x00),   // 9
 432                             array(0xff, 0x00, 0x00, 0x00),   // 10
 433                             array(0x00, 0xff, 0x00, 0x00),   // 11
 434                             array(0x00, 0x00, 0xff, 0x00),   // 12
 435                             array(0xff, 0xff, 0x00, 0x00),   // 13
 436                             array(0xff, 0x00, 0xff, 0x00),   // 14
 437                             array(0x00, 0xff, 0xff, 0x00),   // 15
 438                             array(0x80, 0x00, 0x00, 0x00),   // 16
 439                             array(0x00, 0x80, 0x00, 0x00),   // 17
 440                             array(0x00, 0x00, 0x80, 0x00),   // 18
 441                             array(0x80, 0x80, 0x00, 0x00),   // 19
 442                             array(0x80, 0x00, 0x80, 0x00),   // 20
 443                             array(0x00, 0x80, 0x80, 0x00),   // 21
 444                             array(0xc0, 0xc0, 0xc0, 0x00),   // 22
 445                             array(0x80, 0x80, 0x80, 0x00),   // 23
 446                             array(0x99, 0x99, 0xff, 0x00),   // 24
 447                             array(0x99, 0x33, 0x66, 0x00),   // 25
 448                             array(0xff, 0xff, 0xcc, 0x00),   // 26
 449                             array(0xcc, 0xff, 0xff, 0x00),   // 27
 450                             array(0x66, 0x00, 0x66, 0x00),   // 28
 451                             array(0xff, 0x80, 0x80, 0x00),   // 29
 452                             array(0x00, 0x66, 0xcc, 0x00),   // 30
 453                             array(0xcc, 0xcc, 0xff, 0x00),   // 31
 454                             array(0x00, 0x00, 0x80, 0x00),   // 32
 455                             array(0xff, 0x00, 0xff, 0x00),   // 33
 456                             array(0xff, 0xff, 0x00, 0x00),   // 34
 457                             array(0x00, 0xff, 0xff, 0x00),   // 35
 458                             array(0x80, 0x00, 0x80, 0x00),   // 36
 459                             array(0x80, 0x00, 0x00, 0x00),   // 37
 460                             array(0x00, 0x80, 0x80, 0x00),   // 38
 461                             array(0x00, 0x00, 0xff, 0x00),   // 39
 462                             array(0x00, 0xcc, 0xff, 0x00),   // 40
 463                             array(0xcc, 0xff, 0xff, 0x00),   // 41
 464                             array(0xcc, 0xff, 0xcc, 0x00),   // 42
 465                             array(0xff, 0xff, 0x99, 0x00),   // 43
 466                             array(0x99, 0xcc, 0xff, 0x00),   // 44
 467                             array(0xff, 0x99, 0xcc, 0x00),   // 45
 468                             array(0xcc, 0x99, 0xff, 0x00),   // 46
 469                             array(0xff, 0xcc, 0x99, 0x00),   // 47
 470                             array(0x33, 0x66, 0xff, 0x00),   // 48
 471                             array(0x33, 0xcc, 0xcc, 0x00),   // 49
 472                             array(0x99, 0xcc, 0x00, 0x00),   // 50
 473                             array(0xff, 0xcc, 0x00, 0x00),   // 51
 474                             array(0xff, 0x99, 0x00, 0x00),   // 52
 475                             array(0xff, 0x66, 0x00, 0x00),   // 53
 476                             array(0x66, 0x66, 0x99, 0x00),   // 54
 477                             array(0x96, 0x96, 0x96, 0x00),   // 55
 478                             array(0x00, 0x33, 0x66, 0x00),   // 56
 479                             array(0x33, 0x99, 0x66, 0x00),   // 57
 480                             array(0x00, 0x33, 0x00, 0x00),   // 58
 481                             array(0x33, 0x33, 0x00, 0x00),   // 59
 482                             array(0x99, 0x33, 0x00, 0x00),   // 60
 483                             array(0x99, 0x33, 0x66, 0x00),   // 61
 484                             array(0x33, 0x33, 0x99, 0x00),   // 62
 485                             array(0x33, 0x33, 0x33, 0x00),   // 63
 486                           );
 487      }
 488  
 489      /**
 490      * Assemble worksheets into a workbook and send the BIFF data to an OLE
 491      * storage.
 492      *
 493      * @access private
 494      * @return mixed true on success. PEAR_Error on failure
 495      */
 496      function _storeWorkbook()
 497      {
 498          // Ensure that at least one worksheet has been selected.
 499          if ($this->_activesheet == 0) {
 500              $this->_worksheets[0]->selected = 1;
 501          }
 502  
 503          // Calculate the number of selected worksheet tabs and call the finalization
 504          // methods for each worksheet
 505          $total_worksheets = count($this->_worksheets);
 506          for ($i = 0; $i < $total_worksheets; $i++) {
 507              if ($this->_worksheets[$i]->selected) {
 508                  $this->_selected++;
 509              }
 510              $this->_worksheets[$i]->close($this->_sheetnames);
 511          }
 512  
 513          // Add Workbook globals
 514          $this->_storeBof(0x0005);
 515          $this->_storeCodepage();
 516          if ($this->_BIFF_version == 0x0600) {
 517              $this->_storeWindow1();
 518          }
 519          if ($this->_BIFF_version == 0x0500) {
 520              $this->_storeExterns();    // For print area and repeat rows
 521          }
 522          $this->_storeNames();      // For print area and repeat rows
 523          if ($this->_BIFF_version == 0x0500) {
 524              $this->_storeWindow1();
 525          }
 526          $this->_storeDatemode();
 527          $this->_storeAllFonts();
 528          $this->_storeAllNumFormats();
 529          $this->_storeAllXfs();
 530          $this->_storeAllStyles();
 531          $this->_storePalette();
 532          $this->_calcSheetOffsets();
 533  
 534          // Add BOUNDSHEET records
 535          for ($i = 0; $i < $total_worksheets; $i++) {
 536              $this->_storeBoundsheet($this->_worksheets[$i]->name,$this->_worksheets[$i]->offset);
 537          }
 538  
 539          if ($this->_country_code != -1) {
 540              $this->_storeCountry();
 541          }
 542  
 543          if ($this->_BIFF_version == 0x0600) {
 544              //$this->_storeSupbookInternal();
 545              /* TODO: store external SUPBOOK records and XCT and CRN records
 546              in case of external references for BIFF8 */
 547              //$this->_storeExternsheetBiff8();
 548              $this->_storeSharedStringsTable();
 549          }
 550  
 551          // End Workbook globals
 552          $this->_storeEof();
 553  
 554          // Store the workbook in an OLE container
 555          $res = $this->_storeOLEFile();
 556          if ($this->isError($res)) {
 557              return $this->raiseError($res->getMessage());
 558          }
 559          return true;
 560      }
 561  
 562      /**
 563      * Sets the temp dir used for storing the OLE file
 564      *
 565      * @access public
 566      * @param string $dir The dir to be used as temp dir
 567      * @return true if given dir is valid, false otherwise
 568      */
 569      function setTempDir($dir)
 570      {
 571          if (is_dir($dir)) {
 572              $this->_tmp_dir = $dir;
 573              return true;
 574          }
 575          return false;
 576      }
 577  
 578      /**
 579      * Store the workbook in an OLE container
 580      *
 581      * @access private
 582      * @return mixed true on success. PEAR_Error on failure
 583      */
 584      function _storeOLEFile()
 585      {
 586          $OLE = new OLE_PPS_File(OLE::Asc2Ucs('Book'));
 587          if ($this->_tmp_dir != '') {
 588              $OLE->setTempDir($this->_tmp_dir);
 589          }
 590          $res = $OLE->init();
 591          if ($this->isError($res)) {
 592              return $this->raiseError("OLE Error: ".$res->getMessage());
 593          }
 594          $OLE->append($this->_data);
 595  
 596          $total_worksheets = count($this->_worksheets);
 597          for ($i = 0; $i < $total_worksheets; $i++) {
 598              while ($tmp = $this->_worksheets[$i]->getData()) {
 599                  $OLE->append($tmp);
 600              }
 601          }
 602  
 603          $root = new OLE_PPS_Root(time(), time(), array($OLE));
 604          if ($this->_tmp_dir != '') {
 605              $root->setTempDir($this->_tmp_dir);
 606          }
 607  
 608          $res = $root->save($this->_filename);
 609          if ($this->isError($res)) {
 610              return $this->raiseError("OLE Error: ".$res->getMessage());
 611          }
 612          return true;
 613      }
 614  
 615      /**
 616      * Calculate offsets for Worksheet BOF records.
 617      *
 618      * @access private
 619      */
 620      function _calcSheetOffsets()
 621      {
 622          if ($this->_BIFF_version == 0x0600) {
 623              $boundsheet_length = 12;  // fixed length for a BOUNDSHEET record
 624          } else {
 625              $boundsheet_length = 11;
 626          }
 627          $EOF               = 4;
 628          $offset            = $this->_datasize;
 629  
 630          if ($this->_BIFF_version == 0x0600) {
 631              // add the length of the SST
 632              /* TODO: check this works for a lot of strings (> 8224 bytes) */
 633              $offset += $this->_calculateSharedStringsSizes();
 634              if ($this->_country_code != -1) {
 635                  $offset += 8; // adding COUNTRY record
 636              }
 637              // add the lenght of SUPBOOK, EXTERNSHEET and NAME records
 638              //$offset += 8; // FIXME: calculate real value when storing the records
 639          }
 640          $total_worksheets = count($this->_worksheets);
 641          // add the length of the BOUNDSHEET records
 642          for ($i = 0; $i < $total_worksheets; $i++) {
 643              $offset += $boundsheet_length + strlen($this->_worksheets[$i]->name);
 644          }
 645          $offset += $EOF;
 646  
 647          for ($i = 0; $i < $total_worksheets; $i++) {
 648              $this->_worksheets[$i]->offset = $offset;
 649              $offset += $this->_worksheets[$i]->_datasize;
 650          }
 651          $this->_biffsize = $offset;
 652      }
 653  
 654      /**
 655      * Store the Excel FONT records.
 656      *
 657      * @access private
 658      */
 659      function _storeAllFonts()
 660      {
 661          // tmp_format is added by the constructor. We use this to write the default XF's
 662          $format = $this->_tmp_format;
 663          $font   = $format->getFont();
 664  
 665          // Note: Fonts are 0-indexed. According to the SDK there is no index 4,
 666          // so the following fonts are 0, 1, 2, 3, 5
 667          //
 668          for ($i = 1; $i <= 5; $i++){
 669              $this->_append($font);
 670          }
 671  
 672          // Iterate through the XF objects and write a FONT record if it isn't the
 673          // same as the default FONT and if it hasn't already been used.
 674          //
 675          $fonts = array();
 676          $index = 6;                  // The first user defined FONT
 677  
 678          $key = $format->getFontKey(); // The default font from _tmp_format
 679          $fonts[$key] = 0;             // Index of the default font
 680  
 681          $total_formats = count($this->_formats);
 682          for ($i = 0; $i < $total_formats; $i++) {
 683              $key = $this->_formats[$i]->getFontKey();
 684              if (isset($fonts[$key])) {
 685                  // FONT has already been used
 686                  $this->_formats[$i]->font_index = $fonts[$key];
 687              } else {
 688                  // Add a new FONT record
 689                  $fonts[$key]        = $index;
 690                  $this->_formats[$i]->font_index = $index;
 691                  $index++;
 692                  $font = $this->_formats[$i]->getFont();
 693                  $this->_append($font);
 694              }
 695          }
 696      }
 697  
 698      /**
 699      * Store user defined numerical formats i.e. FORMAT records
 700      *
 701      * @access private
 702      */
 703      function _storeAllNumFormats()
 704      {
 705          // Leaning num_format syndrome
 706          $hash_num_formats = array();
 707          $num_formats      = array();
 708          $index = 164;
 709  
 710          // Iterate through the XF objects and write a FORMAT record if it isn't a
 711          // built-in format type and if the FORMAT string hasn't already been used.
 712          $total_formats = count($this->_formats);
 713          for ($i = 0; $i < $total_formats; $i++) {
 714              $num_format = $this->_formats[$i]->_num_format;
 715  
 716              // Check if $num_format is an index to a built-in format.
 717              // Also check for a string of zeros, which is a valid format string
 718              // but would evaluate to zero.
 719              //
 720              if (!preg_match("/^0+\d/", $num_format)) {
 721                  if (preg_match("/^\d+$/", $num_format)) { // built-in format
 722                      continue;
 723                  }
 724              }
 725  
 726              if (isset($hash_num_formats[$num_format])) {
 727                  // FORMAT has already been used
 728                  $this->_formats[$i]->_num_format = $hash_num_formats[$num_format];
 729              } else{
 730                  // Add a new FORMAT
 731                  $hash_num_formats[$num_format]  = $index;
 732                  $this->_formats[$i]->_num_format = $index;
 733                  array_push($num_formats,$num_format);
 734                  $index++;
 735              }
 736          }
 737  
 738          // Write the new FORMAT records starting from 0xA4
 739          $index = 164;
 740          foreach ($num_formats as $num_format) {
 741              $this->_storeNumFormat($num_format,$index);
 742              $index++;
 743          }
 744      }
 745  
 746      /**
 747      * Write all XF records.
 748      *
 749      * @access private
 750      */
 751      function _storeAllXfs()
 752      {
 753          // _tmp_format is added by the constructor. We use this to write the default XF's
 754          // The default font index is 0
 755          //
 756          $format = $this->_tmp_format;
 757          for ($i = 0; $i <= 14; $i++) {
 758              $xf = $format->getXf('style'); // Style XF
 759              $this->_append($xf);
 760          }
 761  
 762          $xf = $format->getXf('cell');      // Cell XF
 763          $this->_append($xf);
 764  
 765          // User defined XFs
 766          $total_formats = count($this->_formats);
 767          for ($i = 0; $i < $total_formats; $i++) {
 768              $xf = $this->_formats[$i]->getXf('cell');
 769              $this->_append($xf);
 770          }
 771      }
 772  
 773      /**
 774      * Write all STYLE records.
 775      *
 776      * @access private
 777      */
 778      function _storeAllStyles()
 779      {
 780          $this->_storeStyle();
 781      }
 782  
 783      /**
 784      * Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for
 785      * the NAME records.
 786      *
 787      * @access private
 788      */
 789      function _storeExterns()
 790      {
 791          // Create EXTERNCOUNT with number of worksheets
 792          $this->_storeExterncount(count($this->_worksheets));
 793  
 794          // Create EXTERNSHEET for each worksheet
 795          foreach ($this->_sheetnames as $sheetname) {
 796              $this->_storeExternsheet($sheetname);
 797          }
 798      }
 799  
 800      /**
 801      * Write the NAME record to define the print area and the repeat rows and cols.
 802      *
 803      * @access private
 804      */
 805      function _storeNames()
 806      {
 807          // Create the print area NAME records
 808          $total_worksheets = count($this->_worksheets);
 809          for ($i = 0; $i < $total_worksheets; $i++) {
 810              // Write a Name record if the print area has been defined
 811              if (isset($this->_worksheets[$i]->print_rowmin)) {
 812                  $this->_storeNameShort(
 813                      $this->_worksheets[$i]->index,
 814                      0x06, // NAME type
 815                      $this->_worksheets[$i]->print_rowmin,
 816                      $this->_worksheets[$i]->print_rowmax,
 817                      $this->_worksheets[$i]->print_colmin,
 818                      $this->_worksheets[$i]->print_colmax
 819                      );
 820              }
 821          }
 822  
 823          // Create the print title NAME records
 824          $total_worksheets = count($this->_worksheets);
 825          for ($i = 0; $i < $total_worksheets; $i++) {
 826              $rowmin = $this->_worksheets[$i]->title_rowmin;
 827              $rowmax = $this->_worksheets[$i]->title_rowmax;
 828              $colmin = $this->_worksheets[$i]->title_colmin;
 829              $colmax = $this->_worksheets[$i]->title_colmax;
 830  
 831              // Determine if row + col, row, col or nothing has been defined
 832              // and write the appropriate record
 833              //
 834              if (isset($rowmin) && isset($colmin)) {
 835                  // Row and column titles have been defined.
 836                  // Row title has been defined.
 837                  $this->_storeNameLong(
 838                      $this->_worksheets[$i]->index,
 839                      0x07, // NAME type
 840                      $rowmin,
 841                      $rowmax,
 842                      $colmin,
 843                      $colmax
 844                      );
 845              } elseif (isset($rowmin)) {
 846                  // Row title has been defined.
 847                  $this->_storeNameShort(
 848                      $this->_worksheets[$i]->index,
 849                      0x07, // NAME type
 850                      $rowmin,
 851                      $rowmax,
 852                      0x00,
 853                      0xff
 854                      );
 855              } elseif (isset($colmin)) {
 856                  // Column title has been defined.
 857                  $this->_storeNameShort(
 858                      $this->_worksheets[$i]->index,
 859                      0x07, // NAME type
 860                      0x0000,
 861                      0x3fff,
 862                      $colmin,
 863                      $colmax
 864                      );
 865              } else {
 866                  // Print title hasn't been defined.
 867              }
 868          }
 869      }
 870  
 871  
 872  
 873  
 874      /******************************************************************************
 875      *
 876      * BIFF RECORDS
 877      *
 878      */
 879  
 880      /**
 881      * Stores the CODEPAGE biff record.
 882      *
 883      * @access private
 884      */
 885      function _storeCodepage()
 886      {
 887          $record          = 0x0042;             // Record identifier
 888          $length          = 0x0002;             // Number of bytes to follow
 889          $cv              = $this->_codepage;   // The code page
 890  
 891          $header          = pack('vv', $record, $length);
 892          $data            = pack('v',  $cv);
 893  
 894          $this->_append($header . $data);
 895      }
 896  
 897      /**
 898      * Write Excel BIFF WINDOW1 record.
 899      *
 900      * @access private
 901      */
 902      function _storeWindow1()
 903      {
 904          $record    = 0x003D;                 // Record identifier
 905          $length    = 0x0012;                 // Number of bytes to follow
 906  
 907          $xWn       = 0x0000;                 // Horizontal position of window
 908          $yWn       = 0x0000;                 // Vertical position of window
 909          $dxWn      = 0x25BC;                 // Width of window
 910          $dyWn      = 0x1572;                 // Height of window
 911  
 912          $grbit     = 0x0038;                 // Option flags
 913          $ctabsel   = $this->_selected;       // Number of workbook tabs selected
 914          $wTabRatio = 0x0258;                 // Tab to scrollbar ratio
 915  
 916          $itabFirst = $this->_firstsheet;     // 1st displayed worksheet
 917          $itabCur   = $this->_activesheet;    // Active worksheet
 918  
 919          $header    = pack("vv",        $record, $length);
 920          $data      = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,
 921                                         $grbit,
 922                                         $itabCur, $itabFirst,
 923                                         $ctabsel, $wTabRatio);
 924          $this->_append($header . $data);
 925      }
 926  
 927      /**
 928      * Writes Excel BIFF BOUNDSHEET record.
 929      * FIXME: inconsistent with BIFF documentation
 930      *
 931      * @param string  $sheetname Worksheet name
 932      * @param integer $offset    Location of worksheet BOF
 933      * @access private
 934      */
 935      function _storeBoundsheet($sheetname,$offset)
 936      {
 937          $record    = 0x0085;                    // Record identifier
 938          if ($this->_BIFF_version == 0x0600) {
 939              $length    = 0x08 + strlen($sheetname); // Number of bytes to follow
 940          } else {
 941              $length = 0x07 + strlen($sheetname); // Number of bytes to follow
 942          }
 943  
 944          $grbit     = 0x0000;                    // Visibility and sheet type
 945          $cch       = strlen($sheetname);        // Length of sheet name
 946  
 947          $header    = pack("vv",  $record, $length);
 948          if ($this->_BIFF_version == 0x0600) {
 949              $data      = pack("Vvv", $offset, $grbit, $cch);
 950          } else {
 951              $data      = pack("VvC", $offset, $grbit, $cch);
 952          }
 953          $this->_append($header.$data.$sheetname);
 954      }
 955  
 956      /**
 957      * Write Internal SUPBOOK record
 958      *
 959      * @access private
 960      */
 961      function _storeSupbookInternal()
 962      {
 963          $record    = 0x01AE;   // Record identifier
 964          $length    = 0x0004;   // Bytes to follow
 965  
 966          $header    = pack("vv", $record, $length);
 967          $data      = pack("vv", count($this->_worksheets), 0x0104);
 968          $this->_append($header . $data);
 969      }
 970  
 971      /**
 972      * Writes the Excel BIFF EXTERNSHEET record. These references are used by
 973      * formulas.
 974      *
 975      * @param string $sheetname Worksheet name
 976      * @access private
 977      */
 978      function _storeExternsheetBiff8()
 979      {
 980          $total_references = count($this->_parser->_references);
 981          $record   = 0x0017;                     // Record identifier
 982          $length   = 2 + 6 * $total_references;  // Number of bytes to follow
 983  
 984          $supbook_index = 0;           // FIXME: only using internal SUPBOOK record
 985          $header           = pack("vv",  $record, $length);
 986          $data             = pack('v', $total_references);
 987          for ($i = 0; $i < $total_references; $i++) {
 988              $data .= $this->_parser->_references[$i];
 989          }
 990          $this->_append($header . $data);
 991      }
 992  
 993      /**
 994      * Write Excel BIFF STYLE records.
 995      *
 996      * @access private
 997      */
 998      function _storeStyle()
 999      {
1000          $record    = 0x0293;   // Record identifier
1001          $length    = 0x0004;   // Bytes to follow
1002  
1003          $ixfe      = 0x8000;   // Index to style XF
1004          $BuiltIn   = 0x00;     // Built-in style
1005          $iLevel    = 0xff;     // Outline style level
1006  
1007          $header    = pack("vv",  $record, $length);
1008          $data      = pack("vCC", $ixfe, $BuiltIn, $iLevel);
1009          $this->_append($header . $data);
1010      }
1011  
1012  
1013      /**
1014      * Writes Excel FORMAT record for non "built-in" numerical formats.
1015      *
1016      * @param string  $format Custom format string
1017      * @param integer $ifmt   Format index code
1018      * @access private
1019      */
1020      function _storeNumFormat($format, $ifmt)
1021      {
1022          $record    = 0x041E;                      // Record identifier
1023  
1024          if ($this->_BIFF_version == 0x0600) {
1025              $length    = 5 + strlen($format);      // Number of bytes to follow
1026              $encoding = 0x0;
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          } elseif ($this->_BIFF_version == 0x0500) {
1037              $data      = pack("vC", $ifmt, $cch);
1038          }
1039          $this->_append($header . $data . $format);
1040      }
1041  
1042      /**
1043      * Write DATEMODE record to indicate the date system in use (1904 or 1900).
1044      *
1045      * @access private
1046      */
1047      function _storeDatemode()
1048      {
1049          $record    = 0x0022;         // Record identifier
1050          $length    = 0x0002;         // Bytes to follow
1051  
1052          $f1904     = $this->_1904;   // Flag for 1904 date system
1053  
1054          $header    = pack("vv", $record, $length);
1055          $data      = pack("v", $f1904);
1056          $this->_append($header . $data);
1057      }
1058  
1059  
1060      /**
1061      * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
1062      * references in the workbook.
1063      *
1064      * Excel only stores references to external sheets that are used in NAME.
1065      * The workbook NAME record is required to define the print area and the repeat
1066      * rows and columns.
1067      *
1068      * A similar method is used in Worksheet.php for a slightly different purpose.
1069      *
1070      * @param integer $cxals Number of external references
1071      * @access private
1072      */
1073      function _storeExterncount($cxals)
1074      {
1075          $record   = 0x0016;          // Record identifier
1076          $length   = 0x0002;          // Number of bytes to follow
1077  
1078          $header   = pack("vv", $record, $length);
1079          $data     = pack("v",  $cxals);
1080          $this->_append($header . $data);
1081      }
1082  
1083  
1084      /**
1085      * Writes the Excel BIFF EXTERNSHEET record. These references are used by
1086      * formulas. NAME record is required to define the print area and the repeat
1087      * rows and columns.
1088      *
1089      * A similar method is used in Worksheet.php for a slightly different purpose.
1090      *
1091      * @param string $sheetname Worksheet name
1092      * @access private
1093      */
1094      function _storeExternsheet($sheetname)
1095      {
1096          $record      = 0x0017;                     // Record identifier
1097          $length      = 0x02 + strlen($sheetname);  // Number of bytes to follow
1098  
1099          $cch         = strlen($sheetname);         // Length of sheet name
1100          $rgch        = 0x03;                       // Filename encoding
1101  
1102          $header      = pack("vv",  $record, $length);
1103          $data        = pack("CC", $cch, $rgch);
1104          $this->_append($header . $data . $sheetname);
1105      }
1106  
1107  
1108      /**
1109      * Store the NAME record in the short format that is used for storing the print
1110      * area, repeat rows only and repeat columns only.
1111      *
1112      * @param integer $index  Sheet index
1113      * @param integer $type   Built-in name type
1114      * @param integer $rowmin Start row
1115      * @param integer $rowmax End row
1116      * @param integer $colmin Start colum
1117      * @param integer $colmax End column
1118      * @access private
1119      */
1120      function _storeNameShort($index, $type, $rowmin, $rowmax, $colmin, $colmax)
1121      {
1122          $record          = 0x0018;       // Record identifier
1123          $length          = 0x0024;       // Number of bytes to follow
1124  
1125          $grbit           = 0x0020;       // Option flags
1126          $chKey           = 0x00;         // Keyboard shortcut
1127          $cch             = 0x01;         // Length of text name
1128          $cce             = 0x0015;       // Length of text definition
1129          $ixals           = $index + 1;   // Sheet index
1130          $itab            = $ixals;       // Equal to ixals
1131          $cchCustMenu     = 0x00;         // Length of cust menu text
1132          $cchDescription  = 0x00;         // Length of description text
1133          $cchHelptopic    = 0x00;         // Length of help topic text
1134          $cchStatustext   = 0x00;         // Length of status bar text
1135          $rgch            = $type;        // Built-in name type
1136  
1137          $unknown03       = 0x3b;
1138          $unknown04       = 0xffff-$index;
1139          $unknown05       = 0x0000;
1140          $unknown06       = 0x0000;
1141          $unknown07       = 0x1087;
1142          $unknown08       = 0x8005;
1143  
1144          $header             = pack("vv", $record, $length);
1145          $data               = pack("v", $grbit);
1146          $data              .= pack("C", $chKey);
1147          $data              .= pack("C", $cch);
1148          $data              .= pack("v", $cce);
1149          $data              .= pack("v", $ixals);
1150          $data              .= pack("v", $itab);
1151          $data              .= pack("C", $cchCustMenu);
1152          $data              .= pack("C", $cchDescription);
1153          $data              .= pack("C", $cchHelptopic);
1154          $data              .= pack("C", $cchStatustext);
1155          $data              .= pack("C", $rgch);
1156          $data              .= pack("C", $unknown03);
1157          $data              .= pack("v", $unknown04);
1158          $data              .= pack("v", $unknown05);
1159          $data              .= pack("v", $unknown06);
1160          $data              .= pack("v", $unknown07);
1161          $data              .= pack("v", $unknown08);
1162          $data              .= pack("v", $index);
1163          $data              .= pack("v", $index);
1164          $data              .= pack("v", $rowmin);
1165          $data              .= pack("v", $rowmax);
1166          $data              .= pack("C", $colmin);
1167          $data              .= pack("C", $colmax);
1168          $this->_append($header . $data);
1169      }
1170  
1171  
1172      /**
1173      * Store the NAME record in the long format that is used for storing the repeat
1174      * rows and columns when both are specified. This shares a lot of code with
1175      * _storeNameShort() but we use a separate method to keep the code clean.
1176      * Code abstraction for reuse can be carried too far, and I should know. ;-)
1177      *
1178      * @param integer $index Sheet index
1179      * @param integer $type  Built-in name type
1180      * @param integer $rowmin Start row
1181      * @param integer $rowmax End row
1182      * @param integer $colmin Start colum
1183      * @param integer $colmax End column
1184      * @access private
1185      */
1186      function _storeNameLong($index, $type, $rowmin, $rowmax, $colmin, $colmax)
1187      {
1188          $record          = 0x0018;       // Record identifier
1189          $length          = 0x003d;       // Number of bytes to follow
1190          $grbit           = 0x0020;       // Option flags
1191          $chKey           = 0x00;         // Keyboard shortcut
1192          $cch             = 0x01;         // Length of text name
1193          $cce             = 0x002e;       // Length of text definition
1194          $ixals           = $index + 1;   // Sheet index
1195          $itab            = $ixals;       // Equal to ixals
1196          $cchCustMenu     = 0x00;         // Length of cust menu text
1197          $cchDescription  = 0x00;         // Length of description text
1198          $cchHelptopic    = 0x00;         // Length of help topic text
1199          $cchStatustext   = 0x00;         // Length of status bar text
1200          $rgch            = $type;        // Built-in name type
1201  
1202          $unknown01       = 0x29;
1203          $unknown02       = 0x002b;
1204          $unknown03       = 0x3b;
1205          $unknown04       = 0xffff-$index;
1206          $unknown05       = 0x0000;
1207          $unknown06       = 0x0000;
1208          $unknown07       = 0x1087;
1209          $unknown08       = 0x8008;
1210  
1211          $header             = pack("vv",  $record, $length);
1212          $data               = pack("v", $grbit);
1213          $data              .= pack("C", $chKey);
1214          $data              .= pack("C", $cch);
1215          $data              .= pack("v", $cce);
1216          $data              .= pack("v", $ixals);
1217          $data              .= pack("v", $itab);
1218          $data              .= pack("C", $cchCustMenu);
1219          $data              .= pack("C", $cchDescription);
1220          $data              .= pack("C", $cchHelptopic);
1221          $data              .= pack("C", $cchStatustext);
1222          $data              .= pack("C", $rgch);
1223          $data              .= pack("C", $unknown01);
1224          $data              .= pack("v", $unknown02);
1225          // Column definition
1226          $data              .= pack("C", $unknown03);
1227          $data              .= pack("v", $unknown04);
1228          $data              .= pack("v", $unknown05);
1229          $data              .= pack("v", $unknown06);
1230          $data              .= pack("v", $unknown07);
1231          $data              .= pack("v", $unknown08);
1232          $data              .= pack("v", $index);
1233          $data              .= pack("v", $index);
1234          $data              .= pack("v", 0x0000);
1235          $data              .= pack("v", 0x3fff);
1236          $data              .= pack("C", $colmin);
1237          $data              .= pack("C", $colmax);
1238          // Row definition
1239          $data              .= pack("C", $unknown03);
1240          $data              .= pack("v", $unknown04);
1241          $data              .= pack("v", $unknown05);
1242          $data              .= pack("v", $unknown06);
1243          $data              .= pack("v", $unknown07);
1244          $data              .= pack("v", $unknown08);
1245          $data              .= pack("v", $index);
1246          $data              .= pack("v", $index);
1247          $data              .= pack("v", $rowmin);
1248          $data              .= pack("v", $rowmax);
1249          $data              .= pack("C", 0x00);
1250          $data              .= pack("C", 0xff);
1251          // End of data
1252          $data              .= pack("C", 0x10);
1253          $this->_append($header . $data);
1254      }
1255  
1256      /**
1257      * Stores the COUNTRY record for localization
1258      *
1259      * @access private
1260      */
1261      function _storeCountry()
1262      {
1263          $record          = 0x008C;    // Record identifier
1264          $length          = 4;         // Number of bytes to follow
1265  
1266          $header = pack('vv',  $record, $length);
1267          /* using the same country code always for simplicity */
1268          $data = pack('vv', $this->_country_code, $this->_country_code);
1269          $this->_append($header . $data);
1270      }
1271  
1272      /**
1273      * Stores the PALETTE biff record.
1274      *
1275      * @access private
1276      */
1277      function _storePalette()
1278      {
1279          $aref            = $this->_palette;
1280  
1281          $record          = 0x0092;                 // Record identifier
1282          $length          = 2 + 4 * count($aref);   // Number of bytes to follow
1283          $ccv             =         count($aref);   // Number of RGB values to follow
1284          $data = '';                                // The RGB data
1285  
1286          // Pack the RGB data
1287          foreach ($aref as $color) {
1288              foreach ($color as $byte) {
1289                  $data .= pack("C",$byte);
1290              }
1291          }
1292  
1293          $header = pack("vvv",  $record, $length, $ccv);
1294          $this->_append($header . $data);
1295      }
1296  
1297      /**
1298      * Calculate
1299      * Handling of the SST continue blocks is complicated by the need to include an
1300      * additional continuation byte depending on whether the string is split between
1301      * blocks or whether it starts at the beginning of the block. (There are also
1302      * additional complications that will arise later when/if Rich Strings are
1303      * supported).
1304      *
1305      * @access private
1306      */
1307      function _calculateSharedStringsSizes()
1308      {
1309          /* Iterate through the strings to calculate the CONTINUE block sizes.
1310             For simplicity we use the same size for the SST and CONTINUE records:
1311             8228 : Maximum Excel97 block size
1312               -4 : Length of block header
1313               -8 : Length of additional SST header information
1314           = 8216
1315          */
1316          $continue_limit     = 8216;
1317          $block_length       = 0;
1318          $written            = 0;
1319          $this->_block_sizes = array();
1320          $continue           = 0;
1321  
1322          foreach (array_keys($this->_str_table) as $string) {
1323              $string_length = strlen($string);
1324  
1325              // Block length is the total length of the strings that will be
1326              // written out in a single SST or CONTINUE block.
1327              $block_length += $string_length;
1328  
1329              // We can write the string if it doesn't cross a CONTINUE boundary
1330              if ($block_length < $continue_limit) {
1331                  $written      += $string_length;
1332                  continue;
1333              }
1334  
1335              // Deal with the cases where the next string to be written will exceed
1336              // the CONTINUE boundary. If the string is very long it may need to be
1337              // written in more than one CONTINUE record.
1338              while ($block_length >= $continue_limit) {
1339  
1340                  // We need to avoid the case where a string is continued in the first
1341                  // n bytes that contain the string header information.
1342                  $header_length   = 3; // Min string + header size -1
1343                  $space_remaining = $continue_limit - $written - $continue;
1344  
1345  
1346                  /* TODO: Unicode data should only be split on char (2 byte)
1347                  boundaries. Therefore, in some cases we need to reduce the
1348                  amount of available
1349                  */
1350  
1351                  if ($space_remaining > $header_length) {
1352                      // Write as much as possible of the string in the current block
1353                      $written      += $space_remaining;
1354  
1355                      // Reduce the current block length by the amount written
1356                      $block_length -= $continue_limit - $continue;
1357  
1358                      // Store the max size for this block
1359                      $this->_block_sizes[] = $continue_limit;
1360  
1361                      // If the current string was split then the next CONTINUE block
1362                      // should have the string continue flag (grbit) set unless the
1363                      // split string fits exactly into the remaining space.
1364                      if ($block_length > 0) {
1365                          $continue = 1;
1366                      } else {
1367                          $continue = 0;
1368                      }
1369                  } else {
1370                      // Store the max size for this block
1371                      $this->_block_sizes[] = $written + $continue;
1372  
1373                      // Not enough space to start the string in the current block
1374                      $block_length -= $continue_limit - $space_remaining - $continue;
1375                      $continue = 0;
1376  
1377                  }
1378  
1379                  // If the string (or substr) is small enough we can write it in the
1380                  // new CONTINUE block. Else, go through the loop again to write it in
1381                  // one or more CONTINUE blocks
1382                  if ($block_length < $continue_limit) {
1383                      $written = $block_length;
1384                  } else {
1385                      $written = 0;
1386                  }
1387              }
1388          }
1389  
1390          // Store the max size for the last block unless it is empty
1391          if ($written + $continue) {
1392              $this->_block_sizes[] = $written + $continue;
1393          }
1394  
1395  
1396          /* Calculate the total length of the SST and associated CONTINUEs (if any).
1397           The SST record will have a length even if it contains no strings.
1398           This length is required to set the offsets in the BOUNDSHEET records since
1399           they must be written before the SST records
1400          */
1401          $total_offset = array_sum($this->_block_sizes);
1402          // SST information
1403          $total_offset += 8;
1404          if (!empty($this->_block_sizes)) {
1405              $total_offset += (count($this->_block_sizes)) * 4; // add CONTINUE headers
1406          }
1407          return $total_offset;
1408      }
1409  
1410      /**
1411      * Write all of the workbooks strings into an indexed array.
1412      * See the comments in _calculate_shared_string_sizes() for more information.
1413      *
1414      * The Excel documentation says that the SST record should be followed by an
1415      * EXTSST record. The EXTSST record is a hash table that is used to optimise
1416      * access to SST. However, despite the documentation it doesn't seem to be
1417      * required so we will ignore it.
1418      *
1419      * @access private
1420      */
1421      function _storeSharedStringsTable()
1422      {
1423          $record  = 0x00fc;  // Record identifier
1424          // sizes are upside down
1425          $this->_block_sizes = array_reverse($this->_block_sizes);
1426          $length = array_pop($this->_block_sizes) + 8; // First block size plus SST information
1427  
1428          // Write the SST block header information
1429          $header      = pack("vv", $record, $length);
1430          $data        = pack("VV", $this->_str_total, $this->_str_unique);
1431          $this->_append($header . $data);
1432  
1433  
1434          // Iterate through the strings to calculate the CONTINUE block sizes
1435          $continue_limit = 8216;
1436          $block_length   = 0;
1437          $written        = 0;
1438          $continue       = 0;
1439  
1440  
1441          /* TODO: not good for performance */
1442          foreach (array_keys($this->_str_table) as $string) {
1443  
1444              $string_length = strlen($string);
1445              $encoding      = 0; // assume there are no Unicode strings
1446              $split_string  = 0;
1447  
1448              // Block length is the total length of the strings that will be
1449              // written out in a single SST or CONTINUE block.
1450              //
1451              $block_length += $string_length;
1452  
1453  
1454              // We can write the string if it doesn't cross a CONTINUE boundary
1455              if ($block_length < $continue_limit) {
1456                  $this->_append($string);
1457                  $written += $string_length;
1458                  continue;
1459              }
1460  
1461              // Deal with the cases where the next string to be written will exceed
1462              // the CONTINUE boundary. If the string is very long it may need to be
1463              // written in more than one CONTINUE record.
1464              //
1465              while ($block_length >= $continue_limit) {
1466  
1467                  // We need to avoid the case where a string is continued in the first
1468                  // n bytes that contain the string header information.
1469                  //
1470                  $header_length   = 3; // Min string + header size -1
1471                  $space_remaining = $continue_limit - $written - $continue;
1472  
1473  
1474                  // Unicode data should only be split on char (2 byte) boundaries.
1475                  // Therefore, in some cases we need to reduce the amount of available
1476  
1477                  if ($space_remaining > $header_length) {
1478                      // Write as much as possible of the string in the current block
1479                      $tmp = substr($string, 0, $space_remaining);
1480                      $this->_append($tmp);
1481  
1482                      // The remainder will be written in the next block(s)
1483                      $string = substr($string, $space_remaining);
1484  
1485                      // Reduce the current block length by the amount written
1486                      $block_length -= $continue_limit - $continue;
1487  
1488                      // If the current string was split then the next CONTINUE block
1489                      // should have the string continue flag (grbit) set unless the
1490                      // split string fits exactly into the remaining space.
1491                      //
1492                      if ($block_length > 0) {
1493                          $continue = 1;
1494                      } else {
1495                          $continue = 0;
1496                      }
1497                  } else {
1498                      // Not enough space to start the string in the current block
1499                      $block_length -= $continue_limit - $space_remaining - $continue;
1500                      $continue = 0;
1501                  }
1502  
1503                  // Write the CONTINUE block header
1504                  if (!empty($this->_block_sizes)) {
1505                      $record  = 0x003C;
1506                      $length  = array_pop($this->_block_sizes);
1507                      $header  = pack('vv', $record, $length);
1508                      if ($continue) {
1509                          $header .= pack('C', $encoding);
1510                      }
1511                      $this->_append($header);
1512                  }
1513  
1514                  // If the string (or substr) is small enough we can write it in the
1515                  // new CONTINUE block. Else, go through the loop again to write it in
1516                  // one or more CONTINUE blocks
1517                  //
1518                  if ($block_length < $continue_limit) {
1519                      $this->_append($string);
1520                      $written = $block_length;
1521                  } else {
1522                      $written = 0;
1523                  }
1524              }
1525          }
1526      }
1527  }
1528  ?>