View | Details | Raw Unified | Return to bug 44504
Collapse All | Expand All

(-)src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (-1 / +1 lines)
Lines 639-645 Link Here
639
            
639
            
640
            //only set to default if there is no extended format index already set
640
            //only set to default if there is no extended format index already set
641
            if (rec.getXFIndex() == (short)0) rec.setXFIndex(( short ) 0x0f);
641
            if (rec.getXFIndex() == (short)0) rec.setXFIndex(( short ) 0x0f);
642
            FormulaParser fp = new FormulaParser(formula+";",book);
642
            FormulaParser fp = new FormulaParser(formula, book);
643
            fp.parse();
643
            fp.parse();
644
            Ptg[] ptg  = fp.getRPNPtg();
644
            Ptg[] ptg  = fp.getRPNPtg();
645
            int   size = 0;
645
            int   size = 0;
(-)src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (-2 / +2 lines)
Lines 400-406 Link Here
400
400
401
       //formula fields ( size and data )
401
       //formula fields ( size and data )
402
       String str_formula = obj_validation.getFirstFormula();
402
       String str_formula = obj_validation.getFirstFormula();
403
       FormulaParser fp = new FormulaParser(str_formula+";",book);
403
       FormulaParser fp = new FormulaParser(str_formula, book);
404
       fp.parse();
404
       fp.parse();
405
       Stack ptg_arr = new Stack();
405
       Stack ptg_arr = new Stack();
406
       Ptg[] ptg  = fp.getRPNPtg();
406
       Ptg[] ptg  = fp.getRPNPtg();
Lines 424-430 Link Here
424
       if ( obj_validation.getSecondFormula() != null )
424
       if ( obj_validation.getSecondFormula() != null )
425
       {
425
       {
426
         str_formula = obj_validation.getSecondFormula();
426
         str_formula = obj_validation.getSecondFormula();
427
         fp = new FormulaParser(str_formula+";",book);
427
         fp = new FormulaParser(str_formula, book);
428
         fp.parse();
428
         fp.parse();
429
         ptg_arr = new Stack();
429
         ptg_arr = new Stack();
430
         ptg  = fp.getRPNPtg();
430
         ptg  = fp.getRPNPtg();
(-)src/java/org/apache/poi/hssf/model/FormulaParser.java (-625 / +626 lines)
Lines 1-4 Link Here
1
2
/* ====================================================================
1
/* ====================================================================
3
   Licensed to the Apache Software Foundation (ASF) under one or more
2
   Licensed to the Apache Software Foundation (ASF) under one or more
4
   contributor license agreements.  See the NOTICE file distributed with
3
   contributor license agreements.  See the NOTICE file distributed with
Lines 15-29 Link Here
15
   See the License for the specific language governing permissions and
14
   See the License for the specific language governing permissions and
16
   limitations under the License.
15
   limitations under the License.
17
==================================================================== */
16
==================================================================== */
18
        
19
17
20
21
package org.apache.poi.hssf.model;
18
package org.apache.poi.hssf.model;
22
19
23
import java.util.ArrayList;
20
import java.util.ArrayList;
24
import java.util.Iterator;
21
import java.util.Iterator;
25
import java.util.LinkedList;
26
import java.util.List;
22
import java.util.List;
23
import java.util.Stack;
27
import java.util.regex.Pattern;
24
import java.util.regex.Pattern;
28
25
29
//import PTG's .. since we need everything, import *
26
//import PTG's .. since we need everything, import *
Lines 33-39 Link Here
33
30
34
/**
31
/**
35
 * This class parses a formula string into a List of tokens in RPN order.
32
 * This class parses a formula string into a List of tokens in RPN order.
36
 * Inspired by 
33
 * Inspired by
37
 *           Lets Build a Compiler, by Jack Crenshaw
34
 *           Lets Build a Compiler, by Jack Crenshaw
38
 * BNF for the formula expression is :
35
 * BNF for the formula expression is :
39
 * <expression> ::= <term> [<addop> <term>]*
36
 * <expression> ::= <term> [<addop> <term>]*
Lines 48-185 Link Here
48
 *  @author Peter M. Murray (pete at quantrix dot com)
45
 *  @author Peter M. Murray (pete at quantrix dot com)
49
 *  @author Pavel Krupets (pkrupets at palmtreebusiness dot com)
46
 *  @author Pavel Krupets (pkrupets at palmtreebusiness dot com)
50
 */
47
 */
51
public class FormulaParser {
48
public final class FormulaParser {
52
    
49
    
50
    /**
51
     * Specific exception thrown when a supplied formula does not parse properly.<br/>
52
     * Primarily used by test cases when testing for specific parsing exceptions.</p>
53
     *    
54
     */
55
    static final class FormulaParseException extends RuntimeException {
56
        // This class was given package scope until it would become clear that it is useful to
57
        // general client code. 
58
        public FormulaParseException(String msg) {
59
            super(msg);
60
        }
61
    }
62
53
    public static int FORMULA_TYPE_CELL = 0;
63
    public static int FORMULA_TYPE_CELL = 0;
54
    public static int FORMULA_TYPE_SHARED = 1;
64
    public static int FORMULA_TYPE_SHARED = 1;
55
    public static int FORMULA_TYPE_ARRAY =2;
65
    public static int FORMULA_TYPE_ARRAY =2;
56
    public static int FORMULA_TYPE_CONDFOMRAT = 3;
66
    public static int FORMULA_TYPE_CONDFOMRAT = 3;
57
    public static int FORMULA_TYPE_NAMEDRANGE = 4;
67
    public static int FORMULA_TYPE_NAMEDRANGE = 4;
58
    
59
    private String formulaString;
60
    private int pointer=0;
61
    private int formulaLength;
62
    
63
    private List tokens = new java.util.Stack();
64
    
65
    /**
66
     * Using an unsynchronized linkedlist to implement a stack since we're not multi-threaded.
67
     */
68
    private List functionTokens = new LinkedList();
69
68
69
    private final String formulaString;
70
    private final int formulaLength;
71
    private int pointer;
72
73
    private final List tokens = new Stack();
74
70
    /**
75
    /**
71
     * Used for spotting if we have a cell reference,
76
     * Used for spotting if we have a cell reference,
72
     *  or a named range
77
     *  or a named range
73
     */
78
     */
74
    private final static Pattern CELL_REFERENCE_PATTERN = Pattern.compile("(?:('?)[^:\\\\/\\?\\*\\[\\]]+\\1!)?\\$?[A-Za-z]+\\$?[\\d]+");
79
    private final static Pattern CELL_REFERENCE_PATTERN = Pattern.compile("(?:('?)[^:\\\\/\\?\\*\\[\\]]+\\1!)?\\$?[A-Za-z]+\\$?[\\d]+");
75
        
80
76
    private static char TAB = '\t';
81
    private static char TAB = '\t';
77
    private static char CR = '\n';
82
78
    
83
    /**
79
   private char look;              // Lookahead Character
84
     * Lookahead Character.
80
   
85
     * gets value '\0' when the input string is exhausted
81
   private Workbook book;
86
     */
82
    
87
    private char look;
83
    
88
84
    /** 
89
    private Workbook book;
90
91
92
    /**
85
     * Create the formula parser, with the string that is to be
93
     * Create the formula parser, with the string that is to be
86
     *  parsed against the supplied workbook.
94
     *  parsed against the supplied workbook.
87
     * A later call the parse() method to return ptg list in
95
     * A later call the parse() method to return ptg list in
88
     *  rpn order, then call the getRPNPtg() to retrive the
96
     *  rpn order, then call the getRPNPtg() to retrive the
89
     *  parse results.
97
     *  parse results.
90
     * This class is recommended only for single threaded use.
98
     * This class is recommended only for single threaded use.
91
     * 
99
     *
92
     * If you only have a usermodel.HSSFWorkbook, and not a
100
     * If you only have a usermodel.HSSFWorkbook, and not a
93
     *  model.Workbook, then use the convenience method on
101
     *  model.Workbook, then use the convenience method on
94
     *  usermodel.HSSFFormulaEvaluator 
102
     *  usermodel.HSSFFormulaEvaluator
95
     */
103
     */
96
    public FormulaParser(String formula, Workbook book){
104
    public FormulaParser(String formula, Workbook book){
97
        formulaString = formula;
105
        formulaString = formula;
98
        pointer=0;
106
        pointer=0;
99
        this.book = book;
107
        this.book = book;
100
    	formulaLength = formulaString.length();
108
        formulaLength = formulaString.length();
101
    }
109
    }
102
    
103
110
111
    public static Ptg[] parse(String formula, Workbook book) {
112
        FormulaParser fp = new FormulaParser(formula, book);
113
        fp.parse();
114
        return fp.getRPNPtg();
115
    }
116
104
    /** Read New Character From Input Stream */
117
    /** Read New Character From Input Stream */
105
    private void GetChar() {
118
    private void GetChar() {
106
        // Check to see if we've walked off the end of the string.
119
        // Check to see if we've walked off the end of the string.
107
	// Just return if so and reset Look to smoething to keep 
120
        if (pointer > formulaLength) {
108
	// SkipWhitespace from spinning
121
            throw new RuntimeException("too far");
109
        if (pointer == formulaLength) {
122
        }
123
        if (pointer < formulaLength) {
124
            look=formulaString.charAt(pointer);
125
        } else {
126
            // Just return if so and reset 'look' to something to keep
127
            // SkipWhitespace from spinning
110
            look = (char)0;
128
            look = (char)0;
111
	    return;
129
        }    
112
	}
130
        pointer++;
113
        look=formulaString.charAt(pointer++);
114
        //System.out.println("Got char: "+ look);
131
        //System.out.println("Got char: "+ look);
115
    }
132
    }
116
    
117
133
118
    /** Report an Error */
134
    /** Report What Was Expected */
119
    private void Error(String s) {
135
    private RuntimeException expected(String s) {
120
        System.out.println("Error: "+s);
136
        return new FormulaParseException(s + " Expected");
121
    }
137
    }
122
    
123
    
124
 
125
    /** Report Error and Halt */
126
    private void Abort(String s) {
127
        Error(s);
128
        //System.exit(1);  //throw exception??
129
        throw new RuntimeException("Cannot Parse, sorry : " + s + " @ " + pointer + " [Formula String was: '" + formulaString + "']");
130
    }
131
    
132
    
133
138
134
    /** Report What Was Expected */
139
135
    private void Expected(String s) {
140
136
        Abort(s + " Expected");
137
    }
138
    
139
    
140
 
141
    /** Recognize an Alpha Character */
141
    /** Recognize an Alpha Character */
142
    private boolean IsAlpha(char c) {
142
    private boolean IsAlpha(char c) {
143
        return Character.isLetter(c) || c == '$' || c=='_';
143
        return Character.isLetter(c) || c == '$' || c=='_';
144
    }
144
    }
145
    
145
146
    
146
147
 
147
148
    /** Recognize a Decimal Digit */
148
    /** Recognize a Decimal Digit */
149
    private boolean IsDigit(char c) {
149
    private boolean IsDigit(char c) {
150
        //System.out.println("Checking digit for"+c);
150
        //System.out.println("Checking digit for"+c);
151
        return Character.isDigit(c);
151
        return Character.isDigit(c);
152
    }
152
    }
153
    
154
    
155
153
154
155
156
    /** Recognize an Alphanumeric */
156
    /** Recognize an Alphanumeric */
157
    private boolean  IsAlNum(char c) {
157
    private boolean  IsAlNum(char c) {
158
        return  (IsAlpha(c) || IsDigit(c));
158
        return  (IsAlpha(c) || IsDigit(c));
159
    }
159
    }
160
    
161
    
162
160
163
    /** Recognize an Addop */
164
    private boolean IsAddop( char c) {
165
        return (c =='+' || c =='-');
166
    }
167
    
168
161
169
    /** Recognize White Space */
162
    /** Recognize White Space */
170
    private boolean IsWhite( char c) {
163
    private boolean IsWhite( char c) {
171
        return  (c ==' ' || c== TAB);
164
        return  (c ==' ' || c== TAB);
172
    }
165
    }
173
    
174
    /**
175
     * Determines special characters;primarily in use for definition of string literals
176
     * @param c
177
     * @return boolean
178
     */
179
    private boolean IsSpecialChar(char c) {
180
    	return (c == '>' || c== '<' || c== '=' || c=='&' || c=='[' || c==']');
181
    }
182
    
183
166
184
    /** Skip Over Leading White Space */
167
    /** Skip Over Leading White Space */
185
    private void SkipWhite() {
168
    private void SkipWhite() {
Lines 187-294 Link Here
187
            GetChar();
170
            GetChar();
188
        }
171
        }
189
    }
172
    }
190
    
191
    
192
173
193
    /** Match a Specific Input Character */
174
    /**
175
     *  Consumes the next input character if it is equal to the one specified otherwise throws an
176
     *  unchecked exception. This method does <b>not</b> consume whitespace (before or after the
177
     *  matched character). 
178
     */
194
    private void Match(char x) {
179
    private void Match(char x) {
195
        if (look != x) {
180
        if (look != x) {
196
            Expected("" + x + "");
181
            throw expected("'" + x + "'");
197
        }else {
198
            GetChar();
199
            SkipWhite();
200
        }
182
        }
183
        GetChar();
201
    }
184
    }
202
    
185
203
    /** Get an Identifier */
186
    /** Get an Identifier */
204
    private String GetName() {
187
    private String GetName() {
205
        StringBuffer Token = new StringBuffer();
188
        StringBuffer Token = new StringBuffer();
206
        if (!IsAlpha(look) && look != '\'') {
189
        if (!IsAlpha(look) && look != '\'') {
207
            Expected("Name");
190
            throw expected("Name");
208
        }
191
        }
209
        if(look == '\'')
192
        if(look == '\'')
210
        {
193
        {
211
        	Match('\'');
194
            Match('\'');
212
        	boolean done = look == '\'';
195
            boolean done = look == '\'';
213
        	while(!done)
196
            while(!done)
214
        	{
197
            {
215
        		Token.append(Character.toUpperCase(look));
198
                Token.append(look);
216
        		GetChar();
199
                GetChar();
217
        		if(look == '\'')
200
                if(look == '\'')
218
        		{
201
                {
219
        			Match('\'');
202
                    Match('\'');
220
        			done = look != '\'';
203
                    done = look != '\'';
221
        		}
204
                }
222
        	}
205
            }
223
        }
206
        }
224
        else
207
        else
225
        {
208
        {
226
	        while (IsAlNum(look)) {
209
            while (IsAlNum(look)) {
227
	            Token.append(Character.toUpperCase(look));
210
                Token.append(look);
228
	            GetChar();
211
                GetChar();
229
	        }
212
            }
230
		}
231
        SkipWhite();
232
        return Token.toString();
233
    }
234
    
235
    /**Get an Identifier AS IS, without stripping white spaces or 
236
       converting to uppercase; used for literals */
237
    private String GetNameAsIs() {
238
        StringBuffer Token = new StringBuffer();
239
		
240
		while (IsAlNum(look) || IsWhite(look) || IsSpecialChar(look)) {
241
            Token = Token.append(look);
242
            GetChar();
243
        }
213
        }
244
        return Token.toString();
214
        return Token.toString();
245
    }
215
    }
246
    
216
247
    
217
248
    /** Get a Number */
218
    /** Get a Number */
249
    private String GetNum() {
219
    private String GetNum() {
250
        StringBuffer value = new StringBuffer();
220
        StringBuffer value = new StringBuffer();
251
        
221
252
        while (IsDigit(this.look)){
222
        while (IsDigit(this.look)){
253
            value.append(this.look);
223
            value.append(this.look);
254
            GetChar();
224
            GetChar();
255
        }
225
        }
256
        
257
        SkipWhite();
258
        
259
        return value.length() == 0 ? null : value.toString();
226
        return value.length() == 0 ? null : value.toString();
260
    }
227
    }
261
        
262
    
263
    /** Output a String with Tab */
264
    private void  Emit(String s){
265
        System.out.print(TAB+s);
266
    }
267
228
268
    /** Output a String with Tab and CRLF */
269
    private void EmitLn(String s) {
270
        Emit(s);
271
        System.out.println();;
272
    }
273
    
274
    /** Parse and Translate a String Identifier */
229
    /** Parse and Translate a String Identifier */
275
    private void Ident() {
230
    private Ptg parseIdent() {
276
        String name;
231
        String name;
277
        name = GetName();
232
        name = GetName();
278
        if (look == '('){
233
        if (look == '('){
279
            //This is a function
234
            //This is a function
280
            function(name);
235
            return function(name);
281
        } else if (look == ':' || look == '.') { // this is a AreaReference
236
        }
237
238
        if (look == ':' || look == '.') { // this is a AreaReference
282
            GetChar();
239
            GetChar();
283
            
240
284
            while (look == '.') { // formulas can have . or .. or ... instead of :
241
            while (look == '.') { // formulas can have . or .. or ... instead of :
285
                GetChar();
242
                GetChar();
286
            }
243
            }
287
            
244
288
            String first = name;
245
            String first = name;
289
            String second = GetName();
246
            String second = GetName();
290
            tokens.add(new AreaPtg(first+":"+second));
247
            return new AreaPtg(first+":"+second);
291
        } else if (look == '!') {
248
        }
249
250
        if (look == '!') {
292
            Match('!');
251
            Match('!');
293
            String sheetName = name;
252
            String sheetName = name;
294
            String first = GetName();
253
            String first = GetName();
Lines 297-375 Link Here
297
                Match(':');
256
                Match(':');
298
                String second=GetName();
257
                String second=GetName();
299
                if (look == '!') {
258
                if (look == '!') {
300
                	//The sheet name was included in both of the areas. Only really
259
                    //The sheet name was included in both of the areas. Only really
301
                	//need it once
260
                    //need it once
302
                	Match('!');
261
                    Match('!');
303
                	String third=GetName();
262
                    String third=GetName();
304
                	
263
305
                	if (!sheetName.equals(second))
264
                    if (!sheetName.equals(second))
306
                		throw new RuntimeException("Unhandled double sheet reference.");
265
                        throw new RuntimeException("Unhandled double sheet reference.");
307
                	
266
308
                	tokens.add(new Area3DPtg(first+":"+third,externIdx));
267
                    return new Area3DPtg(first+":"+third,externIdx);
309
                } else {                                  
310
                  tokens.add(new Area3DPtg(first+":"+second,externIdx));
311
                }
268
                }
312
            } else {
269
                return new Area3DPtg(first+":"+second,externIdx);
313
                tokens.add(new Ref3DPtg(first,externIdx));
314
            }
270
            }
315
        } else {
271
            return new Ref3DPtg(first,externIdx);
316
            // This can be either a cell ref or a named range
272
        }
317
        	// Try to spot which it is
273
        if (name.equalsIgnoreCase("TRUE") || name.equalsIgnoreCase("FALSE")) {
318
        	boolean cellRef = CELL_REFERENCE_PATTERN.matcher(name).matches();
274
            return new BoolPtg(name.toUpperCase());
319
            boolean boolLit = (name.equals("TRUE") || name.equals("FALSE"));
275
        }
320
            
276
321
            if (boolLit) {
277
        // This can be either a cell ref or a named range
322
                tokens.add(new BoolPtg(name));
278
        // Try to spot which it is
323
            } else if (cellRef) {
279
        boolean cellRef = CELL_REFERENCE_PATTERN.matcher(name).matches();
324
                tokens.add(new ReferencePtg(name));
280
 
325
            } else {
281
        if (cellRef) {
326
            	boolean nameRecordExists = false;
282
            return new ReferencePtg(name);
327
                for(int i = 0; i < book.getNumNames(); i++) {
283
        }
328
                	// Our formula will by now contain an upper-cased
284
329
                	//  version of any named range names
285
        for(int i = 0; i < book.getNumNames(); i++) {
330
                    if(book.getNameRecord(i).getNameText().toUpperCase().equals(name)) {
286
            // named range name matching is case insensitive
331
                        nameRecordExists = true;
287
            if(book.getNameRecord(i).getNameText().equalsIgnoreCase(name)) {
332
                    }
288
                return new NamePtg(name, book);
333
                }
334
                if(!nameRecordExists)
335
                    Abort("Found reference to named range \"" + name + "\", but that named range wasn't defined!");
336
                tokens.add(new NamePtg(name, book));
337
            }
289
            }
338
        }
290
        }
291
        throw new FormulaParseException("Found reference to named range \"" 
292
                    + name + "\", but that named range wasn't defined!");
339
    }
293
    }
340
    
294
341
    /**
295
    /**
342
     * Adds a pointer to the last token to the latest function argument list.
296
     * Adds a pointer to the last token to the latest function argument list.
343
     * @param obj
297
     * @param obj
344
     */
298
     */
345
    private void addArgumentPointer() {
299
    private void addArgumentPointer(List argumentPointers) {
346
		if (this.functionTokens.size() > 0) {
300
        argumentPointers.add(tokens.get(tokens.size()-1));
347
			//no bounds check because this method should not be called unless a token array is setup by function()
348
			List arguments = (List)this.functionTokens.get(0);
349
			arguments.add(tokens.get(tokens.size()-1));
350
		}
351
    }
301
    }
352
    
302
353
    private void function(String name) {
303
    /**
354
    	//average 2 args per function
304
     * Note - Excel function names are 'case aware but not case sensitive'.  This method may end
355
    	this.functionTokens.add(0, new ArrayList(2));
305
     * up creating a defined name record in the workbook if the specified name is not an internal
356
    	
306
     * Excel function, and has not been encountered before. 
307
     * 
308
     * @param name case preserved function name (as it was entered/appeared in the formula). 
309
     */
310
    private Ptg function(String name) {
311
        int numArgs =0 ;
312
        // Note regarding parameter - 
313
        if(!AbstractFunctionPtg.isInternalFunctionName(name)) {
314
            // external functions get a Name token which points to a defined name record
315
            NamePtg nameToken = new NamePtg(name, this.book);
316
            
317
            // in the token tree, the name is more or less the first argument
318
            numArgs++;  
319
            tokens.add(nameToken);
320
        }
321
        //average 2 args per function
322
        List argumentPointers = new ArrayList(2);
323
357
        Match('(');
324
        Match('(');
358
        int numArgs = Arguments();
325
        numArgs += Arguments(argumentPointers);
359
        Match(')');
326
        Match(')');
360
                
361
        AbstractFunctionPtg functionPtg = getFunction(name,(byte)numArgs);
362
        
363
		tokens.add(functionPtg);
364
 
365
        if (functionPtg.getName().equals("externalflag")) {
366
            tokens.add(new NamePtg(name, this.book));
367
        }
368
327
369
 		//remove what we just put in
328
        return getFunction(name, numArgs, argumentPointers);
370
		this.functionTokens.remove(0);
371
    }
329
    }
372
    
330
373
    /**
331
    /**
374
     * Adds the size of all the ptgs after the provided index (inclusive).
332
     * Adds the size of all the ptgs after the provided index (inclusive).
375
     * <p>
333
     * <p>
Lines 378-394 Link Here
378
     * @return int
336
     * @return int
379
     */
337
     */
380
    private int getPtgSize(int index) {
338
    private int getPtgSize(int index) {
381
    	int count = 0;
339
        int count = 0;
382
    	
340
383
    	Iterator ptgIterator = tokens.listIterator(index);
341
        Iterator ptgIterator = tokens.listIterator(index);
384
    	while (ptgIterator.hasNext()) {
342
        while (ptgIterator.hasNext()) {
385
    		Ptg ptg = (Ptg)ptgIterator.next();
343
            Ptg ptg = (Ptg)ptgIterator.next();
386
    		count+=ptg.getSize();
344
            count+=ptg.getSize();
387
    	}
345
        }
388
    	
346
389
    	return count;
347
        return count;
390
    }
348
    }
391
    
349
392
    private int getPtgSize(int start, int end) {
350
    private int getPtgSize(int start, int end) {
393
        int count = 0;
351
        int count = 0;
394
        int index = start;
352
        int index = start;
Lines 398-787 Link Here
398
            count+=ptg.getSize();
356
            count+=ptg.getSize();
399
            index++;
357
            index++;
400
        }
358
        }
401
        
359
402
        return count;
360
        return count;
403
    }
361
    }
404
    /**
362
    /**
405
     * Generates the variable function ptg for the formula.
363
     * Generates the variable function ptg for the formula.
406
     * <p>
364
     * <p>
407
     * For IF Formulas, additional PTGs are added to the tokens 
365
     * For IF Formulas, additional PTGs are added to the tokens
408
     * @param name
366
     * @param name
409
     * @param numArgs
367
     * @param numArgs
410
     * @return Ptg a null is returned if we're in an IF formula, it needs extreme manipulation and is handled in this function
368
     * @return Ptg a null is returned if we're in an IF formula, it needs extreme manipulation and is handled in this function
411
     */
369
     */
412
    private AbstractFunctionPtg getFunction(String name, byte numArgs) {
370
    private AbstractFunctionPtg getFunction(String name, int numArgs, List argumentPointers) {
413
        AbstractFunctionPtg retval = null;
371
414
        
372
        AbstractFunctionPtg retval = new FuncVarPtg(name, (byte)numArgs);
415
        if (name.equals("IF")) {
373
        if (!name.equals(AbstractFunctionPtg.FUNCTION_NAME_IF)) {
416
            retval = new FuncVarPtg(AbstractFunctionPtg.ATTR_NAME, numArgs);
374
            // early return for everything else besides IF()
417
            
375
            return retval;
418
            //simulated pop, no bounds checking because this list better be populated by function()
419
            List argumentPointers = (List)this.functionTokens.get(0);
420
            
421
            
422
            AttrPtg ifPtg = new AttrPtg();
423
            ifPtg.setData((short)7); //mirroring excel output
424
            ifPtg.setOptimizedIf(true);
425
            
426
            if (argumentPointers.size() != 2  && argumentPointers.size() != 3) {
427
                throw new IllegalArgumentException("["+argumentPointers.size()+"] Arguments Found - An IF formula requires 2 or 3 arguments. IF(CONDITION, TRUE_VALUE, FALSE_VALUE [OPTIONAL]");
428
            }
429
            
430
            //Biffview of an IF formula record indicates the attr ptg goes after the condition ptgs and are
431
            //tracked in the argument pointers
432
            //The beginning first argument pointer is the last ptg of the condition
433
            int ifIndex = tokens.indexOf(argumentPointers.get(0))+1;
434
            tokens.add(ifIndex, ifPtg);
435
            
436
            //we now need a goto ptgAttr to skip to the end of the formula after a true condition
437
            //the true condition is should be inserted after the last ptg in the first argument
438
            
439
            int gotoIndex = tokens.indexOf(argumentPointers.get(1))+1;
440
            
441
            AttrPtg goto1Ptg = new AttrPtg();
442
            goto1Ptg.setGoto(true);
443
            
444
            
445
            tokens.add(gotoIndex, goto1Ptg);
446
            
447
            
448
            if (numArgs > 2) { //only add false jump if there is a false condition
449
                
450
                //second goto to skip past the function ptg
451
                AttrPtg goto2Ptg = new AttrPtg();
452
                goto2Ptg.setGoto(true);
453
                goto2Ptg.setData((short)(retval.getSize()-1));
454
                //Page 472 of the Microsoft Excel Developer's kit states that:
455
                //The b(or w) field specifies the number byes (or words to skip, minus 1
456
                
457
                tokens.add(goto2Ptg); //this goes after all the arguments are defined
458
            }
459
            
460
            //data portion of the if ptg points to the false subexpression (Page 472 of MS Excel Developer's kit)
461
            //count the number of bytes after the ifPtg to the False Subexpression
462
            //doesn't specify -1 in the documentation
463
            ifPtg.setData((short)(getPtgSize(ifIndex+1, gotoIndex)));
464
            
465
            //count all the additional (goto) ptgs but dont count itself
466
            int ptgCount = this.getPtgSize(gotoIndex)-goto1Ptg.getSize()+retval.getSize();
467
            if (ptgCount > (int)Short.MAX_VALUE) {
468
                throw new RuntimeException("Ptg Size exceeds short when being specified for a goto ptg in an if");
469
            }
470
            
471
            goto1Ptg.setData((short)(ptgCount-1));
472
            
473
        } else {
474
            
475
            retval = new FuncVarPtg(name,numArgs);
476
        }
376
        }
477
        
377
378
379
        AttrPtg ifPtg = new AttrPtg();
380
        ifPtg.setData((short)7); //mirroring excel output
381
        ifPtg.setOptimizedIf(true);
382
383
        if (argumentPointers.size() != 2  && argumentPointers.size() != 3) {
384
            throw new IllegalArgumentException("["+argumentPointers.size()+"] Arguments Found - An IF formula requires 2 or 3 arguments. IF(CONDITION, TRUE_VALUE, FALSE_VALUE [OPTIONAL]");
385
        }
386
387
        //Biffview of an IF formula record indicates the attr ptg goes after the condition ptgs and are
388
        //tracked in the argument pointers
389
        //The beginning first argument pointer is the last ptg of the condition
390
        int ifIndex = tokens.indexOf(argumentPointers.get(0))+1;
391
        tokens.add(ifIndex, ifPtg);
392
393
        //we now need a goto ptgAttr to skip to the end of the formula after a true condition
394
        //the true condition is should be inserted after the last ptg in the first argument
395
396
        int gotoIndex = tokens.indexOf(argumentPointers.get(1))+1;
397
398
        AttrPtg goto1Ptg = new AttrPtg();
399
        goto1Ptg.setGoto(true);
400
401
402
        tokens.add(gotoIndex, goto1Ptg);
403
404
405
        if (numArgs > 2) { //only add false jump if there is a false condition
406
407
            //second goto to skip past the function ptg
408
            AttrPtg goto2Ptg = new AttrPtg();
409
            goto2Ptg.setGoto(true);
410
            goto2Ptg.setData((short)(retval.getSize()-1));
411
            //Page 472 of the Microsoft Excel Developer's kit states that:
412
            //The b(or w) field specifies the number byes (or words to skip, minus 1
413
414
            tokens.add(goto2Ptg); //this goes after all the arguments are defined
415
        }
416
417
        //data portion of the if ptg points to the false subexpression (Page 472 of MS Excel Developer's kit)
418
        //count the number of bytes after the ifPtg to the False Subexpression
419
        //doesn't specify -1 in the documentation
420
        ifPtg.setData((short)(getPtgSize(ifIndex+1, gotoIndex)));
421
422
        //count all the additional (goto) ptgs but dont count itself
423
        int ptgCount = this.getPtgSize(gotoIndex)-goto1Ptg.getSize()+retval.getSize();
424
        if (ptgCount > Short.MAX_VALUE) {
425
            throw new RuntimeException("Ptg Size exceeds short when being specified for a goto ptg in an if");
426
        }
427
428
        goto1Ptg.setData((short)(ptgCount-1));
429
478
        return retval;
430
        return retval;
479
    }
431
    }
432
433
    private static boolean isArgumentDelimiter(char ch) {
434
        return ch ==  ',' || ch == ')';
435
    }
480
    
436
    
481
    /** get arguments to a function */
437
    /** get arguments to a function */
482
    private int Arguments() {
438
    private int Arguments(List argumentPointers) {
439
        SkipWhite();
440
        if(look == ')') {
441
            return 0;
442
        }
443
        
444
        boolean missedPrevArg = true;
445
        
483
        int numArgs = 0;
446
        int numArgs = 0;
484
        if (look != ')')  {
447
        while(true) {
485
            numArgs++; 
448
            SkipWhite();
486
            Expression();
449
            if(isArgumentDelimiter(look)) {
487
			   addArgumentPointer();
450
                if(missedPrevArg) {
488
        }
451
                    tokens.add(new MissingArgPtg());
489
        while (look == ','  || look == ';') { //TODO handle EmptyArgs
452
                    addArgumentPointer(argumentPointers);
490
            if(look == ',') {
453
                    numArgs++;
491
              Match(',');
454
                }
455
                if(look == ')') {
456
                    break;
457
                }
458
                Match(',');
459
                missedPrevArg = true;
460
                continue;
492
            }
461
            }
493
            else {
462
            comparisonExpression();
494
              Match(';');
463
            addArgumentPointer(argumentPointers);
495
            }
496
            Expression();
497
			   addArgumentPointer();
498
            numArgs++;
464
            numArgs++;
465
            missedPrevArg = false;
499
        }
466
        }
500
        return numArgs;
467
        return numArgs;
501
    }
468
    }
502
469
503
   /** Parse and Translate a Math Factor  */
470
   /** Parse and Translate a Math Factor  */
504
    private void Factor() {
471
    private void powerFactor() {
505
    	if (look == '-')
472
        percentFactor();
506
    	{
473
        while(true) {
507
    		Match('-');
474
            SkipWhite();
508
    		Factor();
475
            if(look != '^') {
509
    		tokens.add(new UnaryMinusPtg());
476
                return;
510
    	}
477
            }
511
        else if (look == '+') {
478
            Match('^');
512
            Match('+');
479
            percentFactor();
513
            Factor();
480
            tokens.add(new PowerPtg());
514
            tokens.add(new UnaryPlusPtg());
515
        }
481
        }
516
        else if (look == '(' ) {
482
    }
517
            Match('(');
483
    
518
            Expression();
484
    private void percentFactor() {
519
            Match(')');
485
        tokens.add(parseSimpleFactor());
520
            tokens.add(new ParenthesisPtg());
486
        while(true) {
521
        } else if (IsAlpha(look) || look == '\''){
487
            SkipWhite();
522
            Ident();
488
            if(look != '%') {
523
        } else if(look == '"') {
489
                return;
524
           StringLiteral();
525
        } else if (look == ')' || look == ',') {
526
        	tokens.add(new MissingArgPtg());
527
        } else {
528
            String number2 = null;
529
            String exponent = null;
530
            String number1 = GetNum();
531
            
532
            if (look == '.') {
533
                GetChar();
534
                number2 = GetNum();
535
            }
490
            }
536
            
491
            Match('%');
537
            if (look == 'E') {
492
            tokens.add(new PercentPtg());
493
        }
494
    }
495
    
496
    
497
    /**
498
     * factors (without ^ or % )
499
     */
500
    private Ptg parseSimpleFactor() {
501
        SkipWhite();
502
        switch(look) {
503
            case '#':
504
                return parseErrorLiteral();
505
            case '-':
506
                Match('-');
507
                powerFactor();
508
                return new UnaryMinusPtg();
509
            case '+':
510
                Match('+');
511
                powerFactor();
512
                return new UnaryPlusPtg();
513
            case '(':
514
                Match('(');
515
                comparisonExpression();
516
                Match(')');
517
                return new ParenthesisPtg();
518
            case '"':
519
                return parseStringLiteral();
520
            case ',':
521
            case ')':
522
                return new MissingArgPtg(); // TODO - not quite the right place to recognise a missing arg
523
        }
524
        if (IsAlpha(look) || look == '\''){
525
            return parseIdent();
526
        }
527
        // else - assume number
528
        return parseNumber();
529
    }
530
531
532
    private Ptg parseNumber() {
533
        String number2 = null;
534
        String exponent = null;
535
        String number1 = GetNum();
536
537
        if (look == '.') {
538
            GetChar();
539
            number2 = GetNum();
540
        }
541
542
        if (look == 'E') {
543
            GetChar();
544
545
            String sign = "";
546
            if (look == '+') {
538
                GetChar();
547
                GetChar();
539
                
548
            } else if (look == '-') {
540
                String sign = "";
549
                GetChar();
541
                if (look == '+') {
550
                sign = "-";
542
                    GetChar();
543
                } else if (look == '-') {
544
                    GetChar();
545
                    sign = "-";
546
                }
547
                
548
                String number = GetNum();
549
                if (number == null) {
550
                    Expected("Integer");
551
                }
552
                exponent = sign + number;
553
            }
551
            }
554
            
552
555
            if (number1 == null && number2 == null) {
553
            String number = GetNum();
556
                Expected("Integer");
554
            if (number == null) {
555
                throw expected("Integer");
557
            }
556
            }
558
            
557
            exponent = sign + number;
559
            tokens.add(getNumberPtgFromString(number1, number2, exponent));
560
        }
558
        }
559
560
        if (number1 == null && number2 == null) {
561
            throw expected("Integer");
562
        }
563
564
        return getNumberPtgFromString(number1, number2, exponent);
561
    }
565
    }
562
    
566
563
	/** 
567
564
	 * Get a PTG for an integer from its string representation. 
568
    private ErrPtg parseErrorLiteral() {
565
	 * return Int or Number Ptg based on size of input
569
        Match('#');
566
	 */
570
        String part1 = GetName().toUpperCase();
567
	private Ptg getNumberPtgFromString(String number1, String number2, String exponent) {
571
572
        switch(part1.charAt(0)) {
573
            case 'V':
574
                if(part1.equals("VALUE")) {
575
                    Match('!');
576
                    return ErrPtg.VALUE_INVALID;
577
                }
578
                throw expected("#VALUE!");
579
            case 'R':
580
                if(part1.equals("REF")) {
581
                    Match('!');
582
                    return ErrPtg.REF_INVALID;
583
                }
584
                throw expected("#REF!");
585
            case 'D':
586
                if(part1.equals("DIV")) {
587
                    Match('/');
588
                    Match('0');
589
                    Match('!');
590
                    return ErrPtg.DIV_ZERO;
591
                }
592
                throw expected("#DIV/0!");
593
            case 'N':
594
                if(part1.equals("NAME")) {
595
                    Match('?');  // only one that ends in '?'
596
                    return ErrPtg.NAME_INVALID;
597
                }
598
                if(part1.equals("NUM")) {
599
                    Match('!');
600
                    return ErrPtg.NUM_ERROR;
601
                }
602
                if(part1.equals("NULL")) {
603
                    Match('!');
604
                    return ErrPtg.NULL_INTERSECTION;
605
                }
606
                if(part1.equals("N")) {
607
                    Match('/');
608
                    if(look != 'A' && look != 'a') {
609
                        throw expected("#N/A");
610
                    }
611
                    Match(look);
612
                    // Note - no '!' or '?' suffix
613
                    return ErrPtg.N_A;
614
                }
615
                throw expected("#NAME?, #NUM!, #NULL! or #N/A");
616
617
        }
618
        throw expected("#VALUE!, #REF!, #DIV/0!, #NAME?, #NUM!, #NULL! or #N/A");
619
    }
620
621
622
    /**
623
     * Get a PTG for an integer from its string representation.
624
     * return Int or Number Ptg based on size of input
625
     */
626
    private static Ptg getNumberPtgFromString(String number1, String number2, String exponent) {
568
        StringBuffer number = new StringBuffer();
627
        StringBuffer number = new StringBuffer();
569
        
628
570
	    if (number2 == null) {
629
        if (number2 == null) {
571
	        number.append(number1);
630
            number.append(number1);
572
    	    
631
573
    	    if (exponent != null) {
632
            if (exponent != null) {
574
    	        number.append('E');
633
                number.append('E');
575
    	        number.append(exponent);
634
                number.append(exponent);
576
    	    }
635
            }
577
    	    
636
578
            String numberStr = number.toString();
637
            String numberStr = number.toString();
579
            
638
            int intVal;
580
            try {
639
            try {
581
                return new IntPtg(numberStr);
640
                intVal = Integer.parseInt(numberStr);
582
            } catch (NumberFormatException e) {
641
            } catch (NumberFormatException e) {
583
                return new NumberPtg(numberStr);
642
                return new NumberPtg(numberStr);
584
            }
643
            }
585
	    } else {
644
            if (IntPtg.isInRange(intVal)) {
586
            if (number1 != null) {
645
                return new IntPtg(intVal);
587
                number.append(number1);
588
            }
646
            }
589
            
647
            return new NumberPtg(numberStr);
590
            number.append('.');
648
        }
591
            number.append(number2);
649
592
            
650
        if (number1 != null) {
593
            if (exponent != null) {
651
            number.append(number1);
594
                number.append('E');
652
        }
595
                number.append(exponent);
653
596
            }
654
        number.append('.');
597
            
655
        number.append(number2);
598
            return new NumberPtg(number.toString());
656
599
	    }
657
        if (exponent != null) {
600
	}
658
            number.append('E');
601
	
659
            number.append(exponent);
602
	
660
        }
603
	private void StringLiteral() 
661
604
	{
662
        return new NumberPtg(number.toString());
605
		// Can't use match here 'cuz it consumes whitespace
606
		// which we need to preserve inside the string.
607
		// - pete
608
		// Match('"');
609
		if (look != '"')
610
			Expected("\"");
611
		else
612
		{
613
			GetChar();
614
			StringBuffer Token = new StringBuffer();
615
			for (;;)
616
			{
617
				if (look == '"')
618
				{
619
					GetChar();
620
					SkipWhite(); //potential white space here since it doesnt matter up to the operator
621
					if (look == '"')
622
						Token.append("\"");
623
					else
624
						break;
625
				}
626
				else if (look == 0)
627
				{
628
					break;
629
				}
630
				else
631
				{
632
					Token.append(look);
633
					GetChar();
634
				}
635
			}
636
			tokens.add(new StringPtg(Token.toString()));
637
		}
638
	}
639
    
640
    /** Recognize and Translate a Multiply */
641
    private void Multiply(){
642
        Match('*');
643
        Factor();
644
        tokens.add(new MultiplyPtg());
645
  
646
    }
663
    }
647
    
648
    
649
    /** Recognize and Translate a Divide */
650
    private void Divide() {
651
        Match('/');
652
        Factor();
653
        tokens.add(new DividePtg());
654
664
665
666
    private StringPtg parseStringLiteral()
667
    {
668
        Match('"');
669
        
670
        StringBuffer token = new StringBuffer();
671
        while (true) {
672
            if (look == '"') {
673
                GetChar();
674
                if (look != '"') {
675
                    break;
676
                }
677
             }
678
            token.append(look);
679
            GetChar();
680
        }
681
        return new StringPtg(token.toString());
655
    }
682
    }
656
    
683
657
    
658
    /** Parse and Translate a Math Term */
684
    /** Parse and Translate a Math Term */
659
    private void  Term(){
685
    private void  Term() {
660
        Factor();
686
        powerFactor();
661
		 while (look == '*' || look == '/' || look == '^' || look == '&') {
687
        while(true) {
662
        
688
            SkipWhite();
663
            ///TODO do we need to do anything here??
689
            switch(look) {
664
            if (look == '*') Multiply();
690
                case '*':
665
            else if (look == '/') Divide();
691
                    Match('*');
666
            else if (look == '^') Power();
692
                    powerFactor();
667
            else if (look == '&') Concat();
693
                    tokens.add(new MultiplyPtg());
694
                    continue;
695
                case '/':
696
                    Match('/');
697
                    powerFactor();
698
                    tokens.add(new DividePtg());
699
                    continue;
700
            }
701
            return; // finished with Term
668
        }
702
        }
669
    }
703
    }
670
    
704
    
671
    /** Recognize and Translate an Add */
705
    private void comparisonExpression() {
672
    private void Add() {
706
        concatExpression();
673
        Match('+');
707
        while (true) {
674
        Term();
708
            SkipWhite();
675
        tokens.add(new AddPtg());
709
            switch(look) {
710
                case '=':
711
                case '>':
712
                case '<':
713
                    Ptg comparisonToken = getComparisonToken();
714
                    concatExpression();
715
                    tokens.add(comparisonToken);
716
                    continue;
717
            }
718
            return; // finished with predicate expression
719
        }
676
    }
720
    }
677
    
721
678
    /** Recognize and Translate a Concatination */
722
    private Ptg getComparisonToken() {
679
    private void Concat() {
723
        if(look == '=') {
680
        Match('&');
724
            Match(look);
681
        Term();
725
            return new EqualPtg();
682
        tokens.add(new ConcatPtg());
726
        }
727
        boolean isGreater = look == '>';
728
        Match(look);
729
        if(isGreater) {
730
            if(look == '=') {
731
                Match('=');
732
                return new GreaterEqualPtg();
733
            }
734
            return new GreaterThanPtg();
735
        }
736
        switch(look) {
737
            case '=':
738
                Match('=');
739
                return new LessEqualPtg();
740
            case '>':
741
                Match('>');
742
                return new NotEqualPtg();
743
        }
744
        return new LessThanPtg();
683
    }
745
    }
684
    
746
    
685
    /** Recognize and Translate a test for Equality  */
747
686
    private void Equal() {
748
    private void concatExpression() {
687
        Match('=');
749
        additiveExpression();
688
        Expression();
750
        while (true) {
689
        tokens.add(new EqualPtg());
751
            SkipWhite();
752
            if(look != '&') {
753
                break; // finished with concat expression
754
            }
755
            Match('&');
756
            additiveExpression();
757
            tokens.add(new ConcatPtg());
758
        }
690
    }
759
    }
691
    
760
    
692
    /** Recognize and Translate a Subtract */
693
    private void Subtract() {
694
        Match('-');
695
        Term();
696
        tokens.add(new SubtractPtg());
697
    }    
698
761
699
    private void Power() {
700
        Match('^');
701
        Term();
702
        tokens.add(new PowerPtg());
703
    }
704
    
705
    
706
    /** Parse and Translate an Expression */
762
    /** Parse and Translate an Expression */
707
    private void Expression() {
763
    private void additiveExpression() {
708
        Term();
764
        Term();
709
        while (IsAddop(look)) {
765
        while (true) {
710
            if (look == '+' )  Add();
766
            SkipWhite();
711
            else if (look == '-') Subtract();
767
            switch(look) {
768
                case '+':
769
                    Match('+');
770
                    Term();
771
                    tokens.add(new AddPtg());
772
                    continue;
773
                case '-':
774
                    Match('-');
775
                    Term();
776
                    tokens.add(new SubtractPtg());
777
                    continue;
778
            }
779
            return; // finished with additive expression
712
        }
780
        }
713
        
714
		/*
715
		 * This isn't quite right since it would allow multiple comparison operators.
716
		 */
717
		
718
		  if(look == '=' || look == '>' || look == '<') {
719
		  		if (look == '=') Equal();
720
		      else if (look == '>') GreaterThan();
721
		      else if (look == '<') LessThan();
722
		      return;
723
		  }        
724
        
725
        
726
    }
781
    }
727
    
728
    /** Recognize and Translate a Greater Than  */
729
    private void GreaterThan() {
730
		Match('>');
731
		if(look == '=')
732
		    GreaterEqual();
733
		else {
734
		    Expression();
735
		    tokens.add(new GreaterThanPtg());
736
		}
737
    }
738
    
739
    /** Recognize and Translate a Less Than  */
740
    private void LessThan() {
741
		Match('<');
742
		if(look == '=')
743
		    LessEqual();
744
		else if(look == '>')
745
		    NotEqual();
746
		else {
747
		    Expression();
748
		    tokens.add(new LessThanPtg());
749
		}
750
782
751
	}  
752
   
753
   /**
754
    * Recognize and translate Greater than or Equal
755
    *
756
    */ 
757
	private void GreaterEqual() {
758
	    Match('=');
759
	    Expression();
760
	    tokens.add(new GreaterEqualPtg());
761
	}    
762
763
	/**
764
	 * Recognize and translate Less than or Equal
765
	 *
766
	 */ 
767
768
	private void LessEqual() {
769
	    Match('=');
770
	    Expression();
771
	    tokens.add(new LessEqualPtg());
772
	}
773
	
774
	/**
775
	 * Recognize and not Equal
776
	 *
777
	 */ 
778
779
	private void NotEqual() {
780
	    Match('>');
781
	    Expression();
782
	    tokens.add(new NotEqualPtg());
783
	}    
784
    
785
    //{--------------------------------------------------------------}
783
    //{--------------------------------------------------------------}
786
    //{ Parse and Translate an Assignment Statement }
784
    //{ Parse and Translate an Assignment Statement }
787
    /**
785
    /**
Lines 794-841 Link Here
794
792
795
end;
793
end;
796
     **/
794
     **/
797
    
795
798
 
796
799
    /** Initialize */
800
    
801
    private void  init() {
802
        GetChar();
803
        SkipWhite();
804
    }
805
    
806
    /** API call to execute the parsing of the formula
797
    /** API call to execute the parsing of the formula
807
     *
798
     *
808
     */
799
     */
809
    public void parse() {
800
    public void parse() {
810
        synchronized (tokens) {
801
        pointer=0;
811
            init();
802
        GetChar();
812
            Expression();
803
        comparisonExpression();
804
805
        if(pointer <= formulaLength) {
806
            String msg = "Unused input [" + formulaString.substring(pointer-1) 
807
                + "] after attempting to parse the formula [" + formulaString + "]"; 
808
            throw new FormulaParseException(msg);
813
        }
809
        }
814
    }
810
    }
815
    
811
816
    
812
817
    /*********************************
813
    /*********************************
818
     * PARSER IMPLEMENTATION ENDS HERE
814
     * PARSER IMPLEMENTATION ENDS HERE
819
     * EXCEL SPECIFIC METHODS BELOW
815
     * EXCEL SPECIFIC METHODS BELOW
820
     *******************************/
816
     *******************************/
821
    
817
822
    /** API call to retrive the array of Ptgs created as 
818
    /** API call to retrive the array of Ptgs created as
823
     * a result of the parsing
819
     * a result of the parsing
824
     */
820
     */
825
    public Ptg[] getRPNPtg() {
821
    public Ptg[] getRPNPtg() {
826
     return getRPNPtg(FORMULA_TYPE_CELL);
822
     return getRPNPtg(FORMULA_TYPE_CELL);
827
    }
823
    }
828
    
824
829
    public Ptg[] getRPNPtg(int formulaType) {
825
    public Ptg[] getRPNPtg(int formulaType) {
830
        Node node = createTree();
826
        Node node = createTree();
831
        setRootLevelRVA(node, formulaType);
827
        setRootLevelRVA(node, formulaType);
832
        setParameterRVA(node,formulaType);
828
        setParameterRVA(node,formulaType);
833
        return (Ptg[]) tokens.toArray(new Ptg[0]);
829
        return (Ptg[]) tokens.toArray(new Ptg[0]);
834
    }
830
    }
835
    
831
836
    private void setRootLevelRVA(Node n, int formulaType) {
832
    private void setRootLevelRVA(Node n, int formulaType) {
837
        //Pg 16, excelfileformat.pdf @ openoffice.org
833
        //Pg 16, excelfileformat.pdf @ openoffice.org
838
        Ptg p = (Ptg) n.getValue();
834
        Ptg p = n.getValue();
839
            if (formulaType == FormulaParser.FORMULA_TYPE_NAMEDRANGE) {
835
            if (formulaType == FormulaParser.FORMULA_TYPE_NAMEDRANGE) {
840
                if (p.getDefaultOperandClass() == Ptg.CLASS_REF) {
836
                if (p.getDefaultOperandClass() == Ptg.CLASS_REF) {
841
                    setClass(n,Ptg.CLASS_REF);
837
                    setClass(n,Ptg.CLASS_REF);
Lines 845-853 Link Here
845
            } else {
841
            } else {
846
                setClass(n,Ptg.CLASS_VALUE);
842
                setClass(n,Ptg.CLASS_VALUE);
847
            }
843
            }
848
        
844
849
    }
845
    }
850
    
846
851
    private void setParameterRVA(Node n, int formulaType) {
847
    private void setParameterRVA(Node n, int formulaType) {
852
        Ptg p = n.getValue();
848
        Ptg p = n.getValue();
853
        int numOperands = n.getNumChildren();
849
        int numOperands = n.getNumChildren();
Lines 863-873 Link Here
863
            for (int i =0;i<numOperands;i++) {
859
            for (int i =0;i<numOperands;i++) {
864
                setParameterRVA(n.getChild(i),formulaType);
860
                setParameterRVA(n.getChild(i),formulaType);
865
            }
861
            }
866
        } 
862
        }
867
    }
863
    }
868
    private void setParameterRVA(Node n, int expectedClass,int formulaType) {
864
    private void setParameterRVA(Node n, int expectedClass,int formulaType) {
869
        Ptg p = (Ptg) n.getValue();
865
        Ptg p = n.getValue();
870
        if (expectedClass == Ptg.CLASS_REF) { //pg 15, table 1 
866
        if (expectedClass == Ptg.CLASS_REF) { //pg 15, table 1
871
            if (p.getDefaultOperandClass() == Ptg.CLASS_REF ) {
867
            if (p.getDefaultOperandClass() == Ptg.CLASS_REF ) {
872
                setClass(n, Ptg.CLASS_REF);
868
                setClass(n, Ptg.CLASS_REF);
873
            }
869
            }
Lines 887-893 Link Here
887
            } else {
883
            } else {
888
                setClass(n,Ptg.CLASS_VALUE);
884
                setClass(n,Ptg.CLASS_VALUE);
889
            }
885
            }
890
        } else { //Array class, pg 16. 
886
        } else { //Array class, pg 16.
891
            if (p.getDefaultOperandClass() == Ptg.CLASS_VALUE &&
887
            if (p.getDefaultOperandClass() == Ptg.CLASS_VALUE &&
892
                 (formulaType==FORMULA_TYPE_CELL || formulaType == FORMULA_TYPE_SHARED)) {
888
                 (formulaType==FORMULA_TYPE_CELL || formulaType == FORMULA_TYPE_SHARED)) {
893
                 setClass(n,Ptg.CLASS_VALUE);
889
                 setClass(n,Ptg.CLASS_VALUE);
Lines 896-904 Link Here
896
            }
892
            }
897
        }
893
        }
898
    }
894
    }
899
    
895
900
     private void setClass(Node n, byte theClass) {
896
     private void setClass(Node n, byte theClass) {
901
        Ptg p = (Ptg) n.getValue();
897
        Ptg p = n.getValue();
902
        if (p instanceof AbstractFunctionPtg || !(p instanceof OperationPtg)) {
898
        if (p instanceof AbstractFunctionPtg || !(p instanceof OperationPtg)) {
903
            p.setClass(theClass);
899
            p.setClass(theClass);
904
        } else {
900
        } else {
Lines 909-915 Link Here
909
     }
905
     }
910
    /**
906
    /**
911
     * Convience method which takes in a list then passes it to the
907
     * Convience method which takes in a list then passes it to the
912
     *  other toFormulaString signature. 
908
     *  other toFormulaString signature.
913
     * @param book   workbook for 3D and named references
909
     * @param book   workbook for 3D and named references
914
     * @param lptgs  list of Ptg, can be null or empty
910
     * @param lptgs  list of Ptg, can be null or empty
915
     * @return a human readable String
911
     * @return a human readable String
Lines 930-938 Link Here
930
     * @return a human readable String
926
     * @return a human readable String
931
     */
927
     */
932
    public String toFormulaString(List lptgs) {
928
    public String toFormulaString(List lptgs) {
933
    	return toFormulaString(book, lptgs);
929
        return toFormulaString(book, lptgs);
934
    }
930
    }
935
    
931
936
    /**
932
    /**
937
     * Static method to convert an array of Ptgs in RPN order
933
     * Static method to convert an array of Ptgs in RPN order
938
     * to a human readable string format in infix mode.
934
     * to a human readable string format in infix mode.
Lines 941-1010 Link Here
941
     * @return a human readable String
937
     * @return a human readable String
942
     */
938
     */
943
    public static String toFormulaString(Workbook book, Ptg[] ptgs) {
939
    public static String toFormulaString(Workbook book, Ptg[] ptgs) {
944
        if (ptgs == null || ptgs.length == 0) return "#NAME";
940
        if (ptgs == null || ptgs.length == 0) {
945
        java.util.Stack stack = new java.util.Stack();
941
            // TODO - what is the justification for returning "#NAME" (which is not "#NAME?", btw)
946
        AttrPtg ifptg = null;
942
            return "#NAME";
943
        }
944
        Stack stack = new Stack();
947
945
948
           // Excel allows to have AttrPtg at position 0 (such as Blanks) which
946
           // Excel allows to have AttrPtg at position 0 (such as Blanks) which
949
           // do not have any operands. Skip them.
947
           // do not have any operands. Skip them.
950
        int i;
948
        int i;
951
        if(ptgs[0] instanceof AttrPtg) {
949
        if(ptgs[0] instanceof AttrPtg) {
952
        	// TODO -this requirement is unclear and is not addressed by any junits
950
            AttrPtg attrPtg0 = (AttrPtg) ptgs[0];
953
        	stack.push(ptgs[0].toFormulaString(book));
951
            if(attrPtg0.isSemiVolatile()) {
954
        	i=1;
952
                // no visible formula for semi-volatile
953
            } else {
954
                // TODO -this requirement is unclear and is not addressed by any junits
955
                stack.push(ptgs[0].toFormulaString(book));
956
            }
957
            i=1;
955
        } else {
958
        } else {
956
        	i=0;
959
            i=0;
957
        }
960
        }
958
                  
961
959
        for ( ; i < ptgs.length; i++) {
962
        for ( ; i < ptgs.length; i++) {
960
            Ptg ptg = ptgs[i];
963
            Ptg ptg = ptgs[i];
964
            // TODO - what about MemNoMemPtg?
965
            if(ptg instanceof MemAreaPtg || ptg instanceof MemFuncPtg || ptg instanceof MemErrPtg) {
966
                // marks the start of a list of area expressions which will be naturally combined
967
                // by their trailing operators (e.g. UnionPtg)
968
                // TODO - put comment and throw exception in toFormulaString() of these classes
969
                continue;
970
            }
961
            if (! (ptg instanceof OperationPtg)) {
971
            if (! (ptg instanceof OperationPtg)) {
962
                stack.push(ptg.toFormulaString(book));
972
                stack.push(ptg.toFormulaString(book));
963
                continue;
973
                continue;
964
            }
974
            }
965
                      
975
966
            if (ptg instanceof AttrPtg && ((AttrPtg) ptg).isOptimizedIf()) {
976
            if (ptg instanceof AttrPtg && ((AttrPtg) ptg).isOptimizedIf()) {
967
                ifptg = (AttrPtg) ptg;
968
                continue;
977
                continue;
969
            }
978
            }
970
                      
979
971
            final OperationPtg o = (OperationPtg) ptg;
980
            final OperationPtg o = (OperationPtg) ptg;
972
            final String[] operands = new String[o.getNumberOfOperands()];
981
            int nOperands = o.getNumberOfOperands();
982
            final String[] operands = new String[nOperands];
973
983
974
            for (int j = operands.length; j > 0; j--) {
984
            for (int j = nOperands-1; j >= 0; j--) {
975
                //TODO: catch stack underflow and throw parse exception.
985
                if(stack.isEmpty()) {
976
                operands[j - 1] = (String) stack.pop();
986
                    //TODO: write junit to prove this works
977
                      }  
987
                   String msg = "Too few arguments suppled to operation token ("
978
988
                        + o.getClass().getName() + "). Expected (" + nOperands
989
                        + " but got " + (nOperands - j + 1);
990
                    throw new FormulaParseException(msg);
991
                }
992
                operands[j] = (String) stack.pop();
993
            }
979
            stack.push(o.toFormulaString(operands));
994
            stack.push(o.toFormulaString(operands));
980
            if (!(o instanceof AbstractFunctionPtg)) continue;
981
982
            final AbstractFunctionPtg f = (AbstractFunctionPtg) o;
983
            final String fname = f.getName();
984
            if (fname == null) continue;
985
986
            if ((ifptg != null) && (fname.equals("specialflag"))) {
987
                             // this special case will be way different.
988
                stack.push(ifptg.toFormulaString(new String[]{(String) stack.pop()}));
989
                continue;
990
                      }
991
            if (fname.equals("externalflag")) {
992
                final String top = (String) stack.pop();
993
                final int paren = top.indexOf('(');
994
                final int comma = top.indexOf(',');
995
                if (comma == -1) {
996
                    final int rparen = top.indexOf(')');
997
                    stack.push(top.substring(paren + 1, rparen) + "()");
998
                  }
999
                else {
1000
                    stack.push(top.substring(paren + 1, comma) + '(' +
1001
                               top.substring(comma + 1));
1002
            }
1003
        }
995
        }
996
        if(stack.isEmpty()) {
997
            // inspection of the code above reveals that every stack.pop() is followed by a 
998
            // stack.push(). So this is either an internal error or impossible.
999
            throw new IllegalStateException("Stack underflow");
1000
        }
1001
        String result = (String) stack.pop();
1002
        if(!stack.isEmpty()) {
1003
            // Might be caused by some tokens like AttrPtg and Mem*Ptg, which really shouldn't
1004
            // put anything on the stack
1005
            throw new IllegalStateException("too much stuff left on the stack");
1006
        }
1007
        return result;
1004
    }
1008
    }
1005
        // TODO: catch stack underflow and throw parse exception.
1006
        return (String) stack.pop();
1007
    }
1008
    /**
1009
    /**
1009
     * Static method to convert an array of Ptgs in RPN order
1010
     * Static method to convert an array of Ptgs in RPN order
1010
     *  to a human readable string format in infix mode. Works
1011
     *  to a human readable string format in infix mode. Works
Lines 1013-1019 Link Here
1013
     * @return a human readable String
1014
     * @return a human readable String
1014
     */
1015
     */
1015
    public String toFormulaString(Ptg[] ptgs) {
1016
    public String toFormulaString(Ptg[] ptgs) {
1016
    	return toFormulaString(book, ptgs);
1017
        return toFormulaString(book, ptgs);
1017
    }
1018
    }
1018
1019
1019
1020
Lines 1021-1039 Link Here
1021
     *used to run the class(RVA) change algo
1022
     *used to run the class(RVA) change algo
1022
     */
1023
     */
1023
    private Node createTree() {
1024
    private Node createTree() {
1024
        java.util.Stack stack = new java.util.Stack();
1025
        Stack stack = new Stack();
1025
        int numPtgs = tokens.size();
1026
        int numPtgs = tokens.size();
1026
        OperationPtg o;
1027
        OperationPtg o;
1027
        int numOperands;
1028
        int numOperands;
1028
        Node[] operands;
1029
        Node[] operands;
1029
        for (int i=0;i<numPtgs;i++) {
1030
        for (int i=0;i<numPtgs;i++) {
1030
            if (tokens.get(i) instanceof OperationPtg) {
1031
            if (tokens.get(i) instanceof OperationPtg) {
1031
                
1032
1032
                o = (OperationPtg) tokens.get(i);
1033
                o = (OperationPtg) tokens.get(i);
1033
                numOperands = o.getNumberOfOperands();
1034
                numOperands = o.getNumberOfOperands();
1034
                operands = new Node[numOperands];
1035
                operands = new Node[numOperands];
1035
                for (int j=0;j<numOperands;j++) {
1036
                for (int j=0;j<numOperands;j++) {
1036
                    operands[numOperands-j-1] = (Node) stack.pop(); 
1037
                    operands[numOperands-j-1] = (Node) stack.pop();
1037
                }
1038
                }
1038
                Node result = new Node(o);
1039
                Node result = new Node(o);
1039
                result.setChildren(operands);
1040
                result.setChildren(operands);
Lines 1044-1050 Link Here
1044
        }
1045
        }
1045
        return (Node) stack.pop();
1046
        return (Node) stack.pop();
1046
    }
1047
    }
1047
   
1048
1048
    /** toString on the parser instance returns the RPN ordered list of tokens
1049
    /** toString on the parser instance returns the RPN ordered list of tokens
1049
     *   Useful for testing
1050
     *   Useful for testing
1050
     */
1051
     */
Lines 1053-1073 Link Here
1053
           for (int i=0;i<tokens.size();i++) {
1054
           for (int i=0;i<tokens.size();i++) {
1054
            buf.append( ( (Ptg)tokens.get(i)).toFormulaString(book));
1055
            buf.append( ( (Ptg)tokens.get(i)).toFormulaString(book));
1055
            buf.append(' ');
1056
            buf.append(' ');
1056
        } 
1057
        }
1057
        return buf.toString();
1058
        return buf.toString();
1058
    }
1059
    }
1059
    
1060
1060
}   
1061
    /** Private helper class, used to create a tree representation of the formula*/
1061
    /** Private helper class, used to create a tree representation of the formula*/
1062
    class Node {
1062
    private static final class Node {
1063
        private Ptg value=null;
1063
        private Ptg value=null;
1064
        private Node[] children=new Node[0];
1064
        private Node[] children=new Node[0];
1065
        private int numChild=0;
1065
        private int numChild=0;
1066
        public Node(Ptg val) {
1066
        public Node(Ptg val) {
1067
            value = val; 
1067
            value = val;
1068
        }
1068
        }
1069
        public void setChildren(Node[] child) {children = child;numChild=child.length;}
1069
        public void setChildren(Node[] child) {children = child;numChild=child.length;}
1070
        public int getNumChildren() {return numChild;}
1070
        public int getNumChildren() {return numChild;}
1071
        public Node getChild(int number) {return children[number];}
1071
        public Node getChild(int number) {return children[number];}
1072
        public Ptg getValue() {return value;}
1072
        public Ptg getValue() {return value;}
1073
    }
1073
    }
1074
}
(-)src/java/org/apache/poi/hssf/record/formula/AbstractFunctionPtg.java (-26 / +54 lines)
Lines 29-38 Link Here
29
 * @author Andrew C. Oliver (acoliver at apache dot org)
29
 * @author Andrew C. Oliver (acoliver at apache dot org)
30
 */
30
 */
31
public abstract class AbstractFunctionPtg extends OperationPtg {
31
public abstract class AbstractFunctionPtg extends OperationPtg {
32
	//constant used allow a ptgAttr to be mapped properly for its functionPtg
32
33
	public static final String ATTR_NAME = "specialflag";
33
    /**
34
	    
34
     * The name of the IF function (i.e. "IF").  Extracted as a constant for clarity.
35
    public static final short INDEX_EXTERNAL = 255;
35
     */ 
36
    public static final String FUNCTION_NAME_IF = "IF";
37
    /** All external functions have function index 255 */
38
    private static final short FUNCTION_INDEX_EXTERNAL = 255;
36
    
39
    
37
    private static BinaryTree map = produceHash(); 
40
    private static BinaryTree map = produceHash(); 
38
    protected static Object[][] functionData = produceFunctionData();
41
    protected static Object[][] functionData = produceFunctionData();
Lines 66-71 Link Here
66
    public String getName() {
69
    public String getName() {
67
        return lookupName(field_2_fnc_index);
70
        return lookupName(field_2_fnc_index);
68
    }
71
    }
72
    /**
73
     * external functions get some special processing
74
     * @return <code>true</code> if this is an external function
75
     */
76
    public boolean isExternalFunction() {
77
        return field_2_fnc_index == FUNCTION_INDEX_EXTERNAL;
78
    }
69
    
79
    
70
    public String toFormulaString(Workbook book) {
80
    public String toFormulaString(Workbook book) {
71
        return getName();
81
        return getName();
Lines 73-111 Link Here
73
    
83
    
74
    public String toFormulaString(String[] operands) {
84
    public String toFormulaString(String[] operands) {
75
        StringBuffer buf = new StringBuffer();        
85
        StringBuffer buf = new StringBuffer();        
76
          
86
        
77
          if (field_2_fnc_index != 1) {
87
        if(isExternalFunction()) {
78
              buf.append(getName());
88
            buf.append(operands[0]); // first operand is actually the function name
79
              buf.append('(');
89
            appendArgs(buf, 1, operands);
80
          }
90
        } else {
81
          if (operands.length >0) {
91
            buf.append(getName());
82
              for (int i=0;i<operands.length;i++) {
92
            appendArgs(buf, 0, operands);
83
                  buf.append(operands[i]);
93
        }
84
                  buf.append(',');
85
              }
86
              buf.deleteCharAt(buf.length()-1);
87
          }
88
          if (field_2_fnc_index != 1) {
89
            buf.append(")");
90
          }
91
        return buf.toString();
94
        return buf.toString();
92
    }
95
    }
96
97
    private static void appendArgs(StringBuffer buf, int firstArgIx, String[] operands) {
98
        buf.append('(');
99
        for (int i=firstArgIx;i<operands.length;i++) {
100
            if (i>firstArgIx) {
101
                buf.append(',');
102
            }
103
            buf.append(operands[i]);
104
        }
105
        buf.append(")");
106
    }
93
    
107
    
94
    public abstract void writeBytes(byte[] array, int offset);
108
    public abstract void writeBytes(byte[] array, int offset);
95
    public abstract int getSize();
109
    public abstract int getSize();
96
    
110
    
97
   
111
   
112
    /**
113
     * Used to detect whether a function name found in a formula is one of the standard excel functions
114
     * <p>
115
     * The name matching is case insensitive.
116
     * @return <code>true</code> if the name specifies a standard worksheet function, 
117
     *  <code>false</code> if the name should be assumed to be an external function.
118
     */
119
    public static final boolean isInternalFunctionName(String name) {
120
        return map.containsValue(name.toUpperCase());
121
    }
98
    
122
    
99
100
    
101
    protected String lookupName(short index) {
123
    protected String lookupName(short index) {
102
        return ((String)map.get(new Integer(index))); 
124
        return ((String)map.get(new Integer(index))); 
103
    }
125
    }
104
    
126
    
105
    protected short lookupIndex(String name) {
127
    /**
106
        Integer index = (Integer) map.getKeyForValue(name);
128
     * Resolves internal function names into function indexes.
129
     * <p>
130
     * The name matching is case insensitive.
131
     * @return the standard worksheet function index if found, otherwise <tt>FUNCTION_INDEX_EXTERNAL</tt>
132
     */
133
    protected static short lookupIndex(String name) {
134
        Integer index = (Integer) map.getKeyForValue(name.toUpperCase());
107
        if (index != null) return index.shortValue();
135
        if (index != null) return index.shortValue();
108
        return INDEX_EXTERNAL;
136
        return FUNCTION_INDEX_EXTERNAL;
109
    }
137
    }
110
    
138
    
111
    /**
139
    /**
Lines 115-121 Link Here
115
        BinaryTree dmap = new BinaryTree();
143
        BinaryTree dmap = new BinaryTree();
116
144
117
        dmap.put(new Integer(0),"COUNT");
145
        dmap.put(new Integer(0),"COUNT");
118
        dmap.put(new Integer(1),"specialflag");
146
        dmap.put(new Integer(1),FUNCTION_NAME_IF);
119
        dmap.put(new Integer(2),"ISNA");
147
        dmap.put(new Integer(2),"ISNA");
120
        dmap.put(new Integer(3),"ISERROR");
148
        dmap.put(new Integer(3),"ISERROR");
121
        dmap.put(new Integer(4),"SUM");
149
        dmap.put(new Integer(4),"SUM");
Lines 354-360 Link Here
354
        dmap.put(new Integer(252),"FREQUENCY");
382
        dmap.put(new Integer(252),"FREQUENCY");
355
        dmap.put(new Integer(253),"ADDTOOLBAR");
383
        dmap.put(new Integer(253),"ADDTOOLBAR");
356
        dmap.put(new Integer(254),"DELETETOOLBAR");
384
        dmap.put(new Integer(254),"DELETETOOLBAR");
357
        dmap.put(new Integer(255),"externalflag");
385
        dmap.put(new Integer(FUNCTION_INDEX_EXTERNAL),"externalflag");
358
        dmap.put(new Integer(256),"RESETTOOLBAR");
386
        dmap.put(new Integer(256),"RESETTOOLBAR");
359
        dmap.put(new Integer(257),"EVALUATE");
387
        dmap.put(new Integer(257),"EVALUATE");
360
        dmap.put(new Integer(258),"GETTOOLBAR");
388
        dmap.put(new Integer(258),"GETTOOLBAR");
(-)src/java/org/apache/poi/hssf/record/formula/NamePtg.java (-11 / +27 lines)
Lines 33-38 Link Here
33
{
33
{
34
    public final static short sid  = 0x23;
34
    public final static short sid  = 0x23;
35
    private final static int  SIZE = 5;
35
    private final static int  SIZE = 5;
36
    /** one-based index to defined name record */
36
    private short             field_1_label_index;
37
    private short             field_1_label_index;
37
    private short             field_2_zero;   // reserved must be 0
38
    private short             field_2_zero;   // reserved must be 0
38
    boolean xtra=false;
39
    boolean xtra=false;
Lines 42-65 Link Here
42
      //Required for clone methods
43
      //Required for clone methods
43
    }
44
    }
44
45
45
    /** Creates new NamePtg */
46
    /**
46
47
     * Creates new NamePtg and sets its name index to that of the corresponding defined name record
47
    public NamePtg(String name, Workbook book)
48
     * in the workbook.  The search for the name record is case insensitive.  If it is not found, 
48
    {
49
     * it gets created.
49
        final short n = (short) (book.getNumNames() + 1);
50
     */
51
    public NamePtg(String name, Workbook book) {
52
        field_1_label_index = (short)(1+getOrCreateNameRecord(book, name)); // convert to 1-based
53
    }
54
    /**
55
     * @return zero based index of the found or newly created defined name record. 
56
     */
57
    private static final int getOrCreateNameRecord(Workbook book, String name) {
58
        // perhaps this logic belongs in Workbook
59
        int countNames = book.getNumNames();
50
        NameRecord rec;
60
        NameRecord rec;
51
        for (short i = 1; i < n; i++) {
61
        for (int i = 0; i < countNames; i++) {
52
            rec = book.getNameRecord(i - 1);
62
            rec = book.getNameRecord(i);
53
            if (name.equals(rec.getNameText())) {
63
            if (name.equalsIgnoreCase(rec.getNameText())) {
54
                field_1_label_index = i;
64
                return i; 
55
                return;
56
            }
65
            }
57
        }
66
        }
58
        rec = new NameRecord();
67
        rec = new NameRecord();
59
        rec.setNameText(name);
68
        rec.setNameText(name);
60
        rec.setNameTextLength((byte) name.length());
69
        rec.setNameTextLength((byte) name.length());
61
        book.addName(rec);
70
        book.addName(rec);
62
        field_1_label_index = n;
71
        return countNames;
63
    }
72
    }
64
73
65
    /** Creates new NamePtg */
74
    /** Creates new NamePtg */
Lines 71-76 Link Here
71
        field_2_zero        = in.readShort();
80
        field_2_zero        = in.readShort();
72
        //if (data[offset+6]==0) xtra=true;
81
        //if (data[offset+6]==0) xtra=true;
73
    }
82
    }
83
    
84
    /**
85
     * @return zero based index to a defined name record in the LinkTable
86
     */
87
    public int getIndex() {
88
        return field_1_label_index-1; // convert to zero based
89
    }
74
90
75
    public void writeBytes(byte [] array, int offset)
91
    public void writeBytes(byte [] array, int offset)
76
    {
92
    {
(-)src/java/org/apache/poi/hssf/record/formula/IntPtg.java (-58 / +37 lines)
Lines 1-4 Link Here
1
2
/* ====================================================================
1
/* ====================================================================
3
   Licensed to the Apache Software Foundation (ASF) under one or more
2
   Licensed to the Apache Software Foundation (ASF) under one or more
4
   contributor license agreements.  See the NOTICE file distributed with
3
   contributor license agreements.  See the NOTICE file distributed with
Lines 16-27 Link Here
16
   limitations under the License.
15
   limitations under the License.
17
==================================================================== */
16
==================================================================== */
18
17
19
20
/*
21
 * IntPtg.java
22
 *
23
 * Created on October 29, 2001, 7:37 PM
24
 */
25
package org.apache.poi.hssf.record.formula;
18
package org.apache.poi.hssf.record.formula;
26
19
27
import org.apache.poi.util.LittleEndian;
20
import org.apache.poi.util.LittleEndian;
Lines 29-92 Link Here
29
import org.apache.poi.hssf.record.RecordInputStream;
22
import org.apache.poi.hssf.record.RecordInputStream;
30
23
31
/**
24
/**
32
 * Integer (unsigned short intger)
25
 * Integer (unsigned short integer)
33
 * Stores an unsigned short value (java int) in a formula
26
 * Stores an unsigned short value (java int) in a formula
34
 * @author  Andrew C. Oliver (acoliver at apache dot org)
27
 * @author  Andrew C. Oliver (acoliver at apache dot org)
35
 * @author Jason Height (jheight at chariot dot net dot au)
28
 * @author Jason Height (jheight at chariot dot net dot au)
36
 */
29
 */
30
public final class IntPtg extends Ptg {
31
    // 16 bit unsigned integer
32
    private static final int MIN_VALUE = 0x0000;
33
    private static final int MAX_VALUE = 0xFFFF;
34
    
35
    /**
36
     * Excel represents integers 0..65535 with the tInt token. 
37
     * @return <code>true</code> if the specified value is within the range of values 
38
     * <tt>IntPtg</tt> can represent. 
39
     */
40
    public static boolean isInRange(int i) {
41
        return i>=MIN_VALUE && i <=MAX_VALUE;
42
    }
37
43
38
public class IntPtg
39
    extends Ptg
40
{
41
    public final static int  SIZE = 3;
44
    public final static int  SIZE = 3;
42
    public final static byte sid  = 0x1e;
45
    public final static byte sid  = 0x1e;
43
    private int            field_1_value;
46
    private int            field_1_value;
44
  
47
  
45
    private IntPtg() {
48
    public IntPtg(RecordInputStream in) {
46
      //Required for clone methods
49
        this(in.readUShort());
47
    }
50
    }
48
51
49
    public IntPtg(RecordInputStream in)
50
    {
51
        setValue(in.readUShort());
52
    }
53
    
54
    
55
    // IntPtg should be able to create itself, shouldnt have to call setValue
56
    public IntPtg(String formulaToken) {
57
        setValue(Integer.parseInt(formulaToken));
58
    }
59
52
60
    /**
53
    public IntPtg(int value) {
61
     * Sets the wrapped value.
54
        if(!isInRange(value)) {
62
     * Normally you should call with a positive int.
55
            throw new IllegalArgumentException("value is out of range: " + value);
63
     */
56
        }
64
    public void setValue(int value)
65
    {
66
        if(value < 0 || value > (Short.MAX_VALUE + 1)*2 )
67
            throw new IllegalArgumentException("Unsigned short is out of range: " + value);
68
        field_1_value = value;
57
        field_1_value = value;
69
    }
58
    }
70
59
71
    /**
60
    public int getValue() {
72
     * Returns the value as a short, which may have
73
     *  been wrapped into negative numbers
74
     */
75
    public int getValue()
76
    {
77
        return field_1_value;
61
        return field_1_value;
78
    }
62
    }
79
63
80
    /**
81
     * Returns the value as an unsigned positive int.
82
     */
83
    public int getValueAsInt()
84
    {
85
    	if(field_1_value < 0) {
86
    		return (Short.MAX_VALUE + 1)*2 + field_1_value;
87
    	}
88
        return field_1_value;
89
    }
90
64
91
    public void writeBytes(byte [] array, int offset)
65
    public void writeBytes(byte [] array, int offset)
92
    {
66
    {
Lines 94-113 Link Here
94
        LittleEndian.putUShort(array, offset + 1, getValue());
68
        LittleEndian.putUShort(array, offset + 1, getValue());
95
    }
69
    }
96
70
97
    public int getSize()
71
    public int getSize() {
98
    {
99
        return SIZE;
72
        return SIZE;
100
    }
73
    }
101
74
102
    public String toFormulaString(Workbook book)
75
    public String toFormulaString(Workbook book) {
103
    {
76
        return String.valueOf(getValue());
104
        return "" + getValue();
105
    }
77
    }
106
 public byte getDefaultOperandClass() {return Ptg.CLASS_VALUE;}   
78
    public byte getDefaultOperandClass() {
79
        return Ptg.CLASS_VALUE;
80
    }
107
81
108
   public Object clone() {
82
    public Object clone() {
109
     IntPtg ptg = new IntPtg();
83
     return new IntPtg(field_1_value);
110
     ptg.field_1_value = field_1_value;
84
    }
111
     return ptg;
85
    public String toString() {
112
   }
86
        StringBuffer sb = new StringBuffer(64);
87
        sb.append(getClass().getName()).append(" [");
88
        sb.append(field_1_value);
89
        sb.append("]");
90
        return sb.toString();
91
    }
113
}
92
}
(-)src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java (-30 / +38 lines)
Lines 80-85 Link Here
80
import org.apache.poi.hssf.record.formula.eval.LessEqualEval;
80
import org.apache.poi.hssf.record.formula.eval.LessEqualEval;
81
import org.apache.poi.hssf.record.formula.eval.LessThanEval;
81
import org.apache.poi.hssf.record.formula.eval.LessThanEval;
82
import org.apache.poi.hssf.record.formula.eval.MultiplyEval;
82
import org.apache.poi.hssf.record.formula.eval.MultiplyEval;
83
import org.apache.poi.hssf.record.formula.eval.NameEval;
83
import org.apache.poi.hssf.record.formula.eval.NotEqualEval;
84
import org.apache.poi.hssf.record.formula.eval.NotEqualEval;
84
import org.apache.poi.hssf.record.formula.eval.NumberEval;
85
import org.apache.poi.hssf.record.formula.eval.NumberEval;
85
import org.apache.poi.hssf.record.formula.eval.OperationEval;
86
import org.apache.poi.hssf.record.formula.eval.OperationEval;
Lines 360-375 Link Here
360
        EvaluationCycleDetector tracker = EvaluationCycleDetectorManager.getTracker();
361
        EvaluationCycleDetector tracker = EvaluationCycleDetectorManager.getTracker();
361
        
362
        
362
        if(!tracker.startEvaluate(workbook, sheet, srcRowNum, srcColNum)) {
363
        if(!tracker.startEvaluate(workbook, sheet, srcRowNum, srcColNum)) {
363
        	return ErrorEval.CIRCULAR_REF_ERROR;
364
            return ErrorEval.CIRCULAR_REF_ERROR;
364
        }
365
        }
365
        try {
366
        try {
366
        	return evaluateCell(workbook, sheet, srcRowNum, srcColNum, srcCell.getCellFormula());
367
            return evaluateCell(workbook, sheet, srcRowNum, srcColNum, srcCell.getCellFormula());
367
        } finally {
368
        } finally {
368
        	tracker.endEvaluate(workbook, sheet, srcRowNum, srcColNum);
369
            tracker.endEvaluate(workbook, sheet, srcRowNum, srcColNum);
369
        }
370
        }
370
    }
371
    }
371
    private static ValueEval evaluateCell(HSSFWorkbook workbook, HSSFSheet sheet, 
372
    private static ValueEval evaluateCell(HSSFWorkbook workbook, HSSFSheet sheet, 
372
    		int srcRowNum, short srcColNum, String cellFormulaText) {
373
            int srcRowNum, short srcColNum, String cellFormulaText) {
373
        FormulaParser parser = new FormulaParser(cellFormulaText, workbook.getWorkbook());
374
        FormulaParser parser = new FormulaParser(cellFormulaText, workbook.getWorkbook());
374
        parser.parse();
375
        parser.parse();
375
        Ptg[] ptgs = parser.getRPNPtg();
376
        Ptg[] ptgs = parser.getRPNPtg();
Lines 380-394 Link Here
380
        for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
381
        for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
381
382
382
            // since we don't know how to handle these yet :(
383
            // since we don't know how to handle these yet :(
383
            if (ptgs[i] instanceof ControlPtg) { continue; }
384
            Ptg ptg = ptgs[i];
384
            if (ptgs[i] instanceof MemErrPtg) { continue; }
385
            if (ptg instanceof ControlPtg) { continue; }
385
            if (ptgs[i] instanceof MissingArgPtg) { continue; }
386
            if (ptg instanceof MemErrPtg) { continue; }
386
            if (ptgs[i] instanceof NamePtg) { continue; }
387
            if (ptg instanceof MissingArgPtg) { continue; }
387
            if (ptgs[i] instanceof NameXPtg) { continue; }
388
            if (ptg instanceof NamePtg) { 
388
            if (ptgs[i] instanceof UnknownPtg) { continue; }
389
                NamePtg namePtg = (NamePtg) ptg;
390
                stack.push(new NameEval(namePtg.getIndex()));
391
                continue; 
392
            }
393
            if (ptg instanceof NameXPtg) {
394
                continue;
395
            }
396
            if (ptg instanceof UnknownPtg) { continue; }
389
397
390
            if (ptgs[i] instanceof OperationPtg) {
398
            if (ptg instanceof OperationPtg) {
391
                OperationPtg optg = (OperationPtg) ptgs[i];
399
                OperationPtg optg = (OperationPtg) ptg;
392
400
393
                // parens can be ignored since we have RPN tokens
401
                // parens can be ignored since we have RPN tokens
394
                if (optg instanceof ParenthesisPtg) { continue; }
402
                if (optg instanceof ParenthesisPtg) { continue; }
Lines 408-443 Link Here
408
                Eval opresult = invokeOperation(operation, ops, srcRowNum, srcColNum, workbook, sheet);
416
                Eval opresult = invokeOperation(operation, ops, srcRowNum, srcColNum, workbook, sheet);
409
                stack.push(opresult);
417
                stack.push(opresult);
410
            }
418
            }
411
            else if (ptgs[i] instanceof ReferencePtg) {
419
            else if (ptg instanceof ReferencePtg) {
412
                ReferencePtg ptg = (ReferencePtg) ptgs[i];
420
                ReferencePtg refPtg = (ReferencePtg) ptg;
413
                short colnum = ptg.getColumn();
421
                short colnum = refPtg.getColumn();
414
                short rownum = ptg.getRow();
422
                short rownum = refPtg.getRow();
415
                HSSFRow row = sheet.getRow(rownum);
423
                HSSFRow row = sheet.getRow(rownum);
416
                HSSFCell cell = (row != null) ? row.getCell(colnum) : null;
424
                HSSFCell cell = (row != null) ? row.getCell(colnum) : null;
417
                stack.push(createRef2DEval(ptg, cell, row, sheet, workbook));
425
                stack.push(createRef2DEval(refPtg, cell, row, sheet, workbook));
418
            }
426
            }
419
            else if (ptgs[i] instanceof Ref3DPtg) {
427
            else if (ptg instanceof Ref3DPtg) {
420
                Ref3DPtg ptg = (Ref3DPtg) ptgs[i];
428
                Ref3DPtg refPtg = (Ref3DPtg) ptg;
421
                short colnum = ptg.getColumn();
429
                short colnum = refPtg.getColumn();
422
                short rownum = ptg.getRow();
430
                short rownum = refPtg.getRow();
423
                Workbook wb = workbook.getWorkbook();
431
                Workbook wb = workbook.getWorkbook();
424
                HSSFSheet xsheet = workbook.getSheetAt(wb.getSheetIndexFromExternSheetIndex(ptg.getExternSheetIndex()));
432
                HSSFSheet xsheet = workbook.getSheetAt(wb.getSheetIndexFromExternSheetIndex(refPtg.getExternSheetIndex()));
425
                HSSFRow row = xsheet.getRow(rownum);
433
                HSSFRow row = xsheet.getRow(rownum);
426
                HSSFCell cell = (row != null) ? row.getCell(colnum) : null;
434
                HSSFCell cell = (row != null) ? row.getCell(colnum) : null;
427
                stack.push(createRef3DEval(ptg, cell, row, xsheet, workbook));
435
                stack.push(createRef3DEval(refPtg, cell, row, xsheet, workbook));
428
            }
436
            }
429
            else if (ptgs[i] instanceof AreaPtg) {
437
            else if (ptg instanceof AreaPtg) {
430
                AreaPtg ap = (AreaPtg) ptgs[i];
438
                AreaPtg ap = (AreaPtg) ptg;
431
                AreaEval ae = evaluateAreaPtg(sheet, workbook, ap);
439
                AreaEval ae = evaluateAreaPtg(sheet, workbook, ap);
432
                stack.push(ae);
440
                stack.push(ae);
433
            }
441
            }
434
            else if (ptgs[i] instanceof Area3DPtg) {
442
            else if (ptg instanceof Area3DPtg) {
435
                Area3DPtg a3dp = (Area3DPtg) ptgs[i];
443
                Area3DPtg a3dp = (Area3DPtg) ptg;
436
                AreaEval ae = evaluateArea3dPtg(workbook, a3dp);
444
                AreaEval ae = evaluateArea3dPtg(workbook, a3dp);
437
                stack.push(ae);
445
                stack.push(ae);
438
            }
446
            }
439
            else {
447
            else {
440
                Eval ptgEval = getEvalForPtg(ptgs[i]);
448
                Eval ptgEval = getEvalForPtg(ptg);
441
                stack.push(ptgEval);
449
                stack.push(ptgEval);
442
            }
450
            }
443
        }
451
        }
Lines 486-492 Link Here
486
        // (eg C:C)
494
        // (eg C:C)
487
        // TODO: Handle whole column ranges properly
495
        // TODO: Handle whole column ranges properly
488
        if(row1 == -1 && row0 >= 0) {
496
        if(row1 == -1 && row0 >= 0) {
489
        	row1 = (short)sheet.getLastRowNum();
497
            row1 = (short)sheet.getLastRowNum();
490
        }
498
        }
491
        
499
        
492
        ValueEval[] values = new ValueEval[(row1 - row0 + 1) * (col1 - col0 + 1)];
500
        ValueEval[] values = new ValueEval[(row1 - row0 + 1) * (col1 - col0 + 1)];
Lines 514-520 Link Here
514
        // (eg C:C)
522
        // (eg C:C)
515
        // TODO: Handle whole column ranges properly
523
        // TODO: Handle whole column ranges properly
516
        if(row1 == -1 && row0 >= 0) {
524
        if(row1 == -1 && row0 >= 0) {
517
        	row1 = (short)xsheet.getLastRowNum();
525
            row1 = (short)xsheet.getLastRowNum();
518
        }
526
        }
519
        
527
        
520
        ValueEval[] values = new ValueEval[(row1 - row0 + 1) * (col1 - col0 + 1)];
528
        ValueEval[] values = new ValueEval[(row1 - row0 + 1) * (col1 - col0 + 1)];
(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/OperandResolver.java (+3 lines)
Lines 223-230 Link Here
223
	 *  Some examples:<br/> 
223
	 *  Some examples:<br/> 
224
	 *  " 123 " -&gt; 123.0<br/>
224
	 *  " 123 " -&gt; 123.0<br/>
225
	 *  ".123" -&gt; 0.123<br/>
225
	 *  ".123" -&gt; 0.123<br/>
226
	 *  These not supported yet:<br/>
226
	 *  " $ 1,000.00 " -&gt; 1000.0<br/>
227
	 *  " $ 1,000.00 " -&gt; 1000.0<br/>
227
	 *  "$1.25E4" -&gt; 12500.0<br/>
228
	 *  "$1.25E4" -&gt; 12500.0<br/>
229
	 *  "5**2" -&gt; 500<br/>
230
	 *  "250%" -&gt; 2.5<br/>
228
	 *  
231
	 *  
229
	 * @param text
232
	 * @param text
230
	 * @return <code>null</code> if the specified text cannot be parsed as a number
233
	 * @return <code>null</code> if the specified text cannot be parsed as a number
(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/FunctionEval.java (-2 / +4 lines)
Lines 38-44 Link Here
38
        public static final int OFFSET = 78;
38
        public static final int OFFSET = 78;
39
        /** 148 */
39
        /** 148 */
40
        public static final int INDIRECT = 148;
40
        public static final int INDIRECT = 148;
41
        
41
        /** 255 */
42
        public static final int EXTERNAL_FUNC = 255;
42
    }
43
    }
43
    // convenient access to namespace
44
    // convenient access to namespace
44
    private static final FunctionID ID = null;
45
    private static final FunctionID ID = null;
Lines 51-56 Link Here
51
        Map m = new HashMap();
52
        Map m = new HashMap();
52
        addMapping(m, ID.OFFSET, new Offset());
53
        addMapping(m, ID.OFFSET, new Offset());
53
        addMapping(m, ID.INDIRECT, new Indirect());
54
        addMapping(m, ID.INDIRECT, new Indirect());
55
        addMapping(m, ID.EXTERNAL_FUNC, new ExternalFunction());
54
        freeRefFunctionsByIdMap = m;
56
        freeRefFunctionsByIdMap = m;
55
    }
57
    }
56
    private static void addMapping(Map m, int offset, FreeRefFunction frf) {
58
    private static void addMapping(Map m, int offset, FreeRefFunction frf) {
Lines 316-322 Link Here
316
        retval[252] = new Frequency(); // FREQUENCY
318
        retval[252] = new Frequency(); // FREQUENCY
317
        retval[253] = new NotImplementedFunction(); // ADDTOOLBAR
319
        retval[253] = new NotImplementedFunction(); // ADDTOOLBAR
318
        retval[254] = new NotImplementedFunction(); // DELETETOOLBAR
320
        retval[254] = new NotImplementedFunction(); // DELETETOOLBAR
319
        retval[255] = new NotImplementedFunction(); // EXTERNALFLAG
321
        retval[ID.EXTERNAL_FUNC] = null; // ExternalFunction is a FreeREfFunction
320
        retval[256] = new NotImplementedFunction(); // RESETTOOLBAR
322
        retval[256] = new NotImplementedFunction(); // RESETTOOLBAR
321
        retval[257] = new Evaluate(); // EVALUATE
323
        retval[257] = new Evaluate(); // EVALUATE
322
        retval[258] = new NotImplementedFunction(); // GETTOOLBAR
324
        retval[258] = new NotImplementedFunction(); // GETTOOLBAR
(-)src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java (-13 / +261 lines)
Lines 18-44 Link Here
18
        
18
        
19
package org.apache.poi.hssf.model;
19
package org.apache.poi.hssf.model;
20
20
21
import java.util.List;
21
import junit.framework.AssertionFailedError;
22
23
import junit.framework.TestCase;
22
import junit.framework.TestCase;
24
23
25
import org.apache.poi.hssf.record.formula.AbstractFunctionPtg;
24
import org.apache.poi.hssf.record.formula.AbstractFunctionPtg;
26
import org.apache.poi.hssf.record.formula.AddPtg;
25
import org.apache.poi.hssf.record.formula.AddPtg;
26
import org.apache.poi.hssf.record.formula.AreaPtg;
27
import org.apache.poi.hssf.record.formula.AttrPtg;
27
import org.apache.poi.hssf.record.formula.AttrPtg;
28
import org.apache.poi.hssf.record.formula.BoolPtg;
28
import org.apache.poi.hssf.record.formula.BoolPtg;
29
import org.apache.poi.hssf.record.formula.ConcatPtg;
29
import org.apache.poi.hssf.record.formula.DividePtg;
30
import org.apache.poi.hssf.record.formula.DividePtg;
30
import org.apache.poi.hssf.record.formula.EqualPtg;
31
import org.apache.poi.hssf.record.formula.EqualPtg;
32
import org.apache.poi.hssf.record.formula.ErrPtg;
31
import org.apache.poi.hssf.record.formula.FuncPtg;
33
import org.apache.poi.hssf.record.formula.FuncPtg;
32
import org.apache.poi.hssf.record.formula.FuncVarPtg;
34
import org.apache.poi.hssf.record.formula.FuncVarPtg;
33
import org.apache.poi.hssf.record.formula.IntPtg;
35
import org.apache.poi.hssf.record.formula.IntPtg;
34
import org.apache.poi.hssf.record.formula.LessEqualPtg;
36
import org.apache.poi.hssf.record.formula.LessEqualPtg;
35
import org.apache.poi.hssf.record.formula.LessThanPtg;
37
import org.apache.poi.hssf.record.formula.LessThanPtg;
38
import org.apache.poi.hssf.record.formula.MissingArgPtg;
39
import org.apache.poi.hssf.record.formula.MultiplyPtg;
36
import org.apache.poi.hssf.record.formula.NamePtg;
40
import org.apache.poi.hssf.record.formula.NamePtg;
37
import org.apache.poi.hssf.record.formula.NotEqualPtg;
41
import org.apache.poi.hssf.record.formula.NotEqualPtg;
38
import org.apache.poi.hssf.record.formula.NumberPtg;
42
import org.apache.poi.hssf.record.formula.NumberPtg;
43
import org.apache.poi.hssf.record.formula.PercentPtg;
44
import org.apache.poi.hssf.record.formula.PowerPtg;
39
import org.apache.poi.hssf.record.formula.Ptg;
45
import org.apache.poi.hssf.record.formula.Ptg;
40
import org.apache.poi.hssf.record.formula.ReferencePtg;
46
import org.apache.poi.hssf.record.formula.ReferencePtg;
41
import org.apache.poi.hssf.record.formula.StringPtg;
47
import org.apache.poi.hssf.record.formula.StringPtg;
48
import org.apache.poi.hssf.record.formula.SubtractPtg;
42
import org.apache.poi.hssf.record.formula.UnaryMinusPtg;
49
import org.apache.poi.hssf.record.formula.UnaryMinusPtg;
43
import org.apache.poi.hssf.record.formula.UnaryPlusPtg;
50
import org.apache.poi.hssf.record.formula.UnaryPlusPtg;
44
import org.apache.poi.hssf.usermodel.HSSFCell;
51
import org.apache.poi.hssf.usermodel.HSSFCell;
Lines 64-78 Link Here
64
    public void tearDown() {
71
    public void tearDown() {
65
        
72
        
66
    }
73
    }
74
    /**
75
     * @return parsed token array already confirmed not <code>null</code>
76
     */
77
    private static Ptg[] parseFormula(String s) {
78
        FormulaParser fp = new FormulaParser(s, null);
79
        fp.parse();
80
        Ptg[] result = fp.getRPNPtg();
81
        assertNotNull("Ptg array should not be null", result);
82
        return result;
83
    }
67
    
84
    
68
    public void testSimpleFormula() {
85
    public void testSimpleFormula() {
69
        FormulaParser fp = new FormulaParser("2+2;",null);
86
        FormulaParser fp = new FormulaParser("2+2",null);
70
        fp.parse();
87
        fp.parse();
71
        Ptg[] ptgs = fp.getRPNPtg();
88
        Ptg[] ptgs = fp.getRPNPtg();
72
        assertTrue("three tokens expected, got "+ptgs.length,ptgs.length == 3);
89
        assertTrue("three tokens expected, got "+ptgs.length,ptgs.length == 3);
73
    }
90
    }
74
    public void testFormulaWithSpace1() {
91
    public void testFormulaWithSpace1() {
75
        FormulaParser fp = new FormulaParser(" 2 + 2 ;",null);
92
        FormulaParser fp = new FormulaParser(" 2 + 2 ",null);
76
        fp.parse();
93
        fp.parse();
77
        Ptg[] ptgs = fp.getRPNPtg();
94
        Ptg[] ptgs = fp.getRPNPtg();
78
        assertTrue("three tokens expected, got "+ptgs.length,ptgs.length == 3);
95
        assertTrue("three tokens expected, got "+ptgs.length,ptgs.length == 3);
Lines 85-91 Link Here
85
    public void testFormulaWithSpace2() {
102
    public void testFormulaWithSpace2() {
86
        Ptg[] ptgs;
103
        Ptg[] ptgs;
87
        FormulaParser fp;
104
        FormulaParser fp;
88
        fp = new FormulaParser("2+ sum( 3 , 4) ;",null);
105
        fp = new FormulaParser("2+ sum( 3 , 4) ",null);
89
        fp.parse();
106
        fp.parse();
90
        ptgs = fp.getRPNPtg();
107
        ptgs = fp.getRPNPtg();
91
        assertTrue("five tokens expected, got "+ptgs.length,ptgs.length == 5);
108
        assertTrue("five tokens expected, got "+ptgs.length,ptgs.length == 5);
Lines 94-100 Link Here
94
     public void testFormulaWithSpaceNRef() {
111
     public void testFormulaWithSpaceNRef() {
95
        Ptg[] ptgs;
112
        Ptg[] ptgs;
96
        FormulaParser fp;
113
        FormulaParser fp;
97
        fp = new FormulaParser("sum( A2:A3 );",null);
114
        fp = new FormulaParser("sum( A2:A3 )",null);
98
        fp.parse();
115
        fp.parse();
99
        ptgs = fp.getRPNPtg();
116
        ptgs = fp.getRPNPtg();
100
        assertTrue("two tokens expected, got "+ptgs.length,ptgs.length == 2);
117
        assertTrue("two tokens expected, got "+ptgs.length,ptgs.length == 2);
Lines 103-109 Link Here
103
    public void testFormulaWithString() {
120
    public void testFormulaWithString() {
104
        Ptg[] ptgs;
121
        Ptg[] ptgs;
105
        FormulaParser fp;
122
        FormulaParser fp;
106
        fp = new FormulaParser("\"hello\" & \"world\" ;",null);
123
        fp = new FormulaParser("\"hello\" & \"world\" ",null);
107
        fp.parse();
124
        fp.parse();
108
        ptgs = fp.getRPNPtg();
125
        ptgs = fp.getRPNPtg();
109
        assertTrue("three token expected, got " + ptgs.length, ptgs.length == 3);
126
        assertTrue("three token expected, got " + ptgs.length, ptgs.length == 3);
Lines 276-295 Link Here
276
	}
293
	}
277
	    
294
	    
278
    public void testMacroFunction() {
295
    public void testMacroFunction() {
279
        Workbook w = new Workbook();
296
        Workbook w = Workbook.createWorkbook();
280
        FormulaParser fp = new FormulaParser("FOO()", w);
297
        FormulaParser fp = new FormulaParser("FOO()", w);
281
        fp.parse();
298
        fp.parse();
282
        Ptg[] ptg = fp.getRPNPtg();
299
        Ptg[] ptg = fp.getRPNPtg();
283
300
284
        AbstractFunctionPtg tfunc = (AbstractFunctionPtg) ptg[0];
301
        // the name gets encoded as the first arg
302
        NamePtg tname = (NamePtg) ptg[0];
303
        assertEquals("FOO", tname.toFormulaString(w));
304
        
305
        AbstractFunctionPtg tfunc = (AbstractFunctionPtg) ptg[1];
285
        assertEquals("externalflag", tfunc.getName());
306
        assertEquals("externalflag", tfunc.getName());
286
287
        NamePtg tname = (NamePtg) ptg[1];
288
        assertEquals("FOO", tname.toFormulaString(w));
289
    }
307
    }
290
308
291
    public void testEmbeddedSlash() {
309
    public void testEmbeddedSlash() {
292
        FormulaParser fp = new FormulaParser("HYPERLINK(\"http://www.jakarta.org\",\"Jakarta\");",null);
310
        FormulaParser fp = new FormulaParser("HYPERLINK(\"http://www.jakarta.org\",\"Jakarta\")",null);
293
        fp.parse();
311
        fp.parse();
294
        Ptg[] ptg = fp.getRPNPtg();
312
        Ptg[] ptg = fp.getRPNPtg();
295
        assertTrue("first ptg is string",ptg[0] instanceof StringPtg);
313
        assertTrue("first ptg is string",ptg[0] instanceof StringPtg);
Lines 589-592 Link Here
589
        };
607
        };
590
        assertEquals("NA()", FormulaParser.toFormulaString(book, ptgs));
608
        assertEquals("NA()", FormulaParser.toFormulaString(book, ptgs));
591
    }
609
    }
610
611
    public void testPercent() {
612
        Ptg[] ptgs;
613
        ptgs = parseFormula("5%");
614
        assertEquals(2, ptgs.length);
615
        assertEquals(ptgs[0].getClass(), IntPtg.class);
616
        assertEquals(ptgs[1].getClass(), PercentPtg.class);
617
618
        // spaces OK
619
        ptgs = parseFormula(" 250 % ");
620
        assertEquals(2, ptgs.length);
621
        assertEquals(ptgs[0].getClass(), IntPtg.class);
622
        assertEquals(ptgs[1].getClass(), PercentPtg.class);
623
        
624
        
625
        // double percent OK 
626
        ptgs = parseFormula("12345.678%%");
627
        assertEquals(3, ptgs.length);
628
        assertEquals(ptgs[0].getClass(), NumberPtg.class);
629
        assertEquals(ptgs[1].getClass(), PercentPtg.class);
630
        assertEquals(ptgs[2].getClass(), PercentPtg.class);
631
        
632
        // percent of a bracketed expression
633
        ptgs = parseFormula("(A1+35)%*B1%");
634
        assertEquals(8, ptgs.length);
635
        assertEquals(ptgs[4].getClass(), PercentPtg.class);
636
        assertEquals(ptgs[6].getClass(), PercentPtg.class);
637
        
638
        // percent of a text quantity
639
        ptgs = parseFormula("\"8.75\"%");
640
        assertEquals(2, ptgs.length);
641
        assertEquals(ptgs[0].getClass(), StringPtg.class);
642
        assertEquals(ptgs[1].getClass(), PercentPtg.class);
643
644
        // percent to the power of
645
        ptgs = parseFormula("50%^3");
646
        assertEquals(4, ptgs.length);
647
        assertEquals(ptgs[0].getClass(), IntPtg.class);
648
        assertEquals(ptgs[1].getClass(), PercentPtg.class);
649
        assertEquals(ptgs[2].getClass(), IntPtg.class);
650
        assertEquals(ptgs[3].getClass(), PowerPtg.class);
651
652
        //
653
        // things that parse OK but would *evaluate* to an error
654
        
655
        ptgs = parseFormula("\"abc\"%");
656
        assertEquals(2, ptgs.length);
657
        assertEquals(ptgs[0].getClass(), StringPtg.class);
658
        assertEquals(ptgs[1].getClass(), PercentPtg.class);
659
        
660
        ptgs = parseFormula("#N/A%");
661
        assertEquals(2, ptgs.length);
662
        assertEquals(ptgs[0].getClass(), ErrPtg.class);
663
        assertEquals(ptgs[1].getClass(), PercentPtg.class);
664
    }
665
    
666
    /**
667
     * Tests combinations of various operators in the absence of brackets
668
     */
669
    public void testPrecedenceAndAssociativity() {
670
671
        Class[] expClss;
672
        
673
        // TRUE=TRUE=2=2  evaluates to FALSE
674
        expClss = new Class[] { BoolPtg.class, BoolPtg.class, EqualPtg.class, 
675
                IntPtg.class, EqualPtg.class, IntPtg.class, EqualPtg.class,  };
676
        confirmTokenClasses("TRUE=TRUE=2=2", expClss);
677
       
678
679
        //  2^3^2    evaluates to 64 not 512
680
        expClss = new Class[] { IntPtg.class, IntPtg.class, PowerPtg.class, 
681
                IntPtg.class, PowerPtg.class, };
682
        confirmTokenClasses("2^3^2", expClss);
683
        
684
        // "abc" & 2 + 3 & "def"   evaluates to "abc5def"
685
        expClss = new Class[] { StringPtg.class, IntPtg.class, IntPtg.class, 
686
                AddPtg.class, ConcatPtg.class, StringPtg.class, ConcatPtg.class, };
687
        confirmTokenClasses("\"abc\"&2+3&\"def\"", expClss);
688
        
689
        
690
        //  (1 / 2) - (3 * 4)
691
        expClss = new Class[] { IntPtg.class, IntPtg.class, DividePtg.class, 
692
                IntPtg.class, IntPtg.class, MultiplyPtg.class, SubtractPtg.class, };
693
        confirmTokenClasses("1/2-3*4", expClss);
694
        
695
        // 2 * (2^2)
696
        expClss = new Class[] { IntPtg.class, IntPtg.class, IntPtg.class, PowerPtg.class, MultiplyPtg.class, };
697
        // NOT: (2 *2) ^ 2 -> int int multiply int power
698
        confirmTokenClasses("2*2^2", expClss);
699
        
700
        //  2^200% -> 2 not 1.6E58
701
        expClss = new Class[] { IntPtg.class, IntPtg.class, PercentPtg.class, PowerPtg.class, };
702
        confirmTokenClasses("2^200%", expClss);
703
    }
704
    
705
    private static void confirmTokenClasses(String formula, Class[] expectedClasses) {
706
        Ptg[] ptgs = parseFormula(formula);
707
        assertEquals(expectedClasses.length, ptgs.length);
708
        for (int i = 0; i < expectedClasses.length; i++) {
709
            if(expectedClasses[i] != ptgs[i].getClass()) {
710
                fail("difference at token[" + i + "]: expected ("
711
                    + expectedClasses[i].getName() + ") but got (" 
712
                    + ptgs[i].getClass().getName() + ")");
713
            }
714
        }
715
    }
716
717
    public void testPower() {
718
        confirmTokenClasses("2^5", new Class[] { IntPtg.class, IntPtg.class, PowerPtg.class, });
719
    }
720
721
    private static Ptg parseSingleToken(String formula, Class ptgClass) {
722
        Ptg[] ptgs = parseFormula(formula);
723
        assertEquals(1, ptgs.length);
724
        Ptg result = ptgs[0];
725
        assertEquals(ptgClass, result.getClass());
726
        return result;
727
    }
728
729
    public void testParseNumber() {
730
        IntPtg ip;
731
        
732
        // bug 33160
733
        ip = (IntPtg) parseSingleToken("40", IntPtg.class);
734
        assertEquals(40, ip.getValue());
735
        ip = (IntPtg) parseSingleToken("40000", IntPtg.class);
736
        assertEquals(40000, ip.getValue());
737
        
738
        // check the upper edge of the IntPtg range:
739
        ip = (IntPtg) parseSingleToken("65535", IntPtg.class);
740
        assertEquals(65535, ip.getValue());
741
        NumberPtg np = (NumberPtg) parseSingleToken("65536", NumberPtg.class);
742
        assertEquals(65536, np.getValue(), 0);
743
        
744
        np = (NumberPtg) parseSingleToken("65534.6", NumberPtg.class);
745
        assertEquals(65534.6, np.getValue(), 0);
746
    }
747
    
748
    public void testMissingArgs() {
749
        
750
        Class[] expClss;
751
        
752
        expClss = new Class[] { ReferencePtg.class, MissingArgPtg.class, ReferencePtg.class, 
753
                FuncVarPtg.class, };
754
        confirmTokenClasses("if(A1, ,C1)", expClss);
755
        
756
        expClss = new Class[] { MissingArgPtg.class, AreaPtg.class, MissingArgPtg.class,
757
                FuncVarPtg.class, };
758
        confirmTokenClasses("counta( , A1:B2, )", expClss);
759
    }
760
761
    public void testParseErrorLiterals() {
762
        
763
        confirmParseErrorLiteral(ErrPtg.NULL_INTERSECTION, "#NULL!");
764
        confirmParseErrorLiteral(ErrPtg.DIV_ZERO, "#DIV/0!");
765
        confirmParseErrorLiteral(ErrPtg.VALUE_INVALID, "#VALUE!");
766
        confirmParseErrorLiteral(ErrPtg.REF_INVALID, "#REF!");
767
        confirmParseErrorLiteral(ErrPtg.NAME_INVALID, "#NAME?");
768
        confirmParseErrorLiteral(ErrPtg.NUM_ERROR, "#NUM!");
769
        confirmParseErrorLiteral(ErrPtg.N_A, "#N/A");
770
    }
771
772
    private static void confirmParseErrorLiteral(ErrPtg expectedToken, String formula) {
773
        assertEquals(expectedToken, parseSingleToken(formula, ErrPtg.class));
774
    }
775
    
776
    /**
777
     * To aid readability the parameters have been encoded with single quotes instead of double
778
     * quotes.  This method converts single quotes to double quotes before performing the parse
779
     * and result check.
780
     */
781
    private static void confirmStringParse(String singleQuotedValue) {
782
        // formula: internal quotes become double double, surround with double quotes
783
        String formula = '"' + singleQuotedValue.replaceAll("'", "\"\"") + '"';
784
        String expectedValue = singleQuotedValue.replace('\'', '"');
785
        
786
        StringPtg sp = (StringPtg) parseSingleToken(formula, StringPtg.class);
787
        assertEquals(expectedValue, sp.getValue());
788
    }
789
    
790
    public void testPaseStringLiterals() {
791
        confirmStringParse("goto considered harmful");
792
        
793
        confirmStringParse("goto 'considered' harmful");
794
        
795
        confirmStringParse("");
796
        confirmStringParse("'");
797
        confirmStringParse("''");
798
        confirmStringParse("' '");
799
        confirmStringParse(" ' ");
800
    }
801
    
802
    public void testParseSumIfSum() {
803
        String formulaString;
804
        Ptg[] ptgs;
805
        ptgs = parseFormula("sum(5, 2, if(3>2, sum(A1:A2), 6))");
806
        formulaString = FormulaParser.toFormulaString(null, ptgs);
807
        assertEquals("SUM(5,2,IF(3>2,SUM(A1:A2),6))", formulaString);
808
809
        ptgs = parseFormula("if(1<2,sum(5, 2, if(3>2, sum(A1:A2), 6)),4)");
810
        formulaString = FormulaParser.toFormulaString(null, ptgs);
811
        assertEquals("IF(1<2,SUM(5,2,IF(3>2,SUM(A1:A2),6)),4)", formulaString);
812
    }
813
    public void testParserErrors() {
814
        parseExpectedException("1 2");
815
        parseExpectedException(" 12 . 345  ");
816
        parseExpectedException("1 .23  ");
817
818
        parseExpectedException("sum(#NAME)");
819
        parseExpectedException("1 + #N / A * 2");
820
        parseExpectedException("#value?");
821
        parseExpectedException("#DIV/ 0+2");
822
        
823
        
824
        if (false) { // TODO - add functionality to detect func arg count mismatch
825
            parseExpectedException("IF(TRUE)");
826
            parseExpectedException("countif(A1:B5, C1, D1)");
827
        }
828
    }
829
    
830
    private static void parseExpectedException(String formula) {
831
        try {
832
            parseFormula(formula);
833
            throw new AssertionFailedError("expected parse exception");
834
        } catch (RuntimeException e) {
835
            // TODO - catch more specific exception
836
            // expected during successful test
837
            return;
838
        }
839
    }
592
}
840
}

Return to bug 44504