first cut support for sheet references. Writing works fine, reading also works except for bug 9632 .. which means that the formula string retrieved is incorrect. will fix soon

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

7 files changed, 104 insertions, 28 deletions

diff --git a/src/java/org/apache/poi/hssf/model/Sheet.java b/src/java/org/apache/poi/hssf/model/Sheet.java
index 39073c4ad9..7ceaf728a1 100644
--- a/src/java/org/apache/poi/hssf/model/Sheet.java
+++ b/src/java/org/apache/poi/hssf/model/Sheet.java
@@ -743,7 +743,7 @@ public class Sheet
         rec.setOptions(( short ) 2);
         rec.setValue(0);
         rec.setXFIndex(( short ) 0x0f);
-        FormulaParser fp = new FormulaParser(formula);
+        FormulaParser fp = new FormulaParser(formula,null); //fix - do we need this method?
         fp.parse();
         Ptg[] ptg  = fp.getRPNPtg();
         int   size = 0;
diff --git a/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java b/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java
index 14034edd87..bf757241ff 100644
--- a/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java
+++ b/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java
@@ -57,6 +57,7 @@ package org.apache.poi.hssf.record.formula;
 
 import org.apache.poi.util.LittleEndian;
 import org.apache.poi.hssf.util.RangeAddress;
+import org.apache.poi.hssf.util.AreaReference;
 
 /**
  * Title:        Area 3D Ptg - 3D referecnce (Sheet + Area)<P>
@@ -77,11 +78,22 @@ public class Area3DPtg extends Ptg
     private short             field_5_last_column;
 
     /** Creates new AreaPtg */
-
-    public Area3DPtg()
-    {
+    public Area3DPtg() {}
+   
+   protected Area3DPtg(String arearef, short externIdx) {
+        AreaReference ar = new AreaReference(arearef);
+        
+        setFirstRow((short)ar.getCells()[0].getRow());
+        setFirstColumn((short)ar.getCells()[0].getCol());
+        setLastRow((short)ar.getCells()[1].getRow());
+        setLastColumn((short)ar.getCells()[1].getCol());
+        //setFirstColRelative(!ar.getCells()[0].isColAbsolute());
+        //setLastColRelative(!ar.getCells()[1].isColAbsolute());
+        //setFirstRowRelative(!ar.getCells()[0].isRowAbsolute());
+        //setLastRowRelative(!ar.getCells()[1].isRowAbsolute());
+        setExternSheetIndex(externIdx);
+        
     }
-
     public Area3DPtg(byte[] data, int offset)
     {
         offset++;
@@ -113,7 +125,7 @@ public class Area3DPtg extends Ptg
 
     public void writeBytes(byte [] array, int offset)
     {
-        array[ 0 + offset ] = sid;
+        array[ 0 + offset ] = (byte) (sid + ptgClass);
         LittleEndian.putShort(array, 1 + offset , getExternSheetIndex());
         LittleEndian.putShort(array, 3 + offset , getFirstRow());
         LittleEndian.putShort(array, 5 + offset , getLastRow());
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 a07199f19d..49aae2d32b 100644
--- a/src/java/org/apache/poi/hssf/record/formula/FormulaParser.java
+++ b/src/java/org/apache/poi/hssf/record/formula/FormulaParser.java
@@ -63,6 +63,8 @@ import java.util.Stack;
 import java.io.FileOutputStream;
 import java.io.File;
 
+import org.apache.poi.hssf.model.Workbook;
+
 
 /**
  * This class parses a formula string into a List of tokens in RPN order.
@@ -97,6 +99,8 @@ public class FormulaParser {
     private static char CR = '\n';
     
    private char Look;              // Lookahead Character 
+   
+   private Workbook book;
     
     
     /** create the parser with the string that is to be parsed
@@ -106,9 +110,10 @@ public class FormulaParser {
      *  The parse and getPRNPtg are internally synchronized for safety, thus
      *  while it is safe to use in a multithreaded environment, you will get long lock waits.  
      */
-    public FormulaParser(String formula){
+    public FormulaParser(String formula, Workbook book){
         formulaString = formula;
         pointer=0;
+        this.book = book;
     }
     
 
@@ -130,7 +135,7 @@ public class FormulaParser {
     private void Abort(String s) {
         Error(s);
         //System.exit(1);  //throw exception??
-        throw new RuntimeException("Cannot Parse, sorry");
+        throw new RuntimeException("Cannot Parse, sorry : "+s);
     }
     
     
@@ -235,34 +240,50 @@ public class FormulaParser {
         System.out.println();;
     }
     
-    /** Parse and Translate a Identifier */
+    /** Parse and Translate a String Identifier */
     private void Ident() {
         String name;
         name = GetName();
         if (Look == '('){
-            //This is a function 
-            Match('(');
-            int numArgs = Arguments(); 
-            Match(')');
-            //this is the end of the function
-            tokens.add(function(name,(byte)numArgs));
+            //This is a function
+            function(name);
         } else if (Look == ':') { // this is a AreaReference
             String first = name;
             Match(':');
             String second = GetName();
             tokens.add(new AreaPtg(first+":"+second));
+        } else if (Look == '!') {
+            Match('!');
+            String sheetName = name;
+            String first = GetName();
+            short externIdx = book.checkExternSheet(book.getSheetIndex(sheetName));
+            if (Look == ':') {
+                Match(':');
+                String second=GetName();
+                
+                tokens.add(new Area3DPtg(first+":"+second,externIdx));
+            } else {
+                tokens.add(new Ref3DPtg(first,externIdx));
+            }
         } else {
             //this can be either a cell ref or a named range !!
             boolean cellRef = true ; //we should probably do it with reg exp??
             if (cellRef) {
-                tokens.add(new ReferencePtg(name)); 
+                tokens.add(new ReferencePtg(name));
             }else {
                 //handle after named range is integrated!!
             }
         }
     }
     
-    private Ptg function(String name,byte numArgs) {
+    private void function(String name) {
+        Match('(');
+        int numArgs = Arguments();
+        Match(')');
+        tokens.add(getFunction(name,(byte)numArgs));
+    }
+    
+    private Ptg getFunction(String name,byte numArgs) {
         Ptg retval = null;
         retval = new FuncVarPtg(name,numArgs);
        /** if (numArgs == 1 && name.equals("SUM")) {
diff --git a/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java b/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java
index 9c91baeeaf..579e40ae74 100644
--- a/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java
+++ b/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java
@@ -58,7 +58,7 @@ package org.apache.poi.hssf.record.formula;
 
 import org.apache.poi.util.LittleEndian;
 import org.apache.poi.hssf.util.RangeAddress;
-
+import org.apache.poi.hssf.util.CellReference;
 /**
  * Title:        Reference 3D Ptg <P>
  * Description:  Defined a cell in extern sheet. <P>
@@ -75,9 +75,7 @@ public class Ref3DPtg extends Ptg {
     private short             field_3_column;
 
     /** Creates new AreaPtg */
-
-    public Ref3DPtg() {
-    }
+    public Ref3DPtg() {}
 
     public Ref3DPtg(byte[] data, int offset) {
         offset++;
@@ -85,6 +83,15 @@ public class Ref3DPtg extends Ptg {
         field_2_row          = LittleEndian.getShort(data, 2 + offset);
         field_3_column        = LittleEndian.getShort(data, 4 + offset);
     }
+    
+    protected Ref3DPtg(String cellref, short externIdx ) {
+        CellReference c= new CellReference(cellref);
+        setRow((short) c.getRow());
+        setColumn((short) c.getCol());
+        //setColRelative(!c.isColAbsolute());
+        //setRowRelative(!c.isRowAbsolute());   
+        setExternSheetIndex(externIdx);
+    }
 
     public String toString() {
         StringBuffer buffer = new StringBuffer();
@@ -100,7 +107,7 @@ public class Ref3DPtg extends Ptg {
     }
 
     public void writeBytes(byte [] array, int offset) {
-        array[ 0 + offset ] = sid;
+        array[ 0 + offset ] = (byte) (sid + ptgClass);
         LittleEndian.putShort(array, 1 + offset , getExternSheetIndex());
         LittleEndian.putShort(array, 3 + offset , getRow());
         LittleEndian.putShort(array, 5 + offset , getColumnRaw());
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
index 41dfda8435..110954034f 100644
--- a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
@@ -701,7 +701,7 @@ public class HSSFCell
             rec.setOptions(( short ) 2);
             rec.setValue(0);
             rec.setXFIndex(( short ) 0x0f);
-            FormulaParser fp = new FormulaParser(formula+";");
+            FormulaParser fp = new FormulaParser(formula+";",book);
             fp.parse();
             Ptg[] ptg  = fp.getRPNPtg();
             int   size = 0;
diff --git a/src/testcases/org/apache/poi/hssf/record/formula/TestFormulaParser.java b/src/testcases/org/apache/poi/hssf/record/formula/TestFormulaParser.java
index 84dd8376c8..1af25b9cba 100644
--- a/src/testcases/org/apache/poi/hssf/record/formula/TestFormulaParser.java
+++ b/src/testcases/org/apache/poi/hssf/record/formula/TestFormulaParser.java
@@ -20,13 +20,13 @@ public class TestFormulaParser extends TestCase {
     }
     
     public void testSimpleFormula() {
-        FormulaParser fp = new FormulaParser("2+2;");
+        FormulaParser fp = new FormulaParser("2+2;",null);
         fp.parse();
         Ptg[] ptgs = fp.getRPNPtg();
         assertTrue("three tokens expected, got "+ptgs.length,ptgs.length == 3);
     }
     public void testFormulaWithSpace1() {
-        FormulaParser fp = new FormulaParser(" 2 + 2 ;");
+        FormulaParser fp = new FormulaParser(" 2 + 2 ;",null);
         fp.parse();
         Ptg[] ptgs = fp.getRPNPtg();
         assertTrue("three tokens expected, got "+ptgs.length,ptgs.length == 3);
@@ -39,7 +39,7 @@ public class TestFormulaParser extends TestCase {
     public void testFormulaWithSpace2() {
         Ptg[] ptgs;
         FormulaParser fp;
-        fp = new FormulaParser("2+ sum( 3 , 4) ;");
+        fp = new FormulaParser("2+ sum( 3 , 4) ;",null);
         fp.parse();
         ptgs = fp.getRPNPtg();
         assertTrue("five tokens expected, got "+ptgs.length,ptgs.length == 5);
@@ -48,7 +48,7 @@ public class TestFormulaParser extends TestCase {
      public void testFormulaWithSpaceNRef() {
         Ptg[] ptgs;
         FormulaParser fp;
-        fp = new FormulaParser("sum( A2:A3 );");
+        fp = new FormulaParser("sum( A2:A3 );",null);
         fp.parse();
         ptgs = fp.getRPNPtg();
         assertTrue("two tokens expected, got "+ptgs.length,ptgs.length == 2);
@@ -57,7 +57,7 @@ public class TestFormulaParser extends TestCase {
     public void testFormulaWithString() {
         Ptg[] ptgs;
         FormulaParser fp;
-        fp = new FormulaParser("\"hello\" & \"world\" ;");
+        fp = new FormulaParser("\"hello\" & \"world\" ;",null);
         fp.parse();
         ptgs = fp.getRPNPtg();
         assertTrue("three token expected, got " + ptgs.length, ptgs.length == 3);
diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java b/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java
index e3e17fa7e8..98785eb5b8 100644
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java
@@ -751,6 +751,42 @@ extends TestCase {
             in.close();
     }
     
+    public void testSheetFunctions()
+        throws java.io.IOException
+    {
+        String filename = System.getProperty("HSSF.testdata.path");
+
+    File file = File.createTempFile("testSheetFormula",".xls");
+            FileOutputStream out    = new FileOutputStream(file);
+            HSSFWorkbook     wb     = new HSSFWorkbook();
+            HSSFSheet        s      = wb.createSheet("A");
+            HSSFRow          r      = null;
+            HSSFCell         c      = null;
+            r = s.createRow((short)0);
+            c = r.createCell((short)0);c.setCellValue(1);
+            c = r.createCell((short)1);c.setCellValue(2);
+            
+            s      = wb.createSheet("B");
+            r = s.createRow((short)0);
+            c=r.createCell((short)0); c.setCellFormula("AVERAGE(A!A1:B1)");
+            c=r.createCell((short)1); c.setCellFormula("A!A1+A!B1");
+            c=r.createCell((short)2); c.setCellFormula("C!A1+C!B1");
+            wb.write(out);
+            out.close();
+            
+             assertTrue("file exists",file.exists());
+            
+            FileInputStream in = new FileInputStream(file);
+            wb = new HSSFWorkbook(in);
+            s = wb.getSheet("B");
+            r = s.getRow(0);
+            c = r.getCell((short)0);
+            //assertTrue("expected: AVERAGE(A!A1:B1) got: "+c.getCellFormula(), ("AVERAGE(A!A1:B1)").equals(c.getCellFormula()));
+            c = r.getCell((short)1);
+            //assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
+            in.close();
+    }
+    
     public void testRVAoperands() throws Exception {
          File file = File.createTempFile("testFormulaRVA",".xls");
             FileOutputStream out    = new FileOutputStream(file);