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 2015/10/25 17:13:16 UTC
svn commit: r1710459 - in /poi/trunk/src:
java/org/apache/poi/ss/format/CellFormat.java
java/org/apache/poi/ss/usermodel/DataFormatter.java
java/org/apache/poi/ss/util/DateFormatConverter.java
testcases/org/apache/poi/ss/format/TestCellFormat.java
Author: nick
Date: Sun Oct 25 16:13:16 2015
New Revision: 1710459
URL: http://svn.apache.org/viewvc?rev=1710459&view=rev
Log:
Mention about Locale definitions in Excel cell data format strings
Modified:
poi/trunk/src/java/org/apache/poi/ss/format/CellFormat.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/DataFormatter.java
poi/trunk/src/java/org/apache/poi/ss/util/DateFormatConverter.java
poi/trunk/src/testcases/org/apache/poi/ss/format/TestCellFormat.java
Modified: poi/trunk/src/java/org/apache/poi/ss/format/CellFormat.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/format/CellFormat.java?rev=1710459&r1=1710458&r2=1710459&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/format/CellFormat.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/format/CellFormat.java Sun Oct 25 16:13:16 2015
@@ -33,6 +33,7 @@ import org.apache.poi.ss.usermodel.Condi
import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
+import org.apache.poi.ss.util.DateFormatConverter;
/**
* Format a value according to the standard Excel behavior. This "standard" is
@@ -64,6 +65,11 @@ import org.apache.poi.ss.usermodel.DateU
* fourth part (example: text in the cell's usual color, with the text value
* surround by brackets). </dl>
* <p/>
+ * A given format part may specify a given Locale, by including something
+ * like <tt>[$$-409]</tt> or <tt>[$\u00A3-809]</tt> or <tt>[$-40C]</tt>. These
+ * are (currently) largely ignored. You can use {@link DateFormatConverter}
+ * to look these up into Java Locales if desired.
+ * <p/>
* In addition to these, there is a general format that is used when no format
* is specified. This formatting is presented by the {@link #GENERAL_FORMAT}
* object.
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=1710459&r1=1710458&r2=1710459&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 Sun Oct 25 16:13:16 2015
@@ -42,6 +42,7 @@ import java.util.regex.Pattern;
import org.apache.poi.ss.format.CellFormat;
import org.apache.poi.ss.format.CellFormatResult;
+import org.apache.poi.ss.util.DateFormatConverter;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.util.LocaleUtil;
import org.apache.poi.util.POILogFactory;
@@ -105,6 +106,15 @@ import org.apache.poi.util.POILogger;
* <li>simulate Excel's handling of a format string of all # when the value is 0.
* Excel will output "", <code>DataFormatter</code> will output "0".
* </ul>
+ * <p>
+ * Some formats are automatically "localised" by Excel, eg show as mm/dd/yyyy when
+ * loaded in Excel in some Locales but as dd/mm/yyyy in others. These are always
+ * returned in the "default" (US) format, as stored in the file.
+ * Some format strings request an alternate locale, eg
+ * <code>[$-809]d/m/yy h:mm AM/PM</code> which explicitly requests UK locale.
+ * These locale directives are (currently) ignored.
+ * You can use {@link DateFormatConverter} to do some of this localisation if
+ * you need it.
*/
public class DataFormatter implements Observer {
private static final String defaultFractionWholePartFormat = "#";
@@ -1129,13 +1139,17 @@ public class DataFormatter implements Ob
* Constant, non-cachable wrapper around a {@link CellFormatResult}
*/
@SuppressWarnings("serial")
- private static final class CellFormatResultWrapper extends Format {
+ private final class CellFormatResultWrapper extends Format {
private final CellFormatResult result;
private CellFormatResultWrapper(CellFormatResult result) {
this.result = result;
}
public StringBuffer format(Object obj, StringBuffer toAppendTo, FieldPosition pos) {
- return toAppendTo.append(result.text);
+ if (emulateCsv) {
+ return toAppendTo.append(result.text);
+ } else {
+ return toAppendTo.append(result.text.trim());
+ }
}
public Object parseObject(String source, ParsePosition pos) {
return null; // Not supported
Modified: poi/trunk/src/java/org/apache/poi/ss/util/DateFormatConverter.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/util/DateFormatConverter.java?rev=1710459&r1=1710458&r2=1710459&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/util/DateFormatConverter.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/util/DateFormatConverter.java Sun Oct 25 16:13:16 2015
@@ -42,10 +42,9 @@ import org.apache.poi.util.POILogger;
* cellStyle.setDataFormat(poiFormat.getFormat(excelFormatPattern));
* cell.setCellValue(new Date());
* cell.setCellStyle(cellStyle); // formats date as '2012\u5e743\u670817\u65e5'
- *
* </code></pre>
*
- *
+ * TODO Generalise this for all Excel format strings
*/
public class DateFormatConverter {
private static POILogger logger = POILogFactory.getLogger(DateFormatConverter.class);
Modified: poi/trunk/src/testcases/org/apache/poi/ss/format/TestCellFormat.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/format/TestCellFormat.java?rev=1710459&r1=1710458&r2=1710459&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/format/TestCellFormat.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/format/TestCellFormat.java Sun Oct 25 16:13:16 2015
@@ -36,6 +36,7 @@ import org.apache.poi.ss.usermodel.Workb
import org.apache.poi.util.LocaleUtil;
import org.junit.AfterClass;
import org.junit.BeforeClass;
+import org.junit.Ignore;
import org.junit.Test;
public class TestCellFormat {
@@ -923,4 +924,40 @@ public class TestCellFormat {
wb.close();
}
}
+
+ @Test
+ @Ignore("TODO") // TODO
+ public void testAccountingFormats() throws IOException {
+ char pound = '\u00A3';
+ char euro = '\u20AC';
+
+ // Accounting -> 0 decimal places, default currency symbol
+ String formatDft = "_-\"$\"* #,##0_-;\\-\"$\"* #,##0_-;_-\"$\"* \"-\"_-;_-@_-";
+ // Accounting -> 0 decimal places, US currency symbol
+ String formatUS = "_-[$$-409]* #,##0_ ;_-[$$-409]* -#,##0 ;_-[$$-409]* \"-\"_ ;_-@_ ";
+ // Accounting -> 0 decimal places, UK currency symbol
+ String formatUK = "_-[$"+pound+"-809]* #,##0_-;\\-[$"+pound+"-809]* #,##0_-;_-[$"+pound+"-809]* \"-\"??_-;_-@_-";
+ // Accounting -> 0 decimal places, French currency symbol
+ String formatFR = "_-[$"+euro+"-40C]* #,##0_-;\\-[$"+euro+"-40C]* #,##0_-;_-[$"+euro+"-40C]* \"-\"??_-;_-@_-";
+
+ // Has +ve, -ve and zero rules
+ CellFormat cfDft = CellFormat.getInstance(formatDft);
+ CellFormat cfUS = CellFormat.getInstance(formatUS);
+ CellFormat cfUK = CellFormat.getInstance(formatUK);
+ CellFormat cfFR = CellFormat.getInstance(formatFR);
+
+ // For +ve numbers, should be Space + currency symbol + spaces + whole number with commas + space
+ assertEquals(" $ 12 ",cfDft.apply(Double.valueOf(12.33)).text);
+ assertEquals(" $ 12 ", cfUS.apply(Double.valueOf(12.33)).text);
+ assertEquals(" "+pound+" 12 ", cfUK.apply(Double.valueOf(12.33)).text);
+ assertEquals(" "+pound+" 12 ", cfFR.apply(Double.valueOf(12.33)).text);
+ assertEquals(" "+pound+" 16,789 ", cfUK.apply(Double.valueOf(16789.2)).text);
+ // TODO More
+
+ // For -ve numbers, should be Minus + currency symbol + spaces + whole number with commas
+ // TODO
+
+ // For zero, should be Space + currency symbol + spaces + Minus + spaces
+ // TODO
+ }
}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org