Attached is a patch that wires in the formula parser to the UserModel (Its a
combined patch against latest CVS). Its
actually just a few lines addition since my last patch .. primarily, an
addition of a
setCellFormula in HSSFCell.
Try running >java org.apache.poi.hssf.record.formula.FormulaParser 2+5
This will create a test.xls in the current directory with the formula in the
cell(0,0). (I'll write it up as a proper testcase later).
If u try
>java org.apache.poi.hssf.record.formula.FormulaParser (1+2)*(3+4)
the cell is created with the formula and it calculates properly in excel, the
display of the formula in excel is wrong.
(depending on your version, you might have to recalc (F9) after opening the
sheet ..
coz we do not calculate formula results in java, only store the parsed
formula expression)
I'll first work on fixing the display in excel, then i'll work on doing cell
refs. I am not planning to do much on reading at the moment.
Cheers
-
Avik
? formula2.patch
? src/documentation/xdocs/.nbattrs
? src/documentation/xdocs/hdf/.nbattrs
? src/documentation/xdocs/hpsf/.nbattrs
? src/documentation/xdocs/hssf/.nbattrs
? src/documentation/xdocs/news/.nbattrs
? src/documentation/xdocs/plan/.nbattrs
? src/documentation/xdocs/poifs/.nbattrs
? src/documentation/xdocs/resolutions/.nbattrs
? src/documentation/xdocs/utils/.nbattrs
? src/java/org/apache/poi/hssf/model/.nbattrs
? src/java/org/apache/poi/hssf/record/.nbattrs
? src/java/org/apache/poi/hssf/record/formula/.nbattrs
? src/java/org/apache/poi/hssf/record/formula/DummyFunctionPtg.java
? src/java/org/apache/poi/hssf/usermodel/.nbattrs
? tools/antipede/lib/temp
Index: .cvsignore
===================================================================
RCS file: /home/cvspublic/jakarta-poi/.cvsignore,v
retrieving revision 1.7
diff -u -r1.7 .cvsignore
--- .cvsignore 15 Apr 2002 08:07:45 -0000 1.7
+++ .cvsignore 26 Apr 2002 12:02:27 -0000
@@ -6,3 +6,4 @@
log*.*
*.log
build
+.nbattrs
Index: src/documentation/xdocs/plan/POI20Vision.xml
===================================================================
RCS file: /home/cvspublic/jakarta-poi/src/documentation/xdocs/plan/POI20Vision.xml,v
retrieving revision 1.5
diff -u -r1.5 POI20Vision.xml
--- src/documentation/xdocs/plan/POI20Vision.xml 6 Apr 2002 13:36:06 -0000
1.5
+++ src/documentation/xdocs/plan/POI20Vision.xml 26 Apr 2002 12:03:07 -0000
@@ -17,12 +17,12 @@
<section title="Preface">
<p>
This is the POI 2.0 cycle vision document. Although the vision
- has not changed and this document is certainly not out of date and
- the vision has not changed, the structure of the project has
+ has not changed and this document is certainly not out of date,
+ the structure of the project has
changed a bit. We're not going to change the vision document to
reflect this (however proper that may be) because it would only
involve deletion. There is no purpose in providing less
- information provided we give clarification.
+ information, provided we give clarification.
</p>
<p>
This document was created before the POI components for
@@ -94,7 +94,7 @@
</li>
<li>
Refactor HSSF for greater
- performance as well as an event-driven API for reading
+ performance as well as provide an event-driven API for reading
</li>
<li>
Extend HSSF by adding the ability to read and write formulas.
@@ -116,7 +116,7 @@
POIFSSerializer and HSSFSerializer
</li>
<li>
- Providing the create excel charts. (write only)
+ Providing the ability to create excel charts. (write only)
</li>
</ul>
</section>
Index: src/java/org/apache/poi/hssf/record/FormulaRecord.java
===================================================================
RCS file:
/home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/record/FormulaRecord.java,v
retrieving revision 1.5
diff -u -r1.5 FormulaRecord.java
--- src/java/org/apache/poi/hssf/record/FormulaRecord.java 18 Apr 2002 12:00:53
-0000 1.5
+++ src/java/org/apache/poi/hssf/record/FormulaRecord.java 26 Apr 2002 12:03:36
+-0000
@@ -78,7 +78,7 @@
implements CellValueRecordInterface, Comparable
{
- public static final boolean EXPERIMENTAL_FORMULA_SUPPORT_ENABLED=false;
+ public static final boolean EXPERIMENTAL_FORMULA_SUPPORT_ENABLED=true;
public static final short sid =
0x06; // docs say 406...because of a bug Microsoft support site article
#Q184647)
@@ -359,11 +359,10 @@
LittleEndian.putShort(data, 18 + offset, getOptions());
LittleEndian.putInt(data, 20 + offset, field_6_zero);
LittleEndian.putShort(data, 24 + offset, getExpressionLength());
+ serializePtgs(data, 26+offset);
} else {
System.arraycopy(all_data,0,data,offset,all_data.length);
}
-
- // serializePtgs(data, 26+offset);
return getRecordSize();
}
Index: src/java/org/apache/poi/hssf/record/formula/AddPtg.java
===================================================================
RCS file:
/home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/AddPtg.java,v
retrieving revision 1.3
diff -u -r1.3 AddPtg.java
--- src/java/org/apache/poi/hssf/record/formula/AddPtg.java 23 Apr 2002 23:56:46
-0000 1.3
+++ src/java/org/apache/poi/hssf/record/formula/AddPtg.java 26 Apr 2002 12:03:47
+-0000
@@ -141,6 +141,7 @@
return buffer.toString();
}
+
public int getPrecedence() {
return 5;
}
@@ -148,6 +149,18 @@
public int getStringLength() {
return 1;
}
+
+
+ public String toFormulaString(String[] operands) {
+ StringBuffer buffer = new StringBuffer();
+
+ buffer.append(operands[ 0 ]);
+ buffer.append("+");
+ buffer.append(operands[ 1 ]);
+ return buffer.toString();
+ }
+
+
public void manipulate(List source, List results, int pos) {
standardBinaryManipulation(source,results,pos);
Index: src/java/org/apache/poi/hssf/record/formula/AttrPtg.java
===================================================================
RCS file:
/home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/AttrPtg.java,v
retrieving revision 1.2
diff -u -r1.2 AttrPtg.java
--- src/java/org/apache/poi/hssf/record/formula/AttrPtg.java 23 Apr 2002 23:56:46
-0000 1.2
+++ src/java/org/apache/poi/hssf/record/formula/AttrPtg.java 26 Apr 2002 12:03:56
+-0000
@@ -210,5 +210,12 @@
public void manipulate(List source, List results, int pos) {
}
+ public String toFormulaString(String[] operands) {
+ return "SUM(" + operands[ 0 ] + ")";
+ }
+
+ public int getPrecedence() {
+ return 1;
+ }
}
Index: src/java/org/apache/poi/hssf/record/formula/DividePtg.java
===================================================================
RCS file:
/home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/DividePtg.java,v
retrieving revision 1.2
diff -u -r1.2 DividePtg.java
--- src/java/org/apache/poi/hssf/record/formula/DividePtg.java 23 Apr 2002 23:56:46
-0000 1.2
+++ src/java/org/apache/poi/hssf/record/formula/DividePtg.java 26 Apr 2002 12:04:02
+-0000
@@ -119,7 +119,25 @@
buffer.append(operands[ 1 ].toFormulaString());
return buffer.toString();
}
+
+ public int getPrecedence() {
+ return 4;
+ }
+ public int getStringLength() {
+ return 1;
+ }
+
+ public String toFormulaString(String[] operands) {
+ StringBuffer buffer = new StringBuffer();
+
+ buffer.append(operands[ 0 ]);
+ buffer.append("/");
+ buffer.append(operands[ 1 ]);
+ return buffer.toString();
+ }
+
+
public void manipulate(List source, List results, int pos) {
}
Index: src/java/org/apache/poi/hssf/record/formula/FormulaParser.java
===================================================================
RCS file:
/home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/FormulaParser.java,v
retrieving revision 1.1
diff -u -r1.1 FormulaParser.java
--- src/java/org/apache/poi/hssf/record/formula/FormulaParser.java 17 Apr 2002
23:06:49 -0000 1.1
+++ src/java/org/apache/poi/hssf/record/formula/FormulaParser.java 26 Apr 2002
+12:04:20 -0000
@@ -60,56 +60,66 @@
import java.util.ArrayList;
import java.util.Stack;
+import org.apache.poi.hssf.usermodel.*;
+import java.io.FileOutputStream;
+
/**
- * EXPERIMENTAL code to parse formulas back and forth between RPN and not
+ * EXPERIMENTAL
+ *
+ * @author Avik Sengupta <avik AT Avik Sengupta DOT com>
*
- * @author Avik Sengupta <[EMAIL PROTECTED]>
+ * This class parses a formula string into a List of tokens in RPN order
+ * Inspired by
+ * Lets Build a Compiler, by Jack Crenshaw
+ * BNF for the formula expression is :
+ * <expression> ::= <term> [<addop> <term>]*
+ * <term> ::= <factor> [ <mulop> <factor ]*
+ * <factor> ::= <number> | (<expression>) | <cellRef>
*/
public class FormulaParser {
private String formulaString;
private int pointer=0;
- private Stack operationsList = new java.util.Stack();
- private Stack operandsList = new java.util.Stack();
+ private List tokens = new java.util.Stack();
+ //private Stack tokens = new java.util.Stack();
private List result = new ArrayList();
private int numParen;
- //{--------------------------------------------------------------}
- //{ Constant Declarations }
-
private static char TAB = '\t';
private static char CR = '\n';
- //{--------------------------------------------------------------}
- //{ Variable Declarations }
+ private char Look; // Lookahead Character
- private char Look; //{ Lookahead Character }
+ /** create the parser with the string that is to be parsed
+ * later call the parse() method to return ptg list in rpn order
+ * then call the getRPNPtg() to retrive the parse results
+ * This class is recommended only for single threaded use
+ * 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){
formulaString = formula;
pointer=0;
}
- //{--------------------------------------------------------------}
- //{ Read New Character From Input Stream }
-
+
+ /** Read New Character From Input Stream */
private void GetChar() {
Look=formulaString.charAt(pointer++);
- System.out.println("Got char: "+Look);
+ //System.out.println("Got char: "+Look);
}
- //{--------------------------------------------------------------}
- //{ Report an Error }
-
+
+ /** Report an Error */
private void Error(String s) {
System.out.println("Error: "+s);
}
- //{--------------------------------------------------------------}
- //{ Report Error and Halt }
-
+
+ /** Report Error and Halt */
private void Abort(String s) {
Error(s);
//System.exit(1); //throw exception??
@@ -117,61 +127,50 @@
}
- //{--------------------------------------------------------------}
- //{ Report What Was Expected }
-
+
+ /** Report What Was Expected */
private void Expected(String s) {
Abort(s + " Expected");
}
- //{--------------------------------------------------------------}
- //{ Recognize an Alpha Character }
-
+
+ /** Recognize an Alpha Character */
private boolean IsAlpha(char c) {
return Character.isLetter(c);
- //return UpCase(c) in ['A'..'Z'];
}
- //{--------------------------------------------------------------}
- //{ Recognize a Decimal Digit }
-
+
+ /** Recognize a Decimal Digit */
private boolean IsDigit(char c) {
- System.out.println("Checking digit for"+c);
+ //System.out.println("Checking digit for"+c);
return Character.isDigit(c);
-
- //return ("0123456789".indexOf( (int) c) != 0)//c in ['0'..'9'];
}
- //{--------------------------------------------------------------}
- //{ Recognize an Alphanumeric }
-
+
+ /** Recognize an Alphanumeric */
private boolean IsAlNum(char c) {
return (IsAlpha(c) || IsDigit(c));
}
- //{--------------------------------------------------------------}
- //{ Recognize an Addop }
-
+
+ /** Recognize an Addop */
private boolean IsAddop( char c) {
return (c =='+' || c =='-');
}
-
- //{--------------------------------------------------------------}
- //{ Recognize White Space }
-
+
+ /** Recognize White Space */
private boolean IsWhite( char c) {
return (c ==' ' || c== TAB);
}
- //{--------------------------------------------------------------}
- //{ Skip Over Leading White Space }
-
+
+ /** Skip Over Leading White Space */
private void SkipWhite() {
while (IsWhite(Look)) {
GetChar();
@@ -179,9 +178,8 @@
}
- //{--------------------------------------------------------------}
- //{ Match a Specific Input Character }
-
+
+ /** Match a Specific Input Character */
private void Match(char x) {
if (Look != x) {
Expected("" + x + "");
@@ -192,9 +190,7 @@
}
- //{--------------------------------------------------------------}
- //{ Get an Identifier }
-
+ /** Get an Identifier */
private String GetName() {
String Token;
Token = "";
@@ -211,9 +207,7 @@
}
- //{--------------------------------------------------------------}
- //{ Get a Number }
-
+ /** Get a Number */
private String GetNum() {
String Value ="";
if (!IsDigit(Look)) Expected("Integer");
@@ -224,63 +218,64 @@
SkipWhite();
return Value;
}
-
-
- //{--------------------------------------------------------------}
- //{ Output a String with Tab }
-
+
+ /** Output a String with Tab */
private void Emit(String s){
System.out.print(TAB+s);
}
-
-
- //{--------------------------------------------------------------}
- //{ Output a String with Tab and CRLF }
-
+
+ /** Output a String with Tab and CRLF */
private void EmitLn(String s) {
Emit(s);
System.out.println();;
}
-
- //{---------------------------------------------------------------}
- //{ Parse and Translate a Identifier }
-
+ /** Parse and Translate a Identifier */
private void Ident() {
String Name;
Name = GetName();
if (Look == '('){
+ //This is a function
Match('(');
- //Expression() -- add this!
+ int numArgs = Arguments(); //Expression() -- add this!
Match(')');
//this is the end of the function
- //EmitLn("BSR " + Name);
+ tokens.add(new DummyFunctionPtg(Name,numArgs));
} else {
- //EmitLn("MOVE " + Name + "(PC),D0b");
//this can be either a cell ref or a named range !!
boolean cellRef = true ; //we should probably do it with reg exp??
if (cellRef) {
- operationsList.add(new ValueReferencePtg()); //TODO we need to pass
in Name somewhere
+ tokens.add(new ValueReferencePtg()); //TODO we need to pass in Name
+somewhere??
}else {
//handle after named range is integrated!!
}
}
}
-
-
- //{---------------------------------------------------------------}
- //{ Parse and Translate a Math Factor }
-
- //procedure Expression; Forward;
+ /** get arguments to a function */
+ private int Arguments() {
+ int numArgs = 0;
+ if (Look != ')') {
+ numArgs++;
+ Expression();
+ }
+ while (Look == ',') {
+ Match(',');
+ Expression();
+ numArgs++;
+ }
+ return numArgs;
+ }
+
+ /** Parse and Translate a Math Factor */
private void Factor() {
if (Look == '(' ) {
Match('(');
- operationsList.add(new ParenthesisPtg());
+ //tokens.add(new ParenthesisPtg());
Expression();
Match(')');
- operationsList.add(new ParenthesisPtg());
+ //tokens.add(new ParenthesisPtg());
return;
} else if (IsAlpha(Look)){
Ident();
@@ -288,38 +283,32 @@
//EmitLn("MOVE #" + GetNum() + ",D0");
IntPtg p = new IntPtg();
p.setValue(Short.parseShort(GetNum()));
- operandsList.add(p);
+ tokens.add(p);
}
}
- //{--------------------------------------------------------------}
- //{ Recognize and Translate a Multiply }
-
+ /** Recognize and Translate a Multiply */
private void Multiply(){
Match('*');
Factor();
- operationsList.add(new MultiplyPtg());
+ tokens.add(new MultiplyPtg());
//EmitLn("MULS (SP)+,D0");
}
- //{-------------------------------------------------------------}
- //{ Recognize and Translate a Divide }
-
+ /** Recognize and Translate a Divide */
private void Divide() {
Match('/');
Factor();
- operationsList.add(new DividePtg());
+ tokens.add(new DividePtg());
//EmitLn("MOVE (SP)+,D1");
//EmitLn("EXS.L D0");
//EmitLn("DIVS D1,D0");
}
- //{---------------------------------------------------------------}
- //{ Parse and Translate a Math Term }
-
+ /** Parse and Translate a Math Term */
private void Term(){
Factor();
while (Look == '*' || Look == '/' ) {
@@ -330,33 +319,23 @@
}
}
-
- //{--------------------------------------------------------------}
- //{ Recognize and Translate an Add }
-
+ /** Recognize and Translate an Add */
private void Add() {
Match('+');
Term();
- //EmitLn("ADD (SP)+,D0");
- operationsList.add(new AddPtg());
+ tokens.add(new AddPtg());
}
- //{-------------------------------------------------------------}
- //{ Recognize and Translate a Subtract }
-
+ /** Recognize and Translate a Subtract */
private void Subtract() {
Match('-');
Term();
- operationsList.add(new SubtractPtg());
- //EmitLn("SUB (SP)+,D0");
- //EmitLn("NEG D0");
+ tokens.add(new SubtractPtg());
}
- //{---------------------------------------------------------------}
- //{ Parse and Translate an Expression }
-
+ /** Parse and Translate an Expression */
private void Expression() {
if (IsAddop(Look)) {
EmitLn("CLR D0"); //unaryAdd ptg???
@@ -364,9 +343,10 @@
Term();
}
while (IsAddop(Look)) {
- EmitLn("MOVE D0,-(SP)");
if ( Look == '+' ) Add();
if (Look == '-') Subtract();
+ // if (Look == '*') Multiply();
+ // if (Look == '/') Divide();
}
}
@@ -380,71 +360,104 @@
Name := GetName;
Match('=');
Expression;
- EmitLn('LEA ' + Name + '(PC),A0');
- EmitLn('MOVE D0,(A0)')
+
end;
**/
- //{--------------------------------------------------------------}
- //{ Initialize }
+
+ /** Initialize */
private void Init() {
GetChar();
SkipWhite();
}
+ /** API call to execute the parsing of the formula
+ *
+ */
public void parse() {
- Init();
- Expression();
- //now tokenisation is done .. convert to RPN!!
- tokenToRPN();
+ synchronized (tokens) {
+ Init();
+ Expression();
+ }
}
- private void tokenToRPN() {
- OperationPtg op;
- Ptg operand;
- int numOper = 0;
- int numOnStack = 0;
- result.add(operandsList.pop()); numOnStack++;
-
- while (!operationsList.isEmpty()) {
- op = (OperationPtg) operationsList.pop();
- if (op instanceof ParenthesisPtg) {
- // do something smart
+ /** API call to retrive the array of Ptgs created as
+ * a result of the parsing
+ */
+ public Ptg[] getRPNPtg() {
+ synchronized (tokens) {
+ if (tokens == null) throw new IllegalStateException("Please parse a
+string before trying to access the parse result");
+ Ptg[] retval = new Ptg[tokens.size()];
+ return (Ptg[]) tokens.toArray(retval);
+ }
+ }
+
+ /** Static method to convert an array of Ptgs in RPN order
+ * to a human readable string format in infix mode
+ * TODO - extra brackets might appear, but string will be semantically correct.
+ */
+ public static String toFormulaString(Ptg[] ptgs) {
+ java.util.Stack stack = new java.util.Stack();
+ int numPtgs = ptgs.length;
+ OperationPtg o;
+ int numOperands;
+ String[] operands;
+ for (int i=0;i<numPtgs;i++) {
+ if (ptgs[i] instanceof OperationPtg) {
+ o = (OperationPtg) ptgs[i];
+ numOperands = o.getNumberOfOperands();
+ operands = new String[numOperands];
+ for (int j=0;j<numOperands;j++) {
+ operands[numOperands-j-1] = (String) stack.pop(); //TODO: catch
+stack underflow and throw parse exception.
+
+ }
+ String result = o.toFormulaString(operands);
+ if (! (o instanceof DummyFunctionPtg) ) result = "("+result+")" ;
+ stack.push(result);
+ } else {
+ stack.push(ptgs[i].toFormulaString());
}
-
-
- for (numOper = op.getNumberOfOperands();numOper>0;numOper--) {
- if (numOnStack==0) {
- result.add(operandsList.pop());//numOnStack++;
- } else {
- numOnStack--;
- }
- }
- result.add(op);
- numOnStack++;
}
+ return (String) stack.pop(); //TODO: catch stack underflow and throw parse
+exception.
}
-
+
public String toString() {
StringBuffer buf = new StringBuffer();
- for (int i=0;i<result.size();i++) {
- buf.append( ( (Ptg)result.get(i)).toFormulaString());
+ for (int i=0;i<tokens.size();i++) {
+ buf.append( ( (Ptg)tokens.get(i)).toFormulaString());
buf.append(' ');
}
return buf.toString();
}
- //{--------------------------------------------------------------}
- //{ Main Program for testing}
+ /** Main Program for testing*/
public static void main(String[] argv) {
FormulaParser fp = new FormulaParser(argv[0]+";");
+ System.out.println("\nFormula is: ");
fp.parse();
- System.out.println(fp.toString());
+ System.out.println("RPN Form is: " +fp.toString());
+
+ System.out.println("Converted Text form is :
+"+fp.toFormulaString(fp.getRPNPtg()));
+ try {
+ short rownum = 0;
+ FileOutputStream out = new FileOutputStream("test.xls");
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFSheet s = wb.createSheet();
+ HSSFRow r = null;
+ HSSFCell c = null;
+
+ r = s.createRow((short) 0);
+ c = r.createCell((short) 0);
+ c.setCellFormula(argv[0]);
+ wb.write(out);
+ out.close();
+ } catch (java.io.IOException ioe) {
+ ioe.printStackTrace();
+ }
//If Look <> CR then Expected('NewLine');
}
- //{--------------------------------------------------------------}
}
Index: src/java/org/apache/poi/hssf/record/formula/MultiplyPtg.java
===================================================================
RCS file:
/home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/MultiplyPtg.java,v
retrieving revision 1.2
diff -u -r1.2 MultiplyPtg.java
--- src/java/org/apache/poi/hssf/record/formula/MultiplyPtg.java 23 Apr 2002
23:56:46 -0000 1.2
+++ src/java/org/apache/poi/hssf/record/formula/MultiplyPtg.java 26 Apr 2002
+12:04:27 -0000
@@ -132,6 +132,16 @@
return buffer.toString();
}
+ public String toFormulaString(String[] operands) {
+ StringBuffer buffer = new StringBuffer();
+
+ buffer.append(operands[ 0 ]);
+ buffer.append("*");
+ buffer.append(operands[ 1 ]);
+ return buffer.toString();
+ }
+
+
public void manipulate(List source, List results, int pos) {
standardBinaryManipulation(source, results, pos);
}
@@ -155,4 +165,5 @@
}
+
}
Index: src/java/org/apache/poi/hssf/record/formula/OperationPtg.java
===================================================================
RCS file:
/home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/OperationPtg.java,v
retrieving revision 1.2
diff -u -r1.2 OperationPtg.java
--- src/java/org/apache/poi/hssf/record/formula/OperationPtg.java 23 Apr 2002
23:56:46 -0000 1.2
+++ src/java/org/apache/poi/hssf/record/formula/OperationPtg.java 26 Apr 2002
+12:04:31 -0000
@@ -74,6 +74,10 @@
public final static int TYPE_FUNCTION = 2;
public abstract int getType();
+
+ public abstract String toFormulaString(String[] operands);
+
+ public abstract int getPrecedence();
public abstract int getNumberOfOperands();
Index: src/java/org/apache/poi/hssf/record/formula/ParenthesisPtg.java
===================================================================
RCS file:
/home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/ParenthesisPtg.java,v
retrieving revision 1.3
diff -u -r1.3 ParenthesisPtg.java
--- src/java/org/apache/poi/hssf/record/formula/ParenthesisPtg.java 23 Apr 2002
23:56:46 -0000 1.3
+++ src/java/org/apache/poi/hssf/record/formula/ParenthesisPtg.java 26 Apr 2002
+12:04:37 -0000
@@ -99,11 +99,19 @@
public String toFormulaString(Ptg [] operands)
{
- return "(";
+ return "";
}
public void manipulate(List source, List results, int pos) {
}
+
+ public String toFormulaString(String[] operands) {
+ return "";
+ }
+
+ public int getPrecedence() {
+ return 2;
+ }
}
Index: src/java/org/apache/poi/hssf/record/formula/PowerPtg.java
===================================================================
RCS file:
/home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/PowerPtg.java,v
retrieving revision 1.2
diff -u -r1.2 PowerPtg.java
--- src/java/org/apache/poi/hssf/record/formula/PowerPtg.java 23 Apr 2002 23:56:46
-0000 1.2
+++ src/java/org/apache/poi/hssf/record/formula/PowerPtg.java 26 Apr 2002 12:04:42
+-0000
@@ -109,6 +109,10 @@
{
return "^";
}
+
+ public int getPrecedence() {
+ return 3;
+ }
public String toFormulaString(Ptg [] operands)
{
@@ -119,8 +123,21 @@
buffer.append(operands[ 0 ].toFormulaString());
return buffer.toString();
}
+
+
+ public String toFormulaString(String[] operands) {
+ StringBuffer buffer = new StringBuffer();
+
+ buffer.append(operands[ 1 ]);
+ buffer.append("^");
+ buffer.append(operands[ 0 ]);
+ return buffer.toString();
+ }
+
+
public void manipulate(List source, List results, int pos) {
}
+
}
Index: src/java/org/apache/poi/hssf/record/formula/Ptg.java
===================================================================
RCS file:
/home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/Ptg.java,v
retrieving revision 1.4
diff -u -r1.4 Ptg.java
--- src/java/org/apache/poi/hssf/record/formula/Ptg.java 23 Apr 2002 22:24:41
-0000 1.4
+++ src/java/org/apache/poi/hssf/record/formula/Ptg.java 26 Apr 2002 12:04:52
+-0000
@@ -77,6 +77,54 @@
{
}
+ /** convert infix order ptg list to rpn order ptg list
+ * @return List ptgs in RPN order
+ * @param infixPtgs List of ptgs in infix order
+ */
+ public static List ptgsToRpn(List infixPtgs) {
+ java.util.Stack operands = new java.util.Stack();
+ java.util.List retval = new java.util.Stack();
+
+ java.util.ListIterator i = infixPtgs.listIterator();
+ Object p;
+ OperationPtg o ;
+ boolean weHaveABracket = false;
+ while (i.hasNext()) {
+ p=i.next();
+ if (p instanceof OperationPtg) {
+ if (p instanceof ParenthesisPtg) {
+ if (!weHaveABracket) {
+ operands.push(p);
+ weHaveABracket = true;
+ } else {
+ o = (OperationPtg) operands.pop();
+ while (!(o instanceof ParenthesisPtg)) {
+ retval.add(o);
+ }
+ weHaveABracket = false;
+ }
+ } else {
+
+ while (!operands.isEmpty() && ((OperationPtg)
+operands.peek()).getPrecedence() >= ((OperationPtg) p).getPrecedence() ) { //TODO
+handle ^ since it is right associative
+ retval.add(operands.pop());
+ }
+ operands.push(p);
+ }
+ } else {
+ retval.add(p);
+ }
+ }
+ while (!operands.isEmpty()) {
+ if (operands.peek() instanceof ParenthesisPtg ){
+ //throw some error
+ } else {
+ retval.add(operands.pop());
+ }
+ }
+ return retval;
+ }
+
+
/*
Index: src/java/org/apache/poi/hssf/record/formula/SubtractPtg.java
===================================================================
RCS file:
/home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/SubtractPtg.java,v
retrieving revision 1.2
diff -u -r1.2 SubtractPtg.java
--- src/java/org/apache/poi/hssf/record/formula/SubtractPtg.java 23 Apr 2002
23:56:46 -0000 1.2
+++ src/java/org/apache/poi/hssf/record/formula/SubtractPtg.java 26 Apr 2002
+12:04:59 -0000
@@ -119,9 +119,27 @@
buffer.append(operands[ 1 ].toFormulaString());
return buffer.toString();
}
+
+ public int getPrecedence() {
+ return 5;
+ }
- public void manipulate(List source, List results, int pos) {
+ public int getStringLength() {
+ return 1;
+ }
+
+ public String toFormulaString(String[] operands) {
+ StringBuffer buffer = new StringBuffer();
+
+ buffer.append(operands[ 0 ]);
+ buffer.append("-");
+ buffer.append(operands[ 1 ]);
+ return buffer.toString();
}
+
+ public void manipulate(List source, List results, int pos) {
+ }
+
}
Index: src/java/org/apache/poi/hssf/record/formula/ValueVariableFunctionPtg.java
===================================================================
RCS file:
/home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/ValueVariableFunctionPtg.java,v
retrieving revision 1.2
diff -u -r1.2 ValueVariableFunctionPtg.java
--- src/java/org/apache/poi/hssf/record/formula/ValueVariableFunctionPtg.java 23 Apr
2002 23:56:46 -0000 1.2
+++ src/java/org/apache/poi/hssf/record/formula/ValueVariableFunctionPtg.java 26 Apr
+2002 12:05:04 -0000
@@ -128,8 +128,20 @@
{
return toFormulaString();
}
+
+ public String toFormulaString(String[] operands) {
+ return toFormulaString();
+ }
+
+
public void manipulate(List source, List results, int pos) {
}
+ public int getPrecedence() {
+ return 1;
+ }
+
+
+
}
Index: src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
===================================================================
RCS file:
/home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java,v
retrieving revision 1.5
diff -u -r1.5 HSSFCell.java
--- src/java/org/apache/poi/hssf/usermodel/HSSFCell.java 15 Mar 2002 12:15:59
-0000 1.5
+++ src/java/org/apache/poi/hssf/usermodel/HSSFCell.java 26 Apr 2002 12:05:42
+-0000
@@ -70,6 +70,9 @@
import org.apache.poi.hssf.record.BlankRecord;
import org.apache.poi.hssf.record.BoolErrRecord;
import org.apache.poi.hssf.record.ExtendedFormatRecord;
+import org.apache.poi.hssf.record.formula.Ptg;
+
+import org.apache.poi.hssf.record.formula.FormulaParser;
import java.util.Date;
import java.util.Calendar;
@@ -684,6 +687,32 @@
}
}
+ public void setCellFormula(String formula) {
+ if (formula==null) {
+ setCellType(CELL_TYPE_BLANK,false);
+ } else {
+
+ setCellType(CELL_TYPE_FORMULA,false);
+ FormulaRecord rec = (FormulaRecord) record;
+ rec.setOptions(( short ) 2);
+ rec.setValue(0);
+ rec.setXFIndex(( short ) 0x0f);
+ FormulaParser fp = new FormulaParser(formula+";");
+ fp.parse();
+ Ptg[] ptg = fp.getRPNPtg();
+ int size = 0;
+ System.out.println("got Ptgs " + ptg.length);
+ for (int k = 0; k < ptg.length; k++) {
+ size += ptg[ k ].getSize();
+ rec.pushExpressionToken(ptg[ k ]);
+ }
+ rec.setExpressionLength(( short ) size);
+ //return rec;
+
+ }
+ }
+
+
/**
* get the value of the cell as a number. For strings we throw an exception.
* For blank cells we return a 0.
/*
* DummyFunctionPtg.java
*
*
*/
package org.apache.poi.hssf.record.formula;
import java.util.List;
/**
* DUMMY DUMMY DUMMY
* This class exists only becoz i dont know how to handle functions in formula's properly
* to be used only for testing my parser.
* @author aviks
* @version
*/
public class DummyFunctionPtg extends OperationPtg {
private String name;
private int numOperands;
/** Creates new DummyFunctionPtg */
public DummyFunctionPtg() {
}
public DummyFunctionPtg(String pName,int pNumOperands) {
name=pName;
numOperands = pNumOperands;
}
public int getPrecedence() {
return 1;
}
public int getType() {
return -1;
}
public int getNumberOfOperands() {
return numOperands;
}
public String getName() {
return name;
}
public String toFormulaString() {
return getName()+getNumberOfOperands();
}
public String toFormulaString(Ptg[] operands) {
StringBuffer buf = new StringBuffer();
buf.append(getName()+"(");
for (int i=0;i<operands.length;i++) {
buf.append(operands[i].toFormulaString());
}
buf.append(")");
return buf.toString();
}
public String toFormulaString(String[] operands) {
StringBuffer buf = new StringBuffer();
buf.append(getName()+"(");
if (operands.length >0) {
for (int i=0;i<operands.length;i++) {
buf.append(operands[i]);
buf.append(',');
}
buf.deleteCharAt(buf.length()-1);
}
buf.append(")");
return buf.toString();
}
public void writeBytes(byte[] array, int offset) {
}
public int getSize() {
return 0;
}
public void manipulate(List source, List results, int pos) {
}
}
