Merged revisions 638786-638802,638805-638811,638813-638814,638816-639230,639233-639241,639243-639253,639255-639486,639488-639601,639603-639835,639837-639917,639919-640056,640058-640710,640712-641156,641158-641184,641186-641795,641797-641798,641800-641933,641935-641963,641965-641966,641968-641995,641997-642230,642232-642562,642564-642565,642568-642570,642572-642573,642576-642736,642739-642877,642879,642881-642890,642892-642903,642905-642945,642947-643624,643626-643653,643655-643669,643671,643673-643830,643832-643833,643835-644342,644344-644472,644474-644508,644510-645347,645349-645351,645353-645559,645561-645565,645568-645951,645953-646193,646195-646311,646313-646404,646406-646665,646667-646853,646855-646869,646871-647151,647153-647185,647187-647277,647279-647566,647568-647573,647575,647578-647711,647714-647737,647739-647823,647825-648155,648157-648202,648204-648273,648275,648277-648302,648304-648333,648335-648588,648590-648622,648625-648673,648675-649141,649144,649146-649556,649558-649795,649799,649801-649910,649912-649913,649915-650128,650131-650132,650134-650137,650140-650914,650916-651991,651993-652284,652286-652287,652289,652291,652293-652297,652299-652328,652330-652425,652427-652445,652447-652560,652562-652933,652935,652937-652993,652995-653116,653118-653124,653126-653483,653487-653519,653522-653550,653552-653607,653609-653667,653669-653674,653676-653814,653817-653830,653832-653891,653893-653944,653946-654055,654057-654355,654357-654365,654367-654648,654651-655215,655217-655277,655279-655281,655283-655911,655913-656212,656214,656216-656251,656253-656698,656700-656756,656758-656892,656894-657135,657137-657165,657168-657179,657181-657354,657356-657357,657359-657701,657703-657874,657876-658032,658034-658284,658286,658288-658301,658303-658307,658309-658321,658323-658335,658337-658348,658351,658353-658832,658834-658983,658985,658987-659066,659068-659402,659404-659428,659430-659451,659453-659454,659456-659461,659463-659477,659479-659524,659526-659571,659574,659576-660255,660257-660262,660264-660279,660281-660343,660345-660473,660475-660827,660829-660833,660835-660888,660890-663321,663323-663435,663437-663764,663766-663854,663856-664219,664221-664489,664494-664514,664516-668013,668015-668142,668144-668152,668154,668156-668256,668258,668260-669139,669141-669455,669457-669657,669659-669808,669810-670189,670191-671321,671323-672229,672231-672549,672551-672552,672554-672561,672563-672566,672568,672571-673049,673051-673852,673854-673862,673864-673986,673988-673996,673998-674347,674349-674890,674892-674910,674912-674936,674938-674952,674954-675078,675080-675085,675087-675217,675219-675660,675662-675670,675672-675716,675718-675726,675728-675733,675735-675775,675777-675782,675784,675786-675791,675794-675852,675854-676200,676202,676204,676206-676220,676222-676309,676311-676456,676458-676994,676996-677027,677030-677040,677042-677056,677058-678287 via svnmerge from

https://svn.apache.org:443/repos/asf/poi/trunk

........
  r677376 | josh | 2008-07-16 19:47:13 +0100 (Wed, 16 Jul 2008) | 1 line
  
  Patch 45410 - removed dependency on commons beanutils, collections and lang
........
  r677969 | nick | 2008-07-18 18:02:29 +0100 (Fri, 18 Jul 2008) | 1 line
  
  Patch from Jukka from bug #45394 - tidy up examples bit of build
........
  r677972 | nick | 2008-07-18 18:11:51 +0100 (Fri, 18 Jul 2008) | 1 line
  
  Update homepage for 3.5.1 beta 1
........
  r677974 | nick | 2008-07-18 18:16:50 +0100 (Fri, 18 Jul 2008) | 1 line
  
  Patch from bug #45398 - Support detecting date formats containing "am/pm" as date times
........
  r677995 | nick | 2008-07-18 19:40:11 +0100 (Fri, 18 Jul 2008) | 1 line
  
  Patch from bug #45414 - Don't add too many UncalcedRecords to sheets with charts in them
........
  r678287 | nick | 2008-07-20 18:18:07 +0100 (Sun, 20 Jul 2008) | 1 line
  
  Apply, with some tweaks, the patch from bug #45404 - New class, hssf.usermodel.HSSFDataFormatter, for formatting numbers and dates in the same way that Excel does
........


git-svn-id: https://svn.apache.org/repos/asf/poi/branches/ooxml@678288 13f79535-47bb-0310-9956-ffa450edef68

13 files changed, 1270 insertions, 163 deletions

diff --git a/build.xml b/build.xml
index b3d4ca8dd0..a16a660263 100644
--- a/build.xml
+++ b/build.xml
@@ -106,28 +106,11 @@ under the License.
   <property name="contrib.reports.test" location="build/contrib-test-results"/>
   <property name="contrib.output.dir" location="build/contrib-classes"/>
   <property name="contrib.output.test.dir" location="build/contrib-test-classes"/>
-  <property name="contrib.jar1.dir" location="${contrib.lib}/commons-beanutils-1.7.0.jar"/>
-  <property name="contrib.jar1.url" value="${repository}/commons-beanutils/jars/commons-beanutils-1.7.0.jar"/>
-  <property name="contrib.jar2.dir" location="${contrib.lib}/commons-collections-3.2.jar"/>
-  <property name="contrib.jar2.url" value="${repository}/commons-collections/jars/commons-collections-3.2.jar"/>
-  <property name="contrib.jar3.dir" location="${contrib.lib}/commons-lang-2.1.jar"/>
-  <property name="contrib.jar3.url" value="${repository}/commons-lang/jars/commons-lang-2.1.jar"/>
   <property name="contrib.testokfile" location="build/contrib-testokfile.txt"/>
 
   <!-- Examples: -->
   <property name="examples.src" location="src/examples/src"/>
-  <property name="examples.src.test" location="src/examples/testcases"/>
-  <property name="examples.lib" location="src/examples/lib"/>
-  <property name="examples.reports.test" location="build/examples-test-results"/>
   <property name="examples.output.dir" location="build/examples-classes"/>
-  <property name="examples.output.test.dir" location="build/examples-test-classes"/>
-  <property name="examples.jar1.dir" location="${examples.lib}/commons-beanutils-1.7.0.jar"/>
-  <property name="examples.jar1.url" value="${repository}/commons-beanutils/jars/commons-beanutils-1.7.0.jar"/>
-  <property name="examples.jar2.dir" location="${examples.lib}/commons-collections-3.2.jar"/>
-  <property name="examples.jar2.url" value="${repository}/commons-collections/jars/commons-collections-3.2.jar"/>
-  <property name="examples.jar3.dir" location="${examples.lib}/commons-lang-2.1.jar"/>
-  <property name="examples.jar3.url" value="${repository}/commons-lang/jars/commons-lang-2.1.jar"/>
-  <property name="examples.testokfile" location="build/examples-testokfile.txt"/>
 
   <!-- Common interfaces for ole2 and ooxml classes -->
   <property name="interfaces.jdk14.src" location="src/ooxml/interfaces-jdk14"/>
@@ -208,9 +191,6 @@ under the License.
 
     <path id="contrib.classpath">
         <path refid="main.classpath"/>
-        <pathelement location="${contrib.jar1.dir}"/>
-        <pathelement location="${contrib.jar2.dir}"/>
-        <pathelement location="${contrib.jar3.dir}"/>
         <pathelement location="${main.output.dir}"/>
         <pathelement location="${main.output.test.dir}"/>
         <pathelement location="${scratchpad.output.dir}"/>
@@ -351,9 +331,6 @@ under the License.
                 <and>
                     <available file="${main.jar1.dir}"/>
                     <available file="${main.jar2.dir}"/>
-                    <available file="${contrib.jar1.dir}"/>
-                    <available file="${contrib.jar2.dir}"/>
-                    <available file="${contrib.jar3.dir}"/>
                     <available file="${junit.jar1.dir}"/>
                     <available file="${ooxml.jar1.dir}"/>
                     <available file="${ooxml.jar2.dir}"/>
@@ -371,9 +348,6 @@ under the License.
      description="Fetches needed JAR files from the Internet">
         <get src="${main.jar1.url}" dest="${main.jar1.dir}"/>
         <get src="${main.jar2.url}" dest="${main.jar2.dir}"/>
-        <get src="${contrib.jar1.url}" dest="${contrib.jar1.dir}"/>
-        <get src="${contrib.jar2.url}" dest="${contrib.jar2.dir}"/>
-        <get src="${contrib.jar3.url}" dest="${contrib.jar3.dir}"/>
         <get src="${junit.jar1.url}" dest="${junit.jar1.dir}"/>
 
         <get src="${ooxml.jar1.url}" dest="${ooxml.jar1.dir}"/>
diff --git a/src/contrib/src/org/apache/poi/hssf/usermodel/contrib/HSSFCellUtil.java b/src/contrib/src/org/apache/poi/hssf/usermodel/contrib/HSSFCellUtil.java
index 5e2cf1e0fe..67f4b38cb5 100644
--- a/src/contrib/src/org/apache/poi/hssf/usermodel/contrib/HSSFCellUtil.java
+++ b/src/contrib/src/org/apache/poi/hssf/usermodel/contrib/HSSFCellUtil.java
@@ -1,4 +1,3 @@
-
 /* ====================================================================
    Licensed to the Apache Software Foundation (ASF) under one or more
    contributor license agreements.  See the NOTICE file distributed with
@@ -15,20 +14,21 @@
    See the License for the specific language governing permissions and
    limitations under the License.
 ==================================================================== */
-        
 
 package org.apache.poi.hssf.usermodel.contrib;
 
 
-import org.apache.commons.beanutils.PropertyUtils;
-import org.apache.commons.lang.StringUtils;
-import org.apache.commons.lang.exception.NestableException;
-import org.apache.poi.hssf.usermodel.*;
-
 import java.util.HashMap;
-import java.util.Iterator;
 import java.util.Map;
 
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFCellStyle;
+import org.apache.poi.hssf.usermodel.HSSFFont;
+import org.apache.poi.hssf.usermodel.HSSFRichTextString;
+import org.apache.poi.hssf.usermodel.HSSFRow;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+
 /**
  *  Various utility functions that make working with a cells and rows easier.  The various
  * methods that deal with style's allow you to create your HSSFCellStyles as you need them.
@@ -39,12 +39,46 @@ import java.util.Map;
  *
  *@author     Eric Pugh epugh@upstate.com
  */
-
-public class HSSFCellUtil
+public final class HSSFCellUtil
 {
 
-    private static HashMap unicodeMappings = new HashMap();
+    public static final String ALIGNMENT = "alignment";
+    public static final String BORDER_BOTTOM = "borderBottom";
+    public static final String BORDER_LEFT = "borderLeft";
+    public static final String BORDER_RIGHT = "borderRight";
+    public static final String BORDER_TOP = "borderTop";
+    public static final String BOTTOM_BORDER_COLOR = "bottomBorderColor";
+    public static final String DATA_FORMAT = "dataFormat";
+    public static final String FILL_BACKGROUND_COLOR = "fillBackgroundColor";
+    public static final String FILL_FOREGROUND_COLOR = "fillForegroundColor";
+    public static final String FILL_PATTERN = "fillPattern";
+    public static final String FONT = "font";
+    public static final String HIDDEN = "hidden";
+    public static final String INDENTION = "indention";
+    public static final String LEFT_BORDER_COLOR = "leftBorderColor";
+    public static final String LOCKED = "locked";
+    public static final String RIGHT_BORDER_COLOR = "rightBorderColor";
+    public static final String ROTATION = "rotation";
+    public static final String TOP_BORDER_COLOR = "topBorderColor";
+    public static final String VERTICAL_ALIGNMENT = "verticalAlignment";
+    public static final String WRAP_TEXT = "wrapText";
+
+    private static UnicodeMapping unicodeMappings[];
+
+    private static final class UnicodeMapping {
+
+        public final String entityName;
+        public final String resolvedValue;
+
+        public UnicodeMapping(String pEntityName, String pResolvedValue) {
+            entityName = "&" + pEntityName + ";";
+            resolvedValue = pResolvedValue;
+        }
+    }
 
+    private HSSFCellUtil() {
+        // no instances of this class
+    }
 
     /**
      *  Get a row from the spreadsheet, and create it if it doesn't exist.
@@ -94,7 +128,6 @@ public class HSSFCellUtil
      * @param  style   If the style is not null, then set
      * @return         A new HSSFCell
      */
-
     public static HSSFCell createCell( HSSFRow row, int column, String value, HSSFCellStyle style )
     {
         HSSFCell cell = getCell( row, column );
@@ -129,13 +162,12 @@ public class HSSFCellUtil
      *@param  cell     the cell to set the alignment for
      *@param  workbook               The workbook that is being worked with.
      *@param  align  the column alignment to use.
-     *@exception  NestableException  Thrown if an error happens.
      *
      * @see HSSFCellStyle for alignment options
      */
-    public static void setAlignment( HSSFCell cell, HSSFWorkbook workbook, short align ) throws NestableException
+    public static void setAlignment( HSSFCell cell, HSSFWorkbook workbook, short align )
     {
-        setCellStyleProperty( cell, workbook, "alignment", new Short( align ) );
+        setCellStyleProperty( cell, workbook, ALIGNMENT, new Short( align ) );
     }
 
     /**
@@ -144,18 +176,17 @@ public class HSSFCellUtil
      *@param  cell     the cell to set the alignment for
      *@param  workbook               The workbook that is being worked with.
      *@param  font  The HSSFFont that you want to set...
-     *@exception  NestableException  Thrown if an error happens.
      */
-    public static void setFont( HSSFCell cell, HSSFWorkbook workbook, HSSFFont font ) throws NestableException
+    public static void setFont( HSSFCell cell, HSSFWorkbook workbook, HSSFFont font )
     {
-        setCellStyleProperty( cell, workbook, "font", font );
+        setCellStyleProperty( cell, workbook, FONT, font );
     }
 
     /**
      *  This method attempt to find an already existing HSSFCellStyle that matches
      *  what you want the style to be. If it does not find the style, then it
-     *  creates a new one. If it does create a new one, then it applyies the
-     *  propertyName and propertyValue to the style. This is nessasary because
+     *  creates a new one. If it does create a new one, then it applies the
+     *  propertyName and propertyValue to the style. This is necessary because
      *  Excel has an upper limit on the number of Styles that it supports.
      *
      *@param  workbook               The workbook that is being worked with.
@@ -167,15 +198,11 @@ public class HSSFCellUtil
      *@exception  NestableException  Thrown if an error happens.
      */
     public static void setCellStyleProperty( HSSFCell cell, HSSFWorkbook workbook, String propertyName, Object propertyValue )
-            throws NestableException
     {
-        try
-        {
             HSSFCellStyle originalStyle = cell.getCellStyle();
             HSSFCellStyle newStyle = null;
-            Map values = PropertyUtils.describe( originalStyle );
+            Map values = getFormatProperties( originalStyle );
             values.put( propertyName, propertyValue );
-            values.remove( "index" );
 
             // index seems like what  index the cellstyle is in the list of styles for a workbook.
             // not good to compare on!
@@ -184,8 +211,7 @@ public class HSSFCellUtil
             for ( short i = 0; i < numberCellStyles; i++ )
             {
                 HSSFCellStyle wbStyle = workbook.getCellStyleAt( i );
-                Map wbStyleMap = PropertyUtils.describe( wbStyle );
-                wbStyleMap.remove( "index" );
+                Map wbStyleMap = getFormatProperties( wbStyle );
 
                 if ( wbStyleMap.equals( values ) )
                 {
@@ -197,21 +223,131 @@ public class HSSFCellUtil
             if ( newStyle == null )
             {
                 newStyle = workbook.createCellStyle();
-                newStyle.setFont( workbook.getFontAt( originalStyle.getFontIndex() ) );
-                PropertyUtils.copyProperties( newStyle, originalStyle );
-                PropertyUtils.setProperty( newStyle, propertyName, propertyValue );
+                setFormatProperties( newStyle, workbook, values );
             }
 
             cell.setCellStyle( newStyle );
+    }
+
+    /**
+     * Returns a map containing the format properties of the given cell style.
+     *
+     * @param style cell style
+     * @return map of format properties (String -> Object)
+     * @see #setFormatProperties(HSSFCellStyle, Map)
+     */
+    private static Map getFormatProperties(HSSFCellStyle style) {
+        Map properties = new HashMap();
+        putShort( properties, ALIGNMENT, style.getAlignment() );
+        putShort( properties, BORDER_BOTTOM, style.getBorderBottom() );
+        putShort( properties, BORDER_LEFT, style.getBorderLeft() );
+        putShort( properties, BORDER_RIGHT, style.getBorderRight() );
+        putShort( properties, BORDER_TOP, style.getBorderTop() );
+        putShort( properties, BOTTOM_BORDER_COLOR, style.getBottomBorderColor() );
+        putShort( properties, DATA_FORMAT, style.getDataFormat() );
+        putShort( properties, FILL_BACKGROUND_COLOR, style.getFillBackgroundColor() );
+        putShort( properties, FILL_FOREGROUND_COLOR, style.getFillForegroundColor() );
+        putShort( properties, FILL_PATTERN, style.getFillPattern() );
+        putShort( properties, FONT, style.getFontIndex() );
+        putBoolean( properties, HIDDEN, style.getHidden() );
+        putShort( properties, INDENTION, style.getIndention() );
+        putShort( properties, LEFT_BORDER_COLOR, style.getLeftBorderColor() );
+        putBoolean( properties, LOCKED, style.getLocked() );
+        putShort( properties, RIGHT_BORDER_COLOR, style.getRightBorderColor() );
+        putShort( properties, ROTATION, style.getRotation() );
+        putShort( properties, TOP_BORDER_COLOR, style.getTopBorderColor() );
+        putShort( properties, VERTICAL_ALIGNMENT, style.getVerticalAlignment() );
+        putBoolean( properties, WRAP_TEXT, style.getWrapText() );
+        return properties;
+    }
+
+    /**
+     * Sets the format properties of the given style based on the given map.
+     *
+     * @param style cell style
+     * @param workbook parent workbook
+     * @param properties map of format properties (String -> Object)
+     * @see #getFormatProperties(HSSFCellStyle)
+     */
+    private static void setFormatProperties(
+            HSSFCellStyle style, HSSFWorkbook workbook, Map properties) {
+        style.setAlignment( getShort( properties, ALIGNMENT ) );
+        style.setBorderBottom( getShort( properties, BORDER_BOTTOM ) );
+        style.setBorderLeft( getShort( properties, BORDER_LEFT ) );
+        style.setBorderRight( getShort( properties, BORDER_RIGHT ) );
+        style.setBorderTop( getShort( properties, BORDER_TOP ) );
+        style.setBottomBorderColor( getShort( properties, BOTTOM_BORDER_COLOR ) );
+        style.setDataFormat( getShort( properties, DATA_FORMAT ) );
+        style.setFillBackgroundColor( getShort( properties, FILL_BACKGROUND_COLOR ) );
+        style.setFillForegroundColor( getShort( properties, FILL_FOREGROUND_COLOR ) );
+        style.setFillPattern( getShort( properties, FILL_PATTERN ) );
+        style.setFont( workbook.getFontAt( getShort( properties, FONT ) ) );
+        style.setHidden( getBoolean( properties, HIDDEN ) );
+        style.setIndention( getShort( properties, INDENTION ) );
+        style.setLeftBorderColor( getShort( properties, LEFT_BORDER_COLOR ) );
+        style.setLocked( getBoolean( properties, LOCKED ) );
+        style.setRightBorderColor( getShort( properties, RIGHT_BORDER_COLOR ) );
+        style.setRotation( getShort( properties, ROTATION ) );
+        style.setTopBorderColor( getShort( properties, TOP_BORDER_COLOR ) );
+        style.setVerticalAlignment( getShort( properties, VERTICAL_ALIGNMENT ) );
+        style.setWrapText( getBoolean( properties, WRAP_TEXT ) );
+    }
+
+    /**
+     * Utility method that returns the named short value form the given map.
+     * Returns zero if the property does not exist, or is not a {@link Short}.
+     *
+     * @param properties map of named properties (String -> Object)
+     * @param name property name
+     * @return property value, or zero
+     */
+    private static short getShort(Map properties, String name) {
+        Object value = properties.get( name );
+        if ( value instanceof Short ) {
+            return ((Short) value).shortValue();
+        } else {
+            return 0;
         }
-        catch ( Exception e )
-        {
-            e.printStackTrace();
+    }
 
-            throw new NestableException( "Couldn't setCellStyleProperty.", e );
+    /**
+     * Utility method that returns the named boolean value form the given map.
+     * Returns false if the property does not exist, or is not a {@link Boolean}.
+     *
+     * @param properties map of properties (String -> Object)
+     * @param name property name
+     * @return property value, or false
+     */
+    private static boolean getBoolean(Map properties, String name) {
+        Object value = properties.get( name );
+        if ( value instanceof Boolean ) {
+            return ((Boolean) value).booleanValue();
+        } else {
+            return false;
         }
     }
 
+    /**
+     * Utility method that puts the named short value to the given map.
+     *
+     * @param properties map of properties (String -> Object)
+     * @param name property name
+     * @param value property value
+     */
+    private static void putShort(Map properties, String name, short value) {
+        properties.put( name, new Short( value ) );
+    }
+
+    /**
+     * Utility method that puts the named boolean value to the given map.
+     *
+     * @param properties map of properties (String -> Object)
+     * @param name property name
+     * @param value property value
+     */
+    private static void putBoolean(Map properties, String name, boolean value) {
+        properties.put( name, new Boolean( value ) );
+    }
 
     /**
      *  Looks for text in the cell that should be unicode, like &alpha; and provides the
@@ -225,42 +361,45 @@ public class HSSFCellUtil
 
         String s = cell.getRichStringCellValue().getString(); 
         boolean foundUnicode = false;
+        String lowerCaseStr = s.toLowerCase();
 
-        for ( Iterator i = unicodeMappings.entrySet().iterator(); i.hasNext(); )
-        {
-            Map.Entry entry = (Map.Entry) i.next();
-            String key = (String) entry.getKey();
-            if ( s.toLowerCase().indexOf( key ) != -1 )
+        for (int i = 0; i < unicodeMappings.length; i++) {
+            UnicodeMapping entry = unicodeMappings[i];
+            String key = entry.entityName;
+            if ( lowerCaseStr.indexOf( key ) != -1 )
             {
-                s = StringUtils.replace( s, key, "" + entry.getValue().toString() + "" );
+                s =  s.replaceAll(key, entry.resolvedValue);
                 foundUnicode = true;
             }
         }
         if ( foundUnicode )
         {
-            cell.setEncoding( HSSFCell.ENCODING_UTF_16 );
-            cell.setCellValue( s );
+            cell.setCellValue(new HSSFRichTextString(s));
         }
         return cell;
     }
 
-    
     static {
-        unicodeMappings.put( "&alpha;",   "\u03B1" );
-        unicodeMappings.put( "&beta;",    "\u03B2" );
-        unicodeMappings.put( "&gamma;",   "\u03B3" );
-        unicodeMappings.put( "&delta;",   "\u03B4" );
-        unicodeMappings.put( "&epsilon;", "\u03B5" );
-        unicodeMappings.put( "&zeta;",    "\u03B6" );
-        unicodeMappings.put( "&eta;",     "\u03B7" );
-        unicodeMappings.put( "&theta;",   "\u03B8" );
-        unicodeMappings.put( "&iota;",    "\u03B9" );
-        unicodeMappings.put( "&kappa;",   "\u03BA" );
-        unicodeMappings.put( "&lambda;",  "\u03BB" );
-        unicodeMappings.put( "&mu;",      "\u03BC" );
-        unicodeMappings.put( "&nu;",      "\u03BD" );
-        unicodeMappings.put( "&xi;",      "\u03BE" );
-        unicodeMappings.put( "&omicron;", "\u03BF" );
+        unicodeMappings = new UnicodeMapping[] {
+            um("alpha",   "\u03B1" ),
+            um("beta",    "\u03B2" ),
+            um("gamma",   "\u03B3" ),
+            um("delta",   "\u03B4" ),
+            um("epsilon", "\u03B5" ),
+            um("zeta",    "\u03B6" ),
+            um("eta",     "\u03B7" ),
+            um("theta",   "\u03B8" ),
+            um("iota",    "\u03B9" ),
+            um("kappa",   "\u03BA" ),
+            um("lambda",  "\u03BB" ),
+            um("mu",      "\u03BC" ),
+            um("nu",      "\u03BD" ),
+            um("xi",      "\u03BE" ),
+            um("omicron", "\u03BF" ),
+        };
     }
 
+    private static UnicodeMapping um(String entityName, String resolvedValue) {
+        return new UnicodeMapping(entityName, resolvedValue);
+    }
 }
diff --git a/src/contrib/src/org/apache/poi/hssf/usermodel/contrib/HSSFRegionUtil.java b/src/contrib/src/org/apache/poi/hssf/usermodel/contrib/HSSFRegionUtil.java
index ea525149c7..53e176c076 100644
--- a/src/contrib/src/org/apache/poi/hssf/usermodel/contrib/HSSFRegionUtil.java
+++ b/src/contrib/src/org/apache/poi/hssf/usermodel/contrib/HSSFRegionUtil.java
@@ -1,4 +1,3 @@
-
 /* ====================================================================
    Licensed to the Apache Software Foundation (ASF) under one or more
    contributor license agreements.  See the NOTICE file distributed with
@@ -15,7 +14,6 @@
    See the License for the specific language governing permissions and
    limitations under the License.
 ==================================================================== */
-        
 
 package org.apache.poi.hssf.usermodel.contrib;
 
@@ -26,33 +24,31 @@ import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 
 import org.apache.poi.ss.util.Region;
 
-import org.apache.commons.lang.exception.NestableException;
-
 /**
  *  Various utility functions that make working with a region of cells easier.
  *
  *@author     Eric Pugh epugh@upstate.com
  *@since      July 29, 2002
  */
-
-public class HSSFRegionUtil
+public final class HSSFRegionUtil
 {
 
   /**  Constructor for the HSSFRegionUtil object */
-  private HSSFRegionUtil() { }
+  private HSSFRegionUtil() {
+      // no instances of this class
+  }
 
   /**
    *  Sets the left border for a region of cells by manipulating the cell style
-   *  of the indidual cells on the left
+   *  of the individual cells on the left
    *
    *@param  border                 The new border
    *@param  region                 The region that should have the border
    *@param  workbook               The workbook that the region is on.
    *@param  sheet                  The sheet that the region is on.
-   *@exception  NestableException  Thrown if the CellStyle can't be changed
    */
   public static void setBorderLeft( short border, Region region, HSSFSheet sheet, HSSFWorkbook workbook )
-    throws NestableException {
+  {
     int rowStart = region.getRowFrom();
     int rowEnd = region.getRowTo();
     int column = region.getColumnFrom();
@@ -60,7 +56,8 @@ public class HSSFRegionUtil
     for ( int i = rowStart; i <= rowEnd; i++ ) {
       HSSFRow row = HSSFCellUtil.getRow( i, sheet );
       HSSFCell cell = HSSFCellUtil.getCell( row, column );
-      HSSFCellUtil.setCellStyleProperty( cell, workbook, "borderLeft", new Short( border ) );
+      HSSFCellUtil.setCellStyleProperty(
+          cell, workbook, HSSFCellUtil.BORDER_LEFT, new Short( border ) );
     }
   }
 
@@ -71,11 +68,9 @@ public class HSSFRegionUtil
    *@param  region                 The region that should have the border
    *@param  workbook               The workbook that the region is on.
    *@param  sheet                  The sheet that the region is on.
-   *@exception  NestableException  Thrown if the CellStyle can't be changed
-   *      properly.
    */
   public static void setLeftBorderColor( short color, Region region, HSSFSheet sheet, HSSFWorkbook workbook )
-    throws NestableException {
+  {
     int rowStart = region.getRowFrom();
     int rowEnd = region.getRowTo();
     int column = region.getColumnFrom();
@@ -83,7 +78,8 @@ public class HSSFRegionUtil
     for ( int i = rowStart; i <= rowEnd; i++ ) {
       HSSFRow row = HSSFCellUtil.getRow( i, sheet );
       HSSFCell cell = HSSFCellUtil.getCell( row, column );
-      HSSFCellUtil.setCellStyleProperty( cell, workbook, "leftBorderColor", new Short( color ) );
+      HSSFCellUtil.setCellStyleProperty(
+          cell, workbook, HSSFCellUtil.LEFT_BORDER_COLOR, new Short( color ) );
     }
   }
 
@@ -94,10 +90,9 @@ public class HSSFRegionUtil
    *@param  region                 The region that should have the border
    *@param  workbook               The workbook that the region is on.
    *@param  sheet                  The sheet that the region is on.
-   *@exception  NestableException  Thrown if the CellStyle can't be changed
    */
   public static void setBorderRight( short border, Region region, HSSFSheet sheet, HSSFWorkbook workbook )
-    throws NestableException {
+  {
     int rowStart = region.getRowFrom();
     int rowEnd = region.getRowTo();
     int column = region.getColumnTo();
@@ -106,7 +101,8 @@ public class HSSFRegionUtil
       HSSFRow row = HSSFCellUtil.getRow( i, sheet );
       HSSFCell cell = HSSFCellUtil.getCell( row, column );
 
-      HSSFCellUtil.setCellStyleProperty( cell, workbook, "borderRight", new Short( border ) );
+      HSSFCellUtil.setCellStyleProperty(
+          cell, workbook, HSSFCellUtil.BORDER_RIGHT, new Short( border ) );
     }
   }
 
@@ -117,11 +113,9 @@ public class HSSFRegionUtil
    *@param  region                 The region that should have the border
    *@param  workbook               The workbook that the region is on.
    *@param  sheet                  The sheet that the region is on.
-   *@exception  NestableException  Thrown if the CellStyle can't be changed
-   *      properly.
    */
   public static void setRightBorderColor( short color, Region region, HSSFSheet sheet, HSSFWorkbook workbook )
-    throws NestableException {
+  {
     int rowStart = region.getRowFrom();
     int rowEnd = region.getRowTo();
     int column = region.getColumnTo();
@@ -129,7 +123,8 @@ public class HSSFRegionUtil
     for ( int i = rowStart; i <= rowEnd; i++ ) {
       HSSFRow row = HSSFCellUtil.getRow( i, sheet );
       HSSFCell cell = HSSFCellUtil.getCell( row, column );
-      HSSFCellUtil.setCellStyleProperty( cell, workbook, "rightBorderColor", new Short( color ) );
+      HSSFCellUtil.setCellStyleProperty(
+          cell, workbook, HSSFCellUtil.RIGHT_BORDER_COLOR, new Short( color ) );
     }
   }
 
@@ -140,10 +135,9 @@ public class HSSFRegionUtil
    *@param  region                 The region that should have the border
    *@param  workbook               The workbook that the region is on.
    *@param  sheet                  The sheet that the region is on.
-   *@exception  NestableException  Thrown if the CellStyle can't be changed
    */
   public static void setBorderBottom( short border, Region region, HSSFSheet sheet, HSSFWorkbook workbook )
-    throws NestableException {
+  {
     int colStart = region.getColumnFrom();
     int colEnd = region.getColumnTo();
     int rowIndex = region.getRowTo();
@@ -151,7 +145,8 @@ public class HSSFRegionUtil
     for ( int i = colStart; i <= colEnd; i++ ) {
 
       HSSFCell cell = HSSFCellUtil.getCell( row, i );
-      HSSFCellUtil.setCellStyleProperty( cell, workbook, "borderBottom", new Short( border ) );
+      HSSFCellUtil.setCellStyleProperty(
+          cell, workbook, HSSFCellUtil.BORDER_BOTTOM, new Short( border ) );
     }
   }
 
@@ -162,22 +157,20 @@ public class HSSFRegionUtil
    *@param  region                 The region that should have the border
    *@param  workbook               The workbook that the region is on.
    *@param  sheet                  The sheet that the region is on.
-   *@exception  NestableException  Thrown if the CellStyle can't be changed
-   *      properly.
    */
   public static void setBottomBorderColor( short color, Region region, HSSFSheet sheet, HSSFWorkbook workbook )
-    throws NestableException {
+  {
     int colStart = region.getColumnFrom();
     int colEnd = region.getColumnTo();
     int rowIndex = region.getRowTo();
     HSSFRow row = HSSFCellUtil.getRow( rowIndex, sheet );
     for ( int i = colStart; i <= colEnd; i++ ) {
       HSSFCell cell = HSSFCellUtil.getCell( row, i );
-      HSSFCellUtil.setCellStyleProperty( cell, workbook, "bottomBorderColor", new Short( color ) );
+      HSSFCellUtil.setCellStyleProperty(
+          cell, workbook, HSSFCellUtil.BOTTOM_BORDER_COLOR, new Short( color ) );
     }
   }
 
-
   /**
    *  Sets the borderBottom attribute of the HSSFRegionUtil object
    *
@@ -185,10 +178,9 @@ public class HSSFRegionUtil
    *@param  region                 The region that should have the border
    *@param  workbook               The workbook that the region is on.
    *@param  sheet                  The sheet that the region is on.
-   *@exception  NestableException  Thrown if the CellStyle can't be changed
    */
   public static void setBorderTop( short border, Region region, HSSFSheet sheet, HSSFWorkbook workbook )
-    throws NestableException {
+  {
     int colStart = region.getColumnFrom();
     int colEnd = region.getColumnTo();
     int rowIndex = region.getRowFrom();
@@ -196,7 +188,8 @@ public class HSSFRegionUtil
     for ( int i = colStart; i <= colEnd; i++ ) {
 
       HSSFCell cell = HSSFCellUtil.getCell( row, i );
-      HSSFCellUtil.setCellStyleProperty( cell, workbook, "borderTop", new Short( border ) );
+      HSSFCellUtil.setCellStyleProperty(
+          cell, workbook, HSSFCellUtil.BORDER_TOP, new Short( border ) );
     }
   }
 
@@ -207,21 +200,18 @@ public class HSSFRegionUtil
    *@param  region                 The region that should have the border
    *@param  workbook               The workbook that the region is on.
    *@param  sheet                  The sheet that the region is on.
-   *@exception  NestableException  Thrown if the CellStyle can't be changed
-   *      properly.
    */
   public static void setTopBorderColor( short color, Region region, HSSFSheet sheet, HSSFWorkbook workbook )
-    throws NestableException {
+  {
     int colStart = region.getColumnFrom();
     int colEnd = region.getColumnTo();
     int rowIndex = region.getRowFrom();
     HSSFRow row = HSSFCellUtil.getRow( rowIndex, sheet );
     for ( int i = colStart; i <= colEnd; i++ ) {
       HSSFCell cell = HSSFCellUtil.getCell( row, i );
-      HSSFCellUtil.setCellStyleProperty( cell, workbook, "topBorderColor", new Short( color ) );
-
+      HSSFCellUtil.setCellStyleProperty(
+          cell, workbook, HSSFCellUtil.TOP_BORDER_COLOR, new Short( color ) );
     }
   }
-
 }
 
diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml
index 1da164e945..b98efd167c 100644
--- a/src/documentation/content/xdocs/changes.xml
+++ b/src/documentation/content/xdocs/changes.xml
@@ -50,6 +50,10 @@
            <action dev="POI-DEVELOPERS" type="add">Created a common interface for handling Excel files, irrespective of if they are .xls or .xlsx</action>
         </release>
         <release version="3.1.1-alpha1" date="2008-??-??">
+           <action dev="POI-DEVELOPERS" type="add">45404 - New class, hssf.usermodel.HSSFDataFormatter, for formatting numbers and dates in the same way that Excel does</action>
+           <action dev="POI-DEVELOPERS" type="fix">45414 - Don't add too many UncalcedRecords to sheets with charts in them</action>
+           <action dev="POI-DEVELOPERS" type="fix">45398 - Support detecting date formats containing "am/pm" as date times</action>
+           <action dev="POI-DEVELOPERS" type="fix">45410 - Removed dependency from contrib on commons beanutils,collections and lang</action>
            <action dev="POI-DEVELOPERS" type="add">New helper, HSSFOptimiser, which handles removing duplicated font and style records, to avoid going over the limits in Excel</action>
            <action dev="POI-DEVELOPERS" type="fix">45322 - Fixed NPE in HSSFSheet.autoSizeColumn() when cell number format was not found</action>
            <action dev="POI-DEVELOPERS" type="add">45380 - Missing return keyword in ArrayPtg.toFormulaString()</action>
diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml
index 155d8ff153..c4e6cc5189 100644
--- a/src/documentation/content/xdocs/status.xml
+++ b/src/documentation/content/xdocs/status.xml
@@ -47,6 +47,10 @@
            <action dev="POI-DEVELOPERS" type="add">Created a common interface for handling Excel files, irrespective of if they are .xls or .xlsx</action>
         </release>
         <release version="3.1.1-alpha1" date="2008-??-??">
+           <action dev="POI-DEVELOPERS" type="add">45404 - New class, hssf.usermodel.HSSFDataFormatter, for formatting numbers and dates in the same way that Excel does</action>
+           <action dev="POI-DEVELOPERS" type="fix">45414 - Don't add too many UncalcedRecords to sheets with charts in them</action>
+           <action dev="POI-DEVELOPERS" type="fix">45398 - Support detecting date formats containing "am/pm" as date times</action>
+           <action dev="POI-DEVELOPERS" type="fix">45410 - Removed dependency from contrib on commons beanutils,collections and lang</action>
            <action dev="POI-DEVELOPERS" type="add">New helper, HSSFOptimiser, which handles removing duplicated font and style records, to avoid going over the limits in Excel</action>
            <action dev="POI-DEVELOPERS" type="fix">45322 - Fixed NPE in HSSFSheet.autoSizeColumn() when cell number format was not found</action>
            <action dev="POI-DEVELOPERS" type="add">45380 - Missing return keyword in ArrayPtg.toFormulaString()</action>
diff --git a/src/java/org/apache/poi/hssf/eventusermodel/FormatTrackingHSSFListener.java b/src/java/org/apache/poi/hssf/eventusermodel/FormatTrackingHSSFListener.java
index 5a84f45646..355a9b71f0 100644
--- a/src/java/org/apache/poi/hssf/eventusermodel/FormatTrackingHSSFListener.java
+++ b/src/java/org/apache/poi/hssf/eventusermodel/FormatTrackingHSSFListener.java
@@ -32,6 +32,7 @@ import org.apache.poi.hssf.record.FormulaRecord;
 import org.apache.poi.hssf.record.NumberRecord;
 import org.apache.poi.hssf.record.Record;
 import org.apache.poi.hssf.usermodel.HSSFDataFormat;
+import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
 import org.apache.poi.hssf.usermodel.HSSFDateUtil;
 
 /**
@@ -41,6 +42,7 @@ import org.apache.poi.hssf.usermodel.HSSFDateUtil;
  */
 public class FormatTrackingHSSFListener implements HSSFListener {
 	private HSSFListener childListener;
+	private HSSFDataFormatter formatter = new HSSFDataFormatter();
 	private Map customFormatRecords = new Hashtable();
 	private List xfRecords = new ArrayList();
 
@@ -102,32 +104,9 @@ public class FormatTrackingHSSFListener implements HSSFListener {
 		if(formatString == null) {
             return Double.toString(value);
         } else {
-        	// Is it a date?
-        	if(HSSFDateUtil.isADateFormat(formatIndex,formatString) &&
-        			HSSFDateUtil.isValidExcelDate(value)) {
-        		// Java wants M not m for month
-        		formatString = formatString.replace('m','M');
-        		// Change \- into -, if it's there
-        		formatString = formatString.replaceAll("\\\\-","-");
-        		
-        		// Format as a date
-        		Date d = HSSFDateUtil.getJavaDate(value, false);
-        		DateFormat df = new SimpleDateFormat(formatString);
-	            return df.format(d);
-        	} else {
-        		if(formatString == "General") {
-        			// Some sort of wierd default
-        			return Double.toString(value);
-        		}
-        		if(formatString == "0.00E+00") {
-        			// This seems to mean output as a normal double
-        			return Double.toString(value);
-        		}
-        		
-        		// Format as a number
-	            DecimalFormat df = new DecimalFormat(formatString);
-	            return df.format(value);
-        	}
+        	// Format, using the nice new
+        	//  HSSFDataFormatter to do the work for us 
+        	return formatter.formatRawCellContents(value, formatIndex, formatString);
         }
 	}
 	
diff --git a/src/java/org/apache/poi/hssf/model/Sheet.java b/src/java/org/apache/poi/hssf/model/Sheet.java
index b95ad6bba3..cbbe663b1d 100644
--- a/src/java/org/apache/poi/hssf/model/Sheet.java
+++ b/src/java/org/apache/poi/hssf/model/Sheet.java
@@ -827,16 +827,21 @@ public final class Sheet implements Model {
 
             // If the BOF record was just serialized then add the IndexRecord
             if (record.getSid() == BOFRecord.sid) {
-              // Add an optional UncalcedRecord
-              if (_isUncalced) {
-                  UncalcedRecord rec = new UncalcedRecord();
-                  pos += rec.serialize(pos, data);
-              }
-              //Can there be more than one BOF for a sheet? If not then we can
-              //remove this guard. So be safe it is left here.
-              if (rows != null && !haveSerializedIndex) {
+              if (!haveSerializedIndex) {
                 haveSerializedIndex = true;
-                pos += serializeIndexRecord(k, pos, data);
+                // Add an optional UncalcedRecord. However, we should add
+                //  it in only the once, after the sheet's own BOFRecord.
+                // If there are diagrams, they have their own BOFRecords,
+                //  and one shouldn't go in after that!
+                if (_isUncalced) {
+                    UncalcedRecord rec = new UncalcedRecord();
+                    pos += rec.serialize(pos, data);
+                }
+                //Can there be more than one BOF for a sheet? If not then we can
+                //remove this guard. So be safe it is left here.
+                if (rows != null) {
+                  pos += serializeIndexRecord(k, pos, data);
+                }
               }
             }
         }
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
index 4d4ad500eb..3283f98dde 100644
--- a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
@@ -690,10 +690,13 @@ public class HSSFCell implements Cell
 
 
     /**
-     * get the value of the cell as a number.  For strings we throw an exception.
+     * Get the value of the cell as a number.  
+     * For strings we throw an exception.
      * For blank cells we return a 0.
+     * See {@link HSSFDataFormatter} for turning this
+     *  number into a string similar to that which
+     *  Excel would render this number as. 
      */
-
     public double getNumericCellValue()
     {
         if (cellType == CELL_TYPE_BLANK)
@@ -727,8 +730,11 @@ public class HSSFCell implements Cell
     }
 
     /**
-     * get the value of the cell as a date.  For strings we throw an exception.
+     * Get the value of the cell as a date.  
+     * For strings we throw an exception.
      * For blank cells we return a null.
+     * See {@link HSSFDataFormatter} for formatting
+     *  this date into a string similar to how excel does.
      */
     public Date getDateCellValue()
     {
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFDataFormatter.java b/src/java/org/apache/poi/hssf/usermodel/HSSFDataFormatter.java
new file mode 100644
index 0000000000..c1701e22b3
--- /dev/null
+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFDataFormatter.java
@@ -0,0 +1,703 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+
+package org.apache.poi.hssf.usermodel;
+
+import java.text.DecimalFormat;
+import java.text.FieldPosition;
+import java.text.Format;
+import java.text.ParsePosition;
+import java.text.SimpleDateFormat;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.HashMap;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
+/**
+ * HSSFDataFormatter contains methods for formatting the value stored in an
+ * HSSFCell. This can be useful for reports and GUI presentations when you
+ * need to display data exactly as it appears in Excel. Supported formats
+ * include currency, SSN, percentages, decimals, dates, phone numbers, zip
+ * codes, etc.
+ * <p>
+ * Internally, formats will be implemented using subclasses of {@link Format}
+ * such as {@link DecimalFormat} and {@link SimpleDateFormat}. Therefore the
+ * formats used by this class must obey the same pattern rules as these Format
+ * subclasses. This means that only legal number pattern characters ("0", "#",
+ * ".", "," etc.) may appear in number formats. Other characters can be
+ * inserted <em>before</em> or <em> after</em> the number pattern to form a
+ * prefix or suffix.
+ * </p>
+ * <p>
+ * For example the Excel pattern <code>"$#,##0.00 "USD"_);($#,##0.00 "USD")"
+ * </code> will be correctly formatted as "$1,000.00 USD" or "($1,000.00 USD)".
+ * However the pattern <code>"00-00-00"</code> is incorrectly formatted by
+ * DecimalFormat as "000000--". For Excel formats that are not compatible with
+ * DecimalFormat, you can provide your own custom {@link Format} implementation
+ * via <code>HSSFDataFormatter.addFormat(String,Format)</code>. The following
+ * custom formats are already provided by this class:
+ * </p>
+ * <pre>
+ * <ul><li>SSN "000-00-0000"</li>
+ *     <li>Phone Number "(###) ###-####"</li>
+ *     <li>Zip plus 4 "00000-0000"</li>
+ * </ul>
+ * </pre>
+ * <p>
+ * If the Excel format pattern cannot be parsed successfully, then a default
+ * format will be used. The default number format will mimic the Excel General
+ * format: "#" for whole numbers and "#.##########" for decimal numbers. You
+ * can override the default format pattern with <code>
+ * HSSFDataFormatter.setDefaultNumberFormat(Format)</code>. <b>Note:</b> the
+ * default format will only be used when a Format cannot be created from the
+ * cell's data format string.  
+ * 
+ * @author James May (james dot may at fmr dot com)
+ *
+ */
+public class HSSFDataFormatter {
+
+	/** Pattern to find a number format: "0" or  "#" */
+	protected Pattern numPattern;
+	
+	/** Pattern to find days of week as text "ddd...." */
+	protected Pattern daysAsText;
+	
+	/** Pattern to find "AM/PM" marker */
+	protected Pattern amPmPattern;
+
+	/** A regex to find patterns like [$$-1009] and [$�-452]. */
+	protected Pattern specialPatternGroup;
+	
+	/** <em>General</em> format for whole numbers. */
+	protected Format generalWholeNumFormat;
+	
+	/** <em>General</em> format for decimal numbers. */
+	protected Format generalDecimalNumFormat;	
+	
+	/** A default format to use when a number pattern cannot be parsed. */
+	protected Format defaultNumFormat;
+	
+	/** 
+	 * A map to cache formats.
+	 *  Map<String,Format> formats
+	 */
+	protected Map formats;
+	
+
+	/**
+	 * Constructor
+	 */
+	public HSSFDataFormatter() {
+		numPattern = Pattern.compile("[0#]+");
+		daysAsText = Pattern.compile("([d]{3,})", Pattern.CASE_INSENSITIVE);
+		amPmPattern = Pattern.compile("((A|P)[M/P]*)", Pattern.CASE_INSENSITIVE);
+		specialPatternGroup = Pattern.compile("(\\[\\$[^-\\]]*-[0-9A-Z]+\\])");
+		generalWholeNumFormat =  new DecimalFormat("#");
+		generalDecimalNumFormat =  new DecimalFormat("#.##########");	
+		formats = new HashMap();
+		
+		// init built-in formats
+		init();
+	}
+
+	/**
+	 * Initialize the formatter. Called after construction.
+	 */
+	protected void init() {		
+		
+		ZipPlusFourFormat zipFormat = new ZipPlusFourFormat();
+		addFormat("00000\\-0000", zipFormat);
+		addFormat("00000-0000", zipFormat);
+		
+		PhoneFormat phoneFormat = new PhoneFormat();
+		// allow for format string variations
+		addFormat("[<=9999999]###\\-####;\\(###\\)\\ ###\\-####", phoneFormat);
+		addFormat("[<=9999999]###-####;(###) ###-####", phoneFormat);
+		addFormat("###\\-####;\\(###\\)\\ ###\\-####", phoneFormat);
+		addFormat("###-####;(###) ###-####", phoneFormat);		
+		
+		SSNFormat ssnFormat = new SSNFormat();
+		addFormat("000\\-00\\-0000", ssnFormat);		
+		addFormat("000-00-0000", ssnFormat);			
+	}
+	
+	/**
+	 * Return a Format for the given cell if one exists, otherwise try to
+	 * create one. This method will return <code>null</code> if the any of the
+	 * following is true:
+	 * <ul>
+	 * <li>the cell's style is null</li>
+	 * <li>the style's data format string is null or empty</li>
+	 * <li>the format string cannot be recognized as either a number or date</li>
+	 * </ul>
+	 * 
+	 * @param cell The cell to retrieve a Format for
+	 * @return A Format for the format String
+	 */
+	protected Format getFormat(HSSFCell cell) {
+		if ( cell.getCellStyle() == null) {
+			return null;
+		}
+		
+		int formatIndex = cell.getCellStyle().getDataFormat();
+		String formatStr = cell.getCellStyle().getDataFormatString();
+		if(formatStr == null || formatStr.trim().length() == 0) {
+			return null;
+		}
+		return getFormat(cell.getNumericCellValue(), formatIndex, formatStr);
+	}
+	
+	private Format getFormat(double cellValue, int formatIndex, String formatStr) {
+		Format format = (Format)formats.get(formatStr);
+		if (format != null) {
+			return format;
+		} else if (formatStr.equals("General")) {
+			if (HSSFDataFormatter.isWholeNumber(cellValue)) {
+				return generalWholeNumFormat;
+			} else {
+				return generalDecimalNumFormat;
+			}
+		} else {
+			format = createFormat(cellValue, formatIndex, formatStr);
+			formats.put(formatStr, format);
+			return format;
+		}
+	}
+	
+	/**
+	 * Create and return a Format based on the format string from a  cell's
+	 * style. If the pattern cannot be parsed, return a default pattern.
+	 * 
+	 * @param cell The Excel cell
+	 * @return A Format representing the excel format. May return null.
+	 */
+	protected Format createFormat(HSSFCell cell) {	
+		String sFormat = cell.getCellStyle().getDataFormatString();
+		
+		int formatIndex = cell.getCellStyle().getDataFormat();
+		String formatStr = cell.getCellStyle().getDataFormatString();
+		return createFormat(cell.getNumericCellValue(), formatIndex, formatStr); 
+	}
+	
+	private Format createFormat(double cellValue, int formatIndex, String sFormat) {
+		// remove color formatting if present
+		String formatStr = sFormat.replaceAll("\\[[a-zA-Z]*\\]", "");
+		
+		// try to extract special characters like currency
+		Matcher m = specialPatternGroup.matcher(formatStr);		
+		try {
+			while(m.find()) {
+				String match = m.group();
+				String symbol = match.substring(match.indexOf('$') + 1, match.indexOf('-'));
+				if (symbol.indexOf('$') > -1) {
+					StringBuffer sb = new StringBuffer();
+					sb.append(symbol.substring(0, symbol.indexOf('$')));
+					sb.append('\\');
+					sb.append(symbol.substring(symbol.indexOf('$'), symbol.length()));
+					symbol = sb.toString();
+				}
+				formatStr = m.replaceAll(symbol);
+			}
+		} catch (Exception e) {
+			return getDefaultFormat(cellValue);
+		}
+		
+		if(formatStr == null || formatStr.trim().length() == 0) {
+			return getDefaultFormat(cellValue);
+		}
+
+		Format returnVal = null;
+		StringBuffer sb = null;
+
+    	if(HSSFDateUtil.isADateFormat(formatIndex,formatStr) &&
+    			HSSFDateUtil.isValidExcelDate(cellValue)) {
+	    	formatStr = formatStr.replaceAll("\\\\-","-");
+	    	formatStr = formatStr.replaceAll("\\\\,",",");
+	    	formatStr = formatStr.replaceAll("\\\\ "," ");
+	    	formatStr = formatStr.replaceAll(";@", "");
+	    	boolean hasAmPm = false;
+	    	Matcher amPmMatcher = amPmPattern.matcher(formatStr);
+	    	while (amPmMatcher.find()) {
+	    		formatStr = amPmMatcher.replaceAll("a");
+	    		hasAmPm = true;
+	    	}
+	    	
+	    	Matcher dateMatcher = daysAsText.matcher(formatStr);
+	    	if (dateMatcher.find()) {
+				String match = dateMatcher.group(0);
+				formatStr = dateMatcher.replaceAll(match.toUpperCase().replaceAll("D", "E"));
+	    	}
+	    	
+	    	// Convert excel date format to SimpleDateFormat.
+	    	// Excel uses lower case 'm' for both minutes and months.
+	    	// From Excel help:
+	    	/* 
+			  The "m" or "mm" code must appear immediately after the "h" or"hh"
+			  code or immediately before the "ss" code; otherwise, Microsoft
+			  Excel displays the month instead of minutes."
+			*/
+	    	
+	    	sb = new StringBuffer();
+	    	char[] chars = formatStr.toCharArray();
+	    	boolean mIsMonth = true;
+	    	List ms = new ArrayList();
+	    	for(int j=0; j<chars.length; j++) {
+	    		char c = chars[j];
+	    		if (c == 'h' || c == 'H') {
+	    			mIsMonth = false;
+	    			if (hasAmPm) {
+	    				sb.append('h');
+	    			} else {
+	    				sb.append('H');
+	    			}	    			
+	    		}
+	    		else if (c == 'm') {
+	    			if(mIsMonth) {
+	    				sb.append('M');
+	    				ms.add(
+	    						new Integer(sb.length() -1)
+	    				);
+	    			} else {
+	    				sb.append('m');
+	    			}
+	    		}
+	    		else if (c == 's' || c == 'S') {
+	    			sb.append('s');
+	    			// if 'M' precedes 's' it should be minutes ('m') 
+	    			for (int i = 0; i < ms.size(); i++) {
+	    				int index = ((Integer)ms.get(i)).intValue();
+	    				if (sb.charAt(index) == 'M') {
+	    					sb.replace(index, index+1, "m");
+	    				}
+	    			}
+	    			mIsMonth = true;
+	    			ms.clear();
+	    		}
+	    		else if (Character.isLetter(c)) {
+	    			mIsMonth = true;
+	    			ms.clear();
+	    			if (c == 'y' || c == 'Y') {
+	    				sb.append('y');
+	    			}
+	    			else if (c == 'd' || c == 'D') {
+	    				sb.append('d');	    			
+	    			}
+	    			else {
+	    				sb.append(c);
+	    			}	    			
+	    		}
+	    		else {
+	    			sb.append(c);
+	    		}
+	    	}
+			formatStr = sb.toString();
+			
+			try {
+				returnVal = new SimpleDateFormat(formatStr);				
+			} catch(IllegalArgumentException iae) {
+				
+				// the pattern could not be parsed correctly,
+				// so fall back to the default number format
+				return getDefaultFormat(cellValue);
+			}	    	
+			
+		} else if (numPattern.matcher(formatStr).find()) {
+			sb = new StringBuffer(formatStr);
+			for (int i = 0; i < sb.length(); i++) {
+				char c = sb.charAt(i);
+				//handle (#,##0_);
+				if (c == '(') {
+					int idx = sb.indexOf(")", i);
+					if (idx > -1 && sb.charAt(idx -1) == '_') {
+						sb.deleteCharAt(idx);
+						sb.deleteCharAt(idx - 1);
+						sb.deleteCharAt(i);
+						i--;
+					}
+				} else if (c == ')' && i > 0 && sb.charAt(i - 1) == '_') {
+					sb.deleteCharAt(i);
+					sb.deleteCharAt(i - 1);
+					i--;
+				// remove quotes and back slashes
+				} else if (c == '\\' || c == '"') {
+					sb.deleteCharAt(i);
+					i--;
+					
+				// for scientific/engineering notation
+				} else if (c == '+' && i > 0 && sb.charAt(i - 1) == 'E') {
+					sb.deleteCharAt(i);
+					i--;
+				}							
+			}
+			formatStr = sb.toString();
+			try {
+				returnVal = new DecimalFormat(formatStr);				
+			} catch(IllegalArgumentException iae) {
+
+				// the pattern could not be parsed correctly,
+				// so fall back to the default number format
+				return getDefaultFormat(cellValue);
+			}
+		}
+		return returnVal;
+	}
+	
+	/**
+	 * Return true if the double value represents a whole number
+	 * @param d the double value to check
+	 * @return true if d is a whole number
+	 */
+	private static boolean isWholeNumber(double d) {
+		return d == Math.floor(d);
+	}
+	
+	/**
+	 * Returns a default format for a cell.
+	 * @param cell The cell
+	 * @return a default format
+	 */
+	protected Format getDefaultFormat(HSSFCell cell) {
+		return getDefaultFormat(cell.getNumericCellValue());
+	}
+	private Format getDefaultFormat(double cellValue) {
+		// for numeric cells try user supplied default
+		if (defaultNumFormat != null) {
+			return defaultNumFormat;
+			
+		  // otherwise use general format	
+		} else if (isWholeNumber(cellValue)){
+			return generalWholeNumFormat;
+		} else {
+			return generalDecimalNumFormat;
+		}
+	}
+	
+	/**
+	 * Returns the formatted value of an Excel date as a <tt>String</tt> based
+	 * on the cell's <code>DataFormat</code>. i.e. "Thursday, January 02, 2003"
+	 * , "01/02/2003" , "02-Jan" , etc.
+	 * 
+	 * @param cell The cell
+	 * @return a formatted date string
+	 */      
+    protected String getFormattedDateString(HSSFCell cell) {
+    	Format dateFormat = getFormat(cell);
+    	Date d = cell.getDateCellValue();
+    	if (dateFormat != null) {
+    		return dateFormat.format(d);
+    	} else {
+    		return d.toString();
+    	}
+    }
+    
+	/**
+	 * Returns the formatted value of an Excel number as a <tt>String</tt>
+	 * based on the cell's <code>DataFormat</code>. Supported formats include
+	 * currency, percents, decimals, phone number, SSN, etc.:
+	 * "61.54%", "$100.00", "(800) 555-1234".
+	 * 
+	 * @param cell The cell
+	 * @return a formatted number string
+	 */ 
+    protected String getFormattedNumberString(HSSFCell cell) {
+    	
+    	Format numberFormat = getFormat(cell);
+    	double d = cell.getNumericCellValue();
+    	if (numberFormat != null) {
+    		return numberFormat.format(new Double(d));
+    	} else {
+    		return String.valueOf(d);
+    	} 	
+    }
+
+    /**
+     * Formats the given raw cell value, based on the supplied
+     *  format index and string, according to excel style rules.
+     * @see #formatCellValue(HSSFCell)
+     */
+    public String formatRawCellContents(double value, int formatIndex, String formatString) {
+    	// Is it a date?
+    	if(HSSFDateUtil.isADateFormat(formatIndex,formatString) &&
+    			HSSFDateUtil.isValidExcelDate(value)) {
+    		
+        	Format dateFormat = getFormat(value, formatIndex, formatString);
+        	Date d = HSSFDateUtil.getJavaDate(value);
+        	if (dateFormat != null) {
+        		return dateFormat.format(d);
+        	} else {
+        		return d.toString();
+        	}
+    	} else {
+    		// Number
+        	Format numberFormat = getFormat(value, formatIndex, formatString);
+        	if (numberFormat != null) {
+        		return numberFormat.format(new Double(value));
+        	} else {
+        		return String.valueOf(value);
+        	} 	
+    	}
+    }
+    
+	/**
+	 * <p>
+	 * Returns the formatted value of a cell as a <tt>String</tt> regardless
+	 * of the cell type. If the Excel format pattern cannot be parsed then the
+	 * cell value will be formatted using a default format. 
+	 * </p>
+	 * <p>When passed a null or blank cell, this method will return an empty
+	 * String (""). Formulas in formula type cells will not be evaluated.
+	 * </p>
+	 * 
+	 * @param cell The cell
+	 * @return the formatted cell value as a String
+	 */
+	public String formatCellValue(HSSFCell cell) {
+		return formatCellValue(cell, null);
+	}
+    
+	/**
+	 * <p>
+	 * Returns the formatted value of a cell as a <tt>String</tt> regardless
+	 * of the cell type. If the Excel format pattern cannot be parsed then the
+	 * cell value will be formatted using a default format. 
+	 * </p>
+	 * <p>When passed a null or blank cell, this method will return an empty
+	 * String (""). Formula cells will be evaluated using the given
+	 * {@link HSSFFormulaEvaluator} if the evaluator is non-null. If the
+	 * evaluator is null, then the formula String will be returned. The caller
+	 * is responsible for setting the currentRow on the evaluator, otherwise an
+	 * IllegalArgumentException may be thrown.
+	 *</p>
+	 * 
+	 * @param cell The cell
+	 * @param evaluator The HSSFFormulaEvaluator (can be null)
+	 * @return a string value of the cell
+	 * @throws IllegalArgumentException if cell type is <code>
+	 * HSSFCell.CELL_TYPE_FORMULA</code> <b>and</b> evaluator is not null
+	 * <b>and</b> the evlaluator's currentRow has not been set.
+	 */
+	public String formatCellValue(HSSFCell cell, 
+			HSSFFormulaEvaluator evaluator) throws IllegalArgumentException {
+
+		String value = "";
+		if (cell == null) {
+			return value;
+		}
+		
+		int cellType = cell.getCellType();
+		if (evaluator != null && cellType == HSSFCell.CELL_TYPE_FORMULA) {
+			try {
+				cellType = evaluator.evaluateFormulaCell(cell);
+			} catch (Throwable t) {
+				throw new IllegalArgumentException("Did you forget to set the current" +
+						" row on the HSSFFormulaEvaluator?", t);
+			}
+		}		
+		switch (cellType)
+        {
+            case HSSFCell.CELL_TYPE_FORMULA :
+            	// should only occur if evaluator is null
+            	value = cell.getCellFormula();
+                break;
+
+            case HSSFCell.CELL_TYPE_NUMERIC :
+            	
+            	if (HSSFDateUtil.isCellDateFormatted(cell)) {
+            		value = getFormattedDateString(cell);
+            	} else {
+            		value = getFormattedNumberString(cell);
+            	}
+                break;
+
+            case HSSFCell.CELL_TYPE_STRING :
+                value = cell.getRichStringCellValue().getString();
+                break;
+                
+            case HSSFCell.CELL_TYPE_BOOLEAN :
+            	value = String.valueOf(cell.getBooleanCellValue());
+        }
+		return value;
+	}
+		
+	
+	/**
+	 * <p>
+	 * Sets a default number format to be used when the Excel format cannot be
+	 * parsed successfully. <b>Note:</b> This is a fall back for when an error
+	 * occurs while parsing an Excel number format pattern. This will not
+	 * affect cells with the <em>General</em> format.
+	 * </p>
+	 * <p>
+	 * The value that will be passed to the Format's format method (specified
+	 * by <code>java.text.Format#format</code>) will be a double value from a
+	 * numeric cell. Therefore the code in the format method should expect a
+	 * <code>Number</code> value.
+	 * </p>
+	 * 
+	 * @param format A Format instance to be used as a default
+	 * @see java.text.Format#format
+	 */
+	public void setDefaultNumberFormat(Format format) {
+		Iterator itr = formats.entrySet().iterator();
+		while(itr.hasNext()) {
+			Map.Entry entry = (Map.Entry)itr.next();
+			if (entry.getValue() == generalDecimalNumFormat
+					|| entry.getValue() == generalWholeNumFormat) {
+				entry.setValue(format);
+			}
+		}
+		defaultNumFormat = format;
+	}	
+	
+	/**
+	 * Adds a new format to the available formats.
+	 * <p>
+	 * The value that will be passed to the Format's format method (specified
+	 * by <code>java.text.Format#format</code>) will be a double value from a
+	 * numeric cell. Therefore the code in the format method should expect a
+	 * <code>Number</code> value.
+	 * </p>	 
+	 * @param excelFormatStr The data format string
+	 * @param format A Format instance
+	 */
+	public void addFormat(String excelFormatStr, Format format) {
+		formats.put(excelFormatStr, format);
+	}
+
+	// Some custom formats
+	
+	/**
+	 * Format class for Excel's SSN format. This class mimics Excel's built-in
+	 * SSN formatting.
+	 * 
+	 * @author James May
+	 */
+	static class SSNFormat extends Format {
+		private DecimalFormat df;
+		
+		/** Constructor */
+		public SSNFormat() {
+			df = new DecimalFormat("000000000");
+			df.setParseIntegerOnly(true);
+		}
+		
+		/** Format a number as an SSN */
+		public String format(Number num) {
+			String result = df.format(num);
+			StringBuffer sb = new StringBuffer();
+			sb.append(result.substring(0, 3)).append('-');
+			sb.append(result.substring(3, 5)).append('-');
+			sb.append(result.substring(5, 9));
+			return sb.toString();
+		}
+		
+		public StringBuffer format(Object obj, StringBuffer toAppendTo,
+				FieldPosition pos) {
+			return toAppendTo.append(format((Number)obj));
+		}
+		
+		public Object parseObject(String source, ParsePosition pos) {
+			return df.parseObject(source, pos);
+		}
+	}
+	
+	/**
+	 * Format class for Excel Zip + 4 format. This class mimics Excel's
+	 * built-in formatting for Zip + 4.
+	 * @author James May
+	 */	
+	static class ZipPlusFourFormat extends Format {
+		private DecimalFormat df;
+		
+		/** Constructor */
+		public ZipPlusFourFormat() {
+			df = new DecimalFormat("000000000");
+			df.setParseIntegerOnly(true);
+		}
+		
+		/** Format a number as Zip + 4 */
+		public String format(Number num) {
+			String result = df.format(num);
+			StringBuffer sb = new StringBuffer();
+			sb.append(result.substring(0, 5)).append('-');
+			sb.append(result.substring(5, 9));
+			return sb.toString();
+		}
+		
+		public StringBuffer format(Object obj, StringBuffer toAppendTo,
+				FieldPosition pos) {
+			return toAppendTo.append(format((Number)obj));
+		}
+		
+		public Object parseObject(String source, ParsePosition pos) {
+			return df.parseObject(source, pos);
+		}
+	}	
+	
+	/**
+	 * Format class for Excel phone number format. This class mimics Excel's
+	 * built-in phone number formatting.
+	 * @author James May
+	 */	
+	static class PhoneFormat extends Format {
+		private DecimalFormat df;
+
+		/** Constructor */
+		public PhoneFormat() {
+			df = new DecimalFormat("##########");
+			df.setParseIntegerOnly(true);
+		}
+		
+		/** Format a number as a phone number */
+		public String format(Number num) {
+			String result = df.format(num);
+			StringBuffer sb = new StringBuffer();
+			String seg1, seg2, seg3;
+			int len = result.length();
+			if (len <= 4) {
+				return result;
+			}
+			
+			seg3 = result.substring(len - 4, len);
+			seg2 = result.substring(Math.max(0, len - 7), len - 4); 
+			seg1 = result.substring(Math.max(0, len - 10), Math.max(0, len - 7));
+			
+			if(seg1 != null && seg1.trim().length() > 0) {
+				sb.append('(').append(seg1).append(") ");
+			}
+			if(seg2 != null && seg2.trim().length() > 0) {
+				sb.append(seg2).append('-'); 
+			}
+			sb.append(seg3);
+			return sb.toString();
+		}
+		
+		public StringBuffer format(Object obj, StringBuffer toAppendTo,
+				FieldPosition pos) {
+			return toAppendTo.append(format((Number)obj));
+		}
+		
+		public Object parseObject(String source, ParsePosition pos) {
+			return df.parseObject(source, pos);
+		}
+	}	
+}
diff --git a/src/java/org/apache/poi/ss/usermodel/DateUtil.java b/src/java/org/apache/poi/ss/usermodel/DateUtil.java
index 0215af0126..08f8dda43b 100644
--- a/src/java/org/apache/poi/ss/usermodel/DateUtil.java
+++ b/src/java/org/apache/poi/ss/usermodel/DateUtil.java
@@ -232,7 +232,7 @@ public class DateUtil
     	//  y m d h s - / , . :
     	// optionally followed by AM/PM
     	// optionally followed by AM/PM
-    	if(fs.matches("^[yYmMdDhHsS\\-/,. :]+[ampAMP]*$")) {
+    	if(fs.matches("^[yYmMdDhHsS\\-/,. :]+[ampAMP/]*$")) {
     		return true;
     	}
     	
diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
index 40e4bd34dd..69937d1034 100644
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
@@ -1363,4 +1363,24 @@ public final class TestBugs extends TestCase {
         HSSFSheet sh = wb.getSheetAt(0);
         for(short i=0; i < 30; i++) sh.autoSizeColumn(i);
      }
+    
+    /**
+     * We used to add too many UncalcRecords to sheets
+     *  with diagrams on. Don't any more
+     */
+    public void test45414() throws Exception {
+    	HSSFWorkbook wb = openSample("WithThreeCharts.xls");
+    	wb.getSheetAt(0).setForceFormulaRecalculation(true);
+    	wb.getSheetAt(1).setForceFormulaRecalculation(false);
+    	wb.getSheetAt(2).setForceFormulaRecalculation(true);
+    	
+    	// Write out and back in again
+    	// This used to break
+    	HSSFWorkbook nwb = writeOutAndReadBack(wb);
+    	
+    	// Check now set as it should be
+    	assertTrue(nwb.getSheetAt(0).getForceFormulaRecalculation());
+    	assertFalse(nwb.getSheetAt(1).getForceFormulaRecalculation());
+    	assertTrue(nwb.getSheetAt(2).getForceFormulaRecalculation());
+    }
 }
diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java
new file mode 100644
index 0000000000..39baedd885
--- /dev/null
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java
@@ -0,0 +1,282 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+
+package org.apache.poi.hssf.usermodel;
+
+import java.text.DecimalFormat;
+import java.text.Format;
+import java.util.Iterator;
+
+import junit.framework.TestCase;
+
+/**
+ * Unit tests for HSSFDataFormatter.java
+ * 
+ * @author James May (james dot may at fmr dot com)
+ *
+ */
+public class TestHSSFDataFormatter extends TestCase {
+
+	HSSFDataFormatter formatter;
+	HSSFWorkbook wb;
+	
+	public TestHSSFDataFormatter() {
+		// create the formatter to test
+		formatter = new HSSFDataFormatter();
+		
+		// create a workbook to test with
+	    wb = new HSSFWorkbook();
+	    HSSFSheet sheet = wb.createSheet();
+	    HSSFDataFormat format = wb.createDataFormat();
+  
+	    // create a row and put some cells in it
+	    HSSFRow row = sheet.createRow((short)0);
+
+	    // date value for July 8 1901 1:19 PM
+	    double dateNum = 555.555;
+	    
+	    //valid date formats -- all should have "Jul" in output
+	    String[] goodDatePatterns = new String[] {
+    		"[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy",
+    		"mmm/d/yy\\ h:mm PM;@",
+    		"mmmm/d/yy\\ h:mm;@",
+    		"mmmm/d;@",
+    		"mmmm/d/yy;@",
+    		"mmm/dd/yy;@",
+    		"[$-409]d\\-mmm;@",
+    		"[$-409]d\\-mmm\\-yy;@",
+    		"[$-409]dd\\-mmm\\-yy;@",
+    		"[$-409]mmm\\-yy;@",
+    		"[$-409]mmmm\\-yy;@",
+    		"[$-409]mmmm\\ d\\,\\ yyyy;@",
+    		"[$-409]mmm/d/yy\\ h:mm:ss;@",
+    		"[$-409]mmmm/d/yy\\ h:mm:ss am;@",
+    		"[$-409]mmmmm;@",
+    		"[$-409]mmmmm\\-yy;@",
+    		"mmmm/d/yyyy;@",
+    		"[$-409]d\\-mmm\\-yyyy;@"
+	    };
+	    
+	    // valid number formats
+	    String[] goodNumPatterns = new String[] {
+	    		"#,##0.0000",
+	    		"#,##0;[Red]#,##0",
+	    		"(#,##0.00_);(#,##0.00)",
+	    		"($#,##0.00_);[Red]($#,##0.00)",
+	    		"$#,##0.00",
+	    		"[$�-809]#,##0.00",
+	    		"[$�-2] #,##0.00",
+	    		"0000.00000%",
+	    		"0.000E+00",
+	    		"0.00E+00",
+	    };
+	    
+	    // invalid date formats -- will throw exception in DecimalFormat ctor
+	    String[] badNumPatterns = new String[] {
+	    		"#,#$'#0.0000",
+	    		"'#','#ABC#0;##,##0",
+	    		"000 '123 4'5'6 000",
+	    		"#''0#0'1#10L16EE"
+	    };	    
+	    
+	    // create cells with good date patterns
+	    for (int i = 0; i < goodDatePatterns.length; i++) {
+	    	HSSFCell cell = row.createCell((short) i);
+	    	cell.setCellValue(dateNum);
+	    	HSSFCellStyle cellStyle = wb.createCellStyle();
+	    	cellStyle.setDataFormat(format.getFormat(goodDatePatterns[i]));
+	    	cell.setCellStyle(cellStyle);
+	    }
+	    row = sheet.createRow(1);
+	    
+	    // create cells with num patterns
+	    for (int i = 0; i < goodNumPatterns.length; i++) {
+	    	HSSFCell cell = row.createCell((short) i);
+	    	cell.setCellValue(-1234567890.12345);
+	    	HSSFCellStyle cellStyle = wb.createCellStyle();
+	    	cellStyle.setDataFormat(format.getFormat(goodNumPatterns[i]));
+	    	cell.setCellStyle(cellStyle);
+	    }
+	    row = sheet.createRow(2);
+	    
+	    // create cells with bad num patterns
+	    for (int i = 0; i < badNumPatterns.length; i++) {
+	    	HSSFCell cell = row.createCell((short) i);
+	    	cell.setCellValue(1234567890.12345);
+	    	HSSFCellStyle cellStyle = wb.createCellStyle();
+	    	cellStyle.setDataFormat(format.getFormat(badNumPatterns[i]));
+	    	cell.setCellStyle(cellStyle);
+	    }
+	    
+	    // Built in formats
+
+	    { // Zip + 4 format
+	    	row = sheet.createRow(3);
+	    	HSSFCell cell = row.createCell((short) 0);
+	    	cell.setCellValue(123456789);
+	    	HSSFCellStyle cellStyle = wb.createCellStyle();
+	    	cellStyle.setDataFormat(format.getFormat("00000-0000"));
+	    	cell.setCellStyle(cellStyle);
+	    }
+	    
+	    { // Phone number format
+	    	row = sheet.createRow(4);
+	    	HSSFCell cell = row.createCell((short) 0);
+	    	cell.setCellValue(5551234567D);
+	    	HSSFCellStyle cellStyle = wb.createCellStyle();
+	    	cellStyle.setDataFormat(format.getFormat("[<=9999999]###-####;(###) ###-####"));
+	    	cell.setCellStyle(cellStyle);
+	    }
+	    
+	    { // SSN format
+	    	row = sheet.createRow(5);
+	    	HSSFCell cell = row.createCell((short) 0);
+	    	cell.setCellValue(444551234);
+	    	HSSFCellStyle cellStyle = wb.createCellStyle();
+	    	cellStyle.setDataFormat(format.getFormat("000-00-0000"));
+	    	cell.setCellStyle(cellStyle);
+	    }
+	    
+	    { // formula cell
+	    	row = sheet.createRow(6);
+	    	HSSFCell cell = row.createCell((short) 0);
+	        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
+	        cell.setCellFormula("SUM(12.25,12.25)/100");
+	    	HSSFCellStyle cellStyle = wb.createCellStyle();
+	    	cellStyle.setDataFormat(format.getFormat("##.00%;"));
+	    	cell.setCellStyle(cellStyle);
+	    }	    
+	}
+
+	/**
+	 * Test getting formatted values from numeric and date cells.
+	 */
+	public void testGetFormattedCellValueHSSFCell() {
+		// Valid date formats -- cell values should be date formatted & not "555.555"
+		HSSFRow row = wb.getSheetAt(0).getRow(0);
+		Iterator it = row.cellIterator();
+		System.out.println("==== VALID DATE FORMATS ====");
+		while (it.hasNext()) {
+			HSSFCell cell = (HSSFCell) it.next();
+			System.out.println(formatter.formatCellValue(cell));
+
+			// should not be equal to "555.555"
+			assertTrue( ! "555.555".equals(formatter.formatCellValue(cell)));
+			
+			// should contain "Jul" in the String
+			assertTrue( formatter.formatCellValue(cell).indexOf("Jul") > -1);
+		}
+
+		// test number formats
+		row = wb.getSheetAt(0).getRow(1);
+		it = row.cellIterator();
+		System.out.println("\n==== VALID NUMBER FORMATS ====");
+		while (it.hasNext()) {
+			HSSFCell cell = (HSSFCell) it.next();
+			System.out.println(formatter.formatCellValue(cell));
+			
+			// should not be equal to "1234567890.12345"
+			assertTrue( ! "1234567890.12345".equals(formatter.formatCellValue(cell)));
+		}
+		
+		// test bad number formats
+		row = wb.getSheetAt(0).getRow(2);
+		it = row.cellIterator();
+		System.out.println("\n==== INVALID NUMBER FORMATS ====");
+		while (it.hasNext()) {
+			HSSFCell cell = (HSSFCell) it.next();
+			System.out.println(formatter.formatCellValue(cell));
+			// should be equal to "1234567890.12345"
+			assertEquals("1234567890.12345", formatter.formatCellValue(cell));
+		}
+		
+		// test Zip+4 format
+		row = wb.getSheetAt(0).getRow(3);
+		HSSFCell cell = row.getCell(0);
+		System.out.println("\n==== ZIP FORMAT ====");
+		System.out.println(formatter.formatCellValue(cell));
+		assertEquals("12345-6789", formatter.formatCellValue(cell));
+		
+		// test phone number format
+		row = wb.getSheetAt(0).getRow(4);
+		cell = row.getCell(0);
+		System.out.println("\n==== PHONE FORMAT ====");
+		System.out.println(formatter.formatCellValue(cell));
+		assertEquals("(555) 123-4567", formatter.formatCellValue(cell));
+		
+		// test SSN format
+		row = wb.getSheetAt(0).getRow(5);
+		cell = row.getCell(0);
+		System.out.println("\n==== SSN FORMAT ====");
+		System.out.println(formatter.formatCellValue(cell));
+		assertEquals("444-55-1234", formatter.formatCellValue(cell));		
+		
+		// null test-- null cell should result in empty String
+		assertEquals(formatter.formatCellValue(null), "");
+		
+		// null test-- null cell should result in empty String
+		assertEquals(formatter.formatCellValue(null), "");		
+		
+	}
+
+	public void testGetFormattedCellValueHSSFCellHSSFFormulaEvaluator() {
+		// test formula format
+		HSSFRow row = wb.getSheetAt(0).getRow(6);
+		HSSFCell cell = row.getCell(0);
+		System.out.println("\n==== FORMULA CELL ====");
+		
+		// first without a formula evaluator
+		System.out.println(formatter.formatCellValue(cell) + "\t (without evaluator)");
+		assertEquals("SUM(12.25,12.25)/100", formatter.formatCellValue(cell));	
+		
+		// now with a formula evaluator		
+		HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb.getSheetAt(0), wb);
+		//! must set current row !
+		evaluator.setCurrentRow(row);
+		System.out.println(formatter.formatCellValue(cell, evaluator) + "\t\t\t (with evaluator)");
+		assertEquals("24.50%", formatter.formatCellValue(cell,evaluator));
+	}
+
+
+
+	/**
+	 * Test using a default number format. The format should be used when a
+	 * format pattern cannot be parsed by DecimalFormat.
+	 */
+	public void testSetDefaultNumberFormat() {
+		HSSFRow row = wb.getSheetAt(0).getRow(2);
+		Iterator it = row.cellIterator();	
+		Format defaultFormat = new DecimalFormat("Balance $#,#00.00 USD;Balance -$#,#00.00 USD");
+		formatter.setDefaultNumberFormat(defaultFormat);
+		double value = 10d;
+		System.out.println("\n==== DEFAULT NUMBER FORMAT ====");
+		while (it.hasNext()) {
+			HSSFCell cell = (HSSFCell) it.next();		
+			cell.setCellValue(cell.getNumericCellValue() * Math.random() / 1000000 - 1000);
+			System.out.println(formatter.formatCellValue(cell));
+			assertTrue(formatter.formatCellValue(cell).startsWith("Balance "));
+			assertTrue(formatter.formatCellValue(cell).endsWith(" USD"));
+		}	
+	}
+
+    public static void main(String [] args) {
+        System.out
+                .println("Testing org.apache.poi.hssf.usermodel.TestHSSFDataFormatter");
+        junit.textui.TestRunner.run(TestHSSFDataFormatter.class);
+    }	
+
+}
diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java
index 4f526b61c0..281d1b1cb0 100644
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java
@@ -273,7 +273,8 @@ public class TestHSSFDateUtil extends TestCase {
                 "yyyy-mm-dd hh:mm:ss", "yyyy/mm/dd HH:MM:SS", 
                 "mm/dd HH:MM", "yy/mmm/dd SS",
                 "mm/dd HH:MM AM", "mm/dd HH:MM am",
-                "mm/dd HH:MM PM", "mm/dd HH:MM pm" 
+                "mm/dd HH:MM PM", "mm/dd HH:MM pm",
+                "m/d/yy h:mm AM/PM"
         };
         for(int i=0; i<formats.length; i++) {
             assertTrue(