better support for sheet functions.... more refactoring after i have written tests for named range. Inclueds a fix for bug 9632

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

6 files changed, 108 insertions, 47 deletions

diff --git a/src/java/org/apache/poi/hssf/model/Workbook.java b/src/java/org/apache/poi/hssf/model/Workbook.java
index b52394a187..8ae0822a54 100644
--- a/src/java/org/apache/poi/hssf/model/Workbook.java
+++ b/src/java/org/apache/poi/hssf/model/Workbook.java
@@ -88,6 +88,8 @@ import org.apache.poi.util.POILogFactory;
 public class Workbook {
     private static final int   DEBUG       = POILogger.DEBUG;
     
+    public static ThreadLocal currentBook = new ThreadLocal();
+    
     /**
      * constant used to set the "codepage" wherever "codepage" is set in records
      * (which is duplciated in more than one record)
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 bf757241ff..3c3aaf3520 100644
--- a/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java
+++ b/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java
@@ -58,12 +58,17 @@ 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;
+import org.apache.poi.hssf.util.CellReference;
+
+import org.apache.poi.hssf.model.Workbook;
+import org.apache.poi.util.BitField;
 
 /**
  * Title:        Area 3D Ptg - 3D referecnce (Sheet + Area)<P>
  * Description:  Defined a area in Extern Sheet. <P>
  * REFERENCE:  <P>
  * @author Libin Roman (Vista Portal LDT. Developer)
+ * @author avik
  * @version 1.0-pre
  */
 
@@ -76,6 +81,9 @@ public class Area3DPtg extends Ptg
     private short             field_3_last_row;
     private short             field_4_first_column;
     private short             field_5_last_column;
+    
+    private BitField         rowRelative = new BitField(0x8000);
+    private BitField         colRelative = new BitField(0x4000);
 
     /** Creates new AreaPtg */
     public Area3DPtg() {}
@@ -87,10 +95,10 @@ public class Area3DPtg extends Ptg
         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());
+        setFirstColRelative(!ar.getCells()[0].isColAbsolute());
+        setLastColRelative(!ar.getCells()[1].isColAbsolute());
+        setFirstRowRelative(!ar.getCells()[0].isRowAbsolute());
+        setLastRowRelative(!ar.getCells()[1].isRowAbsolute());
         setExternSheetIndex(externIdx);
         
     }
@@ -114,10 +122,10 @@ public class Area3DPtg extends Ptg
         buffer.append("lastRow  = " + getLastRow()).append("\n");
         buffer.append("firstCol = " + getFirstColumn()).append("\n");
         buffer.append("lastCol  = " + getLastColumn()).append("\n");
-        buffer.append("firstColRowRel= "
-                      + isFirstColRowRelative()).append("\n");
+        buffer.append("firstColRel= "
+                      + isFirstRowRelative()).append("\n");
         buffer.append("lastColRowRel = "
-                      + isLastColRowRelative()).append("\n");
+                      + isLastRowRelative()).append("\n");
         buffer.append("firstColRel   = " + isFirstColRelative()).append("\n");
         buffer.append("lastColRel    = " + isLastColRelative()).append("\n");
         return buffer.toString();
@@ -176,14 +184,14 @@ public class Area3DPtg extends Ptg
         return field_4_first_column;
     }
 
-    public boolean isFirstColRowRelative()
+    public boolean isFirstRowRelative()
     {
-        return (((getFirstColumnRaw()) & 0x8000) == 0x8000);
+        return rowRelative.isSet(field_4_first_column);
     }
-
+    
     public boolean isFirstColRelative()
     {
-        return (((getFirstColumnRaw()) & 0x4000) == 0x4000);
+        return colRelative.isSet(field_4_first_column);
     }
 
     public void setFirstColumn(short column)
@@ -207,16 +215,15 @@ public class Area3DPtg extends Ptg
         return field_5_last_column;
     }
 
-    public boolean isLastColRowRelative()
+     public boolean isLastRowRelative()
     {
-        return (((getLastColumnRaw()) & 0x8000) == 1);
+        return rowRelative.isSet(field_5_last_column);
     }
-
     public boolean isLastColRelative()
     {
-        return (((getFirstColumnRaw()) & 0x4000) == 1);
+        return colRelative.isSet(field_5_last_column);
     }
-
+    
     public void setLastColumn(short column)
     {
         field_5_last_column &= 0xFF00;
@@ -227,6 +234,37 @@ public class Area3DPtg extends Ptg
     {
         field_5_last_column = column;
     }
+    
+        /**
+     * sets the first row to relative or not
+     * @param isRelative or not.
+     */
+    public void setFirstRowRelative(boolean rel) {
+        field_4_first_column=rowRelative.setShortBoolean(field_4_first_column,rel);
+    }
+
+    /**
+     * set whether the first column is relative 
+     */
+    public void setFirstColRelative(boolean rel) {
+        field_4_first_column=colRelative.setShortBoolean(field_4_first_column,rel);
+    }
+    
+    /**
+     * set whether the last row is relative or not
+     * @param last row relative
+     */
+    public void setLastRowRelative(boolean rel) {
+        field_5_last_column=rowRelative.setShortBoolean(field_5_last_column,rel);
+    }
+    
+    /**
+     * set whether the last column should be relative or not
+     */
+    public void setLastColRelative(boolean rel) {
+        field_5_last_column=colRelative.setShortBoolean(field_5_last_column,rel);
+    }
+    
 
     public String getArea(){
         RangeAddress ra = new RangeAddress( getFirstColumn(),getFirstRow() + 1, getLastColumn(), getLastRow() + 1);
@@ -250,13 +288,20 @@ public class Area3DPtg extends Ptg
 
     public String toFormulaString()
     {
-        String result = getArea();
-
-        return result;
+        StringBuffer retval = new StringBuffer();
+        Object book = Workbook.currentBook.get();
+        if (book != null) {
+            retval.append(((Workbook) book).findSheetNameFromExternSheet(this.field_1_index_extern_sheet));
+            retval.append('!');
+        }
+        retval.append((new CellReference(getFirstRow(),getFirstColumn(),!isFirstRowRelative(),!isFirstColRelative())).toString()); 
+        retval.append(':');
+        retval.append((new CellReference(getLastRow(),getLastColumn(),!isLastRowRelative(),!isLastColRelative())).toString());
+        return retval.toString();
     }
 
    public byte getDefaultOperandClass() {
-       return Ptg.CLASS_VALUE;
+       return Ptg.CLASS_REF;
    }
 
 }
diff --git a/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java b/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java
index fc6126f6ad..f8f2d44615 100644
--- a/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java
+++ b/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java
@@ -88,9 +88,7 @@ public class AreaPtg
     
    
     protected AreaPtg(String arearef) {
-        //int[] xyxy = ReferenceUtil.getXYXYFromAreaRef(arearef);
         AreaReference ar = new AreaReference(arearef);
-        
         setFirstRow((short)ar.getCells()[0].getRow());
         setFirstColumn((short)ar.getCells()[0].getCol());
         setLastRow((short)ar.getCells()[1].getRow());
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 579e40ae74..885267ecca 100644
--- a/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java
+++ b/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java
@@ -59,6 +59,9 @@ 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;
+import org.apache.poi.util.BitField;
+import org.apache.poi.hssf.model.Workbook;
+
 /**
  * Title:        Reference 3D Ptg <P>
  * Description:  Defined a cell in extern sheet. <P>
@@ -73,6 +76,8 @@ public class Ref3DPtg extends Ptg {
     private short             field_1_index_extern_sheet;
     private short             field_2_row;
     private short             field_3_column;
+    private BitField         rowRelative = new BitField(0x8000);
+    private BitField         colRelative = new BitField(0x4000);
 
     /** Creates new AreaPtg */
     public Ref3DPtg() {}
@@ -88,8 +93,8 @@ public class Ref3DPtg extends Ptg {
         CellReference c= new CellReference(cellref);
         setRow((short) c.getRow());
         setColumn((short) c.getCol());
-        //setColRelative(!c.isColAbsolute());
-        //setRowRelative(!c.isRowAbsolute());   
+        setColRelative(!c.isColAbsolute());
+        setRowRelative(!c.isRowAbsolute());   
         setExternSheetIndex(externIdx);
     }
 
@@ -101,7 +106,7 @@ public class Ref3DPtg extends Ptg {
         buffer.append("Row = " + getRow()).append("\n");
         buffer.append("Col  = " + getColumn()).append("\n");
         buffer.append("ColRowRel= "
-        + isColRowRelative()).append("\n");
+        + isRowRelative()).append("\n");
         buffer.append("ColRel   = " + isColRelative()).append("\n");
         return buffer.toString();
     }
@@ -141,14 +146,23 @@ public class Ref3DPtg extends Ptg {
         return field_3_column;
     }
 
-    public boolean isColRowRelative() {
-        return (((getColumnRaw()) & 0x8000) == 0x8000);
+     public boolean isRowRelative()
+    {
+        return rowRelative.isSet(field_3_column);
     }
-
-    public boolean isColRelative() {
-        return (((getColumnRaw()) & 0x4000) == 0x4000);
+    
+    public void setRowRelative(boolean rel) {
+        field_3_column=rowRelative.setShortBoolean(field_3_column,rel);
+    }
+    
+    public boolean isColRelative()
+    {
+        return colRelative.isSet(field_3_column);
+    }
+    
+    public void setColRelative(boolean rel) {
+        field_3_column=colRelative.setShortBoolean(field_3_column,rel);
     }
-
     public void setColumn(short column) {
         field_3_column &= 0xFF00;
         field_3_column |= column & 0xFF;
@@ -177,11 +191,16 @@ public class Ref3DPtg extends Ptg {
     }
 
     public String toFormulaString() {
-        String result = getArea();
-
-        return result;
+        StringBuffer retval = new StringBuffer();
+        Object book = Workbook.currentBook.get();
+        if (book != null) {
+            retval.append(((Workbook) book).findSheetNameFromExternSheet(this.field_1_index_extern_sheet));
+            retval.append('!');
+        }
+        retval.append((new CellReference(getRow(),getColumn(),!isRowRelative(),!isColRelative())).toString()); 
+        return retval.toString();
     }
 
-   public byte getDefaultOperandClass() {return Ptg.CLASS_VALUE;}
+   public byte getDefaultOperandClass() {return Ptg.CLASS_REF;}
 
 }
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
index 110954034f..ef81c646ae 100644
--- a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
@@ -692,10 +692,10 @@ public class HSSFCell
     }
 
     public void setCellFormula(String formula) {
+        Workbook.currentBook.set(book);
         if (formula==null) {
             setCellType(CELL_TYPE_BLANK,false);
         } else {
-            
             setCellType(CELL_TYPE_FORMULA,false);
             FormulaRecord rec = (FormulaRecord) record;
             rec.setOptions(( short ) 2);
@@ -711,17 +711,14 @@ public class HSSFCell
                 rec.pushExpressionToken(ptg[ k ]);
             }
             rec.setExpressionLength(( short ) size);
-            //return rec;
-            
+            Workbook.currentBook.set(null);
         }
     }
     
     public String getCellFormula() {
-        String retval=null;
-        
-        retval = 
-              FormulaParser.toFormulaString(((FormulaRecord)record).getParsedExpression());
-        
+        Workbook.currentBook.set(book);
+        String retval = FormulaParser.toFormulaString(((FormulaRecord)record).getParsedExpression());
+        Workbook.currentBook.set(null);
         return retval;   
     }
     
diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java b/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java
index 98785eb5b8..447191b305 100644
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java
@@ -756,7 +756,7 @@ extends TestCase {
     {
         String filename = System.getProperty("HSSF.testdata.path");
 
-    File file = File.createTempFile("testSheetFormula",".xls");
+            File file = File.createTempFile("testSheetFormula",".xls");
             FileOutputStream out    = new FileOutputStream(file);
             HSSFWorkbook     wb     = new HSSFWorkbook();
             HSSFSheet        s      = wb.createSheet("A");
@@ -770,7 +770,7 @@ extends TestCase {
             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");
+            c=r.createCell((short)2); c.setCellFormula("A!$A$1+A!$B1");
             wb.write(out);
             out.close();
             
@@ -781,9 +781,9 @@ extends TestCase {
             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()));
+            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()));
+            assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
             in.close();
     }