ASF Bugzilla – Attachment 2493 Details for
Bug 11068
[PATCH] - Ability to have user defined data formats
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
Remember
[x]
|
Forgot Password
Login:
[x]
[patch]
New Patch
numberformat.txt (text/plain), 22.07 KB, created by
Shawn Laubach
on 2002-07-26 02:05:04 UTC
(
hide
)
Description:
New Patch
Filename:
MIME Type:
Creator:
Shawn Laubach
Created:
2002-07-26 02:05:04 UTC
Size:
22.07 KB
patch
obsolete
>Index: src/documentation/xdocs/who.xml >=================================================================== >RCS file: /home/cvspublic/jakarta-poi/src/documentation/xdocs/who.xml,v >retrieving revision 1.14 >diff -u -r1.14 who.xml >--- src/documentation/xdocs/who.xml 2 May 2002 12:57:40 -0000 1.14 >+++ src/documentation/xdocs/who.xml 26 Jul 2002 01:58:41 -0000 >@@ -50,7 +50,9 @@ > </ul> > </section> > <section title="Developers"> >- >+ <ul> >+ <li>Shawn Laubach (shawnlaubach at cox dot net)</li> >+ </ul> > </section> > </section> > >Index: src/documentation/xdocs/hssf/quick-guide.xml >=================================================================== >RCS file: /home/cvspublic/jakarta-poi/src/documentation/xdocs/hssf/quick-guide.xml,v >retrieving revision 1.6 >diff -u -r1.6 quick-guide.xml >--- src/documentation/xdocs/hssf/quick-guide.xml 9 Jun 2002 12:48:34 -0000 1.6 >+++ src/documentation/xdocs/hssf/quick-guide.xml 26 Jul 2002 01:58:44 -0000 >@@ -29,6 +29,7 @@ > <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"> >@@ -324,6 +325,38 @@ > 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> >Index: src/java/org/apache/poi/hssf/model/Workbook.java >=================================================================== >RCS file: /home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/model/Workbook.java,v >retrieving revision 1.13 >diff -u -r1.13 Workbook.java >--- src/java/org/apache/poi/hssf/model/Workbook.java 21 Jul 2002 03:03:57 -0000 1.13 >+++ src/java/org/apache/poi/hssf/model/Workbook.java 26 Jul 2002 01:58:48 -0000 >@@ -84,6 +84,7 @@ > * Kit (Microsoft Press) and the documentation at http://sc.openoffice.org/excelfileformat.pdf > * before even attempting to use this. > * >+ * @author Shawn Laubach (shawnlaubach at cox.net) (Data Formats) > * @author Andrew C. Oliver (acoliver at apache dot org) > * @author Glen Stampoultzis (glens at apache.org) > * @author Sergei Kozello (sergeikozello at mail.ru) >@@ -128,6 +129,8 @@ > */ > > >+ >+ protected ArrayList formats = new ArrayList(); > protected ArrayList boundsheets = new ArrayList(); > > protected ArrayList names = new ArrayList(); >@@ -150,6 +153,8 @@ > 0; // holds the position of last name record > private int supbookpos = > 0; // holds the position of sup book >+ private short maxformatid = >+ -1; // holds the max format id > > private static POILogger log = > POILogFactory.getLogger(Workbook.class); >@@ -238,7 +243,11 @@ > log.log(DEBUG, "found SupBook record at " + k); > retval.supbookpos = k; > break; >- >+ case FormatRecord.sid : >+ log.log(DEBUG, "found format record at " + k); >+ retval.formats.add(rec); >+ retval.maxformatid = retval.maxformatid >= ((FormatRecord)rec).getIndexCode() ? retval.maxformatid : ((FormatRecord)rec).getIndexCode(); >+ break; > default : > } > records.add(rec); >@@ -263,6 +272,9 @@ > log.log(DEBUG, "creating new workbook from scratch"); > Workbook retval = new Workbook(); > ArrayList records = new ArrayList(30); >+ ArrayList formats = new ArrayList(8); >+ Record rec; >+ int i; > > records.add(retval.createBOF()); > records.add(retval.createInterfaceHdr()); >@@ -293,14 +305,13 @@ > records.add(retval.createFont()); > retval.fontpos = records.size() - 1; // last font record postion > retval.numfonts = 4; >- records.add(retval.createFormat(0)); >- records.add(retval.createFormat(1)); >- records.add(retval.createFormat(2)); >- records.add(retval.createFormat(3)); >- records.add(retval.createFormat(4)); >- records.add(retval.createFormat(5)); >- records.add(retval.createFormat(6)); >- records.add(retval.createFormat(7)); >+ for (i = 0; i <= 7; i++) { >+ rec = retval.createFormat(i); >+ retval.maxformatid = retval.maxformatid >= ((FormatRecord)rec).getIndexCode() ? retval.maxformatid : ((FormatRecord)rec).getIndexCode(); >+ formats.add(rec); >+ records.add(rec); >+ } >+ retval.formats = formats; > for (int k = 0; k < 21; k++) { > records.add(retval.createExtendedFormat(k)); > retval.numxfs++; >@@ -1790,11 +1801,57 @@ > SupBookRecord supbook = new SupBookRecord(); > > supbook.setNumberOfSheets((short)getNumSheets()); >+ > //supbook.setFlag(); > > records.add(supbookpos + 1 , supbook); >- >- return rec; >+ >+ return rec; >+ } >+ >+ /** >+ * Returns a format index that matches the passed in format. It does not tie into HSSFDataFormat. >+ * @param format the format string >+ * @param createIfNotFound creates a new format if format not found >+ * @return the format id of a format that matches or -1 if none found and createIfNotFound >+ */ >+ public short getFormat(String format, boolean createIfNotFound) { >+ Iterator iterator; >+ for (iterator = formats.iterator(); iterator.hasNext();) { >+ FormatRecord r = (FormatRecord)iterator.next(); >+ if (r.getFormatString().equals(format)) { >+ return r.getIndexCode(); >+ } >+ } >+ >+ if (createIfNotFound) { >+ return createFormat(format); >+ } >+ >+ return -1; >+ } >+ >+ /** >+ * Creates a FormatRecord, inserts it, and returns the index code. >+ * @param format the format string >+ * @return the index code of the format record. >+ * @see org.apache.poi.hssf.record.FormatRecord >+ * @see org.apache.poi.hssf.record.Record >+ */ >+ public short createFormat(String format) { >+ FormatRecord rec = new FormatRecord(); >+ maxformatid = maxformatid >= (short)0xa4 ? (short)(maxformatid + 1) : (short)0xa4; //Starting value from M$ empiracle study. >+ rec.setIndexCode(maxformatid); >+ rec.setFormatStringLength((byte)format.length()); >+ rec.setFormatString(format); >+ >+ int pos = 0; >+ while (pos < records.size() && ((Record)records.get(pos)).getSid() != FormatRecord.sid) >+ pos++; >+ pos += formats.size(); >+ formats.add(rec); >+ records.add(pos, rec); >+ return maxformatid; > } > > >@@ -1804,6 +1861,22 @@ > > public Record findFirstRecordBySid(short sid) { > for (Iterator iterator = records.iterator(); iterator.hasNext(); ) { >+ Record record = ( Record ) iterator.next(); >+ >+ if (record.getSid() == sid) { >+ return record; >+ } >+ } >+ return null; >+ } >+ >+ /** >+ * Returns the next occurance of a record matching a particular sid. >+ */ >+ public Record findNextRecordBySid(short sid, int pos) { >+ Iterator iterator = records.iterator(); >+ for (;pos > 0 && iterator.hasNext(); iterator.next(),pos--); >+ while (iterator.hasNext()) { > Record record = ( Record ) iterator.next(); > > if (record.getSid() == sid) { >Index: src/java/org/apache/poi/hssf/usermodel/HSSFDataFormat.java >=================================================================== >RCS file: /home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/usermodel/HSSFDataFormat.java,v >retrieving revision 1.1.1.1 >diff -u -r1.1.1.1 HSSFDataFormat.java >--- src/java/org/apache/poi/hssf/usermodel/HSSFDataFormat.java 31 Jan 2002 02:23:44 -0000 1.1.1.1 >+++ src/java/org/apache/poi/hssf/usermodel/HSSFDataFormat.java 26 Jul 2002 01:58:49 -0000 >@@ -1,4 +1,3 @@ >- > /* ==================================================================== > * The Apache Software License, Version 1.1 > * >@@ -60,11 +59,16 @@ > */ > package org.apache.poi.hssf.usermodel; > >-import java.util.ArrayList; >+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. The following is a list of the formats as >+ * 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> >@@ -112,75 +116,92 @@ > > public class HSSFDataFormat > { >- private static ArrayList formats; >+ 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 populateFormats() >+ private static synchronized void populateBuiltinFormats() > { >- formats = new ArrayList(); >- formats.add(0, "General"); >- formats.add(1, "0"); >- formats.add(2, "0.00"); >- formats.add(3, "#,##0"); >- formats.add(4, "#,##0.00"); >- formats.add(5, "($#,##0_);($#,##0)"); >- formats.add(6, "($#,##0_);[Red]($#,##0)"); >- formats.add(7, "($#,##0.00);($#,##0.00)"); >- formats.add(8, "($#,##0.00_);[Red]($#,##0.00)"); >- formats.add(9, "0%"); >- formats.add(0xa, "0.00%"); >- formats.add(0xb, "0.00E+00"); >- formats.add(0xc, "# ?/?"); >- formats.add(0xd, "# ??/??"); >- formats.add(0xe, "m/d/yy"); >- formats.add(0xf, "d-mmm-yy"); >- formats.add(0x10, "d-mmm"); >- formats.add(0x11, "mmm-yy"); >- formats.add(0x12, "h:mm AM/PM"); >- formats.add(0x13, "h:mm:ss AM/PM"); >- formats.add(0x14, "h:mm"); >- formats.add(0x15, "h:mm:ss"); >- formats.add(0x16, "m/d/yy h:mm"); >+ 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 >- formats.add(0x17, "0x17"); >- formats.add(0x18, "0x18"); >- formats.add(0x19, "0x19"); >- formats.add(0x1a, "0x1a"); >- formats.add(0x1b, "0x1b"); >- formats.add(0x1c, "0x1c"); >- formats.add(0x1d, "0x1d"); >- formats.add(0x1e, "0x1e"); >- formats.add(0x1f, "0x1f"); >- formats.add(0x20, "0x20"); >- formats.add(0x21, "0x21"); >- formats.add(0x22, "0x22"); >- formats.add(0x23, "0x23"); >- formats.add(0x24, "0x24"); >+ 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 >- formats.add(0x25, "(#,##0_);(#,##0)"); >- formats.add(0x26, "(#,##0_);[Red](#,##0)"); >- formats.add(0x27, "(#,##0.00_);(#,##0.00)"); >- formats.add(0x28, "(#,##0.00_);[Red](#,##0.00)"); >- formats.add(0x29, "_(*#,##0_);_(*(#,##0);_(* \"-\"_);_(@_)"); >- formats.add(0x2a, "_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)"); >- formats.add(0x2b, "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)"); >- formats.add(0x2c, >+ 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);_($*\"-\"??_);_(@_)"); >- formats.add(0x2d, "mm:ss"); >- formats.add(0x2e, "[h]:mm:ss"); >- formats.add(0x2f, "mm:ss.0"); >- formats.add(0x30, "##0.0E+0"); >- formats.add(0x31, "@"); >+ 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 getFormats() >+ public static List getBuiltinFormats() > { >- if (formats == null) >+ if (builtinFormats == null) > { >- populateFormats(); >+ populateBuiltinFormats(); > } >- return formats; >+ return builtinFormats; > } > > /** >@@ -189,17 +210,17 @@ > * @return index of format or -1 if undefined. > */ > >- public static short getFormat(String format) >+ public static short getBuiltinFormat(String format) > { >- if (formats == null) >+ if (builtinFormats == null) > { >- populateFormats(); >+ populateBuiltinFormats(); > } > short retval = -1; > > for (short k = 0; k < 0x31; k++) > { >- String nformat = ( String ) formats.get(k); >+ String nformat = ( String ) builtinFormats.get(k); > > if ((nformat != null) && nformat.equals(format)) > { >@@ -211,31 +232,79 @@ > } > > /** >+ * 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 getFormat(short index) >+ public static String getBuiltinFormat(short index) > { >- if (formats == null) >+ if (builtinFormats == null) > { >- populateFormats(); >+ populateBuiltinFormats(); > } >- return ( String ) formats.get(index); >+ return ( String ) builtinFormats.get(index); > } > > /** >- * get the number of builtin and reserved formats >- * @return number of builtin and reserved formats >+ * get the number of builtin and reserved builtinFormats >+ * @return number of builtin and reserved builtinFormats > */ > >- public static int getNumberOfBuiltinFormats() >+ public static int getNumberOfBuiltinBuiltinFormats() > { >- if (formats == null) >+ if (builtinFormats == null) > { >- populateFormats(); >+ populateBuiltinFormats(); > } >- return formats.size(); >+ return builtinFormats.size(); > } > } >Index: src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java >=================================================================== >RCS file: /home/cvspublic/jakarta-poi/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java,v >retrieving revision 1.8 >diff -u -r1.8 HSSFWorkbook.java >--- src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java 21 Jul 2002 03:03:57 -0000 1.8 >+++ src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java 26 Jul 2002 01:58:52 -0000 >@@ -84,6 +84,7 @@ > * @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 > */ > >@@ -637,7 +638,17 @@ > 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 > */ >Index: src/testcases/org/apache/poi/hssf/usermodel/TestWorkbook.java >=================================================================== >RCS file: /home/cvspublic/jakarta-poi/src/testcases/org/apache/poi/hssf/usermodel/TestWorkbook.java,v >retrieving revision 1.3 >diff -u -r1.3 TestWorkbook.java >--- src/testcases/org/apache/poi/hssf/usermodel/TestWorkbook.java 10 May 2002 03:01:11 -0000 1.3 >+++ src/testcases/org/apache/poi/hssf/usermodel/TestWorkbook.java 26 Jul 2002 01:58:59 -0000 >@@ -234,6 +234,33 @@ > } > > /** >+ * 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>
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
Attachments on
bug 11068
:
2440
|
2441
| 2493 |
2494
|
2507
|
2508