1    
2    /* ====================================================================
3     * The Apache Software License, Version 1.1
4     *
5     * Copyright (c) 2002 The Apache Software Foundation.  All rights
6     * reserved.
7     *
8     * Redistribution and use in source and binary forms, with or without
9     * modification, are permitted provided that the following conditions
10    * are met:
11    *
12    * 1. Redistributions of source code must retain the above copyright
13    *    notice, this list of conditions and the following disclaimer.
14    *
15    * 2. Redistributions in binary form must reproduce the above copyright
16    *    notice, this list of conditions and the following disclaimer in
17    *    the documentation and/or other materials provided with the
18    *    distribution.
19    *
20    * 3. The end-user documentation included with the redistribution,
21    *    if any, must include the following acknowledgment:
22    *       "This product includes software developed by the
23    *        Apache Software Foundation (http://www.apache.org/)."
24    *    Alternately, this acknowledgment may appear in the software itself,
25    *    if and wherever such third-party acknowledgments normally appear.
26    *
27    * 4. The names "Apache" and "Apache Software Foundation" and
28    *    "Apache POI" must not be used to endorse or promote products
29    *    derived from this software without prior written permission. For
30    *    written permission, please contact apache@apache.org.
31    *
32    * 5. Products derived from this software may not be called "Apache",
33    *    "Apache POI", nor may "Apache" appear in their name, without
34    *    prior written permission of the Apache Software Foundation.
35    *
36    * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
37    * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
38    * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
39    * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
40    * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
41    * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
42    * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
43    * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
44    * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
45    * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
46    * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
47    * SUCH DAMAGE.
48    * ====================================================================
49    *
50    * This software consists of voluntary contributions made by many
51    * individuals on behalf of the Apache Software Foundation.  For more
52    * information on the Apache Software Foundation, please see
53    * <http://www.apache.org/>.
54    */
55   
56   /*
57    * FormulaRecord.java
58    *
59    * Created on October 28, 2001, 5:44 PM
60    */
61   package org.apache.poi.hssf.record;
62   
63   import java.util.Stack;
64   import java.util.List;
65   
66   import org.apache.poi.util.LittleEndian;
67   import org.apache.poi.hssf.record.formula.*;
68   
69   /**
70    * Formula Record.
71    * REFERENCE:  PG 317/444 Microsoft Excel 97 Developer's Kit (ISBN: 1-57231-498-2)<P>
72    * @author Andrew C. Oliver (acoliver at apache dot org)
73    * @author Jason Height (jheight at chariot dot net dot au)
74    * @version 2.0-pre
75    */
76   
77   public class FormulaRecord
78       extends Record
79       implements CellValueRecordInterface, Comparable
80   {
81       
82       public static final boolean EXPERIMENTAL_FORMULA_SUPPORT_ENABLED=true;
83       
84       public static final short sid =
85           0x06;   // docs say 406...because of a bug Microsoft support site article #Q184647)
86       
87       //private short             field_1_row;
88       private int             field_1_row;
89       private short             field_2_column;
90       private short             field_3_xf;
91       private double            field_4_value;
92       private short             field_5_options;
93       private int               field_6_zero;
94       private short             field_7_expression_len;
95       private Stack             field_8_parsed_expr;
96       
97       private byte[]            all_data; //if formula support is not enabled then
98                                           //we'll just store/reserialize
99   
100      /** Creates new FormulaRecord */
101  
102      public FormulaRecord()
103      {
104          field_8_parsed_expr = new Stack();
105      }
106  
107      /**
108       * Constructs a Formula record and sets its fields appropriately.
109       *
110       * @param id     id must be 0x06 (NOT 0x406 see MSKB #Q184647 for an "explanation of
111       * this bug in the documentation) or an exception will be throw upon validation
112       * @param size  the size of the data area of the record
113       * @param data  data of the record (should not contain sid/len)
114       */
115  
116      public FormulaRecord(short id, short size, byte [] data)
117      {
118          super(id, size, data);
119      }
120  
121      /**
122       * Constructs a Formula record and sets its fields appropriately.
123       *
124       * @param id     id must be 0x06 (NOT 0x406 see MSKB #Q184647 for an "explanation of
125       * this bug in the documentation) or an exception will be throw upon validation
126       * @param size  the size of the data area of the record
127       * @param data  data of the record (should not contain sid/len)
128       * @param offset of the record's data
129       */
130  
131      public FormulaRecord(short id, short size, byte [] data, int offset)
132      {
133          super(id, size, data, offset);
134      }
135  
136      protected void fillFields(byte [] data, short size, int offset)
137      {
138          try {
139          //field_1_row            = LittleEndian.getShort(data, 0 + offset);
140          field_1_row            = LittleEndian.getUShort(data, 0 + offset);
141          field_2_column         = LittleEndian.getShort(data, 2 + offset);
142          field_3_xf             = LittleEndian.getShort(data, 4 + offset);
143          field_4_value          = LittleEndian.getDouble(data, 6 + offset);
144          field_5_options        = LittleEndian.getShort(data, 14 + offset);
145          field_6_zero           = LittleEndian.getInt(data, 16 + offset);
146          field_7_expression_len = LittleEndian.getShort(data, 20 + offset);
147          field_8_parsed_expr    = getParsedExpressionTokens(data, size,
148                                   22 + offset);
149          
150          } catch (java.lang.UnsupportedOperationException uoe)  {
151              field_8_parsed_expr = null;
152              all_data = new byte[size+4];
153              LittleEndian.putShort(all_data,0,sid);
154              LittleEndian.putShort(all_data,2,size);
155              System.arraycopy(data,offset,all_data,4,size);
156              System.err.println("[WARNING] Unknown Ptg " 
157                      + uoe.getMessage() 
158                      + " at cell ("+field_1_row+","+field_2_column+")");
159          }
160  
161      }
162  
163      private Stack getParsedExpressionTokens(byte [] data, short size,
164                                              int offset)
165      {
166          Stack stack = new Stack();
167          int   pos   = offset;
168  
169          while (pos < size)
170          {
171              Ptg ptg = Ptg.createPtg(data, pos);
172              pos += ptg.getSize();
173              stack.push(ptg);
174          }
175          return stack;
176      }
177  
178      //public void setRow(short row)
179      public void setRow(int row)
180      {
181          field_1_row = row;
182      }
183  
184      public void setColumn(short column)
185      {
186          field_2_column = column;
187      }
188  
189      public void setXFIndex(short xf)
190      {
191          field_3_xf = xf;
192      }
193  
194      /**
195       * set the calculated value of the formula
196       *
197       * @param value  calculated value
198       */
199  
200      public void setValue(double value)
201      {
202          field_4_value = value;
203      }
204  
205      /**
206       * set the option flags
207       *
208       * @param options  bitmask
209       */
210  
211      public void setOptions(short options)
212      {
213          field_5_options = options;
214      }
215  
216      /**
217       * set the length (in number of tokens) of the expression
218       * @param len  length
219       */
220  
221      public void setExpressionLength(short len)
222      {
223          field_7_expression_len = len;
224      }
225  
226      //public short getRow()
227      public int getRow()
228      {
229          return field_1_row;
230      }
231  
232      public short getColumn()
233      {
234          return field_2_column;
235      }
236  
237      public short getXFIndex()
238      {
239          return field_3_xf;
240      }
241  
242      /**
243       * get the calculated value of the formula
244       *
245       * @return calculated value
246       */
247  
248      public double getValue()
249      {
250          return field_4_value;
251      }
252  
253      /**
254       * get the option flags
255       *
256       * @return bitmask
257       */
258  
259      public short getOptions()
260      {
261          return field_5_options;
262      }
263  
264      /**
265       * get the length (in number of tokens) of the expression
266       * @return  expression length
267       */
268  
269      public short getExpressionLength()
270      {
271          return field_7_expression_len;
272      }
273  
274      /**
275       * push a token onto the stack
276       *
277       * @param ptg  the token
278       */
279  
280      public void pushExpressionToken(Ptg ptg)
281      {
282          field_8_parsed_expr.push(ptg);
283      }
284  
285      /**
286       * pop a token off of the stack
287       *
288       * @return Ptg - the token
289       */
290  
291      public Ptg popExpressionToken()
292      {
293          return ( Ptg ) field_8_parsed_expr.pop();
294      }
295  
296      /**
297       * peek at the token on the top of stack
298       *
299       * @return Ptg - the token
300       */
301  
302      public Ptg peekExpressionToken()
303      {
304          return ( Ptg ) field_8_parsed_expr.peek();
305      }
306  
307      /**
308       * get the size of the stack
309       * @return size of the stack
310       */
311  
312      public int getNumberOfExpressionTokens()
313      {
314          if (this.field_8_parsed_expr == null) {
315              return 0;
316          } else {
317              return field_8_parsed_expr.size();
318          }
319      }
320  
321      /**
322       * get the stack as a list
323       *
324       * @return list of tokens (casts stack to a list and returns it!)
325       * this method can return null is we are unable to create Ptgs from 
326       *     existing excel file
327       * callers should check for null!
328       */
329  
330      public List getParsedExpression()
331      {
332          return field_8_parsed_expr;
333      }
334  
335      /**
336       * called by constructor, should throw runtime exception in the event of a
337       * record passed with a differing ID.
338       *
339       * @param id alleged id for this record
340       */
341  
342      protected void validateSid(short id)
343      {
344          if (id != sid)
345          {
346              throw new RecordFormatException("NOT A FORMULA RECORD");
347          }
348      }
349  
350      public short getSid()
351      {
352          return sid;
353      }
354  
355      /**
356       * called by the class that is responsible for writing this sucker.
357       * Subclasses should implement this so that their data is passed back in a
358       * byte array.
359       *
360       * @return byte array containing instance data
361       */
362  
363      public int serialize(int offset, byte [] data)
364      {
365          if (this.field_8_parsed_expr != null) {
366          int ptgSize = getTotalPtgSize();
367  
368          LittleEndian.putShort(data, 0 + offset, sid);
369          LittleEndian.putShort(data, 2 + offset, ( short ) (22 + ptgSize));
370          //LittleEndian.putShort(data, 4 + offset, getRow());
371          LittleEndian.putShort(data, 4 + offset, ( short ) getRow());
372          LittleEndian.putShort(data, 6 + offset, getColumn());
373          LittleEndian.putShort(data, 8 + offset, getXFIndex());
374          LittleEndian.putDouble(data, 10 + offset, field_4_value);
375          LittleEndian.putShort(data, 18 + offset, getOptions());
376          LittleEndian.putInt(data, 20 + offset, field_6_zero);
377          LittleEndian.putShort(data, 24 + offset, getExpressionLength());
378          serializePtgs(data, 26+offset);
379          } else {
380              System.arraycopy(all_data,0,data,offset,all_data.length);
381          }
382          return getRecordSize();
383      }
384      
385      
386      
387  
388      public int getRecordSize()
389      {
390          int retval =0;
391          
392          if (this.field_8_parsed_expr != null) {
393              retval = getTotalPtgSize() + 26;
394          } else {
395              retval =all_data.length;
396          }
397          return retval;
398  
399          // return getTotalPtgSize() + 28;
400      }
401  
402      private int getTotalPtgSize()
403      {
404          List list   = getParsedExpression();
405          int  retval = 0;
406  
407          for (int k = 0; k < list.size(); k++)
408          {
409              Ptg ptg = ( Ptg ) list.get(k);
410  
411              retval += ptg.getSize();
412          }
413          return retval;
414      }
415  
416      private void serializePtgs(byte [] data, int offset)
417      {
418          int pos = offset;
419  
420          for (int k = 0; k < field_8_parsed_expr.size(); k++)
421          {
422              Ptg ptg = ( Ptg ) field_8_parsed_expr.get(k);
423  
424              ptg.writeBytes(data, pos);
425              pos += ptg.getSize();
426          }
427      }
428  
429      public boolean isBefore(CellValueRecordInterface i)
430      {
431          if (this.getRow() > i.getRow())
432          {
433              return false;
434          }
435          if ((this.getRow() == i.getRow())
436                  && (this.getColumn() > i.getColumn()))
437          {
438              return false;
439          }
440          if ((this.getRow() == i.getRow())
441                  && (this.getColumn() == i.getColumn()))
442          {
443              return false;
444          }
445          return true;
446      }
447  
448      public boolean isAfter(CellValueRecordInterface i)
449      {
450          if (this.getRow() < i.getRow())
451          {
452              return false;
453          }
454          if ((this.getRow() == i.getRow())
455                  && (this.getColumn() < i.getColumn()))
456          {
457              return false;
458          }
459          if ((this.getRow() == i.getRow())
460                  && (this.getColumn() == i.getColumn()))
461          {
462              return false;
463          }
464          return true;
465      }
466  
467      public boolean isEqual(CellValueRecordInterface i)
468      {
469          return ((this.getRow() == i.getRow())
470                  && (this.getColumn() == i.getColumn()));
471      }
472  
473      public boolean isInValueSection()
474      {
475          return true;
476      }
477  
478      public boolean isValue()
479      {
480          return true;
481      }
482  
483      public int compareTo(Object obj)
484      {
485          CellValueRecordInterface loc = ( CellValueRecordInterface ) obj;
486  
487          if ((this.getRow() == loc.getRow())
488                  && (this.getColumn() == loc.getColumn()))
489          {
490              return 0;
491          }
492          if (this.getRow() < loc.getRow())
493          {
494              return -1;
495          }
496          if (this.getRow() > loc.getRow())
497          {
498              return 1;
499          }
500          if (this.getColumn() < loc.getColumn())
501          {
502              return -1;
503          }
504          if (this.getColumn() > loc.getColumn())
505          {
506              return 1;
507          }
508          return -1;
509      }
510  
511      public boolean equals(Object obj)
512      {
513          if (!(obj instanceof CellValueRecordInterface))
514          {
515              return false;
516          }
517          CellValueRecordInterface loc = ( CellValueRecordInterface ) obj;
518  
519          if ((this.getRow() == loc.getRow())
520                  && (this.getColumn() == loc.getColumn()))
521          {
522              return true;
523          }
524          return false;
525      }
526      
527      
528      public String toString()
529      {
530          StringBuffer buffer = new StringBuffer();
531          if (EXPERIMENTAL_FORMULA_SUPPORT_ENABLED) {
532              buffer.append("[FORMULA]\n");
533              buffer.append("    .row       = ")
534                  .append(Integer.toHexString(getRow())).append("\n");
535              buffer.append("    .column    = ")
536                  .append(Integer.toHexString(getColumn()))
537                  .append("\n");
538              buffer.append("    .xf              = ")
539                  .append(Integer.toHexString(getXFIndex())).append("\n");
540              buffer.append("    .value           = ").append(getValue())
541                  .append("\n");
542              buffer.append("    .options         = ").append(getOptions())
543                  .append("\n");
544              buffer.append("    .zero            = ").append(field_6_zero)
545                  .append("\n");
546              buffer.append("    .expressionlength= ").append(getExpressionLength())
547                  .append("\n");
548  
549              if (field_8_parsed_expr != null) {
550                  buffer.append("    .numptgsinarray  = ").append(field_8_parsed_expr.size())
551                      .append("\n");
552              
553              
554                  for (int k = 0; k < field_8_parsed_expr.size(); k++ ) {
555  /*                buffer.append("formula ").append(k).append(" ")
556                    .append(((Ptg)field_8_parsed_expr.get(k)).toFormulaString());*/
557                      buffer.append("Formula ")
558                          .append(k)
559                          .append("=")
560                          .append(field_8_parsed_expr.get(k).toString())
561                          .append("\n")
562                          .append(((Ptg)field_8_parsed_expr.get(k)).toDebugString())
563                          .append("\n");
564                  }
565              }
566              
567              
568              buffer.append("[/FORMULA]\n");
569          } else {
570              buffer.append(super.toString());
571          }
572          return buffer.toString();
573      }
574      
575      public Object clone() {
576        FormulaRecord rec = new FormulaRecord();
577        rec.field_1_row = field_1_row;
578        rec.field_2_column = field_2_column;
579        rec.field_3_xf = field_3_xf;
580        rec.field_4_value = field_4_value;
581        rec.field_5_options = field_5_options;
582        rec.field_6_zero = field_6_zero;
583        rec.field_7_expression_len = field_7_expression_len;
584        rec.field_8_parsed_expr = new Stack();
585        int size = 0;
586        if (field_8_parsed_expr != null)
587          size = field_8_parsed_expr.size();
588        for (int i=0; i< size; i++) {
589          Ptg ptg = (Ptg)((Ptg)field_8_parsed_expr.get(i)).clone();
590          rec.field_8_parsed_expr.set(i, ptg);
591        }
592        rec.all_data = all_data;
593        return rec;
594      }
595  
596  }
597