From 6099faa72238b572e5f5c22500a7283690bde5f6 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Tue, 1 Jun 2010 15:20:57 +0000 Subject: [PATCH] More work inspired by bug #48872 - handle MMMMM and elapsed time formatting rules in DataFormatter git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@950113 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/status.xml | 1 + .../poi/ss/usermodel/DataFormatter.java | 49 ++++-- .../ss/usermodel/ExcelStyleDateFormatter.java | 145 ++++++++++++++++++ .../hssf/usermodel/TestHSSFDataFormatter.java | 23 +-- .../poi/ss/usermodel/TestDataFormatter.java | 68 ++++++++ 5 files changed, 264 insertions(+), 22 deletions(-) create mode 100644 src/java/org/apache/poi/ss/usermodel/ExcelStyleDateFormatter.java diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index b73f1ce5be..d1cae47add 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ + 48872 - handle MMMMM and elapsed time formatting rules in DataFormatter 48872 - handle zero formatting rules, and better color detection in DataFormatter 48872 - support for more kinds of formatting in DataFormatter 43161 - fixed construction of the DIB picture header diff --git a/src/java/org/apache/poi/ss/usermodel/DataFormatter.java b/src/java/org/apache/poi/ss/usermodel/DataFormatter.java index bef1b69d31..029f24cd40 100644 --- a/src/java/org/apache/poi/ss/usermodel/DataFormatter.java +++ b/src/java/org/apache/poi/ss/usermodel/DataFormatter.java @@ -19,6 +19,7 @@ package org.apache.poi.ss.usermodel; import java.util.regex.Pattern; import java.util.regex.Matcher; import java.util.*; +import java.math.RoundingMode; import java.text.*; /** @@ -275,7 +276,10 @@ public class DataFormatter { formatStr = formatStr.replaceAll("\\\\-","-"); formatStr = formatStr.replaceAll("\\\\,",","); formatStr = formatStr.replaceAll("\\\\ "," "); + formatStr = formatStr.replaceAll("\\\\/","/"); // weird: m\\/d\\/yyyy formatStr = formatStr.replaceAll(";@", ""); + formatStr = formatStr.replaceAll("\"/\"", "/"); // "/" is escaped for no reason in: mm"/"dd"/"yyyy + boolean hasAmPm = false; Matcher amPmMatcher = amPmPattern.matcher(formatStr); while (amPmMatcher.find()) { @@ -357,7 +361,7 @@ public class DataFormatter { formatStr = sb.toString(); try { - return new SimpleDateFormat(formatStr, dateSymbols); + return new ExcelStyleDateFormatter(formatStr, dateSymbols); } catch(IllegalArgumentException iae) { // the pattern could not be parsed correctly, @@ -372,16 +376,19 @@ public class DataFormatter { // If they requested spacers, with "_", // remove those as we don't do spacing + // If they requested full-column-width + // padding, with "*", remove those too for(int i = 0; i < sb.length(); i++) { char c = sb.charAt(i); - if(c == '_') { + if(c == '_' || c == '*') { if(i > 0 && sb.charAt((i-1)) == '\\') { // It's escaped, don't worry continue; } else { if(i < sb.length()-1) { // Remove the character we're supposed - // to match the space of + // to match the space of / pad to the + // column width with sb.deleteCharAt(i+1); } // Remove the _ too @@ -407,7 +414,9 @@ public class DataFormatter { } try { - return new DecimalFormat(sb.toString(), decimalSymbols); + DecimalFormat df = new DecimalFormat(sb.toString(), decimalSymbols); + df.setRoundingMode(RoundingMode.HALF_UP); + return df; } catch(IllegalArgumentException iae) { // the pattern could not be parsed correctly, @@ -445,6 +454,17 @@ public class DataFormatter { } return generalDecimalNumFormat; } + + /** + * Performs Excel-style date formatting, using the + * supplied Date and format + */ + private String performDateFormatting(Date d, Format dateFormat) { + if(dateFormat != null) { + return dateFormat.format(d); + } + return d.toString(); + } /** * Returns the formatted value of an Excel date as a String based @@ -456,11 +476,14 @@ public class DataFormatter { */ private String getFormattedDateString(Cell cell) { Format dateFormat = getFormat(cell); - Date d = cell.getDateCellValue(); - if (dateFormat != null) { - return dateFormat.format(d); + if(dateFormat instanceof ExcelStyleDateFormatter) { + // Hint about the raw excel value + ((ExcelStyleDateFormatter)dateFormat).setDateToBeFormatted( + cell.getNumericCellValue() + ); } - return d.toString(); + Date d = cell.getDateCellValue(); + return performDateFormatting(d, dateFormat); } /** @@ -491,13 +514,13 @@ public class DataFormatter { // Is it a date? if(DateUtil.isADateFormat(formatIndex,formatString) && DateUtil.isValidExcelDate(value)) { - Format dateFormat = getFormat(value, formatIndex, formatString); - Date d = DateUtil.getJavaDate(value); - if (dateFormat == null) { - return d.toString(); + if(dateFormat instanceof ExcelStyleDateFormatter) { + // Hint about the raw excel value + ((ExcelStyleDateFormatter)dateFormat).setDateToBeFormatted(value); } - return dateFormat.format(d); + Date d = DateUtil.getJavaDate(value); + return performDateFormatting(d, dateFormat); } // else Number Format numberFormat = getFormat(value, formatIndex, formatString); diff --git a/src/java/org/apache/poi/ss/usermodel/ExcelStyleDateFormatter.java b/src/java/org/apache/poi/ss/usermodel/ExcelStyleDateFormatter.java new file mode 100644 index 0000000000..ebe67a734a --- /dev/null +++ b/src/java/org/apache/poi/ss/usermodel/ExcelStyleDateFormatter.java @@ -0,0 +1,145 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ +package org.apache.poi.ss.usermodel; + +import java.util.regex.Pattern; +import java.util.regex.Matcher; +import java.util.*; +import java.math.RoundingMode; +import java.text.*; + +/** + * A wrapper around a {@link SimpleDateFormat} instance, + * which handles a few Excel-style extensions that + * are not supported by {@link SimpleDateFormat}. + * Currently, the extensions are around the handling + * of elapsed time, eg rendering 1 day 2 hours + * as 26 hours. + */ +public class ExcelStyleDateFormatter extends SimpleDateFormat { + public static final char MMMMM_START_SYMBOL = '\ue001'; + public static final char MMMMM_TRUNCATE_SYMBOL = '\ue002'; + public static final char H_BRACKET_SYMBOL = '\ue010'; + public static final char HH_BRACKET_SYMBOL = '\ue011'; + public static final char M_BRACKET_SYMBOL = '\ue012'; + public static final char MM_BRACKET_SYMBOL = '\ue013'; + public static final char S_BRACKET_SYMBOL = '\ue014'; + public static final char SS_BRACKET_SYMBOL = '\ue015'; + + private DecimalFormat format1digit = new DecimalFormat("0"); + private DecimalFormat format2digits = new DecimalFormat("00"); + { + format1digit.setRoundingMode(RoundingMode.HALF_UP); + format2digits.setRoundingMode(RoundingMode.HALF_UP); + } + + private double dateToBeFormatted = 0.0; + + public ExcelStyleDateFormatter() { + super(); + } + public ExcelStyleDateFormatter(String pattern) { + super(processFormatPattern(pattern)); + } + public ExcelStyleDateFormatter(String pattern, + DateFormatSymbols formatSymbols) { + super(processFormatPattern(pattern), formatSymbols); + } + public ExcelStyleDateFormatter(String pattern, Locale locale) { + super(processFormatPattern(pattern), locale); + } + + /** + * Takes a format String, and replaces Excel specific bits + * with our detection sequences + */ + private static String processFormatPattern(String f) { + f = f.replaceAll("MMMMM", MMMMM_START_SYMBOL + "MMM" + MMMMM_TRUNCATE_SYMBOL); + f = f.replaceAll("\\[H\\]", String.valueOf(H_BRACKET_SYMBOL)); + f = f.replaceAll("\\[HH\\]", String.valueOf(HH_BRACKET_SYMBOL)); + f = f.replaceAll("\\[m\\]", String.valueOf(M_BRACKET_SYMBOL)); + f = f.replaceAll("\\[mm\\]", String.valueOf(MM_BRACKET_SYMBOL)); + f = f.replaceAll("\\[s\\]", String.valueOf(S_BRACKET_SYMBOL)); + f = f.replaceAll("\\[ss\\]", String.valueOf(SS_BRACKET_SYMBOL)); + return f; + } + + /** + * Used to let us know what the date being + * formatted is, in Excel terms, which we + * may wish to use when handling elapsed + * times. + */ + public void setDateToBeFormatted(double date) { + this.dateToBeFormatted = date; + } + + @Override + public StringBuffer format(Date date, StringBuffer paramStringBuffer, + FieldPosition paramFieldPosition) { + // Do the normal format + String s = super.format(date, paramStringBuffer, paramFieldPosition).toString(); + + // Now handle our special cases + if(s.indexOf(MMMMM_START_SYMBOL) != -1) { + s = s.replaceAll( + MMMMM_START_SYMBOL + "(\\w)\\w+" + MMMMM_TRUNCATE_SYMBOL, + "$1" + ); + } + + if(s.indexOf(H_BRACKET_SYMBOL) != -1 || + s.indexOf(HH_BRACKET_SYMBOL) != -1) { + double hours = dateToBeFormatted * 24; + s = s.replaceAll( + String.valueOf(H_BRACKET_SYMBOL), + format1digit.format(hours) + ); + s = s.replaceAll( + String.valueOf(HH_BRACKET_SYMBOL), + format2digits.format(hours) + ); + } + + if(s.indexOf(M_BRACKET_SYMBOL) != -1 || + s.indexOf(MM_BRACKET_SYMBOL) != -1) { + double minutes = dateToBeFormatted * 24 * 60; + s = s.replaceAll( + String.valueOf(M_BRACKET_SYMBOL), + format1digit.format(minutes) + ); + s = s.replaceAll( + String.valueOf(MM_BRACKET_SYMBOL), + format2digits.format(minutes) + ); + } + if(s.indexOf(S_BRACKET_SYMBOL) != -1 || + s.indexOf(SS_BRACKET_SYMBOL) != -1) { + double seconds = dateToBeFormatted * 24 * 60 * 60; + s = s.replaceAll( + String.valueOf(S_BRACKET_SYMBOL), + format1digit.format(seconds) + ); + s = s.replaceAll( + String.valueOf(SS_BRACKET_SYMBOL), + format2digits.format(seconds) + ); + } + + return new StringBuffer(s); + } +} diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java index 6453699892..d0e5c4183f 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java @@ -181,19 +181,24 @@ public final class TestHSSFDataFormatter extends TestCase { log("==== VALID DATE FORMATS ===="); while (it.hasNext()) { Cell cell = it.next(); - String fmtval = formatter.formatCellValue(cell); - log(fmtval); + String fmtval = formatter.formatCellValue(cell); + log(fmtval); // should not be equal to "555.555" assertTrue( ! "555.555".equals(fmtval)); - String fmt = cell.getCellStyle().getDataFormatString(); - //assert the correct month form, as in the original Excel format - String monthPtrn = fmt.indexOf("mmmm") != -1 ? "MMMM" : "MMM"; - - // this line is intended to compute how "July" would look like in the current locale - String jul = new SimpleDateFormat(monthPtrn).format(new GregorianCalendar(2010,6,15).getTime()); - assertTrue( fmtval.indexOf(jul) > -1); + String fmt = cell.getCellStyle().getDataFormatString(); + + //assert the correct month form, as in the original Excel format + String monthPtrn = fmt.indexOf("mmmm") != -1 ? "MMMM" : "MMM"; + // this line is intended to compute how "July" would look like in the current locale + String jul = new SimpleDateFormat(monthPtrn).format(new GregorianCalendar(2010,6,15).getTime()); + // special case for MMMMM = 1st letter of month name + if(fmt.indexOf("mmmmm") > -1) { + jul = jul.substring(0,1); + } + // check we found july properly + assertTrue("Format came out incorrect - " + fmt, fmtval.indexOf(jul) > -1); } // test number formats diff --git a/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java b/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java index 51c4848475..bc3bb67847 100644 --- a/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java +++ b/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java @@ -17,6 +17,8 @@ package org.apache.poi.ss.usermodel; +import java.util.Calendar; +import java.util.Date; import java.util.Locale; import org.apache.poi.hssf.usermodel.TestHSSFDataFormatter; @@ -121,4 +123,70 @@ public class TestDataFormatter extends TestCase { // assertEquals("12.34", dfUS.formatRawCellContents(12.343, -1, p2dp_n1dpTSP)); // assertEquals("(12.3)", dfUS.formatRawCellContents(-12.343, -1, p2dp_n1dpTSP)); } + + /** + * 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. + */ + public void testPaddingSpaces() { + DataFormatter dfUS = new DataFormatter(Locale.US); + assertEquals("12.34", dfUS.formatRawCellContents(12.343, -1, "##.##_ ")); + assertEquals("12.34", dfUS.formatRawCellContents(12.343, -1, "##.##_1")); + assertEquals("12.34", dfUS.formatRawCellContents(12.343, -1, "##.##_)")); + assertEquals("12.34", dfUS.formatRawCellContents(12.343, -1, "_-##.##")); + + assertEquals("12.34", dfUS.formatRawCellContents(12.343, -1, "##.##* ")); + assertEquals("12.34", dfUS.formatRawCellContents(12.343, -1, "##.##*1")); + assertEquals("12.34", dfUS.formatRawCellContents(12.343, -1, "##.##*)")); + assertEquals("12.34", dfUS.formatRawCellContents(12.343, -1, "*-##.##")); + } + + /** + * Test that the special Excel month format MMMMM + * gets turned into the first letter of the month + */ + public void testMMMMM() { + DataFormatter dfUS = new DataFormatter(Locale.US); + + Calendar c = Calendar.getInstance(); + c.set(Calendar.MILLISECOND, 0); + c.set(2010, 5, 1, 2, 0, 0); + + assertEquals("2010-J-1 2:00:00", dfUS.formatRawCellContents( + DateUtil.getExcelDate(c, false), -1, "YYYY-MMMMM-D h:mm:ss" + )); + } + + /** + * Test that we can handle elapsed time, + * eg formatting 1 day 4 hours as 28 hours + */ + public void testElapsedTime() { + DataFormatter dfUS = new DataFormatter(Locale.US); + + double hour = 1.0/24.0; + + assertEquals("01:00", dfUS.formatRawCellContents(1*hour, -1, "hh:mm")); + assertEquals("05:00", dfUS.formatRawCellContents(5*hour, -1, "hh:mm")); + assertEquals("20:00", dfUS.formatRawCellContents(20*hour, -1, "hh:mm")); + assertEquals("23:00", dfUS.formatRawCellContents(23*hour, -1, "hh:mm")); + assertEquals("00:00", dfUS.formatRawCellContents(24*hour, -1, "hh:mm")); + assertEquals("02:00", dfUS.formatRawCellContents(26*hour, -1, "hh:mm")); + assertEquals("20:00", dfUS.formatRawCellContents(44*hour, -1, "hh:mm")); + assertEquals("02:00", dfUS.formatRawCellContents(50*hour, -1, "hh:mm")); + + assertEquals("01:00", dfUS.formatRawCellContents(1*hour, -1, "[hh]:mm")); + assertEquals("05:00", dfUS.formatRawCellContents(5*hour, -1, "[hh]:mm")); + assertEquals("20:00", dfUS.formatRawCellContents(20*hour, -1, "[hh]:mm")); + assertEquals("23:00", dfUS.formatRawCellContents(23*hour, -1, "[hh]:mm")); + assertEquals("24:00", dfUS.formatRawCellContents(24*hour, -1, "[hh]:mm")); + assertEquals("26:00", dfUS.formatRawCellContents(26*hour, -1, "[hh]:mm")); + assertEquals("44:00", dfUS.formatRawCellContents(44*hour, -1, "[hh]:mm")); + assertEquals("50:00", dfUS.formatRawCellContents(50*hour, -1, "[hh]:mm")); + + assertEquals("30:00", dfUS.formatRawCellContents(0.5*hour, -1, "[mm]:ss")); + assertEquals("60:00", dfUS.formatRawCellContents(1*hour, -1, "[mm]:ss")); + assertEquals("120:00", dfUS.formatRawCellContents(2*hour, -1, "[mm]:ss")); + } } -- 2.39.5