From 701291c9796f4cac261b109db515ddd4e3f9ee2c Mon Sep 17 00:00:00 2001 From: Shawn Laubach Date: Wed, 9 Oct 2002 12:41:58 +0000 Subject: [PATCH] Corrected bug in HSSFDataFormat and added alias for text format. Updated documentation dealing with data format. PR: Obtained from: Submitted by: Reviewed by: git-svn-id: https://svn.apache.org/repos/asf/jakarta/poi/trunk@352887 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/xdocs/faq.xml | 21 +++++---- src/documentation/xdocs/hssf/how-to.xml | 10 +++-- .../poi/hssf/usermodel/HSSFDataFormat.java | 45 +++++++++++-------- 3 files changed, 46 insertions(+), 30 deletions(-) diff --git a/src/documentation/xdocs/faq.xml b/src/documentation/xdocs/faq.xml index bc5b39a930..46587108bf 100644 --- a/src/documentation/xdocs/faq.xml +++ b/src/documentation/xdocs/faq.xml @@ -134,18 +134,21 @@ - I want to set a cell format (Data format of a cell) of a excel sheet as###,###,###.#### or ###,###,###.0000. Is it possible using POI ? + I want to set a cell format (Data format of a cell) of a excel sheet as ###,###,###.#### or ###,###,###.0000. Is it possible using POI ?

- HSSF does not yet support custom data formats, however, it should be a - reasonably easy facillity to add and we'll gladly accept contributions - in this area. - - These are the built in formats that it does support: - - - http://jakarta.apache.org/poi/javadocs/javasrc/org/apache/poi/hssf/usermodel/HSSFDataFormat_java.html#HSSFDataFormat + Yes. You first need to get a HSSFDataFormat object from the workbook and call getFormat with the desired format. Some examples are here. +

+ + + + + I want to set a cell format (Data format of a cell) of a excel sheet as text. Is it possible using POI ? + + +

+ Yes. This is a built-in format for excel that you can get from HSSFDataFormat object using the format string "@". Also, the string "text" will alias this format.

diff --git a/src/documentation/xdocs/hssf/how-to.xml b/src/documentation/xdocs/hssf/how-to.xml index 39aa03ca22..e9db5b9798 100644 --- a/src/documentation/xdocs/hssf/how-to.xml +++ b/src/documentation/xdocs/hssf/how-to.xml @@ -21,7 +21,8 @@ This release allows numeric and string cell values to be written to or read from an XLS file as well as reading and writing dates. Also in this release is row and column sizing, cell styling (bold, - italics, borders,etc), and support for built-in data formats. New + italics, borders,etc), and support for both built-in and user + defined data formats. New to this release is an event-based API for reading XLS files. It differs greatly from the read/write API and is intended for intermediate developers who need a smaller @@ -96,6 +97,7 @@ HSSFCell c = null; HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); +HSSFDataFormat df = wb.createDataFormat(); // create 2 fonts objects HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); @@ -119,13 +121,15 @@ f2.setStrikeout( true ); //set cell stlye cs.setFont(f); -//set the cell format see HSSFDataFromat for a full list -cs.setDataFormat(HSSFDataFormat.getFormat("($#,##0_);[Red]($#,##0)")); +//set the cell format +cs.setDataFormat(df.getFormat("#,##0.0")); //set a thin border cs2.setBorderBottom(cs2.BORDER_THIN); //fill w fg fill color cs2.setFillPattern((short) HSSFCellStyle.SOLID_FOREGROUND); +//set the cell format to text see HSSFDataFormat for a full list +cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); // set the font cs2.setFont(f2); diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFDataFormat.java b/src/java/org/apache/poi/hssf/usermodel/HSSFDataFormat.java index b9d3f737c0..5a88535c4f 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFDataFormat.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFDataFormat.java @@ -95,23 +95,24 @@ import java.util.Vector; * 0x15, "h:mm:ss"
* 0x16, "m/d/yy h:mm"
*

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

+ * 0x26, "(#,##0_);[Red](#,##0)"

+ * 0x27, "(#,##0.00_);(#,##0.00)"

+ * 0x28, "(#,##0.00_);[Red](#,##0.00)"

+ * 0x29, "_(*#,##0_);_(*(#,##0);_(* \"-\"_);_(@_)"

+ * 0x2a, "_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)"

+ * 0x2b, "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)"

+ * 0x2c, "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)"

+ * 0x2d, "mm:ss"

+ * 0x2e, "[h]:mm:ss"

+ * 0x2f, "mm:ss.0"

+ * 0x30, "##0.0E+0"

+ * 0x31, "@" - This is text format.

+ * 0x31 "text" - Alias for "@"

* * @author Andrew C. Oliver (acoliver at apache dot org) + * @author Shawn M. Laubach (slaubach at apache dot org) */ public class HSSFDataFormat @@ -217,20 +218,24 @@ public class HSSFDataFormat } /** - * get the format index that matches the given format string + * get the format index that matches the given format string

+ * Automatically converts "text" to excel's format string to represent text. * @param format string matching a built in format * @return index of format or -1 if undefined. */ public static short getBuiltinFormat( String format ) { + if (format.toUpperCase().equals("TEXT")) + format = "@"; + if ( builtinFormats == null ) { populateBuiltinFormats(); } short retval = -1; - for ( short k = 0; k < 0x31; k++ ) + for (short k = 0; k <= 0x31; k++) { String nformat = (String) builtinFormats.get( k ); @@ -245,7 +250,7 @@ public class HSSFDataFormat /** * get the format index that matches the given format string. - * Creates a new format if one is not found. + * Creates a new format if one is not found. Aliases text to the proper format. * @param format string matching a built in format * @return index of format. */ @@ -254,6 +259,10 @@ public class HSSFDataFormat { ListIterator i; int ind; + + if (format.toUpperCase().equals("TEXT")) + format = "@"; + if ( !movedBuiltins ) { i = builtinFormats.listIterator(); -- 2.39.5