FormulaRecord


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