PageRenderTime 226ms CodeModel.GetById 60ms app.highlight 114ms RepoModel.GetById 37ms app.codeStats 1ms

/functions/PEAR/Spreadsheet/Excel/Writer/Parser.php

https://bitbucket.org/bertramtruong/phpipam
PHP | 1703 lines | 1500 code | 33 blank | 170 comment | 20 complexity | e55934f36468f623b1a8a17ec5787e84 MD5 | raw file

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

   1<?php
   2/**
   3*  Class for parsing Excel formulas
   4*
   5*  License Information:
   6*
   7*    Spreadsheet_Excel_Writer:  A library for generating Excel Spreadsheets
   8*    Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com
   9*
  10*    This library is free software; you can redistribute it and/or
  11*    modify it under the terms of the GNU Lesser General Public
  12*    License as published by the Free Software Foundation; either
  13*    version 2.1 of the License, or (at your option) any later version.
  14*
  15*    This library is distributed in the hope that it will be useful,
  16*    but WITHOUT ANY WARRANTY; without even the implied warranty of
  17*    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  18*    Lesser General Public License for more details.
  19*
  20*    You should have received a copy of the GNU Lesser General Public
  21*    License along with this library; if not, write to the Free Software
  22*    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  23*/
  24
  25/**
  26* @const SPREADSHEET_EXCEL_WRITER_ADD token identifier for character "+"
  27*/
  28define('SPREADSHEET_EXCEL_WRITER_ADD', "+");
  29
  30/**
  31* @const SPREADSHEET_EXCEL_WRITER_SUB token identifier for character "-"
  32*/
  33define('SPREADSHEET_EXCEL_WRITER_SUB', "-");
  34
  35/**
  36* @const SPREADSHEET_EXCEL_WRITER_MUL token identifier for character "*"
  37*/
  38define('SPREADSHEET_EXCEL_WRITER_MUL', "*");
  39
  40/**
  41* @const SPREADSHEET_EXCEL_WRITER_DIV token identifier for character "/"
  42*/
  43define('SPREADSHEET_EXCEL_WRITER_DIV', "/");
  44
  45/**
  46* @const SPREADSHEET_EXCEL_WRITER_OPEN token identifier for character "("
  47*/
  48define('SPREADSHEET_EXCEL_WRITER_OPEN', "(");
  49
  50/**
  51* @const SPREADSHEET_EXCEL_WRITER_CLOSE token identifier for character ")"
  52*/
  53define('SPREADSHEET_EXCEL_WRITER_CLOSE', ")");
  54
  55/**
  56* @const SPREADSHEET_EXCEL_WRITER_COMA token identifier for character ","
  57*/
  58define('SPREADSHEET_EXCEL_WRITER_COMA', ",");
  59
  60/**
  61* @const SPREADSHEET_EXCEL_WRITER_SEMICOLON token identifier for character ";"
  62*/
  63define('SPREADSHEET_EXCEL_WRITER_SEMICOLON', ";");
  64
  65/**
  66* @const SPREADSHEET_EXCEL_WRITER_GT token identifier for character ">"
  67*/
  68define('SPREADSHEET_EXCEL_WRITER_GT', ">");
  69
  70/**
  71* @const SPREADSHEET_EXCEL_WRITER_LT token identifier for character "<"
  72*/
  73define('SPREADSHEET_EXCEL_WRITER_LT', "<");
  74
  75/**
  76* @const SPREADSHEET_EXCEL_WRITER_LE token identifier for character "<="
  77*/
  78define('SPREADSHEET_EXCEL_WRITER_LE', "<=");
  79
  80/**
  81* @const SPREADSHEET_EXCEL_WRITER_GE token identifier for character ">="
  82*/
  83define('SPREADSHEET_EXCEL_WRITER_GE', ">=");
  84
  85/**
  86* @const SPREADSHEET_EXCEL_WRITER_EQ token identifier for character "="
  87*/
  88define('SPREADSHEET_EXCEL_WRITER_EQ', "=");
  89
  90/**
  91* @const SPREADSHEET_EXCEL_WRITER_NE token identifier for character "<>"
  92*/
  93define('SPREADSHEET_EXCEL_WRITER_NE', "<>");
  94
  95/**
  96* * @const SPREADSHEET_EXCEL_WRITER_CONCAT token identifier for character "&"
  97*/
  98define('SPREADSHEET_EXCEL_WRITER_CONCAT', "&");
  99
 100require_once 'PEAR.php';
 101
 102/**
 103* Class for parsing Excel formulas
 104*
 105* @author   Xavier Noguer <xnoguer@rezebra.com>
 106* @category FileFormats
 107* @package  Spreadsheet_Excel_Writer
 108*/
 109
 110class Spreadsheet_Excel_Writer_Parser extends PEAR
 111{
 112    /**
 113    * The index of the character we are currently looking at
 114    * @var integer
 115    */
 116    var $_current_char;
 117
 118    /**
 119    * The token we are working on.
 120    * @var string
 121    */
 122    var $_current_token;
 123
 124    /**
 125    * The formula to parse
 126    * @var string
 127    */
 128    var $_formula;
 129
 130    /**
 131    * The character ahead of the current char
 132    * @var string
 133    */
 134    var $_lookahead;
 135
 136    /**
 137    * The parse tree to be generated
 138    * @var string
 139    */
 140    var $_parse_tree;
 141
 142    /**
 143    * The byte order. 1 => big endian, 0 => little endian.
 144    * @var integer
 145    */
 146    var $_byte_order;
 147
 148    /**
 149    * Array of external sheets
 150    * @var array
 151    */
 152    var $_ext_sheets;
 153
 154    /**
 155    * Array of sheet references in the form of REF structures
 156    * @var array
 157    */
 158    var $_references;
 159
 160    /**
 161    * The BIFF version for the workbook
 162    * @var integer
 163    */
 164    var $_BIFF_version;
 165
 166    /**
 167    * The class constructor
 168    *
 169    * @param integer $byte_order The byte order (Little endian or Big endian) of the architecture
 170                                 (optional). 1 => big endian, 0 (default) little endian.
 171    */
 172    function Spreadsheet_Excel_Writer_Parser($byte_order, $biff_version)
 173    {
 174        $this->_current_char  = 0;
 175        $this->_BIFF_version  = $biff_version;
 176        $this->_current_token = '';       // The token we are working on.
 177        $this->_formula       = '';       // The formula to parse.
 178        $this->_lookahead     = '';       // The character ahead of the current char.
 179        $this->_parse_tree    = '';       // The parse tree to be generated.
 180        $this->_initializeHashes();      // Initialize the hashes: ptg's and function's ptg's
 181        $this->_byte_order = $byte_order; // Little Endian or Big Endian
 182        $this->_ext_sheets = array();
 183        $this->_references = array();
 184    }
 185
 186    /**
 187    * Initialize the ptg and function hashes.
 188    *
 189    * @access private
 190    */
 191    function _initializeHashes()
 192    {
 193        // The Excel ptg indices
 194        $this->ptg = array(
 195            'ptgExp'       => 0x01,
 196            'ptgTbl'       => 0x02,
 197            'ptgAdd'       => 0x03,
 198            'ptgSub'       => 0x04,
 199            'ptgMul'       => 0x05,
 200            'ptgDiv'       => 0x06,
 201            'ptgPower'     => 0x07,
 202            'ptgConcat'    => 0x08,
 203            'ptgLT'        => 0x09,
 204            'ptgLE'        => 0x0A,
 205            'ptgEQ'        => 0x0B,
 206            'ptgGE'        => 0x0C,
 207            'ptgGT'        => 0x0D,
 208            'ptgNE'        => 0x0E,
 209            'ptgIsect'     => 0x0F,
 210            'ptgUnion'     => 0x10,
 211            'ptgRange'     => 0x11,
 212            'ptgUplus'     => 0x12,
 213            'ptgUminus'    => 0x13,
 214            'ptgPercent'   => 0x14,
 215            'ptgParen'     => 0x15,
 216            'ptgMissArg'   => 0x16,
 217            'ptgStr'       => 0x17,
 218            'ptgAttr'      => 0x19,
 219            'ptgSheet'     => 0x1A,
 220            'ptgEndSheet'  => 0x1B,
 221            'ptgErr'       => 0x1C,
 222            'ptgBool'      => 0x1D,
 223            'ptgInt'       => 0x1E,
 224            'ptgNum'       => 0x1F,
 225            'ptgArray'     => 0x20,
 226            'ptgFunc'      => 0x21,
 227            'ptgFuncVar'   => 0x22,
 228            'ptgName'      => 0x23,
 229            'ptgRef'       => 0x24,
 230            'ptgArea'      => 0x25,
 231            'ptgMemArea'   => 0x26,
 232            'ptgMemErr'    => 0x27,
 233            'ptgMemNoMem'  => 0x28,
 234            'ptgMemFunc'   => 0x29,
 235            'ptgRefErr'    => 0x2A,
 236            'ptgAreaErr'   => 0x2B,
 237            'ptgRefN'      => 0x2C,
 238            'ptgAreaN'     => 0x2D,
 239            'ptgMemAreaN'  => 0x2E,
 240            'ptgMemNoMemN' => 0x2F,
 241            'ptgNameX'     => 0x39,
 242            'ptgRef3d'     => 0x3A,
 243            'ptgArea3d'    => 0x3B,
 244            'ptgRefErr3d'  => 0x3C,
 245            'ptgAreaErr3d' => 0x3D,
 246            'ptgArrayV'    => 0x40,
 247            'ptgFuncV'     => 0x41,
 248            'ptgFuncVarV'  => 0x42,
 249            'ptgNameV'     => 0x43,
 250            'ptgRefV'      => 0x44,
 251            'ptgAreaV'     => 0x45,
 252            'ptgMemAreaV'  => 0x46,
 253            'ptgMemErrV'   => 0x47,
 254            'ptgMemNoMemV' => 0x48,
 255            'ptgMemFuncV'  => 0x49,
 256            'ptgRefErrV'   => 0x4A,
 257            'ptgAreaErrV'  => 0x4B,
 258            'ptgRefNV'     => 0x4C,
 259            'ptgAreaNV'    => 0x4D,
 260            'ptgMemAreaNV' => 0x4E,
 261            'ptgMemNoMemN' => 0x4F,
 262            'ptgFuncCEV'   => 0x58,
 263            'ptgNameXV'    => 0x59,
 264            'ptgRef3dV'    => 0x5A,
 265            'ptgArea3dV'   => 0x5B,
 266            'ptgRefErr3dV' => 0x5C,
 267            'ptgAreaErr3d' => 0x5D,
 268            'ptgArrayA'    => 0x60,
 269            'ptgFuncA'     => 0x61,
 270            'ptgFuncVarA'  => 0x62,
 271            'ptgNameA'     => 0x63,
 272            'ptgRefA'      => 0x64,
 273            'ptgAreaA'     => 0x65,
 274            'ptgMemAreaA'  => 0x66,
 275            'ptgMemErrA'   => 0x67,
 276            'ptgMemNoMemA' => 0x68,
 277            'ptgMemFuncA'  => 0x69,
 278            'ptgRefErrA'   => 0x6A,
 279            'ptgAreaErrA'  => 0x6B,
 280            'ptgRefNA'     => 0x6C,
 281            'ptgAreaNA'    => 0x6D,
 282            'ptgMemAreaNA' => 0x6E,
 283            'ptgMemNoMemN' => 0x6F,
 284            'ptgFuncCEA'   => 0x78,
 285            'ptgNameXA'    => 0x79,
 286            'ptgRef3dA'    => 0x7A,
 287            'ptgArea3dA'   => 0x7B,
 288            'ptgRefErr3dA' => 0x7C,
 289            'ptgAreaErr3d' => 0x7D
 290            );
 291
 292        // Thanks to Michael Meeks and Gnumeric for the initial arg values.
 293        //
 294        // The following hash was generated by "function_locale.pl" in the distro.
 295        // Refer to function_locale.pl for non-English function names.
 296        //
 297        // The array elements are as follow:
 298        // ptg:   The Excel function ptg code.
 299        // args:  The number of arguments that the function takes:
 300        //           >=0 is a fixed number of arguments.
 301        //           -1  is a variable  number of arguments.
 302        // class: The reference, value or array class of the function args.
 303        // vol:   The function is volatile.
 304        //
 305        $this->_functions = array(
 306              // function                  ptg  args  class  vol
 307              'COUNT'           => array(   0,   -1,    0,    0 ),
 308              'IF'              => array(   1,   -1,    1,    0 ),
 309              'ISNA'            => array(   2,    1,    1,    0 ),
 310              'ISERROR'         => array(   3,    1,    1,    0 ),
 311              'SUM'             => array(   4,   -1,    0,    0 ),
 312              'AVERAGE'         => array(   5,   -1,    0,    0 ),
 313              'MIN'             => array(   6,   -1,    0,    0 ),
 314              'MAX'             => array(   7,   -1,    0,    0 ),
 315              'ROW'             => array(   8,   -1,    0,    0 ),
 316              'COLUMN'          => array(   9,   -1,    0,    0 ),
 317              'NA'              => array(  10,    0,    0,    0 ),
 318              'NPV'             => array(  11,   -1,    1,    0 ),
 319              'STDEV'           => array(  12,   -1,    0,    0 ),
 320              'DOLLAR'          => array(  13,   -1,    1,    0 ),
 321              'FIXED'           => array(  14,   -1,    1,    0 ),
 322              'SIN'             => array(  15,    1,    1,    0 ),
 323              'COS'             => array(  16,    1,    1,    0 ),
 324              'TAN'             => array(  17,    1,    1,    0 ),
 325              'ATAN'            => array(  18,    1,    1,    0 ),
 326              'PI'              => array(  19,    0,    1,    0 ),
 327              'SQRT'            => array(  20,    1,    1,    0 ),
 328              'EXP'             => array(  21,    1,    1,    0 ),
 329              'LN'              => array(  22,    1,    1,    0 ),
 330              'LOG10'           => array(  23,    1,    1,    0 ),
 331              'ABS'             => array(  24,    1,    1,    0 ),
 332              'INT'             => array(  25,    1,    1,    0 ),
 333              'SIGN'            => array(  26,    1,    1,    0 ),
 334              'ROUND'           => array(  27,    2,    1,    0 ),
 335              'LOOKUP'          => array(  28,   -1,    0,    0 ),
 336              'INDEX'           => array(  29,   -1,    0,    1 ),
 337              'REPT'            => array(  30,    2,    1,    0 ),
 338              'MID'             => array(  31,    3,    1,    0 ),
 339              'LEN'             => array(  32,    1,    1,    0 ),
 340              'VALUE'           => array(  33,    1,    1,    0 ),
 341              'TRUE'            => array(  34,    0,    1,    0 ),
 342              'FALSE'           => array(  35,    0,    1,    0 ),
 343              'AND'             => array(  36,   -1,    0,    0 ),
 344              'OR'              => array(  37,   -1,    0,    0 ),
 345              'NOT'             => array(  38,    1,    1,    0 ),
 346              'MOD'             => array(  39,    2,    1,    0 ),
 347              'DCOUNT'          => array(  40,    3,    0,    0 ),
 348              'DSUM'            => array(  41,    3,    0,    0 ),
 349              'DAVERAGE'        => array(  42,    3,    0,    0 ),
 350              'DMIN'            => array(  43,    3,    0,    0 ),
 351              'DMAX'            => array(  44,    3,    0,    0 ),
 352              'DSTDEV'          => array(  45,    3,    0,    0 ),
 353              'VAR'             => array(  46,   -1,    0,    0 ),
 354              'DVAR'            => array(  47,    3,    0,    0 ),
 355              'TEXT'            => array(  48,    2,    1,    0 ),
 356              'LINEST'          => array(  49,   -1,    0,    0 ),
 357              'TREND'           => array(  50,   -1,    0,    0 ),
 358              'LOGEST'          => array(  51,   -1,    0,    0 ),
 359              'GROWTH'          => array(  52,   -1,    0,    0 ),
 360              'PV'              => array(  56,   -1,    1,    0 ),
 361              'FV'              => array(  57,   -1,    1,    0 ),
 362              'NPER'            => array(  58,   -1,    1,    0 ),
 363              'PMT'             => array(  59,   -1,    1,    0 ),
 364              'RATE'            => array(  60,   -1,    1,    0 ),
 365              'MIRR'            => array(  61,    3,    0,    0 ),
 366              'IRR'             => array(  62,   -1,    0,    0 ),
 367              'RAND'            => array(  63,    0,    1,    1 ),
 368              'MATCH'           => array(  64,   -1,    0,    0 ),
 369              'DATE'            => array(  65,    3,    1,    0 ),
 370              'TIME'            => array(  66,    3,    1,    0 ),
 371              'DAY'             => array(  67,    1,    1,    0 ),
 372              'MONTH'           => array(  68,    1,    1,    0 ),
 373              'YEAR'            => array(  69,    1,    1,    0 ),
 374              'WEEKDAY'         => array(  70,   -1,    1,    0 ),
 375              'HOUR'            => array(  71,    1,    1,    0 ),
 376              'MINUTE'          => array(  72,    1,    1,    0 ),
 377              'SECOND'          => array(  73,    1,    1,    0 ),
 378              'NOW'             => array(  74,    0,    1,    1 ),
 379              'AREAS'           => array(  75,    1,    0,    1 ),
 380              'ROWS'            => array(  76,    1,    0,    1 ),
 381              'COLUMNS'         => array(  77,    1,    0,    1 ),
 382              'OFFSET'          => array(  78,   -1,    0,    1 ),
 383              'SEARCH'          => array(  82,   -1,    1,    0 ),
 384              'TRANSPOSE'       => array(  83,    1,    1,    0 ),
 385              'TYPE'            => array(  86,    1,    1,    0 ),
 386              'ATAN2'           => array(  97,    2,    1,    0 ),
 387              'ASIN'            => array(  98,    1,    1,    0 ),
 388              'ACOS'            => array(  99,    1,    1,    0 ),
 389              'CHOOSE'          => array( 100,   -1,    1,    0 ),
 390              'HLOOKUP'         => array( 101,   -1,    0,    0 ),
 391              'VLOOKUP'         => array( 102,   -1,    0,    0 ),
 392              'ISREF'           => array( 105,    1,    0,    0 ),
 393              'LOG'             => array( 109,   -1,    1,    0 ),
 394              'CHAR'            => array( 111,    1,    1,    0 ),
 395              'LOWER'           => array( 112,    1,    1,    0 ),
 396              'UPPER'           => array( 113,    1,    1,    0 ),
 397              'PROPER'          => array( 114,    1,    1,    0 ),
 398              'LEFT'            => array( 115,   -1,    1,    0 ),
 399              'RIGHT'           => array( 116,   -1,    1,    0 ),
 400              'EXACT'           => array( 117,    2,    1,    0 ),
 401              'TRIM'            => array( 118,    1,    1,    0 ),
 402              'REPLACE'         => array( 119,    4,    1,    0 ),
 403              'SUBSTITUTE'      => array( 120,   -1,    1,    0 ),
 404              'CODE'            => array( 121,    1,    1,    0 ),
 405              'FIND'            => array( 124,   -1,    1,    0 ),
 406              'CELL'            => array( 125,   -1,    0,    1 ),
 407              'ISERR'           => array( 126,    1,    1,    0 ),
 408              'ISTEXT'          => array( 127,    1,    1,    0 ),
 409              'ISNUMBER'        => array( 128,    1,    1,    0 ),
 410              'ISBLANK'         => array( 129,    1,    1,    0 ),
 411              'T'               => array( 130,    1,    0,    0 ),
 412              'N'               => array( 131,    1,    0,    0 ),
 413              'DATEVALUE'       => array( 140,    1,    1,    0 ),
 414              'TIMEVALUE'       => array( 141,    1,    1,    0 ),
 415              'SLN'             => array( 142,    3,    1,    0 ),
 416              'SYD'             => array( 143,    4,    1,    0 ),
 417              'DDB'             => array( 144,   -1,    1,    0 ),
 418              'INDIRECT'        => array( 148,   -1,    1,    1 ),
 419              'CALL'            => array( 150,   -1,    1,    0 ),
 420              'CLEAN'           => array( 162,    1,    1,    0 ),
 421              'MDETERM'         => array( 163,    1,    2,    0 ),
 422              'MINVERSE'        => array( 164,    1,    2,    0 ),
 423              'MMULT'           => array( 165,    2,    2,    0 ),
 424              'IPMT'            => array( 167,   -1,    1,    0 ),
 425              'PPMT'            => array( 168,   -1,    1,    0 ),
 426              'COUNTA'          => array( 169,   -1,    0,    0 ),
 427              'PRODUCT'         => array( 183,   -1,    0,    0 ),
 428              'FACT'            => array( 184,    1,    1,    0 ),
 429              'DPRODUCT'        => array( 189,    3,    0,    0 ),
 430              'ISNONTEXT'       => array( 190,    1,    1,    0 ),
 431              'STDEVP'          => array( 193,   -1,    0,    0 ),
 432              'VARP'            => array( 194,   -1,    0,    0 ),
 433              'DSTDEVP'         => array( 195,    3,    0,    0 ),
 434              'DVARP'           => array( 196,    3,    0,    0 ),
 435              'TRUNC'           => array( 197,   -1,    1,    0 ),
 436              'ISLOGICAL'       => array( 198,    1,    1,    0 ),
 437              'DCOUNTA'         => array( 199,    3,    0,    0 ),
 438              'ROUNDUP'         => array( 212,    2,    1,    0 ),
 439              'ROUNDDOWN'       => array( 213,    2,    1,    0 ),
 440              'RANK'            => array( 216,   -1,    0,    0 ),
 441              'ADDRESS'         => array( 219,   -1,    1,    0 ),
 442              'DAYS360'         => array( 220,   -1,    1,    0 ),
 443              'TODAY'           => array( 221,    0,    1,    1 ),
 444              'VDB'             => array( 222,   -1,    1,    0 ),
 445              'MEDIAN'          => array( 227,   -1,    0,    0 ),
 446              'SUMPRODUCT'      => array( 228,   -1,    2,    0 ),
 447              'SINH'            => array( 229,    1,    1,    0 ),
 448              'COSH'            => array( 230,    1,    1,    0 ),
 449              'TANH'            => array( 231,    1,    1,    0 ),
 450              'ASINH'           => array( 232,    1,    1,    0 ),
 451              'ACOSH'           => array( 233,    1,    1,    0 ),
 452              'ATANH'           => array( 234,    1,    1,    0 ),
 453              'DGET'            => array( 235,    3,    0,    0 ),
 454              'INFO'            => array( 244,    1,    1,    1 ),
 455              'DB'              => array( 247,   -1,    1,    0 ),
 456              'FREQUENCY'       => array( 252,    2,    0,    0 ),
 457              'ERROR.TYPE'      => array( 261,    1,    1,    0 ),
 458              'REGISTER.ID'     => array( 267,   -1,    1,    0 ),
 459              'AVEDEV'          => array( 269,   -1,    0,    0 ),
 460              'BETADIST'        => array( 270,   -1,    1,    0 ),
 461              'GAMMALN'         => array( 271,    1,    1,    0 ),
 462              'BETAINV'         => array( 272,   -1,    1,    0 ),
 463              'BINOMDIST'       => array( 273,    4,    1,    0 ),
 464              'CHIDIST'         => array( 274,    2,    1,    0 ),
 465              'CHIINV'          => array( 275,    2,    1,    0 ),
 466              'COMBIN'          => array( 276,    2,    1,    0 ),
 467              'CONFIDENCE'      => array( 277,    3,    1,    0 ),
 468              'CRITBINOM'       => array( 278,    3,    1,    0 ),
 469              'EVEN'            => array( 279,    1,    1,    0 ),
 470              'EXPONDIST'       => array( 280,    3,    1,    0 ),
 471              'FDIST'           => array( 281,    3,    1,    0 ),
 472              'FINV'            => array( 282,    3,    1,    0 ),
 473              'FISHER'          => array( 283,    1,    1,    0 ),
 474              'FISHERINV'       => array( 284,    1,    1,    0 ),
 475              'FLOOR'           => array( 285,    2,    1,    0 ),
 476              'GAMMADIST'       => array( 286,    4,    1,    0 ),
 477              'GAMMAINV'        => array( 287,    3,    1,    0 ),
 478              'CEILING'         => array( 288,    2,    1,    0 ),
 479              'HYPGEOMDIST'     => array( 289,    4,    1,    0 ),
 480              'LOGNORMDIST'     => array( 290,    3,    1,    0 ),
 481              'LOGINV'          => array( 291,    3,    1,    0 ),
 482              'NEGBINOMDIST'    => array( 292,    3,    1,    0 ),
 483              'NORMDIST'        => array( 293,    4,    1,    0 ),
 484              'NORMSDIST'       => array( 294,    1,    1,    0 ),
 485              'NORMINV'         => array( 295,    3,    1,    0 ),
 486              'NORMSINV'        => array( 296,    1,    1,    0 ),
 487              'STANDARDIZE'     => array( 297,    3,    1,    0 ),
 488              'ODD'             => array( 298,    1,    1,    0 ),
 489              'PERMUT'          => array( 299,    2,    1,    0 ),
 490              'POISSON'         => array( 300,    3,    1,    0 ),
 491              'TDIST'           => array( 301,    3,    1,    0 ),
 492              'WEIBULL'         => array( 302,    4,    1,    0 ),
 493              'SUMXMY2'         => array( 303,    2,    2,    0 ),
 494              'SUMX2MY2'        => array( 304,    2,    2,    0 ),
 495              'SUMX2PY2'        => array( 305,    2,    2,    0 ),
 496              'CHITEST'         => array( 306,    2,    2,    0 ),
 497              'CORREL'          => array( 307,    2,    2,    0 ),
 498              'COVAR'           => array( 308,    2,    2,    0 ),
 499              'FORECAST'        => array( 309,    3,    2,    0 ),
 500              'FTEST'           => array( 310,    2,    2,    0 ),
 501              'INTERCEPT'       => array( 311,    2,    2,    0 ),
 502              'PEARSON'         => array( 312,    2,    2,    0 ),
 503              'RSQ'             => array( 313,    2,    2,    0 ),
 504              'STEYX'           => array( 314,    2,    2,    0 ),
 505              'SLOPE'           => array( 315,    2,    2,    0 ),
 506              'TTEST'           => array( 316,    4,    2,    0 ),
 507              'PROB'            => array( 317,   -1,    2,    0 ),
 508              'DEVSQ'           => array( 318,   -1,    0,    0 ),
 509              'GEOMEAN'         => array( 319,   -1,    0,    0 ),
 510              'HARMEAN'         => array( 320,   -1,    0,    0 ),
 511              'SUMSQ'           => array( 321,   -1,    0,    0 ),
 512              'KURT'            => array( 322,   -1,    0,    0 ),
 513              'SKEW'            => array( 323,   -1,    0,    0 ),
 514              'ZTEST'           => array( 324,   -1,    0,    0 ),
 515              'LARGE'           => array( 325,    2,    0,    0 ),
 516              'SMALL'           => array( 326,    2,    0,    0 ),
 517              'QUARTILE'        => array( 327,    2,    0,    0 ),
 518              'PERCENTILE'      => array( 328,    2,    0,    0 ),
 519              'PERCENTRANK'     => array( 329,   -1,    0,    0 ),
 520              'MODE'            => array( 330,   -1,    2,    0 ),
 521              'TRIMMEAN'        => array( 331,    2,    0,    0 ),
 522              'TINV'            => array( 332,    2,    1,    0 ),
 523              'CONCATENATE'     => array( 336,   -1,    1,    0 ),
 524              'POWER'           => array( 337,    2,    1,    0 ),
 525              'RADIANS'         => array( 342,    1,    1,    0 ),
 526              'DEGREES'         => array( 343,    1,    1,    0 ),
 527              'SUBTOTAL'        => array( 344,   -1,    0,    0 ),
 528              'SUMIF'           => array( 345,   -1,    0,    0 ),
 529              'COUNTIF'         => array( 346,    2,    0,    0 ),
 530              'COUNTBLANK'      => array( 347,    1,    0,    0 ),
 531              'ROMAN'           => array( 354,   -1,    1,    0 )
 532              );
 533    }
 534
 535    /**
 536    * Convert a token to the proper ptg value.
 537    *
 538    * @access private
 539    * @param mixed $token The token to convert.
 540    * @return mixed the converted token on success. PEAR_Error if the token
 541    *               is not recognized
 542    */
 543    function _convert($token)
 544    {
 545        if (preg_match("/^\"[^\"]{0,255}\"$/", $token)) {
 546            return $this->_convertString($token);
 547
 548        } elseif (is_numeric($token)) {
 549            return $this->_convertNumber($token);
 550
 551        // match references like A1 or $A$1
 552        } elseif (preg_match('/^\$?([A-Ia-i]?[A-Za-z])\$?(\d+)$/',$token)) {
 553            return $this->_convertRef2d($token);
 554
 555        // match external references like Sheet1!A1 or Sheet1:Sheet2!A1
 556        } elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z](\d+)$/u",$token)) {
 557            return $this->_convertRef3d($token);
 558
 559        // match external references like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1
 560        } elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z](\d+)$/u",$token)) {
 561            return $this->_convertRef3d($token);
 562
 563        // match ranges like A1:B2
 564        } elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\:(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token)) {
 565            return $this->_convertRange2d($token);
 566
 567        // match ranges like A1..B2
 568        } elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token)) {
 569            return $this->_convertRange2d($token);
 570
 571        // match external ranges like Sheet1!A1 or Sheet1:Sheet2!A1:B2
 572        } elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/u",$token)) {
 573            return $this->_convertRange3d($token);
 574
 575        // match external ranges like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2
 576        } elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/u",$token)) {
 577            return $this->_convertRange3d($token);
 578
 579        // operators (including parentheses)
 580        } elseif (isset($this->ptg[$token])) {
 581            return pack("C", $this->ptg[$token]);
 582
 583        // commented so argument number can be processed correctly. See toReversePolish().
 584        /*elseif (preg_match("/[A-Z0-9\xc0-\xdc\.]+/",$token))
 585        {
 586            return($this->_convertFunction($token,$this->_func_args));
 587        }*/
 588
 589        // if it's an argument, ignore the token (the argument remains)
 590        } elseif ($token == 'arg') {
 591            return '';
 592        }
 593        // TODO: use real error codes
 594        return $this->raiseError("Unknown token $token");
 595    }
 596
 597    /**
 598    * Convert a number token to ptgInt or ptgNum
 599    *
 600    * @access private
 601    * @param mixed $num an integer or double for conversion to its ptg value
 602    */
 603    function _convertNumber($num)
 604    {
 605        // Integer in the range 0..2**16-1
 606        if ((preg_match("/^\d+$/", $num)) and ($num <= 65535)) {
 607            return pack("Cv", $this->ptg['ptgInt'], $num);
 608        } else { // A float
 609            if ($this->_byte_order) { // if it's Big Endian
 610                $num = strrev($num);
 611            }
 612            return pack("Cd", $this->ptg['ptgNum'], $num);
 613        }
 614    }
 615
 616    /**
 617    * Convert a string token to ptgStr
 618    *
 619    * @access private
 620    * @param string $string A string for conversion to its ptg value.
 621    * @return mixed the converted token on success. PEAR_Error if the string
 622    *               is longer than 255 characters.
 623    */
 624    function _convertString($string)
 625    {
 626        // chop away beggining and ending quotes
 627        $string = substr($string, 1, strlen($string) - 2);
 628        if (strlen($string) > 255) {
 629            return $this->raiseError("String is too long");
 630        }
 631
 632        if ($this->_BIFF_version == 0x0500) {
 633            return pack("CC", $this->ptg['ptgStr'], strlen($string)).$string;
 634        } elseif ($this->_BIFF_version == 0x0600) {
 635            $encoding = 0;   // TODO: Unicode support
 636            return pack("CCC", $this->ptg['ptgStr'], strlen($string), $encoding).$string;
 637        }
 638    }
 639
 640    /**
 641    * Convert a function to a ptgFunc or ptgFuncVarV depending on the number of
 642    * args that it takes.
 643    *
 644    * @access private
 645    * @param string  $token    The name of the function for convertion to ptg value.
 646    * @param integer $num_args The number of arguments the function receives.
 647    * @return string The packed ptg for the function
 648    */
 649    function _convertFunction($token, $num_args)
 650    {
 651        $args     = $this->_functions[$token][1];
 652        $volatile = $this->_functions[$token][3];
 653
 654        // Fixed number of args eg. TIME($i,$j,$k).
 655        if ($args >= 0) {
 656            return pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]);
 657        }
 658        // Variable number of args eg. SUM($i,$j,$k, ..).
 659        if ($args == -1) {
 660            return pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]);
 661        }
 662    }
 663
 664    /**
 665    * Convert an Excel range such as A1:D4 to a ptgRefV.
 666    *
 667    * @access private
 668    * @param string $range An Excel range in the A1:A2 or A1..A2 format.
 669    */
 670    function _convertRange2d($range, $class=0)
 671    {
 672
 673        // TODO: possible class value 0,1,2 check Formula.pm
 674        // Split the range into 2 cell refs
 675        if (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\:([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) {
 676            list($cell1, $cell2) = split(':', $range);
 677        } elseif (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\.\.([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) {
 678            list($cell1, $cell2) = split('\.\.', $range);
 679
 680        } else {
 681            // TODO: use real error codes
 682            return $this->raiseError("Unknown range separator", 0, PEAR_ERROR_DIE);
 683        }
 684
 685        // Convert the cell references
 686        $cell_array1 = $this->_cellToPackedRowcol($cell1);
 687        if (PEAR::isError($cell_array1)) {
 688            return $cell_array1;
 689        }
 690        list($row1, $col1) = $cell_array1;
 691        $cell_array2 = $this->_cellToPackedRowcol($cell2);
 692        if (PEAR::isError($cell_array2)) {
 693            return $cell_array2;
 694        }
 695        list($row2, $col2) = $cell_array2;
 696
 697        // The ptg value depends on the class of the ptg.
 698        if ($class == 0) {
 699            $ptgArea = pack("C", $this->ptg['ptgArea']);
 700        } elseif ($class == 1) {
 701            $ptgArea = pack("C", $this->ptg['ptgAreaV']);
 702        } elseif ($class == 2) {
 703            $ptgArea = pack("C", $this->ptg['ptgAreaA']);
 704        } else {
 705            // TODO: use real error codes
 706            return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
 707        }
 708        return $ptgArea . $row1 . $row2 . $col1. $col2;
 709    }
 710
 711    /**
 712    * Convert an Excel 3d range such as "Sheet1!A1:D4" or "Sheet1:Sheet2!A1:D4" to
 713    * a ptgArea3d.
 714    *
 715    * @access private
 716    * @param string $token An Excel range in the Sheet1!A1:A2 format.
 717    * @return mixed The packed ptgArea3d token on success, PEAR_Error on failure.
 718    */
 719    function _convertRange3d($token)
 720    {
 721        $class = 2; // as far as I know, this is magick.
 722
 723        // Split the ref at the ! symbol
 724        list($ext_ref, $range) = split('!', $token);
 725
 726        // Convert the external reference part (different for BIFF8)
 727        if ($this->_BIFF_version == 0x0500) {
 728            $ext_ref = $this->_packExtRef($ext_ref);
 729            if (PEAR::isError($ext_ref)) {
 730                return $ext_ref;
 731            }
 732        } elseif ($this->_BIFF_version == 0x0600) {
 733             $ext_ref = $this->_getRefIndex($ext_ref);
 734             if (PEAR::isError($ext_ref)) {
 735                 return $ext_ref;
 736             }
 737        }
 738
 739        // Split the range into 2 cell refs
 740        list($cell1, $cell2) = split(':', $range);
 741
 742        // Convert the cell references
 743        if (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/", $cell1)) {
 744            $cell_array1 = $this->_cellToPackedRowcol($cell1);
 745            if (PEAR::isError($cell_array1)) {
 746                return $cell_array1;
 747            }
 748            list($row1, $col1) = $cell_array1;
 749            $cell_array2 = $this->_cellToPackedRowcol($cell2);
 750            if (PEAR::isError($cell_array2)) {
 751                return $cell_array2;
 752            }
 753            list($row2, $col2) = $cell_array2;
 754        } else { // It's a rows range (like 26:27)
 755             $cells_array = $this->_rangeToPackedRange($cell1.':'.$cell2);
 756             if (PEAR::isError($cells_array)) {
 757                 return $cells_array;
 758             }
 759             list($row1, $col1, $row2, $col2) = $cells_array;
 760        }
 761
 762        // The ptg value depends on the class of the ptg.
 763        if ($class == 0) {
 764            $ptgArea = pack("C", $this->ptg['ptgArea3d']);
 765        } elseif ($class == 1) {
 766            $ptgArea = pack("C", $this->ptg['ptgArea3dV']);
 767        } elseif ($class == 2) {
 768            $ptgArea = pack("C", $this->ptg['ptgArea3dA']);
 769        } else {
 770            return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
 771        }
 772
 773        return $ptgArea . $ext_ref . $row1 . $row2 . $col1. $col2;
 774    }
 775
 776    /**
 777    * Convert an Excel reference such as A1, $B2, C$3 or $D$4 to a ptgRefV.
 778    *
 779    * @access private
 780    * @param string $cell An Excel cell reference
 781    * @return string The cell in packed() format with the corresponding ptg
 782    */
 783    function _convertRef2d($cell)
 784    {
 785        $class = 2; // as far as I know, this is magick.
 786
 787        // Convert the cell reference
 788        $cell_array = $this->_cellToPackedRowcol($cell);
 789        if (PEAR::isError($cell_array)) {
 790            return $cell_array;
 791        }
 792        list($row, $col) = $cell_array;
 793
 794        // The ptg value depends on the class of the ptg.
 795        if ($class == 0) {
 796            $ptgRef = pack("C", $this->ptg['ptgRef']);
 797        } elseif ($class == 1) {
 798            $ptgRef = pack("C", $this->ptg['ptgRefV']);
 799        } elseif ($class == 2) {
 800            $ptgRef = pack("C", $this->ptg['ptgRefA']);
 801        } else {
 802            // TODO: use real error codes
 803            return $this->raiseError("Unknown class $class");
 804        }
 805        return $ptgRef.$row.$col;
 806    }
 807
 808    /**
 809    * Convert an Excel 3d reference such as "Sheet1!A1" or "Sheet1:Sheet2!A1" to a
 810    * ptgRef3d.
 811    *
 812    * @access private
 813    * @param string $cell An Excel cell reference
 814    * @return mixed The packed ptgRef3d token on success, PEAR_Error on failure.
 815    */
 816    function _convertRef3d($cell)
 817    {
 818        $class = 2; // as far as I know, this is magick.
 819
 820        // Split the ref at the ! symbol
 821        list($ext_ref, $cell) = split('!', $cell);
 822
 823        // Convert the external reference part (different for BIFF8)
 824        if ($this->_BIFF_version == 0x0500) {
 825            $ext_ref = $this->_packExtRef($ext_ref);
 826            if (PEAR::isError($ext_ref)) {
 827                return $ext_ref;
 828            }
 829        } elseif ($this->_BIFF_version == 0x0600) {
 830            $ext_ref = $this->_getRefIndex($ext_ref);
 831            if (PEAR::isError($ext_ref)) {
 832                return $ext_ref;
 833            }
 834        }
 835
 836        // Convert the cell reference part
 837        list($row, $col) = $this->_cellToPackedRowcol($cell);
 838
 839        // The ptg value depends on the class of the ptg.
 840        if ($class == 0) {
 841            $ptgRef = pack("C", $this->ptg['ptgRef3d']);
 842        } elseif ($class == 1) {
 843            $ptgRef = pack("C", $this->ptg['ptgRef3dV']);
 844        } elseif ($class == 2) {
 845            $ptgRef = pack("C", $this->ptg['ptgRef3dA']);
 846        } else {
 847            return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
 848        }
 849
 850        return $ptgRef . $ext_ref. $row . $col;
 851    }
 852
 853    /**
 854    * Convert the sheet name part of an external reference, for example "Sheet1" or
 855    * "Sheet1:Sheet2", to a packed structure.
 856    *
 857    * @access private
 858    * @param string $ext_ref The name of the external reference
 859    * @return string The reference index in packed() format
 860    */
 861    function _packExtRef($ext_ref)
 862    {
 863        $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading  ' if any.
 864        $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
 865
 866        // Check if there is a sheet range eg., Sheet1:Sheet2.
 867        if (preg_match("/:/", $ext_ref)) {
 868            list($sheet_name1, $sheet_name2) = split(':', $ext_ref);
 869
 870            $sheet1 = $this->_getSheetIndex($sheet_name1);
 871            if ($sheet1 == -1) {
 872                return $this->raiseError("Unknown sheet name $sheet_name1 in formula");
 873            }
 874            $sheet2 = $this->_getSheetIndex($sheet_name2);
 875            if ($sheet2 == -1) {
 876                return $this->raiseError("Unknown sheet name $sheet_name2 in formula");
 877            }
 878
 879            // Reverse max and min sheet numbers if necessary
 880            if ($sheet1 > $sheet2) {
 881                list($sheet1, $sheet2) = array($sheet2, $sheet1);
 882            }
 883        } else { // Single sheet name only.
 884            $sheet1 = $this->_getSheetIndex($ext_ref);
 885            if ($sheet1 == -1) {
 886                return $this->raiseError("Unknown sheet name $ext_ref in formula");
 887            }
 888            $sheet2 = $sheet1;
 889        }
 890
 891        // References are stored relative to 0xFFFF.
 892        $offset = -1 - $sheet1;
 893
 894        return pack('vdvv', $offset, 0x00, $sheet1, $sheet2);
 895    }
 896
 897    /**
 898    * Look up the REF index that corresponds to an external sheet name
 899    * (or range). If it doesn't exist yet add it to the workbook's references
 900    * array. It assumes all sheet names given must exist.
 901    *
 902    * @access private
 903    * @param string $ext_ref The name of the external reference
 904    * @return mixed The reference index in packed() format on success,
 905    *               PEAR_Error on failure
 906    */
 907    function _getRefIndex($ext_ref)
 908    {
 909        $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading  ' if any.
 910        $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
 911
 912        // Check if there is a sheet range eg., Sheet1:Sheet2.
 913        if (preg_match("/:/", $ext_ref)) {
 914            list($sheet_name1, $sheet_name2) = split(':', $ext_ref);
 915
 916            $sheet1 = $this->_getSheetIndex($sheet_name1);
 917            if ($sheet1 == -1) {
 918                return $this->raiseError("Unknown sheet name $sheet_name1 in formula");
 919            }
 920            $sheet2 = $this->_getSheetIndex($sheet_name2);
 921            if ($sheet2 == -1) {
 922                return $this->raiseError("Unknown sheet name $sheet_name2 in formula");
 923            }
 924
 925            // Reverse max and min sheet numbers if necessary
 926            if ($sheet1 > $sheet2) {
 927                list($sheet1, $sheet2) = array($sheet2, $sheet1);
 928            }
 929        } else { // Single sheet name only.
 930            $sheet1 = $this->_getSheetIndex($ext_ref);
 931            if ($sheet1 == -1) {
 932                return $this->raiseError("Unknown sheet name $ext_ref in formula");
 933            }
 934            $sheet2 = $sheet1;
 935        }
 936
 937        // assume all references belong to this document
 938        $supbook_index = 0x00;
 939        $ref = pack('vvv', $supbook_index, $sheet1, $sheet2);
 940        $total_references = count($this->_references);
 941        $index = -1;
 942        for ($i = 0; $i < $total_references; $i++) {
 943            if ($ref == $this->_references[$i]) {
 944                $index = $i;
 945                break;
 946            }
 947        }
 948        // if REF was not found add it to references array
 949        if ($index == -1) {
 950            $this->_references[$total_references] = $ref;
 951            $index = $total_references;
 952        }
 953
 954        return pack('v', $index);
 955    }
 956
 957    /**
 958    * Look up the index that corresponds to an external sheet name. The hash of
 959    * sheet names is updated by the addworksheet() method of the
 960    * Spreadsheet_Excel_Writer_Workbook class.
 961    *
 962    * @access private
 963    * @return integer The sheet index, -1 if the sheet was not found
 964    */
 965    function _getSheetIndex($sheet_name)
 966    {
 967        if (!isset($this->_ext_sheets[$sheet_name])) {
 968            return -1;
 969        } else {
 970            return $this->_ext_sheets[$sheet_name];
 971        }
 972    }
 973
 974    /**
 975    * This method is used to update the array of sheet names. It is
 976    * called by the addWorksheet() method of the
 977    * Spreadsheet_Excel_Writer_Workbook class.
 978    *
 979    * @access public
 980    * @see Spreadsheet_Excel_Writer_Workbook::addWorksheet()
 981    * @param string  $name  The name of the worksheet being added
 982    * @param integer $index The index of the worksheet being added
 983    */
 984    function setExtSheet($name, $index)
 985    {
 986        $this->_ext_sheets[$name] = $index;
 987    }
 988
 989    /**
 990    * pack() row and column into the required 3 or 4 byte format.
 991    *
 992    * @access private
 993    * @param string $cell The Excel cell reference to be packed
 994    * @return array Array containing the row and column in packed() format
 995    */
 996    function _cellToPackedRowcol($cell)
 997    {
 998        $cell = strtoupper($cell);
 999        list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
1000        if ($col >= 256) {
1001            return $this->raiseError("Column in: $cell greater than 255");
1002        }
1003        // FIXME: change for BIFF8
1004        if ($row >= 16384) {
1005            return $this->raiseError("Row in: $cell greater than 16384 ");
1006        }
1007
1008        // Set the high bits to indicate if row or col are relative.
1009        if ($this->_BIFF_version == 0x0500) {
1010            $row    |= $col_rel << 14;
1011            $row    |= $row_rel << 15;
1012            $col     = pack('C', $col);
1013        } elseif ($this->_BIFF_version == 0x0600) {
1014            $col    |= $col_rel << 14;
1015            $col    |= $row_rel << 15;
1016            $col     = pack('v', $col);
1017        }
1018        $row     = pack('v', $row);
1019
1020        return array($row, $col);
1021    }
1022
1023    /**
1024    * pack() row range into the required 3 or 4 byte format.
1025    * Just using maximum col/rows, which is probably not the correct solution
1026    *
1027    * @access private
1028    * @param string $range The Excel range to be packed
1029    * @return array Array containing (row1,col1,row2,col2) in packed() format
1030    */
1031    function _rangeToPackedRange($range)
1032    {
1033        preg_match('/(\$)?(\d+)\:(\$)?(\d+)/', $range, $match);
1034        // return absolute rows if there is a $ in the ref
1035        $row1_rel = empty($match[1]) ? 1 : 0;
1036        $row1     = $match[2];
1037        $row2_rel = empty($match[3]) ? 1 : 0;
1038        $row2     = $match[4];
1039        // Convert 1-index to zero-index
1040        $row1--;
1041        $row2--;
1042        // Trick poor inocent Excel
1043        $col1 = 0;
1044        $col2 = 16383; // FIXME: maximum possible value for Excel 5 (change this!!!)
1045
1046        // FIXME: this changes for BIFF8
1047        if (($row1 >= 16384) or ($row2 >= 16384)) {
1048            return $this->raiseError("Row in: $range greater than 16384 ");
1049        }
1050
1051        // Set the high bits to indicate if rows are relative.
1052        if ($this->_BIFF_version == 0x0500) {
1053            $row1    |= $row1_rel << 14; // FIXME: probably a bug
1054            $row2    |= $row2_rel << 15;
1055            $col1     = pack('C', $col1);
1056            $col2     = pack('C', $col2);
1057        } elseif ($this->_BIFF_version == 0x0600) {
1058            $col1    |= $row1_rel << 15;
1059            $col2    |= $row2_rel << 15;
1060            $col1     = pack('v', $col1);
1061            $col2     = pack('v', $col2);
1062        }
1063        $row1     = pack('v', $row1);
1064        $row2     = pack('v', $row2);
1065
1066        return array($row1, $col1, $row2, $col2);
1067    }
1068
1069    /**
1070    * Convert an Excel cell reference such as A1 or $B2 or C$3 or $D$4 to a zero
1071    * indexed row and column number. Also returns two (0,1) values to indicate
1072    * whether the row or column are relative references.
1073    *
1074    * @access private
1075    * @param string $cell The Excel cell reference in A1 format.
1076    * @return array
1077    */
1078    function _cellToRowcol($cell)
1079    {
1080        preg_match('/(\$)?([A-I]?[A-Z])(\$)?(\d+)/',$cell,$match);
1081        // return absolute column if there is a $ in the ref
1082        $col_rel = empty($match[1]) ? 1 : 0;
1083        $col_ref = $match[2];
1084        $row_rel = empty($match[3]) ? 1 : 0;
1085        $row     = $match[4];
1086
1087        // Convert base26 column string to a number.
1088        $expn   = strlen($col_ref) - 1;
1089        $col    = 0;
1090        $col_ref_length = strlen($col_ref);
1091        for ($i = 0; $i < $col_ref_length; $i++) {
1092            $col += (ord($col_ref{$i}) - ord('A') + 1) * pow(26, $expn);
1093            $expn--;
1094        }
1095
1096        // Convert 1-index to zero-index
1097        $row--;
1098        $col--;
1099
1100        return array($row, $col, $row_rel, $col_rel);
1101    }
1102
1103    /**
1104    * Advance to the next valid token.
1105    *
1106    * @access private
1107    */
1108    function _advance()
1109    {
1110        $i = $this->_current_char;
1111        $formula_length = strlen($this->_formula);
1112        // eat up white spaces
1113        if ($i < $formula_length) {
1114            while ($this->_formula{$i} == " ") {
1115                $i++;
1116            }
1117
1118            if ($i < ($formula_length - 1)) {
1119                $this->_lookahead = $this->_formula{$i+1};
1120            }
1121            $token = '';
1122        }
1123
1124        while ($i < $formula_length) {
1125            $token .= $this->_formula{$i};
1126            if ($i < ($formula_length - 1)) {
1127                $this->_lookahead = $this->_formula{$i+1};
1128            } else {
1129                $this->_lookahead = '';
1130            }
1131
1132            if ($this->_match($token) != '') {
1133                //if ($i < strlen($this->_formula) - 1) {
1134                //    $this->_lookahead = $this->_formula{$i+1};
1135                //}
1136                $this->_current_char = $i + 1;
1137                $this->_current_token = $token;
1138                return 1;
1139            }
1140
1141            if ($i < ($formula_length - 2)) {
1142                $this->_lookahead = $this->_formula{$i+2};
1143            } else { // if we run out of characters _lookahead becomes empty
1144                $this->_lookahead = '';
1145            }
1146            $i++;
1147        }
1148        //die("Lexical error ".$this->_current_char);
1149    }
1150
1151    /**
1152    * Checks if it's a valid token.
1153    *
1154    * @access private
1155    * @param mixed $token The token to check.
1156    * @return mixed       The checked token or false on failure
1157    */
1158    function _match($token)
1159    {
1160        switch($token) {
1161            case SPREADSHEET_EXCEL_WRITER_ADD:
1162                return $token;
1163                break;
1164            case SPREADSHEET_EXCEL_WRITER_SUB:
1165                return $token;
1166                break;
1167            case SPREADSHEET_EXCEL_WRITER_MUL:
1168                return $token;
1169                break;
1170            case SPREADSHEET_EXCEL_WRITER_DIV:
1171                return $token;
1172                break;
1173            case SPREADSHEET_EXCEL_WRITER_OPEN:
1174                return $token;
1175                break;
1176            case SPREADSHEET_EXCEL_WRITER_CLOSE:
1177                return $token;
1178                break;
1179            case SPREADSHEET_EXCEL_WRITER_COMA:
1180                return $token;
1181                break;
1182            case SPREADSHEET_EXCEL_WRITER_SEMICOLON:
1183                return $token;
1184                break;
1185            case SPREADSHEET_EXCEL_WRITER_GT:
1186                if ($this->_lookahead == '=') { // it's a GE token
1187                    break;
1188                }
1189                return $token;
1190                break;
1191            case SPREADSHEET_EXCEL_WRITER_LT:
1192                // it's a LE or a NE token
1193                if (($this->_lookahead == '=') or ($this->_lookahead == '>')) {
1194                    break;
1195                }
1196                return $token;
1197                break;
1198            case SPREADSHEET_EXCEL_WRITER_GE:
1199                return $token;
1200                break;
1201            case SPREADSHEET_EXCEL_WRITER_LE:
1202                return $token;
1203                break;
1204            case SPREADSHEET_EXCEL_WRITER_EQ:
1205                return $token;
1206                break;
1207            case SPREADSHEET_EXCEL_WRITER_NE:
1208                return $token;
1209                break;
1210            case SPREADSHEET_EXCEL_WRITER_CONCAT:
1211                return $token;
1212                break;
1213            default:
1214                // if it's a reference
1215                if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$token) and
1216                   !preg_match("/[0-9]/",$this->_lookahead) and 
1217    

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