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