/include/php_writeexcel/class.writeexcel_worksheet.inc.php

<?php

/*
 * Copyleft 2002 Johann Hanne
 *
 * This 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 software 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 software; if not, write to the
 * Free Software Foundation, Inc., 59 Temple Place,
 * Suite 330, Boston, MA  02111-1307 USA
 */

/*
 * This is the Spreadsheet::WriteExcel Perl package ported to PHP
 * Spreadsheet::WriteExcel was written by John McNamara, jmcnamara@cpan.org
 */

require_once "class.writeexcel_biffwriter.inc.php";

class writeexcel_worksheet extends writeexcel_biffwriter {

    var $_name;
    var $_index;
    var $_activesheet;
    var $_firstsheet;
    var $_url_format;
    var $_parser;
    var $_tempdir;

    var $_ext_sheets;
    var $_using_tmpfile;
    var $_filehandle;
    var $_fileclosed;
    var $_offset;
    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 $_title_colmax;
    var $_print_rowmin;
    var $_print_rowmax;
    var $_print_colmin;
    var $_print_colmax;

    var $_print_gridlines;
    var $_screen_gridlines;
    var $_print_headers;

    var $_fit_page;
    var $_fit_width;
    var $_fit_height;

    var $_hbreaks;
    var $_vbreaks;

    var $_protect;
    var $_password;

    var $_col_sizes;
    var $_row_sizes;

    var $_col_formats;
    var $_row_formats;

    var $_zoom;
    var $_print_scale;

    //added to get the tmp file name
    var $_tmp_file_name;
    /*
     * Constructor. Creates a new Worksheet object from a BIFFwriter object
     */
    function writeexcel_worksheet($name, $index, &$activesheet, &$firstsheet,
                                  &$url_format, &$parser, $tempdir) {

        $this->writeexcel_biffwriter();

        $rowmax                   = 65536; // 16384 in Excel 5
        $colmax                   = 256;
        $strmax                   = 20000;

        $this->_name              = $name;
        $this->_index             = $index;
        $this->_activesheet       = &$activesheet;
        $this->_firstsheet        = &$firstsheet;
        $this->_url_format        = &$url_format;
        $this->_parser            = &$parser;
        $this->_tempdir           = $tempdir;

        $this->_ext_sheets        = array();
        $this->_using_tmpfile     = 1;
        $this->_filehandle        = false;
        $this->_fileclosed        = 0;
        $this->_offset            = 0;
        $this->_xls_rowmax        = $rowmax;
        $this->_xls_colmax        = $colmax;
        $this->_xls_strmax        = $strmax;
        $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);
        $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      = false;
        $this->_title_rowmax      = false;
        $this->_title_colmin      = false;
        $this->_title_colmax      = false;
        $this->_print_rowmin      = false;
        $this->_print_rowmax      = false;
        $this->_print_colmin      = false;
        $this->_print_colmax      = false;

        $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          = false;

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

        $this->_col_formats       = array();
        $this->_row_formats       = array();

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

        $this->_initialize();
    }

###############################################################################
#
# _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.
#
function _initialize() {

    # Open tmp file for storing Worksheet data.
    $this->_tmp_file_name = tempnam($this->_tempdir, "php_writeexcel");
    $fh=fopen($this->_tmp_file_name, "w+b");

    if ($fh) {
        # Store filehandle
        $this->_filehandle = $fh;
    } else {
        # If tempfile() failed store data in memory
        $this->_using_tmpfile = 0;

        if ($this->_index == 0) {
            $dir = $this->_tempdir;

//todo            warn "Unable to create temp files in $dir. Refer to set_tempdir()".
//                 " in the Spreadsheet::WriteExcel documentation.\n" ;
        }
    }
}

    /*
     * Add data to the beginning of the workbook (note the reverse order)
     * and to the end of the workbook.
     */
    function _close($sheetnames) {

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

        $this->_store_dimensions();        // Prepend the sheet dimensions
        $this->_store_password();          // Prepend the sheet password
        $this->_store_protect();           // Prepend the sheet protection
        $this->_store_setup();             // Prepend the page setup
        $this->_store_margin_bottom();     // Prepend the bottom margin
        $this->_store_margin_top();        // Prepend the top margin
        $this->_store_margin_right();      // Prepend the right margin
        $this->_store_margin_left();       // Prepend the left margin
        $this->_store_vcenter();           // Prepend the page vertical
                                           // centering
        $this->_store_hcenter();           // Prepend the page horizontal
                                           // centering
        $this->_store_footer();            // Prepend the page footer
        $this->_store_header();            // Prepend the page header
        $this->_store_vbreak();            // Prepend the vertical page breaks
        $this->_store_hbreak();            // Prepend the horizontal
                                           // page breaks
        $this->_store_wsbool();            // Prepend WSBOOL
        $this->_store_gridset();           // Prepend GRIDSET
        $this->_store_print_gridlines();   // Prepend PRINTGRIDLINES
        $this->_store_print_headers();     // Prepend PRINTHEADERS

        // Prepend EXTERNSHEET references
        $num_sheets = sizeof($sheetnames);
        for ($i = $num_sheets; $i > 0; $i--) {
            $sheetname = $sheetnames[$i-1];
            $this->_store_externsheet($sheetname);
        }

        $this->_store_externcount($num_sheets);   // Prepend the EXTERNCOUNT
                                                  // of external references.

        // Prepend the COLINFO records if they exist
        if (sizeof($this->_colinfo)>0){
            while (sizeof($this->_colinfo)>0) {
                $arrayref = array_pop ($this->_colinfo);
                $this->_store_colinfo($arrayref);
            }
            $this->_store_defcol();
        }

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

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

        // Append
        $this->_store_window2();
        $this->_store_zoom();

        if (sizeof($this->_panes)>0) {
            $this->_store_panes($this->_panes);
        }

        $this->_store_selection($this->_selection);
        $this->_store_eof();
    }

    /*
     * Retrieve the worksheet name.
     */
    function get_name() {
        return $this->_name;
    }

###############################################################################
#
# get_data().
#
# Retrieves data from memory in one chunk, or from disk in $buffer
# sized chunks.
#
function get_data() {

    $buffer = 4096;

    # Return data stored in memory
    if ($this->_data!==false) {
        $tmp           = $this->_data;
        $this->_data=false;
        $fh         = $this->_filehandle;
        if ($this->_using_tmpfile) {
            fseek($fh, 0, SEEK_SET);
        }

        if ($this->_debug) {
            print "*** worksheet::get_data() called (1):";
            for ($c=0;$c<strlen($tmp);$c++) {
                if ($c%16==0) {
                    print "\n";
                }
                printf("%02X ", ord($tmp[$c]));
            }
            print "\n";
        }

        return $tmp;
    }

    # Return data stored on disk
    if ($this->_using_tmpfile) {
        if ($tmp=fread($this->_filehandle, $buffer)) {

            if ($this->_debug) {
                print "*** worksheet::get_data() called (2):";
                for ($c=0;$c<strlen($tmp);$c++) {
                    if ($c%16==0) {
                        print "\n";
                    }
                    printf("%02X ", ord($tmp[$c]));
                }
                print "\n";
            }

            return $tmp;
        }
    }

    # No data to return
    return false;
}

    /*
     * Set this worksheet as a selected worksheet, i.e. the worksheet has
     * its tab highlighted.
     */
    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.
     */
    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.
     */
    function set_first_sheet() {
        $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.
     */
    function protect($password) {
        $this->_protect   = 1;
        $this->_password  = $this->_encode_password($password);
    }

###############################################################################
#
# set_column($firstcol, $lastcol, $width, $format, $hidden)
#
# Set the width of a single column or a range of column.
# See also: _store_colinfo
#
function set_column() {

    $_=func_get_args();

    $cell = $_[0];

    # Check for a cell reference in A1 notation and substitute row and column
    if (preg_match('/^\D/', $cell)) {
        $_ = $this->_substitute_cellref($_);
    }

    array_push($this->_colinfo, $_);

    # Store the col sizes for use when calculating image vertices taking
    # hidden columns into account. Also store the column formats.
    #
    if (sizeof($_)<3) {
        # Ensure at least $firstcol, $lastcol and $width
        return;
    }

    $width  = $_[4] ? 0 : $_[2]; # Set width to zero if column is hidden
    $format = $_[3];

    list($firstcol, $lastcol) = $_;

    for ($col=$firstcol;$col<=$lastcol;$col++) {
        $this->_col_sizes[$col]   = $width;
        if ($format) {
            $this->_col_formats[$col] = $format;
        }
    }
}

###############################################################################
#
# set_selection()
#
# Set which cell or cells are selected in a worksheet: see also the
# function _store_selection
#
function set_selection() {

    $_=func_get_args();

    # Check for a cell reference in A1 notation and substitute row and column
    if (preg_match('/^\D/', $_[0])) {
        $_ = $this->_substitute_cellref($_);
    }

    $this->_selection = $_;
}

###############################################################################
#
# freeze_panes()
#
# Set panes and mark them as frozen. See also _store_panes().
#
function freeze_panes() {

    $_=func_get_args();

    # Check for a cell reference in A1 notation and substitute row and column
    if (preg_match('/^\D/', $_[0])) {
        $_ = $this->_substitute_cellref($_);
    }

    $this->_frozen = 1;
    $this->_panes  = $_;
}

###############################################################################
#
# thaw_panes()
#
# Set panes and mark them as unfrozen. See also _store_panes().
#
function thaw_panes() {

    $_=func_get_args();

    $this->_frozen = 0;
    $this->_panes  = $_;
}

    /*
     * Set the page orientation as portrait.
     */
    function set_portrait() {
        $this->_orientation = 1;
    }

    /*
     * Set the page orientation as landscape.
     */
    function set_landscape() {
        $this->_orientation = 0;
    }

    /*
     * Set the paper type. Ex. 1 = US Letter, 9 = A4
     */
    function set_paper($type) {
        $this->_paper_size = $type;
    }

    /*
     * Set the page header caption and optional margin.
     */
    function set_header($string, $margin) {

        if (strlen($string) >= 255) {
            trigger_error("Header string must be less than 255 characters",
                          E_USER_WARNING);
            return;
        }

        $this->_header      = $string;
        $this->_margin_head = $margin;
    }

    /*
     * Set the page footer caption and optional margin.
     */
    function set_footer($string, $margin) {
        if (strlen($string) >= 255) {
            trigger_error("Footer string must be less than 255 characters",
                          E_USER_WARNING);
            return;
        }

        $this->_footer      = $string;
        $this->_margin_foot = $margin;
    }

    /*
     * Center the page horizontally.
     */
    function center_horizontally($hcenter=1) {
        $this->_hcenter = $hcenter;
    }

    /*
     * Center the page horizontally.
     */
    function center_vertically($vcenter=1) {
        $this->_vcenter = $vcenter;
    }

    /*
     * Set all the page margins to the same value in inches.
     */
    function set_margins($margin) {
        $this->set_margin_left($margin);
        $this->set_margin_right($margin);
        $this->set_margin_top($margin);
        $this->set_margin_bottom($margin);
    }

    /*
     * Set the left and right margins to the same value in inches.
     */
    function set_margins_LR($margin) {
        $this->set_margin_left($margin);
        $this->set_margin_right($margin);
    }

    /*
     * Set the top and bottom margins to the same value in inches.
     */
    function set_margins_TB($margin) {
        $this->set_margin_top($margin);
        $this->set_margin_bottom($margin);
    }

    /*
     * Set the left margin in inches.
     */
    function set_margin_left($margin=0.75) {
        $this->_margin_left = $margin;
    }

    /*
     * Set the right margin in inches.
     */
    function set_margin_right($margin=0.75) {
        $this->_margin_right = $margin;
    }

    /*
     * Set the top margin in inches.
     */
    function set_margin_top($margin=1.00) {
        $this->_margin_top = $margin;
    }

    /*
     * Set the bottom margin in inches.
     */
    function set_margin_bottom($margin=1.00) {
        $this->_margin_bottom = $margin;
    }

###############################################################################
#
# repeat_rows($first_row, $last_row)
#
# Set the rows to repeat at the top of each printed page. See also the
# _store_name_xxxx() methods in Workbook.pm.
#
function repeat_rows() {

    $_=func_get_args();

    $this->_title_rowmin  = $_[0];
    $this->_title_rowmax  = isset($_[1]) ? $_[1] : $_[0]; # Second row is optional
}

###############################################################################
#
# repeat_columns($first_col, $last_col)
#
# Set the columns to repeat at the left hand side of each printed page.
# See also the _store_names() methods in Workbook.pm.
#
function repeat_columns() {

    $_=func_get_args();

    # Check for a cell reference in A1 notation and substitute row and column
    if (preg_match('/^\D/', $_[0])) {
        $_ = $this->_substitute_cellref($_);
    }

    $this->_title_colmin  = $_[0];
    $this->_title_colmax  = isset($_[1]) ? $_[1] : $_[0]; # Second col is optional
}

###############################################################################
#
# print_area($first_row, $first_col, $last_row, $last_col)
#
# Set the area of each worksheet that will be printed. See also the
# _store_names() methods in Workbook.pm.
#
function print_area() {

    $_=func_get_args();

    # Check for a cell reference in A1 notation and substitute row and column
    if (preg_match('/^\D/', $_[0])) {
        $_ = $this->_substitute_cellref($_);
    }

    if (sizeof($_) != 4) {
        # Require 4 parameters
        return;
    }

    $this->_print_rowmin = $_[0];
    $this->_print_colmin = $_[1];
    $this->_print_rowmax = $_[2];
    $this->_print_colmax = $_[3];
}

    /*
     * Set the option to hide gridlines on the screen and the printed page.
     * There are two ways of doing this in the Excel BIFF format: The first
     * is by setting the DspGrid field of the WINDOW2 record, this turns off
     * the screen and subsequently the print gridline. The second method is
     * to via the PRINTGRIDLINES and GRIDSET records, this turns off the
     * printed gridlines only. The first method is probably sufficient for
     * most cases. The second method is supported for backwards compatibility.
     */
    function hide_gridlines($option=1) {
        if ($option == 0) {
            $this->_print_gridlines  = 1; # 1 = display, 0 = hide
            $this->_screen_gridlines = 1;
        } elseif ($option == 1) {
            $this->_print_gridlines  = 0;
            $this->_screen_gridlines = 1;
        } else {
            $this->_print_gridlines  = 0;
            $this->_screen_gridlines = 0;
        }
    }

    /*
     * Set the option to print the row and column headers on the printed page.
     * See also the _store_print_headers() method below.
     */
    function print_row_col_headers($headers=1) {
        $this->_print_headers = $headers;
    }

    /*
     * Store the vertical and horizontal number of pages that will define
     * the maximum area printed. See also _store_setup() and _store_wsbool()
     * below.
     */
    function fit_to_pages($width, $height) {
        $this->_fit_page   = 1;
        $this->_fit_width  = $width;
        $this->_fit_height = $height;
    }

    /*
     * Store the horizontal page breaks on a worksheet.
     */
    function set_h_pagebreaks($breaks) {
        $this->_hbreaks=array_merge($this->_hbreaks, $breaks);
    }

    /*
     * Store the vertical page breaks on a worksheet.
     */
    function set_v_pagebreaks($breaks) {
        $this->_vbreaks=array_merge($this->_vbreaks, $breaks);
    }

    /*
     * Set the worksheet zoom factor.
     */
    function set_zoom($scale=100) {
        // Confine the scale to Excel's range
        if ($scale < 10 || $scale > 400) {
            trigger_error("Zoom factor $scale outside range: ".
                          "10 <= zoom <= 400", E_USER_WARNING);
            $scale = 100;
        }

        $this->_zoom = $scale;
    }

    /*
     * Set the scale factor for the printed page.
     */
    function set_print_scale($scale=100) {
        // Confine the scale to Excel's range
        if ($scale < 10 || $scale > 400) {
            trigger_error("Print scale $scale outside range: ".
                          "10 <= zoom <= 400", E_USER_WARNING);
            $scale = 100;
        }

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

        $this->_print_scale = $scale;
    }

###############################################################################
#
# write($row, $col, $token, $format)
#
# Parse $token call appropriate write method. $row and $column are zero
# indexed. $format is optional.
#
# Returns: return value of called subroutine
#
function write() {

    $_=func_get_args();

    # Check for a cell reference in A1 notation and substitute row and column
    if (preg_match('/^\D/', $_[0])) {
        $_ = $this->_substitute_cellref($_);
    }

    $token = $_[2];

    # Match an array ref.
    if (is_array($token)) {
        return call_user_method_array('write_row', $this, $_);
    }

    # Match number
    if (preg_match('/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/', $token)) {
        return call_user_method_array('write_number', $this, $_);
    }
    # Match http, https or ftp URL
    elseif (preg_match('|^[fh]tt?ps?://|', $token)) {
        return call_user_method_array('write_url', $this, $_);
    }
    # Match mailto:
    elseif (preg_match('/^mailto:/', $token)) {
        return call_user_method_array('write_url', $this, $_);
    }
    # Match internal or external sheet link
    elseif (preg_match('[^(?:in|ex)ternal:]', $token)) {
        return call_user_method_array('write_url', $this, $_);
    }
    # Match formula
    elseif (preg_match('/^=/', $token)) {
        return call_user_method_array('write_formula', $this, $_);
    }
    # Match blank
    elseif ($token == '') {
        array_splice($_, 2, 1); # remove the empty string from the parameter list
        return call_user_method_array('write_blank', $this, $_);
    }
    # Default: match string
    else {
        return call_user_method_array('write_string', $this, $_);
    }
}

###############################################################################
#
# write_row($row, $col, $array_ref, $format)
#
# Write a row of data starting from ($row, $col). Call write_col() if any of
# the elements of the array ref are in turn array refs. This allows the writing
# of 1D or 2D arrays of data in one go.
#
# Returns: the first encountered error value or zero for no errors
#
function write_row() {

    $_=func_get_args();

    # Check for a cell reference in A1 notation and substitute row and column
    if (preg_match('/^\D/', $_[0])) {
        $_ = $this->_substitute_cellref($_);
    }

    # Catch non array refs passed by user.
    if (!is_array($_[2])) {
        trigger_error("Not an array ref in call to write_row()!", E_USER_ERROR);
    }

    list($row, $col, $tokens)=array_splice($_, 0, 3);
    $options = $_[0];
    $error   = 0;

    foreach ($tokens as $token) {

        # Check for nested arrays
        if (is_array($token)) {
            $ret = $this->write_col($row, $col, $token, $options);
        } else {
            $ret = $this->write    ($row, $col, $token, $options);
        }

        # Return only the first error encountered, if any.
        $error = $error || $ret;
        $col++;
    }

    return $error;
}

###############################################################################
#
# _XF()
#
# Returns an index to the XF record in the workbook.
# TODO
#
# Note: this is a function, not a method.
#
function _XF($row=false, $col=false, $format=false) {

    if ($format) {
        return $format->get_xf_index();
    } elseif (isset($this->_row_formats[$row])) {
        return $this->_row_formats[$row]->get_xf_index();
    } elseif (isset($this->_col_formats[$col])) {
        return $this->_col_formats[$col]->get_xf_index();
    } else {
        return 0x0F;
    }
}

###############################################################################
#
# write_col($row, $col, $array_ref, $format)
#
# Write a column of data starting from ($row, $col). Call write_row() if any of
# the elements of the array ref are in turn array refs. This allows the writing
# of 1D or 2D arrays of data in one go.
#
# Returns: the first encountered error value or zero for no errors
#
function write_col() {

    $_=func_get_args();

    # Check for a cell reference in A1 notation and substitute row and column
    if (preg_match('/^\D/', $_[0])) {
        $_ = $this->_substitute_cellref($_);
    }

    # Catch non array refs passed by user.
    if (!is_array($_[2])) {
        trigger_error("Not an array ref in call to write_row()!", E_USER_ERROR);
    }

    $row     = array_shift($_);
    $col     = array_shift($_);
    $tokens  = array_shift($_);
    $options = $_;

    $error   = 0;

    foreach ($tokens as $token) {

        # write() will deal with any nested arrays
        $ret = $this->write($row, $col, $token, $options);

        # Return only the first error encountered, if any.
        $error = $error || $ret;
        $row++;
    }

    return $error;
}

###############################################################################
###############################################################################
#
# Internal methods
#

###############################################################################
#
# _append(), overloaded.
#
# Store Worksheet data in memory using the base class _append() or to a
# temporary file, the default.
#
function _append($data) {

    if (func_num_args()>1) {
        trigger_error("writeexcel_worksheet::_append() ".
                      "called with more than one argument", E_USER_ERROR);
    }

    if ($this->_using_tmpfile) {

        if ($this->_debug) {
            print "worksheet::_append() called:";
            for ($c=0;$c<strlen($data);$c++) {
                if ($c%16==0) {
                    print "\n";
                }
                printf("%02X ", ord($data[$c]));
            }
            print "\n";
        }

        # Add CONTINUE records if necessary
        if (strlen($data) > $this->_limit) {
            $data = $this->_add_continue($data);
        }

        fputs($this->_filehandle, $data);
        $this->_datasize += strlen($data);
    } else {
        parent::_append($data);
    }
}

###############################################################################
#
# _substitute_cellref()
#
# 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").
#
// Exactly one array must be passed!
function _substitute_cellref($_) {
    $cell = strtoupper(array_shift($_));

    # Convert a column range: 'A:A' or 'B:G'
    if (preg_match('/([A-I]?[A-Z]):([A-I]?[A-Z])/', $cell, $reg)) {
        list($dummy, $col1) =  $this->_cell_to_rowcol($reg[1] .'1'); # Add a dummy row
        list($dummy, $col2) =  $this->_cell_to_rowcol($reg[2] .'1'); # Add a dummy row
        return array_merge(array($col1, $col2), $_);
    }

    # Convert a cell range: 'A1:B7'
    if (preg_match('/\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/', $cell, $reg)) {
        list($row1, $col1) =  $this->_cell_to_rowcol($reg[1]);
        list($row2, $col2) =  $this->_cell_to_rowcol($reg[2]);
        return array_merge(array($row1, $col1, $row2, $col2), $_);
    }

    # Convert a cell reference: 'A1' or 'AD2000'
    if (preg_match('/\$?([A-I]?[A-Z]\$?\d+)/', $cell, $reg)) {
        list($row1, $col1) =  $this->_cell_to_rowcol($reg[1]);
        return array_merge(array($row1, $col1), $_);

    }

    trigger_error("Unknown cell reference $cell", E_USER_ERROR);
}

###############################################################################
#
# _cell_to_rowcol($cell_ref)
#
# Convert an Excel cell reference in A1 notation to a zero based row and column
# reference; converts C1 to (0, 2).
#
# Returns: row, column
#
# TODO use functions in Utility.pm
#
function _cell_to_rowcol($cell) {

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

    $col     = $reg[1];
    $row     = $reg[2];

    # Convert base26 column string to number
    # All your Base are belong to us.
    $chars = preg_split('//', $col, -1, PREG_SPLIT_NO_EMPTY);
    $expn  = 0;
    $col      = 0;

    while (sizeof($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);
}

    /*
     * This is an internal method that is used to filter elements of the
     * array of pagebreaks used in the _store_hbreak() and _store_vbreak()
     * methods. It:
     *   1. Removes duplicate entries from the list.
     *   2. Sorts the list.
     *   3. Removes 0 from the list if present.
     */
    function _sort_pagebreaks($breaks) {
        // Hash slice to remove duplicates
        foreach ($breaks as $break) {
            $hash["$break"]=1;
        }

        // Numerical sort
        $breaks=array_keys($hash);
        sort($breaks, SORT_NUMERIC);

        // Remove zero
        if ($breaks[0] == 0) {
            array_shift($breaks);
        }

        // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
        // It is slightly higher in Excel 97/200, approx. 1026
        if (sizeof($breaks) > 1000) {
            array_splice($breaks, 1000);
        }

        return $breaks;
    }

    /*
     * Based on the algorithm provided by Daniel Rentz of OpenOffice.
     */
    function _encode_password($plaintext) {
        $chars=preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY);
        $count=sizeof($chars);

        for ($c=0;$c<sizeof($chars);$c++) {
            $char=&$chars[$c];
            $char    = ord($char) << ++$i;
            $low_15  = $char & 0x7fff;
            $high_15 = $char & 0x7fff << 15;
            $high_15 = $high_15 >> 15;
            $char    = $low_15 | $high_15;
        }

        $password = 0x0000;

        foreach ($chars as $char) {
            $password ^= $char;
        }

        $password ^= $count;
        $password ^= 0xCE4B;

        return $password;
    }

###############################################################################
###############################################################################
#
# BIFF RECORDS
#

###############################################################################
#
# write_number($row, $col, $num, $format)
#
# 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
#         -1 : insufficient number of arguments
#         -2 : row or column out of range
#
function write_number() {

    $_=func_get_args();

    # Check for a cell reference in A1 notation and substitute row and column
    if (preg_match('/^\D/', $_[0])) {
        $_ = $this->_substitute_cellref($_);
    }

    # Check the number of args
    if (sizeof($_) < 3) {
        return -1;
    }

    $record  = 0x0203;                        # Record identifier
    $length  = 0x000E;                        # Number of bytes to follow

    $row     = $_[0];                         # Zero indexed row
    $col     = $_[1];                         # Zero indexed column
    $num     = $_[2];
//!!!
    $xf      = $this->_XF($row, $col, $_[3]); # 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) {
//TODO
        $xl_double = strrev($xl_double);
    }

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

    return 0;
}

###############################################################################
#
# write_string ($row, $col, $string, $format)
#
# 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
#         -1 : insufficient number of arguments
#         -2 : row or column out of range
#         -3 : long string truncated to 255 chars
#
function write_string() {

    $_=func_get_args();

    # Check for a cell reference in A1 notation and substitute row and column
    if (preg_match('/^\D/', $_[0])) {
        $_ = $this->_substitute_cellref($_);
    }

    # Check the number of args
    if (sizeof($_) < 3) {
        return -1;
    }

    $record  = 0x0204;                        # Record identifier
    $length  = 0x0008 + strlen($_[2]);        # Bytes to follow

    $row     = $_[0];                         # Zero indexed row
    $col     = $_[1];                         # Zero indexed column
    $strlen  = strlen($_[2]);
    $str     = $_[2];
    $xf      = $this->_XF($row, $col, $_[3]); # 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;
}

###############################################################################
#
# write_blank($row, $col, $format)
#
# 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. This is mainly an optimisation
# for the write_row() and write_col() methods.
#
# Returns  0 : normal termination (including no format)
#         -1 : insufficient number of arguments
#         -2 : row or column out of range
#
function write_blank() {

    $_=func_get_args();

    # Check for a cell reference in A1 notation and substitute row and column
    if (preg_match('/^\D/', $_[0])) {
        $_ = $this->_substitute_cellref($_);
    }

    # Check the number of args
    if (sizeof($_) < 2) {
        return -1;
    }

    # Don't write a blank cell unless it has a format
    if (!isset($_[2])) {
        return 0;
    }

    $record  = 0x0201;                        # Record identifier
    $length  = 0x0006;                        # Number of bytes to follow

    $row     = $_[0];                         # Zero indexed row
    $col     = $_[1];                         # Zero indexed column
    $xf      = $this->_XF($row, $col, $_[2]); # 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_formula($row, $col, $formula, $format)
#
# Write a formula to the specified row and column (zero indexed).
# The textual representation of the formula is passed to the parser in
# Formula.pm which returns a packed binary string.
#
# $format is optional.
#
# Returns  0 : normal termination
#         -1 : insufficient number of arguments
#         -2 : row or column out of range
#
function write_formula() {

    $_=func_get_args();

    # Check for a cell reference in A1 notation and substitute row and column
    if (preg_match('/^\D/', $_[0])) {
        $_ = $this->_substitute_cellref($_);
    }

    # Check the number of args
    if (sizeof($_) < 3) {
        return -1;
    }

    $record    = 0x0006;     # Record identifier
    $length=0;                 # Bytes to follow

    $row       = $_[0];      # Zero indexed row
    $col       = $_[1];      # Zero indexed column
    $formula   = $_[2];      # The formula text string

    # 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($row, $col, $_[3]); # 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 = sign at the beginning of the formula string
    $formula = preg_replace('/^=/', "", $formula);

    # Parse the formula using the parser in Formula.pm
    $parser =& $this->_parser;
    $formula   = $parser->parse_formula($formula);

    $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_url($row, $col, $url, $string, $format)
#
# 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
# write_string() method. Therefore the 255 characters string limit applies.
# $string and $format are optional and their order is interchangeable.
#
# The hyperlink can be to a http, ftp, mail, internal sheet, or external
# directory url.
#
# Returns  0 : normal termination
#         -1 : insufficient number of arguments
#         -2 : row or column out of range
#         -3 : long string truncated to 255 chars
#
function write_url() {

    $_=func_get_args();

    # Check for a cell reference in A1 notation and substitute row and column
    if (preg_match('/^\D/', $_[0])) {
        $_ = $this->_substitute_cellref($_);
    }

    # Check the number of args
    if (sizeof($_) < 3) {
        return -1;
    }

    # Add start row and col to arg list
    return call_user_method_array('write_url_range', $this,
                                  array_merge(array($_[0], $_[1]), $_));
}

###############################################################################
#
# write_url_range($row1, $col1, $row2, $col2, $url, $string, $format)
#
# This is the more general form of write_url(). 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').
#
# See also write_url() above for a general description and return values.
#
function write_url_range() {

    $_=func_get_args();

    # Check for a cell reference in A1 notation and substitute row and column
    if (preg_match('/^\D/', $_[0])) {
        $_ = $this->_substitute_cellref($_);
    }

    # Check the number of args
    if (sizeof($_) < 5) {
        return -1;
    }

    # Reverse the order of $string and $format if necessary.
//TODO    ($_[5], $_[6]) = ($_[6], $_[5]) if (ref $_[5]);

    $url = $_[4];

    # Check for internal/external sheet links or default to web link
    if (preg_match('[^internal:]', $url)) {
        return call_user_method_array('_write_url_internal', $this, $_);
    }

    if (preg_match('[^external:]', $url)) {
        return call_user_method_array('_write_url_external', $this, $_);
    }

    return call_user_method_array('_write_url_web', $this, $_);
}

###############################################################################
#
# _write_url_web($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.
#
# See also write_url() above for a general description and return values.
#
function _write_url_web() {

    $_=func_get_args();

    $record      = 0x01B8;                       # Record identifier
    $length      = 0x00000;                      # Bytes to follow

    $row1        = $_[0];                        # Start row
    $col1        = $_[1];                        # Start column
    $row2        = $_[2];                        # End row
    $col2        = $_[3];                        # End column
    $url         = $_[4];                        # URL string
    if (isset($_[5])) {
        $str         = $_[5];                        # Alternative label
    }
    $xf          = $_[6] ? $_[6] : $this->_url_format;  # The cell format

    # Write the visible label using the write_string() method.
    if(!isset($str)) {
        $str            = $url;
    }

    $str_error   = $this->write_string($row1, $col1, $str, $xf);

    if ($str_error == -2) {
        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;
}

###############################################################################
#
# _write_url_internal($row1, $col1, $row2, $col2, $url, $string, $format)
#
# Used to write internal reference hyperlinks such as "Sheet1!A1".
#
# See also write_url() above for a general description and return values.
#
function _write_url_internal() {

    $_=func_get_args();

    $record      = 0x01B8;                       # Record identifier
    $length      = 0x00000;                      # Bytes to follow

    $row1        = $_[0];                        # Start row
    $col1        = $_[1];                        # Start column
    $row2        = $_[2];                        # End row
    $col2        = $_[3];                        # End column
    $url         = $_[4];                        # URL string
    if (isset($_[5])) {
        $str         = $_[5];                        # Alternative label
    }
    $xf          = $_[6] ? $_[6] : $this->_url_format;  # The cell format

    # Strip URL type
    $url = preg_replace('s[^internal:]', '', $url);

    # Write the visible label
    if (!isset($str)) {
        $str = $url;
    }
    $str_error   = $this->write_string($row1, $col1, $str, $xf);

    if ($str_error == -2) {
        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", int(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_url_external($row1, $col1, $row2, $col2, $url, $string, $format)
#
# 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.
#
# See also write_url() above for a general description and return values.
#
function _write_url_external() {

    $_=func_get_args();

    # Network drives are different. We will handle them separately
    # MS/Novell network drives and shares start with \\
    if (preg_match('[^external:\\\\]', $_[4])) {
        return call_user_method_array('_write_url_external_net', $this, $_);
    }

    $record      = 0x01B8;                       # Record identifier
    $length      = 0x00000;                      # Bytes to follow

    $row1        = $_[0];                        # Start row
    $col1        = $_[1];                        # Start column
    $row2        = $_[2];                        # End row
    $col2        = $_[3];                        # End column
    $url         = $_[4];                        # URL string
    if (isset($_[5])) {
        $str         = $_[5];                        # Alternative label
    }
    $xf          = $_[6] ? $_[6] : $this->_url_format;  # The cell 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 (!isset($str)) {
        $str = preg_replace('[\#]', ' - ', $url);
    }
    $str_error   = $this->write_string($row1, $col1, $str, $xf);
    if ($str_error == -2) {
        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) = preg_split('/\#/', $url);
    $link_type           = 0x01 | $absolute;

//!!!
    if (isset($sheet)) {
        $link_type |= 0x08;
        $sheet_len  = pack("V", length($sheet) + 0x01);
        $sheet      = join("\0", split('', $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)
/* TODO
    $up_count    = 0;
    $up_count++       while $dir_long =~ s[^\.\.\\][];
    $up_count       = pack("v", $up_count);
*/

    # Store the short dos dir name (null terminated)
    $dir_short   = $dir_long . "\0";

    # Store the long dir name as a wchar string (non-null terminated)
    $dir_long       = join("\0", preg_split('', $dir_long, -1, PREG_SPLIT_NO_EMPTY));
    $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;
}

###############################################################################
#
# write_url_xxx($row1, $col1, $row2, $col2, $url, $string, $format)
#
# Write links to external MS/Novell network drives and shares such as
# '//NETWORK/share/foo.xls' and '//NETWORK/share/foo.xls#Sheet1!A1'.
#
# See also write_url() above for a general description and return values.
#
function _write_url_external_net() {

    $_=func_get_args();

    $record      = 0x01B8;                       # Record identifier
    $length      = 0x00000;                      # Bytes to follow

    $row1        = $_[0];                        # Start row
    $col1        = $_[1];                        # Start column
    $row2        = $_[2];                        # End row
    $col2        = $_[3];                        # End column
    $url         = $_[4];                        # URL string
    if(isset($_[5])) {
         $str         = $_[5];                        # Alternative label
    }
    $xf          = $_[6] ? $_[6] : $this->_url_format;  # The cell format

    # Strip URL type and change Unix dir separator to Dos style (if needed)
    #
    $url            = preg_replace('[^external:]', "", $url);
    $url            = preg_replace('[/]', "\\");

    # Write the visible label
    if (!isset($str)) {
        $str = preg_replace('[\#]', " - ", $url);
    }

    $str_error   = $this->write_string($row1, $col1, $str, $xf);
    if ($str_error == -2) {
        return $str_error;
    }

    # 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) = preg_split('\#', $url);
    $link_type           = 0x0103; # Always absolute

//!!!
    if (isset($sheet)) {
        $link_type |= 0x08;
        $sheet_len  = pack("V", strlen($sheet) + 0x01);
        $sheet      = join("\0", preg_split("''", $sheet, -1, PREG_SPLIT_NO_EMPTY));
        $sheet     .= "\0\0\0";
    } else {
        $sheet_len   = '';
        $sheet       = '';
    }

    # Pack the link type
    $link_type      = pack("V", $link_type);

    # Make the string null terminated
    $dir_long       = $dir_long . "\0";

    # Pack the lengths of the dir string
    $dir_long_len  = pack("V", strlen($dir_long));

    # Store the long dir name as a wchar string (non-null terminated)
    $dir_long       = join("\0", preg_split("''", $dir_long, -1, PREG_SPLIT_NO_EMPTY));
    $dir_long       = $dir_long . "\0";

    # Pack the undocumented part of the hyperlink stream
    $unknown1    = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000');

    # Pack the main data stream
    $data        = pack("vvvv", $row1, $row2, $col1, $col2) .
                      $unknown1     .
                      $link_type    .
                      $dir_long_len .
                      $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;
}

###############################################################################
#
# set_row($row, $height, $XF)
#
# This method is used to set the height and XF format for a row.
# Writes the  BIFF record ROW.
#
function set_row() {

    $_=func_get_args();

    $record      = 0x0208;               # Record identifier
    $length      = 0x0010;               # Number of bytes to follow

    $rw          = $_[0];                # Row Number
    $colMic      = 0x0000;               # First defined column
    $colMac      = 0x0000;               # Last defined column
    //$miyRw;                              # Row height
    $irwMac      = 0x0000;               # Used by Excel to optimise loading
    $reserved    = 0x0000;               # Reserved
    $grbit       = 0x01C0;               # Option flags. (monkey) see $1 do
    //$ixfe;                               # XF index
    if (isset($_[2])) {
        $format      = $_[2];                # Format object
    }

    # Check for a format object
    if (isset($_[2])) {
        $ixfe = $format->get_xf_index();
    } else {
        $ixfe = 0x0F;
    }

    # Use set_row($row, undef, $XF) to set XF without setting height
    if (isset($_[1])) {
        $miyRw = $_[1] *20;
    } else {
        $miyRw = 0xff;
    }

    $header   = pack("vv",       $record, $length);
    $data     = pack("vvvvvvvv", $rw, $colMic, $colMac, $miyRw,
                                 $irwMac,$reserved, $grbit, $ixfe);

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

    # Store the row sizes for use when calculating image vertices.
    # Also store the column formats.
    #
    # Ensure at least $row and $height
    if (sizeof($_) < 2) {
        return;
    }

    $this->_row_sizes[$_[0]]  = $_[1];
    if (isset($_[2])) {
        $this->_row_formats[$_[0]] = $_[2];
    }
}

    /*
     * Writes Excel DIMENSIONS to define the area in which there is data.
     */
    function _store_dimensions() {
        $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.
     */
    function _store_window2() {
        $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       = $this->_screen_gridlines;   // 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.
     */
    function _store_defcol() {
        $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);
    }

###############################################################################
#
# _store_colinfo($firstcol, $lastcol, $width, $format, $hidden)
#
# Write BIFF record COLINFO to define column widths
#
# Note: The SDK says the record length is 0x0B but Excel writes a 0x0C
# length record.
#
function _store_colinfo($_) {

    $record   = 0x007D;          # Record identifier
    $length   = 0x000B;          # Number of bytes to follow

    $colFirst = $_[0] ? $_[0] : 0;      # First formatted column
    $colLast  = $_[1] ? $_[1] : 0;      # Last formatted column
    $coldx    = $_[2] ? $_[2] : 8.43;   # Col width, 8.43 is Excel default

    $coldx       += 0.72;           # Fudge. Excel subtracts 0.72 !?
    $coldx       *= 256;            # Convert to units of 1/256 of a char

    //$ixfe;                       # XF index
    $grbit    = $_[4] || 0;      # Option flags
    $reserved = 0x00;            # Reserved
    $format   = $_[3];           # Format object

    # Check for a format object
    if (isset($_[3])) {
        $ixfe = $format->get_xf_index();
    } else {
        $ixfe = 0x0F;
    }

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

###############################################################################
#
# _store_selection($first_row, $first_col, $last_row, $last_col)
#
# Write BIFF record SELECTION.
#
function _store_selection($_) {

    $record   = 0x001D;                  # Record identifier
    $length   = 0x000F;                  # Number of bytes to follow

    $pnn      = $this->_active_pane;     # Pane position
    $rwAct    = $_[0];                   # Active row
    $colAct   = $_[1];                   # Active column
    $irefAct  = 0;                       # Active cell ref
    $cref     = 1;                       # Number of refs

    $rwFirst  = $_[0];                   # First row in reference
    $colFirst = $_[1];                   # First col in reference
    $rwLast   = $_[2] ? $_[2] : $rwFirst;       # Last  row in reference
    $colLast  = $_[3] ? $_[3] : $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.
     */
    function _store_externcount($cxals) {
        // $cxals   Number of external references

        $record   = 0x0016;   // Record identifier
        $length   = 0x0002;   // Number of bytes to follow

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

        $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.
     */
    function _store_externsheet($sheetname) {
        $record    = 0x0017;         # Record identifier
        // $length   Number of bytes to follow

        // $cch      Length of sheet name
        // $rgch     Filename encoding

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

###############################################################################
#
# _store_panes()
#
#
# Writes the Excel BIFF PANE record.
# The panes can either be frozen or thawed (unfrozen).
# Frozen panes are specified in terms of a integer number of rows and columns.
# Thawed panes are specified in terms of Excel's units for rows and columns.
#
function _store_panes() {

    $_=func_get_args();

    $record  = 0x0041;       # Record identifier
    $length  = 0x000A;       # Number of bytes to follow

    $y       = $_[0] || 0;   # Vertical split position
    $x       = $_[1] || 0;   # Horizontal split position
    if (isset($_[2])) {
        $rwTop   = $_[2];        # Top row visible
    }
    if (isset($_[3])) {
        $colLeft = $_[3];        # Leftmost column visible
    }
    if (isset($_[4])) {
        $pnnAct  = $_[4];        # Active pane
    }

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

    $this->_active_pane = $pnnAct; # Used in _store_selection

    $header     = pack("vv",    $record, $length);
    $data       = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct);

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

    /*
     * Store the page setup SETUP BIFF record.
     */
    function _store_setup() {
        $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) {
            $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.
     */
    function _store_header() {
        $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.
     */
    function _store_footer() {
        $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.
     */
    function _store_hcenter() {
        $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.
      */
    function _store_vcenter() {
        $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.
     */
    function _store_margin_left() {
        $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) {
            $data = strrev($data);
        }

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

    /*
     * Store the RIGHTMARGIN BIFF record.
     */
    function _store_margin_right() {
        $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) {
            $data = strrev($data);
        }

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

    /*
     * Store the TOPMARGIN BIFF record.
     */
    function _store_margin_top() {
        $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) {
            $data = strrev($data);
        }

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

    /*
     * Store the BOTTOMMARGIN BIFF record.
     */
    function _store_margin_bottom() {
        $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) {
            $data = strrev($data);
        }

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

###############################################################################
#
# merge_cells($first_row, $first_col, $last_row, $last_col)
#
# This is an Excel97/2000 method. It is required to perform more complicated
# merging than the normal align merge in Format.pm
#
function merge_cells() {

    $_=func_get_args();

    // Check for a cell reference in A1 notation and substitute row and column
    if (preg_match('/^\D/', $_[0])) {
        $_ = $this->_substitute_cellref($_);
    }

    $record  = 0x00E5;                   # Record identifier
    $length  = 0x000A;                   # Bytes to follow

    $cref     = 1;                       # Number of refs
    $rwFirst  = $_[0];                   # First row in reference
    $colFirst = $_[1];                   # First col in reference
    $rwLast   = $_[2] || $rwFirst;       # Last  row in reference
    $colLast  = $_[3] || $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("vvvvv",    $cref,
                                 $rwFirst, $rwLast,
                                 $colFirst, $colLast);

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

    /*
     * Write the PRINTHEADERS BIFF record.
     */
    function _store_print_headers() {
        $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.
     */
    function _store_print_gridlines() {
        $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.
     */
    function _store_gridset() {
        $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.
     */
    function _store_wsbool() {
        $record      = 0x0081;   # Record identifier
        $length      = 0x0002;   # Bytes to follow

        // $grbit   Option flags

        // 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.
     */
    function _store_hbreak() {
        // Return if the user hasn't specified pagebreaks
        if(sizeof($this->_hbreaks)==0) {
            return;
        }

        # Sort and filter array of page breaks
        $breaks  = $this->_sort_pagebreaks($this->_hbreaks);

        $record  = 0x001b;             // Record identifier
        $cbrk    = sizeof($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.
     */
    function _store_vbreak() {
        // Return if the user hasn't specified pagebreaks
        if(sizeof($this->_vbreaks)==0) {
            return;
        }

        // Sort and filter array of page breaks
        $breaks  = $this->_sort_pagebreaks($this->_vbreaks);

        $record  = 0x001a;            // Record identifier
        $cbrk    = sizeof($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.
     */
    function _store_protect() {
        // Exit unless sheet protection has been specified
        if (!$this->_protect) {
            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.
     */
    function _store_password() {
        // Exit unless sheet protection and password have been specified
        if (!$this->_protect || !$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_bitmap($row, $col, $filename, $x, $y, $scale_x, $scale_y)
#
# Insert a 24bit bitmap image in a worksheet. The main record required is
# IMDATA but it must be proceeded by a OBJ record to define its position.
#
function insert_bitmap() {

    $_=func_get_args();

    # Check for a cell reference in A1 notation and substitute row and column
    if (preg_match('/^\D/', $_[0])) {
        $_ = $this->_substitute_cellref($_);
    }

    $row         = $_[0];
    $col         = $_[1];
    $bitmap      = $_[2];
    $x           = $_[3] ? $_[3] : 0;
    $y           = $_[4] ? $_[4] : 0;
    $scale_x     = $_[5] ? $_[5] : 1;
    $scale_y     = $_[6] ? $_[6] : 1;

    list($width, $height, $size, $data) = $this->_process_bitmap($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->_position_image($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
     *
     * Note: the SDK incorrectly states that the height should be expressed
     * as a percentage of 1024.
     */
    function _position_image($col_start, $row_start, $x1, $y1,
                             $width, $height) {
        // $col_start   Col containing upper left corner of object
        // $x1          Distance to left side of object

        // $row_start   Row containing top left corner of object
        // $y1          Distance to top of object

        // $col_end     Col containing lower right corner of object
        // $x2          Distance to right side of object

        // $row_end     Row containing bottom right corner of object
        // $y2          Distance to bottom of object

        // $width       Width of image frame
        // $height      Height of image frame

        // Initialise end cell to the same as the start cell
        $col_end = $col_start;
        $row_end = $row_start;

        // Zero the specified offset if greater than the cell dimensions
        if ($x1 >= $this->_size_col($col_start)) {
            $x1 = 0;
        }
        if ($y1 >= $this->_size_row($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->_size_col($col_end)) {
            $width -= $this->_size_col($col_end);
            $col_end++;
        }

        // Subtract the underlying cell heights to find the end cell of the
        // image
        while ($height >= $this->_size_row($row_end)) {
            $height -= $this->_size_row($row_end);
            $row_end++;
        }

        // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a
        // cell with zero height or width.
        if ($this->_size_col($col_start) == 0) { return; }
        if ($this->_size_col($col_end)   == 0) { return; }
        if ($this->_size_row($row_start) == 0) { return; }
        if ($this->_size_row($row_end)   == 0) { return; }

        // Convert the pixel values to the percentage value expected by Excel
        $x1 = $x1     / $this->_size_col($col_start) * 1024;
        $y1 = $y1     / $this->_size_row($row_start) *  256;
        $x2 = $width  / $this->_size_col($col_end)   * 1024;
        $y2 = $height / $this->_size_row($row_end)   *  256;

        $this->_store_obj_picture($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.
     */
    function _size_col($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).
     */
    function _size_row($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 generalized to support other Excel objects.
     */
    function _store_obj_picture($col_start, $x1, $row_start, $y1,
                                $col_end, $x2, $row_end, $y2) {
        $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

        $colL        = $col_start;   // Col containing upper left corner of
                                     // object
        $dxL         = $x1;          // Distance from left side of cell

        $rwT         = $row_start;   // Row containing top left corner of
                                     // object
        $dyT         = $y1;          // Distance from top of cell

        $colR        = $col_end;     // Col containing lower right corner of 
                                     // object
        $dxR         = $x2;          // Distance from right of cell

        $rwB         = $row_end;     // Row containing bottom right corner of
                                     // object
        $dyB         = $y2;          // Distance from bottom of cell

        $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.
     */
    function _process_bitmap($bitmap) {
        // Open file and binmode the data in case the platform needs it.
        $bmp=fopen($bitmap, "rb");
        if (!$bmp) {
            trigger_error("Could not open file '$bitmap'.", E_USER_ERROR);
        }

        $data=fread($bmp, filesize($bitmap));

        // Check that the file is big enough to be a bitmap.
        if (strlen($data) <= 0x36) {
            trigger_error("$bitmap doesn't contain enough data.",
                          E_USER_ERROR);
        }

        // The first 2 bytes are used to identify the bitmap.
        if (substr($data, 0, 2) != "BM") {
            trigger_error("$bitmap doesn't appear to to be a ".
                          "valid bitmap image.", E_USER_ERROR);
        }

        // 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.
        $array = unpack("Vsize", $data);
        $data = substr($data, 4);
        $size   =  $array["size"];
        $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.
        $array = unpack("Vwidth/Vheight", $data);
        $data = substr($data, 8);
        $width = $array["width"];
        $height = $array["height"];

        if ($width > 0xFFFF) {
            trigger_error("$bitmap: largest image width supported is 64k.",
                          E_USER_ERROR);
        }

        if ($height > 0xFFFF) {
            trigger_error("$bitmap: largest image height supported is 64k.",
                          E_USER_ERROR);
        }

        // Read and remove the bitmap planes and bpp data. Verify them.
        $array = unpack("vplanes/vbitcount", $data);
        $data = substr($data, 4);
        $planes = $array["planes"];
        $bitcount = $array["bitcount"];

        if ($bitcount != 24) {
            trigger_error("$bitmap isn't a 24bit true color bitmap.",
                          E_USER_ERROR);
        }

        if ($planes != 1) {
            trigger_error("$bitmap: only 1 plane supported in bitmap image.",
                          E_USER_ERROR);
        }

        // Read and remove the bitmap compression. Verify compression.
        $array = unpack("Vcompression", $data);
        $data = substr($data, 4);
        $compression = $array["compression"];

        if ($compression != 0) {
            trigger_error("$bitmap: compression not supported in bitmap image.",
                          E_USER_ERROR);
        }

        // 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.
     */
    function _store_zoom() {
        // 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);
    }

}

?>