/core/vendors/excel/writer/worksheet.php
PHP | 3502 lines | 1738 code | 423 blank | 1341 comment | 267 complexity | d9e77dec2ca22d0b179a78dca96bb85a MD5 | raw file
Possible License(s): GPL-3.0
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_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 CORE_PATH . 'vendors/excel/writer/parser.php';
- require_once CORE_PATH . 'vendors/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 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;
- /**
- * 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
- * @access private
- */
- function Spreadsheet_Excel_Writer_Worksheet($BIFF_version, $name,
- $index, &$activesheet,
- &$firstsheet, &$str_total,
- &$str_unique, &$str_table,
- &$url_format, &$parser)
- {
- // It needs to call its parent's constructor explicitly
- $this->Spreadsheet_Excel_Writer_BIFFwriter();
- $this->_BIFF_version = $BIFF_version;
- $rowmax = 65536; // 16384 in Excel 5
- $colmax = 256;
- $this->name = $name;
- $this->index = $index;
- $this->activesheet = &$activesheet;
- $this->firstsheet = &$firstsheet;
- $this->_str_total = &$str_total;
- $this->_str_unique = &$str_unique;
- $this->_str_table = &$str_table;
- $this->_url_format = &$url_format;
- $this->_parser = &$parser;
- //$this->ext_sheets = array();
- $this->_filehandle = '';
- $this->_using_tmpfile = true;
- //$this->fileclosed = 0;
- //$this->offset = 0;
- $this->_xls_rowmax = $rowmax;
- $this->_xls_colmax = $colmax;
- $this->_xls_strmax = 255;
- $this->_dim_rowmin = $rowmax + 1;
- $this->_dim_rowmax = 0;
- $this->_dim_colmin = $colmax + 1;
- $this->_dim_colmax = 0;
- $this->_colinfo = array();
- $this->_selection = array(0,0,0,0);
- $this->_panes = array();
- $this->_active_pane = 3;
- $this->_frozen = 0;
- $this->selected = 0;
- $this->_paper_size = 0x0;
- $this->_orientation = 0x1;
- $this->_header = '';
- $this->_footer = '';
- $this->_hcenter = 0;
- $this->_vcenter = 0;
- $this->_margin_head = 0.50;
- $this->_margin_foot = 0.50;
- $this->_margin_left = 0.75;
- $this->_margin_right = 0.75;
- $this->_margin_top = 1.00;
- $this->_margin_bottom = 1.00;
- $this->title_rowmin = null;
- $this->title_rowmax = null;
- $this->title_colmin = null;
- $this->title_colmax = null;
- $this->print_rowmin = null;
- $this->print_rowmax = null;
- $this->print_colmin = null;
- $this->print_colmax = null;
- $this->_print_gridlines = 1;
- $this->_screen_gridlines = 1;
- $this->_print_headers = 0;
- $this->_fit_page = 0;
- $this->_fit_width = 0;
- $this->_fit_height = 0;
- $this->_hbreaks = array();
- $this->_vbreaks = array();
- $this->_protect = 0;
- $this->_password = null;
- $this->col_sizes = array();
- $this->_row_sizes = array();
- $this->_zoom = 100;
- $this->_print_scale = 100;
- $this->_outline_row_level = 0;
- $this->_outline_style = 0;
- $this->_outline_below = 1;
- $this->_outline_right = 1;
- $this->_outline_on = 1;
- $this->_merged_ranges = array();
- $this->_input_encoding = '';
- $this->_dv = array();
- $this->_initialize();
- }
- /**
- * Open a tmp file to store the majority of the Worksheet data. If this fails,
- * for example due to write permissions, store the data in memory. This can be
- * slow for large files.
- *
- * @access private
- */
- function _initialize()
- {
- // Open tmp file for storing Worksheet data
- $fh = tmpfile();
- if ($fh) {
- // Store filehandle
- $this->_filehandle = $fh;
- } else {
- // If tmpfile() fails store data in memory
- $this->_using_tmpfile = false;
- }
- }
- /**
- * Add data to the beginning of the workbook (note the reverse order)
- * and to the end of the workbook.
- *
- * @access public
- * @see Spreadsheet_Excel_Writer_Workbook::storeWorkbook()
- * @param array $sheetnames The array of sheetnames from the Workbook this
- * worksheet belongs to
- */
- function close($sheetnames)
- {
- $num_sheets = count($sheetnames);
- /***********************************************
- * Prepend in reverse order!!
- */
- // Prepend the sheet dimensions
- $this->_storeDimensions();
- // Prepend the sheet password
- $this->_storePassword();
- // Prepend the sheet protection
- $this->_storeProtect();
- // Prepend the page setup
- $this->_storeSetup();
- /* 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();
- }
- /**
- * 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;
- }
- // don't check rowmin, rowmax, etc... because we don't know when this
- // is going to be called
- $this->_merged_ranges[] = array($first_row, $first_col, $last_row, $last_col);
- }
- /**
- * 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)
- {
- $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);
- }
- /**
- * Set the left and right margins to the same value in inches.
- *
- * @access public
- * @param float $margin The margin to set in inches
- */
- function setMargins_LR($margin)
- {
- $this->setMarginLeft($margin);
- $this->setMarginRight($margin);
- }
- /**
- * Set the top and bottom margins to the same value in inches.
- *
- * @access public
- * @param float $margin The margin to set in inches
- */
- function setMargins_TB($margin)
- {
- $this->setMarginTop($margin);
- $this->setMarginBottom($margin);
- }
- /**
- * Set the left margin in inches.
- *
- * @access public
- * @param float $margin The margin to set in inches
- */
- function setMarginLeft($margin = 0.75)
- {
- $this->_margin_left = $margin;
- }
- /**
- * Set the right margin in inches.
- *
- * @access public
- * @param float $margin The margin to set in inches
- */
- function setMarginRight($margin = 0.75)
- {
- $this->_margin_right = $margin;
- }
- /**
- * Set the top margin in inches.
- *
- * @access public
- * @param float $margin The margin to set in inches
- */
- function setMarginTop($margin = 1.00)
- {
- $this->_margin_top = $margin;
- }
- /**
- * Set the bottom margin in inches.
- *
- * @access public
- * @param float $margin The margin to set in inches
- */
- function setMarginBottom($margin = 1.00)
- {
- $this->_margin_bottom = $margin;
- }
- /**
- * Set the rows to repeat at the top of each printed page.
- *
- * @access public
- * @param integer $first_row First row to repeat
- * @param integer $last_row Last row to repeat. Optional.
- */
- function repeatRows($first_row, $last_row = null)
- {
- $this->title_rowmin = $first_row;
- if (isset($last_row)) { //Second row is optional
- $this->title_rowmax = $last_row;
- } else {
- $this->title_rowmax = $first_row;
- }
- }
- /**
- * Set the columns to repeat at the left hand side of each printed page.
- *
- * @access public
- * @param integer $first_col First column to repeat
- * @param integer $last_col Last column to repeat. Optional.
- */
- function repeatColumns($first_col, $last_col = null)
- {
- $this->title_colmin = $first_col;
- if (isset($last_col)) { // Second col is optional
- $this->title_colmax = $last_col;
- } else {
- $this->title_colmax = $first_col;
- }
- }
- /**
- * Set the area of each worksheet that will be printed.
- *
- * @access public
- * @param integer $first_row First row of the area to print
- * @param integer $first_col First column of the area to print
- * @param integer $last_row Last row of the area to print
- * @param integer $last_col Last column of the area to print
- */
- function printArea($first_row, $first_col, $last_row, $last_col)
- {
- $this->print_rowmin = $first_row;
- $this->print_colmin = $first_col;
- $this->print_rowmax = $last_row;
- $this->print_colmax = $last_col;
- }
- /**
- * Set the option to hide gridlines on the printed page.
- *
- * @access public
- */
- function hideGridlines()
- {
- $this->_print_gridlines = 0;
- }
- /**
- * Set the option to hide gridlines on the worksheet (as seen on the screen).
- *
- * @access public
- */
- function hideScreenGridlines()
- {
- $this->_screen_gridlines = 0;
- }
- /**
- * Set the option to print the row and column headers on the printed page.
- *
- * @access public
- * @param integer $print Whether to print the headers or not. Defaults to 1 (print).
- */
- function printRowColHeaders($print = 1)
- {
- $this->_print_headers = $print;
- }
- /**
- * Set the vertical and horizontal number of pages that will define the maximum area printed.
- * It doesn't seem to work with OpenOffice.
- *
- * @access public
- * @param integer $width Maximun width of printed area in pages
- * @param integer $height Maximun heigth of printed area in pages
- * @see setPrintScale()
- */
- function fitToPages($width, $height)
- {
- $this->_fit_page = 1;
- $this->_fit_width = $width;
- $this->_fit_height = $height;
- }
- /**
- * Store the horizontal page breaks on a worksheet (for printing).
- * The breaks represent the row after which the break is inserted.
- *
- * @access public
- * @param array $breaks Array containing the horizontal page breaks
- */
- function setHPagebreaks($breaks)
- {
- foreach ($breaks as $break) {
- array_push($this->_hbreaks, $break);
- }
- }
- /**
- * Store the vertical page breaks on a worksheet (for printing).
- * The breaks represent the column after which the break is inserted.
- *
- * @access public
- * @param array $breaks Array containing the vertical page breaks
- */
- function setVPagebreaks($breaks)
- {
- foreach ($breaks as $break) {
- array_push($this->_vbreaks, $break);
- }
- }
- /**
- * Set the worksheet zoom factor.
- *
- * @access public
- * @param integer $scale The zoom factor
- */
- function setZoom($scale = 100)
- {
- // Confine the scale to Excel's range
- if ($scale < 10 || $scale > 400) {
- $this->raiseError("Zoom factor $scale outside range: 10 <= zoom <= 400");
- $scale = 100;
- }
- $this->_zoom = floor($scale);
- }
- /**
- * Set the scale factor for the printed page.
- * It turns off the "fit to page" option
- *
- * @access public
- * @param integer $scale The optional scale factor. Defaults to 100
- */
- function setPrintScale($scale = 100)
- {
- // Confine the scale to Excel's range
- if ($scale < 10 || $scale > 400) {
- $this->raiseError("Print scale $scale outside range: 10 <= zoom <= 400");
- $scale = 100;
- }
- // Turn off "fit to page" option
- $this->_fit_page = 0;
- $this->_print_scale = floor($scale);
- }
- /**
- * Map to the appropriate write method acording to the token recieved.
- *
- * @access public
- * @param integer $row The row of the cell we are writing to
- * @param integer $col The column of the cell we are writing to
- * @param mixed $token What we are writing
- * @param mixed $format The optional format to apply to the cell
- */
- function write($row, $col, $token, $format = null)
- {
- // Check for a cell reference in A1 notation and substitute row and column
- /*if ($_[0] =~ /^\D/) {
- @_ = $this->_substituteCellref(@_);
- }*/
- if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", $token)) {
- // Match number
- return $this->writeNumber($row, $col, $token, $format);
- } elseif (preg_match("/^[fh]tt?p:\/\//", $token)) {
- // Match http or ftp URL
- return $this->writeUrl($row, $col, $token, '', $format);
- } elseif (preg_match("/^mailto:/", $token)) {
- // Match mailto:
- return $this->writeUrl($row, $col, $token, '', $format);
- } elseif (preg_match("/^(?:in|ex)ternal:/", $token)) {
- // Match internal or external sheet link
- return $this->writeUrl($row, $col, $token, '', $format);
- } elseif (preg_match("/^=/", $token)) {
- // Match formula
- return $this->writeFormula($row, $col, $token, $format);
- } elseif (preg_match("/^@/", $token)) {
- // Match formula
- return $this->writeFormula($row, $col, $token, $format);
- } elseif ($token == '') {
- // Match blank
- return $this->writeBlank($row, $col, $format);
- } else {
- // Default: match string
- return $this->writeString($row, $col, $token, $format);
- }
- }
- /**
- * Write an array of values as a row
- *
- * @access public
- * @param integer $row The row we are writing to
- * @param integer $col The first col (leftmost col) we are writing to
- * @param array $val The array of values to write
- * @param mixed $format The optional format to apply to the cell
- * @return mixed PEAR_Error on failure
- */
- function writeRow($row, $col, $val, $format = null)
- {
- $retval = '';
- if (is_array($val)) {
- foreach ($val as $v) {
- if (is_array($v)) {
- $this->writeCol($row, $col, $v, $format);
- } else {
- $this->write($row, $col, $v, $format);
- }
- $col++;
- }
- } else {
- $retval = new PEAR_Error('$val needs to be an array');
- }
- return($retval);
- }
- /**
- * Write an array of values as a column
- *
- * @access public
- * @param integer $row The first row (uppermost row) we are writing to
- * @param integer $col The col we are writing to
- * @param array $val The array of values to write
- * @param mixed $format The optional format to apply to the cell
- * @return mixed PEAR_Error on failure
- */
- function writeCol($row, $col, $val, $format = null)
- {
- $retval = '';
- if (is_array($val)) {
- foreach ($val as $v) {
- $this->write($row, $col, $v, $format);
- $row++;
- }
- } else {
- $retval = new PEAR_Error('$val needs to be an array');
- }
- return($retval);
- }
- /**
- * Returns an index to the XF record in the workbook
- *
- * @access private
- * @param mixed &$format The optional XF format
- * @return integer The XF record index
- */
- function _XF(&$format)
- {
- if ($format) {
- return($format->getXfIndex());
- } else {
- return(0x0F);
- }
- }
- /******************************************************************************
- *******************************************************************************
- *
- * Internal methods
- */
- /**
- * Store Worksheet data in memory using the parent's class append() or to a
- * temporary file, the default.
- *
- * @access private
- * @param string $data The binary data to append
- */
- function _append($data)
- {
- if ($this->_using_tmpfile) {
- // Add CONTINUE records if necessary
- if (strlen($data) > $this->_limit) {
- $data = $this->_addContinue($data);
- }
- fwrite($this->_filehandle, $data);
- $this->_datasize += strlen($data);
- } else {
- parent::_append($data);
- }
- }
- /**
- * Substitute an Excel cell reference in A1 notation for zero based row and
- * column values in an argument list.
- *
- * Ex: ("A4", "Hello") is converted to (3, 0, "Hello").
- *
- * @access private
- * @param string $cell The cell reference. Or range of cells.
- * @return array
- */
- function _substituteCellref($cell)
- {
- $cell = strtoupper($cell);
- // Convert a column range: 'A:A' or 'B:G'
- if (preg_match("/([A-I]?[A-Z]):([A-I]?[A-Z])/", $cell, $match)) {
- list($no_use, $col1) = $this->_cellToRowcol($match[1] .'1'); // Add a dummy row
- list($no_use, $col2) = $this->_cellToRowcol($match[2] .'1'); // Add a dummy row
- return(array($col1, $col2));
- }
- // Convert a cell range: 'A1:B7'
- if (preg_match("/\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/", $cell, $match)) {
- list($row1, $col1) = $this->_cellToRowcol($match[1]);
- list($row2, $col2) = $this->_cellToRowcol($match[2]);
- return(array($row1, $col1, $row2, $col2));
- }
- // Convert a cell reference: 'A1' or 'AD2000'
- if (preg_match("/\$?([A-I]?[A-Z]\$?\d+)/", $cell)) {
- list($row1, $col1) = $this->_cellToRowcol($match[1]);
- return(array($row1, $col1));
- }
- // TODO use real error codes
- $this->raiseError("Unknown cell reference $cell", 0, PEAR_ERROR_DIE);
- }
- /**
- * Convert an Excel cell reference in A1 notation to a zero based row and column
- * reference; converts C1 to (0, 2).
- *
- * @access private
- * @param string $cell The cell reference.
- * @return array containing (row, column)
- */
- function _cellToRowcol($cell)
- {
- preg_match("/\$?([A-I]?[A-Z])\$?(\d+)/",$cell,$match);
- $col = $match[1];
- $row = $match[2];
- // Convert base26 column string to number
- $chars = 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.
- *
- * @access private
- * @param string $plaintext The password to be encoded in plaintext.
- * @return string The encoded password
- */
- function _encodePassword($plaintext)
- {
- $password = 0x0000;
- $i = 1; // char position
- // split the plain text password in its component characters
- $chars = preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY);
- foreach ($chars as $char) {
- $value = ord($char) << $i; // shifted ASCII value
- $rotated_bits = $value >> 15; // rotated bits beyond bit 15
- $value &= 0x7fff; // first 15 bits
- $password ^= ($value | $rotated_bits);
- $i++;
- }
- $password ^= strlen($plaintext);
- $password ^= 0xCE4B;
- return($password);
- }
- /**
- * This method sets the properties for outlining and grouping. The defaults
- * correspond to Excel's defaults.
- *
- * @param bool $visible
- * @param bool $symbols_below
- * @param bool $symbols_right
- * @param bool $auto_style
- */
- function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false)
- {
- $this->_outline_on = $visible;
- $this->_outline_below = $symbols_below;
- $this->_outline_right = $symbols_right;
- $this->_outline_style = $auto_style;
- // Ensure this is a boolean vale for Window2
- if ($this->_outline_on) {
- $this->_outline_on = 1;
- }
- }
- /******************************************************************************
- *******************************************************************************
- *
- * BIFF RECORDS
- */
- /**
- * Write a double to the specified row and column (zero indexed).
- * An integer can be written as a double. Excel will display an
- * integer. $format is optional.
- *
- * Returns 0 : normal termination
- * -2 : row or column out of range
- *
- * @access public
- * @param integer $row Zero indexed row
- * @param integer $col Zero indexed column
- * @param float $num The number to write
- * @param mixed $format The optional XF format
- * @return integer
- */
- function writeNumber($row, $col, $num, $format = null)
- {
- $record = 0x0203; // Record identifier
- $length = 0x000E; // Number of bytes to follow
- $xf = $this->_XF($format); // The cell format
- // Check that row and col are valid and store max and min values
- if ($row >= $this->_xls_rowmax) {
- return(-2);
- }
- if ($col >= $this->_xls_colmax) {
- return(-2);
- }
- if ($row < $this->_dim_rowmin) {
- $this->_dim_rowmin = $row;
- }
- if ($row > $this->_dim_rowmax) {
- $this->_dim_rowmax = $row;
- }
- if ($col < $this->_dim_colmin) {
- $this->_dim_colmin = $col;
- }
- if ($col > $this->_dim_colmax) {
- $this->_dim_colmax = $col;
- }
- $header = pack("vv", $record, $length);
- $data = pack("vvv", $row, $col, $xf);
- $xl_double = pack("d", $num);
- if ($this->_byte_order) { // if it's Big Endian
- $xl_double = strrev($xl_double);
- }
- $this->_append($header.$data.$xl_double);
- return(0);
- }
- /**
- * Write a string to the specified row and column (zero indexed).
- * NOTE: there is an Excel 5 defined limit of 255 characters.
- * $format is optional.
- * Returns 0 : normal termination
- * -2 : row or column out of range
- * -3 : long string truncated to 255 chars
- *
- * @access public
- * @param integer $row Zero indexed row
- * @param integer $col Zero indexed column
- * @param string $str The string to write
- * @param mixed $format The XF format for the cell
- * @return integer
- */
- function writeString($row, $col, $str, $format = null)
- {
- if ($this->_BIFF_version == 0x0600) {
- return $this->writeStringBIFF8($row, $col, $str, $format);
- }
- $strlen = strlen($str);
- $record = 0x0204; // Record identifier
- $length = 0x0008 + $strlen; // Bytes to follow
- $xf = $this->_XF($format); // The cell format
- $str_error = 0;
- // Check that row and col are valid and store max and min values
- if ($row >= $this->_xls_rowmax) {
- return(-2);
- }
- if ($col >= $this->_xls_colmax) {
- return(-2);
- }
- if ($row < $this->_dim_rowmin) {
- $this->_dim_rowmin = $row;
- }
- if ($row > $this->_dim_rowmax) {
- $this->_dim_rowmax = $row;
- }
- if ($col < $this->_dim_colmin) {
- $this->_dim_colmin = $col;
- }
- if ($col > $this->_dim_colmax) {
- $this->_dim_colmax = $col;
- }
- if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars
- $str = substr($str, 0, $this->_xls_strmax);
- $length = 0x0008 + $this->_xls_strmax;
- $strlen = $this->_xls_strmax;
- $str_error = -3;
- }
- $header = pack("vv", $record, $length);
- $data = pack("vvvv", $row, $col, $xf, $strlen);
- $this->_append($header . $data . $str);
- return($str_error);
- }
- /**
- * Sets Input Encoding for writing strings
- *
- * @access public
- * @param string $encoding The encoding. Ex: 'UTF-16LE', 'utf-8', 'ISO-859-7'
- */
- function setInputEncoding($encoding)
- {
- if ($encoding != 'UTF-16LE' && !function_exists('iconv')) {
- $this->raiseError("Using an input encoding other than UTF-16LE requires PHP support for iconv");
- }
- $this->_input_encoding = $encoding;
- }
- /**
- * Write a string to the specified row and column (zero indexed).
- * This is the BIFF8 version (no 255 chars limit).
- * $format is optional.
- * Returns 0 : normal termination
- * -2 : row or column out of range
- * -3 : long string truncated to 255 chars
- *
- * @access public
- * @param integer $row Zero indexed row
- * @param integer $col Zero indexed column
- * @param string $str The string to write
- * @param mixed $format The XF format for the cell
- * @return integer
- */
- function writeStringBIFF8($row, $col, $str, $format = null)
- {
- if ($this->_input_encoding == 'UTF-16LE')
- {
- $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
- $encoding = 0x1;
- }
- elseif ($this->_input_encoding != '')
- {
- $str = iconv($this->_input_encoding, 'UTF-16LE', $str);
- $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
- $encoding = 0x1;
- }
- else
- {
- $strlen = strlen($str);
- $encoding = 0x0;
- }
- $record = 0x00FD; // Record identifier
- $length = 0x000A; // Bytes to follow
- $xf = $this->_XF($format); // The cell format
- $str_error = 0;
- // Check that row and col are valid and store max and min values
- if ($this->_checkRowCol($row, $col) == false) {
- return -2;
- }
- $str = pack('vC', $strlen, $encoding).$str;
- /* check if string is already present */
- if (!isset($this->_str_table[$str])) {
- $this->_str_table[$str] = $this->_str_unique++;
- }
- $this->_str_total++;
- $header = pack('vv', $record, $length);
- $data = pack('vvvV', $row, $col, $xf, $this->_str_table[$str]);
- $this->_append($header.$data);
- return $str_error;
- }
- /**
- * Check row and col before writing to a cell, and update the sheet's
- * dimensions accordingly
- *
- * @access private
- * @param integer $row Zero indexed row
- * @param integer $col Zero indexed column
- * @return boolean true for success, false if row and/or col are grester
- * then maximums allowed.
- */
- function _checkRowCol($row, $col)
- {
- if ($row >= $this->_xls_rowmax) {
- return false;
- }
- if ($col >= $this->_xls_colmax) {
- return false;
- }
- if ($row < $this->_dim_rowmin) {
- $this->_dim_rowmin = $row;
- }
- if ($row > $this->_dim_rowmax) {
- $this->_dim_rowmax = $row;
- }
- if ($col < $this->_dim_colmin) {
- $this->_dim_colmin = $col;
- }
- if ($col > $this->_dim_colmax) {
- $this->_dim_colmax = $col;
- }
- return true;
- }
- /**
- * Writes a note associated with the cell given by the row and column.
- * NOTE records don't have a length limit.
- *
- * @access public
- * @param integer $row Zero indexed row
- * @param integer $col Zero indexed column
- * @param string $note The note to write
- */
- function writeNote($row, $col, $note)
- {
- $note_length = strlen($note);
- $record = 0x001C; // Record identifier
- $max_length = 2048; // Maximun length for a NOTE record
- //$length = 0x0006 + $note_length; // Bytes to follow
- // Check that row and col are valid and store max and min values
- if ($row >= $this->_xls_rowmax) {
- return(-2);
- }
- if ($col >= $this->_xls_colmax) {
- return(-2);
- }
- if ($row < $this->_dim_rowmin) {
- $this->_dim_rowmin = $row;
- }
- if ($row > $this->_dim_rowmax) {
- $this->_dim_rowmax = $row;
- }
- if ($col < $this->_dim_colmin) {
- $this->_dim_colmin = $col;
- }
- if ($col > $this->_dim_colmax) {
- $this->_dim_colmax = $col;
- }
- // Length for this record is no more than 2048 + 6
- $length = 0x0006 + min($note_length, 2048);
- $header = pack("vv", $record, $length);
- $data = pack("vvv", $row, $col, $note_length);
- $this->_append($header . $data . substr($note, 0, 2048));
- for ($i = $max_length; $i < $note_length; $i += $max_length) {
- $chunk = substr($note, $i, $max_length);
- $length = 0x0006 + strlen($chunk);
- $header = pack("vv", $record, $length);
- $data = pack("vvv", -1, 0, strlen($chunk));
- $this->_append($header.$data.$chunk);
- }
- return(0);
- }
- /**
- * Write a blank cell to the specified row and column (zero indexed).
- * A blank cell is used to specify formatting without adding a string
- * or a number.
- *
- * A blank cell without a format serves no purpose. Therefore, we don't write
- * a BLANK record unless a format is specified.
- *
- * Returns 0 : normal termination (including no format)
- * -1 : insufficient number of arguments
- * -2 : row or column out of range
- *
- * @access public
- * @param integer $row Zero indexed row
- * @param integer $col Zero indexed column
- * @param mixed $format The XF format
- */
- function writeBlank($row, $col, $format)
- {
- // Don't write a blank cell unless it has a format
- if (!$format) {
- return(0);
- }
- $record = 0x0201; // Record identifier
- $length = 0x0006; // Number of bytes to follow
- $xf = $this->_XF($format); // The cell format
- // Check that row and col are valid and store max and min values
- if ($row >= $this->_xls_rowmax) {
- return(-2);
- }
- if ($col >= $this->_xls_colmax) {
- return(-2);
- }
- if ($row < $this->_dim_rowmin) {
- $this->_dim_rowmin = $row;
- }
- if ($row > $this->_dim_rowmax) {
- $this->_dim_rowmax = $row;
- }
- if ($col < $this->_dim_colmin) {
- $this->_dim_colmin = $col;
- }
- if ($col > $this->_dim_colmax) {
- $this->_dim_colmax = $col;
- }
- $header = pack("vv", $record, $length);
- $data = pack("vvv", $row, $col, $xf);
- $this->_append($header . $data);
- return 0;
- }
- /**
- * Write a formula to the specified row and column (zero indexed).
- * The textual representation of the formula is passed to the parser in
- * Parser.php which returns a packed binary string.
- *
- * Returns 0 : normal termination
- * -1 : formula errors (bad formula)
- * -2 : row or column out of range
- *
- * @access public
- * @param integer $row Zero indexed row
- * @param integer $col Zero indexed column
- * @param string $formula The formula text string
- * @param mixed $format The optional XF format
- * @return integer
- */
- function writeFormula($row, $col, $formula, $format = null)
- {
- $record = 0x0006; // Record identifier
- // Excel normally stores the last calculated value of the formula in $num.
- // Clearly we are not in a position to calculate this a priori. Instead
- // we set $num to zero and set the option flags in $grbit to ensure
- // automatic calculation of the formula when the file is opened.
- //
- $xf = $this->_XF($format); // The cell format
- $num = 0x00; // Current value of formula
- $grbit = 0x03; // Op…
Large files files are truncated, but you can click here to view the full file