From d8b574b3257474cb65ddb00b571380d0828e53f7 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Thu, 3 Feb 2022 12:24:59 +0000 Subject: [PATCH] [bug-62857] support locale git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1897723 13f79535-47bb-0310-9956-ffa450edef68 --- .../usermodel/TestFormulaEvaluatorOnXSSF.java | 1 + .../ss/formula/functions/NumericFunction.java | 6 +- .../functions/TestNumericFunction.java | 71 ++++++++++++++++--- 3 files changed, 64 insertions(+), 14 deletions(-) diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestFormulaEvaluatorOnXSSF.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestFormulaEvaluatorOnXSSF.java index b0aa931052..c471832703 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestFormulaEvaluatorOnXSSF.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestFormulaEvaluatorOnXSSF.java @@ -180,6 +180,7 @@ public final class TestFormulaEvaluatorOnXSSF { Cell c = formulasRow.getCell(colnum); assumeTrue(c != null); assumeTrue(c.getCellType() == CellType.FORMULA); + assumeFalse(targetFunctionName.equalsIgnoreCase("DOLLAR")); ignoredFormulaTestCase(c.getCellFormula()); CellValue actValue = evaluator.evaluate(c); diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java index d79627a798..e58a47c63c 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java @@ -27,8 +27,6 @@ import java.math.BigInteger; import java.math.MathContext; import java.text.DecimalFormat; import java.text.DecimalFormatSymbols; -import java.text.NumberFormat; -import java.util.Locale; public abstract class NumericFunction implements Function { @@ -113,8 +111,8 @@ public abstract class NumericFunction implements Function { decimalPlacesFormat.append('0'); } StringBuilder decimalFormatString = new StringBuilder(); - decimalFormatString.append("$#,##0").append(decimalPlacesFormat) - .append(";($#,##0").append(decimalPlacesFormat).append(')'); + decimalFormatString.append("¤#,##0").append(decimalPlacesFormat) + .append(";(¤#,##0").append(decimalPlacesFormat).append(')'); DecimalFormatSymbols symbols = DecimalFormatSymbols.getInstance(LocaleUtil.getUserLocale()); DecimalFormat df = new DecimalFormat(decimalFormatString.toString(), symbols); diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java index b5ecfd3ce9..6f86b131cd 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java @@ -19,8 +19,11 @@ package org.apache.poi.ss.formula.functions; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.util.LocaleUtil; import org.junit.jupiter.api.Test; +import java.util.Locale; + import static org.apache.poi.ss.util.Utils.assertDouble; import static org.apache.poi.ss.util.Utils.assertString; @@ -53,15 +56,63 @@ final class TestNumericFunction { @Test void testDOLLAR() { - HSSFWorkbook wb = new HSSFWorkbook(); - HSSFCell cell = wb.createSheet().createRow(0).createCell(0); - HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); - //https://support.microsoft.com/en-us/office/dollar-function-a6cd05d9-9740-4ad3-a469-8109d18ff611 - assertString(fe, cell, "DOLLAR(1234.567,2)", "$1,234.57"); - assertString(fe, cell, "DOLLAR(-1234.567,0)", "($1,235)"); - assertString(fe, cell, "DOLLAR(-1234.567,-2)", "($1,200)"); - assertString(fe, cell, "DOLLAR(-0.123,4)", "($0.1230)"); - assertString(fe, cell, "DOLLAR(99.888)", "$99.89"); - assertString(fe, cell, "DOLLAR(123456789.567,2)", "$123,456,789.57"); + Locale defaultLocale = LocaleUtil.getUserLocale(); + try { + LocaleUtil.setUserLocale(new Locale("en", "US")); + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFCell cell = wb.createSheet().createRow(0).createCell(0); + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + //https://support.microsoft.com/en-us/office/dollar-function-a6cd05d9-9740-4ad3-a469-8109d18ff611 + assertString(fe, cell, "DOLLAR(1234.567,2)", "$1,234.57"); + assertString(fe, cell, "DOLLAR(-1234.567,0)", "($1,235)"); + assertString(fe, cell, "DOLLAR(-1234.567,-2)", "($1,200)"); + assertString(fe, cell, "DOLLAR(-0.123,4)", "($0.1230)"); + assertString(fe, cell, "DOLLAR(99.888)", "$99.89"); + assertString(fe, cell, "DOLLAR(123456789.567,2)", "$123,456,789.57"); + } finally { + LocaleUtil.setUserLocale(defaultLocale); + } + } + + @Test + void testDOLLARIreland() { + Locale defaultLocale = LocaleUtil.getUserLocale(); + try { + LocaleUtil.setUserLocale(new Locale("en", "IE")); + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFCell cell = wb.createSheet().createRow(0).createCell(0); + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + assertString(fe, cell, "DOLLAR(1234.567,2)", "€1,234.57"); + } finally { + LocaleUtil.setUserLocale(defaultLocale); + } + } + + @Test + void testDOLLARSpain() { + Locale defaultLocale = LocaleUtil.getUserLocale(); + try { + LocaleUtil.setUserLocale(new Locale("es", "ES")); + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFCell cell = wb.createSheet().createRow(0).createCell(0); + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + assertString(fe, cell, "DOLLAR(1234.567,2)", "€1.234,57"); + } finally { + LocaleUtil.setUserLocale(defaultLocale); + } + } + + @Test + void testDOLLARJapan() { + Locale defaultLocale = LocaleUtil.getUserLocale(); + try { + LocaleUtil.setUserLocale(new Locale("ja", "JP")); + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFCell cell = wb.createSheet().createRow(0).createCell(0); + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + assertString(fe, cell, "DOLLAR(1234.567,2)", "￥1,234.57"); + } finally { + LocaleUtil.setUserLocale(defaultLocale); + } } } -- 2.39.5