3 * Module written/ported by Xavier Noguer <xnoguer@rezebra.com>
5 * The majority of this is _NOT_ my code. I simply ported it from the
6 * PERL Spreadsheet::WriteExcel module.
8 * The author of the Spreadsheet::WriteExcel module is John McNamara
11 * I _DO_ maintain this code, and John McNamara has nothing to do with the
12 * porting of this code to PHP. Any questions directly related to this
13 * class library should be directed to me.
15 * License Information:
17 * Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets
18 * Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com
20 * This library is free software; you can redistribute it and/or
21 * modify it under the terms of the GNU Lesser General Public
22 * License as published by the Free Software Foundation; either
23 * version 2.1 of the License, or (at your option) any later version.
25 * This library is distributed in the hope that it will be useful,
26 * but WITHOUT ANY WARRANTY; without even the implied warranty of
27 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
28 * Lesser General Public License for more details.
30 * You should have received a copy of the GNU Lesser General Public
31 * License along with this library; if not, write to the Free Software
32 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
35 require_once 'Spreadsheet/Excel/Writer/Parser.php';
36 require_once 'Spreadsheet/Excel/Writer/BIFFwriter.php';
39 * Class for generating Excel Spreadsheets
41 * @author Xavier Noguer <xnoguer@rezebra.com>
42 * @category FileFormats
43 * @package Spreadsheet_Excel_Writer
46 class Spreadsheet_Excel_Writer_Worksheet extends Spreadsheet_Excel_Writer_BIFFwriter
49 * Name of the Worksheet
55 * Index for the Worksheet
61 * Reference to the (default) Format object for URLs
67 * Reference to the parser used for parsing formulas
73 * Filehandle to the temporary file for storing data
79 * Boolean indicating if we are using a temporary file for storing data
85 * Maximum number of rows for an Excel spreadsheet (BIFF5)
91 * Maximum number of columns for an Excel spreadsheet (BIFF5)
97 * Maximum number of characters for a string (LABEL record in BIFF5)
103 * First row for the DIMENSIONS record
105 * @see _storeDimensions()
110 * Last row for the DIMENSIONS record
112 * @see _storeDimensions()
117 * First column for the DIMENSIONS record
119 * @see _storeDimensions()
124 * Last column for the DIMENSIONS record
126 * @see _storeDimensions()
131 * Array containing format information for columns
137 * Array containing the selected area for the worksheet
143 * Array containing the panes for the worksheet
149 * The active pane for the worksheet
155 * Bit specifying if panes are frozen
161 * Bit specifying if the worksheet is selected
167 * The paper size (for printing) (DOCUMENT!!!)
173 * Bit specifying paper orientation (for printing). 0 => landscape, 1 => portrait
179 * The page header caption
185 * The page footer caption
191 * The horizontal centering value for the page
197 * The vertical centering value for the page
203 * The margin for the header
209 * The margin for the footer
215 * The left margin for the worksheet in inches
221 * The right margin for the worksheet in inches
227 * The top margin for the worksheet in inches
233 * The bottom margin for the worksheet in inches
239 * First row to reapeat on each printed page
245 * Last row to reapeat on each printed page
251 * First column to reapeat on each printed page
257 * First row of the area to print
263 * Last row to of the area to print
269 * First column of the area to print
275 * Last column of the area to print
281 * Whether to display RightToLeft.
287 * Whether to use outline.
293 * Auto outline styles.
299 * Whether to have outline summary below.
305 * Whether to have outline summary at the right.
314 var $_outline_row_level;
317 * Whether to fit to page when printing or not.
323 * Number of pages to fit wide
329 * Number of pages to fit high
335 * Reference to the total number of strings in the workbook
341 * Reference to the number of unique strings in the workbook
347 * Reference to the array containing all the unique strings in the workbook
353 * Number of merged cell ranges in actual record
354 * @var int $_merged_cells_counter
356 var $_merged_cells_counter = 0;
359 * Number of actual mergedcells record
360 * @var int $_merged_cells_record
362 var $_merged_cells_record = 0;
371 * Charset encoding currently used when calling writeString()
374 var $_input_encoding;
379 var $_print_gridlines;
380 var $_screen_gridlines;
398 * @param string $name The name of the new worksheet
399 * @param integer $index The index of the new worksheet
400 * @param mixed &$activesheet The current activesheet of the workbook we belong to
401 * @param mixed &$firstsheet The first worksheet in the workbook we belong to
402 * @param mixed &$url_format The default format for hyperlinks
403 * @param mixed &$parser The formula parser created for the Workbook
404 * @param string $tmp_dir The path to the directory for temporary files
407 function __construct($BIFF_version, $name,
408 $index, &$activesheet,
409 &$firstsheet, &$str_total,
410 &$str_unique, &$str_table,
411 &$url_format, &$parser,
414 // It needs to call its parent's constructor explicitly
415 parent::__construct();
416 $this->_BIFF_version = $BIFF_version;
417 $rowmax = 65536; // 16384 in Excel 5
421 $this->index = $index;
422 $this->activesheet = &$activesheet;
423 $this->firstsheet = &$firstsheet;
424 $this->_str_total = &$str_total;
425 $this->_str_unique = &$str_unique;
426 $this->_str_table = &$str_table;
427 $this->_url_format = &$url_format;
428 $this->_parser = &$parser;
430 //$this->ext_sheets = array();
431 $this->_filehandle = '';
432 $this->_using_tmpfile = true;
433 //$this->fileclosed = 0;
435 $this->_xls_rowmax = $rowmax;
436 $this->_xls_colmax = $colmax;
437 $this->_xls_strmax = 255;
438 $this->_dim_rowmin = $rowmax + 1;
439 $this->_dim_rowmax = 0;
440 $this->_dim_colmin = $colmax + 1;
441 $this->_dim_colmax = 0;
442 $this->_colinfo = array();
443 $this->_selection = array(0,0,0,0);
444 $this->_panes = array();
445 $this->_active_pane = 3;
449 $this->_paper_size = 0x0;
450 $this->_orientation = 0x1;
455 $this->_margin_head = 0.50;
456 $this->_margin_foot = 0.50;
457 $this->_margin_left = 0.75;
458 $this->_margin_right = 0.75;
459 $this->_margin_top = 1.00;
460 $this->_margin_bottom = 1.00;
462 $this->title_rowmin = null;
463 $this->title_rowmax = null;
464 $this->title_colmin = null;
465 $this->title_colmax = null;
466 $this->print_rowmin = null;
467 $this->print_rowmax = null;
468 $this->print_colmin = null;
469 $this->print_colmax = null;
471 $this->_print_gridlines = 1;
472 $this->_screen_gridlines = 1;
473 $this->_print_headers = 0;
475 $this->_fit_page = 0;
476 $this->_fit_width = 0;
477 $this->_fit_height = 0;
479 $this->_hbreaks = array();
480 $this->_vbreaks = array();
483 $this->_password = null;
485 $this->col_sizes = array();
486 $this->_row_sizes = array();
489 $this->_print_scale = 100;
491 $this->_outline_row_level = 0;
492 $this->_outline_style = 0;
493 $this->_outline_below = 1;
494 $this->_outline_right = 1;
495 $this->_outline_on = 1;
498 $this->_merged_ranges = array();
500 $this->_input_encoding = '';
502 $this->_dv = array();
504 $this->_tmp_dir = $tmp_dir;
505 $this->_tmp_file = '';
507 $this->_initialize();
511 * Open a tmp file to store the majority of the Worksheet data. If this fails,
512 * for example due to write permissions, store the data in memory. This can be
513 * slow for large files.
517 function _initialize()
519 if ($this->_using_tmpfile == false) {
523 if ($this->_tmp_dir === '' && ini_get('open_basedir') === true) {
524 // open_basedir restriction in effect - store data in memory
525 // ToDo: Let the error actually have an effect somewhere
526 $this->_using_tmpfile = false;
527 return new PEAR_Error('Temp file could not be opened since open_basedir restriction in effect - please use setTmpDir() - using memory storage instead');
530 // Open tmp file for storing Worksheet data
531 if ($this->_tmp_dir === '') {
534 // For people with open base dir restriction
535 $this->_tmp_file = tempnam($this->_tmp_dir, "Spreadsheet_Excel_Writer");
536 $fh = @fopen($this->_tmp_file, "w+b");
540 // If tmpfile() fails store data in memory
541 $this->_using_tmpfile = false;
544 $this->_filehandle = $fh;
549 * Add data to the beginning of the workbook (note the reverse order)
550 * and to the end of the workbook.
553 * @see Spreadsheet_Excel_Writer_Workbook::storeWorkbook()
554 * @param array $sheetnames The array of sheetnames from the Workbook this
555 * worksheet belongs to
557 function close($sheetnames)
559 $num_sheets = count($sheetnames);
561 /***********************************************
562 * Prepend in reverse order!!
565 // Prepend the sheet dimensions
566 $this->_storeDimensions();
568 // Prepend the sheet password
569 $this->_storePassword();
571 // Prepend the sheet protection
572 $this->_storeProtect();
574 // Prepend the page setup
575 $this->_storeSetup();
577 /* FIXME: margins are actually appended */
578 // Prepend the bottom margin
579 $this->_storeMarginBottom();
581 // Prepend the top margin
582 $this->_storeMarginTop();
584 // Prepend the right margin
585 $this->_storeMarginRight();
587 // Prepend the left margin
588 $this->_storeMarginLeft();
590 // Prepend the page vertical centering
591 $this->_storeVcenter();
593 // Prepend the page horizontal centering
594 $this->_storeHcenter();
596 // Prepend the page footer
597 $this->_storeFooter();
599 // Prepend the page header
600 $this->_storeHeader();
602 // Prepend the vertical page breaks
603 $this->_storeVbreak();
605 // Prepend the horizontal page breaks
606 $this->_storeHbreak();
609 $this->_storeWsbool();
612 $this->_storeGridset();
615 if ($this->_BIFF_version == 0x0500) {
619 // Prepend PRINTGRIDLINES
620 $this->_storePrintGridlines();
622 // Prepend PRINTHEADERS
623 $this->_storePrintHeaders();
625 // Prepend EXTERNSHEET references
626 if ($this->_BIFF_version == 0x0500) {
627 for ($i = $num_sheets; $i > 0; $i--) {
628 $sheetname = $sheetnames[$i-1];
629 $this->_storeExternsheet($sheetname);
633 // Prepend the EXTERNCOUNT of external references.
634 if ($this->_BIFF_version == 0x0500) {
635 $this->_storeExterncount($num_sheets);
638 // Prepend the COLINFO records if they exist
639 if (!empty($this->_colinfo)) {
640 $colcount = count($this->_colinfo);
641 for ($i = 0; $i < $colcount; $i++) {
642 $this->_storeColinfo($this->_colinfo[$i]);
644 $this->_storeDefcol();
647 // Prepend the BOF record
648 $this->_storeBof(0x0010);
651 * End of prepend. Read upwards from here.
652 ***********************************************/
655 $this->_storeWindow2();
657 if (!empty($this->_panes)) {
658 $this->_storePanes($this->_panes);
660 $this->_storeSelection($this->_selection);
661 $this->_storeMergedCells();
662 /* TODO: add data validity */
663 /*if ($this->_BIFF_version == 0x0600) {
664 $this->_storeDataValidity();
668 if ( $this->_tmp_file != '' ) {
669 if ( $this->_filehandle ) {
670 fclose($this->_filehandle);
671 $this->_filehandle = '';
673 @unlink($this->_tmp_file);
674 $this->_tmp_file = '';
675 $this->_using_tmpfile = true;
680 * Retrieve the worksheet name.
681 * This is usefull when creating worksheets without a name.
684 * @return string The worksheet's name
692 * Retrieves data from memory in one chunk, or from disk in $buffer
695 * @return string The data
701 // Return data stored in memory
702 if (isset($this->_data)) {
705 $fh = $this->_filehandle;
706 if ($this->_using_tmpfile) {
711 // Return data stored on disk
712 if ($this->_using_tmpfile) {
713 if ($tmp = fread($this->_filehandle, $buffer)) {
723 * Sets a merged cell range
726 * @param integer $first_row First row of the area to merge
727 * @param integer $first_col First column of the area to merge
728 * @param integer $last_row Last row of the area to merge
729 * @param integer $last_col Last column of the area to merge
731 function setMerge($first_row, $first_col, $last_row, $last_col)
733 if (($last_row < $first_row) || ($last_col < $first_col)) {
737 $max_record_ranges = floor(($this->_limit - 6) / 8);
738 if($this->_merged_cells_counter >= $max_record_ranges)
740 $this->_merged_cells_record++;
741 $this->_merged_cells_counter = 0;
744 // don't check rowmin, rowmax, etc... because we don't know when this
745 // is going to be called
746 $this->_merged_ranges[$this->_merged_cells_record][] = array($first_row, $first_col, $last_row, $last_col);
747 $this->_merged_cells_counter++;
751 * Set this worksheet as a selected worksheet,
752 * i.e. the worksheet has its tab highlighted.
762 * Set this worksheet as the active worksheet,
763 * i.e. the worksheet that is displayed when the workbook is opened.
764 * Also set it as selected.
771 $this->activesheet = $this->index;
775 * Set this worksheet as the first visible sheet.
776 * This is necessary when there are a large number of worksheets and the
777 * activated worksheet is not visible on the screen.
781 function setFirstSheet()
783 $this->firstsheet = $this->index;
787 * Set the worksheet protection flag
788 * to prevent accidental modification and to
789 * hide formulas if the locked and hidden format properties have been set.
792 * @param string $password The password to use for protecting the sheet.
794 function protect($password)
797 $this->_password = $this->_encodePassword($password);
801 * Set the width of a single column or a range of columns.
804 * @param integer $firstcol first column on the range
805 * @param integer $lastcol last column on the range
806 * @param integer $width width to set
807 * @param mixed $format The optional XF format to apply to the columns
808 * @param integer $hidden The optional hidden atribute
809 * @param integer $level The optional outline level
811 function setColumn($firstcol, $lastcol, $width, $format = null, $hidden = 0, $level = 0)
812 { // added by Dan Lynn <dan@spiderweblabs.com) on 2006-12-06
813 // look for any ranges this might overlap and remove, size or split where necessary
814 foreach ($this->_colinfo as $key => $colinfo)
816 $existing_start = $colinfo[0]; $existing_end = $colinfo[1];
817 // if the new range starts within another range
818 if ($firstcol > $existing_start && $firstcol < $existing_end)
819 { // trim the existing range to the beginning of the new range
820 $this->_colinfo[$key][1] = $firstcol - 1;
821 // if the new range lies WITHIN the existing range
822 if ($lastcol < $existing_end)
823 { // split the existing range by adding a range after our new range
824 $this->_colinfo[] = array($lastcol+1, $existing_end, $colinfo[2], &$colinfo[3], $colinfo[4], $colinfo[5]);
826 } // if the new range ends inside an existing range
827 elseif ($lastcol > $existing_start && $lastcol < $existing_end)
828 { // trim the existing range to the end of the new range
829 $this->_colinfo[$key][0] = $lastcol + 1;
830 } // if the new range completely overlaps the existing range
831 elseif ($firstcol <= $existing_start && $lastcol >= $existing_end)
833 unset($this->_colinfo[$key]);
835 } // added by Dan Lynn <dan@spiderweblabs.com) on 2006-12-06
837 $this->_colinfo = array_values($this->_colinfo);
838 $this->_colinfo[] = array($firstcol, $lastcol, $width, &$format, $hidden, $level);
839 // Set width to zero if column is hidden
840 $width = ($hidden) ? 0 : $width;
841 for ($col = $firstcol; $col <= $lastcol; $col++)
843 $this->col_sizes[$col] = $width;
848 * Set which cell or cells are selected in a worksheet
851 * @param integer $first_row first row in the selected quadrant
852 * @param integer $first_column first column in the selected quadrant
853 * @param integer $last_row last row in the selected quadrant
854 * @param integer $last_column last column in the selected quadrant
856 function setSelection($first_row,$first_column,$last_row,$last_column)
858 $this->_selection = array($first_row,$first_column,$last_row,$last_column);
862 * Set panes and mark them as frozen.
865 * @param array $panes This is the only parameter received and is composed of the following:
866 * 0 => Vertical split position,
867 * 1 => Horizontal split position
868 * 2 => Top row visible
869 * 3 => Leftmost column visible
872 function freezePanes($panes)
875 $this->_panes = $panes;
879 * Set panes and mark them as unfrozen.
882 * @param array $panes This is the only parameter received and is composed of the following:
883 * 0 => Vertical split position,
884 * 1 => Horizontal split position
885 * 2 => Top row visible
886 * 3 => Leftmost column visible
889 function thawPanes($panes)
892 $this->_panes = $panes;
896 * Set the page orientation as portrait.
900 function setPortrait()
902 $this->_orientation = 1;
906 * Set the page orientation as landscape.
910 function setLandscape()
912 $this->_orientation = 0;
916 * Set the paper type. Ex. 1 = US Letter, 9 = A4
919 * @param integer $size The type of paper size to use
921 function setPaper($size = 0)
923 $this->_paper_size = $size;
928 * Set the page header caption and optional margin.
931 * @param string $string The header text
932 * @param float $margin optional head margin in inches.
934 function setHeader($string,$margin = 0.50)
936 if (strlen($string) >= 255) {
937 //carp 'Header string must be less than 255 characters';
940 $this->_header = $string;
941 $this->_margin_head = $margin;
945 * Set the page footer caption and optional margin.
948 * @param string $string The footer text
949 * @param float $margin optional foot margin in inches.
951 function setFooter($string,$margin = 0.50)
953 if (strlen($string) >= 255) {
954 //carp 'Footer string must be less than 255 characters';
957 $this->_footer = $string;
958 $this->_margin_foot = $margin;
962 * Center the page horinzontally.
965 * @param integer $center the optional value for centering. Defaults to 1 (center).
967 function centerHorizontally($center = 1)
969 $this->_hcenter = $center;
973 * Center the page vertically.
976 * @param integer $center the optional value for centering. Defaults to 1 (center).
978 function centerVertically($center = 1)
980 $this->_vcenter = $center;
984 * Set all the page margins to the same value in inches.
987 * @param float $margin The margin to set in inches
989 function setMargins($margin)
991 $this->setMarginLeft($margin);
992 $this->setMarginRight($margin);
993 $this->setMarginTop($margin);
994 $this->setMarginBottom($margin);
998 * Set the left and right margins to the same value in inches.
1001 * @param float $margin The margin to set in inches
1003 function setMargins_LR($margin)
1005 $this->setMarginLeft($margin);
1006 $this->setMarginRight($margin);
1010 * Set the top and bottom margins to the same value in inches.
1013 * @param float $margin The margin to set in inches
1015 function setMargins_TB($margin)
1017 $this->setMarginTop($margin);
1018 $this->setMarginBottom($margin);
1022 * Set the left margin in inches.
1025 * @param float $margin The margin to set in inches
1027 function setMarginLeft($margin = 0.75)
1029 $this->_margin_left = $margin;
1033 * Set the right margin in inches.
1036 * @param float $margin The margin to set in inches
1038 function setMarginRight($margin = 0.75)
1040 $this->_margin_right = $margin;
1044 * Set the top margin in inches.
1047 * @param float $margin The margin to set in inches
1049 function setMarginTop($margin = 1.00)
1051 $this->_margin_top = $margin;
1055 * Set the bottom margin in inches.
1058 * @param float $margin The margin to set in inches
1060 function setMarginBottom($margin = 1.00)
1062 $this->_margin_bottom = $margin;
1066 * Set the rows to repeat at the top of each printed page.
1069 * @param integer $first_row First row to repeat
1070 * @param integer $last_row Last row to repeat. Optional.
1072 function repeatRows($first_row, $last_row = null)
1074 $this->title_rowmin = $first_row;
1075 if (isset($last_row)) { //Second row is optional
1076 $this->title_rowmax = $last_row;
1078 $this->title_rowmax = $first_row;
1083 * Set the columns to repeat at the left hand side of each printed page.
1086 * @param integer $first_col First column to repeat
1087 * @param integer $last_col Last column to repeat. Optional.
1089 function repeatColumns($first_col, $last_col = null)
1091 $this->title_colmin = $first_col;
1092 if (isset($last_col)) { // Second col is optional
1093 $this->title_colmax = $last_col;
1095 $this->title_colmax = $first_col;
1100 * Set the area of each worksheet that will be printed.
1103 * @param integer $first_row First row of the area to print
1104 * @param integer $first_col First column of the area to print
1105 * @param integer $last_row Last row of the area to print
1106 * @param integer $last_col Last column of the area to print
1108 function printArea($first_row, $first_col, $last_row, $last_col)
1110 $this->print_rowmin = $first_row;
1111 $this->print_colmin = $first_col;
1112 $this->print_rowmax = $last_row;
1113 $this->print_colmax = $last_col;
1118 * Set the option to hide gridlines on the printed page.
1122 function hideGridlines()
1124 $this->_print_gridlines = 0;
1128 * Set the option to hide gridlines on the worksheet (as seen on the screen).
1132 function hideScreenGridlines()
1134 $this->_screen_gridlines = 0;
1138 * Set the option to print the row and column headers on the printed page.
1141 * @param integer $print Whether to print the headers or not. Defaults to 1 (print).
1143 function printRowColHeaders($print = 1)
1145 $this->_print_headers = $print;
1149 * Set the vertical and horizontal number of pages that will define the maximum area printed.
1150 * It doesn't seem to work with OpenOffice.
1153 * @param integer $width Maximun width of printed area in pages
1154 * @param integer $height Maximun heigth of printed area in pages
1155 * @see setPrintScale()
1157 function fitToPages($width, $height)
1159 $this->_fit_page = 1;
1160 $this->_fit_width = $width;
1161 $this->_fit_height = $height;
1165 * Store the horizontal page breaks on a worksheet (for printing).
1166 * The breaks represent the row after which the break is inserted.
1169 * @param array $breaks Array containing the horizontal page breaks
1171 function setHPagebreaks($breaks)
1173 foreach ($breaks as $break) {
1174 array_push($this->_hbreaks, $break);
1179 * Store the vertical page breaks on a worksheet (for printing).
1180 * The breaks represent the column after which the break is inserted.
1183 * @param array $breaks Array containing the vertical page breaks
1185 function setVPagebreaks($breaks)
1187 foreach ($breaks as $break) {
1188 array_push($this->_vbreaks, $break);
1194 * Set the worksheet zoom factor.
1197 * @param integer $scale The zoom factor
1199 function setZoom($scale = 100)
1201 // Confine the scale to Excel's range
1202 if ($scale < 10 || $scale > 400) {
1203 $this->raiseError("Zoom factor $scale outside range: 10 <= zoom <= 400");
1207 $this->_zoom = floor($scale);
1211 * Set the scale factor for the printed page.
1212 * It turns off the "fit to page" option
1215 * @param integer $scale The optional scale factor. Defaults to 100
1217 function setPrintScale($scale = 100)
1219 // Confine the scale to Excel's range
1220 if ($scale < 10 || $scale > 400) {
1221 $this->raiseError("Print scale $scale outside range: 10 <= zoom <= 400");
1225 // Turn off "fit to page" option
1226 $this->_fit_page = 0;
1228 $this->_print_scale = floor($scale);
1232 * Map to the appropriate write method acording to the token recieved.
1235 * @param integer $row The row of the cell we are writing to
1236 * @param integer $col The column of the cell we are writing to
1237 * @param mixed $token What we are writing
1238 * @param mixed $format The optional format to apply to the cell
1240 function write($row, $col, $token, $format = null)
1242 // Check for a cell reference in A1 notation and substitute row and column
1243 /*if ($_[0] =~ /^\D/) {
1244 @_ = $this->_substituteCellref(@_);
1247 if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", $token)) {
1249 return $this->writeNumber($row, $col, $token, $format);
1250 } elseif (preg_match("/^[fh]tt?p:\/\//", $token)) {
1251 // Match http or ftp URL
1252 return $this->writeUrl($row, $col, $token, '', $format);
1253 } elseif (preg_match("/^mailto:/", $token)) {
1255 return $this->writeUrl($row, $col, $token, '', $format);
1256 } elseif (preg_match("/^(?:in|ex)ternal:/", $token)) {
1257 // Match internal or external sheet link
1258 return $this->writeUrl($row, $col, $token, '', $format);
1259 } elseif (preg_match("/^=/", $token)) {
1261 return $this->writeFormula($row, $col, $token, $format);
1262 } elseif ($token == '') {
1264 return $this->writeBlank($row, $col, $format);
1266 // Default: match string
1267 return $this->writeString($row, $col, $token, $format);
1272 * Write an array of values as a row
1275 * @param integer $row The row we are writing to
1276 * @param integer $col The first col (leftmost col) we are writing to
1277 * @param array $val The array of values to write
1278 * @param mixed $format The optional format to apply to the cell
1279 * @return mixed PEAR_Error on failure
1282 function writeRow($row, $col, $val, $format = null)
1285 if (is_array($val)) {
1286 foreach ($val as $v) {
1288 $this->writeCol($row, $col, $v, $format);
1290 $this->write($row, $col, $v, $format);
1295 $retval = new PEAR_Error('$val needs to be an array');
1301 * Write an array of values as a column
1304 * @param integer $row The first row (uppermost row) we are writing to
1305 * @param integer $col The col we are writing to
1306 * @param array $val The array of values to write
1307 * @param mixed $format The optional format to apply to the cell
1308 * @return mixed PEAR_Error on failure
1311 function writeCol($row, $col, $val, $format = null)
1314 if (is_array($val)) {
1315 foreach ($val as $v) {
1316 $this->write($row, $col, $v, $format);
1320 $retval = new PEAR_Error('$val needs to be an array');
1326 * Returns an index to the XF record in the workbook
1329 * @param mixed &$format The optional XF format
1330 * @return integer The XF record index
1332 function _XF(&$format)
1335 return($format->getXfIndex());
1342 /******************************************************************************
1343 *******************************************************************************
1350 * Store Worksheet data in memory using the parent's class append() or to a
1351 * temporary file, the default.
1354 * @param string $data The binary data to append
1356 function _append($data)
1358 if ($this->_using_tmpfile) {
1359 // Add CONTINUE records if necessary
1360 if (strlen($data) > $this->_limit) {
1361 $data = $this->_addContinue($data);
1363 fwrite($this->_filehandle, $data);
1364 $this->_datasize += strlen($data);
1366 parent::_append($data);
1371 * Substitute an Excel cell reference in A1 notation for zero based row and
1372 * column values in an argument list.
1374 * Ex: ("A4", "Hello") is converted to (3, 0, "Hello").
1377 * @param string $cell The cell reference. Or range of cells.
1380 function _substituteCellref($cell)
1382 $cell = strtoupper($cell);
1384 // Convert a column range: 'A:A' or 'B:G'
1385 if (preg_match("/([A-I]?[A-Z]):([A-I]?[A-Z])/", $cell, $match)) {
1386 list($no_use, $col1) = $this->_cellToRowcol($match[1] .'1'); // Add a dummy row
1387 list($no_use, $col2) = $this->_cellToRowcol($match[2] .'1'); // Add a dummy row
1388 return(array($col1, $col2));
1391 // Convert a cell range: 'A1:B7'
1392 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/", $cell, $match)) {
1393 list($row1, $col1) = $this->_cellToRowcol($match[1]);
1394 list($row2, $col2) = $this->_cellToRowcol($match[2]);
1395 return(array($row1, $col1, $row2, $col2));
1398 // Convert a cell reference: 'A1' or 'AD2000'
1399 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+)/", $cell)) {
1400 list($row1, $col1) = $this->_cellToRowcol($match[1]);
1401 return(array($row1, $col1));
1404 // TODO use real error codes
1405 $this->raiseError("Unknown cell reference $cell", 0, PEAR_ERROR_DIE);
1409 * Convert an Excel cell reference in A1 notation to a zero based row and column
1410 * reference; converts C1 to (0, 2).
1413 * @param string $cell The cell reference.
1414 * @return array containing (row, column)
1416 function _cellToRowcol($cell)
1418 preg_match("/\$?([A-I]?[A-Z])\$?(\d+)/",$cell,$match);
1422 // Convert base26 column string to number
1423 $chars = explode('', $col);
1428 $char = array_pop($chars); // LS char first
1429 $col += (ord($char) -ord('A') +1) * pow(26,$expn);
1433 // Convert 1-index to zero-index
1437 return(array($row, $col));
1441 * Based on the algorithm provided by Daniel Rentz of OpenOffice.
1444 * @param string $plaintext The password to be encoded in plaintext.
1445 * @return string The encoded password
1447 function _encodePassword($plaintext)
1450 $i = 1; // char position
1452 // split the plain text password in its component characters
1453 $chars = preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY);
1454 foreach ($chars as $char) {
1455 $value = ord($char) << $i; // shifted ASCII value
1456 $rotated_bits = $value >> 15; // rotated bits beyond bit 15
1457 $value &= 0x7fff; // first 15 bits
1458 $password ^= ($value | $rotated_bits);
1462 $password ^= strlen($plaintext);
1463 $password ^= 0xCE4B;
1469 * This method sets the properties for outlining and grouping. The defaults
1470 * correspond to Excel's defaults.
1472 * @param bool $visible
1473 * @param bool $symbols_below
1474 * @param bool $symbols_right
1475 * @param bool $auto_style
1477 function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false)
1479 $this->_outline_on = $visible;
1480 $this->_outline_below = $symbols_below;
1481 $this->_outline_right = $symbols_right;
1482 $this->_outline_style = $auto_style;
1484 // Ensure this is a boolean vale for Window2
1485 if ($this->_outline_on) {
1486 $this->_outline_on = 1;
1491 * This method sets the worksheet direction to right-to-left (RTL)
1495 function setRTL($rtl = true)
1497 $this->_Arabic = ($rtl ? 1 : 0);
1500 /******************************************************************************
1501 *******************************************************************************
1508 * Write a double to the specified row and column (zero indexed).
1509 * An integer can be written as a double. Excel will display an
1510 * integer. $format is optional.
1512 * Returns 0 : normal termination
1513 * -2 : row or column out of range
1516 * @param integer $row Zero indexed row
1517 * @param integer $col Zero indexed column
1518 * @param float $num The number to write
1519 * @param mixed $format The optional XF format
1522 function writeNumber($row, $col, $num, $format = null)
1524 $record = 0x0203; // Record identifier
1525 $length = 0x000E; // Number of bytes to follow
1527 $xf = $this->_XF($format); // The cell format
1529 // Check that row and col are valid and store max and min values
1530 if ($row >= $this->_xls_rowmax) {
1533 if ($col >= $this->_xls_colmax) {
1536 if ($row < $this->_dim_rowmin) {
1537 $this->_dim_rowmin = $row;
1539 if ($row > $this->_dim_rowmax) {
1540 $this->_dim_rowmax = $row;
1542 if ($col < $this->_dim_colmin) {
1543 $this->_dim_colmin = $col;
1545 if ($col > $this->_dim_colmax) {
1546 $this->_dim_colmax = $col;
1549 $header = pack("vv", $record, $length);
1550 $data = pack("vvv", $row, $col, $xf);
1551 $xl_double = pack("d", $num);
1552 if ($this->_byte_order) { // if it's Big Endian
1553 $xl_double = strrev($xl_double);
1556 $this->_append($header.$data.$xl_double);
1561 * Write a string to the specified row and column (zero indexed).
1562 * NOTE: there is an Excel 5 defined limit of 255 characters.
1563 * $format is optional.
1564 * Returns 0 : normal termination
1565 * -2 : row or column out of range
1566 * -3 : long string truncated to 255 chars
1569 * @param integer $row Zero indexed row
1570 * @param integer $col Zero indexed column
1571 * @param string $str The string to write
1572 * @param mixed $format The XF format for the cell
1575 function writeString($row, $col, $str, $format = null)
1577 if ($this->_BIFF_version == 0x0600) {
1578 return $this->writeStringBIFF8($row, $col, $str, $format);
1580 $strlen = strlen($str);
1581 $record = 0x0204; // Record identifier
1582 $length = 0x0008 + $strlen; // Bytes to follow
1583 $xf = $this->_XF($format); // The cell format
1587 // Check that row and col are valid and store max and min values
1588 if ($row >= $this->_xls_rowmax) {
1591 if ($col >= $this->_xls_colmax) {
1594 if ($row < $this->_dim_rowmin) {
1595 $this->_dim_rowmin = $row;
1597 if ($row > $this->_dim_rowmax) {
1598 $this->_dim_rowmax = $row;
1600 if ($col < $this->_dim_colmin) {
1601 $this->_dim_colmin = $col;
1603 if ($col > $this->_dim_colmax) {
1604 $this->_dim_colmax = $col;
1607 if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars
1608 $str = substr($str, 0, $this->_xls_strmax);
1609 $length = 0x0008 + $this->_xls_strmax;
1610 $strlen = $this->_xls_strmax;
1614 $header = pack("vv", $record, $length);
1615 $data = pack("vvvv", $row, $col, $xf, $strlen);
1616 $this->_append($header . $data . $str);
1621 * Sets Input Encoding for writing strings
1624 * @param string $encoding The encoding. Ex: 'UTF-16LE', 'utf-8', 'ISO-859-7'
1626 function setInputEncoding($encoding)
1628 if ($encoding != 'UTF-16LE' && !function_exists('iconv')) {
1629 $this->raiseError("Using an input encoding other than UTF-16LE requires PHP support for iconv");
1631 $this->_input_encoding = $encoding;
1635 * Write a string to the specified row and column (zero indexed).
1636 * This is the BIFF8 version (no 255 chars limit).
1637 * $format is optional.
1638 * Returns 0 : normal termination
1639 * -2 : row or column out of range
1640 * -3 : long string truncated to 255 chars
1643 * @param integer $row Zero indexed row
1644 * @param integer $col Zero indexed column
1645 * @param string $str The string to write
1646 * @param mixed $format The XF format for the cell
1649 function writeStringBIFF8($row, $col, $str, $format = null)
1651 // If the string is Unicode and contains any "surrogate pairs" then using mb_strlen($str, 'UTF-16LE')
1652 // as the string length will cause a "found unreadable content" error when opening the worksheet in Excel
1653 // (apparently the length is expected to be the number of 16-bit code points, not the number of characters).
1654 // Instead, always use the byte length divided by two for Unicode strings, and if mb_strlen() exists use
1655 // mb_strlen($str, '8bit') just in case mbstring.func_overload is set to overload strlen().
1656 if ($this->_input_encoding == 'UTF-16LE')
1658 $strlen = (function_exists('mb_strlen') ? mb_strlen($str, '8bit') : strlen($str)) / 2;
1661 elseif ($this->_input_encoding != '')
1663 $str = @iconv($this->_input_encoding, 'UTF-16LE', $str);
1664 $strlen = (function_exists('mb_strlen') ? mb_strlen($str, '8bit') : strlen($str)) / 2;
1669 $strlen = function_exists('mb_strlen') ? mb_strlen($str, '8bit') : strlen($str);
1672 $record = 0x00FD; // Record identifier
1673 $length = 0x000A; // Bytes to follow
1674 $xf = $this->_XF($format); // The cell format
1678 // Check that row and col are valid and store max and min values
1679 if ($this->_checkRowCol($row, $col) == false) {
1683 $str = pack('vC', $strlen, $encoding).$str;
1685 /* check if string is already present */
1686 if (!isset($this->_str_table[$str])) {
1687 $this->_str_table[$str] = $this->_str_unique++;
1689 $this->_str_total++;
1691 $header = pack('vv', $record, $length);
1692 $data = pack('vvvV', $row, $col, $xf, $this->_str_table[$str]);
1693 $this->_append($header.$data);
1698 * Check row and col before writing to a cell, and update the sheet's
1699 * dimensions accordingly
1702 * @param integer $row Zero indexed row
1703 * @param integer $col Zero indexed column
1704 * @return boolean true for success, false if row and/or col are grester
1705 * then maximums allowed.
1707 function _checkRowCol($row, $col)
1709 if ($row >= $this->_xls_rowmax) {
1712 if ($col >= $this->_xls_colmax) {
1715 if ($row < $this->_dim_rowmin) {
1716 $this->_dim_rowmin = $row;
1718 if ($row > $this->_dim_rowmax) {
1719 $this->_dim_rowmax = $row;
1721 if ($col < $this->_dim_colmin) {
1722 $this->_dim_colmin = $col;
1724 if ($col > $this->_dim_colmax) {
1725 $this->_dim_colmax = $col;
1731 * Writes a note associated with the cell given by the row and column.
1732 * NOTE records don't have a length limit.
1735 * @param integer $row Zero indexed row
1736 * @param integer $col Zero indexed column
1737 * @param string $note The note to write
1739 function writeNote($row, $col, $note)
1741 $note_length = strlen($note);
1742 $record = 0x001C; // Record identifier
1743 $max_length = 2048; // Maximun length for a NOTE record
1744 //$length = 0x0006 + $note_length; // Bytes to follow
1746 // Check that row and col are valid and store max and min values
1747 if ($row >= $this->_xls_rowmax) {
1750 if ($col >= $this->_xls_colmax) {
1753 if ($row < $this->_dim_rowmin) {
1754 $this->_dim_rowmin = $row;
1756 if ($row > $this->_dim_rowmax) {
1757 $this->_dim_rowmax = $row;
1759 if ($col < $this->_dim_colmin) {
1760 $this->_dim_colmin = $col;
1762 if ($col > $this->_dim_colmax) {
1763 $this->_dim_colmax = $col;
1766 // Length for this record is no more than 2048 + 6
1767 $length = 0x0006 + min($note_length, 2048);
1768 $header = pack("vv", $record, $length);
1769 $data = pack("vvv", $row, $col, $note_length);
1770 $this->_append($header . $data . substr($note, 0, 2048));
1772 for ($i = $max_length; $i < $note_length; $i += $max_length) {
1773 $chunk = substr($note, $i, $max_length);
1774 $length = 0x0006 + strlen($chunk);
1775 $header = pack("vv", $record, $length);
1776 $data = pack("vvv", -1, 0, strlen($chunk));
1777 $this->_append($header.$data.$chunk);
1783 * Write a blank cell to the specified row and column (zero indexed).
1784 * A blank cell is used to specify formatting without adding a string
1787 * A blank cell without a format serves no purpose. Therefore, we don't write
1788 * a BLANK record unless a format is specified.
1790 * Returns 0 : normal termination (including no format)
1791 * -1 : insufficient number of arguments
1792 * -2 : row or column out of range
1795 * @param integer $row Zero indexed row
1796 * @param integer $col Zero indexed column
1797 * @param mixed $format The XF format
1799 function writeBlank($row, $col, $format)
1801 // Don't write a blank cell unless it has a format
1806 $record = 0x0201; // Record identifier
1807 $length = 0x0006; // Number of bytes to follow
1808 $xf = $this->_XF($format); // The cell format
1810 // Check that row and col are valid and store max and min values
1811 if ($row >= $this->_xls_rowmax) {
1814 if ($col >= $this->_xls_colmax) {
1817 if ($row < $this->_dim_rowmin) {
1818 $this->_dim_rowmin = $row;
1820 if ($row > $this->_dim_rowmax) {
1821 $this->_dim_rowmax = $row;
1823 if ($col < $this->_dim_colmin) {
1824 $this->_dim_colmin = $col;
1826 if ($col > $this->_dim_colmax) {
1827 $this->_dim_colmax = $col;
1830 $header = pack("vv", $record, $length);
1831 $data = pack("vvv", $row, $col, $xf);
1832 $this->_append($header . $data);
1837 * Write a formula to the specified row and column (zero indexed).
1838 * The textual representation of the formula is passed to the parser in
1839 * Parser.php which returns a packed binary string.
1841 * Returns 0 : normal termination
1842 * -1 : formula errors (bad formula)
1843 * -2 : row or column out of range
1846 * @param integer $row Zero indexed row
1847 * @param integer $col Zero indexed column
1848 * @param string $formula The formula text string
1849 * @param mixed $format The optional XF format
1852 function writeFormula($row, $col, $formula, $format = null)
1854 $record = 0x0006; // Record identifier
1856 // Excel normally stores the last calculated value of the formula in $num.
1857 // Clearly we are not in a position to calculate this a priori. Instead
1858 // we set $num to zero and set the option flags in $grbit to ensure
1859 // automatic calculation of the formula when the file is opened.
1861 $xf = $this->_XF($format); // The cell format
1862 $num = 0x00; // Current value of formula
1863 $grbit = 0x03; // Option flags
1864 $unknown = 0x0000; // Must be zero
1867 // Check that row and col are valid and store max and min values
1868 if ($this->_checkRowCol($row, $col) == false) {
1872 // Strip the '=' or '@' sign at the beginning of the formula string
1873 if (preg_match("/^=/", $formula)) {
1874 $formula = preg_replace("/(^=)/", "", $formula);
1875 } elseif (preg_match("/^@/", $formula)) {
1876 $formula = preg_replace("/(^@)/", "", $formula);
1879 $this->writeString($row, $col, 'Unrecognised character for formula');
1883 // Parse the formula using the parser in Parser.php
1884 $error = $this->_parser->parse($formula);
1885 if ($this->isError($error)) {
1886 $this->writeString($row, $col, $error->getMessage());
1890 $formula = $this->_parser->toReversePolish();
1891 if ($this->isError($formula)) {
1892 $this->writeString($row, $col, $formula->getMessage());
1896 $formlen = strlen($formula); // Length of the binary string
1897 $length = 0x16 + $formlen; // Length of the record data
1899 $header = pack("vv", $record, $length);
1900 $data = pack("vvvdvVv", $row, $col, $xf, $num,
1901 $grbit, $unknown, $formlen);
1903 $this->_append($header . $data . $formula);
1908 * Write a hyperlink.
1909 * This is comprised of two elements: the visible label and
1910 * the invisible link. The visible label is the same as the link unless an
1911 * alternative string is specified. The label is written using the
1912 * writeString() method. Therefore the 255 characters string limit applies.
1913 * $string and $format are optional.
1915 * The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external
1918 * Returns 0 : normal termination
1919 * -2 : row or column out of range
1920 * -3 : long string truncated to 255 chars
1923 * @param integer $row Row
1924 * @param integer $col Column
1925 * @param string $url URL string
1926 * @param string $string Alternative label
1927 * @param mixed $format The cell format
1930 function writeUrl($row, $col, $url, $string = '', $format = null)
1932 // Add start row and col to arg list
1933 return($this->_writeUrlRange($row, $col, $row, $col, $url, $string, $format));
1937 * This is the more general form of writeUrl(). It allows a hyperlink to be
1938 * written to a range of cells. This function also decides the type of hyperlink
1939 * to be written. These are either, Web (http, ftp, mailto), Internal
1940 * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
1944 * @param integer $row1 Start row
1945 * @param integer $col1 Start column
1946 * @param integer $row2 End row
1947 * @param integer $col2 End column
1948 * @param string $url URL string
1949 * @param string $string Alternative label
1950 * @param mixed $format The cell format
1954 function _writeUrlRange($row1, $col1, $row2, $col2, $url, $string = '', $format = null)
1957 // Check for internal/external sheet links or default to web link
1958 if (preg_match('[^internal:]', $url)) {
1959 return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url, $string, $format));
1961 if (preg_match('[^external:]', $url)) {
1962 return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url, $string, $format));
1964 return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url, $string, $format));
1969 * Used to write http, ftp and mailto hyperlinks.
1970 * The link type ($options) is 0x03 is the same as absolute dir ref without
1971 * sheet. However it is differentiated by the $unknown2 data stream.
1975 * @param integer $row1 Start row
1976 * @param integer $col1 Start column
1977 * @param integer $row2 End row
1978 * @param integer $col2 End column
1979 * @param string $url URL string
1980 * @param string $str Alternative label
1981 * @param mixed $format The cell format
1984 function _writeUrlWeb($row1, $col1, $row2, $col2, $url, $str, $format = null)
1986 $record = 0x01B8; // Record identifier
1987 $length = 0x00000; // Bytes to follow
1990 $format = $this->_url_format;
1993 // Write the visible label using the writeString() method.
1997 $str_error = is_numeric($str) ? $this->writeNumber($row1, $col1, $str, $format) : $this->writeString($row1, $col1, $str, $format);
1998 if (($str_error == -2) || ($str_error == -3)) {
2002 // Pack the undocumented parts of the hyperlink stream
2003 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
2004 $unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");
2006 // Pack the option flags
2007 $options = pack("V", 0x03);
2009 // Convert URL to a null terminated wchar string
2010 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
2011 $url = $url . "\0\0\0";
2013 // Pack the length of the URL
2014 $url_len = pack("V", strlen($url));
2016 // Calculate the data length
2017 $length = 0x34 + strlen($url);
2019 // Pack the header data
2020 $header = pack("vv", $record, $length);
2021 $data = pack("vvvv", $row1, $row2, $col1, $col2);
2023 // Write the packed data
2024 $this->_append($header . $data .
2025 $unknown1 . $options .
2026 $unknown2 . $url_len . $url);
2031 * Used to write internal reference hyperlinks such as "Sheet1!A1".
2035 * @param integer $row1 Start row
2036 * @param integer $col1 Start column
2037 * @param integer $row2 End row
2038 * @param integer $col2 End column
2039 * @param string $url URL string
2040 * @param string $str Alternative label
2041 * @param mixed $format The cell format
2044 function _writeUrlInternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
2046 $record = 0x01B8; // Record identifier
2047 $length = 0x00000; // Bytes to follow
2050 $format = $this->_url_format;
2054 $url = preg_replace('/^internal:/', '', $url);
2056 // Write the visible label
2060 $str_error = is_numeric($str) ? $this->writeNumber($row1, $col1, $str, $format) : $this->writeString($row1, $col1, $str, $format);
2061 if (($str_error == -2) || ($str_error == -3)) {
2065 // Pack the undocumented parts of the hyperlink stream
2066 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
2068 // Pack the option flags
2069 $options = pack("V", 0x08);
2071 // Convert the URL type and to a null terminated wchar string
2072 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
2073 $url = $url . "\0\0\0";
2075 // Pack the length of the URL as chars (not wchars)
2076 $url_len = pack("V", floor(strlen($url)/2));
2078 // Calculate the data length
2079 $length = 0x24 + strlen($url);
2081 // Pack the header data
2082 $header = pack("vv", $record, $length);
2083 $data = pack("vvvv", $row1, $row2, $col1, $col2);
2085 // Write the packed data
2086 $this->_append($header . $data .
2087 $unknown1 . $options .
2093 * Write links to external directory names such as 'c:\foo.xls',
2094 * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
2096 * Note: Excel writes some relative links with the $dir_long string. We ignore
2097 * these cases for the sake of simpler code.
2101 * @param integer $row1 Start row
2102 * @param integer $col1 Start column
2103 * @param integer $row2 End row
2104 * @param integer $col2 End column
2105 * @param string $url URL string
2106 * @param string $str Alternative label
2107 * @param mixed $format The cell format
2110 function _writeUrlExternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
2112 // Network drives are different. We will handle them separately
2113 // MS/Novell network drives and shares start with \\
2114 if (preg_match('[^external:\\\\]', $url)) {
2115 return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format));
2118 $record = 0x01B8; // Record identifier
2119 $length = 0x00000; // Bytes to follow
2122 $format = $this->_url_format;
2125 // Strip URL type and change Unix dir separator to Dos style (if needed)
2127 $url = preg_replace('/^external:/', '', $url);
2128 $url = preg_replace('/\//', "\\", $url);
2130 // Write the visible label
2132 $str = preg_replace('/\#/', ' - ', $url);
2134 $str_error = is_numeric($str) ? $this->writeNumber($row1, $col1, $str, $format) : $this->writeString($row1, $col1, $str, $format);
2135 if (($str_error == -2) or ($str_error == -3)) {
2139 // Determine if the link is relative or absolute:
2140 // relative if link contains no dir separator, "somefile.xls"
2141 // relative if link starts with up-dir, "..\..\somefile.xls"
2142 // otherwise, absolute
2144 $absolute = 0x02; // Bit mask
2145 if (!preg_match("/\\\/", $url)) {
2148 if (preg_match("/^\.\.\\\/", $url)) {
2151 $link_type = 0x01 | $absolute;
2153 // Determine if the link contains a sheet reference and change some of the
2154 // parameters accordingly.
2155 // Split the dir name and sheet name (if it exists)
2156 /*if (preg_match("/\#/", $url)) {
2157 list($dir_long, $sheet) = split("\#", $url);
2162 if (isset($sheet)) {
2164 $sheet_len = pack("V", strlen($sheet) + 0x01);
2165 $sheet = join("\0", split('', $sheet));
2172 if (preg_match("/\#/", $url)) {
2178 // Pack the link type
2179 $link_type = pack("V", $link_type);
2181 // Calculate the up-level dir count e.g.. (..\..\..\ == 3)
2182 $up_count = preg_match_all("/\.\.\\\/", $dir_long, $useless);
2183 $up_count = pack("v", $up_count);
2185 // Store the short dos dir name (null terminated)
2186 $dir_short = preg_replace("/\.\.\\\/", '', $dir_long) . "\0";
2188 // Store the long dir name as a wchar string (non-null terminated)
2189 //$dir_long = join("\0", split('', $dir_long));
2190 $dir_long = $dir_long . "\0";
2192 // Pack the lengths of the dir strings
2193 $dir_short_len = pack("V", strlen($dir_short) );
2194 $dir_long_len = pack("V", strlen($dir_long) );
2195 $stream_len = pack("V", 0);//strlen($dir_long) + 0x06);
2197 // Pack the undocumented parts of the hyperlink stream
2198 $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000' );
2199 $unknown2 = pack("H*",'0303000000000000C000000000000046' );
2200 $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000');
2201 $unknown4 = pack("v", 0x03 );
2203 // Pack the main data stream
2204 $data = pack("vvvv", $row1, $row2, $col1, $col2) .
2219 // Pack the header data
2220 $length = strlen($data);
2221 $header = pack("vv", $record, $length);
2223 // Write the packed data
2224 $this->_append($header. $data);
2230 * This method is used to set the height and format for a row.
2233 * @param integer $row The row to set
2234 * @param integer $height Height we are giving to the row.
2235 * Use null to set XF without setting height
2236 * @param mixed $format XF format we are giving to the row
2237 * @param bool $hidden The optional hidden attribute
2238 * @param integer $level The optional outline level for row, in range [0,7]
2240 function setRow($row, $height, $format = null, $hidden = false, $level = 0)
2242 $record = 0x0208; // Record identifier
2243 $length = 0x0010; // Number of bytes to follow
2245 $colMic = 0x0000; // First defined column
2246 $colMac = 0x0000; // Last defined column
2247 $irwMac = 0x0000; // Used by Excel to optimise loading
2248 $reserved = 0x0000; // Reserved
2249 $grbit = 0x0000; // Option flags
2250 $ixfe = $this->_XF($format); // XF index
2252 // set _row_sizes so _sizeRow() can use it
2253 $this->_row_sizes[$row] = $height;
2255 // Use setRow($row, null, $XF) to set XF format without setting height
2256 if ($height != null) {
2257 $miyRw = $height * 20; // row height
2259 $miyRw = 0xff; // default row height is 256
2262 $level = max(0, min($level, 7)); // level should be between 0 and 7
2263 $this->_outline_row_level = max($level, $this->_outline_row_level);
2266 // Set the options flags. fUnsynced is used to show that the font and row
2267 // heights are not compatible. This is usually the case for WriteExcel.
2268 // The collapsed flag 0x10 doesn't seem to be used to indicate that a row
2269 // is collapsed. Instead it is used to indicate that the previous row is
2270 // collapsed. The zero height flag, 0x20, is used to collapse a row.
2276 $grbit |= 0x0040; // fUnsynced
2282 $header = pack("vv", $record, $length);
2283 $data = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw,
2284 $irwMac,$reserved, $grbit, $ixfe);
2285 $this->_append($header.$data);
2289 * Writes Excel DIMENSIONS to define the area in which there is data.
2293 function _storeDimensions()
2295 $record = 0x0200; // Record identifier
2296 $row_min = $this->_dim_rowmin; // First row
2297 $row_max = $this->_dim_rowmax + 1; // Last row plus 1
2298 $col_min = $this->_dim_colmin; // First column
2299 $col_max = $this->_dim_colmax + 1; // Last column plus 1
2300 $reserved = 0x0000; // Reserved by Excel
2302 if ($this->_BIFF_version == 0x0500) {
2303 $length = 0x000A; // Number of bytes to follow
2304 $data = pack("vvvvv", $row_min, $row_max,
2305 $col_min, $col_max, $reserved);
2306 } elseif ($this->_BIFF_version == 0x0600) {
2308 $data = pack("VVvvv", $row_min, $row_max,
2309 $col_min, $col_max, $reserved);
2311 $header = pack("vv", $record, $length);
2312 $this->_prepend($header.$data);
2316 * Write BIFF record Window2.
2320 function _storeWindow2()
2322 $record = 0x023E; // Record identifier
2323 if ($this->_BIFF_version == 0x0500) {
2324 $length = 0x000A; // Number of bytes to follow
2325 } elseif ($this->_BIFF_version == 0x0600) {
2329 $grbit = 0x00B6; // Option flags
2330 $rwTop = 0x0000; // Top row visible in window
2331 $colLeft = 0x0000; // Leftmost column visible in window
2334 // The options flags that comprise $grbit
2335 $fDspFmla = 0; // 0 - bit
2336 $fDspGrid = $this->_screen_gridlines; // 1
2337 $fDspRwCol = 1; // 2
2338 $fFrozen = $this->_frozen; // 3
2339 $fDspZeros = 1; // 4
2340 $fDefaultHdr = 1; // 5
2341 $fArabic = $this->_Arabic; // 6
2342 $fDspGuts = $this->_outline_on; // 7
2343 $fFrozenNoSplit = 0; // 0 - bit
2344 $fSelected = $this->selected; // 1
2348 $grbit |= $fDspGrid << 1;
2349 $grbit |= $fDspRwCol << 2;
2350 $grbit |= $fFrozen << 3;
2351 $grbit |= $fDspZeros << 4;
2352 $grbit |= $fDefaultHdr << 5;
2353 $grbit |= $fArabic << 6;
2354 $grbit |= $fDspGuts << 7;
2355 $grbit |= $fFrozenNoSplit << 8;
2356 $grbit |= $fSelected << 9;
2357 $grbit |= $fPaged << 10;
2359 $header = pack("vv", $record, $length);
2360 $data = pack("vvv", $grbit, $rwTop, $colLeft);
2362 if ($this->_BIFF_version == 0x0500) {
2363 $rgbHdr = 0x00000000; // Row/column heading and gridline color
2364 $data .= pack("V", $rgbHdr);
2365 } elseif ($this->_BIFF_version == 0x0600) {
2366 $rgbHdr = 0x0040; // Row/column heading and gridline color index
2367 $zoom_factor_page_break = 0x0000;
2368 $zoom_factor_normal = 0x0000;
2369 $data .= pack("vvvvV", $rgbHdr, 0x0000, $zoom_factor_page_break, $zoom_factor_normal, 0x00000000);
2371 $this->_append($header.$data);
2375 * Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
2379 function _storeDefcol()
2381 $record = 0x0055; // Record identifier
2382 $length = 0x0002; // Number of bytes to follow
2383 $colwidth = 0x0008; // Default column width
2385 $header = pack("vv", $record, $length);
2386 $data = pack("v", $colwidth);
2387 $this->_prepend($header . $data);
2391 * Write BIFF record COLINFO to define column widths
2393 * Note: The SDK says the record length is 0x0B but Excel writes a 0x0C
2397 * @param array $col_array This is the only parameter received and is composed of the following:
2398 * 0 => First formatted column,
2399 * 1 => Last formatted column,
2400 * 2 => Col width (8.43 is Excel default),
2401 * 3 => The optional XF format of the column,
2402 * 4 => Option flags.
2403 * 5 => Optional outline level
2405 function _storeColinfo($col_array)
2407 if (isset($col_array[0])) {
2408 $colFirst = $col_array[0];
2410 if (isset($col_array[1])) {
2411 $colLast = $col_array[1];
2413 if (isset($col_array[2])) {
2414 $coldx = $col_array[2];
2418 if (isset($col_array[3])) {
2419 $format = $col_array[3];
2423 if (isset($col_array[4])) {
2424 $grbit = $col_array[4];
2428 if (isset($col_array[5])) {
2429 $level = $col_array[5];
2433 $record = 0x007D; // Record identifier
2434 $length = 0x000B; // Number of bytes to follow
2436 $coldx += 0.72; // Fudge. Excel subtracts 0.72 !?
2437 $coldx *= 256; // Convert to units of 1/256 of a char
2439 $ixfe = $this->_XF($format);
2440 $reserved = 0x00; // Reserved
2442 $level = max(0, min($level, 7));
2443 $grbit |= $level << 8;
2445 $header = pack("vv", $record, $length);
2446 $data = pack("vvvvvC", $colFirst, $colLast, $coldx,
2447 $ixfe, $grbit, $reserved);
2448 $this->_prepend($header.$data);
2452 * Write BIFF record SELECTION.
2455 * @param array $array array containing ($rwFirst,$colFirst,$rwLast,$colLast)
2456 * @see setSelection()
2458 function _storeSelection($array)
2460 list($rwFirst,$colFirst,$rwLast,$colLast) = $array;
2461 $record = 0x001D; // Record identifier
2462 $length = 0x000F; // Number of bytes to follow
2464 $pnn = $this->_active_pane; // Pane position
2465 $rwAct = $rwFirst; // Active row
2466 $colAct = $colFirst; // Active column
2467 $irefAct = 0; // Active cell ref
2468 $cref = 1; // Number of refs
2470 if (!isset($rwLast)) {
2471 $rwLast = $rwFirst; // Last row in reference
2473 if (!isset($colLast)) {
2474 $colLast = $colFirst; // Last col in reference
2477 // Swap last row/col for first row/col as necessary
2478 if ($rwFirst > $rwLast) {
2479 list($rwFirst, $rwLast) = array($rwLast, $rwFirst);
2482 if ($colFirst > $colLast) {
2483 list($colFirst, $colLast) = array($colLast, $colFirst);
2486 $header = pack("vv", $record, $length);
2487 $data = pack("CvvvvvvCC", $pnn, $rwAct, $colAct,
2490 $colFirst, $colLast);
2491 $this->_append($header . $data);
2495 * Store the MERGEDCELLS record for all ranges of merged cells
2499 function _storeMergedCells()
2501 // if there are no merged cell ranges set, return
2502 if (count($this->_merged_ranges) == 0) {
2506 foreach($this->_merged_ranges as $ranges)
2508 $length = 2 + count($ranges) * 8;
2509 $header = pack('vv', $record, $length);
2510 $data = pack('v', count($ranges));
2511 foreach ($ranges as $range)
2512 $data .= pack('vvvv', $range[0], $range[2], $range[1], $range[3]);
2513 $string = $header.$data;
2514 $this->_append($string, true);
2519 * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
2520 * references in a worksheet.
2522 * Excel only stores references to external sheets that are used in formulas.
2523 * For simplicity we store references to all the sheets in the workbook
2524 * regardless of whether they are used or not. This reduces the overall
2525 * complexity and eliminates the need for a two way dialogue between the formula
2526 * parser the worksheet objects.
2529 * @param integer $count The number of external sheet references in this worksheet
2531 function _storeExterncount($count)
2533 $record = 0x0016; // Record identifier
2534 $length = 0x0002; // Number of bytes to follow
2536 $header = pack("vv", $record, $length);
2537 $data = pack("v", $count);
2538 $this->_prepend($header . $data);
2542 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
2543 * formulas. A formula references a sheet name via an index. Since we store a
2544 * reference to all of the external worksheets the EXTERNSHEET index is the same
2545 * as the worksheet index.
2548 * @param string $sheetname The name of a external worksheet
2550 function _storeExternsheet($sheetname)
2552 $record = 0x0017; // Record identifier
2554 // References to the current sheet are encoded differently to references to
2557 if ($this->name == $sheetname) {
2559 $length = 0x02; // The following 2 bytes
2560 $cch = 1; // The following byte
2561 $rgch = 0x02; // Self reference
2563 $length = 0x02 + strlen($sheetname);
2564 $cch = strlen($sheetname);
2565 $rgch = 0x03; // Reference to a sheet in the current workbook
2568 $header = pack("vv", $record, $length);
2569 $data = pack("CC", $cch, $rgch);
2570 $this->_prepend($header . $data . $sheetname);
2574 * Writes the Excel BIFF PANE record.
2575 * The panes can either be frozen or thawed (unfrozen).
2576 * Frozen panes are specified in terms of an integer number of rows and columns.
2577 * Thawed panes are specified in terms of Excel's units for rows and columns.
2580 * @param array $panes This is the only parameter received and is composed of the following:
2581 * 0 => Vertical split position,
2582 * 1 => Horizontal split position
2583 * 2 => Top row visible
2584 * 3 => Leftmost column visible
2587 function _storePanes($panes)
2592 $colLeft = $panes[3];
2593 if (count($panes) > 4) { // if Active pane was received
2594 $pnnAct = $panes[4];
2598 $record = 0x0041; // Record identifier
2599 $length = 0x000A; // Number of bytes to follow
2601 // Code specific to frozen or thawed panes.
2602 if ($this->_frozen) {
2603 // Set default values for $rwTop and $colLeft
2604 if (!isset($rwTop)) {
2607 if (!isset($colLeft)) {
2611 // Set default values for $rwTop and $colLeft
2612 if (!isset($rwTop)) {
2615 if (!isset($colLeft)) {
2619 // Convert Excel's row and column units to the internal units.
2620 // The default row height is 12.75
2621 // The default column width is 8.43
2622 // The following slope and intersection values were interpolated.
2625 $x = 113.879*$x + 390;
2629 // Determine which pane should be active. There is also the undocumented
2630 // option to override this should it be necessary: may be removed later.
2632 if (!isset($pnnAct)) {
2633 if ($x != 0 && $y != 0) {
2634 $pnnAct = 0; // Bottom right
2636 if ($x != 0 && $y == 0) {
2637 $pnnAct = 1; // Top right
2639 if ($x == 0 && $y != 0) {
2640 $pnnAct = 2; // Bottom left
2642 if ($x == 0 && $y == 0) {
2643 $pnnAct = 3; // Top left
2647 $this->_active_pane = $pnnAct; // Used in _storeSelection
2649 $header = pack("vv", $record, $length);
2650 $data = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct);
2651 $this->_append($header . $data);
2655 * Store the page setup SETUP BIFF record.
2659 function _storeSetup()
2661 $record = 0x00A1; // Record identifier
2662 $length = 0x0022; // Number of bytes to follow
2664 $iPaperSize = $this->_paper_size; // Paper size
2665 $iScale = $this->_print_scale; // Print scaling factor
2666 $iPageStart = 0x01; // Starting page number
2667 $iFitWidth = $this->_fit_width; // Fit to number of pages wide
2668 $iFitHeight = $this->_fit_height; // Fit to number of pages high
2669 $grbit = 0x00; // Option flags
2670 $iRes = 0x0258; // Print resolution
2671 $iVRes = 0x0258; // Vertical print resolution
2672 $numHdr = $this->_margin_head; // Header Margin
2673 $numFtr = $this->_margin_foot; // Footer Margin
2674 $iCopies = 0x01; // Number of copies
2676 $fLeftToRight = 0x0; // Print over then down
2677 $fLandscape = $this->_orientation; // Page orientation
2678 $fNoPls = 0x0; // Setup not read from printer
2679 $fNoColor = 0x0; // Print black and white
2680 $fDraft = 0x0; // Print draft quality
2681 $fNotes = 0x0; // Print notes
2682 $fNoOrient = 0x0; // Orientation not set
2683 $fUsePage = 0x0; // Use custom starting page
2685 $grbit = $fLeftToRight;
2686 $grbit |= $fLandscape << 1;
2687 $grbit |= $fNoPls << 2;
2688 $grbit |= $fNoColor << 3;
2689 $grbit |= $fDraft << 4;
2690 $grbit |= $fNotes << 5;
2691 $grbit |= $fNoOrient << 6;
2692 $grbit |= $fUsePage << 7;
2694 $numHdr = pack("d", $numHdr);
2695 $numFtr = pack("d", $numFtr);
2696 if ($this->_byte_order) { // if it's Big Endian
2697 $numHdr = strrev($numHdr);
2698 $numFtr = strrev($numFtr);
2701 $header = pack("vv", $record, $length);
2702 $data1 = pack("vvvvvvvv", $iPaperSize,
2710 $data2 = $numHdr.$numFtr;
2711 $data3 = pack("v", $iCopies);
2712 $this->_prepend($header . $data1 . $data2 . $data3);
2716 * Store the header caption BIFF record.
2720 function _storeHeader()
2722 $record = 0x0014; // Record identifier
2724 $str = $this->_header; // header string
2725 $cch = strlen($str); // Length of header string
2726 if ($this->_BIFF_version == 0x0600) {
2727 $encoding = 0x0; // TODO: Unicode support
2728 $length = 3 + $cch; // Bytes to follow
2730 $length = 1 + $cch; // Bytes to follow
2733 $header = pack("vv", $record, $length);
2734 if ($this->_BIFF_version == 0x0600) {
2735 $data = pack("vC", $cch, $encoding);
2737 $data = pack("C", $cch);
2740 $this->_prepend($header.$data.$str);
2744 * Store the footer caption BIFF record.
2748 function _storeFooter()
2750 $record = 0x0015; // Record identifier
2752 $str = $this->_footer; // Footer string
2753 $cch = strlen($str); // Length of footer string
2754 if ($this->_BIFF_version == 0x0600) {
2755 $encoding = 0x0; // TODO: Unicode support
2756 $length = 3 + $cch; // Bytes to follow
2761 $header = pack("vv", $record, $length);
2762 if ($this->_BIFF_version == 0x0600) {
2763 $data = pack("vC", $cch, $encoding);
2765 $data = pack("C", $cch);
2768 $this->_prepend($header . $data . $str);
2772 * Store the horizontal centering HCENTER BIFF record.
2776 function _storeHcenter()
2778 $record = 0x0083; // Record identifier
2779 $length = 0x0002; // Bytes to follow
2781 $fHCenter = $this->_hcenter; // Horizontal centering
2783 $header = pack("vv", $record, $length);
2784 $data = pack("v", $fHCenter);
2786 $this->_prepend($header.$data);
2790 * Store the vertical centering VCENTER BIFF record.
2794 function _storeVcenter()
2796 $record = 0x0084; // Record identifier
2797 $length = 0x0002; // Bytes to follow
2799 $fVCenter = $this->_vcenter; // Horizontal centering
2801 $header = pack("vv", $record, $length);
2802 $data = pack("v", $fVCenter);
2803 $this->_prepend($header . $data);
2807 * Store the LEFTMARGIN BIFF record.
2811 function _storeMarginLeft()
2813 $record = 0x0026; // Record identifier
2814 $length = 0x0008; // Bytes to follow
2816 $margin = $this->_margin_left; // Margin in inches
2818 $header = pack("vv", $record, $length);
2819 $data = pack("d", $margin);
2820 if ($this->_byte_order) { // if it's Big Endian
2821 $data = strrev($data);
2824 $this->_prepend($header . $data);
2828 * Store the RIGHTMARGIN BIFF record.
2832 function _storeMarginRight()
2834 $record = 0x0027; // Record identifier
2835 $length = 0x0008; // Bytes to follow
2837 $margin = $this->_margin_right; // Margin in inches
2839 $header = pack("vv", $record, $length);
2840 $data = pack("d", $margin);
2841 if ($this->_byte_order) { // if it's Big Endian
2842 $data = strrev($data);
2845 $this->_prepend($header . $data);
2849 * Store the TOPMARGIN BIFF record.
2853 function _storeMarginTop()
2855 $record = 0x0028; // Record identifier
2856 $length = 0x0008; // Bytes to follow
2858 $margin = $this->_margin_top; // Margin in inches
2860 $header = pack("vv", $record, $length);
2861 $data = pack("d", $margin);
2862 if ($this->_byte_order) { // if it's Big Endian
2863 $data = strrev($data);
2866 $this->_prepend($header . $data);
2870 * Store the BOTTOMMARGIN BIFF record.
2874 function _storeMarginBottom()
2876 $record = 0x0029; // Record identifier
2877 $length = 0x0008; // Bytes to follow
2879 $margin = $this->_margin_bottom; // Margin in inches
2881 $header = pack("vv", $record, $length);
2882 $data = pack("d", $margin);
2883 if ($this->_byte_order) { // if it's Big Endian
2884 $data = strrev($data);
2887 $this->_prepend($header . $data);
2891 * Merges the area given by its arguments.
2892 * This is an Excel97/2000 method. It is required to perform more complicated
2893 * merging than the normal setAlign('merge').
2896 * @param integer $first_row First row of the area to merge
2897 * @param integer $first_col First column of the area to merge
2898 * @param integer $last_row Last row of the area to merge
2899 * @param integer $last_col Last column of the area to merge
2901 function mergeCells($first_row, $first_col, $last_row, $last_col)
2903 $record = 0x00E5; // Record identifier
2904 $length = 0x000A; // Bytes to follow
2905 $cref = 1; // Number of refs
2907 // Swap last row/col for first row/col as necessary
2908 if ($first_row > $last_row) {
2909 list($first_row, $last_row) = array($last_row, $first_row);
2912 if ($first_col > $last_col) {
2913 list($first_col, $last_col) = array($last_col, $first_col);
2916 $header = pack("vv", $record, $length);
2917 $data = pack("vvvvv", $cref, $first_row, $last_row,
2918 $first_col, $last_col);
2920 $this->_append($header.$data);
2924 * Write the PRINTHEADERS BIFF record.
2928 function _storePrintHeaders()
2930 $record = 0x002a; // Record identifier
2931 $length = 0x0002; // Bytes to follow
2933 $fPrintRwCol = $this->_print_headers; // Boolean flag
2935 $header = pack("vv", $record, $length);
2936 $data = pack("v", $fPrintRwCol);
2937 $this->_prepend($header . $data);
2941 * Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the
2946 function _storePrintGridlines()
2948 $record = 0x002b; // Record identifier
2949 $length = 0x0002; // Bytes to follow
2951 $fPrintGrid = $this->_print_gridlines; // Boolean flag
2953 $header = pack("vv", $record, $length);
2954 $data = pack("v", $fPrintGrid);
2955 $this->_prepend($header . $data);
2959 * Write the GRIDSET BIFF record. Must be used in conjunction with the
2960 * PRINTGRIDLINES record.
2964 function _storeGridset()
2966 $record = 0x0082; // Record identifier
2967 $length = 0x0002; // Bytes to follow
2969 $fGridSet = !($this->_print_gridlines); // Boolean flag
2971 $header = pack("vv", $record, $length);
2972 $data = pack("v", $fGridSet);
2973 $this->_prepend($header . $data);
2977 * Write the GUTS BIFF record. This is used to configure the gutter margins
2978 * where Excel outline symbols are displayed. The visibility of the gutters is
2979 * controlled by a flag in WSBOOL.
2981 * @see _storeWsbool()
2984 function _storeGuts()
2986 $record = 0x0080; // Record identifier
2987 $length = 0x0008; // Bytes to follow
2989 $dxRwGut = 0x0000; // Size of row gutter
2990 $dxColGut = 0x0000; // Size of col gutter
2992 $row_level = $this->_outline_row_level;
2995 // Calculate the maximum column outline level. The equivalent calculation
2996 // for the row outline level is carried out in setRow().
2997 $colcount = count($this->_colinfo);
2998 for ($i = 0; $i < $colcount; $i++) {
2999 // Skip cols without outline level info.
3000 if (count($this->_colinfo[$i]) >= 6) {
3001 $col_level = max($this->_colinfo[$i][5], $col_level);
3005 // Set the limits for the outline levels (0 <= x <= 7).
3006 $col_level = max(0, min($col_level, 7));
3008 // The displayed level is one greater than the max outline levels
3016 $header = pack("vv", $record, $length);
3017 $data = pack("vvvv", $dxRwGut, $dxColGut, $row_level, $col_level);
3019 $this->_prepend($header.$data);
3024 * Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction
3025 * with the SETUP record.
3029 function _storeWsbool()
3031 $record = 0x0081; // Record identifier
3032 $length = 0x0002; // Bytes to follow
3035 // The only option that is of interest is the flag for fit to page. So we
3036 // set all the options in one go.
3038 /*if ($this->_fit_page) {
3043 // Set the option flags
3044 $grbit |= 0x0001; // Auto page breaks visible
3045 if ($this->_outline_style) {
3046 $grbit |= 0x0020; // Auto outline styles
3048 if ($this->_outline_below) {
3049 $grbit |= 0x0040; // Outline summary below
3051 if ($this->_outline_right) {
3052 $grbit |= 0x0080; // Outline summary right
3054 if ($this->_fit_page) {
3055 $grbit |= 0x0100; // Page setup fit to page
3057 if ($this->_outline_on) {
3058 $grbit |= 0x0400; // Outline symbols displayed
3061 $header = pack("vv", $record, $length);
3062 $data = pack("v", $grbit);
3063 $this->_prepend($header . $data);
3067 * Write the HORIZONTALPAGEBREAKS BIFF record.
3071 function _storeHbreak()
3073 // Return if the user hasn't specified pagebreaks
3074 if (empty($this->_hbreaks)) {
3078 // Sort and filter array of page breaks
3079 $breaks = $this->_hbreaks;
3080 sort($breaks, SORT_NUMERIC);
3081 if ($breaks[0] == 0) { // don't use first break if it's 0
3082 array_shift($breaks);
3085 $record = 0x001b; // Record identifier
3086 $cbrk = count($breaks); // Number of page breaks
3087 if ($this->_BIFF_version == 0x0600) {
3088 $length = 2 + 6*$cbrk; // Bytes to follow
3090 $length = 2 + 2*$cbrk; // Bytes to follow
3093 $header = pack("vv", $record, $length);
3094 $data = pack("v", $cbrk);
3096 // Append each page break
3097 foreach ($breaks as $break) {
3098 if ($this->_BIFF_version == 0x0600) {
3099 $data .= pack("vvv", $break, 0x0000, 0x00ff);
3101 $data .= pack("v", $break);
3105 $this->_prepend($header.$data);
3110 * Write the VERTICALPAGEBREAKS BIFF record.
3114 function _storeVbreak()
3116 // Return if the user hasn't specified pagebreaks
3117 if (empty($this->_vbreaks)) {
3121 // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
3122 // It is slightly higher in Excel 97/200, approx. 1026
3123 $breaks = array_slice($this->_vbreaks,0,1000);
3125 // Sort and filter array of page breaks
3126 sort($breaks, SORT_NUMERIC);
3127 if ($breaks[0] == 0) { // don't use first break if it's 0
3128 array_shift($breaks);
3131 $record = 0x001a; // Record identifier
3132 $cbrk = count($breaks); // Number of page breaks
3133 if ($this->_BIFF_version == 0x0600) {
3134 $length = 2 + 6*$cbrk; // Bytes to follow
3136 $length = 2 + 2*$cbrk; // Bytes to follow
3139 $header = pack("vv", $record, $length);
3140 $data = pack("v", $cbrk);
3142 // Append each page break
3143 foreach ($breaks as $break) {
3144 if ($this->_BIFF_version == 0x0600) {
3145 $data .= pack("vvv", $break, 0x0000, 0xffff);
3147 $data .= pack("v", $break);
3151 $this->_prepend($header . $data);
3155 * Set the Biff PROTECT record to indicate that the worksheet is protected.
3159 function _storeProtect()
3161 // Exit unless sheet protection has been specified
3162 if ($this->_protect == 0) {
3166 $record = 0x0012; // Record identifier
3167 $length = 0x0002; // Bytes to follow
3169 $fLock = $this->_protect; // Worksheet is protected
3171 $header = pack("vv", $record, $length);
3172 $data = pack("v", $fLock);
3174 $this->_prepend($header.$data);
3178 * Write the worksheet PASSWORD record.
3182 function _storePassword()
3184 // Exit unless sheet protection and password have been specified
3185 if (($this->_protect == 0) || (!isset($this->_password))) {
3189 $record = 0x0013; // Record identifier
3190 $length = 0x0002; // Bytes to follow
3192 $wPassword = $this->_password; // Encoded password
3194 $header = pack("vv", $record, $length);
3195 $data = pack("v", $wPassword);
3197 $this->_prepend($header . $data);
3202 * Insert a 24bit bitmap image in a worksheet.
3205 * @param integer $row The row we are going to insert the bitmap into
3206 * @param integer $col The column we are going to insert the bitmap into
3207 * @param string $bitmap The bitmap filename
3208 * @param integer $x The horizontal position (offset) of the image inside the cell.
3209 * @param integer $y The vertical position (offset) of the image inside the cell.
3210 * @param integer $scale_x The horizontal scale
3211 * @param integer $scale_y The vertical scale
3213 function insertBitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1)
3215 $bitmap_array = $this->_processBitmap($bitmap);
3216 //var_dump($bitmap_array);exit;
3217 if ($this->isError($bitmap_array)) {
3218 $this->writeString($row, $col, $bitmap_array->getMessage());
3221 list($width, $height, $size, $data) = $bitmap_array; //$this->_processBitmap($bitmap);
3223 // Scale the frame of the image.
3225 $height *= $scale_y;
3227 // Calculate the vertices of the image and write the OBJ record
3228 $this->_positionImage($col, $row, $x, $y, $width, $height);
3230 // Write the IMDATA record to store the bitmap data
3232 $length = 8 + $size;
3237 $header = pack("vvvvV", $record, $length, $cf, $env, $lcb);
3238 $this->_append($header.$data);
3242 * Calculate the vertices that define the position of the image as required by
3245 * +------------+------------+
3247 * +-----+------------+------------+
3249 * | 1 |(A1)._______|______ |
3252 * +-----+----| BITMAP |-----+
3254 * | 2 | |______________. |
3257 * +---- +------------+------------+
3259 * Example of a bitmap that covers some of the area from cell A1 to cell B2.
3261 * Based on the width and height of the bitmap we need to calculate 8 vars:
3262 * $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
3263 * The width and height of the cells are also variable and have to be taken into
3265 * The values of $col_start and $row_start are passed in from the calling
3266 * function. The values of $col_end and $row_end are calculated by subtracting
3267 * the width and height of the bitmap from the width and height of the
3269 * The vertices are expressed as a percentage of the underlying cell width as
3270 * follows (rhs values are in pixels):
3274 * x2 = (X-1) / W *1024
3275 * y2 = (Y-1) / H *256
3277 * Where: X is distance from the left side of the underlying cell
3278 * Y is distance from the top of the underlying cell
3279 * W is the width of the cell
3280 * H is the height of the cell
3283 * @note the SDK incorrectly states that the height should be expressed as a
3284 * percentage of 1024.
3285 * @param integer $col_start Col containing upper left corner of object
3286 * @param integer $row_start Row containing top left corner of object
3287 * @param integer $x1 Distance to left side of object
3288 * @param integer $y1 Distance to top of object
3289 * @param integer $width Width of image frame
3290 * @param integer $height Height of image frame
3292 function _positionImage($col_start, $row_start, $x1, $y1, $width, $height)
3294 // Initialise end cell to the same as the start cell
3295 $col_end = $col_start; // Col containing lower right corner of object
3296 $row_end = $row_start; // Row containing bottom right corner of object
3298 // Zero the specified offset if greater than the cell dimensions
3299 if ($x1 >= $this->_sizeCol($col_start)) {
3302 if ($y1 >= $this->_sizeRow($row_start)) {
3306 $width = $width + $x1 -1;
3307 $height = $height + $y1 -1;
3309 // Subtract the underlying cell widths to find the end cell of the image
3310 while ($width >= $this->_sizeCol($col_end)) {
3311 $width -= $this->_sizeCol($col_end);
3315 // Subtract the underlying cell heights to find the end cell of the image
3316 while ($height >= $this->_sizeRow($row_end)) {
3317 $height -= $this->_sizeRow($row_end);
3321 // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
3322 // with zero eight or width.
3324 if ($this->_sizeCol($col_start) == 0) {
3327 if ($this->_sizeCol($col_end) == 0) {
3330 if ($this->_sizeRow($row_start) == 0) {
3333 if ($this->_sizeRow($row_end) == 0) {
3337 // Convert the pixel values to the percentage value expected by Excel
3338 $x1 = $x1 / $this->_sizeCol($col_start) * 1024;
3339 $y1 = $y1 / $this->_sizeRow($row_start) * 256;
3340 $x2 = $width / $this->_sizeCol($col_end) * 1024; // Distance to right side of object
3341 $y2 = $height / $this->_sizeRow($row_end) * 256; // Distance to bottom of object
3343 $this->_storeObjPicture($col_start, $x1,
3350 * Convert the width of a cell from user's units to pixels. By interpolation
3351 * the relationship is: y = 7x +5. If the width hasn't been set by the user we
3352 * use the default value. If the col is hidden we use a value of zero.
3355 * @param integer $col The column
3356 * @return integer The width in pixels
3358 function _sizeCol($col)
3360 // Look up the cell value to see if it has been changed
3361 if (isset($this->col_sizes[$col])) {
3362 if ($this->col_sizes[$col] == 0) {
3365 return(floor(7 * $this->col_sizes[$col] + 5));
3373 * Convert the height of a cell from user's units to pixels. By interpolation
3374 * the relationship is: y = 4/3x. If the height hasn't been set by the user we
3375 * use the default value. If the row is hidden we use a value of zero. (Not
3376 * possible to hide row yet).
3379 * @param integer $row The row
3380 * @return integer The width in pixels
3382 function _sizeRow($row)
3384 // Look up the cell value to see if it has been changed
3385 if (isset($this->_row_sizes[$row])) {
3386 if ($this->_row_sizes[$row] == 0) {
3389 return(floor(4/3 * $this->_row_sizes[$row]));
3397 * Store the OBJ record that precedes an IMDATA record. This could be generalise
3398 * to support other Excel objects.
3401 * @param integer $colL Column containing upper left corner of object
3402 * @param integer $dxL Distance from left side of cell
3403 * @param integer $rwT Row containing top left corner of object
3404 * @param integer $dyT Distance from top of cell
3405 * @param integer $colR Column containing lower right corner of object
3406 * @param integer $dxR Distance from right of cell
3407 * @param integer $rwB Row containing bottom right corner of object
3408 * @param integer $dyB Distance from bottom of cell
3410 function _storeObjPicture($colL,$dxL,$rwT,$dyT,$colR,$dxR,$rwB,$dyB)
3412 $record = 0x005d; // Record identifier
3413 $length = 0x003c; // Bytes to follow
3415 $cObj = 0x0001; // Count of objects in file (set to 1)
3416 $OT = 0x0008; // Object type. 8 = Picture
3417 $id = 0x0001; // Object ID
3418 $grbit = 0x0614; // Option flags
3420 $cbMacro = 0x0000; // Length of FMLA structure
3421 $Reserved1 = 0x0000; // Reserved
3422 $Reserved2 = 0x0000; // Reserved
3424 $icvBack = 0x09; // Background colour
3425 $icvFore = 0x09; // Foreground colour
3426 $fls = 0x00; // Fill pattern
3427 $fAuto = 0x00; // Automatic fill
3428 $icv = 0x08; // Line colour
3429 $lns = 0xff; // Line style
3430 $lnw = 0x01; // Line weight
3431 $fAutoB = 0x00; // Automatic border
3432 $frs = 0x0000; // Frame style
3433 $cf = 0x0009; // Image format, 9 = bitmap
3434 $Reserved3 = 0x0000; // Reserved
3435 $cbPictFmla = 0x0000; // Length of FMLA structure
3436 $Reserved4 = 0x0000; // Reserved
3437 $grbit2 = 0x0001; // Option flags
3438 $Reserved5 = 0x0000; // Reserved
3441 $header = pack("vv", $record, $length);
3442 $data = pack("V", $cObj);
3443 $data .= pack("v", $OT);
3444 $data .= pack("v", $id);
3445 $data .= pack("v", $grbit);
3446 $data .= pack("v", $colL);
3447 $data .= pack("v", $dxL);
3448 $data .= pack("v", $rwT);
3449 $data .= pack("v", $dyT);
3450 $data .= pack("v", $colR);
3451 $data .= pack("v", $dxR);
3452 $data .= pack("v", $rwB);
3453 $data .= pack("v", $dyB);
3454 $data .= pack("v", $cbMacro);
3455 $data .= pack("V", $Reserved1);
3456 $data .= pack("v", $Reserved2);
3457 $data .= pack("C", $icvBack);
3458 $data .= pack("C", $icvFore);
3459 $data .= pack("C", $fls);
3460 $data .= pack("C", $fAuto);
3461 $data .= pack("C", $icv);
3462 $data .= pack("C", $lns);
3463 $data .= pack("C", $lnw);
3464 $data .= pack("C", $fAutoB);
3465 $data .= pack("v", $frs);
3466 $data .= pack("V", $cf);
3467 $data .= pack("v", $Reserved3);
3468 $data .= pack("v", $cbPictFmla);
3469 $data .= pack("v", $Reserved4);
3470 $data .= pack("v", $grbit2);
3471 $data .= pack("V", $Reserved5);
3473 $this->_append($header . $data);
3477 * Convert a 24 bit bitmap into the modified internal format used by Windows.
3478 * This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the
3482 * @param string $bitmap The bitmap to process
3483 * @return array Array with data and properties of the bitmap
3485 function _processBitmap($bitmap)
3488 $bmp_fd = @fopen($bitmap,"rb");
3490 $this->raiseError("Couldn't import $bitmap");
3493 // Slurp the file into a string.
3494 $data = fread($bmp_fd, filesize($bitmap));
3496 // Check that the file is big enough to be a bitmap.
3497 if (strlen($data) <= 0x36) {
3498 $this->raiseError("$bitmap doesn't contain enough data.\n");
3501 // The first 2 bytes are used to identify the bitmap.
3502 $identity = unpack("A2ident", $data);
3503 if ($identity['ident'] != "BM") {
3504 $this->raiseError("$bitmap doesn't appear to be a valid bitmap image.\n");
3507 // Remove bitmap data: ID.
3508 $data = substr($data, 2);
3510 // Read and remove the bitmap size. This is more reliable than reading
3511 // the data size at offset 0x22.
3513 $size_array = unpack("Vsa", substr($data, 0, 4));
3514 $size = $size_array['sa'];
3515 $data = substr($data, 4);
3516 $size -= 0x36; // Subtract size of bitmap header.
3517 $size += 0x0C; // Add size of BIFF header.
3519 // Remove bitmap data: reserved, offset, header length.
3520 $data = substr($data, 12);
3522 // Read and remove the bitmap width and height. Verify the sizes.
3523 $width_and_height = unpack("V2", substr($data, 0, 8));
3524 $width = $width_and_height[1];
3525 $height = $width_and_height[2];
3526 $data = substr($data, 8);
3527 if ($width > 0xFFFF) {
3528 $this->raiseError("$bitmap: largest image width supported is 65k.\n");
3530 if ($height > 0xFFFF) {
3531 $this->raiseError("$bitmap: largest image height supported is 65k.\n");
3534 // Read and remove the bitmap planes and bpp data. Verify them.
3535 $planes_and_bitcount = unpack("v2", substr($data, 0, 4));
3536 $data = substr($data, 4);
3537 if ($planes_and_bitcount[2] != 24) { // Bitcount
3538 $this->raiseError("$bitmap isn't a 24bit true color bitmap.\n");
3540 if ($planes_and_bitcount[1] != 1) {
3541 $this->raiseError("$bitmap: only 1 plane supported in bitmap image.\n");
3544 // Read and remove the bitmap compression. Verify compression.
3545 $compression = unpack("Vcomp", substr($data, 0, 4));
3546 $data = substr($data, 4);
3549 if ($compression['comp'] != 0) {
3550 $this->raiseError("$bitmap: compression not supported in bitmap image.\n");
3553 // Remove bitmap data: data size, hres, vres, colours, imp. colours.
3554 $data = substr($data, 20);
3556 // Add the BITMAPCOREHEADER data
3557 $header = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18);
3558 $data = $header . $data;
3560 return (array($width, $height, $size, $data));
3564 * Store the window zoom factor. This should be a reduced fraction but for
3565 * simplicity we will store all fractions with a numerator of 100.
3569 function _storeZoom()
3571 // If scale is 100 we don't need to write a record
3572 if ($this->_zoom == 100) {
3576 $record = 0x00A0; // Record identifier
3577 $length = 0x0004; // Bytes to follow
3579 $header = pack("vv", $record, $length);
3580 $data = pack("vv", $this->_zoom, 100);
3581 $this->_append($header . $data);
3585 * FIXME: add comments
3587 function setValidation($row1, $col1, $row2, $col2, &$validator)
3589 $this->_dv[] = $validator->_getData() .
3590 pack("vvvvv", 1, $row1, $row2, $col1, $col2);
3594 * Store the DVAL and DV records.
3598 function _storeDataValidity()
3600 $record = 0x01b2; // Record identifier
3601 $length = 0x0012; // Bytes to follow
3603 $grbit = 0x0002; // Prompt box at cell, no cached validity data at DV records
3604 $horPos = 0x00000000; // Horizontal position of prompt box, if fixed position
3605 $verPos = 0x00000000; // Vertical position of prompt box, if fixed position
3606 $objId = 0xffffffff; // Object identifier of drop down arrow object, or -1 if not visible
3608 $header = pack('vv', $record, $length);
3609 $data = pack('vVVVV', $grbit, $horPos, $verPos, $objId,
3611 $this->_append($header.$data);
3613 $record = 0x01be; // Record identifier
3614 foreach ($this->_dv as $dv) {
3615 $length = strlen($dv); // Bytes to follow
3616 $header = pack("vv", $record, $length);
3617 $this->_append($header . $dv);