1 /*
2 * GeoVISTA Center (Penn State, Dept. of Geography)
3 * Copyright (c), 1999 - 2002, GeoVISTA Center
4 * All Rights Researved.
5 *
6 * Description:
7 * - For formula processing
8 * - The code reference to code written by Hua Zhong from Columbia University
9 * Apr 2, 2003
10 * Time: 10:42:12 PM
11 * @author Hua Zhong
12 * @author Jin Chen
13 */
14
15 package edu.psu.geovista.app.spreadsheet.formula;
16
17 import edu.psu.geovista.app.spreadsheet.functions.*;
18
19 import javax.swing.*;
20 import javax.swing.table.TableModel;
21 import java.util.*;
22 import java.io.*;
23 import java.awt.Point ;
24
25 import edu.psu.geovista.app.spreadsheet.exception.ParserException;
26 import edu.psu.geovista.app.spreadsheet.util.Debug;
27 import edu.psu.geovista.app.spreadsheet.table.SSTableModel;
28 import edu.psu.geovista.app.spreadsheet.exception.NoReferenceException;
29 import edu.psu.geovista.app.spreadsheet.SpreadSheetBean;
30 import edu.psu.geovista.app.spreadsheet.table.SSTable;
31
32
33
34 public class Formula {
35 // a static hash table for function handlers
36 static private HashMap funcTable;
37
38 //jin:
39 private Number value;
40 // the raw edu.psu.geovista.app.spreadsheet.formula string
41 private String formulaString; // jin: the expression
42 //private String expression;
43
44
45 // tokens in order of postfix - used in calculation
46 private Cell owner;//direct owner
47 private HashSet owners; // all owners, including direct and indirect owner
48 private LinkedList nodes; // The nodes make up of the fomula.
49 private LinkedList postfix;
50
51 // where the edu.psu.geovista.app.spreadsheet.formula is - important to calculate rel_addr
52 private int row, col;
53
54 // error message
55 private ParserException error;
56
57 // whether this edu.psu.geovista.app.spreadsheet.formula needs recalculation
58 private boolean needsRecalc;
59
60 /***
61 * edu.psu.geovista.app.spreadsheet.formula.Formula contructor.
62 *
63 * This is used to construct a edu.psu.geovista.app.spreadsheet.formula.Formula object without any
64 * parsing process.
65 *
66 * @param input the edu.psu.geovista.app.spreadsheet.formula string
67 * @param row the current row where the edu.psu.geovista.app.spreadsheet.formula is stored
68 * @param col the current column where the forluma is stored
69 * @param e a edu.psu.geovista.app.spreadsheet.exception.ParserException
70 */
71 public Formula(String input, int row, int col, ParserException e) {
72 formulaString = input.toUpperCase();
73 this.col = col;
74 this.row = row;
75 error = e;
76 }
77
78 /***
79 * edu.psu.geovista.app.spreadsheet.formula.Formula contructor.
80 *
81 * Parse the input string and translate into postfix form.
82 *
83 * @param input the edu.psu.geovista.app.spreadsheet.formula string
84 * @param row the current row where the edu.psu.geovista.app.spreadsheet.formula is stored
85 * @param col the current column where the forluma is stored
86 * @edu.psu.geovista.app.spreadsheet.exception edu.psu.geovista.app.spreadsheet.exception.ParserException
87 * @see #toPostfix
88 */
89 public Formula(Cell owner, String input, int row, int col) throws ParserException {
90
91 this.col = col;
92 this.row = row;
93 formulaString = input.toUpperCase();
94 try {
95 this.owner =owner;
96 this.getOwners().add(owner);
97 // tokenize and convert the edu.psu.geovista.app.spreadsheet.formula to postfix form (a list of edu.psu.geovista.app.spreadsheet.formula.Node)
98 nodes= tokenize(formulaString);
99 //Debug.println("edu.psu.geovista.app.spreadsheet.formula.Formula() nodes: "+nodes);
100 //dependency = createDependency(nodes);//this depend on cells in dependency to evaluate
101 //Debug.println("Dependency: "+dependency);
102 postfix = toPostfix(convertParams(nodes));
103 //this.showTokens(postfix);
104 Debug.println("Postfix: "+postfix);
105 }catch (ParserException e) {
106 Debug.println("edu.psu.geovista.app.spreadsheet.formula.Formula constructor: "+e);
107 throwError(e);
108 }
109 }
110 /*
111 public Formula(HashSet owners){
112 this.owners =owners;
113
114 } */
115
116
117 /*********************************************************************
118 * Return value *
119 ********************************************************************/
120 public Number getValue(){
121 return this.value ;
122 }
123 /*public String getExpression(){
124 return this.expression ;
125 } */
126 /***
127 * generate expression based on given node
128 * Not yet
129 */
130 private void evalueExpression(LinkedList nodes){
131 if (this.isBad() ){//for invalid edu.psu.geovista.app.spreadsheet.formula not passing
132 return;
133 }
134 StringBuffer sb=new StringBuffer();
135 Iterator it=nodes.iterator() ;
136 while(it.hasNext() ){
137 Node node=(Node)it.next() ;
138 if (node.isType(Node.REL_ADDR) )
139 { String addrs;
140 Cell cell=node.getReference() ;
141 Point address=cell.getViewAddress() ; //address in Table
142 if (address!=null){
143 /*int x=(int)address.getX() ;
144 int y=(int)address.getY();
145 //String row=edu.psu.geovista.app.spreadsheet.formula.Node.translateRow(x );
146 String row=Integer.toString(x);
147 String col=Cell.translateVVColumn(y);//translate to String. e.g. 1=>A
148 addrs=col+row;
149 System.out.print("node:("+addrs+")"); */
150 sb.append(cell.getViewAddressText() );
151 }
152 else{
153 sb.append("#REF!");
154 }
155 }
156 else if(node.isType(Node.ABS_ADDR )){
157
158 Point address=node.getAddress() ;//absolute address
159 int x=(int)address.getX() ; //view's row
160 int y=(int)address.getY(); //view's col
161 String row=translateRow(x);
162 String col=SSTable.translateVVColumn(y);///.translateColumn(y);
163 String addrs="$"+col+"$"+row;
164 System.out.print("node:("+addrs+")");
165 sb.append(addrs);
166 }
167 else if(node.isType(Node.COLON )){ // e.g.(A1:A10)
168 Point sp=null,ep=null;//start point, end point of the range
169 Node start=node.getNextRange() ;//start(upLeft) of the range
170 Node end=start.getNextRange() ;//end (downRight) of the range
171 StringBuffer address=new StringBuffer();
172 if (start.getType() ==Node.REL_ADDR ){
173 String addr=null;
174 Cell cell=start.getReference() ;
175 sp=cell.getViewAddress();
176 String spAddr=Cell.getRelCellAddress(sp);
177 address.append(spAddr);
178
179 }
180 else if(start.getType() ==Node.ABS_ADDR ){
181 sp=start.getAddress();
182 String spAddr=Cell.getAbsCellAddress(sp);
183 address.append(spAddr);
184
185 }
186 else{
187 assert false: "Unable to handle the expression";
188 //throw edu.psu.geovista.app.spreadsheet.exception: unable to handle the expression
189 }
190
191 if (end.getType() ==Node.REL_ADDR ){
192 Cell cell=end.getReference() ;
193 ep=cell.getViewAddress();
194 address.append(":");
195 String spAddr=Cell.getRelCellAddress(ep);
196 address.append(spAddr);
197 }
198 else if(end.getType() ==Node.ABS_ADDR ){
199 ep=end.getAddress() ;
200 address.append(":");
201 String spAddr=Cell.getAbsCellAddress(ep);
202 address.append(spAddr);
203 }
204 else{
205
206 assert false: "Unable to handle the expression";
207 //throw edu.psu.geovista.app.spreadsheet.exception: unable to handle the expression
208 }
209 sb.append(address.toString() );
210
211 }
212 else{
213 String s=node.getData() ;
214 sb.append(s);
215 System.out.print(s+" ");
216 }
217 } //while
218 this.formulaString =sb.toString() ;
219 }
220
221
222
223 /***
224 * Check for bad edu.psu.geovista.app.spreadsheet.formula.
225 *
226 * @return boolean true if postfix
227 */
228 public boolean isBad() {
229 // postfix was set to null when there was any error
230 // in processing the edu.psu.geovista.app.spreadsheet.formula string
231 return postfix == null;
232 }
233
234 public void setBad() {
235 postfix=null;
236 }
237
238 /***
239 * Check whether needs a recalc
240 *
241 * @return boolean true if needs recalculation
242 */
243 public boolean needsRecalc() {
244 return needsRecalc;
245 }
246
247 /***
248 * Mark it as needsRecalc
249 *
250 * @parem boolean true if needs recalculation
251 */
252 public void setNeedsRecalc(boolean needs) {
253 needsRecalc = needs;
254 }
255
256
257
258
259 /***
260 * Tokenize the edu.psu.geovista.app.spreadsheet.formula string into a list of Nodes.
261 *
262 * @param input the input string to tokenize
263 * @edu.psu.geovista.app.spreadsheet.exception edu.psu.geovista.app.spreadsheet.exception.ParserException
264 *
265 * @see edu.psu.geovista.app.spreadsheet.formula.Node
266 */
267 private LinkedList tokenize(String input) throws ParserException {
268 LinkedList tokens = new LinkedList();
269 //Stack stack = new Stack(); Jin: no use
270 final Node zero = new Node();
271 zero.setType(Node.NUMBER);
272 zero.setNumber(0);
273 // input.toUpperCase();
274
275 int cur = 0;
276 int lastType = Node.DEFAULT;
277 Node lastToken = null;
278 // boolean hasRange = false; // has a pending address range
279 int nParen = 0; // balance of parens
280
281 while (cur < input.length()) {
282 Node node = new Node();
283
284 try {
285 char c = input.charAt(cur++);
286 node.setData(String.valueOf(c));
287 //1.
288 if (Character.isLetter(c)) {
289 // 1. edu.psu.geovista.app.spreadsheet.functions.Function or Relative Address
290 node.setType(Node.FUNCTION);
291 node.setParams(new LinkedList());
292
293 // get all preceding letters
294 while (cur < input.length() &&
295 Character.isLetter(input.charAt(cur)))
296 node.appendData(input.charAt(cur++));
297
298 if (Character.isDigit(input.charAt(cur))) {
299 //2. !!! Relative address
300 Cell owner=this.getOwner() ;
301 SSTable table=owner.getDataModel().getTable() ;
302 node.setType(Node.REL_ADDR);
303 String scol=node.getData(); //Column name in view. e.g.: "B"
304 //int refcol=Cell.translateColumn(scol) ;//Model col
305 int refcol=table.translateColumn(scol) ;//Model col
306 //node.setCol(refcol);
307 node.setData("");
308 while (cur < input.length() &&
309 Character.isDigit(input.charAt(cur)))
310 node.appendData(input.charAt(cur++));
311 // relative row
312 int refrow=translateRow(node.getData()) ;//absolute row number
313 //node.setRow(refrow- row); //relative postion regard to current row(row, col)
314 //node.setRow(refrow);
315 node.setData(null);
316 //Jin->
317 Cell cell=(Cell)this.getCell(refrow,refcol);
318 //System.out.println("set Refrence ("+refrow+","+refcol+") ="+cell.getValue() );
319 node.setReference(cell);
320 //Jin<-
321 }
322 }else if (Character.isDigit(c) || c == '.') { //Number or .
323 /*||
324 (lastType == edu.psu.geovista.app.spreadsheet.formula.Node.DEFAULT ||
325 lastType == edu.psu.geovista.app.spreadsheet.formula.Node.LPAREN || lastType == edu.psu.geovista.app.spreadsheet.formula.Node.COMMA) &&
326 (c == '+' || c == '-')) */
327 // Numbers
328 while (cur < input.length() &&
329 (Character.isDigit(input.charAt(cur)) ||
330 input.charAt(cur) == '.'))
331 // OK, we don't check for input like "3.56.4"
332 // this will be checked below by parseNumber
333 node.appendData(input.charAt(cur++));
334
335 try {
336 try {
337 node.setNumber(Integer.parseInt(node.getData()));
338 }
339 catch (NumberFormatException e) {
340 node.setNumber(Float.parseFloat(node.getData()));
341 }
342 node.setType(Node.NUMBER);
343 }catch (NumberFormatException e) {
344 // invalid number format
345 throwError("#NUM?");
346 }
347 }else if (c == '(') {
348 nParen++;
349 node.setType(Node.LPAREN);
350 }else if (c == ')') {
351 nParen--;
352 node.setType(Node.RPAREN);
353 }else if (c == ',') {
354 node.setType(Node.COMMA);
355 }else if (c == ':') {
356
357 node.setPending(true);
358 node.setType(Node.COLON );
359
360 Node prev = null;
361
362 try {
363 prev = (Node)tokens.removeLast();
364 }
365 catch (Exception e) {
366 throwError("#ADDR?");
367 };
368
369 if (prev.isType(Node.REL_ADDR) ||
370 prev.isType(Node.ABS_ADDR)) {
371 node.setNextRange(prev);
372 }
373 else
374 // invalid address format
375 throwError("#ADDR?");
376
377 }else if (c == '+' || c == '-' || c == '*' || c == '/' ||
378 c == '^' || c == '%') {
379 node.setType(Node.OPERATOR);
380 }else if (c == '$') {
381 // !!! Absolute Address starts with $
382 node.setType(Node.ABS_ADDR);
383 node.setData("");
384 // a letter must follow the $
385 if (! Character.isLetter(input.charAt(cur))) {
386 // invalid address format
387 throwError("#ADDR?");
388 }
389 // look for column
390 while (Character.isLetter(input.charAt(cur)))
391 node.appendData(input.charAt(cur++));
392
393 // absolute address has to be the form of
394 // ${letters}${numbers}
395 if (input.charAt(cur++) != '$' ||
396 ! Character.isDigit(input.charAt(cur))) {
397 // invalid address format
398 throwError("#ADDR?");
399 }
400 String scol=node.getData();//view's column in String. e.g.: "A"
401 int refcol=SSTable.translateVVColumn(scol);//view column
402 //node.setCol(refcol);
403 node.setData("");
404 while (cur < input.length() &&
405 Character.isDigit(input.charAt(cur)))
406 node.appendData(input.charAt(cur++));
407 String data=node.getData(); //Table's row index in String
408 int refrow=translateRow(data) ; //Table's row
409 //node.setRow(refrow);
410 node.setData(null);
411 node.setAddress(refrow,refcol);
412 } //absolute address
413 else if (c == ' ')
414 continue;
415 else
416 // invalid char
417 throwError("#NAME?");
418
419 //this.showTokens(tokens);
420
421 //2.
422 // after a ADDR or NUMBER token the following char
423 // should not be a letter or digit
424 if (cur < input.length() && (node.isType(Node.REL_ADDR) ||
425 node.isType(Node.ABS_ADDR) ||
426 node.isType(Node.NUMBER)) &&
427 Character.isLetterOrDigit(input.charAt(cur))) {
428 throwError
429 // invalid char
430 ("#NAME?");
431 }
432
433 // process the second address of a cell range
434 if (lastToken != null &&
435 lastToken.isType(Node.COLON) &&
436 lastToken.isPending()) { //Range
437 if (node.isType(Node.REL_ADDR) || node.isType(Node.ABS_ADDR)) {
438
439 Node range = (Node) tokens.removeLast();
440
441 try {
442 ((Node) range.getNextRange()).setNextRange(node);
443 range.setPending(false);
444 } catch (NullPointerException e) {
445 // invalid address format
446 throwError("#ADDR?");
447 }
448
449 node = range;
450 // util.Debug.println("edu.psu.geovista.app.spreadsheet.formula.Node: "+node);
451 } else
452 throwError("#ADDR?");
453 }
454
455 // edu.psu.geovista.app.spreadsheet.util.Debug.println("Add: "+node);
456
457 if (node.isType(Node.OPERATOR) &&
458 (node.getData().equals("+") ||
459 node.getData().equals("-")) &&
460 (lastToken == null || lastToken.isType(Node.LPAREN) ||
461 lastToken.isType(Node.COMMA))) {
462 tokens.add(zero);
463 }
464 System.out.println("node:"+node.getData());
465 tokens.add(node);
466 lastType = node.getType();
467 Debug.println("lastType:"+lastType);
468 lastToken = node;
469 //this.showTokens(tokens);
470
471 }catch (IndexOutOfBoundsException e) {
472 // error
473 throwError("#NAME?");
474 }catch (ParserException e) {
475 throwError(e);
476 }catch (Exception e) {
477 e.printStackTrace() ;
478 ///edu.psu.geovista.app.spreadsheet.util.Debug.println(e.toString());
479 } //try
480
481
482 }//while loop at beginning
483
484 if (nParen != 0) // imbalanced parenthesis
485 throwError("#PAREN?");
486
487 /*Debug.showLinkedList(tokens,"tokenize() show token");
488 Node node=(Node)tokens.get(2);
489 Debug.showNode(node,"node 2");
490 Node r1=node.getNextRange() ;
491 Debug.showNode(r1,"Range 1");
492 Debug.showNode(r1.getNextRange(),"Range2");
493 /*LinkedList param=node.getParams() ;
494 Iterator iter=param.iterator() ;
495 while(iter.hasNext() ){
496 System.out.println(" "+iter.next() );
497 } */
498 //this.showTokens(tokens); */
499 return tokens;
500 }
501
502 private void showTokens(LinkedList tokens){
503 System.out.println("showTokens ->");
504 Iterator it=tokens.iterator() ;
505 while(it.hasNext() ){
506 Node node=(Node)it.next() ;
507 if (node.isType(Node.REL_ADDR) ){
508 System.out.print(node.getReference().getValue()+" ");
509 }
510 else{
511 System.out.print(node.getData()+" ");
512 }
513 }
514 System.out.println("\n showTokens <-\n");
515 }
516
517 /***
518 * Convert function parameters. From a linear sequence of nodes,
519 * output a tree-like structure, with all the functions having a
520 * linked list of parameters, and each parameter having a linked
521 * list of nodes (that is, each parameter can be a edu.psu.geovista.app.spreadsheet.formula).
522 *
523 * The basic rules are:
524 * <ol>
525 * <li>Pass values to the output (a linked list used as a stack) except
526 * the following.</li>
527 * <li>If a function name is encountered, it's set to "pending" (meaning
528 * it's expecting an enclosing parenthesis) and passed to the output, and
529 * its following '(' is discarded.</li>
530 * <li>If a left parenthesis is encountered, it's set to "pending"
531 * and passed to the output.</li>
532 * <li>If a comma is encountered, pop up all the previous nodes to a list
533 * until an unpending function node is found. Then set the list having
534 * all the popped nodes as the function's last parameter. The function
535 * node is pushed back.</li>
536 * <li>For a ')', pop all the previous nodes to a list until an unpending
537 * left parenthesis or an unpending function is found. For the former,
538 * the left parenthesis is set to "unpending", and push back all the
539 * popped nodes (including the right parenthesis). For the latter,
540 * it's the same as the comma case, except that the function node is
541 * set to "unpending".</li>
542 * </ol>
543 *
544 */
545 private LinkedList convertParams(final LinkedList tokens)
546 throws ParserException {
547
548 if (tokens == null) {
549 throw error;
550 }
551
552 LinkedList stack = new LinkedList();
553
554 Iterator it = tokens.iterator();
555
556 try {
557 while (it.hasNext()) {
558 Node node = (Node)it.next();
559
560 if (node.isType(Node.FUNCTION)) {
561 node.setPending(true);
562 stack.add(node);
563 node = (Node)it.next();
564 // should be LParen
565 if (!node.isType(Node.LPAREN)) // ( expected
566 throwError("#NO(?");
567 }
568 else if (node.isType(Node.LPAREN)) {
569 node.setPending(true);
570 stack.add(node);
571 }
572 else if (node.isType(Node.COMMA)) {
573 Node exp = new Node();
574 LinkedList list = new LinkedList();
575 Node param = (Node)stack.removeLast();//pop();
576 // pop out until the unpending FUNCTION
577 while (!param.isType(Node.FUNCTION) ||
578 !param.isPending()) {
579 list.addFirst(param);
580 param = (Node)stack.removeLast();//pop();
581 }
582
583 exp.setType(Node.EXP);
584 exp.setExp(list);
585
586 param.addParam(exp);
587
588 // still pending
589 // stack.push(param);
590 stack.add(param);
591 }
592 else if (node.isType(Node.RPAREN)) {
593 // we don't know whether this is for a function.
594 Node exp = new Node();
595 LinkedList list = new LinkedList();
596 Node param = (Node)stack.removeLast(); //stack.pop();
597
598 // process the last parameter
599 while (!param.isPending() ||
600 !param.isType(Node.FUNCTION) &&
601 !param.isType(Node.LPAREN)) {
602 list.addFirst(param);
603 param = (Node)stack.removeLast();//pop();
604 }
605
606 // set to unpending
607 if (param.isType(Node.LPAREN)) {
608 // this is a normal left paren
609 param.setPending(false);
610 // push back
611 stack.add(param);
612 stack.addAll(list);
613 stack.add(node);
614 }
615 else {
616 // this is a function left paren
617 // edu.psu.geovista.app.spreadsheet.util.Debug.println("exp is "+list);
618 // set the expression of that parameter
619 exp.setType(Node.EXP);
620 exp.setExp(list);
621 // add a parameter for the function
622 param.addParam(exp);
623 param.setPending(false);
624 stack.add(param);
625 }
626 }
627 else
628 stack.add(node); //push(node);
629
630 }
631
632 }
633 catch (ParserException e) {
634 throw e;
635 }
636 catch (Exception e) {
637 Debug.println(e);
638 // general param error
639 throwError("#PARAM?");
640 }
641
642 return stack;
643 }
644
645 /***
646 * This converts tokens to postfix format using stack.
647 * <p>
648 * The basic rules are:
649 * <ol>
650 * <li>Pass values to the output (a linked list)</li>
651 * <li>Push '(' to the stack</li>
652 * <li>For an operator, pop all the previous operators that have a lower
653 * priority to the output and push this one to the stack</li>
654 * <li>For ')', pop all the previous operators until a (</li>
655 * <li>If we reach the end, pop up everything</li>
656 * </ol>
657 *
658 * @param tokens a linked list to convert
659 * @edu.psu.geovista.app.spreadsheet.exception edu.psu.geovista.app.spreadsheet.exception.ParserException
660 *
661 * @see edu.psu.geovista.app.spreadsheet.formula.Node
662 * @see #tokenize
663 * @see #convertParam
664 */
665 private LinkedList toPostfix(LinkedList tokens) throws ParserException {
666 if (tokens == null) {
667 throw error;
668 }
669
670 // stack is used for the conversion
671 Stack stack = new Stack();
672 LinkedList postfix = new LinkedList();
673 Iterator it = tokens.iterator();
674 while (it.hasNext()) {
675 Node node = (Node)it.next();
676 switch (node.getType()) {
677
678 case Node.NUMBER:
679 case Node.REL_ADDR:
680 case Node.ABS_ADDR:
681 case Node.COLON:
682 // just add normal values to the list
683 postfix.add(node);
684 break;
685
686 case Node.LPAREN:
687 // push to stack; pop out when a RPAREN is encountered
688 stack.push(node);
689 break;
690 case Node.OPERATOR:
691 // get the precedence priority of the operator
692 int priority = getPriority(node);
693
694 // pop up operators with the same or higher priority from
695 // the stack
696 while (! stack.empty() &&
697 ! ((Node)stack.peek()).isType(Node.LPAREN) &&
698 getPriority((Node)stack.peek()) >= priority) {
699 postfix.add((Node)stack.pop());
700 }
701 stack.push(node);
702 break;
703 case Node.RPAREN:
704 try {
705 Node op = (Node)stack.pop();
706 // pop out until the last LPAREN
707 while (! op.isType(Node.LPAREN)) {
708 postfix.add(op);
709 op = (Node)stack.pop();
710 }
711 }
712 catch (EmptyStackException e) {
713 // should not happen - imbalance in parenthesis
714 throwError("#PAREN?");
715 }
716 break;
717 case Node.FUNCTION:
718
719 // get the param list
720 LinkedList params = node.getParams();
721
722 Iterator paramIter = params.iterator();
723
724 while (paramIter.hasNext()) {
725 Node exp = (Node)paramIter.next();
726 exp.setExp(toPostfix(exp.getExp()));
727 }
728
729 postfix.add(node);
730
731 break;
732
733 default:
734 // unknown error - should not happen
735 throwError("#ERROR?");
736 }
737 }
738
739 // pop up the rest nodes
740 while (!stack.empty())
741 postfix.add((Node)stack.pop());
742
743 return postfix;
744 }
745
746
747 /***
748 * get cell from TableModel, if null, create a new cell
749 * and store it in the TableModel
750 */
751 private Cell getCell(int row, int col){
752 //SSTableModel tbm=SSTableModel.getInstance();
753 SSTableModel tbm=this.getOwner().getDataModel() ;
754 return(Cell) tbm.getValueAt(row, col) ;
755 }
756
757 /***
758 * From the edu.psu.geovista.app.spreadsheet.formula.Node list; Creates the dependency set.
759 *
760 * @return a HashSet of edu.psu.geovista.app.spreadsheet.formula.CellPoint that the current cell references
761 */
762
763 public HashSet getOwners() {
764 if (this.owners ==null){
765 this.owners =new HashSet();
766 }
767 return owners;
768 }
769
770 public Cell getOwner() {
771 return owner;
772 }
773
774 public boolean addOwner(Cell cell) {
775 if (this.owners ==null){
776 this.owners =new HashSet();
777 }
778 return this.owners.add(cell);
779 }
780
781
782
783
784 /***
785 * This gets the priority of an operator.
786 *
787 * @param op the operator character
788 * @return 1='+' '-', 2='*' '/', 3='^'
789 */
790 private static int getPriority(char op) {
791 switch (op) {
792
793 case '+':
794 case '-':
795 return 1;
796 case '*':
797 case '/':
798 case '%':
799 return 2;
800 case '^':
801 return 3;
802 default:
803 return 0;
804 }
805 }
806
807 /***
808 * This returns the highest-priority node.
809 */
810 private static int getPriority(Node node) {
811 return getPriority(node.getData().charAt(0));
812 }
813
814 /***
815 * This returns the string value of the edu.psu.geovista.app.spreadsheet.formula.
816 *
817 * @return the string value
818 */
819 public String toString() {
820 this.evalueExpression(this.nodes );
821 return formulaString;
822 }
823
824 /***
825 * This takes an operator and two operands and returns the result.
826 *
827 * @param op the operator
828 * @param op1 operand 1
829 * @param op2 operand 2
830 * @return the float value of operand 1 operator operand 2
831 */
832 private static Number calc(char op, Number op1, Number op2) {
833 float n1 = op1.floatValue();
834 float n2 = op2.floatValue();
835 float result;
836 switch (op) {
837 case '+': result = n1+n2; break;
838 case '-': result = n1-n2; break;
839 case '*': result = n1*n2; break;
840 case '/': result = n1/n2; break;
841 case '^': result = (float)Math.pow(n1, n2); break;
842 case '%': result = (float)((int)n1%(int)n2); break;
843 default: result = 0; break;
844 }
845
846 return new Float(result);
847 }
848
849 /***
850 * This evaluates the function.
851 *
852 * @param table the TableModel object
853 * @param node the head node of the function
854 * @return the value as a Float object
855 * @edu.psu.geovista.app.spreadsheet.exception edu.psu.geovista.app.spreadsheet.exception.ParserException
856 */
857 private Number evalFunction(Node node)
858 throws ParserException,NoReferenceException {
859
860 String funcName = node.getData();
861
862 // get function handler from the funcTable
863
864 //Function func = getFuncHandler(funcName);
865 Function func = this.getFunctionManager().getFuncHandler(funcName);
866
867 //func.setOwners(this.getOwners() );
868
869 if (func == null) {
870 // not registered function
871 throw new ParserException("#FUNC?");
872
873 }else{
874 func.setOwner(this);
875 return func.evaluate(node);
876 }
877
878 }
879 public FunctionManager getFunctionManager() {
880 SSTable table=owner.getDataModel().getTable() ;
881 return table.getFunManager();
882 }
883
884 public Number evaluate() throws ParserException,NoReferenceException {
885 return evaluate(this.postfix );
886 }
887
888 /***
889 * It evaluates the postfix expression by a stack.
890 *
891 * @param table the TableModel object
892 * @param postfix the edu.psu.geovista.app.spreadsheet.formula in postfix form
893 * @param row the row of the cell to be evaluated
894 * @param col the column of the cell to be evaluated
895 * @return the result as a Float object
896 * @edu.psu.geovista.app.spreadsheet.exception edu.psu.geovista.app.spreadsheet.exception.ParserException
897 */
898 public Number evaluate(LinkedList postfix) throws ParserException,NoReferenceException {
899 if (this.isBad() ){
900 throw new ParserException("#LOOP?");
901 }
902
903 try {
904 Stack stack = new Stack();
905
906 Iterator it = postfix.iterator();
907 int numOfNode=0;
908 Object o;
909 while (it.hasNext()) {
910 numOfNode++;
911 Node node = (Node)it.next();
912 //Number result;
913 Number result;
914 Cell cell=null;
915 switch (node.getType()) {
916 case Node.OPERATOR:
917 // pop the 2 operands from stack top and save the result
918 // back to stack
919 Number n2 = (Number)stack.pop();
920 Number n1 = (Number)stack.pop();
921 result = calc(node.getData().charAt(0), n1, n2);
922 break;
923 case Node.FUNCTION:
924 // evaluate the function
925 result = evalFunction(node);
926 Debug.println("result :"+result);
927 break;
928 case Node.NUMBER:
929 // directly return the number
930 result = new Float(node.getNumber());
931 break;
932 case Node.ABS_ADDR:
933 // get the numeric value of that cell
934 //result = //getNumericValueAt(table, node.getRow(),
935 // node.getCol());
936 //table.getNumericValueAt(node.getRow(), node.getCol());
937 Point address=node.getAddress() ;
938 //JTable tb=SpreadSheetBean.getTableInstance() ;
939 JTable tb=owner.getDataModel().getTable() ;
940 int x=(int)address.getX() ;
941 int y=(int)address.getY() ;
942 cell=(Cell)tb.getValueAt(x,y);
943 this.checkReference(cell);
944
945 cell.evaluate();
946
947 o=cell.getValue() ;
948 if (o==null){ //If refrenced cell is null, show 0
949 result=new Float(0.0f);
950 }
951 else if (o instanceof String){
952 //reference is marked as deleted
953 if( o.equals("#REF!")){
954 throw new NoReferenceException("#REF!");
955 }
956 else{//the refrenced cell is a String
957 if (numOfNode==1&&!it.hasNext() ){
958
959 //If the edu.psu.geovista.app.spreadsheet.formula contain ONLY the cell, just throw the String to the referencing cell
960 // which is setValue() as the string
961 throw new ParserException((String)o);
962 }
963 else{
964 ////If the edu.psu.geovista.app.spreadsheet.formula contain more than the cell, throw "#VALUE!"
965 throw new ParserException("#VALUE!");
966 }
967 }
968 }
969 else{
970 result= (Number)o;
971 }
972 /*
973 o=cell.getValue() ;
974 if (o==null){//If refrenced cell is null, show 0
975 result=new Float(0.0f);
976 }
977 result=(Number)cell.getValue() ;
978
979 //System.out.println(" Not implemented yet!"); */
980
981 break;
982 case Node.REL_ADDR:
983 // get the numeric value of that cell
984 /*result = //getNumericValueAt(table, node.getRow()+row,
985 // node.getCol()+col);
986 table.getNumericValueAt(node.getRow()+row,
987 node.getCol()+col); */
988 cell=node.getReference() ;
989 this.checkReference(cell);
990 cell.evaluate();
991
992 o=cell.getValue() ;
993 if (o==null){ //If refrenced cell is null, show 0
994 result=new Float(0.0f);
995 }
996 else if (o instanceof String){
997 //reference is marked as deleted
998 if( o.equals("#REF!")){
999 throw new NoReferenceException("#REF!");
1000 }
1001 else{//the refrenced cell is a String
1002 if (numOfNode==1&&!it.hasNext() ){
1003
1004 //If the edu.psu.geovista.app.spreadsheet.formula contain ONLY the cell, just throw the String to the referencing cell
1005 // which is setValue() as the string
1006 throw new ParserException((String)o);
1007 }
1008 else{
1009 ////If the edu.psu.geovista.app.spreadsheet.formula contain more than the cell, throw "#VALUE!"
1010 throw new ParserException("#VALUE!");
1011 }
1012 }
1013 }
1014 else{
1015 result= (Number)o;
1016 }
1017 //result=null;
1018 //System.out.println(" Not implemented yet!");
1019 break;
1020 default:
1021 // evaluation error
1022 throw new ParserException("#EVAL?");
1023 }
1024
1025 // push to the stack
1026 stack.push(result);
1027 }
1028
1029 Number result = (Number)stack.pop();
1030
1031 return result;
1032 }catch (EmptyStackException e) {
1033 // imbalance between operands and operators
1034 throw new ParserException("#OP?");
1035 // ("Wrong format of edu.psu.geovista.app.spreadsheet.formula: too many operators");
1036 }catch (ParserException e) {
1037 throw e;
1038 } catch (NoReferenceException e) {
1039 throw e;
1040
1041 }catch (Exception e) {
1042 e.printStackTrace() ;
1043 //edu.psu.geovista.app.spreadsheet.util.Debug.println(e);
1044 }
1045
1046 return new Integer(0);
1047 }
1048 /***
1049 *
1050 */
1051 private void checkReference(Cell cell) throws ParserException{
1052 if(this.owners.contains(cell)){
1053 this.setBad() ;//stop any further evaluation on the edu.psu.geovista.app.spreadsheet.formula
1054 //current cell reference to one of his owner
1055 //JFrame mf=(JFrame)SwingUtilities.getAncestorOfClass(JFrame.class,SpreadSheetBean.getTableInstance() );
1056 JFrame mf=(JFrame)SwingUtilities.getAncestorOfClass(JFrame.class,owner.getDataModel().getTable() );
1057
1058 JOptionPane.showConfirmDialog(mf,
1059 "Loop reference error","Error",JOptionPane.OK_OPTION);
1060 throw new ParserException("#LOOP?");
1061 }
1062 else{
1063 HashSet owners=this.getOwners() ;
1064 Iterator iter = owners.iterator();
1065 while(iter.hasNext()){
1066 Cell c = (Cell)iter.next();
1067 if (cell.isFormula() ){
1068 Debug.println("cell:"+cell.getViewAddress() );
1069 cell.addOwner(c);
1070 }
1071 //false NOT mean loop: e.g.: A reference to B,C and B,C feference to D
1072 // Thus A is owner of D for twice
1073 }
1074
1075 }
1076 }
1077
1078 public static Number processCellValue(Object o)
1079 throws ParserException,NoReferenceException{
1080 Number result=null;
1081 if (o==null){ //If refrenced cell is null, show 0
1082 result=new Float(0.0f);
1083 }
1084 else if (o instanceof String){
1085 //reference is marked as deleted
1086 if( o.equals("#REF!")){
1087 throw new NoReferenceException("#REF!");
1088 }
1089 else{//the refrenced cell is a String
1090 throw new ParserException("#VALUE!");
1091 }
1092 }
1093 else{
1094 result= (Number)o;
1095 }
1096 return result;
1097 }
1098
1099 private Number evaluateCell(Cell cell) throws ParserException {
1100
1101 if (cell != null) {
1102 int type = cell.getValueType();
1103 if (cell.isFormula() ) {
1104 Object value = cell.getValue();
1105 Formula form = cell.getFormula();
1106 // if need recalc
1107 if (form.needsRecalc()) {
1108 try {
1109 value = form.evaluate();
1110 cell.setValue(value);
1111 }
1112 catch (ParserException e) {
1113 cell.setValue(e);
1114 value = e;
1115 }
1116 catch(NoReferenceException e){
1117 e.printStackTrace() ;//only for debug
1118 cell.setValue("#REF");
1119 }
1120 }
1121
1122 if (value instanceof ParserException)
1123 throw (ParserException)value;
1124 else
1125 return (Number)cell.getValue();
1126 }
1127 else if (type == Cell.NUMBER)
1128 return (Number)cell.getValue();
1129 else
1130 return new Float(0);
1131 }
1132 else
1133 // a string or null
1134 // return new Float(0);
1135 throw new ParserException("#REFS?");
1136
1137 }
1138
1139
1140 // The following are just simple edu.psu.geovista.app.spreadsheet.functions
1141
1142 /***
1143 * This translates the string form of row into row number ('12' -> 12),
1144 * Translate View's row to Model's row.
1145 *
1146 * @param row the string representation of the row
1147 * @return the int representation of the row
1148 */
1149 final private static int translateRow(String row) {
1150 int x=Integer.parseInt(row);
1151 return SSTable.transRowViewToTable(x);
1152 }
1153
1154 /***
1155 * This translates the int form of row into row string (12 -> '12').
1156 * Translate Table's row to View's row.
1157 * @param row the int representation of the row
1158 * @return the string representation of the row
1159 */
1160 final private static String translateRow(int row) {
1161 int x= SSTable.transRowTableToView(row);
1162 //int x= SSTable.transRowTableToView(row);
1163 return Integer.toString(x);
1164 }
1165
1166
1167
1168 /***
1169 * Label the bad cells and throw edu.psu.geovista.app.spreadsheet.exception.ParserException.
1170 * error is saved so next time it won't re-evaluate again:
1171 * it directly throws the same edu.psu.geovista.app.spreadsheet.exception.
1172 *
1173 * @param s the thing that's bad
1174 * @edu.psu.geovista.app.spreadsheet.exception edu.psu.geovista.app.spreadsheet.exception.ParserException
1175 */
1176 private void throwError(Object s) throws ParserException {
1177 // test code
1178 // System.err.println("Marking edu.psu.geovista.app.spreadsheet.formula "+formulaString+" as bad");
1179 postfix = null;
1180 if (error instanceof ParserException)
1181 throw (ParserException) s;
1182 else {
1183 error = new ParserException(s);
1184 throw error;
1185 }
1186 }
1187
1188
1189
1190
1191 }
This page was automatically generated by Maven