avik 2002/06/25 01:35:16
Modified: src/java/org/apache/poi/hssf/model Sheet.java
src/java/org/apache/poi/hssf/record/formula Area3DPtg.java
FormulaParser.java Ref3DPtg.java
src/java/org/apache/poi/hssf/usermodel HSSFCell.java
src/testcases/org/apache/poi/hssf/record/formula
TestFormulaParser.java
src/testcases/org/apache/poi/hssf/usermodel
TestFormulas.java
Log:
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
Revision Changes Path
1.10 +1 -1 jakarta-poi/src/java/org/apache/poi/hssf/model/Sheet.java
Index: Sheet.java
===================================================================
RCS file: /home/cvs/jakarta-poi/src/java/org/apache/poi/hssf/model/Sheet.java,v
retrieving revision 1.9
retrieving revision 1.10
diff -u -r1.9 -r1.10
--- Sheet.java 10 May 2002 03:01:10 -0000 1.9
+++ Sheet.java 25 Jun 2002 08:35:16 -0000 1.10
@@ -743,7 +743,7 @@
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;
1.5 +17 -5
jakarta-poi/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java
Index: Area3DPtg.java
===================================================================
RCS file:
/home/cvs/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -r1.4 -r1.5
--- Area3DPtg.java 12 Jun 2002 07:50:28 -0000 1.4
+++ Area3DPtg.java 25 Jun 2002 08:35:16 -0000 1.5
@@ -57,6 +57,7 @@
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 @@
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 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());
1.22 +32 -11
jakarta-poi/src/java/org/apache/poi/hssf/record/formula/FormulaParser.java
Index: FormulaParser.java
===================================================================
RCS file:
/home/cvs/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/FormulaParser.java,v
retrieving revision 1.21
retrieving revision 1.22
diff -u -r1.21 -r1.22
--- FormulaParser.java 12 Jun 2002 12:40:03 -0000 1.21
+++ FormulaParser.java 25 Jun 2002 08:35:16 -0000 1.22
@@ -63,6 +63,8 @@
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 @@
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 @@
* 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 @@
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 @@
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")) {
1.4 +12 -5
jakarta-poi/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java
Index: Ref3DPtg.java
===================================================================
RCS file:
/home/cvs/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -r1.3 -r1.4
--- Ref3DPtg.java 12 Jun 2002 07:50:28 -0000 1.3
+++ Ref3DPtg.java 25 Jun 2002 08:35:16 -0000 1.4
@@ -58,7 +58,7 @@
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 @@
private short field_3_column;
/** Creates new AreaPtg */
-
- public Ref3DPtg() {
- }
+ public Ref3DPtg() {}
public Ref3DPtg(byte[] data, int offset) {
offset++;
@@ -85,6 +83,15 @@
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 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());
1.10 +1 -1 jakarta-poi/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
Index: HSSFCell.java
===================================================================
RCS file:
/home/cvs/jakarta-poi/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java,v
retrieving revision 1.9
retrieving revision 1.10
diff -u -r1.9 -r1.10
--- HSSFCell.java 10 May 2002 03:01:10 -0000 1.9
+++ HSSFCell.java 25 Jun 2002 08:35:16 -0000 1.10
@@ -701,7 +701,7 @@
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;
1.3 +5 -5
jakarta-poi/src/testcases/org/apache/poi/hssf/record/formula/TestFormulaParser.java
Index: TestFormulaParser.java
===================================================================
RCS file:
/home/cvs/jakarta-poi/src/testcases/org/apache/poi/hssf/record/formula/TestFormulaParser.java,v
retrieving revision 1.2
retrieving revision 1.3
diff -u -r1.2 -r1.3
--- TestFormulaParser.java 28 May 2002 12:41:17 -0000 1.2
+++ TestFormulaParser.java 25 Jun 2002 08:35:16 -0000 1.3
@@ -20,13 +20,13 @@
}
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 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 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 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);
1.20 +36 -0
jakarta-poi/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java
Index: TestFormulas.java
===================================================================
RCS file:
/home/cvs/jakarta-poi/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java,v
retrieving revision 1.19
retrieving revision 1.20
diff -u -r1.19 -r1.20
--- TestFormulas.java 12 Jun 2002 12:40:03 -0000 1.19
+++ TestFormulas.java 25 Jun 2002 08:35:16 -0000 1.20
@@ -751,6 +751,42 @@
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);
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
