Subversion Repositories eFlore/Applications.cel

Rev

Rev 1604 | Rev 2552 | Go to most recent revision | Details | Compare with Previous | 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
 
1605 raphael 885
        $data = $header . $data;
886
		$this->_append($data);
1604 raphael 887
    }
888
 
889
    /**
418 aurelien 890
    * Write Excel BIFF WINDOW1 record.
891
    *
892
    * @access private
893
    */
894
    function _storeWindow1()
895
    {
896
        $record    = 0x003D;                 // Record identifier
897
        $length    = 0x0012;                 // Number of bytes to follow
1604 raphael 898
 
418 aurelien 899
        $xWn       = 0x0000;                 // Horizontal position of window
900
        $yWn       = 0x0000;                 // Vertical position of window
901
        $dxWn      = 0x25BC;                 // Width of window
902
        $dyWn      = 0x1572;                 // Height of window
1604 raphael 903
 
418 aurelien 904
        $grbit     = 0x0038;                 // Option flags
905
        $ctabsel   = $this->_selected;       // Number of workbook tabs selected
906
        $wTabRatio = 0x0258;                 // Tab to scrollbar ratio
1604 raphael 907
 
418 aurelien 908
        $itabFirst = $this->_firstsheet;     // 1st displayed worksheet
909
        $itabCur   = $this->_activesheet;    // Active worksheet
1604 raphael 910
 
418 aurelien 911
        $header    = pack("vv",        $record, $length);
912
        $data      = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,
913
                                       $grbit,
914
                                       $itabCur, $itabFirst,
915
                                       $ctabsel, $wTabRatio);
1605 raphael 916
        $data = $header . $data;
917
		$this->_append($data);
418 aurelien 918
    }
1604 raphael 919
 
418 aurelien 920
    /**
921
    * Writes Excel BIFF BOUNDSHEET record.
1604 raphael 922
    * FIXME: inconsistent with BIFF documentation
418 aurelien 923
    *
924
    * @param string  $sheetname Worksheet name
925
    * @param integer $offset    Location of worksheet BOF
926
    * @access private
927
    */
928
    function _storeBoundsheet($sheetname,$offset)
929
    {
930
        $record    = 0x0085;                    // Record identifier
1604 raphael 931
        if ($this->_BIFF_version == 0x0600) {
932
            $length    = 0x08 + strlen($sheetname); // Number of bytes to follow
933
        } else {
934
            $length = 0x07 + strlen($sheetname); // Number of bytes to follow
935
        }
936
 
937
        $grbit     = 0x0000;                    // Visibility and sheet type
938
        if ($this->_BIFF_version == 0x0600) {
939
            $cch       = mb_strlen($sheetname,'UTF-16LE'); // Length of sheet name
940
        } else {
941
            $cch       = strlen($sheetname);        // Length of sheet name
942
        }
943
 
418 aurelien 944
        $header    = pack("vv",  $record, $length);
1604 raphael 945
        if ($this->_BIFF_version == 0x0600) {
946
            $data      = pack("VvCC", $offset, $grbit, $cch, 0x1);
947
        } else {
948
            $data      = pack("VvC", $offset, $grbit, $cch);
949
        }
1605 raphael 950
 
951
		$data = $header.$data.$sheetname;
952
        $this->_append($data);
418 aurelien 953
    }
1604 raphael 954
 
418 aurelien 955
    /**
1604 raphael 956
    * Write Internal SUPBOOK record
957
    *
958
    * @access private
959
    */
960
    function _storeSupbookInternal()
961
    {
962
        $record    = 0x01AE;   // Record identifier
963
        $length    = 0x0004;   // Bytes to follow
964
 
965
        $header    = pack("vv", $record, $length);
966
        $data      = pack("vv", count($this->_worksheets), 0x0104);
1605 raphael 967
        $data = $header . $data;
968
		$this->_append($data);
1604 raphael 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
        }
1605 raphael 990
        $data = $header . $data;
991
		$this->_append($data);
1604 raphael 992
    }
993
 
994
    /**
418 aurelien 995
    * Write Excel BIFF STYLE records.
996
    *
997
    * @access private
998
    */
999
    function _storeStyle()
1000
    {
1001
        $record    = 0x0293;   // Record identifier
1002
        $length    = 0x0004;   // Bytes to follow
1604 raphael 1003
 
418 aurelien 1004
        $ixfe      = 0x8000;   // Index to style XF
1005
        $BuiltIn   = 0x00;     // Built-in style
1006
        $iLevel    = 0xff;     // Outline style level
1604 raphael 1007
 
418 aurelien 1008
        $header    = pack("vv",  $record, $length);
1009
        $data      = pack("vCC", $ixfe, $BuiltIn, $iLevel);
1605 raphael 1010
        $data = $header . $data;
1011
		$this->_append($data);
418 aurelien 1012
    }
1604 raphael 1013
 
1014
 
418 aurelien 1015
    /**
1016
    * Writes Excel FORMAT record for non "built-in" numerical formats.
1017
    *
1018
    * @param string  $format Custom format string
1019
    * @param integer $ifmt   Format index code
1020
    * @access private
1021
    */
1604 raphael 1022
    function _storeNumFormat($format, $ifmt)
418 aurelien 1023
    {
1024
        $record    = 0x041E;                      // Record identifier
1604 raphael 1025
 
1026
        if ($this->_BIFF_version == 0x0600) {
1027
            $length    = 5 + strlen($format);      // Number of bytes to follow
1028
            $encoding = 0x0;
1029
        } elseif ($this->_BIFF_version == 0x0500) {
1030
            $length    = 3 + strlen($format);      // Number of bytes to follow
1031
        }
1032
 
1033
        if ( $this->_BIFF_version == 0x0600 && function_exists('iconv') ) {     // Encode format String
1034
            if (mb_detect_encoding($format, 'auto') !== 'UTF-16LE'){
1035
                $format = iconv(mb_detect_encoding($format, 'auto'),'UTF-16LE',$format);
1036
            }
1037
            $encoding = 1;
1038
            $cch = function_exists('mb_strlen') ? mb_strlen($format, 'UTF-16LE') : (strlen($format) / 2);
1039
        } else {
1040
            $encoding = 0;
1041
            $cch  = strlen($format);             // Length of format string
1042
        }
1043
        $length = strlen($format);
1044
 
1045
        if ($this->_BIFF_version == 0x0600) {
1046
            $header    = pack("vv", $record, 5 + $length);
1047
            $data      = pack("vvC", $ifmt, $cch, $encoding);
1048
        } elseif ($this->_BIFF_version == 0x0500) {
1049
            $header    = pack("vv", $record, 3 + $length);
1050
            $data      = pack("vC", $ifmt, $cch);
1051
        }
1605 raphael 1052
		$data = $header . $data . $format;
1053
        $this->_append($data);
418 aurelien 1054
    }
1604 raphael 1055
 
418 aurelien 1056
    /**
1604 raphael 1057
    * Write DATEMODE record to indicate the date system in use (1904 or 1900).
418 aurelien 1058
    *
1059
    * @access private
1060
    */
1604 raphael 1061
    function _storeDatemode()
418 aurelien 1062
    {
1063
        $record    = 0x0022;         // Record identifier
1064
        $length    = 0x0002;         // Bytes to follow
1604 raphael 1065
 
418 aurelien 1066
        $f1904     = $this->_1904;   // Flag for 1904 date system
1604 raphael 1067
 
418 aurelien 1068
        $header    = pack("vv", $record, $length);
1069
        $data      = pack("v", $f1904);
1605 raphael 1070
        $data = $header . $data;
1071
		$this->_append($data);
418 aurelien 1072
    }
1604 raphael 1073
 
1074
 
418 aurelien 1075
    /**
1076
    * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
1077
    * references in the workbook.
1078
    *
1079
    * Excel only stores references to external sheets that are used in NAME.
1080
    * The workbook NAME record is required to define the print area and the repeat
1081
    * rows and columns.
1082
    *
1083
    * A similar method is used in Worksheet.php for a slightly different purpose.
1084
    *
1085
    * @param integer $cxals Number of external references
1086
    * @access private
1087
    */
1088
    function _storeExterncount($cxals)
1089
    {
1090
        $record   = 0x0016;          // Record identifier
1091
        $length   = 0x0002;          // Number of bytes to follow
1604 raphael 1092
 
418 aurelien 1093
        $header   = pack("vv", $record, $length);
1094
        $data     = pack("v",  $cxals);
1605 raphael 1095
        $data = $header . $data;
1096
		$this->_append($data);
418 aurelien 1097
    }
1604 raphael 1098
 
1099
 
418 aurelien 1100
    /**
1101
    * Writes the Excel BIFF EXTERNSHEET record. These references are used by
1102
    * formulas. NAME record is required to define the print area and the repeat
1103
    * rows and columns.
1104
    *
1105
    * A similar method is used in Worksheet.php for a slightly different purpose.
1106
    *
1107
    * @param string $sheetname Worksheet name
1108
    * @access private
1109
    */
1110
    function _storeExternsheet($sheetname)
1111
    {
1112
        $record      = 0x0017;                     // Record identifier
1113
        $length      = 0x02 + strlen($sheetname);  // Number of bytes to follow
1604 raphael 1114
 
418 aurelien 1115
        $cch         = strlen($sheetname);         // Length of sheet name
1116
        $rgch        = 0x03;                       // Filename encoding
1604 raphael 1117
 
418 aurelien 1118
        $header      = pack("vv",  $record, $length);
1119
        $data        = pack("CC", $cch, $rgch);
1605 raphael 1120
		$data = $header . $data . $sheetname;
1121
        $this->_append($data);
418 aurelien 1122
    }
1604 raphael 1123
 
1124
 
418 aurelien 1125
    /**
1126
    * Store the NAME record in the short format that is used for storing the print
1127
    * area, repeat rows only and repeat columns only.
1128
    *
1129
    * @param integer $index  Sheet index
1130
    * @param integer $type   Built-in name type
1131
    * @param integer $rowmin Start row
1132
    * @param integer $rowmax End row
1133
    * @param integer $colmin Start colum
1134
    * @param integer $colmax End column
1135
    * @access private
1136
    */
1604 raphael 1137
    function _storeNameShort($index, $type, $rowmin, $rowmax, $colmin, $colmax)
418 aurelien 1138
    {
1139
        $record          = 0x0018;       // Record identifier
1140
        $length          = 0x0024;       // Number of bytes to follow
1604 raphael 1141
 
418 aurelien 1142
        $grbit           = 0x0020;       // Option flags
1143
        $chKey           = 0x00;         // Keyboard shortcut
1144
        $cch             = 0x01;         // Length of text name
1145
        $cce             = 0x0015;       // Length of text definition
1146
        $ixals           = $index + 1;   // Sheet index
1147
        $itab            = $ixals;       // Equal to ixals
1148
        $cchCustMenu     = 0x00;         // Length of cust menu text
1149
        $cchDescription  = 0x00;         // Length of description text
1150
        $cchHelptopic    = 0x00;         // Length of help topic text
1151
        $cchStatustext   = 0x00;         // Length of status bar text
1152
        $rgch            = $type;        // Built-in name type
1604 raphael 1153
 
418 aurelien 1154
        $unknown03       = 0x3b;
1155
        $unknown04       = 0xffff-$index;
1156
        $unknown05       = 0x0000;
1157
        $unknown06       = 0x0000;
1158
        $unknown07       = 0x1087;
1159
        $unknown08       = 0x8005;
1604 raphael 1160
 
418 aurelien 1161
        $header             = pack("vv", $record, $length);
1162
        $data               = pack("v", $grbit);
1163
        $data              .= pack("C", $chKey);
1164
        $data              .= pack("C", $cch);
1165
        $data              .= pack("v", $cce);
1166
        $data              .= pack("v", $ixals);
1167
        $data              .= pack("v", $itab);
1168
        $data              .= pack("C", $cchCustMenu);
1169
        $data              .= pack("C", $cchDescription);
1170
        $data              .= pack("C", $cchHelptopic);
1171
        $data              .= pack("C", $cchStatustext);
1172
        $data              .= pack("C", $rgch);
1173
        $data              .= pack("C", $unknown03);
1174
        $data              .= pack("v", $unknown04);
1175
        $data              .= pack("v", $unknown05);
1176
        $data              .= pack("v", $unknown06);
1177
        $data              .= pack("v", $unknown07);
1178
        $data              .= pack("v", $unknown08);
1179
        $data              .= pack("v", $index);
1180
        $data              .= pack("v", $index);
1181
        $data              .= pack("v", $rowmin);
1182
        $data              .= pack("v", $rowmax);
1183
        $data              .= pack("C", $colmin);
1184
        $data              .= pack("C", $colmax);
1605 raphael 1185
        $data = $header . $data;
1186
		$this->_append($data);
418 aurelien 1187
    }
1604 raphael 1188
 
1189
 
418 aurelien 1190
    /**
1191
    * Store the NAME record in the long format that is used for storing the repeat
1192
    * rows and columns when both are specified. This shares a lot of code with
1193
    * _storeNameShort() but we use a separate method to keep the code clean.
1194
    * Code abstraction for reuse can be carried too far, and I should know. ;-)
1195
    *
1196
    * @param integer $index Sheet index
1197
    * @param integer $type  Built-in name type
1198
    * @param integer $rowmin Start row
1199
    * @param integer $rowmax End row
1200
    * @param integer $colmin Start colum
1201
    * @param integer $colmax End column
1202
    * @access private
1203
    */
1604 raphael 1204
    function _storeNameLong($index, $type, $rowmin, $rowmax, $colmin, $colmax)
418 aurelien 1205
    {
1206
        $record          = 0x0018;       // Record identifier
1207
        $length          = 0x003d;       // Number of bytes to follow
1208
        $grbit           = 0x0020;       // Option flags
1209
        $chKey           = 0x00;         // Keyboard shortcut
1210
        $cch             = 0x01;         // Length of text name
1211
        $cce             = 0x002e;       // Length of text definition
1212
        $ixals           = $index + 1;   // Sheet index
1213
        $itab            = $ixals;       // Equal to ixals
1214
        $cchCustMenu     = 0x00;         // Length of cust menu text
1215
        $cchDescription  = 0x00;         // Length of description text
1216
        $cchHelptopic    = 0x00;         // Length of help topic text
1217
        $cchStatustext   = 0x00;         // Length of status bar text
1218
        $rgch            = $type;        // Built-in name type
1604 raphael 1219
 
418 aurelien 1220
        $unknown01       = 0x29;
1221
        $unknown02       = 0x002b;
1222
        $unknown03       = 0x3b;
1223
        $unknown04       = 0xffff-$index;
1224
        $unknown05       = 0x0000;
1225
        $unknown06       = 0x0000;
1226
        $unknown07       = 0x1087;
1227
        $unknown08       = 0x8008;
1604 raphael 1228
 
418 aurelien 1229
        $header             = pack("vv",  $record, $length);
1230
        $data               = pack("v", $grbit);
1231
        $data              .= pack("C", $chKey);
1232
        $data              .= pack("C", $cch);
1233
        $data              .= pack("v", $cce);
1234
        $data              .= pack("v", $ixals);
1235
        $data              .= pack("v", $itab);
1236
        $data              .= pack("C", $cchCustMenu);
1237
        $data              .= pack("C", $cchDescription);
1238
        $data              .= pack("C", $cchHelptopic);
1239
        $data              .= pack("C", $cchStatustext);
1240
        $data              .= pack("C", $rgch);
1241
        $data              .= pack("C", $unknown01);
1242
        $data              .= pack("v", $unknown02);
1243
        // Column definition
1244
        $data              .= pack("C", $unknown03);
1245
        $data              .= pack("v", $unknown04);
1246
        $data              .= pack("v", $unknown05);
1247
        $data              .= pack("v", $unknown06);
1248
        $data              .= pack("v", $unknown07);
1249
        $data              .= pack("v", $unknown08);
1250
        $data              .= pack("v", $index);
1251
        $data              .= pack("v", $index);
1252
        $data              .= pack("v", 0x0000);
1253
        $data              .= pack("v", 0x3fff);
1254
        $data              .= pack("C", $colmin);
1255
        $data              .= pack("C", $colmax);
1256
        // Row definition
1257
        $data              .= pack("C", $unknown03);
1258
        $data              .= pack("v", $unknown04);
1259
        $data              .= pack("v", $unknown05);
1260
        $data              .= pack("v", $unknown06);
1261
        $data              .= pack("v", $unknown07);
1262
        $data              .= pack("v", $unknown08);
1263
        $data              .= pack("v", $index);
1264
        $data              .= pack("v", $index);
1265
        $data              .= pack("v", $rowmin);
1266
        $data              .= pack("v", $rowmax);
1267
        $data              .= pack("C", 0x00);
1268
        $data              .= pack("C", 0xff);
1269
        // End of data
1270
        $data              .= pack("C", 0x10);
1605 raphael 1271
        $data = $header . $data;
1272
		$this->_append($data);
418 aurelien 1273
    }
1604 raphael 1274
 
418 aurelien 1275
    /**
1604 raphael 1276
    * Stores the COUNTRY record for localization
1277
    *
1278
    * @access private
1279
    */
1280
    function _storeCountry()
1281
    {
1282
        $record          = 0x008C;    // Record identifier
1283
        $length          = 4;         // Number of bytes to follow
1284
 
1285
        $header = pack('vv',  $record, $length);
1286
        /* using the same country code always for simplicity */
1287
        $data = pack('vv', $this->_country_code, $this->_country_code);
1605 raphael 1288
        $data = $header . $data;
1289
		$this->_append($data);
1604 raphael 1290
    }
1291
 
1292
    /**
418 aurelien 1293
    * Stores the PALETTE biff record.
1294
    *
1295
    * @access private
1296
    */
1297
    function _storePalette()
1298
    {
1299
        $aref            = $this->_palette;
1604 raphael 1300
 
418 aurelien 1301
        $record          = 0x0092;                 // Record identifier
1302
        $length          = 2 + 4 * count($aref);   // Number of bytes to follow
1303
        $ccv             =         count($aref);   // Number of RGB values to follow
1304
        $data = '';                                // The RGB data
1604 raphael 1305
 
418 aurelien 1306
        // Pack the RGB data
1604 raphael 1307
        foreach ($aref as $color) {
1308
            foreach ($color as $byte) {
418 aurelien 1309
                $data .= pack("C",$byte);
1310
            }
1311
        }
1604 raphael 1312
 
418 aurelien 1313
        $header = pack("vvv",  $record, $length, $ccv);
1605 raphael 1314
        $data = $header . $data;
1315
		$this->_append($data);
418 aurelien 1316
    }
1604 raphael 1317
 
1318
    /**
1319
    * Calculate
1320
    * Handling of the SST continue blocks is complicated by the need to include an
1321
    * additional continuation byte depending on whether the string is split between
1322
    * blocks or whether it starts at the beginning of the block. (There are also
1323
    * additional complications that will arise later when/if Rich Strings are
1324
    * supported).
1325
    *
1326
    * @access private
1327
    */
1328
    function _calculateSharedStringsSizes()
1329
    {
1330
        /* Iterate through the strings to calculate the CONTINUE block sizes.
1331
           For simplicity we use the same size for the SST and CONTINUE records:
1332
           8228 : Maximum Excel97 block size
1333
             -4 : Length of block header
1334
             -8 : Length of additional SST header information
1335
             -8 : Arbitrary number to keep within _add_continue() limit = 8208
1336
        */
1337
        $continue_limit     = 8208;
1338
        $block_length       = 0;
1339
        $written            = 0;
1340
        $this->_block_sizes = array();
1341
        $continue           = 0;
1342
 
1343
        foreach (array_keys($this->_str_table) as $string) {
1344
            $string_length = strlen($string);
1345
            $headerinfo    = unpack("vlength/Cencoding", $string);
1346
            $encoding      = $headerinfo["encoding"];
1347
            $split_string  = 0;
1348
 
1349
            // Block length is the total length of the strings that will be
1350
            // written out in a single SST or CONTINUE block.
1351
            $block_length += $string_length;
1352
 
1353
            // We can write the string if it doesn't cross a CONTINUE boundary
1354
            if ($block_length < $continue_limit) {
1355
                $written      += $string_length;
1356
                continue;
1357
            }
1358
 
1359
            // Deal with the cases where the next string to be written will exceed
1360
            // the CONTINUE boundary. If the string is very long it may need to be
1361
            // written in more than one CONTINUE record.
1362
            while ($block_length >= $continue_limit) {
1363
 
1364
                // We need to avoid the case where a string is continued in the first
1365
                // n bytes that contain the string header information.
1366
                $header_length   = 3; // Min string + header size -1
1367
                $space_remaining = $continue_limit - $written - $continue;
1368
 
1369
 
1370
                /* TODO: Unicode data should only be split on char (2 byte)
1371
                boundaries. Therefore, in some cases we need to reduce the
1372
                amount of available
1373
                */
1374
                $align = 0;
1375
 
1376
                // Only applies to Unicode strings
1377
                if ($encoding == 1) {
1378
                    // Min string + header size -1
1379
                    $header_length = 4;
1380
 
1381
                    if ($space_remaining > $header_length) {
1382
                        // String contains 3 byte header => split on odd boundary
1383
                        if (!$split_string && $space_remaining % 2 != 1) {
1384
                            $space_remaining--;
1385
                            $align = 1;
1386
                        }
1387
                        // Split section without header => split on even boundary
1388
                        else if ($split_string && $space_remaining % 2 == 1) {
1389
                            $space_remaining--;
1390
                            $align = 1;
1391
                        }
1392
 
1393
                        $split_string = 1;
1394
                    }
1395
                }
1396
 
1397
 
1398
                if ($space_remaining > $header_length) {
1399
                    // Write as much as possible of the string in the current block
1400
                    $written      += $space_remaining;
1401
 
1402
                    // Reduce the current block length by the amount written
1403
                    $block_length -= $continue_limit - $continue - $align;
1404
 
1405
                    // Store the max size for this block
1406
                    $this->_block_sizes[] = $continue_limit - $align;
1407
 
1408
                    // If the current string was split then the next CONTINUE block
1409
                    // should have the string continue flag (grbit) set unless the
1410
                    // split string fits exactly into the remaining space.
1411
                    if ($block_length > 0) {
1412
                        $continue = 1;
1413
                    } else {
1414
                        $continue = 0;
1415
                    }
1416
                } else {
1417
                    // Store the max size for this block
1418
                    $this->_block_sizes[] = $written + $continue;
1419
 
1420
                    // Not enough space to start the string in the current block
1421
                    $block_length -= $continue_limit - $space_remaining - $continue;
1422
                    $continue = 0;
1423
 
1424
                }
1425
 
1426
                // If the string (or substr) is small enough we can write it in the
1427
                // new CONTINUE block. Else, go through the loop again to write it in
1428
                // one or more CONTINUE blocks
1429
                if ($block_length < $continue_limit) {
1430
                    $written = $block_length;
1431
                } else {
1432
                    $written = 0;
1433
                }
1434
            }
1435
        }
1436
 
1437
        // Store the max size for the last block unless it is empty
1438
        if ($written + $continue) {
1439
            $this->_block_sizes[] = $written + $continue;
1440
        }
1441
 
1442
 
1443
        /* Calculate the total length of the SST and associated CONTINUEs (if any).
1444
         The SST record will have a length even if it contains no strings.
1445
         This length is required to set the offsets in the BOUNDSHEET records since
1446
         they must be written before the SST records
1447
        */
1448
 
1449
        $tmp_block_sizes = array();
1450
        $tmp_block_sizes = $this->_block_sizes;
1451
 
1452
        $length  = 12;
1453
        if (!empty($tmp_block_sizes)) {
1454
            $length += array_shift($tmp_block_sizes); // SST
1455
        }
1456
        while (!empty($tmp_block_sizes)) {
1457
            $length += 4 + array_shift($tmp_block_sizes); // CONTINUEs
1458
        }
1459
 
1460
        return $length;
1461
    }
1462
 
1463
    /**
1464
    * Write all of the workbooks strings into an indexed array.
1465
    * See the comments in _calculate_shared_string_sizes() for more information.
1466
    *
1467
    * The Excel documentation says that the SST record should be followed by an
1468
    * EXTSST record. The EXTSST record is a hash table that is used to optimise
1469
    * access to SST. However, despite the documentation it doesn't seem to be
1470
    * required so we will ignore it.
1471
    *
1472
    * @access private
1473
    */
1474
    function _storeSharedStringsTable()
1475
    {
1476
        $record  = 0x00fc;  // Record identifier
1477
        $length  = 0x0008;  // Number of bytes to follow
1478
        $total   = 0x0000;
1479
 
1480
        // Iterate through the strings to calculate the CONTINUE block sizes
1481
        $continue_limit = 8208;
1482
        $block_length   = 0;
1483
        $written        = 0;
1484
        $continue       = 0;
1485
 
1486
        // sizes are upside down
1487
        $tmp_block_sizes = $this->_block_sizes;
1488
        // $tmp_block_sizes = array_reverse($this->_block_sizes);
1489
 
1490
        // The SST record is required even if it contains no strings. Thus we will
1491
        // always have a length
1492
        //
1493
        if (!empty($tmp_block_sizes)) {
1494
            $length = 8 + array_shift($tmp_block_sizes);
1495
        }
1496
        else {
1497
            // No strings
1498
            $length = 8;
1499
        }
1500
 
1501
 
1502
 
1503
        // Write the SST block header information
1504
        $header      = pack("vv", $record, $length);
1505
        $data        = pack("VV", $this->_str_total, $this->_str_unique);
1605 raphael 1506
        $data = $header . $data;
1507
		$this->_append($data);
1604 raphael 1508
 
1509
 
1510
 
1511
 
1512
        /* TODO: not good for performance */
1513
        foreach (array_keys($this->_str_table) as $string) {
1514
 
1515
            $string_length = strlen($string);
1516
            $headerinfo    = unpack("vlength/Cencoding", $string);
1517
            $encoding      = $headerinfo["encoding"];
1518
            $split_string  = 0;
1519
 
1520
            // Block length is the total length of the strings that will be
1521
            // written out in a single SST or CONTINUE block.
1522
            //
1523
            $block_length += $string_length;
1524
 
1525
 
1526
            // We can write the string if it doesn't cross a CONTINUE boundary
1527
            if ($block_length < $continue_limit) {
1528
                $this->_append($string);
1529
                $written += $string_length;
1530
                continue;
1531
            }
1532
 
1533
            // Deal with the cases where the next string to be written will exceed
1534
            // the CONTINUE boundary. If the string is very long it may need to be
1535
            // written in more than one CONTINUE record.
1536
            //
1537
            while ($block_length >= $continue_limit) {
1538
 
1539
                // We need to avoid the case where a string is continued in the first
1540
                // n bytes that contain the string header information.
1541
                //
1542
                $header_length   = 3; // Min string + header size -1
1543
                $space_remaining = $continue_limit - $written - $continue;
1544
 
1545
 
1546
                // Unicode data should only be split on char (2 byte) boundaries.
1547
                // Therefore, in some cases we need to reduce the amount of available
1548
                // space by 1 byte to ensure the correct alignment.
1549
                $align = 0;
1550
 
1551
                // Only applies to Unicode strings
1552
                if ($encoding == 1) {
1553
                    // Min string + header size -1
1554
                    $header_length = 4;
1555
 
1556
                    if ($space_remaining > $header_length) {
1557
                        // String contains 3 byte header => split on odd boundary
1558
                        if (!$split_string && $space_remaining % 2 != 1) {
1559
                            $space_remaining--;
1560
                            $align = 1;
1561
                        }
1562
                        // Split section without header => split on even boundary
1563
                        else if ($split_string && $space_remaining % 2 == 1) {
1564
                            $space_remaining--;
1565
                            $align = 1;
1566
                        }
1567
 
1568
                        $split_string = 1;
1569
                    }
1570
                }
1571
 
1572
 
1573
                if ($space_remaining > $header_length) {
1574
                    // Write as much as possible of the string in the current block
1575
                    $tmp = substr($string, 0, $space_remaining);
1576
                    $this->_append($tmp);
1577
 
1578
                    // The remainder will be written in the next block(s)
1579
                    $string = substr($string, $space_remaining);
1580
 
1581
                    // Reduce the current block length by the amount written
1582
                    $block_length -= $continue_limit - $continue - $align;
1583
 
1584
                    // If the current string was split then the next CONTINUE block
1585
                    // should have the string continue flag (grbit) set unless the
1586
                    // split string fits exactly into the remaining space.
1587
                    //
1588
                    if ($block_length > 0) {
1589
                        $continue = 1;
1590
                    } else {
1591
                        $continue = 0;
1592
                    }
1593
                } else {
1594
                    // Not enough space to start the string in the current block
1595
                    $block_length -= $continue_limit - $space_remaining - $continue;
1596
                    $continue = 0;
1597
                }
1598
 
1599
                // Write the CONTINUE block header
1600
                if (!empty($this->_block_sizes)) {
1601
                    $record  = 0x003C;
1602
                    $length  = array_shift($tmp_block_sizes);
1603
 
1604
                    $header  = pack('vv', $record, $length);
1605
                    if ($continue) {
1606
                        $header .= pack('C', $encoding);
1607
                    }
1608
                    $this->_append($header);
1609
                }
1610
 
1611
                // If the string (or substr) is small enough we can write it in the
1612
                // new CONTINUE block. Else, go through the loop again to write it in
1613
                // one or more CONTINUE blocks
1614
                //
1615
                if ($block_length < $continue_limit) {
1616
                    $this->_append($string);
1617
                    $written = $block_length;
1618
                } else {
1619
                    $written = 0;
1620
                }
1621
            }
1622
        }
1623
    }
1624
 
1625
 
418 aurelien 1626
}
1604 raphael 1627