From d1ea6125fa915719d4515837d0f54c46bb7cdad9 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Wed, 4 Nov 2015 18:23:17 +0000 Subject: [PATCH] Bug #55265 - DataFormatter correct support for alternate number grouping characters, eg 1234 + #'##0 = 1'234 not 1,234 git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1712605 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/usermodel/DataFormatter.java | 24 +++++++++++-- .../poi/ss/usermodel/BaseTestDataFormat.java | 36 +++++++++++++++++++ 2 files changed, 58 insertions(+), 2 deletions(-) diff --git a/src/java/org/apache/poi/ss/usermodel/DataFormatter.java b/src/java/org/apache/poi/ss/usermodel/DataFormatter.java index 5ab2478ee2..37115feb34 100644 --- a/src/java/org/apache/poi/ss/usermodel/DataFormatter.java +++ b/src/java/org/apache/poi/ss/usermodel/DataFormatter.java @@ -157,6 +157,12 @@ public class DataFormatter implements Observer { */ private static final Pattern fractionStripper = Pattern.compile("(\"[^\"]*\")|([^ \\?#\\d\\/]+)"); + /** + * A regex to detect if an alternate grouping character is used + * in a numeric format + */ + private static final Pattern alternateGrouping = Pattern.compile("([#0]([^.#0])[#0]{3})"); + /** * Cells formatted with a date or time format and which contain invalid date or time values * show 255 pound signs ("#"). @@ -658,10 +664,24 @@ public class DataFormatter implements Observer { } private Format createNumberFormat(String formatStr, double cellValue) { - final String format = cleanFormatForNumber(formatStr); + String format = cleanFormatForNumber(formatStr); + DecimalFormatSymbols symbols = decimalSymbols; + + // Do we need to change the grouping character? + // eg for a format like #'##0 which wants 12'345 not 12,345 + Matcher agm = alternateGrouping.matcher(format); + if (agm.find()) { + symbols = DecimalFormatSymbols.getInstance(locale); + + char grouping = agm.group(2).charAt(0); + symbols.setGroupingSeparator(grouping); + String oldPart = agm.group(1); + String newPart = oldPart.replace(grouping, ','); + format = format.replace(oldPart, newPart); + } try { - DecimalFormat df = new DecimalFormat(format, decimalSymbols); + DecimalFormat df = new DecimalFormat(format, symbols); setExcelStyleRoundingMode(df); return df; } catch(IllegalArgumentException iae) { diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestDataFormat.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestDataFormat.java index 0fd26ed7c1..03a3631a37 100644 --- a/src/testcases/org/apache/poi/ss/usermodel/BaseTestDataFormat.java +++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestDataFormat.java @@ -165,4 +165,40 @@ public abstract class BaseTestDataFormat extends TestCase { // TODO Fix this to not have an extra 0 at the end //assertEquals(pound+" - ", formatter.formatCellValue(zero)); } + + /** + * Using a single quote (') instead of a comma (,) as + * a number separator, eg 1000 -> 1'000 + */ + public final void test55265() { + Workbook wb = _testDataProvider.createWorkbook(); + DataFormatter formatter = new DataFormatter(); + DataFormat fmt = wb.createDataFormat(); + Sheet sheet = wb.createSheet(); + Row r = sheet.createRow(0); + + CellStyle cs = wb.createCellStyle(); + cs.setDataFormat(fmt.getFormat("#'##0")); + + Cell zero = r.createCell(0); + zero.setCellValue(0); + zero.setCellStyle(cs); + + Cell sml = r.createCell(1); + sml.setCellValue(12); + sml.setCellStyle(cs); + + Cell med = r.createCell(2); + med.setCellValue(1234); + med.setCellStyle(cs); + + Cell lge = r.createCell(3); + lge.setCellValue(12345678); + lge.setCellStyle(cs); + + assertEquals("0", formatter.formatCellValue(zero)); + assertEquals("12", formatter.formatCellValue(sml)); + assertEquals("1'234", formatter.formatCellValue(med)); + assertEquals("12'345'678", formatter.formatCellValue(lge)); + } } -- 2.39.5