WebSVN – eFlore/Applications.cel – Diff – /trunk/jrest/lib/Spreadsheet/Excel/Writer/Parser.php

 */
 /**
 * @const SPREADSHEET_EXCEL_WRITER_ADD token identifier for character "+"
 */
-define('SPREADSHEET_EXCEL_WRITER_ADD',"+");
+define('SPREADSHEET_EXCEL_WRITER_ADD', "+");
 /**
 * @const SPREADSHEET_EXCEL_WRITER_SUB token identifier for character "-"
 */
-define('SPREADSHEET_EXCEL_WRITER_SUB',"-");
+define('SPREADSHEET_EXCEL_WRITER_SUB', "-");
 /**
 * @const SPREADSHEET_EXCEL_WRITER_MUL token identifier for character "*"
 */
-define('SPREADSHEET_EXCEL_WRITER_MUL',"*");
+define('SPREADSHEET_EXCEL_WRITER_MUL', "*");
 /**
 * @const SPREADSHEET_EXCEL_WRITER_DIV token identifier for character "/"
 */
-define('SPREADSHEET_EXCEL_WRITER_DIV',"/");
+define('SPREADSHEET_EXCEL_WRITER_DIV', "/");
 /**
 * @const SPREADSHEET_EXCEL_WRITER_OPEN token identifier for character "("
 */
-define('SPREADSHEET_EXCEL_WRITER_OPEN',"(");
+define('SPREADSHEET_EXCEL_WRITER_OPEN', "(");
 /**
 * @const SPREADSHEET_EXCEL_WRITER_CLOSE token identifier for character ")"
+*/
+define('SPREADSHEET_EXCEL_WRITER_CLOSE', ")");
+/**
+* @const SPREADSHEET_EXCEL_WRITER_COMA token identifier for character ","
 */
-define('SPREADSHEET_EXCEL_WRITER_CLOSE',")");
+define('SPREADSHEET_EXCEL_WRITER_COMA', ",");
 /**
-* @const SPREADSHEET_EXCEL_WRITER_COMA token identifier for character ","
+* @const SPREADSHEET_EXCEL_WRITER_SEMICOLON token identifier for character ";"
 */
-define('SPREADSHEET_EXCEL_WRITER_COMA',",");
+define('SPREADSHEET_EXCEL_WRITER_SEMICOLON', ";");
 /**
 * @const SPREADSHEET_EXCEL_WRITER_GT token identifier for character ">"
 */
-define('SPREADSHEET_EXCEL_WRITER_GT',">");
+define('SPREADSHEET_EXCEL_WRITER_GT', ">");
 /**
 * @const SPREADSHEET_EXCEL_WRITER_LT token identifier for character "<"
 */
-define('SPREADSHEET_EXCEL_WRITER_LT',"<");
+define('SPREADSHEET_EXCEL_WRITER_LT', "<");
 /**
 * @const SPREADSHEET_EXCEL_WRITER_LE token identifier for character "<="
 */
-define('SPREADSHEET_EXCEL_WRITER_LE',"<=");
+define('SPREADSHEET_EXCEL_WRITER_LE', "<=");
 /**
+* @const SPREADSHEET_EXCEL_WRITER_GE token identifier for character ">="
+*/
+define('SPREADSHEET_EXCEL_WRITER_GE', ">=");
-* @const SPREADSHEET_EXCEL_WRITER_GE token identifier for character ">="
+/**
-*/
+* @const SPREADSHEET_EXCEL_WRITER_EQ token identifier for character "="
-define('SPREADSHEET_EXCEL_WRITER_GE',">=");
+*/
 define('SPREADSHEET_EXCEL_WRITER_EQ', "=");
 /**
     * @var integer
     */
     var $_byte_order;
     /**
-    * Number of arguments for the current function
+    * Array of external sheets
-    * @var integer
+    * @var array
     */
-    var $_func_args;
+    var $_ext_sheets;
     /**
-    * Array of external sheets
+    * Array of sheet references in the form of REF structures
     * @var array
+    */
+    var $_references;
+    /**
+    * The BIFF version for the workbook
+    * @var integer
     */
-    var $_ext_sheets;
+    var $_BIFF_version;
     /**
     * The class constructor
+    *
     * @param integer $byte_order The byte order (Little endian or Big endian) of the architecture
-                                 (optional). 1 => big endian, 0 (default) => little endian.
+                                 (optional). 1 => big endian, 0 (default) little endian.
     */
+    function Spreadsheet_Excel_Writer_Parser($byte_order, $biff_version)
     function Spreadsheet_Excel_Writer_Parser($byte_order = 0)
-    {
+        $this->_current_char  = 0;
-        $this->_current_char  = 0;
+        $this->_BIFF_version  = $biff_version;
         $this->_current_token = '';       // The token we are working on.
-        $this->_formula       = "";       // The formula to parse.
+        $this->_formula       = '';       // The formula to parse.
         $this->_lookahead     = '';       // The character ahead of the current char.
-        $this->_parse_tree    = '';       // The parse tree to be generated.
-        $this->_initializeHashes();      // Initialize the hashes: ptg's and function's ptg's
+        $this->_parse_tree    = '';       // The parse tree to be generated.
+        $this->_initializeHashes();      // Initialize the hashes: ptg's and function's ptg's
         $this->_byte_order = $byte_order; // Little Endian or Big Endian
-        $this->_func_args  = 0;           // Number of arguments for the current function
+        $this->_ext_sheets = array();
-        $this->_ext_sheets = array();
+        $this->_references = array();
     }
     /**
     * Initialize the ptg and function hashes.
+    *
     * @access private
             'ptgRef3dA'    => 0x7A,
             'ptgArea3dA'   => 0x7B,
             'ptgRefErr3dA' => 0x7C,
             'ptgAreaErr3d' => 0x7D
             );
         // Thanks to Michael Meeks and Gnumeric for the initial arg values.
         //
         // The following hash was generated by "function_locale.pl" in the distro.
         // Refer to function_locale.pl for non-English function names.
         //
               'COUNTIF'         => array( 346,    2,    0,    0 ),
               'COUNTBLANK'      => array( 347,    1,    0,    0 ),
               'ROMAN'           => array( 354,   -1,    1,    0 )
               );
+    }
     /**
     * Convert a token to the proper ptg value.
+    *
     * @access private
     * @param mixed $token The token to convert.
+    * @return mixed the converted token on success. PEAR_Error if the token
+    *               is not recognized
     */
     function _convert($token)
+    {
-        if (preg_match("/^\"[^\"]{0,255}\"$/", $token))
+        if (preg_match("/^\"[^\"]{0,255}\"$/", $token)) {
-        {
             return $this->_convertString($token);
-        }
-        elseif (is_numeric($token))
+        } elseif (is_numeric($token)) {
-        {
             return $this->_convertNumber($token);
-        }
         // match references like A1 or $A$1
-        elseif(preg_match('/^\$?([A-I]?[A-Z])\$?(\d+)$/',$token))
+        } elseif (preg_match('/^\$?([A-Ia-i]?[A-Za-z])\$?(\d+)$/',$token)) {
-        {
-            return($this->_convertRef2d($token));
+            return $this->_convertRef2d($token);
-        }
-        // match external references like Sheet1:Sheet2!A1
+        // match external references like Sheet1!A1 or Sheet1:Sheet2!A1
-        elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\![A-I]?[A-Z](\d+)$/",$token))
+        } elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z](\d+)$/u",$token)) {
-        {
             return $this->_convertRef3d($token);
+        // match external references like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1
+        } elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z](\d+)$/u",$token)) {
-        }
+            return $this->_convertRef3d($token);
         // match ranges like A1:B2
-        elseif(preg_match("/^(\$)?[A-I]?[A-Z](\$)?(\d+)\:(\$)?[A-I]?[A-Z](\$)?(\d+)$/",$token))
+        } elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\:(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token)) {
-        {
-            return($this->_convertRange2d($token));
+            return $this->_convertRange2d($token);
-        }
         // match ranges like A1..B2
-        elseif(preg_match("/^(\$)?[A-I]?[A-Z](\$)?(\d+)\.\.(\$)?[A-I]?[A-Z](\$)?(\d+)$/",$token))
+        } elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token)) {
-        {
-            return($this->_convertRange2d($token));
+            return $this->_convertRange2d($token);
-        }
-        // match external ranges like Sheet1:Sheet2!A1:B2
+        // match external ranges like Sheet1!A1 or Sheet1:Sheet2!A1:B2
-        elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\!([A-I]?[A-Z])?(\d+)\:([A-I]?[A-Z])?(\d+)$/",$token))
+        } elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/u",$token)) {
-        {
             return $this->_convertRange3d($token);
+        // match external ranges like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2
+        } elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/u",$token)) {
-        }
+            return $this->_convertRange3d($token);
-        elseif(isset($this->ptg[$token])) // operators (including parentheses)
+        // operators (including parentheses)
-        {
+        } elseif (isset($this->ptg[$token])) {
-            return(pack("C", $this->ptg[$token]));
+            return pack("C", $this->ptg[$token]);
-        }
+        // commented so argument number can be processed correctly. See toReversePolish().
-        elseif(preg_match("/[A-Z0-9\xc0-\xdc\.]+/",$token))
+        /*elseif (preg_match("/[A-Z0-9\xc0-\xdc\.]+/",$token))
+        {
             return($this->_convertFunction($token,$this->_func_args));
-        }
+        }*/
         // if it's an argument, ignore the token (the argument remains)
-        elseif($token == 'arg')
+        } elseif ($token == 'arg') {
-        {
-            $this->_func_args++;
-            return('');
+            return '';
+        }
         // TODO: use real error codes
-        $this->raiseError("Unknown token $token", 0, PEAR_ERROR_DIE);
+        return $this->raiseError("Unknown token $token");
+    }
     /**
     * Convert a number token to ptgInt or ptgNum
+    *
     * @access private
     * @param mixed $num an integer or double for conversion to its ptg value
     */
     function _convertNumber($num)
+    {
         // Integer in the range 0..2**16-1
-        if ((preg_match("/^\d+$/",$num)) and ($num <= 65535)) {
+        if ((preg_match("/^\d+$/", $num)) and ($num <= 65535)) {
-            return(pack("Cv", $this->ptg['ptgInt'], $num));
+            return pack("Cv", $this->ptg['ptgInt'], $num);
-        }
-        else // A float
+        } else { // A float
-        {
-            if($this->_byte_order) // if it's Big Endian
+            if ($this->_byte_order) { // if it's Big Endian
-            {
                 $num = strrev($num);
+            }
-            return(pack("Cd", $this->ptg['ptgNum'], $num));
+            return pack("Cd", $this->ptg['ptgNum'], $num);
+        }
+    }
     /**
     * Convert a string token to ptgStr
+    *
     * @access private
-    * @param string $string A string for conversion to its ptg value
+    * @param string $string A string for conversion to its ptg value.
+    * @return mixed the converted token on success. PEAR_Error if the string
+    *               is longer than 255 characters.
     */
     function _convertString($string)
+    {
         // chop away beggining and ending quotes
         $string = substr($string, 1, strlen($string) - 2);
+        if (strlen($string) > 255) {
+            return $this->raiseError("String is too long");
+        }
+        if ($this->_BIFF_version == 0x0500) {
-        return pack("CC", $this->ptg['ptgStr'], strlen($string)).$string;
+            return pack("CC", $this->ptg['ptgStr'], strlen($string)).$string;
+        } elseif ($this->_BIFF_version == 0x0600) {
+            $encoding = 0;   // TODO: Unicode support
+            return pack("CCC", $this->ptg['ptgStr'], strlen($string), $encoding).$string;
+        }
+    }
     /**
     * Convert a function to a ptgFunc or ptgFuncVarV depending on the number of
     * args that it takes.
+    *
     * @access private
     * @param string  $token    The name of the function for convertion to ptg value.
-    * @param integer $num_args The number of arguments the function recieves.
+    * @param integer $num_args The number of arguments the function receives.
+    * @return string The packed ptg for the function
     */
     function _convertFunction($token, $num_args)
+    {
-        $this->_func_args = 0; // re initialize the number of arguments
         $args     = $this->_functions[$token][1];
         $volatile = $this->_functions[$token][3];
         // Fixed number of args eg. TIME($i,$j,$k).
         if ($args >= 0) {
-            return(pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]));
+            return pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]);
+        }
         // Variable number of args eg. SUM($i,$j,$k, ..).
         if ($args == -1) {
-            return(pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]));
+            return pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]);
+        }
+    }
     /**
     * Convert an Excel range such as A1:D4 to a ptgRefV.
+    *
     * @access private
     * @param string $range An Excel range in the A1:A2 or A1..A2 format.
     */
-    function _convertRange2d($range)
+    function _convertRange2d($range, $class=0)
+    {
-        $class = 2; // as far as I know, this is magick.
+        // TODO: possible class value 0,1,2 check Formula.pm
         // Split the range into 2 cell refs
-        if(preg_match("/^([A-I]?[A-Z])(\d+)\:([A-I]?[A-Z])(\d+)$/",$range)) {
+        if (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\:([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) {
             list($cell1, $cell2) = explode(':', $range);
-        }
-        elseif(preg_match("/^([A-I]?[A-Z])(\d+)\.\.([A-I]?[A-Z])(\d+)$/",$range)) {
+        } elseif (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\.\.([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) {
-            list($cell1, $cell2) = explode('\.\.', $range);
+            list($cell1, $cell2) = explode('..', $range);
-        }
-        else {
+        } else {
             // TODO: use real error codes
-            $this->raiseError("Unknown range separator", 0, PEAR_ERROR_DIE);
+            return $this->raiseError("Unknown range separator", 0, PEAR_ERROR_DIE);
+        }
         // Convert the cell references
         $cell_array1 = $this->_cellToPackedRowcol($cell1);
-        if($this->isError($cell_array1)) {
+        if (PEAR::isError($cell_array1)) {
-            return($cell_array1);
+            return $cell_array1;
-            }
+        }
-        list($row1, $col1) = $cell_array1; //$this->_cellToPackedRowcol($cell1);
+        list($row1, $col1) = $cell_array1;
         $cell_array2 = $this->_cellToPackedRowcol($cell2);
-        if($this->isError($cell_array2)) {
+        if (PEAR::isError($cell_array2)) {
-            return($cell_array2);
+            return $cell_array2;
-            }
+        }
-        list($row2, $col2) = $cell_array2; //$this->_cellToPackedRowcol($cell2);
+        list($row2, $col2) = $cell_array2;
         // The ptg value depends on the class of the ptg.
         if ($class == 0) {
             $ptgArea = pack("C", $this->ptg['ptgArea']);
-        }
-        elseif ($class == 1) {
+        } elseif ($class == 1) {
             $ptgArea = pack("C", $this->ptg['ptgAreaV']);
-        }
-        elseif ($class == 2) {
+        } elseif ($class == 2) {
             $ptgArea = pack("C", $this->ptg['ptgAreaA']);
-        }
-        else {
+        } else {
             // TODO: use real error codes
-            $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
+            return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
+        }
-        return($ptgArea . $row1 . $row2 . $col1. $col2);
+        return $ptgArea . $row1 . $row2 . $col1. $col2;
+    }
     /**
     * Convert an Excel 3d range such as "Sheet1!A1:D4" or "Sheet1:Sheet2!A1:D4" to
-    * a ptgArea3dV.
+    * a ptgArea3d.
+    *
     * @access private
     * @param string $token An Excel range in the Sheet1!A1:A2 format.
+    * @return mixed The packed ptgArea3d token on success, PEAR_Error on failure.
     */
     function _convertRange3d($token)
+    {
         $class = 2; // as far as I know, this is magick.
         // Split the ref at the ! symbol
         list($ext_ref, $range) = explode('!', $token);
-        // Convert the external reference part
+        // Convert the external reference part (different for BIFF8)
+        if ($this->_BIFF_version == 0x0500) {
-        $ext_ref = $this->_packExtRef($ext_ref);
+            $ext_ref = $this->_packExtRef($ext_ref);
-        if ($this->isError($ext_ref)) {
+            if (PEAR::isError($ext_ref)) {
-            return $ext_ref;
+                return $ext_ref;
+            }
+        } elseif ($this->_BIFF_version == 0x0600) {
+             $ext_ref = $this->_getRefIndex($ext_ref);
+             if (PEAR::isError($ext_ref)) {
+                 return $ext_ref;
+             }
+        }
         // Split the range into 2 cell refs
         list($cell1, $cell2) = explode(':', $range);
         // Convert the cell references
-        if (preg_match("/^(\$)?[A-I]?[A-Z](\$)?(\d+)$/", $cell1))
+        if (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/", $cell1)) {
-        {
             $cell_array1 = $this->_cellToPackedRowcol($cell1);
             if (PEAR::isError($cell_array1)) {
                 return $cell_array1;
+            }
             list($row1, $col1) = $cell_array1;
             $cell_array2 = $this->_cellToPackedRowcol($cell2);
             if (PEAR::isError($cell_array2)) {
                 return $cell_array2;
+            }
             list($row2, $col2) = $cell_array2;
-        }
-        else { // It's a columns range (like 26:27)
+        } else { // It's a rows range (like 26:27)
              $cells_array = $this->_rangeToPackedRange($cell1.':'.$cell2);
              if (PEAR::isError($cells_array)) {
                  return $cells_array;
+             }
              list($row1, $col1, $row2, $col2) = $cells_array;
+        }
         // The ptg value depends on the class of the ptg.
         if ($class == 0) {
             $ptgArea = pack("C", $this->ptg['ptgArea3d']);
-        }
-        elseif ($class == 1) {
+        } elseif ($class == 1) {
             $ptgArea = pack("C", $this->ptg['ptgArea3dV']);
-        }
-        elseif ($class == 2) {
+        } elseif ($class == 2) {
             $ptgArea = pack("C", $this->ptg['ptgArea3dA']);
+        } else {
+            return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
+        }
-        else {
-            $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
-        }
         return $ptgArea . $ext_ref . $row1 . $row2 . $col1. $col2;
+    }
     /**
     * @return string The cell in packed() format with the corresponding ptg
     */
     function _convertRef2d($cell)
+    {
         $class = 2; // as far as I know, this is magick.
         // Convert the cell reference
         $cell_array = $this->_cellToPackedRowcol($cell);
-        if($this->isError($cell_array)) {
+        if (PEAR::isError($cell_array)) {
-            return($cell_array);
+            return $cell_array;
-            }
+        }
         list($row, $col) = $cell_array;
         // The ptg value depends on the class of the ptg.
         if ($class == 0) {
             $ptgRef = pack("C", $this->ptg['ptgRef']);
-        }
-        elseif ($class == 1) {
+        } elseif ($class == 1) {
             $ptgRef = pack("C", $this->ptg['ptgRefV']);
-        }
-        elseif ($class == 2) {
+        } elseif ($class == 2) {
             $ptgRef = pack("C", $this->ptg['ptgRefA']);
-        }
-        else {
+        } else {
             // TODO: use real error codes
-            $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
+            return $this->raiseError("Unknown class $class");
+        }
-        return($ptgRef.$row.$col);
+        return $ptgRef.$row.$col;
+    }
     /**
     * Convert an Excel 3d reference such as "Sheet1!A1" or "Sheet1:Sheet2!A1" to a
-    * ptgRef3dV.
+    * ptgRef3d.
+    *
     * @access private
     * @param string $cell An Excel cell reference
-    * @return string The cell in packed() format with the corresponding ptg
+    * @return mixed The packed ptgRef3d token on success, PEAR_Error on failure.
     */
     function _convertRef3d($cell)
+    {
         $class = 2; // as far as I know, this is magick.
         // Split the ref at the ! symbol
         list($ext_ref, $cell) = explode('!', $cell);
-        // Convert the external reference part
+        // Convert the external reference part (different for BIFF8)
+        if ($this->_BIFF_version == 0x0500) {
-        $ext_ref = $this->_packExtRef($ext_ref);
+            $ext_ref = $this->_packExtRef($ext_ref);
+            if (PEAR::isError($ext_ref)) {
+                return $ext_ref;
+            }
+        } elseif ($this->_BIFF_version == 0x0600) {
+            $ext_ref = $this->_getRefIndex($ext_ref);
-        if ($this->isError($ext_ref)) {
+            if (PEAR::isError($ext_ref)) {
-            return $ext_ref;
+                return $ext_ref;
+            }
+        }
         // Convert the cell reference part
         list($row, $col) = $this->_cellToPackedRowcol($cell);
         // The ptg value depends on the class of the ptg.
         if ($class == 0) {
             $ptgRef = pack("C", $this->ptg['ptgRef3d']);
-        }
-        elseif ($class == 1) {
+        } elseif ($class == 1) {
             $ptgRef = pack("C", $this->ptg['ptgRef3dV']);
-        }
-        elseif ($class == 2) {
+        } elseif ($class == 2) {
             $ptgRef = pack("C", $this->ptg['ptgRef3dA']);
-        }
-        else {
+        } else {
-            $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
+            return $this->raiseError("Unknown class $class", 0, PEAR_ERROR_DIE);
+        }
         return $ptgRef . $ext_ref. $row . $col;
     */
     function _packExtRef($ext_ref)
+    {
         $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading  ' if any.
         $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
         // Check if there is a sheet range eg., Sheet1:Sheet2.
-        if (preg_match("/:/", $ext_ref))
+        if (preg_match("/:/", $ext_ref)) {
-        {
             list($sheet_name1, $sheet_name2) = explode(':', $ext_ref);
             $sheet1 = $this->_getSheetIndex($sheet_name1);
             if ($sheet1 == -1) {
                 return $this->raiseError("Unknown sheet name $sheet_name1 in formula");
+            }
             $sheet2 = $this->_getSheetIndex($sheet_name2);
             if ($sheet2 == -1) {
                 return $this->raiseError("Unknown sheet name $sheet_name2 in formula");
+            }
             // Reverse max and min sheet numbers if necessary
             if ($sheet1 > $sheet2) {
                 list($sheet1, $sheet2) = array($sheet2, $sheet1);
+            }
-        }
-        else // Single sheet name only.
+        } else { // Single sheet name only.
-        {
             $sheet1 = $this->_getSheetIndex($ext_ref);
             if ($sheet1 == -1) {
                 return $this->raiseError("Unknown sheet name $ext_ref in formula");
+            }
             $sheet2 = $sheet1;
+        }
         // References are stored relative to 0xFFFF.
         $offset = -1 - $sheet1;
         return pack('vdvv', $offset, 0x00, $sheet1, $sheet2);
+    }
+    /**
+    * Look up the REF index that corresponds to an external sheet name
+    * (or range). If it doesn't exist yet add it to the workbook's references
+    * array. It assumes all sheet names given must exist.
+    *
+    * @access private
+    * @param string $ext_ref The name of the external reference
+    * @return mixed The reference index in packed() format on success,
+    *               PEAR_Error on failure
+    */
+    function _getRefIndex($ext_ref)
+    {
+        $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading  ' if any.
+        $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
+        // Check if there is a sheet range eg., Sheet1:Sheet2.
+        if (preg_match("/:/", $ext_ref)) {
+            list($sheet_name1, $sheet_name2) = explode(':', $ext_ref);
+            $sheet1 = $this->_getSheetIndex($sheet_name1);
+            if ($sheet1 == -1) {
+                return $this->raiseError("Unknown sheet name $sheet_name1 in formula");
+            }
+            $sheet2 = $this->_getSheetIndex($sheet_name2);
+            if ($sheet2 == -1) {
+                return $this->raiseError("Unknown sheet name $sheet_name2 in formula");
+            }
+            // Reverse max and min sheet numbers if necessary
+            if ($sheet1 > $sheet2) {
+                list($sheet1, $sheet2) = array($sheet2, $sheet1);
+            }
+        } else { // Single sheet name only.
+            $sheet1 = $this->_getSheetIndex($ext_ref);
+            if ($sheet1 == -1) {
+                return $this->raiseError("Unknown sheet name $ext_ref in formula");
+            }
+            $sheet2 = $sheet1;
+        }
+        // assume all references belong to this document
+        $supbook_index = 0x00;
+        $ref = pack('vvv', $supbook_index, $sheet1, $sheet2);
+        $total_references = count($this->_references);
+        $index = -1;
+        for ($i = 0; $i < $total_references; $i++) {
+            if ($ref == $this->_references[$i]) {
+                $index = $i;
+                break;
+            }
+        }
+        // if REF was not found add it to references array
+        if ($index == -1) {
+            $this->_references[$total_references] = $ref;
+            $index = $total_references;
+        }
+        return pack('v', $index);
+    }
     /**
     * Look up the index that corresponds to an external sheet name. The hash of
     * sheet names is updated by the addworksheet() method of the
     * Spreadsheet_Excel_Writer_Workbook class.
     *
     * @access private
-    * @return integer
+    * @return integer The sheet index, -1 if the sheet was not found
     */
     function _getSheetIndex($sheet_name)
+    {
-        if (!isset($this->_ext_sheets[$sheet_name])) {
-            return -1;
+        if (!isset($this->_ext_sheets[$sheet_name])) {
             return -1;
-        else {
+        } else {
             return $this->_ext_sheets[$sheet_name];
+        }
+    }
+    /**
-    /**
+    * This method is used to update the array of sheet names. It is
-    * This method is used to update the array of sheet names. It is
+    * called by the addWorksheet() method of the
+    * Spreadsheet_Excel_Writer_Workbook class.
     * called by the addWorksheet() method of the Spreadsheet_Excel_Writer_Workbook class.
     * @access public
-    * @access private
+    * @see Spreadsheet_Excel_Writer_Workbook::addWorksheet()
     * @param string  $name  The name of the worksheet being added
     * @param integer $index The index of the worksheet being added
     */
     function setExtSheet($name, $index)
+    {
         $this->_ext_sheets[$name] = $index;
+    }
     /**
-    * pack() row and column into the required 3 byte format.
+    * pack() row and column into the required 3 or 4 byte format.
+    *
     * @access private
     * @param string $cell The Excel cell reference to be packed
+    * @return array Array containing the row and column in packed() format
-    * @return array Array containing the row and column in packed() format
+    */
-    */
+    function _cellToPackedRowcol($cell)
-    function _cellToPackedRowcol($cell)
+    {
         $cell = strtoupper($cell);
+        list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
-        list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
+        if ($col >= 256) {
-        if ($col >= 256) {
+            return $this->raiseError("Column in: $cell greater than 255");
             return($this->raiseError("Column in: $cell greater than 255"));
-        }
+        // FIXME: change for BIFF8
         if ($row >= 16384) {
+            return $this->raiseError("Row in: $cell greater than 16384 ");
-            return($this->raiseError("Row in: $cell greater than 16384 "));
+        }
-        }
+        // Set the high bits to indicate if row or col are relative.
+        if ($this->_BIFF_version == 0x0500) {
+            $row    |= $col_rel << 14;
+            $row    |= $row_rel << 15;
+            $col     = pack('C', $col);
+        } elseif ($this->_BIFF_version == 0x0600) {
-        // Set the high bits to indicate if row or col are relative.
+            $col    |= $col_rel << 14;
-        $row    |= $col_rel << 14;
-        $row    |= $row_rel << 15;
+            $col    |= $row_rel << 15;
+            $col     = pack('v', $col);
         $row     = pack('v', $row);
-        $col     = pack('C', $col);
+        $row     = pack('v', $row);
-        return(array($row, $col));
+        return array($row, $col);
     }
     /**
-    * pack() row range into the required 3 byte format.
+    * pack() row range into the required 3 or 4 byte format.
-    * Just using maximun col/rows, which is probably not the correct solution
+    * Just using maximum col/rows, which is probably not the correct solution
+    *
         // Convert 1-index to zero-index
         $row1--;
         $row2--;
         // Trick poor inocent Excel
         $col1 = 0;
-        $col2 = 16383; // maximum possible value for Excel 5 (change this!!!)
+        $col2 = 16383; // FIXME: maximum possible value for Excel 5 (change this!!!)
-        //list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
+        // FIXME: this changes for BIFF8
         if (($row1 >= 16384) or ($row2 >= 16384)) {
-            return new PEAR_Error("Row in: $range greater than 16384 ");
+            return $this->raiseError("Row in: $range greater than 16384 ");
         }
+        // Set the high bits to indicate if rows are relative.
+        if ($this->_BIFF_version == 0x0500) {
-        // Set the high bits to indicate if rows are relative.
+            $row1    |= $row1_rel << 14; // FIXME: probably a bug
+            $row2    |= $row2_rel << 15;
+            $col1     = pack('C', $col1);
+            $col2     = pack('C', $col2);
+        } elseif ($this->_BIFF_version == 0x0600) {
-        $row1    |= $row1_rel << 14;
+            $col1    |= $row1_rel << 15;
+            $col2    |= $row2_rel << 15;
+            $col1     = pack('v', $col1);
-        $row2    |= $row2_rel << 15;
+            $col2     = pack('v', $col2);
+        }
         $row1     = pack('v', $row1);
-        $row2     = pack('v', $row2);
-        $col1     = pack('C', $col1);
-        $col2     = pack('C', $col2);
+        $row2     = pack('v', $row2);
         return array($row1, $col1, $row2, $col2);
+    }
         // return absolute column if there is a $ in the ref
         $col_rel = empty($match[1]) ? 1 : 0;
         $col_ref = $match[2];
         $row_rel = empty($match[3]) ? 1 : 0;
         $row     = $match[4];
         // Convert base26 column string to a number.
         $expn   = strlen($col_ref) - 1;
         $col    = 0;
-        for($i=0; $i < strlen($col_ref); $i++)
+        $col_ref_length = strlen($col_ref);
-        {
+        for ($i = 0; $i < $col_ref_length; $i++) {
             $col += (ord($col_ref{$i}) - ord('A') + 1) * pow(26, $expn);
             $expn--;
+        }
         // Convert 1-index to zero-index
         $row--;
         $col--;
-        return(array($row, $col, $row_rel, $col_rel));
+        return array($row, $col, $row_rel, $col_rel);
+    }
     /**
     * Advance to the next valid token.
+    *
     * @access private
     */
     function _advance()
+    {
         $i = $this->_current_char;
+        $formula_length = strlen($this->_formula);
         // eat up white spaces
-        if($i < strlen($this->_formula))
+        if ($i < $formula_length) {
-        {
-            while($this->_formula{$i} == " ") {
+            while ($this->_formula{$i} == " ") {
                 $i++;
+            }
-            if($i < strlen($this->_formula) - 1) {
+            if ($i < ($formula_length - 1)) {
                 $this->_lookahead = $this->_formula{$i+1};
+            }
-            $token = "";
+            $token = '';
+        }
-        while($i < strlen($this->_formula))
+        while ($i < $formula_length) {
-        {
             $token .= $this->_formula{$i};
+            if ($i < ($formula_length - 1)) {
+                $this->_lookahead = $this->_formula{$i+1};
+            } else {
-            if($this->_match($token) != '')
+                $this->_lookahead = '';
-            {
+            }
+            if ($this->_match($token) != '') {
-                if($i < strlen($this->_formula) - 1) {
+                //if ($i < strlen($this->_formula) - 1) {
-                    $this->_lookahead = $this->_formula{$i+1};
+                //    $this->_lookahead = $this->_formula{$i+1};
-                }
+                //}
                 $this->_current_char = $i + 1;
                 $this->_current_token = $token;
-                return(1);
+                return 1;
+            }
-            if ($i < strlen($this->_formula) - 2) {
+            if ($i < ($formula_length - 2)) {
                 $this->_lookahead = $this->_formula{$i+2};
-            }
-            // if we run out of characters _lookahead becomes empty
+            } else { // if we run out of characters _lookahead becomes empty
-            else {
                 $this->_lookahead = '';
+            }
             $i++;
+        }
         //die("Lexical error ".$this->_current_char);
+    }
     /**
     * Checks if it's a valid token.
+    *
     * @access private
     * @param mixed $token The token to check.
     * @return mixed       The checked token or false on failure
     */
     function _match($token)
+    {
-        switch($token)
+        switch($token) {
-        {
             case SPREADSHEET_EXCEL_WRITER_ADD:
-                return($token);
+                return $token;
                 break;
             case SPREADSHEET_EXCEL_WRITER_SUB:
-                return($token);
+                return $token;
                 break;
             case SPREADSHEET_EXCEL_WRITER_MUL:
-                return($token);
+                return $token;
                 break;
             case SPREADSHEET_EXCEL_WRITER_DIV:
-                return($token);
+                return $token;
                 break;
             case SPREADSHEET_EXCEL_WRITER_OPEN:
-                return($token);
+                return $token;
                 break;
             case SPREADSHEET_EXCEL_WRITER_CLOSE:
-                return($token);
+                return $token;
                 break;
             case SPREADSHEET_EXCEL_WRITER_COMA:
-                return($token);
+                return $token;
+                break;
+            case SPREADSHEET_EXCEL_WRITER_SEMICOLON:
+                return $token;
                 break;
             case SPREADSHEET_EXCEL_WRITER_GT:
                 if ($this->_lookahead == '=') { // it's a GE token
                     break;
+                }
-                return($token);
+                return $token;
                 break;
             case SPREADSHEET_EXCEL_WRITER_LT:
                 // it's a LE or a NE token
                 if (($this->_lookahead == '=') or ($this->_lookahead == '>')) {
                     break;
+                }
-                return($token);
+                return $token;
                 break;
             case SPREADSHEET_EXCEL_WRITER_GE:
-                return($token);
+                return $token;
                 break;
             case SPREADSHEET_EXCEL_WRITER_LE:
-                return($token);
+                return $token;
                 break;
             case SPREADSHEET_EXCEL_WRITER_EQ:
-                return($token);
+                return $token;
                 break;
             case SPREADSHEET_EXCEL_WRITER_NE:
-                return($token);
+                return $token;
+                break;
+            case SPREADSHEET_EXCEL_WRITER_CONCAT:
+                return $token;
                 break;
             default:
                 // if it's a reference
-                if (preg_match('/^\$?[A-I]?[A-Z]\$?[0-9]+$/',$token) and
+                if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$token) and
-                   !ereg("[0-9]",$this->_lookahead) and
+                   !preg_match("/[0-9]/",$this->_lookahead) and
                    ($this->_lookahead != ':') and ($this->_lookahead != '.') and
                    ($this->_lookahead != '!'))
+                {
                     return $token;
+                }
                 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
+                elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/u",$token) and
+                       !preg_match("/[0-9]/",$this->_lookahead) and
+                       ($this->_lookahead != ':') and ($this->_lookahead != '.'))
+                {
+                    return $token;
+                }
+                // If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1)
-                elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\![A-I]?[A-Z][0-9]+$/",$token) and
+                elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z][0-9]+$/u",$token) and
-                       !ereg("[0-9]",$this->_lookahead) and
+                       !preg_match("/[0-9]/",$this->_lookahead) and
                        ($this->_lookahead != ':') and ($this->_lookahead != '.'))
+                {
                     return $token;
+                }
                 // if it's a range (A1:A2)
-                elseif (preg_match("/^(\$)?[A-I]?[A-Z](\$)?[0-9]+:(\$)?[A-I]?[A-Z](\$)?[0-9]+$/",$token) and
+                elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and
-                       !ereg("[0-9]",$this->_lookahead))
+                       !preg_match("/[0-9]/",$this->_lookahead))
+                {
                     return $token;
+                }
                 // if it's a range (A1..A2)
-                elseif (preg_match("/^(\$)?[A-I]?[A-Z](\$)?[0-9]+\.\.(\$)?[A-I]?[A-Z](\$)?[0-9]+$/",$token) and
+                elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and
-                       !ereg("[0-9]",$this->_lookahead))
+                       !preg_match("/[0-9]/",$this->_lookahead))
+                {
                     return $token;
+                }
+                // If it's an external range like Sheet1!A1 or Sheet1:Sheet2!A1:B2
+                elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$token) and
+                       !preg_match("/[0-9]/",$this->_lookahead))
+                {
-                // If it's an external range
+                    return $token;
+                }
+                // If it's an external range like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2
-                elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\!([A-I]?[A-Z])?[0-9]+:([A-I]?[A-Z])?[0-9]+$/",$token) and
+                elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$token) and
-                       !ereg("[0-9]",$this->_lookahead))
+                       !preg_match("/[0-9]/",$this->_lookahead))
+                {
                     return $token;
+                }
                 // If it's a number (check that it's not a sheet name or range)
+                elseif (is_numeric($token) and
-                elseif (is_numeric($token) and !is_numeric($token.$this->_lookahead) and
+                        (!is_numeric($token.$this->_lookahead) or ($this->_lookahead == '')) and
-                        ($this->_lookahead != '!') and (($this->_lookahead != ':')))
+                        ($this->_lookahead != '!') and ($this->_lookahead != ':'))
+                {
                     return $token;
+                }
                 // If it's a string (of maximum 255 characters)
-                elseif(ereg("^\"[^\"]{0,255}\"$",$token))
+                elseif (preg_match("/^\"[^\"]{0,255}\"$/",$token))
+                {
-                    return($token);
+                    return $token;
+                }
                 // if it's a function call
-                elseif(eregi("^[A-Z0-9\xc0-\xdc\.]+$",$token) and ($this->_lookahead == "("))
+                elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$token) and ($this->_lookahead == "("))
+                {
-                    return($token);
+                    return $token;
+                }
                 return '';
+        }
+    }
     /**
     * The parsing method. It parses a formula.
+    *
     * @access public
-    * @param string $formula The formula to parse, without the initial equal sign (=).
+    * @param string $formula The formula to parse, without the initial equal
+    *                        sign (=).
+    * @return mixed true on success, PEAR_Error on failure
     */
     function parse($formula)
+    {
         $this->_current_char = 0;
         $this->_formula      = $formula;
         $this->_lookahead    = $formula{1};
         $this->_advance();
         $this->_parse_tree   = $this->_condition();
-        if ($this->isError($this->_parse_tree)) {
+        if (PEAR::isError($this->_parse_tree)) {
             return $this->_parse_tree;
+        }
+        return true;
+    }
     /**
     * It parses a condition. It assumes the following rule:
     * Cond -> Expr [(">" | "<") Expr]
+    *
     * @access private
-    * @return mixed The parsed ptg'd tree
+    * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
     */
     function _condition()
+    {
         $result = $this->_expression();
-        if($this->isError($result)) {
+        if (PEAR::isError($result)) {
             return $result;
+        }
-        if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LT)
+        if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LT) {
-        {
             $this->_advance();
             $result2 = $this->_expression();
-            if($this->isError($result2)) {
+            if (PEAR::isError($result2)) {
                 return $result2;
+            }
             $result = $this->_createTree('ptgLT', $result, $result2);
-        }
-        elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GT)
+        } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GT) {
-        {
             $this->_advance();
             $result2 = $this->_expression();
-            if($this->isError($result2)) {
+            if (PEAR::isError($result2)) {
                 return $result2;
+            }
             $result = $this->_createTree('ptgGT', $result, $result2);
-        }
-        elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LE)
+        } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LE) {
-        {
             $this->_advance();
             $result2 = $this->_expression();
-            if($this->isError($result2)) {
+            if (PEAR::isError($result2)) {
                 return $result2;
+            }
             $result = $this->_createTree('ptgLE', $result, $result2);
-        }
-        elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GE)
+        } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GE) {
-        {
             $this->_advance();
             $result2 = $this->_expression();
-            if($this->isError($result2)) {
+            if (PEAR::isError($result2)) {
                 return $result2;
+            }
             $result = $this->_createTree('ptgGE', $result, $result2);
-        }
-        elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_EQ)
+        } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_EQ) {
-        {
             $this->_advance();
             $result2 = $this->_expression();
-            if($this->isError($result2)) {
+            if (PEAR::isError($result2)) {
                 return $result2;
+            }
             $result = $this->_createTree('ptgEQ', $result, $result2);
-        }
-        elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_NE)
+        } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_NE) {
-        {
             $this->_advance();
             $result2 = $this->_expression();
-            if($this->isError($result2)) {
+            if (PEAR::isError($result2)) {
                 return $result2;
+            }
             $result = $this->_createTree('ptgNE', $result, $result2);
+        } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_CONCAT) {
+            $this->_advance();
+            $result2 = $this->_expression();
+            if (PEAR::isError($result2)) {
+                return $result2;
+        }
+            $result = $this->_createTree('ptgConcat', $result, $result2);
+        }
         return $result;
+    }
     /**
     * It parses a expression. It assumes the following rule:
     * Expr -> Term [("+" | "-") Term]
+    *      -> "string"
+    *      -> "-" Term
+    *
     * @access private
-    * @return mixed The parsed ptg'd tree
+    * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
     */
     function _expression()
+    {
         // If it's a string return a string node
-        if (ereg("^\"[^\"]{0,255}\"$", $this->_current_token))
+        if (preg_match("/^\"[^\"]{0,255}\"$/", $this->_current_token)) {
-        {
             $result = $this->_createTree($this->_current_token, '', '');
             $this->_advance();
-            return($result);
+            return $result;
+        } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB) {
+            // catch "-" Term
+            $this->_advance();
+            $result2 = $this->_expression();
+            $result = $this->_createTree('ptgUminus', $result2, '');
+            return $result;
+        }
         $result = $this->_term();
-        if($this->isError($result)) {
+        if (PEAR::isError($result)) {
-            return($result);
+            return $result;
+        }
         while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) or
-               ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB))
+               ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB)) {
-        {
+        /**/
-            if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD)
+            if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) {
-            {
                 $this->_advance();
                 $result2 = $this->_term();
-                if($this->isError($result2)) {
+                if (PEAR::isError($result2)) {
-                    return($result2);
+                    return $result2;
+                }
                 $result = $this->_createTree('ptgAdd', $result, $result2);
-            }
-            else
+            } else {
-            {
                 $this->_advance();
                 $result2 = $this->_term();
-                if($this->isError($result2)) {
+                if (PEAR::isError($result2)) {
-                    return($result2);
+                    return $result2;
+                }
                 $result = $this->_createTree('ptgSub', $result, $result2);
+            }
+        }
-        return($result);
+        return $result;
+    }
     /**
     * This function just introduces a ptgParen element in the tree, so that Excel
     * doesn't get confused when working with a parenthesized formula afterwards.
+    *
     * @access private
     * @see _fact()
-    * @return mixed The parsed ptg'd tree
+    * @return array The parsed ptg'd tree
     */
     function _parenthesizedExpression()
+    {
         $result = $this->_createTree('ptgParen', $this->_expression(), '');
-        return($result);
+        return $result;
+    }
     /**
     * It parses a term. It assumes the following rule:
     * Term -> Fact [("*" | "/") Fact]
+    *
     * @access private
-    * @return mixed The parsed ptg'd tree
+    * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
     */
     function _term()
+    {
         $result = $this->_fact();
-        if($this->isError($result)) {
+        if (PEAR::isError($result)) {
-            return($result);
+            return $result;
+        }
         while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) or
-               ($this->_current_token == SPREADSHEET_EXCEL_WRITER_DIV))
+               ($this->_current_token == SPREADSHEET_EXCEL_WRITER_DIV)) {
-        {
+        /**/
-            if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL)
+            if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) {
-            {
                 $this->_advance();
                 $result2 = $this->_fact();
-                if($this->isError($result2)) {
+                if (PEAR::isError($result2)) {
-                    return($result2);
+                    return $result2;
+                }
                 $result = $this->_createTree('ptgMul', $result, $result2);
-            }
-            else
+            } else {
-            {
                 $this->_advance();
                 $result2 = $this->_fact();
-                if($this->isError($result2)) {
+                if (PEAR::isError($result2)) {
-                    return($result2);
+                    return $result2;
+                }
                 $result = $this->_createTree('ptgDiv', $result, $result2);
+            }
+        }
-        return($result);
+        return $result;
+    }
     /**
     * It parses a factor. It assumes the following rule:
     * Fact -> ( Expr )
     *       | CellRef
     *       | CellRange
     *       | Number
     *       | Function
+    *
     * @access private
-    * @return mixed The parsed ptg'd tree
+    * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
     */
     function _fact()
+    {
-        if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_OPEN)
+        if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_OPEN) {
-        {
             $this->_advance();         // eat the "("
             $result = $this->_parenthesizedExpression();
             if ($this->_current_token != SPREADSHEET_EXCEL_WRITER_CLOSE) {
-                return($this->raiseError("')' token expected."));
+                return $this->raiseError("')' token expected.");
+            }
             $this->_advance();         // eat the ")"
             return $result;
+        }
         // if it's a reference
-        if (preg_match('/^\$?[A-I]?[A-Z]\$?[0-9]+$/',$this->_current_token))
+        if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$this->_current_token))
+        {
             $result = $this->_createTree($this->_current_token, '', '');
             $this->_advance();
             return $result;
+        }
         // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
+        elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/u",$this->_current_token))
+        {
+            $result = $this->_createTree($this->_current_token, '', '');
+            $this->_advance();
+            return $result;
+        }
+        // If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1)
-        elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\![A-I]?[A-Z][0-9]+$/",$this->_current_token))
+        elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z][0-9]+$/u",$this->_current_token))
+        {
             $result = $this->_createTree($this->_current_token, '', '');
             $this->_advance();
             return $result;
+        }
         // if it's a range
-        elseif (preg_match("/^(\$)?[A-I]?[A-Z](\$)?[0-9]+:(\$)?[A-I]?[A-Z](\$)?[0-9]+$/",$this->_current_token) or
+        elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token) or
-                preg_match("/^(\$)?[A-I]?[A-Z](\$)?[0-9]+\.\.(\$)?[A-I]?[A-Z](\$)?[0-9]+$/",$this->_current_token))
+                preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token))
+        {
+            $result = $this->_current_token;
+            $this->_advance();
+            return $result;
+        }
+        // If it's an external range (Sheet1!A1 or Sheet1!A1:B2)
+        elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$this->_current_token))
+        {
             $result = $this->_current_token;
             $this->_advance();
             return $result;
+        }
-        // If it's an external range (Sheet1!A1:B2)
+        // If it's an external range ('Sheet1'!A1 or 'Sheet1'!A1:B2)
-        elseif (preg_match("/^[A-Za-z0-9_]+(\:[A-Za-z0-9_]+)?\!([A-I]?[A-Z])?[0-9]+:([A-I]?[A-Z])?[0-9]+$/",$this->_current_token))
+        elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$this->_current_token))
+        {
             $result = $this->_current_token;
             $this->_advance();
-            return($result);
+            return $result;
+        }
         elseif (is_numeric($this->_current_token))
+        {
             $result = $this->_createTree($this->_current_token, '', '');
             $this->_advance();
-            return($result);
+            return $result;
+        }
         // if it's a function call
-        elseif (eregi("^[A-Z0-9\xc0-\xdc\.]+$",$this->_current_token))
+        elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$this->_current_token))
+        {
             $result = $this->_func();
-            return($result);
+            return $result;
+        }
-        return($this->raiseError("Sintactic error: ".$this->_current_token.", lookahead: ".
+        return $this->raiseError("Syntax error: ".$this->_current_token.
+                                 ", lookahead: ".$this->_lookahead.
-                                 $this->_lookahead.", current char: ".$this->_current_char));
+                                 ", current char: ".$this->_current_char);
+    }
     /**
     * It parses a function call. It assumes the following rule:
     * Func -> ( Expr [,Expr]* )
+    *
     * @access private
+    * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
     */
     function _func()
+    {
         $num_args = 0; // number of arguments received
-        $function = $this->_current_token;
+        $function = strtoupper($this->_current_token);
+        $result   = ''; // initialize result
         $this->_advance();
         $this->_advance();         // eat the "("
-        while($this->_current_token != ')')
+        while ($this->_current_token != ')') {
-        {
+        /**/
-            if($num_args > 0)
+            if ($num_args > 0) {
-            {
-                if($this->_current_token == SPREADSHEET_EXCEL_WRITER_COMA) {
+                if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_COMA or
-                    $this->_advance();  // eat the ","
+                    $this->_current_token == SPREADSHEET_EXCEL_WRITER_SEMICOLON)
-                }
+                {
+                    $this->_advance();  // eat the "," or ";"
-                else {
+                } else {
-                    return new PEAR_Error("Sintactic error: coma expected in ".
+                    return $this->raiseError("Syntax error: comma expected in ".
-                                          "function $function, {$num_args}� arg");
+                                      "function $function, arg #{$num_args}");
+                }
                 $result2 = $this->_condition();
-                if($this->isError($result2)) {
+                if (PEAR::isError($result2)) {
-                    return($result2);
+                    return $result2;
+                }
                 $result = $this->_createTree('arg', $result, $result2);
-            }
-            else // first argument
+            } else { // first argument
-            {
                 $result2 = $this->_condition();
-                if($this->isError($result2)) {
+                if (PEAR::isError($result2)) {
-                    return($result2);
+                    return $result2;
+                }
                 $result = $this->_createTree('arg', '', $result2);
+            }
             $num_args++;
+        }
+        if (!isset($this->_functions[$function])) {
+            return $this->raiseError("Function $function() doesn't exist");
+        }
         $args = $this->_functions[$function][1];
         // If fixed number of args eg. TIME($i,$j,$k). Check that the number of args is valid.
-        if (($args >= 0) and ($args != $num_args))
+        if (($args >= 0) and ($args != $num_args)) {
-        {
-            return($this->raiseError("Incorrect number of arguments in function $function() "));
+            return $this->raiseError("Incorrect number of arguments in function $function() ");
+        }
-        $result = $this->_createTree($function, $result, '');
+        $result = $this->_createTree($function, $result, $num_args);
         $this->_advance();         // eat the ")"
-        return($result);
+        return $result;
+    }
     /**
     * Creates a tree. In fact an array which may have one or two arrays (sub-trees)
     * as elements.
+    *
     * @access private
     * @param mixed $value The value of this node.
     * @param mixed $left  The left array (sub-tree) or a final node.
     * @param mixed $right The right array (sub-tree) or a final node.
+    * @return array A tree
     */
     function _createTree($value, $left, $right)
+    {
-        return(array('value' => $value, 'left' => $left, 'right' => $right));
+        return array('value' => $value, 'left' => $left, 'right' => $right);
+    }
     /**
     * Builds a string containing the tree in reverse polish notation (What you
     * would use in a HP calculator stack).
     * The following tree:
     *
     *    +
     *   / \
     *  2   3
+    *
     * produces: "23+"
     * @return string The tree in reverse polish notation
     */
     function toReversePolish($tree = array())
+    {
         $polish = ""; // the string we are going to return
-        if (empty($tree)) // If it's the first call use _parse_tree
+        if (empty($tree)) { // If it's the first call use _parse_tree
-        {
             $tree = $this->_parse_tree;
+        }
-        if (is_array($tree['left']))
+        if (is_array($tree['left'])) {
-        {
             $converted_tree = $this->toReversePolish($tree['left']);
-            if($this->isError($converted_tree)) {
+            if (PEAR::isError($converted_tree)) {
-                return($converted_tree);
+                return $converted_tree;
-                }
+            }
             $polish .= $converted_tree;
-        }
-        elseif($tree['left'] != '') // It's a final node
+        } elseif ($tree['left'] != '') { // It's a final node
-        {
             $converted_tree = $this->_convert($tree['left']);
-            if($this->isError($converted_tree)) {
+            if (PEAR::isError($converted_tree)) {
-                return($converted_tree);
+                return $converted_tree;
-                }
+            }
             $polish .= $converted_tree;
+        }
-        if (is_array($tree['right']))
+        if (is_array($tree['right'])) {
-        {
             $converted_tree = $this->toReversePolish($tree['right']);
-            if($this->isError($converted_tree)) {
+            if (PEAR::isError($converted_tree)) {
-                return($converted_tree);
+                return $converted_tree;
-                }
+            }
             $polish .= $converted_tree;
-        }
-        elseif($tree['right'] != '') // It's a final node
+        } elseif ($tree['right'] != '') { // It's a final node
-        {
             $converted_tree = $this->_convert($tree['right']);
-            if($this->isError($converted_tree)) {
+            if (PEAR::isError($converted_tree)) {
-                return($converted_tree);
+                return $converted_tree;
-                }
+            }
             $polish .= $converted_tree;
+        }
+        // if it's a function convert it here (so we can set it's arguments)
+        if (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/",$tree['value']) and
+            !preg_match('/^([A-Ia-i]?[A-Za-z])(\d+)$/',$tree['value']) and
+            !preg_match("/^[A-Ia-i]?[A-Za-z](\d+)\.\.[A-Ia-i]?[A-Za-z](\d+)$/",$tree['value']) and
+            !is_numeric($tree['value']) and
+            !isset($this->ptg[$tree['value']]))
+        {
+            // left subtree for a function is always an array.
+            if ($tree['left'] != '') {
+                $left_tree = $this->toReversePolish($tree['left']);
+            } else {
+                $left_tree = '';
+            }
+            if (PEAR::isError($left_tree)) {
+                return $left_tree;
+            }
+            // add it's left subtree and return.
+            return $left_tree.$this->_convertFunction($tree['value'], $tree['right']);
+        } else {
-        $converted_tree = $this->_convert($tree['value']);
+            $converted_tree = $this->_convert($tree['value']);
-        if($this->isError($converted_tree)) {
+            if (PEAR::isError($converted_tree)) {
-            return($converted_tree);
+                return $converted_tree;
+            }
+        }
         $polish .= $converted_tree;
-        return($polish);
+        return $polish;
+    }
+}
 ?>

Subversion Repositories eFlore/Applications.cel

(root)/trunk/jrest/lib/Spreadsheet/Excel/Writer/Parser.php – Rev 996 → 1604