From b3709bbd36fb91e1680db0ac01c473e9502e8c45 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Tue, 20 Dec 2011 06:58:01 +0000 Subject: [PATCH] Fix bug #52349 - Correctly support excel style date format strings in the TEXT function git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1221123 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/status.xml | 1 + .../ss/formula/functions/TextFunction.java | 21 ++++---- .../poi/ss/formula/functions/TestText.java | 51 ++++++++++++------- 3 files changed, 45 insertions(+), 28 deletions(-) diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 6f49c59b98..eead9ffc07 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ + 52349 - Correctly support excel style date format strings in the TEXT function 52369 - XSSFExcelExtractor should format numeric cells based on the format strings applied to them 52369 - Event based XSSF parsing should handle formatting of formula values in XSSFSheetXMLHandler 52348 - Avoid exception when creating cell style in a workbook that has an empty xf table diff --git a/src/java/org/apache/poi/ss/formula/functions/TextFunction.java b/src/java/org/apache/poi/ss/formula/functions/TextFunction.java index 86e1e32687..dc75b03401 100644 --- a/src/java/org/apache/poi/ss/formula/functions/TextFunction.java +++ b/src/java/org/apache/poi/ss/formula/functions/TextFunction.java @@ -30,6 +30,7 @@ import org.apache.poi.ss.formula.eval.NumberEval; import org.apache.poi.ss.formula.eval.OperandResolver; import org.apache.poi.ss.formula.eval.StringEval; import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.usermodel.DataFormatter; /** * @author Amol S. Deshmukh < amolweb at ya hoo dot com > @@ -37,8 +38,8 @@ import org.apache.poi.ss.formula.eval.ValueEval; * @author Stephen Wolke (smwolke at geistig.com) */ public abstract class TextFunction implements Function { - - protected static final String EMPTY_STRING = ""; + protected static final DataFormatter formatter = new DataFormatter(); + protected static final String EMPTY_STRING = ""; protected static final String evaluateStringArg(ValueEval eval, int srcRow, int srcCol) throws EvaluationException { ValueEval ve = OperandResolver.getSingleValue(eval, srcRow, srcCol); @@ -281,8 +282,11 @@ public abstract class TextFunction implements Function { * TEXT returns a number value formatted with the given * number formatting string. This function is not a complete implementation of * the Excel function. This function implements decimal formatting - * with the Java class DecimalFormat. For date formatting this function uses - * the SimpleDateFormat class.

+ * with the Java class DecimalFormat. For date formatting, this function uses + * {@link DataFormatter}, which attempts to replicate the Excel date + * format string. + * + * TODO Merge much of this logic with {@link DataFormatter} * * Syntax:
TEXT(value, format_text)
* @@ -343,12 +347,9 @@ public abstract class TextFunction implements Function { } } else { try { - DateFormat dateFormatter = new SimpleDateFormat(s1); - Calendar cal = new GregorianCalendar(1899, 11, 30, 0, 0, 0); - cal.add(Calendar.DATE, (int)Math.floor(s0)); - double dayFraction = s0 - Math.floor(s0); - cal.add(Calendar.MILLISECOND, (int) Math.round(dayFraction * 24 * 60 * 60 * 1000)); - return new StringEval(dateFormatter.format(cal.getTime())); + // Ask DataFormatter to handle the Date string for us + String formattedDate = formatter.formatRawCellContents(s0, -1, s1); + return new StringEval(formattedDate); } catch (Exception e) { return ErrorEval.VALUE_INVALID; } diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestText.java b/src/testcases/org/apache/poi/ss/formula/functions/TestText.java index 8ff0bbfa92..7b545da394 100644 --- a/src/testcases/org/apache/poi/ss/formula/functions/TestText.java +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestText.java @@ -92,22 +92,37 @@ public final class TestText extends TestCase { assertEquals(testResult.toString(), result.toString()); } - public void testTextWithDateFormatSecondArg() { - - ValueEval numArg = new NumberEval(321.321); - ValueEval formatArg = new StringEval("dd:MM:yyyy hh:mm:ss"); - ValueEval[] args = { numArg, formatArg }; - ValueEval result = T.TEXT.evaluate(args, -1, (short)-1); - ValueEval testResult = new StringEval("16:11:1900 07:42:14"); - assertEquals(testResult.toString(), result.toString()); - - // this line is intended to compute how "November" would look like in the current locale - String november = new SimpleDateFormat("MMMM").format(new GregorianCalendar(2010,10,15).getTime()); - - formatArg = new StringEval("MMMM dd, yyyy"); - args[1] = formatArg; - result = T.TEXT.evaluate(args, -1, (short)-1); - testResult = new StringEval(november + " 16, 1900"); - assertEquals(testResult.toString(), result.toString()); - } + public void testTextWithDateFormatSecondArg() { + // Test with Java style M=Month + ValueEval numArg = new NumberEval(321.321); + ValueEval formatArg = new StringEval("dd:MM:yyyy hh:mm:ss"); + ValueEval[] args = { numArg, formatArg }; + ValueEval result = T.TEXT.evaluate(args, -1, (short)-1); + ValueEval testResult = new StringEval("16:11:1900 07:42:14"); + assertEquals(testResult.toString(), result.toString()); + + // Excel also supports "m before h is month" + formatArg = new StringEval("dd:mm:yyyy hh:mm:ss"); + args[1] = formatArg; + result = T.TEXT.evaluate(args, -1, (short)-1); + testResult = new StringEval("16:11:1900 07:42:14"); + assertEquals(testResult.toString(), result.toString()); + + // this line is intended to compute how "November" would look like in the current locale + String november = new SimpleDateFormat("MMMM").format(new GregorianCalendar(2010,10,15).getTime()); + + // Again with Java style + formatArg = new StringEval("MMMM dd, yyyy"); + args[1] = formatArg; + result = T.TEXT.evaluate(args, -1, (short)-1); + testResult = new StringEval(november + " 16, 1900"); + assertEquals(testResult.toString(), result.toString()); + + // And Excel style + formatArg = new StringEval("mmmm dd, yyyy"); + args[1] = formatArg; + result = T.TEXT.evaluate(args, -1, (short)-1); + testResult = new StringEval(november + " 16, 1900"); + assertEquals(testResult.toString(), result.toString()); + } } -- 2.39.5