"Fossies" - the Fresh Open Source Software Archive

Member "php_writeexcel-0.3.0/class.writeexcel_formula.inc.php" (1 Nov 2005, 56012 Bytes) of package /linux/www/old/php_writeexcel-0.3.0.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) PHP source code syntax highlighting (style: standard) with prefixed line numbers and code folding option. Alternatively you can here view or download the uninterpreted source code file.

    1 <?php
    2 
    3 /*
    4  * Copyleft 2002 Johann Hanne
    5  *
    6  * This is free software; you can redistribute it and/or
    7  * modify it under the terms of the GNU Lesser General Public
    8  * License as published by the Free Software Foundation; either
    9  * version 2.1 of the License, or (at your option) any later version.
   10  *
   11  * This software is distributed in the hope that it will be useful,
   12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
   13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
   14  * Lesser General Public License for more details.
   15  *
   16  * You should have received a copy of the GNU Lesser General Public
   17  * License along with this software; if not, write to the
   18  * Free Software Foundation, Inc., 59 Temple Place,
   19  * Suite 330, Boston, MA  02111-1307 USA
   20  */
   21 
   22 /* This file contains source from the PEAR::Spreadsheet class Parser.php file version 0.4 .
   23    The raiseError was replaced by triggerError function.
   24    The PEAR::isError was imported to keep compatibility to PEAR::Spreadsheet class 
   25    
   26    Imported and adapted by Andreas Brodowski 2003 (andreas.brodowski@oscar-gmbh.com).
   27    
   28    There should be no license rights in question because the Parser.php from PEAR class is 
   29    published under GNU License the same way like this class.
   30    
   31    Changes: 03/08/27 Added SPREADSHEET_EXCEL_WRITER_SCOLON for arg seperation in excel functions
   32  */
   33 
   34 /*
   35  * This is the Spreadsheet::WriteExcel Perl package ported to PHP
   36  * Spreadsheet::WriteExcel was written by John McNamara, jmcnamara@cpan.org
   37  */
   38 
   39 define('SPREADSHEET_EXCEL_WRITER_ADD',"+");
   40     // @const SPREADSHEET_EXCEL_WRITER_ADD token identifier for character "+"
   41 define('SPREADSHEET_EXCEL_WRITER_SUB',"-");
   42     // @const SPREADSHEET_EXCEL_WRITER_SUB token identifier for character "-"
   43 define('SPREADSHEET_EXCEL_WRITER_MUL',"*");
   44     // @const SPREADSHEET_EXCEL_WRITER_MUL token identifier for character "*"
   45 define('SPREADSHEET_EXCEL_WRITER_DIV',"/");
   46     // @const SPREADSHEET_EXCEL_WRITER_DIV token identifier for character "/"
   47 define('SPREADSHEET_EXCEL_WRITER_OPEN',"(");
   48    // @const SPREADSHEET_EXCEL_WRITER_OPEN token identifier for character "("
   49 define('SPREADSHEET_EXCEL_WRITER_CLOSE',")"); 
   50  // @const SPREADSHEET_EXCEL_WRITER_CLOSE token identifier for character ")"
   51 define('SPREADSHEET_EXCEL_WRITER_COMA',",");
   52    // @const SPREADSHEET_EXCEL_WRITER_COMA token identifier for character ","
   53 define('SPREADSHEET_EXCEL_WRITER_SCOLON',";"); 
   54 // @const SPREADSHEET_EXCEL_WRITER_SCOLON token identifier for character ";"
   55 define('SPREADSHEET_EXCEL_WRITER_GT',">");
   56      // @const SPREADSHEET_EXCEL_WRITER_GT token identifier for character ">"
   57 define('SPREADSHEET_EXCEL_WRITER_LT',"<");
   58      // @const SPREADSHEET_EXCEL_WRITER_LT token identifier for character "<"
   59 define('SPREADSHEET_EXCEL_WRITER_LE',"<=");
   60     // @const SPREADSHEET_EXCEL_WRITER_LE token identifier for character "<="
   61 define('SPREADSHEET_EXCEL_WRITER_GE',">=");
   62     // @const SPREADSHEET_EXCEL_WRITER_GE token identifier for character ">="
   63 define('SPREADSHEET_EXCEL_WRITER_EQ',"=");
   64      // @const SPREADSHEET_EXCEL_WRITER_EQ token identifier for character "="
   65 define('SPREADSHEET_EXCEL_WRITER_NE',"<>");
   66     // @const SPREADSHEET_EXCEL_WRITER_NE token identifier for character "<>"
   67 
   68 
   69 class writeexcel_formula {
   70 
   71 ###############################################################################
   72 #
   73 # Class data.
   74 #
   75 var $parser;
   76 var $ptg;
   77 var $_functions;
   78 var $_current_char;
   79 var $_current_token;
   80 var $_lookahead;
   81 var $_debug;
   82 var $_byte_order;
   83 var $_volatile;
   84 var $_workbook;
   85 var $_ext_sheets;
   86 var $_formula;
   87 
   88 ###############################################################################
   89 #
   90 # new()
   91 #
   92 # Constructor
   93 #
   94 function writeexcel_formula($byte_order) {
   95 
   96     $this->parser          = false;
   97     $this->ptg             = array();
   98     $this->_functions       = array();
   99     $this->_debug          = 0;
  100     $this->_byte_order     = $byte_order;
  101     $this->_volatile       = 0;
  102     $this->_workbook       = "";
  103     $this->_ext_sheets     = array();
  104     $this->_current_token  = '';
  105     $this->_lookahead      = '';
  106     $this->_current_char   = 0;    
  107     $this->_formula    = '';
  108 }
  109 
  110 ###############################################################################
  111 #
  112 # _init_parser()
  113 #
  114 # There is a small overhead involved in generating the parser. Therefore, the
  115 # initialisation is delayed until a formula is required. TODO: use a pre-
  116 # compiled header.
  117 #
  118 function _init_parser() {
  119 
  120     $this->_initializeHashes();
  121 
  122 
  123     if ($this->_debug) {
  124         print "Init_parser.\n\n";
  125     }
  126 }
  127 
  128 ###############################################################################
  129 #
  130 # parse_formula()
  131 #
  132 # This is the only public method. It takes a textual description of a formula
  133 # and returns a RPN encoded byte string.
  134 #
  135 function parse_formula() {
  136 
  137     $_=func_get_args();
  138 
  139     # Initialise the parser if this is the first call
  140     if ($this->parser===false) {
  141         $this->_init_parser();
  142     }
  143 
  144     $formula = array_shift($_);
  145     //$str;
  146     //$tokens;
  147 
  148     if ($this->_debug) {
  149         print "$formula\n";
  150     }
  151 
  152     # Build the parse tree for the formula
  153     
  154     $this->_formula  = $formula;
  155     $this->_current_char = 0;
  156     $this->_lookahead    = $this->_formula{1};
  157     $this->_advance($formula);
  158     $parsetree = $this->_condition();
  159 
  160     $str = $this->toReversePolish($parsetree);
  161 
  162     return $str;
  163 }
  164 
  165 function isError($data) {
  166     return (bool)(is_object($data) &&
  167                   (get_class($data) == 'pear_error' ||
  168                   is_subclass_of($data, 'pear_error')));
  169 }
  170 
  171 /**
  172 * Class for parsing Excel formulas
  173 *
  174 * @author   Xavier Noguer <xnoguer@rezebra.com>
  175 * @category FileFormats
  176 * @package  Spreadsheet_Excel_Writer
  177 */
  178 
  179     
  180 /**
  181 * Initialize the ptg and function hashes. 
  182 *
  183 * @access private
  184 */
  185 function _initializeHashes()
  186  {
  187     // The Excel ptg indices
  188     $this->ptg = array(
  189         'ptgExp'       => 0x01,
  190         'ptgTbl'       => 0x02,
  191         'ptgAdd'       => 0x03,
  192         'ptgSub'       => 0x04,
  193         'ptgMul'       => 0x05,
  194         'ptgDiv'       => 0x06,
  195         'ptgPower'     => 0x07,        'ptgConcat'    => 0x08,
  196         'ptgLT'        => 0x09,
  197         'ptgLE'        => 0x0A,
  198         'ptgEQ'        => 0x0B,
  199         'ptgGE'        => 0x0C,
  200         'ptgGT'        => 0x0D,
  201         'ptgNE'        => 0x0E,
  202         'ptgIsect'     => 0x0F,
  203         'ptgUnion'     => 0x10,
  204         'ptgRange'     => 0x11,
  205         'ptgUplus'     => 0x12,
  206         'ptgUminus'    => 0x13,
  207         'ptgPercent'   => 0x14,
  208         'ptgParen'     => 0x15,
  209         'ptgMissArg'   => 0x16,
  210         'ptgStr'       => 0x17,
  211         'ptgAttr'      => 0x19,
  212         'ptgSheet'     => 0x1A,
  213         'ptgEndSheet'  => 0x1B,
  214         'ptgErr'       => 0x1C,
  215         'ptgBool'      => 0x1D,
  216         'ptgInt'       => 0x1E,
  217         'ptgNum'       => 0x1F,
  218         'ptgArray'     => 0x20,
  219         'ptgFunc'      => 0x21,
  220         'ptgFuncVar'   => 0x22,
  221         'ptgName'      => 0x23,
  222         'ptgRef'       => 0x24,
  223         'ptgArea'      => 0x25,
  224         'ptgMemArea'   => 0x26,
  225         'ptgMemErr'    => 0x27,
  226         'ptgMemNoMem'  => 0x28,
  227         'ptgMemFunc'   => 0x29,
  228     'ptgRefErr'    => 0x2A,
  229         'ptgAreaErr'   => 0x2B,
  230         'ptgRefN'      => 0x2C,
  231         'ptgAreaN'     => 0x2D,
  232         'ptgMemAreaN'  => 0x2E,
  233         'ptgMemNoMemN' => 0x2F,
  234         'ptgNameX'     => 0x39,
  235         'ptgRef3d'     => 0x3A,
  236 
  237         'ptgArea3d'    => 0x3B,
  238         'ptgRefErr3d'  => 0x3C,
  239         'ptgAreaErr3d' => 0x3D,
  240         'ptgArrayV'    => 0x40,
  241         'ptgFuncV'     => 0x41,
  242         'ptgFuncVarV'  => 0x42,
  243         'ptgNameV'     => 0x43,
  244         'ptgRefV'      => 0x44,
  245         'ptgAreaV'     => 0x45,
  246         'ptgMemAreaV'  => 0x46,
  247         'ptgMemErrV'   => 0x47,
  248         'ptgMemNoMemV' => 0x48,
  249         'ptgMemFuncV'  => 0x49,
  250         'ptgRefErrV'   => 0x4A,
  251         'ptgAreaErrV'  => 0x4B,
  252         'ptgRefNV'     => 0x4C,
  253         'ptgAreaNV'    => 0x4D,
  254         'ptgMemAreaNV' => 0x4E,
  255         'ptgMemNoMemN' => 0x4F,
  256         'ptgFuncCEV'   => 0x58,
  257         'ptgNameXV'    => 0x59,
  258         'ptgRef3dV'    => 0x5A,
  259         'ptgArea3dV'   => 0x5B,        'ptgRefErr3dV' => 0x5C,
  260         'ptgAreaErr3d' => 0x5D,
  261         'ptgArrayA'    => 0x60,
  262         'ptgFuncA'     => 0x61,
  263         'ptgFuncVarA'  => 0x62,
  264         'ptgNameA'     => 0x63,        'ptgRefA'      => 0x64,
  265           'ptgAreaA'     => 0x65,
  266         'ptgMemAreaA'  => 0x66,
  267         'ptgMemErrA'   => 0x67,
  268         'ptgMemNoMemA' => 0x68,
  269         'ptgMemFuncA'  => 0x69,
  270         'ptgRefErrA'   => 0x6A,
  271         'ptgAreaErrA'  => 0x6B,
  272         'ptgRefNA'     => 0x6C,
  273         'ptgAreaNA'    => 0x6D,
  274         'ptgMemAreaNA' => 0x6E,
  275         'ptgMemNoMemN' => 0x6F,
  276         'ptgFuncCEA'   => 0x78,
  277         'ptgNameXA'    => 0x79,
  278         'ptgRef3dA'    => 0x7A,
  279         'ptgArea3dA'   => 0x7B,
  280         'ptgRefErr3dA' => 0x7C,
  281         'ptgAreaErr3d' => 0x7D
  282         );
  283     
  284     // Thanks to Michael Meeks and Gnumeric for the initial arg values.
  285     //
  286     // The following hash was generated by "function_locale.pl" in the distro.
  287     // Refer to function_locale.pl for non-English function names.
  288     //
  289     // The array elements are as follow:
  290     // ptg:   The Excel function ptg code.
  291     // args:  The number of arguments that the function takes:
  292     //           >=0 is a fixed number of arguments.
  293     //           -1  is a variable  number of arguments.
  294     // class: The reference, value or array class of the function args.
  295     // vol:   The function is volatile.
  296     //
  297     $this->_functions = array(
  298     // function                  ptg  args  class  vol
  299     'COUNT'           => array(   0,   -1,    0,    0 ),
  300         'IF'              => array(   1,   -1,    1,    0 ),
  301         'ISNA'            => array(   2,    1,    1,    0 ),
  302         'ISERROR'         => array(   3,    1,    1,    0 ),
  303         'SUM'             => array(   4,   -1,    0,    0 ),
  304         'AVERAGE'         => array(   5,   -1,    0,    0 ),
  305         'MIN'             => array(   6,   -1,    0,    0 ),
  306         'MAX'             => array(   7,   -1,    0,    0 ),
  307         'ROW'             => array(   8,   -1,    0,    0 ),
  308         'COLUMN'          => array(   9,   -1,    0,    0 ),
  309         'NA'              => array(  10,    0,    0,    0 ),
  310         'NPV'             => array(  11,   -1,    1,    0 ),
  311         'STDEV'           => array(  12,   -1,    0,    0 ),
  312         'DOLLAR'          => array(  13,   -1,    1,    0 ),
  313         'FIXED'           => array(  14,   -1,    1,    0 ),
  314         'SIN'             => array(  15,    1,    1,    0 ),
  315         'COS'             => array(  16,    1,    1,    0 ),
  316         'TAN'             => array(  17,    1,    1,    0 ),
  317         'ATAN'            => array(  18,    1,    1,    0 ),
  318         'PI'              => array(  19,    0,    1,    0 ),
  319         'SQRT'            => array(  20,    1,    1,    0 ),
  320         'EXP'             => array(  21,    1,    1,    0 ),
  321         'LN'              => array(  22,    1,    1,    0 ),
  322         'LOG10'           => array(  23,    1,    1,    0 ),
  323         'ABS'             => array(  24,    1,    1,    0 ),
  324         'INT'             => array(  25,    1,    1,    0 ),
  325         'SIGN'            => array(  26,    1,    1,    0 ),
  326         'ROUND'           => array(  27,    2,    1,    0 ),
  327         'LOOKUP'          => array(  28,   -1,    0,    0 ),
  328         'INDEX'           => array(  29,   -1,    0,    1 ),
  329         'REPT'            => array(  30,    2,    1,    0 ),
  330         'MID'             => array(  31,    3,    1,    0 ),
  331         'LEN'             => array(  32,    1,    1,    0 ),
  332         'VALUE'           => array(  33,    1,    1,    0 ),
  333         'TRUE'            => array(  34,    0,    1,    0 ),
  334         'FALSE'           => array(  35,    0,    1,    0 ),
  335         'AND'             => array(  36,   -1,    0,    0 ),
  336         'OR'              => array(  37,   -1,    0,    0 ),
  337         'NOT'             => array(  38,    1,    1,    0 ),
  338         'MOD'             => array(  39,    2,    1,    0 ),
  339         'DCOUNT'          => array(  40,    3,    0,    0 ),
  340         'DSUM'            => array(  41,    3,    0,    0 ),
  341         'DAVERAGE'        => array(  42,    3,    0,    0 ),
  342         'DMIN'            => array(  43,    3,    0,    0 ),
  343         'DMAX'            => array(  44,    3,    0,    0 ),
  344         'DSTDEV'          => array(  45,    3,    0,    0 ),
  345         'VAR'             => array(  46,   -1,    0,    0 ),
  346         'DVAR'            => array(  47,    3,    0,    0 ),
  347         'TEXT'            => array(  48,    2,    1,    0 ),
  348         'LINEST'          => array(  49,   -1,    0,    0 ),
  349         'TREND'           => array(  50,   -1,    0,    0 ),
  350         'LOGEST'          => array(  51,   -1,    0,    0 ),
  351         'GROWTH'          => array(  52,   -1,    0,    0 ),
  352         'PV'              => array(  56,   -1,    1,    0 ),
  353         'FV'              => array(  57,   -1,    1,    0 ),
  354         'NPER'            => array(  58,   -1,    1,    0 ),
  355         'PMT'             => array(  59,   -1,    1,    0 ),
  356         'RATE'            => array(  60,   -1,    1,    0 ),
  357         'MIRR'            => array(  61,    3,    0,    0 ),
  358         'IRR'             => array(  62,   -1,    0,    0 ),
  359         'RAND'            => array(  63,    0,    1,    1 ),
  360         'MATCH'           => array(  64,   -1,    0,    0 ),
  361         'DATE'            => array(  65,    3,    1,    0 ),
  362         'TIME'            => array(  66,    3,    1,    0 ),
  363         'DAY'             => array(  67,    1,    1,    0 ),
  364         'MONTH'           => array(  68,    1,    1,    0 ),
  365         'YEAR'            => array(  69,    1,    1,    0 ),
  366         'WEEKDAY'         => array(  70,   -1,    1,    0 ),
  367         'HOUR'            => array(  71,    1,    1,    0 ),
  368         'MINUTE'          => array(  72,    1,    1,    0 ),
  369         'SECOND'          => array(  73,    1,    1,    0 ),
  370         'NOW'             => array(  74,    0,    1,    1 ),
  371         'AREAS'           => array(  75,    1,    0,    1 ),
  372         'ROWS'            => array(  76,    1,    0,    1 ),
  373         'COLUMNS'         => array(  77,    1,    0,    1 ),
  374         'OFFSET'          => array(  78,   -1,    0,    1 ),
  375         'SEARCH'          => array(  82,   -1,    1,    0 ),
  376         'TRANSPOSE'       => array(  83,    1,    1,    0 ),
  377         'TYPE'            => array(  86,    1,    1,    0 ),
  378         'ATAN2'           => array(  97,    2,    1,    0 ),
  379         'ASIN'            => array(  98,    1,    1,    0 ),
  380         'ACOS'            => array(  99,    1,    1,    0 ),
  381         'CHOOSE'          => array( 100,   -1,    1,    0 ),
  382         'HLOOKUP'         => array( 101,   -1,    0,    0 ),
  383         'VLOOKUP'         => array( 102,   -1,    0,    0 ),
  384         'ISREF'           => array( 105,    1,    0,    0 ),
  385         'LOG'             => array( 109,   -1,    1,    0 ),
  386         'CHAR'            => array( 111,    1,    1,    0 ),
  387         'LOWER'           => array( 112,    1,    1,    0 ),
  388         'UPPER'           => array( 113,    1,    1,    0 ),
  389         'PROPER'          => array( 114,    1,    1,    0 ),
  390         'LEFT'            => array( 115,   -1,    1,    0 ),
  391         'RIGHT'           => array( 116,   -1,    1,    0 ),
  392         'EXACT'           => array( 117,    2,    1,    0 ),
  393         'TRIM'            => array( 118,    1,    1,    0 ),
  394         'REPLACE'         => array( 119,    4,    1,    0 ),
  395         'SUBSTITUTE'      => array( 120,   -1,    1,    0 ),
  396         'CODE'            => array( 121,    1,    1,    0 ),
  397         'FIND'            => array( 124,   -1,    1,    0 ),
  398         'CELL'            => array( 125,   -1,    0,    1 ),
  399         'ISERR'           => array( 126,    1,    1,    0 ),
  400         'ISTEXT'          => array( 127,    1,    1,    0 ),
  401         'ISNUMBER'        => array( 128,    1,    1,    0 ),
  402         'ISBLANK'         => array( 129,    1,    1,    0 ),
  403         'T'               => array( 130,    1,    0,    0 ),
  404         'N'               => array( 131,    1,    0,    0 ),
  405         'DATEVALUE'       => array( 140,    1,    1,    0 ),
  406         'TIMEVALUE'       => array( 141,    1,    1,    0 ),
  407         'SLN'             => array( 142,    3,    1,    0 ),
  408         'SYD'             => array( 143,    4,    1,    0 ),
  409         'DDB'             => array( 144,   -1,    1,    0 ),
  410         'INDIRECT'        => array( 148,   -1,    1,    1 ),
  411         'CALL'            => array( 150,   -1,    1,    0 ),
  412         'CLEAN'           => array( 162,    1,    1,    0 ),
  413         'MDETERM'         => array( 163,    1,    2,    0 ),
  414         'MINVERSE'        => array( 164,    1,    2,    0 ),
  415         'MMULT'           => array( 165,    2,    2,    0 ),
  416         'IPMT'            => array( 167,   -1,    1,    0 ),
  417         'PPMT'            => array( 168,   -1,    1,    0 ),
  418         'COUNTA'          => array( 169,   -1,    0,    0 ),
  419         'PRODUCT'         => array( 183,   -1,    0,    0 ),
  420         'FACT'            => array( 184,    1,    1,    0 ),
  421         'DPRODUCT'        => array( 189,    3,    0,    0 ),
  422         'ISNONTEXT'       => array( 190,    1,    1,    0 ),
  423         'STDEVP'          => array( 193,   -1,    0,    0 ),
  424         'VARP'            => array( 194,   -1,    0,    0 ),
  425         'DSTDEVP'         => array( 195,    3,    0,    0 ),
  426         'DVARP'           => array( 196,    3,    0,    0 ),
  427         'TRUNC'           => array( 197,   -1,    1,    0 ),
  428         'ISLOGICAL'       => array( 198,    1,    1,    0 ),
  429         'DCOUNTA'         => array( 199,    3,    0,    0 ),
  430         'ROUNDUP'         => array( 212,    2,    1,    0 ),
  431         'ROUNDDOWN'       => array( 213,    2,    1,    0 ),
  432         'RANK'            => array( 216,   -1,    0,    0 ),
  433         'ADDRESS'         => array( 219,   -1,    1,    0 ),
  434         'DAYS360'         => array( 220,   -1,    1,    0 ),
  435         'TODAY'           => array( 221,    0,    1,    1 ),
  436         'VDB'             => array( 222,   -1,    1,    0 ),
  437         'MEDIAN'          => array( 227,   -1,    0,    0 ),
  438         'SUMPRODUCT'      => array( 228,   -1,    2,    0 ),
  439         'SINH'            => array( 229,    1,    1,    0 ),
  440         'COSH'            => array( 230,    1,    1,    0 ),
  441         'TANH'            => array( 231,    1,    1,    0 ),
  442         'ASINH'           => array( 232,    1,    1,    0 ),
  443         'ACOSH'           => array( 233,    1,    1,    0 ),
  444         'ATANH'           => array( 234,    1,    1,    0 ),
  445         'DGET'            => array( 235,    3,    0,    0 ),
  446         'INFO'            => array( 244,    1,    1,    1 ),
  447         'DB'              => array( 247,   -1,    1,    0 ),
  448         'FREQUENCY'       => array( 252,    2,    0,    0 ),
  449         'ERROR.TYPE'      => array( 261,    1,    1,    0 ),
  450         'REGISTER.ID'     => array( 267,   -1,    1,    0 ),
  451         'AVEDEV'          => array( 269,   -1,    0,    0 ),
  452         'BETADIST'        => array( 270,   -1,    1,    0 ),
  453         'GAMMALN'         => array( 271,    1,    1,    0 ),
  454         'BETAINV'         => array( 272,   -1,    1,    0 ),
  455         'BINOMDIST'       => array( 273,    4,    1,    0 ),
  456         'CHIDIST'         => array( 274,    2,    1,    0 ),
  457         'CHIINV'          => array( 275,    2,    1,    0 ),
  458         'COMBIN'          => array( 276,    2,    1,    0 ),
  459         'CONFIDENCE'      => array( 277,    3,    1,    0 ),
  460         'CRITBINOM'       => array( 278,    3,    1,    0 ),
  461         'EVEN'            => array( 279,    1,    1,    0 ),
  462         'EXPONDIST'       => array( 280,    3,    1,    0 ),
  463         'FDIST'           => array( 281,    3,    1,    0 ),
  464         'FINV'            => array( 282,    3,    1,    0 ),
  465         'FISHER'          => array( 283,    1,    1,    0 ),
  466         'FISHERINV'       => array( 284,    1,    1,    0 ),
  467         'FLOOR'           => array( 285,    2,    1,    0 ),
  468         'GAMMADIST'       => array( 286,    4,    1,    0 ),
  469         'GAMMAINV'        => array( 287,    3,    1,    0 ),
  470         'CEILING'         => array( 288,    2,    1,    0 ),
  471         'HYPGEOMDIST'     => array( 289,    4,    1,    0 ),
  472         'LOGNORMDIST'     => array( 290,    3,    1,    0 ),
  473         'LOGINV'          => array( 291,    3,    1,    0 ),
  474         'NEGBINOMDIST'    => array( 292,    3,    1,    0 ),
  475         'NORMDIST'        => array( 293,    4,    1,    0 ),
  476         'NORMSDIST'       => array( 294,    1,    1,    0 ),
  477         'NORMINV'         => array( 295,    3,    1,    0 ),
  478         'NORMSINV'        => array( 296,    1,    1,    0 ),
  479         'STANDARDIZE'     => array( 297,    3,    1,    0 ),
  480         'ODD'             => array( 298,    1,    1,    0 ),
  481         'PERMUT'          => array( 299,    2,    1,    0 ),
  482         'POISSON'         => array( 300,    3,    1,    0 ),
  483         'TDIST'           => array( 301,    3,    1,    0 ),
  484         'WEIBULL'         => array( 302,    4,    1,    0 ),
  485         'SUMXMY2'         => array( 303,    2,    2,    0 ),
  486         'SUMX2MY2'        => array( 304,    2,    2,    0 ),
  487         'SUMX2PY2'        => array( 305,    2,    2,    0 ),
  488         'CHITEST'         => array( 306,    2,    2,    0 ),
  489         'CORREL'          => array( 307,    2,    2,    0 ),
  490         'COVAR'           => array( 308,    2,    2,    0 ),
  491         'FORECAST'        => array( 309,    3,    2,    0 ),
  492         'FTEST'           => array( 310,    2,    2,    0 ),
  493         'INTERCEPT'       => array( 311,    2,    2,    0 ),
  494         'PEARSON'         => array( 312,    2,    2,    0 ),
  495         'RSQ'             => array( 313,    2,    2,    0 ),
  496         'STEYX'           => array( 314,    2,    2,    0 ),
  497         'SLOPE'           => array( 315,    2,    2,    0 ),
  498         'TTEST'           => array( 316,    4,    2,    0 ),
  499         'PROB'            => array( 317,   -1,    2,    0 ),
  500         'DEVSQ'           => array( 318,   -1,    0,    0 ),
  501         'GEOMEAN'         => array( 319,   -1,    0,    0 ),
  502         'HARMEAN'         => array( 320,   -1,    0,    0 ),
  503         'SUMSQ'           => array( 321,   -1,    0,    0 ),
  504         'KURT'            => array( 322,   -1,    0,    0 ),
  505         'SKEW'            => array( 323,   -1,    0,    0 ),
  506         'ZTEST'           => array( 324,   -1,    0,    0 ),
  507         'LARGE'           => array( 325,    2,    0,    0 ),
  508         'SMALL'           => array( 326,    2,    0,    0 ),
  509         'QUARTILE'        => array( 327,    2,    0,    0 ),
  510         'PERCENTILE'      => array( 328,    2,    0,    0 ),
  511         'PERCENTRANK'     => array( 329,   -1,    0,    0 ),
  512         'MODE'            => array( 330,   -1,    2,    0 ),
  513         'TRIMMEAN'        => array( 331,    2,    0,    0 ),
  514         'TINV'            => array( 332,    2,    1,    0 ),
  515         'CONCATENATE'     => array( 336,   -1,    1,    0 ),
  516         'POWER'           => array( 337,    2,    1,    0 ),
  517         'RADIANS'         => array( 342,    1,    1,    0 ),
  518         'DEGREES'         => array( 343,    1,    1,    0 ),
  519         'SUBTOTAL'        => array( 344,   -1,    0,    0 ),
  520         'SUMIF'           => array( 345,   -1,    0,    0 ),
  521         'COUNTIF'         => array( 346,    2,    0,    0 ),
  522         'COUNTBLANK'      => array( 347,    1,    0,    0 ),
  523         'ROMAN'           => array( 354,   -1,    1,    0 )
  524         );
  525 }
  526     
  527 /**
  528 * Convert a token to the proper ptg value.
  529 *
  530 * @access private
  531 * @param mixed $token The token to convert.
  532 * @return mixed the converted token on success. PEAR_Error if the token
  533 *               is not recognized
  534 */
  535 function _convert($token)
  536  {
  537     if (preg_match('/^"[^"]{0,255}"$/', $token))
  538  {
  539         return $this->_convertString($token);
  540     }
  541  elseif (is_numeric($token))
  542  {
  543         return $this->_convertNumber($token);
  544     }
  545     // match references like A1 or $A$1
  546     
  547 elseif (preg_match('/^\$?([A-Ia-i]?[A-Za-z])\$?(\d+)$/',$token))
  548  { 
  549         return $this->_convertRef2d($token);
  550     }
  551     // match external references like Sheet1:Sheet2!A1
  552     elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\![A-Ia-i]?[A-Za-z](\d+)$/",$token))
  553  {
  554  
  555         return $this->_convertRef3d($token);
  556     }
  557     // match ranges like A1:B2
  558     elseif (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?\d+\:\$?[A-Ia-i]?[A-Za-z]\$?\d+$/',$token))
  559  {
  560         return $this->_convertRange2d($token);
  561     }
  562     // match ranges like A1..B2
  563     elseif (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?\d+\.\.\$?[A-Ia-i]?[A-Za-z]\$?\d+$/',$token))
  564  {
  565         return $this->_convertRange2d($token);
  566     }
  567     // match external ranges like Sheet1:Sheet2!A1:B2
  568     elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/",$token))
  569  {
  570         return $this->_convertRange3d($token);
  571     }
  572     // match external ranges like 'Sheet1:Sheet2'!A1:B2
  573     elseif (preg_match("/^'[A-Za-z0-9_ ]+(\:[A-Za-z0-9_ ]+)?'\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/",$token))
  574  {
  575         return $this->_convertRange3d($token);
  576     }
  577     elseif (isset($this->ptg[$token])) // operators (including parentheses)
  578  {
  579         return pack("C", $this->ptg[$token]);
  580     }
  581     // commented so argument number can be processed correctly. See toReversePolish().
  582     /*elseif (preg_match("/[A-Z0-9\xc0-\xdc\.]+/",$token))
  583     {
  584         return($this->_convertFunction($token,$this->_func_args));
  585     }*/
  586     // if it's an argument, ignore the token (the argument remains)
  587     elseif ($token == 'arg')
  588  {
  589         return '';
  590     }
  591     // TODO: use real error codes
  592     trigger_error("Unknown token $token", E_USER_ERROR);
  593 }
  594     
  595 /**
  596 * Convert a number token to ptgInt or ptgNum
  597 *
  598 * @access private
  599 * @param mixed $num an integer or double for conversion to its ptg value
  600 */
  601 function _convertNumber($num)
  602  {
  603 
  604     // Integer in the range 0..2**16-1
  605 
  606     if ((preg_match("/^\d+$/",$num)) and ($num <= 65535)) {
  607         return(pack("Cv", $this->ptg['ptgInt'], $num));
  608     }
  609  else { // A float
  610         if ($this->_byte_order) { // if it's Big Endian
  611             $num = strrev($num);
  612         }
  613         return pack("Cd", $this->ptg['ptgNum'], $num);
  614     }
  615 }
  616     
  617 /**
  618 * Convert a string token to ptgStr
  619 *
  620 * @access private
  621 * @param string $string A string for conversion to its ptg value
  622 */
  623 function _convertString($string)
  624  {
  625     // chop away beggining and ending quotes
  626     $string = substr($string, 1, strlen($string) - 2);
  627     return pack("CC", $this->ptg['ptgStr'], strlen($string)).$string;
  628 }
  629 
  630 /**
  631 * Convert a function to a ptgFunc or ptgFuncVarV depending on the number of
  632 * args that it takes.
  633 *
  634 * @access private
  635 * @param string  $token    The name of the function for convertion to ptg value.
  636 * @param integer $num_args The number of arguments the function receives.
  637 * @return string The packed ptg for the function
  638 */
  639 function _convertFunction($token, $num_args)
  640  {
  641     $args     = $this->_functions[$token][1];
  642     $volatile = $this->_functions[$token][3];
  643     
  644     // Fixed number of args eg. TIME($i,$j,$k).
  645     if ($args >= 0) {
  646         return pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]);
  647     }
  648     // Variable number of args eg. SUM($i,$j,$k, ..).
  649     if ($args == -1) {
  650         return pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]);
  651     }
  652 }
  653     
  654 /**
  655 * Convert an Excel range such as A1:D4 to a ptgRefV.
  656 *
  657 * @access private
  658 * @param string $range An Excel range in the A1:A2 or A1..A2 format.
  659 */
  660 function _convertRange2d($range)
  661  {
  662     $class = 2; // as far as I know, this is magick.
  663     
  664     // Split the range into 2 cell refs
  665     if (preg_match('/^\$?([A-Ia-i]?[A-Za-z])\$?(\d+)\:\$?([A-Ia-i]?[A-Za-z])\$?(\d+)$/',$range)) {
  666         list($cell1, $cell2) = split(':', $range);
  667     }
  668  elseif (preg_match('/^\$?([A-Ia-i]?[A-Za-z])\$?(\d+)\.\.\$?([A-Ia-i]?[A-Za-z])\$?(\d+)$/',$range)) {
  669         list($cell1, $cell2) = split('\.\.', $range);
  670     }
  671  else {
  672         // TODO: use real error codes
  673         trigger_error("Unknown range separator", E_USER_ERROR);
  674     }
  675     
  676     // Convert the cell references
  677     $cell_array1 = $this->_cellToPackedRowcol($cell1);
  678     if ($this->isError($cell_array1)) {
  679         return $cell_array1;
  680     }
  681     list($row1, $col1) = $cell_array1;
  682     $cell_array2 = $this->_cellToPackedRowcol($cell2);
  683     if ($this->isError($cell_array2)) {
  684         return $cell_array2;
  685     }
  686     list($row2, $col2) = $cell_array2;
  687     
  688     // The ptg value depends on the class of the ptg.
  689     if ($class == 0) {
  690         $ptgArea = pack("C", $this->ptg['ptgArea']);
  691     }
  692  elseif ($class == 1) {
  693         $ptgArea = pack("C", $this->ptg['ptgAreaV']);
  694     }
  695  elseif ($class == 2) {
  696         $ptgArea = pack("C", $this->ptg['ptgAreaA']);
  697     }
  698  else {
  699         // TODO: use real error codes
  700         trigger_error("Unknown class $class", E_USER_ERROR);
  701     }
  702     return $ptgArea . $row1 . $row2 . $col1. $col2;
  703 }
  704  
  705 /**
  706 * Convert an Excel 3d range such as "Sheet1!A1:D4" or "Sheet1:Sheet2!A1:D4" to
  707 * a ptgArea3dV.
  708 *
  709 * @access private
  710 * @param string $token An Excel range in the Sheet1!A1:A2 format.
  711 */
  712 function _convertRange3d($token)
  713  {
  714     $class = 2; // as far as I know, this is magick.
  715 
  716     // Split the ref at the ! symbol
  717     list($ext_ref, $range) = split('!', $token);
  718 
  719     // Convert the external reference part
  720     $ext_ref = $this->_packExtRef($ext_ref);
  721     if ($this->isError($ext_ref)) {
  722         return $ext_ref;
  723     }
  724 
  725     // Split the range into 2 cell refs
  726     list($cell1, $cell2) = split(':', $range);
  727 
  728     // Convert the cell references
  729     if (preg_match('/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/', $cell1))
  730  {
  731         $cell_array1 = $this->_cellToPackedRowcol($cell1);
  732         if ($this->isError($cell_array1)) {
  733             return $cell_array1;
  734         }
  735     list($row1, $col1) = $cell_array1;
  736         $cell_array2 = $this->_cellToPackedRowcol($cell2);
  737         if ($this->isError($cell_array2)) {
  738         return $cell_array2;
  739         }
  740         list($row2, $col2) = $cell_array2;
  741     }
  742  else { // It's a columns range (like 26:27)
  743     $cells_array = $this->_rangeToPackedRange($cell1.':'.$cell2);
  744     if ($this->isError($cells_array)) {
  745             return $cells_array;
  746         }
  747     list($row1, $col1, $row2, $col2) = $cells_array;
  748     }
  749  
  750     // The ptg value depends on the class of the ptg.
  751     if ($class == 0) {
  752         $ptgArea = pack("C", $this->ptg['ptgArea3d']);
  753     }
  754  elseif ($class == 1) {
  755         $ptgArea = pack("C", $this->ptg['ptgArea3dV']);
  756     }
  757  elseif ($class == 2) {
  758         $ptgArea = pack("C", $this->ptg['ptgArea3dA']);
  759     }
  760  else {
  761         trigger_error("Unknown class $class", E_USER_ERROR);
  762     }
  763  
  764     return $ptgArea . $ext_ref . $row1 . $row2 . $col1. $col2;
  765 }
  766 
  767 /**
  768 * Convert an Excel reference such as A1, $B2, C$3 or $D$4 to a ptgRefV.
  769 *
  770 * @access private
  771 * @param string $cell An Excel cell reference
  772 * @return string The cell in packed() format with the corresponding ptg
  773 */
  774 function _convertRef2d($cell)
  775  {
  776     $class = 2; // as far as I know, this is magick.
  777     
  778     // Convert the cell reference
  779     $cell_array = $this->_cellToPackedRowcol($cell);
  780     if ($this->isError($cell_array)) {
  781         return $cell_array;
  782     }
  783     list($row, $col) = $cell_array;
  784 
  785     // The ptg value depends on the class of the ptg.
  786     if ($class == 0) {
  787         $ptgRef = pack("C", $this->ptg['ptgRef']);
  788     }
  789  elseif ($class == 1) {
  790         $ptgRef = pack("C", $this->ptg['ptgRefV']);
  791     }
  792  elseif ($class == 2) {
  793         $ptgRef = pack("C", $this->ptg['ptgRefA']);
  794     }
  795  else {
  796         // TODO: use real error codes
  797         trigger_error("Unknown class $class",E_USER_ERROR);
  798     }
  799     return $ptgRef.$row.$col;
  800 }
  801     
  802 /**
  803 * Convert an Excel 3d reference such as "Sheet1!A1" or "Sheet1:Sheet2!A1" to a
  804 * ptgRef3dV.
  805 *
  806 * @access private
  807 * @param string $cell An Excel cell reference
  808 * @return string The cell in packed() format with the corresponding ptg
  809 */
  810 function _convertRef3d($cell)
  811  {
  812     $class = 2; // as far as I know, this is magick.
  813  
  814     // Split the ref at the ! symbol
  815     list($ext_ref, $cell) = split('!', $cell);
  816  
  817     // Convert the external reference part
  818     $ext_ref = $this->_packExtRef($ext_ref);
  819     if ($this->isError($ext_ref)) {
  820         return $ext_ref;
  821     }
  822  
  823     // Convert the cell reference part
  824     list($row, $col) = $this->_cellToPackedRowcol($cell);
  825  
  826     // The ptg value depends on the class of the ptg.
  827     if ($class == 0) {
  828         $ptgRef = pack("C", $this->ptg['ptgRef3d']);
  829     } elseif ($class == 1) {
  830         $ptgRef = pack("C", $this->ptg['ptgRef3dV']);
  831     } elseif ($class == 2) {
  832         $ptgRef = pack("C", $this->ptg['ptgRef3dA']);
  833     }
  834  else {
  835         trigger_error("Unknown class $class", E_USER_ERROR);
  836     }
  837 
  838     return $ptgRef . $ext_ref. $row . $col;
  839 }
  840 
  841 /**
  842 * Convert the sheet name part of an external reference, for example "Sheet1" or
  843 * "Sheet1:Sheet2", to a packed structure.
  844 *
  845 * @access private
  846 * @param string $ext_ref The name of the external reference
  847 * @return string The reference index in packed() format
  848 */
  849 function _packExtRef($ext_ref) {
  850     $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading  ' if any.
  851     $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
  852 
  853     // Check if there is a sheet range eg., Sheet1:Sheet2.
  854     if (preg_match("/:/", $ext_ref))
  855  {
  856         list($sheet_name1, $sheet_name2) = split(':', $ext_ref);
  857 
  858         $sheet1 = $this->_getSheetIndex($sheet_name1);
  859         if ($sheet1 == -1) {
  860             trigger_error("Unknown sheet name $sheet_name1 in formula",E_USER_ERROR);
  861         }
  862         $sheet2 = $this->_getSheetIndex($sheet_name2);
  863         if ($sheet2 == -1) {
  864             trigger_error("Unknown sheet name $sheet_name2 in formula",E_USER_ERROR);
  865         }
  866 
  867         // Reverse max and min sheet numbers if necessary
  868         if ($sheet1 > $sheet2) {
  869             list($sheet1, $sheet2) = array($sheet2, $sheet1);
  870         }
  871     }
  872  else { // Single sheet name only.
  873         $sheet1 = $this->_getSheetIndex($ext_ref);
  874         if ($sheet1 == -1) {
  875             trigger_error("Unknown sheet name $ext_ref in formula",E_USER_ERROR);
  876         }
  877         $sheet2 = $sheet1;
  878     }
  879  
  880     // References are stored relative to 0xFFFF.
  881     $offset = -1 - $sheet1;
  882 
  883     return pack('vdvv', $offset, 0x00, $sheet1, $sheet2);
  884 }
  885 
  886 /**
  887 * Look up the index that corresponds to an external sheet name. The hash of
  888 * sheet names is updated by the addworksheet() method of the 
  889 * Spreadsheet_Excel_Writer_Workbook class.
  890 *
  891 * @access private
  892 * @return integer
  893 */
  894 function _getSheetIndex($sheet_name)
  895  {
  896     if (!isset($this->_ext_sheets[$sheet_name])) {
  897         return -1;
  898     }
  899  else {
  900         return $this->_ext_sheets[$sheet_name];
  901     }
  902 }
  903 
  904 /**
  905 * This method is used to update the array of sheet names. It is
  906 * called by the addWorksheet() method of the Spreadsheet_Excel_Writer_Workbook class.
  907 *
  908 * @access private
  909 * @param string  $name  The name of the worksheet being added
  910 * @param integer $index The index of the worksheet being added
  911 */
  912 function set_ext_sheet($name, $index)
  913  {
  914     $this->_ext_sheets[$name] = $index;
  915 }
  916 
  917 /**
  918 * pack() row and column into the required 3 byte format.
  919 *
  920 * @access private
  921 * @param string $cell The Excel cell reference to be packed
  922 * @return array Array containing the row and column in packed() format
  923 */
  924 function _cellToPackedRowcol($cell)
  925  {
  926     $cell = strtoupper($cell);
  927     list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
  928     if ($col >= 256) {
  929         trigger_error("Column in: $cell greater than 255", E_USER_ERROR);
  930     }
  931     if ($row >= 16384) {
  932         trigger_error("Row in: $cell greater than 16384 ", E_USER_ERROR);
  933     }
  934 
  935     // Set the high bits to indicate if row or col are relative.
  936     $row    |= $col_rel << 14;
  937     $row    |= $row_rel << 15;
  938 
  939     $row     = pack('v', $row);
  940     $col     = pack('C', $col);
  941 
  942     return array($row, $col);
  943 }
  944     
  945 /**
  946 * pack() row range into the required 3 byte format.
  947 * Just using maximun col/rows, which is probably not the correct solution
  948 *
  949 * @access private
  950 * @param string $range The Excel range to be packed
  951 * @return array Array containing (row1,col1,row2,col2) in packed() format
  952 */
  953 function _rangeToPackedRange($range)
  954  {
  955     preg_match('/(\$)?(\d+)\:(\$)?(\d+)/', $range, $match);
  956     // return absolute rows if there is a $ in the ref
  957     $row1_rel = empty($match[1]) ? 1 : 0;
  958     $row1     = $match[2];
  959     $row2_rel = empty($match[3]) ? 1 : 0;
  960     $row2     = $match[4];
  961     // Convert 1-index to zero-index
  962     $row1--;
  963     $row2--;
  964     // Trick poor inocent Excel
  965     $col1 = 0;
  966     $col2 = 16383; // maximum possible value for Excel 5 (change this!!!)
  967 
  968     //list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
  969     if (($row1 >= 16384) or ($row2 >= 16384)) {
  970         trigger_error("Row in: $range greater than 16384 ",E_USER_ERROR);
  971     }
  972 
  973     // Set the high bits to indicate if rows are relative.
  974     $row1    |= $row1_rel << 14;
  975     $row2    |= $row2_rel << 15;
  976 
  977     $row1     = pack('v', $row1);
  978     $row2     = pack('v', $row2);
  979     $col1     = pack('C', $col1);
  980     $col2     = pack('C', $col2);
  981 
  982     return array($row1, $col1, $row2, $col2);
  983 }
  984 
  985 /**
  986 * Convert an Excel cell reference such as A1 or $B2 or C$3 or $D$4 to a zero
  987 * indexed row and column number. Also returns two (0,1) values to indicate
  988 * whether the row or column are relative references.
  989 *
  990 * @access private
  991 * @param string $cell The Excel cell reference in A1 format.
  992 * @return array
  993 */
  994 function _cellToRowcol($cell)
  995  {
  996     preg_match('/(\$)?([A-I]?[A-Z])(\$)?(\d+)/',$cell,$match);
  997     // return absolute column if there is a $ in the ref
  998     $col_rel = empty($match[1]) ? 1 : 0;
  999     $col_ref = $match[2];
 1000     $row_rel = empty($match[3]) ? 1 : 0;
 1001     $row     = $match[4];
 1002     
 1003     // Convert base26 column string to a number.
 1004     $expn   = strlen($col_ref) - 1;
 1005     $col    = 0;
 1006     for ($i=0; $i < strlen($col_ref); $i++)
 1007  {
 1008         $col += (ord($col_ref{$i}) - ord('A') + 1) * pow(26, $expn);
 1009         $expn--;
 1010     }
 1011     
 1012     // Convert 1-index to zero-index
 1013     $row--;
 1014     $col--;
 1015     
 1016     return array($row, $col, $row_rel, $col_rel);
 1017 }
 1018     
 1019 /**
 1020 * Advance to the next valid token.
 1021 *
 1022 * @access private
 1023 */
 1024 function _advance()
 1025  {
 1026     $i = $this->_current_char;
 1027     // eat up white spaces
 1028     if ($i < strlen($this->_formula))
 1029  {
 1030         while ($this->_formula{$i} == " ") {
 1031             $i++;
 1032         }
 1033         if ($i < strlen($this->_formula) - 1) {
 1034             $this->_lookahead = $this->_formula{$i+1};
 1035         }
 1036         $token = "";
 1037     }
 1038     while ($i < strlen($this->_formula))
 1039  {
 1040         $token .= $this->_formula{$i};
 1041         if ($i < strlen($this->_formula) - 1) {
 1042             $this->_lookahead = $this->_formula{$i+1};
 1043         }
 1044  else {
 1045             $this->_lookahead = '';
 1046         }
 1047         if ($this->_match($token) != '')
 1048  {
 1049             //if ($i < strlen($this->_formula) - 1) {
 1050             //    $this->_lookahead = $this->_formula{$i+1};
 1051             //}
 1052             $this->_current_char = $i + 1;
 1053             $this->_current_token = $token;
 1054             return 1;
 1055         }
 1056         if ($i < strlen($this->_formula) - 2) {
 1057             $this->_lookahead = $this->_formula{$i+2};
 1058         }
 1059  else {
 1060         // if we run out of characters _lookahead becomes empty
 1061             $this->_lookahead = '';
 1062         }
 1063         $i++;
 1064     }
 1065     //die("Lexical error ".$this->_current_char);
 1066 }
 1067     
 1068 /**
 1069 * Checks if it's a valid token.
 1070 *
 1071 * @access private
 1072 * @param mixed $token The token to check.
 1073 * @return mixed       The checked token or false on failure
 1074 */
 1075 function _match($token)
 1076  {
 1077     switch($token)
 1078  {
 1079         case SPREADSHEET_EXCEL_WRITER_ADD:
 1080             return($token);
 1081             break;
 1082         case SPREADSHEET_EXCEL_WRITER_SUB:
 1083             return($token);
 1084             break;
 1085         case SPREADSHEET_EXCEL_WRITER_MUL:
 1086             return($token);
 1087             break;
 1088         case SPREADSHEET_EXCEL_WRITER_DIV:
 1089             return($token);
 1090             break;
 1091         case SPREADSHEET_EXCEL_WRITER_OPEN:
 1092             return($token);
 1093             break;
 1094         case SPREADSHEET_EXCEL_WRITER_CLOSE:
 1095             return($token);
 1096             break;
 1097         case SPREADSHEET_EXCEL_WRITER_SCOLON:
 1098             return($token);
 1099             break;
 1100         case SPREADSHEET_EXCEL_WRITER_COMA:
 1101             return($token);
 1102             break;
 1103         case SPREADSHEET_EXCEL_WRITER_GT:
 1104             if ($this->_lookahead == '=') { // it's a GE token
 1105                 break;
 1106             }
 1107             return($token);
 1108             break;
 1109         case SPREADSHEET_EXCEL_WRITER_LT:
 1110             // it's a LE or a NE token
 1111             if (($this->_lookahead == '=') or ($this->_lookahead == '>')) {
 1112                 break;
 1113             }
 1114             return($token);
 1115             break;
 1116         case SPREADSHEET_EXCEL_WRITER_GE:
 1117             return($token);
 1118             break;
 1119         case SPREADSHEET_EXCEL_WRITER_LE:
 1120             return($token);
 1121             break;
 1122         case SPREADSHEET_EXCEL_WRITER_EQ:
 1123             return($token);
 1124             break;
 1125         case SPREADSHEET_EXCEL_WRITER_NE:
 1126             return($token);
 1127             break;
 1128         default:
 1129             // if it's a reference
 1130             if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$token) and
 1131                !ereg("[0-9]",$this->_lookahead) and 
 1132                ($this->_lookahead != ':') and ($this->_lookahead != '.') and
 1133                ($this->_lookahead != '!'))
 1134  {
 1135                 return $token;
 1136             }
 1137             // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
 1138             elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\![A-Ia-i]?[A-Za-z][0-9]+$/",$token) and
 1139                    !ereg("[0-9]",$this->_lookahead) and
 1140                    ($this->_lookahead != ':') and ($this->_lookahead != '.'))
 1141  {
 1142                 return $token;
 1143             }
 1144             // if it's a range (A1:A2)
 1145             elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and 
 1146                    !ereg("[0-9]",$this->_lookahead))
 1147  {
 1148                 return $token;
 1149             }
 1150             // if it's a range (A1..A2)
 1151             elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and 
 1152                    !ereg("[0-9]",$this->_lookahead))
 1153  {
 1154                 return $token;
 1155             }
 1156             // If it's an external range like Sheet1:Sheet2!A1:B2
 1157             elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/",$token) and
 1158                    !ereg("[0-9]",$this->_lookahead))
 1159  {
 1160                 return $token;
 1161             }
 1162         // If it's an external range like 'Sheet1:Sheet2'!A1:B2
 1163             elseif (preg_match("/^'[A-Za-z0-9_ ]+(\:[A-Za-z0-9_ ]+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/",$token) and
 1164                    !ereg("[0-9]",$this->_lookahead))
 1165  {
 1166                 return $token;
 1167             }
 1168             // If it's a number (check that it's not a sheet name or range)
 1169             elseif (is_numeric($token) and 
 1170                     (!is_numeric($token.$this->_lookahead) or ($this->_lookahead == '')) and
 1171                     ($this->_lookahead != '!') and ($this->_lookahead != ':'))
 1172  {
 1173                 return $token;
 1174             }
 1175             // If it's a string (of maximum 255 characters)
 1176             elseif (ereg("^\"[^\"]{0,255}\"$",$token))
 1177  {
 1178                 return $token;
 1179             }
 1180             // if it's a function call
 1181             elseif (eregi("^[A-Z0-9\xc0-\xdc\.]+$",$token) and ($this->_lookahead == "(")) {
 1182                 return $token;
 1183             }
 1184             return '';
 1185     }
 1186 }
 1187     
 1188 /**
 1189 * The parsing method. It parses a formula.
 1190 *
 1191 * @access public
 1192 * @param string $formula The formula to parse, without the initial equal sign (=).
 1193 */
 1194 function parse($formula)
 1195  {
 1196     $this->_current_char = 0;
 1197     $this->_formula      = $formula;
 1198     $this->_lookahead    = $formula{1};
 1199     $this->_advance();
 1200     $this->_parse_tree   = $this->_condition();
 1201     if ($this->isError($this->_parse_tree)) {
 1202         return $this->_parse_tree;
 1203     }
 1204 }
 1205     
 1206 /**
 1207 * It parses a condition. It assumes the following rule:
 1208 * Cond -> Expr [(">" | "<") Expr]
 1209 *
 1210 * @access private
 1211 * @return mixed The parsed ptg'd tree
 1212 */
 1213 function _condition()
 1214  {
 1215     $result = $this->_expression();
 1216     if ($this->isError($result)) {
 1217         return $result;
 1218     }
 1219     if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LT)
 1220  {
 1221         $this->_advance();
 1222         $result2 = $this->_expression();
 1223         if ($this->isError($result2)) {
 1224             return $result2;
 1225         }
 1226         $result = $this->_createTree('ptgLT', $result, $result2);
 1227     }
 1228  elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GT) 
 1229 {
 1230         $this->_advance();
 1231         $result2 = $this->_expression();
 1232         if ($this->isError($result2)) {
 1233             return $result2;
 1234         }
 1235         $result = $this->_createTree('ptgGT', $result, $result2);
 1236     }
 1237  elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LE) 
 1238 {
 1239         $this->_advance();
 1240         $result2 = $this->_expression();
 1241         if ($this->isError($result2)) {
 1242             return $result2;
 1243         }
 1244         $result = $this->_createTree('ptgLE', $result, $result2);
 1245     }
 1246  elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GE) 
 1247 {
 1248         $this->_advance();
 1249         $result2 = $this->_expression();
 1250         if ($this->isError($result2)) {
 1251             return $result2;
 1252         }
 1253         $result = $this->_createTree('ptgGE', $result, $result2);
 1254     }
 1255  elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_EQ) 
 1256 {
 1257         $this->_advance();
 1258         $result2 = $this->_expression();
 1259         if ($this->isError($result2)) {
 1260             return $result2;
 1261         }
 1262         $result = $this->_createTree('ptgEQ', $result, $result2);
 1263     }
 1264  elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_NE) 
 1265 {
 1266         $this->_advance();
 1267         $result2 = $this->_expression();
 1268         if ($this->isError($result2)) {
 1269             return $result2;
 1270         }
 1271         $result = $this->_createTree('ptgNE', $result, $result2);
 1272     }
 1273     return $result;
 1274 }
 1275 
 1276 /**
 1277 * It parses a expression. It assumes the following rule:
 1278 * Expr -> Term [("+" | "-") Term]
 1279 *
 1280 * @access private
 1281 * @return mixed The parsed ptg'd tree
 1282 */
 1283 function _expression()
 1284  {
 1285     // If it's a string return a string node
 1286     if (ereg("^\"[^\"]{0,255}\"$", $this->_current_token))
 1287  {
 1288         $result = $this->_createTree($this->_current_token, '', '');
 1289         $this->_advance();
 1290         return $result;
 1291     }
 1292     $result = $this->_term();
 1293     if ($this->isError($result)) {
 1294         return $result;
 1295     }
 1296     while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) or 
 1297            ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB))
 1298  {
 1299         if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD)
 1300  
 1301 {
 1302             $this->_advance();
 1303             $result2 = $this->_term();
 1304             if ($this->isError($result2)) {
 1305                 return $result2;
 1306             }
 1307             $result = $this->_createTree('ptgAdd', $result, $result2);
 1308         }
 1309  else 
 1310 {
 1311             $this->_advance();
 1312             $result2 = $this->_term();
 1313             if ($this->isError($result2)) {
 1314                 return $result2;
 1315             }
 1316             $result = $this->_createTree('ptgSub', $result, $result2);
 1317         }
 1318     }
 1319     return $result;
 1320 }
 1321     
 1322 /**
 1323 * This function just introduces a ptgParen element in the tree, so that Excel
 1324 * doesn't get confused when working with a parenthesized formula afterwards.
 1325 *
 1326 * @access private
 1327 * @see _fact()
 1328 * @return mixed The parsed ptg'd tree
 1329 */
 1330 function _parenthesizedExpression()
 1331  {
 1332     $result = $this->_createTree('ptgParen', $this->_expression(), '');
 1333     return $result;
 1334 }
 1335     
 1336 /**
 1337 * It parses a term. It assumes the following rule:
 1338 * Term -> Fact [("*" | "/") Fact]
 1339 *
 1340 * @access private
 1341 * @return mixed The parsed ptg'd tree
 1342 */
 1343 function _term()
 1344  {
 1345     $result = $this->_fact();
 1346     if ($this->isError($result)) {
 1347         return $result;
 1348     }
 1349     while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) or 
 1350            ($this->_current_token == SPREADSHEET_EXCEL_WRITER_DIV)) {
 1351         if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL)
 1352  
 1353 {
 1354             $this->_advance();
 1355             $result2 = $this->_fact();
 1356             if ($this->isError($result2)) {
 1357                 return $result2;
 1358             }
 1359             $result = $this->_createTree('ptgMul', $result, $result2);
 1360         }
 1361  else 
 1362 {
 1363             $this->_advance();
 1364             $result2 = $this->_fact();
 1365             if ($this->isError($result2)) {
 1366                 return $result2;
 1367             }
 1368             $result = $this->_createTree('ptgDiv', $result, $result2);
 1369         }
 1370     }
 1371     return $result;
 1372 }
 1373     
 1374 /**
 1375 * It parses a factor. It assumes the following rule:
 1376 * Fact -> ( Expr )
 1377 *       | CellRef
 1378 *       | CellRange
 1379 *       | Number
 1380 *       | Function
 1381 *
 1382 * @access private
 1383 * @return mixed The parsed ptg'd tree
 1384 */
 1385 function _fact()
 1386  {
 1387     if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_OPEN)
 1388  {
 1389         $this->_advance();         // eat the "("
 1390         $result = $this->_parenthesizedExpression();
 1391         if ($this->_current_token != SPREADSHEET_EXCEL_WRITER_CLOSE) {
 1392             trigger_error("')' token expected.",E_USER_ERROR);
 1393         }
 1394         $this->_advance();         // eat the ")"
 1395         return $result;
 1396     }
 1397  if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$this->_current_token))
 1398  {
 1399     // if it's a reference
 1400         $result = $this->_createTree($this->_current_token, '', '');
 1401         $this->_advance();
 1402         return $result;
 1403     }
 1404  elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\![A-Ia-i]?[A-Za-z][0-9]+$/",$this->_current_token))
 1405  {
 1406     // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
 1407         $result = $this->_createTree($this->_current_token, '', '');
 1408         $this->_advance();
 1409         return $result;
 1410     }
 1411  elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token) or 
 1412               preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token))
 1413  {
 1414     // if it's a range
 1415         $result = $this->_current_token;
 1416         $this->_advance();
 1417         return $result;
 1418     }
 1419  elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/",$this->_current_token))
 1420  {
 1421     // If it's an external range (Sheet1!A1:B2)
 1422         $result = $this->_current_token;
 1423         $this->_advance();
 1424         return $result;
 1425     }
 1426  elseif (preg_match("/^'[A-Za-z0-9_ ]+(\:[A-Za-z0-9_ ]+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/",$this->_current_token))
 1427  {
 1428     // If it's an external range ('Sheet1'!A1:B2)
 1429         $result = $this->_current_token;
 1430         $this->_advance();
 1431         return $result;
 1432     }
 1433  elseif (is_numeric($this->_current_token))
 1434  {
 1435         $result = $this->_createTree($this->_current_token, '', '');
 1436         $this->_advance();
 1437         return $result;
 1438     }
 1439  elseif (eregi("^[A-Z0-9\xc0-\xdc\.]+$",$this->_current_token))
 1440  {
 1441     // if it's a function call
 1442         $result = $this->_func();
 1443         return $result;
 1444     }
 1445     trigger_error("Sintactic error: ".$this->_current_token.", lookahead: ".
 1446                           $this->_lookahead.", current char: ".$this->_current_char, E_USER_ERROR);
 1447 }
 1448     
 1449 /**
 1450 * It parses a function call. It assumes the following rule:
 1451 * Func -> ( Expr [,Expr]* )
 1452 *
 1453 * @access private
 1454 */
 1455 function _func()
 1456  {
 1457     $num_args = 0; // number of arguments received
 1458     $function = $this->_current_token;
 1459     $this->_advance();
 1460     $this->_advance();         // eat the "("
 1461     while ($this->_current_token != ')')
 1462  {
 1463         if ($num_args > 0)
 1464  {
 1465             if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_COMA ||
 1466         $this->_current_token == SPREADSHEET_EXCEL_WRITER_SCOLON) {
 1467                 $this->_advance();  // eat the ","
 1468             }
 1469  else {
 1470                 trigger_error("Sintactic error: coma expected in ".
 1471                                   "function $function, {$num_args} arg", E_USER_ERROR);
 1472             }
 1473             $result2 = $this->_condition();
 1474             if ($this->isError($result2)) {
 1475                 return $result2;
 1476             }
 1477             $result = $this->_createTree('arg', $result, $result2);
 1478         }
 1479  else { // first argument
 1480             $result2 = $this->_condition();
 1481             if ($this->isError($result2)) {
 1482                 return $result2;
 1483             }
 1484             $result = $this->_createTree('arg', '', $result2);
 1485         }
 1486         $num_args++;
 1487     }
 1488     $args = $this->_functions[$function][1];
 1489     // If fixed number of args eg. TIME($i,$j,$k). Check that the number of args is valid.
 1490     if (($args >= 0) and ($args != $num_args)) {
 1491         trigger_error("Incorrect number of arguments in function $function() ",E_USER_ERROR);
 1492     }
 1493 
 1494     $result = $this->_createTree($function, $result, $num_args);
 1495     $this->_advance();         // eat the ")"
 1496     return $result;
 1497 }
 1498     
 1499 /**
 1500 * Creates a tree. In fact an array which may have one or two arrays (sub-trees)
 1501 * as elements.
 1502 *
 1503 * @access private
 1504 * @param mixed $value The value of this node.
 1505 * @param mixed $left  The left array (sub-tree) or a final node.
 1506 * @param mixed $right The right array (sub-tree) or a final node.
 1507 */
 1508 function _createTree($value, $left, $right)
 1509  {
 1510     return(array('value' => $value, 'left' => $left, 'right' => $right));
 1511 }
 1512     
 1513 /**
 1514 * Builds a string containing the tree in reverse polish notation (What you 
 1515 * would use in a HP calculator stack).
 1516 * The following tree:
 1517 * 
 1518 *    +
 1519 *   / \
 1520 *  2   3
 1521 *
 1522 * produces: "23+"
 1523 *
 1524 * The following tree:
 1525 *
 1526 *    +
 1527 *   / \
 1528 *  3   *
 1529 *     / \
 1530 *    6   A1
 1531 *
 1532 * produces: "36A1*+"
 1533 *
 1534 * In fact all operands, functions, references, etc... are written as ptg's
 1535 *
 1536 * @access public
 1537 * @param array $tree The optional tree to convert.
 1538 * @return string The tree in reverse polish notation
 1539 */
 1540 function toReversePolish($tree = array())
 1541  {
 1542     $polish = ""; // the string we are going to return
 1543     if (empty($tree)) { // If it's the first call use _parse_tree
 1544         $tree = $this->_parse_tree;
 1545     }
 1546     if (is_array($tree['left']))
 1547  {
 1548         $converted_tree = $this->toReversePolish($tree['left']);
 1549         if ($this->isError($converted_tree)) {
 1550             return $converted_tree;
 1551         }
 1552         $polish .= $converted_tree;
 1553     }
 1554  elseif ($tree['left'] != '') { // It's a final node
 1555         $converted_tree = $this->_convert($tree['left']);
 1556         if ($this->isError($converted_tree)) {
 1557             return $converted_tree;
 1558         }
 1559         $polish .= $converted_tree;
 1560     }
 1561     if (is_array($tree['right']))
 1562  {
 1563         $converted_tree = $this->toReversePolish($tree['right']);
 1564         if ($this->isError($converted_tree)) {
 1565             return $converted_tree;
 1566         }
 1567         $polish .= $converted_tree;
 1568     }
 1569  elseif ($tree['right'] != '') { // It's a final node
 1570         $converted_tree = $this->_convert($tree['right']);
 1571         if ($this->isError($converted_tree)) {
 1572             return $converted_tree;
 1573         }
 1574         $polish .= $converted_tree;
 1575     }
 1576     // if it's a function convert it here (so we can set it's arguments)
 1577     if (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/",$tree['value']) and
 1578         !preg_match('/^([A-Ia-i]?[A-Za-z])(\d+)$/',$tree['value']) and
 1579         !preg_match("/^[A-Ia-i]?[A-Za-z](\d+)\.\.[A-Ia-i]?[A-Za-z](\d+)$/",$tree['value']) and
 1580         !is_numeric($tree['value']) and
 1581         !isset($this->ptg[$tree['value']]))
 1582  {
 1583         // left subtree for a function is always an array.
 1584         if ($tree['left'] != '') {
 1585             $left_tree = $this->toReversePolish($tree['left']);
 1586         }
 1587  else {
 1588             $left_tree = '';
 1589         }
 1590         if ($this->isError($left_tree)) {
 1591             return $left_tree;
 1592         }
 1593         // add it's left subtree and return.
 1594         return $left_tree.$this->_convertFunction($tree['value'], $tree['right']);
 1595     }
 1596  else
 1597  {
 1598         $converted_tree = $this->_convert($tree['value']);
 1599         if ($this->isError($converted_tree)) {
 1600             return $converted_tree;
 1601         }
 1602     }
 1603     $polish .= $converted_tree;
 1604     return $polish;
 1605 }
 1606 
 1607 }
 1608 
 1609 
 1610 ?>