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::WriteExcel: A library for generating Excel Spreadsheets
18 * Copyright (C) 2002 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
36 * @const ADD token identifier for character "+"
41 * @const SUB token identifier for character "-"
46 * @const EQUAL token identifier for character "="
51 * @const MUL token identifier for character "*"
56 * @const DIV token identifier for character "/"
61 * @const OPEN token identifier for character "("
66 * @const CLOSE token identifier for character ")"
71 * @const COMA token identifier for character ","
76 * Class for writing Excel BIFF records.
78 * From "MICROSOFT EXCEL BINARY FILE FORMAT" by Mark O'Brien (Microsoft Corporation):
80 * BIFF (BInary File Format) is the file format in which Excel documents are
81 * saved on disk. A BIFF file is a complete description of an Excel document.
82 * BIFF files consist of sequences of variable-length records. There are many
83 * different types of BIFF records. For example, one record type describes a
84 * formula entered into a cell; one describes the size and location of a
85 * window into a document; another describes a picture format.
87 * @author Xavier Noguer <xnoguer@rezebra.com>
88 * @package Spreadsheet_WriteExcel
93 var $_BIFF_version = 0x0500;
100 function BIFFwriter()
102 // The byte order of this architecture. 0 => little endian, 1 => big endian
103 $this->_byte_order = '';
104 // The string containing the data of the BIFF stream
106 // Should be the same as strlen($this->_data)
107 $this->_datasize = 0;
108 // The maximun length for a BIFF record. See _add_continue()
109 $this->_limit = 2080;
110 // Set the byte order
111 $this->_set_byte_order();
115 * Determine the byte order and store it as class data to avoid
116 * recalculating it for each call to new().
120 function _set_byte_order()
122 if ($this->_byte_order == '')
124 // Check if "pack" gives the required IEEE 64bit float
125 $teststr = pack("d", 1.2345);
126 $number = pack("C8", 0x8D, 0x97, 0x6E, 0x12, 0x83, 0xC0, 0xF3, 0x3F);
127 if ($number == $teststr) {
128 $byte_order = 0; // Little Endian
130 elseif ($number == strrev($teststr)){
131 $byte_order = 1; // Big Endian
134 // Give up. I'll fix this in a later version.
135 die("Required floating point format not supported ".
136 "on this platform. See the portability section ".
137 "of the documentation."
141 $this->_byte_order = $byte_order;
145 * General storage function
147 * @param string $data binary data to prepend
150 function _prepend($data)
152 if (strlen($data) > $this->_limit) {
153 $data = $this->_add_continue($data);
155 $this->_data = $data.$this->_data;
156 $this->_datasize += strlen($data);
160 * General storage function
162 * @param string $data binary data to append
165 function _append($data)
167 if (strlen($data) > $this->_limit) {
168 $data = $this->_add_continue($data);
170 $this->_data = $this->_data.$data;
171 $this->_datasize += strlen($data);
175 * Writes Excel BOF record to indicate the beginning of a stream or
176 * sub-stream in the BIFF file.
178 * @param integer $type type of BIFF file to write: 0x0005 Workbook, 0x0010 Worksheet.
181 function _store_bof($type)
183 $record = 0x0809; // Record identifier
184 $length = 0x0008; // Number of bytes to follow
185 $version = $this->_BIFF_version;
187 // According to the SDK $build and $year should be set to zero.
188 // However, this throws a warning in Excel 5. So, use these
193 $header = pack("vv", $record, $length);
194 $data = pack("vvvv", $version, $type, $build, $year);
195 $this->_prepend($header.$data);
199 * Writes Excel EOF record to indicate the end of a BIFF stream.
203 function _store_eof()
205 $record = 0x000A; // Record identifier
206 $length = 0x0000; // Number of bytes to follow
207 $header = pack("vv", $record, $length);
208 $this->_append($header);
212 * Excel limits the size of BIFF records. In Excel 5 the limit is 2084 bytes. In
213 * Excel 97 the limit is 8228 bytes. Records that are longer than these limits
214 * must be split up into CONTINUE blocks.
216 * This function takes a long BIFF record and inserts CONTINUE records as
219 * @param string $data The original binary data to be written
220 * @return string A very convenient string of continue blocks
223 function _add_continue($data)
225 $limit = $this->_limit;
226 $record = 0x003C; // Record identifier
228 // The first 2080/8224 bytes remain intact. However, we have to change
229 // the length field of the record.
230 $tmp = substr($data, 0, 2).pack("v", $limit-4).substr($data, 4, $limit - 4);
232 $header = pack("vv", $record, $limit); // Headers for continue records
234 // Retrieve chunks of 2080/8224 bytes +4 for the header.
235 for($i = $limit; $i < strlen($data) - $limit; $i += $limit)
238 $tmp .= substr($data, $i, $limit);
241 // Retrieve the last chunk of data
242 $header = pack("vv", $record, strlen($data) - $i);
244 $tmp .= substr($data,$i,strlen($data) - $i);
251 * Class for creating OLE streams for Excel Spreadsheets
253 * @author Xavier Noguer <xnoguer@rezebra.com>
254 * @package Spreadsheet_WriteExcel
259 * Filename for the OLE stream
266 * Filehandle for the OLE stream
272 * Name of the temporal file in case OLE stream goes to stdout
278 * Variable for preventing closing two times
284 * Size of the data to be written to the OLE stream
290 * Real data size to be written to the OLE stream
296 * Number of big blocks in the OLE stream
302 * Number of list blocks in the OLE stream
308 * Number of big blocks in the OLE stream
314 * Class for creating an OLEwriter
316 * @param string $OLEfilename the name of the file for the OLE stream
318 function OLEwriter($OLEfilename)
320 $this->_OLEfilename = $OLEfilename;
321 $this->_filehandle = "";
322 $this->_tmp_filename = "";
323 $this->_fileclosed = 0;
324 //$this->_size_allowed = 0;
325 $this->_biffsize = 0;
326 $this->_booksize = 0;
327 $this->_big_blocks = 0;
328 $this->_list_blocks = 0;
329 $this->_root_start = 0;
330 //$this->_block_count = 4;
331 $this->_initialize();
335 * Check for a valid filename and store the filehandle.
336 * Filehandle "-" writes to STDOUT
338 function _initialize()
340 $OLEfile = $this->_OLEfilename;
342 if(($OLEfile == '-') or ($OLEfile == ''))
344 $this->_tmp_filename = tempnam("/tmp", "OLEwriter");
345 $fh = fopen($this->_tmp_filename,"wb");
347 die("Can't create temporary file.");
352 // Create a new file, open for writing (in binmode)
353 $fh = fopen($OLEfile,"wb");
355 die("Can't open $OLEfile. It may be in use or protected.");
360 $this->_filehandle = $fh;
365 * Set the size of the data to be written to the OLE stream.
366 * The maximun size comes from this:
367 * $big_blocks = (109 depot block x (128 -1 marker word)
368 * - (1 x end words)) = 13842
369 * $maxsize = $big_blocks * 512 bytes = 7087104
372 * @see Workbook::store_OLE_file()
373 * @param integer $biffsize The size of the data to be written to the OLE stream
374 * @return integer 1 for success
376 function set_size($biffsize)
378 $maxsize = 7087104; // TODO: extend max size
380 if ($biffsize > $maxsize) {
381 die("Maximum file size, $maxsize, exceeded.");
384 $this->_biffsize = $biffsize;
385 // Set the min file size to 4k to avoid having to use small blocks
386 if ($biffsize > 4096) {
387 $this->_booksize = $biffsize;
390 $this->_booksize = 4096;
392 //$this->_size_allowed = 1;
398 * Calculate various sizes needed for the OLE stream
400 function _calculate_sizes()
402 $datasize = $this->_booksize;
403 if ($datasize % 512 == 0) {
404 $this->_big_blocks = $datasize/512;
407 $this->_big_blocks = floor($datasize/512) + 1;
409 // There are 127 list blocks and 1 marker blocks for each big block
410 // depot + 1 end of chain block
411 $this->_list_blocks = floor(($this->_big_blocks)/127) + 1;
412 $this->_root_start = $this->_big_blocks;
416 * Write root entry, big block list and close the filehandle.
417 * This routine is used to explicitly close the open filehandle without
418 * having to wait for DESTROY.
421 * @see Workbook::store_OLE_file()
425 //return if not $this->{_size_allowed};
426 $this->_write_padding();
427 $this->_write_property_storage();
428 $this->_write_big_block_depot();
429 // Close the filehandle
430 fclose($this->_filehandle);
431 if(($this->_OLEfilename == '-') or ($this->_OLEfilename == ''))
433 $fh = fopen($this->_tmp_filename, "rb");
435 die("Can't read temporary file.");
438 // Delete the temporary file.
439 @unlink($this->_tmp_filename);
441 $this->_fileclosed = 1;
446 * Write BIFF data to OLE file.
448 * @param string $data string of bytes to be written
450 function write($data) //por ahora sólo a STDOUT
452 fwrite($this->_filehandle,$data,strlen($data));
457 * Write OLE header block.
459 function write_header()
461 $this->_calculate_sizes();
462 $root_start = $this->_root_start;
463 $num_lists = $this->_list_blocks;
464 $id = pack("nnnn", 0xD0CF, 0x11E0, 0xA1B1, 0x1AE1);
465 $unknown1 = pack("VVVV", 0x00, 0x00, 0x00, 0x00);
466 $unknown2 = pack("vv", 0x3E, 0x03);
467 $unknown3 = pack("v", -2);
468 $unknown4 = pack("v", 0x09);
469 $unknown5 = pack("VVV", 0x06, 0x00, 0x00);
470 $num_bbd_blocks = pack("V", $num_lists);
471 $root_startblock = pack("V", $root_start);
472 $unknown6 = pack("VV", 0x00, 0x1000);
473 $sbd_startblock = pack("V", -2);
474 $unknown7 = pack("VVV", 0x00, -2 ,0x00);
475 $unused = pack("V", -1);
477 fwrite($this->_filehandle,$id);
478 fwrite($this->_filehandle,$unknown1);
479 fwrite($this->_filehandle,$unknown2);
480 fwrite($this->_filehandle,$unknown3);
481 fwrite($this->_filehandle,$unknown4);
482 fwrite($this->_filehandle,$unknown5);
483 fwrite($this->_filehandle,$num_bbd_blocks);
484 fwrite($this->_filehandle,$root_startblock);
485 fwrite($this->_filehandle,$unknown6);
486 fwrite($this->_filehandle,$sbd_startblock);
487 fwrite($this->_filehandle,$unknown7);
489 for($i=1; $i <= $num_lists; $i++)
492 fwrite($this->_filehandle,pack("V",$root_start));
494 for($i = $num_lists; $i <=108; $i++)
496 fwrite($this->_filehandle,$unused);
502 * Write big block depot.
504 function _write_big_block_depot()
506 $num_blocks = $this->_big_blocks;
507 $num_lists = $this->_list_blocks;
508 $total_blocks = $num_lists *128;
509 $used_blocks = $num_blocks + $num_lists +2;
511 $marker = pack("V", -3);
512 $end_of_chain = pack("V", -2);
513 $unused = pack("V", -1);
515 for($i=1; $i < $num_blocks; $i++)
517 fwrite($this->_filehandle,pack("V",$i));
519 fwrite($this->_filehandle,$end_of_chain);
520 fwrite($this->_filehandle,$end_of_chain);
521 for($i=0; $i < $num_lists; $i++)
523 fwrite($this->_filehandle,$marker);
525 for($i=$used_blocks; $i <= $total_blocks; $i++)
527 fwrite($this->_filehandle,$unused);
532 * Write property storage. TODO: add summary sheets
534 function _write_property_storage()
537 /*************** name type dir start size */
538 $this->_write_pps("Root Entry", 0x05, 1, -2, 0x00);
539 $this->_write_pps("Book", 0x02, -1, 0x00, $this->_booksize);
540 $this->_write_pps('', 0x00, -1, 0x00, 0x0000);
541 $this->_write_pps('', 0x00, -1, 0x00, 0x0000);
545 * Write property sheet in property storage
547 * @param string $name name of the property storage.
548 * @param integer $type type of the property storage.
549 * @param integer $dir dir of the property storage.
550 * @param integer $start start of the property storage.
551 * @param integer $size size of the property storage.
554 function _write_pps($name,$type,$dir,$start,$size)
561 $name = $name . "\0";
562 for($i=0;$i<strlen($name);$i++)
564 // Simulate a Unicode string
565 $rawname .= pack("H*",dechex(ord($name{$i}))).pack("C",0);
567 $length = strlen($name) * 2;
570 $zero = pack("C", 0);
571 $pps_sizeofname = pack("v", $length); // 0x40
572 $pps_type = pack("v", $type); // 0x42
573 $pps_prev = pack("V", -1); // 0x44
574 $pps_next = pack("V", -1); // 0x48
575 $pps_dir = pack("V", $dir); // 0x4c
577 $unknown1 = pack("V", 0);
579 $pps_ts1s = pack("V", 0); // 0x64
580 $pps_ts1d = pack("V", 0); // 0x68
581 $pps_ts2s = pack("V", 0); // 0x6c
582 $pps_ts2d = pack("V", 0); // 0x70
583 $pps_sb = pack("V", $start); // 0x74
584 $pps_size = pack("V", $size); // 0x78
587 fwrite($this->_filehandle,$rawname);
588 for($i=0; $i < (64 -$length); $i++) {
589 fwrite($this->_filehandle,$zero);
591 fwrite($this->_filehandle,$pps_sizeofname);
592 fwrite($this->_filehandle,$pps_type);
593 fwrite($this->_filehandle,$pps_prev);
594 fwrite($this->_filehandle,$pps_next);
595 fwrite($this->_filehandle,$pps_dir);
596 for($i=0; $i < 5; $i++) {
597 fwrite($this->_filehandle,$unknown1);
599 fwrite($this->_filehandle,$pps_ts1s);
600 fwrite($this->_filehandle,$pps_ts1d);
601 fwrite($this->_filehandle,$pps_ts2d);
602 fwrite($this->_filehandle,$pps_ts2d);
603 fwrite($this->_filehandle,$pps_sb);
604 fwrite($this->_filehandle,$pps_size);
605 fwrite($this->_filehandle,$unknown1);
609 * Pad the end of the file
611 function _write_padding()
613 $biffsize = $this->_biffsize;
614 if ($biffsize < 4096) {
620 if ($biffsize % $min_size != 0)
622 $padding = $min_size - ($biffsize % $min_size);
623 for($i=0; $i < $padding; $i++) {
624 fwrite($this->_filehandle,"\0");
631 * Class for generating Excel XF records (formats)
633 * @author Xavier Noguer <xnoguer@rezebra.com>
634 * @package Spreadsheet_WriteExcel
643 * @param integer $index the XF index for the format.
644 * @param array $properties array with properties to be set on initialization.
646 function Format($index = 0,$properties = array())
648 $this->xf_index = $index;
650 $this->font_index = 0;
651 $this->font = 'Arial';
653 $this->bold = 0x0190;
655 $this->color = 0x7FFF;
656 $this->_underline = 0;
657 $this->font_strikeout = 0;
658 $this->font_outline = 0;
659 $this->font_shadow = 0;
660 $this->font_script = 0;
661 $this->font_family = 0;
662 $this->font_charset = 0;
664 $this->_num_format = 0;
669 $this->_text_h_align = 0;
670 $this->_text_wrap = 0;
671 $this->text_v_align = 2;
672 $this->text_justlast = 0;
675 $this->fg_color = 0x40;
676 $this->bg_color = 0x41;
685 $this->bottom_color = 0x40;
686 $this->top_color = 0x40;
687 $this->left_color = 0x40;
688 $this->right_color = 0x40;
690 // Set properties passed to Workbook::add_format()
691 foreach($properties as $property => $value)
693 if(method_exists($this,"set_$property"))
695 $aux = 'set_'.$property;
702 * Generate an Excel BIFF XF record (style or cell).
704 * @param string $style The type of the XF record ('style' or 'cell').
705 * @return string The XF record
707 function get_xf($style)
709 // Set the type of the XF record and some of the attributes.
710 if ($style == "style") {
714 $style = $this->locked;
715 $style |= $this->hidden << 1;
718 // Flags to indicate if attributes have been set.
719 $atr_num = ($this->_num_format != 0)?1:0;
720 $atr_fnt = ($this->font_index != 0)?1:0;
721 $atr_alc = ($this->_text_wrap)?1:0;
722 $atr_bdr = ($this->bottom ||
726 $atr_pat = (($this->fg_color != 0x40) ||
727 ($this->bg_color != 0x41) ||
731 // Zero the default border colour if the border has not been set.
732 if ($this->bottom == 0) {
733 $this->bottom_color = 0;
735 if ($this->top == 0) {
736 $this->top_color = 0;
738 if ($this->right == 0) {
739 $this->right_color = 0;
741 if ($this->left == 0) {
742 $this->left_color = 0;
745 $record = 0x00E0; // Record identifier
746 $length = 0x0010; // Number of bytes to follow
748 $ifnt = $this->font_index; // Index to FONT record
749 $ifmt = $this->_num_format; // Index to FORMAT record
751 $align = $this->_text_h_align; // Alignment
752 $align |= $this->_text_wrap << 3;
753 $align |= $this->text_v_align << 4;
754 $align |= $this->text_justlast << 7;
755 $align |= $this->rotation << 8;
756 $align |= $atr_num << 10;
757 $align |= $atr_fnt << 11;
758 $align |= $atr_alc << 12;
759 $align |= $atr_bdr << 13;
760 $align |= $atr_pat << 14;
761 $align |= $atr_prot << 15;
763 $icv = $this->fg_color; // fg and bg pattern colors
764 $icv |= $this->bg_color << 7;
766 $fill = $this->pattern; // Fill and border line style
767 $fill |= $this->bottom << 6;
768 $fill |= $this->bottom_color << 9;
770 $border1 = $this->top; // Border line style and color
771 $border1 |= $this->left << 3;
772 $border1 |= $this->right << 6;
773 $border1 |= $this->top_color << 9;
775 $border2 = $this->left_color; // Border color
776 $border2 |= $this->right_color << 7;
778 $header = pack("vv", $record, $length);
779 $data = pack("vvvvvvvv", $ifnt, $ifmt, $style, $align,
782 return($header.$data);
786 * Generate an Excel BIFF FONT record.
788 * @see Workbook::_store_all_fonts()
789 * @return string The FONT record
793 $dyHeight = $this->size * 20; // Height of font (1/20 of a point)
794 $icv = $this->color; // Index to color palette
795 $bls = $this->bold; // Bold style
796 $sss = $this->font_script; // Superscript/subscript
797 $uls = $this->_underline; // Underline
798 $bFamily = $this->font_family; // Font family
799 $bCharSet = $this->font_charset; // Character set
800 $rgch = $this->font; // Font name
802 $cch = strlen($rgch); // Length of font name
803 $record = 0x31; // Record identifier
804 $length = 0x0F + $cch; // Record length
805 $reserved = 0x00; // Reserved
806 $grbit = 0x00; // Font attributes
807 if ($this->_italic) {
810 if ($this->font_strikeout) {
813 if ($this->font_outline) {
816 if ($this->font_shadow) {
820 $header = pack("vv", $record, $length);
821 $data = pack("vvvvvCCCCC", $dyHeight, $grbit, $icv, $bls,
822 $sss, $uls, $bFamily,
823 $bCharSet, $reserved, $cch);
824 return($header . $data. $this->font);
828 * Returns a unique hash key for a font. Used by Workbook->_store_all_fonts()
830 * The elements that form the key are arranged to increase the probability of
831 * generating a unique key. Elements that hold a large range of numbers
832 * (eg. _color) are placed between two binary elements such as _italic
834 * @return string A key for this font
836 function get_font_key()
838 $key = "$this->font$this->size";
839 $key .= "$this->font_script$this->_underline";
840 $key .= "$this->font_strikeout$this->bold$this->font_outline";
841 $key .= "$this->font_family$this->font_charset";
842 $key .= "$this->font_shadow$this->color$this->_italic";
843 $key = str_replace(" ","_",$key);
848 * Returns the index used by Worksheet->_XF()
850 * @return integer The index for the XF record
852 function get_xf_index()
854 return($this->xf_index);
858 * Used in conjunction with the set_xxx_color methods to convert a color
859 * string into a number. Color range is 0..63 but we will restrict it
860 * to 8..63 to comply with Gnumeric. Colors 0..7 are repeated in 8..15.
862 * @param string $name_color name of the color (i.e.: 'blue', 'red', etc..). Optional.
863 * @return integer The color index
865 function _get_color($name_color = '')
888 // Return the default color, 0x7FFF, if undef,
889 if($name_color == '') {
893 // or the color string converted to an integer,
894 if(isset($colors[$name_color])) {
895 return($colors[$name_color]);
898 // or the default color if string is unrecognised,
899 if(preg_match("/\D/",$name_color)) {
903 // or an index < 8 mapped into the correct range,
904 if($name_color < 8) {
905 return($name_color + 8);
908 // or the default color if arg is outside range,
909 if($name_color > 63) {
913 // or an integer in the valid range
918 * Set cell alignment.
921 * @param string $location alignment for the cell ('left', 'right', etc...).
923 function set_align($location)
925 if (preg_match("/\d/",$location)) {
926 return; // Ignore numbers
929 $location = strtolower($location);
931 if ($location == 'left')
932 $this->_text_h_align = 1;
933 if ($location == 'centre')
934 $this->_text_h_align = 2;
935 if ($location == 'center')
936 $this->_text_h_align = 2;
937 if ($location == 'right')
938 $this->_text_h_align = 3;
939 if ($location == 'fill')
940 $this->_text_h_align = 4;
941 if ($location == 'justify')
942 $this->_text_h_align = 5;
943 if ($location == 'merge')
944 $this->_text_h_align = 6;
945 if ($location == 'equal_space') // For T.K.
946 $this->_text_h_align = 7;
947 if ($location == 'top')
948 $this->text_v_align = 0;
949 if ($location == 'vcentre')
950 $this->text_v_align = 1;
951 if ($location == 'vcenter')
952 $this->text_v_align = 1;
953 if ($location == 'bottom')
954 $this->text_v_align = 2;
955 if ($location == 'vjustify')
956 $this->text_v_align = 3;
957 if ($location == 'vequal_space') // For T.K.
958 $this->text_v_align = 4;
962 * This is an alias for the unintuitive set_align('merge')
968 $this->set_align('merge');
972 * Bold has a range 0x64..0x3E8.
973 * 0x190 is normal. 0x2BC is bold.
976 * @param integer $weight Weight for the text, 0 maps to 0x190, 1 maps to 0x2BC.
977 It's Optional, default is 1 (bold).
979 function set_bold($weight = 1)
982 $weight = 0x2BC; // Bold text
985 $weight = 0x190; // Normal text
987 if($weight < 0x064) {
988 $weight = 0x190; // Lower bound
990 if($weight > 0x3E8) {
991 $weight = 0x190; // Upper bound
993 $this->bold = $weight;
997 /************************************
998 * FUNCTIONS FOR SETTING CELLS BORDERS
1002 * Sets the bottom border of the cell
1005 * @param integer $style style of the cell border. 1 => thin, 2 => thick.
1007 function set_bottom($style)
1009 $this->bottom = $style;
1013 * Sets the top border of the cell
1016 * @param integer $style style of the cell top border. 1 => thin, 2 => thick.
1018 function set_top($style)
1020 $this->top = $style;
1024 * Sets the left border of the cell
1027 * @param integer $style style of the cell left border. 1 => thin, 2 => thick.
1029 function set_left($style)
1031 $this->left = $style;
1035 * Sets the right border of the cell
1038 * @param integer $style style of the cell right border. 1 => thin, 2 => thick.
1040 function set_right($style)
1042 $this->right = $style;
1047 * Set cells borders to the same style
1050 * @param integer $style style to apply for all cell borders. 1 => thin, 2 => thick.
1052 function set_border($style)
1054 $this->set_bottom($style);
1055 $this->set_top($style);
1056 $this->set_left($style);
1057 $this->set_right($style);
1061 /*******************************************
1062 * FUNCTIONS FOR SETTING CELLS BORDERS COLORS
1066 * Sets all the cell's borders to the same color
1069 * @param mixed $color The color we are setting. Either a string (like 'blue'),
1070 * or an integer (like 0x41).
1072 function set_border_color($color)
1074 $this->set_bottom_color($color);
1075 $this->set_top_color($color);
1076 $this->set_left_color($color);
1077 $this->set_right_color($color);
1081 * Sets the cell's bottom border color
1084 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
1086 function set_bottom_color($color)
1088 $value = $this->_get_color($color);
1089 $this->bottom_color = $value;
1093 * Sets the cell's top border color
1096 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
1098 function set_top_color($color)
1100 $value = $this->_get_color($color);
1101 $this->top_color = $value;
1105 * Sets the cell's left border color
1108 * @param mixed $color either a string (like 'blue'), or an integer (like 0x41).
1110 function set_left_color($color)
1112 $value = $this->_get_color($color);
1113 $this->left_color = $value;
1117 * Sets the cell's right border color
1120 * @param mixed $color either a string (like 'blue'), or an integer (like 0x41).
1122 function set_right_color($color)
1124 $value = $this->_get_color($color);
1125 $this->right_color = $value;
1130 * Sets the cell's foreground color
1133 * @param mixed $color either a string (like 'blue'), or an integer (like 0x41).
1135 function set_fg_color($color)
1137 $value = $this->_get_color($color);
1138 $this->fg_color = $value;
1142 * Sets the cell's background color
1145 * @param mixed $color either a string (like 'blue'), or an integer (like 0x41).
1147 function set_bg_color($color)
1149 $value = $this->_get_color($color);
1150 $this->bg_color = $value;
1154 * Sets the cell's color
1157 * @param mixed $color either a string (like 'blue'), or an integer (like 0x41).
1159 function set_color($color)
1161 $value = $this->_get_color($color);
1162 $this->color = $value;
1166 * Sets the pattern attribute of a cell
1169 * @param integer $arg Optional. Defaults to 1.
1171 function set_pattern($arg = 1)
1173 $this->pattern = $arg;
1177 * Sets the underline of the text
1180 * @param integer $underline The value for underline. Possible values are:
1181 * 1 => underline, 2 => double underline.
1183 function set_underline($underline)
1185 $this->_underline = $underline;
1189 * Sets the font style as italic
1193 function set_italic()
1199 * Sets the font size
1202 * @param integer $size The font size (in pixels I think).
1204 function set_size($size)
1206 $this->size = $size;
1210 * Sets the num format
1213 * @param integer $num_format The num format.
1215 function set_num_format($num_format)
1217 $this->_num_format = $num_format;
1221 * Sets text wrapping
1224 * @param integer $text_wrap Optional. 0 => no text wrapping, 1 => text wrapping.
1227 function set_text_wrap($text_wrap = 1)
1229 $this->_text_wrap = $text_wrap;
1235 * Class for parsing Excel formulas
1237 * @author Xavier Noguer <xnoguer@rezebra.com>
1238 * @package Spreadsheet_WriteExcel
1243 * The class constructor
1245 * @param integer $byte_order The byte order (Little endian or Big endian) of the architecture
1246 (optional). 1 => big endian, 0 (default) => little endian.
1248 function Parser($byte_order = 0)
1250 $this->_current_char = 0; // The index of the character we are currently looking at.
1251 $this->_current_token = ''; // The token we are working on.
1252 $this->_formula = ""; // The formula to parse.
1253 $this->_lookahead = ''; // The character ahead of the current char.
1254 $this->_parse_tree = ''; // The parse tree to be generated.
1255 $this->_initialize_hashes(); // Initialize the hashes: ptg's and function's ptg's
1256 $this->_byte_order = $byte_order; // Little Endian or Big Endian
1257 $this->_func_args = 0; // Number of arguments for the current function
1258 $this->_volatile = 0;
1262 * Initialize the ptg and function hashes.
1264 function _initialize_hashes()
1266 // The Excel ptg indices
1275 'ptgConcat' => 0x08,
1286 'ptgUminus' => 0x13,
1287 'ptgPercent' => 0x14,
1289 'ptgMissArg' => 0x16,
1293 'ptgEndSheet' => 0x1B,
1300 'ptgFuncVar' => 0x22,
1304 'ptgMemArea' => 0x26,
1305 'ptgMemErr' => 0x27,
1306 'ptgMemNoMem' => 0x28,
1307 'ptgMemFunc' => 0x29,
1308 'ptgRefErr' => 0x2A,
1309 'ptgAreaErr' => 0x2B,
1312 'ptgMemAreaN' => 0x2E,
1313 'ptgMemNoMemN' => 0x2F,
1316 'ptgArea3d' => 0x3B,
1317 'ptgRefErr3d' => 0x3C,
1318 'ptgAreaErr3d' => 0x3D,
1319 'ptgArrayV' => 0x40,
1321 'ptgFuncVarV' => 0x42,
1325 'ptgMemAreaV' => 0x46,
1326 'ptgMemErrV' => 0x47,
1327 'ptgMemNoMemV' => 0x48,
1328 'ptgMemFuncV' => 0x49,
1329 'ptgRefErrV' => 0x4A,
1330 'ptgAreaErrV' => 0x4B,
1332 'ptgAreaNV' => 0x4D,
1333 'ptgMemAreaNV' => 0x4E,
1334 'ptgMemNoMemN' => 0x4F,
1335 'ptgFuncCEV' => 0x58,
1336 'ptgNameXV' => 0x59,
1337 'ptgRef3dV' => 0x5A,
1338 'ptgArea3dV' => 0x5B,
1339 'ptgRefErr3dV' => 0x5C,
1340 'ptgAreaErr3d' => 0x5D,
1341 'ptgArrayA' => 0x60,
1343 'ptgFuncVarA' => 0x62,
1347 'ptgMemAreaA' => 0x66,
1348 'ptgMemErrA' => 0x67,
1349 'ptgMemNoMemA' => 0x68,
1350 'ptgMemFuncA' => 0x69,
1351 'ptgRefErrA' => 0x6A,
1352 'ptgAreaErrA' => 0x6B,
1354 'ptgAreaNA' => 0x6D,
1355 'ptgMemAreaNA' => 0x6E,
1356 'ptgMemNoMemN' => 0x6F,
1357 'ptgFuncCEA' => 0x78,
1358 'ptgNameXA' => 0x79,
1359 'ptgRef3dA' => 0x7A,
1360 'ptgArea3dA' => 0x7B,
1361 'ptgRefErr3dA' => 0x7C,
1362 'ptgAreaErr3d' => 0x7D
1365 // Thanks to Michael Meeks and Gnumeric for the initial arg values.
1367 // The following hash was generated by "function_locale.pl" in the distro.
1368 // Refer to function_locale.pl for non-English function names.
1370 // The array elements are as follow:
1371 // ptg: The Excel function ptg code.
1372 // args: The number of arguments that the function takes:
1373 // >=0 is a fixed number of arguments.
1374 // -1 is a variable number of arguments.
1375 // class: The reference, value or array class of the function args.
1376 // vol: The function is volatile.
1378 $this->_functions = array(
1379 // function ptg args class vol
1380 'COUNT' => array( 0, -1, 0, 0 ),
1381 'IF' => array( 1, -1, 1, 0 ),
1382 'ISNA' => array( 2, 1, 1, 0 ),
1383 'ISERROR' => array( 3, 1, 1, 0 ),
1384 'SUM' => array( 4, -1, 0, 0 ),
1385 'AVERAGE' => array( 5, -1, 0, 0 ),
1386 'MIN' => array( 6, -1, 0, 0 ),
1387 'MAX' => array( 7, -1, 0, 0 ),
1388 'ROW' => array( 8, -1, 0, 0 ),
1389 'COLUMN' => array( 9, -1, 0, 0 ),
1390 'NA' => array( 10, 0, 0, 0 ),
1391 'NPV' => array( 11, -1, 1, 0 ),
1392 'STDEV' => array( 12, -1, 0, 0 ),
1393 'DOLLAR' => array( 13, -1, 1, 0 ),
1394 'FIXED' => array( 14, -1, 1, 0 ),
1395 'SIN' => array( 15, 1, 1, 0 ),
1396 'COS' => array( 16, 1, 1, 0 ),
1397 'TAN' => array( 17, 1, 1, 0 ),
1398 'ATAN' => array( 18, 1, 1, 0 ),
1399 'PI' => array( 19, 0, 1, 0 ),
1400 'SQRT' => array( 20, 1, 1, 0 ),
1401 'EXP' => array( 21, 1, 1, 0 ),
1402 'LN' => array( 22, 1, 1, 0 ),
1403 'LOG10' => array( 23, 1, 1, 0 ),
1404 'ABS' => array( 24, 1, 1, 0 ),
1405 'INT' => array( 25, 1, 1, 0 ),
1406 'SIGN' => array( 26, 1, 1, 0 ),
1407 'ROUND' => array( 27, 2, 1, 0 ),
1408 'LOOKUP' => array( 28, -1, 0, 0 ),
1409 'INDEX' => array( 29, -1, 0, 1 ),
1410 'REPT' => array( 30, 2, 1, 0 ),
1411 'MID' => array( 31, 3, 1, 0 ),
1412 'LEN' => array( 32, 1, 1, 0 ),
1413 'VALUE' => array( 33, 1, 1, 0 ),
1414 'TRUE' => array( 34, 0, 1, 0 ),
1415 'FALSE' => array( 35, 0, 1, 0 ),
1416 'AND' => array( 36, -1, 0, 0 ),
1417 'OR' => array( 37, -1, 0, 0 ),
1418 'NOT' => array( 38, 1, 1, 0 ),
1419 'MOD' => array( 39, 2, 1, 0 ),
1420 'DCOUNT' => array( 40, 3, 0, 0 ),
1421 'DSUM' => array( 41, 3, 0, 0 ),
1422 'DAVERAGE' => array( 42, 3, 0, 0 ),
1423 'DMIN' => array( 43, 3, 0, 0 ),
1424 'DMAX' => array( 44, 3, 0, 0 ),
1425 'DSTDEV' => array( 45, 3, 0, 0 ),
1426 'VAR' => array( 46, -1, 0, 0 ),
1427 'DVAR' => array( 47, 3, 0, 0 ),
1428 'TEXT' => array( 48, 2, 1, 0 ),
1429 'LINEST' => array( 49, -1, 0, 0 ),
1430 'TREND' => array( 50, -1, 0, 0 ),
1431 'LOGEST' => array( 51, -1, 0, 0 ),
1432 'GROWTH' => array( 52, -1, 0, 0 ),
1433 'PV' => array( 56, -1, 1, 0 ),
1434 'FV' => array( 57, -1, 1, 0 ),
1435 'NPER' => array( 58, -1, 1, 0 ),
1436 'PMT' => array( 59, -1, 1, 0 ),
1437 'RATE' => array( 60, -1, 1, 0 ),
1438 'MIRR' => array( 61, 3, 0, 0 ),
1439 'IRR' => array( 62, -1, 0, 0 ),
1440 'RAND' => array( 63, 0, 1, 1 ),
1441 'MATCH' => array( 64, -1, 0, 0 ),
1442 'DATE' => array( 65, 3, 1, 0 ),
1443 'TIME' => array( 66, 3, 1, 0 ),
1444 'DAY' => array( 67, 1, 1, 0 ),
1445 'MONTH' => array( 68, 1, 1, 0 ),
1446 'YEAR' => array( 69, 1, 1, 0 ),
1447 'WEEKDAY' => array( 70, -1, 1, 0 ),
1448 'HOUR' => array( 71, 1, 1, 0 ),
1449 'MINUTE' => array( 72, 1, 1, 0 ),
1450 'SECOND' => array( 73, 1, 1, 0 ),
1451 'NOW' => array( 74, 0, 1, 1 ),
1452 'AREAS' => array( 75, 1, 0, 1 ),
1453 'ROWS' => array( 76, 1, 0, 1 ),
1454 'COLUMNS' => array( 77, 1, 0, 1 ),
1455 'OFFSET' => array( 78, -1, 0, 1 ),
1456 'SEARCH' => array( 82, -1, 1, 0 ),
1457 'TRANSPOSE' => array( 83, 1, 1, 0 ),
1458 'TYPE' => array( 86, 1, 1, 0 ),
1459 'ATAN2' => array( 97, 2, 1, 0 ),
1460 'ASIN' => array( 98, 1, 1, 0 ),
1461 'ACOS' => array( 99, 1, 1, 0 ),
1462 'CHOOSE' => array( 100, -1, 1, 0 ),
1463 'HLOOKUP' => array( 101, -1, 0, 0 ),
1464 'VLOOKUP' => array( 102, -1, 0, 0 ),
1465 'ISREF' => array( 105, 1, 0, 0 ),
1466 'LOG' => array( 109, -1, 1, 0 ),
1467 'CHAR' => array( 111, 1, 1, 0 ),
1468 'LOWER' => array( 112, 1, 1, 0 ),
1469 'UPPER' => array( 113, 1, 1, 0 ),
1470 'PROPER' => array( 114, 1, 1, 0 ),
1471 'LEFT' => array( 115, -1, 1, 0 ),
1472 'RIGHT' => array( 116, -1, 1, 0 ),
1473 'EXACT' => array( 117, 2, 1, 0 ),
1474 'TRIM' => array( 118, 1, 1, 0 ),
1475 'REPLACE' => array( 119, 4, 1, 0 ),
1476 'SUBSTITUTE' => array( 120, -1, 1, 0 ),
1477 'CODE' => array( 121, 1, 1, 0 ),
1478 'FIND' => array( 124, -1, 1, 0 ),
1479 'CELL' => array( 125, -1, 0, 1 ),
1480 'ISERR' => array( 126, 1, 1, 0 ),
1481 'ISTEXT' => array( 127, 1, 1, 0 ),
1482 'ISNUMBER' => array( 128, 1, 1, 0 ),
1483 'ISBLANK' => array( 129, 1, 1, 0 ),
1484 'T' => array( 130, 1, 0, 0 ),
1485 'N' => array( 131, 1, 0, 0 ),
1486 'DATEVALUE' => array( 140, 1, 1, 0 ),
1487 'TIMEVALUE' => array( 141, 1, 1, 0 ),
1488 'SLN' => array( 142, 3, 1, 0 ),
1489 'SYD' => array( 143, 4, 1, 0 ),
1490 'DDB' => array( 144, -1, 1, 0 ),
1491 'INDIRECT' => array( 148, -1, 1, 1 ),
1492 'CALL' => array( 150, -1, 1, 0 ),
1493 'CLEAN' => array( 162, 1, 1, 0 ),
1494 'MDETERM' => array( 163, 1, 2, 0 ),
1495 'MINVERSE' => array( 164, 1, 2, 0 ),
1496 'MMULT' => array( 165, 2, 2, 0 ),
1497 'IPMT' => array( 167, -1, 1, 0 ),
1498 'PPMT' => array( 168, -1, 1, 0 ),
1499 'COUNTA' => array( 169, -1, 0, 0 ),
1500 'PRODUCT' => array( 183, -1, 0, 0 ),
1501 'FACT' => array( 184, 1, 1, 0 ),
1502 'DPRODUCT' => array( 189, 3, 0, 0 ),
1503 'ISNONTEXT' => array( 190, 1, 1, 0 ),
1504 'STDEVP' => array( 193, -1, 0, 0 ),
1505 'VARP' => array( 194, -1, 0, 0 ),
1506 'DSTDEVP' => array( 195, 3, 0, 0 ),
1507 'DVARP' => array( 196, 3, 0, 0 ),
1508 'TRUNC' => array( 197, -1, 1, 0 ),
1509 'ISLOGICAL' => array( 198, 1, 1, 0 ),
1510 'DCOUNTA' => array( 199, 3, 0, 0 ),
1511 'ROUNDUP' => array( 212, 2, 1, 0 ),
1512 'ROUNDDOWN' => array( 213, 2, 1, 0 ),
1513 'RANK' => array( 216, -1, 0, 0 ),
1514 'ADDRESS' => array( 219, -1, 1, 0 ),
1515 'DAYS360' => array( 220, -1, 1, 0 ),
1516 'TODAY' => array( 221, 0, 1, 1 ),
1517 'VDB' => array( 222, -1, 1, 0 ),
1518 'MEDIAN' => array( 227, -1, 0, 0 ),
1519 'SUMPRODUCT' => array( 228, -1, 2, 0 ),
1520 'SINH' => array( 229, 1, 1, 0 ),
1521 'COSH' => array( 230, 1, 1, 0 ),
1522 'TANH' => array( 231, 1, 1, 0 ),
1523 'ASINH' => array( 232, 1, 1, 0 ),
1524 'ACOSH' => array( 233, 1, 1, 0 ),
1525 'ATANH' => array( 234, 1, 1, 0 ),
1526 'DGET' => array( 235, 3, 0, 0 ),
1527 'INFO' => array( 244, 1, 1, 1 ),
1528 'DB' => array( 247, -1, 1, 0 ),
1529 'FREQUENCY' => array( 252, 2, 0, 0 ),
1530 'ERROR.TYPE' => array( 261, 1, 1, 0 ),
1531 'REGISTER.ID' => array( 267, -1, 1, 0 ),
1532 'AVEDEV' => array( 269, -1, 0, 0 ),
1533 'BETADIST' => array( 270, -1, 1, 0 ),
1534 'GAMMALN' => array( 271, 1, 1, 0 ),
1535 'BETAINV' => array( 272, -1, 1, 0 ),
1536 'BINOMDIST' => array( 273, 4, 1, 0 ),
1537 'CHIDIST' => array( 274, 2, 1, 0 ),
1538 'CHIINV' => array( 275, 2, 1, 0 ),
1539 'COMBIN' => array( 276, 2, 1, 0 ),
1540 'CONFIDENCE' => array( 277, 3, 1, 0 ),
1541 'CRITBINOM' => array( 278, 3, 1, 0 ),
1542 'EVEN' => array( 279, 1, 1, 0 ),
1543 'EXPONDIST' => array( 280, 3, 1, 0 ),
1544 'FDIST' => array( 281, 3, 1, 0 ),
1545 'FINV' => array( 282, 3, 1, 0 ),
1546 'FISHER' => array( 283, 1, 1, 0 ),
1547 'FISHERINV' => array( 284, 1, 1, 0 ),
1548 'FLOOR' => array( 285, 2, 1, 0 ),
1549 'GAMMADIST' => array( 286, 4, 1, 0 ),
1550 'GAMMAINV' => array( 287, 3, 1, 0 ),
1551 'CEILING' => array( 288, 2, 1, 0 ),
1552 'HYPGEOMDIST' => array( 289, 4, 1, 0 ),
1553 'LOGNORMDIST' => array( 290, 3, 1, 0 ),
1554 'LOGINV' => array( 291, 3, 1, 0 ),
1555 'NEGBINOMDIST' => array( 292, 3, 1, 0 ),
1556 'NORMDIST' => array( 293, 4, 1, 0 ),
1557 'NORMSDIST' => array( 294, 1, 1, 0 ),
1558 'NORMINV' => array( 295, 3, 1, 0 ),
1559 'NORMSINV' => array( 296, 1, 1, 0 ),
1560 'STANDARDIZE' => array( 297, 3, 1, 0 ),
1561 'ODD' => array( 298, 1, 1, 0 ),
1562 'PERMUT' => array( 299, 2, 1, 0 ),
1563 'POISSON' => array( 300, 3, 1, 0 ),
1564 'TDIST' => array( 301, 3, 1, 0 ),
1565 'WEIBULL' => array( 302, 4, 1, 0 ),
1566 'SUMXMY2' => array( 303, 2, 2, 0 ),
1567 'SUMX2MY2' => array( 304, 2, 2, 0 ),
1568 'SUMX2PY2' => array( 305, 2, 2, 0 ),
1569 'CHITEST' => array( 306, 2, 2, 0 ),
1570 'CORREL' => array( 307, 2, 2, 0 ),
1571 'COVAR' => array( 308, 2, 2, 0 ),
1572 'FORECAST' => array( 309, 3, 2, 0 ),
1573 'FTEST' => array( 310, 2, 2, 0 ),
1574 'INTERCEPT' => array( 311, 2, 2, 0 ),
1575 'PEARSON' => array( 312, 2, 2, 0 ),
1576 'RSQ' => array( 313, 2, 2, 0 ),
1577 'STEYX' => array( 314, 2, 2, 0 ),
1578 'SLOPE' => array( 315, 2, 2, 0 ),
1579 'TTEST' => array( 316, 4, 2, 0 ),
1580 'PROB' => array( 317, -1, 2, 0 ),
1581 'DEVSQ' => array( 318, -1, 0, 0 ),
1582 'GEOMEAN' => array( 319, -1, 0, 0 ),
1583 'HARMEAN' => array( 320, -1, 0, 0 ),
1584 'SUMSQ' => array( 321, -1, 0, 0 ),
1585 'KURT' => array( 322, -1, 0, 0 ),
1586 'SKEW' => array( 323, -1, 0, 0 ),
1587 'ZTEST' => array( 324, -1, 0, 0 ),
1588 'LARGE' => array( 325, 2, 0, 0 ),
1589 'SMALL' => array( 326, 2, 0, 0 ),
1590 'QUARTILE' => array( 327, 2, 0, 0 ),
1591 'PERCENTILE' => array( 328, 2, 0, 0 ),
1592 'PERCENTRANK' => array( 329, -1, 0, 0 ),
1593 'MODE' => array( 330, -1, 2, 0 ),
1594 'TRIMMEAN' => array( 331, 2, 0, 0 ),
1595 'TINV' => array( 332, 2, 1, 0 ),
1596 'CONCATENATE' => array( 336, -1, 1, 0 ),
1597 'POWER' => array( 337, 2, 1, 0 ),
1598 'RADIANS' => array( 342, 1, 1, 0 ),
1599 'DEGREES' => array( 343, 1, 1, 0 ),
1600 'SUBTOTAL' => array( 344, -1, 0, 0 ),
1601 'SUMIF' => array( 345, -1, 0, 0 ),
1602 'COUNTIF' => array( 346, 2, 0, 0 ),
1603 'COUNTBLANK' => array( 347, 1, 0, 0 ),
1604 'ROMAN' => array( 354, -1, 1, 0 )
1609 * Convert a token to the proper ptg value.
1611 * @param mixed $token The token to convert.
1613 function _convert($token)
1615 if(is_numeric($token))
1617 return($this->_convert_number($token));
1619 // match references like A1
1620 elseif(preg_match("/^([A-I]?[A-Z])(\d+)$/",$token))
1622 return($this->_convert_ref2d($token));
1624 // match ranges like A1:B2
1625 elseif(preg_match("/^([A-I]?[A-Z])(\d+)\:([A-I]?[A-Z])(\d+)$/",$token))
1627 return($this->_convert_range2d($token));
1629 // match ranges like A1..B2
1630 elseif(preg_match("/^([A-I]?[A-Z])(\d+)\.\.([A-I]?[A-Z])(\d+)$/",$token))
1632 return($this->_convert_range2d($token));
1634 elseif(isset($this->ptg[$token])) // operators (including parentheses)
1636 return(pack("C", $this->ptg[$token]));
1638 elseif(preg_match("/[A-Z0-9À-Ü\.]+/",$token))
1640 return($this->_convert_function($token,$this->_func_args));
1642 // if it's an argument, ignore the token (the argument remains)
1643 elseif($token == 'arg')
1645 $this->_func_args++;
1648 die("Unknown token $token");
1652 * Convert a number token to ptgInt or ptgNum
1654 * @param mixed $num an integer or double for conersion to its ptg value
1656 function _convert_number($num)
1658 // Integer in the range 0..2**16-1
1659 if ((preg_match("/^\d+$/",$num)) and ($num <= 65535)) {
1660 return pack("Cv", $this->ptg['ptgInt'], $num);
1664 if($this->_byte_order) // if it's Big Endian
1666 $num = strrev($num);
1668 return pack("Cd", $this->ptg['ptgNum'], $num);
1673 * Convert a function to a ptgFunc or ptgFuncVarV depending on the number of
1674 * args that it takes.
1676 * @param string $token The name of the function for convertion to ptg value.
1677 * @param integer $num_args The number of arguments the function recieves.
1679 function _convert_function($token, $num_args)
1681 $this->_func_args = 0; // re initialize the number of arguments
1682 $args = $this->_functions[$token][1];
1683 $volatile = $this->_functions[$token][3];
1686 $this->_volatile = 1;
1688 // Fixed number of args eg. TIME($i,$j,$k).
1691 return(pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]));
1693 // Variable number of args eg. SUM($i,$j,$k, ..).
1695 return(pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]));
1700 * Convert an Excel range such as A1:D4 to a ptgRefV.
1702 * @param string $range An Excel range in the A1:A2 or A1..A2 format.
1704 function _convert_range2d($range)
1706 $class = 2; // as far as I know, this is magick.
1708 // Split the range into 2 cell refs
1709 if(preg_match("/^([A-I]?[A-Z])(\d+)\:([A-I]?[A-Z])(\d+)$/",$range)) {
1710 list($cell1, $cell2) = split(':', $range);
1712 elseif(preg_match("/^([A-I]?[A-Z])(\d+)\.\.([A-I]?[A-Z])(\d+)$/",$range)) {
1713 list($cell1, $cell2) = split('\.\.', $range);
1716 die("Unknown range separator");
1719 // Convert the cell references
1720 list($row1, $col1) = $this->_cell_to_packed_rowcol($cell1);
1721 list($row2, $col2) = $this->_cell_to_packed_rowcol($cell2);
1723 // The ptg value depends on the class of the ptg.
1725 $ptgArea = pack("C", $this->ptg['ptgArea']);
1727 elseif ($class == 1) {
1728 $ptgArea = pack("C", $this->ptg['ptgAreaV']);
1730 elseif ($class == 2) {
1731 $ptgArea = pack("C", $this->ptg['ptgAreaA']);
1734 die("Unknown class ");
1737 return($ptgArea . $row1 . $row2 . $col1. $col2);
1741 * Convert an Excel reference such as A1, $B2, C$3 or $D$4 to a ptgRefV.
1743 * @param string $cell An Excel cell reference
1745 function _convert_ref2d($cell)
1747 $class = 2; // as far as I know, this is magick.
1749 // Convert the cell reference
1750 list($row, $col) = $this->_cell_to_packed_rowcol($cell);
1752 // The ptg value depends on the class of the ptg.
1754 $ptgRef = pack("C", $this->ptg['ptgRef']);
1756 elseif ($class == 1) {
1757 $ptgRef = pack("C", $this->ptg['ptgRefV']);
1759 elseif ($class == 2) {
1760 $ptgRef = pack("C", $this->ptg['ptgRefA']);
1763 die("Unknown class ");
1765 return $ptgRef.$row.$col;
1769 * pack() row and column into the required 3 byte format.
1771 * @param string $cell The Excel cell reference to be packed
1773 function _cell_to_packed_rowcol($cell)
1775 list($row, $col, $row_rel, $col_rel) = $this->_cell_to_rowcol($cell);
1777 die("Column in: $cell greater than 255 ");
1779 if ($row >= 16384) {
1780 die("Row in: $cell greater than 16384 ");
1783 // Set the high bits to indicate if row or col are relative.
1784 $row |= $col_rel << 14;
1785 $row |= $row_rel << 15;
1787 $row = pack('v', $row);
1788 $col = pack('C', $col);
1790 return (array($row, $col));
1794 * Convert an Excel cell reference such as A1 or $B2 or C$3 or $D$4 to a zero
1795 * indexed row and column number. Also returns two boolean values to indicate
1796 * whether the row or column are relative references.
1798 * @param string $cell The Excel cell reference in A1 format.
1800 function _cell_to_rowcol($cell)
1802 preg_match('/(\$)?([A-I]?[A-Z])(\$)?(\d+)/',$cell,$match);
1803 // return absolute column if there is a $ in the ref
1804 $col_rel = empty($match[1]) ? 1 : 0;
1805 $col_ref = $match[2];
1806 $row_rel = empty($match[3]) ? 1 : 0;
1809 // Convert base26 column string to a number.
1810 $expn = strlen($col_ref) - 1;
1812 for($i=0; $i < strlen($col_ref); $i++)
1814 $col += (ord($col_ref{$i}) - ord('A') + 1) * pow(26, $expn);
1818 // Convert 1-index to zero-index
1822 return(array($row, $col, $row_rel, $col_rel));
1826 * Advance to the next valid token.
1830 $i = $this->_current_char;
1831 // eat up white spaces
1832 if($i < strlen($this->_formula))
1834 while($this->_formula{$i} == " ")
1838 if($i < strlen($this->_formula) - 1)
1840 $this->_lookahead = $this->_formula{$i+1};
1844 while($i < strlen($this->_formula))
1846 $token .= $this->_formula{$i};
1847 if($this->_match($token) != '')
1849 if($i < strlen($this->_formula) - 1)
1851 $this->_lookahead = $this->_formula{$i+1};
1853 $this->_current_char = $i + 1;
1854 $this->_current_token = $token;
1857 $this->_lookahead = $this->_formula{$i+2};
1860 //die("Lexical error ".$this->_current_char);
1864 * Checks if it's a valid token.
1866 * @param mixed $token The token to check.
1868 function _match($token)
1894 // if it's a reference
1895 if(eregi("^[A-I]?[A-Z][0-9]+$",$token) and
1896 !ereg("[0-9]",$this->_lookahead) and
1897 ($this->_lookahead != ':') and ($this->_lookahead != '.'))
1901 // if it's a range (A1:A2)
1902 elseif(eregi("^[A-I]?[A-Z][0-9]+:[A-I]?[A-Z][0-9]+$",$token) and
1903 !ereg("[0-9]",$this->_lookahead))
1907 // if it's a range (A1..A2)
1908 elseif(eregi("^[A-I]?[A-Z][0-9]+\.\.[A-I]?[A-Z][0-9]+$",$token) and
1909 !ereg("[0-9]",$this->_lookahead))
1913 elseif(is_numeric($token) and !is_numeric($token.$this->_lookahead))
1917 // if it's a function call
1918 elseif(eregi("^[A-Z0-9À-Ü\.]+$",$token) and ($this->_lookahead == "("))
1928 * The parsing method. It parses a formula.
1931 * @param string $formula The formula to parse, without the initial equal sign (=).
1933 function parse($formula)
1935 $this->_current_char = 0;
1936 $this->_formula = $formula;
1937 $this->_lookahead = $formula{1};
1939 $this->_parse_tree = $this->_expression();
1943 * It parses a expression. It assumes the following rule:
1944 * Expr -> Term [("+" | "-") Term]
1946 * @return mixed The parsed ptg'd tree
1948 function _expression()
1950 $result = $this->_term();
1951 while ($this->_current_token == ADD or $this->_current_token == SUB)
1953 if ($this->_current_token == ADD)
1956 $result = $this->_create_tree('ptgAdd', $result, $this->_term());
1961 $result = $this->_create_tree('ptgSub', $result, $this->_term());
1968 * This function just introduces a ptgParen element in the tree, so that Excel
1969 * doesn't get confused when working with a parenthesized formula afterwards.
1972 * @return mixed The parsed ptg'd tree
1974 function _parenthesized_expression()
1976 $result = $this->_create_tree('ptgParen', $this->_expression(), '');
1981 * It parses a term. It assumes the following rule:
1982 * Term -> Fact [("*" | "/") Fact]
1984 * @return mixed The parsed ptg'd tree
1988 $result = $this->_fact();
1989 while ($this->_current_token == MUL || $this->_current_token == DIV)
1991 if ($this->_current_token == MUL)
1994 $result = $this->_create_tree('ptgMul', $result, $this->_fact());
1999 $result = $this->_create_tree('ptgDiv', $result, $this->_fact());
2006 * It parses a factor. It assumes the following rule:
2013 * @return mixed The parsed ptg'd tree
2017 if ($this->_current_token == OPEN)
2019 $this->_advance(); // eat the "("
2020 $result = $this->_parenthesized_expression();//$this->_expression();
2022 if ($this->_current_token != CLOSE) {
2023 die("')' token expected.");
2025 $this->_advance(); // eat the ")"
2028 // if it's a reference
2029 if (eregi("^[A-I]?[A-Z][0-9]+$",$this->_current_token))
2031 $result = $this->_create_tree($this->_current_token, '', '');
2036 elseif (eregi("^[A-I]?[A-Z][0-9]+:[A-I]?[A-Z][0-9]+$",$this->_current_token) or
2037 eregi("^[A-I]?[A-Z][0-9]+\.\.[A-I]?[A-Z][0-9]+$",$this->_current_token))
2039 $result = $this->_current_token;
2043 elseif (is_numeric($this->_current_token))
2045 $result = $this->_create_tree($this->_current_token, '', '');
2049 // if it's a function call
2050 elseif (eregi("^[A-Z0-9À-Ü\.]+$",$this->_current_token))
2052 $result = $this->_func();
2055 die("Sintactic error: ".$this->_current_token.", lookahead: ".
2056 $this->_lookahead.", current char: ".$this->_current_char);
2060 * It parses a function call. It assumes the following rule:
2061 * Func -> ( Expr [,Expr]* )
2066 $num_args = 0; // number of arguments received
2067 $function = $this->_current_token;
2069 $this->_advance(); // eat the "("
2070 while($this->_current_token != ')')
2074 if($this->_current_token == COMA) {
2075 $this->_advance(); // eat the ","
2078 die("Sintactic error: coma expected $num_args");
2080 $result = $this->_create_tree('arg', $result, $this->_expression());
2083 $result = $this->_create_tree('arg', '', $this->_expression());
2087 $args = $this->_functions[$function][1];
2088 // If fixed number of args eg. TIME($i,$j,$k). Check that the number of args is valid.
2089 if (($args >= 0) and ($args != $num_args))
2091 die("Incorrect number of arguments in function $function() ");
2094 $result = $this->_create_tree($function, $result, '');
2095 $this->_advance(); // eat the ")"
2100 * Creates a tree. In fact an array which may have one or two arrays (sub-trees)
2103 * @param mixed $value The value of this node.
2104 * @param mixed $left The left array (sub-tree) or a final node.
2105 * @param mixed $right The right array (sub-tree) or a final node.
2107 function _create_tree($value, $left, $right)
2109 return array('value' => $value, 'left' => $left, 'right' => $right);
2113 * Builds a string containing the tree in reverse polish notation (What you
2114 * would use in a HP calculator stack).
2115 * The following tree:
2123 * The following tree:
2131 * produces: "36A1*+"
2133 * In fact all operands, functions, references, etc... are written as ptg's
2136 * @param array $tree The optional tree to convert.
2138 function to_reverse_polish($tree = array())
2140 $polish = ""; // the string we are going to return
2141 if (empty($tree)) // If it's the first call use _parse_tree
2143 $tree = $this->_parse_tree;
2145 if (is_array($tree['left']))
2147 $polish .= $this->to_reverse_polish($tree['left']);
2149 elseif($tree['left'] != '') // It's a final node
2151 $polish .= $this->_convert($tree['left']); //$tree['left'];
2153 if (is_array($tree['right']))
2155 $polish .= $this->to_reverse_polish($tree['right']);
2157 elseif($tree['right'] != '') // It's a final node
2159 $polish .= $this->_convert($tree['right']);
2161 $polish .= $this->_convert($tree['value']);
2167 * Class for generating Excel Spreadsheets
2169 * @author Xavier Noguer <xnoguer@rezebra.com>
2170 * @package Spreadsheet_WriteExcel
2173 class Worksheet extends BIFFwriter
2179 * @param string $name The name of the new worksheet
2180 * @param integer $index The index of the new worksheet
2181 * @param mixed &$activesheet The current activesheet of the workbook we belong to
2182 * @param mixed &$firstsheet The first worksheet in the workbook we belong to
2183 * @param mixed &$url_format The default format for hyperlinks
2184 * @param mixed &$parser The formula parser created for the Workbook
2186 function Worksheet($name,$index,&$activesheet,&$firstsheet,&$url_format,&$parser)
2188 $this->BIFFwriter(); // It needs to call its parent's constructor explicitly
2189 $rowmax = 65536; // 16384 in Excel 5
2193 $this->name = $name;
2194 $this->index = $index;
2195 $this->activesheet = &$activesheet;
2196 $this->firstsheet = &$firstsheet;
2197 $this->_url_format = $url_format;
2198 $this->_parser = &$parser;
2200 $this->ext_sheets = array();
2201 $this->_using_tmpfile = 1;
2202 $this->_filehandle = "";
2203 $this->fileclosed = 0;
2205 $this->xls_rowmax = $rowmax;
2206 $this->xls_colmax = $colmax;
2207 $this->xls_strmax = $strmax;
2208 $this->dim_rowmin = $rowmax +1;
2209 $this->dim_rowmax = 0;
2210 $this->dim_colmin = $colmax +1;
2211 $this->dim_colmax = 0;
2212 $this->colinfo = array();
2213 $this->_selection = array(0,0,0,0);
2214 $this->_panes = array();
2215 $this->_active_pane = 3;
2217 $this->selected = 0;
2219 $this->_paper_size = 0x0;
2220 $this->_orientation = 0x1;
2221 $this->_header = '';
2222 $this->_footer = '';
2223 $this->_hcenter = 0;
2224 $this->_vcenter = 0;
2225 $this->_margin_head = 0.50;
2226 $this->_margin_foot = 0.50;
2227 $this->_margin_left = 0.75;
2228 $this->_margin_right = 0.75;
2229 $this->_margin_top = 1.00;
2230 $this->_margin_bottom = 1.00;
2232 $this->_title_rowmin = NULL;
2233 $this->_title_rowmax = NULL;
2234 $this->_title_colmin = NULL;
2235 $this->_title_colmax = NULL;
2236 $this->_print_rowmin = NULL;
2237 $this->_print_rowmax = NULL;
2238 $this->_print_colmin = NULL;
2239 $this->_print_colmax = NULL;
2241 $this->_print_gridlines = 1;
2242 $this->_print_headers = 0;
2244 $this->_fit_page = 0;
2245 $this->_fit_width = 0;
2246 $this->_fit_height = 0;
2248 $this->_hbreaks = array();
2249 $this->_vbreaks = array();
2251 $this->_protect = 0;
2252 $this->_password = NULL;
2254 $this->col_sizes = array();
2255 $this->row_sizes = array();
2258 $this->_print_scale = 100;
2259 $this->_rtl = 0; // Added by Joe Hunt 2009-03-05 for arabic languages
2261 $this->_initialize();
2265 * Open a tmp file to store the majority of the Worksheet data. If this fails,
2266 * for example due to write permissions, store the data in memory. This can be
2267 * slow for large files.
2269 function _initialize()
2271 // Open tmp file for storing Worksheet data
2275 $this->_filehandle = $fh;
2278 // If tmpfile() fails store data in memory
2279 $this->_using_tmpfile = 0;
2284 * Add data to the beginning of the workbook (note the reverse order)
2285 * and to the end of the workbook.
2288 * @see Workbook::store_workbook()
2289 * @param array $sheetnames The array of sheetnames from the Workbook this
2290 * worksheet belongs to
2292 function close($sheetnames)
2294 $num_sheets = count($sheetnames);
2296 /***********************************************
2297 * Prepend in reverse order!!
2300 // Prepend the sheet dimensions
2301 $this->_store_dimensions();
2303 // Prepend the sheet password
2304 $this->_store_password();
2306 // Prepend the sheet protection
2307 $this->_store_protect();
2309 // Prepend the page setup
2310 $this->_store_setup();
2312 // Prepend the bottom margin
2313 $this->_store_margin_bottom();
2315 // Prepend the top margin
2316 $this->_store_margin_top();
2318 // Prepend the right margin
2319 $this->_store_margin_right();
2321 // Prepend the left margin
2322 $this->_store_margin_left();
2324 // Prepend the page vertical centering
2325 $this->store_vcenter();
2327 // Prepend the page horizontal centering
2328 $this->store_hcenter();
2330 // Prepend the page footer
2331 $this->store_footer();
2333 // Prepend the page header
2334 $this->store_header();
2336 // Prepend the vertical page breaks
2337 $this->_store_vbreak();
2339 // Prepend the horizontal page breaks
2340 $this->_store_hbreak();
2343 $this->_store_wsbool();
2346 $this->_store_gridset();
2348 // Prepend PRINTGRIDLINES
2349 $this->_store_print_gridlines();
2351 // Prepend PRINTHEADERS
2352 $this->_store_print_headers();
2354 // Prepend EXTERNSHEET references
2355 for ($i = $num_sheets; $i > 0; $i--) {
2356 $sheetname = $sheetnames[$i-1];
2357 $this->_store_externsheet($sheetname);
2360 // Prepend the EXTERNCOUNT of external references.
2361 $this->_store_externcount($num_sheets);
2363 // Prepend the COLINFO records if they exist
2364 if (!empty($this->colinfo)){
2365 for($i=0; $i < count($this->colinfo); $i++)
2367 $this->_store_colinfo($this->colinfo[$i]);
2369 $this->_store_defcol();
2372 // Prepend the BOF record
2373 $this->_store_bof(0x0010);
2376 * End of prepend. Read upwards from here.
2377 ***********************************************/
2380 $this->_store_window2();
2381 $this->_store_zoom();
2382 if(!empty($this->_panes))
2383 $this->_store_panes($this->_panes);
2384 $this->_store_selection($this->_selection);
2385 $this->_store_eof();
2389 * Retrieve the worksheet name. This is usefull when creating worksheets
2393 * @return string The worksheet's name
2397 return($this->name);
2401 * Retrieves data from memory in one chunk, or from disk in $buffer
2404 * @return string The data
2410 // Return data stored in memory
2411 if (isset($this->_data)) {
2412 $tmp = $this->_data;
2413 unset($this->_data);
2414 $fh = $this->_filehandle;
2415 if ($this->_using_tmpfile) {
2420 // Return data stored on disk
2421 if ($this->_using_tmpfile) {
2422 if ($tmp = fread($this->_filehandle, $buffer)) {
2427 // No data to return
2432 * Set this worksheet as a selected worksheet, i.e. the worksheet has its tab
2439 $this->selected = 1;
2443 * Set this worksheet as the active worksheet, i.e. the worksheet that is
2444 * displayed when the workbook is opened. Also set it as selected.
2450 $this->selected = 1;
2451 $this->activesheet =& $this->index;
2455 * Set this worksheet as the first visible sheet. This is necessary
2456 * when there are a large number of worksheets and the activated
2457 * worksheet is not visible on the screen.
2461 function set_first_sheet()
2463 $this->firstsheet = $this->index;
2467 * Set the worksheet protection flag to prevent accidental modification and to
2468 * hide formulas if the locked and hidden format properties have been set.
2471 * @param string $password The password to use for protecting the sheet.
2473 function protect($password)
2475 $this->_protect = 1;
2476 $this->_password = $this->_encode_password($password);
2480 * Set the width of a single column or a range of columns.
2483 * @see _store_colinfo()
2484 * @param integer $firstcol first column on the range
2485 * @param integer $lastcol last column on the range
2486 * @param integer $width width to set
2487 * @param mixed $format The optional XF format to apply to the columns
2488 * @param integer $hidden The optional hidden atribute
2490 function set_column($firstcol, $lastcol, $width, $format = null, $hidden = 0)
2492 $this->colinfo[] = array($firstcol, $lastcol, $width, $format, $hidden);
2494 // Set width to zero if column is hidden
2495 $width = ($hidden) ? 0 : $width;
2497 for($col = $firstcol; $col <= $lastcol; $col++) {
2498 $this->col_sizes[$col] = $width;
2503 * Set which cell or cells are selected in a worksheet
2506 * @param integer $first_row first row in the selected quadrant
2507 * @param integer $first_column first column in the selected quadrant
2508 * @param integer $last_row last row in the selected quadrant
2509 * @param integer $last_column last column in the selected quadrant
2510 * @see _store_selection()
2512 function set_selection($first_row,$first_column,$last_row,$last_column)
2514 $this->_selection = array($first_row,$first_column,$last_row,$last_column);
2518 * Set panes and mark them as frozen.
2521 * @param array $panes This is the only parameter received and is composed of the following:
2522 * 0 => Vertical split position,
2523 * 1 => Horizontal split position
2524 * 2 => Top row visible
2525 * 3 => Leftmost column visible
2528 function freeze_panes($panes)
2531 $this->_panes = $panes;
2535 * Set panes and mark them as unfrozen.
2538 * @param array $panes This is the only parameter received and is composed of the following:
2539 * 0 => Vertical split position,
2540 * 1 => Horizontal split position
2541 * 2 => Top row visible
2542 * 3 => Leftmost column visible
2545 function thaw_panes($panes)
2548 $this->_panes = $panes;
2552 * Set the page orientation as portrait.
2556 function set_portrait()
2558 $this->_orientation = 1;
2562 * Set the page orientation as landscape.
2566 function set_landscape()
2568 $this->_orientation = 0;
2572 * Set the paper type. Ex. 1 = US Letter, 9 = A4
2575 * @param integer $size The type of paper size to use
2577 function set_paper($size = 0)
2579 $this->_paper_size = $size;
2584 * Set the page header caption and optional margin.
2587 * @param string $string The header text
2588 * @param float $margin optional head margin in inches.
2590 function set_header($string,$margin = 0.50)
2592 if (strlen($string) >= 255) {
2593 //carp 'Header string must be less than 255 characters';
2596 $this->_header = $string;
2597 $this->_margin_head = $margin;
2601 * Set the page footer caption and optional margin.
2604 * @param string $string The footer text
2605 * @param float $margin optional foot margin in inches.
2607 function set_footer($string,$margin = 0.50)
2609 if (strlen($string) >= 255) {
2610 //carp 'Footer string must be less than 255 characters';
2613 $this->_footer = $string;
2614 $this->_margin_foot = $margin;
2618 * Center the page horinzontally.
2621 * @param integer $center the optional value for centering. Defaults to 1 (center).
2623 function center_horizontally($center = 1)
2625 $this->_hcenter = $center;
2629 * Center the page horinzontally.
2632 * @param integer $center the optional value for centering. Defaults to 1 (center).
2634 function center_vertically($center = 1)
2636 $this->_vcenter = $center;
2640 * Set all the page margins to the same value in inches.
2643 * @param float $margin The margin to set in inches
2645 function set_margins($margin)
2647 $this->set_margin_left($margin);
2648 $this->set_margin_right($margin);
2649 $this->set_margin_top($margin);
2650 $this->set_margin_bottom($margin);
2654 * Set the left and right margins to the same value in inches.
2657 * @param float $margin The margin to set in inches
2659 function set_margins_LR($margin)
2661 $this->set_margin_left($margin);
2662 $this->set_margin_right($margin);
2666 * Set the top and bottom margins to the same value in inches.
2669 * @param float $margin The margin to set in inches
2671 function set_margins_TB($margin)
2673 $this->set_margin_top($margin);
2674 $this->set_margin_bottom($margin);
2678 * Set the left margin in inches.
2681 * @param float $margin The margin to set in inches
2683 function set_margin_left($margin = 0.75)
2685 $this->_margin_left = $margin;
2689 * Set the right margin in inches.
2692 * @param float $margin The margin to set in inches
2694 function set_margin_right($margin = 0.75)
2696 $this->_margin_right = $margin;
2700 * Set the top margin in inches.
2703 * @param float $margin The margin to set in inches
2705 function set_margin_top($margin = 1.00)
2707 $this->_margin_top = $margin;
2711 * Set the bottom margin in inches.
2714 * @param float $margin The margin to set in inches
2716 function set_margin_bottom($margin = 1.00)
2718 $this->_margin_bottom = $margin;
2722 * Set the rows to repeat at the top of each printed page. See also the
2723 * _store_name_xxxx() methods in Workbook.php
2726 * @param integer $first_row First row to repeat
2727 * @param integer $last_row Last row to repeat. Optional.
2729 function repeat_rows($first_row, $last_row = NULL)
2731 $this->_title_rowmin = $first_row;
2732 if(isset($last_row)) { //Second row is optional
2733 $this->_title_rowmax = $last_row;
2736 $this->_title_rowmax = $first_row;
2741 * Set the columns to repeat at the left hand side of each printed page.
2742 * See also the _store_names() methods in Workbook.php
2745 * @param integer $first_col First column to repeat
2746 * @param integer $last_col Last column to repeat. Optional.
2748 function repeat_columns($first_col, $last_col = NULL)
2750 $this->_title_colmin = $first_col;
2751 if(isset($last_col)) { // Second col is optional
2752 $this->_title_colmax = $last_col;
2755 $this->_title_colmax = $first_col;
2760 * Set the area of each worksheet that will be printed.
2763 * @see Workbook::_store_names()
2764 * @param integer $first_row First row of the area to print
2765 * @param integer $first_col First column of the area to print
2766 * @param integer $last_row Last row of the area to print
2767 * @param integer $last_col Last column of the area to print
2769 function print_area($first_row, $first_col, $last_row, $last_col)
2771 $this->_print_rowmin = $first_row;
2772 $this->_print_colmin = $first_col;
2773 $this->_print_rowmax = $last_row;
2774 $this->_print_colmax = $last_col;
2779 * Set the option to hide gridlines on the printed page.
2782 * @see _store_print_gridlines(), _store_gridset()
2784 function hide_gridlines()
2786 $this->_print_gridlines = 0;
2790 * Set the option to print the row and column headers on the printed page.
2791 * See also the _store_print_headers() method below.
2794 * @see _store_print_headers()
2795 * @param integer $print Whether to print the headers or not. Defaults to 1 (print).
2797 function print_row_col_headers($print = 1)
2799 $this->_print_headers = $print;
2803 * Store the vertical and horizontal number of pages that will define the
2804 * maximum area printed. It doesn't seem to work with OpenOffice.
2807 * @param integer $width Maximun width of printed area in pages
2808 * @param integer $heigth Maximun heigth of printed area in pages
2809 * @see set_print_scale()
2811 function fit_to_pages($width, $height)
2813 $this->_fit_page = 1;
2814 $this->_fit_width = $width;
2815 $this->_fit_height = $height;
2819 * Store the horizontal page breaks on a worksheet (for printing).
2820 * The breaks represent the row after which the break is inserted.
2823 * @param array $breaks Array containing the horizontal page breaks
2825 function set_h_pagebreaks($breaks)
2827 foreach($breaks as $break) {
2828 array_push($this->_hbreaks,$break);
2833 * Store the vertical page breaks on a worksheet (for printing).
2834 * The breaks represent the column after which the break is inserted.
2837 * @param array $breaks Array containing the vertical page breaks
2839 function set_v_pagebreaks($breaks)
2841 foreach($breaks as $break) {
2842 array_push($this->_vbreaks,$break);
2848 * Set the worksheet zoom factor.
2851 * @param integer $scale The zoom factor
2853 function set_zoom($scale = 100)
2855 // Confine the scale to Excel's range
2856 if ($scale < 10 or $scale > 400) {
2857 //carp "Zoom factor $scale outside range: 10 <= zoom <= 400";
2861 $this->_zoom = floor($scale);
2865 * Set the scale factor for the printed page.
2866 * It turns off the "fit to page" option
2869 * @param integer $scale The optional scale factor. Defaults to 100
2871 function set_print_scale($scale = 100)
2873 // Confine the scale to Excel's range
2874 if ($scale < 10 or $scale > 400)
2876 // REPLACE THIS FOR A WARNING
2877 die("Print scale $scale outside range: 10 <= zoom <= 400");
2881 // Turn off "fit to page" option
2882 $this->_fit_page = 0;
2884 $this->_print_scale = floor($scale);
2887 /** added 2009-03-05 by Joe Hunt, FA for arabic languages */
2893 * Map to the appropriate write method acording to the token recieved.
2896 * @param integer $row The row of the cell we are writing to
2897 * @param integer $col The column of the cell we are writing to
2898 * @param mixed $token What we are writing
2899 * @param mixed $format The optional format to apply to the cell
2901 function write($row, $col, $token, $format = null)
2903 // Check for a cell reference in A1 notation and substitute row and column
2904 /*if ($_[0] =~ /^\D/) {
2905 @_ = $this->_substitute_cellref(@_);
2909 # Match an array ref.
2910 if (ref $token eq "ARRAY") {
2911 return $this->write_row(@_);
2915 if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/",$token)) {
2916 return $this->write_number($row,$col,$token,$format);
2918 // Match http or ftp URL
2919 elseif (preg_match("/^[fh]tt?p:\/\//",$token)) {
2920 return $this->write_url($row, $col, $token, $format);
2923 elseif (preg_match("/^mailto:/",$token)) {
2924 return $this->write_url($row, $col, $token, $format);
2926 // Match internal or external sheet link
2927 elseif (preg_match("/^(?:in|ex)ternal:/",$token)) {
2928 return $this->write_url($row, $col, $token, $format);
2931 elseif (preg_match("/^=/",$token)) {
2932 return $this->write_formula($row, $col, $token, $format);
2935 elseif (preg_match("/^@/",$token)) {
2936 return $this->write_formula($row, $col, $token, $format);
2939 elseif ($token == '') {
2940 return $this->write_blank($row,$col,$format);
2942 // Default: match string
2944 return $this->write_string($row,$col,$token,$format);
2949 * Returns an index to the XF record in the workbook
2951 * @param mixed $format The optional XF format
2952 * @return integer The XF record index
2954 //function _XF(&$format)
2955 function _XF($format=null)
2959 return($format->get_xf_index());
2968 /******************************************************************************
2969 *******************************************************************************
2976 * Store Worksheet data in memory using the parent's class append() or to a
2977 * temporary file, the default.
2979 * @param string $data The binary data to append
2981 function _append($data)
2983 if ($this->_using_tmpfile)
2985 // Add CONTINUE records if necessary
2986 if (strlen($data) > $this->_limit) {
2987 $data = $this->_add_continue($data);
2989 fwrite($this->_filehandle,$data);
2990 $this->_datasize += strlen($data);
2993 parent::_append($data);
2998 * Substitute an Excel cell reference in A1 notation for zero based row and
2999 * column values in an argument list.
3001 * Ex: ("A4", "Hello") is converted to (3, 0, "Hello").
3003 * @param string $cell The cell reference. Or range of cells.
3006 function _substitute_cellref($cell)
3008 $cell = strtoupper($cell);
3010 // Convert a column range: 'A:A' or 'B:G'
3011 if (preg_match("/([A-I]?[A-Z]):([A-I]?[A-Z])/",$cell,$match)) {
3012 list($no_use, $col1) = $this->_cell_to_rowcol($match[1] .'1'); // Add a dummy row
3013 list($no_use, $col2) = $this->_cell_to_rowcol($match[2] .'1'); // Add a dummy row
3014 return(array($col1, $col2));
3017 // Convert a cell range: 'A1:B7'
3018 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/",$cell,$match)) {
3019 list($row1, $col1) = $this->_cell_to_rowcol($match[1]);
3020 list($row2, $col2) = $this->_cell_to_rowcol($match[2]);
3021 return(array($row1, $col1, $row2, $col2));
3024 // Convert a cell reference: 'A1' or 'AD2000'
3025 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+)/",$cell)) {
3026 list($row1, $col1) = $this->_cell_to_rowcol($match[1]);
3027 return(array($row1, $col1));
3030 die("Unknown cell reference $cell ");
3034 * Convert an Excel cell reference in A1 notation to a zero based row and column
3035 * reference; converts C1 to (0, 2).
3037 * @param string $cell The cell reference.
3038 * @return array containing (row, column)
3040 function _cell_to_rowcol($cell)
3042 preg_match("/\$?([A-I]?[A-Z])\$?(\d+)/",$cell,$match);
3046 // Convert base26 column string to number
3047 $chars = split('', $col);
3052 $char = array_pop($chars); // LS char first
3053 $col += (ord($char) -ord('A') +1) * pow(26,$expn);
3057 // Convert 1-index to zero-index
3061 return(array($row, $col));
3065 * Based on the algorithm provided by Daniel Rentz of OpenOffice.
3067 * @param string $plaintext The password to be encoded in plaintext.
3068 * @return string The encoded password
3070 function _encode_password($plaintext)
3073 $i = 1; // char position
3075 // split the plain text password in its component characters
3076 $chars = preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY);
3077 foreach($chars as $char)
3079 $value = ord($char) << $i; // shifted ASCII value
3080 $bit_16 = $value & 0x8000; // the bit 16
3081 $bit_16 >>= 15; // 0x0000 or 0x0001
3082 //$bit_17 = $value & 0x00010000;
3084 $value &= 0x7fff; // first 15 bits
3085 $password ^= ($value | $bit_16);
3086 //$password ^= ($value | $bit_16 | $bit_17);
3090 $password ^= strlen($plaintext);
3091 $password ^= 0xCE4B;
3096 /******************************************************************************
3097 *******************************************************************************
3104 * Write a double to the specified row and column (zero indexed).
3105 * An integer can be written as a double. Excel will display an
3106 * integer. $format is optional.
3108 * Returns 0 : normal termination
3109 * -2 : row or column out of range
3112 * @param integer $row Zero indexed row
3113 * @param integer $col Zero indexed column
3114 * @param float $num The number to write
3115 * @param mixed $format The optional XF format
3117 function write_number($row, $col, $num, $format = null)
3119 $record = 0x0203; // Record identifier
3120 $length = 0x000E; // Number of bytes to follow
3121 $xf = $this->_XF($format); // The cell format
3123 // Check that row and col are valid and store max and min values
3124 if ($row >= $this->xls_rowmax)
3128 if ($col >= $this->xls_colmax)
3132 if ($row < $this->dim_rowmin)
3134 $this->dim_rowmin = $row;
3136 if ($row > $this->dim_rowmax)
3138 $this->dim_rowmax = $row;
3140 if ($col < $this->dim_colmin)
3142 $this->dim_colmin = $col;
3144 if ($col > $this->dim_colmax)
3146 $this->dim_colmax = $col;
3149 $header = pack("vv", $record, $length);
3150 $data = pack("vvv", $row, $col, $xf);
3151 $xl_double = pack("d", $num);
3152 if ($this->_byte_order) // if it's Big Endian
3154 $xl_double = strrev($xl_double);
3157 $this->_append($header.$data.$xl_double);
3162 * Write a string to the specified row and column (zero indexed).
3163 * NOTE: there is an Excel 5 defined limit of 255 characters.
3164 * $format is optional.
3165 * Returns 0 : normal termination
3166 * -1 : insufficient number of arguments
3167 * -2 : row or column out of range
3168 * -3 : long string truncated to 255 chars
3171 * @param integer $row Zero indexed row
3172 * @param integer $col Zero indexed column
3173 * @param string $str The string to write
3174 * @param mixed $format The XF format for the cell
3176 function write_string($row, $col, $str, $format = null)
3178 $strlen = strlen($str);
3179 $record = 0x0204; // Record identifier
3180 $length = 0x0008 + $strlen; // Bytes to follow
3181 $xf = $this->_XF($format); // The cell format
3185 // Check that row and col are valid and store max and min values
3186 if ($row >= $this->xls_rowmax)
3190 if ($col >= $this->xls_colmax)
3194 if ($row < $this->dim_rowmin)
3196 $this->dim_rowmin = $row;
3198 if ($row > $this->dim_rowmax)
3200 $this->dim_rowmax = $row;
3202 if ($col < $this->dim_colmin)
3204 $this->dim_colmin = $col;
3206 if ($col > $this->dim_colmax)
3208 $this->dim_colmax = $col;
3211 if ($strlen > $this->xls_strmax) // LABEL must be < 255 chars
3213 $str = substr($str, 0, $this->xls_strmax);
3214 $length = 0x0008 + $this->xls_strmax;
3215 $strlen = $this->xls_strmax;
3219 $header = pack("vv", $record, $length);
3220 $data = pack("vvvv", $row, $col, $xf, $strlen);
3221 $this->_append($header.$data.$str);
3226 * Writes a note associated with the cell given by the row and column.
3227 * NOTE records don't have a length limit.
3230 * @param integer $row Zero indexed row
3231 * @param integer $col Zero indexed column
3232 * @param string $note The note to write
3234 function write_note($row, $col, $note)
3236 $note_length = strlen($note);
3237 $record = 0x001C; // Record identifier
3238 $max_length = 2048; // Maximun length for a NOTE record
3239 //$length = 0x0006 + $note_length; // Bytes to follow
3241 // Check that row and col are valid and store max and min values
3242 if ($row >= $this->xls_rowmax)
3246 if ($col >= $this->xls_colmax)
3250 if ($row < $this->dim_rowmin)
3252 $this->dim_rowmin = $row;
3254 if ($row > $this->dim_rowmax)
3256 $this->dim_rowmax = $row;
3258 if ($col < $this->dim_colmin)
3260 $this->dim_colmin = $col;
3262 if ($col > $this->dim_colmax)
3264 $this->dim_colmax = $col;
3267 // Length for this record is no more than 2048 + 6
3268 $length = 0x0006 + min($note_length, 2048);
3269 $header = pack("vv", $record, $length);
3270 $data = pack("vvv", $row, $col, $note_length);
3271 $this->_append($header.$data.substr($note, 0, 2048));
3273 for($i = $max_length; $i < $note_length; $i += $max_length)
3275 $chunk = substr($note, $i, $max_length);
3276 $length = 0x0006 + strlen($chunk);
3277 $header = pack("vv", $record, $length);
3278 $data = pack("vvv", -1, 0, strlen($chunk));
3279 $this->_append($header.$data.$chunk);
3285 * Write a blank cell to the specified row and column (zero indexed).
3286 * A blank cell is used to specify formatting without adding a string
3289 * A blank cell without a format serves no purpose. Therefore, we don't write
3290 * a BLANK record unless a format is specified. This is mainly an optimisation
3291 * for the write_row() and write_col() methods.
3293 * Returns 0 : normal termination (including no format)
3294 * -1 : insufficient number of arguments
3295 * -2 : row or column out of range
3298 * @param integer $row Zero indexed row
3299 * @param integer $col Zero indexed column
3300 * @param mixed $format The XF format
3302 function write_blank($row, $col, $format = null)
3304 // Don't write a blank cell unless it has a format
3305 if ($format == null)
3310 $record = 0x0201; // Record identifier
3311 $length = 0x0006; // Number of bytes to follow
3312 $xf = $this->_XF($format); // The cell format
3314 // Check that row and col are valid and store max and min values
3315 if ($row >= $this->xls_rowmax)
3319 if ($col >= $this->xls_colmax)
3323 if ($row < $this->dim_rowmin)
3325 $this->dim_rowmin = $row;
3327 if ($row > $this->dim_rowmax)
3329 $this->dim_rowmax = $row;
3331 if ($col < $this->dim_colmin)
3333 $this->dim_colmin = $col;
3335 if ($col > $this->dim_colmax)
3337 $this->dim_colmax = $col;
3340 $header = pack("vv", $record, $length);
3341 $data = pack("vvv", $row, $col, $xf);
3342 $this->_append($header.$data);
3347 * Write a formula to the specified row and column (zero indexed).
3348 * The textual representation of the formula is passed to the parser in
3349 * Parser.php which returns a packed binary string.
3351 * Returns 0 : normal termination
3352 * -2 : row or column out of range
3355 * @param integer $row Zero indexed row
3356 * @param integer $col Zero indexed column
3357 * @param string $formula The formula text string
3358 * @param mixed $format The optional XF format
3360 function write_formula($row, $col, $formula, $format = null)
3362 $record = 0x0006; // Record identifier
3364 // Excel normally stores the last calculated value of the formula in $num.
3365 // Clearly we are not in a position to calculate this a priori. Instead
3366 // we set $num to zero and set the option flags in $grbit to ensure
3367 // automatic calculation of the formula when the file is opened.
3369 $xf = $this->_XF($format); // The cell format
3370 $num = 0x00; // Current value of formula
3371 $grbit = 0x03; // Option flags
3372 $chn = 0x0000; // Must be zero
3375 // Check that row and col are valid and store max and min values
3376 if ($row >= $this->xls_rowmax)
3380 if ($col >= $this->xls_colmax)
3384 if ($row < $this->dim_rowmin)
3386 $this->dim_rowmin = $row;
3388 if ($row > $this->dim_rowmax)
3390 $this->dim_rowmax = $row;
3392 if ($col < $this->dim_colmin)
3394 $this->dim_colmin = $col;
3396 if ($col > $this->dim_colmax)
3398 $this->dim_colmax = $col;
3401 // Strip the '=' or '@' sign at the beginning of the formula string
3402 if (ereg("^=",$formula)) {
3403 $formula = preg_replace("/(^=)/","",$formula);
3405 elseif(ereg("^@",$formula)) {
3406 $formula = preg_replace("/(^@)/","",$formula);
3409 die("Unrecognised character for formula");
3412 // Parse the formula using the parser in Parser.php
3413 //$tree = new Parser($this->_byte_order);
3414 $this->_parser->parse($formula);
3415 //$tree->parse($formula);
3416 $formula = $this->_parser->to_reverse_polish();
3418 $formlen = strlen($formula); // Length of the binary string
3419 $length = 0x16 + $formlen; // Length of the record data
3421 $header = pack("vv", $record, $length);
3422 $data = pack("vvvdvVv", $row, $col, $xf, $num,
3423 $grbit, $chn, $formlen);
3425 $this->_append($header.$data.$formula);
3430 * Write a hyperlink. This is comprised of two elements: the visible label and
3431 * the invisible link. The visible label is the same as the link unless an
3432 * alternative string is specified. The label is written using the
3433 * write_string() method. Therefore the 255 characters string limit applies.
3434 * $string and $format are optional and their order is interchangeable.
3436 * The hyperlink can be to a http, ftp, mail, internal sheet, or external
3439 * Returns 0 : normal termination
3440 * -1 : insufficient number of arguments
3441 * -2 : row or column out of range
3442 * -3 : long string truncated to 255 chars
3445 * @param integer $row Row
3446 * @param integer $col Column
3447 * @param string $url URL string
3448 * @param string $string Alternative label
3449 * @param mixed $format The cell format
3451 function write_url($row, $col, $url, $string = '', $format = null)
3453 // Add start row and col to arg list
3454 return($this->_write_url_range($row, $col, $row, $col, $url, $string, $format));
3458 * This is the more general form of write_url(). It allows a hyperlink to be
3459 * written to a range of cells. This function also decides the type of hyperlink
3460 * to be written. These are either, Web (http, ftp, mailto), Internal
3461 * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
3463 * See also write_url() above for a general description and return values.
3465 * @param integer $row1 Start row
3466 * @param integer $col1 Start column
3467 * @param integer $row2 End row
3468 * @param integer $col2 End column
3469 * @param string $url URL string
3470 * @param string $string Alternative label
3471 * @param mixed $format The cell format
3474 function _write_url_range($row1, $col1, $row2, $col2, $url, $string = '', $format = null)
3476 // Check for internal/external sheet links or default to web link
3477 if (preg_match('[^internal:]', $url)) {
3478 return($this->_write_url_internal($row1, $col1, $row2, $col2, $url, $string, $format));
3480 if (preg_match('[^external:]', $url)) {
3481 return($this->_write_url_external($row1, $col1, $row2, $col2, $url, $string, $format));
3483 return($this->_write_url_web($row1, $col1, $row2, $col2, $url, $string, $format));
3488 * Used to write http, ftp and mailto hyperlinks.
3489 * The link type ($options) is 0x03 is the same as absolute dir ref without
3490 * sheet. However it is differentiated by the $unknown2 data stream.
3493 * @param integer $row1 Start row
3494 * @param integer $col1 Start column
3495 * @param integer $row2 End row
3496 * @param integer $col2 End column
3497 * @param string $url URL string
3498 * @param string $str Alternative label
3499 * @param mixed $format The cell format
3501 function _write_url_web($row1, $col1, $row2, $col2, $url, $str, $format = null)
3503 $record = 0x01B8; // Record identifier
3504 $length = 0x00000; // Bytes to follow
3506 if($format == null) {
3507 $format = $this->_url_format;
3510 // Write the visible label using the write_string() method.
3514 $str_error = $this->write_string($row1, $col1, $str, $format);
3515 if ($str_error == -2) {
3519 // Pack the undocumented parts of the hyperlink stream
3520 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
3521 $unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");
3523 // Pack the option flags
3524 $options = pack("V", 0x03);
3526 // Convert URL to a null terminated wchar string
3527 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
3528 $url = $url . "\0\0\0";
3530 // Pack the length of the URL
3531 $url_len = pack("V", strlen($url));
3533 // Calculate the data length
3534 $length = 0x34 + strlen($url);
3536 // Pack the header data
3537 $header = pack("vv", $record, $length);
3538 $data = pack("vvvv", $row1, $row2, $col1, $col2);
3540 // Write the packed data
3541 $this->_append( $header. $data.
3542 $unknown1. $options.
3543 $unknown2. $url_len. $url);
3548 * Used to write internal reference hyperlinks such as "Sheet1!A1".
3551 * @param integer $row1 Start row
3552 * @param integer $col1 Start column
3553 * @param integer $row2 End row
3554 * @param integer $col2 End column
3555 * @param string $url URL string
3556 * @param string $str Alternative label
3557 * @param mixed $format The cell format
3559 function _write_url_internal($row1, $col1, $row2, $col2, $url, $str, $format = null)
3561 $record = 0x01B8; // Record identifier
3562 $length = 0x00000; // Bytes to follow
3564 if ($format == null) {
3565 $format = $this->_url_format;
3569 $url = preg_replace('s[^internal:]', '', $url);
3571 // Write the visible label
3575 $str_error = $this->write_string($row1, $col1, $str, $format);
3576 if ($str_error == -2) {
3580 // Pack the undocumented parts of the hyperlink stream
3581 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
3583 // Pack the option flags
3584 $options = pack("V", 0x08);
3586 // Convert the URL type and to a null terminated wchar string
3587 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
3588 $url = $url . "\0\0\0";
3590 // Pack the length of the URL as chars (not wchars)
3591 $url_len = pack("V", floor(strlen($url)/2));
3593 // Calculate the data length
3594 $length = 0x24 + strlen($url);
3596 // Pack the header data
3597 $header = pack("vv", $record, $length);
3598 $data = pack("vvvv", $row1, $row2, $col1, $col2);
3600 // Write the packed data
3601 $this->_append($header. $data.
3602 $unknown1. $options.
3608 * Write links to external directory names such as 'c:\foo.xls',
3609 * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
3611 * Note: Excel writes some relative links with the $dir_long string. We ignore
3612 * these cases for the sake of simpler code.
3615 * @param integer $row1 Start row
3616 * @param integer $col1 Start column
3617 * @param integer $row2 End row
3618 * @param integer $col2 End column
3619 * @param string $url URL string
3620 * @param string $str Alternative label
3621 * @param mixed $format The cell format
3623 function _write_url_external($row1, $col1, $row2, $col2, $url, $str, $format = null)
3625 // Network drives are different. We will handle them separately
3626 // MS/Novell network drives and shares start with \\
3627 if (preg_match('[^external:\\\\]', $url)) {
3628 return($this->_write_url_external_net($row1, $col1, $row2, $col2, $url, $str, $format));
3631 $record = 0x01B8; // Record identifier
3632 $length = 0x00000; // Bytes to follow
3634 if ($format == null) {
3635 $format = $this->_url_format;
3638 // Strip URL type and change Unix dir separator to Dos style (if needed)
3640 $url = preg_replace('[^external:]', '', $url);
3641 $url = preg_replace('[/]', "\\", $url);
3643 // Write the visible label
3645 $str = preg_replace('[\#]', ' - ', $url);
3647 $str_error = $this->write_string($row1, $col1, $str, $format);
3648 if ($str_error == -2) {
3652 // Determine if the link is relative or absolute:
3653 // relative if link contains no dir separator, "somefile.xls"
3654 // relative if link starts with up-dir, "..\..\somefile.xls"
3655 // otherwise, absolute
3657 $absolute = 0x02; // Bit mask
3658 if (!preg_match('[\\]', $url)) {
3661 if (preg_match('[^\.\.\\]', $url)) {
3665 // Determine if the link contains a sheet reference and change some of the
3666 // parameters accordingly.
3667 // Split the dir name and sheet name (if it exists)
3668 list($dir_long , $sheet) = split('/\#/', $url);
3669 $link_type = 0x01 | $absolute;
3671 if (isset($sheet)) {
3673 $sheet_len = pack("V", strlen($sheet) + 0x01);
3674 $sheet = join("\0", split('', $sheet));
3682 // Pack the link type
3683 $link_type = pack("V", $link_type);
3685 // Calculate the up-level dir count e.g.. (..\..\..\ == 3)
3686 $up_count = preg_match_all("/\.\.\\/", $dir_long, $useless);
3687 $up_count = pack("v", $up_count);
3689 // Store the short dos dir name (null terminated)
3690 $dir_short = preg_replace('/\.\.\\/', '', $dir_long) . "\0";
3692 // Store the long dir name as a wchar string (non-null terminated)
3693 $dir_long = join("\0", split('', $dir_long));
3694 $dir_long = $dir_long . "\0";
3696 // Pack the lengths of the dir strings
3697 $dir_short_len = pack("V", strlen($dir_short) );
3698 $dir_long_len = pack("V", strlen($dir_long) );
3699 $stream_len = pack("V", strlen($dir_long) + 0x06);
3701 // Pack the undocumented parts of the hyperlink stream
3702 $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000' );
3703 $unknown2 = pack("H*",'0303000000000000C000000000000046' );
3704 $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000');
3705 $unknown4 = pack("v", 0x03 );
3707 // Pack the main data stream
3708 $data = pack("vvvv", $row1, $row2, $col1, $col2) .
3723 // Pack the header data
3724 $length = strlen($data);
3725 $header = pack("vv", $record, $length);
3727 // Write the packed data
3728 $this->_append($header. $data);
3734 ###############################################################################
3736 # write_url_xxx($row1, $col1, $row2, $col2, $url, $string, $format)
3738 # Write links to external MS/Novell network drives and shares such as
3739 # '//NETWORK/share/foo.xls' and '//NETWORK/share/foo.xls#Sheet1!A1'.
3741 # See also write_url() above for a general description and return values.
3743 sub _write_url_external_net {
3747 my $record = 0x01B8; # Record identifier
3748 my $length = 0x00000; # Bytes to follow
3750 my $row1 = $_[0]; # Start row
3751 my $col1 = $_[1]; # Start column
3752 my $row2 = $_[2]; # End row
3753 my $col2 = $_[3]; # End column
3754 my $url = $_[4]; # URL string
3755 my $str = $_[5]; # Alternative label
3756 my $xf = $_[6] || $this->{_url_format};# The cell format
3759 # Strip URL type and change Unix dir separator to Dos style (if needed)
3761 $url =~ s[^external:][];
3765 # Write the visible label
3766 ($str = $url) =~ s[\#][ - ] unless defined $str;
3767 my $str_error = $this->write_string($row1, $col1, $str, $xf);
3768 return $str_error if $str_error == -2;
3771 # Determine if the link contains a sheet reference and change some of the
3772 # parameters accordingly.
3773 # Split the dir name and sheet name (if it exists)
3775 my ($dir_long , $sheet) = split /\#/, $url;
3776 my $link_type = 0x0103; # Always absolute
3779 if (defined $sheet) {
3781 $sheet_len = pack("V", length($sheet) + 0x01);
3782 $sheet = join("\0", split('', $sheet));
3790 # Pack the link type
3791 $link_type = pack("V", $link_type);
3794 # Make the string null terminated
3795 $dir_long = $dir_long . "\0";
3798 # Pack the lengths of the dir string
3799 my $dir_long_len = pack("V", length $dir_long);
3802 # Store the long dir name as a wchar string (non-null terminated)
3803 $dir_long = join("\0", split('', $dir_long));
3804 $dir_long = $dir_long . "\0";
3807 # Pack the undocumented part of the hyperlink stream
3808 my $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000');
3811 # Pack the main data stream
3812 my $data = pack("vvvv", $row1, $row2, $col1, $col2) .
3821 # Pack the header data
3822 $length = length $data;
3823 my $header = pack("vv", $record, $length);
3826 # Write the packed data
3827 $this->_append( $header, $data);
3833 * This method is used to set the height and XF format for a row.
3834 * Writes the BIFF record ROW.
3837 * @param integer $row The row to set
3838 * @param integer $height Height we are giving to the row.
3839 * Use NULL to set XF without setting height
3840 * @param mixed $format XF format we are giving to the row
3842 function set_row($row, $height, $format = null)
3844 $record = 0x0208; // Record identifier
3845 $length = 0x0010; // Number of bytes to follow
3847 $colMic = 0x0000; // First defined column
3848 $colMac = 0x0000; // Last defined column
3849 $irwMac = 0x0000; // Used by Excel to optimise loading
3850 $reserved = 0x0000; // Reserved
3851 $grbit = 0x01C0; // Option flags. (monkey) see $1 do
3852 $ixfe = $this->_XF($format); // XF index
3854 // Use set_row($row, NULL, $XF) to set XF without setting height
3855 if ($height != NULL) {
3856 $miyRw = $height * 20; // row height
3859 $miyRw = 0xff; // default row height is 256
3862 $header = pack("vv", $record, $length);
3863 $data = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw,
3864 $irwMac,$reserved, $grbit, $ixfe);
3865 $this->_append($header.$data);
3869 * Writes Excel DIMENSIONS to define the area in which there is data.
3871 function _store_dimensions()
3873 $record = 0x0000; // Record identifier
3874 $length = 0x000A; // Number of bytes to follow
3875 $row_min = $this->dim_rowmin; // First row
3876 $row_max = $this->dim_rowmax; // Last row plus 1
3877 $col_min = $this->dim_colmin; // First column
3878 $col_max = $this->dim_colmax; // Last column plus 1
3879 $reserved = 0x0000; // Reserved by Excel
3881 $header = pack("vv", $record, $length);
3882 $data = pack("vvvvv", $row_min, $row_max,
3883 $col_min, $col_max, $reserved);
3884 $this->_prepend($header.$data);
3888 * Write BIFF record Window2.
3890 function _store_window2()
3892 $record = 0x023E; // Record identifier
3893 $length = 0x000A; // Number of bytes to follow
3895 $grbit = 0x00B6; // Option flags
3896 $rwTop = 0x0000; // Top row visible in window
3897 $colLeft = 0x0000; // Leftmost column visible in window
3898 $rgbHdr = 0x00000000; // Row/column heading and gridline color
3900 // The options flags that comprise $grbit
3901 $fDspFmla = 0; // 0 - bit
3903 $fDspRwCol = 1; // 2
3904 $fFrozen = $this->_frozen; // 3
3905 $fDspZeros = 1; // 4
3906 $fDefaultHdr = 1; // 5
3907 $fArabic = $this->_rtl; // 6
3909 $fFrozenNoSplit = 0; // 0 - bit
3910 $fSelected = $this->selected; // 1
3914 $grbit |= $fDspGrid << 1;
3915 $grbit |= $fDspRwCol << 2;
3916 $grbit |= $fFrozen << 3;
3917 $grbit |= $fDspZeros << 4;
3918 $grbit |= $fDefaultHdr << 5;
3919 $grbit |= $fArabic << 6;
3920 $grbit |= $fDspGuts << 7;
3921 $grbit |= $fFrozenNoSplit << 8;
3922 $grbit |= $fSelected << 9;
3923 $grbit |= $fPaged << 10;
3925 $header = pack("vv", $record, $length);
3926 $data = pack("vvvV", $grbit, $rwTop, $colLeft, $rgbHdr);
3927 $this->_append($header.$data);
3931 * Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
3933 function _store_defcol()
3935 $record = 0x0055; // Record identifier
3936 $length = 0x0002; // Number of bytes to follow
3937 $colwidth = 0x0008; // Default column width
3939 $header = pack("vv", $record, $length);
3940 $data = pack("v", $colwidth);
3941 $this->_prepend($header.$data);
3945 * Write BIFF record COLINFO to define column widths
3947 * Note: The SDK says the record length is 0x0B but Excel writes a 0x0C
3950 * @param array $col_array This is the only parameter received and is composed of the following:
3951 * 0 => First formatted column,
3952 * 1 => Last formatted column,
3953 * 2 => Col width (8.43 is Excel default),
3954 * 3 => The optional XF format of the column,
3955 * 4 => Option flags.
3957 function _store_colinfo($col_array)
3959 if(isset($col_array[0])) {
3960 $colFirst = $col_array[0];
3962 if(isset($col_array[1])) {
3963 $colLast = $col_array[1];
3965 if(isset($col_array[2])) {
3966 $coldx = $col_array[2];
3971 if(isset($col_array[3])) {
3972 $format = $col_array[3];
3977 if(isset($col_array[4])) {
3978 $grbit = $col_array[4];
3983 $record = 0x007D; // Record identifier
3984 $length = 0x000B; // Number of bytes to follow
3986 $coldx += 0.72; // Fudge. Excel subtracts 0.72 !?
3987 $coldx *= 256; // Convert to units of 1/256 of a char
3989 $ixfe = $this->_XF($format);
3990 $reserved = 0x00; // Reserved
3992 $header = pack("vv", $record, $length);
3993 $data = pack("vvvvvC", $colFirst, $colLast, $coldx,
3994 $ixfe, $grbit, $reserved);
3995 $this->_prepend($header.$data);
3999 * Write BIFF record SELECTION.
4001 * @param array $array array containing ($rwFirst,$colFirst,$rwLast,$colLast)
4002 * @see set_selection()
4004 function _store_selection($array)
4006 list($rwFirst,$colFirst,$rwLast,$colLast) = $array;
4007 $record = 0x001D; // Record identifier
4008 $length = 0x000F; // Number of bytes to follow
4010 $pnn = $this->_active_pane; // Pane position
4011 $rwAct = $rwFirst; // Active row
4012 $colAct = $colFirst; // Active column
4013 $irefAct = 0; // Active cell ref
4014 $cref = 1; // Number of refs
4016 if (!isset($rwLast)) {
4017 $rwLast = $rwFirst; // Last row in reference
4019 if (!isset($colLast)) {
4020 $colLast = $colFirst; // Last col in reference
4023 // Swap last row/col for first row/col as necessary
4024 if ($rwFirst > $rwLast)
4026 list($rwFirst, $rwLast) = array($rwLast, $rwFirst);
4029 if ($colFirst > $colLast)
4031 list($colFirst, $colLast) = array($colLast, $colFirst);
4034 $header = pack("vv", $record, $length);
4035 $data = pack("CvvvvvvCC", $pnn, $rwAct, $colAct,
4038 $colFirst, $colLast);
4039 $this->_append($header.$data);
4044 * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
4045 * references in a worksheet.
4047 * Excel only stores references to external sheets that are used in formulas.
4048 * For simplicity we store references to all the sheets in the workbook
4049 * regardless of whether they are used or not. This reduces the overall
4050 * complexity and eliminates the need for a two way dialogue between the formula
4051 * parser the worksheet objects.
4053 * @param integer $count The number of external sheet references in this worksheet
4055 function _store_externcount($count)
4057 $record = 0x0016; // Record identifier
4058 $length = 0x0002; // Number of bytes to follow
4060 $header = pack("vv", $record, $length);
4061 $data = pack("v", $count);
4062 $this->_prepend($header.$data);
4066 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
4067 * formulas. A formula references a sheet name via an index. Since we store a
4068 * reference to all of the external worksheets the EXTERNSHEET index is the same
4069 * as the worksheet index.
4071 * @param string $sheetname The name of a external worksheet
4073 function _store_externsheet($sheetname)
4075 $record = 0x0017; // Record identifier
4077 // References to the current sheet are encoded differently to references to
4080 if ($this->name == $sheetname) {
4082 $length = 0x02; // The following 2 bytes
4083 $cch = 1; // The following byte
4084 $rgch = 0x02; // Self reference
4087 $length = 0x02 + strlen($sheetname);
4088 $cch = strlen($sheetname);
4089 $rgch = 0x03; // Reference to a sheet in the current workbook
4092 $header = pack("vv", $record, $length);
4093 $data = pack("CC", $cch, $rgch);
4094 $this->_prepend($header.$data.$sheetname);
4098 * Writes the Excel BIFF PANE record.
4099 * The panes can either be frozen or thawed (unfrozen).
4100 * Frozen panes are specified in terms of an integer number of rows and columns.
4101 * Thawed panes are specified in terms of Excel's units for rows and columns.
4103 * @param array $panes This is the only parameter received and is composed of the following:
4104 * 0 => Vertical split position,
4105 * 1 => Horizontal split position
4106 * 2 => Top row visible
4107 * 3 => Leftmost column visible
4110 function _store_panes($panes)
4115 $colLeft = $panes[3];
4116 if(count($panes) > 4) { // if Active pane was received
4117 $pnnAct = $panes[4];
4122 $record = 0x0041; // Record identifier
4123 $length = 0x000A; // Number of bytes to follow
4125 // Code specific to frozen or thawed panes.
4126 if ($this->_frozen) {
4127 // Set default values for $rwTop and $colLeft
4128 if(!isset($rwTop)) {
4131 if(!isset($colLeft)) {
4136 // Set default values for $rwTop and $colLeft
4137 if(!isset($rwTop)) {
4140 if(!isset($colLeft)) {
4144 // Convert Excel's row and column units to the internal units.
4145 // The default row height is 12.75
4146 // The default column width is 8.43
4147 // The following slope and intersection values were interpolated.
4150 $x = 113.879*$x + 390;
4154 // Determine which pane should be active. There is also the undocumented
4155 // option to override this should it be necessary: may be removed later.
4157 if (!isset($pnnAct))
4159 if ($x != 0 and $y != 0)
4160 $pnnAct = 0; // Bottom right
4161 if ($x != 0 and $y == 0)
4162 $pnnAct = 1; // Top right
4163 if ($x == 0 and $y != 0)
4164 $pnnAct = 2; // Bottom left
4165 if ($x == 0 and $y == 0)
4166 $pnnAct = 3; // Top left
4169 $this->_active_pane = $pnnAct; // Used in _store_selection
4171 $header = pack("vv", $record, $length);
4172 $data = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct);
4173 $this->_append($header.$data);
4177 * Store the page setup SETUP BIFF record.
4179 function _store_setup()
4181 $record = 0x00A1; // Record identifier
4182 $length = 0x0022; // Number of bytes to follow
4184 $iPaperSize = $this->_paper_size; // Paper size
4185 $iScale = $this->_print_scale; // Print scaling factor
4186 $iPageStart = 0x01; // Starting page number
4187 $iFitWidth = $this->_fit_width; // Fit to number of pages wide
4188 $iFitHeight = $this->_fit_height; // Fit to number of pages high
4189 $grbit = 0x00; // Option flags
4190 $iRes = 0x0258; // Print resolution
4191 $iVRes = 0x0258; // Vertical print resolution
4192 $numHdr = $this->_margin_head; // Header Margin
4193 $numFtr = $this->_margin_foot; // Footer Margin
4194 $iCopies = 0x01; // Number of copies
4196 $fLeftToRight = 0x0; // Print over then down
4197 $fLandscape = $this->_orientation; // Page orientation
4198 $fNoPls = 0x0; // Setup not read from printer
4199 $fNoColor = 0x0; // Print black and white
4200 $fDraft = 0x0; // Print draft quality
4201 $fNotes = 0x0; // Print notes
4202 $fNoOrient = 0x0; // Orientation not set
4203 $fUsePage = 0x0; // Use custom starting page
4205 $grbit = $fLeftToRight;
4206 $grbit |= $fLandscape << 1;
4207 $grbit |= $fNoPls << 2;
4208 $grbit |= $fNoColor << 3;
4209 $grbit |= $fDraft << 4;
4210 $grbit |= $fNotes << 5;
4211 $grbit |= $fNoOrient << 6;
4212 $grbit |= $fUsePage << 7;
4214 $numHdr = pack("d", $numHdr);
4215 $numFtr = pack("d", $numFtr);
4216 if ($this->_byte_order) // if it's Big Endian
4218 $numHdr = strrev($numHdr);
4219 $numFtr = strrev($numFtr);
4222 $header = pack("vv", $record, $length);
4223 $data1 = pack("vvvvvvvv", $iPaperSize,
4231 $data2 = $numHdr .$numFtr;
4232 $data3 = pack("v", $iCopies);
4233 $this->_prepend($header.$data1.$data2.$data3);
4237 * Store the header caption BIFF record.
4239 function store_header()
4241 $record = 0x0014; // Record identifier
4243 $str = $this->_header; // header string
4244 $cch = strlen($str); // Length of header string
4245 $length = 1 + $cch; // Bytes to follow
4247 $header = pack("vv", $record, $length);
4248 $data = pack("C", $cch);
4250 $this->_append($header.$data.$str);
4254 * Store the footer caption BIFF record.
4256 function store_footer()
4258 $record = 0x0015; // Record identifier
4260 $str = $this->_footer; // Footer string
4261 $cch = strlen($str); // Length of footer string
4262 $length = 1 + $cch; // Bytes to follow
4264 $header = pack("vv", $record, $length);
4265 $data = pack("C", $cch);
4267 $this->_append($header.$data.$str);
4271 * Store the horizontal centering HCENTER BIFF record.
4273 function store_hcenter()
4275 $record = 0x0083; // Record identifier
4276 $length = 0x0002; // Bytes to follow
4278 $fHCenter = $this->_hcenter; // Horizontal centering
4280 $header = pack("vv", $record, $length);
4281 $data = pack("v", $fHCenter);
4283 $this->_append($header.$data);
4287 * Store the vertical centering VCENTER BIFF record.
4289 function store_vcenter()
4291 $record = 0x0084; // Record identifier
4292 $length = 0x0002; // Bytes to follow
4294 $fVCenter = $this->_vcenter; // Horizontal centering
4296 $header = pack("vv", $record, $length);
4297 $data = pack("v", $fVCenter);
4298 $this->_append($header.$data);
4302 * Store the LEFTMARGIN BIFF record.
4304 function _store_margin_left()
4306 $record = 0x0026; // Record identifier
4307 $length = 0x0008; // Bytes to follow
4309 $margin = $this->_margin_left; // Margin in inches
4311 $header = pack("vv", $record, $length);
4312 $data = pack("d", $margin);
4313 if ($this->_byte_order) // if it's Big Endian
4315 $data = strrev($data);
4318 $this->_append($header.$data);
4322 * Store the RIGHTMARGIN BIFF record.
4324 function _store_margin_right()
4326 $record = 0x0027; // Record identifier
4327 $length = 0x0008; // Bytes to follow
4329 $margin = $this->_margin_right; // Margin in inches
4331 $header = pack("vv", $record, $length);
4332 $data = pack("d", $margin);
4333 if ($this->_byte_order) // if it's Big Endian
4335 $data = strrev($data);
4338 $this->_append($header.$data);
4342 * Store the TOPMARGIN BIFF record.
4344 function _store_margin_top()
4346 $record = 0x0028; // Record identifier
4347 $length = 0x0008; // Bytes to follow
4349 $margin = $this->_margin_top; // Margin in inches
4351 $header = pack("vv", $record, $length);
4352 $data = pack("d", $margin);
4353 if ($this->_byte_order) // if it's Big Endian
4355 $data = strrev($data);
4358 $this->_append($header.$data);
4362 * Store the BOTTOMMARGIN BIFF record.
4364 function _store_margin_bottom()
4366 $record = 0x0029; // Record identifier
4367 $length = 0x0008; // Bytes to follow
4369 $margin = $this->_margin_bottom; // Margin in inches
4371 $header = pack("vv", $record, $length);
4372 $data = pack("d", $margin);
4373 if ($this->_byte_order) // if it's Big Endian
4375 $data = strrev($data);
4378 $this->_append($header.$data);
4382 * This is an Excel97/2000 method. It is required to perform more complicated
4383 * merging than the normal set_align('merge'). It merges the area given by
4387 * @param integer $first_row First row of the area to merge
4388 * @param integer $first_col First column of the area to merge
4389 * @param integer $last_row Last row of the area to merge
4390 * @param integer $last_col Last column of the area to merge
4392 function merge_cells($first_row, $first_col, $last_row, $last_col)
4394 $record = 0x00E5; // Record identifier
4395 $length = 0x000A; // Bytes to follow
4396 $cref = 1; // Number of refs
4398 // Swap last row/col for first row/col as necessary
4399 if ($first_row > $last_row) {
4400 list($first_row, $last_row) = array($last_row, $first_row);
4403 if ($first_col > $last_col) {
4404 list($first_col, $last_col) = array($last_col, $first_col);
4407 $header = pack("vv", $record, $length);
4408 $data = pack("vvvvv", $cref, $first_row, $last_row,
4409 $first_col, $last_col);
4411 $this->_append($header.$data);
4415 * Write the PRINTHEADERS BIFF record.
4417 function _store_print_headers()
4419 $record = 0x002a; // Record identifier
4420 $length = 0x0002; // Bytes to follow
4422 $fPrintRwCol = $this->_print_headers; // Boolean flag
4424 $header = pack("vv", $record, $length);
4425 $data = pack("v", $fPrintRwCol);
4426 $this->_prepend($header.$data);
4430 * Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the
4433 function _store_print_gridlines()
4435 $record = 0x002b; // Record identifier
4436 $length = 0x0002; // Bytes to follow
4438 $fPrintGrid = $this->_print_gridlines; // Boolean flag
4440 $header = pack("vv", $record, $length);
4441 $data = pack("v", $fPrintGrid);
4442 $this->_prepend($header.$data);
4446 * Write the GRIDSET BIFF record. Must be used in conjunction with the
4447 * PRINTGRIDLINES record.
4449 function _store_gridset()
4451 $record = 0x0082; // Record identifier
4452 $length = 0x0002; // Bytes to follow
4454 $fGridSet = !($this->_print_gridlines); // Boolean flag
4456 $header = pack("vv", $record, $length);
4457 $data = pack("v", $fGridSet);
4458 $this->_prepend($header.$data);
4462 * Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction
4463 * with the SETUP record.
4465 function _store_wsbool()
4467 $record = 0x0081; // Record identifier
4468 $length = 0x0002; // Bytes to follow
4470 // The only option that is of interest is the flag for fit to page. So we
4471 // set all the options in one go.
4473 if ($this->_fit_page) {
4480 $header = pack("vv", $record, $length);
4481 $data = pack("v", $grbit);
4482 $this->_prepend($header.$data);
4487 * Write the HORIZONTALPAGEBREAKS BIFF record.
4489 function _store_hbreak()
4491 // Return if the user hasn't specified pagebreaks
4492 if(empty($this->_hbreaks)) {
4496 // Sort and filter array of page breaks
4497 $breaks = $this->_hbreaks;
4498 sort($breaks,SORT_NUMERIC);
4499 if($breaks[0] == 0) { // don't use first break if it's 0
4500 array_shift($breaks);
4503 $record = 0x001b; // Record identifier
4504 $cbrk = count($breaks); // Number of page breaks
4505 $length = ($cbrk + 1) * 2; // Bytes to follow
4507 $header = pack("vv", $record, $length);
4508 $data = pack("v", $cbrk);
4510 // Append each page break
4511 foreach($breaks as $break) {
4512 $data .= pack("v", $break);
4515 $this->_prepend($header.$data);
4520 * Write the VERTICALPAGEBREAKS BIFF record.
4522 function _store_vbreak()
4524 // Return if the user hasn't specified pagebreaks
4525 if(empty($this->_vbreaks)) {
4529 // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
4530 // It is slightly higher in Excel 97/200, approx. 1026
4531 $breaks = array_slice($this->_vbreaks,0,1000);
4533 // Sort and filter array of page breaks
4534 sort($breaks,SORT_NUMERIC);
4535 if($breaks[0] == 0) { // don't use first break if it's 0
4536 array_shift($breaks);
4539 $record = 0x001a; // Record identifier
4540 $cbrk = count($breaks); // Number of page breaks
4541 $length = ($cbrk + 1) * 2; // Bytes to follow
4543 $header = pack("vv", $record, $length);
4544 $data = pack("v", $cbrk);
4546 // Append each page break
4547 foreach ($breaks as $break) {
4548 $data .= pack("v", $break);
4551 $this->_prepend($header.$data);
4555 * Set the Biff PROTECT record to indicate that the worksheet is protected.
4557 function _store_protect()
4559 // Exit unless sheet protection has been specified
4560 if($this->_protect == 0) {
4564 $record = 0x0012; // Record identifier
4565 $length = 0x0002; // Bytes to follow
4567 $fLock = $this->_protect; // Worksheet is protected
4569 $header = pack("vv", $record, $length);
4570 $data = pack("v", $fLock);
4572 $this->_prepend($header.$data);
4576 * Write the worksheet PASSWORD record.
4578 function _store_password()
4580 // Exit unless sheet protection and password have been specified
4581 if(($this->_protect == 0) or (!isset($this->_password))) {
4585 $record = 0x0013; // Record identifier
4586 $length = 0x0002; // Bytes to follow
4588 $wPassword = $this->_password; // Encoded password
4590 $header = pack("vv", $record, $length);
4591 $data = pack("v", $wPassword);
4593 $this->_prepend($header.$data);
4597 * Insert a 24bit bitmap image in a worksheet. The main record required is
4598 * IMDATA but it must be proceeded by a OBJ record to define its position.
4601 * @param integer $row The row we are going to insert the bitmap into
4602 * @param integer $col The column we are going to insert the bitmap into
4603 * @param string $bitmap The bitmap filename
4604 * @param integer $x The horizontal position (offset) of the image inside the cell.
4605 * @param integer $y The vertical position (offset) of the image inside the cell.
4606 * @param integer $scale_x The horizontal scale
4607 * @param integer $scale_y The vertical scale
4609 function insert_bitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1)
4611 list($width, $height, $size, $data) = $this->_process_bitmap($bitmap);
4613 // Scale the frame of the image.
4615 $height *= $scale_y;
4617 // Calculate the vertices of the image and write the OBJ record
4618 $this->_position_image($col, $row, $x, $y, $width, $height);
4620 // Write the IMDATA record to store the bitmap data
4622 $length = 8 + $size;
4627 $header = pack("vvvvV", $record, $length, $cf, $env, $lcb);
4628 $this->_append($header.$data);
4632 * Calculate the vertices that define the position of the image as required by
4635 * +------------+------------+
4637 * +-----+------------+------------+
4639 * | 1 |(A1)._______|______ |
4642 * +-----+----| BITMAP |-----+
4644 * | 2 | |______________. |
4647 * +---- +------------+------------+
4649 * Example of a bitmap that covers some of the area from cell A1 to cell B2.
4651 * Based on the width and height of the bitmap we need to calculate 8 vars:
4652 * $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
4653 * The width and height of the cells are also variable and have to be taken into
4655 * The values of $col_start and $row_start are passed in from the calling
4656 * function. The values of $col_end and $row_end are calculated by subtracting
4657 * the width and height of the bitmap from the width and height of the
4659 * The vertices are expressed as a percentage of the underlying cell width as
4660 * follows (rhs values are in pixels):
4664 * x2 = (X-1) / W *1024
4665 * y2 = (Y-1) / H *256
4667 * Where: X is distance from the left side of the underlying cell
4668 * Y is distance from the top of the underlying cell
4669 * W is the width of the cell
4670 * H is the height of the cell
4672 * @note the SDK incorrectly states that the height should be expressed as a
4673 * percentage of 1024.
4674 * @param integer $col_start Col containing upper left corner of object
4675 * @param integer $row_start Row containing top left corner of object
4676 * @param integer $x1 Distance to left side of object
4677 * @param integer $y1 Distance to top of object
4678 * @param integer $width Width of image frame
4679 * @param integer $height Height of image frame
4681 function _position_image($col_start, $row_start, $x1, $y1, $width, $height)
4683 // Initialise end cell to the same as the start cell
4684 $col_end = $col_start; // Col containing lower right corner of object
4685 $row_end = $row_start; // Row containing bottom right corner of object
4687 // Zero the specified offset if greater than the cell dimensions
4688 if ($x1 >= $this->size_col($col_start))
4692 if ($y1 >= $this->size_row($row_start))
4697 $width = $width + $x1 -1;
4698 $height = $height + $y1 -1;
4700 // Subtract the underlying cell widths to find the end cell of the image
4701 while ($width >= $this->size_col($col_end)) {
4702 $width -= $this->size_col($col_end);
4706 // Subtract the underlying cell heights to find the end cell of the image
4707 while ($height >= $this->size_row($row_end)) {
4708 $height -= $this->size_row($row_end);
4712 // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
4713 // with zero eight or width.
4715 if ($this->size_col($col_start) == 0)
4717 if ($this->size_col($col_end) == 0)
4719 if ($this->size_row($row_start) == 0)
4721 if ($this->size_row($row_end) == 0)
4724 // Convert the pixel values to the percentage value expected by Excel
4725 $x1 = $x1 / $this->size_col($col_start) * 1024;
4726 $y1 = $y1 / $this->size_row($row_start) * 256;
4727 $x2 = $width / $this->size_col($col_end) * 1024; // Distance to right side of object
4728 $y2 = $height / $this->size_row($row_end) * 256; // Distance to bottom of object
4730 $this->_store_obj_picture( $col_start, $x1,
4738 * Convert the width of a cell from user's units to pixels. By interpolation
4739 * the relationship is: y = 7x +5. If the width hasn't been set by the user we
4740 * use the default value. If the col is hidden we use a value of zero.
4742 * @param integer $col The column
4743 * @return integer The width in pixels
4745 function size_col($col)
4747 // Look up the cell value to see if it has been changed
4748 if (isset($this->col_sizes[$col])) {
4749 if ($this->col_sizes[$col] == 0) {
4753 return(floor(7 * $this->col_sizes[$col] + 5));
4762 * Convert the height of a cell from user's units to pixels. By interpolation
4763 * the relationship is: y = 4/3x. If the height hasn't been set by the user we
4764 * use the default value. If the row is hidden we use a value of zero. (Not
4765 * possible to hide row yet).
4767 * @param integer $row The row
4768 * @return integer The width in pixels
4770 function size_row($row)
4772 // Look up the cell value to see if it has been changed
4773 if (isset($this->row_sizes[$row])) {
4774 if ($this->row_sizes[$row] == 0) {
4778 return(floor(4/3 * $this->row_sizes[$row]));
4787 * Store the OBJ record that precedes an IMDATA record. This could be generalise
4788 * to support other Excel objects.
4790 * @param integer $colL Column containing upper left corner of object
4791 * @param integer $dxL Distance from left side of cell
4792 * @param integer $rwT Row containing top left corner of object
4793 * @param integer $dyT Distance from top of cell
4794 * @param integer $colR Column containing lower right corner of object
4795 * @param integer $dxR Distance from right of cell
4796 * @param integer $rwB Row containing bottom right corner of object
4797 * @param integer $dyB Distance from bottom of cell
4799 function _store_obj_picture($colL,$dxL,$rwT,$dyT,$colR,$dxR,$rwB,$dyB)
4801 $record = 0x005d; // Record identifier
4802 $length = 0x003c; // Bytes to follow
4804 $cObj = 0x0001; // Count of objects in file (set to 1)
4805 $OT = 0x0008; // Object type. 8 = Picture
4806 $id = 0x0001; // Object ID
4807 $grbit = 0x0614; // Option flags
4809 $cbMacro = 0x0000; // Length of FMLA structure
4810 $Reserved1 = 0x0000; // Reserved
4811 $Reserved2 = 0x0000; // Reserved
4813 $icvBack = 0x09; // Background colour
4814 $icvFore = 0x09; // Foreground colour
4815 $fls = 0x00; // Fill pattern
4816 $fAuto = 0x00; // Automatic fill
4817 $icv = 0x08; // Line colour
4818 $lns = 0xff; // Line style
4819 $lnw = 0x01; // Line weight
4820 $fAutoB = 0x00; // Automatic border
4821 $frs = 0x0000; // Frame style
4822 $cf = 0x0009; // Image format, 9 = bitmap
4823 $Reserved3 = 0x0000; // Reserved
4824 $cbPictFmla = 0x0000; // Length of FMLA structure
4825 $Reserved4 = 0x0000; // Reserved
4826 $grbit2 = 0x0001; // Option flags
4827 $Reserved5 = 0x0000; // Reserved
4830 $header = pack("vv", $record, $length);
4831 $data = pack("V", $cObj);
4832 $data .= pack("v", $OT);
4833 $data .= pack("v", $id);
4834 $data .= pack("v", $grbit);
4835 $data .= pack("v", $colL);
4836 $data .= pack("v", $dxL);
4837 $data .= pack("v", $rwT);
4838 $data .= pack("v", $dyT);
4839 $data .= pack("v", $colR);
4840 $data .= pack("v", $dxR);
4841 $data .= pack("v", $rwB);
4842 $data .= pack("v", $dyB);
4843 $data .= pack("v", $cbMacro);
4844 $data .= pack("V", $Reserved1);
4845 $data .= pack("v", $Reserved2);
4846 $data .= pack("C", $icvBack);
4847 $data .= pack("C", $icvFore);
4848 $data .= pack("C", $fls);
4849 $data .= pack("C", $fAuto);
4850 $data .= pack("C", $icv);
4851 $data .= pack("C", $lns);
4852 $data .= pack("C", $lnw);
4853 $data .= pack("C", $fAutoB);
4854 $data .= pack("v", $frs);
4855 $data .= pack("V", $cf);
4856 $data .= pack("v", $Reserved3);
4857 $data .= pack("v", $cbPictFmla);
4858 $data .= pack("v", $Reserved4);
4859 $data .= pack("v", $grbit2);
4860 $data .= pack("V", $Reserved5);
4862 $this->_append($header.$data);
4866 * Convert a 24 bit bitmap into the modified internal format used by Windows.
4867 * This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the
4870 * @param string $bitmap The bitmap to process
4871 * @return array Array with data and properties of the bitmap
4873 function _process_bitmap($bitmap)
4876 $bmp_fd = fopen($bitmap,"rb");
4878 die("Couldn't import $bitmap");
4881 // Slurp the file into a string.
4882 $data = fread($bmp_fd, filesize($bitmap));
4884 // Check that the file is big enough to be a bitmap.
4885 if (strlen($data) <= 0x36) {
4886 die("$bitmap doesn't contain enough data.\n");
4889 // The first 2 bytes are used to identify the bitmap.
4890 $identity = unpack("A2", $data);
4891 if ($identity[''] != "BM") {
4892 die("$bitmap doesn't appear to be a valid bitmap image.\n");
4895 // Remove bitmap data: ID.
4896 $data = substr($data, 2);
4898 // Read and remove the bitmap size. This is more reliable than reading
4899 // the data size at offset 0x22.
4901 $size_array = unpack("V", substr($data, 0, 4));
4902 $size = $size_array[''];
4903 $data = substr($data, 4);
4904 $size -= 0x36; // Subtract size of bitmap header.
4905 $size += 0x0C; // Add size of BIFF header.
4907 // Remove bitmap data: reserved, offset, header length.
4908 $data = substr($data, 12);
4910 // Read and remove the bitmap width and height. Verify the sizes.
4911 $width_and_height = unpack("V2", substr($data, 0, 8));
4912 $width = $width_and_height[1];
4913 $height = $width_and_height[2];
4914 $data = substr($data, 8);
4915 if ($width > 0xFFFF) {
4916 die("$bitmap: largest image width supported is 65k.\n");
4918 if ($height > 0xFFFF) {
4919 die("$bitmap: largest image height supported is 65k.\n");
4922 // Read and remove the bitmap planes and bpp data. Verify them.
4923 $planes_and_bitcount = unpack("v2", substr($data, 0, 4));
4924 $data = substr($data, 4);
4925 if ($planes_and_bitcount[2] != 24) { // Bitcount
4926 die("$bitmap isn't a 24bit true color bitmap.\n");
4928 if ($planes_and_bitcount[1] != 1) {
4929 die("$bitmap: only 1 plane supported in bitmap image.\n");
4932 // Read and remove the bitmap compression. Verify compression.
4933 $compression = unpack("V", substr($data, 0, 4));
4934 $data = substr($data, 4);
4937 if ($compression[""] != 0) {
4938 die("$bitmap: compression not supported in bitmap image.\n");
4941 // Remove bitmap data: data size, hres, vres, colours, imp. colours.
4942 $data = substr($data, 20);
4944 // Add the BITMAPCOREHEADER data
4945 $header = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18);
4946 $data = $header . $data;
4948 return (array($width, $height, $size, $data));
4952 * Store the window zoom factor. This should be a reduced fraction but for
4953 * simplicity we will store all fractions with a numerator of 100.
4955 function _store_zoom()
4957 // If scale is 100 we don't need to write a record
4958 if ($this->_zoom == 100) {
4962 $record = 0x00A0; // Record identifier
4963 $length = 0x0004; // Bytes to follow
4965 $header = pack("vv", $record, $length);
4966 $data = pack("vv", $this->_zoom, 100);
4967 $this->_append($header.$data);
4972 * Class for generating Excel Spreadsheets
4974 * @author Xavier Noguer <xnoguer@rezebra.com>
4975 * @package Spreadsheet_WriteExcel
4978 class Workbook extends BIFFwriter
4983 * @param string filename for storing the workbook. "-" for writing to stdout.
4985 function Workbook($filename)
4987 $this->BIFFwriter(); // It needs to call its parent's constructor explicitly
4989 $this->_filename = $filename;
4990 $this->parser = new Parser($this->_byte_order);
4992 $this->activesheet = 0;
4993 $this->firstsheet = 0;
4994 $this->selected = 0;
4995 $this->xf_index = 16; // 15 style XF's and 1 cell XF.
4996 $this->_fileclosed = 0;
4997 $this->_biffsize = 0;
4998 $this->sheetname = "Sheet";
4999 $this->tmp_format = new Format();
5000 $this->worksheets = array();
5001 $this->sheetnames = array();
5002 $this->formats = array();
5003 $this->palette = array();
5005 // Add the default format for hyperlinks
5006 $this->url_format =& $this->add_format(array('color' => 'blue', 'underline' => 1));
5008 // Check for a filename
5009 //if ($this->_filename == '') {
5010 // die('Filename required by Spreadsheet::WriteExcel->new()');
5013 # Warn if tmpfiles can't be used.
5014 //$this->tmpfile_warning();
5015 $this->_set_palette_xl97();
5019 * Calls finalization methods and explicitly close the OLEwriter file
5024 if ($this->_fileclosed) { // Prevent close() from being called twice.
5027 $this->store_workbook();
5028 $this->_fileclosed = 1;
5033 * An accessor for the _worksheets[] array
5034 * Returns an array of the worksheet objects in a workbook
5040 return($this->worksheets());
5044 * An accessor for the _worksheets[] array.
5048 function worksheets()
5050 return($this->worksheets);
5054 * Add a new worksheet to the Excel workbook.
5055 * TODO: Add accessor for $this->{_sheetname} for international Excel versions.
5058 * @param string $name the optional name of the worksheet
5059 * @return &object reference to a worksheet object
5061 function &add_worksheet($name = '')
5063 $index = count($this->worksheets);
5064 $sheetname = $this->sheetname;
5067 $name = $sheetname.($index+1);
5070 // Check that sheetname is <= 31 chars (Excel limit).
5071 if(strlen($name) > 31) {
5072 die("Sheetname $name must be <= 31 chars");
5075 // Check that the worksheet name doesn't already exist: a fatal Excel error.
5076 for($i=0; $i < count($this->worksheets); $i++)
5078 if($name == $this->worksheets[$i]->get_name()) {
5079 die("Worksheet '$name' already exists");
5083 $worksheet = new Worksheet($name,$index,$this->activesheet,
5084 $this->firstsheet,$this->url_format,
5086 $this->worksheets[$index] = &$worksheet; // Store ref for iterator
5087 $this->sheetnames[$index] = $name; // Store EXTERNSHEET names
5088 //$this->parser->set_ext_sheet($name,$index); // Store names in Formula.php
5093 * DEPRECATED!! Use add_worksheet instead
5096 * @deprecated Use add_worksheet instead
5097 * @param string $name the optional name of the worksheet
5098 * @return &object reference to a worksheet object
5100 function &addworksheet($name = '')
5102 return($this->add_worksheet($name));
5106 * Add a new format to the Excel workbook. This adds an XF record and
5107 * a FONT record. Also, pass any properties to the Format constructor.
5110 * @param array $properties array with properties for initializing the format (see Format.php)
5111 * @return &object reference to an XF format
5113 function &add_format($properties = array())
5115 $format = new Format($this->xf_index,$properties);
5116 $this->xf_index += 1;
5117 $this->formats[] = &$format;
5122 * DEPRECATED!! Use add_format instead
5125 * @deprecated Use add_format instead
5126 * @param array $properties array with properties for initializing the format (see Format.php)
5127 * @return &object reference to an XF format
5129 function &addformat($properties = array())
5131 return($this->add_format($properties));
5136 * Change the RGB components of the elements in the colour palette.
5139 * @param integer $index colour index
5140 * @param integer $red red RGB value [0-255]
5141 * @param integer $green green RGB value [0-255]
5142 * @param integer $blue blue RGB value [0-255]
5143 * @return integer The palette index for the custom color
5145 function set_custom_color($index,$red,$green,$blue)
5147 // Match a HTML #xxyyzz style parameter
5148 /*if (defined $_[1] and $_[1] =~ /^#(\w\w)(\w\w)(\w\w)/ ) {
5149 @_ = ($_[0], hex $1, hex $2, hex $3);
5152 // Check that the colour index is the right range
5153 if ($index < 8 or $index > 64) {
5154 die("Color index $index outside range: 8 <= index <= 64");
5157 // Check that the colour components are in the right range
5158 if ( ($red < 0 or $red > 255) ||
5159 ($green < 0 or $green > 255) ||
5160 ($blue < 0 or $blue > 255) )
5162 die("Color component outside range: 0 <= color <= 255");
5165 $index -= 8; // Adjust colour index (wingless dragonfly)
5167 // Set the RGB value
5168 $this->palette[$index] = array($red, $green, $blue, 0);
5173 * Sets the colour palette to the Excel 97+ default.
5175 function _set_palette_xl97()
5177 $this->palette = array(
5178 array(0x00, 0x00, 0x00, 0x00), // 8
5179 array(0xff, 0xff, 0xff, 0x00), // 9
5180 array(0xff, 0x00, 0x00, 0x00), // 10
5181 array(0x00, 0xff, 0x00, 0x00), // 11
5182 array(0x00, 0x00, 0xff, 0x00), // 12
5183 array(0xff, 0xff, 0x00, 0x00), // 13
5184 array(0xff, 0x00, 0xff, 0x00), // 14
5185 array(0x00, 0xff, 0xff, 0x00), // 15
5186 array(0x80, 0x00, 0x00, 0x00), // 16
5187 array(0x00, 0x80, 0x00, 0x00), // 17
5188 array(0x00, 0x00, 0x80, 0x00), // 18
5189 array(0x80, 0x80, 0x00, 0x00), // 19
5190 array(0x80, 0x00, 0x80, 0x00), // 20
5191 array(0x00, 0x80, 0x80, 0x00), // 21
5192 array(0xc0, 0xc0, 0xc0, 0x00), // 22
5193 array(0x80, 0x80, 0x80, 0x00), // 23
5194 array(0x99, 0x99, 0xff, 0x00), // 24
5195 array(0x99, 0x33, 0x66, 0x00), // 25
5196 array(0xff, 0xff, 0xcc, 0x00), // 26
5197 array(0xcc, 0xff, 0xff, 0x00), // 27
5198 array(0x66, 0x00, 0x66, 0x00), // 28
5199 array(0xff, 0x80, 0x80, 0x00), // 29
5200 array(0x00, 0x66, 0xcc, 0x00), // 30
5201 array(0xcc, 0xcc, 0xff, 0x00), // 31
5202 array(0x00, 0x00, 0x80, 0x00), // 32
5203 array(0xff, 0x00, 0xff, 0x00), // 33
5204 array(0xff, 0xff, 0x00, 0x00), // 34
5205 array(0x00, 0xff, 0xff, 0x00), // 35
5206 array(0x80, 0x00, 0x80, 0x00), // 36
5207 array(0x80, 0x00, 0x00, 0x00), // 37
5208 array(0x00, 0x80, 0x80, 0x00), // 38
5209 array(0x00, 0x00, 0xff, 0x00), // 39
5210 array(0x00, 0xcc, 0xff, 0x00), // 40
5211 array(0xcc, 0xff, 0xff, 0x00), // 41
5212 array(0xcc, 0xff, 0xcc, 0x00), // 42
5213 array(0xff, 0xff, 0x99, 0x00), // 43
5214 array(0x99, 0xcc, 0xff, 0x00), // 44
5215 array(0xff, 0x99, 0xcc, 0x00), // 45
5216 array(0xcc, 0x99, 0xff, 0x00), // 46
5217 array(0xff, 0xcc, 0x99, 0x00), // 47
5218 array(0x33, 0x66, 0xff, 0x00), // 48
5219 array(0x33, 0xcc, 0xcc, 0x00), // 49
5220 array(0x99, 0xcc, 0x00, 0x00), // 50
5221 array(0xff, 0xcc, 0x00, 0x00), // 51
5222 array(0xff, 0x99, 0x00, 0x00), // 52
5223 array(0xff, 0x66, 0x00, 0x00), // 53
5224 array(0x66, 0x66, 0x99, 0x00), // 54
5225 array(0x96, 0x96, 0x96, 0x00), // 55
5226 array(0x00, 0x33, 0x66, 0x00), // 56
5227 array(0x33, 0x99, 0x66, 0x00), // 57
5228 array(0x00, 0x33, 0x00, 0x00), // 58
5229 array(0x33, 0x33, 0x00, 0x00), // 59
5230 array(0x99, 0x33, 0x00, 0x00), // 60
5231 array(0x99, 0x33, 0x66, 0x00), // 61
5232 array(0x33, 0x33, 0x99, 0x00), // 62
5233 array(0x33, 0x33, 0x33, 0x00), // 63
5238 ###############################################################################
5240 # _tmpfile_warning()
5242 # Check that tmp files can be created for use in Worksheet.pm. A CGI, mod_perl
5243 # or IIS might not have permission to create tmp files. The test is here rather
5244 # than in Worksheet.pm so that only one warning is given.
5246 /*sub _tmpfile_warning {
5248 my $fh = IO::File->new_tmpfile();
5250 if ((not defined $fh) && ($^W)) {
5251 carp("Unable to create tmp files via IO::File->new_tmpfile(). " .
5252 "Storing data in memory")
5257 * Assemble worksheets into a workbook and send the BIFF data to an OLE
5260 function store_workbook()
5262 // Ensure that at least one worksheet has been selected.
5263 if ($this->activesheet == 0) {
5264 $this->worksheets[0]->selected = 1;
5267 // Calculate the number of selected worksheet tabs and call the finalization
5268 // methods for each worksheet
5269 for($i=0; $i < count($this->worksheets); $i++)
5271 if($this->worksheets[$i]->selected)
5273 $this->worksheets[$i]->close($this->sheetnames);
5276 // Add Workbook globals
5277 $this->_store_bof(0x0005);
5278 $this->_store_externs(); // For print area and repeat rows
5279 $this->_store_names(); // For print area and repeat rows
5280 $this->_store_window1();
5281 $this->_store_1904();
5282 $this->_store_all_fonts();
5283 $this->_store_all_num_formats();
5284 $this->_store_all_xfs();
5285 $this->_store_all_styles();
5286 $this->_store_palette();
5287 $this->_calc_sheet_offsets();
5289 // Add BOUNDSHEET records
5290 for($i=0; $i < count($this->worksheets); $i++) {
5291 $this->_store_boundsheet($this->worksheets[$i]->name,$this->worksheets[$i]->offset);
5294 // End Workbook globals
5295 $this->_store_eof();
5297 // Store the workbook in an OLE container
5298 $this->_store_OLE_file();
5302 * Store the workbook in an OLE container if the total size of the workbook data
5303 * is less than ~ 7MB.
5305 function _store_OLE_file()
5307 $OLE = new OLEwriter($this->_filename);
5308 // Write Worksheet data if data <~ 7MB
5309 if ($OLE->set_size($this->_biffsize))
5311 $OLE->write_header();
5312 $OLE->write($this->_data);
5313 foreach($this->worksheets as $sheet)
5315 while ($tmp = $sheet->get_data()) {
5324 * Calculate offsets for Worksheet BOF records.
5326 function _calc_sheet_offsets()
5330 $offset = $this->_datasize;
5331 for($i=0; $i < count($this->worksheets); $i++) {
5332 $offset += $BOF + strlen($this->worksheets[$i]->name);
5335 for($i=0; $i < count($this->worksheets); $i++) {
5336 $this->worksheets[$i]->offset = $offset;
5337 $offset += $this->worksheets[$i]->_datasize;
5339 $this->_biffsize = $offset;
5343 * Store the Excel FONT records.
5345 function _store_all_fonts()
5347 // tmp_format is added by new(). We use this to write the default XF's
5348 $format = $this->tmp_format;
5349 $font = $format->get_font();
5351 // Note: Fonts are 0-indexed. According to the SDK there is no index 4,
5352 // so the following fonts are 0, 1, 2, 3, 5
5354 for($i=1; $i <= 5; $i++){
5355 $this->_append($font);
5358 // Iterate through the XF objects and write a FONT record if it isn't the
5359 // same as the default FONT and if it hasn't already been used.
5362 $index = 6; // The first user defined FONT
5364 $key = $format->get_font_key(); // The default font from _tmp_format
5365 $fonts[$key] = 0; // Index of the default font
5367 for($i=0; $i < count($this->formats); $i++) {
5368 $key = $this->formats[$i]->get_font_key();
5369 if (isset($fonts[$key])) {
5370 // FONT has already been used
5371 $this->formats[$i]->font_index = $fonts[$key];
5374 // Add a new FONT record
5375 $fonts[$key] = $index;
5376 $this->formats[$i]->font_index = $index;
5378 $font = $this->formats[$i]->get_font();
5379 $this->_append($font);
5385 * Store user defined numerical formats i.e. FORMAT records
5387 function _store_all_num_formats()
5389 // Leaning num_format syndrome
5390 $hash_num_formats = array();
5391 $num_formats = array();
5394 // Iterate through the XF objects and write a FORMAT record if it isn't a
5395 // built-in format type and if the FORMAT string hasn't already been used.
5397 for($i=0; $i < count($this->formats); $i++)
5399 $num_format = $this->formats[$i]->_num_format;
5401 // Check if $num_format is an index to a built-in format.
5402 // Also check for a string of zeros, which is a valid format string
5403 // but would evaluate to zero.
5405 if (!preg_match("/^0+\d/",$num_format))
5407 if (preg_match("/^\d+$/",$num_format)) { // built-in format
5412 if (isset($hash_num_formats[$num_format])) {
5413 // FORMAT has already been used
5414 $this->formats[$i]->_num_format = $hash_num_formats[$num_format];
5419 $hash_num_formats[$num_format] = $index;
5420 $this->formats[$i]->_num_format = $index;
5421 array_push($num_formats,$num_format);
5426 // Write the new FORMAT records starting from 0xA4
5428 foreach ($num_formats as $num_format)
5430 $this->_store_num_format($num_format,$index);
5436 * Write all XF records.
5438 function _store_all_xfs()
5440 // tmp_format is added by the constructor. We use this to write the default XF's
5441 // The default font index is 0
5443 $format = $this->tmp_format;
5444 for ($i=0; $i <= 14; $i++)
5446 $xf = $format->get_xf('style'); // Style XF
5447 $this->_append($xf);
5450 $xf = $format->get_xf('cell'); // Cell XF
5451 $this->_append($xf);
5454 for($i=0; $i < count($this->formats); $i++)
5456 $xf = $this->formats[$i]->get_xf('cell');
5457 $this->_append($xf);
5462 * Write all STYLE records.
5464 function _store_all_styles()
5466 $this->_store_style();
5470 * Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for
5473 function _store_externs()
5475 // Create EXTERNCOUNT with number of worksheets
5476 $this->_store_externcount(count($this->worksheets));
5478 // Create EXTERNSHEET for each worksheet
5479 foreach ($this->sheetnames as $sheetname) {
5480 $this->_store_externsheet($sheetname);
5485 * Write the NAME record to define the print area and the repeat rows and cols.
5487 function _store_names()
5489 // Create the print area NAME records
5490 foreach ($this->worksheets as $worksheet)
5492 // Write a Name record if the print area has been defined
5493 if (isset($worksheet->_print_rowmin))
5495 $this->store_name_short(
5498 $worksheet->_print_rowmin,
5499 $worksheet->_print_rowmax,
5500 $worksheet->_print_colmin,
5501 $worksheet->_print_colmax
5506 // Create the print title NAME records
5507 foreach ($this->worksheets as $worksheet)
5509 $rowmin = $worksheet->_title_rowmin;
5510 $rowmax = $worksheet->_title_rowmax;
5511 $colmin = $worksheet->_title_colmin;
5512 $colmax = $worksheet->_title_colmax;
5514 // Determine if row + col, row, col or nothing has been defined
5515 // and write the appropriate record
5517 if (isset($rowmin) && isset($colmin))
5519 // Row and column titles have been defined.
5520 // Row title has been defined.
5521 $this->store_name_long(
5530 elseif (isset($rowmin))
5532 // Row title has been defined.
5533 $this->store_name_short(
5542 elseif (isset($colmin))
5544 // Column title has been defined.
5545 $this->store_name_short(
5555 // Print title hasn't been defined.
5563 /******************************************************************************
5570 * Write Excel BIFF WINDOW1 record.
5572 function _store_window1()
5574 $record = 0x003D; // Record identifier
5575 $length = 0x0012; // Number of bytes to follow
5577 $xWn = 0x0000; // Horizontal position of window
5578 $yWn = 0x0000; // Vertical position of window
5579 $dxWn = 0x25BC; // Width of window
5580 $dyWn = 0x1572; // Height of window
5582 $grbit = 0x0038; // Option flags
5583 $ctabsel = $this->selected; // Number of workbook tabs selected
5584 $wTabRatio = 0x0258; // Tab to scrollbar ratio
5586 $itabFirst = $this->firstsheet; // 1st displayed worksheet
5587 $itabCur = $this->activesheet; // Active worksheet
5589 $header = pack("vv", $record, $length);
5590 $data = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,
5592 $itabCur, $itabFirst,
5593 $ctabsel, $wTabRatio);
5594 $this->_append($header.$data);
5598 * Writes Excel BIFF BOUNDSHEET record.
5600 * @param string $sheetname Worksheet name
5601 * @param integer $offset Location of worksheet BOF
5603 function _store_boundsheet($sheetname,$offset)
5605 $record = 0x0085; // Record identifier
5606 $length = 0x07 + strlen($sheetname); // Number of bytes to follow
5608 $grbit = 0x0000; // Sheet identifier
5609 $cch = strlen($sheetname); // Length of sheet name
5611 $header = pack("vv", $record, $length);
5612 $data = pack("VvC", $offset, $grbit, $cch);
5613 $this->_append($header.$data.$sheetname);
5617 * Write Excel BIFF STYLE records.
5619 function _store_style()
5621 $record = 0x0293; // Record identifier
5622 $length = 0x0004; // Bytes to follow
5624 $ixfe = 0x8000; // Index to style XF
5625 $BuiltIn = 0x00; // Built-in style
5626 $iLevel = 0xff; // Outline style level
5628 $header = pack("vv", $record, $length);
5629 $data = pack("vCC", $ixfe, $BuiltIn, $iLevel);
5630 $this->_append($header.$data);
5635 * Writes Excel FORMAT record for non "built-in" numerical formats.
5637 * @param string $format Custom format string
5638 * @param integer $ifmt Format index code
5640 function _store_num_format($format,$ifmt)
5642 $record = 0x041E; // Record identifier
5643 $length = 0x03 + strlen($format); // Number of bytes to follow
5645 $cch = strlen($format); // Length of format string
5647 $header = pack("vv", $record, $length);
5648 $data = pack("vC", $ifmt, $cch);
5649 $this->_append($header.$data.$format);
5653 * Write Excel 1904 record to indicate the date system in use.
5655 function _store_1904()
5657 $record = 0x0022; // Record identifier
5658 $length = 0x0002; // Bytes to follow
5660 $f1904 = $this->_1904; // Flag for 1904 date system
5662 $header = pack("vv", $record, $length);
5663 $data = pack("v", $f1904);
5664 $this->_append($header.$data);
5669 * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
5670 * references in the workbook.
5672 * Excel only stores references to external sheets that are used in NAME.
5673 * The workbook NAME record is required to define the print area and the repeat
5676 * A similar method is used in Worksheet.php for a slightly different purpose.
5678 * @param integer $cxals Number of external references
5680 function _store_externcount($cxals)
5682 $record = 0x0016; // Record identifier
5683 $length = 0x0002; // Number of bytes to follow
5685 $header = pack("vv", $record, $length);
5686 $data = pack("v", $cxals);
5687 $this->_append($header.$data);
5692 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
5693 * formulas. NAME record is required to define the print area and the repeat
5696 * A similar method is used in Worksheet.php for a slightly different purpose.
5698 * @param string $sheetname Worksheet name
5700 function _store_externsheet($sheetname)
5702 $record = 0x0017; // Record identifier
5703 $length = 0x02 + strlen($sheetname); // Number of bytes to follow
5705 $cch = strlen($sheetname); // Length of sheet name
5706 $rgch = 0x03; // Filename encoding
5708 $header = pack("vv", $record, $length);
5709 $data = pack("CC", $cch, $rgch);
5710 $this->_append($header.$data.$sheetname);
5715 * Store the NAME record in the short format that is used for storing the print
5716 * area, repeat rows only and repeat columns only.
5718 * @param integer $index Sheet index
5719 * @param integer $type Built-in name type
5720 * @param integer $rowmin Start row
5721 * @param integer $rowmax End row
5722 * @param integer $colmin Start colum
5723 * @param integer $colmax End column
5725 function store_name_short($index,$type,$rowmin,$rowmax,$colmin,$colmax)
5727 $record = 0x0018; // Record identifier
5728 $length = 0x0024; // Number of bytes to follow
5730 $grbit = 0x0020; // Option flags
5731 $chKey = 0x00; // Keyboard shortcut
5732 $cch = 0x01; // Length of text name
5733 $cce = 0x0015; // Length of text definition
5734 $ixals = $index + 1; // Sheet index
5735 $itab = $ixals; // Equal to ixals
5736 $cchCustMenu = 0x00; // Length of cust menu text
5737 $cchDescription = 0x00; // Length of description text
5738 $cchHelptopic = 0x00; // Length of help topic text
5739 $cchStatustext = 0x00; // Length of status bar text
5740 $rgch = $type; // Built-in name type
5743 $unknown04 = 0xffff-$index;
5744 $unknown05 = 0x0000;
5745 $unknown06 = 0x0000;
5746 $unknown07 = 0x1087;
5747 $unknown08 = 0x8005;
5749 $header = pack("vv", $record, $length);
5750 $data = pack("v", $grbit);
5751 $data .= pack("C", $chKey);
5752 $data .= pack("C", $cch);
5753 $data .= pack("v", $cce);
5754 $data .= pack("v", $ixals);
5755 $data .= pack("v", $itab);
5756 $data .= pack("C", $cchCustMenu);
5757 $data .= pack("C", $cchDescription);
5758 $data .= pack("C", $cchHelptopic);
5759 $data .= pack("C", $cchStatustext);
5760 $data .= pack("C", $rgch);
5761 $data .= pack("C", $unknown03);
5762 $data .= pack("v", $unknown04);
5763 $data .= pack("v", $unknown05);
5764 $data .= pack("v", $unknown06);
5765 $data .= pack("v", $unknown07);
5766 $data .= pack("v", $unknown08);
5767 $data .= pack("v", $index);
5768 $data .= pack("v", $index);
5769 $data .= pack("v", $rowmin);
5770 $data .= pack("v", $rowmax);
5771 $data .= pack("C", $colmin);
5772 $data .= pack("C", $colmax);
5773 $this->_append($header.$data);
5778 * Store the NAME record in the long format that is used for storing the repeat
5779 * rows and columns when both are specified. This share a lot of code with
5780 * _store_name_short() but we use a separate method to keep the code clean.
5781 * Code abstraction for reuse can be carried too far, and I should know. ;-)
5783 * @param integer $index Sheet index
5784 * @param integer $type Built-in name type
5785 * @param integer $rowmin Start row
5786 * @param integer $rowmax End row
5787 * @param integer $colmin Start colum
5788 * @param integer $colmax End column
5790 function store_name_long($index,$type,$rowmin,$rowmax,$colmin,$colmax)
5792 $record = 0x0018; // Record identifier
5793 $length = 0x003d; // Number of bytes to follow
5794 $grbit = 0x0020; // Option flags
5795 $chKey = 0x00; // Keyboard shortcut
5796 $cch = 0x01; // Length of text name
5797 $cce = 0x002e; // Length of text definition
5798 $ixals = $index + 1; // Sheet index
5799 $itab = $ixals; // Equal to ixals
5800 $cchCustMenu = 0x00; // Length of cust menu text
5801 $cchDescription = 0x00; // Length of description text
5802 $cchHelptopic = 0x00; // Length of help topic text
5803 $cchStatustext = 0x00; // Length of status bar text
5804 $rgch = $type; // Built-in name type
5807 $unknown02 = 0x002b;
5809 $unknown04 = 0xffff-$index;
5810 $unknown05 = 0x0000;
5811 $unknown06 = 0x0000;
5812 $unknown07 = 0x1087;
5813 $unknown08 = 0x8008;
5815 $header = pack("vv", $record, $length);
5816 $data = pack("v", $grbit);
5817 $data .= pack("C", $chKey);
5818 $data .= pack("C", $cch);
5819 $data .= pack("v", $cce);
5820 $data .= pack("v", $ixals);
5821 $data .= pack("v", $itab);
5822 $data .= pack("C", $cchCustMenu);
5823 $data .= pack("C", $cchDescription);
5824 $data .= pack("C", $cchHelptopic);
5825 $data .= pack("C", $cchStatustext);
5826 $data .= pack("C", $rgch);
5827 $data .= pack("C", $unknown01);
5828 $data .= pack("v", $unknown02);
5829 // Column definition
5830 $data .= pack("C", $unknown03);
5831 $data .= pack("v", $unknown04);
5832 $data .= pack("v", $unknown05);
5833 $data .= pack("v", $unknown06);
5834 $data .= pack("v", $unknown07);
5835 $data .= pack("v", $unknown08);
5836 $data .= pack("v", $index);
5837 $data .= pack("v", $index);
5838 $data .= pack("v", 0x0000);
5839 $data .= pack("v", 0x3fff);
5840 $data .= pack("C", $colmin);
5841 $data .= pack("C", $colmax);
5843 $data .= pack("C", $unknown03);
5844 $data .= pack("v", $unknown04);
5845 $data .= pack("v", $unknown05);
5846 $data .= pack("v", $unknown06);
5847 $data .= pack("v", $unknown07);
5848 $data .= pack("v", $unknown08);
5849 $data .= pack("v", $index);
5850 $data .= pack("v", $index);
5851 $data .= pack("v", $rowmin);
5852 $data .= pack("v", $rowmax);
5853 $data .= pack("C", 0x00);
5854 $data .= pack("C", 0xff);
5856 $data .= pack("C", 0x10);
5857 $this->_append($header.$data);
5862 * Stores the PALETTE biff record.
5864 function _store_palette()
5866 $aref = $this->palette;
5868 $record = 0x0092; // Record identifier
5869 $length = 2 + 4 * count($aref); // Number of bytes to follow
5870 $ccv = count($aref); // Number of RGB values to follow
5871 $data = ''; // The RGB data
5873 // Pack the RGB data
5874 foreach($aref as $color)
5876 foreach($color as $byte) {
5877 $data .= pack("C",$byte);
5881 $header = pack("vvv", $record, $length, $ccv);
5882 $this->_append($header.$data);