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', "<>");
107 * Class for creating OLE streams for Excel Spreadsheets
109 * @author Xavier Noguer <xnoguer@rezebra.com>
110 * @package Spreadsheet_WriteExcel
115 * Filename for the OLE stream
122 * Filehandle for the OLE stream
128 * Name of the temporal file in case OLE stream goes to stdout
134 * Variable for preventing closing two times
140 * Size of the data to be written to the OLE stream
146 * Real data size to be written to the OLE stream
152 * Number of big blocks in the OLE stream
158 * Number of list blocks in the OLE stream
164 * Number of big blocks in the OLE stream
170 * Constructor for the OLEwriter class
172 * @param string $OLEfilename the name of the file for the OLE stream
174 function OLEwriter($OLEfilename)
176 $this->_OLEfilename = $OLEfilename;
177 $this->_filehandle = '';
178 $this->_tmp_filename = '';
179 $this->_fileclosed = 0;
180 $this->_biff_only = 0;
181 //$this->_size_allowed = 0;
182 $this->_biffsize = 0;
183 $this->_booksize = 0;
184 $this->_big_blocks = 0;
185 $this->_list_blocks = 0;
186 $this->_root_start = 0;
187 //$this->_block_count = 4;
188 $this->_initialize();
192 * Check for a valid filename and store the filehandle.
193 * Filehandle "-" writes to STDOUT
197 function _initialize()
199 $OLEfile = $this->_OLEfilename;
201 if (($OLEfile == '-') or ($OLEfile == '')) {
202 $this->_tmp_filename = tempnam("/tmp", "OLEwriter");
203 $fh = fopen($this->_tmp_filename, "wb");
205 die("Can't create temporary file.");
208 // Create a new file, open for writing (in binmode)
209 $fh = fopen($OLEfile, "wb");
211 die("Can't open $OLEfile. It may be in use or protected.");
216 $this->_filehandle = $fh;
221 * Set the size of the data to be written to the OLE stream.
222 * The maximun size comes from this:
223 * $big_blocks = (109 depot block x (128 -1 marker word)
224 * - (1 x end words)) = 13842
225 * $maxsize = $big_blocks * 512 bytes = 7087104
228 * @see Spreadsheet_Excel_Writer_Workbook::store_OLE_file()
229 * @param integer $biffsize The size of the data to be written to the OLE stream
230 * @return integer 1 for success
232 function setSize($biffsize)
234 $maxsize = 7087104; // TODO: extend max size
236 if ($biffsize > $maxsize) {
237 die("Maximum file size, $maxsize, exceeded.");
240 $this->_biffsize = $biffsize;
241 // Set the min file size to 4k to avoid having to use small blocks
242 if ($biffsize > 4096) {
243 $this->_booksize = $biffsize;
245 $this->_booksize = 4096;
247 //$this->_size_allowed = 1;
253 * Calculate various sizes needed for the OLE stream
257 function _calculateSizes()
259 $datasize = $this->_booksize;
260 if ($datasize % 512 == 0) {
261 $this->_big_blocks = $datasize/512;
263 $this->_big_blocks = floor($datasize/512) + 1;
265 // There are 127 list blocks and 1 marker blocks for each big block
266 // depot + 1 end of chain block
267 $this->_list_blocks = floor(($this->_big_blocks)/127) + 1;
268 $this->_root_start = $this->_big_blocks;
272 * Write root entry, big block list and close the filehandle.
273 * This routine is used to explicitly close the open filehandle without
274 * having to wait for DESTROY.
277 * @see Spreadsheet_Excel_Writer_Workbook::store_OLE_file()
281 //return if not $this->{_size_allowed};
282 $this->_writePadding();
283 $this->_writePropertyStorage();
284 $this->_writeBigBlockDepot();
285 // Close the filehandle
286 fclose($this->_filehandle);
287 if (($this->_OLEfilename == '-') or ($this->_OLEfilename == '')) {
288 $fh = fopen($this->_tmp_filename, "rb");
290 die("Can't read temporary file.");
293 @unlink($this->_tmp_filename);
295 $this->_fileclosed = 1;
300 * Write BIFF data to OLE file.
302 * @param string $data string of bytes to be written
304 function write($data)
306 fwrite($this->_filehandle, $data, strlen($data));
311 * Write OLE header block.
313 function writeHeader()
315 $this->_calculateSizes();
316 $root_start = $this->_root_start;
317 $num_lists = $this->_list_blocks;
318 $id = pack("nnnn", 0xD0CF, 0x11E0, 0xA1B1, 0x1AE1);
319 $unknown1 = pack("VVVV", 0x00, 0x00, 0x00, 0x00);
320 $unknown2 = pack("vv", 0x3E, 0x03);
321 $unknown3 = pack("v", -2);
322 $unknown4 = pack("v", 0x09);
323 $unknown5 = pack("VVV", 0x06, 0x00, 0x00);
324 $num_bbd_blocks = pack("V", $num_lists);
325 $root_startblock = pack("V", $root_start);
326 $unknown6 = pack("VV", 0x00, 0x1000);
327 $sbd_startblock = pack("V", -2);
328 $unknown7 = pack("VVV", 0x00, -2 ,0x00);
329 $unused = pack("V", -1);
331 fwrite($this->_filehandle, $id);
332 fwrite($this->_filehandle, $unknown1);
333 fwrite($this->_filehandle, $unknown2);
334 fwrite($this->_filehandle, $unknown3);
335 fwrite($this->_filehandle, $unknown4);
336 fwrite($this->_filehandle, $unknown5);
337 fwrite($this->_filehandle, $num_bbd_blocks);
338 fwrite($this->_filehandle, $root_startblock);
339 fwrite($this->_filehandle, $unknown6);
340 fwrite($this->_filehandle, $sbd_startblock);
341 fwrite($this->_filehandle, $unknown7);
343 for ($i=1; $i <= $num_lists; $i++) {
345 fwrite($this->_filehandle, pack("V",$root_start));
347 for ($i = $num_lists; $i <=108; $i++) {
348 fwrite($this->_filehandle, $unused);
354 * Write big block depot.
358 function _writeBigBlockDepot()
360 $num_blocks = $this->_big_blocks;
361 $num_lists = $this->_list_blocks;
362 $total_blocks = $num_lists *128;
363 $used_blocks = $num_blocks + $num_lists +2;
365 $marker = pack("V", -3);
366 $end_of_chain = pack("V", -2);
367 $unused = pack("V", -1);
369 for ($i = 1; $i < $num_blocks; $i++) {
370 fwrite($this->_filehandle, pack("V",$i));
373 fwrite($this->_filehandle, $end_of_chain);
374 fwrite($this->_filehandle, $end_of_chain);
375 for ($i = 0; $i < $num_lists; $i++) {
376 fwrite($this->_filehandle, $marker);
379 for ($i = $used_blocks; $i <= $total_blocks; $i++) {
380 fwrite($this->_filehandle, $unused);
385 * Write property storage. TODO: add summary sheets
389 function _writePropertyStorage()
392 /*************** name type dir start size */
393 $this->_writePps("Root Entry", 0x05, 1, -2, 0x00);
394 $this->_writePps("Book", 0x02, -1, 0x00, $this->_booksize);
395 $this->_writePps('', 0x00, -1, 0x00, 0x0000);
396 $this->_writePps('', 0x00, -1, 0x00, 0x0000);
400 * Write property sheet in property storage
402 * @param string $name name of the property storage.
403 * @param integer $type type of the property storage.
404 * @param integer $dir dir of the property storage.
405 * @param integer $start start of the property storage.
406 * @param integer $size size of the property storage.
409 function _writePps($name, $type, $dir, $start, $size)
415 $name = $name . "\0";
416 $name_length = strlen($name);
417 for ($i = 0; $i < $name_length; $i++) {
418 // Simulate a Unicode string
419 $rawname .= pack("H*",dechex(ord($name{$i}))).pack("C",0);
421 $length = strlen($name) * 2;
424 $zero = pack("C", 0);
425 $pps_sizeofname = pack("v", $length); // 0x40
426 $pps_type = pack("v", $type); // 0x42
427 $pps_prev = pack("V", -1); // 0x44
428 $pps_next = pack("V", -1); // 0x48
429 $pps_dir = pack("V", $dir); // 0x4c
431 $unknown1 = pack("V", 0);
433 $pps_ts1s = pack("V", 0); // 0x64
434 $pps_ts1d = pack("V", 0); // 0x68
435 $pps_ts2s = pack("V", 0); // 0x6c
436 $pps_ts2d = pack("V", 0); // 0x70
437 $pps_sb = pack("V", $start); // 0x74
438 $pps_size = pack("V", $size); // 0x78
441 fwrite($this->_filehandle, $rawname);
442 for ($i = 0; $i < (64 -$length); $i++) {
443 fwrite($this->_filehandle, $zero);
445 fwrite($this->_filehandle, $pps_sizeofname);
446 fwrite($this->_filehandle, $pps_type);
447 fwrite($this->_filehandle, $pps_prev);
448 fwrite($this->_filehandle, $pps_next);
449 fwrite($this->_filehandle, $pps_dir);
450 for ($i = 0; $i < 5; $i++) {
451 fwrite($this->_filehandle, $unknown1);
453 fwrite($this->_filehandle, $pps_ts1s);
454 fwrite($this->_filehandle, $pps_ts1d);
455 fwrite($this->_filehandle, $pps_ts2d);
456 fwrite($this->_filehandle, $pps_ts2d);
457 fwrite($this->_filehandle, $pps_sb);
458 fwrite($this->_filehandle, $pps_size);
459 fwrite($this->_filehandle, $unknown1);
463 * Pad the end of the file
467 function _writePadding()
469 $biffsize = $this->_biffsize;
470 if ($biffsize < 4096) {
475 if ($biffsize % $min_size != 0) {
476 $padding = $min_size - ($biffsize % $min_size);
477 for ($i = 0; $i < $padding; $i++) {
478 fwrite($this->_filehandle, "\0");
485 * Class for writing Excel BIFF records.
487 * From "MICROSOFT EXCEL BINARY FILE FORMAT" by Mark O'Brien (Microsoft Corporation):
489 * BIFF (BInary File Format) is the file format in which Excel documents are
490 * saved on disk. A BIFF file is a complete description of an Excel document.
491 * BIFF files consist of sequences of variable-length records. There are many
492 * different types of BIFF records. For example, one record type describes a
493 * formula entered into a cell; one describes the size and location of a
494 * window into a document; another describes a picture format.
496 * @author Xavier Noguer <xnoguer@php.net>
497 * @category FileFormats
498 * @package Spreadsheet_Excel_Writer
501 class Spreadsheet_Excel_Writer_BIFFwriter
504 * The BIFF/Excel version (5).
507 var $_BIFF_version = 0x0500;
510 * The byte order of this architecture. 0 => little endian, 1 => big endian
516 * The string containing the data of the BIFF stream
522 * The size of the data in bytes. Should be the same as strlen($this->_data)
528 * The maximun length for a BIFF record. See _addContinue()
530 * @see _addContinue()
539 function Spreadsheet_Excel_Writer_BIFFwriter()
541 $this->_byte_order = '';
543 $this->_datasize = 0;
544 $this->_limit = 2080;
545 // Set the byte order
546 $this->_setByteOrder();
550 * Determine the byte order and store it as class data to avoid
551 * recalculating it for each call to new().
555 function _setByteOrder()
557 // Check if "pack" gives the required IEEE 64bit float
558 $teststr = pack("d", 1.2345);
559 $number = pack("C8", 0x8D, 0x97, 0x6E, 0x12, 0x83, 0xC0, 0xF3, 0x3F);
560 if ($number == $teststr) {
561 $byte_order = 0; // Little Endian
562 } elseif ($number == strrev($teststr)){
563 $byte_order = 1; // Big Endian
565 // Give up. I'll fix this in a later version.
566 die("Required floating point format ".
567 "not supported on this platform.");
569 $this->_byte_order = $byte_order;
573 * General storage function
575 * @param string $data binary data to prepend
578 function _prepend($data)
580 if (strlen($data) > $this->_limit) {
581 $data = $this->_addContinue($data);
583 $this->_data = $data.$this->_data;
584 $this->_datasize += strlen($data);
588 * General storage function
590 * @param string $data binary data to append
593 function _append($data)
595 if (strlen($data) > $this->_limit) {
596 $data = $this->_addContinue($data);
598 $this->_data = $this->_data.$data;
599 $this->_datasize += strlen($data);
603 * Writes Excel BOF record to indicate the beginning of a stream or
604 * sub-stream in the BIFF file.
606 * @param integer $type Type of BIFF file to write: 0x0005 Workbook,
610 function _storeBof($type)
612 $record = 0x0809; // Record identifier
614 // According to the SDK $build and $year should be set to zero.
615 // However, this throws a warning in Excel 5. So, use magic numbers.
616 if ($this->_BIFF_version == 0x0500) {
621 } elseif ($this->_BIFF_version == 0x0600) {
623 $unknown = pack("VV", 0x00000041, 0x00000006); //unknown last 8 bytes for BIFF8
627 $version = $this->_BIFF_version;
629 $header = pack("vv", $record, $length);
630 $data = pack("vvvv", $version, $type, $build, $year);
631 $this->_prepend($header . $data . $unknown);
635 * Writes Excel EOF record to indicate the end of a BIFF stream.
641 $record = 0x000A; // Record identifier
642 $length = 0x0000; // Number of bytes to follow
643 $header = pack("vv", $record, $length);
644 $this->_append($header);
648 * Excel limits the size of BIFF records. In Excel 5 the limit is 2084 bytes. In
649 * Excel 97 the limit is 8228 bytes. Records that are longer than these limits
650 * must be split up into CONTINUE blocks.
652 * This function takes a long BIFF record and inserts CONTINUE records as
655 * @param string $data The original binary data to be written
656 * @return string A very convenient string of continue blocks
659 function _addContinue($data)
661 $limit = $this->_limit;
662 $record = 0x003C; // Record identifier
664 // The first 2080/8224 bytes remain intact. However, we have to change
665 // the length field of the record.
666 $tmp = substr($data, 0, 2).pack("v", $limit-4).substr($data, 4, $limit - 4);
668 $header = pack("vv", $record, $limit); // Headers for continue records
670 // Retrieve chunks of 2080/8224 bytes +4 for the header.
671 $data_length = strlen($data);
672 for ($i = $limit; $i < ($data_length - $limit); $i += $limit) {
674 $tmp .= substr($data, $i, $limit);
677 // Retrieve the last chunk of data
678 $header = pack("vv", $record, strlen($data) - $i);
680 $tmp .= substr($data, $i, strlen($data) - $i);
687 FIXME: change prefixes
689 define("OP_BETWEEN", 0x00);
690 define("OP_NOTBETWEEN", 0x01);
691 define("OP_EQUAL", 0x02);
692 define("OP_NOTEQUAL", 0x03);
693 define("OP_GT", 0x04);
694 define("OP_LT", 0x05);
695 define("OP_GTE", 0x06);
696 define("OP_LTE", 0x07);
699 * Baseclass for generating Excel DV records (validations)
701 * @author Herman Kuiper
702 * @category FileFormats
703 * @package Spreadsheet_Excel_Writer
705 class Spreadsheet_Excel_Writer_Validator
722 * The parser from the workbook. Used to parse validation formulas also
723 * @var Spreadsheet_Excel_Writer_Parser
727 function Spreadsheet_Excel_Writer_Validator(&$parser)
729 $this->_parser = $parser;
730 $this->_type = 0x01; // FIXME: add method for setting datatype
731 $this->_style = 0x00;
732 $this->_fixedList = false;
733 $this->_blank = false;
734 $this->_incell = false;
735 $this->_showprompt = false;
736 $this->_showerror = true;
737 $this->_title_prompt = "\x00";
738 $this->_descr_prompt = "\x00";
739 $this->_title_error = "\x00";
740 $this->_descr_error = "\x00";
741 $this->_operator = 0x00; // default is equal
742 $this->_formula1 = '';
743 $this->_formula2 = '';
746 function setPrompt($promptTitle = "\x00", $promptDescription = "\x00", $showPrompt = true)
748 $this->_showprompt = $showPrompt;
749 $this->_title_prompt = $promptTitle;
750 $this->_descr_prompt = $promptDescription;
753 function setError($errorTitle = "\x00", $errorDescription = "\x00", $showError = true)
755 $this->_showerror = $showError;
756 $this->_title_error = $errorTitle;
757 $this->_descr_error = $errorDescription;
760 function allowBlank()
762 $this->_blank = true;
765 function onInvalidStop()
767 $this->_style = 0x00;
770 function onInvalidWarn()
772 $this->_style = 0x01;
775 function onInvalidInfo()
777 $this->_style = 0x02;
780 function setFormula1($formula)
782 // Parse the formula using the parser in Parser.php
783 $this->_parser->parse($formula);
785 $this->_formula1 = $this->_parser->toReversePolish();
789 function setFormula2($formula)
791 // Parse the formula using the parser in Parser.php
792 $this->_parser->parse($formula);
794 $this->_formula2 = $this->_parser->toReversePolish();
798 function _getOptions()
800 $options = $this->_type;
801 $options |= $this->_style << 3;
802 if ($this->_fixedList) {
808 if (!$this->_incell) {
811 if ($this->_showprompt) {
814 if ($this->_showerror) {
817 $options |= $this->_operator << 20;
824 $title_prompt_len = strlen($this->_title_prompt);
825 $descr_prompt_len = strlen($this->_descr_prompt);
826 $title_error_len = strlen($this->_title_error);
827 $descr_error_len = strlen($this->_descr_error);
829 $formula1_size = strlen($this->_formula1);
830 $formula2_size = strlen($this->_formula2);
832 $data = pack("V", $this->_getOptions());
833 $data .= pack("vC", $title_prompt_len, 0x00) . $this->_title_prompt;
834 $data .= pack("vC", $title_error_len, 0x00) . $this->_title_error;
835 $data .= pack("vC", $descr_prompt_len, 0x00) . $this->_descr_prompt;
836 $data .= pack("vC", $descr_error_len, 0x00) . $this->_descr_error;
838 $data .= pack("vv", $formula1_size, 0x0000) . $this->_formula1;
839 $data .= pack("vv", $formula2_size, 0x0000) . $this->_formula2;
846 * Class for generating Excel XF records (formats)
848 * @author Xavier Noguer <xnoguer@rezebra.com>
849 * @category FileFormats
850 * @package Spreadsheet_Excel_Writer
853 class Spreadsheet_Excel_Writer_Format
856 * The index given by the workbook when creating a new format.
862 * Index to the FONT record.
868 * The font name (ASCII).
874 * Height of font (1/20 of a point)
886 * Bit specifiying if the font is italic.
892 * Index to the cell's color
898 * The text underline property
904 * Bit specifiying if the font has strikeout.
907 var $_font_strikeout;
910 * Bit specifiying if the font has outline.
916 * Bit specifiying if the font has shadow.
922 * 2 bytes specifiying the script type for the font.
928 * Byte specifiying the font family.
934 * Byte specifiying the font charset.
940 * An index (2 bytes) to a FORMAT record (number format).
946 * Bit specifying if formulas are hidden.
952 * Bit specifying if the cell is locked.
958 * The three bits specifying the text horizontal alignment.
964 * Bit specifying if the text is wrapped at the right border.
970 * The three bits specifying the text vertical alignment.
976 * 1 bit, apparently not used.
982 * The two bits specifying the text rotation.
988 * The cell's foreground color.
994 * The cell's background color.
1000 * The cell's background fill pattern.
1006 * Style of the bottom border of the cell
1012 * Color of the bottom border of the cell.
1018 * Style of the top border of the cell
1024 * Color of the top border of the cell.
1030 * Style of the left border of the cell
1036 * Color of the left border of the cell.
1042 * Style of the right border of the cell
1048 * Color of the right border of the cell.
1057 * @param integer $index the XF index for the format.
1058 * @param array $properties array with properties to be set on initialization.
1060 function Spreadsheet_Excel_Writer_Format($BIFF_version, $index = 0, $properties = array())
1062 $this->_xf_index = $index;
1063 $this->_BIFF_version = $BIFF_version;
1064 $this->font_index = 0;
1065 $this->_font_name = 'Arial';
1067 $this->_bold = 0x0190;
1069 $this->_color = 0x7FFF;
1070 $this->_underline = 0;
1071 $this->_font_strikeout = 0;
1072 $this->_font_outline = 0;
1073 $this->_font_shadow = 0;
1074 $this->_font_script = 0;
1075 $this->_font_family = 0;
1076 $this->_font_charset = 0;
1078 $this->_num_format = 0;
1083 $this->_text_h_align = 0;
1084 $this->_text_wrap = 0;
1085 $this->_text_v_align = 2;
1086 $this->_text_justlast = 0;
1087 $this->_rotation = 0;
1089 $this->_fg_color = 0x40;
1090 $this->_bg_color = 0x41;
1092 $this->_pattern = 0;
1100 $this->_bottom_color = 0x40;
1101 $this->_top_color = 0x40;
1102 $this->_left_color = 0x40;
1103 $this->_right_color = 0x40;
1104 $this->_diag_color = 0x40;
1106 // Set properties passed to Spreadsheet_Excel_Writer_Workbook::addFormat()
1107 foreach ($properties as $property => $value)
1109 if (method_exists($this, 'set'.ucwords($property))) {
1110 $method_name = 'set'.ucwords($property);
1111 $this->$method_name($value);
1118 * Generate an Excel BIFF XF record (style or cell).
1120 * @param string $style The type of the XF record ('style' or 'cell').
1121 * @return string The XF record
1123 function getXf($style)
1125 // Set the type of the XF record and some of the attributes.
1126 if ($style == 'style') {
1129 $style = $this->_locked;
1130 $style |= $this->_hidden << 1;
1133 // Flags to indicate if attributes have been set.
1134 $atr_num = ($this->_num_format != 0)?1:0;
1135 $atr_fnt = ($this->font_index != 0)?1:0;
1136 $atr_alc = ($this->_text_wrap)?1:0;
1137 $atr_bdr = ($this->_bottom ||
1141 $atr_pat = (($this->_fg_color != 0x40) ||
1142 ($this->_bg_color != 0x41) ||
1143 $this->_pattern)?1:0;
1144 $atr_prot = $this->_locked | $this->_hidden;
1146 // Zero the default border colour if the border has not been set.
1147 if ($this->_bottom == 0) {
1148 $this->_bottom_color = 0;
1150 if ($this->_top == 0) {
1151 $this->_top_color = 0;
1153 if ($this->_right == 0) {
1154 $this->_right_color = 0;
1156 if ($this->_left == 0) {
1157 $this->_left_color = 0;
1159 if ($this->_diag == 0) {
1160 $this->_diag_color = 0;
1163 $record = 0x00E0; // Record identifier
1164 if ($this->_BIFF_version == 0x0500) {
1165 $length = 0x0010; // Number of bytes to follow
1167 if ($this->_BIFF_version == 0x0600) {
1171 $ifnt = $this->font_index; // Index to FONT record
1172 $ifmt = $this->_num_format; // Index to FORMAT record
1173 if ($this->_BIFF_version == 0x0500) {
1174 $align = $this->_text_h_align; // Alignment
1175 $align |= $this->_text_wrap << 3;
1176 $align |= $this->_text_v_align << 4;
1177 $align |= $this->_text_justlast << 7;
1178 $align |= $this->_rotation << 8;
1179 $align |= $atr_num << 10;
1180 $align |= $atr_fnt << 11;
1181 $align |= $atr_alc << 12;
1182 $align |= $atr_bdr << 13;
1183 $align |= $atr_pat << 14;
1184 $align |= $atr_prot << 15;
1186 $icv = $this->_fg_color; // fg and bg pattern colors
1187 $icv |= $this->_bg_color << 7;
1189 $fill = $this->_pattern; // Fill and border line style
1190 $fill |= $this->_bottom << 6;
1191 $fill |= $this->_bottom_color << 9;
1193 $border1 = $this->_top; // Border line style and color
1194 $border1 |= $this->_left << 3;
1195 $border1 |= $this->_right << 6;
1196 $border1 |= $this->_top_color << 9;
1198 $border2 = $this->_left_color; // Border color
1199 $border2 |= $this->_right_color << 7;
1201 $header = pack("vv", $record, $length);
1202 $data = pack("vvvvvvvv", $ifnt, $ifmt, $style, $align,
1204 $border1, $border2);
1205 } elseif ($this->_BIFF_version == 0x0600) {
1206 $align = $this->_text_h_align; // Alignment
1207 $align |= $this->_text_wrap << 3;
1208 $align |= $this->_text_v_align << 4;
1209 $align |= $this->_text_justlast << 7;
1211 $used_attrib = $atr_num << 2;
1212 $used_attrib |= $atr_fnt << 3;
1213 $used_attrib |= $atr_alc << 4;
1214 $used_attrib |= $atr_bdr << 5;
1215 $used_attrib |= $atr_pat << 6;
1216 $used_attrib |= $atr_prot << 7;
1218 $icv = $this->_fg_color; // fg and bg pattern colors
1219 $icv |= $this->_bg_color << 7;
1221 $border1 = $this->_left; // Border line style and color
1222 $border1 |= $this->_right << 4;
1223 $border1 |= $this->_top << 8;
1224 $border1 |= $this->_bottom << 12;
1225 $border1 |= $this->_left_color << 16;
1226 $border1 |= $this->_right_color << 23;
1227 $diag_tl_to_rb = 0; // FIXME: add method
1228 $diag_tr_to_lb = 0; // FIXME: add method
1229 $border1 |= $diag_tl_to_rb << 30;
1230 $border1 |= $diag_tr_to_lb << 31;
1232 $border2 = $this->_top_color; // Border color
1233 $border2 |= $this->_bottom_color << 7;
1234 $border2 |= $this->_diag_color << 14;
1235 $border2 |= $this->_diag << 21;
1236 $border2 |= $this->_pattern << 26;
1238 $header = pack("vv", $record, $length);
1241 $biff8_options = 0x00;
1242 $data = pack("vvvC", $ifnt, $ifmt, $style, $align);
1243 $data .= pack("CCC", $rotation, $biff8_options, $used_attrib);
1244 $data .= pack("VVv", $border1, $border2, $icv);
1247 return($header . $data);
1251 * Generate an Excel BIFF FONT record.
1253 * @return string The FONT record
1257 $dyHeight = $this->_size * 20; // Height of font (1/20 of a point)
1258 $icv = $this->_color; // Index to color palette
1259 $bls = $this->_bold; // Bold style
1260 $sss = $this->_font_script; // Superscript/subscript
1261 $uls = $this->_underline; // Underline
1262 $bFamily = $this->_font_family; // Font family
1263 $bCharSet = $this->_font_charset; // Character set
1264 $encoding = 0; // TODO: Unicode support
1266 $cch = strlen($this->_font_name); // Length of font name
1267 $record = 0x31; // Record identifier
1268 if ($this->_BIFF_version == 0x0500) {
1269 $length = 0x0F + $cch; // Record length
1270 } elseif ($this->_BIFF_version == 0x0600) {
1271 $length = 0x10 + $cch;
1273 $reserved = 0x00; // Reserved
1274 $grbit = 0x00; // Font attributes
1275 if ($this->_italic) {
1278 if ($this->_font_strikeout) {
1281 if ($this->_font_outline) {
1284 if ($this->_font_shadow) {
1288 $header = pack("vv", $record, $length);
1289 if ($this->_BIFF_version == 0x0500) {
1290 $data = pack("vvvvvCCCCC", $dyHeight, $grbit, $icv, $bls,
1291 $sss, $uls, $bFamily,
1292 $bCharSet, $reserved, $cch);
1293 } elseif ($this->_BIFF_version == 0x0600) {
1294 $data = pack("vvvvvCCCCCC", $dyHeight, $grbit, $icv, $bls,
1295 $sss, $uls, $bFamily,
1296 $bCharSet, $reserved, $cch, $encoding);
1298 return($header . $data . $this->_font_name);
1302 * Returns a unique hash key for a font.
1303 * Used by Spreadsheet_Excel_Writer_Workbook::_storeAllFonts()
1305 * The elements that form the key are arranged to increase the probability of
1306 * generating a unique key. Elements that hold a large range of numbers
1307 * (eg. _color) are placed between two binary elements such as _italic
1309 * @return string A key for this font
1311 function getFontKey()
1313 $key = "$this->_font_name$this->_size";
1314 $key .= "$this->_font_script$this->_underline";
1315 $key .= "$this->_font_strikeout$this->_bold$this->_font_outline";
1316 $key .= "$this->_font_family$this->_font_charset";
1317 $key .= "$this->_font_shadow$this->_color$this->_italic";
1318 $key = str_replace(' ', '_', $key);
1323 * Returns the index used by Spreadsheet_Excel_Writer_Worksheet::_XF()
1325 * @return integer The index for the XF record
1327 function getXfIndex()
1329 return($this->_xf_index);
1333 * Used in conjunction with the set_xxx_color methods to convert a color
1334 * string into a number. Color range is 0..63 but we will restrict it
1335 * to 8..63 to comply with Gnumeric. Colors 0..7 are repeated in 8..15.
1338 * @param string $name_color name of the color (i.e.: 'blue', 'red', etc..). Optional.
1339 * @return integer The color index
1341 function _getColor($name_color = '')
1364 // Return the default color, 0x7FFF, if undef,
1365 if ($name_color == '') {
1369 // or the color string converted to an integer,
1370 if (isset($colors[$name_color])) {
1371 return($colors[$name_color]);
1374 // or the default color if string is unrecognised,
1375 if (preg_match("/\D/",$name_color)) {
1379 // or an index < 8 mapped into the correct range,
1380 if ($name_color < 8) {
1381 return($name_color + 8);
1384 // or the default color if arg is outside range,
1385 if ($name_color > 63) {
1389 // or an integer in the valid range
1390 return($name_color);
1394 * Set cell alignment.
1397 * @param string $location alignment for the cell ('left', 'right', etc...).
1399 function setAlign($location)
1401 if (preg_match("/\d/",$location)) {
1402 return; // Ignore numbers
1405 $location = strtolower($location);
1407 if ($location == 'left') {
1408 $this->_text_h_align = 1;
1410 if ($location == 'centre') {
1411 $this->_text_h_align = 2;
1413 if ($location == 'center') {
1414 $this->_text_h_align = 2;
1416 if ($location == 'right') {
1417 $this->_text_h_align = 3;
1419 if ($location == 'fill') {
1420 $this->_text_h_align = 4;
1422 if ($location == 'justify') {
1423 $this->_text_h_align = 5;
1425 if ($location == 'merge') {
1426 $this->_text_h_align = 6;
1428 if ($location == 'equal_space') { // For T.K.
1429 $this->_text_h_align = 7;
1431 if ($location == 'top') {
1432 $this->_text_v_align = 0;
1434 if ($location == 'vcentre') {
1435 $this->_text_v_align = 1;
1437 if ($location == 'vcenter') {
1438 $this->_text_v_align = 1;
1440 if ($location == 'bottom') {
1441 $this->_text_v_align = 2;
1443 if ($location == 'vjustify') {
1444 $this->_text_v_align = 3;
1446 if ($location == 'vequal_space') { // For T.K.
1447 $this->_text_v_align = 4;
1452 * Set cell horizontal alignment.
1455 * @param string $location alignment for the cell ('left', 'right', etc...).
1457 function setHAlign($location)
1459 if (preg_match("/\d/",$location)) {
1460 return; // Ignore numbers
1463 $location = strtolower($location);
1465 if ($location == 'left') {
1466 $this->_text_h_align = 1;
1468 if ($location == 'centre') {
1469 $this->_text_h_align = 2;
1471 if ($location == 'center') {
1472 $this->_text_h_align = 2;
1474 if ($location == 'right') {
1475 $this->_text_h_align = 3;
1477 if ($location == 'fill') {
1478 $this->_text_h_align = 4;
1480 if ($location == 'justify') {
1481 $this->_text_h_align = 5;
1483 if ($location == 'merge') {
1484 $this->_text_h_align = 6;
1486 if ($location == 'equal_space') { // For T.K.
1487 $this->_text_h_align = 7;
1492 * Set cell vertical alignment.
1495 * @param string $location alignment for the cell ('top', 'vleft', 'vright', etc...).
1497 function setVAlign($location)
1499 if (preg_match("/\d/",$location)) {
1500 return; // Ignore numbers
1503 $location = strtolower($location);
1505 if ($location == 'top') {
1506 $this->_text_v_align = 0;
1508 if ($location == 'vcentre') {
1509 $this->_text_v_align = 1;
1511 if ($location == 'vcenter') {
1512 $this->_text_v_align = 1;
1514 if ($location == 'bottom') {
1515 $this->_text_v_align = 2;
1517 if ($location == 'vjustify') {
1518 $this->_text_v_align = 3;
1520 if ($location == 'vequal_space') { // For T.K.
1521 $this->_text_v_align = 4;
1526 * This is an alias for the unintuitive setAlign('merge')
1532 $this->setAlign('merge');
1536 * Sets the boldness of the text.
1537 * Bold has a range 100..1000.
1538 * 0 (400) is normal. 1 (700) is bold.
1541 * @param integer $weight Weight for the text, 0 maps to 400 (normal text),
1542 1 maps to 700 (bold text). Valid range is: 100-1000.
1543 It's Optional, default is 1 (bold).
1545 function setBold($weight = 1)
1548 $weight = 0x2BC; // Bold text
1551 $weight = 0x190; // Normal text
1553 if ($weight < 0x064) {
1554 $weight = 0x190; // Lower bound
1556 if ($weight > 0x3E8) {
1557 $weight = 0x190; // Upper bound
1559 $this->_bold = $weight;
1563 /************************************
1564 * FUNCTIONS FOR SETTING CELLS BORDERS
1568 * Sets the width for the bottom border of the cell
1571 * @param integer $style style of the cell border. 1 => thin, 2 => thick.
1573 function setBottom($style)
1575 $this->_bottom = $style;
1579 * Sets the width for the top border of the cell
1582 * @param integer $style style of the cell top border. 1 => thin, 2 => thick.
1584 function setTop($style)
1586 $this->_top = $style;
1590 * Sets the width for the left border of the cell
1593 * @param integer $style style of the cell left border. 1 => thin, 2 => thick.
1595 function setLeft($style)
1597 $this->_left = $style;
1601 * Sets the width for the right border of the cell
1604 * @param integer $style style of the cell right border. 1 => thin, 2 => thick.
1606 function setRight($style)
1608 $this->_right = $style;
1613 * Set cells borders to the same style
1616 * @param integer $style style to apply for all cell borders. 1 => thin, 2 => thick.
1618 function setBorder($style)
1620 $this->setBottom($style);
1621 $this->setTop($style);
1622 $this->setLeft($style);
1623 $this->setRight($style);
1627 /*******************************************
1628 * FUNCTIONS FOR SETTING CELLS BORDERS COLORS
1632 * Sets all the cell's borders to the same color
1635 * @param mixed $color The color we are setting. Either a string (like 'blue'),
1636 * or an integer (range is [8...63]).
1638 function setBorderColor($color)
1640 $this->setBottomColor($color);
1641 $this->setTopColor($color);
1642 $this->setLeftColor($color);
1643 $this->setRightColor($color);
1647 * Sets the cell's bottom border color
1650 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
1652 function setBottomColor($color)
1654 $value = $this->_getColor($color);
1655 $this->_bottom_color = $value;
1659 * Sets the cell's top border color
1662 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
1664 function setTopColor($color)
1666 $value = $this->_getColor($color);
1667 $this->_top_color = $value;
1671 * Sets the cell's left border color
1674 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
1676 function setLeftColor($color)
1678 $value = $this->_getColor($color);
1679 $this->_left_color = $value;
1683 * Sets the cell's right border color
1686 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
1688 function setRightColor($color)
1690 $value = $this->_getColor($color);
1691 $this->_right_color = $value;
1696 * Sets the cell's foreground color
1699 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
1701 function setFgColor($color)
1703 $value = $this->_getColor($color);
1704 $this->_fg_color = $value;
1705 if ($this->_pattern == 0) { // force color to be seen
1706 $this->_pattern = 1;
1711 * Sets the cell's background color
1714 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
1716 function setBgColor($color)
1718 $value = $this->_getColor($color);
1719 $this->_bg_color = $value;
1720 if ($this->_pattern == 0) { // force color to be seen
1721 $this->_pattern = 1;
1726 * Sets the cell's color
1729 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
1731 function setColor($color)
1733 $value = $this->_getColor($color);
1734 $this->_color = $value;
1738 * Sets the fill pattern attribute of a cell
1741 * @param integer $arg Optional. Defaults to 1. Meaningful values are: 0-18,
1742 * 0 meaning no background.
1744 function setPattern($arg = 1)
1746 $this->_pattern = $arg;
1750 * Sets the underline of the text
1753 * @param integer $underline The value for underline. Possible values are:
1754 * 1 => underline, 2 => double underline.
1756 function setUnderline($underline)
1758 $this->_underline = $underline;
1762 * Sets the font style as italic
1766 function setItalic()
1772 * Sets the font size
1775 * @param integer $size The font size (in pixels I think).
1777 function setSize($size)
1779 $this->_size = $size;
1783 * Sets text wrapping
1787 function setTextWrap()
1789 $this->_text_wrap = 1;
1793 * Sets the orientation of the text
1796 * @param integer $angle The rotation angle for the text (clockwise). Possible
1797 values are: 0, 90, 270 and -1 for stacking top-to-bottom.
1799 function setTextRotation($angle)
1804 $this->_rotation = 0;
1807 $this->_rotation = 3;
1810 $this->_rotation = 2;
1813 $this->_rotation = 1;
1816 $this->_rotation = 0;
1822 * Sets the numeric format.
1823 * It can be date, time, currency, etc...
1826 * @param integer $num_format The numeric format.
1828 function setNumFormat($num_format)
1830 $this->_num_format = $num_format;
1834 * Sets font as strikeout.
1838 function setStrikeOut()
1840 $this->_font_strikeout = 1;
1844 * Sets outlining for a font.
1848 function setOutLine()
1850 $this->_font_outline = 1;
1854 * Sets font as shadow.
1858 function setShadow()
1860 $this->_font_shadow = 1;
1864 * Sets the script type of the text
1867 * @param integer $script The value for script type. Possible values are:
1868 * 1 => superscript, 2 => subscript.
1870 function setScript($script)
1872 $this->_font_script = $script;
1880 function setLocked()
1886 * Unlocks a cell. Useful for unprotecting particular cells of a protected sheet.
1890 function setUnLocked()
1896 * Sets the font family name.
1899 * @param string $fontfamily The font family name. Possible values are:
1900 * 'Times New Roman', 'Arial', 'Courier'.
1902 function setFontFamily($font_family)
1904 $this->_font_name = $font_family;
1909 * Class for parsing Excel formulas
1911 * @author Xavier Noguer <xnoguer@rezebra.com>
1912 * @category FileFormats
1913 * @package Spreadsheet_Excel_Writer
1916 class Spreadsheet_Excel_Writer_Parser
1919 * The index of the character we are currently looking at
1925 * The token we are working on.
1928 var $_current_token;
1931 * The formula to parse
1937 * The character ahead of the current char
1943 * The parse tree to be generated
1949 * The byte order. 1 => big endian, 0 => little endian.
1955 * Array of external sheets
1961 * Array of sheet references in the form of REF structures
1967 * The BIFF version for the workbook
1973 * The class constructor
1975 * @param integer $byte_order The byte order (Little endian or Big endian) of the architecture
1976 (optional). 1 => big endian, 0 (default) little endian.
1978 function Spreadsheet_Excel_Writer_Parser($byte_order, $biff_version)
1980 $this->_current_char = 0;
1981 $this->_BIFF_version = $biff_version;
1982 $this->_current_token = ''; // The token we are working on.
1983 $this->_formula = ''; // The formula to parse.
1984 $this->_lookahead = ''; // The character ahead of the current char.
1985 $this->_parse_tree = ''; // The parse tree to be generated.
1986 $this->_initializeHashes(); // Initialize the hashes: ptg's and function's ptg's
1987 $this->_byte_order = $byte_order; // Little Endian or Big Endian
1988 $this->_ext_sheets = array();
1989 $this->_references = array();
1993 * Initialize the ptg and function hashes.
1997 function _initializeHashes()
1999 // The Excel ptg indices
2008 'ptgConcat' => 0x08,
2019 'ptgUminus' => 0x13,
2020 'ptgPercent' => 0x14,
2022 'ptgMissArg' => 0x16,
2026 'ptgEndSheet' => 0x1B,
2033 'ptgFuncVar' => 0x22,
2037 'ptgMemArea' => 0x26,
2038 'ptgMemErr' => 0x27,
2039 'ptgMemNoMem' => 0x28,
2040 'ptgMemFunc' => 0x29,
2041 'ptgRefErr' => 0x2A,
2042 'ptgAreaErr' => 0x2B,
2045 'ptgMemAreaN' => 0x2E,
2046 'ptgMemNoMemN' => 0x2F,
2049 'ptgArea3d' => 0x3B,
2050 'ptgRefErr3d' => 0x3C,
2051 'ptgAreaErr3d' => 0x3D,
2052 'ptgArrayV' => 0x40,
2054 'ptgFuncVarV' => 0x42,
2058 'ptgMemAreaV' => 0x46,
2059 'ptgMemErrV' => 0x47,
2060 'ptgMemNoMemV' => 0x48,
2061 'ptgMemFuncV' => 0x49,
2062 'ptgRefErrV' => 0x4A,
2063 'ptgAreaErrV' => 0x4B,
2065 'ptgAreaNV' => 0x4D,
2066 'ptgMemAreaNV' => 0x4E,
2067 'ptgMemNoMemN' => 0x4F,
2068 'ptgFuncCEV' => 0x58,
2069 'ptgNameXV' => 0x59,
2070 'ptgRef3dV' => 0x5A,
2071 'ptgArea3dV' => 0x5B,
2072 'ptgRefErr3dV' => 0x5C,
2073 'ptgAreaErr3d' => 0x5D,
2074 'ptgArrayA' => 0x60,
2076 'ptgFuncVarA' => 0x62,
2080 'ptgMemAreaA' => 0x66,
2081 'ptgMemErrA' => 0x67,
2082 'ptgMemNoMemA' => 0x68,
2083 'ptgMemFuncA' => 0x69,
2084 'ptgRefErrA' => 0x6A,
2085 'ptgAreaErrA' => 0x6B,
2087 'ptgAreaNA' => 0x6D,
2088 'ptgMemAreaNA' => 0x6E,
2089 'ptgMemNoMemN' => 0x6F,
2090 'ptgFuncCEA' => 0x78,
2091 'ptgNameXA' => 0x79,
2092 'ptgRef3dA' => 0x7A,
2093 'ptgArea3dA' => 0x7B,
2094 'ptgRefErr3dA' => 0x7C,
2095 'ptgAreaErr3d' => 0x7D
2098 // Thanks to Michael Meeks and Gnumeric for the initial arg values.
2100 // The following hash was generated by "function_locale.pl" in the distro.
2101 // Refer to function_locale.pl for non-English function names.
2103 // The array elements are as follow:
2104 // ptg: The Excel function ptg code.
2105 // args: The number of arguments that the function takes:
2106 // >=0 is a fixed number of arguments.
2107 // -1 is a variable number of arguments.
2108 // class: The reference, value or array class of the function args.
2109 // vol: The function is volatile.
2111 $this->_functions = array(
2112 // function ptg args class vol
2113 'COUNT' => array( 0, -1, 0, 0 ),
2114 'IF' => array( 1, -1, 1, 0 ),
2115 'ISNA' => array( 2, 1, 1, 0 ),
2116 'ISERROR' => array( 3, 1, 1, 0 ),
2117 'SUM' => array( 4, -1, 0, 0 ),
2118 'AVERAGE' => array( 5, -1, 0, 0 ),
2119 'MIN' => array( 6, -1, 0, 0 ),
2120 'MAX' => array( 7, -1, 0, 0 ),
2121 'ROW' => array( 8, -1, 0, 0 ),
2122 'COLUMN' => array( 9, -1, 0, 0 ),
2123 'NA' => array( 10, 0, 0, 0 ),
2124 'NPV' => array( 11, -1, 1, 0 ),
2125 'STDEV' => array( 12, -1, 0, 0 ),
2126 'DOLLAR' => array( 13, -1, 1, 0 ),
2127 'FIXED' => array( 14, -1, 1, 0 ),
2128 'SIN' => array( 15, 1, 1, 0 ),
2129 'COS' => array( 16, 1, 1, 0 ),
2130 'TAN' => array( 17, 1, 1, 0 ),
2131 'ATAN' => array( 18, 1, 1, 0 ),
2132 'PI' => array( 19, 0, 1, 0 ),
2133 'SQRT' => array( 20, 1, 1, 0 ),
2134 'EXP' => array( 21, 1, 1, 0 ),
2135 'LN' => array( 22, 1, 1, 0 ),
2136 'LOG10' => array( 23, 1, 1, 0 ),
2137 'ABS' => array( 24, 1, 1, 0 ),
2138 'INT' => array( 25, 1, 1, 0 ),
2139 'SIGN' => array( 26, 1, 1, 0 ),
2140 'ROUND' => array( 27, 2, 1, 0 ),
2141 'LOOKUP' => array( 28, -1, 0, 0 ),
2142 'INDEX' => array( 29, -1, 0, 1 ),
2143 'REPT' => array( 30, 2, 1, 0 ),
2144 'MID' => array( 31, 3, 1, 0 ),
2145 'LEN' => array( 32, 1, 1, 0 ),
2146 'VALUE' => array( 33, 1, 1, 0 ),
2147 'TRUE' => array( 34, 0, 1, 0 ),
2148 'FALSE' => array( 35, 0, 1, 0 ),
2149 'AND' => array( 36, -1, 0, 0 ),
2150 'OR' => array( 37, -1, 0, 0 ),
2151 'NOT' => array( 38, 1, 1, 0 ),
2152 'MOD' => array( 39, 2, 1, 0 ),
2153 'DCOUNT' => array( 40, 3, 0, 0 ),
2154 'DSUM' => array( 41, 3, 0, 0 ),
2155 'DAVERAGE' => array( 42, 3, 0, 0 ),
2156 'DMIN' => array( 43, 3, 0, 0 ),
2157 'DMAX' => array( 44, 3, 0, 0 ),
2158 'DSTDEV' => array( 45, 3, 0, 0 ),
2159 'VAR' => array( 46, -1, 0, 0 ),
2160 'DVAR' => array( 47, 3, 0, 0 ),
2161 'TEXT' => array( 48, 2, 1, 0 ),
2162 'LINEST' => array( 49, -1, 0, 0 ),
2163 'TREND' => array( 50, -1, 0, 0 ),
2164 'LOGEST' => array( 51, -1, 0, 0 ),
2165 'GROWTH' => array( 52, -1, 0, 0 ),
2166 'PV' => array( 56, -1, 1, 0 ),
2167 'FV' => array( 57, -1, 1, 0 ),
2168 'NPER' => array( 58, -1, 1, 0 ),
2169 'PMT' => array( 59, -1, 1, 0 ),
2170 'RATE' => array( 60, -1, 1, 0 ),
2171 'MIRR' => array( 61, 3, 0, 0 ),
2172 'IRR' => array( 62, -1, 0, 0 ),
2173 'RAND' => array( 63, 0, 1, 1 ),
2174 'MATCH' => array( 64, -1, 0, 0 ),
2175 'DATE' => array( 65, 3, 1, 0 ),
2176 'TIME' => array( 66, 3, 1, 0 ),
2177 'DAY' => array( 67, 1, 1, 0 ),
2178 'MONTH' => array( 68, 1, 1, 0 ),
2179 'YEAR' => array( 69, 1, 1, 0 ),
2180 'WEEKDAY' => array( 70, -1, 1, 0 ),
2181 'HOUR' => array( 71, 1, 1, 0 ),
2182 'MINUTE' => array( 72, 1, 1, 0 ),
2183 'SECOND' => array( 73, 1, 1, 0 ),
2184 'NOW' => array( 74, 0, 1, 1 ),
2185 'AREAS' => array( 75, 1, 0, 1 ),
2186 'ROWS' => array( 76, 1, 0, 1 ),
2187 'COLUMNS' => array( 77, 1, 0, 1 ),
2188 'OFFSET' => array( 78, -1, 0, 1 ),
2189 'SEARCH' => array( 82, -1, 1, 0 ),
2190 'TRANSPOSE' => array( 83, 1, 1, 0 ),
2191 'TYPE' => array( 86, 1, 1, 0 ),
2192 'ATAN2' => array( 97, 2, 1, 0 ),
2193 'ASIN' => array( 98, 1, 1, 0 ),
2194 'ACOS' => array( 99, 1, 1, 0 ),
2195 'CHOOSE' => array( 100, -1, 1, 0 ),
2196 'HLOOKUP' => array( 101, -1, 0, 0 ),
2197 'VLOOKUP' => array( 102, -1, 0, 0 ),
2198 'ISREF' => array( 105, 1, 0, 0 ),
2199 'LOG' => array( 109, -1, 1, 0 ),
2200 'CHAR' => array( 111, 1, 1, 0 ),
2201 'LOWER' => array( 112, 1, 1, 0 ),
2202 'UPPER' => array( 113, 1, 1, 0 ),
2203 'PROPER' => array( 114, 1, 1, 0 ),
2204 'LEFT' => array( 115, -1, 1, 0 ),
2205 'RIGHT' => array( 116, -1, 1, 0 ),
2206 'EXACT' => array( 117, 2, 1, 0 ),
2207 'TRIM' => array( 118, 1, 1, 0 ),
2208 'REPLACE' => array( 119, 4, 1, 0 ),
2209 'SUBSTITUTE' => array( 120, -1, 1, 0 ),
2210 'CODE' => array( 121, 1, 1, 0 ),
2211 'FIND' => array( 124, -1, 1, 0 ),
2212 'CELL' => array( 125, -1, 0, 1 ),
2213 'ISERR' => array( 126, 1, 1, 0 ),
2214 'ISTEXT' => array( 127, 1, 1, 0 ),
2215 'ISNUMBER' => array( 128, 1, 1, 0 ),
2216 'ISBLANK' => array( 129, 1, 1, 0 ),
2217 'T' => array( 130, 1, 0, 0 ),
2218 'N' => array( 131, 1, 0, 0 ),
2219 'DATEVALUE' => array( 140, 1, 1, 0 ),
2220 'TIMEVALUE' => array( 141, 1, 1, 0 ),
2221 'SLN' => array( 142, 3, 1, 0 ),
2222 'SYD' => array( 143, 4, 1, 0 ),
2223 'DDB' => array( 144, -1, 1, 0 ),
2224 'INDIRECT' => array( 148, -1, 1, 1 ),
2225 'CALL' => array( 150, -1, 1, 0 ),
2226 'CLEAN' => array( 162, 1, 1, 0 ),
2227 'MDETERM' => array( 163, 1, 2, 0 ),
2228 'MINVERSE' => array( 164, 1, 2, 0 ),
2229 'MMULT' => array( 165, 2, 2, 0 ),
2230 'IPMT' => array( 167, -1, 1, 0 ),
2231 'PPMT' => array( 168, -1, 1, 0 ),
2232 'COUNTA' => array( 169, -1, 0, 0 ),
2233 'PRODUCT' => array( 183, -1, 0, 0 ),
2234 'FACT' => array( 184, 1, 1, 0 ),
2235 'DPRODUCT' => array( 189, 3, 0, 0 ),
2236 'ISNONTEXT' => array( 190, 1, 1, 0 ),
2237 'STDEVP' => array( 193, -1, 0, 0 ),
2238 'VARP' => array( 194, -1, 0, 0 ),
2239 'DSTDEVP' => array( 195, 3, 0, 0 ),
2240 'DVARP' => array( 196, 3, 0, 0 ),
2241 'TRUNC' => array( 197, -1, 1, 0 ),
2242 'ISLOGICAL' => array( 198, 1, 1, 0 ),
2243 'DCOUNTA' => array( 199, 3, 0, 0 ),
2244 'ROUNDUP' => array( 212, 2, 1, 0 ),
2245 'ROUNDDOWN' => array( 213, 2, 1, 0 ),
2246 'RANK' => array( 216, -1, 0, 0 ),
2247 'ADDRESS' => array( 219, -1, 1, 0 ),
2248 'DAYS360' => array( 220, -1, 1, 0 ),
2249 'TODAY' => array( 221, 0, 1, 1 ),
2250 'VDB' => array( 222, -1, 1, 0 ),
2251 'MEDIAN' => array( 227, -1, 0, 0 ),
2252 'SUMPRODUCT' => array( 228, -1, 2, 0 ),
2253 'SINH' => array( 229, 1, 1, 0 ),
2254 'COSH' => array( 230, 1, 1, 0 ),
2255 'TANH' => array( 231, 1, 1, 0 ),
2256 'ASINH' => array( 232, 1, 1, 0 ),
2257 'ACOSH' => array( 233, 1, 1, 0 ),
2258 'ATANH' => array( 234, 1, 1, 0 ),
2259 'DGET' => array( 235, 3, 0, 0 ),
2260 'INFO' => array( 244, 1, 1, 1 ),
2261 'DB' => array( 247, -1, 1, 0 ),
2262 'FREQUENCY' => array( 252, 2, 0, 0 ),
2263 'ERROR.TYPE' => array( 261, 1, 1, 0 ),
2264 'REGISTER.ID' => array( 267, -1, 1, 0 ),
2265 'AVEDEV' => array( 269, -1, 0, 0 ),
2266 'BETADIST' => array( 270, -1, 1, 0 ),
2267 'GAMMALN' => array( 271, 1, 1, 0 ),
2268 'BETAINV' => array( 272, -1, 1, 0 ),
2269 'BINOMDIST' => array( 273, 4, 1, 0 ),
2270 'CHIDIST' => array( 274, 2, 1, 0 ),
2271 'CHIINV' => array( 275, 2, 1, 0 ),
2272 'COMBIN' => array( 276, 2, 1, 0 ),
2273 'CONFIDENCE' => array( 277, 3, 1, 0 ),
2274 'CRITBINOM' => array( 278, 3, 1, 0 ),
2275 'EVEN' => array( 279, 1, 1, 0 ),
2276 'EXPONDIST' => array( 280, 3, 1, 0 ),
2277 'FDIST' => array( 281, 3, 1, 0 ),
2278 'FINV' => array( 282, 3, 1, 0 ),
2279 'FISHER' => array( 283, 1, 1, 0 ),
2280 'FISHERINV' => array( 284, 1, 1, 0 ),
2281 'FLOOR' => array( 285, 2, 1, 0 ),
2282 'GAMMADIST' => array( 286, 4, 1, 0 ),
2283 'GAMMAINV' => array( 287, 3, 1, 0 ),
2284 'CEILING' => array( 288, 2, 1, 0 ),
2285 'HYPGEOMDIST' => array( 289, 4, 1, 0 ),
2286 'LOGNORMDIST' => array( 290, 3, 1, 0 ),
2287 'LOGINV' => array( 291, 3, 1, 0 ),
2288 'NEGBINOMDIST' => array( 292, 3, 1, 0 ),
2289 'NORMDIST' => array( 293, 4, 1, 0 ),
2290 'NORMSDIST' => array( 294, 1, 1, 0 ),
2291 'NORMINV' => array( 295, 3, 1, 0 ),
2292 'NORMSINV' => array( 296, 1, 1, 0 ),
2293 'STANDARDIZE' => array( 297, 3, 1, 0 ),
2294 'ODD' => array( 298, 1, 1, 0 ),
2295 'PERMUT' => array( 299, 2, 1, 0 ),
2296 'POISSON' => array( 300, 3, 1, 0 ),
2297 'TDIST' => array( 301, 3, 1, 0 ),
2298 'WEIBULL' => array( 302, 4, 1, 0 ),
2299 'SUMXMY2' => array( 303, 2, 2, 0 ),
2300 'SUMX2MY2' => array( 304, 2, 2, 0 ),
2301 'SUMX2PY2' => array( 305, 2, 2, 0 ),
2302 'CHITEST' => array( 306, 2, 2, 0 ),
2303 'CORREL' => array( 307, 2, 2, 0 ),
2304 'COVAR' => array( 308, 2, 2, 0 ),
2305 'FORECAST' => array( 309, 3, 2, 0 ),
2306 'FTEST' => array( 310, 2, 2, 0 ),
2307 'INTERCEPT' => array( 311, 2, 2, 0 ),
2308 'PEARSON' => array( 312, 2, 2, 0 ),
2309 'RSQ' => array( 313, 2, 2, 0 ),
2310 'STEYX' => array( 314, 2, 2, 0 ),
2311 'SLOPE' => array( 315, 2, 2, 0 ),
2312 'TTEST' => array( 316, 4, 2, 0 ),
2313 'PROB' => array( 317, -1, 2, 0 ),
2314 'DEVSQ' => array( 318, -1, 0, 0 ),
2315 'GEOMEAN' => array( 319, -1, 0, 0 ),
2316 'HARMEAN' => array( 320, -1, 0, 0 ),
2317 'SUMSQ' => array( 321, -1, 0, 0 ),
2318 'KURT' => array( 322, -1, 0, 0 ),
2319 'SKEW' => array( 323, -1, 0, 0 ),
2320 'ZTEST' => array( 324, -1, 0, 0 ),
2321 'LARGE' => array( 325, 2, 0, 0 ),
2322 'SMALL' => array( 326, 2, 0, 0 ),
2323 'QUARTILE' => array( 327, 2, 0, 0 ),
2324 'PERCENTILE' => array( 328, 2, 0, 0 ),
2325 'PERCENTRANK' => array( 329, -1, 0, 0 ),
2326 'MODE' => array( 330, -1, 2, 0 ),
2327 'TRIMMEAN' => array( 331, 2, 0, 0 ),
2328 'TINV' => array( 332, 2, 1, 0 ),
2329 'CONCATENATE' => array( 336, -1, 1, 0 ),
2330 'POWER' => array( 337, 2, 1, 0 ),
2331 'RADIANS' => array( 342, 1, 1, 0 ),
2332 'DEGREES' => array( 343, 1, 1, 0 ),
2333 'SUBTOTAL' => array( 344, -1, 0, 0 ),
2334 'SUMIF' => array( 345, -1, 0, 0 ),
2335 'COUNTIF' => array( 346, 2, 0, 0 ),
2336 'COUNTBLANK' => array( 347, 1, 0, 0 ),
2337 'ROMAN' => array( 354, -1, 1, 0 )
2342 * Convert a token to the proper ptg value.
2345 * @param mixed $token The token to convert.
2346 * @return mixed the converted token on success. Die if the token
2349 function _convert($token)
2351 if (preg_match("/^\"[^\"]{0,255}\"$/", $token)) {
2352 return $this->_convertString($token);
2354 } elseif (is_numeric($token)) {
2355 return $this->_convertNumber($token);
2357 // match references like A1 or $A$1
2358 } elseif (preg_match('/^\$?([A-Ia-i]?[A-Za-z])\$?(\d+)$/',$token)) {
2359 return $this->_convertRef2d($token);
2361 // match external references like Sheet1!A1 or Sheet1:Sheet2!A1
2362 } elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z](\d+)$/u",$token)) {
2363 return $this->_convertRef3d($token);
2365 // match external references like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1
2366 } elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z](\d+)$/u",$token)) {
2367 return $this->_convertRef3d($token);
2369 // match ranges like A1:B2
2370 } elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\:(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token)) {
2371 return $this->_convertRange2d($token);
2373 // match ranges like A1..B2
2374 } elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token)) {
2375 return $this->_convertRange2d($token);
2377 // match external ranges like Sheet1!A1 or Sheet1:Sheet2!A1:B2
2378 } elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/u",$token)) {
2379 return $this->_convertRange3d($token);
2381 // match external ranges like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2
2382 } elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/u",$token)) {
2383 return $this->_convertRange3d($token);
2385 // operators (including parentheses)
2386 } elseif (isset($this->ptg[$token])) {
2387 return pack("C", $this->ptg[$token]);
2389 // commented so argument number can be processed correctly. See toReversePolish().
2390 /*elseif (preg_match("/[A-Z0-9\xc0-\xdc\.]+/",$token))
2392 return($this->_convertFunction($token,$this->_func_args));
2395 // if it's an argument, ignore the token (the argument remains)
2396 } elseif ($token == 'arg') {
2399 // TODO: use real error codes
2400 die("Unknown token $token");
2404 * Convert a number token to ptgInt or ptgNum
2407 * @param mixed $num an integer or double for conversion to its ptg value
2409 function _convertNumber($num)
2411 // Integer in the range 0..2**16-1
2412 if ((preg_match("/^\d+$/", $num)) and ($num <= 65535)) {
2413 return pack("Cv", $this->ptg['ptgInt'], $num);
2415 if ($this->_byte_order) { // if it's Big Endian
2416 $num = strrev($num);
2418 return pack("Cd", $this->ptg['ptgNum'], $num);
2423 * Convert a string token to ptgStr
2426 * @param string $string A string for conversion to its ptg value.
2427 * @return mixed the converted token on success. PEAR_Error if the string
2428 * is longer than 255 characters.
2430 function _convertString($string)
2432 // chop away beggining and ending quotes
2433 $string = substr($string, 1, strlen($string) - 2);
2434 if (strlen($string) > 255) {
2435 die("String is too long");
2438 if ($this->_BIFF_version == 0x0500) {
2439 return pack("CC", $this->ptg['ptgStr'], strlen($string)).$string;
2440 } elseif ($this->_BIFF_version == 0x0600) {
2441 $encoding = 0; // TODO: Unicode support
2442 return pack("CCC", $this->ptg['ptgStr'], strlen($string), $encoding).$string;
2447 * Convert a function to a ptgFunc or ptgFuncVarV depending on the number of
2448 * args that it takes.
2451 * @param string $token The name of the function for convertion to ptg value.
2452 * @param integer $num_args The number of arguments the function receives.
2453 * @return string The packed ptg for the function
2455 function _convertFunction($token, $num_args)
2457 $args = $this->_functions[$token][1];
2458 $volatile = $this->_functions[$token][3];
2460 // Fixed number of args eg. TIME($i,$j,$k).
2462 return pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]);
2464 // Variable number of args eg. SUM($i,$j,$k, ..).
2466 return pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]);
2471 * Convert an Excel range such as A1:D4 to a ptgRefV.
2474 * @param string $range An Excel range in the A1:A2 or A1..A2 format.
2476 function _convertRange2d($range)
2478 $class = 2; // as far as I know, this is magick.
2480 // Split the range into 2 cell refs
2481 if (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\:([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) {
2482 list($cell1, $cell2) = preg_split('/:/', $range);
2483 } elseif (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\.\.([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) {
2484 list($cell1, $cell2) = preg_split('/\.\./', $range);
2487 // TODO: use real error codes
2488 die("Unknown range separator");
2491 // Convert the cell references
2492 $cell_array1 = $this->_cellToPackedRowcol($cell1);
2493 list($row1, $col1) = $cell_array1;
2494 $cell_array2 = $this->_cellToPackedRowcol($cell2);
2495 list($row2, $col2) = $cell_array2;
2497 // The ptg value depends on the class of the ptg.
2499 $ptgArea = pack("C", $this->ptg['ptgArea']);
2500 } elseif ($class == 1) {
2501 $ptgArea = pack("C", $this->ptg['ptgAreaV']);
2502 } elseif ($class == 2) {
2503 $ptgArea = pack("C", $this->ptg['ptgAreaA']);
2505 // TODO: use real error codes
2506 die("Unknown class $class");
2508 return $ptgArea . $row1 . $row2 . $col1. $col2;
2512 * Convert an Excel 3d range such as "Sheet1!A1:D4" or "Sheet1:Sheet2!A1:D4" to
2516 * @param string $token An Excel range in the Sheet1!A1:A2 format.
2517 * @return mixed The packed ptgArea3d token on success, PEAR_Error on failure.
2519 function _convertRange3d($token)
2521 $class = 2; // as far as I know, this is magick.
2523 // Split the ref at the ! symbol
2524 list($ext_ref, $range) = preg_split('/!/', $token);
2526 // Convert the external reference part (different for BIFF8)
2527 if ($this->_BIFF_version == 0x0500) {
2528 $ext_ref = $this->_packExtRef($ext_ref);
2529 } elseif ($this->_BIFF_version == 0x0600) {
2530 $ext_ref = $this->_getRefIndex($ext_ref);
2533 // Split the range into 2 cell refs
2534 list($cell1, $cell2) = preg_split('/:/', $range);
2536 // Convert the cell references
2537 if (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/", $cell1)) {
2538 $cell_array1 = $this->_cellToPackedRowcol($cell1);
2539 list($row1, $col1) = $cell_array1;
2540 $cell_array2 = $this->_cellToPackedRowcol($cell2);
2541 list($row2, $col2) = $cell_array2;
2542 } else { // It's a rows range (like 26:27)
2543 $cells_array = $this->_rangeToPackedRange($cell1.':'.$cell2);
2544 list($row1, $col1, $row2, $col2) = $cells_array;
2547 // The ptg value depends on the class of the ptg.
2549 $ptgArea = pack("C", $this->ptg['ptgArea3d']);
2550 } elseif ($class == 1) {
2551 $ptgArea = pack("C", $this->ptg['ptgArea3dV']);
2552 } elseif ($class == 2) {
2553 $ptgArea = pack("C", $this->ptg['ptgArea3dA']);
2555 die("Unknown class $class");
2558 return $ptgArea . $ext_ref . $row1 . $row2 . $col1. $col2;
2562 * Convert an Excel reference such as A1, $B2, C$3 or $D$4 to a ptgRefV.
2565 * @param string $cell An Excel cell reference
2566 * @return string The cell in packed() format with the corresponding ptg
2568 function _convertRef2d($cell)
2570 $class = 2; // as far as I know, this is magick.
2572 // Convert the cell reference
2573 $cell_array = $this->_cellToPackedRowcol($cell);
2574 list($row, $col) = $cell_array;
2576 // The ptg value depends on the class of the ptg.
2578 $ptgRef = pack("C", $this->ptg['ptgRef']);
2579 } elseif ($class == 1) {
2580 $ptgRef = pack("C", $this->ptg['ptgRefV']);
2581 } elseif ($class == 2) {
2582 $ptgRef = pack("C", $this->ptg['ptgRefA']);
2584 // TODO: use real error codes
2585 die("Unknown class $class");
2587 return $ptgRef.$row.$col;
2591 * Convert an Excel 3d reference such as "Sheet1!A1" or "Sheet1:Sheet2!A1" to a
2595 * @param string $cell An Excel cell reference
2596 * @return mixed The packed ptgRef3d token on success, PEAR_Error on failure.
2598 function _convertRef3d($cell)
2600 $class = 2; // as far as I know, this is magick.
2602 // Split the ref at the ! symbol
2603 list($ext_ref, $cell) = preg_split('/!/', $cell);
2605 // Convert the external reference part (different for BIFF8)
2606 if ($this->_BIFF_version == 0x0500) {
2607 $ext_ref = $this->_packExtRef($ext_ref);
2608 } elseif ($this->_BIFF_version == 0x0600) {
2609 $ext_ref = $this->_getRefIndex($ext_ref);
2612 // Convert the cell reference part
2613 list($row, $col) = $this->_cellToPackedRowcol($cell);
2615 // The ptg value depends on the class of the ptg.
2617 $ptgRef = pack("C", $this->ptg['ptgRef3d']);
2618 } elseif ($class == 1) {
2619 $ptgRef = pack("C", $this->ptg['ptgRef3dV']);
2620 } elseif ($class == 2) {
2621 $ptgRef = pack("C", $this->ptg['ptgRef3dA']);
2623 die("Unknown class $class");
2626 return $ptgRef . $ext_ref. $row . $col;
2630 * Convert the sheet name part of an external reference, for example "Sheet1" or
2631 * "Sheet1:Sheet2", to a packed structure.
2634 * @param string $ext_ref The name of the external reference
2635 * @return string The reference index in packed() format
2637 function _packExtRef($ext_ref)
2639 $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any.
2640 $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
2642 // Check if there is a sheet range eg., Sheet1:Sheet2.
2643 if (preg_match("/:/", $ext_ref)) {
2644 list($sheet_name1, $sheet_name2) = preg_split('/:/', $ext_ref);
2646 $sheet1 = $this->_getSheetIndex($sheet_name1);
2647 if ($sheet1 == -1) {
2648 die("Unknown sheet name $sheet_name1 in formula");
2650 $sheet2 = $this->_getSheetIndex($sheet_name2);
2651 if ($sheet2 == -1) {
2652 die("Unknown sheet name $sheet_name2 in formula");
2655 // Reverse max and min sheet numbers if necessary
2656 if ($sheet1 > $sheet2) {
2657 list($sheet1, $sheet2) = array($sheet2, $sheet1);
2659 } else { // Single sheet name only.
2660 $sheet1 = $this->_getSheetIndex($ext_ref);
2661 if ($sheet1 == -1) {
2662 die("Unknown sheet name $ext_ref in formula");
2667 // References are stored relative to 0xFFFF.
2668 $offset = -1 - $sheet1;
2670 return pack('vdvv', $offset, 0x00, $sheet1, $sheet2);
2674 * Look up the REF index that corresponds to an external sheet name
2675 * (or range). If it doesn't exist yet add it to the workbook's references
2676 * array. It assumes all sheet names given must exist.
2679 * @param string $ext_ref The name of the external reference
2680 * @return mixed The reference index in packed() format on success,
2681 * PEAR_Error on failure
2683 function _getRefIndex($ext_ref)
2685 $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any.
2686 $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
2688 // Check if there is a sheet range eg., Sheet1:Sheet2.
2689 if (preg_match("/:/", $ext_ref)) {
2690 list($sheet_name1, $sheet_name2) = preg_split('/:/', $ext_ref);
2692 $sheet1 = $this->_getSheetIndex($sheet_name1);
2693 if ($sheet1 == -1) {
2694 die("Unknown sheet name $sheet_name1 in formula");
2696 $sheet2 = $this->_getSheetIndex($sheet_name2);
2697 if ($sheet2 == -1) {
2698 die("Unknown sheet name $sheet_name2 in formula");
2701 // Reverse max and min sheet numbers if necessary
2702 if ($sheet1 > $sheet2) {
2703 list($sheet1, $sheet2) = array($sheet2, $sheet1);
2705 } else { // Single sheet name only.
2706 $sheet1 = $this->_getSheetIndex($ext_ref);
2707 if ($sheet1 == -1) {
2708 die("Unknown sheet name $ext_ref in formula");
2713 // assume all references belong to this document
2714 $supbook_index = 0x00;
2715 $ref = pack('vvv', $supbook_index, $sheet1, $sheet2);
2716 $total_references = count($this->_references);
2718 for ($i = 0; $i < $total_references; $i++) {
2719 if ($ref == $this->_references[$i]) {
2724 // if REF was not found add it to references array
2726 $this->_references[$total_references] = $ref;
2727 $index = $total_references;
2730 return pack('v', $index);
2734 * Look up the index that corresponds to an external sheet name. The hash of
2735 * sheet names is updated by the addworksheet() method of the
2736 * Spreadsheet_Excel_Writer_Workbook class.
2739 * @return integer The sheet index, -1 if the sheet was not found
2741 function _getSheetIndex($sheet_name)
2743 if (!isset($this->_ext_sheets[$sheet_name])) {
2746 return $this->_ext_sheets[$sheet_name];
2751 * This method is used to update the array of sheet names. It is
2752 * called by the addWorksheet() method of the
2753 * Spreadsheet_Excel_Writer_Workbook class.
2756 * @see Spreadsheet_Excel_Writer_Workbook::addWorksheet()
2757 * @param string $name The name of the worksheet being added
2758 * @param integer $index The index of the worksheet being added
2760 function setExtSheet($name, $index)
2762 $this->_ext_sheets[$name] = $index;
2766 * pack() row and column into the required 3 or 4 byte format.
2769 * @param string $cell The Excel cell reference to be packed
2770 * @return array Array containing the row and column in packed() format
2772 function _cellToPackedRowcol($cell)
2774 $cell = strtoupper($cell);
2775 list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
2777 die("Column in: $cell greater than 255");
2779 // FIXME: change for BIFF8
2780 if ($row >= 16384) {
2781 die("Row in: $cell greater than 16384 ");
2784 // Set the high bits to indicate if row or col are relative.
2785 if ($this->_BIFF_version == 0x0500) {
2786 $row |= $col_rel << 14;
2787 $row |= $row_rel << 15;
2788 $col = pack('C', $col);
2789 } elseif ($this->_BIFF_version == 0x0600) {
2790 $col |= $col_rel << 14;
2791 $col |= $row_rel << 15;
2792 $col = pack('v', $col);
2794 $row = pack('v', $row);
2796 return array($row, $col);
2800 * pack() row range into the required 3 or 4 byte format.
2801 * Just using maximum col/rows, which is probably not the correct solution
2804 * @param string $range The Excel range to be packed
2805 * @return array Array containing (row1,col1,row2,col2) in packed() format
2807 function _rangeToPackedRange($range)
2809 preg_match('/(\$)?(\d+)\:(\$)?(\d+)/', $range, $match);
2810 // return absolute rows if there is a $ in the ref
2811 $row1_rel = empty($match[1]) ? 1 : 0;
2813 $row2_rel = empty($match[3]) ? 1 : 0;
2815 // Convert 1-index to zero-index
2818 // Trick poor inocent Excel
2820 $col2 = 16383; // FIXME: maximum possible value for Excel 5 (change this!!!)
2822 // FIXME: this changes for BIFF8
2823 if (($row1 >= 16384) or ($row2 >= 16384)) {
2824 die("Row in: $range greater than 16384 ");
2827 // Set the high bits to indicate if rows are relative.
2828 if ($this->_BIFF_version == 0x0500) {
2829 $row1 |= $row1_rel << 14; // FIXME: probably a bug
2830 $row2 |= $row2_rel << 15;
2831 $col1 = pack('C', $col1);
2832 $col2 = pack('C', $col2);
2833 } elseif ($this->_BIFF_version == 0x0600) {
2834 $col1 |= $row1_rel << 15;
2835 $col2 |= $row2_rel << 15;
2836 $col1 = pack('v', $col1);
2837 $col2 = pack('v', $col2);
2839 $row1 = pack('v', $row1);
2840 $row2 = pack('v', $row2);
2842 return array($row1, $col1, $row2, $col2);
2846 * Convert an Excel cell reference such as A1 or $B2 or C$3 or $D$4 to a zero
2847 * indexed row and column number. Also returns two (0,1) values to indicate
2848 * whether the row or column are relative references.
2851 * @param string $cell The Excel cell reference in A1 format.
2854 function _cellToRowcol($cell)
2856 preg_match('/(\$)?([A-I]?[A-Z])(\$)?(\d+)/',$cell,$match);
2857 // return absolute column if there is a $ in the ref
2858 $col_rel = empty($match[1]) ? 1 : 0;
2859 $col_ref = $match[2];
2860 $row_rel = empty($match[3]) ? 1 : 0;
2863 // Convert base26 column string to a number.
2864 $expn = strlen($col_ref) - 1;
2866 $col_ref_length = strlen($col_ref);
2867 for ($i = 0; $i < $col_ref_length; $i++) {
2868 $col += (ord($col_ref{$i}) - ord('A') + 1) * pow(26, $expn);
2872 // Convert 1-index to zero-index
2876 return array($row, $col, $row_rel, $col_rel);
2880 * Advance to the next valid token.
2886 $i = $this->_current_char;
2887 $formula_length = strlen($this->_formula);
2888 // eat up white spaces
2889 if ($i < $formula_length) {
2890 while ($this->_formula{$i} == " ") {
2894 if ($i < ($formula_length - 1)) {
2895 $this->_lookahead = $this->_formula{$i+1};
2900 while ($i < $formula_length) {
2901 $token .= $this->_formula{$i};
2902 if ($i < ($formula_length - 1)) {
2903 $this->_lookahead = $this->_formula{$i+1};
2905 $this->_lookahead = '';
2908 if ($this->_match($token) != '') {
2909 //if ($i < strlen($this->_formula) - 1) {
2910 // $this->_lookahead = $this->_formula{$i+1};
2912 $this->_current_char = $i + 1;
2913 $this->_current_token = $token;
2917 if ($i < ($formula_length - 2)) {
2918 $this->_lookahead = $this->_formula{$i+2};
2919 } else { // if we run out of characters _lookahead becomes empty
2920 $this->_lookahead = '';
2924 //die("Lexical error ".$this->_current_char);
2928 * Checks if it's a valid token.
2931 * @param mixed $token The token to check.
2932 * @return mixed The checked token or false on failure
2934 function _match($token)
2937 case SPREADSHEET_EXCEL_WRITER_ADD:
2940 case SPREADSHEET_EXCEL_WRITER_SUB:
2943 case SPREADSHEET_EXCEL_WRITER_MUL:
2946 case SPREADSHEET_EXCEL_WRITER_DIV:
2949 case SPREADSHEET_EXCEL_WRITER_OPEN:
2952 case SPREADSHEET_EXCEL_WRITER_CLOSE:
2955 case SPREADSHEET_EXCEL_WRITER_COMA:
2958 case SPREADSHEET_EXCEL_WRITER_SEMICOLON:
2961 case SPREADSHEET_EXCEL_WRITER_GT:
2962 if ($this->_lookahead == '=') { // it's a GE token
2967 case SPREADSHEET_EXCEL_WRITER_LT:
2968 // it's a LE or a NE token
2969 if (($this->_lookahead == '=') or ($this->_lookahead == '>')) {
2974 case SPREADSHEET_EXCEL_WRITER_GE:
2977 case SPREADSHEET_EXCEL_WRITER_LE:
2980 case SPREADSHEET_EXCEL_WRITER_EQ:
2983 case SPREADSHEET_EXCEL_WRITER_NE:
2987 // if it's a reference
2988 if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$token) and
2989 !preg_match("/[0-9]/",$this->_lookahead) and
2990 ($this->_lookahead != ':') and ($this->_lookahead != '.') and
2991 ($this->_lookahead != '!'))
2995 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
2996 elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/u",$token) and
2997 !preg_match("/[0-9]/",$this->_lookahead) and
2998 ($this->_lookahead != ':') and ($this->_lookahead != '.'))
3002 // If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1)
3003 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z][0-9]+$/u",$token) and
3004 !preg_match("/[0-9]/",$this->_lookahead) and
3005 ($this->_lookahead != ':') and ($this->_lookahead != '.'))
3009 // if it's a range (A1:A2)
3010 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and
3011 !preg_match("/[0-9]/",$this->_lookahead))
3015 // if it's a range (A1..A2)
3016 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and
3017 !preg_match("/[0-9]/",$this->_lookahead))
3021 // If it's an external range like Sheet1!A1 or Sheet1:Sheet2!A1:B2
3022 elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$token) and
3023 !preg_match("/[0-9]/",$this->_lookahead))
3027 // If it's an external range like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2
3028 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$token) and
3029 !preg_match("/[0-9]/",$this->_lookahead))
3033 // If it's a number (check that it's not a sheet name or range)
3034 elseif (is_numeric($token) and
3035 (!is_numeric($token.$this->_lookahead) or ($this->_lookahead == '')) and
3036 ($this->_lookahead != '!') and ($this->_lookahead != ':'))
3040 // If it's a string (of maximum 255 characters)
3041 elseif (preg_match("/^\"[^\"]{0,255}\"$/",$token))
3045 // if it's a function call
3046 elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$token) and ($this->_lookahead == "("))
3055 * The parsing method. It parses a formula.
3058 * @param string $formula The formula to parse, without the initial equal
3060 * @return mixed true on success, PEAR_Error on failure
3062 function parse($formula)
3064 $this->_current_char = 0;
3065 $this->_formula = $formula;
3066 $this->_lookahead = $formula{1};
3068 $this->_parse_tree = $this->_condition();
3072 * It parses a condition. It assumes the following rule:
3073 * Cond -> Expr [(">" | "<") Expr]
3076 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3078 function _condition()
3080 $result = $this->_expression();
3081 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LT) {
3083 $result2 = $this->_expression();
3084 $result = $this->_createTree('ptgLT', $result, $result2);
3085 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GT) {
3087 $result2 = $this->_expression();
3088 $result = $this->_createTree('ptgGT', $result, $result2);
3089 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LE) {
3091 $result2 = $this->_expression();
3092 $result = $this->_createTree('ptgLE', $result, $result2);
3093 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GE) {
3095 $result2 = $this->_expression();
3096 $result = $this->_createTree('ptgGE', $result, $result2);
3097 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_EQ) {
3099 $result2 = $this->_expression();
3100 $result = $this->_createTree('ptgEQ', $result, $result2);
3101 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_NE) {
3103 $result2 = $this->_expression();
3104 $result = $this->_createTree('ptgNE', $result, $result2);
3110 * It parses a expression. It assumes the following rule:
3111 * Expr -> Term [("+" | "-") Term]
3116 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3118 function _expression()
3120 // If it's a string return a string node
3121 if (preg_match("/^\"[^\"]{0,255}\"$/", $this->_current_token)) {
3122 $result = $this->_createTree($this->_current_token, '', '');
3125 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB) {
3128 $result2 = $this->_expression();
3129 $result = $this->_createTree('ptgUminus', $result2, '');
3132 $result = $this->_term();
3133 while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) or
3134 ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB)) {
3136 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) {
3138 $result2 = $this->_term();
3139 $result = $this->_createTree('ptgAdd', $result, $result2);
3142 $result2 = $this->_term();
3143 $result = $this->_createTree('ptgSub', $result, $result2);
3150 * This function just introduces a ptgParen element in the tree, so that Excel
3151 * doesn't get confused when working with a parenthesized formula afterwards.
3155 * @return array The parsed ptg'd tree
3157 function _parenthesizedExpression()
3159 $result = $this->_createTree('ptgParen', $this->_expression(), '');
3164 * It parses a term. It assumes the following rule:
3165 * Term -> Fact [("*" | "/") Fact]
3168 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3172 $result = $this->_fact();
3173 while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) or
3174 ($this->_current_token == SPREADSHEET_EXCEL_WRITER_DIV)) {
3176 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) {
3178 $result2 = $this->_fact();
3179 $result = $this->_createTree('ptgMul', $result, $result2);
3182 $result2 = $this->_fact();
3183 $result = $this->_createTree('ptgDiv', $result, $result2);
3190 * It parses a factor. It assumes the following rule:
3198 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3202 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_OPEN) {
3203 $this->_advance(); // eat the "("
3204 $result = $this->_parenthesizedExpression();
3205 if ($this->_current_token != SPREADSHEET_EXCEL_WRITER_CLOSE) {
3206 die("')' token expected.");
3208 $this->_advance(); // eat the ")"
3211 // if it's a reference
3212 if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$this->_current_token))
3214 $result = $this->_createTree($this->_current_token, '', '');
3218 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
3219 elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/u",$this->_current_token))
3221 $result = $this->_createTree($this->_current_token, '', '');
3225 // If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1)
3226 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z][0-9]+$/u",$this->_current_token))
3228 $result = $this->_createTree($this->_current_token, '', '');
3233 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token) or
3234 preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token))
3236 $result = $this->_current_token;
3240 // If it's an external range (Sheet1!A1 or Sheet1!A1:B2)
3241 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))
3243 $result = $this->_current_token;
3247 // If it's an external range ('Sheet1'!A1 or 'Sheet1'!A1:B2)
3248 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))
3250 $result = $this->_current_token;
3254 elseif (is_numeric($this->_current_token))
3256 $result = $this->_createTree($this->_current_token, '', '');
3260 // if it's a function call
3261 elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$this->_current_token))
3263 $result = $this->_func();
3266 die("Syntax error: ".$this->_current_token.
3267 ", lookahead: ".$this->_lookahead.
3268 ", current char: ".$this->_current_char);
3272 * It parses a function call. It assumes the following rule:
3273 * Func -> ( Expr [,Expr]* )
3276 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3280 $num_args = 0; // number of arguments received
3281 $function = strtoupper($this->_current_token);
3282 $result = ''; // initialize result
3284 $this->_advance(); // eat the "("
3285 while ($this->_current_token != ')') {
3287 if ($num_args > 0) {
3288 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_COMA or
3289 $this->_current_token == SPREADSHEET_EXCEL_WRITER_SEMICOLON)
3291 $this->_advance(); // eat the "," or ";"
3293 die("Syntax error: comma expected in ".
3294 "function $function, arg #{$num_args}");
3296 $result2 = $this->_condition();
3297 $result = $this->_createTree('arg', $result, $result2);
3298 } else { // first argument
3299 $result2 = $this->_condition();
3300 $result = $this->_createTree('arg', '', $result2);
3304 if (!isset($this->_functions[$function])) {
3305 die("Function $function() doesn't exist");
3307 $args = $this->_functions[$function][1];
3308 // If fixed number of args eg. TIME($i,$j,$k). Check that the number of args is valid.
3309 if (($args >= 0) and ($args != $num_args)) {
3310 die("Incorrect number of arguments in function $function() ");
3313 $result = $this->_createTree($function, $result, $num_args);
3314 $this->_advance(); // eat the ")"
3319 * Creates a tree. In fact an array which may have one or two arrays (sub-trees)
3323 * @param mixed $value The value of this node.
3324 * @param mixed $left The left array (sub-tree) or a final node.
3325 * @param mixed $right The right array (sub-tree) or a final node.
3326 * @return array A tree
3328 function _createTree($value, $left, $right)
3330 return array('value' => $value, 'left' => $left, 'right' => $right);
3334 * Builds a string containing the tree in reverse polish notation (What you
3335 * would use in a HP calculator stack).
3336 * The following tree:
3344 * The following tree:
3352 * produces: "36A1*+"
3354 * In fact all operands, functions, references, etc... are written as ptg's
3357 * @param array $tree The optional tree to convert.
3358 * @return string The tree in reverse polish notation
3360 function toReversePolish($tree = array())
3362 $polish = ""; // the string we are going to return
3363 if (empty($tree)) { // If it's the first call use _parse_tree
3364 $tree = $this->_parse_tree;
3366 if (is_array($tree['left'])) {
3367 $converted_tree = $this->toReversePolish($tree['left']);
3368 $polish .= $converted_tree;
3369 } elseif ($tree['left'] != '') { // It's a final node
3370 $converted_tree = $this->_convert($tree['left']);
3371 $polish .= $converted_tree;
3373 if (is_array($tree['right'])) {
3374 $converted_tree = $this->toReversePolish($tree['right']);
3375 $polish .= $converted_tree;
3376 } elseif ($tree['right'] != '') { // It's a final node
3377 $converted_tree = $this->_convert($tree['right']);
3378 $polish .= $converted_tree;
3380 // if it's a function convert it here (so we can set it's arguments)
3381 if (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/",$tree['value']) and
3382 !preg_match('/^([A-Ia-i]?[A-Za-z])(\d+)$/',$tree['value']) and
3383 !preg_match("/^[A-Ia-i]?[A-Za-z](\d+)\.\.[A-Ia-i]?[A-Za-z](\d+)$/",$tree['value']) and
3384 !is_numeric($tree['value']) and
3385 !isset($this->ptg[$tree['value']]))
3387 // left subtree for a function is always an array.
3388 if ($tree['left'] != '') {
3389 $left_tree = $this->toReversePolish($tree['left']);
3393 // add it's left subtree and return.
3394 return $left_tree.$this->_convertFunction($tree['value'], $tree['right']);
3396 $converted_tree = $this->_convert($tree['value']);
3398 $polish .= $converted_tree;
3404 * Class for generating Excel Spreadsheets
3406 * @author Xavier Noguer <xnoguer@rezebra.com>
3407 * @category FileFormats
3408 * @package Spreadsheet_Excel_Writer
3411 class Spreadsheet_Excel_Writer_Worksheet extends Spreadsheet_Excel_Writer_BIFFwriter
3414 * Name of the Worksheet
3420 * Index for the Worksheet
3426 * Reference to the (default) Format object for URLs
3427 * @var object Format
3432 * Reference to the parser used for parsing formulas
3433 * @var object Format
3438 * Filehandle to the temporary file for storing data
3444 * Boolean indicating if we are using a temporary file for storing data
3447 var $_using_tmpfile;
3450 * Maximum number of rows for an Excel spreadsheet (BIFF5)
3456 * Maximum number of columns for an Excel spreadsheet (BIFF5)
3462 * Maximum number of characters for a string (LABEL record in BIFF5)
3468 * First row for the DIMENSIONS record
3470 * @see _storeDimensions()
3475 * Last row for the DIMENSIONS record
3477 * @see _storeDimensions()
3482 * First column for the DIMENSIONS record
3484 * @see _storeDimensions()
3489 * Last column for the DIMENSIONS record
3491 * @see _storeDimensions()
3496 * Array containing format information for columns
3502 * Array containing the selected area for the worksheet
3508 * Array containing the panes for the worksheet
3514 * The active pane for the worksheet
3520 * Bit specifying if panes are frozen
3526 * Bit specifying if the worksheet is selected
3532 * The paper size (for printing) (DOCUMENT!!!)
3538 * Bit specifying paper orientation (for printing). 0 => landscape, 1 => portrait
3544 * The page header caption
3550 * The page footer caption
3556 * The horizontal centering value for the page
3562 * The vertical centering value for the page
3568 * The margin for the header
3574 * The margin for the footer
3580 * The left margin for the worksheet in inches
3586 * The right margin for the worksheet in inches
3592 * The top margin for the worksheet in inches
3598 * The bottom margin for the worksheet in inches
3601 var $_margin_bottom;
3604 * First row to reapeat on each printed page
3610 * Last row to reapeat on each printed page
3616 * First column to reapeat on each printed page
3622 * First row of the area to print
3628 * Last row to of the area to print
3634 * First column of the area to print
3640 * Last column of the area to print
3646 * Whether to use outline.
3652 * Auto outline styles.
3655 var $_outline_style;
3658 * Whether to have outline summary below.
3661 var $_outline_below;
3664 * Whether to have outline summary at the right.
3667 var $_outline_right;
3670 * Outline row level.
3673 var $_outline_row_level;
3676 * Whether to fit to page when printing or not.
3682 * Number of pages to fit wide
3688 * Number of pages to fit high
3694 * Reference to the total number of strings in the workbook
3700 * Reference to the number of unique strings in the workbook
3706 * Reference to the array containing all the unique strings in the workbook
3712 * Merged cell ranges
3715 var $_merged_ranges;
3718 * Charset encoding currently used when calling writeString()
3721 var $_input_encoding;
3726 * @param string $name The name of the new worksheet
3727 * @param integer $index The index of the new worksheet
3728 * @param mixed &$activesheet The current activesheet of the workbook we belong to
3729 * @param mixed &$firstsheet The first worksheet in the workbook we belong to
3730 * @param mixed &$url_format The default format for hyperlinks
3731 * @param mixed &$parser The formula parser created for the Workbook
3734 function Spreadsheet_Excel_Writer_Worksheet($BIFF_version, $name,
3735 $index, &$activesheet,
3736 &$firstsheet, &$str_total,
3737 &$str_unique, &$str_table,
3738 &$url_format, &$parser)
3740 // It needs to call its parent's constructor explicitly
3741 $this->Spreadsheet_Excel_Writer_BIFFwriter();
3742 $this->_BIFF_version = $BIFF_version;
3743 $rowmax = 65536; // 16384 in Excel 5
3746 $this->name = $name;
3747 $this->index = $index;
3748 $this->activesheet = &$activesheet;
3749 $this->firstsheet = &$firstsheet;
3750 $this->_str_total = &$str_total;
3751 $this->_str_unique = &$str_unique;
3752 $this->_str_table = &$str_table;
3753 $this->_url_format = &$url_format;
3754 $this->_parser = &$parser;
3756 //$this->ext_sheets = array();
3757 $this->_filehandle = '';
3758 $this->_using_tmpfile = true;
3759 //$this->fileclosed = 0;
3760 //$this->offset = 0;
3761 $this->_xls_rowmax = $rowmax;
3762 $this->_xls_colmax = $colmax;
3763 $this->_xls_strmax = 255;
3764 $this->_dim_rowmin = $rowmax + 1;
3765 $this->_dim_rowmax = 0;
3766 $this->_dim_colmin = $colmax + 1;
3767 $this->_dim_colmax = 0;
3768 $this->_colinfo = array();
3769 $this->_selection = array(0,0,0,0);
3770 $this->_panes = array();
3771 $this->_active_pane = 3;
3773 $this->selected = 0;
3775 $this->_paper_size = 0x0;
3776 $this->_orientation = 0x1;
3777 $this->_header = '';
3778 $this->_footer = '';
3779 $this->_hcenter = 0;
3780 $this->_vcenter = 0;
3781 $this->_margin_head = 0.50;
3782 $this->_margin_foot = 0.50;
3783 $this->_margin_left = 0.75;
3784 $this->_margin_right = 0.75;
3785 $this->_margin_top = 1.00;
3786 $this->_margin_bottom = 1.00;
3788 $this->title_rowmin = null;
3789 $this->title_rowmax = null;
3790 $this->title_colmin = null;
3791 $this->title_colmax = null;
3792 $this->print_rowmin = null;
3793 $this->print_rowmax = null;
3794 $this->print_colmin = null;
3795 $this->print_colmax = null;
3797 $this->_print_gridlines = 1;
3798 $this->_screen_gridlines = 1;
3799 $this->_print_headers = 0;
3801 $this->_fit_page = 0;
3802 $this->_fit_width = 0;
3803 $this->_fit_height = 0;
3805 $this->_hbreaks = array();
3806 $this->_vbreaks = array();
3808 $this->_protect = 0;
3809 $this->_password = null;
3811 $this->col_sizes = array();
3812 $this->_row_sizes = array();
3815 $this->_print_scale = 100;
3817 $this->_outline_row_level = 0;
3818 $this->_outline_style = 0;
3819 $this->_outline_below = 1;
3820 $this->_outline_right = 1;
3821 $this->_outline_on = 1;
3823 $this->_merged_ranges = array();
3825 $this->_rtl = 0; // Added by Joe Hunt 2009-03-05 for arabic languages
3826 $this->_input_encoding = '';
3828 $this->_dv = array();
3830 $this->_initialize();
3834 * Open a tmp file to store the majority of the Worksheet data. If this fails,
3835 * for example due to write permissions, store the data in memory. This can be
3836 * slow for large files.
3840 function _initialize()
3842 // Open tmp file for storing Worksheet data
3846 $this->_filehandle = $fh;
3848 // If tmpfile() fails store data in memory
3849 $this->_using_tmpfile = false;
3854 * Add data to the beginning of the workbook (note the reverse order)
3855 * and to the end of the workbook.
3858 * @see Spreadsheet_Excel_Writer_Workbook::storeWorkbook()
3859 * @param array $sheetnames The array of sheetnames from the Workbook this
3860 * worksheet belongs to
3862 function close($sheetnames)
3864 $num_sheets = count($sheetnames);
3866 /***********************************************
3867 * Prepend in reverse order!!
3870 // Prepend the sheet dimensions
3871 $this->_storeDimensions();
3873 // Prepend the sheet password
3874 $this->_storePassword();
3876 // Prepend the sheet protection
3877 $this->_storeProtect();
3879 // Prepend the page setup
3880 $this->_storeSetup();
3882 /* FIXME: margins are actually appended */
3883 // Prepend the bottom margin
3884 $this->_storeMarginBottom();
3886 // Prepend the top margin
3887 $this->_storeMarginTop();
3889 // Prepend the right margin
3890 $this->_storeMarginRight();
3892 // Prepend the left margin
3893 $this->_storeMarginLeft();
3895 // Prepend the page vertical centering
3896 $this->_storeVcenter();
3898 // Prepend the page horizontal centering
3899 $this->_storeHcenter();
3901 // Prepend the page footer
3902 $this->_storeFooter();
3904 // Prepend the page header
3905 $this->_storeHeader();
3907 // Prepend the vertical page breaks
3908 $this->_storeVbreak();
3910 // Prepend the horizontal page breaks
3911 $this->_storeHbreak();
3914 $this->_storeWsbool();
3917 $this->_storeGridset();
3920 if ($this->_BIFF_version == 0x0500) {
3921 $this->_storeGuts();
3924 // Prepend PRINTGRIDLINES
3925 $this->_storePrintGridlines();
3927 // Prepend PRINTHEADERS
3928 $this->_storePrintHeaders();
3930 // Prepend EXTERNSHEET references
3931 if ($this->_BIFF_version == 0x0500) {
3932 for ($i = $num_sheets; $i > 0; $i--) {
3933 $sheetname = $sheetnames[$i-1];
3934 $this->_storeExternsheet($sheetname);
3938 // Prepend the EXTERNCOUNT of external references.
3939 if ($this->_BIFF_version == 0x0500) {
3940 $this->_storeExterncount($num_sheets);
3943 // Prepend the COLINFO records if they exist
3944 if (!empty($this->_colinfo)) {
3945 $colcount = count($this->_colinfo);
3946 for ($i = 0; $i < $colcount; $i++) {
3947 $this->_storeColinfo($this->_colinfo[$i]);
3949 $this->_storeDefcol();
3952 // Prepend the BOF record
3953 $this->_storeBof(0x0010);
3956 * End of prepend. Read upwards from here.
3957 ***********************************************/
3960 $this->_storeWindow2();
3961 $this->_storeZoom();
3962 if (!empty($this->_panes)) {
3963 $this->_storePanes($this->_panes);
3965 $this->_storeSelection($this->_selection);
3966 $this->_storeMergedCells();
3967 /* TODO: add data validity */
3968 /*if ($this->_BIFF_version == 0x0600) {
3969 $this->_storeDataValidity();
3975 * Retrieve the worksheet name.
3976 * This is usefull when creating worksheets without a name.
3979 * @return string The worksheet's name
3987 * Retrieves data from memory in one chunk, or from disk in $buffer
3990 * @return string The data
3996 // Return data stored in memory
3997 if (isset($this->_data)) {
3998 $tmp = $this->_data;
3999 unset($this->_data);
4000 $fh = $this->_filehandle;
4001 if ($this->_using_tmpfile) {
4006 // Return data stored on disk
4007 if ($this->_using_tmpfile) {
4008 if ($tmp = fread($this->_filehandle, $buffer)) {
4013 // No data to return
4018 * Sets a merged cell range
4021 * @param integer $first_row First row of the area to merge
4022 * @param integer $first_col First column of the area to merge
4023 * @param integer $last_row Last row of the area to merge
4024 * @param integer $last_col Last column of the area to merge
4026 function setMerge($first_row, $first_col, $last_row, $last_col)
4028 if (($last_row < $first_row) || ($last_col < $first_col)) {
4031 // don't check rowmin, rowmax, etc... because we don't know when this
4032 // is going to be called
4033 $this->_merged_ranges[] = array($first_row, $first_col, $last_row, $last_col);
4037 * Set this worksheet as a selected worksheet,
4038 * i.e. the worksheet has its tab highlighted.
4044 $this->selected = 1;
4048 * Set this worksheet as the active worksheet,
4049 * i.e. the worksheet that is displayed when the workbook is opened.
4050 * Also set it as selected.
4056 $this->selected = 1;
4057 $this->activesheet = $this->index;
4061 * Set this worksheet as the first visible sheet.
4062 * This is necessary when there are a large number of worksheets and the
4063 * activated worksheet is not visible on the screen.
4067 function setFirstSheet()
4069 $this->firstsheet = $this->index;
4073 * Set the worksheet protection flag
4074 * to prevent accidental modification and to
4075 * hide formulas if the locked and hidden format properties have been set.
4078 * @param string $password The password to use for protecting the sheet.
4080 function protect($password)
4082 $this->_protect = 1;
4083 $this->_password = $this->_encodePassword($password);
4087 * Set the width of a single column or a range of columns.
4090 * @param integer $firstcol first column on the range
4091 * @param integer $lastcol last column on the range
4092 * @param integer $width width to set
4093 * @param mixed $format The optional XF format to apply to the columns
4094 * @param integer $hidden The optional hidden atribute
4095 * @param integer $level The optional outline level
4097 function setColumn($firstcol, $lastcol, $width, $format = null, $hidden = 0, $level = 0)
4099 $this->_colinfo[] = array($firstcol, $lastcol, $width, &$format, $hidden, $level);
4101 // Set width to zero if column is hidden
4102 $width = ($hidden) ? 0 : $width;
4104 for ($col = $firstcol; $col <= $lastcol; $col++) {
4105 $this->col_sizes[$col] = $width;
4110 * Set which cell or cells are selected in a worksheet
4113 * @param integer $first_row first row in the selected quadrant
4114 * @param integer $first_column first column in the selected quadrant
4115 * @param integer $last_row last row in the selected quadrant
4116 * @param integer $last_column last column in the selected quadrant
4118 function setSelection($first_row,$first_column,$last_row,$last_column)
4120 $this->_selection = array($first_row,$first_column,$last_row,$last_column);
4124 * Set panes and mark them as frozen.
4127 * @param array $panes This is the only parameter received and is composed of the following:
4128 * 0 => Vertical split position,
4129 * 1 => Horizontal split position
4130 * 2 => Top row visible
4131 * 3 => Leftmost column visible
4134 function freezePanes($panes)
4137 $this->_panes = $panes;
4141 * Set panes and mark them as unfrozen.
4144 * @param array $panes This is the only parameter received and is composed of the following:
4145 * 0 => Vertical split position,
4146 * 1 => Horizontal split position
4147 * 2 => Top row visible
4148 * 3 => Leftmost column visible
4151 function thawPanes($panes)
4154 $this->_panes = $panes;
4158 * Set the page orientation as portrait.
4162 function setPortrait()
4164 $this->_orientation = 1;
4168 * Set the page orientation as landscape.
4172 function setLandscape()
4174 $this->_orientation = 0;
4178 * Set the paper type. Ex. 1 = US Letter, 9 = A4
4181 * @param integer $size The type of paper size to use
4183 function setPaper($size = 0)
4185 $this->_paper_size = $size;
4190 * Set the page header caption and optional margin.
4193 * @param string $string The header text
4194 * @param float $margin optional head margin in inches.
4196 function setHeader($string,$margin = 0.50)
4198 if (strlen($string) >= 255) {
4199 //carp 'Header string must be less than 255 characters';
4202 $this->_header = $string;
4203 $this->_margin_head = $margin;
4207 * Set the page footer caption and optional margin.
4210 * @param string $string The footer text
4211 * @param float $margin optional foot margin in inches.
4213 function setFooter($string,$margin = 0.50)
4215 if (strlen($string) >= 255) {
4216 //carp 'Footer string must be less than 255 characters';
4219 $this->_footer = $string;
4220 $this->_margin_foot = $margin;
4224 * Center the page horinzontally.
4227 * @param integer $center the optional value for centering. Defaults to 1 (center).
4229 function centerHorizontally($center = 1)
4231 $this->_hcenter = $center;
4235 * Center the page vertically.
4238 * @param integer $center the optional value for centering. Defaults to 1 (center).
4240 function centerVertically($center = 1)
4242 $this->_vcenter = $center;
4246 * Set all the page margins to the same value in inches.
4249 * @param float $margin The margin to set in inches
4251 function setMargins($margin)
4253 $this->setMarginLeft($margin);
4254 $this->setMarginRight($margin);
4255 $this->setMarginTop($margin);
4256 $this->setMarginBottom($margin);
4260 * Set the left and right margins to the same value in inches.
4263 * @param float $margin The margin to set in inches
4265 function setMargins_LR($margin)
4267 $this->setMarginLeft($margin);
4268 $this->setMarginRight($margin);
4272 * Set the top and bottom margins to the same value in inches.
4275 * @param float $margin The margin to set in inches
4277 function setMargins_TB($margin)
4279 $this->setMarginTop($margin);
4280 $this->setMarginBottom($margin);
4284 * Set the left margin in inches.
4287 * @param float $margin The margin to set in inches
4289 function setMarginLeft($margin = 0.75)
4291 $this->_margin_left = $margin;
4295 * Set the right margin in inches.
4298 * @param float $margin The margin to set in inches
4300 function setMarginRight($margin = 0.75)
4302 $this->_margin_right = $margin;
4306 * Set the top margin in inches.
4309 * @param float $margin The margin to set in inches
4311 function setMarginTop($margin = 1.00)
4313 $this->_margin_top = $margin;
4317 * Set the bottom margin in inches.
4320 * @param float $margin The margin to set in inches
4322 function setMarginBottom($margin = 1.00)
4324 $this->_margin_bottom = $margin;
4328 * Set the rows to repeat at the top of each printed page.
4331 * @param integer $first_row First row to repeat
4332 * @param integer $last_row Last row to repeat. Optional.
4334 function repeatRows($first_row, $last_row = null)
4336 $this->title_rowmin = $first_row;
4337 if (isset($last_row)) { //Second row is optional
4338 $this->title_rowmax = $last_row;
4340 $this->title_rowmax = $first_row;
4345 * Set the columns to repeat at the left hand side of each printed page.
4348 * @param integer $first_col First column to repeat
4349 * @param integer $last_col Last column to repeat. Optional.
4351 function repeatColumns($first_col, $last_col = null)
4353 $this->title_colmin = $first_col;
4354 if (isset($last_col)) { // Second col is optional
4355 $this->title_colmax = $last_col;
4357 $this->title_colmax = $first_col;
4362 * Set the area of each worksheet that will be printed.
4365 * @param integer $first_row First row of the area to print
4366 * @param integer $first_col First column of the area to print
4367 * @param integer $last_row Last row of the area to print
4368 * @param integer $last_col Last column of the area to print
4370 function printArea($first_row, $first_col, $last_row, $last_col)
4372 $this->print_rowmin = $first_row;
4373 $this->print_colmin = $first_col;
4374 $this->print_rowmax = $last_row;
4375 $this->print_colmax = $last_col;
4380 * Set the option to hide gridlines on the printed page.
4384 function hideGridlines()
4386 $this->_print_gridlines = 0;
4390 * Set the option to hide gridlines on the worksheet (as seen on the screen).
4394 function hideScreenGridlines()
4396 $this->_screen_gridlines = 0;
4400 * Set the option to print the row and column headers on the printed page.
4403 * @param integer $print Whether to print the headers or not. Defaults to 1 (print).
4405 function printRowColHeaders($print = 1)
4407 $this->_print_headers = $print;
4411 * Set the vertical and horizontal number of pages that will define the maximum area printed.
4412 * It doesn't seem to work with OpenOffice.
4415 * @param integer $width Maximun width of printed area in pages
4416 * @param integer $height Maximun heigth of printed area in pages
4417 * @see setPrintScale()
4419 function fitToPages($width, $height)
4421 $this->_fit_page = 1;
4422 $this->_fit_width = $width;
4423 $this->_fit_height = $height;
4427 * Store the horizontal page breaks on a worksheet (for printing).
4428 * The breaks represent the row after which the break is inserted.
4431 * @param array $breaks Array containing the horizontal page breaks
4433 function setHPagebreaks($breaks)
4435 foreach ($breaks as $break) {
4436 array_push($this->_hbreaks, $break);
4441 * Store the vertical page breaks on a worksheet (for printing).
4442 * The breaks represent the column after which the break is inserted.
4445 * @param array $breaks Array containing the vertical page breaks
4447 function setVPagebreaks($breaks)
4449 foreach ($breaks as $break) {
4450 array_push($this->_vbreaks, $break);
4456 * Set the worksheet zoom factor.
4459 * @param integer $scale The zoom factor
4461 function setZoom($scale = 100)
4463 // Confine the scale to Excel's range
4464 if ($scale < 10 || $scale > 400) {
4468 $this->_zoom = floor($scale);
4472 * Set the scale factor for the printed page.
4473 * It turns off the "fit to page" option
4476 * @param integer $scale The optional scale factor. Defaults to 100
4478 function setPrintScale($scale = 100)
4480 // Confine the scale to Excel's range
4481 if ($scale < 10 || $scale > 400) {
4485 // Turn off "fit to page" option
4486 $this->_fit_page = 0;
4488 $this->_print_scale = floor($scale);
4492 * Map to the appropriate write method acording to the token recieved.
4495 * @param integer $row The row of the cell we are writing to
4496 * @param integer $col The column of the cell we are writing to
4497 * @param mixed $token What we are writing
4498 * @param mixed $format The optional format to apply to the cell
4500 function write($row, $col, $token, $format = null)
4502 // Check for a cell reference in A1 notation and substitute row and column
4503 /*if ($_[0] =~ /^\D/) {
4504 @_ = $this->_substituteCellref(@_);
4507 if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", $token)) {
4509 return $this->writeNumber($row, $col, $token, $format);
4510 } elseif (preg_match("/^[fh]tt?p:\/\//", $token)) {
4511 // Match http or ftp URL
4512 return $this->writeUrl($row, $col, $token, '', $format);
4513 } elseif (preg_match("/^mailto:/", $token)) {
4515 return $this->writeUrl($row, $col, $token, '', $format);
4516 } elseif (preg_match("/^(?:in|ex)ternal:/", $token)) {
4517 // Match internal or external sheet link
4518 return $this->writeUrl($row, $col, $token, '', $format);
4519 } elseif (preg_match("/^=/", $token)) {
4521 return $this->writeFormula($row, $col, $token, $format);
4522 } elseif (preg_match("/^@/", $token)) {
4524 return $this->writeFormula($row, $col, $token, $format);
4525 } elseif ($token == '') {
4527 return $this->writeBlank($row, $col, $format);
4529 // Default: match string
4530 return $this->writeString($row, $col, $token, $format);
4535 * Write an array of values as a row
4538 * @param integer $row The row we are writing to
4539 * @param integer $col The first col (leftmost col) we are writing to
4540 * @param array $val The array of values to write
4541 * @param mixed $format The optional format to apply to the cell
4542 * @return mixed PEAR_Error on failure
4545 function writeRow($row, $col, $val, $format = null)
4548 if (is_array($val)) {
4549 foreach ($val as $v) {
4551 $this->writeCol($row, $col, $v, $format);
4553 $this->write($row, $col, $v, $format);
4558 die('$val needs to be an array');
4564 * Write an array of values as a column
4567 * @param integer $row The first row (uppermost row) we are writing to
4568 * @param integer $col The col we are writing to
4569 * @param array $val The array of values to write
4570 * @param mixed $format The optional format to apply to the cell
4571 * @return mixed PEAR_Error on failure
4574 function writeCol($row, $col, $val, $format = null)
4577 if (is_array($val)) {
4578 foreach ($val as $v) {
4579 $this->write($row, $col, $v, $format);
4583 die('$val needs to be an array');
4589 * Returns an index to the XF record in the workbook
4592 * @param mixed &$format The optional XF format
4593 * @return integer The XF record index
4595 function _XF(&$format)
4598 return($format->getXfIndex());
4605 /******************************************************************************
4606 *******************************************************************************
4613 * Store Worksheet data in memory using the parent's class append() or to a
4614 * temporary file, the default.
4617 * @param string $data The binary data to append
4619 function _append($data)
4621 if ($this->_using_tmpfile) {
4622 // Add CONTINUE records if necessary
4623 if (strlen($data) > $this->_limit) {
4624 $data = $this->_addContinue($data);
4626 fwrite($this->_filehandle, $data);
4627 $this->_datasize += strlen($data);
4629 parent::_append($data);
4634 * Substitute an Excel cell reference in A1 notation for zero based row and
4635 * column values in an argument list.
4637 * Ex: ("A4", "Hello") is converted to (3, 0, "Hello").
4640 * @param string $cell The cell reference. Or range of cells.
4643 function _substituteCellref($cell)
4645 $cell = strtoupper($cell);
4647 // Convert a column range: 'A:A' or 'B:G'
4648 if (preg_match("/([A-I]?[A-Z]):([A-I]?[A-Z])/", $cell, $match)) {
4649 list($no_use, $col1) = $this->_cellToRowcol($match[1] .'1'); // Add a dummy row
4650 list($no_use, $col2) = $this->_cellToRowcol($match[2] .'1'); // Add a dummy row
4651 return(array($col1, $col2));
4654 // Convert a cell range: 'A1:B7'
4655 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/", $cell, $match)) {
4656 list($row1, $col1) = $this->_cellToRowcol($match[1]);
4657 list($row2, $col2) = $this->_cellToRowcol($match[2]);
4658 return(array($row1, $col1, $row2, $col2));
4661 // Convert a cell reference: 'A1' or 'AD2000'
4662 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+)/", $cell)) {
4663 list($row1, $col1) = $this->_cellToRowcol($match[1]);
4664 return(array($row1, $col1));
4667 // TODO use real error codes
4668 die("Unknown cell reference $cell");
4672 * Convert an Excel cell reference in A1 notation to a zero based row and column
4673 * reference; converts C1 to (0, 2).
4676 * @param string $cell The cell reference.
4677 * @return array containing (row, column)
4679 function _cellToRowcol($cell)
4681 preg_match("/\$?([A-I]?[A-Z])\$?(\d+)/",$cell,$match);
4685 // Convert base26 column string to number
4686 $chars = preg_split('//', $col);
4691 $char = array_pop($chars); // LS char first
4692 $col += (ord($char) -ord('A') +1) * pow(26,$expn);
4696 // Convert 1-index to zero-index
4700 return(array($row, $col));
4704 * Based on the algorithm provided by Daniel Rentz of OpenOffice.
4707 * @param string $plaintext The password to be encoded in plaintext.
4708 * @return string The encoded password
4710 function _encodePassword($plaintext)
4713 $i = 1; // char position
4715 // split the plain text password in its component characters
4716 $chars = preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY);
4717 foreach ($chars as $char) {
4718 $value = ord($char) << $i; // shifted ASCII value
4719 $rotated_bits = $value >> 15; // rotated bits beyond bit 15
4720 $value &= 0x7fff; // first 15 bits
4721 $password ^= ($value | $rotated_bits);
4725 $password ^= strlen($plaintext);
4726 $password ^= 0xCE4B;
4732 * This method sets the properties for outlining and grouping. The defaults
4733 * correspond to Excel's defaults.
4735 * @param bool $visible
4736 * @param bool $symbols_below
4737 * @param bool $symbols_right
4738 * @param bool $auto_style
4740 function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false)
4742 $this->_outline_on = $visible;
4743 $this->_outline_below = $symbols_below;
4744 $this->_outline_right = $symbols_right;
4745 $this->_outline_style = $auto_style;
4747 // Ensure this is a boolean vale for Window2
4748 if ($this->_outline_on) {
4749 $this->_outline_on = 1;
4753 /******************************************************************************
4754 *******************************************************************************
4761 * Write a double to the specified row and column (zero indexed).
4762 * An integer can be written as a double. Excel will display an
4763 * integer. $format is optional.
4765 * Returns 0 : normal termination
4766 * -2 : row or column out of range
4769 * @param integer $row Zero indexed row
4770 * @param integer $col Zero indexed column
4771 * @param float $num The number to write
4772 * @param mixed $format The optional XF format
4775 function writeNumber($row, $col, $num, $format = null)
4777 $record = 0x0203; // Record identifier
4778 $length = 0x000E; // Number of bytes to follow
4780 $xf = $this->_XF($format); // The cell format
4782 // Check that row and col are valid and store max and min values
4783 if ($row >= $this->_xls_rowmax) {
4786 if ($col >= $this->_xls_colmax) {
4789 if ($row < $this->_dim_rowmin) {
4790 $this->_dim_rowmin = $row;
4792 if ($row > $this->_dim_rowmax) {
4793 $this->_dim_rowmax = $row;
4795 if ($col < $this->_dim_colmin) {
4796 $this->_dim_colmin = $col;
4798 if ($col > $this->_dim_colmax) {
4799 $this->_dim_colmax = $col;
4802 $header = pack("vv", $record, $length);
4803 $data = pack("vvv", $row, $col, $xf);
4804 $xl_double = pack("d", $num);
4805 if ($this->_byte_order) { // if it's Big Endian
4806 $xl_double = strrev($xl_double);
4809 $this->_append($header.$data.$xl_double);
4814 * Write a string to the specified row and column (zero indexed).
4815 * NOTE: there is an Excel 5 defined limit of 255 characters.
4816 * $format is optional.
4817 * Returns 0 : normal termination
4818 * -2 : row or column out of range
4819 * -3 : long string truncated to 255 chars
4822 * @param integer $row Zero indexed row
4823 * @param integer $col Zero indexed column
4824 * @param string $str The string to write
4825 * @param mixed $format The XF format for the cell
4828 function writeString($row, $col, $str, $format = null)
4830 if ($this->_BIFF_version == 0x0600) {
4831 return $this->writeStringBIFF8($row, $col, $str, $format);
4833 $strlen = strlen($str);
4834 $record = 0x0204; // Record identifier
4835 $length = 0x0008 + $strlen; // Bytes to follow
4836 $xf = $this->_XF($format); // The cell format
4840 // Check that row and col are valid and store max and min values
4841 if ($row >= $this->_xls_rowmax) {
4844 if ($col >= $this->_xls_colmax) {
4847 if ($row < $this->_dim_rowmin) {
4848 $this->_dim_rowmin = $row;
4850 if ($row > $this->_dim_rowmax) {
4851 $this->_dim_rowmax = $row;
4853 if ($col < $this->_dim_colmin) {
4854 $this->_dim_colmin = $col;
4856 if ($col > $this->_dim_colmax) {
4857 $this->_dim_colmax = $col;
4860 if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars
4861 $str = substr($str, 0, $this->_xls_strmax);
4862 $length = 0x0008 + $this->_xls_strmax;
4863 $strlen = $this->_xls_strmax;
4867 $header = pack("vv", $record, $length);
4868 $data = pack("vvvv", $row, $col, $xf, $strlen);
4869 $this->_append($header . $data . $str);
4874 * Sets Input Encoding for writing strings
4877 * @param string $encoding The encoding. Ex: 'UTF-16LE', 'utf-8', 'ISO-859-7'
4879 function setInputEncoding($encoding)
4881 global $encoding_string;
4882 if ($encoding != 'UTF-16LE' && !function_exists('iconv')) {
4883 die("Using an input encoding other than UTF-16LE requires PHP support for iconv");
4885 $this->_input_encoding = $encoding_string = $encoding;
4888 /** added 2009-03-05 by Joe Hunt, FA for arabic languages */
4895 * Write a string to the specified row and column (zero indexed).
4896 * This is the BIFF8 version (no 255 chars limit).
4897 * $format is optional.
4898 * Returns 0 : normal termination
4899 * -2 : row or column out of range
4900 * -3 : long string truncated to 255 chars
4903 * @param integer $row Zero indexed row
4904 * @param integer $col Zero indexed column
4905 * @param string $str The string to write
4906 * @param mixed $format The XF format for the cell
4909 function writeStringBIFF8($row, $col, $str, $format = null)
4911 if ($this->_input_encoding == 'UTF-16LE')
4913 $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
4916 elseif ($this->_input_encoding != '')
4918 $str = iconv($this->_input_encoding, 'UTF-16LE', $str);
4919 $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
4924 $strlen = strlen($str);
4927 $record = 0x00FD; // Record identifier
4928 $length = 0x000A; // Bytes to follow
4929 $xf = $this->_XF($format); // The cell format
4933 // Check that row and col are valid and store max and min values
4934 if ($this->_checkRowCol($row, $col) == false) {
4938 $str = pack('vC', $strlen, $encoding).$str;
4940 /* check if string is already present */
4941 if (!isset($this->_str_table[$str])) {
4942 $this->_str_table[$str] = $this->_str_unique++;
4944 $this->_str_total++;
4946 $header = pack('vv', $record, $length);
4947 $data = pack('vvvV', $row, $col, $xf, $this->_str_table[$str]);
4948 $this->_append($header.$data);
4953 * Check row and col before writing to a cell, and update the sheet's
4954 * dimensions accordingly
4957 * @param integer $row Zero indexed row
4958 * @param integer $col Zero indexed column
4959 * @return boolean true for success, false if row and/or col are grester
4960 * then maximums allowed.
4962 function _checkRowCol($row, $col)
4964 if ($row >= $this->_xls_rowmax) {
4967 if ($col >= $this->_xls_colmax) {
4970 if ($row < $this->_dim_rowmin) {
4971 $this->_dim_rowmin = $row;
4973 if ($row > $this->_dim_rowmax) {
4974 $this->_dim_rowmax = $row;
4976 if ($col < $this->_dim_colmin) {
4977 $this->_dim_colmin = $col;
4979 if ($col > $this->_dim_colmax) {
4980 $this->_dim_colmax = $col;
4986 * Writes a note associated with the cell given by the row and column.
4987 * NOTE records don't have a length limit.
4990 * @param integer $row Zero indexed row
4991 * @param integer $col Zero indexed column
4992 * @param string $note The note to write
4994 function writeNote($row, $col, $note)
4996 $note_length = strlen($note);
4997 $record = 0x001C; // Record identifier
4998 $max_length = 2048; // Maximun length for a NOTE record
4999 //$length = 0x0006 + $note_length; // Bytes to follow
5001 // Check that row and col are valid and store max and min values
5002 if ($row >= $this->_xls_rowmax) {
5005 if ($col >= $this->_xls_colmax) {
5008 if ($row < $this->_dim_rowmin) {
5009 $this->_dim_rowmin = $row;
5011 if ($row > $this->_dim_rowmax) {
5012 $this->_dim_rowmax = $row;
5014 if ($col < $this->_dim_colmin) {
5015 $this->_dim_colmin = $col;
5017 if ($col > $this->_dim_colmax) {
5018 $this->_dim_colmax = $col;
5021 // Length for this record is no more than 2048 + 6
5022 $length = 0x0006 + min($note_length, 2048);
5023 $header = pack("vv", $record, $length);
5024 $data = pack("vvv", $row, $col, $note_length);
5025 $this->_append($header . $data . substr($note, 0, 2048));
5027 for ($i = $max_length; $i < $note_length; $i += $max_length) {
5028 $chunk = substr($note, $i, $max_length);
5029 $length = 0x0006 + strlen($chunk);
5030 $header = pack("vv", $record, $length);
5031 $data = pack("vvv", -1, 0, strlen($chunk));
5032 $this->_append($header.$data.$chunk);
5038 * Write a blank cell to the specified row and column (zero indexed).
5039 * A blank cell is used to specify formatting without adding a string
5042 * A blank cell without a format serves no purpose. Therefore, we don't write
5043 * a BLANK record unless a format is specified.
5045 * Returns 0 : normal termination (including no format)
5046 * -1 : insufficient number of arguments
5047 * -2 : row or column out of range
5050 * @param integer $row Zero indexed row
5051 * @param integer $col Zero indexed column
5052 * @param mixed $format The XF format
5054 function writeBlank($row, $col, $format)
5056 // Don't write a blank cell unless it has a format
5061 $record = 0x0201; // Record identifier
5062 $length = 0x0006; // Number of bytes to follow
5063 $xf = $this->_XF($format); // The cell format
5065 // Check that row and col are valid and store max and min values
5066 if ($row >= $this->_xls_rowmax) {
5069 if ($col >= $this->_xls_colmax) {
5072 if ($row < $this->_dim_rowmin) {
5073 $this->_dim_rowmin = $row;
5075 if ($row > $this->_dim_rowmax) {
5076 $this->_dim_rowmax = $row;
5078 if ($col < $this->_dim_colmin) {
5079 $this->_dim_colmin = $col;
5081 if ($col > $this->_dim_colmax) {
5082 $this->_dim_colmax = $col;
5085 $header = pack("vv", $record, $length);
5086 $data = pack("vvv", $row, $col, $xf);
5087 $this->_append($header . $data);
5092 * Write a formula to the specified row and column (zero indexed).
5093 * The textual representation of the formula is passed to the parser in
5094 * Parser.php which returns a packed binary string.
5096 * Returns 0 : normal termination
5097 * -1 : formula errors (bad formula)
5098 * -2 : row or column out of range
5101 * @param integer $row Zero indexed row
5102 * @param integer $col Zero indexed column
5103 * @param string $formula The formula text string
5104 * @param mixed $format The optional XF format
5107 function writeFormula($row, $col, $formula, $format = null)
5109 $record = 0x0006; // Record identifier
5111 // Excel normally stores the last calculated value of the formula in $num.
5112 // Clearly we are not in a position to calculate this a priori. Instead
5113 // we set $num to zero and set the option flags in $grbit to ensure
5114 // automatic calculation of the formula when the file is opened.
5116 $xf = $this->_XF($format); // The cell format
5117 $num = 0x00; // Current value of formula
5118 $grbit = 0x03; // Option flags
5119 $unknown = 0x0000; // Must be zero
5122 // Check that row and col are valid and store max and min values
5123 if ($this->_checkRowCol($row, $col) == false) {
5127 // Strip the '=' or '@' sign at the beginning of the formula string
5128 if (preg_match("/^=/", $formula)) {
5129 $formula = preg_replace("/(^=)/", "", $formula);
5130 } elseif (preg_match("/^@/", $formula)) {
5131 $formula = preg_replace("/(^@)/", "", $formula);
5134 $this->writeString($row, $col, 'Unrecognised character for formula');
5138 // Parse the formula using the parser in Parser.php
5139 $this->_parser->parse($formula);
5141 $formula = $this->_parser->toReversePolish();
5143 $formlen = strlen($formula); // Length of the binary string
5144 $length = 0x16 + $formlen; // Length of the record data
5146 $header = pack("vv", $record, $length);
5147 $data = pack("vvvdvVv", $row, $col, $xf, $num,
5148 $grbit, $unknown, $formlen);
5150 $this->_append($header . $data . $formula);
5155 * Write a hyperlink.
5156 * This is comprised of two elements: the visible label and
5157 * the invisible link. The visible label is the same as the link unless an
5158 * alternative string is specified. The label is written using the
5159 * writeString() method. Therefore the 255 characters string limit applies.
5160 * $string and $format are optional.
5162 * The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external
5165 * Returns 0 : normal termination
5166 * -2 : row or column out of range
5167 * -3 : long string truncated to 255 chars
5170 * @param integer $row Row
5171 * @param integer $col Column
5172 * @param string $url URL string
5173 * @param string $string Alternative label
5174 * @param mixed $format The cell format
5177 function writeUrl($row, $col, $url, $string = '', $format = null)
5179 // Add start row and col to arg list
5180 return($this->_writeUrlRange($row, $col, $row, $col, $url, $string, $format));
5184 * This is the more general form of writeUrl(). It allows a hyperlink to be
5185 * written to a range of cells. This function also decides the type of hyperlink
5186 * to be written. These are either, Web (http, ftp, mailto), Internal
5187 * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
5191 * @param integer $row1 Start row
5192 * @param integer $col1 Start column
5193 * @param integer $row2 End row
5194 * @param integer $col2 End column
5195 * @param string $url URL string
5196 * @param string $string Alternative label
5197 * @param mixed $format The cell format
5201 function _writeUrlRange($row1, $col1, $row2, $col2, $url, $string = '', $format = null)
5204 // Check for internal/external sheet links or default to web link
5205 if (preg_match('[^internal:]', $url)) {
5206 return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url, $string, $format));
5208 if (preg_match('[^external:]', $url)) {
5209 return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url, $string, $format));
5211 return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url, $string, $format));
5216 * Used to write http, ftp and mailto hyperlinks.
5217 * The link type ($options) is 0x03 is the same as absolute dir ref without
5218 * sheet. However it is differentiated by the $unknown2 data stream.
5222 * @param integer $row1 Start row
5223 * @param integer $col1 Start column
5224 * @param integer $row2 End row
5225 * @param integer $col2 End column
5226 * @param string $url URL string
5227 * @param string $str Alternative label
5228 * @param mixed $format The cell format
5231 function _writeUrlWeb($row1, $col1, $row2, $col2, $url, $str, $format = null)
5233 $record = 0x01B8; // Record identifier
5234 $length = 0x00000; // Bytes to follow
5237 $format = $this->_url_format;
5240 // Write the visible label using the writeString() method.
5244 $str_error = $this->writeString($row1, $col1, $str, $format);
5245 if (($str_error == -2) || ($str_error == -3)) {
5249 // Pack the undocumented parts of the hyperlink stream
5250 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
5251 $unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");
5253 // Pack the option flags
5254 $options = pack("V", 0x03);
5256 // Convert URL to a null terminated wchar string
5257 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
5258 $url = $url . "\0\0\0";
5260 // Pack the length of the URL
5261 $url_len = pack("V", strlen($url));
5263 // Calculate the data length
5264 $length = 0x34 + strlen($url);
5266 // Pack the header data
5267 $header = pack("vv", $record, $length);
5268 $data = pack("vvvv", $row1, $row2, $col1, $col2);
5270 // Write the packed data
5271 $this->_append($header . $data .
5272 $unknown1 . $options .
5273 $unknown2 . $url_len . $url);
5278 * Used to write internal reference hyperlinks such as "Sheet1!A1".
5282 * @param integer $row1 Start row
5283 * @param integer $col1 Start column
5284 * @param integer $row2 End row
5285 * @param integer $col2 End column
5286 * @param string $url URL string
5287 * @param string $str Alternative label
5288 * @param mixed $format The cell format
5291 function _writeUrlInternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
5293 $record = 0x01B8; // Record identifier
5294 $length = 0x00000; // Bytes to follow
5297 $format = $this->_url_format;
5301 $url = preg_replace('/^internal:/', '', $url);
5303 // Write the visible label
5307 $str_error = $this->writeString($row1, $col1, $str, $format);
5308 if (($str_error == -2) || ($str_error == -3)) {
5312 // Pack the undocumented parts of the hyperlink stream
5313 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
5315 // Pack the option flags
5316 $options = pack("V", 0x08);
5318 // Convert the URL type and to a null terminated wchar string
5319 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
5320 $url = $url . "\0\0\0";
5322 // Pack the length of the URL as chars (not wchars)
5323 $url_len = pack("V", floor(strlen($url)/2));
5325 // Calculate the data length
5326 $length = 0x24 + strlen($url);
5328 // Pack the header data
5329 $header = pack("vv", $record, $length);
5330 $data = pack("vvvv", $row1, $row2, $col1, $col2);
5332 // Write the packed data
5333 $this->_append($header . $data .
5334 $unknown1 . $options .
5340 * Write links to external directory names such as 'c:\foo.xls',
5341 * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
5343 * Note: Excel writes some relative links with the $dir_long string. We ignore
5344 * these cases for the sake of simpler code.
5348 * @param integer $row1 Start row
5349 * @param integer $col1 Start column
5350 * @param integer $row2 End row
5351 * @param integer $col2 End column
5352 * @param string $url URL string
5353 * @param string $str Alternative label
5354 * @param mixed $format The cell format
5357 function _writeUrlExternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
5359 // Network drives are different. We will handle them separately
5360 // MS/Novell network drives and shares start with \\
5361 if (preg_match('[^external:\\\\]', $url)) {
5362 return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format));
5365 $record = 0x01B8; // Record identifier
5366 $length = 0x00000; // Bytes to follow
5369 $format = $this->_url_format;
5372 // Strip URL type and change Unix dir separator to Dos style (if needed)
5374 $url = preg_replace('/^external:/', '', $url);
5375 $url = preg_replace('/\//', "\\", $url);
5377 // Write the visible label
5379 $str = preg_replace('/\#/', ' - ', $url);
5381 $str_error = $this->writeString($row1, $col1, $str, $format);
5382 if (($str_error == -2) or ($str_error == -3)) {
5386 // Determine if the link is relative or absolute:
5387 // relative if link contains no dir separator, "somefile.xls"
5388 // relative if link starts with up-dir, "..\..\somefile.xls"
5389 // otherwise, absolute
5391 $absolute = 0x02; // Bit mask
5392 if (!preg_match("/\\\/", $url)) {
5395 if (preg_match("/^\.\.\\\/", $url)) {
5398 $link_type = 0x01 | $absolute;
5400 // Determine if the link contains a sheet reference and change some of the
5401 // parameters accordingly.
5402 // Split the dir name and sheet name (if it exists)
5403 /*if (preg_match("/\#/", $url)) {
5404 list($dir_long, $sheet) = preg_split("/\#/", $url);
5409 if (isset($sheet)) {
5411 $sheet_len = pack("V", strlen($sheet) + 0x01);
5412 $sheet = join("\0", preg_split('//', $sheet));
5419 if (preg_match("/\#/", $url)) {
5425 // Pack the link type
5426 $link_type = pack("V", $link_type);
5428 // Calculate the up-level dir count e.g.. (..\..\..\ == 3)
5429 $up_count = preg_match_all("/\.\.\\\/", $dir_long, $useless);
5430 $up_count = pack("v", $up_count);
5432 // Store the short dos dir name (null terminated)
5433 $dir_short = preg_replace("/\.\.\\\/", '', $dir_long) . "\0";
5435 // Store the long dir name as a wchar string (non-null terminated)
5436 //$dir_long = join("\0", preg_split('//', $dir_long));
5437 $dir_long = $dir_long . "\0";
5439 // Pack the lengths of the dir strings
5440 $dir_short_len = pack("V", strlen($dir_short) );
5441 $dir_long_len = pack("V", strlen($dir_long) );
5442 $stream_len = pack("V", 0);//strlen($dir_long) + 0x06);
5444 // Pack the undocumented parts of the hyperlink stream
5445 $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000' );
5446 $unknown2 = pack("H*",'0303000000000000C000000000000046' );
5447 $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000');
5448 $unknown4 = pack("v", 0x03 );
5450 // Pack the main data stream
5451 $data = pack("vvvv", $row1, $row2, $col1, $col2) .
5466 // Pack the header data
5467 $length = strlen($data);
5468 $header = pack("vv", $record, $length);
5470 // Write the packed data
5471 $this->_append($header. $data);
5477 * This method is used to set the height and format for a row.
5480 * @param integer $row The row to set
5481 * @param integer $height Height we are giving to the row.
5482 * Use null to set XF without setting height
5483 * @param mixed $format XF format we are giving to the row
5484 * @param bool $hidden The optional hidden attribute
5485 * @param integer $level The optional outline level for row, in range [0,7]
5487 function setRow($row, $height, $format = null, $hidden = false, $level = 0)
5489 $record = 0x0208; // Record identifier
5490 $length = 0x0010; // Number of bytes to follow
5492 $colMic = 0x0000; // First defined column
5493 $colMac = 0x0000; // Last defined column
5494 $irwMac = 0x0000; // Used by Excel to optimise loading
5495 $reserved = 0x0000; // Reserved
5496 $grbit = 0x0000; // Option flags
5497 $ixfe = $this->_XF($format); // XF index
5499 // set _row_sizes so _sizeRow() can use it
5500 $this->_row_sizes[$row] = $height;
5502 // Use setRow($row, null, $XF) to set XF format without setting height
5503 if ($height != null) {
5504 $miyRw = $height * 20; // row height
5506 $miyRw = 0xff; // default row height is 256
5509 $level = max(0, min($level, 7)); // level should be between 0 and 7
5510 $this->_outline_row_level = max($level, $this->_outline_row_level);
5513 // Set the options flags. fUnsynced is used to show that the font and row
5514 // heights are not compatible. This is usually the case for WriteExcel.
5515 // The collapsed flag 0x10 doesn't seem to be used to indicate that a row
5516 // is collapsed. Instead it is used to indicate that the previous row is
5517 // collapsed. The zero height flag, 0x20, is used to collapse a row.
5523 $grbit |= 0x0040; // fUnsynced
5529 $header = pack("vv", $record, $length);
5530 $data = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw,
5531 $irwMac,$reserved, $grbit, $ixfe);
5532 $this->_append($header.$data);
5536 * Writes Excel DIMENSIONS to define the area in which there is data.
5540 function _storeDimensions()
5542 $record = 0x0200; // Record identifier
5543 $row_min = $this->_dim_rowmin; // First row
5544 $row_max = $this->_dim_rowmax + 1; // Last row plus 1
5545 $col_min = $this->_dim_colmin; // First column
5546 $col_max = $this->_dim_colmax + 1; // Last column plus 1
5547 $reserved = 0x0000; // Reserved by Excel
5549 if ($this->_BIFF_version == 0x0500) {
5550 $length = 0x000A; // Number of bytes to follow
5551 $data = pack("vvvvv", $row_min, $row_max,
5552 $col_min, $col_max, $reserved);
5553 } elseif ($this->_BIFF_version == 0x0600) {
5555 $data = pack("VVvvv", $row_min, $row_max,
5556 $col_min, $col_max, $reserved);
5558 $header = pack("vv", $record, $length);
5559 $this->_prepend($header.$data);
5563 * Write BIFF record Window2.
5567 function _storeWindow2()
5569 $record = 0x023E; // Record identifier
5570 if ($this->_BIFF_version == 0x0500) {
5571 $length = 0x000A; // Number of bytes to follow
5572 } elseif ($this->_BIFF_version == 0x0600) {
5576 $grbit = 0x00B6; // Option flags
5577 $rwTop = 0x0000; // Top row visible in window
5578 $colLeft = 0x0000; // Leftmost column visible in window
5581 // The options flags that comprise $grbit
5582 $fDspFmla = 0; // 0 - bit
5583 $fDspGrid = $this->_screen_gridlines; // 1
5584 $fDspRwCol = 1; // 2
5585 $fFrozen = $this->_frozen; // 3
5586 $fDspZeros = 1; // 4
5587 $fDefaultHdr = 1; // 5
5588 $fArabic = $this->_rtl; // 6
5589 $fDspGuts = $this->_outline_on; // 7
5590 $fFrozenNoSplit = 0; // 0 - bit
5591 $fSelected = $this->selected; // 1
5595 $grbit |= $fDspGrid << 1;
5596 $grbit |= $fDspRwCol << 2;
5597 $grbit |= $fFrozen << 3;
5598 $grbit |= $fDspZeros << 4;
5599 $grbit |= $fDefaultHdr << 5;
5600 $grbit |= $fArabic << 6;
5601 $grbit |= $fDspGuts << 7;
5602 $grbit |= $fFrozenNoSplit << 8;
5603 $grbit |= $fSelected << 9;
5604 $grbit |= $fPaged << 10;
5606 $header = pack("vv", $record, $length);
5607 $data = pack("vvv", $grbit, $rwTop, $colLeft);
5609 if ($this->_BIFF_version == 0x0500) {
5610 $rgbHdr = 0x00000000; // Row/column heading and gridline color
5611 $data .= pack("V", $rgbHdr);
5612 } elseif ($this->_BIFF_version == 0x0600) {
5613 $rgbHdr = 0x0040; // Row/column heading and gridline color index
5614 $zoom_factor_page_break = 0x0000;
5615 $zoom_factor_normal = 0x0000;
5616 $data .= pack("vvvvV", $rgbHdr, 0x0000, $zoom_factor_page_break, $zoom_factor_normal, 0x00000000);
5618 $this->_append($header.$data);
5622 * Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
5626 function _storeDefcol()
5628 $record = 0x0055; // Record identifier
5629 $length = 0x0002; // Number of bytes to follow
5630 $colwidth = 0x0008; // Default column width
5632 $header = pack("vv", $record, $length);
5633 $data = pack("v", $colwidth);
5634 $this->_prepend($header . $data);
5638 * Write BIFF record COLINFO to define column widths
5640 * Note: The SDK says the record length is 0x0B but Excel writes a 0x0C
5644 * @param array $col_array This is the only parameter received and is composed of the following:
5645 * 0 => First formatted column,
5646 * 1 => Last formatted column,
5647 * 2 => Col width (8.43 is Excel default),
5648 * 3 => The optional XF format of the column,
5649 * 4 => Option flags.
5650 * 5 => Optional outline level
5652 function _storeColinfo($col_array)
5654 if (isset($col_array[0])) {
5655 $colFirst = $col_array[0];
5657 if (isset($col_array[1])) {
5658 $colLast = $col_array[1];
5660 if (isset($col_array[2])) {
5661 $coldx = $col_array[2];
5665 if (isset($col_array[3])) {
5666 $format = $col_array[3];
5670 if (isset($col_array[4])) {
5671 $grbit = $col_array[4];
5675 if (isset($col_array[5])) {
5676 $level = $col_array[5];
5680 $record = 0x007D; // Record identifier
5681 $length = 0x000B; // Number of bytes to follow
5683 $coldx += 0.72; // Fudge. Excel subtracts 0.72 !?
5684 $coldx *= 256; // Convert to units of 1/256 of a char
5686 $ixfe = $this->_XF($format);
5687 $reserved = 0x00; // Reserved
5689 $level = max(0, min($level, 7));
5690 $grbit |= $level << 8;
5692 $header = pack("vv", $record, $length);
5693 $data = pack("vvvvvC", $colFirst, $colLast, $coldx,
5694 $ixfe, $grbit, $reserved);
5695 $this->_prepend($header.$data);
5699 * Write BIFF record SELECTION.
5702 * @param array $array array containing ($rwFirst,$colFirst,$rwLast,$colLast)
5703 * @see setSelection()
5705 function _storeSelection($array)
5707 list($rwFirst,$colFirst,$rwLast,$colLast) = $array;
5708 $record = 0x001D; // Record identifier
5709 $length = 0x000F; // Number of bytes to follow
5711 $pnn = $this->_active_pane; // Pane position
5712 $rwAct = $rwFirst; // Active row
5713 $colAct = $colFirst; // Active column
5714 $irefAct = 0; // Active cell ref
5715 $cref = 1; // Number of refs
5717 if (!isset($rwLast)) {
5718 $rwLast = $rwFirst; // Last row in reference
5720 if (!isset($colLast)) {
5721 $colLast = $colFirst; // Last col in reference
5724 // Swap last row/col for first row/col as necessary
5725 if ($rwFirst > $rwLast) {
5726 list($rwFirst, $rwLast) = array($rwLast, $rwFirst);
5729 if ($colFirst > $colLast) {
5730 list($colFirst, $colLast) = array($colLast, $colFirst);
5733 $header = pack("vv", $record, $length);
5734 $data = pack("CvvvvvvCC", $pnn, $rwAct, $colAct,
5737 $colFirst, $colLast);
5738 $this->_append($header . $data);
5742 * Store the MERGEDCELLS record for all ranges of merged cells
5746 function _storeMergedCells()
5748 // if there are no merged cell ranges set, return
5749 if (count($this->_merged_ranges) == 0) {
5753 $length = 2 + count($this->_merged_ranges) * 8;
5755 $header = pack('vv', $record, $length);
5756 $data = pack('v', count($this->_merged_ranges));
5757 foreach ($this->_merged_ranges as $range) {
5758 $data .= pack('vvvv', $range[0], $range[2], $range[1], $range[3]);
5760 $this->_append($header . $data);
5764 * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
5765 * references in a worksheet.
5767 * Excel only stores references to external sheets that are used in formulas.
5768 * For simplicity we store references to all the sheets in the workbook
5769 * regardless of whether they are used or not. This reduces the overall
5770 * complexity and eliminates the need for a two way dialogue between the formula
5771 * parser the worksheet objects.
5774 * @param integer $count The number of external sheet references in this worksheet
5776 function _storeExterncount($count)
5778 $record = 0x0016; // Record identifier
5779 $length = 0x0002; // Number of bytes to follow
5781 $header = pack("vv", $record, $length);
5782 $data = pack("v", $count);
5783 $this->_prepend($header . $data);
5787 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
5788 * formulas. A formula references a sheet name via an index. Since we store a
5789 * reference to all of the external worksheets the EXTERNSHEET index is the same
5790 * as the worksheet index.
5793 * @param string $sheetname The name of a external worksheet
5795 function _storeExternsheet($sheetname)
5797 $record = 0x0017; // Record identifier
5799 // References to the current sheet are encoded differently to references to
5802 if ($this->name == $sheetname) {
5804 $length = 0x02; // The following 2 bytes
5805 $cch = 1; // The following byte
5806 $rgch = 0x02; // Self reference
5808 $length = 0x02 + strlen($sheetname);
5809 $cch = strlen($sheetname);
5810 $rgch = 0x03; // Reference to a sheet in the current workbook
5813 $header = pack("vv", $record, $length);
5814 $data = pack("CC", $cch, $rgch);
5815 $this->_prepend($header . $data . $sheetname);
5819 * Writes the Excel BIFF PANE record.
5820 * The panes can either be frozen or thawed (unfrozen).
5821 * Frozen panes are specified in terms of an integer number of rows and columns.
5822 * Thawed panes are specified in terms of Excel's units for rows and columns.
5825 * @param array $panes This is the only parameter received and is composed of the following:
5826 * 0 => Vertical split position,
5827 * 1 => Horizontal split position
5828 * 2 => Top row visible
5829 * 3 => Leftmost column visible
5832 function _storePanes($panes)
5837 $colLeft = $panes[3];
5838 if (count($panes) > 4) { // if Active pane was received
5839 $pnnAct = $panes[4];
5843 $record = 0x0041; // Record identifier
5844 $length = 0x000A; // Number of bytes to follow
5846 // Code specific to frozen or thawed panes.
5847 if ($this->_frozen) {
5848 // Set default values for $rwTop and $colLeft
5849 if (!isset($rwTop)) {
5852 if (!isset($colLeft)) {
5856 // Set default values for $rwTop and $colLeft
5857 if (!isset($rwTop)) {
5860 if (!isset($colLeft)) {
5864 // Convert Excel's row and column units to the internal units.
5865 // The default row height is 12.75
5866 // The default column width is 8.43
5867 // The following slope and intersection values were interpolated.
5870 $x = 113.879*$x + 390;
5874 // Determine which pane should be active. There is also the undocumented
5875 // option to override this should it be necessary: may be removed later.
5877 if (!isset($pnnAct)) {
5878 if ($x != 0 && $y != 0) {
5879 $pnnAct = 0; // Bottom right
5881 if ($x != 0 && $y == 0) {
5882 $pnnAct = 1; // Top right
5884 if ($x == 0 && $y != 0) {
5885 $pnnAct = 2; // Bottom left
5887 if ($x == 0 && $y == 0) {
5888 $pnnAct = 3; // Top left
5892 $this->_active_pane = $pnnAct; // Used in _storeSelection
5894 $header = pack("vv", $record, $length);
5895 $data = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct);
5896 $this->_append($header . $data);
5900 * Store the page setup SETUP BIFF record.
5904 function _storeSetup()
5906 $record = 0x00A1; // Record identifier
5907 $length = 0x0022; // Number of bytes to follow
5909 $iPaperSize = $this->_paper_size; // Paper size
5910 $iScale = $this->_print_scale; // Print scaling factor
5911 $iPageStart = 0x01; // Starting page number
5912 $iFitWidth = $this->_fit_width; // Fit to number of pages wide
5913 $iFitHeight = $this->_fit_height; // Fit to number of pages high
5914 $grbit = 0x00; // Option flags
5915 $iRes = 0x0258; // Print resolution
5916 $iVRes = 0x0258; // Vertical print resolution
5917 $numHdr = $this->_margin_head; // Header Margin
5918 $numFtr = $this->_margin_foot; // Footer Margin
5919 $iCopies = 0x01; // Number of copies
5921 $fLeftToRight = 0x0; // Print over then down
5922 $fLandscape = $this->_orientation; // Page orientation
5923 $fNoPls = 0x0; // Setup not read from printer
5924 $fNoColor = 0x0; // Print black and white
5925 $fDraft = 0x0; // Print draft quality
5926 $fNotes = 0x0; // Print notes
5927 $fNoOrient = 0x0; // Orientation not set
5928 $fUsePage = 0x0; // Use custom starting page
5930 $grbit = $fLeftToRight;
5931 $grbit |= $fLandscape << 1;
5932 $grbit |= $fNoPls << 2;
5933 $grbit |= $fNoColor << 3;
5934 $grbit |= $fDraft << 4;
5935 $grbit |= $fNotes << 5;
5936 $grbit |= $fNoOrient << 6;
5937 $grbit |= $fUsePage << 7;
5939 $numHdr = pack("d", $numHdr);
5940 $numFtr = pack("d", $numFtr);
5941 if ($this->_byte_order) { // if it's Big Endian
5942 $numHdr = strrev($numHdr);
5943 $numFtr = strrev($numFtr);
5946 $header = pack("vv", $record, $length);
5947 $data1 = pack("vvvvvvvv", $iPaperSize,
5955 $data2 = $numHdr.$numFtr;
5956 $data3 = pack("v", $iCopies);
5957 $this->_prepend($header . $data1 . $data2 . $data3);
5961 * Store the header caption BIFF record.
5965 function _storeHeader()
5967 $record = 0x0014; // Record identifier
5969 $str = $this->_header; // header string
5970 $cch = strlen($str); // Length of header string
5971 if ($this->_BIFF_version == 0x0600) {
5972 $encoding = 0x0; // TODO: Unicode support
5973 $length = 3 + $cch; // Bytes to follow
5975 $length = 1 + $cch; // Bytes to follow
5978 $header = pack("vv", $record, $length);
5979 if ($this->_BIFF_version == 0x0600) {
5980 $data = pack("vC", $cch, $encoding);
5982 $data = pack("C", $cch);
5985 $this->_prepend($header.$data.$str);
5989 * Store the footer caption BIFF record.
5993 function _storeFooter()
5995 $record = 0x0015; // Record identifier
5997 $str = $this->_footer; // Footer string
5998 $cch = strlen($str); // Length of footer string
5999 if ($this->_BIFF_version == 0x0600) {
6000 $encoding = 0x0; // TODO: Unicode support
6001 $length = 3 + $cch; // Bytes to follow
6006 $header = pack("vv", $record, $length);
6007 if ($this->_BIFF_version == 0x0600) {
6008 $data = pack("vC", $cch, $encoding);
6010 $data = pack("C", $cch);
6013 $this->_prepend($header . $data . $str);
6017 * Store the horizontal centering HCENTER BIFF record.
6021 function _storeHcenter()
6023 $record = 0x0083; // Record identifier
6024 $length = 0x0002; // Bytes to follow
6026 $fHCenter = $this->_hcenter; // Horizontal centering
6028 $header = pack("vv", $record, $length);
6029 $data = pack("v", $fHCenter);
6031 $this->_prepend($header.$data);
6035 * Store the vertical centering VCENTER BIFF record.
6039 function _storeVcenter()
6041 $record = 0x0084; // Record identifier
6042 $length = 0x0002; // Bytes to follow
6044 $fVCenter = $this->_vcenter; // Horizontal centering
6046 $header = pack("vv", $record, $length);
6047 $data = pack("v", $fVCenter);
6048 $this->_prepend($header . $data);
6052 * Store the LEFTMARGIN BIFF record.
6056 function _storeMarginLeft()
6058 $record = 0x0026; // Record identifier
6059 $length = 0x0008; // Bytes to follow
6061 $margin = $this->_margin_left; // Margin in inches
6063 $header = pack("vv", $record, $length);
6064 $data = pack("d", $margin);
6065 if ($this->_byte_order) { // if it's Big Endian
6066 $data = strrev($data);
6069 $this->_prepend($header . $data);
6073 * Store the RIGHTMARGIN BIFF record.
6077 function _storeMarginRight()
6079 $record = 0x0027; // Record identifier
6080 $length = 0x0008; // Bytes to follow
6082 $margin = $this->_margin_right; // Margin in inches
6084 $header = pack("vv", $record, $length);
6085 $data = pack("d", $margin);
6086 if ($this->_byte_order) { // if it's Big Endian
6087 $data = strrev($data);
6090 $this->_prepend($header . $data);
6094 * Store the TOPMARGIN BIFF record.
6098 function _storeMarginTop()
6100 $record = 0x0028; // Record identifier
6101 $length = 0x0008; // Bytes to follow
6103 $margin = $this->_margin_top; // Margin in inches
6105 $header = pack("vv", $record, $length);
6106 $data = pack("d", $margin);
6107 if ($this->_byte_order) { // if it's Big Endian
6108 $data = strrev($data);
6111 $this->_prepend($header . $data);
6115 * Store the BOTTOMMARGIN BIFF record.
6119 function _storeMarginBottom()
6121 $record = 0x0029; // Record identifier
6122 $length = 0x0008; // Bytes to follow
6124 $margin = $this->_margin_bottom; // Margin in inches
6126 $header = pack("vv", $record, $length);
6127 $data = pack("d", $margin);
6128 if ($this->_byte_order) { // if it's Big Endian
6129 $data = strrev($data);
6132 $this->_prepend($header . $data);
6136 * Merges the area given by its arguments.
6137 * This is an Excel97/2000 method. It is required to perform more complicated
6138 * merging than the normal setAlign('merge').
6141 * @param integer $first_row First row of the area to merge
6142 * @param integer $first_col First column of the area to merge
6143 * @param integer $last_row Last row of the area to merge
6144 * @param integer $last_col Last column of the area to merge
6146 function mergeCells($first_row, $first_col, $last_row, $last_col)
6148 $record = 0x00E5; // Record identifier
6149 $length = 0x000A; // Bytes to follow
6150 $cref = 1; // Number of refs
6152 // Swap last row/col for first row/col as necessary
6153 if ($first_row > $last_row) {
6154 list($first_row, $last_row) = array($last_row, $first_row);
6157 if ($first_col > $last_col) {
6158 list($first_col, $last_col) = array($last_col, $first_col);
6161 $header = pack("vv", $record, $length);
6162 $data = pack("vvvvv", $cref, $first_row, $last_row,
6163 $first_col, $last_col);
6165 $this->_append($header.$data);
6169 * Write the PRINTHEADERS BIFF record.
6173 function _storePrintHeaders()
6175 $record = 0x002a; // Record identifier
6176 $length = 0x0002; // Bytes to follow
6178 $fPrintRwCol = $this->_print_headers; // Boolean flag
6180 $header = pack("vv", $record, $length);
6181 $data = pack("v", $fPrintRwCol);
6182 $this->_prepend($header . $data);
6186 * Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the
6191 function _storePrintGridlines()
6193 $record = 0x002b; // Record identifier
6194 $length = 0x0002; // Bytes to follow
6196 $fPrintGrid = $this->_print_gridlines; // Boolean flag
6198 $header = pack("vv", $record, $length);
6199 $data = pack("v", $fPrintGrid);
6200 $this->_prepend($header . $data);
6204 * Write the GRIDSET BIFF record. Must be used in conjunction with the
6205 * PRINTGRIDLINES record.
6209 function _storeGridset()
6211 $record = 0x0082; // Record identifier
6212 $length = 0x0002; // Bytes to follow
6214 $fGridSet = !($this->_print_gridlines); // Boolean flag
6216 $header = pack("vv", $record, $length);
6217 $data = pack("v", $fGridSet);
6218 $this->_prepend($header . $data);
6222 * Write the GUTS BIFF record. This is used to configure the gutter margins
6223 * where Excel outline symbols are displayed. The visibility of the gutters is
6224 * controlled by a flag in WSBOOL.
6226 * @see _storeWsbool()
6229 function _storeGuts()
6231 $record = 0x0080; // Record identifier
6232 $length = 0x0008; // Bytes to follow
6234 $dxRwGut = 0x0000; // Size of row gutter
6235 $dxColGut = 0x0000; // Size of col gutter
6237 $row_level = $this->_outline_row_level;
6240 // Calculate the maximum column outline level. The equivalent calculation
6241 // for the row outline level is carried out in setRow().
6242 $colcount = count($this->_colinfo);
6243 for ($i = 0; $i < $colcount; $i++) {
6244 // Skip cols without outline level info.
6245 if (count($col_level) >= 6) {
6246 $col_level = max($this->_colinfo[$i][5], $col_level);
6250 // Set the limits for the outline levels (0 <= x <= 7).
6251 $col_level = max(0, min($col_level, 7));
6253 // The displayed level is one greater than the max outline levels
6261 $header = pack("vv", $record, $length);
6262 $data = pack("vvvv", $dxRwGut, $dxColGut, $row_level, $col_level);
6264 $this->_prepend($header.$data);
6269 * Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction
6270 * with the SETUP record.
6274 function _storeWsbool()
6276 $record = 0x0081; // Record identifier
6277 $length = 0x0002; // Bytes to follow
6280 // The only option that is of interest is the flag for fit to page. So we
6281 // set all the options in one go.
6283 /*if ($this->_fit_page) {
6288 // Set the option flags
6289 $grbit |= 0x0001; // Auto page breaks visible
6290 if ($this->_outline_style) {
6291 $grbit |= 0x0020; // Auto outline styles
6293 if ($this->_outline_below) {
6294 $grbit |= 0x0040; // Outline summary below
6296 if ($this->_outline_right) {
6297 $grbit |= 0x0080; // Outline summary right
6299 if ($this->_fit_page) {
6300 $grbit |= 0x0100; // Page setup fit to page
6302 if ($this->_outline_on) {
6303 $grbit |= 0x0400; // Outline symbols displayed
6306 $header = pack("vv", $record, $length);
6307 $data = pack("v", $grbit);
6308 $this->_prepend($header . $data);
6312 * Write the HORIZONTALPAGEBREAKS BIFF record.
6316 function _storeHbreak()
6318 // Return if the user hasn't specified pagebreaks
6319 if (empty($this->_hbreaks)) {
6323 // Sort and filter array of page breaks
6324 $breaks = $this->_hbreaks;
6325 sort($breaks, SORT_NUMERIC);
6326 if ($breaks[0] == 0) { // don't use first break if it's 0
6327 array_shift($breaks);
6330 $record = 0x001b; // Record identifier
6331 $cbrk = count($breaks); // Number of page breaks
6332 if ($this->_BIFF_version == 0x0600) {
6333 $length = 2 + 6*$cbrk; // Bytes to follow
6335 $length = 2 + 2*$cbrk; // Bytes to follow
6338 $header = pack("vv", $record, $length);
6339 $data = pack("v", $cbrk);
6341 // Append each page break
6342 foreach ($breaks as $break) {
6343 if ($this->_BIFF_version == 0x0600) {
6344 $data .= pack("vvv", $break, 0x0000, 0x00ff);
6346 $data .= pack("v", $break);
6350 $this->_prepend($header.$data);
6355 * Write the VERTICALPAGEBREAKS BIFF record.
6359 function _storeVbreak()
6361 // Return if the user hasn't specified pagebreaks
6362 if (empty($this->_vbreaks)) {
6366 // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
6367 // It is slightly higher in Excel 97/200, approx. 1026
6368 $breaks = array_slice($this->_vbreaks,0,1000);
6370 // Sort and filter array of page breaks
6371 sort($breaks, SORT_NUMERIC);
6372 if ($breaks[0] == 0) { // don't use first break if it's 0
6373 array_shift($breaks);
6376 $record = 0x001a; // Record identifier
6377 $cbrk = count($breaks); // Number of page breaks
6378 if ($this->_BIFF_version == 0x0600) {
6379 $length = 2 + 6*$cbrk; // Bytes to follow
6381 $length = 2 + 2*$cbrk; // Bytes to follow
6384 $header = pack("vv", $record, $length);
6385 $data = pack("v", $cbrk);
6387 // Append each page break
6388 foreach ($breaks as $break) {
6389 if ($this->_BIFF_version == 0x0600) {
6390 $data .= pack("vvv", $break, 0x0000, 0xffff);
6392 $data .= pack("v", $break);
6396 $this->_prepend($header . $data);
6400 * Set the Biff PROTECT record to indicate that the worksheet is protected.
6404 function _storeProtect()
6406 // Exit unless sheet protection has been specified
6407 if ($this->_protect == 0) {
6411 $record = 0x0012; // Record identifier
6412 $length = 0x0002; // Bytes to follow
6414 $fLock = $this->_protect; // Worksheet is protected
6416 $header = pack("vv", $record, $length);
6417 $data = pack("v", $fLock);
6419 $this->_prepend($header.$data);
6423 * Write the worksheet PASSWORD record.
6427 function _storePassword()
6429 // Exit unless sheet protection and password have been specified
6430 if (($this->_protect == 0) || (!isset($this->_password))) {
6434 $record = 0x0013; // Record identifier
6435 $length = 0x0002; // Bytes to follow
6437 $wPassword = $this->_password; // Encoded password
6439 $header = pack("vv", $record, $length);
6440 $data = pack("v", $wPassword);
6442 $this->_prepend($header . $data);
6447 * Insert a 24bit bitmap image in a worksheet.
6450 * @param integer $row The row we are going to insert the bitmap into
6451 * @param integer $col The column we are going to insert the bitmap into
6452 * @param string $bitmap The bitmap filename
6453 * @param integer $x The horizontal position (offset) of the image inside the cell.
6454 * @param integer $y The vertical position (offset) of the image inside the cell.
6455 * @param integer $scale_x The horizontal scale
6456 * @param integer $scale_y The vertical scale
6458 function insertBitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1)
6460 $bitmap_array = $this->_processBitmap($bitmap);
6461 if ($this->isError($bitmap_array)) {
6462 $this->writeString($row, $col, $bitmap_array->getMessage());
6465 list($width, $height, $size, $data) = $bitmap_array; //$this->_processBitmap($bitmap);
6467 // Scale the frame of the image.
6469 $height *= $scale_y;
6471 // Calculate the vertices of the image and write the OBJ record
6472 $this->_positionImage($col, $row, $x, $y, $width, $height);
6474 // Write the IMDATA record to store the bitmap data
6476 $length = 8 + $size;
6481 $header = pack("vvvvV", $record, $length, $cf, $env, $lcb);
6482 $this->_append($header.$data);
6486 * Calculate the vertices that define the position of the image as required by
6489 * +------------+------------+
6491 * +-----+------------+------------+
6493 * | 1 |(A1)._______|______ |
6496 * +-----+----| BITMAP |-----+
6498 * | 2 | |______________. |
6501 * +---- +------------+------------+
6503 * Example of a bitmap that covers some of the area from cell A1 to cell B2.
6505 * Based on the width and height of the bitmap we need to calculate 8 vars:
6506 * $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
6507 * The width and height of the cells are also variable and have to be taken into
6509 * The values of $col_start and $row_start are passed in from the calling
6510 * function. The values of $col_end and $row_end are calculated by subtracting
6511 * the width and height of the bitmap from the width and height of the
6513 * The vertices are expressed as a percentage of the underlying cell width as
6514 * follows (rhs values are in pixels):
6518 * x2 = (X-1) / W *1024
6519 * y2 = (Y-1) / H *256
6521 * Where: X is distance from the left side of the underlying cell
6522 * Y is distance from the top of the underlying cell
6523 * W is the width of the cell
6524 * H is the height of the cell
6527 * @note the SDK incorrectly states that the height should be expressed as a
6528 * percentage of 1024.
6529 * @param integer $col_start Col containing upper left corner of object
6530 * @param integer $row_start Row containing top left corner of object
6531 * @param integer $x1 Distance to left side of object
6532 * @param integer $y1 Distance to top of object
6533 * @param integer $width Width of image frame
6534 * @param integer $height Height of image frame
6536 function _positionImage($col_start, $row_start, $x1, $y1, $width, $height)
6538 // Initialise end cell to the same as the start cell
6539 $col_end = $col_start; // Col containing lower right corner of object
6540 $row_end = $row_start; // Row containing bottom right corner of object
6542 // Zero the specified offset if greater than the cell dimensions
6543 if ($x1 >= $this->_sizeCol($col_start)) {
6546 if ($y1 >= $this->_sizeRow($row_start)) {
6550 $width = $width + $x1 -1;
6551 $height = $height + $y1 -1;
6553 // Subtract the underlying cell widths to find the end cell of the image
6554 while ($width >= $this->_sizeCol($col_end)) {
6555 $width -= $this->_sizeCol($col_end);
6559 // Subtract the underlying cell heights to find the end cell of the image
6560 while ($height >= $this->_sizeRow($row_end)) {
6561 $height -= $this->_sizeRow($row_end);
6565 // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
6566 // with zero eight or width.
6568 if ($this->_sizeCol($col_start) == 0) {
6571 if ($this->_sizeCol($col_end) == 0) {
6574 if ($this->_sizeRow($row_start) == 0) {
6577 if ($this->_sizeRow($row_end) == 0) {
6581 // Convert the pixel values to the percentage value expected by Excel
6582 $x1 = $x1 / $this->_sizeCol($col_start) * 1024;
6583 $y1 = $y1 / $this->_sizeRow($row_start) * 256;
6584 $x2 = $width / $this->_sizeCol($col_end) * 1024; // Distance to right side of object
6585 $y2 = $height / $this->_sizeRow($row_end) * 256; // Distance to bottom of object
6587 $this->_storeObjPicture($col_start, $x1,
6594 * Convert the width of a cell from user's units to pixels. By interpolation
6595 * the relationship is: y = 7x +5. If the width hasn't been set by the user we
6596 * use the default value. If the col is hidden we use a value of zero.
6599 * @param integer $col The column
6600 * @return integer The width in pixels
6602 function _sizeCol($col)
6604 // Look up the cell value to see if it has been changed
6605 if (isset($this->col_sizes[$col])) {
6606 if ($this->col_sizes[$col] == 0) {
6609 return(floor(7 * $this->col_sizes[$col] + 5));
6617 * Convert the height of a cell from user's units to pixels. By interpolation
6618 * the relationship is: y = 4/3x. If the height hasn't been set by the user we
6619 * use the default value. If the row is hidden we use a value of zero. (Not
6620 * possible to hide row yet).
6623 * @param integer $row The row
6624 * @return integer The width in pixels
6626 function _sizeRow($row)
6628 // Look up the cell value to see if it has been changed
6629 if (isset($this->_row_sizes[$row])) {
6630 if ($this->_row_sizes[$row] == 0) {
6633 return(floor(4/3 * $this->_row_sizes[$row]));
6641 * Store the OBJ record that precedes an IMDATA record. This could be generalise
6642 * to support other Excel objects.
6645 * @param integer $colL Column containing upper left corner of object
6646 * @param integer $dxL Distance from left side of cell
6647 * @param integer $rwT Row containing top left corner of object
6648 * @param integer $dyT Distance from top of cell
6649 * @param integer $colR Column containing lower right corner of object
6650 * @param integer $dxR Distance from right of cell
6651 * @param integer $rwB Row containing bottom right corner of object
6652 * @param integer $dyB Distance from bottom of cell
6654 function _storeObjPicture($colL,$dxL,$rwT,$dyT,$colR,$dxR,$rwB,$dyB)
6656 $record = 0x005d; // Record identifier
6657 $length = 0x003c; // Bytes to follow
6659 $cObj = 0x0001; // Count of objects in file (set to 1)
6660 $OT = 0x0008; // Object type. 8 = Picture
6661 $id = 0x0001; // Object ID
6662 $grbit = 0x0614; // Option flags
6664 $cbMacro = 0x0000; // Length of FMLA structure
6665 $Reserved1 = 0x0000; // Reserved
6666 $Reserved2 = 0x0000; // Reserved
6668 $icvBack = 0x09; // Background colour
6669 $icvFore = 0x09; // Foreground colour
6670 $fls = 0x00; // Fill pattern
6671 $fAuto = 0x00; // Automatic fill
6672 $icv = 0x08; // Line colour
6673 $lns = 0xff; // Line style
6674 $lnw = 0x01; // Line weight
6675 $fAutoB = 0x00; // Automatic border
6676 $frs = 0x0000; // Frame style
6677 $cf = 0x0009; // Image format, 9 = bitmap
6678 $Reserved3 = 0x0000; // Reserved
6679 $cbPictFmla = 0x0000; // Length of FMLA structure
6680 $Reserved4 = 0x0000; // Reserved
6681 $grbit2 = 0x0001; // Option flags
6682 $Reserved5 = 0x0000; // Reserved
6685 $header = pack("vv", $record, $length);
6686 $data = pack("V", $cObj);
6687 $data .= pack("v", $OT);
6688 $data .= pack("v", $id);
6689 $data .= pack("v", $grbit);
6690 $data .= pack("v", $colL);
6691 $data .= pack("v", $dxL);
6692 $data .= pack("v", $rwT);
6693 $data .= pack("v", $dyT);
6694 $data .= pack("v", $colR);
6695 $data .= pack("v", $dxR);
6696 $data .= pack("v", $rwB);
6697 $data .= pack("v", $dyB);
6698 $data .= pack("v", $cbMacro);
6699 $data .= pack("V", $Reserved1);
6700 $data .= pack("v", $Reserved2);
6701 $data .= pack("C", $icvBack);
6702 $data .= pack("C", $icvFore);
6703 $data .= pack("C", $fls);
6704 $data .= pack("C", $fAuto);
6705 $data .= pack("C", $icv);
6706 $data .= pack("C", $lns);
6707 $data .= pack("C", $lnw);
6708 $data .= pack("C", $fAutoB);
6709 $data .= pack("v", $frs);
6710 $data .= pack("V", $cf);
6711 $data .= pack("v", $Reserved3);
6712 $data .= pack("v", $cbPictFmla);
6713 $data .= pack("v", $Reserved4);
6714 $data .= pack("v", $grbit2);
6715 $data .= pack("V", $Reserved5);
6717 $this->_append($header . $data);
6721 * Convert a 24 bit bitmap into the modified internal format used by Windows.
6722 * This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the
6726 * @param string $bitmap The bitmap to process
6727 * @return array Array with data and properties of the bitmap
6729 function _processBitmap($bitmap)
6732 $bmp_fd = @fopen($bitmap,"rb");
6734 die("Couldn't import $bitmap");
6737 // Slurp the file into a string.
6738 $data = fread($bmp_fd, filesize($bitmap));
6740 // Check that the file is big enough to be a bitmap.
6741 if (strlen($data) <= 0x36) {
6742 die("$bitmap doesn't contain enough data.\n");
6745 // The first 2 bytes are used to identify the bitmap.
6746 $identity = unpack("A2ident", $data);
6747 if ($identity['ident'] != "BM") {
6748 die("$bitmap doesn't appear to be a valid bitmap image.\n");
6751 // Remove bitmap data: ID.
6752 $data = substr($data, 2);
6754 // Read and remove the bitmap size. This is more reliable than reading
6755 // the data size at offset 0x22.
6757 $size_array = unpack("Vsa", substr($data, 0, 4));
6758 $size = $size_array['sa'];
6759 $data = substr($data, 4);
6760 $size -= 0x36; // Subtract size of bitmap header.
6761 $size += 0x0C; // Add size of BIFF header.
6763 // Remove bitmap data: reserved, offset, header length.
6764 $data = substr($data, 12);
6766 // Read and remove the bitmap width and height. Verify the sizes.
6767 $width_and_height = unpack("V2", substr($data, 0, 8));
6768 $width = $width_and_height[1];
6769 $height = $width_and_height[2];
6770 $data = substr($data, 8);
6771 if ($width > 0xFFFF) {
6772 die("$bitmap: largest image width supported is 65k.\n");
6774 if ($height > 0xFFFF) {
6775 die("$bitmap: largest image height supported is 65k.\n");
6778 // Read and remove the bitmap planes and bpp data. Verify them.
6779 $planes_and_bitcount = unpack("v2", substr($data, 0, 4));
6780 $data = substr($data, 4);
6781 if ($planes_and_bitcount[2] != 24) { // Bitcount
6782 die("$bitmap isn't a 24bit true color bitmap.\n");
6784 if ($planes_and_bitcount[1] != 1) {
6785 die("$bitmap: only 1 plane supported in bitmap image.\n");
6788 // Read and remove the bitmap compression. Verify compression.
6789 $compression = unpack("Vcomp", substr($data, 0, 4));
6790 $data = substr($data, 4);
6793 if ($compression['comp'] != 0) {
6794 die("$bitmap: compression not supported in bitmap image.\n");
6797 // Remove bitmap data: data size, hres, vres, colours, imp. colours.
6798 $data = substr($data, 20);
6800 // Add the BITMAPCOREHEADER data
6801 $header = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18);
6802 $data = $header . $data;
6804 return (array($width, $height, $size, $data));
6808 * Store the window zoom factor. This should be a reduced fraction but for
6809 * simplicity we will store all fractions with a numerator of 100.
6813 function _storeZoom()
6815 // If scale is 100 we don't need to write a record
6816 if ($this->_zoom == 100) {
6820 $record = 0x00A0; // Record identifier
6821 $length = 0x0004; // Bytes to follow
6823 $header = pack("vv", $record, $length);
6824 $data = pack("vv", $this->_zoom, 100);
6825 $this->_append($header . $data);
6829 * FIXME: add comments
6831 function setValidation($row1, $col1, $row2, $col2, &$validator)
6833 $this->_dv[] = $validator->_getData() .
6834 pack("vvvvv", 1, $row1, $row2, $col1, $col2);
6838 * Store the DVAL and DV records.
6842 function _storeDataValidity()
6844 $record = 0x01b2; // Record identifier
6845 $length = 0x0012; // Bytes to follow
6847 $grbit = 0x0002; // Prompt box at cell, no cached validity data at DV records
6848 $horPos = 0x00000000; // Horizontal position of prompt box, if fixed position
6849 $verPos = 0x00000000; // Vertical position of prompt box, if fixed position
6850 $objId = 0xffffffff; // Object identifier of drop down arrow object, or -1 if not visible
6852 $header = pack('vv', $record, $length);
6853 $data = pack('vVVVV', $grbit, $horPos, $verPos, $objId,
6855 $this->_append($header.$data);
6857 $record = 0x01be; // Record identifier
6858 foreach ($this->_dv as $dv) {
6859 $length = strlen($dv); // Bytes to follow
6860 $header = pack("vv", $record, $length);
6861 $this->_append($header . $dv);
6867 * Class for generating Excel Spreadsheets
6869 * @author Xavier Noguer <xnoguer@rezebra.com>
6870 * @category FileFormats
6871 * @package Spreadsheet_Excel_Writer
6874 class Spreadsheet_Excel_Writer_Workbook extends Spreadsheet_Excel_Writer_BIFFwriter
6877 * Filename for the Workbook
6884 * @var object Parser
6889 * Flag for 1904 date system (0 => base date is 1900, 1 => base date is 1904)
6895 * The active worksheet of the workbook (0 indexed)
6901 * 1st displayed worksheet in the workbook (0 indexed)
6907 * Number of workbook tabs selected
6913 * Index for creating adding new formats to the workbook
6919 * Flag for preventing close from being called twice.
6926 * The BIFF file size for the workbook.
6928 * @see _calcSheetOffsets()
6933 * The default sheetname for all sheets created.
6939 * The default XF format.
6940 * @var object Format
6945 * Array containing references to all of this workbook's worksheets
6951 * Array of sheetnames for creating the EXTERNSHEET records
6957 * Array containing references to all of this workbook's formats
6963 * Array containing the colour palette
6969 * The default format for URLs.
6970 * @var object Format
6975 * The codepage indicates the text encoding used for strings
6981 * The country code used for localization
6987 * The temporary dir for storing the OLE file
6993 * number of bytes for sizeinfo of strings
6996 var $_string_sizeinfo_size;
7001 * @param string filename for storing the workbook. "-" for writing to stdout.
7004 function Spreadsheet_Excel_Writer_Workbook($filename)
7006 // It needs to call its parent's constructor explicitly
7007 $this->Spreadsheet_Excel_Writer_BIFFwriter();
7009 $this->_filename = $filename;
7010 $this->_parser = new Spreadsheet_Excel_Writer_Parser($this->_byte_order, $this->_BIFF_version);
7012 $this->_activesheet = 0;
7013 $this->_firstsheet = 0;
7014 $this->_selected = 0;
7015 $this->_xf_index = 16; // 15 style XF's and 1 cell XF.
7016 $this->_fileclosed = 0;
7017 $this->_biffsize = 0;
7018 $this->_sheetname = 'Sheet';
7019 $this->_tmp_format = new Spreadsheet_Excel_Writer_Format($this->_BIFF_version);
7020 $this->_worksheets = array();
7021 $this->_sheetnames = array();
7022 $this->_formats = array();
7023 $this->_palette = array();
7024 $this->_codepage = 0x04E4; // FIXME: should change for BIFF8
7025 $this->_country_code = -1;
7026 $this->_string_sizeinfo = 3;
7028 // Add the default format for hyperlinks
7029 $this->_url_format =& $this->addFormat(array('color' => 'blue', 'underline' => 1));
7030 $this->_str_total = 0;
7031 $this->_str_unique = 0;
7032 $this->_str_table = array();
7033 $this->_setPaletteXl97();
7034 $this->_tmp_dir = '';
7038 * Calls finalization methods.
7039 * This method should always be the last one to be called on every workbook
7042 * @return mixed true on success. PEAR_Error on failure
7046 if ($this->_fileclosed) { // Prevent close() from being called twice.
7049 $this->_storeWorkbook();
7050 $this->_fileclosed = 1;
7055 * An accessor for the _worksheets[] array
7056 * Returns an array of the worksheet objects in a workbook
7057 * It actually calls to worksheets()
7065 return $this->worksheets();
7069 * An accessor for the _worksheets[] array.
7070 * Returns an array of the worksheet objects in a workbook
7075 function worksheets()
7077 return $this->_worksheets;
7081 * Sets the BIFF version.
7082 * This method exists just to access experimental functionality
7083 * from BIFF8. It will be deprecated !
7084 * Only possible value is 8 (Excel 97/2000).
7085 * For any other value it fails silently.
7088 * @param integer $version The BIFF version
7090 function setVersion($version)
7092 if ($version == 8) { // only accept version 8
7094 $this->_BIFF_version = $version;
7095 // change BIFFwriter limit for CONTINUE records
7096 $this->_limit = 8228;
7097 $this->_tmp_format->_BIFF_version = $version;
7098 $this->_url_format->_BIFF_version = $version;
7099 $this->_parser->_BIFF_version = $version;
7101 $total_worksheets = count($this->_worksheets);
7102 // change version for all worksheets too
7103 for ($i = 0; $i < $total_worksheets; $i++) {
7104 $this->_worksheets[$i]->_BIFF_version = $version;
7107 $total_formats = count($this->_formats);
7108 // change version for all formats too
7109 for ($i = 0; $i < $total_formats; $i++) {
7110 $this->_formats[$i]->_BIFF_version = $version;
7116 * Set the country identifier for the workbook
7119 * @param integer $code Is the international calling country code for the
7122 function setCountry($code)
7124 $this->_country_code = $code;
7128 * Add a new worksheet to the Excel workbook.
7129 * If no name is given the name of the worksheet will be Sheeti$i, with
7133 * @param string $name the optional name of the worksheet
7134 * @return mixed reference to a worksheet object on success, PEAR_Error
7137 function &addWorksheet($name = '')
7139 $index = count($this->_worksheets);
7140 $sheetname = $this->_sheetname;
7143 $name = $sheetname.($index+1);
7146 // Check that sheetname is <= 31 chars (Excel limit before BIFF8).
7147 if ($this->_BIFF_version != 0x0600)
7149 if (strlen($name) > 31) {
7150 die("Sheetname $name must be <= 31 chars");
7154 // Check that the worksheet name doesn't already exist: a fatal Excel error.
7155 $total_worksheets = count($this->_worksheets);
7156 for ($i = 0; $i < $total_worksheets; $i++) {
7157 if ($this->_worksheets[$i]->getName() == $name) {
7158 die("Worksheet '$name' already exists");
7162 $worksheet = new Spreadsheet_Excel_Writer_Worksheet($this->_BIFF_version,
7164 $this->_activesheet, $this->_firstsheet,
7165 $this->_str_total, $this->_str_unique,
7166 $this->_str_table, $this->_url_format,
7169 $this->_worksheets[$index] = &$worksheet; // Store ref for iterator
7170 $this->_sheetnames[$index] = $name; // Store EXTERNSHEET names
7171 $this->_parser->setExtSheet($name, $index); // Register worksheet name with parser
7176 * Add a new format to the Excel workbook.
7177 * Also, pass any properties to the Format constructor.
7180 * @param array $properties array with properties for initializing the format.
7181 * @return &Spreadsheet_Excel_Writer_Format reference to an Excel Format
7183 function &addFormat($properties = array())
7185 $format = new Spreadsheet_Excel_Writer_Format($this->_BIFF_version, $this->_xf_index, $properties);
7186 $this->_xf_index += 1;
7187 $this->_formats[] = &$format;
7192 * Create new validator.
7195 * @return &Spreadsheet_Excel_Writer_Validator reference to a Validator
7197 function &addValidator()
7199 include_once 'Spreadsheet/Excel/Writer/Validator.php';
7200 /* FIXME: check for successful inclusion*/
7201 $valid = new Spreadsheet_Excel_Writer_Validator($this->_parser);
7206 * Change the RGB components of the elements in the colour palette.
7209 * @param integer $index colour index
7210 * @param integer $red red RGB value [0-255]
7211 * @param integer $green green RGB value [0-255]
7212 * @param integer $blue blue RGB value [0-255]
7213 * @return integer The palette index for the custom color
7215 function setCustomColor($index, $red, $green, $blue)
7217 // Match a HTML #xxyyzz style parameter
7218 /*if (defined $_[1] and $_[1] =~ /^#(\w\w)(\w\w)(\w\w)/ ) {
7219 @_ = ($_[0], hex $1, hex $2, hex $3);
7222 // Check that the colour index is the right range
7223 if ($index < 8 or $index > 64) {
7224 // TODO: assign real error codes
7225 die("Color index $index outside range: 8 <= index <= 64");
7228 // Check that the colour components are in the right range
7229 if (($red < 0 or $red > 255) ||
7230 ($green < 0 or $green > 255) ||
7231 ($blue < 0 or $blue > 255))
7233 die("Color component outside range: 0 <= color <= 255");
7236 $index -= 8; // Adjust colour index (wingless dragonfly)
7238 // Set the RGB value
7239 $this->_palette[$index] = array($red, $green, $blue, 0);
7244 * Sets the colour palette to the Excel 97+ default.
7248 function _setPaletteXl97()
7250 $this->_palette = array(
7251 array(0x00, 0x00, 0x00, 0x00), // 8
7252 array(0xff, 0xff, 0xff, 0x00), // 9
7253 array(0xff, 0x00, 0x00, 0x00), // 10
7254 array(0x00, 0xff, 0x00, 0x00), // 11
7255 array(0x00, 0x00, 0xff, 0x00), // 12
7256 array(0xff, 0xff, 0x00, 0x00), // 13
7257 array(0xff, 0x00, 0xff, 0x00), // 14
7258 array(0x00, 0xff, 0xff, 0x00), // 15
7259 array(0x80, 0x00, 0x00, 0x00), // 16
7260 array(0x00, 0x80, 0x00, 0x00), // 17
7261 array(0x00, 0x00, 0x80, 0x00), // 18
7262 array(0x80, 0x80, 0x00, 0x00), // 19
7263 array(0x80, 0x00, 0x80, 0x00), // 20
7264 array(0x00, 0x80, 0x80, 0x00), // 21
7265 array(0xc0, 0xc0, 0xc0, 0x00), // 22
7266 array(0x80, 0x80, 0x80, 0x00), // 23
7267 array(0x99, 0x99, 0xff, 0x00), // 24
7268 array(0x99, 0x33, 0x66, 0x00), // 25
7269 array(0xff, 0xff, 0xcc, 0x00), // 26
7270 array(0xcc, 0xff, 0xff, 0x00), // 27
7271 array(0x66, 0x00, 0x66, 0x00), // 28
7272 array(0xff, 0x80, 0x80, 0x00), // 29
7273 array(0x00, 0x66, 0xcc, 0x00), // 30
7274 array(0xcc, 0xcc, 0xff, 0x00), // 31
7275 array(0x00, 0x00, 0x80, 0x00), // 32
7276 array(0xff, 0x00, 0xff, 0x00), // 33
7277 array(0xff, 0xff, 0x00, 0x00), // 34
7278 array(0x00, 0xff, 0xff, 0x00), // 35
7279 array(0x80, 0x00, 0x80, 0x00), // 36
7280 array(0x80, 0x00, 0x00, 0x00), // 37
7281 array(0x00, 0x80, 0x80, 0x00), // 38
7282 array(0x00, 0x00, 0xff, 0x00), // 39
7283 array(0x00, 0xcc, 0xff, 0x00), // 40
7284 array(0xcc, 0xff, 0xff, 0x00), // 41
7285 array(0xcc, 0xff, 0xcc, 0x00), // 42
7286 array(0xff, 0xff, 0x99, 0x00), // 43
7287 array(0x99, 0xcc, 0xff, 0x00), // 44
7288 array(0xff, 0x99, 0xcc, 0x00), // 45
7289 array(0xcc, 0x99, 0xff, 0x00), // 46
7290 array(0xff, 0xcc, 0x99, 0x00), // 47
7291 array(0x33, 0x66, 0xff, 0x00), // 48
7292 array(0x33, 0xcc, 0xcc, 0x00), // 49
7293 array(0x99, 0xcc, 0x00, 0x00), // 50
7294 array(0xff, 0xcc, 0x00, 0x00), // 51
7295 array(0xff, 0x99, 0x00, 0x00), // 52
7296 array(0xff, 0x66, 0x00, 0x00), // 53
7297 array(0x66, 0x66, 0x99, 0x00), // 54
7298 array(0x96, 0x96, 0x96, 0x00), // 55
7299 array(0x00, 0x33, 0x66, 0x00), // 56
7300 array(0x33, 0x99, 0x66, 0x00), // 57
7301 array(0x00, 0x33, 0x00, 0x00), // 58
7302 array(0x33, 0x33, 0x00, 0x00), // 59
7303 array(0x99, 0x33, 0x00, 0x00), // 60
7304 array(0x99, 0x33, 0x66, 0x00), // 61
7305 array(0x33, 0x33, 0x99, 0x00), // 62
7306 array(0x33, 0x33, 0x33, 0x00), // 63
7311 * Assemble worksheets into a workbook and send the BIFF data to an OLE
7315 * @return mixed true on success. PEAR_Error on failure
7317 function _storeWorkbook()
7319 // Ensure that at least one worksheet has been selected.
7320 if ($this->_activesheet == 0) {
7321 $this->_worksheets[0]->selected = 1;
7324 // Calculate the number of selected worksheet tabs and call the finalization
7325 // methods for each worksheet
7326 $total_worksheets = count($this->_worksheets);
7327 for ($i = 0; $i < $total_worksheets; $i++) {
7328 if ($this->_worksheets[$i]->selected) {
7331 $this->_worksheets[$i]->close($this->_sheetnames);
7334 // Add Workbook globals
7335 $this->_storeBof(0x0005);
7336 $this->_storeCodepage();
7337 if ($this->_BIFF_version == 0x0600) {
7338 $this->_storeWindow1();
7340 if ($this->_BIFF_version == 0x0500) {
7341 $this->_storeExterns(); // For print area and repeat rows
7343 $this->_storeNames(); // For print area and repeat rows
7344 if ($this->_BIFF_version == 0x0500) {
7345 $this->_storeWindow1();
7347 $this->_storeDatemode();
7348 $this->_storeAllFonts();
7349 $this->_storeAllNumFormats();
7350 $this->_storeAllXfs();
7351 $this->_storeAllStyles();
7352 $this->_storePalette();
7353 $this->_calcSheetOffsets();
7355 // Add BOUNDSHEET records
7356 for ($i = 0; $i < $total_worksheets; $i++) {
7357 $this->_storeBoundsheet($this->_worksheets[$i]->name,$this->_worksheets[$i]->offset);
7360 if ($this->_country_code != -1) {
7361 $this->_storeCountry();
7364 if ($this->_BIFF_version == 0x0600) {
7365 //$this->_storeSupbookInternal();
7366 /* TODO: store external SUPBOOK records and XCT and CRN records
7367 in case of external references for BIFF8 */
7368 //$this->_storeExternsheetBiff8();
7369 $this->_storeSharedStringsTable();
7372 // End Workbook globals
7375 // Store the workbook in an OLE container
7376 $res = $this->_storeOLEFile();
7381 * Sets the temp dir used for storing the OLE file
7384 * @param string $dir The dir to be used as temp dir
7385 * @return true if given dir is valid, false otherwise
7387 function setTempDir($dir)
7390 $this->_tmp_dir = $dir;
7397 * Store the workbook in an OLE container
7400 * @return mixed true on success. PEAR_Error on failure
7402 function _storeOLEFile()
7404 $OLE = new OLEwriter($this->_filename);
7405 // Write Worksheet data if data <~ 7MB
7406 if ($OLE->setSize($this->_biffsize))
7408 $OLE->writeHeader();
7409 $OLE->write($this->_data);
7410 foreach($this->_worksheets as $sheet)
7412 while ($tmp = $sheet->getData()) {
7422 * Calculate offsets for Worksheet BOF records.
7426 function _calcSheetOffsets()
7428 if ($this->_BIFF_version == 0x0600) {
7429 $boundsheet_length = 12; // fixed length for a BOUNDSHEET record
7431 $boundsheet_length = 11;
7434 $offset = $this->_datasize;
7436 if ($this->_BIFF_version == 0x0600) {
7437 // add the length of the SST
7438 /* TODO: check this works for a lot of strings (> 8224 bytes) */
7439 $offset += $this->_calculateSharedStringsSizes();
7440 if ($this->_country_code != -1) {
7441 $offset += 8; // adding COUNTRY record
7443 // add the lenght of SUPBOOK, EXTERNSHEET and NAME records
7444 //$offset += 8; // FIXME: calculate real value when storing the records
7446 $total_worksheets = count($this->_worksheets);
7447 // add the length of the BOUNDSHEET records
7448 for ($i = 0; $i < $total_worksheets; $i++) {
7449 $offset += $boundsheet_length + strlen($this->_worksheets[$i]->name);
7453 for ($i = 0; $i < $total_worksheets; $i++) {
7454 $this->_worksheets[$i]->offset = $offset;
7455 $offset += $this->_worksheets[$i]->_datasize;
7457 $this->_biffsize = $offset;
7461 * Store the Excel FONT records.
7465 function _storeAllFonts()
7467 // tmp_format is added by the constructor. We use this to write the default XF's
7468 $format = $this->_tmp_format;
7469 $font = $format->getFont();
7471 // Note: Fonts are 0-indexed. According to the SDK there is no index 4,
7472 // so the following fonts are 0, 1, 2, 3, 5
7474 for ($i = 1; $i <= 5; $i++){
7475 $this->_append($font);
7478 // Iterate through the XF objects and write a FONT record if it isn't the
7479 // same as the default FONT and if it hasn't already been used.
7482 $index = 6; // The first user defined FONT
7484 $key = $format->getFontKey(); // The default font from _tmp_format
7485 $fonts[$key] = 0; // Index of the default font
7487 $total_formats = count($this->_formats);
7488 for ($i = 0; $i < $total_formats; $i++) {
7489 $key = $this->_formats[$i]->getFontKey();
7490 if (isset($fonts[$key])) {
7491 // FONT has already been used
7492 $this->_formats[$i]->font_index = $fonts[$key];
7494 // Add a new FONT record
7495 $fonts[$key] = $index;
7496 $this->_formats[$i]->font_index = $index;
7498 $font = $this->_formats[$i]->getFont();
7499 $this->_append($font);
7505 * Store user defined numerical formats i.e. FORMAT records
7509 function _storeAllNumFormats()
7511 // Leaning num_format syndrome
7512 $hash_num_formats = array();
7513 $num_formats = array();
7516 // Iterate through the XF objects and write a FORMAT record if it isn't a
7517 // built-in format type and if the FORMAT string hasn't already been used.
7518 $total_formats = count($this->_formats);
7519 for ($i = 0; $i < $total_formats; $i++) {
7520 $num_format = $this->_formats[$i]->_num_format;
7522 // Check if $num_format is an index to a built-in format.
7523 // Also check for a string of zeros, which is a valid format string
7524 // but would evaluate to zero.
7526 if (!preg_match("/^0+\d/", $num_format)) {
7527 if (preg_match("/^\d+$/", $num_format)) { // built-in format
7532 if (isset($hash_num_formats[$num_format])) {
7533 // FORMAT has already been used
7534 $this->_formats[$i]->_num_format = $hash_num_formats[$num_format];
7537 $hash_num_formats[$num_format] = $index;
7538 $this->_formats[$i]->_num_format = $index;
7539 array_push($num_formats,$num_format);
7544 // Write the new FORMAT records starting from 0xA4
7546 foreach ($num_formats as $num_format) {
7547 $this->_storeNumFormat($num_format,$index);
7553 * Write all XF records.
7557 function _storeAllXfs()
7559 // _tmp_format is added by the constructor. We use this to write the default XF's
7560 // The default font index is 0
7562 $format = $this->_tmp_format;
7563 for ($i = 0; $i <= 14; $i++) {
7564 $xf = $format->getXf('style'); // Style XF
7565 $this->_append($xf);
7568 $xf = $format->getXf('cell'); // Cell XF
7569 $this->_append($xf);
7572 $total_formats = count($this->_formats);
7573 for ($i = 0; $i < $total_formats; $i++) {
7574 $xf = $this->_formats[$i]->getXf('cell');
7575 $this->_append($xf);
7580 * Write all STYLE records.
7584 function _storeAllStyles()
7586 $this->_storeStyle();
7590 * Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for
7595 function _storeExterns()
7597 // Create EXTERNCOUNT with number of worksheets
7598 $this->_storeExterncount(count($this->_worksheets));
7600 // Create EXTERNSHEET for each worksheet
7601 foreach ($this->_sheetnames as $sheetname) {
7602 $this->_storeExternsheet($sheetname);
7607 * Write the NAME record to define the print area and the repeat rows and cols.
7611 function _storeNames()
7613 // Create the print area NAME records
7614 $total_worksheets = count($this->_worksheets);
7615 for ($i = 0; $i < $total_worksheets; $i++) {
7616 // Write a Name record if the print area has been defined
7617 if (isset($this->_worksheets[$i]->print_rowmin)) {
7618 $this->_storeNameShort(
7619 $this->_worksheets[$i]->index,
7621 $this->_worksheets[$i]->print_rowmin,
7622 $this->_worksheets[$i]->print_rowmax,
7623 $this->_worksheets[$i]->print_colmin,
7624 $this->_worksheets[$i]->print_colmax
7629 // Create the print title NAME records
7630 $total_worksheets = count($this->_worksheets);
7631 for ($i = 0; $i < $total_worksheets; $i++) {
7632 $rowmin = $this->_worksheets[$i]->title_rowmin;
7633 $rowmax = $this->_worksheets[$i]->title_rowmax;
7634 $colmin = $this->_worksheets[$i]->title_colmin;
7635 $colmax = $this->_worksheets[$i]->title_colmax;
7637 // Determine if row + col, row, col or nothing has been defined
7638 // and write the appropriate record
7640 if (isset($rowmin) && isset($colmin)) {
7641 // Row and column titles have been defined.
7642 // Row title has been defined.
7643 $this->_storeNameLong(
7644 $this->_worksheets[$i]->index,
7651 } elseif (isset($rowmin)) {
7652 // Row title has been defined.
7653 $this->_storeNameShort(
7654 $this->_worksheets[$i]->index,
7661 } elseif (isset($colmin)) {
7662 // Column title has been defined.
7663 $this->_storeNameShort(
7664 $this->_worksheets[$i]->index,
7672 // Print title hasn't been defined.
7680 /******************************************************************************
7687 * Stores the CODEPAGE biff record.
7691 function _storeCodepage()
7693 $record = 0x0042; // Record identifier
7694 $length = 0x0002; // Number of bytes to follow
7695 $cv = $this->_codepage; // The code page
7697 $header = pack('vv', $record, $length);
7698 $data = pack('v', $cv);
7700 $this->_append($header . $data);
7704 * Write Excel BIFF WINDOW1 record.
7708 function _storeWindow1()
7710 $record = 0x003D; // Record identifier
7711 $length = 0x0012; // Number of bytes to follow
7713 $xWn = 0x0000; // Horizontal position of window
7714 $yWn = 0x0000; // Vertical position of window
7715 $dxWn = 0x25BC; // Width of window
7716 $dyWn = 0x1572; // Height of window
7718 $grbit = 0x0038; // Option flags
7719 $ctabsel = $this->_selected; // Number of workbook tabs selected
7720 $wTabRatio = 0x0258; // Tab to scrollbar ratio
7722 $itabFirst = $this->_firstsheet; // 1st displayed worksheet
7723 $itabCur = $this->_activesheet; // Active worksheet
7725 $header = pack("vv", $record, $length);
7726 $data = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,
7728 $itabCur, $itabFirst,
7729 $ctabsel, $wTabRatio);
7730 $this->_append($header . $data);
7734 * Writes Excel BIFF BOUNDSHEET record.
7735 * FIXME: inconsistent with BIFF documentation
7737 * @param string $sheetname Worksheet name
7738 * @param integer $offset Location of worksheet BOF
7741 function _storeBoundsheet($sheetname,$offset)
7743 $record = 0x0085; // Record identifier
7745 if ($this->_BIFF_version == 0x0600) // Tried to fix the correct handling here, with the
7746 { // corrected specification from M$ - Joe Hunt 2009-03-08
7747 global $encoding_string;
7748 if ($encoding_string == 'UTF-16LE')
7750 $strlen = function_exists('mb_strlen') ? mb_strlen($sheetname, 'UTF-16LE') : (strlen($sheetname) / 2);
7753 else if ($encoding_string != '')
7755 $sheetname = iconv($encoding_string, 'UTF-16LE', $sheetname);
7756 $strlen = function_exists('mb_strlen') ? mb_strlen($sheetname, 'UTF-16LE') : (strlen($sheetname) / 2);
7759 if ($strlen % 2 != 0)
7763 //$strlen = strlen($sheetname);
7764 $length = 0x08 + $strlen; // Number of bytes to follow
7766 $strlen = strlen($sheetname);
7767 $length = 0x07 + $strlen; // Number of bytes to follow
7770 $grbit = 0x0000; // Visibility and sheet type
7771 $cch = $strlen; // Length of sheet name
7773 $header = pack("vv", $record, $length);
7774 if ($this->_BIFF_version == 0x0600) {
7775 $data = pack("VvCC", $offset, $grbit, $cch, $encoding);
7777 $data = pack("VvC", $offset, $grbit, $cch);
7780 if ($this->_BIFF_version == 0x0600)
7782 $strlen = strlen($sheetname);
7783 $length = 0x08 + $strlen; // Number of bytes to follow
7785 $strlen = strlen($sheetname);
7786 $length = 0x07 + $strlen; // Number of bytes to follow
7789 $grbit = 0x0000; // Visibility and sheet type
7790 $cch = $strlen; // Length of sheet name
7792 $header = pack("vv", $record, $length);
7793 if ($this->_BIFF_version == 0x0600) {
7794 $data = pack("Vvv", $offset, $grbit, $cch);
7796 $data = pack("VvC", $offset, $grbit, $cch);
7798 $this->_append($header.$data.$sheetname);
7802 * Write Internal SUPBOOK record
7806 function _storeSupbookInternal()
7808 $record = 0x01AE; // Record identifier
7809 $length = 0x0004; // Bytes to follow
7811 $header = pack("vv", $record, $length);
7812 $data = pack("vv", count($this->_worksheets), 0x0104);
7813 $this->_append($header . $data);
7817 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
7820 * @param string $sheetname Worksheet name
7823 function _storeExternsheetBiff8()
7825 $total_references = count($this->_parser->_references);
7826 $record = 0x0017; // Record identifier
7827 $length = 2 + 6 * $total_references; // Number of bytes to follow
7829 $supbook_index = 0; // FIXME: only using internal SUPBOOK record
7830 $header = pack("vv", $record, $length);
7831 $data = pack('v', $total_references);
7832 for ($i = 0; $i < $total_references; $i++) {
7833 $data .= $this->_parser->_references[$i];
7835 $this->_append($header . $data);
7839 * Write Excel BIFF STYLE records.
7843 function _storeStyle()
7845 $record = 0x0293; // Record identifier
7846 $length = 0x0004; // Bytes to follow
7848 $ixfe = 0x8000; // Index to style XF
7849 $BuiltIn = 0x00; // Built-in style
7850 $iLevel = 0xff; // Outline style level
7852 $header = pack("vv", $record, $length);
7853 $data = pack("vCC", $ixfe, $BuiltIn, $iLevel);
7854 $this->_append($header . $data);
7859 * Writes Excel FORMAT record for non "built-in" numerical formats.
7861 * @param string $format Custom format string
7862 * @param integer $ifmt Format index code
7865 function _storeNumFormat($format, $ifmt)
7867 $record = 0x041E; // Record identifier
7869 if ($this->_BIFF_version == 0x0600) {
7870 $length = 5 + strlen($format); // Number of bytes to follow
7872 } elseif ($this->_BIFF_version == 0x0500) {
7873 $length = 3 + strlen($format); // Number of bytes to follow
7876 $cch = strlen($format); // Length of format string
7878 $header = pack("vv", $record, $length);
7879 if ($this->_BIFF_version == 0x0600) {
7880 $data = pack("vvC", $ifmt, $cch, $encoding);
7881 } elseif ($this->_BIFF_version == 0x0500) {
7882 $data = pack("vC", $ifmt, $cch);
7884 $this->_append($header . $data . $format);
7888 * Write DATEMODE record to indicate the date system in use (1904 or 1900).
7892 function _storeDatemode()
7894 $record = 0x0022; // Record identifier
7895 $length = 0x0002; // Bytes to follow
7897 $f1904 = $this->_1904; // Flag for 1904 date system
7899 $header = pack("vv", $record, $length);
7900 $data = pack("v", $f1904);
7901 $this->_append($header . $data);
7906 * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
7907 * references in the workbook.
7909 * Excel only stores references to external sheets that are used in NAME.
7910 * The workbook NAME record is required to define the print area and the repeat
7913 * A similar method is used in Worksheet.php for a slightly different purpose.
7915 * @param integer $cxals Number of external references
7918 function _storeExterncount($cxals)
7920 $record = 0x0016; // Record identifier
7921 $length = 0x0002; // Number of bytes to follow
7923 $header = pack("vv", $record, $length);
7924 $data = pack("v", $cxals);
7925 $this->_append($header . $data);
7930 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
7931 * formulas. NAME record is required to define the print area and the repeat
7934 * A similar method is used in Worksheet.php for a slightly different purpose.
7936 * @param string $sheetname Worksheet name
7939 function _storeExternsheet($sheetname)
7941 $record = 0x0017; // Record identifier
7942 $length = 0x02 + strlen($sheetname); // Number of bytes to follow
7944 $cch = strlen($sheetname); // Length of sheet name
7945 $rgch = 0x03; // Filename encoding
7947 $header = pack("vv", $record, $length);
7948 $data = pack("CC", $cch, $rgch);
7949 $this->_append($header . $data . $sheetname);
7954 * Store the NAME record in the short format that is used for storing the print
7955 * area, repeat rows only and repeat columns only.
7957 * @param integer $index Sheet index
7958 * @param integer $type Built-in name type
7959 * @param integer $rowmin Start row
7960 * @param integer $rowmax End row
7961 * @param integer $colmin Start colum
7962 * @param integer $colmax End column
7965 function _storeNameShort($index, $type, $rowmin, $rowmax, $colmin, $colmax)
7967 $record = 0x0018; // Record identifier
7968 $length = 0x0024; // Number of bytes to follow
7970 $grbit = 0x0020; // Option flags
7971 $chKey = 0x00; // Keyboard shortcut
7972 $cch = 0x01; // Length of text name
7973 $cce = 0x0015; // Length of text definition
7974 $ixals = $index + 1; // Sheet index
7975 $itab = $ixals; // Equal to ixals
7976 $cchCustMenu = 0x00; // Length of cust menu text
7977 $cchDescription = 0x00; // Length of description text
7978 $cchHelptopic = 0x00; // Length of help topic text
7979 $cchStatustext = 0x00; // Length of status bar text
7980 $rgch = $type; // Built-in name type
7983 $unknown04 = 0xffff-$index;
7984 $unknown05 = 0x0000;
7985 $unknown06 = 0x0000;
7986 $unknown07 = 0x1087;
7987 $unknown08 = 0x8005;
7989 $header = pack("vv", $record, $length);
7990 $data = pack("v", $grbit);
7991 $data .= pack("C", $chKey);
7992 $data .= pack("C", $cch);
7993 $data .= pack("v", $cce);
7994 $data .= pack("v", $ixals);
7995 $data .= pack("v", $itab);
7996 $data .= pack("C", $cchCustMenu);
7997 $data .= pack("C", $cchDescription);
7998 $data .= pack("C", $cchHelptopic);
7999 $data .= pack("C", $cchStatustext);
8000 $data .= pack("C", $rgch);
8001 $data .= pack("C", $unknown03);
8002 $data .= pack("v", $unknown04);
8003 $data .= pack("v", $unknown05);
8004 $data .= pack("v", $unknown06);
8005 $data .= pack("v", $unknown07);
8006 $data .= pack("v", $unknown08);
8007 $data .= pack("v", $index);
8008 $data .= pack("v", $index);
8009 $data .= pack("v", $rowmin);
8010 $data .= pack("v", $rowmax);
8011 $data .= pack("C", $colmin);
8012 $data .= pack("C", $colmax);
8013 $this->_append($header . $data);
8018 * Store the NAME record in the long format that is used for storing the repeat
8019 * rows and columns when both are specified. This shares a lot of code with
8020 * _storeNameShort() but we use a separate method to keep the code clean.
8021 * Code abstraction for reuse can be carried too far, and I should know. ;-)
8023 * @param integer $index Sheet index
8024 * @param integer $type Built-in name type
8025 * @param integer $rowmin Start row
8026 * @param integer $rowmax End row
8027 * @param integer $colmin Start colum
8028 * @param integer $colmax End column
8031 function _storeNameLong($index, $type, $rowmin, $rowmax, $colmin, $colmax)
8033 $record = 0x0018; // Record identifier
8034 $length = 0x003d; // Number of bytes to follow
8035 $grbit = 0x0020; // Option flags
8036 $chKey = 0x00; // Keyboard shortcut
8037 $cch = 0x01; // Length of text name
8038 $cce = 0x002e; // Length of text definition
8039 $ixals = $index + 1; // Sheet index
8040 $itab = $ixals; // Equal to ixals
8041 $cchCustMenu = 0x00; // Length of cust menu text
8042 $cchDescription = 0x00; // Length of description text
8043 $cchHelptopic = 0x00; // Length of help topic text
8044 $cchStatustext = 0x00; // Length of status bar text
8045 $rgch = $type; // Built-in name type
8048 $unknown02 = 0x002b;
8050 $unknown04 = 0xffff-$index;
8051 $unknown05 = 0x0000;
8052 $unknown06 = 0x0000;
8053 $unknown07 = 0x1087;
8054 $unknown08 = 0x8008;
8056 $header = pack("vv", $record, $length);
8057 $data = pack("v", $grbit);
8058 $data .= pack("C", $chKey);
8059 $data .= pack("C", $cch);
8060 $data .= pack("v", $cce);
8061 $data .= pack("v", $ixals);
8062 $data .= pack("v", $itab);
8063 $data .= pack("C", $cchCustMenu);
8064 $data .= pack("C", $cchDescription);
8065 $data .= pack("C", $cchHelptopic);
8066 $data .= pack("C", $cchStatustext);
8067 $data .= pack("C", $rgch);
8068 $data .= pack("C", $unknown01);
8069 $data .= pack("v", $unknown02);
8070 // Column definition
8071 $data .= pack("C", $unknown03);
8072 $data .= pack("v", $unknown04);
8073 $data .= pack("v", $unknown05);
8074 $data .= pack("v", $unknown06);
8075 $data .= pack("v", $unknown07);
8076 $data .= pack("v", $unknown08);
8077 $data .= pack("v", $index);
8078 $data .= pack("v", $index);
8079 $data .= pack("v", 0x0000);
8080 $data .= pack("v", 0x3fff);
8081 $data .= pack("C", $colmin);
8082 $data .= pack("C", $colmax);
8084 $data .= pack("C", $unknown03);
8085 $data .= pack("v", $unknown04);
8086 $data .= pack("v", $unknown05);
8087 $data .= pack("v", $unknown06);
8088 $data .= pack("v", $unknown07);
8089 $data .= pack("v", $unknown08);
8090 $data .= pack("v", $index);
8091 $data .= pack("v", $index);
8092 $data .= pack("v", $rowmin);
8093 $data .= pack("v", $rowmax);
8094 $data .= pack("C", 0x00);
8095 $data .= pack("C", 0xff);
8097 $data .= pack("C", 0x10);
8098 $this->_append($header . $data);
8102 * Stores the COUNTRY record for localization
8106 function _storeCountry()
8108 $record = 0x008C; // Record identifier
8109 $length = 4; // Number of bytes to follow
8111 $header = pack('vv', $record, $length);
8112 /* using the same country code always for simplicity */
8113 $data = pack('vv', $this->_country_code, $this->_country_code);
8114 $this->_append($header . $data);
8118 * Stores the PALETTE biff record.
8122 function _storePalette()
8124 $aref = $this->_palette;
8126 $record = 0x0092; // Record identifier
8127 $length = 2 + 4 * count($aref); // Number of bytes to follow
8128 $ccv = count($aref); // Number of RGB values to follow
8129 $data = ''; // The RGB data
8131 // Pack the RGB data
8132 foreach ($aref as $color) {
8133 foreach ($color as $byte) {
8134 $data .= pack("C",$byte);
8138 $header = pack("vvv", $record, $length, $ccv);
8139 $this->_append($header . $data);
8144 * Handling of the SST continue blocks is complicated by the need to include an
8145 * additional continuation byte depending on whether the string is split between
8146 * blocks or whether it starts at the beginning of the block. (There are also
8147 * additional complications that will arise later when/if Rich Strings are
8152 function _calculateSharedStringsSizes()
8154 /* Iterate through the strings to calculate the CONTINUE block sizes.
8155 For simplicity we use the same size for the SST and CONTINUE records:
8156 8228 : Maximum Excel97 block size
8157 -4 : Length of block header
8158 -8 : Length of additional SST header information
8159 -8 : Arbitrary number to keep within _add_continue() limit = 8208
8161 $continue_limit = 8208;
8164 $this->_block_sizes = array();
8167 foreach (array_keys($this->_str_table) as $string) {
8168 $string_length = strlen($string);
8169 $headerinfo = unpack("vlength/Cencoding", $string);
8170 $encoding = $headerinfo["encoding"];
8173 // Block length is the total length of the strings that will be
8174 // written out in a single SST or CONTINUE block.
8175 $block_length += $string_length;
8177 // We can write the string if it doesn't cross a CONTINUE boundary
8178 if ($block_length < $continue_limit) {
8179 $written += $string_length;
8183 // Deal with the cases where the next string to be written will exceed
8184 // the CONTINUE boundary. If the string is very long it may need to be
8185 // written in more than one CONTINUE record.
8186 while ($block_length >= $continue_limit) {
8188 // We need to avoid the case where a string is continued in the first
8189 // n bytes that contain the string header information.
8190 $header_length = 3; // Min string + header size -1
8191 $space_remaining = $continue_limit - $written - $continue;
8194 /* TODO: Unicode data should only be split on char (2 byte)
8195 boundaries. Therefore, in some cases we need to reduce the
8200 // Only applies to Unicode strings
8201 if ($encoding == 1) {
8202 // Min string + header size -1
8205 if ($space_remaining > $header_length) {
8206 // String contains 3 byte header => split on odd boundary
8207 if (!$split_string && $space_remaining % 2 != 1) {
8211 // Split section without header => split on even boundary
8212 else if ($split_string && $space_remaining % 2 == 1) {
8222 if ($space_remaining > $header_length) {
8223 // Write as much as possible of the string in the current block
8224 $written += $space_remaining;
8226 // Reduce the current block length by the amount written
8227 $block_length -= $continue_limit - $continue - $align;
8229 // Store the max size for this block
8230 $this->_block_sizes[] = $continue_limit - $align;
8232 // If the current string was split then the next CONTINUE block
8233 // should have the string continue flag (grbit) set unless the
8234 // split string fits exactly into the remaining space.
8235 if ($block_length > 0) {
8241 // Store the max size for this block
8242 $this->_block_sizes[] = $written + $continue;
8244 // Not enough space to start the string in the current block
8245 $block_length -= $continue_limit - $space_remaining - $continue;
8250 // If the string (or substr) is small enough we can write it in the
8251 // new CONTINUE block. Else, go through the loop again to write it in
8252 // one or more CONTINUE blocks
8253 if ($block_length < $continue_limit) {
8254 $written = $block_length;
8261 // Store the max size for the last block unless it is empty
8262 if ($written + $continue) {
8263 $this->_block_sizes[] = $written + $continue;
8267 /* Calculate the total length of the SST and associated CONTINUEs (if any).
8268 The SST record will have a length even if it contains no strings.
8269 This length is required to set the offsets in the BOUNDSHEET records since
8270 they must be written before the SST records
8273 $tmp_block_sizes = array();
8274 $tmp_block_sizes = $this->_block_sizes;
8277 if (!empty($tmp_block_sizes)) {
8278 $length += array_shift($tmp_block_sizes); // SST
8280 while (!empty($tmp_block_sizes)) {
8281 $length += 4 + array_shift($tmp_block_sizes); // CONTINUEs
8288 * Write all of the workbooks strings into an indexed array.
8289 * See the comments in _calculate_shared_string_sizes() for more information.
8291 * The Excel documentation says that the SST record should be followed by an
8292 * EXTSST record. The EXTSST record is a hash table that is used to optimise
8293 * access to SST. However, despite the documentation it doesn't seem to be
8294 * required so we will ignore it.
8298 function _storeSharedStringsTable()
8300 $record = 0x00fc; // Record identifier
8301 $length = 0x0008; // Number of bytes to follow
8304 // Iterate through the strings to calculate the CONTINUE block sizes
8305 $continue_limit = 8208;
8310 // sizes are upside down
8311 $tmp_block_sizes = $this->_block_sizes;
8312 // $tmp_block_sizes = array_reverse($this->_block_sizes);
8314 // The SST record is required even if it contains no strings. Thus we will
8315 // always have a length
8317 if (!empty($tmp_block_sizes)) {
8318 $length = 8 + array_shift($tmp_block_sizes);
8325 // Write the SST block header information
8326 $header = pack("vv", $record, $length);
8327 $data = pack("VV", $this->_str_total, $this->_str_unique);
8328 $this->_append($header . $data);
8330 /* TODO: not good for performance */
8331 foreach (array_keys($this->_str_table) as $string) {
8333 $string_length = strlen($string);
8334 $headerinfo = unpack("vlength/Cencoding", $string);
8335 $encoding = $headerinfo["encoding"];
8338 // Block length is the total length of the strings that will be
8339 // written out in a single SST or CONTINUE block.
8341 $block_length += $string_length;
8344 // We can write the string if it doesn't cross a CONTINUE boundary
8345 if ($block_length < $continue_limit) {
8346 $this->_append($string);
8347 $written += $string_length;
8351 // Deal with the cases where the next string to be written will exceed
8352 // the CONTINUE boundary. If the string is very long it may need to be
8353 // written in more than one CONTINUE record.
8355 while ($block_length >= $continue_limit) {
8357 // We need to avoid the case where a string is continued in the first
8358 // n bytes that contain the string header information.
8360 $header_length = 3; // Min string + header size -1
8361 $space_remaining = $continue_limit - $written - $continue;
8364 // Unicode data should only be split on char (2 byte) boundaries.
8365 // Therefore, in some cases we need to reduce the amount of available
8366 // space by 1 byte to ensure the correct alignment.
8369 // Only applies to Unicode strings
8370 if ($encoding == 1) {
8371 // Min string + header size -1
8374 if ($space_remaining > $header_length) {
8375 // String contains 3 byte header => split on odd boundary
8376 if (!$split_string && $space_remaining % 2 != 1) {
8380 // Split section without header => split on even boundary
8381 else if ($split_string && $space_remaining % 2 == 1) {
8391 if ($space_remaining > $header_length) {
8392 // Write as much as possible of the string in the current block
8393 $tmp = substr($string, 0, $space_remaining);
8394 $this->_append($tmp);
8396 // The remainder will be written in the next block(s)
8397 $string = substr($string, $space_remaining);
8399 // Reduce the current block length by the amount written
8400 $block_length -= $continue_limit - $continue - $align;
8402 // If the current string was split then the next CONTINUE block
8403 // should have the string continue flag (grbit) set unless the
8404 // split string fits exactly into the remaining space.
8406 if ($block_length > 0) {
8412 // Not enough space to start the string in the current block
8413 $block_length -= $continue_limit - $space_remaining - $continue;
8417 // Write the CONTINUE block header
8418 if (!empty($this->_block_sizes)) {
8420 $length = array_shift($tmp_block_sizes);
8422 $header = pack('vv', $record, $length);
8424 $header .= pack('C', $encoding);
8426 $this->_append($header);
8429 // If the string (or substr) is small enough we can write it in the
8430 // new CONTINUE block. Else, go through the loop again to write it in
8431 // one or more CONTINUE blocks
8433 if ($block_length < $continue_limit) {
8434 $this->_append($string);
8435 $written = $block_length;