1 /* ====================================================================
2 Licensed to the Apache Software Foundation (ASF) under one or more
3 contributor license agreements. See the NOTICE file distributed with
4 this work for additional information regarding copyright ownership.
5 The ASF licenses this file to You under the Apache License, Version 2.0
6 (the "License"); you may not use this file except in compliance with
7 the License. You may obtain a copy of the License at
8
9 http://www.apache.org/licenses/LICENSE-2.0
10
11 Unless required by applicable law or agreed to in writing, software
12 distributed under the License is distributed on an "AS IS" BASIS,
13 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14 See the License for the specific language governing permissions and
15 limitations under the License.
16 ==================================================================== */
17
18 package org.apache.poi.hssf.model;
19
20 import java.util.ArrayList;
21 import java.util.List;
22 import java.util.Stack;
23 import java.util.regex.Pattern;
24
25 //import PTG's .. since we need everything, import *
26 import org.apache.poi.hssf.record.formula;
27 import org.apache.poi.hssf.record.formula.function.FunctionMetadata;
28 import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry;
29 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
30
31 /**
32 * This class parses a formula string into a List of tokens in RPN order.
33 * Inspired by
34 * Lets Build a Compiler, by Jack Crenshaw
35 * BNF for the formula expression is :
36 * <expression> ::= <term> [<addop> <term>]*
37 * <term> ::= <factor> [ <mulop> <factor> ]*
38 * <factor> ::= <number> | (<expression>) | <cellRef> | <function>
39 * <function> ::= <functionName> ([expression [, expression]*])
40 *
41 * @author Avik Sengupta <avik at apache dot org>
42 * @author Andrew C. oliver (acoliver at apache dot org)
43 * @author Eric Ladner (eladner at goldinc dot com)
44 * @author Cameron Riley (criley at ekmail.com)
45 * @author Peter M. Murray (pete at quantrix dot com)
46 * @author Pavel Krupets (pkrupets at palmtreebusiness dot com)
47 */
48 public final class FormulaParser {
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
63 public static final int FORMULA_TYPE_CELL = 0;
64 public static final int FORMULA_TYPE_SHARED = 1;
65 public static final int FORMULA_TYPE_ARRAY =2;
66 public static final int FORMULA_TYPE_CONDFOMRAT = 3;
67 public static final int FORMULA_TYPE_NAMEDRANGE = 4;
68
69 private final String formulaString;
70 private final int formulaLength;
71 private int pointer;
72
73 private ParseNode _rootNode;
74
75 /**
76 * Used for spotting if we have a cell reference,
77 * or a named range
78 */
79 private final static Pattern CELL_REFERENCE_PATTERN = Pattern.compile("(?:('?)[^:\\\\/\\?\\*\\[\\]]+\\1!)?\\$?[A-Za-z]+\\$?[\\d]+");
80
81 private static char TAB = '\t';
82
83 /**
84 * Lookahead Character.
85 * gets value '\0' when the input string is exhausted
86 */
87 private char look;
88
89 private HSSFWorkbook book;
90
91
92 /**
93 * Create the formula parser, with the string that is to be
94 * parsed against the supplied workbook.
95 * A later call the parse() method to return ptg list in
96 * rpn order, then call the getRPNPtg() to retrive the
97 * parse results.
98 * This class is recommended only for single threaded use.
99 *
100 * If you only have a usermodel.HSSFWorkbook, and not a
101 * model.Workbook, then use the convenience method on
102 * usermodel.HSSFFormulaEvaluator
103 */
104 public FormulaParser(String formula, HSSFWorkbook book){
105 formulaString = formula;
106 pointer=0;
107 this.book = book;
108 formulaLength = formulaString.length();
109 }
110
111 public static Ptg[] parse(String formula, HSSFWorkbook book) {
112 FormulaParser fp = new FormulaParser(formula, book);
113 fp.parse();
114 return fp.getRPNPtg();
115 }
116
117 /** Read New Character From Input Stream */
118 private void GetChar() {
119 // Check to see if we've walked off the end of the string.
120 if (pointer > formulaLength) {
121 throw new RuntimeException("too far");
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
128 look = (char)0;
129 }
130 pointer++;
131 //System.out.println("Got char: "+ look);
132 }
133
134 /** Report What Was Expected */
135 private RuntimeException expected(String s) {
136 String msg = "Parse error near char " + (pointer-1) + " '" + look + "'"
137 + " in specified formula '" + formulaString + "'. Expected "
138 + s;
139 return new FormulaParseException(msg);
140 }
141
142 /** Recognize an Alpha Character */
143 private boolean IsAlpha(char c) {
144 return Character.isLetter(c) || c == '$' || c=='_';
145 }
146
147 /** Recognize a Decimal Digit */
148 private boolean IsDigit(char c) {
149 return Character.isDigit(c);
150 }
151
152 /** Recognize an Alphanumeric */
153 private boolean IsAlNum(char c) {
154 return (IsAlpha(c) || IsDigit(c));
155 }
156
157 /** Recognize White Space */
158 private boolean IsWhite( char c) {
159 return (c ==' ' || c== TAB);
160 }
161
162 /** Skip Over Leading White Space */
163 private void SkipWhite() {
164 while (IsWhite(look)) {
165 GetChar();
166 }
167 }
168
169 /**
170 * Consumes the next input character if it is equal to the one specified otherwise throws an
171 * unchecked exception. This method does <b>not</b> consume whitespace (before or after the
172 * matched character).
173 */
174 private void Match(char x) {
175 if (look != x) {
176 throw expected("'" + x + "'");
177 }
178 GetChar();
179 }
180
181 /** Get an Identifier */
182 private String GetName() {
183 StringBuffer Token = new StringBuffer();
184 if (!IsAlpha(look) && look != '\'') {
185 throw expected("Name");
186 }
187 if(look == '\'')
188 {
189 Match('\'');
190 boolean done = look == '\'';
191 while(!done)
192 {
193 Token.append(look);
194 GetChar();
195 if(look == '\'')
196 {
197 Match('\'');
198 done = look != '\'';
199 }
200 }
201 }
202 else
203 {
204 while (IsAlNum(look)) {
205 Token.append(look);
206 GetChar();
207 }
208 }
209 return Token.toString();
210 }
211
212 /** Get a Number */
213 private String GetNum() {
214 StringBuffer value = new StringBuffer();
215
216 while (IsDigit(this.look)){
217 value.append(this.look);
218 GetChar();
219 }
220 return value.length() == 0 ? null : value.toString();
221 }
222
223 private ParseNode parseFunctionOrIdentifier() {
224 String name = GetName();
225 if (look == '('){
226 //This is a function
227 return function(name);
228 }
229 return new ParseNode(parseIdentifier(name));
230 }
231 private Ptg parseIdentifier(String name) {
232
233 if (look == ':' || look == '.') { // this is a AreaReference
234 GetChar();
235
236 while (look == '.') { // formulas can have . or .. or ... instead of :
237 GetChar();
238 }
239
240 String first = name;
241 String second = GetName();
242 return new AreaPtg(first+":"+second);
243 }
244
245 if (look == '!') {
246 Match('!');
247 String sheetName = name;
248 String first = GetName();
249 short externIdx = book.getExternalSheetIndex(book.getSheetIndex(sheetName));
250 if (look == ':') {
251 Match(':');
252 String second=GetName();
253 if (look == '!') {
254 //The sheet name was included in both of the areas. Only really
255 //need it once
256 Match('!');
257 String third=GetName();
258
259 if (!sheetName.equals(second))
260 throw new RuntimeException("Unhandled double sheet reference.");
261
262 return new Area3DPtg(first+":"+third,externIdx);
263 }
264 return new Area3DPtg(first+":"+second,externIdx);
265 }
266 return new Ref3DPtg(first,externIdx);
267 }
268 if (name.equalsIgnoreCase("TRUE") || name.equalsIgnoreCase("FALSE")) {
269 return new BoolPtg(name.toUpperCase());
270 }
271
272 // This can be either a cell ref or a named range
273 // Try to spot which it is
274 boolean cellRef = CELL_REFERENCE_PATTERN.matcher(name).matches();
275
276 if (cellRef) {
277 return new RefPtg(name);
278 }
279
280 for(int i = 0; i < book.getNumberOfNames(); i++) {
281 // named range name matching is case insensitive
282 if(book.getNameAt(i).getNameName().equalsIgnoreCase(name)) {
283 return new NamePtg(name, book);
284 }
285 }
286 throw new FormulaParseException("Found reference to named range \""
287 + name + "\", but that named range wasn't defined!");
288 }
289
290 /**
291 * Note - Excel function names are 'case aware but not case sensitive'. This method may end
292 * up creating a defined name record in the workbook if the specified name is not an internal
293 * Excel function, and has not been encountered before.
294 *
295 * @param name case preserved function name (as it was entered/appeared in the formula).
296 */
297 private ParseNode function(String name) {
298 NamePtg nameToken = null;
299 // Note regarding parameter -
300 if(!AbstractFunctionPtg.isInternalFunctionName(name)) {
301 // external functions get a Name token which points to a defined name record
302 nameToken = new NamePtg(name, this.book);
303
304 // in the token tree, the name is more or less the first argument
305 }
306
307 Match('(');
308 ParseNode[] args = Arguments();
309 Match(')');
310
311 return getFunction(name, nameToken, args);
312 }
313
314 /**
315 * Generates the variable function ptg for the formula.
316 * <p>
317 * For IF Formulas, additional PTGs are added to the tokens
318 * @param name
319 * @param numArgs
320 * @return Ptg a null is returned if we're in an IF formula, it needs extreme manipulation and is handled in this function
321 */
322 private ParseNode getFunction(String name, NamePtg namePtg, ParseNode[] args) {
323
324 FunctionMetadata fm = FunctionMetadataRegistry.getFunctionByName(name.toUpperCase());
325 int numArgs = args.length;
326 if(fm == null) {
327 if (namePtg == null) {
328 throw new IllegalStateException("NamePtg must be supplied for external functions");
329 }
330 // must be external function
331 ParseNode[] allArgs = new ParseNode[numArgs+1];
332 allArgs[0] = new ParseNode(namePtg);
333 System.arraycopy(args, 0, allArgs, 1, numArgs);
334 return new ParseNode(new FuncVarPtg(name, (byte)(numArgs+1)), allArgs);
335 }
336
337 if (namePtg != null) {
338 throw new IllegalStateException("NamePtg no applicable to internal functions");
339 }
340 boolean isVarArgs = !fm.hasFixedArgsLength();
341 int funcIx = fm.getIndex();
342 validateNumArgs(args.length, fm);
343
344 AbstractFunctionPtg retval;
345 if(isVarArgs) {
346 retval = new FuncVarPtg(name, (byte)numArgs);
347 } else {
348 retval = new FuncPtg(funcIx);
349 }
350 return new ParseNode(retval, args);
351 }
352
353 private void validateNumArgs(int numArgs, FunctionMetadata fm) {
354 if(numArgs < fm.getMinParams()) {
355 String msg = "Too few arguments to function '" + fm.getName() + "'. ";
356 if(fm.hasFixedArgsLength()) {
357 msg += "Expected " + fm.getMinParams();
358 } else {
359 msg += "At least " + fm.getMinParams() + " were expected";
360 }
361 msg += " but got " + numArgs + ".";
362 throw new FormulaParseException(msg);
363 }
364 if(numArgs > fm.getMaxParams()) {
365 String msg = "Too many arguments to function '" + fm.getName() + "'. ";
366 if(fm.hasFixedArgsLength()) {
367 msg += "Expected " + fm.getMaxParams();
368 } else {
369 msg += "At most " + fm.getMaxParams() + " were expected";
370 }
371 msg += " but got " + numArgs + ".";
372 throw new FormulaParseException(msg);
373 }
374 }
375
376 private static boolean isArgumentDelimiter(char ch) {
377 return ch == ',' || ch == ')';
378 }
379
380 /** get arguments to a function */
381 private ParseNode[] Arguments() {
382 //average 2 args per function
383 List temp = new ArrayList(2);
384 SkipWhite();
385 if(look == ')') {
386 return ParseNode.EMPTY_ARRAY;
387 }
388
389 boolean missedPrevArg = true;
390 int numArgs = 0;
391 while (true) {
392 SkipWhite();
393 if (isArgumentDelimiter(look)) {
394 if (missedPrevArg) {
395 temp.add(new ParseNode(MissingArgPtg.instance));
396 numArgs++;
397 }
398 if (look == ')') {
399 break;
400 }
401 Match(',');
402 missedPrevArg = true;
403 continue;
404 }
405 temp.add(comparisonExpression());
406 numArgs++;
407 missedPrevArg = false;
408 SkipWhite();
409 if (!isArgumentDelimiter(look)) {
410 throw expected("',' or ')'");
411 }
412 }
413 ParseNode[] result = new ParseNode[temp.size()];
414 temp.toArray(result);
415 return result;
416 }
417
418 /** Parse and Translate a Math Factor */
419 private ParseNode powerFactor() {
420 ParseNode result = percentFactor();
421 while(true) {
422 SkipWhite();
423 if(look != '^') {
424 return result;
425 }
426 Match('^');
427 ParseNode other = percentFactor();
428 result = new ParseNode(PowerPtg.instance, result, other);
429 }
430 }
431
432 private ParseNode percentFactor() {
433 ParseNode result = parseSimpleFactor();
434 while(true) {
435 SkipWhite();
436 if(look != '%') {
437 return result;
438 }
439 Match('%');
440 result = new ParseNode(PercentPtg.instance, result);
441 }
442 }
443
444
445 /**
446 * factors (without ^ or % )
447 */
448 private ParseNode parseSimpleFactor() {
449 SkipWhite();
450 switch(look) {
451 case '#':
452 return new ParseNode(parseErrorLiteral());
453 case '-':
454 Match('-');
455 return new ParseNode(UnaryMinusPtg.instance, powerFactor());
456 case '+':
457 Match('+');
458 return new ParseNode(UnaryPlusPtg.instance, powerFactor());
459 case '(':
460 Match('(');
461 ParseNode inside = comparisonExpression();
462 Match(')');
463 return new ParseNode(ParenthesisPtg.instance, inside);
464 case '"':
465 return new ParseNode(parseStringLiteral());
466 }
467 if (IsAlpha(look) || look == '\''){
468 return parseFunctionOrIdentifier();
469 }
470 // else - assume number
471 return new ParseNode(parseNumber());
472 }
473
474
475 private Ptg parseNumber() {
476 String number2 = null;
477 String exponent = null;
478 String number1 = GetNum();
479
480 if (look == '.') {
481 GetChar();
482 number2 = GetNum();
483 }
484
485 if (look == 'E') {
486 GetChar();
487
488 String sign = "";
489 if (look == '+') {
490 GetChar();
491 } else if (look == '-') {
492 GetChar();
493 sign = "-";
494 }
495
496 String number = GetNum();
497 if (number == null) {
498 throw expected("Integer");
499 }
500 exponent = sign + number;
501 }
502
503 if (number1 == null && number2 == null) {
504 throw expected("Integer");
505 }
506
507 return getNumberPtgFromString(number1, number2, exponent);
508 }
509
510
511 private ErrPtg parseErrorLiteral() {
512 Match('#');
513 String part1 = GetName().toUpperCase();
514
515 switch(part1.charAt(0)) {
516 case 'V':
517 if(part1.equals("VALUE")) {
518 Match('!');
519 return ErrPtg.VALUE_INVALID;
520 }
521 throw expected("#VALUE!");
522 case 'R':
523 if(part1.equals("REF")) {
524 Match('!');
525 return ErrPtg.REF_INVALID;
526 }
527 throw expected("#REF!");
528 case 'D':
529 if(part1.equals("DIV")) {
530 Match('/');
531 Match('0');
532 Match('!');
533 return ErrPtg.DIV_ZERO;
534 }
535 throw expected("#DIV/0!");
536 case 'N':
537 if(part1.equals("NAME")) {
538 Match('?'); // only one that ends in '?'
539 return ErrPtg.NAME_INVALID;
540 }
541 if(part1.equals("NUM")) {
542 Match('!');
543 return ErrPtg.NUM_ERROR;
544 }
545 if(part1.equals("NULL")) {
546 Match('!');
547 return ErrPtg.NULL_INTERSECTION;
548 }
549 if(part1.equals("N")) {
550 Match('/');
551 if(look != 'A' && look != 'a') {
552 throw expected("#N/A");
553 }
554 Match(look);
555 // Note - no '!' or '?' suffix
556 return ErrPtg.N_A;
557 }
558 throw expected("#NAME?, #NUM!, #NULL! or #N/A");
559
560 }
561 throw expected("#VALUE!, #REF!, #DIV/0!, #NAME?, #NUM!, #NULL! or #N/A");
562 }
563
564
565 /**
566 * Get a PTG for an integer from its string representation.
567 * return Int or Number Ptg based on size of input
568 */
569 private static Ptg getNumberPtgFromString(String number1, String number2, String exponent) {
570 StringBuffer number = new StringBuffer();
571
572 if (number2 == null) {
573 number.append(number1);
574
575 if (exponent != null) {
576 number.append('E');
577 number.append(exponent);
578 }
579
580 String numberStr = number.toString();
581 int intVal;
582 try {
583 intVal = Integer.parseInt(numberStr);
584 } catch (NumberFormatException e) {
585 return new NumberPtg(numberStr);
586 }
587 if (IntPtg.isInRange(intVal)) {
588 return new IntPtg(intVal);
589 }
590 return new NumberPtg(numberStr);
591 }
592
593 if (number1 != null) {
594 number.append(number1);
595 }
596
597 number.append('.');
598 number.append(number2);
599
600 if (exponent != null) {
601 number.append('E');
602 number.append(exponent);
603 }
604
605 return new NumberPtg(number.toString());
606 }
607
608
609 private StringPtg parseStringLiteral() {
610 Match('"');
611
612 StringBuffer token = new StringBuffer();
613 while (true) {
614 if (look == '"') {
615 GetChar();
616 if (look != '"') {
617 break;
618 }
619 }
620 token.append(look);
621 GetChar();
622 }
623 return new StringPtg(token.toString());
624 }
625
626 /** Parse and Translate a Math Term */
627 private ParseNode Term() {
628 ParseNode result = powerFactor();
629 while(true) {
630 SkipWhite();
631 Ptg operator;
632 switch(look) {
633 case '*':
634 Match('*');
635 operator = MultiplyPtg.instance;
636 break;
637 case '/':
638 Match('/');
639 operator = DividePtg.instance;
640 break;
641 default:
642 return result; // finished with Term
643 }
644 ParseNode other = powerFactor();
645 result = new ParseNode(operator, result, other);
646 }
647 }
648
649 private ParseNode comparisonExpression() {
650 ParseNode result = concatExpression();
651 while (true) {
652 SkipWhite();
653 switch(look) {
654 case '=':
655 case '>':
656 case '<':
657 Ptg comparisonToken = getComparisonToken();
658 ParseNode other = concatExpression();
659 result = new ParseNode(comparisonToken, result, other);
660 continue;
661 }
662 return result; // finished with predicate expression
663 }
664 }
665
666 private Ptg getComparisonToken() {
667 if(look == '=') {
668 Match(look);
669 return EqualPtg.instance;
670 }
671 boolean isGreater = look == '>';
672 Match(look);
673 if(isGreater) {
674 if(look == '=') {
675 Match('=');
676 return GreaterEqualPtg.instance;
677 }
678 return GreaterThanPtg.instance;
679 }
680 switch(look) {
681 case '=':
682 Match('=');
683 return LessEqualPtg.instance;
684 case '>':
685 Match('>');
686 return NotEqualPtg.instance;
687 }
688 return LessThanPtg.instance;
689 }
690
691
692 private ParseNode concatExpression() {
693 ParseNode result = additiveExpression();
694 while (true) {
695 SkipWhite();
696 if(look != '&') {
697 break; // finished with concat expression
698 }
699 Match('&');
700 ParseNode other = additiveExpression();
701 result = new ParseNode(ConcatPtg.instance, result, other);
702 }
703 return result;
704 }
705
706
707 /** Parse and Translate an Expression */
708 private ParseNode additiveExpression() {
709 ParseNode result = Term();
710 while (true) {
711 SkipWhite();
712 Ptg operator;
713 switch(look) {
714 case '+':
715 Match('+');
716 operator = AddPtg.instance;
717 break;
718 case '-':
719 Match('-');
720 operator = SubtractPtg.instance;
721 break;
722 default:
723 return result; // finished with additive expression
724 }
725 ParseNode other = Term();
726 result = new ParseNode(operator, result, other);
727 }
728 }
729
730 //{--------------------------------------------------------------}
731 //{ Parse and Translate an Assignment Statement }
732 /**
733 procedure Assignment;
734 var Name: string[8];
735 begin
736 Name := GetName;
737 Match('=');
738 Expression;
739
740 end;
741 **/
742
743
744 /**
745 * API call to execute the parsing of the formula
746 * @deprecated use Ptg[] FormulaParser.parse(String, HSSFWorkbook) directly
747 */
748 public void parse() {
749 pointer=0;
750 GetChar();
751 _rootNode = comparisonExpression();
752
753 if(pointer <= formulaLength) {
754 String msg = "Unused input [" + formulaString.substring(pointer-1)
755 + "] after attempting to parse the formula [" + formulaString + "]";
756 throw new FormulaParseException(msg);
757 }
758 }
759
760
761 /*********************************
762 * PARSER IMPLEMENTATION ENDS HERE
763 * EXCEL SPECIFIC METHODS BELOW
764 *******************************/
765
766 /** API call to retrive the array of Ptgs created as
767 * a result of the parsing
768 */
769 public Ptg[] getRPNPtg() {
770 return getRPNPtg(FORMULA_TYPE_CELL);
771 }
772
773 public Ptg[] getRPNPtg(int formulaType) {
774 OperandClassTransformer oct = new OperandClassTransformer(formulaType);
775 // RVA is for 'operand class': 'reference', 'value', 'array'
776 oct.transformFormula(_rootNode);
777 return ParseNode.toTokenArray(_rootNode);
778 }
779
780 /**
781 * Convenience method which takes in a list then passes it to the
782 * other toFormulaString signature.
783 * @param book workbook for 3D and named references
784 * @param lptgs list of Ptg, can be null or empty
785 * @return a human readable String
786 */
787 public static String toFormulaString(HSSFWorkbook book, List lptgs) {
788 String retval = null;
789 if (lptgs == null || lptgs.size() == 0) return "#NAME";
790 Ptg[] ptgs = new Ptg[lptgs.size()];
791 ptgs = (Ptg[])lptgs.toArray(ptgs);
792 retval = toFormulaString(book, ptgs);
793 return retval;
794 }
795 /**
796 * Convenience method which takes in a list then passes it to the
797 * other toFormulaString signature. Works on the current
798 * workbook for 3D and named references
799 * @param lptgs list of Ptg, can be null or empty
800 * @return a human readable String
801 */
802 public String toFormulaString(List lptgs) {
803 return toFormulaString(book, lptgs);
804 }
805
806 /**
807 * Static method to convert an array of Ptgs in RPN order
808 * to a human readable string format in infix mode.
809 * @param book workbook for named and 3D references
810 * @param ptgs array of Ptg, can be null or empty
811 * @return a human readable String
812 */
813 public static String toFormulaString(HSSFWorkbook book, Ptg[] ptgs) {
814 if (ptgs == null || ptgs.length == 0) {
815 // TODO - what is the justification for returning "#NAME" (which is not "#NAME?", btw)
816 return "#NAME";
817 }
818 Stack stack = new Stack();
819
820 for (int i=0 ; i < ptgs.length; i++) {
821 Ptg ptg = ptgs[i];
822 // TODO - what about MemNoMemPtg?
823 if(ptg instanceof MemAreaPtg || ptg instanceof MemFuncPtg || ptg instanceof MemErrPtg) {
824 // marks the start of a list of area expressions which will be naturally combined
825 // by their trailing operators (e.g. UnionPtg)
826 // TODO - put comment and throw exception in toFormulaString() of these classes
827 continue;
828 }
829 if (ptg instanceof ParenthesisPtg) {
830 String contents = (String)stack.pop();
831 stack.push ("(" + contents + ")");
832 continue;
833 }
834 if (ptg instanceof AttrPtg) {
835 AttrPtg attrPtg = ((AttrPtg) ptg);
836 if (attrPtg.isOptimizedIf() || attrPtg.isOptimizedChoose() || attrPtg.isGoto()) {
837 continue;
838 }
839 if (attrPtg.isSpace()) {
840 // POI currently doesn't render spaces in formulas
841 continue;
842 // but if it ever did, care must be taken:
843 // tAttrSpace comes *before* the operand it applies to, which may be consistent
844 // with how the formula text appears but is against the RPN ordering assumed here
845 }
846 if (attrPtg.isSemiVolatile()) {
847 // similar to tAttrSpace - RPN is violated
848 continue;
849 }
850 if (attrPtg.isSum()) {
851 String[] operands = getOperands(stack, attrPtg.getNumberOfOperands());
852 stack.push(attrPtg.toFormulaString(operands));
853 continue;
854 }
855 throw new RuntimeException("Unexpected tAttr: " + attrPtg.toString());
856 }
857
858 if (! (ptg instanceof OperationPtg)) {
859 stack.push(ptg.toFormulaString(book));
860 continue;
861 }
862
863 OperationPtg o = (OperationPtg) ptg;
864 String[] operands = getOperands(stack, o.getNumberOfOperands());
865 stack.push(o.toFormulaString(operands));
866 }
867 if(stack.isEmpty()) {
868 // inspection of the code above reveals that every stack.pop() is followed by a
869 // stack.push(). So this is either an internal error or impossible.
870 throw new IllegalStateException("Stack underflow");
871 }
872 String result = (String) stack.pop();
873 if(!stack.isEmpty()) {
874 // Might be caused by some tokens like AttrPtg and Mem*Ptg, which really shouldn't
875 // put anything on the stack
876 throw new IllegalStateException("too much stuff left on the stack");
877 }
878 return result;
879 }
880
881 private static String[] getOperands(Stack stack, int nOperands) {
882 String[] operands = new String[nOperands];
883
884 for (int j = nOperands-1; j >= 0; j--) { // reverse iteration because args were pushed in-order
885 if(stack.isEmpty()) {
886 String msg = "Too few arguments supplied to operation. Expected (" + nOperands
887 + ") operands but got (" + (nOperands - j - 1) + ")";
888 throw new IllegalStateException(msg);
889 }
890 operands[j] = (String) stack.pop();
891 }
892 return operands;
893 }
894 /**
895 * Static method to convert an array of Ptgs in RPN order
896 * to a human readable string format in infix mode. Works
897 * on the current workbook for named and 3D references.
898 * @param ptgs array of Ptg, can be null or empty
899 * @return a human readable String
900 */
901 public String toFormulaString(Ptg[] ptgs) {
902 return toFormulaString(book, ptgs);
903 }
904 }
Save This Page