1 /* ==================================================================== 2 * The Apache Software License, Version 1.1 3 * 4 * Copyright (c) 2002 The Apache Software Foundation. All rights 5 * reserved. 6 * 7 * Redistribution and use in source and binary forms, with or without 8 * modification, are permitted provided that the following conditions 9 * are met: 10 * 11 * 1. Redistributions of source code must retain the above copyright 12 * notice, this list of conditions and the following disclaimer. 13 * 14 * 2. Redistributions in binary form must reproduce the above copyright 15 * notice, this list of conditions and the following disclaimer in 16 * the documentation and/or other materials provided with the 17 * distribution. 18 * 19 * 3. The end-user documentation included with the redistribution, 20 * if any, must include the following acknowledgment: 21 * "This product includes software developed by the 22 * Apache Software Foundation (http://www.apache.org/)." 23 * Alternately, this acknowledgment may appear in the software itself, 24 * if and wherever such third-party acknowledgments normally appear. 25 * 26 * 4. The names "Apache" and "Apache Software Foundation" and 27 * "Apache POI" must not be used to endorse or promote products 28 * derived from this software without prior written permission. For 29 * written permission, please contact apache@apache.org. 30 * 31 * 5. Products derived from this software may not be called "Apache", 32 * "Apache POI", nor may "Apache" appear in their name, without 33 * prior written permission of the Apache Software Foundation. 34 * 35 * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED 36 * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES 37 * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE 38 * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR 39 * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, 40 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT 41 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF 42 * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND 43 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, 44 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT 45 * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF 46 * SUCH DAMAGE. 47 * ==================================================================== 48 * 49 * This software consists of voluntary contributions made by many 50 * individuals on behalf of the Apache Software Foundation. For more 51 * information on the Apache Software Foundation, please see 52 * <http://www.apache.org/>. 53 */ 54 55 /* 56 * HSSFDataFormat.java 57 * 58 * Created on December 18, 2001, 12:42 PM 59 */ 60 package org.apache.poi.hssf.usermodel; 61 62 import org.apache.poi.hssf.model.Workbook; 63 import org.apache.poi.hssf.record.FormatRecord; 64 65 import java.util.Iterator; 66 import java.util.List; 67 import java.util.ListIterator; 68 import java.util.Vector; 69 70 /** 71 * Utility to identify builin formats. Now can handle user defined data formats also. The following is a list of the formats as 72 * returned by this class.<P> 73 *<P> 74 * 0, "General"<br> 75 * 1, "0"<br> 76 * 2, "0.00"<br> 77 * 3, "#,##0"<br> 78 * 4, "#,##0.00"<br> 79 * 5, "($#,##0_);($#,##0)"<br> 80 * 6, "($#,##0_);[Red]($#,##0)"<br> 81 * 7, "($#,##0.00);($#,##0.00)"<br> 82 * 8, "($#,##0.00_);[Red]($#,##0.00)"<br> 83 * 9, "0%"<br> 84 * 0xa, "0.00%"<br> 85 * 0xb, "0.00E+00"<br> 86 * 0xc, "# ?/?"<br> 87 * 0xd, "# ??/??"<br> 88 * 0xe, "m/d/yy"<br> 89 * 0xf, "d-mmm-yy"<br> 90 * 0x10, "d-mmm"<br> 91 * 0x11, "mmm-yy"<br> 92 * 0x12, "h:mm AM/PM"<br> 93 * 0x13, "h:mm:ss AM/PM"<br> 94 * 0x14, "h:mm"<br> 95 * 0x15, "h:mm:ss"<br> 96 * 0x16, "m/d/yy h:mm"<br> 97 *<P> 98 * // 0x17 - 0x24 reserved for international and undocumented 99 * 0x25, "(#,##0_);(#,##0)"<P> 100 * 0x26, "(#,##0_);[Red](#,##0)"<P> 101 * 0x27, "(#,##0.00_);(#,##0.00)"<P> 102 * 0x28, "(#,##0.00_);[Red](#,##0.00)"<P> 103 * 0x29, "_(*#,##0_);_(*(#,##0);_(* \"-\"_);_(@_)"<P> 104 * 0x2a, "_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)"<P> 105 * 0x2b, "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)"<P> 106 * 0x2c, "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)"<P> 107 * 0x2d, "mm:ss"<P> 108 * 0x2e, "[h]:mm:ss"<P> 109 * 0x2f, "mm:ss.0"<P> 110 * 0x30, "##0.0E+0"<P> 111 * 0x31, "@" - This is text format.<P> 112 * 0x31 "text" - Alias for "@"<P> 113 * 114 * @author Andrew C. Oliver (acoliver at apache dot org) 115 * @author Shawn M. Laubach (slaubach at apache dot org) 116 */ 117 118 public class HSSFDataFormat 119 { 120 private static Vector builtinFormats; 121 122 private Vector formats = new Vector(); 123 private Workbook workbook; 124 private boolean movedBuiltins = false; // Flag to see if need to 125 // check the built in list 126 // or if the regular list 127 // has all entries. 128 129 /** 130 * Construncts a new data formatter. It takes a workbook to have 131 * access to the workbooks format records. 132 * @param workbook the workbook the formats are tied to. 133 */ 134 public HSSFDataFormat( Workbook workbook ) 135 { 136 this.workbook = workbook; 137 if ( builtinFormats == null ) populateBuiltinFormats(); 138 Iterator i = workbook.getFormats().iterator(); 139 while ( i.hasNext() ) 140 { 141 FormatRecord r = (FormatRecord) i.next(); 142 if ( formats.size() < r.getIndexCode() + 1 ) 143 { 144 formats.setSize( r.getIndexCode() + 1 ); 145 } 146 formats.set( r.getIndexCode(), r.getFormatString() ); 147 } 148 149 } 150 151 private static synchronized void populateBuiltinFormats() 152 { 153 builtinFormats = new Vector(); 154 builtinFormats.add( 0, "General" ); 155 builtinFormats.add( 1, "0" ); 156 builtinFormats.add( 2, "0.00" ); 157 builtinFormats.add( 3, "#,##0" ); 158 builtinFormats.add( 4, "#,##0.00" ); 159 builtinFormats.add( 5, "($#,##0_);($#,##0)" ); 160 builtinFormats.add( 6, "($#,##0_);[Red]($#,##0)" ); 161 builtinFormats.add( 7, "($#,##0.00);($#,##0.00)" ); 162 builtinFormats.add( 8, "($#,##0.00_);[Red]($#,##0.00)" ); 163 builtinFormats.add( 9, "0%" ); 164 builtinFormats.add( 0xa, "0.00%" ); 165 builtinFormats.add( 0xb, "0.00E+00" ); 166 builtinFormats.add( 0xc, "# ?/?" ); 167 builtinFormats.add( 0xd, "# ??/??" ); 168 builtinFormats.add( 0xe, "m/d/yy" ); 169 builtinFormats.add( 0xf, "d-mmm-yy" ); 170 builtinFormats.add( 0x10, "d-mmm" ); 171 builtinFormats.add( 0x11, "mmm-yy" ); 172 builtinFormats.add( 0x12, "h:mm AM/PM" ); 173 builtinFormats.add( 0x13, "h:mm:ss AM/PM" ); 174 builtinFormats.add( 0x14, "h:mm" ); 175 builtinFormats.add( 0x15, "h:mm:ss" ); 176 builtinFormats.add( 0x16, "m/d/yy h:mm" ); 177 178 // 0x17 - 0x24 reserved for international and undocumented 179 builtinFormats.add( 0x17, "0x17" ); 180 builtinFormats.add( 0x18, "0x18" ); 181 builtinFormats.add( 0x19, "0x19" ); 182 builtinFormats.add( 0x1a, "0x1a" ); 183 builtinFormats.add( 0x1b, "0x1b" ); 184 builtinFormats.add( 0x1c, "0x1c" ); 185 builtinFormats.add( 0x1d, "0x1d" ); 186 builtinFormats.add( 0x1e, "0x1e" ); 187 builtinFormats.add( 0x1f, "0x1f" ); 188 builtinFormats.add( 0x20, "0x20" ); 189 builtinFormats.add( 0x21, "0x21" ); 190 builtinFormats.add( 0x22, "0x22" ); 191 builtinFormats.add( 0x23, "0x23" ); 192 builtinFormats.add( 0x24, "0x24" ); 193 194 // 0x17 - 0x24 reserved for international and undocumented 195 builtinFormats.add( 0x25, "(#,##0_);(#,##0)" ); 196 builtinFormats.add( 0x26, "(#,##0_);[Red](#,##0)" ); 197 builtinFormats.add( 0x27, "(#,##0.00_);(#,##0.00)" ); 198 builtinFormats.add( 0x28, "(#,##0.00_);[Red](#,##0.00)" ); 199 builtinFormats.add( 0x29, "_(*#,##0_);_(*(#,##0);_(* \"-\"_);_(@_)" ); 200 builtinFormats.add( 0x2a, "_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)" ); 201 builtinFormats.add( 0x2b, "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)" ); 202 builtinFormats.add( 0x2c, 203 "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)" ); 204 builtinFormats.add( 0x2d, "mm:ss" ); 205 builtinFormats.add( 0x2e, "[h]:mm:ss" ); 206 builtinFormats.add( 0x2f, "mm:ss.0" ); 207 builtinFormats.add( 0x30, "##0.0E+0" ); 208 builtinFormats.add( 0x31, "@" ); 209 } 210 211 public static List getBuiltinFormats() 212 { 213 if ( builtinFormats == null ) 214 { 215 populateBuiltinFormats(); 216 } 217 return builtinFormats; 218 } 219 220 /** 221 * get the format index that matches the given format string<p> 222 * Automatically converts "text" to excel's format string to represent text. 223 * @param format string matching a built in format 224 * @return index of format or -1 if undefined. 225 */ 226 227 public static short getBuiltinFormat( String format ) 228 { 229 if (format.toUpperCase().equals("TEXT")) 230 format = "@"; 231 232 if ( builtinFormats == null ) 233 { 234 populateBuiltinFormats(); 235 } 236 short retval = -1; 237 238 for (short k = 0; k <= 0x31; k++) 239 { 240 String nformat = (String) builtinFormats.get( k ); 241 242 if ( ( nformat != null ) && nformat.equals( format ) ) 243 { 244 retval = k; 245 break; 246 } 247 } 248 return retval; 249 } 250 251 /** 252 * get the format index that matches the given format string. 253 * Creates a new format if one is not found. Aliases text to the proper format. 254 * @param format string matching a built in format 255 * @return index of format. 256 */ 257 258 public short getFormat( String format ) 259 { 260 ListIterator i; 261 int ind; 262 263 if (format.toUpperCase().equals("TEXT")) 264 format = "@"; 265 266 if ( !movedBuiltins ) 267 { 268 i = builtinFormats.listIterator(); 269 while ( i.hasNext() ) 270 { 271 ind = i.nextIndex(); 272 formats.add( ind, i.next() ); 273 } 274 movedBuiltins = true; 275 } 276 i = formats.listIterator(); 277 while ( i.hasNext() ) 278 { 279 ind = i.nextIndex(); 280 if ( format.equals( i.next() ) ) 281 return (short) ind; 282 } 283 284 ind = workbook.getFormat( format, true ); 285 if ( formats.size() <= ind ) 286 formats.setSize( ind + 1 ); 287 formats.add( ind, format ); 288 289 return (short) ind; 290 } 291 292 /** 293 * get the format string that matches the given format index 294 * @param index of a format 295 * @return string represented at index of format or null if there is not a format at that index 296 */ 297 298 public String getFormat( short index ) 299 { 300 if ( movedBuiltins ) 301 return (String) formats.get( index ); 302 else 303 return (String) ( builtinFormats.size() > index 304 && builtinFormats.get( index ) != null 305 ? builtinFormats.get( index ) : formats.get( index ) ); 306 } 307 308 /** 309 * get the format string that matches the given format index 310 * @param index of a built in format 311 * @return string represented at index of format or null if there is not a builtin format at that index 312 */ 313 314 public static String getBuiltinFormat( short index ) 315 { 316 if ( builtinFormats == null ) 317 { 318 populateBuiltinFormats(); 319 } 320 return (String) builtinFormats.get( index ); 321 } 322 323 /** 324 * get the number of builtin and reserved builtinFormats 325 * @return number of builtin and reserved builtinFormats 326 */ 327 328 public static int getNumberOfBuiltinBuiltinFormats() 329 { 330 if ( builtinFormats == null ) 331 { 332 populateBuiltinFormats(); 333 } 334 return builtinFormats.size(); 335 } 336 } 337