FormatRecord


1   /*********************************************************************
2   *
3   *      Copyright (C) 2002 Andrew Khan
4   *
5   * This library is free software; you can redistribute it and/or
6   * modify it under the terms of the GNU Lesser General Public
7   * License as published by the Free Software Foundation; either
8   * version 2.1 of the License, or (at your option) any later version.
9   *
10  * This library is distributed in the hope that it will be useful,
11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
13  * Lesser General Public License for more details.
14  *
15  * You should have received a copy of the GNU Lesser General Public
16  * License along with this library; if not, write to the Free Software
17  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
18  ***************************************************************************/
19  
20  package jxl.biff;
21  
22  import java.text.DateFormat  ;
23  import java.text.SimpleDateFormat  ;
24  import java.text.NumberFormat  ;
25  import java.text.DecimalFormat  ;
26  
27  import common.Logger;
28  import common.Assert;
29  
30  import jxl.WorkbookSettings;
31  import jxl.read.biff.Record;
32  import jxl.format.Format;
33  
34  /**
35   * A non-built format record
36   */
37  public class FormatRecord extends WritableRecordData
38    implements DisplayFormat, Format  
39  {
40    /**
41     * The logger
42     */
43    public static Logger logger = Logger.getLogger(FormatRecord.class);
44  
45    /**
46     * Initialized flag
47     */
48    private boolean initialized;
49  
50    /**
51     * The raw data
52     */
53    private byte[] data;
54  
55    /**
56     * The index code
57     */
58    private int indexCode;
59  
60    /**
61     * The formatting string
62     */
63    private String   formatString;
64  
65    /**
66     * Indicates whether this is a date formatting record
67     */
68    private boolean date;
69  
70    /**
71     * Indicates whether this a number formatting record
72     */
73    private boolean number;
74  
75    /**
76     * The format object
77     */
78    private java.text.Format   format;
79  
80    /**
81     * The workbook settings - used mainly for reading
82     */
83    private WorkbookSettings settings;
84  
85    /**
86     * The date strings to look for
87     */
88    private static String  [] dateStrings = new String  []
89    {
90      "dd",
91      "mm",
92      "yy",
93      "hh",
94      "ss",
95      "m/",
96      "/d"
97    };
98  
99    // Type to distinguish between biff7 and biff8
100   private static class BiffType
101   {
102   }
103 
104   public static final BiffType biff8 = new BiffType();
105   public static final BiffType biff7 = new BiffType();
106 
107   /**
108    * Constructor invoked when copying sheets
109    *
110    * @param fmt the format string
111    * @param refno the index code
112    */
113   FormatRecord(String   fmt, int refno)
114   {
115     super(Type.FORMAT);
116     formatString = fmt;
117     indexCode    = refno;
118     initialized  = true;
119   }
120 
121   /**
122    * Constructor used by writable formats
123    */
124   protected FormatRecord()
125   {
126     super(Type.FORMAT);
127     initialized  = false;
128   }
129 
130   /**
131    * Copy constructor - can be invoked by public access
132    *
133    * @param fr the format to copy
134    */
135   protected FormatRecord(FormatRecord fr)
136   {
137     super(Type.FORMAT);
138     initialized = false;
139 
140     formatString = fr.formatString;
141     date = fr.date;
142     number = fr.number;
143     //    format = (java.text.Format) fr.format.clone();
144   }
145 
146   /**
147    * Constructs this object from the raw data.  Used when reading in a
148    * format record
149    *
150    * @param t the raw data
151    * @param ws the workbook settings
152    * @param biffType biff type dummy overload
153    */
154   public FormatRecord(Record t, WorkbookSettings ws, BiffType biffType)
155   {
156     super(t);
157 
158     byte[] data = getRecord().getData();
159     indexCode = IntegerHelper.getInt(data[0], data[1]);
160     initialized = true;
161 
162     if (biffType == biff8)
163     {
164       int numchars = IntegerHelper.getInt(data[2], data[3]);
165       if (data[4] == 0)
166       {
167         formatString = StringHelper.getString(data, numchars, 5, ws);
168       }
169       else
170       {
171         formatString = StringHelper.getUnicodeString(data, numchars, 5);
172       }
173     }
174     else
175     {
176       int numchars = data[2];
177       byte[] chars = new byte[numchars];
178       System.arraycopy(data, 3, chars, 0, chars.length);
179       formatString = new String  (chars);
180     }
181 
182     date = false;
183     number = false;
184 
185     // First see if this is a date format
186     for (int i = 0 ; i < dateStrings.length; i++)
187     {
188       String   dateString = dateStrings[i];
189       if (formatString.indexOf(dateString) != -1 || 
190           formatString.indexOf(dateString.toUpperCase()) != -1)
191       {
192         date = true;
193         break;
194       }
195     }
196 
197     // See if this is number format - look for the # or 0 characters
198     if (!date)
199     {
200       if (formatString.indexOf('#') != -1 ||
201           formatString.indexOf('0') != -1 )
202       {
203         number = true;
204       }
205     }
206   }
207 
208   /**
209    * Used to get the data when writing out the format record
210    *
211    * @return the raw data
212    */
213   public byte[] getData()
214   {
215     data = new byte[formatString.length() * 2 + 3 + 2];
216 
217     IntegerHelper.getTwoBytes(indexCode, data, 0);
218     IntegerHelper.getTwoBytes(formatString.length(), data, 2);
219     data[4] = (byte) 1; // unicode indicator
220     StringHelper.getUnicodeBytes(formatString, data, 5);
221 
222     return data;
223   }
224 
225   /**
226    * Gets the format index of this record
227    *
228    * @return the format index of this record
229    */
230   public int getFormatIndex()
231   {
232     return indexCode;
233   }
234 
235   /**
236    * Accessor to see whether this object is initialized or not.
237    *
238    * @return TRUE if this font record has been initialized, FALSE otherwise
239    */
240   public boolean isInitialized()
241   {
242     return initialized;
243   }
244 
245   /**
246    * Sets the index of this record.  Called from the FormattingRecords
247    *  object
248    *
249    * @param pos the position of this font in the workbooks font list
250    */
251 
252   public void initialize(int pos)
253   {
254     indexCode = pos;
255     initialized = true;
256   }
257 
258   /**
259    * Replaces all instances of search with replace in the input.  Used for
260    * replacing microsoft number formatting characters with java equivalents
261    *
262    * @param input the format string
263    * @param search the Excel character to be replaced
264    * @param replace the java equivalent
265    * @return the input string with the specified substring replaced
266    */
267   protected final String   replace(String   input, String   search, String   replace)
268   {
269     String   fmtstr = input;
270     int pos = fmtstr.indexOf(search);
271     while (pos != -1)
272     {
273       StringBuffer   tmp = new StringBuffer  (fmtstr.substring(0, pos));
274       tmp.append(replace);
275       tmp.append(fmtstr.substring(pos + search.length()));
276       fmtstr = tmp.toString();
277       pos = fmtstr.indexOf(search);
278     }
279     return fmtstr;
280   }
281 
282   /**
283    * Called by the immediate subclass to set the string
284    * once the Java-Excel replacements have been done
285    *
286    * @param s the format string
287    */
288   protected final void setFormatString(String   s)
289   {
290     formatString = s;
291   }
292 
293   /**
294    * Sees if this format is a date format
295    *
296    * @return TRUE if this format is a date
297    */
298   public final  boolean isDate()
299   {
300     return date;
301   }
302 
303   /**
304    * Sees if this format is a number format
305    *
306    * @return TRUE if this format is a number
307    */
308   public final boolean isNumber()
309   {
310     return number;
311   }
312 
313   /**
314    * Gets the java equivalent number format for the formatString
315    *
316    * @return The java equivalent of the number format for this object
317    */
318   public final NumberFormat   getNumberFormat()
319   {
320     if (format != null && format instanceof NumberFormat  )
321     {
322       return (NumberFormat  ) format;
323     }
324 
325     try
326     {
327       String   fs = formatString;
328 
329       // Replace the Excel formatting characters with java equivalents
330       fs = replace(fs, "E+", "E");
331       fs = replace(fs, "_)", "");
332       fs = replace(fs, "_", "");
333       fs = replace(fs, "[Red]", "");
334       fs = replace(fs, "\\", "");
335 
336       format = new DecimalFormat  (fs);
337     }
338     catch (IllegalArgumentException   e)
339     {
340       // Something went wrong with the date format - fail silently
341       // and return a default value
342       format = new DecimalFormat  ("#.###");
343     }
344 
345     return (NumberFormat  ) format;
346   }
347 
348   /**
349    * Gets the java equivalent date format for the formatString
350    *
351    * @return The java equivalent of the date format for this object
352    */
353   public final DateFormat   getDateFormat()
354   {
355     if (format != null && format instanceof DateFormat  )
356     {
357       return (DateFormat  ) format;
358     }
359 
360     String   fmt = formatString;
361 
362     // Replace the AM/PM indicator with an a
363     int pos = fmt.indexOf("AM/PM");
364     while (pos != -1)
365     {
366       StringBuffer   sb = new StringBuffer  (fmt.substring(0, pos));
367       sb.append('a');
368       sb.append(fmt.substring(pos + 5));
369       fmt = sb.toString();
370       pos = fmt.indexOf("AM/PM");
371     }
372 
373     // Replace ss.0 with ss.SSS (necessary to always specify milliseconds
374     // because of NT)
375     pos = fmt.indexOf("ss.0");
376     while (pos != -1)
377     {
378       StringBuffer   sb = new StringBuffer  (fmt.substring(0, pos));
379       sb.append("ss.SSS");
380 
381       // Keep going until we run out of zeros
382       pos += 4;
383       while (pos < fmt.length() && fmt.charAt(pos) == '0')
384       {
385         pos++;
386       }
387 
388       sb.append(fmt.substring(pos));
389       fmt = sb.toString();
390       pos = fmt.indexOf("ss.0");
391     }
392 
393 
394     // Filter out the backslashes
395     StringBuffer   sb = new StringBuffer  ();
396     for (int i = 0; i < fmt.length(); i++)
397     {
398       if (fmt.charAt(i) != '\\')
399       {
400         sb.append(fmt.charAt(i));
401       }
402     }
403 
404     fmt = sb.toString();
405 
406     // If the date format starts with anything inside square brackets then 
407     // filter tham out
408     if (fmt.charAt(0) == '[')
409     {
410       int end = fmt.indexOf(']');
411       if (end != -1)
412       {
413         fmt = fmt.substring(end+1);
414       }
415     }
416     
417     // Get rid of some spurious characters that can creep in
418     fmt = replace(fmt, ";@", "");
419 
420     // We need to convert the month indicator m, to upper case when we
421     // are dealing with dates
422     char[] formatBytes = fmt.toCharArray();
423 
424     for (int i = 0; i < formatBytes.length; i++)
425     {
426       if (formatBytes[i] == 'm')
427       {
428         // Firstly, see if the preceding character is also an m.  If so,
429         // copy that
430         if (i > 0 && (formatBytes[i - 1] == 'm' || formatBytes[i - 1] == 'M'))
431         {
432           formatBytes[i] = formatBytes[i - 1];
433         }
434         else
435         {
436           // There is no easy way out.  We have to deduce whether this an
437           // minute or a month?  See which is closest out of the
438           // letters H d s or y
439           // First, h
440           int minuteDist = Integer.MAX_VALUE;
441           for (int j = i - 1; j > 0; j--)
442           {
443             if (formatBytes[j] == 'h')
444             {
445               minuteDist = i - j;
446               break;
447             }
448           }
449 
450           for (int j = i + 1; j < formatBytes.length; j++)
451           {
452             if (formatBytes[j] == 'h')
453             {
454               minuteDist = Math.min(minuteDist, j - i);
455               break;
456             }
457           }
458 
459           for (int j = i - 1; j > 0; j--)
460           {
461             if (formatBytes[j] == 'H')
462             {
463               minuteDist = i - j;
464               break;
465             }
466           }
467 
468           for (int j = i + 1; j < formatBytes.length; j++)
469           {
470             if (formatBytes[j] == 'H')
471             {
472               minuteDist = Math.min(minuteDist, j - i);
473               break;
474             }
475           }
476 
477           // Now repeat for s
478           for (int j = i - 1; j > 0; j--)
479           {
480             if (formatBytes[j] == 's')
481             {
482               minuteDist = Math.min(minuteDist, i - j);
483               break;
484             }
485           }
486           for (int j = i + 1; j < formatBytes.length; j++)
487           {
488             if (formatBytes[j] == 's')
489             {
490               minuteDist = Math.min(minuteDist, j - i);
491               break;
492             }
493           }
494           // We now have the distance of the closest character which could
495           // indicate the the m refers to a minute
496           // Repeat for d and y
497           int monthDist = Integer.MAX_VALUE;
498           for (int j = i - 1; j > 0; j--)
499           {
500             if (formatBytes[j] == 'd')
501             {
502               monthDist = i - j;
503               break;
504             }
505           }
506 
507           for (int j = i + 1; j < formatBytes.length; j++)
508           {
509             if (formatBytes[j] == 'd')
510             {
511               monthDist = Math.min(monthDist, j - i);
512               break;
513             }
514           }
515           // Now repeat for y
516           for (int j = i - 1; j > 0; j--)
517           {
518             if (formatBytes[j] == 'y')
519             {
520               monthDist = Math.min(monthDist, i - j);
521               break;
522             }
523           }
524           for (int j = i + 1; j < formatBytes.length; j++)
525           {
526             if (formatBytes[j] == 'y')
527             {
528               monthDist = Math.min(monthDist, j - i);
529               break;
530             }
531           }
532 
533           if (monthDist < minuteDist)
534           {
535             // The month indicator is closer, so convert to a capital M
536             formatBytes[i] = Character.toUpperCase(formatBytes[i]);
537           }
538           else if ((monthDist == minuteDist) &&
539                    (monthDist != Integer.MAX_VALUE))
540           {
541             // They are equidistant.  As a tie-breaker, take the formatting
542             // character which precedes the m
543             char ind = formatBytes[i - monthDist];
544             if (ind == 'y' || ind == 'd')
545             {
546               // The preceding item indicates a month measure, so convert
547               formatBytes[i] = Character.toUpperCase(formatBytes[i]);
548             }
549           }
550         }
551       }
552     }
553 
554     try
555     {
556       this.format = new SimpleDateFormat  (new String  (formatBytes));
557     }
558     catch (IllegalArgumentException   e)
559     {
560       // There was a spurious character - fail silently
561       this.format = new SimpleDateFormat  ("dd MM yyyy hh:mm:ss");
562     }
563     return (DateFormat  ) this.format;
564   }
565 
566   /**
567    * Gets the index code, for use as a hash value
568    *
569    * @return the ifmt code for this cell
570    */
571   public int getIndexCode()
572   {
573     return indexCode;
574   }
575 
576   /**
577    * Gets the formatting string.
578    *
579    * @return the excel format string
580    */
581   public String   getFormatString()
582   {
583     return formatString;
584   }
585 
586   /**
587    * Indicates whether this formula is a built in
588    *
589    * @return FALSE
590    */
591   public boolean isBuiltIn()
592   {
593     return false;
594   }
595 
596   /**
597    * Standard hash code method
598    * @return the hash code value for this object
599    */
600   public int hashCode()
601   {
602     return formatString.hashCode();
603   }
604 
605   /**
606    * Standard equals method.  This compares the contents of two
607    * format records, and not their indexCodes, which are ignored
608    *
609    * @param o the object to compare
610    * @return TRUE if the two objects are equal, FALSE otherwise
611    */
612   public boolean equals(Object   o)
613   {
614     if (o == this)
615     {
616       return true;
617     }
618 
619     if (!(o instanceof FormatRecord))
620     {
621       return false;
622     }
623 
624     FormatRecord fr = (FormatRecord) o;
625 
626     // Not interested in uninitialized comparisons
627     if (!initialized || !fr.initialized)
628     {
629       return false;
630     }
631 
632     // Must be either a number or a date format
633     if (date != fr.date ||
634         number != fr.number)
635     {
636       return false;
637     }
638 
639     return formatString.equals(fr.formatString);
640   }
641 }
642 
643 
644 
645 
646 
647 
648
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags