/lib/excel/Worksheet.php

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

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

require_once('Parser.php');
require_once('BIFFwriter.php');

/**
* Class for generating Excel Spreadsheets
*
* @author Xavier Noguer <xnoguer@rezebra.com>
* @package Spreadsheet_WriteExcel
*/

class Worksheet extends BIFFwriter
{

    /**
    * Constructor
    *
    * @param string  $name         The name of the new worksheet
    * @param integer $index        The index of the new worksheet
    * @param mixed   &$activesheet The current activesheet of the workbook we belong to
    * @param mixed   &$firstsheet  The first worksheet in the workbook we belong to 
    * @param mixed   &$url_format  The default format for hyperlinks
    * @param mixed   &$parser      The formula parser created for the Workbook
    */
    function Worksheet($name,$index,&$activesheet,&$firstsheet,&$url_format,&$parser)
    {
        $this->BIFFwriter();     // It needs to call its parent's constructor explicitly
        $rowmax                = 65536; // 16384 in Excel 5
        $colmax                = 256;
        $strmax                = 255;
    
        $this->name            = $name;
        $this->index           = $index;
        $this->activesheet     = &$activesheet;
        $this->firstsheet      = &$firstsheet;
        $this->_url_format     = $url_format;
        $this->_parser         = &$parser;
    
        $this->ext_sheets      = array();
        $this->_using_tmpfile  = 1;
        $this->_filehandle     = "";
        $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,0,0);
        $this->_panes          = array();
        $this->_active_pane    = 3;
        $this->_frozen         = 0;
        $this->selected        = 0;
    
        $this->_paper_size      = 0x0;
        $this->_orientation     = 0x1;
        $this->_header          = '';
        $this->_footer          = '';
        $this->_hcenter         = 0;
        $this->_vcenter         = 0;
        $this->_margin_head     = 0.50;
        $this->_margin_foot     = 0.50;
        $this->_margin_left     = 0.75;
        $this->_margin_right    = 0.75;
        $this->_margin_top      = 1.00;
        $this->_margin_bottom   = 1.00;
    
        $this->_title_rowmin    = NULL;
        $this->_title_rowmax    = NULL;
        $this->_title_colmin    = NULL;
        $this->_title_colmax    = NULL;
        $this->_print_rowmin    = NULL;
        $this->_print_rowmax    = NULL;
        $this->_print_colmin    = NULL;
        $this->_print_colmax    = NULL;
    
        $this->_print_gridlines = 1;
        $this->_print_headers   = 0;
    
        $this->_fit_page        = 0;
        $this->_fit_width       = 0;
        $this->_fit_height      = 0;
    
        $this->_hbreaks         = array();
        $this->_vbreaks         = array();
    
        $this->_protect         = 0;
        $this->_password        = NULL;
    
        $this->col_sizes        = array();
        $this->row_sizes        = array();
    
        $this->_zoom            = 100;
        $this->_print_scale     = 100;
    
        $this->_initialize();
    }
    
    /**
    * Open a tmp file to store the majority of the Worksheet data. If this fails,
    * for example due to write permissions, store the data in memory. This can be
    * slow for large files.
    */
    function _initialize()
    {
        // Open tmp file for storing Worksheet data
        $fh = tmpfile();
        if ( $fh) {
            // Store filehandle
            $this->_filehandle = $fh;
        }
        else {
            // If tmpfile() fails store data in memory
            $this->_using_tmpfile = 0;
        }
    }
    
    /**
    * Add data to the beginning of the workbook (note the reverse order)
    * and to the end of the workbook.
    *
    * @access public 
    * @see Workbook::store_workbook()
    * @param array $sheetnames The array of sheetnames from the Workbook this 
    *                          worksheet belongs to
    */
    function close($sheetnames)
    {
        $num_sheets = count($sheetnames);

        /***********************************************
        * Prepend in reverse order!!
        */
    
        // Prepend the sheet dimensions
        $this->_store_dimensions();
    
        // Prepend the sheet password
        $this->_store_password();
    
        // Prepend the sheet protection
        $this->_store_protect();
    
        // Prepend the page setup
        $this->_store_setup();
    
        // Prepend the bottom margin
        $this->_store_margin_bottom();
    
        // Prepend the top margin
        $this->_store_margin_top();
    
        // Prepend the right margin
        $this->_store_margin_right();
    
        // Prepend the left margin
        $this->_store_margin_left();
    
        // Prepend the page vertical centering
        $this->store_vcenter();
    
        // Prepend the page horizontal centering
        $this->store_hcenter();
    
        // Prepend the page footer
        $this->store_footer();
    
        // Prepend the page header
        $this->store_header();
    
        // Prepend the vertical page breaks
        $this->_store_vbreak();
    
        // Prepend the horizontal page breaks
        $this->_store_hbreak();
    
        // Prepend WSBOOL
        $this->_store_wsbool();
    
        // Prepend GRIDSET
        $this->_store_gridset();
    
        // Prepend PRINTGRIDLINES
        $this->_store_print_gridlines();
    
        // Prepend PRINTHEADERS
        $this->_store_print_headers();
    
        // Prepend EXTERNSHEET references
        for ($i = $num_sheets; $i > 0; $i--) {
            $sheetname = $sheetnames[$i-1];
            $this->_store_externsheet($sheetname);
        }
    
        // Prepend the EXTERNCOUNT of external references.
        $this->_store_externcount($num_sheets);
    
        // Prepend the COLINFO records if they exist
        if (!empty($this->colinfo)){
            for($i=0; $i < count($this->colinfo); $i++)
            {
                $this->_store_colinfo($this->colinfo[$i]);
            }
            $this->_store_defcol();
        }
    
        // Prepend the BOF record
        $this->_store_bof(0x0010);
    
        /*
        * End of prepend. Read upwards from here.
        ***********************************************/
    
        // Append
        $this->_store_window2();
        $this->_store_zoom();
        if(!empty($this->_panes))
          $this->_store_panes($this->_panes);
        $this->_store_selection($this->_selection);
        $this->_store_eof();
    }
    
    /**
    * Retrieve the worksheet name. This is usefull when creating worksheets
    * without a name.
    *
    * @access public
    * @return string The worksheet's name
    */
    function get_name()
    {
        return($this->name);
    }
    
    /**
    * Retrieves data from memory in one chunk, or from disk in $buffer
    * sized chunks.
    *
    * @return string The data
    */
    function get_data()
    {
        $buffer = 4096;
    
        // Return data stored in memory
        if (isset($this->_data)) {
            $tmp   = $this->_data;
            unset($this->_data);
            $fh    = $this->_filehandle;
            if ($this->_using_tmpfile) {
                fseek($fh, 0);
            }
            return($tmp);
        }
        // Return data stored on disk
        if ($this->_using_tmpfile) {
            if ($tmp = fread($this->_filehandle, $buffer)) {
                return($tmp);
            }
        }
    
        // No data to return
        return('');
    }
    
    /**
    * Set this worksheet as a selected worksheet, i.e. the worksheet has its tab
    * highlighted.
    *
    * @access public
    */
    function select()
    {
        $this->selected = 1;
    }
    
    /**
    * Set this worksheet as the active worksheet, i.e. the worksheet that is
    * displayed when the workbook is opened. Also set it as selected.
    *
    * @access public
    */
    function activate()
    {
        $this->selected = 1;
        $this->activesheet =& $this->index;
    }
    
    /**
    * Set this worksheet as the first visible sheet. This is necessary
    * when there are a large number of worksheets and the activated
    * worksheet is not visible on the screen.
    *
    * @access public
    */
    function 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.
    *
    * @access public
    * @param string $password The password to use for protecting the sheet.
    */
    function protect($password)
    {
        $this->_protect   = 1;
        $this->_password  = $this->_encode_password($password);
    }
    
    /**
    * Set the width of a single column or a range of columns.
    *
    * @access public
    * @see _store_colinfo()
    * @param integer $firstcol first column on the range
    * @param integer $lastcol  last column on the range
    * @param integer $width    width to set
    * @param mixed   $format   The optional XF format to apply to the columns
    * @param integer $hidden   The optional hidden atribute
    */
    function set_column($firstcol, $lastcol, $width, $format = 0, $hidden = 0)
    {
        $this->colinfo[] = array($firstcol, $lastcol, $width, $format, $hidden);

        // Set width to zero if column is hidden
        $width = ($hidden) ? 0 : $width;
    
        for($col = $firstcol; $col <= $lastcol; $col++) {
            $this->col_sizes[$col] = $width;
        }
    }
    
    /**
    * Set which cell or cells are selected in a worksheet
    *
    * @access public
    * @param integer $first_row    first row in the selected quadrant
    * @param integer $first_column first column in the selected quadrant
    * @param integer $last_row     last row in the selected quadrant
    * @param integer $last_column  last column in the selected quadrant
    * @see _store_selection()
    */
    function set_selection($first_row,$first_column,$last_row,$last_column)
    {
        $this->_selection = array($first_row,$first_column,$last_row,$last_column);
    }
    
    /**
    * Set panes and mark them as frozen.
    *
    * @access public
    * @param array $panes This is the only parameter received and is composed of the following:
    *                     0 => Vertical split position,
    *                     1 => Horizontal split position
    *                     2 => Top row visible
    *                     3 => Leftmost column visible
    *                     4 => Active pane
    */
    function freeze_panes($panes)
    {
        $this->_frozen = 1;
        $this->_panes  = $panes;
    }
    
    /**
    * Set panes and mark them as unfrozen.
    *
    * @access public
    * @param array $panes This is the only parameter received and is composed of the following:
    *                     0 => Vertical split position,
    *                     1 => Horizontal split position
    *                     2 => Top row visible
    *                     3 => Leftmost column visible
    *                     4 => Active pane
    */
    function thaw_panes($panes)
    {
        $this->_frozen = 0;
        $this->_panes  = $panes;
    }
    
    /**
    * Set the page orientation as portrait.
    *
    * @access public
    */
    function set_portrait()
    {
        $this->_orientation = 1;
    }
    
    /**
    * Set the page orientation as landscape.
    *
    * @access public
    */
    function set_landscape()
    {
        $this->_orientation = 0;
    }
    
    /**
    * Set the paper type. Ex. 1 = US Letter, 9 = A4
    *
    * @access public
    * @param integer $size The type of paper size to use
    */
    function set_paper($size = 0)
    {
        $this->_paper_size = $size;
    }
    
    
    /**
    * Set the page header caption and optional margin.
    *
    * @access public
    * @param string $string The header text
    * @param float  $margin optional head margin in inches.
    */
    function set_header($string,$margin = 0.50)
    {
        if (strlen($string) >= 255) {
            //carp 'Header string must be less than 255 characters';
            return;
        }
        $this->_header      = $string;
        $this->_margin_head = $margin;
    }
    
    /**
    * Set the page footer caption and optional margin.
    *
    * @access public
    * @param string $string The footer text
    * @param float  $margin optional foot margin in inches.
    */
    function set_footer($string,$margin = 0.50)
    {
        if (strlen($string) >= 255) {
            //carp 'Footer string must be less than 255 characters';
            return;
        }
        $this->_footer      = $string;
        $this->_margin_foot = $margin;
    }
    
    /**
    * Center the page horinzontally.
    *
    * @access public
    * @param integer $center the optional value for centering. Defaults to 1 (center).
    */
    function center_horizontally($center = 1)
    {
        $this->_hcenter = $center;
    }
    
    /**
    * Center the page horinzontally.
    *
    * @access public
    * @param integer $center the optional value for centering. Defaults to 1 (center).
    */
    function center_vertically($center = 1)
    {
        $this->_vcenter = $center;
    }
    
    /**
    * Set all the page margins to the same value in inches.
    *
    * @access public
    * @param float $margin The margin to set in inches
    */
    function 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.
    *
    * @access public
    * @param float $margin The margin to set 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.
    *
    * @access public
    * @param float $margin The margin to set in inches
    */
    function set_margins_TB($margin)
    {
        $this->set_margin_top($margin);
        $this->set_margin_bottom($margin);
    }
    
    /**
    * Set the left margin in inches.
    *
    * @access public
    * @param float $margin The margin to set in inches
    */
    function set_margin_left($margin = 0.75)
    {
        $this->_margin_left = $margin;
    }
    
    /**
    * Set the right margin in inches.
    *
    * @access public
    * @param float $margin The margin to set in inches
    */
    function set_margin_right($margin = 0.75)
    {
        $this->_margin_right = $margin;
    }
    
    /**
    * Set the top margin in inches.
    *
    * @access public
    * @param float $margin The margin to set in inches
    */
    function set_margin_top($margin = 1.00)
    {
        $this->_margin_top = $margin;
    }
    
    /**
    * Set the bottom margin in inches.
    *
    * @access public
    * @param float $margin The margin to set in inches
    */
    function set_margin_bottom($margin = 1.00)
    {
        $this->_margin_bottom = $margin;
    }
    
    /**
    * Set the rows to repeat at the top of each printed page. See also the
    * _store_name_xxxx() methods in Workbook.php
    *
    * @access public
    * @param integer $first_row First row to repeat
    * @param integer $last_row  Last row to repeat. Optional.
    */
    function repeat_rows($first_row, $last_row = NULL)
    {
        $this->_title_rowmin  = $first_row;
        if(isset($last_row)) { //Second row is optional
            $this->_title_rowmax  = $last_row;
        }
        else {
            $this->_title_rowmax  = $first_row;
        }
    }
    
    /**
    * Set the columns to repeat at the left hand side of each printed page.
    * See also the _store_names() methods in Workbook.php
    *
    * @access public
    * @param integer $first_col First column to repeat
    * @param integer $last_col  Last column to repeat. Optional.
    */
    function repeat_columns($first_col, $last_col = NULL)
    {
        $this->_title_colmin  = $first_col;
        if(isset($last_col)) { // Second col is optional
            $this->_title_colmax  = $last_col;
        }
        else {
            $this->_title_colmax  = $first_col;
        }
    }
    
    /**
    * Set the area of each worksheet that will be printed.
    *
    * @access public
    * @see Workbook::_store_names()
    * @param integer $first_row First row of the area to print
    * @param integer $first_col First column of the area to print
    * @param integer $last_row  Last row of the area to print
    * @param integer $last_col  Last column of the area to print
    */
    function print_area($first_row, $first_col, $last_row, $last_col)
    {
        $this->_print_rowmin  = $first_row;
        $this->_print_colmin  = $first_col;
        $this->_print_rowmax  = $last_row;
        $this->_print_colmax  = $last_col;
    }
    
    
    /**
    * Set the option to hide gridlines on the printed page. 
    *
    * @access public
    * @see _store_print_gridlines(), _store_gridset()
    */
    function hide_gridlines()
    {
        $this->_print_gridlines = 0;
    }
    
    /**
    * Set the option to print the row and column headers on the printed page.
    * See also the _store_print_headers() method below.
    *
    * @access public
    * @see _store_print_headers()
    * @param integer $print Whether to print the headers or not. Defaults to 1 (print).
    */
    function print_row_col_headers($print = 1)
    {
        $this->_print_headers = $print;
    }
    
    /**
    * Store the vertical and horizontal number of pages that will define the
    * maximum area printed. It doesn't seem to work with OpenOffice.
    *
    * @access public
    * @param  integer $width  Maximun width of printed area in pages
    * @param  integer $heigth Maximun heigth of printed area in pages
    * @see set_print_scale()
    */
    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 (for printing).
    * The breaks represent the row after which the break is inserted.
    *
    * @access public
    * @param array $breaks Array containing the horizontal page breaks
    */
    function set_h_pagebreaks($breaks)
    {
        foreach($breaks as $break) {
            array_push($this->_hbreaks,$break);
        }
    }
    
    /**
    * Store the vertical page breaks on a worksheet (for printing).
    * The breaks represent the column after which the break is inserted.
    *
    * @access public
    * @param array $breaks Array containing the vertical page breaks
    */
    function set_v_pagebreaks($breaks)
    {
        foreach($breaks as $break) {
            array_push($this->_vbreaks,$break);
        }
    }
    
    
    /**
    * Set the worksheet zoom factor.
    *
    * @access public
    * @param integer $scale The zoom factor
    */
    function set_zoom($scale = 100)
    {
        // Confine the scale to Excel's range
        if ($scale < 10 or $scale > 400) {
            //carp "Zoom factor $scale outside range: 10 <= zoom <= 400";
            $scale = 100;
        }
    
        $this->_zoom = floor($scale);
    }
    
    /**
    * Set the scale factor for the printed page. 
    * It turns off the "fit to page" option
    *
    * @access public
    * @param integer $scale The optional scale factor. Defaults to 100
    */
    function set_print_scale($scale = 100)
    {
        // Confine the scale to Excel's range
        if ($scale < 10 or $scale > 400)
        {
            // REPLACE THIS FOR A WARNING
            die("Print scale $scale outside range: 10 <= zoom <= 400");
            $scale = 100;
        }
    
        // Turn off "fit to page" option
        $this->_fit_page    = 0;
    
        $this->_print_scale = floor($scale);
    }
    
    /**
    * Map to the appropriate write method acording to the token recieved.
    *
    * @access public
    * @param integer $row    The row of the cell we are writing to
    * @param integer $col    The column of the cell we are writing to
    * @param mixed   $token  What we are writing
    * @param mixed   $format The optional format to apply to the cell
    */
    function write($row, $col, $token, $format = 0)
    {
        // Check for a cell reference in A1 notation and substitute row and column
        /*if ($_[0] =~ /^\D/) {
            @_ = $this->_substitute_cellref(@_);
    }*/
    
        /*
        # Match an array ref.
        if (ref $token eq "ARRAY") {
            return $this->write_row(@_);
    }*/
    
        // Match number
        if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/",$token)) {
            return $this->write_number($row,$col,$token,$format);
        }
        // Match http or ftp URL
        elseif (preg_match("/^[fh]tt?p:\/\//",$token)) {
            return $this->write_url($row, $col, $token, $format);
        }
        // Match mailto:
        elseif (preg_match("/^mailto:/",$token)) {
            return $this->write_url($row, $col, $token, $format);
        }
        // Match internal or external sheet link
        elseif (preg_match("/^(?:in|ex)ternal:/",$token)) {
            return $this->write_url($row, $col, $token, $format);
        }
        // Match formula
        elseif (preg_match("/^=/",$token)) {
            return $this->write_formula($row, $col, $token, $format);
        }
        // Match formula
        elseif (preg_match("/^@/",$token)) {
            return $this->write_formula($row, $col, $token, $format);
        }
        // Match blank
        elseif ($token == '') {
            return $this->write_blank($row,$col,$format);
        }
        // Default: match string
        else {
            return $this->write_string($row,$col,$token,$format);
        }
    }
 
    /**
    * Returns an index to the XF record in the workbook
    *
    * @param mixed $format The optional XF format
    * @return integer The XF record index
    */
    function _XF(&$format)
    {
        if($format != 0)
        {
            return($format->get_xf_index());
        }
        else
        {
            return(0x0F);
        }
    }
    
    
    /******************************************************************************
    *******************************************************************************
    *
    * Internal methods
    */
    
    
    /**
    * Store Worksheet data in memory using the parent's class append() or to a
    * temporary file, the default.
    *
    * @param string $data The binary data to append
    */
    function _append($data)
    {
        if ($this->_using_tmpfile)
        {
            // Add CONTINUE records if necessary
            if (strlen($data) > $this->_limit) {
                $data = $this->_add_continue($data);
            }
            fwrite($this->_filehandle,$data);
            $this->_datasize += strlen($data);
        }
        else {
            parent::_append($data);
        }
    }
    
    /**
    * Substitute an Excel cell reference in A1 notation for  zero based row and
    * column values in an argument list.
    *
    * Ex: ("A4", "Hello") is converted to (3, 0, "Hello").
    *
    * @param string $cell The cell reference. Or range of cells.
    * @return array
    */
    function _substitute_cellref($cell)
    {
        $cell = strtoupper($cell);
    
        // Convert a column range: 'A:A' or 'B:G'
        if (preg_match("/([A-I]?[A-Z]):([A-I]?[A-Z])/",$cell,$match)) {
            list($no_use, $col1) =  $this->_cell_to_rowcol($match[1] .'1'); // Add a dummy row
            list($no_use, $col2) =  $this->_cell_to_rowcol($match[2] .'1'); // Add a dummy row
            return(array($col1, $col2));
        }
    
        // Convert a cell range: 'A1:B7'
        if (preg_match("/\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/",$cell,$match)) {
            list($row1, $col1) =  $this->_cell_to_rowcol($match[1]);
            list($row2, $col2) =  $this->_cell_to_rowcol($match[2]);
            return(array($row1, $col1, $row2, $col2));
        }
    
        // Convert a cell reference: 'A1' or 'AD2000'
        if (preg_match("/\$?([A-I]?[A-Z]\$?\d+)/",$cell)) {
            list($row1, $col1) =  $this->_cell_to_rowcol($match[1]);
            return(array($row1, $col1));
        }
    
        die("Unknown cell reference $cell ");
    }
    
    /**
    * Convert an Excel cell reference in A1 notation to a zero based row and column
    * reference; converts C1 to (0, 2).
    *
    * @param string $cell The cell reference.
    * @return array containing (row, column)
    */
    function _cell_to_rowcol($cell)
    {
        preg_match("/\$?([A-I]?[A-Z])\$?(\d+)/",$cell,$match);
        $col     = $match[1];
        $row     = $match[2];
    
        // Convert base26 column string to number
        $chars = str_split($col);
        $expn  = 0;
        $col   = 0;
    
        while ($chars) {
            $char = array_pop($chars);        // LS char first
            $col += (ord($char) -ord('A') +1) * pow(26,$expn);
            $expn++;
        }
    
        // Convert 1-index to zero-index
        $row--;
        $col--;
    
        return(array($row, $col));
    }
    
    /**
    * Based on the algorithm provided by Daniel Rentz of OpenOffice.
    *
    * @param string $plaintext The password to be encoded in plaintext.
    * @return string The encoded password
    */
    function _encode_password($plaintext)
    {
        $password = 0x0000;
        $i        = 1;       // char position
 
        // split the plain text password in its component characters
        $chars = preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY);
        foreach($chars as $char)
        {
            $value     = ord($char) << $i;   // shifted ASCII value 
            $bit_16    = $value & 0x8000;    // the bit 16
            $bit_16  >>= 15;                 // 0x0000 or 0x0001
            //$bit_17    = $value & 0x00010000;
            //$bit_17  >>= 15;
            $value    &= 0x7fff;             // first 15 bits
            $password ^= ($value | $bit_16);
            //$password ^= ($value | $bit_16 | $bit_17);
            $i++;
        }
    
        $password ^= strlen($plaintext);
        $password ^= 0xCE4B;

        return($password);
    }
    
    /******************************************************************************
    *******************************************************************************
    *
    * BIFF RECORDS
    */
    
    
    /**
    * Write a double to the specified row and column (zero indexed).
    * An integer can be written as a double. Excel will display an
    * integer. $format is optional.
    *
    * Returns  0 : normal termination
    *         -2 : row or column out of range
    *
    * @access public
    * @param integer $row    Zero indexed row
    * @param integer $col    Zero indexed column
    * @param float   $num    The number to write
    * @param mixed   $format The optional XF format
    */
    function write_number($row, $col, $num, $format = 0)
    {
        $record    = 0x0203;                 // Record identifier
        $length    = 0x000E;                 // Number of bytes to follow
        $xf        = $this->_XF($format);    // The cell format
    
        // Check that row and col are valid and store max and min values
        if ($row >= $this->xls_rowmax)
        {
            return(-2);
        }
        if ($col >= $this->xls_colmax)
        {
            return(-2);
        }
        if ($row <  $this->dim_rowmin) 
        {
            $this->dim_rowmin = $row;
        }
        if ($row >  $this->dim_rowmax) 
        {
            $this->dim_rowmax = $row;
        }
        if ($col <  $this->dim_colmin) 
        {
            $this->dim_colmin = $col;
        }
        if ($col >  $this->dim_colmax) 
        {
            $this->dim_colmax = $col;
        }
    
        $header    = pack("vv",  $record, $length);
        $data      = pack("vvv", $row, $col, $xf);
        $xl_double = pack("d",   $num);
        if ($this->_byte_order) // if it's Big Endian
        {
            $xl_double = strrev($xl_double);
        }
    
        $this->_append($header.$data.$xl_double);
        return(0);
    }
    
    /**
    * Write a string to the specified row and column (zero indexed).
    * NOTE: there is an Excel 5 defined limit of 255 characters.
    * $format is optional.
    * Returns  0 : normal termination
    *         -1 : insufficient number of arguments
    *         -2 : row or column out of range
    *         -3 : long string truncated to 255 chars
    *
    * @access public
    * @param integer $row    Zero indexed row
    * @param integer $col    Zero indexed column
    * @param string  $str    The string to write
    * @param mixed   $format The XF format for the cell
    */
    function write_string($row, $col, $str, $format = 0)
    {
        $strlen    = strlen($str);
        $record    = 0x0204;                   // Record identifier
        $length    = 0x0008 + $strlen;         // Bytes to follow
        $xf        = $this->_XF($format);      // The cell format
        
        $str_error = 0;
    
        // Check that row and col are valid and store max and min values
        if ($row >= $this->xls_rowmax) 
        {
            return(-2);
        }
        if ($col >= $this->xls_colmax) 
        {
            return(-2);
        }
        if ($row <  $this->dim_rowmin) 
        {
            $this->dim_rowmin = $row;
        }
        if ($row >  $this->dim_rowmax) 
        {
            $this->dim_rowmax = $row;
        }
        if ($col <  $this->dim_colmin) 
        {
            $this->dim_colmin = $col;
        }
        if ($col >  $this->dim_colmax) 
        {
            $this->dim_colmax = $col;
        }
    
        if ($strlen > $this->xls_strmax)  // LABEL must be < 255 chars
        {
            $str       = substr($str, 0, $this->xls_strmax);
            $length    = 0x0008 + $this->xls_strmax;
            $strlen    = $this->xls_strmax;
            $str_error = -3;
        }
    
        $header    = pack("vv",   $record, $length);
        $data      = pack("vvvv", $row, $col, $xf, $strlen);
        $this->_append($header.$data.$str);
        return($str_error);
    }
 
    /**
    * Writes a note associated with the cell given by the row and column.
    * NOTE records don't have a length limit.
    *
    * @access public
    * @param integer $row    Zero indexed row
    * @param integer $col    Zero indexed column
    * @param string  $note   The note to write
    */
    function write_note($row, $col, $note)
    {
        $note_length    = strlen($note);
        $record         = 0x001C;                // Record identifier
        $max_length     = 2048;                  // Maximun length for a NOTE record
        //$length      = 0x0006 + $note_length;    // Bytes to follow

        // Check that row and col are valid and store max and min values
        if ($row >= $this->xls_rowmax) 
        {
            return(-2);
        }
        if ($col >= $this->xls_colmax) 
        {
            return(-2);
        }
        if ($row <  $this->dim_rowmin) 
        {
            $this->dim_rowmin = $row;
        }
        if ($row >  $this->dim_rowmax) 
        {
            $this->dim_rowmax = $row;
        }
        if ($col <  $this->dim_colmin) 
        {
            $this->dim_colmin = $col;
        }
        if ($col >  $this->dim_colmax) 
        {
            $this->dim_colmax = $col;
        }
 
        // Length for this record is no more than 2048 + 6
        $length    = 0x0006 + min($note_length, 2048);
        $header    = pack("vv",   $record, $length);
        $data      = pack("vvv", $row, $col, $note_length);
        $this->_append($header.$data.substr($note, 0, 2048));

        for($i = $max_length; $i < $note_length; $i += $max_length)
        {
            $chunk  = substr($note, $i, $max_length);
            $length = 0x0006 + strlen($chunk);
            $header = pack("vv",   $record, $length);
            $data   = pack("vvv", -1, 0, strlen($chunk));
            $this->_append($header.$data.$chunk);
        }
        return(0);
    }
 
    /**
    * Write a blank cell to the specified row and column (zero indexed).
    * A blank cell is used to specify formatting without adding a string
    * or a number.
    *
    * A blank cell without a format serves no purpose. Therefore, we don't write
    * a BLANK record unless a format is specified. 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
    *
    * @access public
    * @param integer $row    Zero indexed row
    * @param integer $col    Zero indexed column
    * @param mixed   $format The XF format
    */
    function write_blank($row, $col, $format = 0)
    {
        // Don't write a blank cell unless it has a format
        if ($format == 0)
        {
            return(0);
        }
    
        $record    = 0x0201;                 // Record identifier
        $length    = 0x0006;                 // Number of bytes to follow
        $xf        = $this->_XF($format);    // The cell format
    
        // Check that row and col are valid and store max and min values
        if ($row >= $this->xls_rowmax) 
        {
            return(-2);
        }
        if ($col >= $this->xls_colmax) 
        {
            return(-2);
        }
        if ($row <  $this->dim_rowmin) 
        {
            $this->dim_rowmin = $row;
        }
        if ($row >  $this->dim_rowmax) 
        {
            $this->dim_rowmax = $row;
        }
        if ($col <  $this->dim_colmin) 
        {
            $this->dim_colmin = $col;
        }
        if ($col >  $this->dim_colmax) 
        {
            $this->dim_colmax = $col;
        }
    
        $header    = pack("vv",  $record, $length);
        $data      = pack("vvv", $row, $col, $xf);
        $this->_append($header.$data);
        return 0;
    }
 
    /**
    * Write a formula to the specified row and column (zero indexed).
    * The textual representation of the formula is passed to the parser in
    * Parser.php which returns a packed binary string.
    *
    * Returns  0 : normal termination
    *         -2 : row or column out of range
    *
    * @access public
    * @param integer $row     Zero indexed row
    * @param integer $col     Zero indexed column
    * @param string  $formula The formula text string
    * @param mixed   $format  The optional XF format
    */
    function write_formula($row, $col, $formula, $format = 0)
    {
        $record    = 0x0006;     // Record identifier
    
        // Excel normally stores the last calculated value of the formula in $num.
        // Clearly we are not in a position to calculate this a priori. Instead
        // we set $num to zero and set the option flags in $grbit to ensure
        // automatic calculation of the formula when the file is opened.
        //
        $xf        = $this->_XF($format); // The cell format
        $num       = 0x00;                // Current value of formula
        $grbit     = 0x03;                // Option flags
        $chn       = 0x0000;              // Must be zero
    
    
        // Check that row and col are valid and store max and min values
        if ($row >= $this->xls_rowmax)
        {
            return(-2);
        }
        if ($col >= $this->xls_colmax)
        {
            return(-2);
        }
        if ($row <  $this->dim_rowmin) 
        {
            $this->dim_rowmin = $row;
        }
        if ($row >  $this->dim_rowmax) 
        {
            $this->dim_rowmax = $row;
        }
        if ($col <  $this->dim_colmin) 
        {
            $this->dim_colmin = $col;
        }
        if ($col >  $this->dim_colmax) 
        {
            $this->dim_colmax = $col;
        }
    
        // Strip the '=' or '@' sign at the beginning of the formula string
        if (preg_match("/^=/",$formula)) {
            $formula = preg_replace("/(^=)/","",$formula);
        }
        elseif(preg_match("/^@/",$formula)) {
            $formula = preg_replace("/(^@)/","",$formula);
        }
        else {
            die("Unrecognised character for formula");
        }
    
        // Parse the formula using the parser in Parser.php
        //$tree      = new Parser($this->_byte_order);
        $this->_parser->parse($formula);
        //$tree->parse($formula);
        $formula = $this->_parser->to_reverse_polish();
    
        $formlen    = strlen($formula);    // Length of the binary string
        $length     = 0x16 + $formlen;     // Length of the record data
    
        $header    = pack("vv",      $record, $length);
        $data      = pack("vvvdvVv", $row, $col, $xf, $num,
                                     $grbit, $chn, $formlen);
    
        $this->_append($header.$data.$formula);
        return 0;
    }
    
    /**
    * Write a hyperlink. This is comprised of two elements: the visible label and
    * the invisible link. The visible label is the same as the link unless an
    * alternative string is specified. The label is written using the
    * 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
    *
    * @access public
    * @param integer $row    Row
    * @param integer $col    Column
    * @param string  $url    URL string
    * @param string  $string Alternative label
    * @param mixed   $format The cell format
    */
    function write_url($row, $col, $url, $string = '', $format = 0)
    {
        // Add start row and col to arg list
        return($this->_write_url_range($row, $col, $row, $col, $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.
    *
    * @param integer $row1   Start row
    * @param integer $col1   Start column
    * @param integer $row2   End row
    * @param integer $col2   End column
    * @param string  $url    URL string
    * @param string  $string Alternative label
    * @param mixed   $format The cell format
    */
    
    function _write_url_range($row1, $col1, $row2, $col2, $url, $string = '', $format = 0)
    {
        // Check for internal/external sheet links or default to web link
        if (preg_match('[^internal:]', $url)) {
            return($this->_write_url_internal($row1, $col1, $row2, $col2, $url, $string, $format));
        }
        if (preg_match('[^external:]', $url)) {
            return($this->_write_url_external($row1, $col1, $row2, $col2, $url, $string, $format));
        }
        return($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 write_url()
    * @param integer $row1   Start row
    * @param integer $col1   Start column
    * @param integer $row2   End row
    * @param integer $col2   End column
    * @param string  $url    URL string
    * @param string  $str    Alternative label
    * @param mixed   $format The cell format
    */
    function _write_url_web($row1, $col1, $row2, $col2, $url, $str, $format = 0)
    {
        $record      = 0x01B8;                       // Record identifier
        $length      = 0x00000;                      // Bytes to follow
    
        if($format == 0) {
            $format = $this->_url_format;
        }
    
        // Write the visible label using the write_string() method.
        if($str == '') {
            $str = $url;
        }
        $str_error = $this->write_string($row1, $col1, $str, $format);
        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);
    }
    
    /**
    * Used to write internal reference hyperlinks such as "Sheet1!A1".
    *
    * @see write_url()
    * @param integer $row1   Start row
    * @param integer $col1   Start column
    * @param integer $row2   End row
    * @param integer $col2   End column
    * @param string  $url    URL string
    * @param string  $str    Alternative label
    * @param mixed   $format The cell format
    */
    function _write_url_internal($row1, $col1, $row2, $col2, $url, $str, $format = 0)
    {
        $record      = 0x01B8;                       // Record identifier
        $length      = 0x00000;                      // Bytes to follow
    
        if ($format == 0) {
            $format = $this->_url_format;
        }
    
        // Strip URL type
        $url = preg_replace('s[^internal:]', '', $url);
    
        // Write the visible label
        if($str == '') {
            $str = $url;
        }
        $str_error = $this->write_string($row1, $col1, $str, $format);
        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", floor(strlen($url)/2));
    
        // Calculate the data length
        $length      = 0x24 + strlen($url);
    
        // Pack the header data
        $header      = pack("vv",   $record, $length);
        $data        = pack("vvvv", $row1, $row2, $col1, $col2);
    
        // Write the packed data
        $this->_append($header. $data.
                       $unknown1. $options.
                       $url_len. $url);
        return($str_error);
    }
    
    /**
    * Write links to external directory names such as 'c:\foo.xls',
    * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
    *
    * Note: Excel writes some relative links with the $dir_long string. We ignore
    * these cases for the sake of simpler code.
    *
    * @see write_url()
    * @param integer $row1   Start row
    * @param integer $col1   Start column
    * @param integer $row2   End row
    * @param integer $col2   End column
    * @param string  $url    URL string
    * @param string  $str    Alternative label
    * @param mixed   $format The cell format
    */
    function _write_url_external($row1, $col1, $row2, $col2, $url, $str, $format = 0)
    {
        // Network drives are different. We will handle them separately
        // MS/Novell network drives and shares start with \\
        if (preg_match('[^external:\\\\]', $url)) {
            return($this->_write_url_external_net($row1, $col1, $row2, $col2, $url, $str, $format));
        }
    
        $record      = 0x01B8;                       // Record identifier
        $length      = 0x00000;                      // Bytes to follow
    
        if ($format == 0) {
            $format = $this->_url_format;
        }
    
        // Strip URL type and change Unix dir separator to Dos style (if needed)
        //
        $url = preg_replace('[^external:]', '', $url);
        $url = preg_replace('[/]', "\\", $url);
    
        // Write the visible label
        if ($str == '') {
            $str = preg_replace('[\#]', ' - ', $url);
        }
        $str_error = $this->write_string($row1, $col1, $str, $format);
        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) = explode('/#/', $url);
        $link_type               = 0x01 | $absolute;
    
        if (isset($sheet)) {
            $link_type |= 0x08;
            $sheet_len  = pack("V", strlen($sheet) + 0x01);
            $sheet      = join("\0", str_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)
        $up_count    = preg_match_all("/\.\.\\/", $dir_long, $useless);
        $up_count    = pack("v", $up_count);
    
        // Store the short dos dir name (null terminated)
        $dir_short   = preg_replace('/\.\.\\/', '', $dir_long) . "\0";
    
        // Store the long dir name as a wchar string (non-null terminated)
        $dir_long       = join("\0", str_split($dir_long));
        $dir_long       = $dir_long . "\0";
    
        // Pack the lengths of the dir strings
        $dir_short_len = pack("V", strlen($dir_short)      );
        $dir_long_len  = pack("V", strlen($dir_long)       );
        $stream_len    = pack("V", 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) .
                …

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