WebSVN – Sites.obs-saisons.fr – Diff – /trunk/applications/jrest/lib/Spreadsheet/Excel/Writer/Worksheet.php


<?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('Parser.php');
require_once('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;
 
    /**
    * 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
    */
    function Spreadsheet_Excel_Writer_Worksheet($name, $index, &$activesheet,
                                                &$firstsheet, &$url_format,
                                                &$parser)
    {
        // It needs to call its parent's constructor explicitly
        $this->Spreadsheet_Excel_Writer_BIFFwriter();
        $rowmax                = 65536; // 16384 in Excel 5
        $colmax                = 256;
    
        $this->name            = $name;
        $this->index           = $index;
        $this->activesheet     = &$activesheet;
        $this->firstsheet      = &$firstsheet;
        $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->_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->_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()
    {
        // Open tmp file for storing Worksheet data
        $fh = tmpfile();
        if ( $fh) {
            // Store filehandle
            $this->_filehandle = $fh;
        }
        else {
            // If tmpfile() fails store data in memory
            $this->_using_tmpfile = false;
        }
    }
    
    /**
    * 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();
    
        // 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 PRINTGRIDLINES
        $this->_storePrintGridlines();
    
        // Prepend PRINTHEADERS
        $this->_storePrintHeaders();
    
        // Prepend EXTERNSHEET references
        for ($i = $num_sheets; $i > 0; $i--)
        {
            $sheetname = $sheetnames[$i-1];
            $this->_storeExternsheet($sheetname);
        }
    
        // Prepend the EXTERNCOUNT of external references.
        $this->_storeExterncount($num_sheets);
    
        // Prepend the COLINFO records if they exist
        if (!empty($this->_colinfo))
        {
            for($i=0; $i < count($this->_colinfo); $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->_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('');
    }
    
    /**
    * 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
    */
    function setColumn($firstcol, $lastcol, $width, $format = 0, $hidden = 0)
    {
        $this->_colinfo[] = array($firstcol, $lastcol, $width, &$format, $hidden);
    
        // 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 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 or $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 or $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 = 0)
    {
        // Check for a cell reference in A1 notation and substitute row and column
        /*if ($_[0] =~ /^\D/) {
            @_ = $this->_substituteCellref(@_);
    }*/
    
    
        // Match number
        if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/",$token)) {
            return $this->writeNumber($row,$col,$token,$format);
        }
        // Match http or ftp URL
        elseif (preg_match("/^[fh]tt?p:\/\//",$token)) {
            return $this->writeUrl($row, $col, $token, $format);
        }
        // Match mailto:
        elseif (preg_match("/^mailto:/",$token)) {
            return $this->writeUrl($row, $col, $token, $format);
        }
        // Match internal or external sheet link
        elseif (preg_match("/^(?:in|ex)ternal:/",$token)) {
            return $this->writeUrl($row, $col, $token, $format);
        }
        // Match formula
        elseif (preg_match("/^=/",$token)) {
            return $this->writeFormula($row, $col, $token, $format);
        }
        // Match formula
        elseif (preg_match("/^@/",$token)) {
            return $this->writeFormula($row, $col, $token, $format);
        }
        // Match blank
        elseif ($token == '') {
            return $this->writeBlank($row,$col,$format);
        }
        // Default: match string
        else {
            return $this->writeString($row,$col,$token,$format);
        }
    }
    
    /**
    * Write an array of values as a row
    *
    * @access public
    * @param
    * @return 
    */
 
    function writeRow($row, $col, $val, $format=0)
    {   
        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
    * @return 
    */
    
    function writeCol($row, $col, $val, $format=0)
    {
        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 != 0) {
            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 = explode('', $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_explode('//', $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);
    }
 
    
    /******************************************************************************
    *******************************************************************************
    *
    * 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 = 0)
    {
        $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 = 0)
    {
        $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);
    }
 
    /**
    * 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 range
    *
    * @access public
    * @param integer $row    Zero indexed row
    * @param integer $col    Zero indexed column
    * @param mixed   $format The XF format
    */
    function writeBlank($row, $col, $format)
    {
        // Don't write a blank cell unless it has a format
        if ($format == 0)
        {
            return(0);
        }
    
        $record    = 0x0201;                 // Record identifier
        $length    = 0x0006;                 // 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);
        $this->_append($header.$data);
        return 0;
    }
 
    /**
    * Write a formula to the specified row and column (zero indexed).
    * The textual representation of the formula is passed to the parser in
    * Parser.php which returns a packed binary string.
    *
    * Returns  0 : normal termination
    *         -1 : formula errors (bad formula)
    *         -2 : row or column out of range
    *
    * @access public
    * @param integer $row     Zero indexed row
    * @param integer $col     Zero indexed column
    * @param string  $formula The formula text string
    * @param mixed   $format  The optional XF format
    * @return integer
    */
    function writeFormula($row, $col, $formula, $format = 0)
    {
        $record    = 0x0006;     // Record identifier
    
        // Excel normally stores the last calculated value of the formula in $num.
        // Clearly we are not in a position to calculate this a priori. Instead
        // we set $num to zero and set the option flags in $grbit to ensure
        // automatic calculation of the formula when the file is opened.
        //
        $xf        = $this->_XF($format); // The cell format
        $num       = 0x00;                // Current value of formula
        $grbit     = 0x03;                // Option flags
        $chn       = 0x0000;              // Must be zero
    
    
        // 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;
        }
    
        // Strip the '=' or '@' sign at the beginning of the formula string
        if (preg_match("/^=/",$formula)) {
            $formula = preg_replace("/(^=)/","",$formula);
        }
        elseif (preg_match("/^@/",$formula)) {
            $formula = preg_replace("/(^@)/","",$formula);
        }
        else
        {
            // Error handling
            $this->writeString($row, $col, 'Unrecognised character for formula');
            return -1;
        }
    
        // Parse the formula using the parser in Parser.php
        $error = $this->_parser->parse($formula);
        if ($this->isError($error))
        {
            $this->writeString($row, $col, $error->getMessage()); 
            return -1;
        }
    
        $formula = $this->_parser->toReversePolish();
        if ($this->isError($formula))
        {
            $this->writeString($row, $col, $formula->getMessage());
            return -1;
        }
    
        $formlen    = strlen($formula);    // Length of the binary string
        $length     = 0x16 + $formlen;     // Length of the record data
    
        $header    = pack("vv",      $record, $length);
        $data      = pack("vvvdvVv", $row, $col, $xf, $num,
                                     $grbit, $chn, $formlen);
    
        $this->_append($header.$data.$formula);
        return 0;
    }
    
    /**
    * Write a hyperlink.
    * This is comprised of two elements: the visible label and
    * the invisible link. The visible label is the same as the link unless an
    * alternative string is specified. The label is written using the
    * writeString() method. Therefore the 255 characters string limit applies.
    * $string and $format are optional.
    *
    * The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external
    * directory url.
    *
    * Returns  0 : normal termination
    *         -2 : row or column out of range
    *         -3 : long string truncated to 255 chars
    *
    * @access public
    * @param integer $row    Row
    * @param integer $col    Column
    * @param string  $url    URL string
    * @param string  $string Alternative label
    * @param mixed   $format The cell format
    * @return integer
    */
    function writeUrl($row, $col, $url, $string = '', $format = 0)
    {
        // Add start row and col to arg list
        return($this->_writeUrl_range($row, $col, $row, $col, $url, $string, $format));
    }
    
    /**
    * This is the more general form of writeUrl(). It allows a hyperlink to be
    * written to a range of cells. This function also decides the type of hyperlink
    * to be written. These are either, Web (http, ftp, mailto), Internal
    * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
    *
    * @access private
    * @see writeUrl()
    * @param integer $row1   Start row
    * @param integer $col1   Start column
    * @param integer $row2   End row
    * @param integer $col2   End column
    * @param string  $url    URL string
    * @param string  $string Alternative label
    * @param mixed   $format The cell format
    * @return integer
    */
    
    function _writeUrl_range($row1, $col1, $row2, $col2, $url, $string = '', $format = 0)
    {
    
        // Check for internal/external sheet links or default to web link
        if (preg_match('[^internal:]', $url)) {
            return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url, $string, $format));
        }
        if (preg_match('[^external:]', $url)) {
            return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url, $string, $format));
        }
        return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url, $string, $format));
    }
    
    
    /**
    * Used to write http, ftp and mailto hyperlinks.
    * The link type ($options) is 0x03 is the same as absolute dir ref without
    * sheet. However it is differentiated by the $unknown2 data stream.
    *
    * @access private
    * @see writeUrl()
    * @param integer $row1   Start row
    * @param integer $col1   Start column
    * @param integer $row2   End row
    * @param integer $col2   End column
    * @param string  $url    URL string
    * @param string  $str    Alternative label
    * @param mixed   $format The cell format
    * @return integer
    */
    function _writeUrlWeb($row1, $col1, $row2, $col2, $url, $str, $format = 0)
    {
        $record      = 0x01B8;                       // Record identifier
        $length      = 0x00000;                      // Bytes to follow
    
        if ($format == 0) {
            $format = $this->_url_format;
        }
    
        // Write the visible label using the writeString() method.
        if ($str == '') {
            $str = $url;
        }
        $str_error = $this->writeString($row1, $col1, $str, $format);
        if (($str_error == -2) or ($str_error == -3)) {
            return $str_error;
        }
    
        // Pack the undocumented parts of the hyperlink stream
        $unknown1    = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
        $unknown2    = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");
    
        // Pack the option flags
        $options     = pack("V", 0x03);
    
        // Convert URL to a null terminated wchar string
        $url         = join("\0", preg_explode("''", $url, -1, PREG_SPLIT_NO_EMPTY));
        $url         = $url . "\0\0\0";
    
        // Pack the length of the URL
        $url_len     = pack("V", strlen($url));
    
        // Calculate the data length
        $length      = 0x34 + strlen($url);
    
        // Pack the header data
        $header      = pack("vv",   $record, $length);
        $data        = pack("vvvv", $row1, $row2, $col1, $col2);
    
        // Write the packed data
        $this->_append( $header. $data.
                        $unknown1. $options.
                        $unknown2. $url_len. $url);
        return($str_error);
    }
    
    /**
    * Used to write internal reference hyperlinks such as "Sheet1!A1".
    *
    * @access private
    * @see writeUrl()
    * @param integer $row1   Start row
    * @param integer $col1   Start column
    * @param integer $row2   End row
    * @param integer $col2   End column
    * @param string  $url    URL string
    * @param string  $str    Alternative label
    * @param mixed   $format The cell format
    * @return integer
    */
    function _writeUrlInternal($row1, $col1, $row2, $col2, $url, $str, $format = 0)
    {
        $record      = 0x01B8;                       // Record identifier
        $length      = 0x00000;                      // Bytes to follow
    
        if ($format == 0) {
            $format = $this->_url_format;
        }
    
        // Strip URL type
        $url = preg_replace('s[^internal:]', '', $url);
    
        // Write the visible label
        if ($str == '') {
            $str = $url;
        }
        $str_error = $this->writeString($row1, $col1, $str, $format);
        if (($str_error == -2) or ($str_error == -3)) {
            return $str_error;
        }
    
        // Pack the undocumented parts of the hyperlink stream
        $unknown1    = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
    
        // Pack the option flags
        $options     = pack("V", 0x08);
    
        // Convert the URL type and to a null terminated wchar string
        $url         = join("\0", preg_explode("''", $url, -1, PREG_SPLIT_NO_EMPTY));
        $url         = $url . "\0\0\0";
    
        // Pack the length of the URL as chars (not wchars)
        $url_len     = pack("V", floor(strlen($url)/2));
    
        // Calculate the data length
        $length      = 0x24 + strlen($url);
    
        // Pack the header data
        $header      = pack("vv",   $record, $length);
        $data        = pack("vvvv", $row1, $row2, $col1, $col2);
    
        // Write the packed data
        $this->_append($header. $data.
                       $unknown1. $options.
                       $url_len. $url);
        return($str_error);
    }
    
    /**
    * Write links to external directory names such as 'c:\foo.xls',
    * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
    *
    * Note: Excel writes some relative links with the $dir_long string. We ignore
    * these cases for the sake of simpler code.
    *
    * @access private
    * @see writeUrl()
    * @param integer $row1   Start row
    * @param integer $col1   Start column
    * @param integer $row2   End row
    * @param integer $col2   End column
    * @param string  $url    URL string
    * @param string  $str    Alternative label
    * @param mixed   $format The cell format
    * @return integer
    */
    function _writeUrlExternal($row1, $col1, $row2, $col2, $url, $str, $format = 0)
    {
        // Network drives are different. We will handle them separately
        // MS/Novell network drives and shares start with \\
        if (preg_match('[^external:\\\\]', $url)) {
            return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format));
        }
    
        $record      = 0x01B8;                       // Record identifier
        $length      = 0x00000;                      // Bytes to follow
    
        if ($format == 0) {
            $format = $this->_url_format;
        }
    
        // Strip URL type and change Unix dir separator to Dos style (if needed)
        //
        $url = preg_replace('[^external:]', '', $url);
        $url = preg_replace('[/]', "\\", $url);
    
        // Write the visible label
        if ($str == '') {
            $str = preg_replace('[\#]', ' - ', $url);
        }
        $str_error = $this->writeString($row1, $col1, $str, $format);
        if (($str_error == -2) or ($str_error == -3)) {
            return $str_error;
        }
    
        // Determine if the link is relative or absolute:
        //   relative if link contains no dir separator, "somefile.xls"
        //   relative if link starts with up-dir, "..\..\somefile.xls"
        //   otherwise, absolute
        
        $absolute    = 0x02; // Bit mask
        if (!preg_match('[\\]', $url)) {
            $absolute    = 0x00;
        }
        if (preg_match('[^\.\.\\]', $url)) {
            $absolute    = 0x00;
        }
    
        // Determine if the link contains a sheet reference and change some of the
        // parameters accordingly.
        // Split the dir name and sheet name (if it exists)
        list($dir_long , $sheet) = explode('/\#/', $url);
        $link_type               = 0x01 | $absolute;
    
        if (isset($sheet)) {
            $link_type |= 0x08;
            $sheet_len  = pack("V", strlen($sheet) + 0x01);
            $sheet      = join("\0", explode('', $sheet));
            $sheet     .= "\0\0\0";
        }
        else {
            $sheet_len   = '';
            $sheet       = '';
        }
    
        // Pack the link type
        $link_type   = pack("V", $link_type);
    
        // Calculate the up-level dir count e.g.. (..\..\..\ == 3)
        $up_count    = preg_match_all("/\.\.\\/", $dir_long, $useless);
        $up_count    = pack("v", $up_count);
    
        // Store the short dos dir name (null terminated)
        $dir_short   = preg_replace('/\.\.\\/', '', $dir_long) . "\0";
    
        // Store the long dir name as a wchar string (non-null terminated)
        $dir_long       = join("\0", explode('', $dir_long));
        $dir_long       = $dir_long . "\0";
    
        // Pack the lengths of the dir strings
        $dir_short_len = pack("V", strlen($dir_short)      );
        $dir_long_len  = pack("V", strlen($dir_long)       );
        $stream_len    = pack("V", strlen($dir_long) + 0x06);
    
        // Pack the undocumented parts of the hyperlink stream
        $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000'       );
        $unknown2 = pack("H*",'0303000000000000C000000000000046'               );
        $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000');
        $unknown4 = pack("v",  0x03                                            );
    
        // Pack the main data stream
        $data        = pack("vvvv", $row1, $row2, $col1, $col2) .
                          $unknown1     .
                          $link_type    .
                          $unknown2     .
                          $up_count     .
                          $dir_short_len.
                          $dir_short    .
                          $unknown3     .
                          $stream_len   .
                          $dir_long_len .
                          $unknown4     .
                          $dir_long     .
                          $sheet_len    .
                          $sheet        ;
    
        // Pack the header data
        $length   = strlen($data);
        $header   = pack("vv", $record, $length);
    
        // Write the packed data
        $this->_append($header. $data);
        return($str_error);
    }
    
    
    /**
    * This method is used to set the height and format for a row.
    *
    * @access public
    * @param integer $row    The row to set
    * @param integer $height Height we are giving to the row. 
    *                        Use NULL to set XF without setting height
    * @param mixed   $format XF format we are giving to the row
    */
    function setRow($row, $height, $format = 0)
    {
        $record      = 0x0208;               // Record identifier
        $length      = 0x0010;               // Number of bytes to follow
    
        $colMic      = 0x0000;               // First defined column
        $colMac      = 0x0000;               // Last defined column
        $irwMac      = 0x0000;               // Used by Excel to optimise loading
        $reserved    = 0x0000;               // Reserved
        $grbit       = 0x01C0;               // Option flags. (monkey) see $1 do
        $ixfe        = $this->_XF($format);  // XF index
    
        // Use setRow($row, NULL, $XF) to set XF format without setting height
        if ($height != NULL) {
            $miyRw = $height * 20;  // row height
        }
        else {
            $miyRw = 0xff;          // default row height is 256
        }
    
        $header   = pack("vv",       $record, $length);
        $data     = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw,
                                     $irwMac,$reserved, $grbit, $ixfe);
        $this->_append($header.$data);
    }
    
    /**
    * Writes Excel DIMENSIONS to define the area in which there is data.
    *
    * @access private
    */
    function storeDimensions()
    {
        $record    = 0x0000;               // Record identifier
        $length    = 0x000A;               // Number of bytes to follow
        $row_min   = $this->_dim_rowmin;   // First row
        $row_max   = $this->_dim_rowmax;   // Last row plus 1
        $col_min   = $this->_dim_colmin;   // First column
        $col_max   = $this->_dim_colmax;   // Last column plus 1
        $reserved  = 0x0000;               // Reserved by Excel
    
        $header    = pack("vv",    $record, $length);
        $data      = pack("vvvvv", $row_min, $row_max,
                                   $col_min, $col_max, $reserved);
        $this->_prepend($header.$data);
    }
    
    /**
    * Write BIFF record Window2.
    *
    * @access private
    */
    function _storeWindow2()
    {
        $record         = 0x023E;     // Record identifier
        $length         = 0x000A;     // Number of bytes to follow
    
        $grbit          = 0x00B6;     // Option flags
        $rwTop          = 0x0000;     // Top row visible in window
        $colLeft        = 0x0000;     // Leftmost column visible in window
        $rgbHdr         = 0x00000000; // Row/column heading and gridline color
    
        // The options flags that comprise $grbit
        $fDspFmla       = 0;                     // 0 - bit
        $fDspGrid       = 1;                     // 1
        $fDspRwCol      = 1;                     // 2
        $fFrozen        = $this->_frozen;        // 3
        $fDspZeros      = 1;                     // 4
        $fDefaultHdr    = 1;                     // 5
        $fArabic        = 0;                     // 6
        $fDspGuts       = 1;                     // 7
        $fFrozenNoSplit = 0;                     // 0 - bit
        $fSelected      = $this->selected;       // 1
        $fPaged         = 1;                     // 2
    
        $grbit             = $fDspFmla;
        $grbit            |= $fDspGrid       << 1;
        $grbit            |= $fDspRwCol      << 2;
        $grbit            |= $fFrozen        << 3;
        $grbit            |= $fDspZeros      << 4;
        $grbit            |= $fDefaultHdr    << 5;
        $grbit            |= $fArabic        << 6;
        $grbit            |= $fDspGuts       << 7;
        $grbit            |= $fFrozenNoSplit << 8;
        $grbit            |= $fSelected      << 9;
        $grbit            |= $fPaged         << 10;
    
        $header  = pack("vv",   $record, $length);
        $data    = pack("vvvV", $grbit, $rwTop, $colLeft, $rgbHdr);
        $this->_append($header.$data);
    }
    
    /**
    * Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
    *
    * @access private
    */
    function _storeDefcol()
    {
        $record   = 0x0055;      // Record identifier
        $length   = 0x0002;      // Number of bytes to follow
        $colwidth = 0x0008;      // Default column width
    
        $header   = pack("vv", $record, $length);
        $data     = pack("v",  $colwidth);
        $this->_prepend($header.$data);
    }
    
    /**
    * Write BIFF record COLINFO to define column widths
    *
    * Note: The SDK says the record length is 0x0B but Excel writes a 0x0C
    * length record.
    *
    * @access private
    * @param array $col_array This is the only parameter received and is composed of the following:
    *                0 => First formatted column,
    *                1 => Last formatted column,
    *                2 => Col width (8.43 is Excel default),
    *                3 => The optional XF format of the column,
    *                4 => Option flags.
    */
    function _storeColinfo($col_array)
    {
        if (isset($col_array[0])) {
            $colFirst = $col_array[0];
        }
        if (isset($col_array[1])) {
            $colLast = $col_array[1];
        }
        if (isset($col_array[2])) {
            $coldx = $col_array[2];
        }
        else {
            $coldx = 8.43;
        }
        if (isset($col_array[3])) {
            $format = $col_array[3];
        }
        else {
            $format = 0;
        }
        if (isset($col_array[4])) {
            $grbit = $col_array[4];
        }
        else {
            $grbit = 0;
        }
        $record   = 0x007D;          // Record identifier
        $length   = 0x000B;          // Number of bytes to follow
    
        $coldx   += 0.72;            // Fudge. Excel subtracts 0.72 !?
        $coldx   *= 256;             // Convert to units of 1/256 of a char
    
        $ixfe     = $this->_XF($format);
        $reserved = 0x00;            // Reserved
    
        $header   = pack("vv",     $record, $length);
        $data     = pack("vvvvvC", $colFirst, $colLast, $coldx,
                                   $ixfe, $grbit, $reserved);
        $this->_prepend($header.$data);
    }
    
    /**
    * Write BIFF record SELECTION.
    *
    * @access private
    * @param array $array array containing ($rwFirst,$colFirst,$rwLast,$colLast)
    * @see setSelection()
    */
    function _storeSelection($array)
    {
        list($rwFirst,$colFirst,$rwLast,$colLast) = $array;
        $record   = 0x001D;                  // Record identifier
        $length   = 0x000F;                  // Number of bytes to follow
    
        $pnn      = $this->_active_pane;     // Pane position
        $rwAct    = $rwFirst;                // Active row
        $colAct   = $colFirst;               // Active column
        $irefAct  = 0;                       // Active cell ref
        $cref     = 1;                       // Number of refs
    
        if (!isset($rwLast)) {
            $rwLast   = $rwFirst;       // Last  row in reference
        }
        if (!isset($colLast)) {
            $colLast  = $colFirst;      // Last  col in reference
        }
    
        // Swap last row/col for first row/col as necessary
        if ($rwFirst > $rwLast)
        {
            list($rwFirst, $rwLast) = array($rwLast, $rwFirst);
        }
    
        if ($colFirst > $colLast)
        {
            list($colFirst, $colLast) = array($colLast, $colFirst);
        }
    
        $header   = pack("vv",         $record, $length);
        $data     = pack("CvvvvvvCC",  $pnn, $rwAct, $colAct,
                                       $irefAct, $cref,
                                       $rwFirst, $rwLast,
                                       $colFirst, $colLast);
        $this->_append($header.$data);
    }
    
    
    /**
    * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
    * references in a worksheet.
    *
    * Excel only stores references to external sheets that are used in formulas.
    * For simplicity we store references to all the sheets in the workbook
    * regardless of whether they are used or not. This reduces the overall
    * complexity and eliminates the need for a two way dialogue between the formula
    * parser the worksheet objects.
    *
    * @access private
    * @param integer $count The number of external sheet references in this worksheet
    */
    function _storeExterncount($count)
    {
        $record   = 0x0016;          // Record identifier
        $length   = 0x0002;          // Number of bytes to follow
    
        $header   = pack("vv", $record, $length);
        $data     = pack("v",  $count);
        $this->_prepend($header.$data);
    }
    
    /**
    * Writes the Excel BIFF EXTERNSHEET record. These references are used by
    * formulas. A formula references a sheet name via an index. Since we store a
    * reference to all of the external worksheets the EXTERNSHEET index is the same
    * as the worksheet index.
    *
    * @access private
    * @param string $sheetname The name of a external worksheet
    */
    function _storeExternsheet($sheetname)
    {
        $record    = 0x0017;         // Record identifier
    
        // References to the current sheet are encoded differently to references to
        // external sheets.
        //
        if ($this->name == $sheetname) {
            $sheetname = '';
            $length    = 0x02;  // The following 2 bytes
            $cch       = 1;     // The following byte
            $rgch      = 0x02;  // Self reference
        }
        else {
            $length    = 0x02 + strlen($sheetname);
            $cch       = strlen($sheetname);
            $rgch      = 0x03;  // Reference to a sheet in the current workbook
        }
    
        $header     = pack("vv",  $record, $length);
        $data       = pack("CC", $cch, $rgch);
        $this->_prepend($header.$data.$sheetname);
    }
    
    /**
    * Writes the Excel BIFF PANE record.
    * The panes can either be frozen or thawed (unfrozen).
    * Frozen panes are specified in terms of an integer number of rows and columns.
    * Thawed panes are specified in terms of Excel's units for rows and columns.
    *
    * @access private
    * @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 _storePanes($panes)
    {
        $y       = $panes[0];
        $x       = $panes[1];
        $rwTop   = $panes[2];
        $colLeft = $panes[3];
        if (count($panes) > 4) { // if Active pane was received
            $pnnAct = $panes[4];
        }
        else {
            $pnnAct = NULL;
        }
        $record  = 0x0041;       // Record identifier
        $length  = 0x000A;       // Number of bytes to follow
    
        // Code specific to frozen or thawed panes.
        if ($this->_frozen)
        {
            // Set default values for $rwTop and $colLeft
            if (!isset($rwTop)) {
                $rwTop   = $y;
            }
            if (!isset($colLeft)) {
                $colLeft = $x;
            }
        }
        else
        {
            // Set default values for $rwTop and $colLeft
            if (!isset($rwTop)) {
                $rwTop   = 0;
            }
            if (!isset($colLeft)) {
                $colLeft = 0;
            }
    
            // Convert Excel's row and column units to the internal units.
            // The default row height is 12.75
            // The default column width is 8.43
            // The following slope and intersection values were interpolated.
            //
            $y = 20*$y      + 255;
            $x = 113.879*$x + 390;
        }
    
    
        // Determine which pane should be active. There is also the undocumented
        // option to override this should it be necessary: may be removed later.
        //
        if (!isset($pnnAct))
        {
            if ($x != 0 and $y != 0)
                $pnnAct = 0; // Bottom right
            if ($x != 0 and $y == 0)
                $pnnAct = 1; // Top right
            if ($x == 0 and $y != 0)
                $pnnAct = 2; // Bottom left
            if ($x == 0 and $y == 0)
                $pnnAct = 3; // Top left
        }
    
        $this->_active_pane = $pnnAct; // Used in _storeSelection
    
        $header     = pack("vv",    $record, $length);
        $data       = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct);
        $this->_append($header.$data);
    }
    
    /**
    * Store the page setup SETUP BIFF record.
    *
    * @access private
    */
    function _storeSetup()
    {
        $record       = 0x00A1;                  // Record identifier
        $length       = 0x0022;                  // Number of bytes to follow
    
        $iPaperSize   = $this->_paper_size;    // Paper size
        $iScale       = $this->_print_scale;   // Print scaling factor
        $iPageStart   = 0x01;                 // Starting page number
        $iFitWidth    = $this->_fit_width;    // Fit to number of pages wide
        $iFitHeight   = $this->_fit_height;   // Fit to number of pages high
        $grbit        = 0x00;                 // Option flags
        $iRes         = 0x0258;               // Print resolution
        $iVRes        = 0x0258;               // Vertical print resolution
        $numHdr       = $this->_margin_head;  // Header Margin
        $numFtr       = $this->_margin_foot;   // Footer Margin
        $iCopies      = 0x01;                 // Number of copies
    
        $fLeftToRight = 0x0;                     // Print over then down
        $fLandscape   = $this->_orientation;     // Page orientation
        $fNoPls       = 0x0;                     // Setup not read from printer
        $fNoColor     = 0x0;                     // Print black and white
        $fDraft       = 0x0;                     // Print draft quality
        $fNotes       = 0x0;                     // Print notes
        $fNoOrient    = 0x0;                     // Orientation not set
        $fUsePage     = 0x0;                     // Use custom starting page
    
        $grbit           = $fLeftToRight;
        $grbit          |= $fLandscape    << 1;
        $grbit          |= $fNoPls        << 2;
        $grbit          |= $fNoColor      << 3;
        $grbit          |= $fDraft        << 4;
        $grbit          |= $fNotes        << 5;
        $grbit          |= $fNoOrient     << 6;
        $grbit          |= $fUsePage      << 7;
    
        $numHdr = pack("d", $numHdr);
        $numFtr = pack("d", $numFtr);
        if ($this->_byte_order) // if it's Big Endian
        {
            $numHdr = strrev($numHdr);
            $numFtr = strrev($numFtr);
        }
    
        $header = pack("vv", $record, $length);
        $data1  = pack("vvvvvvvv", $iPaperSize,
                                   $iScale,
                                   $iPageStart,
                                   $iFitWidth,
                                   $iFitHeight,
                                   $grbit,
                                   $iRes,
                                   $iVRes);
        $data2  = $numHdr.$numFtr;
        $data3  = pack("v", $iCopies);
        $this->_prepend($header.$data1.$data2.$data3);
    }
    
    /**
    * Store the header caption BIFF record.
    *
    * @access private
    */
    function _storeHeader()
    {
        $record  = 0x0014;               // Record identifier
    
        $str     = $this->_header;       // header string
        $cch     = strlen($str);         // Length of header string
        $length  = 1 + $cch;             // Bytes to follow
    
        $header    = pack("vv", $record, $length);
        $data      = pack("C",  $cch);
    
        $this->_append($header.$data.$str);
    }
    
    /**
    * Store the footer caption BIFF record.
    *
    * @access private
    */
    function _storeFooter()
    {
        $record  = 0x0015;               // Record identifier
    
        $str     = $this->_footer;       // Footer string
        $cch     = strlen($str);         // Length of footer string
        $length  = 1 + $cch;             // Bytes to follow
    
        $header    = pack("vv", $record, $length);
        $data      = pack("C",  $cch);
    
        $this->_append($header.$data.$str);
    }
    
    /**
    * Store the horizontal centering HCENTER BIFF record.
    *
    * @access private
    */
    function _storeHcenter()
    {
        $record   = 0x0083;              // Record identifier
        $length   = 0x0002;              // Bytes to follow
    
        $fHCenter = $this->_hcenter;     // Horizontal centering
    
        $header    = pack("vv", $record, $length);
        $data      = pack("v",  $fHCenter);
    
        $this->_append($header.$data);
    }
    
    /**
    * Store the vertical centering VCENTER BIFF record.
    *
    * @access private
    */
    function _storeVcenter()
    {
        $record   = 0x0084;              // Record identifier
        $length   = 0x0002;              // Bytes to follow
    
        $fVCenter = $this->_vcenter;     // Horizontal centering
    
        $header    = pack("vv", $record, $length);
        $data      = pack("v",  $fVCenter);
        $this->_append($header.$data);
    }
    
    /**
    * Store the LEFTMARGIN BIFF record.
    *
    * @access private
    */
    function _storeMarginLeft()
    {
        $record  = 0x0026;                   // Record identifier
        $length  = 0x0008;                   // Bytes to follow
    
        $margin  = $this->_margin_left;       // Margin in inches
    
        $header    = pack("vv",  $record, $length);
        $data      = pack("d",   $margin);
        if ($this->_byte_order) // if it's Big Endian
        { 
            $data = strrev($data);
        }
    
        $this->_append($header.$data);
    }
    
    /**
    * Store the RIGHTMARGIN BIFF record.
    *
    * @access private
    */
    function _storeMarginRight()
    {
        $record  = 0x0027;                   // Record identifier
        $length  = 0x0008;                   // Bytes to follow
    
        $margin  = $this->_margin_right;      // Margin in inches
    
        $header    = pack("vv",  $record, $length);
        $data      = pack("d",   $margin);
        if ($this->_byte_order) // if it's Big Endian
        { 
            $data = strrev($data);
        }
    
        $this->_append($header.$data);
    }
    
    /**
    * Store the TOPMARGIN BIFF record.
    *
    * @access private
    */
    function _storeMarginTop()
    {
        $record  = 0x0028;                   // Record identifier
        $length  = 0x0008;                   // Bytes to follow
    
        $margin  = $this->_margin_top;        // Margin in inches
    
        $header    = pack("vv",  $record, $length);
        $data      = pack("d",   $margin);
        if ($this->_byte_order) // if it's Big Endian
        { 
            $data = strrev($data);
        }
    
        $this->_append($header.$data);
    }
    
    /**
    * Store the BOTTOMMARGIN BIFF record.
    *
    * @access private
    */
    function _storeMarginBottom()
    {
        $record  = 0x0029;                   // Record identifier
        $length  = 0x0008;                   // Bytes to follow
    
        $margin  = $this->_margin_bottom;     // Margin in inches
    
        $header    = pack("vv",  $record, $length);
        $data      = pack("d",   $margin);
        if ($this->_byte_order) // if it's Big Endian
        { 
            $data = strrev($data);
        }
    
        $this->_append($header.$data);
    }
 
    /**
    * Merges the area given by its arguments.
    * This is an Excel97/2000 method. It is required to perform more complicated
    * merging than the normal setAlign('merge').
    *
    * @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 mergeCells($first_row, $first_col, $last_row, $last_col)
    {
        $record  = 0x00E5;                   // Record identifier
        $length  = 0x000A;                   // Bytes to follow
        $cref     = 1;                       // Number of refs
 
        // Swap last row/col for first row/col as necessary
        if ($first_row > $last_row) {
            list($first_row, $last_row) = array($last_row, $first_row);
        }
    
        if ($first_col > $last_col) {
            list($first_col, $last_col) = array($last_col, $first_col);
        }
    
        $header   = pack("vv",    $record, $length);
        $data     = pack("vvvvv", $cref, $first_row, $last_row,
                                  $first_col, $last_col);
    
        $this->_append($header.$data);
    }
 
    /**
    * Write the PRINTHEADERS BIFF record.
    *
    * @access private
    */
    function _storePrintHeaders()
    {
        $record      = 0x002a;                   // Record identifier
        $length      = 0x0002;                   // Bytes to follow
    
        $fPrintRwCol = $this->_print_headers;     // Boolean flag
    
        $header      = pack("vv", $record, $length);
        $data        = pack("v", $fPrintRwCol);
        $this->_prepend($header.$data);
    }
    
    /**
    * Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the
    * GRIDSET record.
    *
    * @access private
    */
    function _storePrintGridlines()
    {
        $record      = 0x002b;                    // Record identifier
        $length      = 0x0002;                    // Bytes to follow
    
        $fPrintGrid  = $this->_print_gridlines;    // Boolean flag
    
        $header      = pack("vv", $record, $length);
        $data        = pack("v", $fPrintGrid);
        $this->_prepend($header.$data);
    }
    
    /**
    * Write the GRIDSET BIFF record. Must be used in conjunction with the
    * PRINTGRIDLINES record.
    *
    * @access private
    */
    function _storeGridset()
    {
        $record      = 0x0082;                        // Record identifier
        $length      = 0x0002;                        // Bytes to follow
    
        $fGridSet    = !($this->_print_gridlines);     // Boolean flag
    
        $header      = pack("vv",  $record, $length);
        $data        = pack("v",   $fGridSet);
        $this->_prepend($header.$data);
    }
    
    /**
    * Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction
    * with the SETUP record.
    *
    * @access private
    */
    function _storeWsbool()
    {
        $record      = 0x0081;   // Record identifier
        $length      = 0x0002;   // Bytes to follow
    
        // The only option that is of interest is the flag for fit to page. So we
        // set all the options in one go.
        //
        if ($this->_fit_page) {
            $grbit = 0x05c1;
        }
        else {
            $grbit = 0x04c1;
        }
    
        $header      = pack("vv", $record, $length);
        $data        = pack("v",  $grbit);
        $this->_prepend($header.$data);
    }
    
    
    /**
    * Write the HORIZONTALPAGEBREAKS BIFF record.
    *
    * @access private
    */
    function _storeHbreak()
    {
        // Return if the user hasn't specified pagebreaks
        if (empty($this->_hbreaks)) {
            return;
        }
    
        // Sort and filter array of page breaks
        $breaks = $this->_hbreaks;
        sort($breaks,SORT_NUMERIC);
        if ($breaks[0] == 0) { // don't use first break if it's 0
            array_shift($breaks);
        }
    
        $record  = 0x001b;               // Record identifier
        $cbrk    = count($breaks);       // Number of page breaks
        $length  = ($cbrk + 1) * 2;      // Bytes to follow
    
        $header  = pack("vv", $record, $length);
        $data    = pack("v",  $cbrk);
    
        // Append each page break
        foreach($breaks as $break) {
            $data .= pack("v", $break);
        }
    
        $this->_prepend($header.$data);
    }
    
    
    /**
    * Write the VERTICALPAGEBREAKS BIFF record.
    *
    * @access private
    */
    function _storeVbreak()
    {
        // Return if the user hasn't specified pagebreaks
        if (empty($this->_vbreaks)) {
            return;
        }
    
        // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
        // It is slightly higher in Excel 97/200, approx. 1026
        $breaks = array_slice($this->_vbreaks,0,1000);
    
        // Sort and filter array of page breaks
        sort($breaks,SORT_NUMERIC);
        if ($breaks[0] == 0) { // don't use first break if it's 0
            array_shift($breaks);
        }
    
        $record  = 0x001a;               // Record identifier
        $cbrk    = count($breaks);       // Number of page breaks
        $length  = ($cbrk + 1) * 2;      // Bytes to follow
    
        $header  = pack("vv",  $record, $length);
        $data    = pack("v",   $cbrk);
    
        // Append each page break
        foreach ($breaks as $break) {
            $data .= pack("v", $break);
        }
    
        $this->_prepend($header.$data);
    }
    
    /**
    * Set the Biff PROTECT record to indicate that the worksheet is protected.
    *
    * @access private
    */
    function _storeProtect()
    {
        // Exit unless sheet protection has been specified
        if ($this->_protect == 0) {
            return;
        }
    
        $record      = 0x0012;             // Record identifier
        $length      = 0x0002;             // Bytes to follow
    
        $fLock       = $this->_protect;    // Worksheet is protected
    
        $header      = pack("vv", $record, $length);
        $data        = pack("v",  $fLock);
    
        $this->_prepend($header.$data);
    }
    
    /**
    * Write the worksheet PASSWORD record.
    *
    * @access private
    */
    function _storePassword()
    {
        // Exit unless sheet protection and password have been specified
        if (($this->_protect == 0) or (!isset($this->_password))) {
            return;
        }
    
        $record      = 0x0013;               // Record identifier
        $length      = 0x0002;               // Bytes to follow
    
        $wPassword   = $this->_password;     // Encoded password
    
        $header      = pack("vv", $record, $length);
        $data        = pack("v",  $wPassword);
    
        $this->_prepend($header.$data);
    }
    
 
    /**
    * Insert a 24bit bitmap image in a worksheet.
    *
    * @access public
    * @param integer $row     The row we are going to insert the bitmap into
    * @param integer $col     The column we are going to insert the bitmap into
    * @param string  $bitmap  The bitmap filename
    * @param integer $x       The horizontal position (offset) of the image inside the cell.
    * @param integer $y       The vertical position (offset) of the image inside the cell.
    * @param integer $scale_x The horizontal scale
    * @param integer $scale_y The vertical scale
    */
    function insertBitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1)
    {
        $bitmap_array = $this->_processBitmap($bitmap);
        if ($this->isError($bitmap_array))
        {
            $this->writeString($row, $col, $bitmap_array->getMessage());
            return;
        }
        list($width, $height, $size, $data) = $bitmap_array; //$this->_processBitmap($bitmap);
    
        // Scale the frame of the image.
        $width  *= $scale_x;
        $height *= $scale_y;
    
        // Calculate the vertices of the image and write the OBJ record
        $this->_positionImage($col, $row, $x, $y, $width, $height);
    
        // Write the IMDATA record to store the bitmap data
        $record      = 0x007f;
        $length      = 8 + $size;
        $cf          = 0x09;
        $env         = 0x01;
        $lcb         = $size;
    
        $header      = pack("vvvvV", $record, $length, $cf, $env, $lcb);
        $this->_append($header.$data);
    }
    
    /**
    * Calculate the vertices that define the position of the image as required by
    * the OBJ record.
    *
    *         +------------+------------+
    *         |     A      |      B     |
    *   +-----+------------+------------+
    *   |     |(x1,y1)     |            |
    *   |  1  |(A1)._______|______      |
    *   |     |    |              |     |
    *   |     |    |              |     |
    *   +-----+----|    BITMAP    |-----+
    *   |     |    |              |     |
    *   |  2  |    |______________.     |
    *   |     |            |        (B2)|
    *   |     |            |     (x2,y2)|
    *   +---- +------------+------------+
    *
    * Example of a bitmap that covers some of the area from cell A1 to cell B2.
    *
    * Based on the width and height of the bitmap we need to calculate 8 vars:
    *     $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
    * The width and height of the cells are also variable and have to be taken into
    * account.
    * The values of $col_start and $row_start are passed in from the calling
    * function. The values of $col_end and $row_end are calculated by subtracting
    * the width and height of the bitmap from the width and height of the
    * underlying cells.
    * The vertices are expressed as a percentage of the underlying cell width as
    * follows (rhs values are in pixels):
    *
    *       x1 = X / W *1024
    *       y1 = Y / H *256
    *       x2 = (X-1) / W *1024
    *       y2 = (Y-1) / H *256
    *
    *       Where:  X is distance from the left side of the underlying cell
    *               Y is distance from the top of the underlying cell
    *               W is the width of the cell
    *               H is the height of the cell
    *
    * @access private
    * @note  the SDK incorrectly states that the height should be expressed as a
    *        percentage of 1024.
    * @param integer $col_start Col containing upper left corner of object
    * @param integer $row_start Row containing top left corner of object
    * @param integer $x1        Distance to left side of object
    * @param integer $y1        Distance to top of object
    * @param integer $width     Width of image frame
    * @param integer $height    Height of image frame
    */
    function _positionImage($col_start, $row_start, $x1, $y1, $width, $height)
    {
        // Initialise end cell to the same as the start cell
        $col_end    = $col_start;  // Col containing lower right corner of object
        $row_end    = $row_start;  // Row containing bottom right corner of object
    
        // Zero the specified offset if greater than the cell dimensions
        if ($x1 >= $this->_sizeCol($col_start))
        {
            $x1 = 0;
        }
        if ($y1 >= $this->_sizeRow($row_start))
        {
            $y1 = 0;
        }
    
        $width      = $width  + $x1 -1;
        $height     = $height + $y1 -1;
    
        // Subtract the underlying cell widths to find the end cell of the image
        while ($width >= $this->_sizeCol($col_end)) {
            $width -= $this->_sizeCol($col_end);
            $col_end++;
        }
    
        // Subtract the underlying cell heights to find the end cell of the image
        while ($height >= $this->_sizeRow($row_end)) {
            $height -= $this->_sizeRow($row_end);
            $row_end++;
        }
    
        // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
        // with zero eight or width.
        //
        if ($this->_sizeCol($col_start) == 0)
            return;
        if ($this->_sizeCol($col_end)   == 0)
            return;
        if ($this->_sizeRow($row_start) == 0)
            return;
        if ($this->_sizeRow($row_end)   == 0)
            return;
    
        // Convert the pixel values to the percentage value expected by Excel
        $x1 = $x1     / $this->_sizeCol($col_start)   * 1024;
        $y1 = $y1     / $this->_sizeRow($row_start)   *  256;
        $x2 = $width  / $this->_sizeCol($col_end)     * 1024; // Distance to right side of object
        $y2 = $height / $this->_sizeRow($row_end)     *  256; // Distance to bottom of object
    
        $this->_storeObjPicture( $col_start, $x1,
                                  $row_start, $y1,
                                  $col_end, $x2,
                                  $row_end, $y2
                                );
    }
    
    /**
    * Convert the width of a cell from user's units to pixels. By interpolation
    * the relationship is: y = 7x +5. If the width hasn't been set by the user we
    * use the default value. If the col is hidden we use a value of zero.
    *
    * @access private
    * @param integer $col The column 
    * @return integer The width in pixels
    */
    function _sizeCol($col)
    {
        // Look up the cell value to see if it has been changed
        if (isset($this->col_sizes[$col])) {
            if ($this->col_sizes[$col] == 0) {
                return(0);
            }
            else {
                return(floor(7 * $this->col_sizes[$col] + 5));
            }
        }
        else {
            return(64);
        }
    }
    
    /**
    * Convert the height of a cell from user's units to pixels. By interpolation
    * the relationship is: y = 4/3x. If the height hasn't been set by the user we
    * use the default value. If the row is hidden we use a value of zero. (Not
    * possible to hide row yet).
    *
    * @access private
    * @param integer $row The row
    * @return integer The width in pixels
    */
    function _sizeRow($row)
    {
        // Look up the cell value to see if it has been changed
        if (isset($this->row_sizes[$row])) {
            if ($this->row_sizes[$row] == 0) {
                return(0);
            }
            else {
                return(floor(4/3 * $this->row_sizes[$row]));
            }
        }
        else {
            return(17);
        }
    }
    
    /**
    * Store the OBJ record that precedes an IMDATA record. This could be generalise
    * to support other Excel objects.
    *
    * @access private
    * @param integer $colL Column containing upper left corner of object
    * @param integer $dxL  Distance from left side of cell
    * @param integer $rwT  Row containing top left corner of object
    * @param integer $dyT  Distance from top of cell
    * @param integer $colR Column containing lower right corner of object
    * @param integer $dxR  Distance from right of cell
    * @param integer $rwB  Row containing bottom right corner of object
    * @param integer $dyB  Distance from bottom of cell
    */
    function _storeObjPicture($colL,$dxL,$rwT,$dyT,$colR,$dxR,$rwB,$dyB)
    {
        $record      = 0x005d;   // Record identifier
        $length      = 0x003c;   // Bytes to follow
    
        $cObj        = 0x0001;   // Count of objects in file (set to 1)
        $OT          = 0x0008;   // Object type. 8 = Picture
        $id          = 0x0001;   // Object ID
        $grbit       = 0x0614;   // Option flags
    
        $cbMacro     = 0x0000;   // Length of FMLA structure
        $Reserved1   = 0x0000;   // Reserved
        $Reserved2   = 0x0000;   // Reserved
    
        $icvBack     = 0x09;     // Background colour
        $icvFore     = 0x09;     // Foreground colour
        $fls         = 0x00;     // Fill pattern
        $fAuto       = 0x00;     // Automatic fill
        $icv         = 0x08;     // Line colour
        $lns         = 0xff;     // Line style
        $lnw         = 0x01;     // Line weight
        $fAutoB      = 0x00;     // Automatic border
        $frs         = 0x0000;   // Frame style
        $cf          = 0x0009;   // Image format, 9 = bitmap
        $Reserved3   = 0x0000;   // Reserved
        $cbPictFmla  = 0x0000;   // Length of FMLA structure
        $Reserved4   = 0x0000;   // Reserved
        $grbit2      = 0x0001;   // Option flags
        $Reserved5   = 0x0000;   // Reserved
    
    
        $header      = pack("vv", $record, $length);
        $data        = pack("V", $cObj);
        $data       .= pack("v", $OT);
        $data       .= pack("v", $id);
        $data       .= pack("v", $grbit);
        $data       .= pack("v", $colL);
        $data       .= pack("v", $dxL);
        $data       .= pack("v", $rwT);
        $data       .= pack("v", $dyT);
        $data       .= pack("v", $colR);
        $data       .= pack("v", $dxR);
        $data       .= pack("v", $rwB);
        $data       .= pack("v", $dyB);
        $data       .= pack("v", $cbMacro);
        $data       .= pack("V", $Reserved1);
        $data       .= pack("v", $Reserved2);
        $data       .= pack("C", $icvBack);
        $data       .= pack("C", $icvFore);
        $data       .= pack("C", $fls);
        $data       .= pack("C", $fAuto);
        $data       .= pack("C", $icv);
        $data       .= pack("C", $lns);
        $data       .= pack("C", $lnw);
        $data       .= pack("C", $fAutoB);
        $data       .= pack("v", $frs);
        $data       .= pack("V", $cf);
        $data       .= pack("v", $Reserved3);
        $data       .= pack("v", $cbPictFmla);
        $data       .= pack("v", $Reserved4);
        $data       .= pack("v", $grbit2);
        $data       .= pack("V", $Reserved5);
    
        $this->_append($header.$data);
    }
    
    /**
    * Convert a 24 bit bitmap into the modified internal format used by Windows.
    * This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the
    * MSDN library.
    *
    * @access private
    * @param string $bitmap The bitmap to process
    * @return array Array with data and properties of the bitmap
    */
    function _processBitmap($bitmap)
    {
        // Open file.
        $bmp_fd = @fopen($bitmap,"rb");
        if (!$bmp_fd) {
            return($this->raiseError("Couldn't import $bitmap"));
        }
            
        // Slurp the file into a string.
        $data = fread($bmp_fd, filesize($bitmap));
    
        // Check that the file is big enough to be a bitmap.
        if (strlen($data) <= 0x36) {
            return($this->raiseError("$bitmap doesn't contain enough data.\n"));
        }
    
        // The first 2 bytes are used to identify the bitmap.
        $identity = unpack("A2", $data);
        if ($identity[''] != "BM") {
            return($this->raiseError("$bitmap doesn't appear to be a valid bitmap image.\n"));
        }
    
        // Remove bitmap data: ID.
        $data = substr($data, 2);
    
        // Read and remove the bitmap size. This is more reliable than reading
        // the data size at offset 0x22.
        //
        $size_array   = unpack("V", substr($data, 0, 4));
        $size   = $size_array[''];
        $data   = substr($data, 4);
        $size  -= 0x36; // Subtract size of bitmap header.
        $size  += 0x0C; // Add size of BIFF header.
    
        // Remove bitmap data: reserved, offset, header length.
        $data = substr($data, 12);
    
        // Read and remove the bitmap width and height. Verify the sizes.
        $width_and_height = unpack("V2", substr($data, 0, 8));
        $width  = $width_and_height[1];
        $height = $width_and_height[2];
        $data   = substr($data, 8);
        if ($width > 0xFFFF) { 
            return($this->raiseError("$bitmap: largest image width supported is 65k.\n"));
        }
        if ($height > 0xFFFF) { 
            return($this->raiseError("$bitmap: largest image height supported is 65k.\n"));
        }
    
        // Read and remove the bitmap planes and bpp data. Verify them.
        $planes_and_bitcount = unpack("v2", substr($data, 0, 4));
        $data = substr($data, 4);
        if ($planes_and_bitcount[2] != 24) { // Bitcount
            return($this->raiseError("$bitmap isn't a 24bit true color bitmap.\n"));
        }
        if ($planes_and_bitcount[1] != 1) {
            return($this->raiseError("$bitmap: only 1 plane nupported in bitmap image.\n"));
        }
    
        // Read and remove the bitmap compression. Verify compression.
        $compression = unpack("V", substr($data, 0, 4));
        $data = substr($data, 4);
      
        //$compression = 0;
        if ($compression[""] != 0) {
            return($this->raiseError("$bitmap: compression not supported in bitmap image.\n"));
        }
    
        // Remove bitmap data: data size, hres, vres, colours, imp. colours.
        $data = substr($data, 20);
    
        // Add the BITMAPCOREHEADER data
        $header  = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18);
        $data    = $header . $data;
    
        return (array($width, $height, $size, $data));
    }
    
    /**
    * Store the window zoom factor. This should be a reduced fraction but for
    * simplicity we will store all fractions with a numerator of 100.
    *
    * @access private
    */
    function _storeZoom()
    {
        // If scale is 100 we don't need to write a record
        if ($this->_zoom == 100) {
            return;
        }
    
        $record      = 0x00A0;               // Record identifier
        $length      = 0x0004;               // Bytes to follow
    
        $header      = pack("vv", $record, $length);
        $data        = pack("vv", $this->_zoom, 100);
        $this->_append($header.$data);
    }
}
?>

Rev 31	Rev 262
1	`<?php`	1	`<?php`
2	`/*`	2	`/*`
3	`* Module written/ported by Xavier Noguer <xnoguer@rezebra.com>`	3	`* Module written/ported by Xavier Noguer <xnoguer@rezebra.com>`
4	`*`	4	`*`
5	`* The majority of this is _NOT_ my code. I simply ported it from the`	5	`* The majority of this is _NOT_ my code. I simply ported it from the`
6	`* PERL Spreadsheet::WriteExcel module.`	6	`* PERL Spreadsheet::WriteExcel module.`
7	`*`	7	`*`
8	`* The author of the Spreadsheet::WriteExcel module is John McNamara`	8	`* The author of the Spreadsheet::WriteExcel module is John McNamara`
9	`* <jmcnamara@cpan.org>`	9	`* <jmcnamara@cpan.org>`
10	`*`	10	`*`
11	`* I _DO_ maintain this code, and John McNamara has nothing to do with the`	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`	12	`* porting of this code to PHP. Any questions directly related to this`
13	`* class library should be directed to me.`	13	`* class library should be directed to me.`
14	`*`	14	`*`
15	`* License Information:`	15	`* License Information:`
16	`*`	16	`*`
17	`* Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets`	17	`* Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets`
18	`* Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com`	18	`* Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com`
19	`*`	19	`*`
20	`* This library is free software; you can redistribute it and/or`	20	`* This library is free software; you can redistribute it and/or`
21	`* modify it under the terms of the GNU Lesser General Public`	21	`* modify it under the terms of the GNU Lesser General Public`
22	`* License as published by the Free Software Foundation; either`	22	`* License as published by the Free Software Foundation; either`
23	`* version 2.1 of the License, or (at your option) any later version.`	23	`* version 2.1 of the License, or (at your option) any later version.`
24	`*`	24	`*`
25	`* This library is distributed in the hope that it will be useful,`	25	`* This library is distributed in the hope that it will be useful,`
26	`* but WITHOUT ANY WARRANTY; without even the implied warranty of`	26	`* but WITHOUT ANY WARRANTY; without even the implied warranty of`
27	`* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU`	27	`* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU`
28	`* Lesser General Public License for more details.`	28	`* Lesser General Public License for more details.`
29	`*`	29	`*`
30	`* You should have received a copy of the GNU Lesser General Public`	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`	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`	32	`* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA`
33	`*/`	33	`*/`
34		34
35	`require_once('Parser.php');`	35	`require_once('Parser.php');`
36	`require_once('BIFFwriter.php');`	36	`require_once('BIFFwriter.php');`
37		37
38	`/**`	38	`/**`
39	`* Class for generating Excel Spreadsheets`	39	`* Class for generating Excel Spreadsheets`
40	`*`	40	`*`
41	`* @author Xavier Noguer <xnoguer@rezebra.com>`	41	`* @author Xavier Noguer <xnoguer@rezebra.com>`
42	`* @category FileFormats`	42	`* @category FileFormats`
43	`* @package Spreadsheet_Excel_Writer`	43	`* @package Spreadsheet_Excel_Writer`
44	`*/`	44	`*/`
45		45
46	`class Spreadsheet_Excel_Writer_Worksheet extends Spreadsheet_Excel_Writer_BIFFwriter`	46	`class Spreadsheet_Excel_Writer_Worksheet extends Spreadsheet_Excel_Writer_BIFFwriter`
47	`{`	47	`{`
48	`/**`	48	`/**`
49	`* Name of the Worksheet`	49	`* Name of the Worksheet`
50	`* @var string`	50	`* @var string`
51	`*/`	51	`*/`
52	`var $name;`	52	`var $name;`
53		53
54	`/**`	54	`/**`
55	`* Index for the Worksheet`	55	`* Index for the Worksheet`
56	`* @var integer`	56	`* @var integer`
57	`*/`	57	`*/`
58	`var $index;`	58	`var $index;`
59		59
60	`/**`	60	`/**`
61	`* Reference to the (default) Format object for URLs`	61	`* Reference to the (default) Format object for URLs`
62	`* @var object Format`	62	`* @var object Format`
63	`*/`	63	`*/`
64	`var $_url_format;`	64	`var $_url_format;`
65		65
66	`/**`	66	`/**`
67	`* Reference to the parser used for parsing formulas`	67	`* Reference to the parser used for parsing formulas`
68	`* @var object Format`	68	`* @var object Format`
69	`*/`	69	`*/`
70	`var $_parser;`	70	`var $_parser;`
71		71
72	`/**`	72	`/**`
73	`* Filehandle to the temporary file for storing data`	73	`* Filehandle to the temporary file for storing data`
74	`* @var resource`	74	`* @var resource`
75	`*/`	75	`*/`
76	`var $_filehandle;`	76	`var $_filehandle;`
77		77
78	`/**`	78	`/**`
79	`* Boolean indicating if we are using a temporary file for storing data`	79	`* Boolean indicating if we are using a temporary file for storing data`
80	`* @var bool`	80	`* @var bool`
81	`*/`	81	`*/`
82	`var $_using_tmpfile;`	82	`var $_using_tmpfile;`
83		83
84	`/**`	84	`/**`
85	`* Maximum number of rows for an Excel spreadsheet (BIFF5)`	85	`* Maximum number of rows for an Excel spreadsheet (BIFF5)`
86	`* @var integer`	86	`* @var integer`
87	`*/`	87	`*/`
88	`var $_xls_rowmax;`	88	`var $_xls_rowmax;`
89		89
90	`/**`	90	`/**`
91	`* Maximum number of columns for an Excel spreadsheet (BIFF5)`	91	`* Maximum number of columns for an Excel spreadsheet (BIFF5)`
92	`* @var integer`	92	`* @var integer`
93	`*/`	93	`*/`
94	`var $_xls_colmax;`	94	`var $_xls_colmax;`
95		95
96	`/**`	96	`/**`
97	`* Maximum number of characters for a string (LABEL record in BIFF5)`	97	`* Maximum number of characters for a string (LABEL record in BIFF5)`
98	`* @var integer`	98	`* @var integer`
99	`*/`	99	`*/`
100	`var $_xls_strmax;`	100	`var $_xls_strmax;`
101		101
102	`/**`	102	`/**`
103	`* First row for the DIMENSIONS record`	103	`* First row for the DIMENSIONS record`
104	`* @var integer`	104	`* @var integer`
105	`* @see storeDimensions()`	105	`* @see storeDimensions()`
106	`*/`	106	`*/`
107	`var $_dim_rowmin;`	107	`var $_dim_rowmin;`
108		108
109	`/**`	109	`/**`
110	`* Last row for the DIMENSIONS record`	110	`* Last row for the DIMENSIONS record`
111	`* @var integer`	111	`* @var integer`
112	`* @see storeDimensions()`	112	`* @see storeDimensions()`
113	`*/`	113	`*/`
114	`var $_dim_rowmax;`	114	`var $_dim_rowmax;`
115		115
116	`/**`	116	`/**`
117	`* First column for the DIMENSIONS record`	117	`* First column for the DIMENSIONS record`
118	`* @var integer`	118	`* @var integer`
119	`* @see storeDimensions()`	119	`* @see storeDimensions()`
120	`*/`	120	`*/`
121	`var $_dim_colmin;`	121	`var $_dim_colmin;`
122		122
123	`/**`	123	`/**`
124	`* Last column for the DIMENSIONS record`	124	`* Last column for the DIMENSIONS record`
125	`* @var integer`	125	`* @var integer`
126	`* @see storeDimensions()`	126	`* @see storeDimensions()`
127	`*/`	127	`*/`
128	`var $_dim_colmax;`	128	`var $_dim_colmax;`
129		129
130	`/**`	130	`/**`
131	`* Array containing format information for columns`	131	`* Array containing format information for columns`
132	`* @var array`	132	`* @var array`
133	`*/`	133	`*/`
134	`var $_colinfo;`	134	`var $_colinfo;`
135		135
136	`/**`	136	`/**`
137	`* Array containing the selected area for the worksheet`	137	`* Array containing the selected area for the worksheet`
138	`* @var array`	138	`* @var array`
139	`*/`	139	`*/`
140	`var $_selection;`	140	`var $_selection;`
141		141
142	`/**`	142	`/**`
143	`* Array containing the panes for the worksheet`	143	`* Array containing the panes for the worksheet`
144	`* @var array`	144	`* @var array`
145	`*/`	145	`*/`
146	`var $_panes;`	146	`var $_panes;`
147		147
148	`/**`	148	`/**`
149	`* The active pane for the worksheet`	149	`* The active pane for the worksheet`
150	`* @var integer`	150	`* @var integer`
151	`*/`	151	`*/`
152	`var $_active_pane;`	152	`var $_active_pane;`
153		153
154	`/**`	154	`/**`
155	`* Bit specifying if panes are frozen`	155	`* Bit specifying if panes are frozen`
156	`* @var integer`	156	`* @var integer`
157	`*/`	157	`*/`
158	`var $_frozen;`	158	`var $_frozen;`
159		159
160	`/**`	160	`/**`
161	`* Bit specifying if the worksheet is selected`	161	`* Bit specifying if the worksheet is selected`
162	`* @var integer`	162	`* @var integer`
163	`*/`	163	`*/`
164	`var $selected;`	164	`var $selected;`
165		165
166	`/**`	166	`/**`
167	`* The paper size (for printing) (DOCUMENT!!!)`	167	`* The paper size (for printing) (DOCUMENT!!!)`
168	`* @var integer`	168	`* @var integer`
169	`*/`	169	`*/`
170	`var $_paper_size;`	170	`var $_paper_size;`
171		171
172	`/**`	172	`/**`
173	`* Bit specifying paper orientation (for printing). 0 => landscape, 1 => portrait`	173	`* Bit specifying paper orientation (for printing). 0 => landscape, 1 => portrait`
174	`* @var integer`	174	`* @var integer`
175	`*/`	175	`*/`
176	`var $_orientation;`	176	`var $_orientation;`
177		177
178	`/**`	178	`/**`
179	`* The page header caption`	179	`* The page header caption`
180	`* @var string`	180	`* @var string`
181	`*/`	181	`*/`
182	`var $_header;`	182	`var $_header;`
183		183
184	`/**`	184	`/**`
185	`* The page footer caption`	185	`* The page footer caption`
186	`* @var string`	186	`* @var string`
187	`*/`	187	`*/`
188	`var $_footer;`	188	`var $_footer;`
189		189
190	`/**`	190	`/**`
191	`* The horizontal centering value for the page`	191	`* The horizontal centering value for the page`
192	`* @var integer`	192	`* @var integer`
193	`*/`	193	`*/`
194	`var $_hcenter;`	194	`var $_hcenter;`
195		195
196	`/**`	196	`/**`
197	`* The vertical centering value for the page`	197	`* The vertical centering value for the page`
198	`* @var integer`	198	`* @var integer`
199	`*/`	199	`*/`
200	`var $_vcenter;`	200	`var $_vcenter;`
201		201
202	`/**`	202	`/**`
203	`* The margin for the header`	203	`* The margin for the header`
204	`* @var float`	204	`* @var float`
205	`*/`	205	`*/`
206	`var $_margin_head;`	206	`var $_margin_head;`
207		207
208	`/**`	208	`/**`
209	`* The margin for the footer`	209	`* The margin for the footer`
210	`* @var float`	210	`* @var float`
211	`*/`	211	`*/`
212	`var $_margin_foot;`	212	`var $_margin_foot;`
213		213
214	`/**`	214	`/**`
215	`* The left margin for the worksheet in inches`	215	`* The left margin for the worksheet in inches`
216	`* @var float`	216	`* @var float`
217	`*/`	217	`*/`
218	`var $_margin_left;`	218	`var $_margin_left;`
219		219
220	`/**`	220	`/**`
221	`* The right margin for the worksheet in inches`	221	`* The right margin for the worksheet in inches`
222	`* @var float`	222	`* @var float`
223	`*/`	223	`*/`
224	`var $_margin_right;`	224	`var $_margin_right;`
225		225
226	`/**`	226	`/**`
227	`* The top margin for the worksheet in inches`	227	`* The top margin for the worksheet in inches`
228	`* @var float`	228	`* @var float`
229	`*/`	229	`*/`
230	`var $_margin_top;`	230	`var $_margin_top;`
231		231
232	`/**`	232	`/**`
233	`* The bottom margin for the worksheet in inches`	233	`* The bottom margin for the worksheet in inches`
234	`* @var float`	234	`* @var float`
235	`*/`	235	`*/`
236	`var $_margin_bottom;`	236	`var $_margin_bottom;`
237		237
238	`/**`	238	`/**`
239	`* First row to reapeat on each printed page`	239	`* First row to reapeat on each printed page`
240	`* @var integer`	240	`* @var integer`
241	`*/`	241	`*/`
242	`var $title_rowmin;`	242	`var $title_rowmin;`
243		243
244	`/**`	244	`/**`
245	`* Last row to reapeat on each printed page`	245	`* Last row to reapeat on each printed page`
246	`* @var integer`	246	`* @var integer`
247	`*/`	247	`*/`
248	`var $title_rowmax;`	248	`var $title_rowmax;`
249		249
250	`/**`	250	`/**`
251	`* First column to reapeat on each printed page`	251	`* First column to reapeat on each printed page`
252	`* @var integer`	252	`* @var integer`
253	`*/`	253	`*/`
254	`var $title_colmin;`	254	`var $title_colmin;`
255		255
256	`/**`	256	`/**`
257	`* First row of the area to print`	257	`* First row of the area to print`
258	`* @var integer`	258	`* @var integer`
259	`*/`	259	`*/`
260	`var $print_rowmin;`	260	`var $print_rowmin;`
261		261
262	`/**`	262	`/**`
263	`* Last row to of the area to print`	263	`* Last row to of the area to print`
264	`* @var integer`	264	`* @var integer`
265	`*/`	265	`*/`
266	`var $print_rowmax;`	266	`var $print_rowmax;`
267		267
268	`/**`	268	`/**`
269	`* First column of the area to print`	269	`* First column of the area to print`
270	`* @var integer`	270	`* @var integer`
271	`*/`	271	`*/`
272	`var $print_colmin;`	272	`var $print_colmin;`
273		273
274	`/**`	274	`/**`
275	`* Last column of the area to print`	275	`* Last column of the area to print`
276	`* @var integer`	276	`* @var integer`
277	`*/`	277	`*/`
278	`var $print_colmax;`	278	`var $print_colmax;`
279		279
280	`/**`	280	`/**`
281	`* Constructor`	281	`* Constructor`
282	`*`	282	`*`
283	`* @param string $name The name of the new worksheet`	283	`* @param string $name The name of the new worksheet`
284	`* @param integer $index The index of the new worksheet`	284	`* @param integer $index The index of the new worksheet`
285	`* @param mixed &$activesheet The current activesheet of the workbook we belong to`	285	`* @param mixed &$activesheet The current activesheet of the workbook we belong to`
286	`* @param mixed &$firstsheet The first worksheet in the workbook we belong to`	286	`* @param mixed &$firstsheet The first worksheet in the workbook we belong to`
287	`* @param mixed &$url_format The default format for hyperlinks`	287	`* @param mixed &$url_format The default format for hyperlinks`
288	`* @param mixed &$parser The formula parser created for the Workbook`	288	`* @param mixed &$parser The formula parser created for the Workbook`
289	`*/`	289	`*/`
290	`function Spreadsheet_Excel_Writer_Worksheet($name, $index, &$activesheet,`	290	`function Spreadsheet_Excel_Writer_Worksheet($name, $index, &$activesheet,`
291	`&$firstsheet, &$url_format,`	291	`&$firstsheet, &$url_format,`
292	`&$parser)`	292	`&$parser)`
293	`{`	293	`{`
294	`// It needs to call its parent's constructor explicitly`	294	`// It needs to call its parent's constructor explicitly`
295	`$this->Spreadsheet_Excel_Writer_BIFFwriter();`	295	`$this->Spreadsheet_Excel_Writer_BIFFwriter();`
296	`$rowmax = 65536; // 16384 in Excel 5`	296	`$rowmax = 65536; // 16384 in Excel 5`
297	`$colmax = 256;`	297	`$colmax = 256;`
298		298
299	`$this->name = $name;`	299	`$this->name = $name;`
300	`$this->index = $index;`	300	`$this->index = $index;`
301	`$this->activesheet = &$activesheet;`	301	`$this->activesheet = &$activesheet;`
302	`$this->firstsheet = &$firstsheet;`	302	`$this->firstsheet = &$firstsheet;`
303	`$this->_url_format = &$url_format;`	303	`$this->_url_format = &$url_format;`
304	`$this->_parser = &$parser;`	304	`$this->_parser = &$parser;`
305		305
306	`//$this->ext_sheets = array();`	306	`//$this->ext_sheets = array();`
307	`$this->_filehandle = "";`	307	`$this->_filehandle = "";`
308	`$this->_using_tmpfile = true;`	308	`$this->_using_tmpfile = true;`
309	`//$this->fileclosed = 0;`	309	`//$this->fileclosed = 0;`
310	`//$this->offset = 0;`	310	`//$this->offset = 0;`
311	`$this->_xls_rowmax = $rowmax;`	311	`$this->_xls_rowmax = $rowmax;`
312	`$this->_xls_colmax = $colmax;`	312	`$this->_xls_colmax = $colmax;`
313	`$this->_xls_strmax = 255;`	313	`$this->_xls_strmax = 255;`
314	`$this->_dim_rowmin = $rowmax + 1;`	314	`$this->_dim_rowmin = $rowmax + 1;`
315	`$this->_dim_rowmax = 0;`	315	`$this->_dim_rowmax = 0;`
316	`$this->_dim_colmin = $colmax + 1;`	316	`$this->_dim_colmin = $colmax + 1;`
317	`$this->_dim_colmax = 0;`	317	`$this->_dim_colmax = 0;`
318	`$this->_colinfo = array();`	318	`$this->_colinfo = array();`
319	`$this->_selection = array(0,0,0,0);`	319	`$this->_selection = array(0,0,0,0);`
320	`$this->_panes = array();`	320	`$this->_panes = array();`
321	`$this->_active_pane = 3;`	321	`$this->_active_pane = 3;`
322	`$this->_frozen = 0;`	322	`$this->_frozen = 0;`
323	`$this->selected = 0;`	323	`$this->selected = 0;`
324		324
325	`$this->_paper_size = 0x0;`	325	`$this->_paper_size = 0x0;`
326	`$this->_orientation = 0x1;`	326	`$this->_orientation = 0x1;`
327	`$this->_header = '';`	327	`$this->_header = '';`
328	`$this->_footer = '';`	328	`$this->_footer = '';`
329	`$this->_hcenter = 0;`	329	`$this->_hcenter = 0;`
330	`$this->_vcenter = 0;`	330	`$this->_vcenter = 0;`
331	`$this->_margin_head = 0.50;`	331	`$this->_margin_head = 0.50;`
332	`$this->_margin_foot = 0.50;`	332	`$this->_margin_foot = 0.50;`
333	`$this->_margin_left = 0.75;`	333	`$this->_margin_left = 0.75;`
334	`$this->_margin_right = 0.75;`	334	`$this->_margin_right = 0.75;`
335	`$this->_margin_top = 1.00;`	335	`$this->_margin_top = 1.00;`
336	`$this->_margin_bottom = 1.00;`	336	`$this->_margin_bottom = 1.00;`
337		337
338	`$this->title_rowmin = NULL;`	338	`$this->title_rowmin = NULL;`
339	`$this->title_rowmax = NULL;`	339	`$this->title_rowmax = NULL;`
340	`$this->title_colmin = NULL;`	340	`$this->title_colmin = NULL;`
341	`$this->title_colmax = NULL;`	341	`$this->title_colmax = NULL;`
342	`$this->print_rowmin = NULL;`	342	`$this->print_rowmin = NULL;`
343	`$this->print_rowmax = NULL;`	343	`$this->print_rowmax = NULL;`
344	`$this->print_colmin = NULL;`	344	`$this->print_colmin = NULL;`
345	`$this->print_colmax = NULL;`	345	`$this->print_colmax = NULL;`
346		346
347	`$this->_print_gridlines = 1;`	347	`$this->_print_gridlines = 1;`
348	`$this->_print_headers = 0;`	348	`$this->_print_headers = 0;`
349		349
350	`$this->_fit_page = 0;`	350	`$this->_fit_page = 0;`
351	`$this->_fit_width = 0;`	351	`$this->_fit_width = 0;`
352	`$this->_fit_height = 0;`	352	`$this->_fit_height = 0;`
353		353
354	`$this->_hbreaks = array();`	354	`$this->_hbreaks = array();`
355	`$this->_vbreaks = array();`	355	`$this->_vbreaks = array();`
356		356
357	`$this->_protect = 0;`	357	`$this->_protect = 0;`
358	`$this->_password = NULL;`	358	`$this->_password = NULL;`
359		359
360	`$this->col_sizes = array();`	360	`$this->col_sizes = array();`
361	`$this->row_sizes = array();`	361	`$this->row_sizes = array();`
362		362
363	`$this->_zoom = 100;`	363	`$this->_zoom = 100;`
364	`$this->_print_scale = 100;`	364	`$this->_print_scale = 100;`
365		365
366	`$this->_initialize();`	366	`$this->_initialize();`
367	`}`	367	`}`
368		368
369	`/**`	369	`/**`
370	`* Open a tmp file to store the majority of the Worksheet data. If this fails,`	370	`* Open a tmp file to store the majority of the Worksheet data. If this fails,`
371	`* for example due to write permissions, store the data in memory. This can be`	371	`* for example due to write permissions, store the data in memory. This can be`
372	`* slow for large files.`	372	`* slow for large files.`
373	`*`	373	`*`
374	`* @access private`	374	`* @access private`
375	`*/`	375	`*/`
376	`function _initialize()`	376	`function _initialize()`
377	`{`	377	`{`
378	`// Open tmp file for storing Worksheet data`	378	`// Open tmp file for storing Worksheet data`
379	`$fh = tmpfile();`	379	`$fh = tmpfile();`
380	`if ( $fh) {`	380	`if ( $fh) {`
381	`// Store filehandle`	381	`// Store filehandle`
382	`$this->_filehandle = $fh;`	382	`$this->_filehandle = $fh;`
383	`}`	383	`}`
384	`else {`	384	`else {`
385	`// If tmpfile() fails store data in memory`	385	`// If tmpfile() fails store data in memory`
386	`$this->_using_tmpfile = false;`	386	`$this->_using_tmpfile = false;`
387	`}`	387	`}`
388	`}`	388	`}`
389		389
390	`/**`	390	`/**`
391	`* Add data to the beginning of the workbook (note the reverse order)`	391	`* Add data to the beginning of the workbook (note the reverse order)`
392	`* and to the end of the workbook.`	392	`* and to the end of the workbook.`
393	`*`	393	`*`
394	`* @access public`	394	`* @access public`
395	`* @see Spreadsheet_Excel_Writer_Workbook::storeWorkbook()`	395	`* @see Spreadsheet_Excel_Writer_Workbook::storeWorkbook()`
396	`* @param array $sheetnames The array of sheetnames from the Workbook this`	396	`* @param array $sheetnames The array of sheetnames from the Workbook this`
397	`* worksheet belongs to`	397	`* worksheet belongs to`
398	`*/`	398	`*/`
399	`function close($sheetnames)`	399	`function close($sheetnames)`
400	`{`	400	`{`
401	`$num_sheets = count($sheetnames);`	401	`$num_sheets = count($sheetnames);`
402		402
403	`/***********************************************`	403	`/***********************************************`
404	`* Prepend in reverse order!!`	404	`* Prepend in reverse order!!`
405	`*/`	405	`*/`
406		406
407	`// Prepend the sheet dimensions`	407	`// Prepend the sheet dimensions`
408	`$this->storeDimensions();`	408	`$this->storeDimensions();`
409		409
410	`// Prepend the sheet password`	410	`// Prepend the sheet password`
411	`$this->_storePassword();`	411	`$this->_storePassword();`
412		412
413	`// Prepend the sheet protection`	413	`// Prepend the sheet protection`
414	`$this->_storeProtect();`	414	`$this->_storeProtect();`
415		415
416	`// Prepend the page setup`	416	`// Prepend the page setup`
417	`$this->_storeSetup();`	417	`$this->_storeSetup();`
418		418
419	`// Prepend the bottom margin`	419	`// Prepend the bottom margin`
420	`$this->_storeMarginBottom();`	420	`$this->_storeMarginBottom();`
421		421
422	`// Prepend the top margin`	422	`// Prepend the top margin`
423	`$this->_storeMarginTop();`	423	`$this->_storeMarginTop();`
424		424
425	`// Prepend the right margin`	425	`// Prepend the right margin`
426	`$this->_storeMarginRight();`	426	`$this->_storeMarginRight();`
427		427
428	`// Prepend the left margin`	428	`// Prepend the left margin`
429	`$this->_storeMarginLeft();`	429	`$this->_storeMarginLeft();`
430		430
431	`// Prepend the page vertical centering`	431	`// Prepend the page vertical centering`
432	`$this->_storeVcenter();`	432	`$this->_storeVcenter();`
433		433
434	`// Prepend the page horizontal centering`	434	`// Prepend the page horizontal centering`
435	`$this->_storeHcenter();`	435	`$this->_storeHcenter();`
436		436
437	`// Prepend the page footer`	437	`// Prepend the page footer`
438	`$this->_storeFooter();`	438	`$this->_storeFooter();`
439		439
440	`// Prepend the page header`	440	`// Prepend the page header`
441	`$this->_storeHeader();`	441	`$this->_storeHeader();`
442		442
443	`// Prepend the vertical page breaks`	443	`// Prepend the vertical page breaks`
444	`$this->_storeVbreak();`	444	`$this->_storeVbreak();`
445		445
446	`// Prepend the horizontal page breaks`	446	`// Prepend the horizontal page breaks`
447	`$this->_storeHbreak();`	447	`$this->_storeHbreak();`
448		448
449	`// Prepend WSBOOL`	449	`// Prepend WSBOOL`
450	`$this->_storeWsbool();`	450	`$this->_storeWsbool();`
451		451
452	`// Prepend GRIDSET`	452	`// Prepend GRIDSET`
453	`$this->_storeGridset();`	453	`$this->_storeGridset();`
454		454
455	`// Prepend PRINTGRIDLINES`	455	`// Prepend PRINTGRIDLINES`
456	`$this->_storePrintGridlines();`	456	`$this->_storePrintGridlines();`
457		457
458	`// Prepend PRINTHEADERS`	458	`// Prepend PRINTHEADERS`
459	`$this->_storePrintHeaders();`	459	`$this->_storePrintHeaders();`
460		460
461	`// Prepend EXTERNSHEET references`	461	`// Prepend EXTERNSHEET references`
462	`for ($i = $num_sheets; $i > 0; $i--)`	462	`for ($i = $num_sheets; $i > 0; $i--)`
463	`{`	463	`{`
464	`$sheetname = $sheetnames[$i-1];`	464	`$sheetname = $sheetnames[$i-1];`
465	`$this->_storeExternsheet($sheetname);`	465	`$this->_storeExternsheet($sheetname);`
466	`}`	466	`}`
467		467
468	`// Prepend the EXTERNCOUNT of external references.`	468	`// Prepend the EXTERNCOUNT of external references.`
469	`$this->_storeExterncount($num_sheets);`	469	`$this->_storeExterncount($num_sheets);`
470		470
471	`// Prepend the COLINFO records if they exist`	471	`// Prepend the COLINFO records if they exist`
472	`if (!empty($this->_colinfo))`	472	`if (!empty($this->_colinfo))`
473	`{`	473	`{`
474	`for($i=0; $i < count($this->_colinfo); $i++) {`	474	`for($i=0; $i < count($this->_colinfo); $i++) {`
475	`$this->_storeColinfo($this->_colinfo[$i]);`	475	`$this->_storeColinfo($this->_colinfo[$i]);`
476	`}`	476	`}`
477	`$this->_storeDefcol();`	477	`$this->_storeDefcol();`
478	`}`	478	`}`
479		479
480	`// Prepend the BOF record`	480	`// Prepend the BOF record`
481	`$this->_storeBof(0x0010);`	481	`$this->_storeBof(0x0010);`
482		482
483	`/*`	483	`/*`
484	`* End of prepend. Read upwards from here.`	484	`* End of prepend. Read upwards from here.`
485	`***********************************************/`	485	`***********************************************/`
486		486
487	`// Append`	487	`// Append`
488	`$this->_storeWindow2();`	488	`$this->_storeWindow2();`
489	`$this->_storeZoom();`	489	`$this->_storeZoom();`
490	`if (!empty($this->_panes)) {`	490	`if (!empty($this->_panes)) {`
491	`$this->_storePanes($this->_panes);`	491	`$this->_storePanes($this->_panes);`
492	`}`	492	`}`
493	`$this->_storeSelection($this->_selection);`	493	`$this->_storeSelection($this->_selection);`
494	`$this->_storeEof();`	494	`$this->_storeEof();`
495	`}`	495	`}`
496		496
497	`/**`	497	`/**`
498	`* Retrieve the worksheet name.`	498	`* Retrieve the worksheet name.`
499	`* This is usefull when creating worksheets without a name.`	499	`* This is usefull when creating worksheets without a name.`
500	`*`	500	`*`

Subversion Repositories Sites.obs-saisons.fr

(root)/trunk/applications/jrest/lib/Spreadsheet/Excel/Writer/Worksheet.php – Rev 31 → 262