Worksheet.php

Go to the documentation of this file.

<?php
/*
*  Module written/ported by Xavier Noguer <xnoguer@rezebra.com>
*
*  The majority of this is _NOT_ my code.  I simply ported it from the
*  PERL Spreadsheet::WriteExcel module.
*
*  The author of the Spreadsheet::WriteExcel module is John McNamara
*  <jmcnamara@cpan.org>
*
*  I _DO_ maintain this code, and John McNamara has nothing to do with the
*  porting of this code to PHP.  Any questions directly related to this
*  class library should be directed to me.
*
*  License Information:
*
*    Spreadsheet_Excel_Writer:  A library for generating Excel Spreadsheets
*    Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com
*
*    This library is free software; you can redistribute it and/or
*    modify it under the terms of the GNU Lesser General Public
*    License as published by the Free Software Foundation; either
*    version 2.1 of the License, or (at your option) any later version.
*
*    This library is distributed in the hope that it will be useful,
*    but WITHOUT ANY WARRANTY; without even the implied warranty of
*    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
*    Lesser General Public License for more details.
*
*    You should have received a copy of the GNU Lesser General Public
*    License along with this library; if not, write to the Free Software
*    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/

require_once 'Spreadsheet/Excel/Writer/Parser.php';
require_once 'Spreadsheet/Excel/Writer/BIFFwriter.php';

class Spreadsheet_Excel_Writer_Worksheet extends Spreadsheet_Excel_Writer_BIFFwriter
{
    var $name;

    var $index;

    var $_url_format;

    var $_parser;

    var $_filehandle;

    var $_using_tmpfile;

    var $_xls_rowmax;

    var $_xls_colmax;

    var $_xls_strmax;

    var $_dim_rowmin;

    var $_dim_rowmax;

    var $_dim_colmin;

    var $_dim_colmax;

    var $_colinfo;

    var $_selection;

    var $_panes;

    var $_active_pane;

    var $_frozen;

    var $selected;

    var $_paper_size;

    var $_orientation;

    var $_header;

    var $_footer;

    var $_hcenter;

    var $_vcenter;

    var $_margin_head;

    var $_margin_foot;

    var $_margin_left;

    var $_margin_right;

    var $_margin_top;

    var $_margin_bottom;

    var $title_rowmin;

    var $title_rowmax;

    var $title_colmin;

    var $print_rowmin;

    var $print_rowmax;

    var $print_colmin;

    var $print_colmax;

    var $_outline_on;

    var $_outline_style;

    var $_outline_below;

    var $_outline_right;

    var $_outline_row_level;

    var $_fit_page;

    var $_fit_width;

    var $_fit_height;

    var $_str_total;

    var $_str_unique;

    var $_str_table;

    var $_merged_ranges;

    var $_input_encoding;

    function Spreadsheet_Excel_Writer_Worksheet($BIFF_version, $name,
                                                $index, &$activesheet,
                                                &$firstsheet, &$str_total,
                                                &$str_unique, &$str_table,
                                                &$url_format, &$parser,
                                                $tmp_dir)
    {
        // It needs to call its parent's constructor explicitly
        $this->Spreadsheet_Excel_Writer_BIFFwriter();
        $this->_BIFF_version   = $BIFF_version;
        $rowmax                = 65536; // 16384 in Excel 5
        $colmax                = 256;

        $this->name            = $name;
        $this->index           = $index;
        $this->activesheet     = &$activesheet;
        $this->firstsheet      = &$firstsheet;
        $this->_str_total      = &$str_total;
        $this->_str_unique     = &$str_unique;
        $this->_str_table      = &$str_table;
        $this->_url_format     = &$url_format;
        $this->_parser         = &$parser;

        //$this->ext_sheets      = array();
        $this->_filehandle     = '';
        $this->_using_tmpfile  = true;
        //$this->fileclosed      = 0;
        //$this->offset          = 0;
        $this->_xls_rowmax     = $rowmax;
        $this->_xls_colmax     = $colmax;
        $this->_xls_strmax     = 255;
        $this->_dim_rowmin     = $rowmax + 1;
        $this->_dim_rowmax     = 0;
        $this->_dim_colmin     = $colmax + 1;
        $this->_dim_colmax     = 0;
        $this->_colinfo        = array();
        $this->_selection      = array(0,0,0,0);
        $this->_panes          = array();
        $this->_active_pane    = 3;
        $this->_frozen         = 0;
        $this->selected        = 0;

        $this->_paper_size      = 0x0;
        $this->_orientation     = 0x1;
        $this->_header          = '';
        $this->_footer          = '';
        $this->_hcenter         = 0;
        $this->_vcenter         = 0;
        $this->_margin_head     = 0.50;
        $this->_margin_foot     = 0.50;
        $this->_margin_left     = 0.75;
        $this->_margin_right    = 0.75;
        $this->_margin_top      = 1.00;
        $this->_margin_bottom   = 1.00;

        $this->title_rowmin     = null;
        $this->title_rowmax     = null;
        $this->title_colmin     = null;
        $this->title_colmax     = null;
        $this->print_rowmin     = null;
        $this->print_rowmax     = null;
        $this->print_colmin     = null;
        $this->print_colmax     = null;

        $this->_print_gridlines  = 1;
        $this->_screen_gridlines = 1;
        $this->_print_headers    = 0;

        $this->_fit_page        = 0;
        $this->_fit_width       = 0;
        $this->_fit_height      = 0;

        $this->_hbreaks         = array();
        $this->_vbreaks         = array();

        $this->_protect         = 0;
        $this->_password        = null;

        $this->col_sizes        = array();
        $this->_row_sizes        = array();

        $this->_zoom            = 100;
        $this->_print_scale     = 100;

        $this->_outline_row_level = 0;
        $this->_outline_style     = 0;
        $this->_outline_below     = 1;
        $this->_outline_right     = 1;
        $this->_outline_on        = 1;

        $this->_merged_ranges     = array();

        $this->_input_encoding    = '';

        $this->_dv                = array();
        
        $this->_tmp_dir = $tmp_dir;

        $this->_initialize();
    }

    function _initialize()
    {
        if ($this->_using_tmpfile == false) {
            return;
        }

        if ($this->_tmp_dir === '' && ini_get('open_basedir') === false) {
            // open_basedir restriction in effect - store data in memory
            // ToDo: Let the error actually have an effect somewhere
            $this->_using_tmpfile = false;  
            return new PEAR_Error('Temp file could not be opened since open_basedir restriction in effect - please use setTmpDir() - using memory storage instead');
        }

        // Open tmp file for storing Worksheet data
        if ($this->_tmp_dir === '') {
            $fh = tmpfile();
        } else {
            // For people with open base dir restriction
            $tmpfilename = tempnam($this->_tmp_dir, "Spreadsheet_Excel_Writer");
            $fh = @fopen($tmpfilename, "w+b");
        }

        if ($fh === false) {
            // If tmpfile() fails store data in memory
            $this->_using_tmpfile = false;
        } else {
            // Store filehandle
            $this->_filehandle = $fh;
        }
    }

    function close($sheetnames)
    {
        $num_sheets = count($sheetnames);

        /***********************************************
        * Prepend in reverse order!!
        */

        // Prepend the sheet dimensions
        $this->_storeDimensions();

        // Prepend the sheet password
        $this->_storePassword();

        // Prepend the sheet protection
        $this->_storeProtect();

        // Prepend the page setup
        $this->_storeSetup();

        /* FIXME: margins are actually appended */
        // Prepend the bottom margin
        $this->_storeMarginBottom();

        // Prepend the top margin
        $this->_storeMarginTop();

        // Prepend the right margin
        $this->_storeMarginRight();

        // Prepend the left margin
        $this->_storeMarginLeft();

        // Prepend the page vertical centering
        $this->_storeVcenter();

        // Prepend the page horizontal centering
        $this->_storeHcenter();

        // Prepend the page footer
        $this->_storeFooter();

        // Prepend the page header
        $this->_storeHeader();

        // Prepend the vertical page breaks
        $this->_storeVbreak();

        // Prepend the horizontal page breaks
        $this->_storeHbreak();

        // Prepend WSBOOL
        $this->_storeWsbool();

        // Prepend GRIDSET
        $this->_storeGridset();

         //  Prepend GUTS
        if ($this->_BIFF_version == 0x0500) {
            $this->_storeGuts();
        }

        // Prepend PRINTGRIDLINES
        $this->_storePrintGridlines();

        // Prepend PRINTHEADERS
        $this->_storePrintHeaders();

        // Prepend EXTERNSHEET references
        if ($this->_BIFF_version == 0x0500) {
            for ($i = $num_sheets; $i > 0; $i--) {
                $sheetname = $sheetnames[$i-1];
                $this->_storeExternsheet($sheetname);
            }
        }

        // Prepend the EXTERNCOUNT of external references.
        if ($this->_BIFF_version == 0x0500) {
            $this->_storeExterncount($num_sheets);
        }

        // Prepend the COLINFO records if they exist
        if (!empty($this->_colinfo)) {
            $colcount = count($this->_colinfo);
            for ($i = 0; $i < $colcount; $i++) {
                $this->_storeColinfo($this->_colinfo[$i]);
            }
            $this->_storeDefcol();
        }

        // Prepend the BOF record
        $this->_storeBof(0x0010);

        /*
        * End of prepend. Read upwards from here.
        ***********************************************/

        // Append
        $this->_storeWindow2();
        $this->_storeZoom();
        if (!empty($this->_panes)) {
            $this->_storePanes($this->_panes);
        }
        $this->_storeSelection($this->_selection);
        $this->_storeMergedCells();
        /* TODO: add data validity */
        /*if ($this->_BIFF_version == 0x0600) {
            $this->_storeDataValidity();
        }*/
        $this->_storeEof();
    }

    function getName()
    {
        return $this->name;
    }

    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 '';
    }

    function setMerge($first_row, $first_col, $last_row, $last_col)
    {
        if (($last_row < $first_row) || ($last_col < $first_col)) {
            return;
        }
        // don't check rowmin, rowmax, etc... because we don't know when this
        // is going to be called
        $this->_merged_ranges[] = array($first_row, $first_col, $last_row, $last_col);
    }

    function select()
    {
        $this->selected = 1;
    }

    function activate()
    {
        $this->selected = 1;
        $this->activesheet = $this->index;
    }

    function setFirstSheet()
    {
        $this->firstsheet = $this->index;
    }

    function protect($password)
    {
        $this->_protect   = 1;
        $this->_password  = $this->_encodePassword($password);
    }

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

        // Set width to zero if column is hidden
        $width = ($hidden) ? 0 : $width;

        for ($col = $firstcol; $col <= $lastcol; $col++) {
            $this->col_sizes[$col] = $width;
        }
    }

    function setSelection($first_row,$first_column,$last_row,$last_column)
    {
        $this->_selection = array($first_row,$first_column,$last_row,$last_column);
    }

    function freezePanes($panes)
    {
        $this->_frozen = 1;
        $this->_panes  = $panes;
    }

    function thawPanes($panes)
    {
        $this->_frozen = 0;
        $this->_panes  = $panes;
    }

    function setPortrait()
    {
        $this->_orientation = 1;
    }

    function setLandscape()
    {
        $this->_orientation = 0;
    }

    function setPaper($size = 0)
    {
        $this->_paper_size = $size;
    }


    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;
    }

    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;
    }

    function centerHorizontally($center = 1)
    {
        $this->_hcenter = $center;
    }

    function centerVertically($center = 1)
    {
        $this->_vcenter = $center;
    }

    function setMargins($margin)
    {
        $this->setMarginLeft($margin);
        $this->setMarginRight($margin);
        $this->setMarginTop($margin);
        $this->setMarginBottom($margin);
    }

    function setMargins_LR($margin)
    {
        $this->setMarginLeft($margin);
        $this->setMarginRight($margin);
    }

    function setMargins_TB($margin)
    {
        $this->setMarginTop($margin);
        $this->setMarginBottom($margin);
    }

    function setMarginLeft($margin = 0.75)
    {
        $this->_margin_left = $margin;
    }

    function setMarginRight($margin = 0.75)
    {
        $this->_margin_right = $margin;
    }

    function setMarginTop($margin = 1.00)
    {
        $this->_margin_top = $margin;
    }

    function setMarginBottom($margin = 1.00)
    {
        $this->_margin_bottom = $margin;
    }

    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;
        }
    }

    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;
        }
    }

    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;
    }


    function hideGridlines()
    {
        $this->_print_gridlines = 0;
    }

    function hideScreenGridlines()
    {
        $this->_screen_gridlines = 0;
    }

    function printRowColHeaders($print = 1)
    {
        $this->_print_headers = $print;
    }

    function fitToPages($width, $height)
    {
        $this->_fit_page      = 1;
        $this->_fit_width     = $width;
        $this->_fit_height    = $height;
    }

    function setHPagebreaks($breaks)
    {
        foreach ($breaks as $break) {
            array_push($this->_hbreaks, $break);
        }
    }

    function setVPagebreaks($breaks)
    {
        foreach ($breaks as $break) {
            array_push($this->_vbreaks, $break);
        }
    }


    function setZoom($scale = 100)
    {
        // Confine the scale to Excel's range
        if ($scale < 10 || $scale > 400) {
            $this->raiseError("Zoom factor $scale outside range: 10 <= zoom <= 400");
            $scale = 100;
        }

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

    function setPrintScale($scale = 100)
    {
        // Confine the scale to Excel's range
        if ($scale < 10 || $scale > 400) {
            $this->raiseError("Print scale $scale outside range: 10 <= zoom <= 400");
            $scale = 100;
        }

        // Turn off "fit to page" option
        $this->_fit_page = 0;

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

    function write($row, $col, $token, $format = null)
    {
        // Check for a cell reference in A1 notation and substitute row and column
        /*if ($_[0] =~ /^\D/) {
            @_ = $this->_substituteCellref(@_);
    }*/

        if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", $token)) {
            // Match number
            return $this->writeNumber($row, $col, $token, $format);
        } elseif (preg_match("/^[fh]tt?p:\/\//", $token)) {
            // Match http or ftp URL
            return $this->writeUrl($row, $col, $token, '', $format);
        } elseif (preg_match("/^mailto:/", $token)) {
            // Match mailto:
            return $this->writeUrl($row, $col, $token, '', $format);
        } elseif (preg_match("/^(?:in|ex)ternal:/", $token)) {
            // Match internal or external sheet link
            return $this->writeUrl($row, $col, $token, '', $format);
        } elseif (preg_match("/^=/", $token)) {
            // Match formula
            return $this->writeFormula($row, $col, $token, $format);
        } elseif ($token == '') {
            // Match blank
            return $this->writeBlank($row, $col, $format);
        } else {
            // Default: match string
            return $this->writeString($row, $col, $token, $format);
        }
    }

    function writeRow($row, $col, $val, $format = null)
    {
        $retval = '';
        if (is_array($val)) {
            foreach ($val as $v) {
                if (is_array($v)) {
                    $this->writeCol($row, $col, $v, $format);
                } else {
                    $this->write($row, $col, $v, $format);
                }
                $col++;
            }
        } else {
            $retval = new PEAR_Error('$val needs to be an array');
        }
        return($retval);
    }

    function writeCol($row, $col, $val, $format = null)
    {
        $retval = '';
        if (is_array($val)) {
            foreach ($val as $v) {
                $this->write($row, $col, $v, $format);
                $row++;
            }
        } else {
            $retval = new PEAR_Error('$val needs to be an array');
        }
        return($retval);
    }

    function _XF(&$format)
    {
        if ($format) {
            return($format->getXfIndex());
        } else {
            return(0x0F);
        }
    }


    /******************************************************************************
    *******************************************************************************
    *
    * Internal methods
    */


    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);
        }
    }

    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);
    }

    function _cellToRowcol($cell)
    {
        preg_match("/\$?([A-I]?[A-Z])\$?(\d+)/",$cell,$match);
        $col     = $match[1];
        $row     = $match[2];

        // Convert base26 column string to number
        $chars = split('', $col);
        $expn  = 0;
        $col   = 0;

        while ($chars) {
            $char = array_pop($chars);        // LS char first
            $col += (ord($char) -ord('A') +1) * pow(26,$expn);
            $expn++;
        }

        // Convert 1-index to zero-index
        $row--;
        $col--;

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

    function _encodePassword($plaintext)
    {
        $password = 0x0000;
        $i        = 1;       // char position

        // split the plain text password in its component characters
        $chars = preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY);
        foreach ($chars as $char) {
            $value        = ord($char) << $i;   // shifted ASCII value
            $rotated_bits = $value >> 15;       // rotated bits beyond bit 15
            $value       &= 0x7fff;             // first 15 bits
            $password    ^= ($value | $rotated_bits);
            $i++;
        }

        $password ^= strlen($plaintext);
        $password ^= 0xCE4B;

        return($password);
    }

    function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false)
    {
        $this->_outline_on    = $visible;
        $this->_outline_below = $symbols_below;
        $this->_outline_right = $symbols_right;
        $this->_outline_style = $auto_style;

        // Ensure this is a boolean vale for Window2
        if ($this->_outline_on) {
            $this->_outline_on = 1;
        }
     }

    /******************************************************************************
    *******************************************************************************
    *
    * BIFF RECORDS
    */


    function writeNumber($row, $col, $num, $format = null)
    {
        $record    = 0x0203;                 // Record identifier
        $length    = 0x000E;                 // Number of bytes to follow

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

        // Check that row and col are valid and store max and min values
        if ($row >= $this->_xls_rowmax) {
            return(-2);
        }
        if ($col >= $this->_xls_colmax) {
            return(-2);
        }
        if ($row <  $this->_dim_rowmin)  {
            $this->_dim_rowmin = $row;
        }
        if ($row >  $this->_dim_rowmax)  {
            $this->_dim_rowmax = $row;
        }
        if ($col <  $this->_dim_colmin)  {
            $this->_dim_colmin = $col;
        }
        if ($col >  $this->_dim_colmax)  {
            $this->_dim_colmax = $col;
        }

        $header    = pack("vv",  $record, $length);
        $data      = pack("vvv", $row, $col, $xf);
        $xl_double = pack("d",   $num);
        if ($this->_byte_order) { // if it's Big Endian
            $xl_double = strrev($xl_double);
        }

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

    function writeString($row, $col, $str, $format = null)
    {
        if ($this->_BIFF_version == 0x0600) {
            return $this->writeStringBIFF8($row, $col, $str, $format);
        }
        $strlen    = strlen($str);
        $record    = 0x0204;                   // Record identifier
        $length    = 0x0008 + $strlen;         // Bytes to follow
        $xf        = $this->_XF($format);      // The cell format

        $str_error = 0;

        // Check that row and col are valid and store max and min values
        if ($row >= $this->_xls_rowmax) {
            return(-2);
        }
        if ($col >= $this->_xls_colmax) {
            return(-2);
        }
        if ($row <  $this->_dim_rowmin) {
            $this->_dim_rowmin = $row;
        }
        if ($row >  $this->_dim_rowmax) {
            $this->_dim_rowmax = $row;
        }
        if ($col <  $this->_dim_colmin) {
            $this->_dim_colmin = $col;
        }
        if ($col >  $this->_dim_colmax) {
            $this->_dim_colmax = $col;
        }

        if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars
            $str       = substr($str, 0, $this->_xls_strmax);
            $length    = 0x0008 + $this->_xls_strmax;
            $strlen    = $this->_xls_strmax;
            $str_error = -3;
        }

        $header    = pack("vv",   $record, $length);
        $data      = pack("vvvv", $row, $col, $xf, $strlen);
        $this->_append($header . $data . $str);
        return($str_error);
    }

    function setInputEncoding($encoding)
    {
         if ($encoding != 'UTF-16LE' && !function_exists('iconv')) {
             $this->raiseError("Using an input encoding other than UTF-16LE requires PHP support for iconv");
         }
         $this->_input_encoding = $encoding;
    }

    function writeStringBIFF8($row, $col, $str, $format = null)
    {
        if ($this->_input_encoding == 'UTF-16LE')
        {
            $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
            $encoding  = 0x1;
        }
        elseif ($this->_input_encoding != '')
        {
            $str = iconv($this->_input_encoding, 'UTF-16LE', $str);
            $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
            $encoding  = 0x1;
        }
        else
        {
            $strlen    = strlen($str);
            $encoding  = 0x0;
        }
        $record    = 0x00FD;                   // Record identifier
        $length    = 0x000A;                   // Bytes to follow
        $xf        = $this->_XF($format);      // The cell format

        $str_error = 0;

        // Check that row and col are valid and store max and min values
        if ($this->_checkRowCol($row, $col) == false) {
            return -2;
        }

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

        /* check if string is already present */
        if (!isset($this->_str_table[$str])) {
            $this->_str_table[$str] = $this->_str_unique++;
        }
        $this->_str_total++;

        $header    = pack('vv',   $record, $length);
        $data      = pack('vvvV', $row, $col, $xf, $this->_str_table[$str]);
        $this->_append($header.$data);
        return $str_error;
    }

    function _checkRowCol($row, $col)
    {
        if ($row >= $this->_xls_rowmax) {
            return false;
        }
        if ($col >= $this->_xls_colmax) {
            return false;
        }
        if ($row <  $this->_dim_rowmin) {
            $this->_dim_rowmin = $row;
        }
        if ($row >  $this->_dim_rowmax) {
            $this->_dim_rowmax = $row;
        }
        if ($col <  $this->_dim_colmin) {
            $this->_dim_colmin = $col;
        }
        if ($col >  $this->_dim_colmax) {
            $this->_dim_colmax = $col;
        }
        return true;
    }

    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);
    }

    function writeBlank($row, $col, $format)
    {
        // Don't write a blank cell unless it has a format
        if (!$format) {
            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;
    }

    function writeFormula($row, $col, $formula, $format = null)
    {
        $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
        $unknown   = 0x0000;              // Must be zero


        // Check that row and col are valid and store max and min values
        if ($this->_checkRowCol($row, $col) == false) {
            return -2;
        }

        // 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, $unknown, $formlen);

        $this->_append($header . $data . $formula);
        return 0;
    }

    function writeUrl($row, $col, $url, $string = '', $format = null)
    {
        // Add start row and col to arg list
        return($this->_writeUrlRange($row, $col, $row, $col, $url, $string, $format));
    }

    function _writeUrlRange($row1, $col1, $row2, $col2, $url, $string = '', $format = null)
    {

        // 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));
    }


    function _writeUrlWeb($row1, $col1, $row2, $col2, $url, $str, $format = null)
    {
        $record      = 0x01B8;                       // Record identifier
        $length      = 0x00000;                      // Bytes to follow

        if (!$format) {
            $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) || ($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_split("''", $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);
    }

    function _writeUrlInternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
    {
        $record      = 0x01B8;                       // Record identifier
        $length      = 0x00000;                      // Bytes to follow

        if (!$format) {
            $format = $this->_url_format;
        }

        // Strip URL type
        $url = preg_replace('/^internal:/', '', $url);

        // Write the visible label
        if ($str == '') {
            $str = $url;
        }
        $str_error = $this->writeString($row1, $col1, $str, $format);
        if (($str_error == -2) || ($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_split("''", $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);
    }

    function _writeUrlExternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
    {
        // 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) {
            $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;
        }
        $link_type               = 0x01 | $absolute;
    
        // 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)
        /*if (preg_match("/\#/", $url)) {
            list($dir_long, $sheet) = split("\#", $url);
        } else {
            $dir_long = $url;
        }
    
        if (isset($sheet)) {
            $link_type |= 0x08;
            $sheet_len  = pack("V", strlen($sheet) + 0x01);
            $sheet      = join("\0", split('', $sheet));
            $sheet     .= "\0\0\0";
        } else {
            $sheet_len   = '';
            $sheet       = '';
        }*/
        $dir_long = $url;
        if (preg_match("/\#/", $url)) {
            $link_type |= 0x08;
        }


    
        // 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", split('', $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", 0);//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);
    }


    function setRow($row, $height, $format = null, $hidden = false, $level = 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       = 0x0000;               // Option flags
        $ixfe        = $this->_XF($format);  // XF index

        // set _row_sizes so _sizeRow() can use it
        $this->_row_sizes[$row] = $height;

        // 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
        }

        $level = max(0, min($level, 7));  // level should be between 0 and 7
        $this->_outline_row_level = max($level, $this->_outline_row_level);


        // Set the options flags. fUnsynced is used to show that the font and row
        // heights are not compatible. This is usually the case for WriteExcel.
        // The collapsed flag 0x10 doesn't seem to be used to indicate that a row
        // is collapsed. Instead it is used to indicate that the previous row is
        // collapsed. The zero height flag, 0x20, is used to collapse a row.

        $grbit |= $level;
        if ($hidden) {
            $grbit |= 0x0020;
        }
        $grbit |= 0x0040; // fUnsynced
        if ($format) {
            $grbit |= 0x0080;
        }
        $grbit |= 0x0100;

        $header   = pack("vv",       $record, $length);
        $data     = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw,
                                     $irwMac,$reserved, $grbit, $ixfe);
        $this->_append($header.$data);
    }

    function _storeDimensions()
    {
        $record    = 0x0200;                 // Record identifier
        $row_min   = $this->_dim_rowmin;     // First row
        $row_max   = $this->_dim_rowmax + 1; // Last row plus 1
        $col_min   = $this->_dim_colmin;     // First column
        $col_max   = $this->_dim_colmax + 1; // Last column plus 1
        $reserved  = 0x0000;                 // Reserved by Excel

        if ($this->_BIFF_version == 0x0500) {
            $length    = 0x000A;               // Number of bytes to follow
            $data      = pack("vvvvv", $row_min, $row_max,
                                       $col_min, $col_max, $reserved);
        } elseif ($this->_BIFF_version == 0x0600) {
            $length    = 0x000E;
            $data      = pack("VVvvv", $row_min, $row_max,
                                       $col_min, $col_max, $reserved);
        }
        $header = pack("vv", $record, $length);
        $this->_prepend($header.$data);
    }

    function _storeWindow2()
    {
        $record         = 0x023E;     // Record identifier
        if ($this->_BIFF_version == 0x0500) {
            $length         = 0x000A;     // Number of bytes to follow
        } elseif ($this->_BIFF_version == 0x0600) {
            $length         = 0x0012;
        }

        $grbit          = 0x00B6;     // Option flags
        $rwTop          = 0x0000;     // Top row visible in window
        $colLeft        = 0x0000;     // Leftmost column visible in window


        // The options flags that comprise $grbit
        $fDspFmla       = 0;                     // 0 - bit
        $fDspGrid       = $this->_screen_gridlines; // 1
        $fDspRwCol      = 1;                     // 2
        $fFrozen        = $this->_frozen;        // 3
        $fDspZeros      = 1;                     // 4
        $fDefaultHdr    = 1;                     // 5
        $fArabic        = 0;                     // 6
        $fDspGuts       = $this->_outline_on;    // 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("vvv", $grbit, $rwTop, $colLeft);
        // FIXME !!!
        if ($this->_BIFF_version == 0x0500) {
            $rgbHdr         = 0x00000000; // Row/column heading and gridline color
            $data .= pack("V", $rgbHdr);
        } elseif ($this->_BIFF_version == 0x0600) {
            $rgbHdr       = 0x0040; // Row/column heading and gridline color index
            $zoom_factor_page_break = 0x0000;
            $zoom_factor_normal     = 0x0000;
            $data .= pack("vvvvV", $rgbHdr, 0x0000, $zoom_factor_page_break, $zoom_factor_normal, 0x00000000);
        }
        $this->_append($header.$data);
    }

    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);
    }

    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;
        }
        if (isset($col_array[5])) {
            $level = $col_array[5];
        } else {
            $level = 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

        $level = max(0, min($level, 7));
        $grbit |= $level << 8;

        $header   = pack("vv",     $record, $length);
        $data     = pack("vvvvvC", $colFirst, $colLast, $coldx,
                                   $ixfe, $grbit, $reserved);
        $this->_prepend($header.$data);
    }

    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);
    }

    function _storeMergedCells()
    {
        // if there are no merged cell ranges set, return
        if (count($this->_merged_ranges) == 0) {
            return;
        }
        $record   = 0x00E5;
        $length   = 2 + count($this->_merged_ranges) * 8;

        $header   = pack('vv', $record, $length);
        $data     = pack('v',  count($this->_merged_ranges));
        foreach ($this->_merged_ranges as $range) {
            $data .= pack('vvvv', $range[0], $range[2], $range[1], $range[3]);
        }
        $this->_append($header . $data);
    }

    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);
    }

    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);
    }

    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 && $y != 0) {
                $pnnAct = 0; // Bottom right
            }
            if ($x != 0 && $y == 0) {
                $pnnAct = 1; // Top right
            }
            if ($x == 0 && $y != 0) {
                $pnnAct = 2; // Bottom left
            }
            if ($x == 0 && $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);
    }

    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);
    }

    function _storeHeader()
    {
        $record  = 0x0014;               // Record identifier

        $str      = $this->_header;       // header string
        $cch      = strlen($str);         // Length of header string
        if ($this->_BIFF_version == 0x0600) {
            $encoding = 0x0;                  // TODO: Unicode support
            $length   = 3 + $cch;             // Bytes to follow
        } else {
            $length  = 1 + $cch;             // Bytes to follow
        }

        $header   = pack("vv", $record, $length);
        if ($this->_BIFF_version == 0x0600) {
            $data     = pack("vC",  $cch, $encoding);
        } else {
            $data      = pack("C",  $cch);
        }

        $this->_prepend($header.$data.$str);
    }

    function _storeFooter()
    {
        $record  = 0x0015;               // Record identifier

        $str      = $this->_footer;       // Footer string
        $cch      = strlen($str);         // Length of footer string
        if ($this->_BIFF_version == 0x0600) {
            $encoding = 0x0;                  // TODO: Unicode support
            $length   = 3 + $cch;             // Bytes to follow
        } else {
            $length  = 1 + $cch;
        }

        $header    = pack("vv", $record, $length);
        if ($this->_BIFF_version == 0x0600) {
            $data      = pack("vC",  $cch, $encoding);
        } else {
            $data      = pack("C",  $cch);
        }

        $this->_prepend($header . $data . $str);
    }

    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->_prepend($header.$data);
    }

    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->_prepend($header . $data);
    }

    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->_prepend($header . $data);
    }

    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->_prepend($header . $data);
    }

    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->_prepend($header . $data);
    }

    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->_prepend($header . $data);
    }

    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);
    }

    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);
    }

    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);
    }

    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);
    }

    function _storeGuts()
    {
        $record      = 0x0080;   // Record identifier
        $length      = 0x0008;   // Bytes to follow

        $dxRwGut     = 0x0000;   // Size of row gutter
        $dxColGut    = 0x0000;   // Size of col gutter

        $row_level   = $this->_outline_row_level;
        $col_level   = 0;

        // Calculate the maximum column outline level. The equivalent calculation
        // for the row outline level is carried out in setRow().
        $colcount = count($this->_colinfo);
        for ($i = 0; $i < $colcount; $i++) {
           // Skip cols without outline level info.
           if (count($this->_colinfo[$i]) >= 6) {
              $col_level = max($this->_colinfo[$i][5], $col_level);
           }
        }

        // Set the limits for the outline levels (0 <= x <= 7).
        $col_level = max(0, min($col_level, 7));

        // The displayed level is one greater than the max outline levels
        if ($row_level) {
            $row_level++;
        }
        if ($col_level) {
            $col_level++;
        }

        $header = pack("vv",   $record, $length);
        $data   = pack("vvvv", $dxRwGut, $dxColGut, $row_level, $col_level);

        $this->_prepend($header.$data);
    }


    function _storeWsbool()
    {
        $record      = 0x0081;   // Record identifier
        $length      = 0x0002;   // Bytes to follow
        $grbit       = 0x0000;

        // 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;
        }*/
        // Set the option flags
        $grbit |= 0x0001;                           // Auto page breaks visible
        if ($this->_outline_style) {
            $grbit |= 0x0020; // Auto outline styles
        }
        if ($this->_outline_below) {
            $grbit |= 0x0040; // Outline summary below
        }
        if ($this->_outline_right) {
            $grbit |= 0x0080; // Outline summary right
        }
        if ($this->_fit_page) {
            $grbit |= 0x0100; // Page setup fit to page
        }
        if ($this->_outline_on) {
            $grbit |= 0x0400; // Outline symbols displayed
        }

        $header = pack("vv", $record, $length);
        $data   = pack("v",  $grbit);
        $this->_prepend($header . $data);
    }

    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
        if ($this->_BIFF_version == 0x0600) {
            $length  = 2 + 6*$cbrk;      // Bytes to follow
        } else {
            $length  = 2 + 2*$cbrk;      // Bytes to follow
        }

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

        // Append each page break
        foreach ($breaks as $break) {
            if ($this->_BIFF_version == 0x0600) {
                $data .= pack("vvv", $break, 0x0000, 0x00ff);
            } else {
                $data .= pack("v", $break);
            }
        }

        $this->_prepend($header.$data);
    }


    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
        if ($this->_BIFF_version == 0x0600) {
            $length  = 2 + 6*$cbrk;      // Bytes to follow
        } else {
            $length  = 2 + 2*$cbrk;      // Bytes to follow
        }

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

        // Append each page break
        foreach ($breaks as $break) {
            if ($this->_BIFF_version == 0x0600) {
                $data .= pack("vvv", $break, 0x0000, 0xffff);
            } else {
                $data .= pack("v", $break);
            }
        }

        $this->_prepend($header . $data);
    }

    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);
    }

    function _storePassword()
    {
        // Exit unless sheet protection and password have been specified
        if (($this->_protect == 0) || (!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);
    }


    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);
    }

    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);
    }

    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);
        }
    }

    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);
        }
    }

    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);
    }

    function _processBitmap($bitmap)
    {
        // Open file.
        $bmp_fd = @fopen($bitmap,"rb");
        if (!$bmp_fd) {
            $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) {
            $this->raiseError("$bitmap doesn't contain enough data.\n");
        }

        // The first 2 bytes are used to identify the bitmap.
        $identity = unpack("A2ident", $data);
        if ($identity['ident'] != "BM") {
            $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("Vsa", substr($data, 0, 4));
        $size   = $size_array['sa'];
        $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) {
            $this->raiseError("$bitmap: largest image width supported is 65k.\n");
        }
        if ($height > 0xFFFF) {
            $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
            $this->raiseError("$bitmap isn't a 24bit true color bitmap.\n");
        }
        if ($planes_and_bitcount[1] != 1) {
            $this->raiseError("$bitmap: only 1 plane supported in bitmap image.\n");
        }

        // Read and remove the bitmap compression. Verify compression.
        $compression = unpack("Vcomp", substr($data, 0, 4));
        $data = substr($data, 4);

        //$compression = 0;
        if ($compression['comp'] != 0) {
            $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));
    }

    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);
    }

    function setValidation($row1, $col1, $row2, $col2, &$validator)
    {
        $this->_dv[] = $validator->_getData() .
                       pack("vvvvv", 1, $row1, $row2, $col1, $col2);
    }

    function _storeDataValidity()
    {
        $record      = 0x01b2;      // Record identifier
        $length      = 0x0012;      // Bytes to follow

        $grbit       = 0x0002;      // Prompt box at cell, no cached validity data at DV records
        $horPos      = 0x00000000;  // Horizontal position of prompt box, if fixed position
        $verPos      = 0x00000000;  // Vertical position of prompt box, if fixed position
        $objId       = 0xffffffff;  // Object identifier of drop down arrow object, or -1 if not visible

        $header      = pack('vv', $record, $length);
        $data        = pack('vVVVV', $grbit, $horPos, $verPos, $objId,
                                     count($this->_dv));
        $this->_append($header.$data);

        $record = 0x01be;              // Record identifier
        foreach ($this->_dv as $dv) {
            $length = strlen($dv);      // Bytes to follow
            $header = pack("vv", $record, $length);
            $this->_append($header . $dv);
        }
    }
}
?>