Attachment #2493 for bug #11068

View | Details | Raw Unified | Return to bug 11068
Collapse All | Expand All

Lines 50-56 Link Here

(-)src/documentation/xdocs/who.xml (-1 / +3 lines)
50	</ul>	50	</ul>
51	</section>	51	</section>
52	<section title="Developers">	52	<section title="Developers">
53		53	<ul>
		54	<li>Shawn Laubach (shawnlaubach at cox dot net)</li>
		55	</ul>
54	</section>	56	</section>
55	</section>	57	</section>
56		58




                    <li><link href="#WorkingWithFonts">Working with fonts</link></li>
                    <li><link href="#ReadWriteWorkbook">Reading and writing</link></li>
                    <li><link href="#NewLinesInCells">Use newlines in cells.</link></li>
                    <li><link href="#DataFormats">Create user defined data formats.</link></li>
                </ul>
            </section>
            <section title="Features">

    wb.write( fileOut );
    fileOut.close();</source>
                </section>
                <anchor id="DataFormats"/>
                <section title="Data Formats">
                    <source>
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("format sheet");
    HSSFCellStyle style;
    HSSFDataFormat format = wb.createDataFormat();
    HSSFRow row;
    HSSFCell cell;
    short rowNum = 0;
    short colNum = 0;

    row = sheet.createRow(rowNum++);
    cell = row.createCell(colNum);
    cell.setCellValue(11111.25);
    style = wb.createCellStyle();
    style.setDataFormat(format.getFormat("0.0"));
    cell.setCellStyle(style);

    row = sheet.createRow(rowNum++);
    cell = row.createCell(colNum);
    cell.setCellValue(11111.25);
    style = wb.createCellStyle();
    style.setDataFormat(format.getFormat("#,##0.0000"));
    cell.setCellStyle(style);

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                    </source>
                </section>

            </section>
        </section>
    </body>

Lines 84-89 Link Here

(-)src/java/org/apache/poi/hssf/model/Workbook.java (-11 / +84 lines)
84	* Kit (Microsoft Press) and the documentation at http://sc.openoffice.org/excelfileformat.pdf	84	* Kit (Microsoft Press) and the documentation at http://sc.openoffice.org/excelfileformat.pdf
85	* before even attempting to use this.	85	* before even attempting to use this.
86	*	86	*
		87	* @author Shawn Laubach (shawnlaubach at cox.net) (Data Formats)
87	* @author Andrew C. Oliver (acoliver at apache dot org)	88	* @author Andrew C. Oliver (acoliver at apache dot org)
88	* @author Glen Stampoultzis (glens at apache.org)	89	* @author Glen Stampoultzis (glens at apache.org)
89	* @author Sergei Kozello (sergeikozello at mail.ru)	90	* @author Sergei Kozello (sergeikozello at mail.ru)
Lines 128-133 Link Here
128	*/	129	*/
129		130
130		131
		132
		133	protected ArrayList formats = new ArrayList();
131	protected ArrayList boundsheets = new ArrayList();	134	protected ArrayList boundsheets = new ArrayList();
132		135
133	protected ArrayList names = new ArrayList();	136	protected ArrayList names = new ArrayList();
Lines 150-155 Link Here
150	0; // holds the position of last name record	153	0; // holds the position of last name record
151	private int supbookpos =	154	private int supbookpos =
152	0; // holds the position of sup book	155	0; // holds the position of sup book
		156	private short maxformatid =
		157	-1; // holds the max format id
153		158
154	private static POILogger log =	159	private static POILogger log =
155	POILogFactory.getLogger(Workbook.class);	160	POILogFactory.getLogger(Workbook.class);
Lines 238-244 Link Here
238	log.log(DEBUG, "found SupBook record at " + k);	243	log.log(DEBUG, "found SupBook record at " + k);
239	retval.supbookpos = k;	244	retval.supbookpos = k;
240	break;	245	break;
241		246	case FormatRecord.sid :
		247	log.log(DEBUG, "found format record at " + k);
		248	retval.formats.add(rec);
		249	retval.maxformatid = retval.maxformatid >= ((FormatRecord)rec).getIndexCode() ? retval.maxformatid : ((FormatRecord)rec).getIndexCode();
		250	break;
242	default :	251	default :
243	}	252	}
244	records.add(rec);	253	records.add(rec);
Lines 263-268 Link Here
263	log.log(DEBUG, "creating new workbook from scratch");	272	log.log(DEBUG, "creating new workbook from scratch");
264	Workbook retval = new Workbook();	273	Workbook retval = new Workbook();
265	ArrayList records = new ArrayList(30);	274	ArrayList records = new ArrayList(30);
		275	ArrayList formats = new ArrayList(8);
		276	Record rec;
		277	int i;
266		278
267	records.add(retval.createBOF());	279	records.add(retval.createBOF());
268	records.add(retval.createInterfaceHdr());	280	records.add(retval.createInterfaceHdr());
Lines 293-306 Link Here
293	records.add(retval.createFont());	305	records.add(retval.createFont());
294	retval.fontpos = records.size() - 1; // last font record postion	306	retval.fontpos = records.size() - 1; // last font record postion
295	retval.numfonts = 4;	307	retval.numfonts = 4;
296	records.add(retval.createFormat(0));	308	for (i = 0; i <= 7; i++) {
297	records.add(retval.createFormat(1));	309	rec = retval.createFormat(i);
298	records.add(retval.createFormat(2));	310	retval.maxformatid = retval.maxformatid >= ((FormatRecord)rec).getIndexCode() ? retval.maxformatid : ((FormatRecord)rec).getIndexCode();
299	records.add(retval.createFormat(3));	311	formats.add(rec);
300	records.add(retval.createFormat(4));	312	records.add(rec);
301	records.add(retval.createFormat(5));	313	}
302	records.add(retval.createFormat(6));	314	retval.formats = formats;
303	records.add(retval.createFormat(7));
304	for (int k = 0; k < 21; k++) {	315	for (int k = 0; k < 21; k++) {
305	records.add(retval.createExtendedFormat(k));	316	records.add(retval.createExtendedFormat(k));
306	retval.numxfs++;	317	retval.numxfs++;
Lines 1790-1800 Link Here
1790	SupBookRecord supbook = new SupBookRecord();	1801	SupBookRecord supbook = new SupBookRecord();
1791		1802
1792	supbook.setNumberOfSheets((short)getNumSheets());	1803	supbook.setNumberOfSheets((short)getNumSheets());
		1804
1793	//supbook.setFlag();	1805	//supbook.setFlag();
1794		1806
1795	records.add(supbookpos + 1 , supbook);	1807	records.add(supbookpos + 1 , supbook);
1796		1808
1797	return rec;	1809	return rec;
		1810	}
		1811
		1812	/**
		1813	* Returns a format index that matches the passed in format. It does not tie into HSSFDataFormat.
		1814	* @param format the format string
		1815	* @param createIfNotFound creates a new format if format not found
		1816	* @return the format id of a format that matches or -1 if none found and createIfNotFound
		1817	*/
		1818	public short getFormat(String format, boolean createIfNotFound) {
		1819	Iterator iterator;
		1820	for (iterator = formats.iterator(); iterator.hasNext();) {
		1821	FormatRecord r = (FormatRecord)iterator.next();
		1822	if (r.getFormatString().equals(format)) {
		1823	return r.getIndexCode();
		1824	}
		1825	}
		1826
		1827	if (createIfNotFound) {
		1828	return createFormat(format);
		1829	}
		1830
		1831	return -1;
		1832	}
		1833
		1834	/**
		1835	* Creates a FormatRecord, inserts it, and returns the index code.
		1836	* @param format the format string
		1837	* @return the index code of the format record.
		1838	* @see org.apache.poi.hssf.record.FormatRecord
		1839	* @see org.apache.poi.hssf.record.Record
		1840	*/
		1841	public short createFormat(String format) {
		1842	FormatRecord rec = new FormatRecord();
		1843	maxformatid = maxformatid >= (short)0xa4 ? (short)(maxformatid + 1) : (short)0xa4; //Starting value from M$ empiracle study.
		1844	rec.setIndexCode(maxformatid);
		1845	rec.setFormatStringLength((byte)format.length());
		1846	rec.setFormatString(format);
		1847
		1848	int pos = 0;
		1849	while (pos < records.size() && ((Record)records.get(pos)).getSid() != FormatRecord.sid)
		1850	pos++;
		1851	pos += formats.size();
		1852	formats.add(rec);
		1853	records.add(pos, rec);
		1854	return maxformatid;
1798	}	1855	}
1799		1856
1800		1857
Lines 1804-1809 Link Here
1804		1861
1805	public Record findFirstRecordBySid(short sid) {	1862	public Record findFirstRecordBySid(short sid) {
1806	for (Iterator iterator = records.iterator(); iterator.hasNext(); ) {	1863	for (Iterator iterator = records.iterator(); iterator.hasNext(); ) {
		1864	Record record = ( Record ) iterator.next();
		1865
		1866	if (record.getSid() == sid) {
		1867	return record;
		1868	}
		1869	}
		1870	return null;
		1871	}
		1872
		1873	/**
		1874	* Returns the next occurance of a record matching a particular sid.
		1875	*/
		1876	public Record findNextRecordBySid(short sid, int pos) {
		1877	Iterator iterator = records.iterator();
		1878	for (;pos > 0 && iterator.hasNext(); iterator.next(),pos--);
		1879	while (iterator.hasNext()) {
1807	Record record = ( Record ) iterator.next();	1880	Record record = ( Record ) iterator.next();
1808		1881
1809	if (record.getSid() == sid) {	1882	if (record.getSid() == sid) {





/* ====================================================================
 * The Apache Software License, Version 1.1
 *

 */
package org.apache.poi.hssf.usermodel;

import java.util.Vector;
import java.util.List;
import java.util.ListIterator;

import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.FormatRecord;

/**
 * Utility to identify builin formats.  Now can handle user defined data formats also.  The following is a list of the formats as
 * returned by this class.<P>
 *<P>
 *       0, "General"<P>


public class HSSFDataFormat
{
    private static Vector builtinFormats;

    private Vector formats = new Vector();
    private Workbook workbook;
    private boolean movedBuiltins = false;  // Flag to see if need to
					    // check the built in list
					    // or if the regular list
					    // has all entries.

    /** 
     * Construncts a new data formatter.  It takes a workbook to have
     * access to the workbooks format records.
     * @param workbook the workbook the formats are tied to.
     */
    public HSSFDataFormat(Workbook workbook) {
	this.workbook = workbook;
	if (builtinFormats == null) populateBuiltinFormats();
    }

    private static synchronized void populateBuiltinFormats()
    {
        builtinFormats = new Vector();
        builtinFormats.add(0, "General");
        builtinFormats.add(1, "0");
        builtinFormats.add(2, "0.00");
        builtinFormats.add(3, "#,##0");
        builtinFormats.add(4, "#,##0.00");
        builtinFormats.add(5, "($#,##0_);($#,##0)");
        builtinFormats.add(6, "($#,##0_);[Red]($#,##0)");
        builtinFormats.add(7, "($#,##0.00);($#,##0.00)");
        builtinFormats.add(8, "($#,##0.00_);[Red]($#,##0.00)");
        builtinFormats.add(9, "0%");
        builtinFormats.add(0xa, "0.00%");
        builtinFormats.add(0xb, "0.00E+00");
        builtinFormats.add(0xc, "# ?/?");
        builtinFormats.add(0xd, "# ??/??");
        builtinFormats.add(0xe, "m/d/yy");
        builtinFormats.add(0xf, "d-mmm-yy");
        builtinFormats.add(0x10, "d-mmm");
        builtinFormats.add(0x11, "mmm-yy");
        builtinFormats.add(0x12, "h:mm AM/PM");
        builtinFormats.add(0x13, "h:mm:ss AM/PM");
        builtinFormats.add(0x14, "h:mm");
        builtinFormats.add(0x15, "h:mm:ss");
        builtinFormats.add(0x16, "m/d/yy h:mm");

        // 0x17 - 0x24 reserved for international and undocumented
        builtinFormats.add(0x17, "0x17");
        builtinFormats.add(0x18, "0x18");
        builtinFormats.add(0x19, "0x19");
        builtinFormats.add(0x1a, "0x1a");
        builtinFormats.add(0x1b, "0x1b");
        builtinFormats.add(0x1c, "0x1c");
        builtinFormats.add(0x1d, "0x1d");
        builtinFormats.add(0x1e, "0x1e");
        builtinFormats.add(0x1f, "0x1f");
        builtinFormats.add(0x20, "0x20");
        builtinFormats.add(0x21, "0x21");
        builtinFormats.add(0x22, "0x22");
        builtinFormats.add(0x23, "0x23");
        builtinFormats.add(0x24, "0x24");

        // 0x17 - 0x24 reserved for international and undocumented
        builtinFormats.add(0x25, "(#,##0_);(#,##0)");
        builtinFormats.add(0x26, "(#,##0_);[Red](#,##0)");
        builtinFormats.add(0x27, "(#,##0.00_);(#,##0.00)");
        builtinFormats.add(0x28, "(#,##0.00_);[Red](#,##0.00)");
        builtinFormats.add(0x29, "_(*#,##0_);_(*(#,##0);_(* \"-\"_);_(@_)");
        builtinFormats.add(0x2a, "_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)");
        builtinFormats.add(0x2b, "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)");
        builtinFormats.add(0x2c,
                    "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)");
        builtinFormats.add(0x2d, "mm:ss");
        builtinFormats.add(0x2e, "[h]:mm:ss");
        builtinFormats.add(0x2f, "mm:ss.0");
        builtinFormats.add(0x30, "##0.0E+0");
        builtinFormats.add(0x31, "@");
    }

    public static List getBuiltinFormats()
    {
        if (builtinFormats == null)
        {
            populateBuiltinFormats();
        }
        return builtinFormats;
    }

    /**

     * @return index of format or -1 if undefined.
     */

    public static short getBuiltinFormat(String format)
    {
        if (builtinFormats == null)
        {
            populateBuiltinFormats();
        }
        short retval = -1;

        for (short k = 0; k < 0x31; k++)
        {
            String nformat = ( String ) builtinFormats.get(k);

            if ((nformat != null) && nformat.equals(format))
            {

    }

    /**
     * get the format index that matches the given format string.
     * Creates a new format if one is not found.
     * @param format string matching a built in format
     * @return index of format.
     */

    public short getFormat(String format)
    {
	ListIterator i;
	int ind;
	if (!movedBuiltins) {
	    i = builtinFormats.listIterator();
	    while (i.hasNext()) {
		ind = i.nextIndex();
		formats.add(ind, i.next());
	    }
	    movedBuiltins = true;
	}
	i = formats.listIterator();
	while (i.hasNext()) {
	    ind = i.nextIndex();
	    if (format.equals(i.next())) 
		return (short)ind;
	}
	
	ind = workbook.getFormat(format, true);
	if (formats.size() <= ind) 
	    formats.setSize(ind + 1);
	formats.add(ind, format);

        return (short)ind;
    }

    /**
     * get the format string that matches the given format index
     * @param index of a format
     * @return string represented at index of format or null if there is not a  format at that index
     */

    public String getFormat(short index)
    {
	if (movedBuiltins)
	    return ( String ) formats.get(index);
	else
	    return (String) (builtinFormats.get(index) != null ? builtinFormats.get(index) : formats.get(index));
    }

    /**
     * get the format string that matches the given format index
     * @param index of a built in format
     * @return string represented at index of format or null if there is not a builtin format at that index
     */

    public static String getBuiltinFormat(short index)
    {
        if (builtinFormats == null)
        {
            populateBuiltinFormats();
        }
        return ( String ) builtinFormats.get(index);
    }

    /**
     * get the number of builtin and reserved builtinFormats
     * @return number of builtin and reserved builtinFormats
     */

    public static int getNumberOfBuiltinBuiltinFormats()
    {
        if (builtinFormats == null)
        {
            populateBuiltinFormats();
        }
        return builtinFormats.size();
    }
}




 * @author  Andrew C. Oliver (acoliver at apache dot org)
 * @author  Glen Stampoultzis (glens at apache.org)
 * @author  Sergei Kozello (sergeikozello at mail.ru)
 * @author  Shawn Laubach (shawnlaubach at cox.net)
 * @version 2.0-pre
 */


        names.remove(index);
        workbook.removeName(index);        
    }

        /**
     * Creates an instance of HSSFDataFormat.
     * @return the HSSFDataFormat object
     * @see org.apache.poi.hssf.record.FormatRecord
     * @see org.apache.poi.hssf.record.Record
     */
    public HSSFDataFormat createDataFormat() {
	return new HSSFDataFormat(workbook);
    }
	
    /** remove the named range by his name
     * @param name named range name
     */    




    }

    /**
     * TEST NAME:  Test Read Simple w/ Data Format<P>
     * OBJECTIVE:  Test that HSSF can read a simple spreadsheet (SimpleWithDataFormat.xls).<P>
     * SUCCESS:    HSSF reads the sheet.  Matches values in their particular positions and format is correct<P>
     * FAILURE:    HSSF does not read a sheet or excepts.  HSSF cannot identify values
     *             in the sheet in their known positions.<P>
     *
     */

    public void testReadSimpleWithDataFormat()
        throws IOException
    {
        String filename = System.getProperty("HSSF.testdata.path");

        filename = filename + "/SimpleWithDataFormat.xls";
        FileInputStream stream   = new FileInputStream(filename);
        POIFSFileSystem fs       = new POIFSFileSystem(stream);
        HSSFWorkbook    workbook = new HSSFWorkbook(fs);
        HSSFSheet       sheet    = workbook.getSheetAt(0);

        assertEquals(1.25,
                     sheet.getRow(( short ) 0).getCell(( short ) 0)
                         .getNumericCellValue(), 1e-10);
	assertEquals(workbook.createFormat("0.0"), 0xa4);
        stream.close();
    }

    /**
     * TEST NAME:  Test Read Employee Simple <P>
     * OBJECTIVE:  Test that HSSF can read a simple spreadsheet (Employee.xls).<P>
     * SUCCESS:    HSSF reads the sheet.  Matches values in their particular positions.<P>

Return to bug 11068

Lines 29-34 Link Here

(-)src/documentation/xdocs/hssf/quick-guide.xml (+33 lines)
29	<li><link href="#WorkingWithFonts">Working with fonts</link></li>	29	<li><link href="#WorkingWithFonts">Working with fonts</link></li>
30	<li><link href="#ReadWriteWorkbook">Reading and writing</link></li>	30	<li><link href="#ReadWriteWorkbook">Reading and writing</link></li>
31	<li><link href="#NewLinesInCells">Use newlines in cells.</link></li>	31	<li><link href="#NewLinesInCells">Use newlines in cells.</link></li>
		32	<li><link href="#DataFormats">Create user defined data formats.</link></li>
32	</ul>	33	</ul>
33	</section>	34	</section>
34	<section title="Features">	35	<section title="Features">
Lines 324-329 Link Here
324	wb.write( fileOut );	325	wb.write( fileOut );
325	fileOut.close();</source>	326	fileOut.close();</source>
326	</section>	327	</section>
		328	<anchor id="DataFormats"/>
		329	<section title="Data Formats">
		330	<source>
		331	HSSFWorkbook wb = new HSSFWorkbook();
		332	HSSFSheet sheet = wb.createSheet("format sheet");
		333	HSSFCellStyle style;
		334	HSSFDataFormat format = wb.createDataFormat();
		335	HSSFRow row;
		336	HSSFCell cell;
		337	short rowNum = 0;
		338	short colNum = 0;
		339
		340	row = sheet.createRow(rowNum++);
		341	cell = row.createCell(colNum);
		342	cell.setCellValue(11111.25);
		343	style = wb.createCellStyle();
		344	style.setDataFormat(format.getFormat("0.0"));
		345	cell.setCellStyle(style);
		346
		347	row = sheet.createRow(rowNum++);
		348	cell = row.createCell(colNum);
		349	cell.setCellValue(11111.25);
		350	style = wb.createCellStyle();
		351	style.setDataFormat(format.getFormat("#,##0.0000"));
		352	cell.setCellStyle(style);
		353
		354	FileOutputStream fileOut = new FileOutputStream("workbook.xls");
		355	wb.write(fileOut);
		356	fileOut.close();
		357	</source>
		358	</section>
		359
327	</section>	360	</section>
328	</section>	361	</section>
329	</body>	362	</body>

Lines 1-4 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFDataFormat.java (-74 / +143 lines)
1
2	/* ====================================================================	1	/* ====================================================================
3	* The Apache Software License, Version 1.1	2	* The Apache Software License, Version 1.1
4	*	3	*
Lines 60-70 Link Here
60	*/	59	*/
61	package org.apache.poi.hssf.usermodel;	60	package org.apache.poi.hssf.usermodel;
62		61
63	import java.util.ArrayList;	62	import java.util.Vector;
64	import java.util.List;	63	import java.util.List;
		64	import java.util.ListIterator;
		65
		66	import org.apache.poi.hssf.model.Workbook;
		67	import org.apache.poi.hssf.record.Record;
		68	import org.apache.poi.hssf.record.FormatRecord;
65		69
66	/**	70	/**
67	* Utility to identify builin formats. The following is a list of the formats as	71	* Utility to identify builin formats. Now can handle user defined data formats also. The following is a list of the formats as
68	* returned by this class.<P>	72	* returned by this class.<P>
69	*<P>	73	*<P>
70	* 0, "General"<P>	74	* 0, "General"<P>
Lines 112-186 Link Here
112		116
113	public class HSSFDataFormat	117	public class HSSFDataFormat
114	{	118	{
115	private static ArrayList formats;	119	private static Vector builtinFormats;
		120
		121	private Vector formats = new Vector();
		122	private Workbook workbook;
		123	private boolean movedBuiltins = false; // Flag to see if need to
		124	// check the built in list
		125	// or if the regular list
		126	// has all entries.
		127
		128	/**
		129	* Construncts a new data formatter. It takes a workbook to have
		130	* access to the workbooks format records.
		131	* @param workbook the workbook the formats are tied to.
		132	*/
		133	public HSSFDataFormat(Workbook workbook) {
		134	this.workbook = workbook;
		135	if (builtinFormats == null) populateBuiltinFormats();
		136	}
116		137
117	private static synchronized void populateFormats()	138	private static synchronized void populateBuiltinFormats()
118	{	139	{
119	formats = new ArrayList();	140	builtinFormats = new Vector();
120	formats.add(0, "General");	141	builtinFormats.add(0, "General");
121	formats.add(1, "0");	142	builtinFormats.add(1, "0");
122	formats.add(2, "0.00");	143	builtinFormats.add(2, "0.00");
123	formats.add(3, "#,##0");	144	builtinFormats.add(3, "#,##0");
124	formats.add(4, "#,##0.00");	145	builtinFormats.add(4, "#,##0.00");
125	formats.add(5, "($#,##0_);($#,##0)");	146	builtinFormats.add(5, "($#,##0_);($#,##0)");
126	formats.add(6, "($#,##0_);[Red]($#,##0)");	147	builtinFormats.add(6, "($#,##0_);[Red]($#,##0)");
127	formats.add(7, "($#,##0.00);($#,##0.00)");	148	builtinFormats.add(7, "($#,##0.00);($#,##0.00)");
128	formats.add(8, "($#,##0.00_);[Red]($#,##0.00)");	149	builtinFormats.add(8, "($#,##0.00_);[Red]($#,##0.00)");
129	formats.add(9, "0%");	150	builtinFormats.add(9, "0%");
130	formats.add(0xa, "0.00%");	151	builtinFormats.add(0xa, "0.00%");
131	formats.add(0xb, "0.00E+00");	152	builtinFormats.add(0xb, "0.00E+00");
132	formats.add(0xc, "# ?/?");	153	builtinFormats.add(0xc, "# ?/?");
133	formats.add(0xd, "# ??/??");	154	builtinFormats.add(0xd, "# ??/??");
134	formats.add(0xe, "m/d/yy");	155	builtinFormats.add(0xe, "m/d/yy");
135	formats.add(0xf, "d-mmm-yy");	156	builtinFormats.add(0xf, "d-mmm-yy");
136	formats.add(0x10, "d-mmm");	157	builtinFormats.add(0x10, "d-mmm");
137	formats.add(0x11, "mmm-yy");	158	builtinFormats.add(0x11, "mmm-yy");
138	formats.add(0x12, "h:mm AM/PM");	159	builtinFormats.add(0x12, "h:mm AM/PM");
139	formats.add(0x13, "h:mm:ss AM/PM");	160	builtinFormats.add(0x13, "h:mm:ss AM/PM");
140	formats.add(0x14, "h:mm");	161	builtinFormats.add(0x14, "h:mm");
141	formats.add(0x15, "h:mm:ss");	162	builtinFormats.add(0x15, "h:mm:ss");
142	formats.add(0x16, "m/d/yy h:mm");	163	builtinFormats.add(0x16, "m/d/yy h:mm");
143		164
144	// 0x17 - 0x24 reserved for international and undocumented	165	// 0x17 - 0x24 reserved for international and undocumented
145	formats.add(0x17, "0x17");	166	builtinFormats.add(0x17, "0x17");
146	formats.add(0x18, "0x18");	167	builtinFormats.add(0x18, "0x18");
147	formats.add(0x19, "0x19");	168	builtinFormats.add(0x19, "0x19");
148	formats.add(0x1a, "0x1a");	169	builtinFormats.add(0x1a, "0x1a");
149	formats.add(0x1b, "0x1b");	170	builtinFormats.add(0x1b, "0x1b");
150	formats.add(0x1c, "0x1c");	171	builtinFormats.add(0x1c, "0x1c");
151	formats.add(0x1d, "0x1d");	172	builtinFormats.add(0x1d, "0x1d");
152	formats.add(0x1e, "0x1e");	173	builtinFormats.add(0x1e, "0x1e");
153	formats.add(0x1f, "0x1f");	174	builtinFormats.add(0x1f, "0x1f");
154	formats.add(0x20, "0x20");	175	builtinFormats.add(0x20, "0x20");
155	formats.add(0x21, "0x21");	176	builtinFormats.add(0x21, "0x21");
156	formats.add(0x22, "0x22");	177	builtinFormats.add(0x22, "0x22");
157	formats.add(0x23, "0x23");	178	builtinFormats.add(0x23, "0x23");
158	formats.add(0x24, "0x24");	179	builtinFormats.add(0x24, "0x24");
159		180
160	// 0x17 - 0x24 reserved for international and undocumented	181	// 0x17 - 0x24 reserved for international and undocumented
161	formats.add(0x25, "(#,##0_);(#,##0)");	182	builtinFormats.add(0x25, "(#,##0_);(#,##0)");
162	formats.add(0x26, "(#,##0_);[Red](#,##0)");	183	builtinFormats.add(0x26, "(#,##0_);[Red](#,##0)");
163	formats.add(0x27, "(#,##0.00_);(#,##0.00)");	184	builtinFormats.add(0x27, "(#,##0.00_);(#,##0.00)");
164	formats.add(0x28, "(#,##0.00_);[Red](#,##0.00)");	185	builtinFormats.add(0x28, "(#,##0.00_);[Red](#,##0.00)");
165	formats.add(0x29, "_(#,##0_);_((#,##0);_(* \"-\"_);_(@_)");	186	builtinFormats.add(0x29, "_(#,##0_);_((#,##0);_(* \"-\"_);_(@_)");
166	formats.add(0x2a, "_($#,##0_);_($(#,##0);_($* \"-\"_);_(@_)");	187	builtinFormats.add(0x2a, "_($#,##0_);_($(#,##0);_($* \"-\"_);_(@_)");
167	formats.add(0x2b, "_(#,##0.00_);_((#,##0.00);_(*\"-\"??_);_(@_)");	188	builtinFormats.add(0x2b, "_(#,##0.00_);_((#,##0.00);_(*\"-\"??_);_(@_)");
168	formats.add(0x2c,	189	builtinFormats.add(0x2c,
169	"_($#,##0.00_);_($(#,##0.00);_($*\"-\"??_);_(@_)");	190	"_($#,##0.00_);_($(#,##0.00);_($*\"-\"??_);_(@_)");
170	formats.add(0x2d, "mm:ss");	191	builtinFormats.add(0x2d, "mm:ss");
171	formats.add(0x2e, "[h]:mm:ss");	192	builtinFormats.add(0x2e, "[h]:mm:ss");
172	formats.add(0x2f, "mm:ss.0");	193	builtinFormats.add(0x2f, "mm:ss.0");
173	formats.add(0x30, "##0.0E+0");	194	builtinFormats.add(0x30, "##0.0E+0");
174	formats.add(0x31, "@");	195	builtinFormats.add(0x31, "@");
175	}	196	}
176		197
177	public static List getFormats()	198	public static List getBuiltinFormats()
178	{	199	{
179	if (formats == null)	200	if (builtinFormats == null)
180	{	201	{
181	populateFormats();	202	populateBuiltinFormats();
182	}	203	}
183	return formats;	204	return builtinFormats;
184	}	205	}
185		206
186	/**	207	/**
Lines 189-205 Link Here
189	* @return index of format or -1 if undefined.	210	* @return index of format or -1 if undefined.
190	*/	211	*/
191		212
192	public static short getFormat(String format)	213	public static short getBuiltinFormat(String format)
193	{	214	{
194	if (formats == null)	215	if (builtinFormats == null)
195	{	216	{
196	populateFormats();	217	populateBuiltinFormats();
197	}	218	}
198	short retval = -1;	219	short retval = -1;
199		220
200	for (short k = 0; k < 0x31; k++)	221	for (short k = 0; k < 0x31; k++)
201	{	222	{
202	String nformat = ( String ) formats.get(k);	223	String nformat = ( String ) builtinFormats.get(k);
203		224
204	if ((nformat != null) && nformat.equals(format))	225	if ((nformat != null) && nformat.equals(format))
205	{	226	{
Lines 211-241 Link Here
211	}	232	}
212		233
213	/**	234	/**
		235	* get the format index that matches the given format string.
		236	* Creates a new format if one is not found.
		237	* @param format string matching a built in format
		238	* @return index of format.
		239	*/
		240
		241	public short getFormat(String format)
		242	{
		243	ListIterator i;
		244	int ind;
		245	if (!movedBuiltins) {
		246	i = builtinFormats.listIterator();
		247	while (i.hasNext()) {
		248	ind = i.nextIndex();
		249	formats.add(ind, i.next());
		250	}
		251	movedBuiltins = true;
		252	}
		253	i = formats.listIterator();
		254	while (i.hasNext()) {
		255	ind = i.nextIndex();
		256	if (format.equals(i.next()))
		257	return (short)ind;
		258	}
		259
		260	ind = workbook.getFormat(format, true);
		261	if (formats.size() <= ind)
		262	formats.setSize(ind + 1);
		263	formats.add(ind, format);
		264
		265	return (short)ind;
		266	}
		267
		268	/**
		269	* get the format string that matches the given format index
		270	* @param index of a format
		271	* @return string represented at index of format or null if there is not a format at that index
		272	*/
		273
		274	public String getFormat(short index)
		275	{
		276	if (movedBuiltins)
		277	return ( String ) formats.get(index);
		278	else
		279	return (String) (builtinFormats.get(index) != null ? builtinFormats.get(index) : formats.get(index));
		280	}
		281
		282	/**
214	* get the format string that matches the given format index	283	* get the format string that matches the given format index
215	* @param index of a built in format	284	* @param index of a built in format
216	* @return string represented at index of format or null if there is not a builtin format at that index	285	* @return string represented at index of format or null if there is not a builtin format at that index
217	*/	286	*/
218		287
219	public static String getFormat(short index)	288	public static String getBuiltinFormat(short index)
220	{	289	{
221	if (formats == null)	290	if (builtinFormats == null)
222	{	291	{
223	populateFormats();	292	populateBuiltinFormats();
224	}	293	}
225	return ( String ) formats.get(index);	294	return ( String ) builtinFormats.get(index);
226	}	295	}
227		296
228	/**	297	/**
229	* get the number of builtin and reserved formats	298	* get the number of builtin and reserved builtinFormats
230	* @return number of builtin and reserved formats	299	* @return number of builtin and reserved builtinFormats
231	*/	300	*/
232		301
233	public static int getNumberOfBuiltinFormats()	302	public static int getNumberOfBuiltinBuiltinFormats()
234	{	303	{
235	if (formats == null)	304	if (builtinFormats == null)
236	{	305	{
237	populateFormats();	306	populateBuiltinFormats();
238	}	307	}
239	return formats.size();	308	return builtinFormats.size();
240	}	309	}
241	}	310	}

Lines 234-239 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestWorkbook.java (+27 lines)
234	}	234	}
235		235
236	/**	236	/**
		237	* TEST NAME: Test Read Simple w/ Data Format<P>
		238	* OBJECTIVE: Test that HSSF can read a simple spreadsheet (SimpleWithDataFormat.xls).<P>
		239	* SUCCESS: HSSF reads the sheet. Matches values in their particular positions and format is correct<P>
		240	* FAILURE: HSSF does not read a sheet or excepts. HSSF cannot identify values
		241	* in the sheet in their known positions.<P>
		242	*
		243	*/
		244
		245	public void testReadSimpleWithDataFormat()
		246	throws IOException
		247	{
		248	String filename = System.getProperty("HSSF.testdata.path");
		249
		250	filename = filename + "/SimpleWithDataFormat.xls";
		251	FileInputStream stream = new FileInputStream(filename);
		252	POIFSFileSystem fs = new POIFSFileSystem(stream);
		253	HSSFWorkbook workbook = new HSSFWorkbook(fs);
		254	HSSFSheet sheet = workbook.getSheetAt(0);
		255
		256	assertEquals(1.25,
		257	sheet.getRow(( short ) 0).getCell(( short ) 0)
		258	.getNumericCellValue(), 1e-10);
		259	assertEquals(workbook.createFormat("0.0"), 0xa4);
		260	stream.close();
		261	}
		262
		263	/**
237	* TEST NAME: Test Read Employee Simple <P>	264	* TEST NAME: Test Read Employee Simple <P>
238	* OBJECTIVE: Test that HSSF can read a simple spreadsheet (Employee.xls).<P>	265	* OBJECTIVE: Test that HSSF can read a simple spreadsheet (Employee.xls).<P>
239	* SUCCESS: HSSF reads the sheet. Matches values in their particular positions.<P>	266	* SUCCESS: HSSF reads the sheet. Matches values in their particular positions.<P>