Return-Path: X-Original-To: apmail-poi-commits-archive@minotaur.apache.org Delivered-To: apmail-poi-commits-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 4256596DA for ; Tue, 20 Dec 2011 07:21:11 +0000 (UTC) Received: (qmail 50017 invoked by uid 500); 20 Dec 2011 07:21:10 -0000 Delivered-To: apmail-poi-commits-archive@poi.apache.org Received: (qmail 49983 invoked by uid 500); 20 Dec 2011 07:21:10 -0000 Mailing-List: contact commits-help@poi.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@poi.apache.org Delivered-To: mailing list commits@poi.apache.org Received: (qmail 49976 invoked by uid 99); 20 Dec 2011 07:21:10 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 20 Dec 2011 07:21:10 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 20 Dec 2011 07:21:06 +0000 Received: from eris.apache.org (localhost [127.0.0.1]) by eris.apache.org (Postfix) with ESMTP id EA3832388860 for ; Tue, 20 Dec 2011 07:20:44 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1221126 - in /poi/trunk/src: documentation/content/xdocs/status.xml java/org/apache/poi/ss/formula/functions/TextFunction.java java/org/apache/poi/ss/usermodel/DataFormatter.java testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java Date: Tue, 20 Dec 2011 07:20:44 -0000 To: commits@poi.apache.org From: nick@apache.org X-Mailer: svnmailer-1.0.8-patched Message-Id: <20111220072044.EA3832388860@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: nick Date: Tue Dec 20 07:20:44 2011 New Revision: 1221126 URL: http://svn.apache.org/viewvc?rev=1221126&view=rev Log: Inspired by bug #52349 - Merge the logic between the TEXT function and DataFormatter Modified: poi/trunk/src/documentation/content/xdocs/status.xml poi/trunk/src/java/org/apache/poi/ss/formula/functions/TextFunction.java poi/trunk/src/java/org/apache/poi/ss/usermodel/DataFormatter.java poi/trunk/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.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=1221126&r1=1221125&r2=1221126&view=diff ============================================================================== --- poi/trunk/src/documentation/content/xdocs/status.xml (original) +++ poi/trunk/src/documentation/content/xdocs/status.xml Tue Dec 20 07:20:44 2011 @@ -34,6 +34,7 @@ + 52349 - Merge the logic between the TEXT function and DataFormatter 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 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=1221126&r1=1221125&r2=1221126&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 07:20:44 2011 @@ -17,12 +17,6 @@ package org.apache.poi.ss.formula.functions; -import java.text.DateFormat; -import java.text.DecimalFormat; -import java.text.NumberFormat; -import java.text.SimpleDateFormat; -import java.util.Calendar; -import java.util.GregorianCalendar; import org.apache.poi.ss.formula.eval.BoolEval; import org.apache.poi.ss.formula.eval.ErrorEval; import org.apache.poi.ss.formula.eval.EvaluationException; @@ -279,17 +273,13 @@ public abstract class TextFunction imple /** * An implementation of the TEXT 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 - * {@link DataFormatter}, which attempts to replicate the Excel date - * format string. - * - * TODO Merge much of this logic with {@link DataFormatter} + * TEXT returns a number value formatted with the given number formatting string. + * This function is not a complete implementation of the Excel function, but + * handles most of the common cases. All work is passed down to + * {@link DataFormatter} to be done, as this works much the same as the + * display focused work that that does. * * Syntax:
TEXT(value, format_text)
- * */ public static final Function TEXT = new Fixed2ArgFunction() { @@ -302,57 +292,13 @@ public abstract class TextFunction imple } catch (EvaluationException e) { return e.getErrorEval(); } - if (s1.matches("[\\d,\\#,\\.,\\$,\\,]+")) { - NumberFormat formatter = new DecimalFormat(s1); - return new StringEval(formatter.format(s0)); - } else if (s1.indexOf("/") == s1.lastIndexOf("/") && s1.indexOf("/") >=0 && !s1.contains("-")) { - double wholePart = Math.floor(s0); - double decPart = s0 - wholePart; - if (wholePart * decPart == 0) { - return new StringEval("0"); - } - String[] parts = s1.split(" "); - String[] fractParts; - if (parts.length == 2) { - fractParts = parts[1].split("/"); - } else { - fractParts = s1.split("/"); - } - - if (fractParts.length == 2) { - double minVal = 1.0; - double currDenom = Math.pow(10 , fractParts[1].length()) - 1d; - double currNeum = 0; - for (int i = (int)(Math.pow(10, fractParts[1].length())- 1d); i > 0; i--) { - for(int i2 = (int)(Math.pow(10, fractParts[1].length())- 1d); i2 > 0; i2--){ - if (minVal >= Math.abs((double)i2/(double)i - decPart)) { - currDenom = i; - currNeum = i2; - minVal = Math.abs((double)i2/(double)i - decPart); - } - } - } - NumberFormat neumFormatter = new DecimalFormat(fractParts[0]); - NumberFormat denomFormatter = new DecimalFormat(fractParts[1]); - if (parts.length == 2) { - NumberFormat wholeFormatter = new DecimalFormat(parts[0]); - String result = wholeFormatter.format(wholePart) + " " + neumFormatter.format(currNeum) + "/" + denomFormatter.format(currDenom); - return new StringEval(result); - } else { - String result = neumFormatter.format(currNeum + (currDenom * wholePart)) + "/" + denomFormatter.format(currDenom); - return new StringEval(result); - } - } else { - return ErrorEval.VALUE_INVALID; - } - } else { - try { - // 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; - } + + try { + // Ask DataFormatter to handle the String for us + String formattedStr = formatter.formatRawCellContents(s0, -1, s1); + return new StringEval(formattedStr); + } catch (Exception e) { + return ErrorEval.VALUE_INVALID; } } }; Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/DataFormatter.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/DataFormatter.java?rev=1221126&r1=1221125&r2=1221126&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/ss/usermodel/DataFormatter.java (original) +++ poi/trunk/src/java/org/apache/poi/ss/usermodel/DataFormatter.java Tue Dec 20 07:20:44 2011 @@ -16,13 +16,28 @@ ==================================================================== */ package org.apache.poi.ss.usermodel; -import java.util.regex.Pattern; -import java.util.regex.Matcher; -import java.util.*; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.math.RoundingMode; -import java.text.*; +import java.text.DateFormatSymbols; +import java.text.DecimalFormat; +import java.text.DecimalFormatSymbols; +import java.text.FieldPosition; +import java.text.Format; +import java.text.NumberFormat; +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.Locale; +import java.util.Map; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +import org.apache.poi.ss.formula.eval.NotImplementedException; /** * DataFormatter contains methods for formatting the value stored in an @@ -257,7 +272,7 @@ public class DataFormatter { if (emulateCsv && cellValue == 0.0 && formatStr.contains("#") && !formatStr.contains("0")) { formatStr = formatStr.replaceAll("#", ""); } - + // See if we already have it cached Format format = formats.get(formatStr); if (format != null) { @@ -332,6 +347,13 @@ public class DataFormatter { DateUtil.isValidExcelDate(cellValue)) { return createDateFormat(formatStr, cellValue); } + + // Excel supports fractions in format strings, which Java doesn't + if (formatStr.indexOf("/") == formatStr.lastIndexOf("/") && + formatStr.indexOf("/") >= 0 && !formatStr.contains("-")) { + return new FractionFormat(formatStr); + } + if (numPattern.matcher(formatStr).find()) { return createNumberFormat(formatStr, cellValue); } @@ -946,6 +968,67 @@ public class DataFormatter { return df.parseObject(source, pos); } } + + /** + * Format class that handles Excel style fractions, such as "# #/#" and "#/###" + */ + @SuppressWarnings("serial") + private static final class FractionFormat extends Format { + private final String str; + public FractionFormat(String s) { + str = s; + } + + public String format(Number num) { + double wholePart = Math.floor(num.doubleValue()); + double decPart = num.doubleValue() - wholePart; + if (wholePart * decPart == 0) { + return "0"; + } + String[] parts = str.split(" "); + String[] fractParts; + if (parts.length == 2) { + fractParts = parts[1].split("/"); + } else { + fractParts = str.split("/"); + } + + if (fractParts.length == 2) { + double minVal = 1.0; + double currDenom = Math.pow(10 , fractParts[1].length()) - 1d; + double currNeum = 0; + for (int i = (int)(Math.pow(10, fractParts[1].length())- 1d); i > 0; i--) { + for(int i2 = (int)(Math.pow(10, fractParts[1].length())- 1d); i2 > 0; i2--){ + if (minVal >= Math.abs((double)i2/(double)i - decPart)) { + currDenom = i; + currNeum = i2; + minVal = Math.abs((double)i2/(double)i - decPart); + } + } + } + NumberFormat neumFormatter = new DecimalFormat(fractParts[0]); + NumberFormat denomFormatter = new DecimalFormat(fractParts[1]); + if (parts.length == 2) { + NumberFormat wholeFormatter = new DecimalFormat(parts[0]); + String result = wholeFormatter.format(wholePart) + " " + neumFormatter.format(currNeum) + "/" + denomFormatter.format(currDenom); + return result; + } else { + String result = neumFormatter.format(currNeum + (currDenom * wholePart)) + "/" + denomFormatter.format(currDenom); + return result; + } + } else { + throw new IllegalArgumentException("Fraction must have 2 parts, found " + fractParts.length + " for fraction format " + str); + } + } + + public StringBuffer format(Object obj, StringBuffer toAppendTo, FieldPosition pos) { + return toAppendTo.append(format((Number)obj)); + } + + public Object parseObject(String source, ParsePosition pos) { + throw new NotImplementedException("Reverse parsing not supported"); + } + } /** * Format class that does nothing and always returns a constant string. Modified: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java?rev=1221126&r1=1221125&r2=1221126&view=diff ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java (original) +++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java Tue Dec 20 07:20:44 2011 @@ -162,6 +162,18 @@ public class TestDataFormatter extends T } /** + * Test that we correctly handle fractions in the + * format string, eg # #/# + */ + public void testFractions() { + DataFormatter dfUS = new DataFormatter(Locale.US); + + assertEquals("321 1/3", dfUS.formatRawCellContents(321.321, -1, "# #/#")); + assertEquals("321 26/81", dfUS.formatRawCellContents(321.321, -1, "# #/##")); + assertEquals("26027/81", dfUS.formatRawCellContents(321.321, -1, "#/##")); + } + + /** * Test that _x (blank with the space taken by "x") * and *x (fill to the column width with "x"s) are * correctly ignored by us. --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org For additional commands, e-mail: commits-help@poi.apache.org