From belljames@example.org Tue Jun 01 12:12:48 2010 Return-Path: <belljames@example.org> Delivered-To: belljames@example.org Received: (qmail 4178 invoked from network); 1 Jun 2010 12:12:48 -0000 Received: from unknown (HELO mail.apache.org) (83.161.120.155) by 83.161.120.155 with SMTP; 1 Jun 2010 12:12:48 -0000 Received: (qmail 35449 invoked by uid 500); 1 Jun 2010 12:12:48 -0000 Delivered-To: belljames@example.org Received: (qmail 35418 invoked by uid 500); 1 Jun 2010 12:12:48 -0000 Mailing-List: contact belljames@example.org; run by ezmlm Precedence: bulk List-Help: <mailto:belljames@example.org> List-Unsubscribe: <mailto:belljames@example.org> List-Post: <mailto:belljames@example.org> List-Id: <commits.poi.apache.org> Reply-To: belljames@example.org Delivered-To: mailing list belljames@example.org Received: (qmail 35411 invoked by uid 99); 1 Jun 2010 12:12:48 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Jun 2010 12:12:48 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [83.161.120.155] (HELO eris.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Jun 2010 12:12:45 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 3065A23888D1; Tue, 1 Jun 2010 12:12:23 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r950035 - in /poi/trunk/src: documentation/content/xdocs/ java/org/apache/poi/ss/usermodel/ testcases/org/apache/poi/hssf/usermodel/ testcases/org/apache/poi/ss/usermodel/ Date: Tue, 01 Jun 2010 12:12:23 -0000 To: belljames@example.org From: belljames@example.org X-Mailer: svnmailer-1.0.8 Message-Id: <belljames@example.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: nick Date: Tue Jun 1 12:12:22 2010 New Revision: 950035 URL: http://svn.apache.org/viewvc?rev=950035&view=rev Log: More fixes inspired by bug #48872 - handle zero formatting rules, and better color detection in DataFormatter Modified: poi/trunk/src/documentation/content/xdocs/status.xml poi/trunk/src/java/org/apache/poi/ss/usermodel/DataFormatter.java poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.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=950035&r1=950034&r2=950035&view=diff ============================================================================== --- poi/trunk/src/documentation/content/xdocs/status.xml (original) +++ poi/trunk/src/documentation/content/xdocs/status.xml Tue Jun 1 12:12:22 2010 @@ -34,6 +34,8 @@ <changes> <release version="3.7-SNAPSHOT" date="2010-??-??"> + <action dev="POI-DEVELOPERS" type="fix">48872 - handle zero formatting rules, and better color detection in DataFormatter</action> + <action dev="POI-DEVELOPERS" type="fix">48872 - support for more kinds of formatting in DataFormatter</action> <action dev="POI-DEVELOPERS" type="fix">43161 - fixed construction of the DIB picture header</action> <action dev="POI-DEVELOPERS" type="add">49311 - initial support for reading AES-encrypted/write-protected OOXML files</action> <action dev="POI-DEVELOPERS" type="fix">48718 - Make the creation of multiple, un-modified fonts in a row in XSSF match the old HSSF behaviour</action> 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=950035&r1=950034&r2=950035&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 Jun 1 12:12:22 2010 @@ -77,6 +77,16 @@ public class DataFormatter { /** A regex to find patterns like [$$-1009] and [$?-452]. */ private static final Pattern specialPatternGroup = Pattern.compile("(\\[\\$[^-\\]]*-[0-9A-Z]+\\])"); + /** + * A regex to match the colour formattings rules. + * Allowed colours are: Black, Blue, Cyan, Green, + * Magenta, Red, White, Yellow, "Color n" (1<=n<=56) + */ + private static final Pattern colorPattern = + Pattern.compile("(\\[BLACK\\])|(\\[BLUE\\])|(\\[CYAN\\])|(\\[GREEN\\])|" + + "(\\[MAGENTA\\])|(\\[RED\\])|(\\[WHITE\\])|(\\[YELLOW\\])|" + + "(\\[COLOR\\s*\\d\\])|(\\[COLOR\\s*[0-5]\\d\\])", Pattern.CASE_INSENSITIVE); + /** * The decimal symbols of the locale used for formatting values. */ @@ -165,6 +175,20 @@ public class DataFormatter { } private Format getFormat(double cellValue, int formatIndex, String formatStr) { + // Excel supports positive/negative/zero, but java + // doesn't, so we need to do it specially + if(formatStr.indexOf(';') != formatStr.lastIndexOf(';')) { + int lastAt = formatStr.lastIndexOf(';'); + String zeroFormat = formatStr.substring(lastAt+1); + String normalFormat = formatStr.substring(0,lastAt); + if(cellValue == 0.0) { + formatStr = zeroFormat; + } else { + formatStr = normalFormat; + } + } + + // See if we already have it cached Format format = formats.get(formatStr); if (format != null) { return format; @@ -195,9 +219,25 @@ public class DataFormatter { } private Format createFormat(double cellValue, int formatIndex, String sFormat) { - // remove color formatting if present - String formatStr = sFormat.replaceAll("\\[[a-zA-Z]*\\]", ""); - + String formatStr = sFormat; + + // Remove colour formatting if present + Matcher colourM = colorPattern.matcher(formatStr); + while(colourM.find()) { + String colour = colourM.group(); + + // Paranoid replacement... + int at = formatStr.indexOf(colour); + if(at == -1) break; + String nFormatStr = formatStr.substring(0,at) + + formatStr.substring(at+colour.length()+1); + if(nFormatStr.equals(formatStr)) break; + + // Try again in case there's multiple + formatStr = nFormatStr; + colourM = colorPattern.matcher(formatStr); + } + // try to extract special characters like currency Matcher m = specialPatternGroup.matcher(formatStr); while(m.find()) { @@ -329,23 +369,33 @@ public class DataFormatter { private Format createNumberFormat(String formatStr, double cellValue) { StringBuffer sb = new StringBuffer(formatStr); - for (int i = 0; i < sb.length(); i++) { + + // If they requested spacers, with "_", + // remove those as we don't do spacing + for(int i = 0; i < sb.length(); i++) { char c = sb.charAt(i); - //handle (#,##0_); - if (c == '(') { - int idx = sb.indexOf(")", i); - if (idx > -1 && sb.charAt(idx -1) == '_') { - sb.deleteCharAt(idx); - sb.deleteCharAt(idx - 1); - sb.deleteCharAt(i); - i--; - } - } else if (c == ')' && i > 0 && sb.charAt(i - 1) == '_') { - sb.deleteCharAt(i); - sb.deleteCharAt(i - 1); - i--; + if(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 + sb.deleteCharAt(i+1); + } + // Remove the _ too + sb.deleteCharAt(i); + } + } + } + + // Now, handle the other aspects like + // quoting and scientific notation + for(int i = 0; i < sb.length(); i++) { + char c = sb.charAt(i); // remove quotes and back slashes - } else if (c == '\\' || c == '"') { + if (c == '\\' || c == '"') { sb.deleteCharAt(i); i--; Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java?rev=950035&r1=950034&r2=950035&view=diff ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java (original) +++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java Tue Jun 1 12:12:22 2010 @@ -91,6 +91,7 @@ public final class TestHSSFDataFormatter "0000.00000%", "0.000E+00", "0.00E+00", + "[BLACK]0.00;[COLOR 5]##.##", }; // invalid date formats -- will throw exception in DecimalFormat ctor 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=950035&r1=950034&r2=950035&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 Jun 1 12:12:22 2010 @@ -19,11 +19,15 @@ package org.apache.poi.ss.usermodel; import java.util.Locale; +import org.apache.poi.hssf.usermodel.TestHSSFDataFormatter; + import junit.framework.TestCase; /** * Tests of {@link DataFormatter} * + * See {@link TestHSSFDataFormatter} too for + * more tests. */ public class TestDataFormatter extends TestCase { /** @@ -40,4 +44,81 @@ public class TestDataFormatter extends T assertEquals("12.34", dfUS.formatRawCellContents(12.34, -1, "@")); assertEquals("12,34", dfFR.formatRawCellContents(12.34, -1, "@")); } + + /** + * Ensure that colours get correctly + * zapped from within the format strings + */ + public void testColours() { + DataFormatter dfUS = new DataFormatter(Locale.US); + + String[] formats = new String[] { + "##.##", + "[WHITE]##.##", + "[BLACK]##.##;[RED]-##.##", + "[COLOR11]##.##;[COLOR 43]-##.00", + }; + for(String format : formats) { + assertEquals( + "Wrong format for: " + format, + "12.34", + dfUS.formatRawCellContents(12.343, -1, format) + ); + assertEquals( + "Wrong format for: " + format, + "-12.34", + dfUS.formatRawCellContents(-12.343, -1, format) + ); + } + + // Ensure that random square brackets remain + assertEquals("12.34[a]", dfUS.formatRawCellContents(12.343, -1, "##.##[a]")); + assertEquals("[ab]12.34[x]", dfUS.formatRawCellContents(12.343, -1, "[ab]##.##[x]")); + } + + /** + * Test how we handle negative and zeros. + * Note - some tests are disabled as DecimalFormat + * and Excel differ, and workarounds are not + * yet in place for all of these + */ + public void testNegativeZero() { + DataFormatter dfUS = new DataFormatter(Locale.US); + + String all2dp = "00.00"; + String alln1dp = "(00.0)"; + String p1dp_n1dp = "00.0;(00.0)"; + String p2dp_n1dp = "00.00;(00.0)"; + String p2dp_n1dp_z0 = "00.00;(00.0);0"; + String all2dpTSP = "00.00_x"; + String p2dp_n2dpTSP = "00.00_x;(00.00)_x"; + String p2dp_n1dpTSP = "00.00_x;(00.0)_x"; + + assertEquals("12.34", dfUS.formatRawCellContents(12.343, -1, all2dp)); + assertEquals("12.34", dfUS.formatRawCellContents(12.343, -1, p2dp_n1dp)); + assertEquals("12.34", dfUS.formatRawCellContents(12.343, -1, p2dp_n1dp_z0)); + + assertEquals("(12.3)", dfUS.formatRawCellContents(12.343, -1, alln1dp)); + assertEquals("-(12.3)", dfUS.formatRawCellContents(-12.343, -1, alln1dp)); + assertEquals("12.3", dfUS.formatRawCellContents(12.343, -1, p1dp_n1dp)); + assertEquals("(12.3)", dfUS.formatRawCellContents(-12.343, -1, p1dp_n1dp)); + + assertEquals("-12.34", dfUS.formatRawCellContents(-12.343, -1, all2dp)); + // TODO - fix case of negative subpattern differing from the + // positive one by more than just the prefix+suffix, which + // is all DecimalFormat supports... +// assertEquals("(12.3)", dfUS.formatRawCellContents(-12.343, -1, p2dp_n1dp)); +// assertEquals("(12.3)", dfUS.formatRawCellContents(-12.343, -1, p2dp_n1dp_z0)); + + assertEquals("00.00", dfUS.formatRawCellContents(0, -1, all2dp)); + assertEquals("00.00", dfUS.formatRawCellContents(0, -1, p2dp_n1dp)); + assertEquals("0", dfUS.formatRawCellContents(0, -1, p2dp_n1dp_z0)); + + // Spaces are skipped + assertEquals("12.34", dfUS.formatRawCellContents(12.343, -1, all2dpTSP)); + assertEquals("12.34", dfUS.formatRawCellContents(12.343, -1, p2dp_n2dpTSP)); + assertEquals("(12.34)", dfUS.formatRawCellContents(-12.343, -1, p2dp_n2dpTSP)); +// assertEquals("12.34", dfUS.formatRawCellContents(12.343, -1, p2dp_n1dpTSP)); +// assertEquals("(12.3)", dfUS.formatRawCellContents(-12.343, -1, p2dp_n1dpTSP)); + } } --------------------------------------------------------------------- To unsubscribe, e-mail: belljames@example.org For additional commands, e-mail: belljames@example.org From belljames@example.org Tue Jun 01 15:21:24 2010 Return-Path: <belljames@example.org> Delivered-To: belljames@example.org Received: (qmail 79076 invoked from network); 1 Jun 2010 15:21:23 -0000 Received: from unknown (HELO mail.apache.org) (83.161.120.155) by 83.161.120.155 with SMTP; 1 Jun 2010 15:21:23 -0000 Received: (qmail 61698 invoked by uid 500); 1 Jun 2010 15:21:23 -0000 Delivered-To: belljames@example.org Received: (qmail 61662 invoked by uid 500); 1 Jun 2010 15:21:23 -0000 Mailing-List: contact belljames@example.org; run by ezmlm Precedence: bulk List-Help: <mailto:belljames@example.org> List-Unsubscribe: <mailto:belljames@example.org> List-Post: <mailto:belljames@example.org> List-Id: <commits.poi.apache.org> Reply-To: belljames@example.org Delivered-To: mailing list belljames@example.org Received: (qmail 61655 invoked by uid 99); 1 Jun 2010 15:21:23 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Jun 2010 15:21:23 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [83.161.120.155] (HELO eris.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Jun 2010 15:21:19 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 09537238899C; Tue, 1 Jun 2010 15:20:58 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r950113 - in /poi/trunk/src: documentation/content/xdocs/ java/org/apache/poi/ss/usermodel/ testcases/org/apache/poi/hssf/usermodel/ testcases/org/apache/poi/ss/usermodel/ Date: Tue, 01 Jun 2010 15:20:57 -0000 To: belljames@example.org From: belljames@example.org X-Mailer: svnmailer-1.0.8 Message-Id: <belljames@example.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: nick Date: Tue Jun 1 15:20:57 2010 New Revision: 950113 URL: http://svn.apache.org/viewvc?rev=950113&view=rev Log: More work inspired by bug #48872 - handle MMMMM and elapsed time formatting rules in DataFormatter Added: poi/trunk/src/java/org/apache/poi/ss/usermodel/ExcelStyleDateFormatter.java Modified: poi/trunk/src/documentation/content/xdocs/status.xml poi/trunk/src/java/org/apache/poi/ss/usermodel/DataFormatter.java poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.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=950113&r1=950112&r2=950113&view=diff ============================================================================== --- poi/trunk/src/documentation/content/xdocs/status.xml (original) +++ poi/trunk/src/documentation/content/xdocs/status.xml Tue Jun 1 15:20:57 2010 @@ -34,6 +34,7 @@ <changes> <release version="3.7-SNAPSHOT" date="2010-??-??"> + <action dev="POI-DEVELOPERS" type="fix">48872 - handle MMMMM and elapsed time formatting rules in DataFormatter</action> <action dev="POI-DEVELOPERS" type="fix">48872 - handle zero formatting rules, and better color detection in DataFormatter</action> <action dev="POI-DEVELOPERS" type="fix">48872 - support for more kinds of formatting in DataFormatter</action> <action dev="POI-DEVELOPERS" type="fix">43161 - fixed construction of the DIB picture header</action> 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=950113&r1=950112&r2=950113&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 Jun 1 15:20:57 2010 @@ -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 <tt>String</tt> 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); Added: poi/trunk/src/java/org/apache/poi/ss/usermodel/ExcelStyleDateFormatter.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/ExcelStyleDateFormatter.java?rev=950113&view=auto ============================================================================== --- poi/trunk/src/java/org/apache/poi/ss/usermodel/ExcelStyleDateFormatter.java (added) +++ poi/trunk/src/java/org/apache/poi/ss/usermodel/ExcelStyleDateFormatter.java Tue Jun 1 15:20:57 2010 @@ -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); + } +} Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java?rev=950113&r1=950112&r2=950113&view=diff ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java (original) +++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java Tue Jun 1 15:20:57 2010 @@ -181,19 +181,24 @@ public final class TestHSSFDataFormatter 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 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=950113&r1=950112&r2=950113&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 Jun 1 15:20:57 2010 @@ -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 T // 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")); + } } --------------------------------------------------------------------- To unsubscribe, e-mail: belljames@example.org For additional commands, e-mail: belljames@example.org From belljames@example.org Tue Jun 01 15:32:16 2010 Return-Path: <belljames@example.org> Delivered-To: belljames@example.org Received: (qmail 82926 invoked from network); 1 Jun 2010 15:32:16 -0000 Received: from unknown (HELO mail.apache.org) (83.161.120.155) by 83.161.120.155 with SMTP; 1 Jun 2010 15:32:16 -0000 Received: (qmail 81887 invoked by uid 500); 1 Jun 2010 15:32:16 -0000 Delivered-To: belljames@example.org Received: (qmail 81852 invoked by uid 500); 1 Jun 2010 15:32:15 -0000 Mailing-List: contact belljames@example.org; run by ezmlm Precedence: bulk List-Help: <mailto:belljames@example.org> List-Unsubscribe: <mailto:belljames@example.org> List-Post: <mailto:belljames@example.org> List-Id: <commits.poi.apache.org> Reply-To: belljames@example.org Delivered-To: mailing list belljames@example.org Received: (qmail 81845 invoked by uid 99); 1 Jun 2010 15:32:15 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Jun 2010 15:32:15 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [83.161.120.155] (HELO eris.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Jun 2010 15:32:13 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 3596B238899C; Tue, 1 Jun 2010 15:31:52 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r950117 - in /poi/trunk/src: documentation/content/xdocs/status.xml java/org/apache/poi/ss/usermodel/DataFormatter.java testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java Date: Tue, 01 Jun 2010 15:31:52 -0000 To: belljames@example.org From: belljames@example.org X-Mailer: svnmailer-1.0.8 Message-Id: <belljames@example.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: nick Date: Tue Jun 1 15:31:51 2010 New Revision: 950117 URL: http://svn.apache.org/viewvc?rev=950117&view=rev Log: Fix inspired by bug #48872 - allow DateFormatter.formatRawCellContents to handle 1904 as well as 1900 dates Modified: poi/trunk/src/documentation/content/xdocs/status.xml 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=950117&r1=950116&r2=950117&view=diff ============================================================================== --- poi/trunk/src/documentation/content/xdocs/status.xml (original) +++ poi/trunk/src/documentation/content/xdocs/status.xml Tue Jun 1 15:31:51 2010 @@ -34,6 +34,7 @@ <changes> <release version="3.7-SNAPSHOT" date="2010-??-??"> + <action dev="POI-DEVELOPERS" type="fix">48872 - allow DateFormatter.formatRawCellContents to handle 1904 as well as 1900 dates</action> <action dev="POI-DEVELOPERS" type="fix">48872 - handle MMMMM and elapsed time formatting rules in DataFormatter</action> <action dev="POI-DEVELOPERS" type="fix">48872 - handle zero formatting rules, and better color detection in DataFormatter</action> <action dev="POI-DEVELOPERS" type="fix">48872 - support for more kinds of formatting in DataFormatter</action> 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=950117&r1=950116&r2=950117&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 Jun 1 15:31:51 2010 @@ -511,6 +511,14 @@ public class DataFormatter { * @see #formatCellValue(Cell) */ public String formatRawCellContents(double value, int formatIndex, String formatString) { + return formatRawCellContents(value, formatIndex, formatString, false); + } + /** + * Formats the given raw cell value, based on the supplied + * format index and string, according to excel style rules. + * @see #formatCellValue(Cell) + */ + public String formatRawCellContents(double value, int formatIndex, String formatString, boolean use1904Windowing) { // Is it a date? if(DateUtil.isADateFormat(formatIndex,formatString) && DateUtil.isValidExcelDate(value)) { @@ -519,7 +527,7 @@ public class DataFormatter { // Hint about the raw excel value ((ExcelStyleDateFormatter)dateFormat).setDateToBeFormatted(value); } - Date d = DateUtil.getJavaDate(value); + Date d = DateUtil.getJavaDate(value, use1904Windowing); return performDateFormatting(d, dateFormat); } // else Number 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=950117&r1=950116&r2=950117&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 Jun 1 15:31:51 2010 @@ -18,13 +18,12 @@ 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; - import junit.framework.TestCase; +import org.apache.poi.hssf.usermodel.TestHSSFDataFormatter; + /** * Tests of {@link DataFormatter} * @@ -189,4 +188,12 @@ public class TestDataFormatter extends T assertEquals("60:00", dfUS.formatRawCellContents(1*hour, -1, "[mm]:ss")); assertEquals("120:00", dfUS.formatRawCellContents(2*hour, -1, "[mm]:ss")); } + + public void testDateWindowing() { + DataFormatter dfUS = new DataFormatter(Locale.US); + + assertEquals("1899-12-31 00:00:00", dfUS.formatRawCellContents(0.0, -1, "yyyy-mm-dd hh:mm:ss")); + assertEquals("1899-12-31 00:00:00", dfUS.formatRawCellContents(0.0, -1, "yyyy-mm-dd hh:mm:ss", false)); + assertEquals("1904-01-01 00:00:00", dfUS.formatRawCellContents(0.0, -1, "yyyy-mm-dd hh:mm:ss", true)); + } } --------------------------------------------------------------------- To unsubscribe, e-mail: belljames@example.org For additional commands, e-mail: belljames@example.org From belljames@example.org Wed Jun 02 11:30:39 2010 Return-Path: <belljames@example.org> Delivered-To: belljames@example.org Received: (qmail 8588 invoked from network); 2 Jun 2010 11:30:39 -0000 Received: from unknown (HELO mail.apache.org) (83.161.120.155) by 83.161.120.155 with SMTP; 2 Jun 2010 11:30:39 -0000 Received: (qmail 50609 invoked by uid 500); 2 Jun 2010 11:30:39 -0000 Delivered-To: belljames@example.org Received: (qmail 50565 invoked by uid 500); 2 Jun 2010 11:30:38 -0000 Mailing-List: contact belljames@example.org; run by ezmlm Precedence: bulk List-Help: <mailto:belljames@example.org> List-Unsubscribe: <mailto:belljames@example.org> List-Post: <mailto:belljames@example.org> List-Id: <commits.poi.apache.org> Reply-To: belljames@example.org Delivered-To: mailing list belljames@example.org Received: (qmail 50558 invoked by uid 99); 2 Jun 2010 11:30:37 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Jun 2010 11:30:37 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [83.161.120.155] (HELO eris.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Jun 2010 11:30:34 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 8F26923888FE; Wed, 2 Jun 2010 11:30:13 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r950499 - /poi/trunk/src/documentation/content/xdocs/faq.xml Date: Wed, 02 Jun 2010 11:30:13 -0000 To: belljames@example.org From: belljames@example.org X-Mailer: svnmailer-1.0.8 Message-Id: <belljames@example.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: nick Date: Wed Jun 2 11:30:13 2010 New Revision: 950499 URL: http://svn.apache.org/viewvc?rev=950499&view=rev Log: Update the FAQ with a note about poi-ooxml-schemas vs the full ooxml-schemas Modified: poi/trunk/src/documentation/content/xdocs/faq.xml Modified: poi/trunk/src/documentation/content/xdocs/faq.xml URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/faq.xml?rev=950499&r1=950498&r2=950499&view=diff ============================================================================== --- poi/trunk/src/documentation/content/xdocs/faq.xml (original) +++ poi/trunk/src/documentation/content/xdocs/faq.xml Wed Jun 2 11:30:13 2010 @@ -53,6 +53,45 @@ System.out.println("Core POI came from " </faq> <faq> <question> + I'm using the poi-ooxml-schemas jar, but my code is failing with "java.lang.NoClassDefFoundError: org/openxmlformats/schemas/*something*" + </question> + <answer> + <p>To use the new OOXML file formats, POI requires a jar containing + the file format XSDs, as compiled by + <link href="http://xmlbeans.apache.org/">XMLBeans</link>. These + XSDs, once compiled into Java classes, live in the + <em>org.openxmlformats.schemas</em> namespace.</p> + <p>There are two jar files available, as described in + <link href="/overview.html">the components overview section</link>. + The <em>full jar of all of the schemas is ooxml-schemas-1.0.jar</em>, + and it is currently around 15mb. The <em>smaller poi-ooxml-schemas + jar</em> is only about 4mb. This latter jar file only contains the + typically used parts though.</p> + <p>Many users choose to use the smaller poi-ooxml-schemas jar to save + space. However, the poi-ooxml-schemas jar only contains the XSDs and + classes that are typically used, as identified by the unit tests. + Every so often, you may try to use part of the file format which + isn't included in the minimal poi-ooxml-schemas jar. In this case, + you should switch to the full ooxml-schemas-1.0.jar. Longer term, + you may also wish to submit a new unit test which uses the extra + parts of the XSDs, so that a future poi-ooxml-schemas jar will + include them.</p> + <p>There are a number of ways to get the full ooxml-schemas-1.0.jar. + If you are a maven user, see the + <link href="/overview.html">the components overview section</link> + for the artifact details to have maven download it for you. + If you download the source release of POI, and/or checkout the + source code from <link href="/subversion.html">subversion</link>, + then you can run the ant task "compile-ooxml-xsds" to have the + OOXML schemas downloaded and compiled for you (This will also + give you the XMLBeans generated source code, in case you wish to + look at this). Finally, you can download the jar by hand from the + <link href="http://www.ibiblio.org/maven/org.apache.poi/jars/">POI + Maven Repository</link>.</p> + </answer> + </faq> + <faq> + <question> Why is reading a simple sheet taking so long? </question> <answer> --------------------------------------------------------------------- To unsubscribe, e-mail: belljames@example.org For additional commands, e-mail: belljames@example.org From belljames@example.org Wed Jun 02 15:24:39 2010 Return-Path: <belljames@example.org> Delivered-To: belljames@example.org Received: (qmail 95856 invoked from network); 2 Jun 2010 15:24:38 -0000 Received: from unknown (HELO mail.apache.org) (83.161.120.155) by 83.161.120.155 with SMTP; 2 Jun 2010 15:24:38 -0000 Received: (qmail 9730 invoked by uid 500); 2 Jun 2010 15:24:38 -0000 Delivered-To: belljames@example.org Received: (qmail 9684 invoked by uid 500); 2 Jun 2010 15:24:38 -0000 Mailing-List: contact belljames@example.org; run by ezmlm Precedence: bulk List-Help: <mailto:belljames@example.org> List-Unsubscribe: <mailto:belljames@example.org> List-Post: <mailto:belljames@example.org> List-Id: <commits.poi.apache.org> Reply-To: belljames@example.org Delivered-To: mailing list belljames@example.org Received: (qmail 9677 invoked by uid 99); 2 Jun 2010 15:24:38 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Jun 2010 15:24:38 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [83.161.120.155] (HELO eris.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Jun 2010 15:24:35 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 004152388978; Wed, 2 Jun 2010 15:24:13 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r950595 - in /poi/trunk/src: documentation/content/xdocs/status.xml scratchpad/src/org/apache/poi/hsmf/extractor/OutlookTextExtactor.java scratchpad/testcases/org/apache/poi/hsmf/extractor/TestOutlookTextExtractor.java Date: Wed, 02 Jun 2010 15:24:13 -0000 To: belljames@example.org From: belljames@example.org X-Mailer: svnmailer-1.0.8 Message-Id: <belljames@example.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: nick Date: Wed Jun 2 15:24:11 2010 New Revision: 950595 URL: http://svn.apache.org/viewvc?rev=950595&view=rev Log: List attachment names in the output of OutlookTextExtractor (to get attachment contents, use ExtractorFactory as normal) Modified: poi/trunk/src/documentation/content/xdocs/status.xml poi/trunk/src/scratchpad/src/org/apache/poi/hsmf/extractor/OutlookTextExtactor.java poi/trunk/src/scratchpad/testcases/org/apache/poi/hsmf/extractor/TestOutlookTextExtractor.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=950595&r1=950594&r2=950595&view=diff ============================================================================== --- poi/trunk/src/documentation/content/xdocs/status.xml (original) +++ poi/trunk/src/documentation/content/xdocs/status.xml Wed Jun 2 15:24:11 2010 @@ -34,6 +34,7 @@ <changes> <release version="3.7-SNAPSHOT" date="2010-??-??"> + <action dev="POI-DEVELOPERS" type="add">List attachment names in the output of OutlookTextExtractor (to get attachment contents, use ExtractorFactory as normal)</action> <action dev="POI-DEVELOPERS" type="fix">48872 - allow DateFormatter.formatRawCellContents to handle 1904 as well as 1900 dates</action> <action dev="POI-DEVELOPERS" type="fix">48872 - handle MMMMM and elapsed time formatting rules in DataFormatter</action> <action dev="POI-DEVELOPERS" type="fix">48872 - handle zero formatting rules, and better color detection in DataFormatter</action> Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hsmf/extractor/OutlookTextExtactor.java URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hsmf/extractor/OutlookTextExtactor.java?rev=950595&r1=950594&r2=950595&view=diff ============================================================================== --- poi/trunk/src/scratchpad/src/org/apache/poi/hsmf/extractor/OutlookTextExtactor.java (original) +++ poi/trunk/src/scratchpad/src/org/apache/poi/hsmf/extractor/OutlookTextExtactor.java Wed Jun 2 15:24:11 2010 @@ -22,6 +22,7 @@ import java.text.SimpleDateFormat; import org.apache.poi.POIOLE2TextExtractor; import org.apache.poi.hsmf.MAPIMessage; +import org.apache.poi.hsmf.datatypes.AttachmentChunks; import org.apache.poi.hsmf.exceptions.ChunkNotFoundException; import org.apache.poi.poifs.filesystem.DirectoryNode; import org.apache.poi.poifs.filesystem.POIFSFileSystem; @@ -93,6 +94,18 @@ public class OutlookTextExtactor extends try { s.append("Subject: " + msg.getSubject() + "\n"); } catch(ChunkNotFoundException e) {} + + // Display attachment names + // To get the attachments, use ExtractorFactory + for(AttachmentChunks att : msg.getAttachmentFiles()) { + String ats = att.attachLongFileName.getValue(); + if(att.attachMimeTag != null && + att.attachMimeTag.getValue() != null) { + ats = att.attachMimeTag.getValue() + " = " + ats; + } + s.append("Attachment: " + ats + "\n"); + } + try { s.append("\n" + msg.getTextBody() + "\n"); } catch(ChunkNotFoundException e) {} Modified: poi/trunk/src/scratchpad/testcases/org/apache/poi/hsmf/extractor/TestOutlookTextExtractor.java URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hsmf/extractor/TestOutlookTextExtractor.java?rev=950595&r1=950594&r2=950595&view=diff ============================================================================== --- poi/trunk/src/scratchpad/testcases/org/apache/poi/hsmf/extractor/TestOutlookTextExtractor.java (original) +++ poi/trunk/src/scratchpad/testcases/org/apache/poi/hsmf/extractor/TestOutlookTextExtractor.java Wed Jun 2 15:24:11 2010 @@ -26,6 +26,9 @@ import java.util.GregorianCalendar; import junit.framework.TestCase; import org.apache.poi.POIDataSamples; +import org.apache.poi.POITextExtractor; +import org.apache.poi.extractor.ExtractorFactory; +import org.apache.poi.extractor.TestExtractorFactory; import org.apache.poi.hsmf.MAPIMessage; import org.apache.poi.poifs.filesystem.POIFSFileSystem; @@ -59,6 +62,7 @@ public final class TestOutlookTextExtrac assertContains(text, "To: Kevin Roast <belljames@example.org>\n"); assertEquals(-1, text.indexOf("CC:")); assertEquals(-1, text.indexOf("BCC:")); + assertEquals(-1, text.indexOf("Attachment:")); assertContains(text, "Subject: Test the content transformer\n"); Calendar cal = new GregorianCalendar(2007, 5, 14, 9, 42, 55); SimpleDateFormat f = new SimpleDateFormat("E, d MMM yyyy HH:mm:ss"); @@ -171,4 +175,31 @@ public final class TestOutlookTextExtrac assertContains(text, "The quick brown fox jumps over the lazy dog"); } } + + /** + * See also {@link TestExtractorFactory#testEmbeded()} + */ + public void testWithAttachments() throws Exception { + POIFSFileSystem simple = new POIFSFileSystem( + new FileInputStream(samples.getFile("attachment_test_msg.msg")) + ); + MAPIMessage msg = new MAPIMessage(simple); + OutlookTextExtactor ext = new OutlookTextExtactor(msg); + + // Check the normal bits + String text = ext.getText(); + + assertContains(text, "From: Nicolas1"); + assertContains(text, "To: 'belljames@example.org'"); + assertEquals(-1, text.indexOf("CC:")); + assertEquals(-1, text.indexOf("BCC:")); + assertContains(text, "Subject: test"); + assertEquals(-1, text.indexOf("Date:")); + assertContains(text, "Attachment: test-unicode.doc\n"); + assertContains(text, "Attachment: pj1.txt\n"); + assertContains(text, "contenu"); + + // Embeded bits are checked in + // TestExtractorFactory + } } --------------------------------------------------------------------- To unsubscribe, e-mail: belljames@example.org For additional commands, e-mail: belljames@example.org From belljames@example.org Wed Jun 02 16:01:51 2010 Return-Path: <belljames@example.org> Delivered-To: belljames@example.org Received: (qmail 6726 invoked from network); 2 Jun 2010 16:01:50 -0000 Received: from unknown (HELO mail.apache.org) (83.161.120.155) by 83.161.120.155 with SMTP; 2 Jun 2010 16:01:50 -0000 Received: (qmail 92920 invoked by uid 500); 2 Jun 2010 16:01:50 -0000 Delivered-To: belljames@example.org Received: (qmail 92878 invoked by uid 500); 2 Jun 2010 16:01:50 -0000 Mailing-List: contact belljames@example.org; run by ezmlm Precedence: bulk List-Help: <mailto:belljames@example.org> List-Unsubscribe: <mailto:belljames@example.org> List-Post: <mailto:belljames@example.org> List-Id: <commits.poi.apache.org> Reply-To: belljames@example.org Delivered-To: mailing list belljames@example.org Received: (qmail 92871 invoked by uid 99); 2 Jun 2010 16:01:50 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Jun 2010 16:01:50 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [83.161.120.155] (HELO eris.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Jun 2010 16:01:48 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 133232388978; Wed, 2 Jun 2010 16:01:27 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r950611 - in /poi/trunk: src/documentation/content/xdocs/ src/java/org/apache/poi/hssf/record/chart/ src/testcases/org/apache/poi/hssf/usermodel/ test-data/spreadsheet/ Date: Wed, 02 Jun 2010 16:01:26 -0000 To: belljames@example.org From: belljames@example.org X-Mailer: svnmailer-1.0.8 Message-Id: <belljames@example.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: nick Date: Wed Jun 2 16:01:26 2010 New Revision: 950611 URL: http://svn.apache.org/viewvc?rev=950611&view=rev Log: Fix bug #49096 - add clone support to Chart begin and end records, to allow cloning of more Chart containing sheets Added: poi/trunk/test-data/spreadsheet/49096.xls (with props) Modified: poi/trunk/src/documentation/content/xdocs/status.xml poi/trunk/src/java/org/apache/poi/hssf/record/chart/BeginRecord.java poi/trunk/src/java/org/apache/poi/hssf/record/chart/EndRecord.java poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.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=950611&r1=950610&r2=950611&view=diff ============================================================================== --- poi/trunk/src/documentation/content/xdocs/status.xml (original) +++ poi/trunk/src/documentation/content/xdocs/status.xml Wed Jun 2 16:01:26 2010 @@ -34,6 +34,7 @@ <changes> <release version="3.7-SNAPSHOT" date="2010-??-??"> + <action dev="POI-DEVELOPERS" type="fix">49096 - add clone support to Chart begin and end records, to allow cloning of more Chart containing sheets</action> <action dev="POI-DEVELOPERS" type="add">List attachment names in the output of OutlookTextExtractor (to get attachment contents, use ExtractorFactory as normal)</action> <action dev="POI-DEVELOPERS" type="fix">48872 - allow DateFormatter.formatRawCellContents to handle 1904 as well as 1900 dates</action> <action dev="POI-DEVELOPERS" type="fix">48872 - handle MMMMM and elapsed time formatting rules in DataFormatter</action> Modified: poi/trunk/src/java/org/apache/poi/hssf/record/chart/BeginRecord.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/chart/BeginRecord.java?rev=950611&r1=950610&r2=950611&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/hssf/record/chart/BeginRecord.java (original) +++ poi/trunk/src/java/org/apache/poi/hssf/record/chart/BeginRecord.java Wed Jun 2 16:01:26 2010 @@ -63,4 +63,10 @@ public final class BeginRecord extends S { return sid; } + + public Object clone() { + BeginRecord br = new BeginRecord(); + // No data so nothing to copy + return br; + } } Modified: poi/trunk/src/java/org/apache/poi/hssf/record/chart/EndRecord.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/chart/EndRecord.java?rev=950611&r1=950610&r2=950611&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/hssf/record/chart/EndRecord.java (original) +++ poi/trunk/src/java/org/apache/poi/hssf/record/chart/EndRecord.java Wed Jun 2 16:01:26 2010 @@ -64,4 +64,10 @@ public final class EndRecord extends Sta { return sid; } + + public Object clone() { + EndRecord er = new EndRecord(); + // No data so nothing to copy + return er; + } } Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java?rev=950611&r1=950610&r2=950611&view=diff ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java (original) +++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java Wed Jun 2 16:01:26 2010 @@ -1572,4 +1572,20 @@ public final class TestBugs extends Base assertEquals("RT", withoutExt.getString()); assertTrue((withoutExt.getOptionFlags() & 0x0004) == 0x0000); } + + /** + * Problem with cloning a sheet with a chart + * contained in it. + */ + public void test49096() throws Exception { + HSSFWorkbook wb = openSample("49096.xls"); + assertEquals(1, wb.getNumberOfSheets()); + + assertNotNull(wb.getSheetAt(0)); + wb.cloneSheet(0); + assertEquals(2, wb.getNumberOfSheets()); + + wb = writeOutAndReadBack(wb); + assertEquals(2, wb.getNumberOfSheets()); + } } Added: poi/trunk/test-data/spreadsheet/49096.xls URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/49096.xls?rev=950611&view=auto ============================================================================== Binary file - no diff available. Propchange: poi/trunk/test-data/spreadsheet/49096.xls ------------------------------------------------------------------------------ svn:mime-type = application/octet-stream --------------------------------------------------------------------- To unsubscribe, e-mail: belljames@example.org For additional commands, e-mail: belljames@example.org From belljames@example.org Wed Jun 02 16:13:29 2010 Return-Path: <belljames@example.org> Delivered-To: belljames@example.org Received: (qmail 12865 invoked from network); 2 Jun 2010 16:13:29 -0000 Received: from unknown (HELO mail.apache.org) (83.161.120.155) by 83.161.120.155 with SMTP; 2 Jun 2010 16:13:29 -0000 Received: (qmail 5719 invoked by uid 500); 2 Jun 2010 16:13:29 -0000 Delivered-To: belljames@example.org Received: (qmail 5687 invoked by uid 500); 2 Jun 2010 16:13:29 -0000 Mailing-List: contact belljames@example.org; run by ezmlm Precedence: bulk List-Help: <mailto:belljames@example.org> List-Unsubscribe: <mailto:belljames@example.org> List-Post: <mailto:belljames@example.org> List-Id: <commits.poi.apache.org> Reply-To: belljames@example.org Delivered-To: mailing list belljames@example.org Received: (qmail 5680 invoked by uid 99); 2 Jun 2010 16:13:29 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Jun 2010 16:13:29 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [83.161.120.155] (HELO eris.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Jun 2010 16:13:26 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 66328238890D; Wed, 2 Jun 2010 16:13:05 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r950616 - in /poi/trunk/src: documentation/content/xdocs/status.xml java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java Date: Wed, 02 Jun 2010 16:13:05 -0000 To: belljames@example.org From: belljames@example.org X-Mailer: svnmailer-1.0.8 Message-Id: <belljames@example.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: nick Date: Wed Jun 2 16:13:04 2010 New Revision: 950616 URL: http://svn.apache.org/viewvc?rev=950616&view=rev Log: Fix bug #48494 - have EventBasedExcelExtractor make use of HSSFDataFormatter, so that numbers and dates come out closer to how Excel would render them Modified: poi/trunk/src/documentation/content/xdocs/status.xml poi/trunk/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java poi/trunk/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.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=950616&r1=950615&r2=950616&view=diff ============================================================================== --- poi/trunk/src/documentation/content/xdocs/status.xml (original) +++ poi/trunk/src/documentation/content/xdocs/status.xml Wed Jun 2 16:13:04 2010 @@ -34,6 +34,7 @@ <changes> <release version="3.7-SNAPSHOT" date="2010-??-??"> + <action dev="POI-DEVELOPERS" type="fix">48494 - have EventBasedExcelExtractor make use of HSSFDataFormatter, so that numbers and dates come out closer to how Excel would render them</action> <action dev="POI-DEVELOPERS" type="fix">49096 - add clone support to Chart begin and end records, to allow cloning of more Chart containing sheets</action> <action dev="POI-DEVELOPERS" type="add">List attachment names in the output of OutlookTextExtractor (to get attachment contents, use ExtractorFactory as normal)</action> <action dev="POI-DEVELOPERS" type="fix">48872 - allow DateFormatter.formatRawCellContents to handle 1904 as well as 1900 dates</action> Modified: poi/trunk/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java?rev=950616&r1=950615&r2=950616&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java (original) +++ poi/trunk/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java Wed Jun 2 16:13:04 2010 @@ -18,11 +18,7 @@ package org.apache.poi.hssf.extractor; import java.io.IOException; -import java.text.DateFormat; -import java.text.DecimalFormat; -import java.text.SimpleDateFormat; import java.util.ArrayList; -import java.util.Date; import java.util.List; import org.apache.poi.POIOLE2TextExtractor; @@ -35,7 +31,6 @@ import org.apache.poi.hssf.eventusermode import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.record.BOFRecord; import org.apache.poi.hssf.record.BoundSheetRecord; -import org.apache.poi.hssf.record.CellValueRecordInterface; import org.apache.poi.hssf.record.FormulaRecord; import org.apache.poi.hssf.record.LabelRecord; import org.apache.poi.hssf.record.LabelSSTRecord; @@ -44,7 +39,6 @@ import org.apache.poi.hssf.record.Number import org.apache.poi.hssf.record.Record; import org.apache.poi.hssf.record.SSTRecord; import org.apache.poi.hssf.record.StringRecord; -import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.DirectoryNode; import org.apache.poi.poifs.filesystem.POIFSFileSystem; @@ -206,7 +200,7 @@ public class EventBasedExcelExtractor ex outputNextStringValue = true; nextRow = frec.getRow(); } else { - thisText = formatNumberDateCell(frec, frec.getValue()); + thisText = _ft.formatNumberDateCell(frec); } } break; @@ -240,7 +234,7 @@ public class EventBasedExcelExtractor ex case NumberRecord.sid: NumberRecord numrec = (NumberRecord) record; thisRow = numrec.getRow(); - thisText = formatNumberDateCell(numrec, numrec.getValue()); + thisText = _ft.formatNumberDateCell(numrec); break; default: break; @@ -257,40 +251,5 @@ public class EventBasedExcelExtractor ex _text.append(thisText); } } - - /** - * Formats a number or date cell, be that a real number, or the - * answer to a formula - */ - private String formatNumberDateCell(CellValueRecordInterface cell, double value) { - // Get the built in format, if there is one - int formatIndex = _ft.getFormatIndex(cell); - String formatString = _ft.getFormatString(cell); - - if(formatString == null) { - return Double.toString(value); - } - // Is it a date? - if(HSSFDateUtil.isADateFormat(formatIndex,formatString) && - HSSFDateUtil.isValidExcelDate(value)) { - // Java wants M not m for month - formatString = formatString.replace('m','M'); - // Change \- into -, if it's there - formatString = formatString.replaceAll("\\\\-","-"); - - // Format as a date - Date d = HSSFDateUtil.getJavaDate(value, false); - DateFormat df = new SimpleDateFormat(formatString); - return df.format(d); - } - if(formatString == "General") { - // Some sort of wierd default - return Double.toString(value); - } - - // Format as a number - DecimalFormat df = new DecimalFormat(formatString); - return df.format(value); - } } } Modified: poi/trunk/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java?rev=950616&r1=950615&r2=950616&view=diff ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java (original) +++ poi/trunk/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java Wed Jun 2 16:13:04 2010 @@ -158,11 +158,11 @@ public final class TestExcelExtractor ex text = extractor.getText(); assertEquals( - "1000.0\t1.0\tSUMIF(A1:A5,\">4000\",B1:B5)\n" + - "2000.0\t2.0\n" + - "3000.0\t3.0\n" + - "4000.0\t4.0\n" + - "5000.0\t5.0\n", + "1000\t1\tSUMIF(A1:A5,\">4000\",B1:B5)\n" + + "2000\t2\n" + + "3000\t3\n" + + "4000\t4\n" + + "5000\t5\n", text ); } --------------------------------------------------------------------- To unsubscribe, e-mail: belljames@example.org For additional commands, e-mail: belljames@example.org From belljames@example.org Wed Jun 02 16:51:27 2010 Return-Path: <belljames@example.org> Delivered-To: belljames@example.org Received: (qmail 21627 invoked from network); 2 Jun 2010 16:51:27 -0000 Received: from unknown (HELO mail.apache.org) (83.161.120.155) by 83.161.120.155 with SMTP; 2 Jun 2010 16:51:27 -0000 Received: (qmail 83666 invoked by uid 500); 2 Jun 2010 16:51:27 -0000 Delivered-To: belljames@example.org Received: (qmail 83634 invoked by uid 500); 2 Jun 2010 16:51:27 -0000 Mailing-List: contact belljames@example.org; run by ezmlm Precedence: bulk List-Help: <mailto:belljames@example.org> List-Unsubscribe: <mailto:belljames@example.org> List-Post: <mailto:belljames@example.org> List-Id: <commits.poi.apache.org> Reply-To: belljames@example.org Delivered-To: mailing list belljames@example.org Received: (qmail 83627 invoked by uid 99); 2 Jun 2010 16:51:26 -0000 Received: from athena.apache.org (HELO athena.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Jun 2010 16:51:26 +0000 X-ASF-Spam-Status: No, hits=-1500.7 required=10.0 tests=ALL_TRUSTED,AWL X-Spam-Check-By: apache.org Received: from [83.161.120.155] (HELO eris.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Jun 2010 16:51:26 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 908E02388978; Wed, 2 Jun 2010 16:51:05 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Subject: svn commit: r950657 - in /poi/trunk/src: documentation/content/xdocs/ java/org/apache/poi/hssf/extractor/ testcases/org/apache/poi/hssf/extractor/ testcases/org/apache/poi/hssf/usermodel/ Date: Wed, 02 Jun 2010 16:51:05 -0000 To: belljames@example.org From: belljames@example.org X-Mailer: svnmailer-1.0.8 Message-Id: <belljames@example.org> Author: nick Date: Wed Jun 2 16:51:05 2010 New Revision: 950657 URL: http://svn.apache.org/viewvc?rev=950657&view=rev Log: Another fix inspired by bug #48494 - have ExcelExtractor make use of HSSFDataFormatter, so that numbers and dates come out closer to how Excel would render them Modified: poi/trunk/src/documentation/content/xdocs/status.xml poi/trunk/src/java/org/apache/poi/hssf/extractor/ExcelExtractor.java poi/trunk/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.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=950657&r1=950656&r2=950657&view=diff ============================================================================== --- poi/trunk/src/documentation/content/xdocs/status.xml (original) +++ poi/trunk/src/documentation/content/xdocs/status.xml Wed Jun 2 16:51:05 2010 @@ -34,6 +34,7 @@ <changes> <release version="3.7-SNAPSHOT" date="2010-??-??"> + <action dev="POI-DEVELOPERS" type="fix">48494 - have ExcelExtractor make use of HSSFDataFormatter, so that numbers and dates come out closer to how Excel would render them</action> <action dev="POI-DEVELOPERS" type="fix">48494 - have EventBasedExcelExtractor make use of HSSFDataFormatter, so that numbers and dates come out closer to how Excel would render them</action> <action dev="POI-DEVELOPERS" type="fix">49096 - add clone support to Chart begin and end records, to allow cloning of more Chart containing sheets</action> <action dev="POI-DEVELOPERS" type="add">List attachment names in the output of OutlookTextExtractor (to get attachment contents, use ExtractorFactory as normal)</action> Modified: poi/trunk/src/java/org/apache/poi/hssf/extractor/ExcelExtractor.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/extractor/ExcelExtractor.java?rev=950657&r1=950656&r2=950657&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/hssf/extractor/ExcelExtractor.java (original) +++ poi/trunk/src/java/org/apache/poi/hssf/extractor/ExcelExtractor.java Wed Jun 2 16:51:05 2010 @@ -26,7 +26,9 @@ import java.io.PrintStream; import org.apache.poi.POIOLE2TextExtractor; import org.apache.poi.hssf.record.formula.eval.ErrorEval; import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFComment; +import org.apache.poi.hssf.usermodel.HSSFDataFormatter; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; @@ -51,6 +53,7 @@ import org.apache.poi.ss.usermodel.Heade */ public class ExcelExtractor extends POIOLE2TextExtractor implements org.apache.poi.ss.extractor.ExcelExtractor { private HSSFWorkbook _wb; + private HSSFDataFormatter _formatter; private boolean _includeSheetNames = true; private boolean _shouldEvaluateFormulas = true; private boolean _includeCellComments = false; @@ -60,6 +63,7 @@ public class ExcelExtractor extends POIO public ExcelExtractor(HSSFWorkbook wb) { super(wb); _wb = wb; + _formatter = new HSSFDataFormatter(); } public ExcelExtractor(POIFSFileSystem fs) throws IOException { this(fs.getRoot(), fs); @@ -323,8 +327,9 @@ public class ExcelExtractor extends POIO text.append(cell.getRichStringCellValue().getString()); break; case HSSFCell.CELL_TYPE_NUMERIC: - // Note - we don't apply any formatting! - text.append(cell.getNumericCellValue()); + text.append( + _formatter.formatCellValue(cell) + ); break; case HSSFCell.CELL_TYPE_BOOLEAN: text.append(cell.getBooleanCellValue()); @@ -344,7 +349,18 @@ public class ExcelExtractor extends POIO } break; case HSSFCell.CELL_TYPE_NUMERIC: - text.append(cell.getNumericCellValue()); + HSSFCellStyle style = cell.getCellStyle(); + if(style == null) { + text.append( cell.getNumericCellValue() ); + } else { + text.append( + _formatter.formatRawCellContents( + cell.getNumericCellValue(), + style.getDataFormat(), + style.getDataFormatString() + ) + ); + } break; case HSSFCell.CELL_TYPE_BOOLEAN: text.append(cell.getBooleanCellValue()); Modified: poi/trunk/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java?rev=950657&r1=950656&r2=950657&view=diff ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java (original) +++ poi/trunk/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java Wed Jun 2 16:51:05 2010 @@ -63,11 +63,11 @@ public final class TestExcelExtractor ex assertEquals( "Sheet1\n" + - "1000.0\t1.0\t5.0\n" + - "2000.0\t2.0\n" + - "3000.0\t3.0\n" + - "4000.0\t4.0\n" + - "5000.0\t5.0\n" + + "1000\t1\t5\n" + + "2000\t2\n" + + "3000\t3\n" + + "4000\t4\n" + + "5000\t5\n" + "Sheet2\nSheet3\n", extractor.getText() ); @@ -76,11 +76,11 @@ public final class TestExcelExtractor ex assertEquals( "Sheet1\n" + - "1000.0\t1.0\tSUMIF(A1:A5,\">4000\",B1:B5)\n" + - "2000.0\t2.0\n" + - "3000.0\t3.0\n" + - "4000.0\t4.0\n" + - "5000.0\t5.0\n" + + "1000\t1\tSUMIF(A1:A5,\">4000\",B1:B5)\n" + + "2000\t2\n" + + "3000\t3\n" + + "4000\t4\n" + + "5000\t5\n" + "Sheet2\nSheet3\n", extractor.getText() ); @@ -173,18 +173,18 @@ public final class TestExcelExtractor ex // Check without comments assertEquals( - "1.0\tone\n" + - "2.0\ttwo\n" + - "3.0\tthree\n", + "1\tone\n" + + "2\ttwo\n" + + "3\tthree\n", extractor.getText() ); // Now with extractor.setIncludeCellComments(true); assertEquals( - "1.0\tone Comment by Yegor Kozlov: Yegor Kozlov: first cell\n" + - "2.0\ttwo Comment by Yegor Kozlov: Yegor Kozlov: second cell\n" + - "3.0\tthree Comment by Yegor Kozlov: Yegor Kozlov: third cell\n", + "1\tone Comment by Yegor Kozlov: Yegor Kozlov: first cell\n" + + "2\ttwo Comment by Yegor Kozlov: Yegor Kozlov: second cell\n" + + "3\tthree Comment by Yegor Kozlov: Yegor Kozlov: third cell\n", extractor.getText() ); } @@ -199,20 +199,65 @@ public final class TestExcelExtractor ex "Sheet1\n" + "&[TAB]\t\n" + "Hello\n" + - "11.0\t23.0\n" + "11\t23\n" )); assertTrue(padded.startsWith( "Sheet1\n" + "&[TAB]\t\n" + "Hello\n" + - "11.0\t\t\t23.0\n" + "11\t\t\t23\n" )); } + public void testFormatting() throws Exception { + ExcelExtractor extractor = createExtractor("Formatting.xls"); + extractor.setIncludeBlankCells(false); + extractor.setIncludeSheetNames(false); + String text = extractor.getText(); + + // Note - not all the formats in the file + // actually quite match what they claim to + // be, as some are auto-local builtins... + + assertTrue(text.startsWith( + "Dates, all 24th November 2006\n" + )); + assertTrue( + text.indexOf( + "yyyy/mm/dd\t2006/11/24\n" + ) > -1 + ); + assertTrue( + text.indexOf( + "yyyy-mm-dd\t2006-11-24\n" + ) > -1 + ); + assertTrue( + text.indexOf( + "dd-mm-yy\t24-11-06\n" + ) > -1 + ); + + assertTrue( + text.indexOf( + "nn.nn\t10.52\n" + ) > -1 + ); + assertTrue( + text.indexOf( + "nn.nnn\t10.520\n" + ) > -1 + ); + assertTrue( + text.indexOf( + "£nn.nn\t£10.52\n" + ) > -1 + ); + } /** - * Embded in a non-excel file + * Embeded in a non-excel file */ public void testWithEmbeded() throws Exception { POIFSFileSystem fs = new POIFSFileSystem( Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java?rev=950657&r1=950656&r2=950657&view=diff ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java (original) +++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java Wed Jun 2 16:51:05 2010 @@ -309,6 +309,63 @@ public final class TestHSSFDataFormatter assertEquals("2345", f.formatCellValue(cellA1)); } + + /** + * Tests various formattings of dates and numbers + */ + public void testFromFile() { + HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("Formatting.xls"); + HSSFSheet sheet = workbook.getSheetAt(0); + + HSSFDataFormatter f = new HSSFDataFormatter(); + + // This one is one of the nasty auto-locale changing ones... + assertEquals("dd/mm/yyyy", sheet.getRow(1).getCell(0).getStringCellValue()); + assertEquals("m/d/yy", sheet.getRow(1).getCell(1).getCellStyle().getDataFormatString()); + assertEquals("11/24/06", f.formatCellValue(sheet.getRow(1).getCell(1))); + + assertEquals("yyyy/mm/dd", sheet.getRow(2).getCell(0).getStringCellValue()); + assertEquals("yyyy/mm/dd", sheet.getRow(2).getCell(1).getCellStyle().getDataFormatString()); + assertEquals("2006/11/24", f.formatCellValue(sheet.getRow(2).getCell(1))); + + assertEquals("yyyy-mm-dd", sheet.getRow(3).getCell(0).getStringCellValue()); + assertEquals("yyyy\\-mm\\-dd", sheet.getRow(3).getCell(1).getCellStyle().getDataFormatString()); + assertEquals("2006-11-24", f.formatCellValue(sheet.getRow(3).getCell(1))); + + assertEquals("yy/mm/dd", sheet.getRow(4).getCell(0).getStringCellValue()); + assertEquals("yy/mm/dd", sheet.getRow(4).getCell(1).getCellStyle().getDataFormatString()); + assertEquals("06/11/24", f.formatCellValue(sheet.getRow(4).getCell(1))); + + // Another builtin fun one + assertEquals("dd/mm/yy", sheet.getRow(5).getCell(0).getStringCellValue()); + assertEquals("d/m/yy;@", sheet.getRow(5).getCell(1).getCellStyle().getDataFormatString()); + assertEquals("24/11/06", f.formatCellValue(sheet.getRow(5).getCell(1))); + + assertEquals("dd-mm-yy", sheet.getRow(6).getCell(0).getStringCellValue()); + assertEquals("dd\\-mm\\-yy", sheet.getRow(6).getCell(1).getCellStyle().getDataFormatString()); + assertEquals("24-11-06", f.formatCellValue(sheet.getRow(6).getCell(1))); + + + // Another builtin fun one + assertEquals("nn.nn", sheet.getRow(9).getCell(0).getStringCellValue()); + assertEquals("General", sheet.getRow(9).getCell(1).getCellStyle().getDataFormatString()); + assertEquals("10.52", f.formatCellValue(sheet.getRow(9).getCell(1))); + + // text isn't quite the format rule... + assertEquals("nn.nnn", sheet.getRow(10).getCell(0).getStringCellValue()); + assertEquals("0.000", sheet.getRow(10).getCell(1).getCellStyle().getDataFormatString()); + assertEquals("10.520", f.formatCellValue(sheet.getRow(10).getCell(1))); + + // text isn't quite the format rule... + assertEquals("nn.n", sheet.getRow(11).getCell(0).getStringCellValue()); + assertEquals("0.0", sheet.getRow(11).getCell(1).getCellStyle().getDataFormatString()); + assertEquals("10.5", f.formatCellValue(sheet.getRow(11).getCell(1))); + + // text isn't quite the format rule... + assertEquals("£nn.nn", sheet.getRow(12).getCell(0).getStringCellValue()); + assertEquals("\"£\"#,##0.00", sheet.getRow(12).getCell(1).getCellStyle().getDataFormatString()); + assertEquals("£10.52", f.formatCellValue(sheet.getRow(12).getCell(1))); + } private static void log(String msg) { if (false) { // successful tests should be silent --------------------------------------------------------------------- To unsubscribe, e-mail: belljames@example.org For additional commands, e-mail: belljames@example.org From belljames@example.org Wed Jun 02 17:18:08 2010 Return-Path: <belljames@example.org> Delivered-To: belljames@example.org Received: (qmail 33824 invoked from network); 2 Jun 2010 17:18:08 -0000 Received: from unknown (HELO mail.apache.org) (83.161.120.155) by 83.161.120.155 with SMTP; 2 Jun 2010 17:18:08 -0000 Received: (qmail 33073 invoked by uid 500); 2 Jun 2010 17:18:08 -0000 Delivered-To: belljames@example.org Received: (qmail 33038 invoked by uid 500); 2 Jun 2010 17:18:08 -0000 Mailing-List: contact belljames@example.org; run by ezmlm Precedence: bulk List-Help: <mailto:belljames@example.org> List-Unsubscribe: <mailto:belljames@example.org> List-Post: <mailto:belljames@example.org> List-Id: <commits.poi.apache.org> Reply-To: belljames@example.org Delivered-To: mailing list belljames@example.org Received: (qmail 33031 invoked by uid 99); 2 Jun 2010 17:18:08 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Jun 2010 17:18:08 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [83.161.120.155] (HELO eris.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Jun 2010 17:18:04 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id B2AB9238898B; Wed, 2 Jun 2010 17:17:42 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r950665 - in /poi/trunk/src: documentation/content/xdocs/status.xml java/org/apache/poi/ss/usermodel/DataFormatter.java java/org/apache/poi/ss/usermodel/DateUtil.java testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java Date: Wed, 02 Jun 2010 17:17:42 -0000 To: belljames@example.org From: belljames@example.org X-Mailer: svnmailer-1.0.8 Message-Id: <belljames@example.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: nick Date: Wed Jun 2 17:17:42 2010 New Revision: 950665 URL: http://svn.apache.org/viewvc?rev=950665&view=rev Log: Fix inspired by bug #48494 - detect and support time formats like HH:MM;HH:MM Modified: poi/trunk/src/documentation/content/xdocs/status.xml poi/trunk/src/java/org/apache/poi/ss/usermodel/DataFormatter.java poi/trunk/src/java/org/apache/poi/ss/usermodel/DateUtil.java poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.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=950665&r1=950664&r2=950665&view=diff ============================================================================== --- poi/trunk/src/documentation/content/xdocs/status.xml (original) +++ poi/trunk/src/documentation/content/xdocs/status.xml Wed Jun 2 17:17:42 2010 @@ -34,6 +34,7 @@ <changes> <release version="3.7-SNAPSHOT" date="2010-??-??"> + <action dev="POI-DEVELOPERS" type="fix">48494 - detect and support time formats like HH:MM;HH:MM</action> <action dev="POI-DEVELOPERS" type="fix">48494 - have ExcelExtractor make use of HSSFDataFormatter, so that numbers and dates come out closer to how Excel would render them</action> <action dev="POI-DEVELOPERS" type="fix">48494 - have EventBasedExcelExtractor make use of HSSFDataFormatter, so that numbers and dates come out closer to how Excel would render them</action> <action dev="POI-DEVELOPERS" type="fix">49096 - add clone support to Chart begin and end records, to allow cloning of more Chart containing sheets</action> 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=950665&r1=950664&r2=950665&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 Wed Jun 2 17:17:42 2010 @@ -297,7 +297,7 @@ public class DataFormatter { } // Convert excel date format to SimpleDateFormat. - // Excel uses lower case 'm' for both minutes and months. + // Excel uses lower and upper case 'm' for both minutes and months. // From Excel help: /* The "m" or "mm" code must appear immediately after the "h" or"hh" @@ -319,7 +319,7 @@ public class DataFormatter { sb.append('H'); } } - else if (c == 'm') { + else if (c == 'm' || c == 'M') { if(mIsMonth) { sb.append('M'); ms.add( Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/DateUtil.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/DateUtil.java?rev=950665&r1=950664&r2=950665&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/ss/usermodel/DateUtil.java (original) +++ poi/trunk/src/java/org/apache/poi/ss/usermodel/DateUtil.java Wed Jun 2 17:17:42 2010 @@ -258,13 +258,19 @@ public class DateUtil { sb.append(c); } fs = sb.toString(); - + // If it starts with [$-...], then could be a date, but // who knows what that starting bit is all about fs = date_ptrn1.matcher(fs).replaceAll(""); // If it starts with something like [Black] or [Yellow], // then it could be a date fs = date_ptrn2.matcher(fs).replaceAll(""); + // You're allowed something like dd/mm/yy;[red]dd/mm/yy + // which would place dates before 1900/1904 in red + // For now, only consider the first one + if(fs.indexOf(';') > 0 && fs.indexOf(';') < fs.length()-1) { + fs = fs.substring(0, fs.indexOf(';')); + } // Otherwise, check it's only made up, in any case, of:
{'source': 'Apache Software Foundation Public Mail Archives'}