/functions/PEAR/Spreadsheet/Excel/Writer/Worksheet.php

Large files files are truncated, but you can click here to view the full file

<?php

/*

*  Module written/ported by Xavier Noguer <xnoguer@rezebra.com>

*

*  The majority of this is _NOT_ my code.  I simply ported it from the

*  PERL Spreadsheet::WriteExcel module.

*

*  The author of the Spreadsheet::WriteExcel module is John McNamara

*  <jmcnamara@cpan.org>

*

*  I _DO_ maintain this code, and John McNamara has nothing to do with the

*  porting of this code to PHP.  Any questions directly related to this

*  class library should be directed to me.

*

*  License Information:

*

*    Spreadsheet_Excel_Writer:  A library for generating Excel Spreadsheets

*    Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com

*

*    This library is free software; you can redistribute it and/or

*    modify it under the terms of the GNU Lesser General Public

*    License as published by the Free Software Foundation; either

*    version 2.1 of the License, or (at your option) any later version.

*

*    This library is distributed in the hope that it will be useful,

*    but WITHOUT ANY WARRANTY; without even the implied warranty of

*    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU

*    Lesser General Public License for more details.

*

*    You should have received a copy of the GNU Lesser General Public

*    License along with this library; if not, write to the Free Software

*    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

*/



require_once '../../functions/PEAR/Spreadsheet/Excel/Writer/Parser.php';

require_once '../../functions/PEAR/Spreadsheet/Excel/Writer/BIFFwriter.php';



/**

* Class for generating Excel Spreadsheets

*

* @author   Xavier Noguer <xnoguer@rezebra.com>

* @category FileFormats

* @package  Spreadsheet_Excel_Writer

*/



class Spreadsheet_Excel_Writer_Worksheet extends Spreadsheet_Excel_Writer_BIFFwriter

{

    /**

    * Name of the Worksheet

    * @var string

    */

    var $name;



    /**

    * Index for the Worksheet

    * @var integer

    */

    var $index;



    /**

    * Reference to the (default) Format object for URLs

    * @var object Format

    */

    var $_url_format;



    /**

    * Reference to the parser used for parsing formulas

    * @var object Format

    */

    var $_parser;



    /**

    * Filehandle to the temporary file for storing data

    * @var resource

    */

    var $_filehandle;



    /**

    * Boolean indicating if we are using a temporary file for storing data

    * @var bool

    */

    var $_using_tmpfile;



    /**

    * Maximum number of rows for an Excel spreadsheet (BIFF5)

    * @var integer

    */

    var $_xls_rowmax;



    /**

    * Maximum number of columns for an Excel spreadsheet (BIFF5)

    * @var integer

    */

    var $_xls_colmax;



    /**

    * Maximum number of characters for a string (LABEL record in BIFF5)

    * @var integer

    */

    var $_xls_strmax;



    /**

    * First row for the DIMENSIONS record

    * @var integer

    * @see _storeDimensions()

    */

    var $_dim_rowmin;



    /**

    * Last row for the DIMENSIONS record

    * @var integer

    * @see _storeDimensions()

    */

    var $_dim_rowmax;



    /**

    * First column for the DIMENSIONS record

    * @var integer

    * @see _storeDimensions()

    */

    var $_dim_colmin;



    /**

    * Last column for the DIMENSIONS record

    * @var integer

    * @see _storeDimensions()

    */

    var $_dim_colmax;



    /**

    * Array containing format information for columns

    * @var array

    */

    var $_colinfo;



    /**

    * Array containing the selected area for the worksheet

    * @var array

    */

    var $_selection;



    /**

    * Array containing the panes for the worksheet

    * @var array

    */

    var $_panes;



    /**

    * The active pane for the worksheet

    * @var integer

    */

    var $_active_pane;



    /**

    * Bit specifying if panes are frozen

    * @var integer

    */

    var $_frozen;



    /**

    * Bit specifying if the worksheet is selected

    * @var integer

    */

    var $selected;



    /**

    * The paper size (for printing) (DOCUMENT!!!)

    * @var integer

    */

    var $_paper_size;



    /**

    * Bit specifying paper orientation (for printing). 0 => landscape, 1 => portrait

    * @var integer

    */

    var $_orientation;



    /**

    * The page header caption

    * @var string

    */

    var $_header;



    /**

    * The page footer caption

    * @var string

    */

    var $_footer;



    /**

    * The horizontal centering value for the page

    * @var integer

    */

    var $_hcenter;



    /**

    * The vertical centering value for the page

    * @var integer

    */

    var $_vcenter;



    /**

    * The margin for the header

    * @var float

    */

    var $_margin_head;



    /**

    * The margin for the footer

    * @var float

    */

    var $_margin_foot;



    /**

    * The left margin for the worksheet in inches

    * @var float

    */

    var $_margin_left;



    /**

    * The right margin for the worksheet in inches

    * @var float

    */

    var $_margin_right;



    /**

    * The top margin for the worksheet in inches

    * @var float

    */

    var $_margin_top;



    /**

    * The bottom margin for the worksheet in inches

    * @var float

    */

    var $_margin_bottom;



    /**

    * First row to reapeat on each printed page

    * @var integer

    */

    var $title_rowmin;



    /**

    * Last row to reapeat on each printed page

    * @var integer

    */

    var $title_rowmax;



    /**

    * First column to reapeat on each printed page

    * @var integer

    */

    var $title_colmin;



    /**

    * First row of the area to print

    * @var integer

    */

    var $print_rowmin;



    /**

    * Last row to of the area to print

    * @var integer

    */

    var $print_rowmax;



    /**

    * First column of the area to print

    * @var integer

    */

    var $print_colmin;



    /**

    * Last column of the area to print

    * @var integer

    */

    var $print_colmax;



    /**

    * Whether to use outline.

    * @var integer

    */

    var $_outline_on;



    /**

    * Auto outline styles.

    * @var bool

    */

    var $_outline_style;



    /**

    * Whether to have outline summary below.

    * @var bool

    */

    var $_outline_below;



    /**

    * Whether to have outline summary at the right.

    * @var bool

    */

    var $_outline_right;



    /**

    * Outline row level.

    * @var integer

    */

    var $_outline_row_level;



    /**

    * Whether to fit to page when printing or not.

    * @var bool

    */

    var $_fit_page;



    /**

    * Number of pages to fit wide

    * @var integer

    */

    var $_fit_width;



    /**

    * Number of pages to fit high

    * @var integer

    */

    var $_fit_height;



    /**

    * Reference to the total number of strings in the workbook

    * @var integer

    */

    var $_str_total;



    /**

    * Reference to the number of unique strings in the workbook

    * @var integer

    */

    var $_str_unique;



    /**

    * Reference to the array containing all the unique strings in the workbook

    * @var array

    */

    var $_str_table;



    /**

    * Merged cell ranges

    * @var array

    */

    var $_merged_ranges;



    /**

    * Charset encoding currently used when calling writeString()

    * @var string

    */

    var $_input_encoding;



    /**

    * Constructor

    *

    * @param string  $name         The name of the new worksheet

    * @param integer $index        The index of the new worksheet

    * @param mixed   &$activesheet The current activesheet of the workbook we belong to

    * @param mixed   &$firstsheet  The first worksheet in the workbook we belong to

    * @param mixed   &$url_format  The default format for hyperlinks

    * @param mixed   &$parser      The formula parser created for the Workbook

    * @param string  $tmp_dir      The path to the directory for temporary files

    * @access private

    */

    function Spreadsheet_Excel_Writer_Worksheet($BIFF_version, $name,

                                                $index, &$activesheet,

                                                &$firstsheet, &$str_total,

                                                &$str_unique, &$str_table,

                                                &$url_format, &$parser,

                                                $tmp_dir)

    {

        // It needs to call its parent's constructor explicitly

        $this->Spreadsheet_Excel_Writer_BIFFwriter();

        $this->_BIFF_version   = $BIFF_version;

        $rowmax                = 65536; // 16384 in Excel 5

        $colmax                = 256;



        $this->name            = $name;

        $this->index           = $index;

        $this->activesheet     = &$activesheet;

        $this->firstsheet      = &$firstsheet;

        $this->_str_total      = &$str_total;

        $this->_str_unique     = &$str_unique;

        $this->_str_table      = &$str_table;

        $this->_url_format     = &$url_format;

        $this->_parser         = &$parser;



        //$this->ext_sheets      = array();

        $this->_filehandle     = '';

        $this->_using_tmpfile  = true;

        //$this->fileclosed      = 0;

        //$this->offset          = 0;

        $this->_xls_rowmax     = $rowmax;

        $this->_xls_colmax     = $colmax;

        $this->_xls_strmax     = 255;

        $this->_dim_rowmin     = $rowmax + 1;

        $this->_dim_rowmax     = 0;

        $this->_dim_colmin     = $colmax + 1;

        $this->_dim_colmax     = 0;

        $this->_colinfo        = array();

        $this->_selection      = array(0,0,0,0);

        $this->_panes          = array();

        $this->_active_pane    = 3;

        $this->_frozen         = 0;

        $this->selected        = 0;



        $this->_paper_size      = 0x0;

        $this->_orientation     = 0x1;

        $this->_header          = '';

        $this->_footer          = '';

        $this->_hcenter         = 0;

        $this->_vcenter         = 0;

        $this->_margin_head     = 0.50;

        $this->_margin_foot     = 0.50;

        $this->_margin_left     = 0.75;

        $this->_margin_right    = 0.75;

        $this->_margin_top      = 1.00;

        $this->_margin_bottom   = 1.00;



        $this->title_rowmin     = null;

        $this->title_rowmax     = null;

        $this->title_colmin     = null;

        $this->title_colmax     = null;

        $this->print_rowmin     = null;

        $this->print_rowmax     = null;

        $this->print_colmin     = null;

        $this->print_colmax     = null;



        $this->_print_gridlines  = 1;

        $this->_screen_gridlines = 1;

        $this->_print_headers    = 0;



        $this->_fit_page        = 0;

        $this->_fit_width       = 0;

        $this->_fit_height      = 0;



        $this->_hbreaks         = array();

        $this->_vbreaks         = array();



        $this->_protect         = 0;

        $this->_password        = null;



        $this->col_sizes        = array();

        $this->_row_sizes        = array();



        $this->_zoom            = 100;

        $this->_print_scale     = 100;



        $this->_outline_row_level = 0;

        $this->_outline_style     = 0;

        $this->_outline_below     = 1;

        $this->_outline_right     = 1;

        $this->_outline_on        = 1;



        $this->_merged_ranges     = array();



        $this->_input_encoding    = '';



        $this->_dv                = array();

        

        $this->_tmp_dir = $tmp_dir;



        $this->_initialize();

    }



    /**

    * Open a tmp file to store the majority of the Worksheet data. If this fails,

    * for example due to write permissions, store the data in memory. This can be

    * slow for large files.

    *

    * @access private

    */

    function _initialize()

    {

        if ($this->_using_tmpfile == false) {

            return;

        }



        if ($this->_tmp_dir === '' && ini_get('open_basedir') === false) {

            // open_basedir restriction in effect - store data in memory

            // ToDo: Let the error actually have an effect somewhere

            $this->_using_tmpfile = false;  

            return new PEAR_Error('Temp file could not be opened since open_basedir restriction in effect - please use setTmpDir() - using memory storage instead');

        }



        // Open tmp file for storing Worksheet data

        if ($this->_tmp_dir === '') {

            $fh = tmpfile();

        } else {

            // For people with open base dir restriction

            $tmpfilename = tempnam($this->_tmp_dir, "Spreadsheet_Excel_Writer");

            $fh = @fopen($tmpfilename, "w+b");

        }



        if ($fh === false) {

            // If tmpfile() fails store data in memory

            $this->_using_tmpfile = false;

        } else {

            // Store filehandle

            $this->_filehandle = $fh;

        }

    }



    /**

    * Add data to the beginning of the workbook (note the reverse order)

    * and to the end of the workbook.

    *

    * @access public

    * @see Spreadsheet_Excel_Writer_Workbook::storeWorkbook()

    * @param array $sheetnames The array of sheetnames from the Workbook this

    *                          worksheet belongs to

    */

    function close($sheetnames)

    {

        $num_sheets = count($sheetnames);



        /***********************************************

        * Prepend in reverse order!!

        */



        // Prepend the sheet dimensions

        $this->_storeDimensions();



        // Prepend the sheet password

        $this->_storePassword();



        // Prepend the sheet protection

        $this->_storeProtect();



        // Prepend the page setup

        $this->_storeSetup();



        /* FIXME: margins are actually appended */

        // Prepend the bottom margin

        $this->_storeMarginBottom();



        // Prepend the top margin

        $this->_storeMarginTop();



        // Prepend the right margin

        $this->_storeMarginRight();



        // Prepend the left margin

        $this->_storeMarginLeft();



        // Prepend the page vertical centering

        $this->_storeVcenter();



        // Prepend the page horizontal centering

        $this->_storeHcenter();



        // Prepend the page footer

        $this->_storeFooter();



        // Prepend the page header

        $this->_storeHeader();



        // Prepend the vertical page breaks

        $this->_storeVbreak();



        // Prepend the horizontal page breaks

        $this->_storeHbreak();



        // Prepend WSBOOL

        $this->_storeWsbool();



        // Prepend GRIDSET

        $this->_storeGridset();



         //  Prepend GUTS

        if ($this->_BIFF_version == 0x0500) {

            $this->_storeGuts();

        }



        // Prepend PRINTGRIDLINES

        $this->_storePrintGridlines();



        // Prepend PRINTHEADERS

        $this->_storePrintHeaders();



        // Prepend EXTERNSHEET references

        if ($this->_BIFF_version == 0x0500) {

            for ($i = $num_sheets; $i > 0; $i--) {

                $sheetname = $sheetnames[$i-1];

                $this->_storeExternsheet($sheetname);

            }

        }



        // Prepend the EXTERNCOUNT of external references.

        if ($this->_BIFF_version == 0x0500) {

            $this->_storeExterncount($num_sheets);

        }



        // Prepend the COLINFO records if they exist

        if (!empty($this->_colinfo)) {

            $colcount = count($this->_colinfo);

            for ($i = 0; $i < $colcount; $i++) {

                $this->_storeColinfo($this->_colinfo[$i]);

            }

            $this->_storeDefcol();

        }



        // Prepend the BOF record

        $this->_storeBof(0x0010);



        /*

        * End of prepend. Read upwards from here.

        ***********************************************/



        // Append

        $this->_storeWindow2();

        $this->_storeZoom();

        if (!empty($this->_panes)) {

            $this->_storePanes($this->_panes);

        }

        $this->_storeSelection($this->_selection);

        $this->_storeMergedCells();

        /* TODO: add data validity */

        /*if ($this->_BIFF_version == 0x0600) {

            $this->_storeDataValidity();

        }*/

        $this->_storeEof();

    }



    /**

    * Retrieve the worksheet name.

    * This is usefull when creating worksheets without a name.

    *

    * @access public

    * @return string The worksheet's name

    */

    function getName()

    {

        return $this->name;

    }



    /**

    * Retrieves data from memory in one chunk, or from disk in $buffer

    * sized chunks.

    *

    * @return string The data

    */

    function getData()

    {

        $buffer = 4096;



        // Return data stored in memory

        if (isset($this->_data)) {

            $tmp   = $this->_data;

            unset($this->_data);

            $fh    = $this->_filehandle;

            if ($this->_using_tmpfile) {

                fseek($fh, 0);

            }

            return $tmp;

        }

        // Return data stored on disk

        if ($this->_using_tmpfile) {

            if ($tmp = fread($this->_filehandle, $buffer)) {

                return $tmp;

            }

        }



        // No data to return

        return '';

    }



    /**

    * Sets a merged cell range

    *

    * @access public

    * @param integer $first_row First row of the area to merge

    * @param integer $first_col First column of the area to merge

    * @param integer $last_row  Last row of the area to merge

    * @param integer $last_col  Last column of the area to merge

    */

    function setMerge($first_row, $first_col, $last_row, $last_col)

    {

        if (($last_row < $first_row) || ($last_col < $first_col)) {

            return;

        }

        // don't check rowmin, rowmax, etc... because we don't know when this

        // is going to be called

        $this->_merged_ranges[] = array($first_row, $first_col, $last_row, $last_col);

    }



    /**

    * Set this worksheet as a selected worksheet,

    * i.e. the worksheet has its tab highlighted.

    *

    * @access public

    */

    function select()

    {

        $this->selected = 1;

    }



    /**

    * Set this worksheet as the active worksheet,

    * i.e. the worksheet that is displayed when the workbook is opened.

    * Also set it as selected.

    *

    * @access public

    */

    function activate()

    {

        $this->selected = 1;

        $this->activesheet = $this->index;

    }



    /**

    * Set this worksheet as the first visible sheet.

    * This is necessary when there are a large number of worksheets and the

    * activated worksheet is not visible on the screen.

    *

    * @access public

    */

    function setFirstSheet()

    {

        $this->firstsheet = $this->index;

    }



    /**

    * Set the worksheet protection flag

    * to prevent accidental modification and to

    * hide formulas if the locked and hidden format properties have been set.

    *

    * @access public

    * @param string $password The password to use for protecting the sheet.

    */

    function protect($password)

    {

        $this->_protect   = 1;

        $this->_password  = $this->_encodePassword($password);

    }



    /**

    * Set the width of a single column or a range of columns.

    *

    * @access public

    * @param integer $firstcol first column on the range

    * @param integer $lastcol  last column on the range

    * @param integer $width    width to set

    * @param mixed   $format   The optional XF format to apply to the columns

    * @param integer $hidden   The optional hidden atribute

    * @param integer $level    The optional outline level

    */

    function setColumn($firstcol, $lastcol, $width, $format = null, $hidden = 0, $level = 0)

    {

        $this->_colinfo[] = array($firstcol, $lastcol, $width, &$format, $hidden, $level);



        // Set width to zero if column is hidden

        $width = ($hidden) ? 0 : $width;



        for ($col = $firstcol; $col <= $lastcol; $col++) {

            $this->col_sizes[$col] = $width;

        }

    }



    /**

    * Set which cell or cells are selected in a worksheet

    *

    * @access public

    * @param integer $first_row    first row in the selected quadrant

    * @param integer $first_column first column in the selected quadrant

    * @param integer $last_row     last row in the selected quadrant

    * @param integer $last_column  last column in the selected quadrant

    */

    function setSelection($first_row,$first_column,$last_row,$last_column)

    {

        $this->_selection = array($first_row,$first_column,$last_row,$last_column);

    }



    /**

    * Set panes and mark them as frozen.

    *

    * @access public

    * @param array $panes This is the only parameter received and is composed of the following:

    *                     0 => Vertical split position,

    *                     1 => Horizontal split position

    *                     2 => Top row visible

    *                     3 => Leftmost column visible

    *                     4 => Active pane

    */

    function freezePanes($panes)

    {

        $this->_frozen = 1;

        $this->_panes  = $panes;

    }



    /**

    * Set panes and mark them as unfrozen.

    *

    * @access public

    * @param array $panes This is the only parameter received and is composed of the following:

    *                     0 => Vertical split position,

    *                     1 => Horizontal split position

    *                     2 => Top row visible

    *                     3 => Leftmost column visible

    *                     4 => Active pane

    */

    function thawPanes($panes)

    {

        $this->_frozen = 0;

        $this->_panes  = $panes;

    }



    /**

    * Set the page orientation as portrait.

    *

    * @access public

    */

    function setPortrait()

    {

        $this->_orientation = 1;

    }



    /**

    * Set the page orientation as landscape.

    *

    * @access public

    */

    function setLandscape()

    {

        $this->_orientation = 0;

    }



    /**

    * Set the paper type. Ex. 1 = US Letter, 9 = A4

    *

    * @access public

    * @param integer $size The type of paper size to use

    */

    function setPaper($size = 0)

    {

        $this->_paper_size = $size;

    }





    /**

    * Set the page header caption and optional margin.

    *

    * @access public

    * @param string $string The header text

    * @param float  $margin optional head margin in inches.

    */

    function setHeader($string,$margin = 0.50)

    {

        if (strlen($string) >= 255) {

            //carp 'Header string must be less than 255 characters';

            return;

        }

        $this->_header      = $string;

        $this->_margin_head = $margin;

    }



    /**

    * Set the page footer caption and optional margin.

    *

    * @access public

    * @param string $string The footer text

    * @param float  $margin optional foot margin in inches.

    */

    function setFooter($string,$margin = 0.50)

    {

        if (strlen($string) >= 255) {

            //carp 'Footer string must be less than 255 characters';

            return;

        }

        $this->_footer      = $string;

        $this->_margin_foot = $margin;

    }



    /**

    * Center the page horinzontally.

    *

    * @access public

    * @param integer $center the optional value for centering. Defaults to 1 (center).

    */

    function centerHorizontally($center = 1)

    {

        $this->_hcenter = $center;

    }



    /**

    * Center the page vertically.

    *

    * @access public

    * @param integer $center the optional value for centering. Defaults to 1 (center).

    */

    function centerVertically($center = 1)

    {

        $this->_vcenter = $center;

    }



    /**

    * Set all the page margins to the same value in inches.

    *

    * @access public

    * @param float $margin The margin to set in inches

    */

    function setMargins($margin)

    {

        $this->setMarginLeft($margin);

        $this->setMarginRight($margin);

        $this->setMarginTop($margin);

        $this->setMarginBottom($margin);

    }



    /**

    * Set the left and right margins to the same value in inches.

    *

    * @access public

    * @param float $margin The margin to set in inches

    */

    function setMargins_LR($margin)

    {

        $this->setMarginLeft($margin);

        $this->setMarginRight($margin);

    }



    /**

    * Set the top and bottom margins to the same value in inches.

    *

    * @access public

    * @param float $margin The margin to set in inches

    */

    function setMargins_TB($margin)

    {

        $this->setMarginTop($margin);

        $this->setMarginBottom($margin);

    }



    /**

    * Set the left margin in inches.

    *

    * @access public

    * @param float $margin The margin to set in inches

    */

    function setMarginLeft($margin = 0.75)

    {

        $this->_margin_left = $margin;

    }



    /**

    * Set the right margin in inches.

    *

    * @access public

    * @param float $margin The margin to set in inches

    */

    function setMarginRight($margin = 0.75)

    {

        $this->_margin_right = $margin;

    }



    /**

    * Set the top margin in inches.

    *

    * @access public

    * @param float $margin The margin to set in inches

    */

    function setMarginTop($margin = 1.00)

    {

        $this->_margin_top = $margin;

    }



    /**

    * Set the bottom margin in inches.

    *

    * @access public

    * @param float $margin The margin to set in inches

    */

    function setMarginBottom($margin = 1.00)

    {

        $this->_margin_bottom = $margin;

    }



    /**

    * Set the rows to repeat at the top of each printed page.

    *

    * @access public

    * @param integer $first_row First row to repeat

    * @param integer $last_row  Last row to repeat. Optional.

    */

    function repeatRows($first_row, $last_row = null)

    {

        $this->title_rowmin  = $first_row;

        if (isset($last_row)) { //Second row is optional

            $this->title_rowmax  = $last_row;

        } else {

            $this->title_rowmax  = $first_row;

        }

    }



    /**

    * Set the columns to repeat at the left hand side of each printed page.

    *

    * @access public

    * @param integer $first_col First column to repeat

    * @param integer $last_col  Last column to repeat. Optional.

    */

    function repeatColumns($first_col, $last_col = null)

    {

        $this->title_colmin  = $first_col;

        if (isset($last_col)) { // Second col is optional

            $this->title_colmax  = $last_col;

        } else {

            $this->title_colmax  = $first_col;

        }

    }



    /**

    * Set the area of each worksheet that will be printed.

    *

    * @access public

    * @param integer $first_row First row of the area to print

    * @param integer $first_col First column of the area to print

    * @param integer $last_row  Last row of the area to print

    * @param integer $last_col  Last column of the area to print

    */

    function printArea($first_row, $first_col, $last_row, $last_col)

    {

        $this->print_rowmin  = $first_row;

        $this->print_colmin  = $first_col;

        $this->print_rowmax  = $last_row;

        $this->print_colmax  = $last_col;

    }





    /**

    * Set the option to hide gridlines on the printed page.

    *

    * @access public

    */

    function hideGridlines()

    {

        $this->_print_gridlines = 0;

    }



    /**

    * Set the option to hide gridlines on the worksheet (as seen on the screen).

    *

    * @access public

    */

    function hideScreenGridlines()

    {

        $this->_screen_gridlines = 0;

    }



    /**

    * Set the option to print the row and column headers on the printed page.

    *

    * @access public

    * @param integer $print Whether to print the headers or not. Defaults to 1 (print).

    */

    function printRowColHeaders($print = 1)

    {

        $this->_print_headers = $print;

    }



    /**

    * Set the vertical and horizontal number of pages that will define the maximum area printed.

    * It doesn't seem to work with OpenOffice.

    *

    * @access public

    * @param  integer $width  Maximun width of printed area in pages

    * @param  integer $height Maximun heigth of printed area in pages

    * @see setPrintScale()

    */

    function fitToPages($width, $height)

    {

        $this->_fit_page      = 1;

        $this->_fit_width     = $width;

        $this->_fit_height    = $height;

    }



    /**

    * Store the horizontal page breaks on a worksheet (for printing).

    * The breaks represent the row after which the break is inserted.

    *

    * @access public

    * @param array $breaks Array containing the horizontal page breaks

    */

    function setHPagebreaks($breaks)

    {

        foreach ($breaks as $break) {

            array_push($this->_hbreaks, $break);

        }

    }



    /**

    * Store the vertical page breaks on a worksheet (for printing).

    * The breaks represent the column after which the break is inserted.

    *

    * @access public

    * @param array $breaks Array containing the vertical page breaks

    */

    function setVPagebreaks($breaks)

    {

        foreach ($breaks as $break) {

            array_push($this->_vbreaks, $break);

        }

    }





    /**

    * Set the worksheet zoom factor.

    *

    * @access public

    * @param integer $scale The zoom factor

    */

    function setZoom($scale = 100)

    {

        // Confine the scale to Excel's range

        if ($scale < 10 || $scale > 400) {

            $this->raiseError("Zoom factor $scale outside range: 10 <= zoom <= 400");

            $scale = 100;

        }



        $this->_zoom = floor($scale);

    }



    /**

    * Set the scale factor for the printed page.

    * It turns off the "fit to page" option

    *

    * @access public

    * @param integer $scale The optional scale factor. Defaults to 100

    */

    function setPrintScale($scale = 100)

    {

        // Confine the scale to Excel's range

        if ($scale < 10 || $scale > 400) {

            $this->raiseError("Print scale $scale outside range: 10 <= zoom <= 400");

            $scale = 100;

        }



        // Turn off "fit to page" option

        $this->_fit_page = 0;



        $this->_print_scale = floor($scale);

    }



    /**

    * Map to the appropriate write method acording to the token recieved.

    *

    * @access public

    * @param integer $row    The row of the cell we are writing to

    * @param integer $col    The column of the cell we are writing to

    * @param mixed   $token  What we are writing

    * @param mixed   $format The optional format to apply to the cell

    */

    function write($row, $col, $token, $format = null)

    {

        // Check for a cell reference in A1 notation and substitute row and column

        /*if ($_[0] =~ /^\D/) {

            @_ = $this->_substituteCellref(@_);

    }*/



        if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", $token)) {

            // Match number

            return $this->writeNumber($row, $col, $token, $format);

        } elseif (preg_match("/^[fh]tt?p:\/\//", $token)) {

            // Match http or ftp URL

            return $this->writeUrl($row, $col, $token, '', $format);

        } elseif (preg_match("/^mailto:/", $token)) {

            // Match mailto:

            return $this->writeUrl($row, $col, $token, '', $format);

        } elseif (preg_match("/^(?:in|ex)ternal:/", $token)) {

            // Match internal or external sheet link

            return $this->writeUrl($row, $col, $token, '', $format);

        } elseif (preg_match("/^=/", $token)) {

            // Match formula

            return $this->writeFormula($row, $col, $token, $format);

        } elseif ($token == '') {

            // Match blank

            return $this->writeBlank($row, $col, $format);

        } else {

            // Default: match string

            return $this->writeString($row, $col, $token, $format);

        }

    }



    /**

    * Write an array of values as a row

    *

    * @access public

    * @param integer $row    The row we are writing to

    * @param integer $col    The first col (leftmost col) we are writing to

    * @param array   $val    The array of values to write

    * @param mixed   $format The optional format to apply to the cell

    * @return mixed PEAR_Error on failure

    */



    function writeRow($row, $col, $val, $format = null)

    {

        $retval = '';

        if (is_array($val)) {

            foreach ($val as $v) {

                if (is_array($v)) {

                    $this->writeCol($row, $col, $v, $format);

                } else {

                    $this->write($row, $col, $v, $format);

                }

                $col++;

            }

        } else {

            $retval = new PEAR_Error('$val needs to be an array');

        }

        return($retval);

    }



    /**

    * Write an array of values as a column

    *

    * @access public

    * @param integer $row    The first row (uppermost row) we are writing to

    * @param integer $col    The col we are writing to

    * @param array   $val    The array of values to write

    * @param mixed   $format The optional format to apply to the cell

    * @return mixed PEAR_Error on failure

    */



    function writeCol($row, $col, $val, $format = null)

    {

        $retval = '';

        if (is_array($val)) {

            foreach ($val as $v) {

                $this->write($row, $col, $v, $format);

                $row++;

            }

        } else {

            $retval = new PEAR_Error('$val needs to be an array');

        }

        return($retval);

    }



    /**

    * Returns an index to the XF record in the workbook

    *

    * @access private

    * @param mixed &$format The optional XF format

    * @return integer The XF record index

    */

    function _XF(&$format)

    {

        if ($format) {

            return($format->getXfIndex());

        } else {

            return(0x0F);

        }

    }





    /******************************************************************************

    *******************************************************************************

    *

    * Internal methods

    */





    /**

    * Store Worksheet data in memory using the parent's class append() or to a

    * temporary file, the default.

    *

    * @access private

    * @param string $data The binary data to append

    */

    function _append($data)

    {

        if ($this->_using_tmpfile) {

            // Add CONTINUE records if necessary

            if (strlen($data) > $this->_limit) {

                $data = $this->_addContinue($data);

            }

            fwrite($this->_filehandle, $data);

            $this->_datasize += strlen($data);

        } else {

            parent::_append($data);

        }

    }



    /**

    * Substitute an Excel cell reference in A1 notation for  zero based row and

    * column values in an argument list.

    *

    * Ex: ("A4", "Hello") is converted to (3, 0, "Hello").

    *

    * @access private

    * @param string $cell The cell reference. Or range of cells.

    * @return array

    */

    function _substituteCellref($cell)

    {

        $cell = strtoupper($cell);



        // Convert a column range: 'A:A' or 'B:G'

        if (preg_match("/([A-I]?[A-Z]):([A-I]?[A-Z])/", $cell, $match)) {

            list($no_use, $col1) =  $this->_cellToRowcol($match[1] .'1'); // Add a dummy row

            list($no_use, $col2) =  $this->_cellToRowcol($match[2] .'1'); // Add a dummy row

            return(array($col1, $col2));

        }



        // Convert a cell range: 'A1:B7'

        if (preg_match("/\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/", $cell, $match)) {

            list($row1, $col1) =  $this->_cellToRowcol($match[1]);

            list($row2, $col2) =  $this->_cellToRowcol($match[2]);

            return(array($row1, $col1, $row2, $col2));

        }



        // Convert a cell reference: 'A1' or 'AD2000'

        if (preg_match("/\$?([A-I]?[A-Z]\$?\d+)/", $cell)) {

            list($row1, $col1) =  $this->_cellToRowcol($match[1]);

            return(array($row1, $col1));

        }



        // TODO use real error codes

        $this->raiseError("Unknown cell reference $cell", 0, PEAR_ERROR_DIE);

    }



    /**

    * Convert an Excel cell reference in A1 notation to a zero based row and column

    * reference; converts C1 to (0, 2).

    *

    * @access private

    * @param string $cell The cell reference.

    * @return array containing (row, column)

    */

    function _cellToRowcol($cell)

    {

        preg_match("/\$?([A-I]?[A-Z])\$?(\d+)/",$cell,$match);

        $col     = $match[1];

        $row     = $match[2];



        // Convert base26 column string to number

        $chars = split('', $col);

        $expn  = 0;

        $col   = 0;



        while ($chars) {

            $char = array_pop($chars);        // LS char first

            $col += (ord($char) -ord('A') +1) * pow(26,$expn);

            $expn++;

        }



        // Convert 1-index to zero-index

        $row--;

        $col--;



        return(array($row, $col));

    }



    /**

    * Based on the algorithm provided by Daniel Rentz of OpenOffice.

    *

    * @access private

    * @param string $plaintext The password to be encoded in plaintext.

    * @return string The encoded password

    */

    function _encodePassword($plaintext)

    {

        $password = 0x0000;

        $i        = 1;       // char position



        // split the plain text password in its component characters

        $chars = preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY);

        foreach ($chars as $char) {

            $value        = ord($char) << $i;   // shifted ASCII value

            $rotated_bits = $value >> 15;       // rotated bits beyond bit 15

            $value       &= 0x7fff;             // first 15 bits

            $password    ^= ($value | $rotated_bits);

            $i++;

        }



        $password ^= strlen($plaintext);

        $password ^= 0xCE4B;



        return($password);

    }



    /**

    * This method sets the properties for outlining and grouping. The defaults

    * correspond to Excel's defaults.

    *

    * @param bool $visible

    * @param bool $symbols_below

    * @param bool $symbols_right

    * @param bool $auto_style

    */

    function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false)

    {

        $this->_outline_on    = $visible;

        $this->_outline_below = $symbols_below;

        $this->_outline_right = $symbols_right;

        $this->_outline_style = $auto_style;



        // Ensure this is a boolean vale for Window2

        if ($this->_outline_on) {

            $this->_outline_on = 1;

        }

     }



    /******************************************************************************

    *******************************************************************************

    *

    * BIFF RECORDS

    */





    /**

    * Write a double to the specified row and column (zero indexed).

    * An integer can be written as a double. Excel will display an

    * integer. $format is optional.

    *

    * Returns  0 : normal termination

    *         -2 : row or column out of range

    *

    * @access public

    * @param integer $row    Zero indexed row

    * @param integer $col    Zero indexed column

    * @param float   $num    The number to write

    * @param mixed   $format The optional XF format

    * @return integer

    */

    function writeNumber($row, $col, $num, $format = null)

    {

        $record    = 0x0203;                 // Record identifier

        $length    = 0x000E;                 // Number of bytes to follow



        $xf        = $this->_XF($format);    // The cell format



        // Check that row and col are valid and store max and min values

        if ($row >= $this->_xls_rowmax) {

            return(-2);

        }

        if ($col >= $this->_xls_colmax) {

            return(-2);

        }

        if ($row <  $this->_dim_rowmin)  {

            $this->_dim_rowmin = $row;

        }

        if ($row >  $this->_dim_rowmax)  {

            $this->_dim_rowmax = $row;

        }

        if ($col <  $this->_dim_colmin)  {

            $this->_dim_colmin = $col;

        }

        if ($col >  $this->_dim_colmax)  {

            $this->_dim_colmax = $col;

        }



        $header    = pack("vv",  $record, $length);

        $data      = pack("vvv", $row, $col, $xf);

        $xl_double = pack("d",   $num);

        if ($this->_byte_order) { // if it's Big Endian

            $xl_double = strrev($xl_double);

        }



        $this->_append($header.$data.$xl_double);

        return(0);

    }



    /**

    * Write a string to the specified row and column (zero indexed).

    * NOTE: there is an Excel 5 defined limit of 255 characters.

    * $format is optional.

    * Returns  0 : normal termination

    *         -2 : row or column out of range

    *         -3 : long string truncated to 255 chars

    *

    * @access public

    * @param integer $row    Zero indexed row

    * @param integer $col    Zero indexed column

    * @param string  $str    The string to write

    * @param mixed   $format The XF format for the cell

    * @return integer

    */

    function writeString($row, $col, $str, $format = null)

    {

        if ($this->_BIFF_version == 0x0600) {

            return $this->writeStringBIFF8($row, $col, $str, $format);

        }

        $strlen    = strlen($str);

        $record    = 0x0204;                   // Record identifier

        $length    = 0x0008 + $strlen;         // Bytes to follow

        $xf        = $this->_XF($format);      // The cell format



        $str_error = 0;



        // Check that row and col are valid and store max and min values

        if ($row >= $this->_xls_rowmax) {

            return(-2);

        }

        if ($col >= $this->_xls_colmax) {

            return(-2);

        }

        if ($row <  $this->_dim_rowmin) {

            $this->_dim_rowmin = $row;

        }

        if ($row >  $this->_dim_rowmax) {

            $this->_dim_rowmax = $row;

        }

        if ($col <  $this->_dim_colmin) {

            $this->_dim_colmin = $col;

        }

        if ($col >  $this->_dim_colmax) {

            $this->_dim_colmax = $col;

        }



        if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars

            $str       = substr($str, 0, $this->_xls_strmax);

            $length    = 0x0008 + $this->_xls_strmax;

            $strlen    = $this->_xls_strmax;

            $str_error = -3;

        }



        $header    = pack("vv",   $record, $length);

        $data      = pack("vvvv", $row, $col, $xf, $strlen);

        $this->_append($header . $data . $str);

        return($str_error);

    }



    /**

    * Sets Input Encoding for writing strings

    *

    * @access public

    * @param string $encoding The encoding. Ex: 'UTF-16LE', 'utf-8', 'ISO-859-7'

    */

    function setInputEncoding($encoding)

    {

         if ($encoding != 'UTF-16LE' && !function_exists('iconv')) {

             $this->raiseError("Using an input encoding other than UTF-16LE requires PHP support for iconv");

         }

         $this->_input_encoding = $encoding;

    }



    /**

    * Write a string to the specified row and column (zero indexed).

    * This is the BIFF8 version (no 255 chars limit).

    * $format is optional.

    * Returns  0 : normal termination

    *         -2 : row or column out of range

    *         -3 : long string truncated to 255 chars

    *

    * @access public

    * @param integer $row    Zero indexed row

    * @param integer $col    Zero indexed column

    * @param string  $str    The string to write

    * @param mixed   $format The XF format for the cell

    * @return integer

    */

    function writeStringBIFF8($row, $col, $str, $format = null)

    {

        if ($this->_input_encoding == 'UTF-16LE')

        {

            $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);

            $encoding  = 0x1;

        }

        elseif ($this->_input_encoding != '')

        {

            $str = iconv($this->_input_encoding, 'UTF-16LE', $str);

            $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);

            $encoding  = 0x1;

        }

        else

        {

            $strlen    = strlen($str);

            $encoding  = 0x0;

        }

        $record    = 0x00FD;                   // Record identifier

        $length    = 0x000A;                   // Bytes to follow

        $xf        = $this->_XF($format);      // The cell format



        $str_error = 0;



        // Check that row and col are valid and store max and min values

        if ($this->_checkRowCol($row, $col) == false) {

            return -2;

        }



        $str = pack('vC', $strlen, $encoding).$str;



        /* check if string is already present */

        if (!isset($this->_str_table[$str])) {

            $this->_str_table[$str] = $this->_str_unique++;

        }

        $this->_str_total++;



        $header    = pack('vv',   $record, $length);

        $data      = pack('vvvV', $row, $col, $xf, $this->_str_table[$str]);

        $this->_append($header.$data);

        return $str_error;

    }



    /**

    * Check row and col before writing to a cell, and update the sheet's

    * dimensions accordingly

    *

    * @access private

    * @param integer $row    Zero indexed row

    * @param integer $col    Zero indexed column

    * @return boolean true for success, false if row and/or col are grester

    *                 then maximums allowed.

    */

    function _checkRowCol($row, $col)

    {

        if ($row >= $this->_xls_rowmax) {

            return false;

        }

        if ($col >= $this->_xls_colmax) {

            return false;

        }

        if ($row <  $this->_dim_rowmin) {

            $this->_dim_rowmin = $row;

        }

        if ($row >  $this->_dim_rowmax) {

            $this->_dim_rowmax = $row;

        }

        if ($col <  $this->_dim_colmin) {

            $this->_dim_colmin = $col;

        }

        if ($col >  $this->_dim_colmax) {

            $this->_dim_colmax = $col;

        }

        return true;

    }



    /**

    * Writes a note associated with the cell given by the row and column.

    * NOTE records don't have a length limit.

    *

    * @access public

    * @param integer $row    Zero indexed row

    * @param integer $col    Zero indexed column

    * @param string  $note   The note to write

    */

    function writeNote($row, $col, $note)

    {

        $note_length    = strlen($note);

        $record         = 0x001C;                // Record identifier

        $max_length     = 2048;                  // Maximun length for a NOTE record

        //$length      = 0x0006 + $note_length;    // Bytes to follow



        // Check that row and col are valid and store max and min values

        if ($row >= $this->_xls_rowmax) {

            return(-2);

        }

        if ($col >= $this->_xls_colmax) {

            return(-2);

        }

        if ($row <  $this->_dim_rowmin) {

            $this->_dim_rowmin = $row;

        }

        if ($row >  $this->_dim_rowmax) {

            $this->_dim_rowmax = $row;

        }

        if ($col <  $this->_dim_colmin) {

            $this->_dim_colmin = $col;

        }

        if ($col >  $this->_dim_colmax) {

            $this->_dim_colmax = $col;

        }



        // Length for this record is no more than 2048 + 6

        $length    = 0x0006 + min($note_length, 2048);

        $header    = pack("vv",   $record, $length);

        $data      = pack("vvv", $row, $col, $note_length);

        $this->_append($header . $data . substr($note, 0, 2048));



        for ($i = $max_length; $i < $note_length; $i += $max_length) {

            $chunk  = substr($note, $i, $max_length);

            $length = 0x0006 + strlen($chunk);

            $header = pack("vv",   $record, $length);

            $data   = pack("vvv", -1, 0, strlen($chunk));

            $this->_append($header.$data.$chunk);

        }

        return(0);

    }



    /**

    * Write a blank cell to the specified row and column (zero indexed).

    * A blank cell is used to specify formatting without adding a string

    * or a number.

    *

    * A blank cell without a format serves no purpose. Therefore, we don't write

    * a BLANK record unless a format is specified.

    *

    * Returns  0 : normal termination (including no format)

    *         -1 : insufficient number of arguments

    *         -2 : row or column out of rang…

Large files files are truncated, but you can click here to view the full file