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    * @version 2.0-pre
74    */
75   
76   public class FormulaRecord
77       extends Record
78       implements CellValueRecordInterface, Comparable
79   {
80       
81       public static final boolean EXPERIMENTAL_FORMULA_SUPPORT_ENABLED=true;
82       
83       public static final short sid =
84           0x06;   // docs say 406...because of a bug Microsoft support site article #Q184647)
85       
86       //private short             field_1_row;
87       private int             field_1_row;
88       private short             field_2_column;
89       private short             field_3_xf;
90       private double            field_4_value;
91       private short             field_5_options;
92       private int               field_6_zero;
93       private short             field_7_expression_len;
94       private Stack             field_8_parsed_expr;
95       
96       private byte[]            all_data; //if formula support is not enabled then
97                                           //we'll just store/reserialize
98   
99       /** Creates new FormulaRecord */
100  
101      public FormulaRecord()
102      {
103          field_8_parsed_expr = new Stack();
104      }
105  
106      /**
107       * Constructs a Formula record and sets its fields appropriately.
108       *
109       * @param id     id must be 0x06 (NOT 0x406 see MSKB #Q184647 for an "explanation of
110       * this bug in the documentation) or an exception will be throw upon validation
111       * @param size  the size of the data area of the record
112       * @param data  data of the record (should not contain sid/len)
113       */
114  
115      public FormulaRecord(short id, short size, byte [] data)
116      {
117          super(id, size, data);
118      }
119  
120      /**
121       * Constructs a Formula record and sets its fields appropriately.
122       *
123       * @param id     id must be 0x06 (NOT 0x406 see MSKB #Q184647 for an "explanation of
124       * this bug in the documentation) or an exception will be throw upon validation
125       * @param size  the size of the data area of the record
126       * @param data  data of the record (should not contain sid/len)
127       * @param offset of the record's data
128       */
129  
130      public FormulaRecord(short id, short size, byte [] data, int offset)
131      {
132          super(id, size, data, offset);
133      }
134  
135      protected void fillFields(byte [] data, short size, int offset)
136      {
137          try {
138          //field_1_row            = LittleEndian.getShort(data, 0 + offset);
139          field_1_row            = LittleEndian.getUShort(data, 0 + offset);
140          field_2_column         = LittleEndian.getShort(data, 2 + offset);
141          field_3_xf             = LittleEndian.getShort(data, 4 + offset);
142          field_4_value          = LittleEndian.getDouble(data, 6 + offset);
143          field_5_options        = LittleEndian.getShort(data, 14 + offset);
144          field_6_zero           = LittleEndian.getInt(data, 16 + offset);
145          field_7_expression_len = LittleEndian.getShort(data, 20 + offset);
146          field_8_parsed_expr    = getParsedExpressionTokens(data, size,
147                                   offset);
148          
149          } catch (java.lang.UnsupportedOperationException uoe)  {
150              field_8_parsed_expr = null;
151              all_data = new byte[size+4];
152              LittleEndian.putShort(all_data,0,sid);
153              LittleEndian.putShort(all_data,2,size);
154              System.arraycopy(data,offset,all_data,4,size);
155              System.err.println("[WARNING] Unknown Ptg " 
156                      + uoe.getMessage() 
157                      + " at cell ("+field_1_row+","+field_2_column+")");
158          }
159  
160      }
161  
162      private Stack getParsedExpressionTokens(byte [] data, short size,
163                                              int offset)
164      {
165          Stack stack = new Stack();
166          int   pos   = 22 + offset;
167  
168          while (pos < size)
169          {
170              Ptg ptg = Ptg.createPtg(data, pos);
171              pos += ptg.getSize();
172              stack.push(ptg);
173          }
174          return stack;
175      }
176  
177      //public void setRow(short row)
178      public void setRow(int row)
179      {
180          field_1_row = row;
181      }
182  
183      public void setColumn(short column)
184      {
185          field_2_column = column;
186      }
187  
188      public void setXFIndex(short xf)
189      {
190          field_3_xf = xf;
191      }
192  
193      /**
194       * set the calculated value of the formula
195       *
196       * @param value  calculated value
197       */
198  
199      public void setValue(double value)
200      {
201          field_4_value = value;
202      }
203  
204      /**
205       * set the option flags
206       *
207       * @param options  bitmask
208       */
209  
210      public void setOptions(short options)
211      {
212          field_5_options = options;
213      }
214  
215      /**
216       * set the length (in number of tokens) of the expression
217       * @param len  length
218       */
219  
220      public void setExpressionLength(short len)
221      {
222          field_7_expression_len = len;
223      }
224  
225      //public short getRow()
226      public int getRow()
227      {
228          return field_1_row;
229      }
230  
231      public short getColumn()
232      {
233          return field_2_column;
234      }
235  
236      public short getXFIndex()
237      {
238          return field_3_xf;
239      }
240  
241      /**
242       * get the calculated value of the formula
243       *
244       * @return calculated value
245       */
246  
247      public double getValue()
248      {
249          return field_4_value;
250      }
251  
252      /**
253       * get the option flags
254       *
255       * @return bitmask
256       */
257  
258      public short getOptions()
259      {
260          return field_5_options;
261      }
262  
263      /**
264       * get the length (in number of tokens) of the expression
265       * @return  expression length
266       */
267  
268      public short getExpressionLength()
269      {
270          return field_7_expression_len;
271      }
272  
273      /**
274       * push a token onto the stack
275       *
276       * @param ptg  the token
277       */
278  
279      public void pushExpressionToken(Ptg ptg)
280      {
281          field_8_parsed_expr.push(ptg);
282      }
283  
284      /**
285       * pop a token off of the stack
286       *
287       * @return Ptg - the token
288       */
289  
290      public Ptg popExpressionToken()
291      {
292          return ( Ptg ) field_8_parsed_expr.pop();
293      }
294  
295      /**
296       * peek at the token on the top of stack
297       *
298       * @return Ptg - the token
299       */
300  
301      public Ptg peekExpressionToken()
302      {
303          return ( Ptg ) field_8_parsed_expr.peek();
304      }
305  
306      /**
307       * get the size of the stack
308       * @return size of the stack
309       */
310  
311      public int getNumberOfExpressionTokens()
312      {
313          if (this.field_8_parsed_expr == null) {
314              return 0;
315          } else {
316              return field_8_parsed_expr.size();
317          }
318      }
319  
320      /**
321       * get the stack as a list
322       *
323       * @return list of tokens (casts stack to a list and returns it!)
324       * this method can return null is we are unable to create Ptgs from 
325       *     existing excel file
326       * callers should check for null!
327       */
328  
329      public List getParsedExpression()
330      {
331          return ( List ) field_8_parsed_expr;
332      }
333  
334      /**
335       * called by constructor, should throw runtime exception in the event of a
336       * record passed with a differing ID.
337       *
338       * @param id alleged id for this record
339       */
340  
341      protected void validateSid(short id)
342      {
343          if (id != sid)
344          {
345              throw new RecordFormatException("NOT A FORMULA RECORD");
346          }
347      }
348  
349      public short getSid()
350      {
351          return sid;
352      }
353  
354      /**
355       * called by the class that is responsible for writing this sucker.
356       * Subclasses should implement this so that their data is passed back in a
357       * byte array.
358       *
359       * @return byte array containing instance data
360       */
361  
362      public int serialize(int offset, byte [] data)
363      {
364          if (this.field_8_parsed_expr != null) {
365          int ptgSize = getTotalPtgSize();
366  
367          LittleEndian.putShort(data, 0 + offset, sid);
368          LittleEndian.putShort(data, 2 + offset, ( short ) (22 + ptgSize));
369          //LittleEndian.putShort(data, 4 + offset, getRow());
370          LittleEndian.putShort(data, 4 + offset, ( short ) getRow());
371          LittleEndian.putShort(data, 6 + offset, getColumn());
372          LittleEndian.putShort(data, 8 + offset, getXFIndex());
373          LittleEndian.putDouble(data, 10 + offset, getValue());
374          LittleEndian.putShort(data, 18 + offset, getOptions());
375          LittleEndian.putInt(data, 20 + offset, field_6_zero);
376          LittleEndian.putShort(data, 24 + offset, getExpressionLength());
377          serializePtgs(data, 26+offset);
378          } else {
379              System.arraycopy(all_data,0,data,offset,all_data.length);
380          }
381          return getRecordSize();
382      }
383      
384      
385      
386  
387      public int getRecordSize()
388      {
389          int retval =0;
390          
391          if (this.field_8_parsed_expr != null) {
392              retval = getTotalPtgSize() + 26;
393          } else {
394              retval =all_data.length;
395          }
396          return retval;
397  
398          // return getTotalPtgSize() + 28;
399      }
400  
401      private int getTotalPtgSize()
402      {
403          List list   = getParsedExpression();
404          int  retval = 0;
405  
406          for (int k = 0; k < list.size(); k++)
407          {
408              Ptg ptg = ( Ptg ) list.get(k);
409  
410              retval += ptg.getSize();
411          }
412          return retval;
413      }
414  
415      private void serializePtgs(byte [] data, int offset)
416      {
417          int pos = offset;
418  
419          for (int k = 0; k < field_8_parsed_expr.size(); k++)
420          {
421              Ptg ptg = ( Ptg ) field_8_parsed_expr.get(k);
422  
423              ptg.writeBytes(data, pos);
424              pos += ptg.getSize();
425          }
426      }
427  
428      public boolean isBefore(CellValueRecordInterface i)
429      {
430          if (this.getRow() > i.getRow())
431          {
432              return false;
433          }
434          if ((this.getRow() == i.getRow())
435                  && (this.getColumn() > i.getColumn()))
436          {
437              return false;
438          }
439          if ((this.getRow() == i.getRow())
440                  && (this.getColumn() == i.getColumn()))
441          {
442              return false;
443          }
444          return true;
445      }
446  
447      public boolean isAfter(CellValueRecordInterface i)
448      {
449          if (this.getRow() < i.getRow())
450          {
451              return false;
452          }
453          if ((this.getRow() == i.getRow())
454                  && (this.getColumn() < i.getColumn()))
455          {
456              return false;
457          }
458          if ((this.getRow() == i.getRow())
459                  && (this.getColumn() == i.getColumn()))
460          {
461              return false;
462          }
463          return true;
464      }
465  
466      public boolean isEqual(CellValueRecordInterface i)
467      {
468          return ((this.getRow() == i.getRow())
469                  && (this.getColumn() == i.getColumn()));
470      }
471  
472      public boolean isInValueSection()
473      {
474          return true;
475      }
476  
477      public boolean isValue()
478      {
479          return true;
480      }
481  
482      public int compareTo(Object obj)
483      {
484          CellValueRecordInterface loc = ( CellValueRecordInterface ) obj;
485  
486          if ((this.getRow() == loc.getRow())
487                  && (this.getColumn() == loc.getColumn()))
488          {
489              return 0;
490          }
491          if (this.getRow() < loc.getRow())
492          {
493              return -1;
494          }
495          if (this.getRow() > loc.getRow())
496          {
497              return 1;
498          }
499          if (this.getColumn() < loc.getColumn())
500          {
501              return -1;
502          }
503          if (this.getColumn() > loc.getColumn())
504          {
505              return 1;
506          }
507          return -1;
508      }
509  
510      public boolean equals(Object obj)
511      {
512          if (!(obj instanceof CellValueRecordInterface))
513          {
514              return false;
515          }
516          CellValueRecordInterface loc = ( CellValueRecordInterface ) obj;
517  
518          if ((this.getRow() == loc.getRow())
519                  && (this.getColumn() == loc.getColumn()))
520          {
521              return true;
522          }
523          return false;
524      }
525      
526      
527      public String toString()
528      {
529          StringBuffer buffer = new StringBuffer();
530          if (EXPERIMENTAL_FORMULA_SUPPORT_ENABLED) {
531              buffer.append("[FORMULA]\n");
532              buffer.append("    .row       = ")
533                  .append(Integer.toHexString(getRow())).append("\n");
534              buffer.append("    .column    = ")
535                  .append(Integer.toHexString(getColumn()))
536                  .append("\n");
537              buffer.append("    .xf              = ")
538                  .append(Integer.toHexString(getXFIndex())).append("\n");
539              buffer.append("    .value           = ").append(getValue())
540                  .append("\n");
541              buffer.append("    .options         = ").append(getOptions())
542                  .append("\n");
543              buffer.append("    .zero            = ").append(field_6_zero)
544                  .append("\n");
545              buffer.append("    .expressionlength= ").append(getExpressionLength())
546                  .append("\n");
547              buffer.append("    .numptgsinarray  = ").append(field_8_parsed_expr.size())
548                  .append("\n");
549              
550              
551              for (int k = 0; k < field_8_parsed_expr.size(); k++ ) {
552  /*                buffer.append("formula ").append(k).append(" ")
553                 .append(((Ptg)field_8_parsed_expr.get(k)).toFormulaString());*/
554                  buffer.append("Formula ")
555                  .append(k)
556                  .append("=")
557                  .append(((Ptg)field_8_parsed_expr.get(k)).toString())
558                  .append("\n")
559                  .append(((Ptg)field_8_parsed_expr.get(k)).toDebugString())
560                  .append("\n");                
561              }
562              
563              
564              buffer.append("[/FORMULA]\n");
565          } else {
566              buffer.append(super.toString());
567          }
568          return buffer.toString();
569      }
570      
571  }
572