Subversion Repositories eFlore/Applications.cel

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

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