Subversion Repositories eFlore/Applications.cel

<?php

/*

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

*

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

*  PERL Spreadsheet::WriteExcel module.

*

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

*  <jmcnamara@cpan.org>

*

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

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

*  class library should be directed to me.

*

*  License Information:

*

*    Spreadsheet_Excel_Writer:  A library for generating Excel Spreadsheets

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

*

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

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

*    License as published by the Free Software Foundation; either

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

*

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

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

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

*    Lesser General Public License for more details.

*

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

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

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

*/

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

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

/**

* Class for generating Excel Spreadsheets

*

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

* @category FileFormats

* @package  Spreadsheet_Excel_Writer

*/

class Spreadsheet_Excel_Writer_Worksheet extends Spreadsheet_Excel_Writer_BIFFwriter

{

    /**

    * Name of the Worksheet

    * @var string

    */

    var $name;

    /**

    * Index for the Worksheet

    * @var integer

    */

    var $index;

    /**

    * Reference to the (default) Format object for URLs

    * @var object Format

    */

    var $_url_format;

    /**

    * Reference to the parser used for parsing formulas

    * @var object Format

    */

    var $_parser;

    /**

    * Filehandle to the temporary file for storing data

    * @var resource

    */

    var $_filehandle;

    /**

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

    * @var bool

    */

    var $_using_tmpfile;

    /**

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

    * @var integer

    */

    var $_xls_rowmax;

    /**

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

    * @var integer

    */

    var $_xls_colmax;

    /**

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

    * @var integer

    */

    var $_xls_strmax;

    /**

    * First row for the DIMENSIONS record

    * @var integer

    * @see _storeDimensions()

    */

    var $_dim_rowmin;

    /**

    * Last row for the DIMENSIONS record

    * @var integer

    * @see _storeDimensions()

    */

    var $_dim_rowmax;

    /**

    * First column for the DIMENSIONS record

    * @var integer

    * @see _storeDimensions()

    */

    var $_dim_colmin;

    /**

    * Last column for the DIMENSIONS record

    * @var integer

    * @see _storeDimensions()

    */

    var $_dim_colmax;

    /**

    * Array containing format information for columns

    * @var array

    */

    var $_colinfo;

    /**

    * Array containing the selected area for the worksheet

    * @var array

    */

    var $_selection;

    /**

    * Array containing the panes for the worksheet

    * @var array

    */

    var $_panes;

    /**

    * The active pane for the worksheet

    * @var integer

    */

    var $_active_pane;

    /**

    * Bit specifying if panes are frozen

    * @var integer

    */

    var $_frozen;

    /**

    * Bit specifying if the worksheet is selected

    * @var integer

    */

    var $selected;

    /**

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

    * @var integer

    */

    var $_paper_size;

    /**

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

    * @var integer

    */

    var $_orientation;

    /**

    * The page header caption

    * @var string

    */

    var $_header;

    /**

    * The page footer caption

    * @var string

    */

    var $_footer;

    /**

    * The horizontal centering value for the page

    * @var integer

    */

    var $_hcenter;

    /**

    * The vertical centering value for the page

    * @var integer

    */

    var $_vcenter;

    /**

    * The margin for the header

    * @var float

    */

    var $_margin_head;

    /**

    * The margin for the footer

    * @var float

    */

    var $_margin_foot;

    /**

    * The left margin for the worksheet in inches

    * @var float

    */

    var $_margin_left;

    /**

    * The right margin for the worksheet in inches

    * @var float

    */

    var $_margin_right;

    /**

    * The top margin for the worksheet in inches

    * @var float

    */

    var $_margin_top;

    /**

    * The bottom margin for the worksheet in inches

    * @var float

    */

    var $_margin_bottom;

    /**

    * First row to reapeat on each printed page

    * @var integer

    */

    var $title_rowmin;

    /**

    * Last row to reapeat on each printed page

    * @var integer

    */

    var $title_rowmax;

    /**

    * First column to reapeat on each printed page

    * @var integer

    */

    var $title_colmin;

    /**

    * First row of the area to print

    * @var integer

    */

    var $print_rowmin;

    /**

    * Last row to of the area to print

    * @var integer

    */

    var $print_rowmax;

    /**

    * First column of the area to print

    * @var integer

    */

    var $print_colmin;

    /**

    * Last column of the area to print

    * @var integer

    */

    var $print_colmax;

    /**

    * Whether to display RightToLeft.

    * @var integer

    */

    var $_Arabic;

    /**

    * Whether to use outline.

    * @var integer

    */

    var $_outline_on;

    /**

    * Auto outline styles.

    * @var bool

    */

    var $_outline_style;

    /**

    * Whether to have outline summary below.

    * @var bool

    */

    var $_outline_below;

    /**

    * Whether to have outline summary at the right.

    * @var bool

    */

    var $_outline_right;

    /**

    * Outline row level.

    * @var integer

    */

    var $_outline_row_level;

    /**

    * Whether to fit to page when printing or not.

    * @var bool

    */

    var $_fit_page;

    /**

    * Number of pages to fit wide

    * @var integer

    */

    var $_fit_width;

    /**

    * Number of pages to fit high

    * @var integer

    */

    var $_fit_height;

    /**

    * Reference to the total number of strings in the workbook

    * @var integer

    */

    var $_str_total;

    /**

    * Reference to the number of unique strings in the workbook

    * @var integer

    */

    var $_str_unique;

    /**

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

    * @var array

    */

    var $_str_table;

    /**

     * Number of merged cell ranges in actual record

     * @var int $_merged_cells_counter

     */

    var $_merged_cells_counter = 0;

    /**

     * Number of actual mergedcells record

     * @var int $_merged_cells_record

     */

    var $_merged_cells_record = 0;

    /**

    * Merged cell ranges

    * @var array

    */

    var $_merged_ranges;

    /**

    * Charset encoding currently used when calling writeString()

    * @var string

    */

    var $_input_encoding;

    /**

    * Constructor

    *

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

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

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

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

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

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

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

    * @access private

    */

    function Spreadsheet_Excel_Writer_Worksheet($BIFF_version, $name,

                                                $index, &$activesheet,

                                                &$firstsheet, &$str_total,

                                                &$str_unique, &$str_table,

                                                &$url_format, &$parser,

                                                $tmp_dir)

    {

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

        $this->Spreadsheet_Excel_Writer_BIFFwriter();

        $this->_BIFF_version   = $BIFF_version;

        $rowmax                = 65536; // 16384 in Excel 5

        $colmax                = 256;

        $this->name            = $name;

        $this->index           = $index;

        $this->activesheet     = &$activesheet;

        $this->firstsheet      = &$firstsheet;

        $this->_str_total      = &$str_total;

        $this->_str_unique     = &$str_unique;

        $this->_str_table      = &$str_table;

        $this->_url_format     = &$url_format;

        $this->_parser         = &$parser;

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

        $this->_filehandle     = '';

        $this->_using_tmpfile  = true;

        //$this->fileclosed      = 0;

        //$this->offset          = 0;

        $this->_xls_rowmax     = $rowmax;

        $this->_xls_colmax     = $colmax;

        $this->_xls_strmax     = 255;

        $this->_dim_rowmin     = $rowmax + 1;

        $this->_dim_rowmax     = 0;

        $this->_dim_colmin     = $colmax + 1;

        $this->_dim_colmax     = 0;

        $this->_colinfo        = array();

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

        $this->_panes          = array();

        $this->_active_pane    = 3;

        $this->_frozen         = 0;

        $this->selected        = 0;

        $this->_paper_size      = 0x0;

        $this->_orientation     = 0x1;

        $this->_header          = '';

        $this->_footer          = '';

        $this->_hcenter         = 0;

        $this->_vcenter         = 0;

        $this->_margin_head     = 0.50;

        $this->_margin_foot     = 0.50;

        $this->_margin_left     = 0.75;

        $this->_margin_right    = 0.75;

        $this->_margin_top      = 1.00;

        $this->_margin_bottom   = 1.00;

        $this->title_rowmin     = null;

        $this->title_rowmax     = null;

        $this->title_colmin     = null;

        $this->title_colmax     = null;

        $this->print_rowmin     = null;

        $this->print_rowmax     = null;

        $this->print_colmin     = null;

        $this->print_colmax     = null;

        $this->_print_gridlines  = 1;

        $this->_screen_gridlines = 1;

        $this->_print_headers    = 0;

        $this->_fit_page        = 0;

        $this->_fit_width       = 0;

        $this->_fit_height      = 0;

        $this->_hbreaks         = array();

        $this->_vbreaks         = array();

        $this->_protect         = 0;

        $this->_password        = null;

        $this->col_sizes        = array();

        $this->_row_sizes        = array();

        $this->_zoom            = 100;

        $this->_print_scale     = 100;

        $this->_outline_row_level = 0;

        $this->_outline_style     = 0;

        $this->_outline_below     = 1;

        $this->_outline_right     = 1;

        $this->_outline_on        = 1;

        $this->_Arabic            = 0;

        $this->_merged_ranges     = array();

        $this->_input_encoding    = '';

        $this->_dv                = array();

        $this->_tmp_dir           = $tmp_dir;

        $this->_tmp_file          = '';

        $this->_initialize();

    }

    /**

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

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

    * slow for large files.

    *

    * @access private

    */

    function _initialize()

    {

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

            return;

        }

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

            // open_basedir restriction in effect - store data in memory

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

            $this->_using_tmpfile = false;

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

        }

        // Open tmp file for storing Worksheet data

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

            $fh = tmpfile();

        } else {

            // For people with open base dir restriction

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

            $fh = @fopen($this->_tmp_file, "w+b");

        }

        if ($fh === false) {

            // If tmpfile() fails store data in memory

            $this->_using_tmpfile = false;

        } else {

            // Store filehandle

            $this->_filehandle = $fh;

        }

    }

    /**

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

    * and to the end of the workbook.

    *

    * @access public

    * @see Spreadsheet_Excel_Writer_Workbook::storeWorkbook()

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

    *                          worksheet belongs to

    */

    function close($sheetnames)

    {

        $num_sheets = count($sheetnames);

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

        * Prepend in reverse order!!

        */

        // Prepend the sheet dimensions

        $this->_storeDimensions();

        // Prepend the sheet password

        $this->_storePassword();

        // Prepend the sheet protection

        $this->_storeProtect();

        // Prepend the page setup

        $this->_storeSetup();

        /* FIXME: margins are actually appended */

        // Prepend the bottom margin

        $this->_storeMarginBottom();

        // Prepend the top margin

        $this->_storeMarginTop();

        // Prepend the right margin

        $this->_storeMarginRight();

        // Prepend the left margin

        $this->_storeMarginLeft();

        // Prepend the page vertical centering

        $this->_storeVcenter();

        // Prepend the page horizontal centering

        $this->_storeHcenter();

        // Prepend the page footer

        $this->_storeFooter();

        // Prepend the page header

        $this->_storeHeader();

        // Prepend the vertical page breaks

        $this->_storeVbreak();

        // Prepend the horizontal page breaks

        $this->_storeHbreak();

        // Prepend WSBOOL

        $this->_storeWsbool();

        // Prepend GRIDSET

        $this->_storeGridset();

         //  Prepend GUTS

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

            $this->_storeGuts();

        }

        // Prepend PRINTGRIDLINES

        $this->_storePrintGridlines();

        // Prepend PRINTHEADERS

        $this->_storePrintHeaders();

        // Prepend EXTERNSHEET references

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

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

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

                $this->_storeExternsheet($sheetname);

            }

        }

        // Prepend the EXTERNCOUNT of external references.

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

            $this->_storeExterncount($num_sheets);

        }

        // Prepend the COLINFO records if they exist

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

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

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

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

            }

            $this->_storeDefcol();

        }

        // Prepend the BOF record

        $this->_storeBof(0x0010);

        /*

        * End of prepend. Read upwards from here.

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

        // Append

        $this->_storeWindow2();

        $this->_storeZoom();

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

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

        }

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

        $this->_storeMergedCells();

        /* TODO: add data validity */

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

            $this->_storeDataValidity();

        }*/

        $this->_storeEof();

        if ( $this->_tmp_file != '' ) {

          if ( $this->_filehandle ) {

            fclose($this->_filehandle);

            $this->_filehandle = '';

          }

          @unlink($this->_tmp_file);

          $this->_tmp_file      = '';

          $this->_using_tmpfile = true;

        }

    }

    /**

    * Retrieve the worksheet name.

    * This is usefull when creating worksheets without a name.

    *

    * @access public

    * @return string The worksheet's name

    */

    function getName()

    {

        return $this->name;

    }

    /**

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

    * sized chunks.

    *

    * @return string The data

    */

    function getData()

    {

        $buffer = 4096;

        // Return data stored in memory

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

            $tmp   = $this->_data;

            unset($this->_data);

            $fh    = $this->_filehandle;

            if ($this->_using_tmpfile) {

                fseek($fh, 0);

            }

            return $tmp;

        }

        // Return data stored on disk

        if ($this->_using_tmpfile) {

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

                return $tmp;

            }

        }

        // No data to return

        return '';

    }

    /**

    * Sets a merged cell range

    *

    * @access public

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

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

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

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

    */

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

    {

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

            return;

        }

        $max_record_ranges = floor(($this->_limit - 6) / 8);

        if($this->_merged_cells_counter >= $max_record_ranges)

          {

            $this->_merged_cells_record++;

            $this->_merged_cells_counter = 0;

          }

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

        // is going to be called

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

        $this->_merged_cells_counter++;

    }

    /**

    * Set this worksheet as a selected worksheet,

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

    *

    * @access public

    */

    function select()

    {

        $this->selected = 1;

    }

    /**

    * Set this worksheet as the active worksheet,

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

    * Also set it as selected.

    *

    * @access public

    */

    function activate()

    {

        $this->selected = 1;

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

    }

    /**

    * Set this worksheet as the first visible sheet.

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

    * activated worksheet is not visible on the screen.

    *

    * @access public

    */

    function setFirstSheet()

    {

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

    }

    /**

    * Set the worksheet protection flag

    * to prevent accidental modification and to

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

    *

    * @access public

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

    */

    function protect($password)

    {

        $this->_protect   = 1;

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

    }

    /**

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

    *

    * @access public

    * @param integer $firstcol first column on the range

    * @param integer $lastcol  last column on the range

    * @param integer $width    width to set

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

    * @param integer $hidden   The optional hidden atribute

    * @param integer $level    The optional outline level

    */

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

    { // added by Dan Lynn <dan@spiderweblabs.com) on 2006-12-06

        // look for any ranges this might overlap and remove, size or split where necessary

        foreach ($this->_colinfo as $key => $colinfo)

        {

            $existing_start = $colinfo[0]; $existing_end = $colinfo[1];

            // if the new range starts within another range

            if ($firstcol > $existing_start && $firstcol < $existing_end)

            { // trim the existing range to the beginning of the new range

                $this->_colinfo[$key][1] = $firstcol - 1;

                // if the new range lies WITHIN the existing range

                if ($lastcol < $existing_end)

                { // split the existing range by adding a range after our new range

                    $this->_colinfo[] = array($lastcol+1, $existing_end, $colinfo[2], &$colinfo[3], $colinfo[4], $colinfo[5]);

                }

            } // if the new range ends inside an existing range

            elseif ($lastcol > $existing_start && $lastcol < $existing_end)

            { // trim the existing range to the end of the new range

                $this->_colinfo[$key][0] = $lastcol + 1;

            } // if the new range completely overlaps the existing range

            elseif ($firstcol <= $existing_start && $lastcol >= $existing_end)

            {

                unset($this->_colinfo[$key]);

            }

        } // added by Dan Lynn <dan@spiderweblabs.com) on 2006-12-06

        // regenerate keys

        $this->_colinfo = array_values($this->_colinfo);

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

        // Set width to zero if column is hidden

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

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

        {

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

        }

    }

    /**

    * Set which cell or cells are selected in a worksheet

    *

    * @access public

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

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

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

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

    */

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

    {

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

    }

    /**

    * Set panes and mark them as frozen.

    *

    * @access public

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

    *                     0 => Vertical split position,

    *                     1 => Horizontal split position

    *                     2 => Top row visible

    *                     3 => Leftmost column visible

    *                     4 => Active pane

    */

    function freezePanes($panes)

    {

        $this->_frozen = 1;

        $this->_panes  = $panes;

    }

    /**

    * Set panes and mark them as unfrozen.

    *

    * @access public

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

    *                     0 => Vertical split position,

    *                     1 => Horizontal split position

    *                     2 => Top row visible

    *                     3 => Leftmost column visible

    *                     4 => Active pane

    */

    function thawPanes($panes)

    {

        $this->_frozen = 0;

        $this->_panes  = $panes;

    }

    /**

    * Set the page orientation as portrait.

    *

    * @access public

    */

    function setPortrait()

    {

        $this->_orientation = 1;

    }

    /**

    * Set the page orientation as landscape.

    *

    * @access public

    */

    function setLandscape()

    {

        $this->_orientation = 0;

    }

    /**

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

    *

    * @access public

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

    */

    function setPaper($size = 0)

    {

        $this->_paper_size = $size;

    }

    /**

    * Set the page header caption and optional margin.

    *

    * @access public

    * @param string $string The header text

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

    */

    function setHeader($string,$margin = 0.50)

    {

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

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

            return;

        }

        $this->_header      = $string;

        $this->_margin_head = $margin;

    }

    /**

    * Set the page footer caption and optional margin.

    *

    * @access public

    * @param string $string The footer text

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

    */

    function setFooter($string,$margin = 0.50)

    {

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

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

            return;

        }

        $this->_footer      = $string;

        $this->_margin_foot = $margin;

    }

    /**

    * Center the page horinzontally.

    *

    * @access public

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

    */

    function centerHorizontally($center = 1)

    {

        $this->_hcenter = $center;

    }

    /**

    * Center the page vertically.

    *

    * @access public

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

    */

    function centerVertically($center = 1)

    {

        $this->_vcenter = $center;

    }

    /**

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

    *

    * @access public

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

    */

    function setMargins($margin)

    {

        $this->setMarginLeft($margin);

        $this->setMarginRight($margin);

        $this->setMarginTop($margin);

        $this->setMarginBottom($margin);

    }

    /**