1 /
55
56
57 package org.apache.poi.hssf.model;
58
59 import org.apache.poi.hssf.record.formula.*;
60 import org.apache.poi.hssf.util.SheetReferences;
61
62 import java.util.ArrayList;
63 import java.util.List;
64
65
66
80 public class FormulaParser {
81
82 public static int FORMULA_TYPE_CELL = 0;
83 public static int FORMULA_TYPE_SHARED = 1;
84 public static int FORMULA_TYPE_ARRAY =2;
85 public static int FORMULA_TYPE_CONDFOMRAT = 3;
86 public static int FORMULA_TYPE_NAMEDRANGE = 4;
87
88 private String formulaString;
89 private int pointer=0;
90 private int formulaLength;
91
92 private List tokens = new java.util.Stack();
93
94 private List result = new ArrayList();
95 private int numParen;
96
97 private static char TAB = '\t';
98 private static char CR = '\n';
99
100 private char look;
101
102 private Workbook book;
103
104
105
110 public FormulaParser(String formula, Workbook book){
111 formulaString = formula;
112 pointer=0;
113 this.book = book;
114 formulaLength = formulaString.length();
115 }
116
117
118
119 private void GetChar() {
120
121
122
123 if (pointer == formulaLength) {
124 look = (char)0;
125 return;
126 }
127 look=formulaString.charAt(pointer++);
128
129 }
130
131
132
133 private void Error(String s) {
134 System.out.println("Error: "+s);
135 }
136
137
138
139
140 private void Abort(String s) {
141 Error(s);
142
143 throw new RuntimeException("Cannot Parse, sorry : "+s);
144 }
145
146
147
148
149 private void Expected(String s) {
150 Abort(s + " Expected");
151 }
152
153
154
155
156 private boolean IsAlpha(char c) {
157 return Character.isLetter(c) || c == '$';
158 }
159
160
161
162
163 private boolean IsDigit(char c) {
164
165 return Character.isDigit(c);
166 }
167
168
169
170
171 private boolean IsAlNum(char c) {
172 return (IsAlpha(c) || IsDigit(c));
173 }
174
175
176
177
178 private boolean IsAddop( char c) {
179 return (c =='+' || c =='-');
180 }
181
182
183
184 private boolean IsWhite( char c) {
185 return (c ==' ' || c== TAB);
186 }
187
188
189
190
191 private void SkipWhite() {
192 while (IsWhite(look)) {
193 GetChar();
194 }
195 }
196
197
198
199
200 private void Match(char x) {
201 if (look != x) {
202 Expected("" + x + "");
203 }else {
204 GetChar();
205 SkipWhite();
206 }
207 }
208
209
210 private String GetName() {
211 StringBuffer Token = new StringBuffer();
212 if (!IsAlpha(look)) {
213 Expected("Name");
214 }
215 while (IsAlNum(look)) {
216 Token = Token.append(Character.toUpperCase(look));
217 GetChar();
218 }
219 SkipWhite();
220 return Token.toString();
221 }
222
223
225 private String GetNameAsIs() {
226 StringBuffer Token = new StringBuffer();
227 if (!IsAlpha(look)) {
228 Expected("Name");
229 }
230 while (IsAlNum(look) || IsWhite(look)) {
231 Token = Token.append(look);
232 GetChar();
233 }
234 return Token.toString();
235 }
236
237
238
239 private String GetNum() {
240 String Value ="";
241 if (!IsDigit(look)) Expected("Integer");
242 while (IsDigit(look)){
243 Value = Value + look;
244 GetChar();
245 }
246 SkipWhite();
247 return Value;
248 }
249
250
251 private void Emit(String s){
252 System.out.print(TAB+s);
253 }
254
255
256 private void EmitLn(String s) {
257 Emit(s);
258 System.out.println();;
259 }
260
261
262 private void Ident() {
263 String name;
264 name = GetName();
265 if (look == '('){
266
267 function(name);
268 } else if (look == ':') {
269 String first = name;
270 Match(':');
271 String second = GetName();
272 tokens.add(new AreaPtg(first+":"+second));
273 } else if (look == '!') {
274 Match('!');
275 String sheetName = name;
276 String first = GetName();
277 short externIdx = book.checkExternSheet(book.getSheetIndex(sheetName));
278 if (look == ':') {
279 Match(':');
280 String second=GetName();
281
282 tokens.add(new Area3DPtg(first+":"+second,externIdx));
283 } else {
284 tokens.add(new Ref3DPtg(first,externIdx));
285 }
286 } else {
287
288 boolean cellRef = true ;
289 boolean boolLit = (name.equals("TRUE") || name.equals("FALSE"));
290 if (boolLit) {
291 tokens.add(new BoolPtg(name));
292 } else if (cellRef) {
293 tokens.add(new ReferencePtg(name));
294 }else {
295
296 }
297 }
298 }
299
300 private void function(String name) {
301 Match('(');
302 int numArgs = Arguments();
303 Match(')');
304 tokens.add(getFunction(name,(byte)numArgs));
305 }
306
307 private Ptg getFunction(String name,byte numArgs) {
308 Ptg retval = null;
309
310 if (name.equals("IF")) {
311 AttrPtg ptg = new AttrPtg();
312 ptg.setData((short)6);
313 ptg.setOptimizedIf(true);
314 retval = ptg;
315 } else {
316 retval = new FuncVarPtg(name,numArgs);
317 }
318
319 return retval;
320 }
321
322
323 private int Arguments() {
324 int numArgs = 0;
325 if (look != ')') {
326 numArgs++;
327 Expression();
328 }
329 while (look == ',' || look == ';') {
330 if(look == ',') {
331 Match(',');
332 }
333 else {
334 Match(';');
335 }
336 Expression();
337 numArgs++;
338 }
339 return numArgs;
340 }
341
342
343 private void Factor() {
344 if (look == '(' ) {
345 Match('(');
346 Expression();
347 Match(')');
348 tokens.add(new ParenthesisPtg());
349 return;
350 } else if (IsAlpha(look)){
351 Ident();
352 } else if(look == '"') {
353 StringLiteral();
354 } else {
355
356 String number = GetNum();
357 if (look=='.') {
358 Match('.');
359 String decimalPart = null;
360 if (IsDigit(look)) number = number +"."+ GetNum();
361 tokens.add(new NumberPtg(number));
362 } else {
363 tokens.add(new IntPtg(number));
364 }
365 }
366 }
367
368 private void StringLiteral() {
369 Match('"');
370 String name= GetNameAsIs();
371 Match('"');
372 tokens.add(new StringPtg(name));
373 }
374
375
376 private void Multiply(){
377 Match('*');
378 Factor();
379 tokens.add(new MultiplyPtg());
380
381 }
382
383
384
385 private void Divide() {
386 Match('/');
387 Factor();
388 tokens.add(new DividePtg());
389
390 }
391
392
393
394 private void Term(){
395 Factor();
396 while (look == '*' || look == '/' || look == '^' || look == '&' || look == '=' ) {
397
398 if (look == '*') Multiply();
399 if (look == '/') Divide();
400 if (look == '^') Power();
401 if (look == '&') Concat();
402 if (look == '=') Equal();
403 }
404 }
405
406
407 private void Add() {
408 Match('+');
409 Term();
410 tokens.add(new AddPtg());
411 }
412
413
414 private void Concat() {
415 Match('&');
416 Term();
417 tokens.add(new ConcatPtg());
418 }
419
420
421 private void Equal() {
422 Match('=');
423 Term();
424 tokens.add(new EqualPtg());
425 }
426
427
428 private void Subtract() {
429 Match('-');
430 Term();
431 tokens.add(new SubtractPtg());
432 }
433
434 private void Power() {
435 Match('^');
436 Term();
437 tokens.add(new PowerPtg());
438 }
439
440
441
442 private void Expression() {
443 if (IsAddop(look)) {
444 EmitLn("CLR D0");
445 } else {
446 Term();
447 }
448 while (IsAddop(look)) {
449 if ( look == '+' ) Add();
450 if (look == '-') Subtract();
451 if (look == '*') Multiply();
452 if (look == '/') Divide();
453 }
454 }
455
456
457
458
459
469
470
471
472
473 private void init() {
474 GetChar();
475 SkipWhite();
476 }
477
478
481 public void parse() {
482 synchronized (tokens) {
483 init();
484 Expression();
485 }
486 }
487
488
489
493
494
497 public Ptg[] getRPNPtg() {
498 return getRPNPtg(FORMULA_TYPE_CELL);
499 }
500
501 public Ptg[] getRPNPtg(int formulaType) {
502 Node node = createTree();
503 setRootLevelRVA(node, formulaType);
504 setParameterRVA(node,formulaType);
505 return (Ptg[]) tokens.toArray(new Ptg[0]);
506 }
507
508 private void setRootLevelRVA(Node n, int formulaType) {
509
510 Ptg p = (Ptg) n.getValue();
511 if (formulaType == this.FORMULA_TYPE_NAMEDRANGE) {
512 if (p.getDefaultOperandClass() == Ptg.CLASS_REF) {
513 setClass(n,Ptg.CLASS_REF);
514 } else {
515 setClass(n,Ptg.CLASS_ARRAY);
516 }
517 } else {
518 setClass(n,Ptg.CLASS_VALUE);
519 }
520
521 }
522
523 private void setParameterRVA(Node n, int formulaType) {
524 Ptg p = (Ptg) n.getValue();
525 if (p instanceof AbstractFunctionPtg) {
526 int numOperands = n.getNumChildren();
527 for (int i =0;i<n.getNumChildren();i++) {
528 setParameterRVA(n.getChild(i),((AbstractFunctionPtg)p).getParameterClass(i),formulaType);
529 if (n.getChild(i).getValue() instanceof AbstractFunctionPtg) {
530 setParameterRVA(n.getChild(i),formulaType);
531 }
532 }
533 } else {
534 for (int i =0;i<n.getNumChildren();i++) {
535 setParameterRVA(n.getChild(i),formulaType);
536 }
537 }
538 }
539 private void setParameterRVA(Node n, int expectedClass,int formulaType) {
540 Ptg p = (Ptg) n.getValue();
541 if (expectedClass == Ptg.CLASS_REF) {
542 if (p.getDefaultOperandClass() == Ptg.CLASS_REF ) {
543 setClass(n, Ptg.CLASS_REF);
544 }
545 if (p.getDefaultOperandClass() == Ptg.CLASS_VALUE) {
546 if (formulaType==FORMULA_TYPE_CELL || formulaType == FORMULA_TYPE_SHARED) {
547 setClass(n,Ptg.CLASS_VALUE);
548 } else {
549 setClass(n,Ptg.CLASS_ARRAY);
550 }
551 }
552 if (p.getDefaultOperandClass() == Ptg.CLASS_ARRAY ) {
553 setClass(n, Ptg.CLASS_ARRAY);
554 }
555 } else if (expectedClass == Ptg.CLASS_VALUE) {
556 if (formulaType == FORMULA_TYPE_NAMEDRANGE) {
557 setClass(n,Ptg.CLASS_ARRAY) ;
558 } else {
559 setClass(n,Ptg.CLASS_VALUE);
560 }
561 } else {
562 if (p.getDefaultOperandClass() == Ptg.CLASS_VALUE &&
563 (formulaType==FORMULA_TYPE_CELL || formulaType == FORMULA_TYPE_SHARED)) {
564 setClass(n,Ptg.CLASS_VALUE);
565 } else {
566 setClass(n,Ptg.CLASS_ARRAY);
567 }
568 }
569 }
570
571 private void setClass(Node n, byte theClass) {
572 Ptg p = (Ptg) n.getValue();
573 if (p instanceof AbstractFunctionPtg || !(p instanceof OperationPtg)) {
574 p.setClass(theClass);
575 } else {
576 for (int i =0;i<n.getNumChildren();i++) {
577 setClass(n.getChild(i),theClass);
578 }
579 }
580 }
581
586 public static String toFormulaString(SheetReferences refs, List lptgs) {
587 String retval = null;
588 if (lptgs == null || lptgs.size() == 0) return "#NAME";
589 Ptg[] ptgs = new Ptg[lptgs.size()];
590 ptgs = (Ptg[])lptgs.toArray(ptgs);
591 retval = toFormulaString(refs, ptgs);
592 return retval;
593 }
594
595
599 public static String toFormulaString(SheetReferences refs, Ptg[] ptgs) {
600 if (ptgs == null || ptgs.length == 0) return "#NAME";
601 java.util.Stack stack = new java.util.Stack();
602 int numPtgs = ptgs.length;
603 OperationPtg o;
604 int numOperands;
605 String result=null;
606 String[] operands;
607 AttrPtg ifptg = null;
608 for (int i=0;i<numPtgs;i++) {
609
610
611 if (ptgs[i] instanceof OperationPtg && i>0) {
612 o = (OperationPtg) ptgs[i];
613
614 if (o instanceof AttrPtg && ((AttrPtg)o).isOptimizedIf()) {
615 ifptg=(AttrPtg)o;
616 } else {
617
618 numOperands = o.getNumberOfOperands();
619 operands = new String[numOperands];
620
621 for (int j=0;j<numOperands;j++) {
622 operands[numOperands-j-1] = (String) stack.pop();
623 }
624
625 if ( (o instanceof AbstractFunctionPtg) &&
626 ((AbstractFunctionPtg)o).getName().equals("specialflag") &&
627 ifptg != null
628 ) {
629
630 result = ifptg.toFormulaString(
631 new String[] {(o.toFormulaString(operands))}
632 );
633 ifptg = null;
634 } else {
635 result = o.toFormulaString(operands);
636 }
637 stack.push(result);
638 }
639
640
641 } else {
642 stack.push(ptgs[i].toFormulaString(refs));
643 }
644 }
645 return (String) stack.pop();
646 }
647
648 private Node createTree() {
649 java.util.Stack stack = new java.util.Stack();
650 int numPtgs = tokens.size();
651 OperationPtg o;
652 int numOperands;
653 Node[] operands;
654 for (int i=0;i<numPtgs;i++) {
655 if (tokens.get(i) instanceof OperationPtg) {
656
657 o = (OperationPtg) tokens.get(i);
658 numOperands = o.getNumberOfOperands();
659 operands = new Node[numOperands];
660 for (int j=0;j<numOperands;j++) {
661 operands[numOperands-j-1] = (Node) stack.pop();
662 }
663 Node result = new Node(o);
664 result.setChildren(operands);
665 stack.push(result);
666 } else {
667 stack.push(new Node((Ptg)tokens.get(i)));
668 }
669 }
670 return (Node) stack.pop();
671 }
672
673
676 public String toString() {
677 SheetReferences refs = book.getSheetReferences();
678 StringBuffer buf = new StringBuffer();
679 for (int i=0;i<tokens.size();i++) {
680 buf.append( ( (Ptg)tokens.get(i)).toFormulaString(refs));
681 buf.append(' ');
682 }
683 return buf.toString();
684 }
685
686 }
687 class Node {
688 private Ptg value=null;
689 private Node[] children=new Node[0];
690 private int numChild=0;
691 public Node(Ptg val) {
692 value = val;
693 }
694 public void setChildren(Node[] child) {children = child;numChild=child.length;}
695 public int getNumChildren() {return numChild;}
696 public Node getChild(int number) {return children[number];}
697 public Ptg getValue() {return value;}
698 }
699