Index: src/java/org/apache/poi/hssf/model/FormulaParser.java =================================================================== RCS file: /home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/model/FormulaParser.java,v --- src/java/org/apache/poi/hssf/model/FormulaParser.java 23 Aug 2003 19:40:04 -0000 1.13.2.1 +++ src/java/org/apache/poi/hssf/model/FormulaParser.java 18 Dec 2003 20:50:56 -0000 @@ -80,6 +80,7 @@ * @author Andrew C. oliver (acoliver at apache dot org) * @author Eric Ladner (eladner at goldinc dot com) * @author Cameron Riley (criley at ekmail.com) + * @author Peter M. Murray (pete at quantrix dot com) */ public class FormulaParser { @@ -228,13 +229,31 @@ /** Get an Identifier */ private String GetName() { StringBuffer Token = new StringBuffer(); - if (!IsAlpha(look)) { + if (!IsAlpha(look) && look != '\'') { Expected("Name"); } - while (IsAlNum(look)) { - Token = Token.append(Character.toUpperCase(look)); - GetChar(); - } + if(look == '\'') + { + Match('\''); + boolean done = look == '\''; + while(!done) + { + Token.append(Character.toUpperCase(look)); + GetChar(); + if(look == '\'') + { + Match('\''); + done = look != '\''; + } + } + } + else + { + while (IsAlNum(look)) { + Token.append(Character.toUpperCase(look)); + GetChar(); + } + } SkipWhite(); return Token.toString(); } @@ -478,12 +497,18 @@ /** Parse and Translate a Math Factor */ private void Factor() { - if (look == '(' ) { + if (look == '-') + { + Match('-'); + Factor(); + tokens.add(new UnaryMinusPtg()); + } + else if (look == '(' ) { Match('('); Expression(); Match(')'); tokens.add(new ParenthesisPtg()); - } else if (IsAlpha(look)){ + } else if (IsAlpha(look) || look == '\''){ Ident(); } else if(look == '"') { StringLiteral(); @@ -501,26 +526,42 @@ } } - private void StringLiteral() { - Match('"'); - StringBuffer Token = new StringBuffer(); - for(;;) { - if(look == '"') { - GetChar(); - SkipWhite(); //potential white space here since it doesnt matter up to the operator - if(look == '"') - Token.append("\""); - else - break; - } else if(look == 0) { - break; - } else { - Token.append(look); - GetChar(); - } + private void StringLiteral() + { + // Can't use match here 'cuz it consumes whitespace + // which we need to preserve inside the string. + // - pete + // Match('"'); + if (look != '"') + Expected("\""); + else + { + GetChar(); + StringBuffer Token = new StringBuffer(); + for (;;) + { + if (look == '"') + { + GetChar(); + SkipWhite(); //potential white space here since it doesnt matter up to the operator + if (look == '"') + Token.append("\""); + else + break; + } + else if (look == 0) + { + break; + } + else + { + Token.append(look); + GetChar(); + } + } + tokens.add(new StringPtg(Token.toString())); } - tokens.add(new StringPtg(Token.toString())); - } + } /** Recognize and Translate a Multiply */ private void Multiply(){ @@ -590,11 +631,7 @@ /** Parse and Translate an Expression */ private void Expression() { - if (IsAddop(look)) { - EmitLn("CLR D0"); //unaryAdd ptg??? - } else { - Term(); - } + Term(); while (IsAddop(look)) { if (look == '+' ) Add(); else if (look == '-') Subtract(); @@ -725,7 +762,7 @@ private void setRootLevelRVA(Node n, int formulaType) { //Pg 16, excelfileformat.pdf @ openoffice.org Ptg p = (Ptg) n.getValue(); - if (formulaType == this.FORMULA_TYPE_NAMEDRANGE) { + if (formulaType == FormulaParser.FORMULA_TYPE_NAMEDRANGE) { if (p.getDefaultOperandClass() == Ptg.CLASS_REF) { setClass(n,Ptg.CLASS_REF); } else { Index: src/java/org/apache/poi/hssf/record/formula/AbstractFunctionPtg.java =================================================================== RCS file: /home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/AbstractFunctionPtg.java,v --- src/java/org/apache/poi/hssf/record/formula/AbstractFunctionPtg.java 23 Aug 2003 19:40:04 -0000 1.10.2.1 +++ src/java/org/apache/poi/hssf/record/formula/AbstractFunctionPtg.java 18 Dec 2003 20:50:56 -0000 @@ -536,7 +536,7 @@ functionData[25][0]=new Byte(Ptg.CLASS_VALUE);functionData[25][1]=new byte[] {Ptg.CLASS_VALUE};functionData[25][2]=new Integer(1); functionData[26][0]=new Byte(Ptg.CLASS_VALUE);functionData[26][1]=new byte[] {Ptg.CLASS_VALUE};functionData[26][2]=new Integer(1); functionData[27][0]=new Byte(Ptg.CLASS_VALUE);functionData[27][1]=new byte[] {Ptg.CLASS_VALUE};functionData[27][2]=new Integer(2); - functionData[28][0]=new Byte(Ptg.CLASS_VALUE);functionData[28][1]=new byte[] {Ptg.CLASS_REF};functionData[28][2]=new Integer(-1); + functionData[28][0]=new Byte(Ptg.CLASS_VALUE);functionData[28][1]=new byte[] {Ptg.CLASS_VALUE, Ptg.CLASS_REF};functionData[28][2]=new Integer(-1); functionData[29][0]=new Byte(Ptg.CLASS_VALUE);functionData[29][1]=new byte[] {Ptg.CLASS_REF};functionData[29][2]=new Integer(-1); functionData[30][0]=new Byte(Ptg.CLASS_VALUE);functionData[30][1]=new byte[] {Ptg.CLASS_VALUE};functionData[30][2]=new Integer(2); functionData[31][0]=new Byte(Ptg.CLASS_VALUE);functionData[31][1]=new byte[] {Ptg.CLASS_VALUE};functionData[31][2]=new Integer(3); @@ -571,7 +571,7 @@ functionData[61][0]=new Byte(Ptg.CLASS_VALUE);functionData[61][1]=new byte[] {Ptg.CLASS_VALUE};functionData[61][2]=new Integer(3); functionData[62][0]=new Byte(Ptg.CLASS_VALUE);functionData[62][1]=new byte[] {Ptg.CLASS_REF};functionData[62][2]=new Integer(-1); functionData[63][0]=new Byte(Ptg.CLASS_VALUE);functionData[63][1]=new byte[] {Ptg.CLASS_REF};functionData[63][2]=new Integer(1); - functionData[64][0]=new Byte(Ptg.CLASS_VALUE);functionData[64][1]=new byte[] {Ptg.CLASS_REF};functionData[64][2]=new Integer(-1); + functionData[64][0]=new Byte(Ptg.CLASS_VALUE);functionData[64][1]=new byte[] {Ptg.CLASS_VALUE, Ptg.CLASS_REF};functionData[64][2]=new Integer(-1); functionData[65][0]=new Byte(Ptg.CLASS_VALUE);functionData[65][1]=new byte[] {Ptg.CLASS_VALUE};functionData[65][2]=new Integer(3); functionData[66][0]=new Byte(Ptg.CLASS_VALUE);functionData[66][1]=new byte[] {Ptg.CLASS_VALUE};functionData[66][2]=new Integer(3); functionData[67][0]=new Byte(Ptg.CLASS_VALUE);functionData[67][1]=new byte[] {Ptg.CLASS_VALUE};functionData[67][2]=new Integer(1); Index: src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java =================================================================== RCS file: /home/cvspublic/jakarta-poi/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java,v --- src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java 13 Oct 2003 16:42:17 -0000 1.7.2.3 +++ src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java 18 Dec 2003 20:50:57 -0000 @@ -55,7 +55,25 @@ import junit.framework.TestCase; -import org.apache.poi.hssf.record.formula.*; +import org.apache.poi.hssf.record.formula.AbstractFunctionPtg; +import org.apache.poi.hssf.record.formula.AddPtg; +import org.apache.poi.hssf.record.formula.AttrPtg; +import org.apache.poi.hssf.record.formula.BoolPtg; +import org.apache.poi.hssf.record.formula.EqualPtg; +import org.apache.poi.hssf.record.formula.FuncVarPtg; +import org.apache.poi.hssf.record.formula.IntPtg; +import org.apache.poi.hssf.record.formula.LessEqualPtg; +import org.apache.poi.hssf.record.formula.LessThanPtg; +import org.apache.poi.hssf.record.formula.NamePtg; +import org.apache.poi.hssf.record.formula.NotEqualPtg; +import org.apache.poi.hssf.record.formula.Ptg; +import org.apache.poi.hssf.record.formula.ReferencePtg; +import org.apache.poi.hssf.record.formula.StringPtg; +import org.apache.poi.hssf.record.formula.UnaryMinusPtg; +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFRow; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; /** * Test the low level formula parser functionality. High level tests are to @@ -314,6 +332,63 @@ assertTrue("second ptg is string",ptg[1] instanceof StringPtg); } + public void testWorksheetReferences() + { + HSSFWorkbook wb = new HSSFWorkbook(); + + wb.createSheet("NoQuotesNeeded"); + wb.createSheet("Quotes Needed Here &#$@"); + + HSSFSheet sheet = wb.createSheet("Test"); + HSSFRow row = sheet.createRow(0); + HSSFCell cell; + + cell = row.createCell((short)0); + cell.setCellFormula("NoQuotesNeeded!A1"); + + cell = row.createCell((short)1); + cell.setCellFormula("'Quotes Needed Here &#$@'!A1"); + } + + public void testUnaryMinus() + { + FormulaParser fp = new FormulaParser("-A1", null); + fp.parse(); + Ptg[] ptg = fp.getRPNPtg(); + assertTrue("got 2 ptgs", ptg.length == 2); + assertTrue("first ptg is reference",ptg[0] instanceof ReferencePtg); + assertTrue("second ptg is string",ptg[1] instanceof UnaryMinusPtg); + } + + public void testLeadingSpaceInString() + { + String value = " hi "; + FormulaParser fp = new FormulaParser("\"" + value + "\"", null); + fp.parse(); + Ptg[] ptg = fp.getRPNPtg(); + + assertTrue("got 1 ptg", ptg.length == 1); + assertTrue("ptg0 is a StringPtg", ptg[0] instanceof StringPtg); + assertTrue("ptg0 contains exact value", ((StringPtg)ptg[0]).getValue().equals(value)); + } + + public void testLookupAndMatchFunctionArgs() + { + FormulaParser fp = new FormulaParser("lookup(A1, A3:A52, B3:B52)", null); + fp.parse(); + Ptg[] ptg = fp.getRPNPtg(); + + assertTrue("got 4 ptg", ptg.length == 4); + assertTrue("ptg0 has Value class", ptg[0].getPtgClass() == Ptg.CLASS_VALUE); + + fp = new FormulaParser("match(A1, A3:A52)", null); + fp.parse(); + ptg = fp.getRPNPtg(); + + assertTrue("got 3 ptg", ptg.length == 3); + assertTrue("ptg0 has Value class", ptg[0].getPtgClass() == Ptg.CLASS_VALUE); + } + public static void main(String [] args) { System.out.println("Testing org.apache.poi.hssf.record.formula.FormulaParser"); junit.textui.TestRunner.run(TestFormulaParser.class);