3 * Module written/ported by Xavier Noguer <xnoguer@rezebra.com>
5 * The majority of this is _NOT_ my code. I simply ported it from the
6 * PERL Spreadsheet::WriteExcel module.
8 * The author of the Spreadsheet::WriteExcel module is John McNamara
11 * I _DO_ maintain this code, and John McNamara has nothing to do with the
12 * porting of this code to PHP. Any questions directly related to this
13 * class library should be directed to me.
15 * License Information:
17 * Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets
18 * Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com
20 * This library is free software; you can redistribute it and/or
21 * modify it under the terms of the GNU Lesser General Public
22 * License as published by the Free Software Foundation; either
23 * version 2.1 of the License, or (at your option) any later version.
25 * This library is distributed in the hope that it will be useful,
26 * but WITHOUT ANY WARRANTY; without even the implied warranty of
27 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
28 * Lesser General Public License for more details.
30 * You should have received a copy of the GNU Lesser General Public
31 * License along with this library; if not, write to the Free Software
32 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
36 * @const SPREADSHEET_EXCEL_WRITER_ADD token identifier for character "+"
38 define('SPREADSHEET_EXCEL_WRITER_ADD', "+");
41 * @const SPREADSHEET_EXCEL_WRITER_SUB token identifier for character "-"
43 define('SPREADSHEET_EXCEL_WRITER_SUB', "-");
46 * @const SPREADSHEET_EXCEL_WRITER_MUL token identifier for character "*"
48 define('SPREADSHEET_EXCEL_WRITER_MUL', "*");
51 * @const SPREADSHEET_EXCEL_WRITER_DIV token identifier for character "/"
53 define('SPREADSHEET_EXCEL_WRITER_DIV', "/");
56 * @const SPREADSHEET_EXCEL_WRITER_OPEN token identifier for character "("
58 define('SPREADSHEET_EXCEL_WRITER_OPEN', "(");
61 * @const SPREADSHEET_EXCEL_WRITER_CLOSE token identifier for character ")"
63 define('SPREADSHEET_EXCEL_WRITER_CLOSE', ")");
66 * @const SPREADSHEET_EXCEL_WRITER_COMA token identifier for character ","
68 define('SPREADSHEET_EXCEL_WRITER_COMA', ",");
71 * @const SPREADSHEET_EXCEL_WRITER_SEMICOLON token identifier for character ";"
73 define('SPREADSHEET_EXCEL_WRITER_SEMICOLON', ";");
76 * @const SPREADSHEET_EXCEL_WRITER_GT token identifier for character ">"
78 define('SPREADSHEET_EXCEL_WRITER_GT', ">");
81 * @const SPREADSHEET_EXCEL_WRITER_LT token identifier for character "<"
83 define('SPREADSHEET_EXCEL_WRITER_LT', "<");
86 * @const SPREADSHEET_EXCEL_WRITER_LE token identifier for character "<="
88 define('SPREADSHEET_EXCEL_WRITER_LE', "<=");
91 * @const SPREADSHEET_EXCEL_WRITER_GE token identifier for character ">="
93 define('SPREADSHEET_EXCEL_WRITER_GE', ">=");
96 * @const SPREADSHEET_EXCEL_WRITER_EQ token identifier for character "="
98 define('SPREADSHEET_EXCEL_WRITER_EQ', "=");
101 * @const SPREADSHEET_EXCEL_WRITER_NE token identifier for character "<>"
103 define('SPREADSHEET_EXCEL_WRITER_NE', "<>");
106 * Class for creating OLE streams for Excel Spreadsheets
108 * @author Xavier Noguer <xnoguer@rezebra.com>
109 * @package Spreadsheet_WriteExcel
114 * Filename for the OLE stream
121 * Filehandle for the OLE stream
127 * Name of the temporal file in case OLE stream goes to stdout
133 * Variable for preventing closing two times
139 * Size of the data to be written to the OLE stream
145 * Real data size to be written to the OLE stream
151 * Number of big blocks in the OLE stream
157 * Number of list blocks in the OLE stream
163 * Number of big blocks in the OLE stream
169 * Constructor for the OLEwriter class
171 * @param string $OLEfilename the name of the file for the OLE stream
173 function OLEwriter($OLEfilename)
175 $this->_OLEfilename = $OLEfilename;
176 $this->_filehandle = '';
177 $this->_tmp_filename = '';
178 $this->_fileclosed = 0;
179 $this->_biff_only = 0;
180 //$this->_size_allowed = 0;
181 $this->_biffsize = 0;
182 $this->_booksize = 0;
183 $this->_big_blocks = 0;
184 $this->_list_blocks = 0;
185 $this->_root_start = 0;
186 //$this->_block_count = 4;
187 $this->_initialize();
191 * Check for a valid filename and store the filehandle.
192 * Filehandle "-" writes to STDOUT
196 function _initialize()
198 $OLEfile = $this->_OLEfilename;
200 if (($OLEfile == '-') or ($OLEfile == '')) {
201 $this->_tmp_filename = tempnam("/tmp", "OLEwriter");
202 $fh = fopen($this->_tmp_filename, "wb");
204 die("Can't create temporary file.");
207 // Create a new file, open for writing (in binmode)
208 $fh = fopen($OLEfile, "wb");
210 die("Can't open $OLEfile. It may be in use or protected.");
215 $this->_filehandle = $fh;
220 * Set the size of the data to be written to the OLE stream.
221 * The maximun size comes from this:
222 * $big_blocks = (109 depot block x (128 -1 marker word)
223 * - (1 x end words)) = 13842
224 * $maxsize = $big_blocks * 512 bytes = 7087104
227 * @see Spreadsheet_Excel_Writer_Workbook::store_OLE_file()
228 * @param integer $biffsize The size of the data to be written to the OLE stream
229 * @return integer 1 for success
231 function setSize($biffsize)
233 $maxsize = 7087104; // TODO: extend max size
235 if ($biffsize > $maxsize) {
236 die("Maximum file size, $maxsize, exceeded.");
239 $this->_biffsize = $biffsize;
240 // Set the min file size to 4k to avoid having to use small blocks
241 if ($biffsize > 4096) {
242 $this->_booksize = $biffsize;
244 $this->_booksize = 4096;
246 //$this->_size_allowed = 1;
252 * Calculate various sizes needed for the OLE stream
256 function _calculateSizes()
258 $datasize = $this->_booksize;
259 if ($datasize % 512 == 0) {
260 $this->_big_blocks = $datasize/512;
262 $this->_big_blocks = floor($datasize/512) + 1;
264 // There are 127 list blocks and 1 marker blocks for each big block
265 // depot + 1 end of chain block
266 $this->_list_blocks = floor(($this->_big_blocks)/127) + 1;
267 $this->_root_start = $this->_big_blocks;
271 * Write root entry, big block list and close the filehandle.
272 * This routine is used to explicitly close the open filehandle without
273 * having to wait for DESTROY.
276 * @see Spreadsheet_Excel_Writer_Workbook::store_OLE_file()
280 //return if not $this->{_size_allowed};
281 $this->_writePadding();
282 $this->_writePropertyStorage();
283 $this->_writeBigBlockDepot();
284 // Close the filehandle
285 fclose($this->_filehandle);
286 if (($this->_OLEfilename == '-') or ($this->_OLEfilename == '')) {
287 $fh = fopen($this->_tmp_filename, "rb");
289 die("Can't read temporary file.");
292 @unlink($this->_tmp_filename);
294 $this->_fileclosed = 1;
299 * Write BIFF data to OLE file.
301 * @param string $data string of bytes to be written
303 function write($data)
305 fwrite($this->_filehandle, $data, strlen($data));
310 * Write OLE header block.
312 function writeHeader()
314 $this->_calculateSizes();
315 $root_start = $this->_root_start;
316 $num_lists = $this->_list_blocks;
317 $id = pack("nnnn", 0xD0CF, 0x11E0, 0xA1B1, 0x1AE1);
318 $unknown1 = pack("VVVV", 0x00, 0x00, 0x00, 0x00);
319 $unknown2 = pack("vv", 0x3E, 0x03);
320 $unknown3 = pack("v", -2);
321 $unknown4 = pack("v", 0x09);
322 $unknown5 = pack("VVV", 0x06, 0x00, 0x00);
323 $num_bbd_blocks = pack("V", $num_lists);
324 $root_startblock = pack("V", $root_start);
325 $unknown6 = pack("VV", 0x00, 0x1000);
326 $sbd_startblock = pack("V", -2);
327 $unknown7 = pack("VVV", 0x00, -2 ,0x00);
328 $unused = pack("V", -1);
330 fwrite($this->_filehandle, $id);
331 fwrite($this->_filehandle, $unknown1);
332 fwrite($this->_filehandle, $unknown2);
333 fwrite($this->_filehandle, $unknown3);
334 fwrite($this->_filehandle, $unknown4);
335 fwrite($this->_filehandle, $unknown5);
336 fwrite($this->_filehandle, $num_bbd_blocks);
337 fwrite($this->_filehandle, $root_startblock);
338 fwrite($this->_filehandle, $unknown6);
339 fwrite($this->_filehandle, $sbd_startblock);
340 fwrite($this->_filehandle, $unknown7);
342 for ($i=1; $i <= $num_lists; $i++) {
344 fwrite($this->_filehandle, pack("V",$root_start));
346 for ($i = $num_lists; $i <=108; $i++) {
347 fwrite($this->_filehandle, $unused);
353 * Write big block depot.
357 function _writeBigBlockDepot()
359 $num_blocks = $this->_big_blocks;
360 $num_lists = $this->_list_blocks;
361 $total_blocks = $num_lists *128;
362 $used_blocks = $num_blocks + $num_lists +2;
364 $marker = pack("V", -3);
365 $end_of_chain = pack("V", -2);
366 $unused = pack("V", -1);
368 for ($i = 1; $i < $num_blocks; $i++) {
369 fwrite($this->_filehandle, pack("V",$i));
372 fwrite($this->_filehandle, $end_of_chain);
373 fwrite($this->_filehandle, $end_of_chain);
374 for ($i = 0; $i < $num_lists; $i++) {
375 fwrite($this->_filehandle, $marker);
378 for ($i = $used_blocks; $i <= $total_blocks; $i++) {
379 fwrite($this->_filehandle, $unused);
384 * Write property storage. TODO: add summary sheets
388 function _writePropertyStorage()
391 /*************** name type dir start size */
392 $this->_writePps("Root Entry", 0x05, 1, -2, 0x00);
393 $this->_writePps("Book", 0x02, -1, 0x00, $this->_booksize);
394 $this->_writePps('', 0x00, -1, 0x00, 0x0000);
395 $this->_writePps('', 0x00, -1, 0x00, 0x0000);
399 * Write property sheet in property storage
401 * @param string $name name of the property storage.
402 * @param integer $type type of the property storage.
403 * @param integer $dir dir of the property storage.
404 * @param integer $start start of the property storage.
405 * @param integer $size size of the property storage.
408 function _writePps($name, $type, $dir, $start, $size)
414 $name = $name . "\0";
415 $name_length = strlen($name);
416 for ($i = 0; $i < $name_length; $i++) {
417 // Simulate a Unicode string
418 $rawname .= pack("H*",dechex(ord($name{$i}))).pack("C",0);
420 $length = strlen($name) * 2;
423 $zero = pack("C", 0);
424 $pps_sizeofname = pack("v", $length); // 0x40
425 $pps_type = pack("v", $type); // 0x42
426 $pps_prev = pack("V", -1); // 0x44
427 $pps_next = pack("V", -1); // 0x48
428 $pps_dir = pack("V", $dir); // 0x4c
430 $unknown1 = pack("V", 0);
432 $pps_ts1s = pack("V", 0); // 0x64
433 $pps_ts1d = pack("V", 0); // 0x68
434 $pps_ts2s = pack("V", 0); // 0x6c
435 $pps_ts2d = pack("V", 0); // 0x70
436 $pps_sb = pack("V", $start); // 0x74
437 $pps_size = pack("V", $size); // 0x78
440 fwrite($this->_filehandle, $rawname);
441 for ($i = 0; $i < (64 -$length); $i++) {
442 fwrite($this->_filehandle, $zero);
444 fwrite($this->_filehandle, $pps_sizeofname);
445 fwrite($this->_filehandle, $pps_type);
446 fwrite($this->_filehandle, $pps_prev);
447 fwrite($this->_filehandle, $pps_next);
448 fwrite($this->_filehandle, $pps_dir);
449 for ($i = 0; $i < 5; $i++) {
450 fwrite($this->_filehandle, $unknown1);
452 fwrite($this->_filehandle, $pps_ts1s);
453 fwrite($this->_filehandle, $pps_ts1d);
454 fwrite($this->_filehandle, $pps_ts2d);
455 fwrite($this->_filehandle, $pps_ts2d);
456 fwrite($this->_filehandle, $pps_sb);
457 fwrite($this->_filehandle, $pps_size);
458 fwrite($this->_filehandle, $unknown1);
462 * Pad the end of the file
466 function _writePadding()
468 $biffsize = $this->_biffsize;
469 if ($biffsize < 4096) {
474 if ($biffsize % $min_size != 0) {
475 $padding = $min_size - ($biffsize % $min_size);
476 for ($i = 0; $i < $padding; $i++) {
477 fwrite($this->_filehandle, "\0");
484 * Class for writing Excel BIFF records.
486 * From "MICROSOFT EXCEL BINARY FILE FORMAT" by Mark O'Brien (Microsoft Corporation):
488 * BIFF (BInary File Format) is the file format in which Excel documents are
489 * saved on disk. A BIFF file is a complete description of an Excel document.
490 * BIFF files consist of sequences of variable-length records. There are many
491 * different types of BIFF records. For example, one record type describes a
492 * formula entered into a cell; one describes the size and location of a
493 * window into a document; another describes a picture format.
495 * @author Xavier Noguer <xnoguer@php.net>
496 * @category FileFormats
497 * @package Spreadsheet_Excel_Writer
500 class Spreadsheet_Excel_Writer_BIFFwriter
503 * The BIFF/Excel version (5).
506 var $_BIFF_version = 0x0500;
509 * The byte order of this architecture. 0 => little endian, 1 => big endian
515 * The string containing the data of the BIFF stream
521 * The size of the data in bytes. Should be the same as strlen($this->_data)
527 * The maximun length for a BIFF record. See _addContinue()
529 * @see _addContinue()
538 function Spreadsheet_Excel_Writer_BIFFwriter()
540 $this->_byte_order = '';
542 $this->_datasize = 0;
543 $this->_limit = 2080;
544 // Set the byte order
545 $this->_setByteOrder();
549 * Determine the byte order and store it as class data to avoid
550 * recalculating it for each call to new().
554 function _setByteOrder()
556 // Check if "pack" gives the required IEEE 64bit float
557 $teststr = pack("d", 1.2345);
558 $number = pack("C8", 0x8D, 0x97, 0x6E, 0x12, 0x83, 0xC0, 0xF3, 0x3F);
559 if ($number == $teststr) {
560 $byte_order = 0; // Little Endian
561 } elseif ($number == strrev($teststr)){
562 $byte_order = 1; // Big Endian
564 // Give up. I'll fix this in a later version.
565 die("Required floating point format ".
566 "not supported on this platform.");
568 $this->_byte_order = $byte_order;
572 * General storage function
574 * @param string $data binary data to prepend
577 function _prepend($data)
579 if (strlen($data) > $this->_limit) {
580 $data = $this->_addContinue($data);
582 $this->_data = $data.$this->_data;
583 $this->_datasize += strlen($data);
587 * General storage function
589 * @param string $data binary data to append
592 function _append($data)
594 if (strlen($data) > $this->_limit) {
595 $data = $this->_addContinue($data);
597 $this->_data = $this->_data.$data;
598 $this->_datasize += strlen($data);
602 * Writes Excel BOF record to indicate the beginning of a stream or
603 * sub-stream in the BIFF file.
605 * @param integer $type Type of BIFF file to write: 0x0005 Workbook,
609 function _storeBof($type)
611 $record = 0x0809; // Record identifier
613 // According to the SDK $build and $year should be set to zero.
614 // However, this throws a warning in Excel 5. So, use magic numbers.
615 if ($this->_BIFF_version == 0x0500) {
620 } elseif ($this->_BIFF_version == 0x0600) {
622 $unknown = pack("VV", 0x00000041, 0x00000006); //unknown last 8 bytes for BIFF8
626 $version = $this->_BIFF_version;
628 $header = pack("vv", $record, $length);
629 $data = pack("vvvv", $version, $type, $build, $year);
630 $this->_prepend($header . $data . $unknown);
634 * Writes Excel EOF record to indicate the end of a BIFF stream.
640 $record = 0x000A; // Record identifier
641 $length = 0x0000; // Number of bytes to follow
642 $header = pack("vv", $record, $length);
643 $this->_append($header);
647 * Excel limits the size of BIFF records. In Excel 5 the limit is 2084 bytes. In
648 * Excel 97 the limit is 8228 bytes. Records that are longer than these limits
649 * must be split up into CONTINUE blocks.
651 * This function takes a long BIFF record and inserts CONTINUE records as
654 * @param string $data The original binary data to be written
655 * @return string A very convenient string of continue blocks
658 function _addContinue($data)
660 $limit = $this->_limit;
661 $record = 0x003C; // Record identifier
663 // The first 2080/8224 bytes remain intact. However, we have to change
664 // the length field of the record.
665 $tmp = substr($data, 0, 2).pack("v", $limit-4).substr($data, 4, $limit - 4);
667 $header = pack("vv", $record, $limit); // Headers for continue records
669 // Retrieve chunks of 2080/8224 bytes +4 for the header.
670 $data_length = strlen($data);
671 for ($i = $limit; $i < ($data_length - $limit); $i += $limit) {
673 $tmp .= substr($data, $i, $limit);
676 // Retrieve the last chunk of data
677 $header = pack("vv", $record, strlen($data) - $i);
679 $tmp .= substr($data, $i, strlen($data) - $i);
686 FIXME: change prefixes
688 define("OP_BETWEEN", 0x00);
689 define("OP_NOTBETWEEN", 0x01);
690 define("OP_EQUAL", 0x02);
691 define("OP_NOTEQUAL", 0x03);
692 define("OP_GT", 0x04);
693 define("OP_LT", 0x05);
694 define("OP_GTE", 0x06);
695 define("OP_LTE", 0x07);
698 * Baseclass for generating Excel DV records (validations)
700 * @author Herman Kuiper
701 * @category FileFormats
702 * @package Spreadsheet_Excel_Writer
704 class Spreadsheet_Excel_Writer_Validator
721 * The parser from the workbook. Used to parse validation formulas also
722 * @var Spreadsheet_Excel_Writer_Parser
726 function Spreadsheet_Excel_Writer_Validator(&$parser)
728 $this->_parser = $parser;
729 $this->_type = 0x01; // FIXME: add method for setting datatype
730 $this->_style = 0x00;
731 $this->_fixedList = false;
732 $this->_blank = false;
733 $this->_incell = false;
734 $this->_showprompt = false;
735 $this->_showerror = true;
736 $this->_title_prompt = "\x00";
737 $this->_descr_prompt = "\x00";
738 $this->_title_error = "\x00";
739 $this->_descr_error = "\x00";
740 $this->_operator = 0x00; // default is equal
741 $this->_formula1 = '';
742 $this->_formula2 = '';
745 function setPrompt($promptTitle = "\x00", $promptDescription = "\x00", $showPrompt = true)
747 $this->_showprompt = $showPrompt;
748 $this->_title_prompt = $promptTitle;
749 $this->_descr_prompt = $promptDescription;
752 function setError($errorTitle = "\x00", $errorDescription = "\x00", $showError = true)
754 $this->_showerror = $showError;
755 $this->_title_error = $errorTitle;
756 $this->_descr_error = $errorDescription;
759 function allowBlank()
761 $this->_blank = true;
764 function onInvalidStop()
766 $this->_style = 0x00;
769 function onInvalidWarn()
771 $this->_style = 0x01;
774 function onInvalidInfo()
776 $this->_style = 0x02;
779 function setFormula1($formula)
781 // Parse the formula using the parser in Parser.php
782 $this->_parser->parse($formula);
784 $this->_formula1 = $this->_parser->toReversePolish();
788 function setFormula2($formula)
790 // Parse the formula using the parser in Parser.php
791 $this->_parser->parse($formula);
793 $this->_formula2 = $this->_parser->toReversePolish();
797 function _getOptions()
799 $options = $this->_type;
800 $options |= $this->_style << 3;
801 if ($this->_fixedList) {
807 if (!$this->_incell) {
810 if ($this->_showprompt) {
813 if ($this->_showerror) {
816 $options |= $this->_operator << 20;
823 $title_prompt_len = strlen($this->_title_prompt);
824 $descr_prompt_len = strlen($this->_descr_prompt);
825 $title_error_len = strlen($this->_title_error);
826 $descr_error_len = strlen($this->_descr_error);
828 $formula1_size = strlen($this->_formula1);
829 $formula2_size = strlen($this->_formula2);
831 $data = pack("V", $this->_getOptions());
832 $data .= pack("vC", $title_prompt_len, 0x00) . $this->_title_prompt;
833 $data .= pack("vC", $title_error_len, 0x00) . $this->_title_error;
834 $data .= pack("vC", $descr_prompt_len, 0x00) . $this->_descr_prompt;
835 $data .= pack("vC", $descr_error_len, 0x00) . $this->_descr_error;
837 $data .= pack("vv", $formula1_size, 0x0000) . $this->_formula1;
838 $data .= pack("vv", $formula2_size, 0x0000) . $this->_formula2;
845 * Class for generating Excel XF records (formats)
847 * @author Xavier Noguer <xnoguer@rezebra.com>
848 * @category FileFormats
849 * @package Spreadsheet_Excel_Writer
852 class Spreadsheet_Excel_Writer_Format
855 * The index given by the workbook when creating a new format.
861 * Index to the FONT record.
867 * The font name (ASCII).
873 * Height of font (1/20 of a point)
885 * Bit specifiying if the font is italic.
891 * Index to the cell's color
897 * The text underline property
903 * Bit specifiying if the font has strikeout.
906 var $_font_strikeout;
909 * Bit specifiying if the font has outline.
915 * Bit specifiying if the font has shadow.
921 * 2 bytes specifiying the script type for the font.
927 * Byte specifiying the font family.
933 * Byte specifiying the font charset.
939 * An index (2 bytes) to a FORMAT record (number format).
945 * Bit specifying if formulas are hidden.
951 * Bit specifying if the cell is locked.
957 * The three bits specifying the text horizontal alignment.
963 * Bit specifying if the text is wrapped at the right border.
969 * The three bits specifying the text vertical alignment.
975 * 1 bit, apparently not used.
981 * The two bits specifying the text rotation.
987 * The cell's foreground color.
993 * The cell's background color.
999 * The cell's background fill pattern.
1005 * Style of the bottom border of the cell
1011 * Color of the bottom border of the cell.
1017 * Style of the top border of the cell
1023 * Color of the top border of the cell.
1029 * Style of the left border of the cell
1035 * Color of the left border of the cell.
1041 * Style of the right border of the cell
1047 * Color of the right border of the cell.
1056 * @param integer $index the XF index for the format.
1057 * @param array $properties array with properties to be set on initialization.
1059 function Spreadsheet_Excel_Writer_Format($BIFF_version, $index = 0, $properties = array())
1061 $this->_xf_index = $index;
1062 $this->_BIFF_version = $BIFF_version;
1063 $this->font_index = 0;
1064 $this->_font_name = 'Arial';
1066 $this->_bold = 0x0190;
1068 $this->_color = 0x7FFF;
1069 $this->_underline = 0;
1070 $this->_font_strikeout = 0;
1071 $this->_font_outline = 0;
1072 $this->_font_shadow = 0;
1073 $this->_font_script = 0;
1074 $this->_font_family = 0;
1075 $this->_font_charset = 0;
1077 $this->_num_format = 0;
1082 $this->_text_h_align = 0;
1083 $this->_text_wrap = 0;
1084 $this->_text_v_align = 2;
1085 $this->_text_justlast = 0;
1086 $this->_rotation = 0;
1088 $this->_fg_color = 0x40;
1089 $this->_bg_color = 0x41;
1091 $this->_pattern = 0;
1099 $this->_bottom_color = 0x40;
1100 $this->_top_color = 0x40;
1101 $this->_left_color = 0x40;
1102 $this->_right_color = 0x40;
1103 $this->_diag_color = 0x40;
1105 // Set properties passed to Spreadsheet_Excel_Writer_Workbook::addFormat()
1106 foreach ($properties as $property => $value)
1108 if (method_exists($this, 'set'.ucwords($property))) {
1109 $method_name = 'set'.ucwords($property);
1110 $this->$method_name($value);
1117 * Generate an Excel BIFF XF record (style or cell).
1119 * @param string $style The type of the XF record ('style' or 'cell').
1120 * @return string The XF record
1122 function getXf($style)
1124 // Set the type of the XF record and some of the attributes.
1125 if ($style == 'style') {
1128 $style = $this->_locked;
1129 $style |= $this->_hidden << 1;
1132 // Flags to indicate if attributes have been set.
1133 $atr_num = ($this->_num_format != 0)?1:0;
1134 $atr_fnt = ($this->font_index != 0)?1:0;
1135 $atr_alc = ($this->_text_wrap)?1:0;
1136 $atr_bdr = ($this->_bottom ||
1140 $atr_pat = (($this->_fg_color != 0x40) ||
1141 ($this->_bg_color != 0x41) ||
1142 $this->_pattern)?1:0;
1143 $atr_prot = $this->_locked | $this->_hidden;
1145 // Zero the default border colour if the border has not been set.
1146 if ($this->_bottom == 0) {
1147 $this->_bottom_color = 0;
1149 if ($this->_top == 0) {
1150 $this->_top_color = 0;
1152 if ($this->_right == 0) {
1153 $this->_right_color = 0;
1155 if ($this->_left == 0) {
1156 $this->_left_color = 0;
1158 if ($this->_diag == 0) {
1159 $this->_diag_color = 0;
1162 $record = 0x00E0; // Record identifier
1163 if ($this->_BIFF_version == 0x0500) {
1164 $length = 0x0010; // Number of bytes to follow
1166 if ($this->_BIFF_version == 0x0600) {
1170 $ifnt = $this->font_index; // Index to FONT record
1171 $ifmt = $this->_num_format; // Index to FORMAT record
1172 if ($this->_BIFF_version == 0x0500) {
1173 $align = $this->_text_h_align; // Alignment
1174 $align |= $this->_text_wrap << 3;
1175 $align |= $this->_text_v_align << 4;
1176 $align |= $this->_text_justlast << 7;
1177 $align |= $this->_rotation << 8;
1178 $align |= $atr_num << 10;
1179 $align |= $atr_fnt << 11;
1180 $align |= $atr_alc << 12;
1181 $align |= $atr_bdr << 13;
1182 $align |= $atr_pat << 14;
1183 $align |= $atr_prot << 15;
1185 $icv = $this->_fg_color; // fg and bg pattern colors
1186 $icv |= $this->_bg_color << 7;
1188 $fill = $this->_pattern; // Fill and border line style
1189 $fill |= $this->_bottom << 6;
1190 $fill |= $this->_bottom_color << 9;
1192 $border1 = $this->_top; // Border line style and color
1193 $border1 |= $this->_left << 3;
1194 $border1 |= $this->_right << 6;
1195 $border1 |= $this->_top_color << 9;
1197 $border2 = $this->_left_color; // Border color
1198 $border2 |= $this->_right_color << 7;
1200 $header = pack("vv", $record, $length);
1201 $data = pack("vvvvvvvv", $ifnt, $ifmt, $style, $align,
1203 $border1, $border2);
1204 } elseif ($this->_BIFF_version == 0x0600) {
1205 $align = $this->_text_h_align; // Alignment
1206 $align |= $this->_text_wrap << 3;
1207 $align |= $this->_text_v_align << 4;
1208 $align |= $this->_text_justlast << 7;
1210 $used_attrib = $atr_num << 2;
1211 $used_attrib |= $atr_fnt << 3;
1212 $used_attrib |= $atr_alc << 4;
1213 $used_attrib |= $atr_bdr << 5;
1214 $used_attrib |= $atr_pat << 6;
1215 $used_attrib |= $atr_prot << 7;
1217 $icv = $this->_fg_color; // fg and bg pattern colors
1218 $icv |= $this->_bg_color << 7;
1220 $border1 = $this->_left; // Border line style and color
1221 $border1 |= $this->_right << 4;
1222 $border1 |= $this->_top << 8;
1223 $border1 |= $this->_bottom << 12;
1224 $border1 |= $this->_left_color << 16;
1225 $border1 |= $this->_right_color << 23;
1226 $diag_tl_to_rb = 0; // FIXME: add method
1227 $diag_tr_to_lb = 0; // FIXME: add method
1228 $border1 |= $diag_tl_to_rb << 30;
1229 $border1 |= $diag_tr_to_lb << 31;
1231 $border2 = $this->_top_color; // Border color
1232 $border2 |= $this->_bottom_color << 7;
1233 $border2 |= $this->_diag_color << 14;
1234 $border2 |= $this->_diag << 21;
1235 $border2 |= $this->_pattern << 26;
1237 $header = pack("vv", $record, $length);
1240 $biff8_options = 0x00;
1241 $data = pack("vvvC", $ifnt, $ifmt, $style, $align);
1242 $data .= pack("CCC", $rotation, $biff8_options, $used_attrib);
1243 $data .= pack("VVv", $border1, $border2, $icv);
1246 return($header . $data);
1250 * Generate an Excel BIFF FONT record.
1252 * @return string The FONT record
1256 $dyHeight = $this->_size * 20; // Height of font (1/20 of a point)
1257 $icv = $this->_color; // Index to color palette
1258 $bls = $this->_bold; // Bold style
1259 $sss = $this->_font_script; // Superscript/subscript
1260 $uls = $this->_underline; // Underline
1261 $bFamily = $this->_font_family; // Font family
1262 $bCharSet = $this->_font_charset; // Character set
1263 $encoding = 0; // TODO: Unicode support
1265 $cch = strlen($this->_font_name); // Length of font name
1266 $record = 0x31; // Record identifier
1267 if ($this->_BIFF_version == 0x0500) {
1268 $length = 0x0F + $cch; // Record length
1269 } elseif ($this->_BIFF_version == 0x0600) {
1270 $length = 0x10 + $cch;
1272 $reserved = 0x00; // Reserved
1273 $grbit = 0x00; // Font attributes
1274 if ($this->_italic) {
1277 if ($this->_font_strikeout) {
1280 if ($this->_font_outline) {
1283 if ($this->_font_shadow) {
1287 $header = pack("vv", $record, $length);
1288 if ($this->_BIFF_version == 0x0500) {
1289 $data = pack("vvvvvCCCCC", $dyHeight, $grbit, $icv, $bls,
1290 $sss, $uls, $bFamily,
1291 $bCharSet, $reserved, $cch);
1292 } elseif ($this->_BIFF_version == 0x0600) {
1293 $data = pack("vvvvvCCCCCC", $dyHeight, $grbit, $icv, $bls,
1294 $sss, $uls, $bFamily,
1295 $bCharSet, $reserved, $cch, $encoding);
1297 return($header . $data . $this->_font_name);
1301 * Returns a unique hash key for a font.
1302 * Used by Spreadsheet_Excel_Writer_Workbook::_storeAllFonts()
1304 * The elements that form the key are arranged to increase the probability of
1305 * generating a unique key. Elements that hold a large range of numbers
1306 * (eg. _color) are placed between two binary elements such as _italic
1308 * @return string A key for this font
1310 function getFontKey()
1312 $key = "$this->_font_name$this->_size";
1313 $key .= "$this->_font_script$this->_underline";
1314 $key .= "$this->_font_strikeout$this->_bold$this->_font_outline";
1315 $key .= "$this->_font_family$this->_font_charset";
1316 $key .= "$this->_font_shadow$this->_color$this->_italic";
1317 $key = str_replace(' ', '_', $key);
1322 * Returns the index used by Spreadsheet_Excel_Writer_Worksheet::_XF()
1324 * @return integer The index for the XF record
1326 function getXfIndex()
1328 return($this->_xf_index);
1332 * Used in conjunction with the set_xxx_color methods to convert a color
1333 * string into a number. Color range is 0..63 but we will restrict it
1334 * to 8..63 to comply with Gnumeric. Colors 0..7 are repeated in 8..15.
1337 * @param string $name_color name of the color (i.e.: 'blue', 'red', etc..). Optional.
1338 * @return integer The color index
1340 function _getColor($name_color = '')
1363 // Return the default color, 0x7FFF, if undef,
1364 if ($name_color == '') {
1368 // or the color string converted to an integer,
1369 if (isset($colors[$name_color])) {
1370 return($colors[$name_color]);
1373 // or the default color if string is unrecognised,
1374 if (preg_match("/\D/",$name_color)) {
1378 // or an index < 8 mapped into the correct range,
1379 if ($name_color < 8) {
1380 return($name_color + 8);
1383 // or the default color if arg is outside range,
1384 if ($name_color > 63) {
1388 // or an integer in the valid range
1389 return($name_color);
1393 * Set cell alignment.
1396 * @param string $location alignment for the cell ('left', 'right', etc...).
1398 function setAlign($location)
1400 if (preg_match("/\d/",$location)) {
1401 return; // Ignore numbers
1404 $location = strtolower($location);
1406 if ($location == 'left') {
1407 $this->_text_h_align = 1;
1409 if ($location == 'centre') {
1410 $this->_text_h_align = 2;
1412 if ($location == 'center') {
1413 $this->_text_h_align = 2;
1415 if ($location == 'right') {
1416 $this->_text_h_align = 3;
1418 if ($location == 'fill') {
1419 $this->_text_h_align = 4;
1421 if ($location == 'justify') {
1422 $this->_text_h_align = 5;
1424 if ($location == 'merge') {
1425 $this->_text_h_align = 6;
1427 if ($location == 'equal_space') { // For T.K.
1428 $this->_text_h_align = 7;
1430 if ($location == 'top') {
1431 $this->_text_v_align = 0;
1433 if ($location == 'vcentre') {
1434 $this->_text_v_align = 1;
1436 if ($location == 'vcenter') {
1437 $this->_text_v_align = 1;
1439 if ($location == 'bottom') {
1440 $this->_text_v_align = 2;
1442 if ($location == 'vjustify') {
1443 $this->_text_v_align = 3;
1445 if ($location == 'vequal_space') { // For T.K.
1446 $this->_text_v_align = 4;
1451 * Set cell horizontal alignment.
1454 * @param string $location alignment for the cell ('left', 'right', etc...).
1456 function setHAlign($location)
1458 if (preg_match("/\d/",$location)) {
1459 return; // Ignore numbers
1462 $location = strtolower($location);
1464 if ($location == 'left') {
1465 $this->_text_h_align = 1;
1467 if ($location == 'centre') {
1468 $this->_text_h_align = 2;
1470 if ($location == 'center') {
1471 $this->_text_h_align = 2;
1473 if ($location == 'right') {
1474 $this->_text_h_align = 3;
1476 if ($location == 'fill') {
1477 $this->_text_h_align = 4;
1479 if ($location == 'justify') {
1480 $this->_text_h_align = 5;
1482 if ($location == 'merge') {
1483 $this->_text_h_align = 6;
1485 if ($location == 'equal_space') { // For T.K.
1486 $this->_text_h_align = 7;
1491 * Set cell vertical alignment.
1494 * @param string $location alignment for the cell ('top', 'vleft', 'vright', etc...).
1496 function setVAlign($location)
1498 if (preg_match("/\d/",$location)) {
1499 return; // Ignore numbers
1502 $location = strtolower($location);
1504 if ($location == 'top') {
1505 $this->_text_v_align = 0;
1507 if ($location == 'vcentre') {
1508 $this->_text_v_align = 1;
1510 if ($location == 'vcenter') {
1511 $this->_text_v_align = 1;
1513 if ($location == 'bottom') {
1514 $this->_text_v_align = 2;
1516 if ($location == 'vjustify') {
1517 $this->_text_v_align = 3;
1519 if ($location == 'vequal_space') { // For T.K.
1520 $this->_text_v_align = 4;
1525 * This is an alias for the unintuitive setAlign('merge')
1531 $this->setAlign('merge');
1535 * Sets the boldness of the text.
1536 * Bold has a range 100..1000.
1537 * 0 (400) is normal. 1 (700) is bold.
1540 * @param integer $weight Weight for the text, 0 maps to 400 (normal text),
1541 1 maps to 700 (bold text). Valid range is: 100-1000.
1542 It's Optional, default is 1 (bold).
1544 function setBold($weight = 1)
1547 $weight = 0x2BC; // Bold text
1550 $weight = 0x190; // Normal text
1552 if ($weight < 0x064) {
1553 $weight = 0x190; // Lower bound
1555 if ($weight > 0x3E8) {
1556 $weight = 0x190; // Upper bound
1558 $this->_bold = $weight;
1562 /************************************
1563 * FUNCTIONS FOR SETTING CELLS BORDERS
1567 * Sets the width for the bottom border of the cell
1570 * @param integer $style style of the cell border. 1 => thin, 2 => thick.
1572 function setBottom($style)
1574 $this->_bottom = $style;
1578 * Sets the width for the top border of the cell
1581 * @param integer $style style of the cell top border. 1 => thin, 2 => thick.
1583 function setTop($style)
1585 $this->_top = $style;
1589 * Sets the width for the left border of the cell
1592 * @param integer $style style of the cell left border. 1 => thin, 2 => thick.
1594 function setLeft($style)
1596 $this->_left = $style;
1600 * Sets the width for the right border of the cell
1603 * @param integer $style style of the cell right border. 1 => thin, 2 => thick.
1605 function setRight($style)
1607 $this->_right = $style;
1612 * Set cells borders to the same style
1615 * @param integer $style style to apply for all cell borders. 1 => thin, 2 => thick.
1617 function setBorder($style)
1619 $this->setBottom($style);
1620 $this->setTop($style);
1621 $this->setLeft($style);
1622 $this->setRight($style);
1626 /*******************************************
1627 * FUNCTIONS FOR SETTING CELLS BORDERS COLORS
1631 * Sets all the cell's borders to the same color
1634 * @param mixed $color The color we are setting. Either a string (like 'blue'),
1635 * or an integer (range is [8...63]).
1637 function setBorderColor($color)
1639 $this->setBottomColor($color);
1640 $this->setTopColor($color);
1641 $this->setLeftColor($color);
1642 $this->setRightColor($color);
1646 * Sets the cell's bottom border color
1649 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
1651 function setBottomColor($color)
1653 $value = $this->_getColor($color);
1654 $this->_bottom_color = $value;
1658 * Sets the cell's top border color
1661 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
1663 function setTopColor($color)
1665 $value = $this->_getColor($color);
1666 $this->_top_color = $value;
1670 * Sets the cell's left border color
1673 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
1675 function setLeftColor($color)
1677 $value = $this->_getColor($color);
1678 $this->_left_color = $value;
1682 * Sets the cell's right border color
1685 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
1687 function setRightColor($color)
1689 $value = $this->_getColor($color);
1690 $this->_right_color = $value;
1695 * Sets the cell's foreground color
1698 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
1700 function setFgColor($color)
1702 $value = $this->_getColor($color);
1703 $this->_fg_color = $value;
1704 if ($this->_pattern == 0) { // force color to be seen
1705 $this->_pattern = 1;
1710 * Sets the cell's background color
1713 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
1715 function setBgColor($color)
1717 $value = $this->_getColor($color);
1718 $this->_bg_color = $value;
1719 if ($this->_pattern == 0) { // force color to be seen
1720 $this->_pattern = 1;
1725 * Sets the cell's color
1728 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
1730 function setColor($color)
1732 $value = $this->_getColor($color);
1733 $this->_color = $value;
1737 * Sets the fill pattern attribute of a cell
1740 * @param integer $arg Optional. Defaults to 1. Meaningful values are: 0-18,
1741 * 0 meaning no background.
1743 function setPattern($arg = 1)
1745 $this->_pattern = $arg;
1749 * Sets the underline of the text
1752 * @param integer $underline The value for underline. Possible values are:
1753 * 1 => underline, 2 => double underline.
1755 function setUnderline($underline)
1757 $this->_underline = $underline;
1761 * Sets the font style as italic
1765 function setItalic()
1771 * Sets the font size
1774 * @param integer $size The font size (in pixels I think).
1776 function setSize($size)
1778 $this->_size = $size;
1782 * Sets text wrapping
1786 function setTextWrap()
1788 $this->_text_wrap = 1;
1792 * Sets the orientation of the text
1795 * @param integer $angle The rotation angle for the text (clockwise). Possible
1796 values are: 0, 90, 270 and -1 for stacking top-to-bottom.
1798 function setTextRotation($angle)
1803 $this->_rotation = 0;
1806 $this->_rotation = 3;
1809 $this->_rotation = 2;
1812 $this->_rotation = 1;
1815 $this->_rotation = 0;
1821 * Sets the numeric format.
1822 * It can be date, time, currency, etc...
1825 * @param integer $num_format The numeric format.
1827 function setNumFormat($num_format)
1829 $this->_num_format = $num_format;
1833 * Sets font as strikeout.
1837 function setStrikeOut()
1839 $this->_font_strikeout = 1;
1843 * Sets outlining for a font.
1847 function setOutLine()
1849 $this->_font_outline = 1;
1853 * Sets font as shadow.
1857 function setShadow()
1859 $this->_font_shadow = 1;
1863 * Sets the script type of the text
1866 * @param integer $script The value for script type. Possible values are:
1867 * 1 => superscript, 2 => subscript.
1869 function setScript($script)
1871 $this->_font_script = $script;
1879 function setLocked()
1885 * Unlocks a cell. Useful for unprotecting particular cells of a protected sheet.
1889 function setUnLocked()
1895 * Sets the font family name.
1898 * @param string $fontfamily The font family name. Possible values are:
1899 * 'Times New Roman', 'Arial', 'Courier'.
1901 function setFontFamily($font_family)
1903 $this->_font_name = $font_family;
1908 * Class for parsing Excel formulas
1910 * @author Xavier Noguer <xnoguer@rezebra.com>
1911 * @category FileFormats
1912 * @package Spreadsheet_Excel_Writer
1915 class Spreadsheet_Excel_Writer_Parser
1918 * The index of the character we are currently looking at
1924 * The token we are working on.
1927 var $_current_token;
1930 * The formula to parse
1936 * The character ahead of the current char
1942 * The parse tree to be generated
1948 * The byte order. 1 => big endian, 0 => little endian.
1954 * Array of external sheets
1960 * Array of sheet references in the form of REF structures
1966 * The BIFF version for the workbook
1972 * The class constructor
1974 * @param integer $byte_order The byte order (Little endian or Big endian) of the architecture
1975 (optional). 1 => big endian, 0 (default) little endian.
1977 function Spreadsheet_Excel_Writer_Parser($byte_order, $biff_version)
1979 $this->_current_char = 0;
1980 $this->_BIFF_version = $biff_version;
1981 $this->_current_token = ''; // The token we are working on.
1982 $this->_formula = ''; // The formula to parse.
1983 $this->_lookahead = ''; // The character ahead of the current char.
1984 $this->_parse_tree = ''; // The parse tree to be generated.
1985 $this->_initializeHashes(); // Initialize the hashes: ptg's and function's ptg's
1986 $this->_byte_order = $byte_order; // Little Endian or Big Endian
1987 $this->_ext_sheets = array();
1988 $this->_references = array();
1992 * Initialize the ptg and function hashes.
1996 function _initializeHashes()
1998 // The Excel ptg indices
2007 'ptgConcat' => 0x08,
2018 'ptgUminus' => 0x13,
2019 'ptgPercent' => 0x14,
2021 'ptgMissArg' => 0x16,
2025 'ptgEndSheet' => 0x1B,
2032 'ptgFuncVar' => 0x22,
2036 'ptgMemArea' => 0x26,
2037 'ptgMemErr' => 0x27,
2038 'ptgMemNoMem' => 0x28,
2039 'ptgMemFunc' => 0x29,
2040 'ptgRefErr' => 0x2A,
2041 'ptgAreaErr' => 0x2B,
2044 'ptgMemAreaN' => 0x2E,
2045 'ptgMemNoMemN' => 0x2F,
2048 'ptgArea3d' => 0x3B,
2049 'ptgRefErr3d' => 0x3C,
2050 'ptgAreaErr3d' => 0x3D,
2051 'ptgArrayV' => 0x40,
2053 'ptgFuncVarV' => 0x42,
2057 'ptgMemAreaV' => 0x46,
2058 'ptgMemErrV' => 0x47,
2059 'ptgMemNoMemV' => 0x48,
2060 'ptgMemFuncV' => 0x49,
2061 'ptgRefErrV' => 0x4A,
2062 'ptgAreaErrV' => 0x4B,
2064 'ptgAreaNV' => 0x4D,
2065 'ptgMemAreaNV' => 0x4E,
2066 'ptgMemNoMemN' => 0x4F,
2067 'ptgFuncCEV' => 0x58,
2068 'ptgNameXV' => 0x59,
2069 'ptgRef3dV' => 0x5A,
2070 'ptgArea3dV' => 0x5B,
2071 'ptgRefErr3dV' => 0x5C,
2072 'ptgAreaErr3d' => 0x5D,
2073 'ptgArrayA' => 0x60,
2075 'ptgFuncVarA' => 0x62,
2079 'ptgMemAreaA' => 0x66,
2080 'ptgMemErrA' => 0x67,
2081 'ptgMemNoMemA' => 0x68,
2082 'ptgMemFuncA' => 0x69,
2083 'ptgRefErrA' => 0x6A,
2084 'ptgAreaErrA' => 0x6B,
2086 'ptgAreaNA' => 0x6D,
2087 'ptgMemAreaNA' => 0x6E,
2088 'ptgMemNoMemN' => 0x6F,
2089 'ptgFuncCEA' => 0x78,
2090 'ptgNameXA' => 0x79,
2091 'ptgRef3dA' => 0x7A,
2092 'ptgArea3dA' => 0x7B,
2093 'ptgRefErr3dA' => 0x7C,
2094 'ptgAreaErr3d' => 0x7D
2097 // Thanks to Michael Meeks and Gnumeric for the initial arg values.
2099 // The following hash was generated by "function_locale.pl" in the distro.
2100 // Refer to function_locale.pl for non-English function names.
2102 // The array elements are as follow:
2103 // ptg: The Excel function ptg code.
2104 // args: The number of arguments that the function takes:
2105 // >=0 is a fixed number of arguments.
2106 // -1 is a variable number of arguments.
2107 // class: The reference, value or array class of the function args.
2108 // vol: The function is volatile.
2110 $this->_functions = array(
2111 // function ptg args class vol
2112 'COUNT' => array( 0, -1, 0, 0 ),
2113 'IF' => array( 1, -1, 1, 0 ),
2114 'ISNA' => array( 2, 1, 1, 0 ),
2115 'ISERROR' => array( 3, 1, 1, 0 ),
2116 'SUM' => array( 4, -1, 0, 0 ),
2117 'AVERAGE' => array( 5, -1, 0, 0 ),
2118 'MIN' => array( 6, -1, 0, 0 ),
2119 'MAX' => array( 7, -1, 0, 0 ),
2120 'ROW' => array( 8, -1, 0, 0 ),
2121 'COLUMN' => array( 9, -1, 0, 0 ),
2122 'NA' => array( 10, 0, 0, 0 ),
2123 'NPV' => array( 11, -1, 1, 0 ),
2124 'STDEV' => array( 12, -1, 0, 0 ),
2125 'DOLLAR' => array( 13, -1, 1, 0 ),
2126 'FIXED' => array( 14, -1, 1, 0 ),
2127 'SIN' => array( 15, 1, 1, 0 ),
2128 'COS' => array( 16, 1, 1, 0 ),
2129 'TAN' => array( 17, 1, 1, 0 ),
2130 'ATAN' => array( 18, 1, 1, 0 ),
2131 'PI' => array( 19, 0, 1, 0 ),
2132 'SQRT' => array( 20, 1, 1, 0 ),
2133 'EXP' => array( 21, 1, 1, 0 ),
2134 'LN' => array( 22, 1, 1, 0 ),
2135 'LOG10' => array( 23, 1, 1, 0 ),
2136 'ABS' => array( 24, 1, 1, 0 ),
2137 'INT' => array( 25, 1, 1, 0 ),
2138 'SIGN' => array( 26, 1, 1, 0 ),
2139 'ROUND' => array( 27, 2, 1, 0 ),
2140 'LOOKUP' => array( 28, -1, 0, 0 ),
2141 'INDEX' => array( 29, -1, 0, 1 ),
2142 'REPT' => array( 30, 2, 1, 0 ),
2143 'MID' => array( 31, 3, 1, 0 ),
2144 'LEN' => array( 32, 1, 1, 0 ),
2145 'VALUE' => array( 33, 1, 1, 0 ),
2146 'TRUE' => array( 34, 0, 1, 0 ),
2147 'FALSE' => array( 35, 0, 1, 0 ),
2148 'AND' => array( 36, -1, 0, 0 ),
2149 'OR' => array( 37, -1, 0, 0 ),
2150 'NOT' => array( 38, 1, 1, 0 ),
2151 'MOD' => array( 39, 2, 1, 0 ),
2152 'DCOUNT' => array( 40, 3, 0, 0 ),
2153 'DSUM' => array( 41, 3, 0, 0 ),
2154 'DAVERAGE' => array( 42, 3, 0, 0 ),
2155 'DMIN' => array( 43, 3, 0, 0 ),
2156 'DMAX' => array( 44, 3, 0, 0 ),
2157 'DSTDEV' => array( 45, 3, 0, 0 ),
2158 'VAR' => array( 46, -1, 0, 0 ),
2159 'DVAR' => array( 47, 3, 0, 0 ),
2160 'TEXT' => array( 48, 2, 1, 0 ),
2161 'LINEST' => array( 49, -1, 0, 0 ),
2162 'TREND' => array( 50, -1, 0, 0 ),
2163 'LOGEST' => array( 51, -1, 0, 0 ),
2164 'GROWTH' => array( 52, -1, 0, 0 ),
2165 'PV' => array( 56, -1, 1, 0 ),
2166 'FV' => array( 57, -1, 1, 0 ),
2167 'NPER' => array( 58, -1, 1, 0 ),
2168 'PMT' => array( 59, -1, 1, 0 ),
2169 'RATE' => array( 60, -1, 1, 0 ),
2170 'MIRR' => array( 61, 3, 0, 0 ),
2171 'IRR' => array( 62, -1, 0, 0 ),
2172 'RAND' => array( 63, 0, 1, 1 ),
2173 'MATCH' => array( 64, -1, 0, 0 ),
2174 'DATE' => array( 65, 3, 1, 0 ),
2175 'TIME' => array( 66, 3, 1, 0 ),
2176 'DAY' => array( 67, 1, 1, 0 ),
2177 'MONTH' => array( 68, 1, 1, 0 ),
2178 'YEAR' => array( 69, 1, 1, 0 ),
2179 'WEEKDAY' => array( 70, -1, 1, 0 ),
2180 'HOUR' => array( 71, 1, 1, 0 ),
2181 'MINUTE' => array( 72, 1, 1, 0 ),
2182 'SECOND' => array( 73, 1, 1, 0 ),
2183 'NOW' => array( 74, 0, 1, 1 ),
2184 'AREAS' => array( 75, 1, 0, 1 ),
2185 'ROWS' => array( 76, 1, 0, 1 ),
2186 'COLUMNS' => array( 77, 1, 0, 1 ),
2187 'OFFSET' => array( 78, -1, 0, 1 ),
2188 'SEARCH' => array( 82, -1, 1, 0 ),
2189 'TRANSPOSE' => array( 83, 1, 1, 0 ),
2190 'TYPE' => array( 86, 1, 1, 0 ),
2191 'ATAN2' => array( 97, 2, 1, 0 ),
2192 'ASIN' => array( 98, 1, 1, 0 ),
2193 'ACOS' => array( 99, 1, 1, 0 ),
2194 'CHOOSE' => array( 100, -1, 1, 0 ),
2195 'HLOOKUP' => array( 101, -1, 0, 0 ),
2196 'VLOOKUP' => array( 102, -1, 0, 0 ),
2197 'ISREF' => array( 105, 1, 0, 0 ),
2198 'LOG' => array( 109, -1, 1, 0 ),
2199 'CHAR' => array( 111, 1, 1, 0 ),
2200 'LOWER' => array( 112, 1, 1, 0 ),
2201 'UPPER' => array( 113, 1, 1, 0 ),
2202 'PROPER' => array( 114, 1, 1, 0 ),
2203 'LEFT' => array( 115, -1, 1, 0 ),
2204 'RIGHT' => array( 116, -1, 1, 0 ),
2205 'EXACT' => array( 117, 2, 1, 0 ),
2206 'TRIM' => array( 118, 1, 1, 0 ),
2207 'REPLACE' => array( 119, 4, 1, 0 ),
2208 'SUBSTITUTE' => array( 120, -1, 1, 0 ),
2209 'CODE' => array( 121, 1, 1, 0 ),
2210 'FIND' => array( 124, -1, 1, 0 ),
2211 'CELL' => array( 125, -1, 0, 1 ),
2212 'ISERR' => array( 126, 1, 1, 0 ),
2213 'ISTEXT' => array( 127, 1, 1, 0 ),
2214 'ISNUMBER' => array( 128, 1, 1, 0 ),
2215 'ISBLANK' => array( 129, 1, 1, 0 ),
2216 'T' => array( 130, 1, 0, 0 ),
2217 'N' => array( 131, 1, 0, 0 ),
2218 'DATEVALUE' => array( 140, 1, 1, 0 ),
2219 'TIMEVALUE' => array( 141, 1, 1, 0 ),
2220 'SLN' => array( 142, 3, 1, 0 ),
2221 'SYD' => array( 143, 4, 1, 0 ),
2222 'DDB' => array( 144, -1, 1, 0 ),
2223 'INDIRECT' => array( 148, -1, 1, 1 ),
2224 'CALL' => array( 150, -1, 1, 0 ),
2225 'CLEAN' => array( 162, 1, 1, 0 ),
2226 'MDETERM' => array( 163, 1, 2, 0 ),
2227 'MINVERSE' => array( 164, 1, 2, 0 ),
2228 'MMULT' => array( 165, 2, 2, 0 ),
2229 'IPMT' => array( 167, -1, 1, 0 ),
2230 'PPMT' => array( 168, -1, 1, 0 ),
2231 'COUNTA' => array( 169, -1, 0, 0 ),
2232 'PRODUCT' => array( 183, -1, 0, 0 ),
2233 'FACT' => array( 184, 1, 1, 0 ),
2234 'DPRODUCT' => array( 189, 3, 0, 0 ),
2235 'ISNONTEXT' => array( 190, 1, 1, 0 ),
2236 'STDEVP' => array( 193, -1, 0, 0 ),
2237 'VARP' => array( 194, -1, 0, 0 ),
2238 'DSTDEVP' => array( 195, 3, 0, 0 ),
2239 'DVARP' => array( 196, 3, 0, 0 ),
2240 'TRUNC' => array( 197, -1, 1, 0 ),
2241 'ISLOGICAL' => array( 198, 1, 1, 0 ),
2242 'DCOUNTA' => array( 199, 3, 0, 0 ),
2243 'ROUNDUP' => array( 212, 2, 1, 0 ),
2244 'ROUNDDOWN' => array( 213, 2, 1, 0 ),
2245 'RANK' => array( 216, -1, 0, 0 ),
2246 'ADDRESS' => array( 219, -1, 1, 0 ),
2247 'DAYS360' => array( 220, -1, 1, 0 ),
2248 'TODAY' => array( 221, 0, 1, 1 ),
2249 'VDB' => array( 222, -1, 1, 0 ),
2250 'MEDIAN' => array( 227, -1, 0, 0 ),
2251 'SUMPRODUCT' => array( 228, -1, 2, 0 ),
2252 'SINH' => array( 229, 1, 1, 0 ),
2253 'COSH' => array( 230, 1, 1, 0 ),
2254 'TANH' => array( 231, 1, 1, 0 ),
2255 'ASINH' => array( 232, 1, 1, 0 ),
2256 'ACOSH' => array( 233, 1, 1, 0 ),
2257 'ATANH' => array( 234, 1, 1, 0 ),
2258 'DGET' => array( 235, 3, 0, 0 ),
2259 'INFO' => array( 244, 1, 1, 1 ),
2260 'DB' => array( 247, -1, 1, 0 ),
2261 'FREQUENCY' => array( 252, 2, 0, 0 ),
2262 'ERROR.TYPE' => array( 261, 1, 1, 0 ),
2263 'REGISTER.ID' => array( 267, -1, 1, 0 ),
2264 'AVEDEV' => array( 269, -1, 0, 0 ),
2265 'BETADIST' => array( 270, -1, 1, 0 ),
2266 'GAMMALN' => array( 271, 1, 1, 0 ),
2267 'BETAINV' => array( 272, -1, 1, 0 ),
2268 'BINOMDIST' => array( 273, 4, 1, 0 ),
2269 'CHIDIST' => array( 274, 2, 1, 0 ),
2270 'CHIINV' => array( 275, 2, 1, 0 ),
2271 'COMBIN' => array( 276, 2, 1, 0 ),
2272 'CONFIDENCE' => array( 277, 3, 1, 0 ),
2273 'CRITBINOM' => array( 278, 3, 1, 0 ),
2274 'EVEN' => array( 279, 1, 1, 0 ),
2275 'EXPONDIST' => array( 280, 3, 1, 0 ),
2276 'FDIST' => array( 281, 3, 1, 0 ),
2277 'FINV' => array( 282, 3, 1, 0 ),
2278 'FISHER' => array( 283, 1, 1, 0 ),
2279 'FISHERINV' => array( 284, 1, 1, 0 ),
2280 'FLOOR' => array( 285, 2, 1, 0 ),
2281 'GAMMADIST' => array( 286, 4, 1, 0 ),
2282 'GAMMAINV' => array( 287, 3, 1, 0 ),
2283 'CEILING' => array( 288, 2, 1, 0 ),
2284 'HYPGEOMDIST' => array( 289, 4, 1, 0 ),
2285 'LOGNORMDIST' => array( 290, 3, 1, 0 ),
2286 'LOGINV' => array( 291, 3, 1, 0 ),
2287 'NEGBINOMDIST' => array( 292, 3, 1, 0 ),
2288 'NORMDIST' => array( 293, 4, 1, 0 ),
2289 'NORMSDIST' => array( 294, 1, 1, 0 ),
2290 'NORMINV' => array( 295, 3, 1, 0 ),
2291 'NORMSINV' => array( 296, 1, 1, 0 ),
2292 'STANDARDIZE' => array( 297, 3, 1, 0 ),
2293 'ODD' => array( 298, 1, 1, 0 ),
2294 'PERMUT' => array( 299, 2, 1, 0 ),
2295 'POISSON' => array( 300, 3, 1, 0 ),
2296 'TDIST' => array( 301, 3, 1, 0 ),
2297 'WEIBULL' => array( 302, 4, 1, 0 ),
2298 'SUMXMY2' => array( 303, 2, 2, 0 ),
2299 'SUMX2MY2' => array( 304, 2, 2, 0 ),
2300 'SUMX2PY2' => array( 305, 2, 2, 0 ),
2301 'CHITEST' => array( 306, 2, 2, 0 ),
2302 'CORREL' => array( 307, 2, 2, 0 ),
2303 'COVAR' => array( 308, 2, 2, 0 ),
2304 'FORECAST' => array( 309, 3, 2, 0 ),
2305 'FTEST' => array( 310, 2, 2, 0 ),
2306 'INTERCEPT' => array( 311, 2, 2, 0 ),
2307 'PEARSON' => array( 312, 2, 2, 0 ),
2308 'RSQ' => array( 313, 2, 2, 0 ),
2309 'STEYX' => array( 314, 2, 2, 0 ),
2310 'SLOPE' => array( 315, 2, 2, 0 ),
2311 'TTEST' => array( 316, 4, 2, 0 ),
2312 'PROB' => array( 317, -1, 2, 0 ),
2313 'DEVSQ' => array( 318, -1, 0, 0 ),
2314 'GEOMEAN' => array( 319, -1, 0, 0 ),
2315 'HARMEAN' => array( 320, -1, 0, 0 ),
2316 'SUMSQ' => array( 321, -1, 0, 0 ),
2317 'KURT' => array( 322, -1, 0, 0 ),
2318 'SKEW' => array( 323, -1, 0, 0 ),
2319 'ZTEST' => array( 324, -1, 0, 0 ),
2320 'LARGE' => array( 325, 2, 0, 0 ),
2321 'SMALL' => array( 326, 2, 0, 0 ),
2322 'QUARTILE' => array( 327, 2, 0, 0 ),
2323 'PERCENTILE' => array( 328, 2, 0, 0 ),
2324 'PERCENTRANK' => array( 329, -1, 0, 0 ),
2325 'MODE' => array( 330, -1, 2, 0 ),
2326 'TRIMMEAN' => array( 331, 2, 0, 0 ),
2327 'TINV' => array( 332, 2, 1, 0 ),
2328 'CONCATENATE' => array( 336, -1, 1, 0 ),
2329 'POWER' => array( 337, 2, 1, 0 ),
2330 'RADIANS' => array( 342, 1, 1, 0 ),
2331 'DEGREES' => array( 343, 1, 1, 0 ),
2332 'SUBTOTAL' => array( 344, -1, 0, 0 ),
2333 'SUMIF' => array( 345, -1, 0, 0 ),
2334 'COUNTIF' => array( 346, 2, 0, 0 ),
2335 'COUNTBLANK' => array( 347, 1, 0, 0 ),
2336 'ROMAN' => array( 354, -1, 1, 0 )
2341 * Convert a token to the proper ptg value.
2344 * @param mixed $token The token to convert.
2345 * @return mixed the converted token on success. Die if the token
2348 function _convert($token)
2350 if (preg_match("/^\"[^\"]{0,255}\"$/", $token)) {
2351 return $this->_convertString($token);
2353 } elseif (is_numeric($token)) {
2354 return $this->_convertNumber($token);
2356 // match references like A1 or $A$1
2357 } elseif (preg_match('/^\$?([A-Ia-i]?[A-Za-z])\$?(\d+)$/',$token)) {
2358 return $this->_convertRef2d($token);
2360 // match external references like Sheet1!A1 or Sheet1:Sheet2!A1
2361 } elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z](\d+)$/u",$token)) {
2362 return $this->_convertRef3d($token);
2364 // match external references like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1
2365 } elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z](\d+)$/u",$token)) {
2366 return $this->_convertRef3d($token);
2368 // match ranges like A1:B2
2369 } elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\:(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token)) {
2370 return $this->_convertRange2d($token);
2372 // match ranges like A1..B2
2373 } elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token)) {
2374 return $this->_convertRange2d($token);
2376 // match external ranges like Sheet1!A1 or Sheet1:Sheet2!A1:B2
2377 } elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/u",$token)) {
2378 return $this->_convertRange3d($token);
2380 // match external ranges like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2
2381 } elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/u",$token)) {
2382 return $this->_convertRange3d($token);
2384 // operators (including parentheses)
2385 } elseif (isset($this->ptg[$token])) {
2386 return pack("C", $this->ptg[$token]);
2388 // commented so argument number can be processed correctly. See toReversePolish().
2389 /*elseif (preg_match("/[A-Z0-9\xc0-\xdc\.]+/",$token))
2391 return($this->_convertFunction($token,$this->_func_args));
2394 // if it's an argument, ignore the token (the argument remains)
2395 } elseif ($token == 'arg') {
2398 // TODO: use real error codes
2399 die("Unknown token $token");
2403 * Convert a number token to ptgInt or ptgNum
2406 * @param mixed $num an integer or double for conversion to its ptg value
2408 function _convertNumber($num)
2410 // Integer in the range 0..2**16-1
2411 if ((preg_match("/^\d+$/", $num)) and ($num <= 65535)) {
2412 return pack("Cv", $this->ptg['ptgInt'], $num);
2414 if ($this->_byte_order) { // if it's Big Endian
2415 $num = strrev($num);
2417 return pack("Cd", $this->ptg['ptgNum'], $num);
2422 * Convert a string token to ptgStr
2425 * @param string $string A string for conversion to its ptg value.
2426 * @return mixed the converted token on success. PEAR_Error if the string
2427 * is longer than 255 characters.
2429 function _convertString($string)
2431 // chop away beggining and ending quotes
2432 $string = substr($string, 1, strlen($string) - 2);
2433 if (strlen($string) > 255) {
2434 die("String is too long");
2437 if ($this->_BIFF_version == 0x0500) {
2438 return pack("CC", $this->ptg['ptgStr'], strlen($string)).$string;
2439 } elseif ($this->_BIFF_version == 0x0600) {
2440 $encoding = 0; // TODO: Unicode support
2441 return pack("CCC", $this->ptg['ptgStr'], strlen($string), $encoding).$string;
2446 * Convert a function to a ptgFunc or ptgFuncVarV depending on the number of
2447 * args that it takes.
2450 * @param string $token The name of the function for convertion to ptg value.
2451 * @param integer $num_args The number of arguments the function receives.
2452 * @return string The packed ptg for the function
2454 function _convertFunction($token, $num_args)
2456 $args = $this->_functions[$token][1];
2457 $volatile = $this->_functions[$token][3];
2459 // Fixed number of args eg. TIME($i,$j,$k).
2461 return pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]);
2463 // Variable number of args eg. SUM($i,$j,$k, ..).
2465 return pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]);
2470 * Convert an Excel range such as A1:D4 to a ptgRefV.
2473 * @param string $range An Excel range in the A1:A2 or A1..A2 format.
2475 function _convertRange2d($range)
2477 $class = 2; // as far as I know, this is magick.
2479 // Split the range into 2 cell refs
2480 if (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\:([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) {
2481 list($cell1, $cell2) = preg_split('/:/', $range);
2482 } elseif (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\.\.([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) {
2483 list($cell1, $cell2) = preg_split('/\.\./', $range);
2486 // TODO: use real error codes
2487 die("Unknown range separator");
2490 // Convert the cell references
2491 $cell_array1 = $this->_cellToPackedRowcol($cell1);
2492 list($row1, $col1) = $cell_array1;
2493 $cell_array2 = $this->_cellToPackedRowcol($cell2);
2494 list($row2, $col2) = $cell_array2;
2496 // The ptg value depends on the class of the ptg.
2498 $ptgArea = pack("C", $this->ptg['ptgArea']);
2499 } elseif ($class == 1) {
2500 $ptgArea = pack("C", $this->ptg['ptgAreaV']);
2501 } elseif ($class == 2) {
2502 $ptgArea = pack("C", $this->ptg['ptgAreaA']);
2504 // TODO: use real error codes
2505 die("Unknown class $class");
2507 return $ptgArea . $row1 . $row2 . $col1. $col2;
2511 * Convert an Excel 3d range such as "Sheet1!A1:D4" or "Sheet1:Sheet2!A1:D4" to
2515 * @param string $token An Excel range in the Sheet1!A1:A2 format.
2516 * @return mixed The packed ptgArea3d token on success, PEAR_Error on failure.
2518 function _convertRange3d($token)
2520 $class = 2; // as far as I know, this is magick.
2522 // Split the ref at the ! symbol
2523 list($ext_ref, $range) = preg_split('/!/', $token);
2525 // Convert the external reference part (different for BIFF8)
2526 if ($this->_BIFF_version == 0x0500) {
2527 $ext_ref = $this->_packExtRef($ext_ref);
2528 } elseif ($this->_BIFF_version == 0x0600) {
2529 $ext_ref = $this->_getRefIndex($ext_ref);
2532 // Split the range into 2 cell refs
2533 list($cell1, $cell2) = preg_split('/:/', $range);
2535 // Convert the cell references
2536 if (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/", $cell1)) {
2537 $cell_array1 = $this->_cellToPackedRowcol($cell1);
2538 list($row1, $col1) = $cell_array1;
2539 $cell_array2 = $this->_cellToPackedRowcol($cell2);
2540 list($row2, $col2) = $cell_array2;
2541 } else { // It's a rows range (like 26:27)
2542 $cells_array = $this->_rangeToPackedRange($cell1.':'.$cell2);
2543 list($row1, $col1, $row2, $col2) = $cells_array;
2546 // The ptg value depends on the class of the ptg.
2548 $ptgArea = pack("C", $this->ptg['ptgArea3d']);
2549 } elseif ($class == 1) {
2550 $ptgArea = pack("C", $this->ptg['ptgArea3dV']);
2551 } elseif ($class == 2) {
2552 $ptgArea = pack("C", $this->ptg['ptgArea3dA']);
2554 die("Unknown class $class");
2557 return $ptgArea . $ext_ref . $row1 . $row2 . $col1. $col2;
2561 * Convert an Excel reference such as A1, $B2, C$3 or $D$4 to a ptgRefV.
2564 * @param string $cell An Excel cell reference
2565 * @return string The cell in packed() format with the corresponding ptg
2567 function _convertRef2d($cell)
2569 $class = 2; // as far as I know, this is magick.
2571 // Convert the cell reference
2572 $cell_array = $this->_cellToPackedRowcol($cell);
2573 list($row, $col) = $cell_array;
2575 // The ptg value depends on the class of the ptg.
2577 $ptgRef = pack("C", $this->ptg['ptgRef']);
2578 } elseif ($class == 1) {
2579 $ptgRef = pack("C", $this->ptg['ptgRefV']);
2580 } elseif ($class == 2) {
2581 $ptgRef = pack("C", $this->ptg['ptgRefA']);
2583 // TODO: use real error codes
2584 die("Unknown class $class");
2586 return $ptgRef.$row.$col;
2590 * Convert an Excel 3d reference such as "Sheet1!A1" or "Sheet1:Sheet2!A1" to a
2594 * @param string $cell An Excel cell reference
2595 * @return mixed The packed ptgRef3d token on success, PEAR_Error on failure.
2597 function _convertRef3d($cell)
2599 $class = 2; // as far as I know, this is magick.
2601 // Split the ref at the ! symbol
2602 list($ext_ref, $cell) = preg_split('/!/', $cell);
2604 // Convert the external reference part (different for BIFF8)
2605 if ($this->_BIFF_version == 0x0500) {
2606 $ext_ref = $this->_packExtRef($ext_ref);
2607 } elseif ($this->_BIFF_version == 0x0600) {
2608 $ext_ref = $this->_getRefIndex($ext_ref);
2611 // Convert the cell reference part
2612 list($row, $col) = $this->_cellToPackedRowcol($cell);
2614 // The ptg value depends on the class of the ptg.
2616 $ptgRef = pack("C", $this->ptg['ptgRef3d']);
2617 } elseif ($class == 1) {
2618 $ptgRef = pack("C", $this->ptg['ptgRef3dV']);
2619 } elseif ($class == 2) {
2620 $ptgRef = pack("C", $this->ptg['ptgRef3dA']);
2622 die("Unknown class $class");
2625 return $ptgRef . $ext_ref. $row . $col;
2629 * Convert the sheet name part of an external reference, for example "Sheet1" or
2630 * "Sheet1:Sheet2", to a packed structure.
2633 * @param string $ext_ref The name of the external reference
2634 * @return string The reference index in packed() format
2636 function _packExtRef($ext_ref)
2638 $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any.
2639 $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
2641 // Check if there is a sheet range eg., Sheet1:Sheet2.
2642 if (preg_match("/:/", $ext_ref)) {
2643 list($sheet_name1, $sheet_name2) = preg_split('/:/', $ext_ref);
2645 $sheet1 = $this->_getSheetIndex($sheet_name1);
2646 if ($sheet1 == -1) {
2647 die("Unknown sheet name $sheet_name1 in formula");
2649 $sheet2 = $this->_getSheetIndex($sheet_name2);
2650 if ($sheet2 == -1) {
2651 die("Unknown sheet name $sheet_name2 in formula");
2654 // Reverse max and min sheet numbers if necessary
2655 if ($sheet1 > $sheet2) {
2656 list($sheet1, $sheet2) = array($sheet2, $sheet1);
2658 } else { // Single sheet name only.
2659 $sheet1 = $this->_getSheetIndex($ext_ref);
2660 if ($sheet1 == -1) {
2661 die("Unknown sheet name $ext_ref in formula");
2666 // References are stored relative to 0xFFFF.
2667 $offset = -1 - $sheet1;
2669 return pack('vdvv', $offset, 0x00, $sheet1, $sheet2);
2673 * Look up the REF index that corresponds to an external sheet name
2674 * (or range). If it doesn't exist yet add it to the workbook's references
2675 * array. It assumes all sheet names given must exist.
2678 * @param string $ext_ref The name of the external reference
2679 * @return mixed The reference index in packed() format on success,
2680 * PEAR_Error on failure
2682 function _getRefIndex($ext_ref)
2684 $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any.
2685 $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
2687 // Check if there is a sheet range eg., Sheet1:Sheet2.
2688 if (preg_match("/:/", $ext_ref)) {
2689 list($sheet_name1, $sheet_name2) = preg_split('/:/', $ext_ref);
2691 $sheet1 = $this->_getSheetIndex($sheet_name1);
2692 if ($sheet1 == -1) {
2693 die("Unknown sheet name $sheet_name1 in formula");
2695 $sheet2 = $this->_getSheetIndex($sheet_name2);
2696 if ($sheet2 == -1) {
2697 die("Unknown sheet name $sheet_name2 in formula");
2700 // Reverse max and min sheet numbers if necessary
2701 if ($sheet1 > $sheet2) {
2702 list($sheet1, $sheet2) = array($sheet2, $sheet1);
2704 } else { // Single sheet name only.
2705 $sheet1 = $this->_getSheetIndex($ext_ref);
2706 if ($sheet1 == -1) {
2707 die("Unknown sheet name $ext_ref in formula");
2712 // assume all references belong to this document
2713 $supbook_index = 0x00;
2714 $ref = pack('vvv', $supbook_index, $sheet1, $sheet2);
2715 $total_references = count($this->_references);
2717 for ($i = 0; $i < $total_references; $i++) {
2718 if ($ref == $this->_references[$i]) {
2723 // if REF was not found add it to references array
2725 $this->_references[$total_references] = $ref;
2726 $index = $total_references;
2729 return pack('v', $index);
2733 * Look up the index that corresponds to an external sheet name. The hash of
2734 * sheet names is updated by the addworksheet() method of the
2735 * Spreadsheet_Excel_Writer_Workbook class.
2738 * @return integer The sheet index, -1 if the sheet was not found
2740 function _getSheetIndex($sheet_name)
2742 if (!isset($this->_ext_sheets[$sheet_name])) {
2745 return $this->_ext_sheets[$sheet_name];
2750 * This method is used to update the array of sheet names. It is
2751 * called by the addWorksheet() method of the
2752 * Spreadsheet_Excel_Writer_Workbook class.
2755 * @see Spreadsheet_Excel_Writer_Workbook::addWorksheet()
2756 * @param string $name The name of the worksheet being added
2757 * @param integer $index The index of the worksheet being added
2759 function setExtSheet($name, $index)
2761 $this->_ext_sheets[$name] = $index;
2765 * pack() row and column into the required 3 or 4 byte format.
2768 * @param string $cell The Excel cell reference to be packed
2769 * @return array Array containing the row and column in packed() format
2771 function _cellToPackedRowcol($cell)
2773 $cell = strtoupper($cell);
2774 list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
2776 die("Column in: $cell greater than 255");
2778 // FIXME: change for BIFF8
2779 if ($row >= 16384) {
2780 die("Row in: $cell greater than 16384 ");
2783 // Set the high bits to indicate if row or col are relative.
2784 if ($this->_BIFF_version == 0x0500) {
2785 $row |= $col_rel << 14;
2786 $row |= $row_rel << 15;
2787 $col = pack('C', $col);
2788 } elseif ($this->_BIFF_version == 0x0600) {
2789 $col |= $col_rel << 14;
2790 $col |= $row_rel << 15;
2791 $col = pack('v', $col);
2793 $row = pack('v', $row);
2795 return array($row, $col);
2799 * pack() row range into the required 3 or 4 byte format.
2800 * Just using maximum col/rows, which is probably not the correct solution
2803 * @param string $range The Excel range to be packed
2804 * @return array Array containing (row1,col1,row2,col2) in packed() format
2806 function _rangeToPackedRange($range)
2808 preg_match('/(\$)?(\d+)\:(\$)?(\d+)/', $range, $match);
2809 // return absolute rows if there is a $ in the ref
2810 $row1_rel = empty($match[1]) ? 1 : 0;
2812 $row2_rel = empty($match[3]) ? 1 : 0;
2814 // Convert 1-index to zero-index
2817 // Trick poor inocent Excel
2819 $col2 = 16383; // FIXME: maximum possible value for Excel 5 (change this!!!)
2821 // FIXME: this changes for BIFF8
2822 if (($row1 >= 16384) or ($row2 >= 16384)) {
2823 die("Row in: $range greater than 16384 ");
2826 // Set the high bits to indicate if rows are relative.
2827 if ($this->_BIFF_version == 0x0500) {
2828 $row1 |= $row1_rel << 14; // FIXME: probably a bug
2829 $row2 |= $row2_rel << 15;
2830 $col1 = pack('C', $col1);
2831 $col2 = pack('C', $col2);
2832 } elseif ($this->_BIFF_version == 0x0600) {
2833 $col1 |= $row1_rel << 15;
2834 $col2 |= $row2_rel << 15;
2835 $col1 = pack('v', $col1);
2836 $col2 = pack('v', $col2);
2838 $row1 = pack('v', $row1);
2839 $row2 = pack('v', $row2);
2841 return array($row1, $col1, $row2, $col2);
2845 * Convert an Excel cell reference such as A1 or $B2 or C$3 or $D$4 to a zero
2846 * indexed row and column number. Also returns two (0,1) values to indicate
2847 * whether the row or column are relative references.
2850 * @param string $cell The Excel cell reference in A1 format.
2853 function _cellToRowcol($cell)
2855 preg_match('/(\$)?([A-I]?[A-Z])(\$)?(\d+)/',$cell,$match);
2856 // return absolute column if there is a $ in the ref
2857 $col_rel = empty($match[1]) ? 1 : 0;
2858 $col_ref = $match[2];
2859 $row_rel = empty($match[3]) ? 1 : 0;
2862 // Convert base26 column string to a number.
2863 $expn = strlen($col_ref) - 1;
2865 $col_ref_length = strlen($col_ref);
2866 for ($i = 0; $i < $col_ref_length; $i++) {
2867 $col += (ord($col_ref{$i}) - ord('A') + 1) * pow(26, $expn);
2871 // Convert 1-index to zero-index
2875 return array($row, $col, $row_rel, $col_rel);
2879 * Advance to the next valid token.
2885 $i = $this->_current_char;
2886 $formula_length = strlen($this->_formula);
2887 // eat up white spaces
2888 if ($i < $formula_length) {
2889 while ($this->_formula{$i} == " ") {
2893 if ($i < ($formula_length - 1)) {
2894 $this->_lookahead = $this->_formula{$i+1};
2899 while ($i < $formula_length) {
2900 $token .= $this->_formula{$i};
2901 if ($i < ($formula_length - 1)) {
2902 $this->_lookahead = $this->_formula{$i+1};
2904 $this->_lookahead = '';
2907 if ($this->_match($token) != '') {
2908 //if ($i < strlen($this->_formula) - 1) {
2909 // $this->_lookahead = $this->_formula{$i+1};
2911 $this->_current_char = $i + 1;
2912 $this->_current_token = $token;
2916 if ($i < ($formula_length - 2)) {
2917 $this->_lookahead = $this->_formula{$i+2};
2918 } else { // if we run out of characters _lookahead becomes empty
2919 $this->_lookahead = '';
2923 //die("Lexical error ".$this->_current_char);
2927 * Checks if it's a valid token.
2930 * @param mixed $token The token to check.
2931 * @return mixed The checked token or false on failure
2933 function _match($token)
2936 case SPREADSHEET_EXCEL_WRITER_ADD:
2939 case SPREADSHEET_EXCEL_WRITER_SUB:
2942 case SPREADSHEET_EXCEL_WRITER_MUL:
2945 case SPREADSHEET_EXCEL_WRITER_DIV:
2948 case SPREADSHEET_EXCEL_WRITER_OPEN:
2951 case SPREADSHEET_EXCEL_WRITER_CLOSE:
2954 case SPREADSHEET_EXCEL_WRITER_COMA:
2957 case SPREADSHEET_EXCEL_WRITER_SEMICOLON:
2960 case SPREADSHEET_EXCEL_WRITER_GT:
2961 if ($this->_lookahead == '=') { // it's a GE token
2966 case SPREADSHEET_EXCEL_WRITER_LT:
2967 // it's a LE or a NE token
2968 if (($this->_lookahead == '=') or ($this->_lookahead == '>')) {
2973 case SPREADSHEET_EXCEL_WRITER_GE:
2976 case SPREADSHEET_EXCEL_WRITER_LE:
2979 case SPREADSHEET_EXCEL_WRITER_EQ:
2982 case SPREADSHEET_EXCEL_WRITER_NE:
2986 // if it's a reference
2987 if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$token) and
2988 !preg_match("/[0-9]/",$this->_lookahead) and
2989 ($this->_lookahead != ':') and ($this->_lookahead != '.') and
2990 ($this->_lookahead != '!'))
2994 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
2995 elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/u",$token) and
2996 !preg_match("/[0-9]/",$this->_lookahead) and
2997 ($this->_lookahead != ':') and ($this->_lookahead != '.'))
3001 // If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1)
3002 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z][0-9]+$/u",$token) and
3003 !preg_match("/[0-9]/",$this->_lookahead) and
3004 ($this->_lookahead != ':') and ($this->_lookahead != '.'))
3008 // if it's a range (A1:A2)
3009 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and
3010 !preg_match("/[0-9]/",$this->_lookahead))
3014 // if it's a range (A1..A2)
3015 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and
3016 !preg_match("/[0-9]/",$this->_lookahead))
3020 // If it's an external range like Sheet1!A1 or Sheet1:Sheet2!A1:B2
3021 elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$token) and
3022 !preg_match("/[0-9]/",$this->_lookahead))
3026 // If it's an external range like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2
3027 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$token) and
3028 !preg_match("/[0-9]/",$this->_lookahead))
3032 // If it's a number (check that it's not a sheet name or range)
3033 elseif (is_numeric($token) and
3034 (!is_numeric($token.$this->_lookahead) or ($this->_lookahead == '')) and
3035 ($this->_lookahead != '!') and ($this->_lookahead != ':'))
3039 // If it's a string (of maximum 255 characters)
3040 elseif (preg_match("/^\"[^\"]{0,255}\"$/",$token))
3044 // if it's a function call
3045 elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$token) and ($this->_lookahead == "("))
3054 * The parsing method. It parses a formula.
3057 * @param string $formula The formula to parse, without the initial equal
3059 * @return mixed true on success, PEAR_Error on failure
3061 function parse($formula)
3063 $this->_current_char = 0;
3064 $this->_formula = $formula;
3065 $this->_lookahead = $formula{1};
3067 $this->_parse_tree = $this->_condition();
3071 * It parses a condition. It assumes the following rule:
3072 * Cond -> Expr [(">" | "<") Expr]
3075 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3077 function _condition()
3079 $result = $this->_expression();
3080 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LT) {
3082 $result2 = $this->_expression();
3083 $result = $this->_createTree('ptgLT', $result, $result2);
3084 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GT) {
3086 $result2 = $this->_expression();
3087 $result = $this->_createTree('ptgGT', $result, $result2);
3088 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LE) {
3090 $result2 = $this->_expression();
3091 $result = $this->_createTree('ptgLE', $result, $result2);
3092 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GE) {
3094 $result2 = $this->_expression();
3095 $result = $this->_createTree('ptgGE', $result, $result2);
3096 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_EQ) {
3098 $result2 = $this->_expression();
3099 $result = $this->_createTree('ptgEQ', $result, $result2);
3100 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_NE) {
3102 $result2 = $this->_expression();
3103 $result = $this->_createTree('ptgNE', $result, $result2);
3109 * It parses a expression. It assumes the following rule:
3110 * Expr -> Term [("+" | "-") Term]
3115 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3117 function _expression()
3119 // If it's a string return a string node
3120 if (preg_match("/^\"[^\"]{0,255}\"$/", $this->_current_token)) {
3121 $result = $this->_createTree($this->_current_token, '', '');
3124 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB) {
3127 $result2 = $this->_expression();
3128 $result = $this->_createTree('ptgUminus', $result2, '');
3131 $result = $this->_term();
3132 while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) or
3133 ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB)) {
3135 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) {
3137 $result2 = $this->_term();
3138 $result = $this->_createTree('ptgAdd', $result, $result2);
3141 $result2 = $this->_term();
3142 $result = $this->_createTree('ptgSub', $result, $result2);
3149 * This function just introduces a ptgParen element in the tree, so that Excel
3150 * doesn't get confused when working with a parenthesized formula afterwards.
3154 * @return array The parsed ptg'd tree
3156 function _parenthesizedExpression()
3158 $result = $this->_createTree('ptgParen', $this->_expression(), '');
3163 * It parses a term. It assumes the following rule:
3164 * Term -> Fact [("*" | "/") Fact]
3167 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3171 $result = $this->_fact();
3172 while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) or
3173 ($this->_current_token == SPREADSHEET_EXCEL_WRITER_DIV)) {
3175 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) {
3177 $result2 = $this->_fact();
3178 $result = $this->_createTree('ptgMul', $result, $result2);
3181 $result2 = $this->_fact();
3182 $result = $this->_createTree('ptgDiv', $result, $result2);
3189 * It parses a factor. It assumes the following rule:
3197 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3201 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_OPEN) {
3202 $this->_advance(); // eat the "("
3203 $result = $this->_parenthesizedExpression();
3204 if ($this->_current_token != SPREADSHEET_EXCEL_WRITER_CLOSE) {
3205 die("')' token expected.");
3207 $this->_advance(); // eat the ")"
3210 // if it's a reference
3211 if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$this->_current_token))
3213 $result = $this->_createTree($this->_current_token, '', '');
3217 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
3218 elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/u",$this->_current_token))
3220 $result = $this->_createTree($this->_current_token, '', '');
3224 // If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1)
3225 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z][0-9]+$/u",$this->_current_token))
3227 $result = $this->_createTree($this->_current_token, '', '');
3232 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token) or
3233 preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token))
3235 $result = $this->_current_token;
3239 // If it's an external range (Sheet1!A1 or Sheet1!A1:B2)
3240 elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$this->_current_token))
3242 $result = $this->_current_token;
3246 // If it's an external range ('Sheet1'!A1 or 'Sheet1'!A1:B2)
3247 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$this->_current_token))
3249 $result = $this->_current_token;
3253 elseif (is_numeric($this->_current_token))
3255 $result = $this->_createTree($this->_current_token, '', '');
3259 // if it's a function call
3260 elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$this->_current_token))
3262 $result = $this->_func();
3265 die("Syntax error: ".$this->_current_token.
3266 ", lookahead: ".$this->_lookahead.
3267 ", current char: ".$this->_current_char);
3271 * It parses a function call. It assumes the following rule:
3272 * Func -> ( Expr [,Expr]* )
3275 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3279 $num_args = 0; // number of arguments received
3280 $function = strtoupper($this->_current_token);
3281 $result = ''; // initialize result
3283 $this->_advance(); // eat the "("
3284 while ($this->_current_token != ')') {
3286 if ($num_args > 0) {
3287 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_COMA or
3288 $this->_current_token == SPREADSHEET_EXCEL_WRITER_SEMICOLON)
3290 $this->_advance(); // eat the "," or ";"
3292 die("Syntax error: comma expected in ".
3293 "function $function, arg #{$num_args}");
3295 $result2 = $this->_condition();
3296 $result = $this->_createTree('arg', $result, $result2);
3297 } else { // first argument
3298 $result2 = $this->_condition();
3299 $result = $this->_createTree('arg', '', $result2);
3303 if (!isset($this->_functions[$function])) {
3304 die("Function $function() doesn't exist");
3306 $args = $this->_functions[$function][1];
3307 // If fixed number of args eg. TIME($i,$j,$k). Check that the number of args is valid.
3308 if (($args >= 0) and ($args != $num_args)) {
3309 die("Incorrect number of arguments in function $function() ");
3312 $result = $this->_createTree($function, $result, $num_args);
3313 $this->_advance(); // eat the ")"
3318 * Creates a tree. In fact an array which may have one or two arrays (sub-trees)
3322 * @param mixed $value The value of this node.
3323 * @param mixed $left The left array (sub-tree) or a final node.
3324 * @param mixed $right The right array (sub-tree) or a final node.
3325 * @return array A tree
3327 function _createTree($value, $left, $right)
3329 return array('value' => $value, 'left' => $left, 'right' => $right);
3333 * Builds a string containing the tree in reverse polish notation (What you
3334 * would use in a HP calculator stack).
3335 * The following tree:
3343 * The following tree:
3351 * produces: "36A1*+"
3353 * In fact all operands, functions, references, etc... are written as ptg's
3356 * @param array $tree The optional tree to convert.
3357 * @return string The tree in reverse polish notation
3359 function toReversePolish($tree = array())
3361 $polish = ""; // the string we are going to return
3362 if (empty($tree)) { // If it's the first call use _parse_tree
3363 $tree = $this->_parse_tree;
3365 if (is_array($tree['left'])) {
3366 $converted_tree = $this->toReversePolish($tree['left']);
3367 $polish .= $converted_tree;
3368 } elseif ($tree['left'] != '') { // It's a final node
3369 $converted_tree = $this->_convert($tree['left']);
3370 $polish .= $converted_tree;
3372 if (is_array($tree['right'])) {
3373 $converted_tree = $this->toReversePolish($tree['right']);
3374 $polish .= $converted_tree;
3375 } elseif ($tree['right'] != '') { // It's a final node
3376 $converted_tree = $this->_convert($tree['right']);
3377 $polish .= $converted_tree;
3379 // if it's a function convert it here (so we can set it's arguments)
3380 if (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/",$tree['value']) and
3381 !preg_match('/^([A-Ia-i]?[A-Za-z])(\d+)$/',$tree['value']) and
3382 !preg_match("/^[A-Ia-i]?[A-Za-z](\d+)\.\.[A-Ia-i]?[A-Za-z](\d+)$/",$tree['value']) and
3383 !is_numeric($tree['value']) and
3384 !isset($this->ptg[$tree['value']]))
3386 // left subtree for a function is always an array.
3387 if ($tree['left'] != '') {
3388 $left_tree = $this->toReversePolish($tree['left']);
3392 // add it's left subtree and return.
3393 return $left_tree.$this->_convertFunction($tree['value'], $tree['right']);
3395 $converted_tree = $this->_convert($tree['value']);
3397 $polish .= $converted_tree;
3403 * Class for generating Excel Spreadsheets
3405 * @author Xavier Noguer <xnoguer@rezebra.com>
3406 * @category FileFormats
3407 * @package Spreadsheet_Excel_Writer
3410 class Spreadsheet_Excel_Writer_Worksheet extends Spreadsheet_Excel_Writer_BIFFwriter
3413 * Name of the Worksheet
3419 * Index for the Worksheet
3425 * Reference to the (default) Format object for URLs
3426 * @var object Format
3431 * Reference to the parser used for parsing formulas
3432 * @var object Format
3437 * Filehandle to the temporary file for storing data
3443 * Boolean indicating if we are using a temporary file for storing data
3446 var $_using_tmpfile;
3449 * Maximum number of rows for an Excel spreadsheet (BIFF5)
3455 * Maximum number of columns for an Excel spreadsheet (BIFF5)
3461 * Maximum number of characters for a string (LABEL record in BIFF5)
3467 * First row for the DIMENSIONS record
3469 * @see _storeDimensions()
3474 * Last row for the DIMENSIONS record
3476 * @see _storeDimensions()
3481 * First column for the DIMENSIONS record
3483 * @see _storeDimensions()
3488 * Last column for the DIMENSIONS record
3490 * @see _storeDimensions()
3495 * Array containing format information for columns
3501 * Array containing the selected area for the worksheet
3507 * Array containing the panes for the worksheet
3513 * The active pane for the worksheet
3519 * Bit specifying if panes are frozen
3525 * Bit specifying if the worksheet is selected
3531 * The paper size (for printing) (DOCUMENT!!!)
3537 * Bit specifying paper orientation (for printing). 0 => landscape, 1 => portrait
3543 * The page header caption
3549 * The page footer caption
3555 * The horizontal centering value for the page
3561 * The vertical centering value for the page
3567 * The margin for the header
3573 * The margin for the footer
3579 * The left margin for the worksheet in inches
3585 * The right margin for the worksheet in inches
3591 * The top margin for the worksheet in inches
3597 * The bottom margin for the worksheet in inches
3600 var $_margin_bottom;
3603 * First row to reapeat on each printed page
3609 * Last row to reapeat on each printed page
3615 * First column to reapeat on each printed page
3621 * First row of the area to print
3627 * Last row to of the area to print
3633 * First column of the area to print
3639 * Last column of the area to print
3645 * Whether to use outline.
3651 * Auto outline styles.
3654 var $_outline_style;
3657 * Whether to have outline summary below.
3660 var $_outline_below;
3663 * Whether to have outline summary at the right.
3666 var $_outline_right;
3669 * Outline row level.
3672 var $_outline_row_level;
3675 * Whether to fit to page when printing or not.
3681 * Number of pages to fit wide
3687 * Number of pages to fit high
3693 * Reference to the total number of strings in the workbook
3699 * Reference to the number of unique strings in the workbook
3705 * Reference to the array containing all the unique strings in the workbook
3711 * Merged cell ranges
3714 var $_merged_ranges;
3717 * Charset encoding currently used when calling writeString()
3720 var $_input_encoding;
3725 * @param string $name The name of the new worksheet
3726 * @param integer $index The index of the new worksheet
3727 * @param mixed &$activesheet The current activesheet of the workbook we belong to
3728 * @param mixed &$firstsheet The first worksheet in the workbook we belong to
3729 * @param mixed &$url_format The default format for hyperlinks
3730 * @param mixed &$parser The formula parser created for the Workbook
3733 function Spreadsheet_Excel_Writer_Worksheet($BIFF_version, $name,
3734 $index, &$activesheet,
3735 &$firstsheet, &$str_total,
3736 &$str_unique, &$str_table,
3737 &$url_format, &$parser)
3739 // It needs to call its parent's constructor explicitly
3740 $this->Spreadsheet_Excel_Writer_BIFFwriter();
3741 $this->_BIFF_version = $BIFF_version;
3742 $rowmax = 65536; // 16384 in Excel 5
3745 $this->name = $name;
3746 $this->index = $index;
3747 $this->activesheet = &$activesheet;
3748 $this->firstsheet = &$firstsheet;
3749 $this->_str_total = &$str_total;
3750 $this->_str_unique = &$str_unique;
3751 $this->_str_table = &$str_table;
3752 $this->_url_format = &$url_format;
3753 $this->_parser = &$parser;
3755 //$this->ext_sheets = array();
3756 $this->_filehandle = '';
3757 $this->_using_tmpfile = true;
3758 //$this->fileclosed = 0;
3759 //$this->offset = 0;
3760 $this->_xls_rowmax = $rowmax;
3761 $this->_xls_colmax = $colmax;
3762 $this->_xls_strmax = 255;
3763 $this->_dim_rowmin = $rowmax + 1;
3764 $this->_dim_rowmax = 0;
3765 $this->_dim_colmin = $colmax + 1;
3766 $this->_dim_colmax = 0;
3767 $this->_colinfo = array();
3768 $this->_selection = array(0,0,0,0);
3769 $this->_panes = array();
3770 $this->_active_pane = 3;
3772 $this->selected = 0;
3774 $this->_paper_size = 0x0;
3775 $this->_orientation = 0x1;
3776 $this->_header = '';
3777 $this->_footer = '';
3778 $this->_hcenter = 0;
3779 $this->_vcenter = 0;
3780 $this->_margin_head = 0.50;
3781 $this->_margin_foot = 0.50;
3782 $this->_margin_left = 0.75;
3783 $this->_margin_right = 0.75;
3784 $this->_margin_top = 1.00;
3785 $this->_margin_bottom = 1.00;
3787 $this->title_rowmin = null;
3788 $this->title_rowmax = null;
3789 $this->title_colmin = null;
3790 $this->title_colmax = null;
3791 $this->print_rowmin = null;
3792 $this->print_rowmax = null;
3793 $this->print_colmin = null;
3794 $this->print_colmax = null;
3796 $this->_print_gridlines = 1;
3797 $this->_screen_gridlines = 1;
3798 $this->_print_headers = 0;
3800 $this->_fit_page = 0;
3801 $this->_fit_width = 0;
3802 $this->_fit_height = 0;
3804 $this->_hbreaks = array();
3805 $this->_vbreaks = array();
3807 $this->_protect = 0;
3808 $this->_password = null;
3810 $this->col_sizes = array();
3811 $this->_row_sizes = array();
3814 $this->_print_scale = 100;
3816 $this->_outline_row_level = 0;
3817 $this->_outline_style = 0;
3818 $this->_outline_below = 1;
3819 $this->_outline_right = 1;
3820 $this->_outline_on = 1;
3822 $this->_merged_ranges = array();
3824 $this->_rtl = 0; // Added by Joe Hunt 2009-03-05 for arabic languages
3825 $this->_input_encoding = '';
3827 $this->_dv = array();
3829 $this->_initialize();
3833 * Open a tmp file to store the majority of the Worksheet data. If this fails,
3834 * for example due to write permissions, store the data in memory. This can be
3835 * slow for large files.
3839 function _initialize()
3841 // Open tmp file for storing Worksheet data
3845 $this->_filehandle = $fh;
3847 // If tmpfile() fails store data in memory
3848 $this->_using_tmpfile = false;
3853 * Add data to the beginning of the workbook (note the reverse order)
3854 * and to the end of the workbook.
3857 * @see Spreadsheet_Excel_Writer_Workbook::storeWorkbook()
3858 * @param array $sheetnames The array of sheetnames from the Workbook this
3859 * worksheet belongs to
3861 function close($sheetnames)
3863 $num_sheets = count($sheetnames);
3865 /***********************************************
3866 * Prepend in reverse order!!
3869 // Prepend the sheet dimensions
3870 $this->_storeDimensions();
3872 // Prepend the sheet password
3873 $this->_storePassword();
3875 // Prepend the sheet protection
3876 $this->_storeProtect();
3878 // Prepend the page setup
3879 $this->_storeSetup();
3881 /* FIXME: margins are actually appended */
3882 // Prepend the bottom margin
3883 $this->_storeMarginBottom();
3885 // Prepend the top margin
3886 $this->_storeMarginTop();
3888 // Prepend the right margin
3889 $this->_storeMarginRight();
3891 // Prepend the left margin
3892 $this->_storeMarginLeft();
3894 // Prepend the page vertical centering
3895 $this->_storeVcenter();
3897 // Prepend the page horizontal centering
3898 $this->_storeHcenter();
3900 // Prepend the page footer
3901 $this->_storeFooter();
3903 // Prepend the page header
3904 $this->_storeHeader();
3906 // Prepend the vertical page breaks
3907 $this->_storeVbreak();
3909 // Prepend the horizontal page breaks
3910 $this->_storeHbreak();
3913 $this->_storeWsbool();
3916 $this->_storeGridset();
3919 if ($this->_BIFF_version == 0x0500) {
3920 $this->_storeGuts();
3923 // Prepend PRINTGRIDLINES
3924 $this->_storePrintGridlines();
3926 // Prepend PRINTHEADERS
3927 $this->_storePrintHeaders();
3929 // Prepend EXTERNSHEET references
3930 if ($this->_BIFF_version == 0x0500) {
3931 for ($i = $num_sheets; $i > 0; $i--) {
3932 $sheetname = $sheetnames[$i-1];
3933 $this->_storeExternsheet($sheetname);
3937 // Prepend the EXTERNCOUNT of external references.
3938 if ($this->_BIFF_version == 0x0500) {
3939 $this->_storeExterncount($num_sheets);
3942 // Prepend the COLINFO records if they exist
3943 if (!empty($this->_colinfo)) {
3944 $colcount = count($this->_colinfo);
3945 for ($i = 0; $i < $colcount; $i++) {
3946 $this->_storeColinfo($this->_colinfo[$i]);
3948 $this->_storeDefcol();
3951 // Prepend the BOF record
3952 $this->_storeBof(0x0010);
3955 * End of prepend. Read upwards from here.
3956 ***********************************************/
3959 $this->_storeWindow2();
3960 $this->_storeZoom();
3961 if (!empty($this->_panes)) {
3962 $this->_storePanes($this->_panes);
3964 $this->_storeSelection($this->_selection);
3965 $this->_storeMergedCells();
3966 /* TODO: add data validity */
3967 /*if ($this->_BIFF_version == 0x0600) {
3968 $this->_storeDataValidity();
3974 * Retrieve the worksheet name.
3975 * This is usefull when creating worksheets without a name.
3978 * @return string The worksheet's name
3986 * Retrieves data from memory in one chunk, or from disk in $buffer
3989 * @return string The data
3995 // Return data stored in memory
3996 if (isset($this->_data)) {
3997 $tmp = $this->_data;
3998 unset($this->_data);
3999 $fh = $this->_filehandle;
4000 if ($this->_using_tmpfile) {
4005 // Return data stored on disk
4006 if ($this->_using_tmpfile) {
4007 if ($tmp = fread($this->_filehandle, $buffer)) {
4012 // No data to return
4017 * Sets a merged cell range
4020 * @param integer $first_row First row of the area to merge
4021 * @param integer $first_col First column of the area to merge
4022 * @param integer $last_row Last row of the area to merge
4023 * @param integer $last_col Last column of the area to merge
4025 function setMerge($first_row, $first_col, $last_row, $last_col)
4027 if (($last_row < $first_row) || ($last_col < $first_col)) {
4030 // don't check rowmin, rowmax, etc... because we don't know when this
4031 // is going to be called
4032 $this->_merged_ranges[] = array($first_row, $first_col, $last_row, $last_col);
4036 * Set this worksheet as a selected worksheet,
4037 * i.e. the worksheet has its tab highlighted.
4043 $this->selected = 1;
4047 * Set this worksheet as the active worksheet,
4048 * i.e. the worksheet that is displayed when the workbook is opened.
4049 * Also set it as selected.
4055 $this->selected = 1;
4056 $this->activesheet = $this->index;
4060 * Set this worksheet as the first visible sheet.
4061 * This is necessary when there are a large number of worksheets and the
4062 * activated worksheet is not visible on the screen.
4066 function setFirstSheet()
4068 $this->firstsheet = $this->index;
4072 * Set the worksheet protection flag
4073 * to prevent accidental modification and to
4074 * hide formulas if the locked and hidden format properties have been set.
4077 * @param string $password The password to use for protecting the sheet.
4079 function protect($password)
4081 $this->_protect = 1;
4082 $this->_password = $this->_encodePassword($password);
4086 * Set the width of a single column or a range of columns.
4089 * @param integer $firstcol first column on the range
4090 * @param integer $lastcol last column on the range
4091 * @param integer $width width to set
4092 * @param mixed $format The optional XF format to apply to the columns
4093 * @param integer $hidden The optional hidden atribute
4094 * @param integer $level The optional outline level
4096 function setColumn($firstcol, $lastcol, $width, $format = null, $hidden = 0, $level = 0)
4098 $this->_colinfo[] = array($firstcol, $lastcol, $width, &$format, $hidden, $level);
4100 // Set width to zero if column is hidden
4101 $width = ($hidden) ? 0 : $width;
4103 for ($col = $firstcol; $col <= $lastcol; $col++) {
4104 $this->col_sizes[$col] = $width;
4109 * Set which cell or cells are selected in a worksheet
4112 * @param integer $first_row first row in the selected quadrant
4113 * @param integer $first_column first column in the selected quadrant
4114 * @param integer $last_row last row in the selected quadrant
4115 * @param integer $last_column last column in the selected quadrant
4117 function setSelection($first_row,$first_column,$last_row,$last_column)
4119 $this->_selection = array($first_row,$first_column,$last_row,$last_column);
4123 * Set panes and mark them as frozen.
4126 * @param array $panes This is the only parameter received and is composed of the following:
4127 * 0 => Vertical split position,
4128 * 1 => Horizontal split position
4129 * 2 => Top row visible
4130 * 3 => Leftmost column visible
4133 function freezePanes($panes)
4136 $this->_panes = $panes;
4140 * Set panes and mark them as unfrozen.
4143 * @param array $panes This is the only parameter received and is composed of the following:
4144 * 0 => Vertical split position,
4145 * 1 => Horizontal split position
4146 * 2 => Top row visible
4147 * 3 => Leftmost column visible
4150 function thawPanes($panes)
4153 $this->_panes = $panes;
4157 * Set the page orientation as portrait.
4161 function setPortrait()
4163 $this->_orientation = 1;
4167 * Set the page orientation as landscape.
4171 function setLandscape()
4173 $this->_orientation = 0;
4177 * Set the paper type. Ex. 1 = US Letter, 9 = A4
4180 * @param integer $size The type of paper size to use
4182 function setPaper($size = 0)
4184 $this->_paper_size = $size;
4189 * Set the page header caption and optional margin.
4192 * @param string $string The header text
4193 * @param float $margin optional head margin in inches.
4195 function setHeader($string,$margin = 0.50)
4197 if (strlen($string) >= 255) {
4198 //carp 'Header string must be less than 255 characters';
4201 $this->_header = $string;
4202 $this->_margin_head = $margin;
4206 * Set the page footer caption and optional margin.
4209 * @param string $string The footer text
4210 * @param float $margin optional foot margin in inches.
4212 function setFooter($string,$margin = 0.50)
4214 if (strlen($string) >= 255) {
4215 //carp 'Footer string must be less than 255 characters';
4218 $this->_footer = $string;
4219 $this->_margin_foot = $margin;
4223 * Center the page horinzontally.
4226 * @param integer $center the optional value for centering. Defaults to 1 (center).
4228 function centerHorizontally($center = 1)
4230 $this->_hcenter = $center;
4234 * Center the page vertically.
4237 * @param integer $center the optional value for centering. Defaults to 1 (center).
4239 function centerVertically($center = 1)
4241 $this->_vcenter = $center;
4245 * Set all the page margins to the same value in inches.
4248 * @param float $margin The margin to set in inches
4250 function setMargins($margin)
4252 $this->setMarginLeft($margin);
4253 $this->setMarginRight($margin);
4254 $this->setMarginTop($margin);
4255 $this->setMarginBottom($margin);
4259 * Set the left and right margins to the same value in inches.
4262 * @param float $margin The margin to set in inches
4264 function setMargins_LR($margin)
4266 $this->setMarginLeft($margin);
4267 $this->setMarginRight($margin);
4271 * Set the top and bottom margins to the same value in inches.
4274 * @param float $margin The margin to set in inches
4276 function setMargins_TB($margin)
4278 $this->setMarginTop($margin);
4279 $this->setMarginBottom($margin);
4283 * Set the left margin in inches.
4286 * @param float $margin The margin to set in inches
4288 function setMarginLeft($margin = 0.75)
4290 $this->_margin_left = $margin;
4294 * Set the right margin in inches.
4297 * @param float $margin The margin to set in inches
4299 function setMarginRight($margin = 0.75)
4301 $this->_margin_right = $margin;
4305 * Set the top margin in inches.
4308 * @param float $margin The margin to set in inches
4310 function setMarginTop($margin = 1.00)
4312 $this->_margin_top = $margin;
4316 * Set the bottom margin in inches.
4319 * @param float $margin The margin to set in inches
4321 function setMarginBottom($margin = 1.00)
4323 $this->_margin_bottom = $margin;
4327 * Set the rows to repeat at the top of each printed page.
4330 * @param integer $first_row First row to repeat
4331 * @param integer $last_row Last row to repeat. Optional.
4333 function repeatRows($first_row, $last_row = null)
4335 $this->title_rowmin = $first_row;
4336 if (isset($last_row)) { //Second row is optional
4337 $this->title_rowmax = $last_row;
4339 $this->title_rowmax = $first_row;
4344 * Set the columns to repeat at the left hand side of each printed page.
4347 * @param integer $first_col First column to repeat
4348 * @param integer $last_col Last column to repeat. Optional.
4350 function repeatColumns($first_col, $last_col = null)
4352 $this->title_colmin = $first_col;
4353 if (isset($last_col)) { // Second col is optional
4354 $this->title_colmax = $last_col;
4356 $this->title_colmax = $first_col;
4361 * Set the area of each worksheet that will be printed.
4364 * @param integer $first_row First row of the area to print
4365 * @param integer $first_col First column of the area to print
4366 * @param integer $last_row Last row of the area to print
4367 * @param integer $last_col Last column of the area to print
4369 function printArea($first_row, $first_col, $last_row, $last_col)
4371 $this->print_rowmin = $first_row;
4372 $this->print_colmin = $first_col;
4373 $this->print_rowmax = $last_row;
4374 $this->print_colmax = $last_col;
4379 * Set the option to hide gridlines on the printed page.
4383 function hideGridlines()
4385 $this->_print_gridlines = 0;
4389 * Set the option to hide gridlines on the worksheet (as seen on the screen).
4393 function hideScreenGridlines()
4395 $this->_screen_gridlines = 0;
4399 * Set the option to print the row and column headers on the printed page.
4402 * @param integer $print Whether to print the headers or not. Defaults to 1 (print).
4404 function printRowColHeaders($print = 1)
4406 $this->_print_headers = $print;
4410 * Set the vertical and horizontal number of pages that will define the maximum area printed.
4411 * It doesn't seem to work with OpenOffice.
4414 * @param integer $width Maximun width of printed area in pages
4415 * @param integer $height Maximun heigth of printed area in pages
4416 * @see setPrintScale()
4418 function fitToPages($width, $height)
4420 $this->_fit_page = 1;
4421 $this->_fit_width = $width;
4422 $this->_fit_height = $height;
4426 * Store the horizontal page breaks on a worksheet (for printing).
4427 * The breaks represent the row after which the break is inserted.
4430 * @param array $breaks Array containing the horizontal page breaks
4432 function setHPagebreaks($breaks)
4434 foreach ($breaks as $break) {
4435 array_push($this->_hbreaks, $break);
4440 * Store the vertical page breaks on a worksheet (for printing).
4441 * The breaks represent the column after which the break is inserted.
4444 * @param array $breaks Array containing the vertical page breaks
4446 function setVPagebreaks($breaks)
4448 foreach ($breaks as $break) {
4449 array_push($this->_vbreaks, $break);
4455 * Set the worksheet zoom factor.
4458 * @param integer $scale The zoom factor
4460 function setZoom($scale = 100)
4462 // Confine the scale to Excel's range
4463 if ($scale < 10 || $scale > 400) {
4467 $this->_zoom = floor($scale);
4471 * Set the scale factor for the printed page.
4472 * It turns off the "fit to page" option
4475 * @param integer $scale The optional scale factor. Defaults to 100
4477 function setPrintScale($scale = 100)
4479 // Confine the scale to Excel's range
4480 if ($scale < 10 || $scale > 400) {
4484 // Turn off "fit to page" option
4485 $this->_fit_page = 0;
4487 $this->_print_scale = floor($scale);
4491 * Map to the appropriate write method acording to the token recieved.
4494 * @param integer $row The row of the cell we are writing to
4495 * @param integer $col The column of the cell we are writing to
4496 * @param mixed $token What we are writing
4497 * @param mixed $format The optional format to apply to the cell
4499 function write($row, $col, $token, $format = null)
4501 // Check for a cell reference in A1 notation and substitute row and column
4502 /*if ($_[0] =~ /^\D/) {
4503 @_ = $this->_substituteCellref(@_);
4506 if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", $token)) {
4508 return $this->writeNumber($row, $col, $token, $format);
4509 } elseif (preg_match("/^[fh]tt?p:\/\//", $token)) {
4510 // Match http or ftp URL
4511 return $this->writeUrl($row, $col, $token, '', $format);
4512 } elseif (preg_match("/^mailto:/", $token)) {
4514 return $this->writeUrl($row, $col, $token, '', $format);
4515 } elseif (preg_match("/^(?:in|ex)ternal:/", $token)) {
4516 // Match internal or external sheet link
4517 return $this->writeUrl($row, $col, $token, '', $format);
4518 } elseif (preg_match("/^=/", $token)) {
4520 return $this->writeFormula($row, $col, $token, $format);
4521 } elseif (preg_match("/^@/", $token)) {
4523 return $this->writeFormula($row, $col, $token, $format);
4524 } elseif ($token == '') {
4526 return $this->writeBlank($row, $col, $format);
4528 // Default: match string
4529 return $this->writeString($row, $col, $token, $format);
4534 * Write an array of values as a row
4537 * @param integer $row The row we are writing to
4538 * @param integer $col The first col (leftmost col) we are writing to
4539 * @param array $val The array of values to write
4540 * @param mixed $format The optional format to apply to the cell
4541 * @return mixed PEAR_Error on failure
4544 function writeRow($row, $col, $val, $format = null)
4547 if (is_array($val)) {
4548 foreach ($val as $v) {
4550 $this->writeCol($row, $col, $v, $format);
4552 $this->write($row, $col, $v, $format);
4557 die('$val needs to be an array');
4563 * Write an array of values as a column
4566 * @param integer $row The first row (uppermost row) we are writing to
4567 * @param integer $col The col we are writing to
4568 * @param array $val The array of values to write
4569 * @param mixed $format The optional format to apply to the cell
4570 * @return mixed PEAR_Error on failure
4573 function writeCol($row, $col, $val, $format = null)
4576 if (is_array($val)) {
4577 foreach ($val as $v) {
4578 $this->write($row, $col, $v, $format);
4582 die('$val needs to be an array');
4588 * Returns an index to the XF record in the workbook
4591 * @param mixed &$format The optional XF format
4592 * @return integer The XF record index
4594 function _XF(&$format)
4597 return($format->getXfIndex());
4604 /******************************************************************************
4605 *******************************************************************************
4612 * Store Worksheet data in memory using the parent's class append() or to a
4613 * temporary file, the default.
4616 * @param string $data The binary data to append
4618 function _append($data)
4620 if ($this->_using_tmpfile) {
4621 // Add CONTINUE records if necessary
4622 if (strlen($data) > $this->_limit) {
4623 $data = $this->_addContinue($data);
4625 fwrite($this->_filehandle, $data);
4626 $this->_datasize += strlen($data);
4628 parent::_append($data);
4633 * Substitute an Excel cell reference in A1 notation for zero based row and
4634 * column values in an argument list.
4636 * Ex: ("A4", "Hello") is converted to (3, 0, "Hello").
4639 * @param string $cell The cell reference. Or range of cells.
4642 function _substituteCellref($cell)
4644 $cell = strtoupper($cell);
4646 // Convert a column range: 'A:A' or 'B:G'
4647 if (preg_match("/([A-I]?[A-Z]):([A-I]?[A-Z])/", $cell, $match)) {
4648 list($no_use, $col1) = $this->_cellToRowcol($match[1] .'1'); // Add a dummy row
4649 list($no_use, $col2) = $this->_cellToRowcol($match[2] .'1'); // Add a dummy row
4650 return(array($col1, $col2));
4653 // Convert a cell range: 'A1:B7'
4654 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/", $cell, $match)) {
4655 list($row1, $col1) = $this->_cellToRowcol($match[1]);
4656 list($row2, $col2) = $this->_cellToRowcol($match[2]);
4657 return(array($row1, $col1, $row2, $col2));
4660 // Convert a cell reference: 'A1' or 'AD2000'
4661 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+)/", $cell)) {
4662 list($row1, $col1) = $this->_cellToRowcol($match[1]);
4663 return(array($row1, $col1));
4666 // TODO use real error codes
4667 die("Unknown cell reference $cell");
4671 * Convert an Excel cell reference in A1 notation to a zero based row and column
4672 * reference; converts C1 to (0, 2).
4675 * @param string $cell The cell reference.
4676 * @return array containing (row, column)
4678 function _cellToRowcol($cell)
4680 preg_match("/\$?([A-I]?[A-Z])\$?(\d+)/",$cell,$match);
4684 // Convert base26 column string to number
4685 $chars = preg_split('//', $col);
4690 $char = array_pop($chars); // LS char first
4691 $col += (ord($char) -ord('A') +1) * pow(26,$expn);
4695 // Convert 1-index to zero-index
4699 return(array($row, $col));
4703 * Based on the algorithm provided by Daniel Rentz of OpenOffice.
4706 * @param string $plaintext The password to be encoded in plaintext.
4707 * @return string The encoded password
4709 function _encodePassword($plaintext)
4712 $i = 1; // char position
4714 // split the plain text password in its component characters
4715 $chars = preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY);
4716 foreach ($chars as $char) {
4717 $value = ord($char) << $i; // shifted ASCII value
4718 $rotated_bits = $value >> 15; // rotated bits beyond bit 15
4719 $value &= 0x7fff; // first 15 bits
4720 $password ^= ($value | $rotated_bits);
4724 $password ^= strlen($plaintext);
4725 $password ^= 0xCE4B;
4731 * This method sets the properties for outlining and grouping. The defaults
4732 * correspond to Excel's defaults.
4734 * @param bool $visible
4735 * @param bool $symbols_below
4736 * @param bool $symbols_right
4737 * @param bool $auto_style
4739 function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false)
4741 $this->_outline_on = $visible;
4742 $this->_outline_below = $symbols_below;
4743 $this->_outline_right = $symbols_right;
4744 $this->_outline_style = $auto_style;
4746 // Ensure this is a boolean vale for Window2
4747 if ($this->_outline_on) {
4748 $this->_outline_on = 1;
4752 /******************************************************************************
4753 *******************************************************************************
4760 * Write a double to the specified row and column (zero indexed).
4761 * An integer can be written as a double. Excel will display an
4762 * integer. $format is optional.
4764 * Returns 0 : normal termination
4765 * -2 : row or column out of range
4768 * @param integer $row Zero indexed row
4769 * @param integer $col Zero indexed column
4770 * @param float $num The number to write
4771 * @param mixed $format The optional XF format
4774 function writeNumber($row, $col, $num, $format = null)
4776 $record = 0x0203; // Record identifier
4777 $length = 0x000E; // Number of bytes to follow
4779 $xf = $this->_XF($format); // The cell format
4781 // Check that row and col are valid and store max and min values
4782 if ($row >= $this->_xls_rowmax) {
4785 if ($col >= $this->_xls_colmax) {
4788 if ($row < $this->_dim_rowmin) {
4789 $this->_dim_rowmin = $row;
4791 if ($row > $this->_dim_rowmax) {
4792 $this->_dim_rowmax = $row;
4794 if ($col < $this->_dim_colmin) {
4795 $this->_dim_colmin = $col;
4797 if ($col > $this->_dim_colmax) {
4798 $this->_dim_colmax = $col;
4801 $header = pack("vv", $record, $length);
4802 $data = pack("vvv", $row, $col, $xf);
4803 $xl_double = pack("d", $num);
4804 if ($this->_byte_order) { // if it's Big Endian
4805 $xl_double = strrev($xl_double);
4808 $this->_append($header.$data.$xl_double);
4813 * Write a string to the specified row and column (zero indexed).
4814 * NOTE: there is an Excel 5 defined limit of 255 characters.
4815 * $format is optional.
4816 * Returns 0 : normal termination
4817 * -2 : row or column out of range
4818 * -3 : long string truncated to 255 chars
4821 * @param integer $row Zero indexed row
4822 * @param integer $col Zero indexed column
4823 * @param string $str The string to write
4824 * @param mixed $format The XF format for the cell
4827 function writeString($row, $col, $str, $format = null)
4829 if ($this->_BIFF_version == 0x0600) {
4830 return $this->writeStringBIFF8($row, $col, $str, $format);
4832 $strlen = strlen($str);
4833 $record = 0x0204; // Record identifier
4834 $length = 0x0008 + $strlen; // Bytes to follow
4835 $xf = $this->_XF($format); // The cell format
4839 // Check that row and col are valid and store max and min values
4840 if ($row >= $this->_xls_rowmax) {
4843 if ($col >= $this->_xls_colmax) {
4846 if ($row < $this->_dim_rowmin) {
4847 $this->_dim_rowmin = $row;
4849 if ($row > $this->_dim_rowmax) {
4850 $this->_dim_rowmax = $row;
4852 if ($col < $this->_dim_colmin) {
4853 $this->_dim_colmin = $col;
4855 if ($col > $this->_dim_colmax) {
4856 $this->_dim_colmax = $col;
4859 if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars
4860 $str = substr($str, 0, $this->_xls_strmax);
4861 $length = 0x0008 + $this->_xls_strmax;
4862 $strlen = $this->_xls_strmax;
4866 $header = pack("vv", $record, $length);
4867 $data = pack("vvvv", $row, $col, $xf, $strlen);
4868 $this->_append($header . $data . $str);
4873 * Sets Input Encoding for writing strings
4876 * @param string $encoding The encoding. Ex: 'UTF-16LE', 'utf-8', 'ISO-859-7'
4878 function setInputEncoding($encoding)
4880 if ($encoding != 'UTF-16LE' && !function_exists('iconv')) {
4881 die("Using an input encoding other than UTF-16LE requires PHP support for iconv");
4883 $this->_input_encoding = $encoding;
4886 /** added 2009-03-05 by Joe Hunt, FA for arabic languages */
4893 * Write a string to the specified row and column (zero indexed).
4894 * This is the BIFF8 version (no 255 chars limit).
4895 * $format is optional.
4896 * Returns 0 : normal termination
4897 * -2 : row or column out of range
4898 * -3 : long string truncated to 255 chars
4901 * @param integer $row Zero indexed row
4902 * @param integer $col Zero indexed column
4903 * @param string $str The string to write
4904 * @param mixed $format The XF format for the cell
4907 function writeStringBIFF8($row, $col, $str, $format = null)
4909 if ($this->_input_encoding == 'UTF-16LE')
4911 $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
4914 elseif ($this->_input_encoding != '')
4916 $str = iconv($this->_input_encoding, 'UTF-16LE', $str);
4917 $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
4922 $strlen = strlen($str);
4925 $record = 0x00FD; // Record identifier
4926 $length = 0x000A; // Bytes to follow
4927 $xf = $this->_XF($format); // The cell format
4931 // Check that row and col are valid and store max and min values
4932 if ($this->_checkRowCol($row, $col) == false) {
4936 $str = pack('vC', $strlen, $encoding).$str;
4938 /* check if string is already present */
4939 if (!isset($this->_str_table[$str])) {
4940 $this->_str_table[$str] = $this->_str_unique++;
4942 $this->_str_total++;
4944 $header = pack('vv', $record, $length);
4945 $data = pack('vvvV', $row, $col, $xf, $this->_str_table[$str]);
4946 $this->_append($header.$data);
4951 * Check row and col before writing to a cell, and update the sheet's
4952 * dimensions accordingly
4955 * @param integer $row Zero indexed row
4956 * @param integer $col Zero indexed column
4957 * @return boolean true for success, false if row and/or col are grester
4958 * then maximums allowed.
4960 function _checkRowCol($row, $col)
4962 if ($row >= $this->_xls_rowmax) {
4965 if ($col >= $this->_xls_colmax) {
4968 if ($row < $this->_dim_rowmin) {
4969 $this->_dim_rowmin = $row;
4971 if ($row > $this->_dim_rowmax) {
4972 $this->_dim_rowmax = $row;
4974 if ($col < $this->_dim_colmin) {
4975 $this->_dim_colmin = $col;
4977 if ($col > $this->_dim_colmax) {
4978 $this->_dim_colmax = $col;
4984 * Writes a note associated with the cell given by the row and column.
4985 * NOTE records don't have a length limit.
4988 * @param integer $row Zero indexed row
4989 * @param integer $col Zero indexed column
4990 * @param string $note The note to write
4992 function writeNote($row, $col, $note)
4994 $note_length = strlen($note);
4995 $record = 0x001C; // Record identifier
4996 $max_length = 2048; // Maximun length for a NOTE record
4997 //$length = 0x0006 + $note_length; // Bytes to follow
4999 // Check that row and col are valid and store max and min values
5000 if ($row >= $this->_xls_rowmax) {
5003 if ($col >= $this->_xls_colmax) {
5006 if ($row < $this->_dim_rowmin) {
5007 $this->_dim_rowmin = $row;
5009 if ($row > $this->_dim_rowmax) {
5010 $this->_dim_rowmax = $row;
5012 if ($col < $this->_dim_colmin) {
5013 $this->_dim_colmin = $col;
5015 if ($col > $this->_dim_colmax) {
5016 $this->_dim_colmax = $col;
5019 // Length for this record is no more than 2048 + 6
5020 $length = 0x0006 + min($note_length, 2048);
5021 $header = pack("vv", $record, $length);
5022 $data = pack("vvv", $row, $col, $note_length);
5023 $this->_append($header . $data . substr($note, 0, 2048));
5025 for ($i = $max_length; $i < $note_length; $i += $max_length) {
5026 $chunk = substr($note, $i, $max_length);
5027 $length = 0x0006 + strlen($chunk);
5028 $header = pack("vv", $record, $length);
5029 $data = pack("vvv", -1, 0, strlen($chunk));
5030 $this->_append($header.$data.$chunk);
5036 * Write a blank cell to the specified row and column (zero indexed).
5037 * A blank cell is used to specify formatting without adding a string
5040 * A blank cell without a format serves no purpose. Therefore, we don't write
5041 * a BLANK record unless a format is specified.
5043 * Returns 0 : normal termination (including no format)
5044 * -1 : insufficient number of arguments
5045 * -2 : row or column out of range
5048 * @param integer $row Zero indexed row
5049 * @param integer $col Zero indexed column
5050 * @param mixed $format The XF format
5052 function writeBlank($row, $col, $format)
5054 // Don't write a blank cell unless it has a format
5059 $record = 0x0201; // Record identifier
5060 $length = 0x0006; // Number of bytes to follow
5061 $xf = $this->_XF($format); // The cell format
5063 // Check that row and col are valid and store max and min values
5064 if ($row >= $this->_xls_rowmax) {
5067 if ($col >= $this->_xls_colmax) {
5070 if ($row < $this->_dim_rowmin) {
5071 $this->_dim_rowmin = $row;
5073 if ($row > $this->_dim_rowmax) {
5074 $this->_dim_rowmax = $row;
5076 if ($col < $this->_dim_colmin) {
5077 $this->_dim_colmin = $col;
5079 if ($col > $this->_dim_colmax) {
5080 $this->_dim_colmax = $col;
5083 $header = pack("vv", $record, $length);
5084 $data = pack("vvv", $row, $col, $xf);
5085 $this->_append($header . $data);
5090 * Write a formula to the specified row and column (zero indexed).
5091 * The textual representation of the formula is passed to the parser in
5092 * Parser.php which returns a packed binary string.
5094 * Returns 0 : normal termination
5095 * -1 : formula errors (bad formula)
5096 * -2 : row or column out of range
5099 * @param integer $row Zero indexed row
5100 * @param integer $col Zero indexed column
5101 * @param string $formula The formula text string
5102 * @param mixed $format The optional XF format
5105 function writeFormula($row, $col, $formula, $format = null)
5107 $record = 0x0006; // Record identifier
5109 // Excel normally stores the last calculated value of the formula in $num.
5110 // Clearly we are not in a position to calculate this a priori. Instead
5111 // we set $num to zero and set the option flags in $grbit to ensure
5112 // automatic calculation of the formula when the file is opened.
5114 $xf = $this->_XF($format); // The cell format
5115 $num = 0x00; // Current value of formula
5116 $grbit = 0x03; // Option flags
5117 $unknown = 0x0000; // Must be zero
5120 // Check that row and col are valid and store max and min values
5121 if ($this->_checkRowCol($row, $col) == false) {
5125 // Strip the '=' or '@' sign at the beginning of the formula string
5126 if (preg_match("/^=/", $formula)) {
5127 $formula = preg_replace("/(^=)/", "", $formula);
5128 } elseif (preg_match("/^@/", $formula)) {
5129 $formula = preg_replace("/(^@)/", "", $formula);
5132 $this->writeString($row, $col, 'Unrecognised character for formula');
5136 // Parse the formula using the parser in Parser.php
5137 $this->_parser->parse($formula);
5139 $formula = $this->_parser->toReversePolish();
5141 $formlen = strlen($formula); // Length of the binary string
5142 $length = 0x16 + $formlen; // Length of the record data
5144 $header = pack("vv", $record, $length);
5145 $data = pack("vvvdvVv", $row, $col, $xf, $num,
5146 $grbit, $unknown, $formlen);
5148 $this->_append($header . $data . $formula);
5153 * Write a hyperlink.
5154 * This is comprised of two elements: the visible label and
5155 * the invisible link. The visible label is the same as the link unless an
5156 * alternative string is specified. The label is written using the
5157 * writeString() method. Therefore the 255 characters string limit applies.
5158 * $string and $format are optional.
5160 * The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external
5163 * Returns 0 : normal termination
5164 * -2 : row or column out of range
5165 * -3 : long string truncated to 255 chars
5168 * @param integer $row Row
5169 * @param integer $col Column
5170 * @param string $url URL string
5171 * @param string $string Alternative label
5172 * @param mixed $format The cell format
5175 function writeUrl($row, $col, $url, $string = '', $format = null)
5177 // Add start row and col to arg list
5178 return($this->_writeUrlRange($row, $col, $row, $col, $url, $string, $format));
5182 * This is the more general form of writeUrl(). It allows a hyperlink to be
5183 * written to a range of cells. This function also decides the type of hyperlink
5184 * to be written. These are either, Web (http, ftp, mailto), Internal
5185 * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
5189 * @param integer $row1 Start row
5190 * @param integer $col1 Start column
5191 * @param integer $row2 End row
5192 * @param integer $col2 End column
5193 * @param string $url URL string
5194 * @param string $string Alternative label
5195 * @param mixed $format The cell format
5199 function _writeUrlRange($row1, $col1, $row2, $col2, $url, $string = '', $format = null)
5202 // Check for internal/external sheet links or default to web link
5203 if (preg_match('[^internal:]', $url)) {
5204 return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url, $string, $format));
5206 if (preg_match('[^external:]', $url)) {
5207 return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url, $string, $format));
5209 return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url, $string, $format));
5214 * Used to write http, ftp and mailto hyperlinks.
5215 * The link type ($options) is 0x03 is the same as absolute dir ref without
5216 * sheet. However it is differentiated by the $unknown2 data stream.
5220 * @param integer $row1 Start row
5221 * @param integer $col1 Start column
5222 * @param integer $row2 End row
5223 * @param integer $col2 End column
5224 * @param string $url URL string
5225 * @param string $str Alternative label
5226 * @param mixed $format The cell format
5229 function _writeUrlWeb($row1, $col1, $row2, $col2, $url, $str, $format = null)
5231 $record = 0x01B8; // Record identifier
5232 $length = 0x00000; // Bytes to follow
5235 $format = $this->_url_format;
5238 // Write the visible label using the writeString() method.
5242 $str_error = $this->writeString($row1, $col1, $str, $format);
5243 if (($str_error == -2) || ($str_error == -3)) {
5247 // Pack the undocumented parts of the hyperlink stream
5248 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
5249 $unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");
5251 // Pack the option flags
5252 $options = pack("V", 0x03);
5254 // Convert URL to a null terminated wchar string
5255 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
5256 $url = $url . "\0\0\0";
5258 // Pack the length of the URL
5259 $url_len = pack("V", strlen($url));
5261 // Calculate the data length
5262 $length = 0x34 + strlen($url);
5264 // Pack the header data
5265 $header = pack("vv", $record, $length);
5266 $data = pack("vvvv", $row1, $row2, $col1, $col2);
5268 // Write the packed data
5269 $this->_append($header . $data .
5270 $unknown1 . $options .
5271 $unknown2 . $url_len . $url);
5276 * Used to write internal reference hyperlinks such as "Sheet1!A1".
5280 * @param integer $row1 Start row
5281 * @param integer $col1 Start column
5282 * @param integer $row2 End row
5283 * @param integer $col2 End column
5284 * @param string $url URL string
5285 * @param string $str Alternative label
5286 * @param mixed $format The cell format
5289 function _writeUrlInternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
5291 $record = 0x01B8; // Record identifier
5292 $length = 0x00000; // Bytes to follow
5295 $format = $this->_url_format;
5299 $url = preg_replace('/^internal:/', '', $url);
5301 // Write the visible label
5305 $str_error = $this->writeString($row1, $col1, $str, $format);
5306 if (($str_error == -2) || ($str_error == -3)) {
5310 // Pack the undocumented parts of the hyperlink stream
5311 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
5313 // Pack the option flags
5314 $options = pack("V", 0x08);
5316 // Convert the URL type and to a null terminated wchar string
5317 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
5318 $url = $url . "\0\0\0";
5320 // Pack the length of the URL as chars (not wchars)
5321 $url_len = pack("V", floor(strlen($url)/2));
5323 // Calculate the data length
5324 $length = 0x24 + strlen($url);
5326 // Pack the header data
5327 $header = pack("vv", $record, $length);
5328 $data = pack("vvvv", $row1, $row2, $col1, $col2);
5330 // Write the packed data
5331 $this->_append($header . $data .
5332 $unknown1 . $options .
5338 * Write links to external directory names such as 'c:\foo.xls',
5339 * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
5341 * Note: Excel writes some relative links with the $dir_long string. We ignore
5342 * these cases for the sake of simpler code.
5346 * @param integer $row1 Start row
5347 * @param integer $col1 Start column
5348 * @param integer $row2 End row
5349 * @param integer $col2 End column
5350 * @param string $url URL string
5351 * @param string $str Alternative label
5352 * @param mixed $format The cell format
5355 function _writeUrlExternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
5357 // Network drives are different. We will handle them separately
5358 // MS/Novell network drives and shares start with \\
5359 if (preg_match('[^external:\\\\]', $url)) {
5360 return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format));
5363 $record = 0x01B8; // Record identifier
5364 $length = 0x00000; // Bytes to follow
5367 $format = $this->_url_format;
5370 // Strip URL type and change Unix dir separator to Dos style (if needed)
5372 $url = preg_replace('/^external:/', '', $url);
5373 $url = preg_replace('/\//', "\\", $url);
5375 // Write the visible label
5377 $str = preg_replace('/\#/', ' - ', $url);
5379 $str_error = $this->writeString($row1, $col1, $str, $format);
5380 if (($str_error == -2) or ($str_error == -3)) {
5384 // Determine if the link is relative or absolute:
5385 // relative if link contains no dir separator, "somefile.xls"
5386 // relative if link starts with up-dir, "..\..\somefile.xls"
5387 // otherwise, absolute
5389 $absolute = 0x02; // Bit mask
5390 if (!preg_match("/\\\/", $url)) {
5393 if (preg_match("/^\.\.\\\/", $url)) {
5396 $link_type = 0x01 | $absolute;
5398 // Determine if the link contains a sheet reference and change some of the
5399 // parameters accordingly.
5400 // Split the dir name and sheet name (if it exists)
5401 /*if (preg_match("/\#/", $url)) {
5402 list($dir_long, $sheet) = preg_split("/\#/", $url);
5407 if (isset($sheet)) {
5409 $sheet_len = pack("V", strlen($sheet) + 0x01);
5410 $sheet = join("\0", preg_split('//', $sheet));
5417 if (preg_match("/\#/", $url)) {
5423 // Pack the link type
5424 $link_type = pack("V", $link_type);
5426 // Calculate the up-level dir count e.g.. (..\..\..\ == 3)
5427 $up_count = preg_match_all("/\.\.\\\/", $dir_long, $useless);
5428 $up_count = pack("v", $up_count);
5430 // Store the short dos dir name (null terminated)
5431 $dir_short = preg_replace("/\.\.\\\/", '', $dir_long) . "\0";
5433 // Store the long dir name as a wchar string (non-null terminated)
5434 //$dir_long = join("\0", preg_split('//', $dir_long));
5435 $dir_long = $dir_long . "\0";
5437 // Pack the lengths of the dir strings
5438 $dir_short_len = pack("V", strlen($dir_short) );
5439 $dir_long_len = pack("V", strlen($dir_long) );
5440 $stream_len = pack("V", 0);//strlen($dir_long) + 0x06);
5442 // Pack the undocumented parts of the hyperlink stream
5443 $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000' );
5444 $unknown2 = pack("H*",'0303000000000000C000000000000046' );
5445 $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000');
5446 $unknown4 = pack("v", 0x03 );
5448 // Pack the main data stream
5449 $data = pack("vvvv", $row1, $row2, $col1, $col2) .
5464 // Pack the header data
5465 $length = strlen($data);
5466 $header = pack("vv", $record, $length);
5468 // Write the packed data
5469 $this->_append($header. $data);
5475 * This method is used to set the height and format for a row.
5478 * @param integer $row The row to set
5479 * @param integer $height Height we are giving to the row.
5480 * Use null to set XF without setting height
5481 * @param mixed $format XF format we are giving to the row
5482 * @param bool $hidden The optional hidden attribute
5483 * @param integer $level The optional outline level for row, in range [0,7]
5485 function setRow($row, $height, $format = null, $hidden = false, $level = 0)
5487 $record = 0x0208; // Record identifier
5488 $length = 0x0010; // Number of bytes to follow
5490 $colMic = 0x0000; // First defined column
5491 $colMac = 0x0000; // Last defined column
5492 $irwMac = 0x0000; // Used by Excel to optimise loading
5493 $reserved = 0x0000; // Reserved
5494 $grbit = 0x0000; // Option flags
5495 $ixfe = $this->_XF($format); // XF index
5497 // set _row_sizes so _sizeRow() can use it
5498 $this->_row_sizes[$row] = $height;
5500 // Use setRow($row, null, $XF) to set XF format without setting height
5501 if ($height != null) {
5502 $miyRw = $height * 20; // row height
5504 $miyRw = 0xff; // default row height is 256
5507 $level = max(0, min($level, 7)); // level should be between 0 and 7
5508 $this->_outline_row_level = max($level, $this->_outline_row_level);
5511 // Set the options flags. fUnsynced is used to show that the font and row
5512 // heights are not compatible. This is usually the case for WriteExcel.
5513 // The collapsed flag 0x10 doesn't seem to be used to indicate that a row
5514 // is collapsed. Instead it is used to indicate that the previous row is
5515 // collapsed. The zero height flag, 0x20, is used to collapse a row.
5521 $grbit |= 0x0040; // fUnsynced
5527 $header = pack("vv", $record, $length);
5528 $data = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw,
5529 $irwMac,$reserved, $grbit, $ixfe);
5530 $this->_append($header.$data);
5534 * Writes Excel DIMENSIONS to define the area in which there is data.
5538 function _storeDimensions()
5540 $record = 0x0200; // Record identifier
5541 $row_min = $this->_dim_rowmin; // First row
5542 $row_max = $this->_dim_rowmax + 1; // Last row plus 1
5543 $col_min = $this->_dim_colmin; // First column
5544 $col_max = $this->_dim_colmax + 1; // Last column plus 1
5545 $reserved = 0x0000; // Reserved by Excel
5547 if ($this->_BIFF_version == 0x0500) {
5548 $length = 0x000A; // Number of bytes to follow
5549 $data = pack("vvvvv", $row_min, $row_max,
5550 $col_min, $col_max, $reserved);
5551 } elseif ($this->_BIFF_version == 0x0600) {
5553 $data = pack("VVvvv", $row_min, $row_max,
5554 $col_min, $col_max, $reserved);
5556 $header = pack("vv", $record, $length);
5557 $this->_prepend($header.$data);
5561 * Write BIFF record Window2.
5565 function _storeWindow2()
5567 $record = 0x023E; // Record identifier
5568 if ($this->_BIFF_version == 0x0500) {
5569 $length = 0x000A; // Number of bytes to follow
5570 } elseif ($this->_BIFF_version == 0x0600) {
5574 $grbit = 0x00B6; // Option flags
5575 $rwTop = 0x0000; // Top row visible in window
5576 $colLeft = 0x0000; // Leftmost column visible in window
5579 // The options flags that comprise $grbit
5580 $fDspFmla = 0; // 0 - bit
5581 $fDspGrid = $this->_screen_gridlines; // 1
5582 $fDspRwCol = 1; // 2
5583 $fFrozen = $this->_frozen; // 3
5584 $fDspZeros = 1; // 4
5585 $fDefaultHdr = 1; // 5
5586 $fArabic = $this->_rtl; // 6
5587 $fDspGuts = $this->_outline_on; // 7
5588 $fFrozenNoSplit = 0; // 0 - bit
5589 $fSelected = $this->selected; // 1
5593 $grbit |= $fDspGrid << 1;
5594 $grbit |= $fDspRwCol << 2;
5595 $grbit |= $fFrozen << 3;
5596 $grbit |= $fDspZeros << 4;
5597 $grbit |= $fDefaultHdr << 5;
5598 $grbit |= $fArabic << 6;
5599 $grbit |= $fDspGuts << 7;
5600 $grbit |= $fFrozenNoSplit << 8;
5601 $grbit |= $fSelected << 9;
5602 $grbit |= $fPaged << 10;
5604 $header = pack("vv", $record, $length);
5605 $data = pack("vvv", $grbit, $rwTop, $colLeft);
5607 if ($this->_BIFF_version == 0x0500) {
5608 $rgbHdr = 0x00000000; // Row/column heading and gridline color
5609 $data .= pack("V", $rgbHdr);
5610 } elseif ($this->_BIFF_version == 0x0600) {
5611 $rgbHdr = 0x0040; // Row/column heading and gridline color index
5612 $zoom_factor_page_break = 0x0000;
5613 $zoom_factor_normal = 0x0000;
5614 $data .= pack("vvvvV", $rgbHdr, 0x0000, $zoom_factor_page_break, $zoom_factor_normal, 0x00000000);
5616 $this->_append($header.$data);
5620 * Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
5624 function _storeDefcol()
5626 $record = 0x0055; // Record identifier
5627 $length = 0x0002; // Number of bytes to follow
5628 $colwidth = 0x0008; // Default column width
5630 $header = pack("vv", $record, $length);
5631 $data = pack("v", $colwidth);
5632 $this->_prepend($header . $data);
5636 * Write BIFF record COLINFO to define column widths
5638 * Note: The SDK says the record length is 0x0B but Excel writes a 0x0C
5642 * @param array $col_array This is the only parameter received and is composed of the following:
5643 * 0 => First formatted column,
5644 * 1 => Last formatted column,
5645 * 2 => Col width (8.43 is Excel default),
5646 * 3 => The optional XF format of the column,
5647 * 4 => Option flags.
5648 * 5 => Optional outline level
5650 function _storeColinfo($col_array)
5652 if (isset($col_array[0])) {
5653 $colFirst = $col_array[0];
5655 if (isset($col_array[1])) {
5656 $colLast = $col_array[1];
5658 if (isset($col_array[2])) {
5659 $coldx = $col_array[2];
5663 if (isset($col_array[3])) {
5664 $format = $col_array[3];
5668 if (isset($col_array[4])) {
5669 $grbit = $col_array[4];
5673 if (isset($col_array[5])) {
5674 $level = $col_array[5];
5678 $record = 0x007D; // Record identifier
5679 $length = 0x000B; // Number of bytes to follow
5681 $coldx += 0.72; // Fudge. Excel subtracts 0.72 !?
5682 $coldx *= 256; // Convert to units of 1/256 of a char
5684 $ixfe = $this->_XF($format);
5685 $reserved = 0x00; // Reserved
5687 $level = max(0, min($level, 7));
5688 $grbit |= $level << 8;
5690 $header = pack("vv", $record, $length);
5691 $data = pack("vvvvvC", $colFirst, $colLast, $coldx,
5692 $ixfe, $grbit, $reserved);
5693 $this->_prepend($header.$data);
5697 * Write BIFF record SELECTION.
5700 * @param array $array array containing ($rwFirst,$colFirst,$rwLast,$colLast)
5701 * @see setSelection()
5703 function _storeSelection($array)
5705 list($rwFirst,$colFirst,$rwLast,$colLast) = $array;
5706 $record = 0x001D; // Record identifier
5707 $length = 0x000F; // Number of bytes to follow
5709 $pnn = $this->_active_pane; // Pane position
5710 $rwAct = $rwFirst; // Active row
5711 $colAct = $colFirst; // Active column
5712 $irefAct = 0; // Active cell ref
5713 $cref = 1; // Number of refs
5715 if (!isset($rwLast)) {
5716 $rwLast = $rwFirst; // Last row in reference
5718 if (!isset($colLast)) {
5719 $colLast = $colFirst; // Last col in reference
5722 // Swap last row/col for first row/col as necessary
5723 if ($rwFirst > $rwLast) {
5724 list($rwFirst, $rwLast) = array($rwLast, $rwFirst);
5727 if ($colFirst > $colLast) {
5728 list($colFirst, $colLast) = array($colLast, $colFirst);
5731 $header = pack("vv", $record, $length);
5732 $data = pack("CvvvvvvCC", $pnn, $rwAct, $colAct,
5735 $colFirst, $colLast);
5736 $this->_append($header . $data);
5740 * Store the MERGEDCELLS record for all ranges of merged cells
5744 function _storeMergedCells()
5746 // if there are no merged cell ranges set, return
5747 if (count($this->_merged_ranges) == 0) {
5751 $length = 2 + count($this->_merged_ranges) * 8;
5753 $header = pack('vv', $record, $length);
5754 $data = pack('v', count($this->_merged_ranges));
5755 foreach ($this->_merged_ranges as $range) {
5756 $data .= pack('vvvv', $range[0], $range[2], $range[1], $range[3]);
5758 $this->_append($header . $data);
5762 * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
5763 * references in a worksheet.
5765 * Excel only stores references to external sheets that are used in formulas.
5766 * For simplicity we store references to all the sheets in the workbook
5767 * regardless of whether they are used or not. This reduces the overall
5768 * complexity and eliminates the need for a two way dialogue between the formula
5769 * parser the worksheet objects.
5772 * @param integer $count The number of external sheet references in this worksheet
5774 function _storeExterncount($count)
5776 $record = 0x0016; // Record identifier
5777 $length = 0x0002; // Number of bytes to follow
5779 $header = pack("vv", $record, $length);
5780 $data = pack("v", $count);
5781 $this->_prepend($header . $data);
5785 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
5786 * formulas. A formula references a sheet name via an index. Since we store a
5787 * reference to all of the external worksheets the EXTERNSHEET index is the same
5788 * as the worksheet index.
5791 * @param string $sheetname The name of a external worksheet
5793 function _storeExternsheet($sheetname)
5795 $record = 0x0017; // Record identifier
5797 // References to the current sheet are encoded differently to references to
5800 if ($this->name == $sheetname) {
5802 $length = 0x02; // The following 2 bytes
5803 $cch = 1; // The following byte
5804 $rgch = 0x02; // Self reference
5806 $length = 0x02 + strlen($sheetname);
5807 $cch = strlen($sheetname);
5808 $rgch = 0x03; // Reference to a sheet in the current workbook
5811 $header = pack("vv", $record, $length);
5812 $data = pack("CC", $cch, $rgch);
5813 $this->_prepend($header . $data . $sheetname);
5817 * Writes the Excel BIFF PANE record.
5818 * The panes can either be frozen or thawed (unfrozen).
5819 * Frozen panes are specified in terms of an integer number of rows and columns.
5820 * Thawed panes are specified in terms of Excel's units for rows and columns.
5823 * @param array $panes This is the only parameter received and is composed of the following:
5824 * 0 => Vertical split position,
5825 * 1 => Horizontal split position
5826 * 2 => Top row visible
5827 * 3 => Leftmost column visible
5830 function _storePanes($panes)
5835 $colLeft = $panes[3];
5836 if (count($panes) > 4) { // if Active pane was received
5837 $pnnAct = $panes[4];
5841 $record = 0x0041; // Record identifier
5842 $length = 0x000A; // Number of bytes to follow
5844 // Code specific to frozen or thawed panes.
5845 if ($this->_frozen) {
5846 // Set default values for $rwTop and $colLeft
5847 if (!isset($rwTop)) {
5850 if (!isset($colLeft)) {
5854 // Set default values for $rwTop and $colLeft
5855 if (!isset($rwTop)) {
5858 if (!isset($colLeft)) {
5862 // Convert Excel's row and column units to the internal units.
5863 // The default row height is 12.75
5864 // The default column width is 8.43
5865 // The following slope and intersection values were interpolated.
5868 $x = 113.879*$x + 390;
5872 // Determine which pane should be active. There is also the undocumented
5873 // option to override this should it be necessary: may be removed later.
5875 if (!isset($pnnAct)) {
5876 if ($x != 0 && $y != 0) {
5877 $pnnAct = 0; // Bottom right
5879 if ($x != 0 && $y == 0) {
5880 $pnnAct = 1; // Top right
5882 if ($x == 0 && $y != 0) {
5883 $pnnAct = 2; // Bottom left
5885 if ($x == 0 && $y == 0) {
5886 $pnnAct = 3; // Top left
5890 $this->_active_pane = $pnnAct; // Used in _storeSelection
5892 $header = pack("vv", $record, $length);
5893 $data = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct);
5894 $this->_append($header . $data);
5898 * Store the page setup SETUP BIFF record.
5902 function _storeSetup()
5904 $record = 0x00A1; // Record identifier
5905 $length = 0x0022; // Number of bytes to follow
5907 $iPaperSize = $this->_paper_size; // Paper size
5908 $iScale = $this->_print_scale; // Print scaling factor
5909 $iPageStart = 0x01; // Starting page number
5910 $iFitWidth = $this->_fit_width; // Fit to number of pages wide
5911 $iFitHeight = $this->_fit_height; // Fit to number of pages high
5912 $grbit = 0x00; // Option flags
5913 $iRes = 0x0258; // Print resolution
5914 $iVRes = 0x0258; // Vertical print resolution
5915 $numHdr = $this->_margin_head; // Header Margin
5916 $numFtr = $this->_margin_foot; // Footer Margin
5917 $iCopies = 0x01; // Number of copies
5919 $fLeftToRight = 0x0; // Print over then down
5920 $fLandscape = $this->_orientation; // Page orientation
5921 $fNoPls = 0x0; // Setup not read from printer
5922 $fNoColor = 0x0; // Print black and white
5923 $fDraft = 0x0; // Print draft quality
5924 $fNotes = 0x0; // Print notes
5925 $fNoOrient = 0x0; // Orientation not set
5926 $fUsePage = 0x0; // Use custom starting page
5928 $grbit = $fLeftToRight;
5929 $grbit |= $fLandscape << 1;
5930 $grbit |= $fNoPls << 2;
5931 $grbit |= $fNoColor << 3;
5932 $grbit |= $fDraft << 4;
5933 $grbit |= $fNotes << 5;
5934 $grbit |= $fNoOrient << 6;
5935 $grbit |= $fUsePage << 7;
5937 $numHdr = pack("d", $numHdr);
5938 $numFtr = pack("d", $numFtr);
5939 if ($this->_byte_order) { // if it's Big Endian
5940 $numHdr = strrev($numHdr);
5941 $numFtr = strrev($numFtr);
5944 $header = pack("vv", $record, $length);
5945 $data1 = pack("vvvvvvvv", $iPaperSize,
5953 $data2 = $numHdr.$numFtr;
5954 $data3 = pack("v", $iCopies);
5955 $this->_prepend($header . $data1 . $data2 . $data3);
5959 * Store the header caption BIFF record.
5963 function _storeHeader()
5965 $record = 0x0014; // Record identifier
5967 $str = $this->_header; // header string
5968 $cch = strlen($str); // Length of header string
5969 if ($this->_BIFF_version == 0x0600) {
5970 $encoding = 0x0; // TODO: Unicode support
5971 $length = 3 + $cch; // Bytes to follow
5973 $length = 1 + $cch; // Bytes to follow
5976 $header = pack("vv", $record, $length);
5977 if ($this->_BIFF_version == 0x0600) {
5978 $data = pack("vC", $cch, $encoding);
5980 $data = pack("C", $cch);
5983 $this->_prepend($header.$data.$str);
5987 * Store the footer caption BIFF record.
5991 function _storeFooter()
5993 $record = 0x0015; // Record identifier
5995 $str = $this->_footer; // Footer string
5996 $cch = strlen($str); // Length of footer string
5997 if ($this->_BIFF_version == 0x0600) {
5998 $encoding = 0x0; // TODO: Unicode support
5999 $length = 3 + $cch; // Bytes to follow
6004 $header = pack("vv", $record, $length);
6005 if ($this->_BIFF_version == 0x0600) {
6006 $data = pack("vC", $cch, $encoding);
6008 $data = pack("C", $cch);
6011 $this->_prepend($header . $data . $str);
6015 * Store the horizontal centering HCENTER BIFF record.
6019 function _storeHcenter()
6021 $record = 0x0083; // Record identifier
6022 $length = 0x0002; // Bytes to follow
6024 $fHCenter = $this->_hcenter; // Horizontal centering
6026 $header = pack("vv", $record, $length);
6027 $data = pack("v", $fHCenter);
6029 $this->_prepend($header.$data);
6033 * Store the vertical centering VCENTER BIFF record.
6037 function _storeVcenter()
6039 $record = 0x0084; // Record identifier
6040 $length = 0x0002; // Bytes to follow
6042 $fVCenter = $this->_vcenter; // Horizontal centering
6044 $header = pack("vv", $record, $length);
6045 $data = pack("v", $fVCenter);
6046 $this->_prepend($header . $data);
6050 * Store the LEFTMARGIN BIFF record.
6054 function _storeMarginLeft()
6056 $record = 0x0026; // Record identifier
6057 $length = 0x0008; // Bytes to follow
6059 $margin = $this->_margin_left; // Margin in inches
6061 $header = pack("vv", $record, $length);
6062 $data = pack("d", $margin);
6063 if ($this->_byte_order) { // if it's Big Endian
6064 $data = strrev($data);
6067 $this->_prepend($header . $data);
6071 * Store the RIGHTMARGIN BIFF record.
6075 function _storeMarginRight()
6077 $record = 0x0027; // Record identifier
6078 $length = 0x0008; // Bytes to follow
6080 $margin = $this->_margin_right; // Margin in inches
6082 $header = pack("vv", $record, $length);
6083 $data = pack("d", $margin);
6084 if ($this->_byte_order) { // if it's Big Endian
6085 $data = strrev($data);
6088 $this->_prepend($header . $data);
6092 * Store the TOPMARGIN BIFF record.
6096 function _storeMarginTop()
6098 $record = 0x0028; // Record identifier
6099 $length = 0x0008; // Bytes to follow
6101 $margin = $this->_margin_top; // Margin in inches
6103 $header = pack("vv", $record, $length);
6104 $data = pack("d", $margin);
6105 if ($this->_byte_order) { // if it's Big Endian
6106 $data = strrev($data);
6109 $this->_prepend($header . $data);
6113 * Store the BOTTOMMARGIN BIFF record.
6117 function _storeMarginBottom()
6119 $record = 0x0029; // Record identifier
6120 $length = 0x0008; // Bytes to follow
6122 $margin = $this->_margin_bottom; // Margin in inches
6124 $header = pack("vv", $record, $length);
6125 $data = pack("d", $margin);
6126 if ($this->_byte_order) { // if it's Big Endian
6127 $data = strrev($data);
6130 $this->_prepend($header . $data);
6134 * Merges the area given by its arguments.
6135 * This is an Excel97/2000 method. It is required to perform more complicated
6136 * merging than the normal setAlign('merge').
6139 * @param integer $first_row First row of the area to merge
6140 * @param integer $first_col First column of the area to merge
6141 * @param integer $last_row Last row of the area to merge
6142 * @param integer $last_col Last column of the area to merge
6144 function mergeCells($first_row, $first_col, $last_row, $last_col)
6146 $record = 0x00E5; // Record identifier
6147 $length = 0x000A; // Bytes to follow
6148 $cref = 1; // Number of refs
6150 // Swap last row/col for first row/col as necessary
6151 if ($first_row > $last_row) {
6152 list($first_row, $last_row) = array($last_row, $first_row);
6155 if ($first_col > $last_col) {
6156 list($first_col, $last_col) = array($last_col, $first_col);
6159 $header = pack("vv", $record, $length);
6160 $data = pack("vvvvv", $cref, $first_row, $last_row,
6161 $first_col, $last_col);
6163 $this->_append($header.$data);
6167 * Write the PRINTHEADERS BIFF record.
6171 function _storePrintHeaders()
6173 $record = 0x002a; // Record identifier
6174 $length = 0x0002; // Bytes to follow
6176 $fPrintRwCol = $this->_print_headers; // Boolean flag
6178 $header = pack("vv", $record, $length);
6179 $data = pack("v", $fPrintRwCol);
6180 $this->_prepend($header . $data);
6184 * Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the
6189 function _storePrintGridlines()
6191 $record = 0x002b; // Record identifier
6192 $length = 0x0002; // Bytes to follow
6194 $fPrintGrid = $this->_print_gridlines; // Boolean flag
6196 $header = pack("vv", $record, $length);
6197 $data = pack("v", $fPrintGrid);
6198 $this->_prepend($header . $data);
6202 * Write the GRIDSET BIFF record. Must be used in conjunction with the
6203 * PRINTGRIDLINES record.
6207 function _storeGridset()
6209 $record = 0x0082; // Record identifier
6210 $length = 0x0002; // Bytes to follow
6212 $fGridSet = !($this->_print_gridlines); // Boolean flag
6214 $header = pack("vv", $record, $length);
6215 $data = pack("v", $fGridSet);
6216 $this->_prepend($header . $data);
6220 * Write the GUTS BIFF record. This is used to configure the gutter margins
6221 * where Excel outline symbols are displayed. The visibility of the gutters is
6222 * controlled by a flag in WSBOOL.
6224 * @see _storeWsbool()
6227 function _storeGuts()
6229 $record = 0x0080; // Record identifier
6230 $length = 0x0008; // Bytes to follow
6232 $dxRwGut = 0x0000; // Size of row gutter
6233 $dxColGut = 0x0000; // Size of col gutter
6235 $row_level = $this->_outline_row_level;
6238 // Calculate the maximum column outline level. The equivalent calculation
6239 // for the row outline level is carried out in setRow().
6240 $colcount = count($this->_colinfo);
6241 for ($i = 0; $i < $colcount; $i++) {
6242 // Skip cols without outline level info.
6243 if (count($col_level) >= 6) {
6244 $col_level = max($this->_colinfo[$i][5], $col_level);
6248 // Set the limits for the outline levels (0 <= x <= 7).
6249 $col_level = max(0, min($col_level, 7));
6251 // The displayed level is one greater than the max outline levels
6259 $header = pack("vv", $record, $length);
6260 $data = pack("vvvv", $dxRwGut, $dxColGut, $row_level, $col_level);
6262 $this->_prepend($header.$data);
6267 * Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction
6268 * with the SETUP record.
6272 function _storeWsbool()
6274 $record = 0x0081; // Record identifier
6275 $length = 0x0002; // Bytes to follow
6278 // The only option that is of interest is the flag for fit to page. So we
6279 // set all the options in one go.
6281 /*if ($this->_fit_page) {
6286 // Set the option flags
6287 $grbit |= 0x0001; // Auto page breaks visible
6288 if ($this->_outline_style) {
6289 $grbit |= 0x0020; // Auto outline styles
6291 if ($this->_outline_below) {
6292 $grbit |= 0x0040; // Outline summary below
6294 if ($this->_outline_right) {
6295 $grbit |= 0x0080; // Outline summary right
6297 if ($this->_fit_page) {
6298 $grbit |= 0x0100; // Page setup fit to page
6300 if ($this->_outline_on) {
6301 $grbit |= 0x0400; // Outline symbols displayed
6304 $header = pack("vv", $record, $length);
6305 $data = pack("v", $grbit);
6306 $this->_prepend($header . $data);
6310 * Write the HORIZONTALPAGEBREAKS BIFF record.
6314 function _storeHbreak()
6316 // Return if the user hasn't specified pagebreaks
6317 if (empty($this->_hbreaks)) {
6321 // Sort and filter array of page breaks
6322 $breaks = $this->_hbreaks;
6323 sort($breaks, SORT_NUMERIC);
6324 if ($breaks[0] == 0) { // don't use first break if it's 0
6325 array_shift($breaks);
6328 $record = 0x001b; // Record identifier
6329 $cbrk = count($breaks); // Number of page breaks
6330 if ($this->_BIFF_version == 0x0600) {
6331 $length = 2 + 6*$cbrk; // Bytes to follow
6333 $length = 2 + 2*$cbrk; // Bytes to follow
6336 $header = pack("vv", $record, $length);
6337 $data = pack("v", $cbrk);
6339 // Append each page break
6340 foreach ($breaks as $break) {
6341 if ($this->_BIFF_version == 0x0600) {
6342 $data .= pack("vvv", $break, 0x0000, 0x00ff);
6344 $data .= pack("v", $break);
6348 $this->_prepend($header.$data);
6353 * Write the VERTICALPAGEBREAKS BIFF record.
6357 function _storeVbreak()
6359 // Return if the user hasn't specified pagebreaks
6360 if (empty($this->_vbreaks)) {
6364 // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
6365 // It is slightly higher in Excel 97/200, approx. 1026
6366 $breaks = array_slice($this->_vbreaks,0,1000);
6368 // Sort and filter array of page breaks
6369 sort($breaks, SORT_NUMERIC);
6370 if ($breaks[0] == 0) { // don't use first break if it's 0
6371 array_shift($breaks);
6374 $record = 0x001a; // Record identifier
6375 $cbrk = count($breaks); // Number of page breaks
6376 if ($this->_BIFF_version == 0x0600) {
6377 $length = 2 + 6*$cbrk; // Bytes to follow
6379 $length = 2 + 2*$cbrk; // Bytes to follow
6382 $header = pack("vv", $record, $length);
6383 $data = pack("v", $cbrk);
6385 // Append each page break
6386 foreach ($breaks as $break) {
6387 if ($this->_BIFF_version == 0x0600) {
6388 $data .= pack("vvv", $break, 0x0000, 0xffff);
6390 $data .= pack("v", $break);
6394 $this->_prepend($header . $data);
6398 * Set the Biff PROTECT record to indicate that the worksheet is protected.
6402 function _storeProtect()
6404 // Exit unless sheet protection has been specified
6405 if ($this->_protect == 0) {
6409 $record = 0x0012; // Record identifier
6410 $length = 0x0002; // Bytes to follow
6412 $fLock = $this->_protect; // Worksheet is protected
6414 $header = pack("vv", $record, $length);
6415 $data = pack("v", $fLock);
6417 $this->_prepend($header.$data);
6421 * Write the worksheet PASSWORD record.
6425 function _storePassword()
6427 // Exit unless sheet protection and password have been specified
6428 if (($this->_protect == 0) || (!isset($this->_password))) {
6432 $record = 0x0013; // Record identifier
6433 $length = 0x0002; // Bytes to follow
6435 $wPassword = $this->_password; // Encoded password
6437 $header = pack("vv", $record, $length);
6438 $data = pack("v", $wPassword);
6440 $this->_prepend($header . $data);
6445 * Insert a 24bit bitmap image in a worksheet.
6448 * @param integer $row The row we are going to insert the bitmap into
6449 * @param integer $col The column we are going to insert the bitmap into
6450 * @param string $bitmap The bitmap filename
6451 * @param integer $x The horizontal position (offset) of the image inside the cell.
6452 * @param integer $y The vertical position (offset) of the image inside the cell.
6453 * @param integer $scale_x The horizontal scale
6454 * @param integer $scale_y The vertical scale
6456 function insertBitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1)
6458 $bitmap_array = $this->_processBitmap($bitmap);
6459 if ($this->isError($bitmap_array)) {
6460 $this->writeString($row, $col, $bitmap_array->getMessage());
6463 list($width, $height, $size, $data) = $bitmap_array; //$this->_processBitmap($bitmap);
6465 // Scale the frame of the image.
6467 $height *= $scale_y;
6469 // Calculate the vertices of the image and write the OBJ record
6470 $this->_positionImage($col, $row, $x, $y, $width, $height);
6472 // Write the IMDATA record to store the bitmap data
6474 $length = 8 + $size;
6479 $header = pack("vvvvV", $record, $length, $cf, $env, $lcb);
6480 $this->_append($header.$data);
6484 * Calculate the vertices that define the position of the image as required by
6487 * +------------+------------+
6489 * +-----+------------+------------+
6491 * | 1 |(A1)._______|______ |
6494 * +-----+----| BITMAP |-----+
6496 * | 2 | |______________. |
6499 * +---- +------------+------------+
6501 * Example of a bitmap that covers some of the area from cell A1 to cell B2.
6503 * Based on the width and height of the bitmap we need to calculate 8 vars:
6504 * $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
6505 * The width and height of the cells are also variable and have to be taken into
6507 * The values of $col_start and $row_start are passed in from the calling
6508 * function. The values of $col_end and $row_end are calculated by subtracting
6509 * the width and height of the bitmap from the width and height of the
6511 * The vertices are expressed as a percentage of the underlying cell width as
6512 * follows (rhs values are in pixels):
6516 * x2 = (X-1) / W *1024
6517 * y2 = (Y-1) / H *256
6519 * Where: X is distance from the left side of the underlying cell
6520 * Y is distance from the top of the underlying cell
6521 * W is the width of the cell
6522 * H is the height of the cell
6525 * @note the SDK incorrectly states that the height should be expressed as a
6526 * percentage of 1024.
6527 * @param integer $col_start Col containing upper left corner of object
6528 * @param integer $row_start Row containing top left corner of object
6529 * @param integer $x1 Distance to left side of object
6530 * @param integer $y1 Distance to top of object
6531 * @param integer $width Width of image frame
6532 * @param integer $height Height of image frame
6534 function _positionImage($col_start, $row_start, $x1, $y1, $width, $height)
6536 // Initialise end cell to the same as the start cell
6537 $col_end = $col_start; // Col containing lower right corner of object
6538 $row_end = $row_start; // Row containing bottom right corner of object
6540 // Zero the specified offset if greater than the cell dimensions
6541 if ($x1 >= $this->_sizeCol($col_start)) {
6544 if ($y1 >= $this->_sizeRow($row_start)) {
6548 $width = $width + $x1 -1;
6549 $height = $height + $y1 -1;
6551 // Subtract the underlying cell widths to find the end cell of the image
6552 while ($width >= $this->_sizeCol($col_end)) {
6553 $width -= $this->_sizeCol($col_end);
6557 // Subtract the underlying cell heights to find the end cell of the image
6558 while ($height >= $this->_sizeRow($row_end)) {
6559 $height -= $this->_sizeRow($row_end);
6563 // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
6564 // with zero eight or width.
6566 if ($this->_sizeCol($col_start) == 0) {
6569 if ($this->_sizeCol($col_end) == 0) {
6572 if ($this->_sizeRow($row_start) == 0) {
6575 if ($this->_sizeRow($row_end) == 0) {
6579 // Convert the pixel values to the percentage value expected by Excel
6580 $x1 = $x1 / $this->_sizeCol($col_start) * 1024;
6581 $y1 = $y1 / $this->_sizeRow($row_start) * 256;
6582 $x2 = $width / $this->_sizeCol($col_end) * 1024; // Distance to right side of object
6583 $y2 = $height / $this->_sizeRow($row_end) * 256; // Distance to bottom of object
6585 $this->_storeObjPicture($col_start, $x1,
6592 * Convert the width of a cell from user's units to pixels. By interpolation
6593 * the relationship is: y = 7x +5. If the width hasn't been set by the user we
6594 * use the default value. If the col is hidden we use a value of zero.
6597 * @param integer $col The column
6598 * @return integer The width in pixels
6600 function _sizeCol($col)
6602 // Look up the cell value to see if it has been changed
6603 if (isset($this->col_sizes[$col])) {
6604 if ($this->col_sizes[$col] == 0) {
6607 return(floor(7 * $this->col_sizes[$col] + 5));
6615 * Convert the height of a cell from user's units to pixels. By interpolation
6616 * the relationship is: y = 4/3x. If the height hasn't been set by the user we
6617 * use the default value. If the row is hidden we use a value of zero. (Not
6618 * possible to hide row yet).
6621 * @param integer $row The row
6622 * @return integer The width in pixels
6624 function _sizeRow($row)
6626 // Look up the cell value to see if it has been changed
6627 if (isset($this->_row_sizes[$row])) {
6628 if ($this->_row_sizes[$row] == 0) {
6631 return(floor(4/3 * $this->_row_sizes[$row]));
6639 * Store the OBJ record that precedes an IMDATA record. This could be generalise
6640 * to support other Excel objects.
6643 * @param integer $colL Column containing upper left corner of object
6644 * @param integer $dxL Distance from left side of cell
6645 * @param integer $rwT Row containing top left corner of object
6646 * @param integer $dyT Distance from top of cell
6647 * @param integer $colR Column containing lower right corner of object
6648 * @param integer $dxR Distance from right of cell
6649 * @param integer $rwB Row containing bottom right corner of object
6650 * @param integer $dyB Distance from bottom of cell
6652 function _storeObjPicture($colL,$dxL,$rwT,$dyT,$colR,$dxR,$rwB,$dyB)
6654 $record = 0x005d; // Record identifier
6655 $length = 0x003c; // Bytes to follow
6657 $cObj = 0x0001; // Count of objects in file (set to 1)
6658 $OT = 0x0008; // Object type. 8 = Picture
6659 $id = 0x0001; // Object ID
6660 $grbit = 0x0614; // Option flags
6662 $cbMacro = 0x0000; // Length of FMLA structure
6663 $Reserved1 = 0x0000; // Reserved
6664 $Reserved2 = 0x0000; // Reserved
6666 $icvBack = 0x09; // Background colour
6667 $icvFore = 0x09; // Foreground colour
6668 $fls = 0x00; // Fill pattern
6669 $fAuto = 0x00; // Automatic fill
6670 $icv = 0x08; // Line colour
6671 $lns = 0xff; // Line style
6672 $lnw = 0x01; // Line weight
6673 $fAutoB = 0x00; // Automatic border
6674 $frs = 0x0000; // Frame style
6675 $cf = 0x0009; // Image format, 9 = bitmap
6676 $Reserved3 = 0x0000; // Reserved
6677 $cbPictFmla = 0x0000; // Length of FMLA structure
6678 $Reserved4 = 0x0000; // Reserved
6679 $grbit2 = 0x0001; // Option flags
6680 $Reserved5 = 0x0000; // Reserved
6683 $header = pack("vv", $record, $length);
6684 $data = pack("V", $cObj);
6685 $data .= pack("v", $OT);
6686 $data .= pack("v", $id);
6687 $data .= pack("v", $grbit);
6688 $data .= pack("v", $colL);
6689 $data .= pack("v", $dxL);
6690 $data .= pack("v", $rwT);
6691 $data .= pack("v", $dyT);
6692 $data .= pack("v", $colR);
6693 $data .= pack("v", $dxR);
6694 $data .= pack("v", $rwB);
6695 $data .= pack("v", $dyB);
6696 $data .= pack("v", $cbMacro);
6697 $data .= pack("V", $Reserved1);
6698 $data .= pack("v", $Reserved2);
6699 $data .= pack("C", $icvBack);
6700 $data .= pack("C", $icvFore);
6701 $data .= pack("C", $fls);
6702 $data .= pack("C", $fAuto);
6703 $data .= pack("C", $icv);
6704 $data .= pack("C", $lns);
6705 $data .= pack("C", $lnw);
6706 $data .= pack("C", $fAutoB);
6707 $data .= pack("v", $frs);
6708 $data .= pack("V", $cf);
6709 $data .= pack("v", $Reserved3);
6710 $data .= pack("v", $cbPictFmla);
6711 $data .= pack("v", $Reserved4);
6712 $data .= pack("v", $grbit2);
6713 $data .= pack("V", $Reserved5);
6715 $this->_append($header . $data);
6719 * Convert a 24 bit bitmap into the modified internal format used by Windows.
6720 * This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the
6724 * @param string $bitmap The bitmap to process
6725 * @return array Array with data and properties of the bitmap
6727 function _processBitmap($bitmap)
6730 $bmp_fd = @fopen($bitmap,"rb");
6732 die("Couldn't import $bitmap");
6735 // Slurp the file into a string.
6736 $data = fread($bmp_fd, filesize($bitmap));
6738 // Check that the file is big enough to be a bitmap.
6739 if (strlen($data) <= 0x36) {
6740 die("$bitmap doesn't contain enough data.\n");
6743 // The first 2 bytes are used to identify the bitmap.
6744 $identity = unpack("A2ident", $data);
6745 if ($identity['ident'] != "BM") {
6746 die("$bitmap doesn't appear to be a valid bitmap image.\n");
6749 // Remove bitmap data: ID.
6750 $data = substr($data, 2);
6752 // Read and remove the bitmap size. This is more reliable than reading
6753 // the data size at offset 0x22.
6755 $size_array = unpack("Vsa", substr($data, 0, 4));
6756 $size = $size_array['sa'];
6757 $data = substr($data, 4);
6758 $size -= 0x36; // Subtract size of bitmap header.
6759 $size += 0x0C; // Add size of BIFF header.
6761 // Remove bitmap data: reserved, offset, header length.
6762 $data = substr($data, 12);
6764 // Read and remove the bitmap width and height. Verify the sizes.
6765 $width_and_height = unpack("V2", substr($data, 0, 8));
6766 $width = $width_and_height[1];
6767 $height = $width_and_height[2];
6768 $data = substr($data, 8);
6769 if ($width > 0xFFFF) {
6770 die("$bitmap: largest image width supported is 65k.\n");
6772 if ($height > 0xFFFF) {
6773 die("$bitmap: largest image height supported is 65k.\n");
6776 // Read and remove the bitmap planes and bpp data. Verify them.
6777 $planes_and_bitcount = unpack("v2", substr($data, 0, 4));
6778 $data = substr($data, 4);
6779 if ($planes_and_bitcount[2] != 24) { // Bitcount
6780 die("$bitmap isn't a 24bit true color bitmap.\n");
6782 if ($planes_and_bitcount[1] != 1) {
6783 die("$bitmap: only 1 plane supported in bitmap image.\n");
6786 // Read and remove the bitmap compression. Verify compression.
6787 $compression = unpack("Vcomp", substr($data, 0, 4));
6788 $data = substr($data, 4);
6791 if ($compression['comp'] != 0) {
6792 die("$bitmap: compression not supported in bitmap image.\n");
6795 // Remove bitmap data: data size, hres, vres, colours, imp. colours.
6796 $data = substr($data, 20);
6798 // Add the BITMAPCOREHEADER data
6799 $header = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18);
6800 $data = $header . $data;
6802 return (array($width, $height, $size, $data));
6806 * Store the window zoom factor. This should be a reduced fraction but for
6807 * simplicity we will store all fractions with a numerator of 100.
6811 function _storeZoom()
6813 // If scale is 100 we don't need to write a record
6814 if ($this->_zoom == 100) {
6818 $record = 0x00A0; // Record identifier
6819 $length = 0x0004; // Bytes to follow
6821 $header = pack("vv", $record, $length);
6822 $data = pack("vv", $this->_zoom, 100);
6823 $this->_append($header . $data);
6827 * FIXME: add comments
6829 function setValidation($row1, $col1, $row2, $col2, &$validator)
6831 $this->_dv[] = $validator->_getData() .
6832 pack("vvvvv", 1, $row1, $row2, $col1, $col2);
6836 * Store the DVAL and DV records.
6840 function _storeDataValidity()
6842 $record = 0x01b2; // Record identifier
6843 $length = 0x0012; // Bytes to follow
6845 $grbit = 0x0002; // Prompt box at cell, no cached validity data at DV records
6846 $horPos = 0x00000000; // Horizontal position of prompt box, if fixed position
6847 $verPos = 0x00000000; // Vertical position of prompt box, if fixed position
6848 $objId = 0xffffffff; // Object identifier of drop down arrow object, or -1 if not visible
6850 $header = pack('vv', $record, $length);
6851 $data = pack('vVVVV', $grbit, $horPos, $verPos, $objId,
6853 $this->_append($header.$data);
6855 $record = 0x01be; // Record identifier
6856 foreach ($this->_dv as $dv) {
6857 $length = strlen($dv); // Bytes to follow
6858 $header = pack("vv", $record, $length);
6859 $this->_append($header . $dv);
6865 * Class for generating Excel Spreadsheets
6867 * @author Xavier Noguer <xnoguer@rezebra.com>
6868 * @category FileFormats
6869 * @package Spreadsheet_Excel_Writer
6872 class Spreadsheet_Excel_Writer_Workbook extends Spreadsheet_Excel_Writer_BIFFwriter
6875 * Filename for the Workbook
6882 * @var object Parser
6887 * Flag for 1904 date system (0 => base date is 1900, 1 => base date is 1904)
6893 * The active worksheet of the workbook (0 indexed)
6899 * 1st displayed worksheet in the workbook (0 indexed)
6905 * Number of workbook tabs selected
6911 * Index for creating adding new formats to the workbook
6917 * Flag for preventing close from being called twice.
6924 * The BIFF file size for the workbook.
6926 * @see _calcSheetOffsets()
6931 * The default sheetname for all sheets created.
6937 * The default XF format.
6938 * @var object Format
6943 * Array containing references to all of this workbook's worksheets
6949 * Array of sheetnames for creating the EXTERNSHEET records
6955 * Array containing references to all of this workbook's formats
6961 * Array containing the colour palette
6967 * The default format for URLs.
6968 * @var object Format
6973 * The codepage indicates the text encoding used for strings
6979 * The country code used for localization
6985 * The temporary dir for storing the OLE file
6991 * number of bytes for sizeinfo of strings
6994 var $_string_sizeinfo_size;
6999 * @param string filename for storing the workbook. "-" for writing to stdout.
7002 function Spreadsheet_Excel_Writer_Workbook($filename)
7004 // It needs to call its parent's constructor explicitly
7005 $this->Spreadsheet_Excel_Writer_BIFFwriter();
7007 $this->_filename = $filename;
7008 $this->_parser = new Spreadsheet_Excel_Writer_Parser($this->_byte_order, $this->_BIFF_version);
7010 $this->_activesheet = 0;
7011 $this->_firstsheet = 0;
7012 $this->_selected = 0;
7013 $this->_xf_index = 16; // 15 style XF's and 1 cell XF.
7014 $this->_fileclosed = 0;
7015 $this->_biffsize = 0;
7016 $this->_sheetname = 'Sheet';
7017 $this->_tmp_format = new Spreadsheet_Excel_Writer_Format($this->_BIFF_version);
7018 $this->_worksheets = array();
7019 $this->_sheetnames = array();
7020 $this->_formats = array();
7021 $this->_palette = array();
7022 $this->_codepage = 0x04E4; // FIXME: should change for BIFF8
7023 $this->_country_code = -1;
7024 $this->_string_sizeinfo = 3;
7026 // Add the default format for hyperlinks
7027 $this->_url_format =& $this->addFormat(array('color' => 'blue', 'underline' => 1));
7028 $this->_str_total = 0;
7029 $this->_str_unique = 0;
7030 $this->_str_table = array();
7031 $this->_setPaletteXl97();
7032 $this->_tmp_dir = '';
7036 * Calls finalization methods.
7037 * This method should always be the last one to be called on every workbook
7040 * @return mixed true on success. PEAR_Error on failure
7044 if ($this->_fileclosed) { // Prevent close() from being called twice.
7047 $this->_storeWorkbook();
7048 $this->_fileclosed = 1;
7053 * An accessor for the _worksheets[] array
7054 * Returns an array of the worksheet objects in a workbook
7055 * It actually calls to worksheets()
7063 return $this->worksheets();
7067 * An accessor for the _worksheets[] array.
7068 * Returns an array of the worksheet objects in a workbook
7073 function worksheets()
7075 return $this->_worksheets;
7079 * Sets the BIFF version.
7080 * This method exists just to access experimental functionality
7081 * from BIFF8. It will be deprecated !
7082 * Only possible value is 8 (Excel 97/2000).
7083 * For any other value it fails silently.
7086 * @param integer $version The BIFF version
7088 function setVersion($version)
7090 if ($version == 8) { // only accept version 8
7092 $this->_BIFF_version = $version;
7093 // change BIFFwriter limit for CONTINUE records
7094 $this->_limit = 8228;
7095 $this->_tmp_format->_BIFF_version = $version;
7096 $this->_url_format->_BIFF_version = $version;
7097 $this->_parser->_BIFF_version = $version;
7099 $total_worksheets = count($this->_worksheets);
7100 // change version for all worksheets too
7101 for ($i = 0; $i < $total_worksheets; $i++) {
7102 $this->_worksheets[$i]->_BIFF_version = $version;
7105 $total_formats = count($this->_formats);
7106 // change version for all formats too
7107 for ($i = 0; $i < $total_formats; $i++) {
7108 $this->_formats[$i]->_BIFF_version = $version;
7114 * Set the country identifier for the workbook
7117 * @param integer $code Is the international calling country code for the
7120 function setCountry($code)
7122 $this->_country_code = $code;
7126 * Add a new worksheet to the Excel workbook.
7127 * If no name is given the name of the worksheet will be Sheeti$i, with
7131 * @param string $name the optional name of the worksheet
7132 * @return mixed reference to a worksheet object on success, PEAR_Error
7135 function &addWorksheet($name = '')
7137 $index = count($this->_worksheets);
7138 $sheetname = $this->_sheetname;
7141 $name = $sheetname.($index+1);
7144 // Check that sheetname is <= 31 chars (Excel limit before BIFF8).
7145 if ($this->_BIFF_version != 0x0600)
7147 if (strlen($name) > 31) {
7148 die("Sheetname $name must be <= 31 chars");
7152 // Check that the worksheet name doesn't already exist: a fatal Excel error.
7153 $total_worksheets = count($this->_worksheets);
7154 for ($i = 0; $i < $total_worksheets; $i++) {
7155 if ($this->_worksheets[$i]->getName() == $name) {
7156 die("Worksheet '$name' already exists");
7160 $worksheet = new Spreadsheet_Excel_Writer_Worksheet($this->_BIFF_version,
7162 $this->_activesheet, $this->_firstsheet,
7163 $this->_str_total, $this->_str_unique,
7164 $this->_str_table, $this->_url_format,
7167 $this->_worksheets[$index] = &$worksheet; // Store ref for iterator
7168 $this->_sheetnames[$index] = $name; // Store EXTERNSHEET names
7169 $this->_parser->setExtSheet($name, $index); // Register worksheet name with parser
7174 * Add a new format to the Excel workbook.
7175 * Also, pass any properties to the Format constructor.
7178 * @param array $properties array with properties for initializing the format.
7179 * @return &Spreadsheet_Excel_Writer_Format reference to an Excel Format
7181 function &addFormat($properties = array())
7183 $format = new Spreadsheet_Excel_Writer_Format($this->_BIFF_version, $this->_xf_index, $properties);
7184 $this->_xf_index += 1;
7185 $this->_formats[] = &$format;
7190 * Create new validator.
7193 * @return &Spreadsheet_Excel_Writer_Validator reference to a Validator
7195 function &addValidator()
7197 include_once 'Spreadsheet/Excel/Writer/Validator.php';
7198 /* FIXME: check for successful inclusion*/
7199 $valid = new Spreadsheet_Excel_Writer_Validator($this->_parser);
7204 * Change the RGB components of the elements in the colour palette.
7207 * @param integer $index colour index
7208 * @param integer $red red RGB value [0-255]
7209 * @param integer $green green RGB value [0-255]
7210 * @param integer $blue blue RGB value [0-255]
7211 * @return integer The palette index for the custom color
7213 function setCustomColor($index, $red, $green, $blue)
7215 // Match a HTML #xxyyzz style parameter
7216 /*if (defined $_[1] and $_[1] =~ /^#(\w\w)(\w\w)(\w\w)/ ) {
7217 @_ = ($_[0], hex $1, hex $2, hex $3);
7220 // Check that the colour index is the right range
7221 if ($index < 8 or $index > 64) {
7222 // TODO: assign real error codes
7223 die("Color index $index outside range: 8 <= index <= 64");
7226 // Check that the colour components are in the right range
7227 if (($red < 0 or $red > 255) ||
7228 ($green < 0 or $green > 255) ||
7229 ($blue < 0 or $blue > 255))
7231 die("Color component outside range: 0 <= color <= 255");
7234 $index -= 8; // Adjust colour index (wingless dragonfly)
7236 // Set the RGB value
7237 $this->_palette[$index] = array($red, $green, $blue, 0);
7242 * Sets the colour palette to the Excel 97+ default.
7246 function _setPaletteXl97()
7248 $this->_palette = array(
7249 array(0x00, 0x00, 0x00, 0x00), // 8
7250 array(0xff, 0xff, 0xff, 0x00), // 9
7251 array(0xff, 0x00, 0x00, 0x00), // 10
7252 array(0x00, 0xff, 0x00, 0x00), // 11
7253 array(0x00, 0x00, 0xff, 0x00), // 12
7254 array(0xff, 0xff, 0x00, 0x00), // 13
7255 array(0xff, 0x00, 0xff, 0x00), // 14
7256 array(0x00, 0xff, 0xff, 0x00), // 15
7257 array(0x80, 0x00, 0x00, 0x00), // 16
7258 array(0x00, 0x80, 0x00, 0x00), // 17
7259 array(0x00, 0x00, 0x80, 0x00), // 18
7260 array(0x80, 0x80, 0x00, 0x00), // 19
7261 array(0x80, 0x00, 0x80, 0x00), // 20
7262 array(0x00, 0x80, 0x80, 0x00), // 21
7263 array(0xc0, 0xc0, 0xc0, 0x00), // 22
7264 array(0x80, 0x80, 0x80, 0x00), // 23
7265 array(0x99, 0x99, 0xff, 0x00), // 24
7266 array(0x99, 0x33, 0x66, 0x00), // 25
7267 array(0xff, 0xff, 0xcc, 0x00), // 26
7268 array(0xcc, 0xff, 0xff, 0x00), // 27
7269 array(0x66, 0x00, 0x66, 0x00), // 28
7270 array(0xff, 0x80, 0x80, 0x00), // 29
7271 array(0x00, 0x66, 0xcc, 0x00), // 30
7272 array(0xcc, 0xcc, 0xff, 0x00), // 31
7273 array(0x00, 0x00, 0x80, 0x00), // 32
7274 array(0xff, 0x00, 0xff, 0x00), // 33
7275 array(0xff, 0xff, 0x00, 0x00), // 34
7276 array(0x00, 0xff, 0xff, 0x00), // 35
7277 array(0x80, 0x00, 0x80, 0x00), // 36
7278 array(0x80, 0x00, 0x00, 0x00), // 37
7279 array(0x00, 0x80, 0x80, 0x00), // 38
7280 array(0x00, 0x00, 0xff, 0x00), // 39
7281 array(0x00, 0xcc, 0xff, 0x00), // 40
7282 array(0xcc, 0xff, 0xff, 0x00), // 41
7283 array(0xcc, 0xff, 0xcc, 0x00), // 42
7284 array(0xff, 0xff, 0x99, 0x00), // 43
7285 array(0x99, 0xcc, 0xff, 0x00), // 44
7286 array(0xff, 0x99, 0xcc, 0x00), // 45
7287 array(0xcc, 0x99, 0xff, 0x00), // 46
7288 array(0xff, 0xcc, 0x99, 0x00), // 47
7289 array(0x33, 0x66, 0xff, 0x00), // 48
7290 array(0x33, 0xcc, 0xcc, 0x00), // 49
7291 array(0x99, 0xcc, 0x00, 0x00), // 50
7292 array(0xff, 0xcc, 0x00, 0x00), // 51
7293 array(0xff, 0x99, 0x00, 0x00), // 52
7294 array(0xff, 0x66, 0x00, 0x00), // 53
7295 array(0x66, 0x66, 0x99, 0x00), // 54
7296 array(0x96, 0x96, 0x96, 0x00), // 55
7297 array(0x00, 0x33, 0x66, 0x00), // 56
7298 array(0x33, 0x99, 0x66, 0x00), // 57
7299 array(0x00, 0x33, 0x00, 0x00), // 58
7300 array(0x33, 0x33, 0x00, 0x00), // 59
7301 array(0x99, 0x33, 0x00, 0x00), // 60
7302 array(0x99, 0x33, 0x66, 0x00), // 61
7303 array(0x33, 0x33, 0x99, 0x00), // 62
7304 array(0x33, 0x33, 0x33, 0x00), // 63
7309 * Assemble worksheets into a workbook and send the BIFF data to an OLE
7313 * @return mixed true on success. PEAR_Error on failure
7315 function _storeWorkbook()
7317 // Ensure that at least one worksheet has been selected.
7318 if ($this->_activesheet == 0) {
7319 $this->_worksheets[0]->selected = 1;
7322 // Calculate the number of selected worksheet tabs and call the finalization
7323 // methods for each worksheet
7324 $total_worksheets = count($this->_worksheets);
7325 for ($i = 0; $i < $total_worksheets; $i++) {
7326 if ($this->_worksheets[$i]->selected) {
7329 $this->_worksheets[$i]->close($this->_sheetnames);
7332 // Add Workbook globals
7333 $this->_storeBof(0x0005);
7334 $this->_storeCodepage();
7335 if ($this->_BIFF_version == 0x0600) {
7336 $this->_storeWindow1();
7338 if ($this->_BIFF_version == 0x0500) {
7339 $this->_storeExterns(); // For print area and repeat rows
7341 $this->_storeNames(); // For print area and repeat rows
7342 if ($this->_BIFF_version == 0x0500) {
7343 $this->_storeWindow1();
7345 $this->_storeDatemode();
7346 $this->_storeAllFonts();
7347 $this->_storeAllNumFormats();
7348 $this->_storeAllXfs();
7349 $this->_storeAllStyles();
7350 $this->_storePalette();
7351 $this->_calcSheetOffsets();
7353 // Add BOUNDSHEET records
7354 for ($i = 0; $i < $total_worksheets; $i++) {
7355 $this->_storeBoundsheet($this->_worksheets[$i]->name,$this->_worksheets[$i]->offset);
7358 if ($this->_country_code != -1) {
7359 $this->_storeCountry();
7362 if ($this->_BIFF_version == 0x0600) {
7363 //$this->_storeSupbookInternal();
7364 /* TODO: store external SUPBOOK records and XCT and CRN records
7365 in case of external references for BIFF8 */
7366 //$this->_storeExternsheetBiff8();
7367 $this->_storeSharedStringsTable();
7370 // End Workbook globals
7373 // Store the workbook in an OLE container
7374 $res = $this->_storeOLEFile();
7379 * Sets the temp dir used for storing the OLE file
7382 * @param string $dir The dir to be used as temp dir
7383 * @return true if given dir is valid, false otherwise
7385 function setTempDir($dir)
7388 $this->_tmp_dir = $dir;
7395 * Store the workbook in an OLE container
7398 * @return mixed true on success. PEAR_Error on failure
7400 function _storeOLEFile()
7402 $OLE = new OLEwriter($this->_filename);
7403 // Write Worksheet data if data <~ 7MB
7404 if ($OLE->setSize($this->_biffsize))
7406 $OLE->writeHeader();
7407 $OLE->write($this->_data);
7408 foreach($this->_worksheets as $sheet)
7410 while ($tmp = $sheet->getData()) {
7420 * Calculate offsets for Worksheet BOF records.
7424 function _calcSheetOffsets()
7426 if ($this->_BIFF_version == 0x0600) {
7427 $boundsheet_length = 12; // fixed length for a BOUNDSHEET record
7429 $boundsheet_length = 11;
7432 $offset = $this->_datasize;
7434 if ($this->_BIFF_version == 0x0600) {
7435 // add the length of the SST
7436 /* TODO: check this works for a lot of strings (> 8224 bytes) */
7437 $offset += $this->_calculateSharedStringsSizes();
7438 if ($this->_country_code != -1) {
7439 $offset += 8; // adding COUNTRY record
7441 // add the lenght of SUPBOOK, EXTERNSHEET and NAME records
7442 //$offset += 8; // FIXME: calculate real value when storing the records
7444 $total_worksheets = count($this->_worksheets);
7445 // add the length of the BOUNDSHEET records
7446 for ($i = 0; $i < $total_worksheets; $i++) {
7447 $offset += $boundsheet_length + strlen($this->_worksheets[$i]->name);
7451 for ($i = 0; $i < $total_worksheets; $i++) {
7452 $this->_worksheets[$i]->offset = $offset;
7453 $offset += $this->_worksheets[$i]->_datasize;
7455 $this->_biffsize = $offset;
7459 * Store the Excel FONT records.
7463 function _storeAllFonts()
7465 // tmp_format is added by the constructor. We use this to write the default XF's
7466 $format = $this->_tmp_format;
7467 $font = $format->getFont();
7469 // Note: Fonts are 0-indexed. According to the SDK there is no index 4,
7470 // so the following fonts are 0, 1, 2, 3, 5
7472 for ($i = 1; $i <= 5; $i++){
7473 $this->_append($font);
7476 // Iterate through the XF objects and write a FONT record if it isn't the
7477 // same as the default FONT and if it hasn't already been used.
7480 $index = 6; // The first user defined FONT
7482 $key = $format->getFontKey(); // The default font from _tmp_format
7483 $fonts[$key] = 0; // Index of the default font
7485 $total_formats = count($this->_formats);
7486 for ($i = 0; $i < $total_formats; $i++) {
7487 $key = $this->_formats[$i]->getFontKey();
7488 if (isset($fonts[$key])) {
7489 // FONT has already been used
7490 $this->_formats[$i]->font_index = $fonts[$key];
7492 // Add a new FONT record
7493 $fonts[$key] = $index;
7494 $this->_formats[$i]->font_index = $index;
7496 $font = $this->_formats[$i]->getFont();
7497 $this->_append($font);
7503 * Store user defined numerical formats i.e. FORMAT records
7507 function _storeAllNumFormats()
7509 // Leaning num_format syndrome
7510 $hash_num_formats = array();
7511 $num_formats = array();
7514 // Iterate through the XF objects and write a FORMAT record if it isn't a
7515 // built-in format type and if the FORMAT string hasn't already been used.
7516 $total_formats = count($this->_formats);
7517 for ($i = 0; $i < $total_formats; $i++) {
7518 $num_format = $this->_formats[$i]->_num_format;
7520 // Check if $num_format is an index to a built-in format.
7521 // Also check for a string of zeros, which is a valid format string
7522 // but would evaluate to zero.
7524 if (!preg_match("/^0+\d/", $num_format)) {
7525 if (preg_match("/^\d+$/", $num_format)) { // built-in format
7530 if (isset($hash_num_formats[$num_format])) {
7531 // FORMAT has already been used
7532 $this->_formats[$i]->_num_format = $hash_num_formats[$num_format];
7535 $hash_num_formats[$num_format] = $index;
7536 $this->_formats[$i]->_num_format = $index;
7537 array_push($num_formats,$num_format);
7542 // Write the new FORMAT records starting from 0xA4
7544 foreach ($num_formats as $num_format) {
7545 $this->_storeNumFormat($num_format,$index);
7551 * Write all XF records.
7555 function _storeAllXfs()
7557 // _tmp_format is added by the constructor. We use this to write the default XF's
7558 // The default font index is 0
7560 $format = $this->_tmp_format;
7561 for ($i = 0; $i <= 14; $i++) {
7562 $xf = $format->getXf('style'); // Style XF
7563 $this->_append($xf);
7566 $xf = $format->getXf('cell'); // Cell XF
7567 $this->_append($xf);
7570 $total_formats = count($this->_formats);
7571 for ($i = 0; $i < $total_formats; $i++) {
7572 $xf = $this->_formats[$i]->getXf('cell');
7573 $this->_append($xf);
7578 * Write all STYLE records.
7582 function _storeAllStyles()
7584 $this->_storeStyle();
7588 * Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for
7593 function _storeExterns()
7595 // Create EXTERNCOUNT with number of worksheets
7596 $this->_storeExterncount(count($this->_worksheets));
7598 // Create EXTERNSHEET for each worksheet
7599 foreach ($this->_sheetnames as $sheetname) {
7600 $this->_storeExternsheet($sheetname);
7605 * Write the NAME record to define the print area and the repeat rows and cols.
7609 function _storeNames()
7611 // Create the print area NAME records
7612 $total_worksheets = count($this->_worksheets);
7613 for ($i = 0; $i < $total_worksheets; $i++) {
7614 // Write a Name record if the print area has been defined
7615 if (isset($this->_worksheets[$i]->print_rowmin)) {
7616 $this->_storeNameShort(
7617 $this->_worksheets[$i]->index,
7619 $this->_worksheets[$i]->print_rowmin,
7620 $this->_worksheets[$i]->print_rowmax,
7621 $this->_worksheets[$i]->print_colmin,
7622 $this->_worksheets[$i]->print_colmax
7627 // Create the print title NAME records
7628 $total_worksheets = count($this->_worksheets);
7629 for ($i = 0; $i < $total_worksheets; $i++) {
7630 $rowmin = $this->_worksheets[$i]->title_rowmin;
7631 $rowmax = $this->_worksheets[$i]->title_rowmax;
7632 $colmin = $this->_worksheets[$i]->title_colmin;
7633 $colmax = $this->_worksheets[$i]->title_colmax;
7635 // Determine if row + col, row, col or nothing has been defined
7636 // and write the appropriate record
7638 if (isset($rowmin) && isset($colmin)) {
7639 // Row and column titles have been defined.
7640 // Row title has been defined.
7641 $this->_storeNameLong(
7642 $this->_worksheets[$i]->index,
7649 } elseif (isset($rowmin)) {
7650 // Row title has been defined.
7651 $this->_storeNameShort(
7652 $this->_worksheets[$i]->index,
7659 } elseif (isset($colmin)) {
7660 // Column title has been defined.
7661 $this->_storeNameShort(
7662 $this->_worksheets[$i]->index,
7670 // Print title hasn't been defined.
7678 /******************************************************************************
7685 * Stores the CODEPAGE biff record.
7689 function _storeCodepage()
7691 $record = 0x0042; // Record identifier
7692 $length = 0x0002; // Number of bytes to follow
7693 $cv = $this->_codepage; // The code page
7695 $header = pack('vv', $record, $length);
7696 $data = pack('v', $cv);
7698 $this->_append($header . $data);
7702 * Write Excel BIFF WINDOW1 record.
7706 function _storeWindow1()
7708 $record = 0x003D; // Record identifier
7709 $length = 0x0012; // Number of bytes to follow
7711 $xWn = 0x0000; // Horizontal position of window
7712 $yWn = 0x0000; // Vertical position of window
7713 $dxWn = 0x25BC; // Width of window
7714 $dyWn = 0x1572; // Height of window
7716 $grbit = 0x0038; // Option flags
7717 $ctabsel = $this->_selected; // Number of workbook tabs selected
7718 $wTabRatio = 0x0258; // Tab to scrollbar ratio
7720 $itabFirst = $this->_firstsheet; // 1st displayed worksheet
7721 $itabCur = $this->_activesheet; // Active worksheet
7723 $header = pack("vv", $record, $length);
7724 $data = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,
7726 $itabCur, $itabFirst,
7727 $ctabsel, $wTabRatio);
7728 $this->_append($header . $data);
7732 * Writes Excel BIFF BOUNDSHEET record.
7733 * FIXME: inconsistent with BIFF documentation
7735 * @param string $sheetname Worksheet name
7736 * @param integer $offset Location of worksheet BOF
7739 function _storeBoundsheet($sheetname,$offset)
7741 $record = 0x0085; // Record identifier
7743 if ($this->_BIFF_version == 0x0600) // Tried to fix the correct handling here, with the
7744 { // corrected specification from M$ - Joe Hunt 2009-03-08
7745 $encoding_string = $this->_input_encoding;
7746 if ($encoding_string == 'UTF-16LE')
7748 $strlen = function_exists('mb_strlen') ? mb_strlen($sheetname, 'UTF-16LE') : (strlen($sheetname) / 2);
7751 else if ($encoding_string != '')
7753 $sheetname = iconv($encoding_string, 'UTF-16LE', $sheetname);
7754 $strlen = function_exists('mb_strlen') ? mb_strlen($sheetname, 'UTF-16LE') : (strlen($sheetname) / 2);
7757 if ($strlen % 2 != 0)
7761 //$strlen = strlen($sheetname);
7762 $length = 0x08 + $strlen; // Number of bytes to follow
7764 $strlen = strlen($sheetname);
7765 $length = 0x07 + $strlen; // Number of bytes to follow
7768 $grbit = 0x0000; // Visibility and sheet type
7769 $cch = $strlen; // Length of sheet name
7771 $header = pack("vv", $record, $length);
7772 if ($this->_BIFF_version == 0x0600) {
7773 $data = pack("VvCC", $offset, $grbit, $cch, $encoding);
7775 $data = pack("VvC", $offset, $grbit, $cch);
7778 if ($this->_BIFF_version == 0x0600)
7780 $strlen = strlen($sheetname);
7781 $length = 0x08 + $strlen; // Number of bytes to follow
7783 $strlen = strlen($sheetname);
7784 $length = 0x07 + $strlen; // Number of bytes to follow
7787 $grbit = 0x0000; // Visibility and sheet type
7788 $cch = $strlen; // Length of sheet name
7790 $header = pack("vv", $record, $length);
7791 if ($this->_BIFF_version == 0x0600) {
7792 $data = pack("Vvv", $offset, $grbit, $cch);
7794 $data = pack("VvC", $offset, $grbit, $cch);
7796 $this->_append($header.$data.$sheetname);
7800 * Write Internal SUPBOOK record
7804 function _storeSupbookInternal()
7806 $record = 0x01AE; // Record identifier
7807 $length = 0x0004; // Bytes to follow
7809 $header = pack("vv", $record, $length);
7810 $data = pack("vv", count($this->_worksheets), 0x0104);
7811 $this->_append($header . $data);
7815 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
7818 * @param string $sheetname Worksheet name
7821 function _storeExternsheetBiff8()
7823 $total_references = count($this->_parser->_references);
7824 $record = 0x0017; // Record identifier
7825 $length = 2 + 6 * $total_references; // Number of bytes to follow
7827 $supbook_index = 0; // FIXME: only using internal SUPBOOK record
7828 $header = pack("vv", $record, $length);
7829 $data = pack('v', $total_references);
7830 for ($i = 0; $i < $total_references; $i++) {
7831 $data .= $this->_parser->_references[$i];
7833 $this->_append($header . $data);
7837 * Write Excel BIFF STYLE records.
7841 function _storeStyle()
7843 $record = 0x0293; // Record identifier
7844 $length = 0x0004; // Bytes to follow
7846 $ixfe = 0x8000; // Index to style XF
7847 $BuiltIn = 0x00; // Built-in style
7848 $iLevel = 0xff; // Outline style level
7850 $header = pack("vv", $record, $length);
7851 $data = pack("vCC", $ixfe, $BuiltIn, $iLevel);
7852 $this->_append($header . $data);
7857 * Writes Excel FORMAT record for non "built-in" numerical formats.
7859 * @param string $format Custom format string
7860 * @param integer $ifmt Format index code
7863 function _storeNumFormat($format, $ifmt)
7865 $record = 0x041E; // Record identifier
7867 if ($this->_BIFF_version == 0x0600) {
7868 $length = 5 + strlen($format); // Number of bytes to follow
7870 } elseif ($this->_BIFF_version == 0x0500) {
7871 $length = 3 + strlen($format); // Number of bytes to follow
7874 $cch = strlen($format); // Length of format string
7876 $header = pack("vv", $record, $length);
7877 if ($this->_BIFF_version == 0x0600) {
7878 $data = pack("vvC", $ifmt, $cch, $encoding);
7879 } elseif ($this->_BIFF_version == 0x0500) {
7880 $data = pack("vC", $ifmt, $cch);
7882 $this->_append($header . $data . $format);
7886 * Write DATEMODE record to indicate the date system in use (1904 or 1900).
7890 function _storeDatemode()
7892 $record = 0x0022; // Record identifier
7893 $length = 0x0002; // Bytes to follow
7895 $f1904 = $this->_1904; // Flag for 1904 date system
7897 $header = pack("vv", $record, $length);
7898 $data = pack("v", $f1904);
7899 $this->_append($header . $data);
7904 * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
7905 * references in the workbook.
7907 * Excel only stores references to external sheets that are used in NAME.
7908 * The workbook NAME record is required to define the print area and the repeat
7911 * A similar method is used in Worksheet.php for a slightly different purpose.
7913 * @param integer $cxals Number of external references
7916 function _storeExterncount($cxals)
7918 $record = 0x0016; // Record identifier
7919 $length = 0x0002; // Number of bytes to follow
7921 $header = pack("vv", $record, $length);
7922 $data = pack("v", $cxals);
7923 $this->_append($header . $data);
7928 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
7929 * formulas. NAME record is required to define the print area and the repeat
7932 * A similar method is used in Worksheet.php for a slightly different purpose.
7934 * @param string $sheetname Worksheet name
7937 function _storeExternsheet($sheetname)
7939 $record = 0x0017; // Record identifier
7940 $length = 0x02 + strlen($sheetname); // Number of bytes to follow
7942 $cch = strlen($sheetname); // Length of sheet name
7943 $rgch = 0x03; // Filename encoding
7945 $header = pack("vv", $record, $length);
7946 $data = pack("CC", $cch, $rgch);
7947 $this->_append($header . $data . $sheetname);
7952 * Store the NAME record in the short format that is used for storing the print
7953 * area, repeat rows only and repeat columns only.
7955 * @param integer $index Sheet index
7956 * @param integer $type Built-in name type
7957 * @param integer $rowmin Start row
7958 * @param integer $rowmax End row
7959 * @param integer $colmin Start colum
7960 * @param integer $colmax End column
7963 function _storeNameShort($index, $type, $rowmin, $rowmax, $colmin, $colmax)
7965 $record = 0x0018; // Record identifier
7966 $length = 0x0024; // Number of bytes to follow
7968 $grbit = 0x0020; // Option flags
7969 $chKey = 0x00; // Keyboard shortcut
7970 $cch = 0x01; // Length of text name
7971 $cce = 0x0015; // Length of text definition
7972 $ixals = $index + 1; // Sheet index
7973 $itab = $ixals; // Equal to ixals
7974 $cchCustMenu = 0x00; // Length of cust menu text
7975 $cchDescription = 0x00; // Length of description text
7976 $cchHelptopic = 0x00; // Length of help topic text
7977 $cchStatustext = 0x00; // Length of status bar text
7978 $rgch = $type; // Built-in name type
7981 $unknown04 = 0xffff-$index;
7982 $unknown05 = 0x0000;
7983 $unknown06 = 0x0000;
7984 $unknown07 = 0x1087;
7985 $unknown08 = 0x8005;
7987 $header = pack("vv", $record, $length);
7988 $data = pack("v", $grbit);
7989 $data .= pack("C", $chKey);
7990 $data .= pack("C", $cch);
7991 $data .= pack("v", $cce);
7992 $data .= pack("v", $ixals);
7993 $data .= pack("v", $itab);
7994 $data .= pack("C", $cchCustMenu);
7995 $data .= pack("C", $cchDescription);
7996 $data .= pack("C", $cchHelptopic);
7997 $data .= pack("C", $cchStatustext);
7998 $data .= pack("C", $rgch);
7999 $data .= pack("C", $unknown03);
8000 $data .= pack("v", $unknown04);
8001 $data .= pack("v", $unknown05);
8002 $data .= pack("v", $unknown06);
8003 $data .= pack("v", $unknown07);
8004 $data .= pack("v", $unknown08);
8005 $data .= pack("v", $index);
8006 $data .= pack("v", $index);
8007 $data .= pack("v", $rowmin);
8008 $data .= pack("v", $rowmax);
8009 $data .= pack("C", $colmin);
8010 $data .= pack("C", $colmax);
8011 $this->_append($header . $data);
8016 * Store the NAME record in the long format that is used for storing the repeat
8017 * rows and columns when both are specified. This shares a lot of code with
8018 * _storeNameShort() but we use a separate method to keep the code clean.
8019 * Code abstraction for reuse can be carried too far, and I should know. ;-)
8021 * @param integer $index Sheet index
8022 * @param integer $type Built-in name type
8023 * @param integer $rowmin Start row
8024 * @param integer $rowmax End row
8025 * @param integer $colmin Start colum
8026 * @param integer $colmax End column
8029 function _storeNameLong($index, $type, $rowmin, $rowmax, $colmin, $colmax)
8031 $record = 0x0018; // Record identifier
8032 $length = 0x003d; // Number of bytes to follow
8033 $grbit = 0x0020; // Option flags
8034 $chKey = 0x00; // Keyboard shortcut
8035 $cch = 0x01; // Length of text name
8036 $cce = 0x002e; // Length of text definition
8037 $ixals = $index + 1; // Sheet index
8038 $itab = $ixals; // Equal to ixals
8039 $cchCustMenu = 0x00; // Length of cust menu text
8040 $cchDescription = 0x00; // Length of description text
8041 $cchHelptopic = 0x00; // Length of help topic text
8042 $cchStatustext = 0x00; // Length of status bar text
8043 $rgch = $type; // Built-in name type
8046 $unknown02 = 0x002b;
8048 $unknown04 = 0xffff-$index;
8049 $unknown05 = 0x0000;
8050 $unknown06 = 0x0000;
8051 $unknown07 = 0x1087;
8052 $unknown08 = 0x8008;
8054 $header = pack("vv", $record, $length);
8055 $data = pack("v", $grbit);
8056 $data .= pack("C", $chKey);
8057 $data .= pack("C", $cch);
8058 $data .= pack("v", $cce);
8059 $data .= pack("v", $ixals);
8060 $data .= pack("v", $itab);
8061 $data .= pack("C", $cchCustMenu);
8062 $data .= pack("C", $cchDescription);
8063 $data .= pack("C", $cchHelptopic);
8064 $data .= pack("C", $cchStatustext);
8065 $data .= pack("C", $rgch);
8066 $data .= pack("C", $unknown01);
8067 $data .= pack("v", $unknown02);
8068 // Column definition
8069 $data .= pack("C", $unknown03);
8070 $data .= pack("v", $unknown04);
8071 $data .= pack("v", $unknown05);
8072 $data .= pack("v", $unknown06);
8073 $data .= pack("v", $unknown07);
8074 $data .= pack("v", $unknown08);
8075 $data .= pack("v", $index);
8076 $data .= pack("v", $index);
8077 $data .= pack("v", 0x0000);
8078 $data .= pack("v", 0x3fff);
8079 $data .= pack("C", $colmin);
8080 $data .= pack("C", $colmax);
8082 $data .= pack("C", $unknown03);
8083 $data .= pack("v", $unknown04);
8084 $data .= pack("v", $unknown05);
8085 $data .= pack("v", $unknown06);
8086 $data .= pack("v", $unknown07);
8087 $data .= pack("v", $unknown08);
8088 $data .= pack("v", $index);
8089 $data .= pack("v", $index);
8090 $data .= pack("v", $rowmin);
8091 $data .= pack("v", $rowmax);
8092 $data .= pack("C", 0x00);
8093 $data .= pack("C", 0xff);
8095 $data .= pack("C", 0x10);
8096 $this->_append($header . $data);
8100 * Stores the COUNTRY record for localization
8104 function _storeCountry()
8106 $record = 0x008C; // Record identifier
8107 $length = 4; // Number of bytes to follow
8109 $header = pack('vv', $record, $length);
8110 /* using the same country code always for simplicity */
8111 $data = pack('vv', $this->_country_code, $this->_country_code);
8112 $this->_append($header . $data);
8116 * Stores the PALETTE biff record.
8120 function _storePalette()
8122 $aref = $this->_palette;
8124 $record = 0x0092; // Record identifier
8125 $length = 2 + 4 * count($aref); // Number of bytes to follow
8126 $ccv = count($aref); // Number of RGB values to follow
8127 $data = ''; // The RGB data
8129 // Pack the RGB data
8130 foreach ($aref as $color) {
8131 foreach ($color as $byte) {
8132 $data .= pack("C",$byte);
8136 $header = pack("vvv", $record, $length, $ccv);
8137 $this->_append($header . $data);
8142 * Handling of the SST continue blocks is complicated by the need to include an
8143 * additional continuation byte depending on whether the string is split between
8144 * blocks or whether it starts at the beginning of the block. (There are also
8145 * additional complications that will arise later when/if Rich Strings are
8150 function _calculateSharedStringsSizes()
8152 /* Iterate through the strings to calculate the CONTINUE block sizes.
8153 For simplicity we use the same size for the SST and CONTINUE records:
8154 8228 : Maximum Excel97 block size
8155 -4 : Length of block header
8156 -8 : Length of additional SST header information
8157 -8 : Arbitrary number to keep within _add_continue() limit = 8208
8159 $continue_limit = 8208;
8162 $this->_block_sizes = array();
8165 foreach (array_keys($this->_str_table) as $string) {
8166 $string_length = strlen($string);
8167 $headerinfo = unpack("vlength/Cencoding", $string);
8168 $encoding = $headerinfo["encoding"];
8171 // Block length is the total length of the strings that will be
8172 // written out in a single SST or CONTINUE block.
8173 $block_length += $string_length;
8175 // We can write the string if it doesn't cross a CONTINUE boundary
8176 if ($block_length < $continue_limit) {
8177 $written += $string_length;
8181 // Deal with the cases where the next string to be written will exceed
8182 // the CONTINUE boundary. If the string is very long it may need to be
8183 // written in more than one CONTINUE record.
8184 while ($block_length >= $continue_limit) {
8186 // We need to avoid the case where a string is continued in the first
8187 // n bytes that contain the string header information.
8188 $header_length = 3; // Min string + header size -1
8189 $space_remaining = $continue_limit - $written - $continue;
8192 /* TODO: Unicode data should only be split on char (2 byte)
8193 boundaries. Therefore, in some cases we need to reduce the
8198 // Only applies to Unicode strings
8199 if ($encoding == 1) {
8200 // Min string + header size -1
8203 if ($space_remaining > $header_length) {
8204 // String contains 3 byte header => split on odd boundary
8205 if (!$split_string && $space_remaining % 2 != 1) {
8209 // Split section without header => split on even boundary
8210 else if ($split_string && $space_remaining % 2 == 1) {
8220 if ($space_remaining > $header_length) {
8221 // Write as much as possible of the string in the current block
8222 $written += $space_remaining;
8224 // Reduce the current block length by the amount written
8225 $block_length -= $continue_limit - $continue - $align;
8227 // Store the max size for this block
8228 $this->_block_sizes[] = $continue_limit - $align;
8230 // If the current string was split then the next CONTINUE block
8231 // should have the string continue flag (grbit) set unless the
8232 // split string fits exactly into the remaining space.
8233 if ($block_length > 0) {
8239 // Store the max size for this block
8240 $this->_block_sizes[] = $written + $continue;
8242 // Not enough space to start the string in the current block
8243 $block_length -= $continue_limit - $space_remaining - $continue;
8248 // If the string (or substr) is small enough we can write it in the
8249 // new CONTINUE block. Else, go through the loop again to write it in
8250 // one or more CONTINUE blocks
8251 if ($block_length < $continue_limit) {
8252 $written = $block_length;
8259 // Store the max size for the last block unless it is empty
8260 if ($written + $continue) {
8261 $this->_block_sizes[] = $written + $continue;
8265 /* Calculate the total length of the SST and associated CONTINUEs (if any).
8266 The SST record will have a length even if it contains no strings.
8267 This length is required to set the offsets in the BOUNDSHEET records since
8268 they must be written before the SST records
8271 $tmp_block_sizes = array();
8272 $tmp_block_sizes = $this->_block_sizes;
8275 if (!empty($tmp_block_sizes)) {
8276 $length += array_shift($tmp_block_sizes); // SST
8278 while (!empty($tmp_block_sizes)) {
8279 $length += 4 + array_shift($tmp_block_sizes); // CONTINUEs
8286 * Write all of the workbooks strings into an indexed array.
8287 * See the comments in _calculate_shared_string_sizes() for more information.
8289 * The Excel documentation says that the SST record should be followed by an
8290 * EXTSST record. The EXTSST record is a hash table that is used to optimise
8291 * access to SST. However, despite the documentation it doesn't seem to be
8292 * required so we will ignore it.
8296 function _storeSharedStringsTable()
8298 $record = 0x00fc; // Record identifier
8299 $length = 0x0008; // Number of bytes to follow
8302 // Iterate through the strings to calculate the CONTINUE block sizes
8303 $continue_limit = 8208;
8308 // sizes are upside down
8309 $tmp_block_sizes = $this->_block_sizes;
8310 // $tmp_block_sizes = array_reverse($this->_block_sizes);
8312 // The SST record is required even if it contains no strings. Thus we will
8313 // always have a length
8315 if (!empty($tmp_block_sizes)) {
8316 $length = 8 + array_shift($tmp_block_sizes);
8323 // Write the SST block header information
8324 $header = pack("vv", $record, $length);
8325 $data = pack("VV", $this->_str_total, $this->_str_unique);
8326 $this->_append($header . $data);
8328 /* TODO: not good for performance */
8329 foreach (array_keys($this->_str_table) as $string) {
8331 $string_length = strlen($string);
8332 $headerinfo = unpack("vlength/Cencoding", $string);
8333 $encoding = $headerinfo["encoding"];
8336 // Block length is the total length of the strings that will be
8337 // written out in a single SST or CONTINUE block.
8339 $block_length += $string_length;
8342 // We can write the string if it doesn't cross a CONTINUE boundary
8343 if ($block_length < $continue_limit) {
8344 $this->_append($string);
8345 $written += $string_length;
8349 // Deal with the cases where the next string to be written will exceed
8350 // the CONTINUE boundary. If the string is very long it may need to be
8351 // written in more than one CONTINUE record.
8353 while ($block_length >= $continue_limit) {
8355 // We need to avoid the case where a string is continued in the first
8356 // n bytes that contain the string header information.
8358 $header_length = 3; // Min string + header size -1
8359 $space_remaining = $continue_limit - $written - $continue;
8362 // Unicode data should only be split on char (2 byte) boundaries.
8363 // Therefore, in some cases we need to reduce the amount of available
8364 // space by 1 byte to ensure the correct alignment.
8367 // Only applies to Unicode strings
8368 if ($encoding == 1) {
8369 // Min string + header size -1
8372 if ($space_remaining > $header_length) {
8373 // String contains 3 byte header => split on odd boundary
8374 if (!$split_string && $space_remaining % 2 != 1) {
8378 // Split section without header => split on even boundary
8379 else if ($split_string && $space_remaining % 2 == 1) {
8389 if ($space_remaining > $header_length) {
8390 // Write as much as possible of the string in the current block
8391 $tmp = substr($string, 0, $space_remaining);
8392 $this->_append($tmp);
8394 // The remainder will be written in the next block(s)
8395 $string = substr($string, $space_remaining);
8397 // Reduce the current block length by the amount written
8398 $block_length -= $continue_limit - $continue - $align;
8400 // If the current string was split then the next CONTINUE block
8401 // should have the string continue flag (grbit) set unless the
8402 // split string fits exactly into the remaining space.
8404 if ($block_length > 0) {
8410 // Not enough space to start the string in the current block
8411 $block_length -= $continue_limit - $space_remaining - $continue;
8415 // Write the CONTINUE block header
8416 if (!empty($this->_block_sizes)) {
8418 $length = array_shift($tmp_block_sizes);
8420 $header = pack('vv', $record, $length);
8422 $header .= pack('C', $encoding);
8424 $this->_append($header);
8427 // If the string (or substr) is small enough we can write it in the
8428 // new CONTINUE block. Else, go through the loop again to write it in
8429 // one or more CONTINUE blocks
8431 if ($block_length < $continue_limit) {
8432 $this->_append($string);
8433 $written = $block_length;