From 8328fca856d6b8fc3665f8e297255c3dd5bbf177 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Sun, 20 Jul 2008 17:44:06 +0000 Subject: [PATCH] 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 --- build.xml | 26 - .../hssf/usermodel/contrib/HSSFCellUtil.java | 255 +++++-- .../usermodel/contrib/HSSFRegionUtil.java | 68 +- src/documentation/content/xdocs/changes.xml | 4 + src/documentation/content/xdocs/status.xml | 4 + .../FormatTrackingHSSFListener.java | 31 +- src/java/org/apache/poi/hssf/model/Sheet.java | 23 +- .../apache/poi/hssf/usermodel/HSSFCell.java | 12 +- .../poi/hssf/usermodel/HSSFDataFormatter.java | 703 ++++++++++++++++++ .../org/apache/poi/ss/usermodel/DateUtil.java | 2 +- .../apache/poi/hssf/usermodel/TestBugs.java | 20 + .../hssf/usermodel/TestHSSFDataFormatter.java | 282 +++++++ .../poi/hssf/usermodel/TestHSSFDateUtil.java | 3 +- 13 files changed, 1270 insertions(+), 163 deletions(-) create mode 100644 src/java/org/apache/poi/hssf/usermodel/HSSFDataFormatter.java create mode 100644 src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java 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. - - - - - - - - - - - - - - - - - @@ -208,9 +191,6 @@ under the License. - - - @@ -351,9 +331,6 @@ under the License. - - - @@ -371,9 +348,6 @@ under the License. description="Fetches needed JAR files from the Internet"> - - - 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 α 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( "α", "\u03B1" ); - unicodeMappings.put( "β", "\u03B2" ); - unicodeMappings.put( "γ", "\u03B3" ); - unicodeMappings.put( "δ", "\u03B4" ); - unicodeMappings.put( "ε", "\u03B5" ); - unicodeMappings.put( "ζ", "\u03B6" ); - unicodeMappings.put( "η", "\u03B7" ); - unicodeMappings.put( "θ", "\u03B8" ); - unicodeMappings.put( "ι", "\u03B9" ); - unicodeMappings.put( "κ", "\u03BA" ); - unicodeMappings.put( "λ", "\u03BB" ); - unicodeMappings.put( "μ", "\u03BC" ); - unicodeMappings.put( "ν", "\u03BD" ); - unicodeMappings.put( "ξ", "\u03BE" ); - unicodeMappings.put( "ο", "\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 @@ Created a common interface for handling Excel files, irrespective of if they are .xls or .xlsx + 45404 - New class, hssf.usermodel.HSSFDataFormatter, for formatting numbers and dates in the same way that Excel does + 45414 - Don't add too many UncalcedRecords to sheets with charts in them + 45398 - Support detecting date formats containing "am/pm" as date times + 45410 - Removed dependency from contrib on commons beanutils,collections and lang New helper, HSSFOptimiser, which handles removing duplicated font and style records, to avoid going over the limits in Excel 45322 - Fixed NPE in HSSFSheet.autoSizeColumn() when cell number format was not found 45380 - Missing return keyword in ArrayPtg.toFormulaString() 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 @@ Created a common interface for handling Excel files, irrespective of if they are .xls or .xlsx + 45404 - New class, hssf.usermodel.HSSFDataFormatter, for formatting numbers and dates in the same way that Excel does + 45414 - Don't add too many UncalcedRecords to sheets with charts in them + 45398 - Support detecting date formats containing "am/pm" as date times + 45410 - Removed dependency from contrib on commons beanutils,collections and lang New helper, HSSFOptimiser, which handles removing duplicated font and style records, to avoid going over the limits in Excel 45322 - Fixed NPE in HSSFSheet.autoSizeColumn() when cell number format was not found 45380 - Missing return keyword in ArrayPtg.toFormulaString() 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. + *

+ * 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 before or after the number pattern to form a + * prefix or suffix. + *

+ *

+ * For example the Excel pattern "$#,##0.00 "USD"_);($#,##0.00 "USD")" + * will be correctly formatted as "$1,000.00 USD" or "($1,000.00 USD)". + * However the pattern "00-00-00" 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 HSSFDataFormatter.addFormat(String,Format). The following + * custom formats are already provided by this class: + *

+ * 
+ * 
  • SSN "000-00-0000"
    • 
+ *     
  • Phone Number "(###) ###-####"
    • 
+ *     
  • Zip plus 4 "00000-0000"
    • 
+ * 

+ *
+ *

+ * 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 + * HSSFDataFormatter.setDefaultNumberFormat(Format). Note: 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; + + /** General format for whole numbers. */ + protected Format generalWholeNumFormat; + + /** General 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 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 null if the any of the + * following is true: + *

    + *
  • the cell's style is null
    • + *
  • the style's data format string is null or empty
    • + *
  • the format string cannot be recognized as either a number or date
    • + *
+ * + * @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 -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 String based + * on the cell's DataFormat. 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 String + * based on the cell's DataFormat. 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); + } + } + } + + /** + *

+ * Returns the formatted value of a cell as a String regardless + * of the cell type. If the Excel format pattern cannot be parsed then the + * cell value will be formatted using a default format. + *

+ *

When passed a null or blank cell, this method will return an empty + * String (""). Formulas in formula type cells will not be evaluated. + *

+ * + * @param cell The cell + * @return the formatted cell value as a String + */ + public String formatCellValue(HSSFCell cell) { + return formatCellValue(cell, null); + } + + /** + *

+ * Returns the formatted value of a cell as a String regardless + * of the cell type. If the Excel format pattern cannot be parsed then the + * cell value will be formatted using a default format. + *

+ *

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. + *

+ * + * @param cell The cell + * @param evaluator The HSSFFormulaEvaluator (can be null) + * @return a string value of the cell + * @throws IllegalArgumentException if cell type is + * HSSFCell.CELL_TYPE_FORMULA and evaluator is not null + * and 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; + } + + + /** + *

+ * Sets a default number format to be used when the Excel format cannot be + * parsed successfully. Note: This is a fall back for when an error + * occurs while parsing an Excel number format pattern. This will not + * affect cells with the General format. + *

+ *

+ * The value that will be passed to the Format's format method (specified + * by java.text.Format#format) will be a double value from a + * numeric cell. Therefore the code in the format method should expect a + * Number value. + *

+ * + * @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. + *

+ * The value that will be passed to the Format's format method (specified + * by java.text.Format#format) will be a double value from a + * numeric cell. Therefore the code in the format method should expect a + * Number value. + *

+ * @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