3
* Module written/ported by Xavier Noguer <xnoguer@rezebra.com>
5
* The majority of this is _NOT_ my code. I simply ported it from the
6
* PERL Spreadsheet::WriteExcel module.
8
* The author of the Spreadsheet::WriteExcel module is John McNamara
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.
15
* License Information:
17
* Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets
18
* Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com
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.
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.
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
35
if (isset($_REQUEST['homedir'])) {die('You cannot start this script directly');}
36
require_once $homedir.'/classes/pear/Spreadsheet/Excel/Writer/Format.php';
37
require_once $homedir.'/classes/pear/Spreadsheet/Excel/Writer/BIFFwriter.php';
38
require_once $homedir.'/classes/pear/Spreadsheet/Excel/Writer/Worksheet.php';
39
require_once $homedir.'/classes/pear/Spreadsheet/Excel/Writer/Parser.php';
40
require_once $homedir.'/classes/pear/OLE/PPS/Root.php';
41
require_once $homedir.'/classes/pear/OLE/PPS/File.php';
44
* Class for generating Excel Spreadsheets
46
* @author Xavier Noguer <xnoguer@rezebra.com>
47
* @category FileFormats
48
* @package Spreadsheet_Excel_Writer
51
class Spreadsheet_Excel_Writer_Workbook extends Spreadsheet_Excel_Writer_BIFFwriter
54
* Filename for the Workbook
66
* Flag for 1904 date system (0 => base date is 1900, 1 => base date is 1904)
72
* The active worksheet of the workbook (0 indexed)
78
* 1st displayed worksheet in the workbook (0 indexed)
84
* Number of workbook tabs selected
90
* Index for creating adding new formats to the workbook
96
* Flag for preventing close from being called twice.
103
* The BIFF file size for the workbook.
105
* @see _calcSheetOffsets()
110
* The default sheetname for all sheets created.
116
* The default XF format.
122
* Array containing references to all of this workbook's worksheets
128
* Array of sheetnames for creating the EXTERNSHEET records
134
* Array containing references to all of this workbook's formats
140
* Array containing the colour palette
146
* The default format for URLs.
152
* The codepage indicates the text encoding used for strings
158
* The country code used for localization
164
* The temporary dir for storing the OLE file
170
* number of bytes for sizeinfo of strings
173
var $_string_sizeinfo_size;
178
* @param string filename for storing the workbook. "-" for writing to stdout.
181
function Spreadsheet_Excel_Writer_Workbook($filename)
183
// It needs to call its parent's constructor explicitly
184
$this->Spreadsheet_Excel_Writer_BIFFwriter();
186
$this->_filename = $filename;
187
$this->_parser =& new Spreadsheet_Excel_Writer_Parser($this->_byte_order, $this->_BIFF_version);
189
$this->_activesheet = 0;
190
$this->_firstsheet = 0;
191
$this->_selected = 0;
192
$this->_xf_index = 16; // 15 style XF's and 1 cell XF.
193
$this->_fileclosed = 0;
194
$this->_biffsize = 0;
195
$this->_sheetname = 'Sheet';
196
$this->_tmp_format =& new Spreadsheet_Excel_Writer_Format($this->_BIFF_version);
197
$this->_worksheets = array();
198
$this->_sheetnames = array();
199
$this->_formats = array();
200
$this->_palette = array();
201
$this->_codepage = 0x04E4; // FIXME: should change for BIFF8
202
$this->_country_code = -1;
203
$this->_string_sizeinfo = 3;
205
// Add the default format for hyperlinks
206
$this->_url_format =& $this->addFormat(array('color' => 'blue', 'underline' => 1));
207
$this->_str_total = 0;
208
$this->_str_unique = 0;
209
$this->_str_table = array();
210
$this->_setPaletteXl97();
211
$this->_tmp_dir = '';
215
* Calls finalization methods.
216
* This method should always be the last one to be called on every workbook
219
* @return mixed true on success. PEAR_Error on failure
223
if ($this->_fileclosed) { // Prevent close() from being called twice.
226
$res = $this->_storeWorkbook();
227
if ($this->isError($res)) {
228
return $this->raiseError($res->getMessage());
230
$this->_fileclosed = 1;
235
* An accessor for the _worksheets[] array
236
* Returns an array of the worksheet objects in a workbook
237
* It actually calls to worksheets()
245
return $this->worksheets();
249
* An accessor for the _worksheets[] array.
250
* Returns an array of the worksheet objects in a workbook
255
function worksheets()
257
return $this->_worksheets;
261
* Sets the BIFF version.
262
* This method exists just to access experimental functionality
263
* from BIFF8. It will be deprecated !
264
* Only possible value is 8 (Excel 97/2000).
265
* For any other value it fails silently.
268
* @param integer $version The BIFF version
270
function setVersion($version)
272
if ($version == 8) { // only accept version 8
274
$this->_BIFF_version = $version;
275
// change BIFFwriter limit for CONTINUE records
276
$this->_limit = 8228;
277
$this->_tmp_format->_BIFF_version = $version;
278
$this->_url_format->_BIFF_version = $version;
279
$this->_parser->_BIFF_version = $version;
281
$total_worksheets = count($this->_worksheets);
282
// change version for all worksheets too
283
for ($i = 0; $i < $total_worksheets; $i++) {
284
$this->_worksheets[$i]->_BIFF_version = $version;
287
$total_formats = count($this->_formats);
288
// change version for all formats too
289
for ($i = 0; $i < $total_formats; $i++) {
290
$this->_formats[$i]->_BIFF_version = $version;
296
* Set the country identifier for the workbook
299
* @param integer $code Is the international calling country code for the
302
function setCountry($code)
304
$this->_country_code = $code;
308
* Add a new worksheet to the Excel workbook.
309
* If no name is given the name of the worksheet will be Sheeti$i, with
313
* @param string $name the optional name of the worksheet
314
* @return mixed reference to a worksheet object on success, PEAR_Error
317
function &addWorksheet($name = '')
319
$index = count($this->_worksheets);
320
$sheetname = $this->_sheetname;
323
$name = $sheetname.($index+1);
326
// Check that sheetname is <= 31 chars (Excel limit before BIFF8).
327
if ($this->_BIFF_version != 0x0600)
329
if (strlen($name) > 31) {
330
return $this->raiseError("Sheetname $name must be <= 31 chars");
334
// Check that the worksheet name doesn't already exist: a fatal Excel error.
335
$total_worksheets = count($this->_worksheets);
336
for ($i = 0; $i < $total_worksheets; $i++) {
337
if ($this->_worksheets[$i]->getName() == $name) {
338
return $this->raiseError("Worksheet '$name' already exists");
342
$worksheet = new Spreadsheet_Excel_Writer_Worksheet($this->_BIFF_version,
344
$this->_activesheet, $this->_firstsheet,
345
$this->_str_total, $this->_str_unique,
346
$this->_str_table, $this->_url_format,
349
$this->_worksheets[$index] = &$worksheet; // Store ref for iterator
350
$this->_sheetnames[$index] = $name; // Store EXTERNSHEET names
351
$this->_parser->setExtSheet($name, $index); // Register worksheet name with parser
356
* Add a new format to the Excel workbook.
357
* Also, pass any properties to the Format constructor.
360
* @param array $properties array with properties for initializing the format.
361
* @return &Spreadsheet_Excel_Writer_Format reference to an Excel Format
363
function &addFormat($properties = array())
365
$format = new Spreadsheet_Excel_Writer_Format($this->_BIFF_version, $this->_xf_index, $properties);
366
$this->_xf_index += 1;
367
$this->_formats[] = &$format;
372
* Create new validator.
375
* @return &Spreadsheet_Excel_Writer_Validator reference to a Validator
377
function &addValidator()
379
include_once 'Spreadsheet/Excel/Writer/Validator.php';
380
/* FIXME: check for successful inclusion*/
381
$valid = new Spreadsheet_Excel_Writer_Validator($this->_parser);
386
* Change the RGB components of the elements in the colour palette.
389
* @param integer $index colour index
390
* @param integer $red red RGB value [0-255]
391
* @param integer $green green RGB value [0-255]
392
* @param integer $blue blue RGB value [0-255]
393
* @return integer The palette index for the custom color
395
function setCustomColor($index, $red, $green, $blue)
397
// Match a HTML #xxyyzz style parameter
398
/*if (defined $_[1] and $_[1] =~ /^#(\w\w)(\w\w)(\w\w)/ ) {
399
@_ = ($_[0], hex $1, hex $2, hex $3);
402
// Check that the colour index is the right range
403
if ($index < 8 or $index > 64) {
404
// TODO: assign real error codes
405
return $this->raiseError("Color index $index outside range: 8 <= index <= 64");
408
// Check that the colour components are in the right range
409
if (($red < 0 or $red > 255) ||
410
($green < 0 or $green > 255) ||
411
($blue < 0 or $blue > 255))
413
return $this->raiseError("Color component outside range: 0 <= color <= 255");
416
$index -= 8; // Adjust colour index (wingless dragonfly)
419
$this->_palette[$index] = array($red, $green, $blue, 0);
424
* Sets the colour palette to the Excel 97+ default.
428
function _setPaletteXl97()
430
$this->_palette = array(
431
array(0x00, 0x00, 0x00, 0x00), // 8
432
array(0xff, 0xff, 0xff, 0x00), // 9
433
array(0xff, 0x00, 0x00, 0x00), // 10
434
array(0x00, 0xff, 0x00, 0x00), // 11
435
array(0x00, 0x00, 0xff, 0x00), // 12
436
array(0xff, 0xff, 0x00, 0x00), // 13
437
array(0xff, 0x00, 0xff, 0x00), // 14
438
array(0x00, 0xff, 0xff, 0x00), // 15
439
array(0x80, 0x00, 0x00, 0x00), // 16
440
array(0x00, 0x80, 0x00, 0x00), // 17
441
array(0x00, 0x00, 0x80, 0x00), // 18
442
array(0x80, 0x80, 0x00, 0x00), // 19
443
array(0x80, 0x00, 0x80, 0x00), // 20
444
array(0x00, 0x80, 0x80, 0x00), // 21
445
array(0xc0, 0xc0, 0xc0, 0x00), // 22
446
array(0x80, 0x80, 0x80, 0x00), // 23
447
array(0x99, 0x99, 0xff, 0x00), // 24
448
array(0x99, 0x33, 0x66, 0x00), // 25
449
array(0xff, 0xff, 0xcc, 0x00), // 26
450
array(0xcc, 0xff, 0xff, 0x00), // 27
451
array(0x66, 0x00, 0x66, 0x00), // 28
452
array(0xff, 0x80, 0x80, 0x00), // 29
453
array(0x00, 0x66, 0xcc, 0x00), // 30
454
array(0xcc, 0xcc, 0xff, 0x00), // 31
455
array(0x00, 0x00, 0x80, 0x00), // 32
456
array(0xff, 0x00, 0xff, 0x00), // 33
457
array(0xff, 0xff, 0x00, 0x00), // 34
458
array(0x00, 0xff, 0xff, 0x00), // 35
459
array(0x80, 0x00, 0x80, 0x00), // 36
460
array(0x80, 0x00, 0x00, 0x00), // 37
461
array(0x00, 0x80, 0x80, 0x00), // 38
462
array(0x00, 0x00, 0xff, 0x00), // 39
463
array(0x00, 0xcc, 0xff, 0x00), // 40
464
array(0xcc, 0xff, 0xff, 0x00), // 41
465
array(0xcc, 0xff, 0xcc, 0x00), // 42
466
array(0xff, 0xff, 0x99, 0x00), // 43
467
array(0x99, 0xcc, 0xff, 0x00), // 44
468
array(0xff, 0x99, 0xcc, 0x00), // 45
469
array(0xcc, 0x99, 0xff, 0x00), // 46
470
array(0xff, 0xcc, 0x99, 0x00), // 47
471
array(0x33, 0x66, 0xff, 0x00), // 48
472
array(0x33, 0xcc, 0xcc, 0x00), // 49
473
array(0x99, 0xcc, 0x00, 0x00), // 50
474
array(0xff, 0xcc, 0x00, 0x00), // 51
475
array(0xff, 0x99, 0x00, 0x00), // 52
476
array(0xff, 0x66, 0x00, 0x00), // 53
477
array(0x66, 0x66, 0x99, 0x00), // 54
478
array(0x96, 0x96, 0x96, 0x00), // 55
479
array(0x00, 0x33, 0x66, 0x00), // 56
480
array(0x33, 0x99, 0x66, 0x00), // 57
481
array(0x00, 0x33, 0x00, 0x00), // 58
482
array(0x33, 0x33, 0x00, 0x00), // 59
483
array(0x99, 0x33, 0x00, 0x00), // 60
484
array(0x99, 0x33, 0x66, 0x00), // 61
485
array(0x33, 0x33, 0x99, 0x00), // 62
486
array(0x33, 0x33, 0x33, 0x00), // 63
491
* Assemble worksheets into a workbook and send the BIFF data to an OLE
495
* @return mixed true on success. PEAR_Error on failure
497
function _storeWorkbook()
499
// Ensure that at least one worksheet has been selected.
500
if ($this->_activesheet == 0) {
501
$this->_worksheets[0]->selected = 1;
504
// Calculate the number of selected worksheet tabs and call the finalization
505
// methods for each worksheet
506
$total_worksheets = count($this->_worksheets);
507
for ($i = 0; $i < $total_worksheets; $i++) {
508
if ($this->_worksheets[$i]->selected) {
511
$this->_worksheets[$i]->close($this->_sheetnames);
514
// Add Workbook globals
515
$this->_storeBof(0x0005);
516
$this->_storeCodepage();
517
if ($this->_BIFF_version == 0x0600) {
518
$this->_storeWindow1();
520
if ($this->_BIFF_version == 0x0500) {
521
$this->_storeExterns(); // For print area and repeat rows
523
$this->_storeNames(); // For print area and repeat rows
524
if ($this->_BIFF_version == 0x0500) {
525
$this->_storeWindow1();
527
$this->_storeDatemode();
528
$this->_storeAllFonts();
529
$this->_storeAllNumFormats();
530
$this->_storeAllXfs();
531
$this->_storeAllStyles();
532
$this->_storePalette();
533
$this->_calcSheetOffsets();
535
// Add BOUNDSHEET records
536
for ($i = 0; $i < $total_worksheets; $i++) {
537
$this->_storeBoundsheet($this->_worksheets[$i]->name,$this->_worksheets[$i]->offset);
540
if ($this->_country_code != -1) {
541
$this->_storeCountry();
544
if ($this->_BIFF_version == 0x0600) {
545
//$this->_storeSupbookInternal();
546
/* TODO: store external SUPBOOK records and XCT and CRN records
547
in case of external references for BIFF8 */
548
//$this->_storeExternsheetBiff8();
549
$this->_storeSharedStringsTable();
552
// End Workbook globals
555
// Store the workbook in an OLE container
556
$res = $this->_storeOLEFile();
557
if ($this->isError($res)) {
558
return $this->raiseError($res->getMessage());
564
* Sets the temp dir used for storing the OLE file
567
* @param string $dir The dir to be used as temp dir
568
* @return true if given dir is valid, false otherwise
570
function setTempDir($dir)
573
$this->_tmp_dir = $dir;
580
* Store the workbook in an OLE container
583
* @return mixed true on success. PEAR_Error on failure
585
function _storeOLEFile()
587
$OLE = new OLE_PPS_File(OLE::Asc2Ucs('Book'));
588
if ($this->_tmp_dir != '') {
589
$OLE->setTempDir($this->_tmp_dir);
592
if ($this->isError($res)) {
593
return $this->raiseError("OLE Error: ".$res->getMessage());
595
$OLE->append($this->_data);
597
$total_worksheets = count($this->_worksheets);
598
for ($i = 0; $i < $total_worksheets; $i++) {
599
while ($tmp = $this->_worksheets[$i]->getData()) {
604
$root = new OLE_PPS_Root(time(), time(), array($OLE));
605
if ($this->_tmp_dir != '') {
606
$root->setTempDir($this->_tmp_dir);
609
$res = $root->save($this->_filename);
610
if ($this->isError($res)) {
611
return $this->raiseError("OLE Error: ".$res->getMessage());
617
* Calculate offsets for Worksheet BOF records.
621
function _calcSheetOffsets()
623
if ($this->_BIFF_version == 0x0600) {
624
$boundsheet_length = 12; // fixed length for a BOUNDSHEET record
626
$boundsheet_length = 11;
629
$offset = $this->_datasize;
631
if ($this->_BIFF_version == 0x0600) {
632
// add the length of the SST
633
/* TODO: check this works for a lot of strings (> 8224 bytes) */
634
$offset += $this->_calculateSharedStringsSizes();
635
if ($this->_country_code != -1) {
636
$offset += 8; // adding COUNTRY record
638
// add the lenght of SUPBOOK, EXTERNSHEET and NAME records
639
//$offset += 8; // FIXME: calculate real value when storing the records
641
$total_worksheets = count($this->_worksheets);
642
// add the length of the BOUNDSHEET records
643
for ($i = 0; $i < $total_worksheets; $i++) {
644
$offset += $boundsheet_length + strlen($this->_worksheets[$i]->name);
648
for ($i = 0; $i < $total_worksheets; $i++) {
649
$this->_worksheets[$i]->offset = $offset;
650
$offset += $this->_worksheets[$i]->_datasize;
652
$this->_biffsize = $offset;
656
* Store the Excel FONT records.
660
function _storeAllFonts()
662
// tmp_format is added by the constructor. We use this to write the default XF's
663
$format = $this->_tmp_format;
664
$font = $format->getFont();
666
// Note: Fonts are 0-indexed. According to the SDK there is no index 4,
667
// so the following fonts are 0, 1, 2, 3, 5
669
for ($i = 1; $i <= 5; $i++){
670
$this->_append($font);
673
// Iterate through the XF objects and write a FONT record if it isn't the
674
// same as the default FONT and if it hasn't already been used.
677
$index = 6; // The first user defined FONT
679
$key = $format->getFontKey(); // The default font from _tmp_format
680
$fonts[$key] = 0; // Index of the default font
682
$total_formats = count($this->_formats);
683
for ($i = 0; $i < $total_formats; $i++) {
684
$key = $this->_formats[$i]->getFontKey();
685
if (isset($fonts[$key])) {
686
// FONT has already been used
687
$this->_formats[$i]->font_index = $fonts[$key];
689
// Add a new FONT record
690
$fonts[$key] = $index;
691
$this->_formats[$i]->font_index = $index;
693
$font = $this->_formats[$i]->getFont();
694
$this->_append($font);
700
* Store user defined numerical formats i.e. FORMAT records
704
function _storeAllNumFormats()
706
// Leaning num_format syndrome
707
$hash_num_formats = array();
708
$num_formats = array();
711
// Iterate through the XF objects and write a FORMAT record if it isn't a
712
// built-in format type and if the FORMAT string hasn't already been used.
713
$total_formats = count($this->_formats);
714
for ($i = 0; $i < $total_formats; $i++) {
715
$num_format = $this->_formats[$i]->_num_format;
717
// Check if $num_format is an index to a built-in format.
718
// Also check for a string of zeros, which is a valid format string
719
// but would evaluate to zero.
721
if (!preg_match("/^0+\d/", $num_format)) {
722
if (preg_match("/^\d+$/", $num_format)) { // built-in format
727
if (isset($hash_num_formats[$num_format])) {
728
// FORMAT has already been used
729
$this->_formats[$i]->_num_format = $hash_num_formats[$num_format];
732
$hash_num_formats[$num_format] = $index;
733
$this->_formats[$i]->_num_format = $index;
734
array_push($num_formats,$num_format);
739
// Write the new FORMAT records starting from 0xA4
741
foreach ($num_formats as $num_format) {
742
$this->_storeNumFormat($num_format,$index);
748
* Write all XF records.
752
function _storeAllXfs()
754
// _tmp_format is added by the constructor. We use this to write the default XF's
755
// The default font index is 0
757
$format = $this->_tmp_format;
758
for ($i = 0; $i <= 14; $i++) {
759
$xf = $format->getXf('style'); // Style XF
763
$xf = $format->getXf('cell'); // Cell XF
767
$total_formats = count($this->_formats);
768
for ($i = 0; $i < $total_formats; $i++) {
769
$xf = $this->_formats[$i]->getXf('cell');
775
* Write all STYLE records.
779
function _storeAllStyles()
781
$this->_storeStyle();
785
* Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for
790
function _storeExterns()
792
// Create EXTERNCOUNT with number of worksheets
793
$this->_storeExterncount(count($this->_worksheets));
795
// Create EXTERNSHEET for each worksheet
796
foreach ($this->_sheetnames as $sheetname) {
797
$this->_storeExternsheet($sheetname);
802
* Write the NAME record to define the print area and the repeat rows and cols.
806
function _storeNames()
808
// Create the print area NAME records
809
$total_worksheets = count($this->_worksheets);
810
for ($i = 0; $i < $total_worksheets; $i++) {
811
// Write a Name record if the print area has been defined
812
if (isset($this->_worksheets[$i]->print_rowmin)) {
813
$this->_storeNameShort(
814
$this->_worksheets[$i]->index,
816
$this->_worksheets[$i]->print_rowmin,
817
$this->_worksheets[$i]->print_rowmax,
818
$this->_worksheets[$i]->print_colmin,
819
$this->_worksheets[$i]->print_colmax
824
// Create the print title NAME records
825
$total_worksheets = count($this->_worksheets);
826
for ($i = 0; $i < $total_worksheets; $i++) {
827
$rowmin = $this->_worksheets[$i]->title_rowmin;
828
$rowmax = $this->_worksheets[$i]->title_rowmax;
829
$colmin = $this->_worksheets[$i]->title_colmin;
830
$colmax = $this->_worksheets[$i]->title_colmax;
832
// Determine if row + col, row, col or nothing has been defined
833
// and write the appropriate record
835
if (isset($rowmin) && isset($colmin)) {
836
// Row and column titles have been defined.
837
// Row title has been defined.
838
$this->_storeNameLong(
839
$this->_worksheets[$i]->index,
846
} elseif (isset($rowmin)) {
847
// Row title has been defined.
848
$this->_storeNameShort(
849
$this->_worksheets[$i]->index,
856
} elseif (isset($colmin)) {
857
// Column title has been defined.
858
$this->_storeNameShort(
859
$this->_worksheets[$i]->index,
867
// Print title hasn't been defined.
875
/******************************************************************************
882
* Stores the CODEPAGE biff record.
886
function _storeCodepage()
888
$record = 0x0042; // Record identifier
889
$length = 0x0002; // Number of bytes to follow
890
$cv = $this->_codepage; // The code page
892
$header = pack('vv', $record, $length);
893
$data = pack('v', $cv);
895
$this->_append($header . $data);
899
* Write Excel BIFF WINDOW1 record.
903
function _storeWindow1()
905
$record = 0x003D; // Record identifier
906
$length = 0x0012; // Number of bytes to follow
908
$xWn = 0x0000; // Horizontal position of window
909
$yWn = 0x0000; // Vertical position of window
910
$dxWn = 0x25BC; // Width of window
911
$dyWn = 0x1572; // Height of window
913
$grbit = 0x0038; // Option flags
914
$ctabsel = $this->_selected; // Number of workbook tabs selected
915
$wTabRatio = 0x0258; // Tab to scrollbar ratio
917
$itabFirst = $this->_firstsheet; // 1st displayed worksheet
918
$itabCur = $this->_activesheet; // Active worksheet
920
$header = pack("vv", $record, $length);
921
$data = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,
923
$itabCur, $itabFirst,
924
$ctabsel, $wTabRatio);
925
$this->_append($header . $data);
929
* Writes Excel BIFF BOUNDSHEET record.
930
* FIXME: inconsistent with BIFF documentation
932
* @param string $sheetname Worksheet name
933
* @param integer $offset Location of worksheet BOF
936
function _storeBoundsheet($sheetname,$offset)
938
$record = 0x0085; // Record identifier
939
if ($this->_BIFF_version == 0x0600) {
940
$length = 0x08 + strlen($sheetname); // Number of bytes to follow
942
$length = 0x07 + strlen($sheetname); // Number of bytes to follow
945
$grbit = 0x0000; // Visibility and sheet type
946
$cch = strlen($sheetname); // Length of sheet name
948
$header = pack("vv", $record, $length);
949
if ($this->_BIFF_version == 0x0600) {
950
$data = pack("Vvv", $offset, $grbit, $cch);
952
$data = pack("VvC", $offset, $grbit, $cch);
954
$this->_append($header.$data.$sheetname);
958
* Write Internal SUPBOOK record
962
function _storeSupbookInternal()
964
$record = 0x01AE; // Record identifier
965
$length = 0x0004; // Bytes to follow
967
$header = pack("vv", $record, $length);
968
$data = pack("vv", count($this->_worksheets), 0x0104);
969
$this->_append($header . $data);
973
* Writes the Excel BIFF EXTERNSHEET record. These references are used by
976
* @param string $sheetname Worksheet name
979
function _storeExternsheetBiff8()
981
$total_references = count($this->_parser->_references);
982
$record = 0x0017; // Record identifier
983
$length = 2 + 6 * $total_references; // Number of bytes to follow
985
$supbook_index = 0; // FIXME: only using internal SUPBOOK record
986
$header = pack("vv", $record, $length);
987
$data = pack('v', $total_references);
988
for ($i = 0; $i < $total_references; $i++) {
989
$data .= $this->_parser->_references[$i];
991
$this->_append($header . $data);
995
* Write Excel BIFF STYLE records.
999
function _storeStyle()
1001
$record = 0x0293; // Record identifier
1002
$length = 0x0004; // Bytes to follow
1004
$ixfe = 0x8000; // Index to style XF
1005
$BuiltIn = 0x00; // Built-in style
1006
$iLevel = 0xff; // Outline style level
1008
$header = pack("vv", $record, $length);
1009
$data = pack("vCC", $ixfe, $BuiltIn, $iLevel);
1010
$this->_append($header . $data);
1015
* Writes Excel FORMAT record for non "built-in" numerical formats.
1017
* @param string $format Custom format string
1018
* @param integer $ifmt Format index code
1021
function _storeNumFormat($format, $ifmt)
1023
$record = 0x041E; // Record identifier
1025
if ($this->_BIFF_version == 0x0600) {
1026
$length = 5 + strlen($format); // Number of bytes to follow
1028
} elseif ($this->_BIFF_version == 0x0500) {
1029
$length = 3 + strlen($format); // Number of bytes to follow
1032
$cch = strlen($format); // Length of format string
1034
$header = pack("vv", $record, $length);
1035
if ($this->_BIFF_version == 0x0600) {
1036
$data = pack("vvC", $ifmt, $cch, $encoding);
1037
} elseif ($this->_BIFF_version == 0x0500) {
1038
$data = pack("vC", $ifmt, $cch);
1040
$this->_append($header . $data . $format);
1044
* Write DATEMODE record to indicate the date system in use (1904 or 1900).
1048
function _storeDatemode()
1050
$record = 0x0022; // Record identifier
1051
$length = 0x0002; // Bytes to follow
1053
$f1904 = $this->_1904; // Flag for 1904 date system
1055
$header = pack("vv", $record, $length);
1056
$data = pack("v", $f1904);
1057
$this->_append($header . $data);
1062
* Write BIFF record EXTERNCOUNT to indicate the number of external sheet
1063
* references in the workbook.
1065
* Excel only stores references to external sheets that are used in NAME.
1066
* The workbook NAME record is required to define the print area and the repeat
1069
* A similar method is used in Worksheet.php for a slightly different purpose.
1071
* @param integer $cxals Number of external references
1074
function _storeExterncount($cxals)
1076
$record = 0x0016; // Record identifier
1077
$length = 0x0002; // Number of bytes to follow
1079
$header = pack("vv", $record, $length);
1080
$data = pack("v", $cxals);
1081
$this->_append($header . $data);
1086
* Writes the Excel BIFF EXTERNSHEET record. These references are used by
1087
* formulas. NAME record is required to define the print area and the repeat
1090
* A similar method is used in Worksheet.php for a slightly different purpose.
1092
* @param string $sheetname Worksheet name
1095
function _storeExternsheet($sheetname)
1097
$record = 0x0017; // Record identifier
1098
$length = 0x02 + strlen($sheetname); // Number of bytes to follow
1100
$cch = strlen($sheetname); // Length of sheet name
1101
$rgch = 0x03; // Filename encoding
1103
$header = pack("vv", $record, $length);
1104
$data = pack("CC", $cch, $rgch);
1105
$this->_append($header . $data . $sheetname);
1110
* Store the NAME record in the short format that is used for storing the print
1111
* area, repeat rows only and repeat columns only.
1113
* @param integer $index Sheet index
1114
* @param integer $type Built-in name type
1115
* @param integer $rowmin Start row
1116
* @param integer $rowmax End row
1117
* @param integer $colmin Start colum
1118
* @param integer $colmax End column
1121
function _storeNameShort($index, $type, $rowmin, $rowmax, $colmin, $colmax)
1123
$record = 0x0018; // Record identifier
1124
$length = 0x0024; // Number of bytes to follow
1126
$grbit = 0x0020; // Option flags
1127
$chKey = 0x00; // Keyboard shortcut
1128
$cch = 0x01; // Length of text name
1129
$cce = 0x0015; // Length of text definition
1130
$ixals = $index + 1; // Sheet index
1131
$itab = $ixals; // Equal to ixals
1132
$cchCustMenu = 0x00; // Length of cust menu text
1133
$cchDescription = 0x00; // Length of description text
1134
$cchHelptopic = 0x00; // Length of help topic text
1135
$cchStatustext = 0x00; // Length of status bar text
1136
$rgch = $type; // Built-in name type
1139
$unknown04 = 0xffff-$index;
1140
$unknown05 = 0x0000;
1141
$unknown06 = 0x0000;
1142
$unknown07 = 0x1087;
1143
$unknown08 = 0x8005;
1145
$header = pack("vv", $record, $length);
1146
$data = pack("v", $grbit);
1147
$data .= pack("C", $chKey);
1148
$data .= pack("C", $cch);
1149
$data .= pack("v", $cce);
1150
$data .= pack("v", $ixals);
1151
$data .= pack("v", $itab);
1152
$data .= pack("C", $cchCustMenu);
1153
$data .= pack("C", $cchDescription);
1154
$data .= pack("C", $cchHelptopic);
1155
$data .= pack("C", $cchStatustext);
1156
$data .= pack("C", $rgch);
1157
$data .= pack("C", $unknown03);
1158
$data .= pack("v", $unknown04);
1159
$data .= pack("v", $unknown05);
1160
$data .= pack("v", $unknown06);
1161
$data .= pack("v", $unknown07);
1162
$data .= pack("v", $unknown08);
1163
$data .= pack("v", $index);
1164
$data .= pack("v", $index);
1165
$data .= pack("v", $rowmin);
1166
$data .= pack("v", $rowmax);
1167
$data .= pack("C", $colmin);
1168
$data .= pack("C", $colmax);
1169
$this->_append($header . $data);
1174
* Store the NAME record in the long format that is used for storing the repeat
1175
* rows and columns when both are specified. This shares a lot of code with
1176
* _storeNameShort() but we use a separate method to keep the code clean.
1177
* Code abstraction for reuse can be carried too far, and I should know. ;-)
1179
* @param integer $index Sheet index
1180
* @param integer $type Built-in name type
1181
* @param integer $rowmin Start row
1182
* @param integer $rowmax End row
1183
* @param integer $colmin Start colum
1184
* @param integer $colmax End column
1187
function _storeNameLong($index, $type, $rowmin, $rowmax, $colmin, $colmax)
1189
$record = 0x0018; // Record identifier
1190
$length = 0x003d; // Number of bytes to follow
1191
$grbit = 0x0020; // Option flags
1192
$chKey = 0x00; // Keyboard shortcut
1193
$cch = 0x01; // Length of text name
1194
$cce = 0x002e; // Length of text definition
1195
$ixals = $index + 1; // Sheet index
1196
$itab = $ixals; // Equal to ixals
1197
$cchCustMenu = 0x00; // Length of cust menu text
1198
$cchDescription = 0x00; // Length of description text
1199
$cchHelptopic = 0x00; // Length of help topic text
1200
$cchStatustext = 0x00; // Length of status bar text
1201
$rgch = $type; // Built-in name type
1204
$unknown02 = 0x002b;
1206
$unknown04 = 0xffff-$index;
1207
$unknown05 = 0x0000;
1208
$unknown06 = 0x0000;
1209
$unknown07 = 0x1087;
1210
$unknown08 = 0x8008;
1212
$header = pack("vv", $record, $length);
1213
$data = pack("v", $grbit);
1214
$data .= pack("C", $chKey);
1215
$data .= pack("C", $cch);
1216
$data .= pack("v", $cce);
1217
$data .= pack("v", $ixals);
1218
$data .= pack("v", $itab);
1219
$data .= pack("C", $cchCustMenu);
1220
$data .= pack("C", $cchDescription);
1221
$data .= pack("C", $cchHelptopic);
1222
$data .= pack("C", $cchStatustext);
1223
$data .= pack("C", $rgch);
1224
$data .= pack("C", $unknown01);
1225
$data .= pack("v", $unknown02);
1226
// Column definition
1227
$data .= pack("C", $unknown03);
1228
$data .= pack("v", $unknown04);
1229
$data .= pack("v", $unknown05);
1230
$data .= pack("v", $unknown06);
1231
$data .= pack("v", $unknown07);
1232
$data .= pack("v", $unknown08);
1233
$data .= pack("v", $index);
1234
$data .= pack("v", $index);
1235
$data .= pack("v", 0x0000);
1236
$data .= pack("v", 0x3fff);
1237
$data .= pack("C", $colmin);
1238
$data .= pack("C", $colmax);
1240
$data .= pack("C", $unknown03);
1241
$data .= pack("v", $unknown04);
1242
$data .= pack("v", $unknown05);
1243
$data .= pack("v", $unknown06);
1244
$data .= pack("v", $unknown07);
1245
$data .= pack("v", $unknown08);
1246
$data .= pack("v", $index);
1247
$data .= pack("v", $index);
1248
$data .= pack("v", $rowmin);
1249
$data .= pack("v", $rowmax);
1250
$data .= pack("C", 0x00);
1251
$data .= pack("C", 0xff);
1253
$data .= pack("C", 0x10);
1254
$this->_append($header . $data);
1258
* Stores the COUNTRY record for localization
1262
function _storeCountry()
1264
$record = 0x008C; // Record identifier
1265
$length = 4; // Number of bytes to follow
1267
$header = pack('vv', $record, $length);
1268
/* using the same country code always for simplicity */
1269
$data = pack('vv', $this->_country_code, $this->_country_code);
1270
$this->_append($header . $data);
1274
* Stores the PALETTE biff record.
1278
function _storePalette()
1280
$aref = $this->_palette;
1282
$record = 0x0092; // Record identifier
1283
$length = 2 + 4 * count($aref); // Number of bytes to follow
1284
$ccv = count($aref); // Number of RGB values to follow
1285
$data = ''; // The RGB data
1287
// Pack the RGB data
1288
foreach ($aref as $color) {
1289
foreach ($color as $byte) {
1290
$data .= pack("C",$byte);
1294
$header = pack("vvv", $record, $length, $ccv);
1295
$this->_append($header . $data);
1300
* Handling of the SST continue blocks is complicated by the need to include an
1301
* additional continuation byte depending on whether the string is split between
1302
* blocks or whether it starts at the beginning of the block. (There are also
1303
* additional complications that will arise later when/if Rich Strings are
1308
function _calculateSharedStringsSizes()
1310
/* Iterate through the strings to calculate the CONTINUE block sizes.
1311
For simplicity we use the same size for the SST and CONTINUE records:
1312
8228 : Maximum Excel97 block size
1313
-4 : Length of block header
1314
-8 : Length of additional SST header information
1315
-8 : Arbitrary number to keep within _add_continue() limit
1318
$continue_limit = 8208;
1321
$this->_block_sizes = array();
1324
foreach (array_keys($this->_str_table) as $string) {
1325
$string_length = strlen($string);
1326
$headerinfo = unpack("vlength/Cencoding", $string);
1327
$encoding = $headerinfo["encoding"];
1330
// Block length is the total length of the strings that will be
1331
// written out in a single SST or CONTINUE block.
1332
$block_length += $string_length;
1334
// We can write the string if it doesn't cross a CONTINUE boundary
1335
if ($block_length < $continue_limit) {
1336
$written += $string_length;
1340
// Deal with the cases where the next string to be written will exceed
1341
// the CONTINUE boundary. If the string is very long it may need to be
1342
// written in more than one CONTINUE record.
1343
while ($block_length >= $continue_limit) {
1345
// We need to avoid the case where a string is continued in the first
1346
// n bytes that contain the string header information.
1347
$header_length = 3; // Min string + header size -1
1348
$space_remaining = $continue_limit - $written - $continue;
1351
/* TODO: Unicode data should only be split on char (2 byte)
1352
boundaries. Therefore, in some cases we need to reduce the
1357
# Only applies to Unicode strings
1358
if ($encoding == 1) {
1359
# Min string + header size -1
1362
if ($space_remaining > $header_length) {
1363
# String contains 3 byte header => split on odd boundary
1364
if (!$split_string && $space_remaining % 2 != 1) {
1368
# Split section without header => split on even boundary
1369
else if ($split_string && $space_remaining % 2 == 1) {
1379
if ($space_remaining > $header_length) {
1380
// Write as much as possible of the string in the current block
1381
$written += $space_remaining;
1383
// Reduce the current block length by the amount written
1384
$block_length -= $continue_limit - $continue - $align;
1386
// Store the max size for this block
1387
$this->_block_sizes[] = $continue_limit - $align;
1389
// If the current string was split then the next CONTINUE block
1390
// should have the string continue flag (grbit) set unless the
1391
// split string fits exactly into the remaining space.
1392
if ($block_length > 0) {
1398
// Store the max size for this block
1399
$this->_block_sizes[] = $written + $continue;
1401
// Not enough space to start the string in the current block
1402
$block_length -= $continue_limit - $space_remaining - $continue;
1407
// If the string (or substr) is small enough we can write it in the
1408
// new CONTINUE block. Else, go through the loop again to write it in
1409
// one or more CONTINUE blocks
1410
if ($block_length < $continue_limit) {
1411
$written = $block_length;
1418
// Store the max size for the last block unless it is empty
1419
if ($written + $continue) {
1420
$this->_block_sizes[] = $written + $continue;
1424
/* Calculate the total length of the SST and associated CONTINUEs (if any).
1425
The SST record will have a length even if it contains no strings.
1426
This length is required to set the offsets in the BOUNDSHEET records since
1427
they must be written before the SST records
1430
$tmp_block_sizes = array();
1431
$tmp_block_sizes = $this->_block_sizes;
1434
if (!empty($tmp_block_sizes)) {
1435
$length += array_shift($tmp_block_sizes); # SST
1437
while (!empty($tmp_block_sizes)) {
1438
$length += 4 + array_shift($tmp_block_sizes); # CONTINUEs
1445
* Write all of the workbooks strings into an indexed array.
1446
* See the comments in _calculate_shared_string_sizes() for more information.
1448
* The Excel documentation says that the SST record should be followed by an
1449
* EXTSST record. The EXTSST record is a hash table that is used to optimise
1450
* access to SST. However, despite the documentation it doesn't seem to be
1451
* required so we will ignore it.
1455
function _storeSharedStringsTable()
1457
$record = 0x00fc; // Record identifier
1458
$length = 0x0008; // Number of bytes to follow
1461
// Iterate through the strings to calculate the CONTINUE block sizes
1462
$continue_limit = 8208;
1467
// sizes are upside down
1468
$tmp_block_sizes = $this->_block_sizes;
1469
// $tmp_block_sizes = array_reverse($this->_block_sizes);
1471
# The SST record is required even if it contains no strings. Thus we will
1472
# always have a length
1474
if (!empty($tmp_block_sizes)) {
1475
$length = 8 + array_shift($tmp_block_sizes);
1484
// Write the SST block header information
1485
$header = pack("vv", $record, $length);
1486
$data = pack("VV", $this->_str_total, $this->_str_unique);
1487
$this->_append($header . $data);
1492
/* TODO: not good for performance */
1493
foreach (array_keys($this->_str_table) as $string) {
1495
$string_length = strlen($string);
1496
$headerinfo = unpack("vlength/Cencoding", $string);
1497
$encoding = $headerinfo["encoding"];
1500
// Block length is the total length of the strings that will be
1501
// written out in a single SST or CONTINUE block.
1503
$block_length += $string_length;
1506
// We can write the string if it doesn't cross a CONTINUE boundary
1507
if ($block_length < $continue_limit) {
1508
$this->_append($string);
1509
$written += $string_length;
1513
// Deal with the cases where the next string to be written will exceed
1514
// the CONTINUE boundary. If the string is very long it may need to be
1515
// written in more than one CONTINUE record.
1517
while ($block_length >= $continue_limit) {
1519
// We need to avoid the case where a string is continued in the first
1520
// n bytes that contain the string header information.
1522
$header_length = 3; // Min string + header size -1
1523
$space_remaining = $continue_limit - $written - $continue;
1526
// Unicode data should only be split on char (2 byte) boundaries.
1527
// Therefore, in some cases we need to reduce the amount of available
1528
// space by 1 byte to ensure the correct alignment.
1531
// Only applies to Unicode strings
1532
if ($encoding == 1) {
1533
// Min string + header size -1
1536
if ($space_remaining > $header_length) {
1537
// String contains 3 byte header => split on odd boundary
1538
if (!$split_string && $space_remaining % 2 != 1) {
1542
// Split section without header => split on even boundary
1543
else if ($split_string && $space_remaining % 2 == 1) {
1553
if ($space_remaining > $header_length) {
1554
// Write as much as possible of the string in the current block
1555
$tmp = substr($string, 0, $space_remaining);
1556
$this->_append($tmp);
1558
// The remainder will be written in the next block(s)
1559
$string = substr($string, $space_remaining);
1561
// Reduce the current block length by the amount written
1562
$block_length -= $continue_limit - $continue - $align;
1564
// If the current string was split then the next CONTINUE block
1565
// should have the string continue flag (grbit) set unless the
1566
// split string fits exactly into the remaining space.
1568
if ($block_length > 0) {
1574
// Not enough space to start the string in the current block
1575
$block_length -= $continue_limit - $space_remaining - $continue;
1579
// Write the CONTINUE block header
1580
if (!empty($this->_block_sizes)) {
1582
$length = array_shift($tmp_block_sizes);
1584
$header = pack('vv', $record, $length);
1586
$header .= pack('C', $encoding);
1588
$this->_append($header);
1591
// If the string (or substr) is small enough we can write it in the
1592
// new CONTINUE block. Else, go through the loop again to write it in
1593
// one or more CONTINUE blocks
1595
if ($block_length < $continue_limit) {
1596
$this->_append($string);
1597
$written = $block_length;