functions + att (optimized functions) looking good. Just filling out big ol function table.

Soon will support most excel functions with areas.  need arrays next.


git-svn-id: https://svn.apache.org/repos/asf/jakarta/poi/trunk@352566 13f79535-47bb-0310-9956-ffa450edef68

4 files changed, 133 insertions, 14 deletions

diff --git a/src/java/org/apache/poi/hssf/record/FormulaRecord.java b/src/java/org/apache/poi/hssf/record/FormulaRecord.java
index a166e21e0b..4a63270eed 100644
--- a/src/java/org/apache/poi/hssf/record/FormulaRecord.java
+++ b/src/java/org/apache/poi/hssf/record/FormulaRecord.java
@@ -536,7 +536,13 @@ public class FormulaRecord
             for (int k = 0; k < field_8_parsed_expr.size(); k++ ) {
 /*                buffer.append("formula ").append(k).append(" ")
                .append(((Ptg)field_8_parsed_expr.get(k)).toFormulaString());*/
-                ((Ptg)field_8_parsed_expr.get(k)).toDebugString();
+                buffer.append("Formula ")
+                .append(k)
+                .append("=")
+                .append(((Ptg)field_8_parsed_expr.get(k)).toString())
+                .append("\n")
+                .append(((Ptg)field_8_parsed_expr.get(k)).toDebugString())
+                .append("\n");                
             }
             
             
diff --git a/src/java/org/apache/poi/hssf/record/formula/FormulaParser.java b/src/java/org/apache/poi/hssf/record/formula/FormulaParser.java
index a0f52a50a8..90f19e2bc8 100644
--- a/src/java/org/apache/poi/hssf/record/formula/FormulaParser.java
+++ b/src/java/org/apache/poi/hssf/record/formula/FormulaParser.java
@@ -269,6 +269,9 @@ public class FormulaParser {
             ptg.setData((short)1); //sums don't care but this is what excel does.
             ptg.setSum(true);
             retval = ptg;
+        } else {
+            FunctionPtg ptg = new FunctionPtg(name,numArgs);
+            retval = ptg;
         }
         
         return retval;
diff --git a/src/java/org/apache/poi/hssf/record/formula/FunctionPtg.java b/src/java/org/apache/poi/hssf/record/formula/FunctionPtg.java
index 02324bc25b..6157e495e6 100644
--- a/src/java/org/apache/poi/hssf/record/formula/FunctionPtg.java
+++ b/src/java/org/apache/poi/hssf/record/formula/FunctionPtg.java
@@ -1,6 +1,8 @@
 package org.apache.poi.hssf.record.formula;
 
 import java.util.List;
+import java.util.ArrayList;
+import org.apache.poi.util.LittleEndian;
 /**
  * This class provides functions with variable arguments.  
  * @author  Avik Sengupta
@@ -9,10 +11,10 @@ import java.util.List;
  */
 public class FunctionPtg extends OperationPtg {
     public final static short sid  = 0x22;
-    private final static int  SIZE = 3;    
+    private final static int  SIZE = 4;    
     
     private byte field_1_num_args;
-    private byte field_2_fnc_index;
+    private short field_2_fnc_index;
     
     //private String name;
     //private int numOperands;
@@ -23,7 +25,7 @@ public class FunctionPtg extends OperationPtg {
     public FunctionPtg(byte[] data, int offset) {
         offset++;
         field_1_num_args = data[ offset + 0 ];
-        field_2_fnc_index  = data[offset + 1 ];
+        field_2_fnc_index  = LittleEndian.getShort(data,offset + 1 );
         
     }
     
@@ -33,6 +35,17 @@ public class FunctionPtg extends OperationPtg {
         field_2_fnc_index = lookupIndex(pName);
         
     }
+    
+    public String toString() {
+        StringBuffer buffer = new StringBuffer();
+        buffer
+        .append("<FunctionPtg>").append("\n")
+        .append("   field_1_num_args=").append(field_1_num_args).append("\n")
+        .append("      name         =").append(lookupName(field_2_fnc_index)).append("\n")
+        .append("   field_2_fnc_index=").append(field_2_fnc_index).append("\n")
+        .append("</FunctionPtg>");
+        return buffer.toString();
+    }
    
     public int getType() {
         return -1;
@@ -42,7 +55,7 @@ public class FunctionPtg extends OperationPtg {
         return field_1_num_args;
     }
     
-    public int getFunctionIndex() {
+    public short getFunctionIndex() {
         return field_2_fnc_index;
     }
     
@@ -82,20 +95,116 @@ public class FunctionPtg extends OperationPtg {
     public void writeBytes(byte[] array, int offset) {
         array[offset+0]=sid;
         array[offset+1]=field_1_num_args;
-        array[offset+2]=field_2_fnc_index;
+        LittleEndian.putShort(array,offset+2,field_2_fnc_index);
     }
     
     public int getSize() {
         return SIZE;
     }
     
-    private String lookupName(byte index) {
-        return "SUM"; //for now always return "SUM"
+    private String lookupName(short index) {
+        String retval = null;
+        switch (index) {
+            case 5: 
+                retval="AVERAGE";
+            break;
+        }
+        return retval; //for now always return "SUM"
     }
     
-    private byte lookupIndex(String name) {
-        return 4; //for now just return SUM everytime...
+    private short lookupIndex(String name) {
+        short retval=0;
+        if (name.equals("AVERAGE")) {
+            retval=(short)5;
+        }
+        return retval; //for now just return SUM everytime...
     }
     
-  
+    /**
+     * Produces the function table hashmap
+     */
+    private static List produceHash() {
+        List list = new ArrayList(349);
+        list.add(0,"COUNT");
+        list.add(2,"ISNA");
+        list.add(3,"ISERROR");
+        list.add(4,"SUM");
+        list.add(5,"AVERAGE");
+        list.add(6,"MIN");
+        list.add(7,"MAX");
+        list.add(8,"ROW");
+        list.add(9,"COLUMN");
+        list.add(10,"NA");
+        list.add(11,"NPV");
+        list.add(12,"STDEV");
+        list.add(13,"DOLLAR");
+        list.add(14,"FIXED");
+        list.add(15,"SIN");
+        list.add(16,"COS");
+        list.add(17,"TAN");
+        list.add(18,"ATAN");
+        list.add(19,"PI");
+        list.add(20,"SQRT");
+        list.add(21,"EXP");
+        list.add(22,"LN");
+        list.add(23,"LOG10");
+        list.add(24,"ABS");
+        list.add(25,"INT");
+        list.add(26,"SIGN");
+        list.add(27,"ROUND");
+        list.add(28,"LOOKUP");
+        list.add(29,"INDEX");
+        list.add(30,"REPT");
+        list.add(31,"MID");
+        list.add(32,"LEN");
+        list.add(33,"VALUE");
+        list.add(34,"TRUE");
+        list.add(35,"FALSE");
+        list.add(36,"AND");
+        list.add(37,"OR");
+        list.add(38,"NOT");
+        list.add(39,"MOD");
+        list.add(40,"DCOUNT");
+        list.add(41,"DSUM");
+        list.add(42,"DAVERAGE");
+        list.add(43,"DMIN");
+        list.add(44,"DMAX");
+        list.add(45,"DSTDEV");
+        list.add(46,"VAR");
+        list.add(47,"DVAR");
+        list.add(48,"TEXT");
+        list.add(49,"LINEST");
+        list.add(50,"TREND");
+        list.add(51,"LOGEST");
+        list.add(52,"GROWTH");
+        list.add(53,"GOTO");
+        list.add(54,"HALT");
+        list.add(56,"PV");
+        list.add(57,"FV");
+        list.add(58,"NPER");
+        list.add(59,"PMT");
+        list.add(60,"RATE");
+        list.add(61,"MIRR");
+        list.add(62,"IRR");
+        list.add(63,"RAND");
+        list.add(64,"MATCH");
+        list.add(65,"DATE");
+        list.add(66,"TIME");
+        list.add(67,"DAY");
+        list.add(68,"MONTH");
+        list.add(69,"YEAR");
+        list.add(70,"WEEKDAY");
+        list.add(71,"HOUR");
+        list.add(72,"MINUTE");
+        list.add(73,"SECOND");
+        list.add(74,"NOW");
+        list.add(75,"AREAS");
+        list.add(76,"ROWS");
+        list.add(77,"COLUMNS");
+        list.add(78,"OFFSET");
+        list.add(79,"ABSREF");
+        list.add(80,"RELREF");
+        list.add(81,"ARGUMENT");
+        return list;
+    }
 }
diff --git a/src/java/org/apache/poi/hssf/record/formula/Ptg.java b/src/java/org/apache/poi/hssf/record/formula/Ptg.java
index da1dcc9906..330b228a22 100644
--- a/src/java/org/apache/poi/hssf/record/formula/Ptg.java
+++ b/src/java/org/apache/poi/hssf/record/formula/Ptg.java
@@ -235,14 +235,14 @@ public abstract class Ptg
                 retval = new FunctionPtg(data, offset);
                 break;
                 
-/*            case valueFunc :
+            case valueFunc :
                 retval = new FunctionPtg(data, offset);
                 break;
                 
             case arrayFunc :
                 retval = new FunctionPtg(data, offset);
                 break;
-  */              
+                
                 
              case NumberPtg.sid :
                 retval = new NumberPtg(data, offset);
@@ -299,7 +299,7 @@ public abstract class Ptg
     /**
      * dump a debug representation (hexdump) to a strnig
      */
-    public void toDebugString() {
+    public String toDebugString() {
         byte[] ba = new byte[getSize()];
         String retval=null;
         writeBytes(ba,0);        
@@ -308,6 +308,7 @@ public abstract class Ptg
         } catch (Exception e) {
             e.printStackTrace();
         }
+        return retval;
     }