You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ni...@apache.org on 2011/12/20 07:58:01 UTC
svn commit: r1221123 - in /poi/trunk/src:
documentation/content/xdocs/status.xml
java/org/apache/poi/ss/formula/functions/TextFunction.java
testcases/org/apache/poi/ss/formula/functions/TestText.java
Author: nick
Date: Tue Dec 20 06:58:01 2011
New Revision: 1221123
URL: http://svn.apache.org/viewvc?rev=1221123&view=rev
Log:
Fix bug #52349 - Correctly support excel style date format strings in the TEXT function
Modified:
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/ss/formula/functions/TextFunction.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestText.java
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=1221123&r1=1221122&r2=1221123&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Tue Dec 20 06:58:01 2011
@@ -34,6 +34,7 @@
<changes>
<release version="3.8-beta6" date="2012-??-??">
+ <action dev="poi-developers" type="fix">52349 - Correctly support excel style date format strings in the TEXT function</action>
<action dev="poi-developers" type="fix">52369 - XSSFExcelExtractor should format numeric cells based on the format strings applied to them</action>
<action dev="poi-developers" type="fix">52369 - Event based XSSF parsing should handle formatting of formula values in XSSFSheetXMLHandler</action>
<action dev="poi-developers" type="fix">52348 - Avoid exception when creating cell style in a workbook that has an empty xf table</action>
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/TextFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/TextFunction.java?rev=1221123&r1=1221122&r2=1221123&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/TextFunction.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/TextFunction.java Tue Dec 20 06:58:01 2011
@@ -30,6 +30,7 @@ import org.apache.poi.ss.formula.eval.Nu
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.Va
* @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 imple
* 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.<p/>
+ * 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}
*
* <b>Syntax<b>:<br/> <b>TEXT</b>(<b>value</b>, <b>format_text</b>)<br/>
*
@@ -343,12 +347,9 @@ public abstract class TextFunction imple
}
} 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;
}
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestText.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestText.java?rev=1221123&r1=1221122&r2=1221123&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestText.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestText.java Tue Dec 20 06:58:01 2011
@@ -92,22 +92,37 @@ public final class TestText extends Test
assertEquals(testResult.toString(), result.toString());
}
- public void testTextWithDateFormatSecondArg() {
+ 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());
- 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());
+ // 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());
- }
+ // 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());
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org