You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ni...@apache.org on 2010/06/02 19:17:42 UTC
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
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:
// y m d h 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=950665&r1=950664&r2=950665&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 17:17:42 2010
@@ -28,6 +28,7 @@ import java.util.Locale;
import org.apache.poi.hssf.HSSFTestDataSamples;
import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.DateUtil;
import junit.framework.TestCase;
@@ -56,9 +57,11 @@ public final class TestHSSFDataFormatter
// date value for July 8 1901 1:19 PM
double dateNum = 555.555;
+ // date value for July 8 1901 11:23 AM
+ double timeNum = 555.47431;
//valid date formats -- all should have "Jul" in output
- String[] goodDatePatterns ={
+ String[] goodDatePatterns = {
"[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy",
"mmm/d/yy\\ h:mm PM;@",
"mmmm/d/yy\\ h:mm;@",
@@ -78,6 +81,18 @@ public final class TestHSSFDataFormatter
"mmmm/d/yyyy;@",
"[$-409]d\\-mmm\\-yyyy;@"
};
+
+ //valid time formats - all should have 11:23 in output
+ String[] goodTimePatterns = {
+ "HH:MM",
+ "HH:MM:SS",
+ "HH:MM;HH:MM;HH:MM",
+ // This is fun - blue if positive time,
+ // red if negative time or green for zero!
+ "[BLUE]HH:MM;[RED]HH:MM;[GREEN]HH:MM",
+ "yyyy-mm-dd hh:mm",
+ "yyyy-mm-dd hh:mm:ss",
+ };
// valid number formats
String[] goodNumPatterns = {
@@ -111,6 +126,16 @@ public final class TestHSSFDataFormatter
cell.setCellStyle(cellStyle);
}
row = sheet.createRow(1);
+
+ // create cells with time patterns
+ for (int i = 0; i < goodTimePatterns.length; i++) {
+ HSSFCell cell = row.createCell(i);
+ cell.setCellValue(timeNum);
+ HSSFCellStyle cellStyle = wb.createCellStyle();
+ cellStyle.setDataFormat(format.getFormat(goodTimePatterns[i]));
+ cell.setCellStyle(cellStyle);
+ }
+ row = sheet.createRow(2);
// create cells with num patterns
for (int i = 0; i < goodNumPatterns.length; i++) {
@@ -120,7 +145,7 @@ public final class TestHSSFDataFormatter
cellStyle.setDataFormat(format.getFormat(goodNumPatterns[i]));
cell.setCellStyle(cellStyle);
}
- row = sheet.createRow(2);
+ row = sheet.createRow(3);
// create cells with bad num patterns
for (int i = 0; i < badNumPatterns.length; i++) {
@@ -134,7 +159,7 @@ public final class TestHSSFDataFormatter
// Built in formats
{ // Zip + 4 format
- row = sheet.createRow(3);
+ row = sheet.createRow(4);
HSSFCell cell = row.createCell(0);
cell.setCellValue(123456789);
HSSFCellStyle cellStyle = wb.createCellStyle();
@@ -143,7 +168,7 @@ public final class TestHSSFDataFormatter
}
{ // Phone number format
- row = sheet.createRow(4);
+ row = sheet.createRow(5);
HSSFCell cell = row.createCell(0);
cell.setCellValue(5551234567D);
HSSFCellStyle cellStyle = wb.createCellStyle();
@@ -152,7 +177,7 @@ public final class TestHSSFDataFormatter
}
{ // SSN format
- row = sheet.createRow(5);
+ row = sheet.createRow(6);
HSSFCell cell = row.createCell(0);
cell.setCellValue(444551234);
HSSFCellStyle cellStyle = wb.createCellStyle();
@@ -161,7 +186,7 @@ public final class TestHSSFDataFormatter
}
{ // formula cell
- row = sheet.createRow(6);
+ row = sheet.createRow(7);
HSSFCell cell = row.createCell(0);
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUM(12.25,12.25)/100");
@@ -184,8 +209,9 @@ public final class TestHSSFDataFormatter
String fmtval = formatter.formatCellValue(cell);
log(fmtval);
- // should not be equal to "555.555"
- assertTrue( ! "555.555".equals(fmtval));
+ // should not be equal to "555.555"
+ assertTrue( DateUtil.isCellDateFormatted(cell) );
+ assertTrue( ! "555.555".equals(fmtval));
String fmt = cell.getCellStyle().getDataFormatString();
@@ -201,6 +227,23 @@ public final class TestHSSFDataFormatter
assertTrue("Format came out incorrect - " + fmt, fmtval.indexOf(jul) > -1);
}
+ row = wb.getSheetAt(0).getRow(1);
+ it = row.cellIterator();
+ log("==== VALID TIME FORMATS ====");
+ while (it.hasNext()) {
+ Cell cell = it.next();
+ String fmt = cell.getCellStyle().getDataFormatString();
+ String fmtval = formatter.formatCellValue(cell);
+ log(fmtval);
+
+ // should not be equal to "555.47431"
+ assertTrue( DateUtil.isCellDateFormatted(cell) );
+ assertTrue( ! "555.47431".equals(fmtval));
+
+ // check we found the time properly
+ assertTrue("Format came out incorrect - " + fmt, fmtval.indexOf("11:23") > -1);
+ }
+
// test number formats
row = wb.getSheetAt(0).getRow(1);
it = row.cellIterator();
@@ -214,7 +257,7 @@ public final class TestHSSFDataFormatter
}
// test bad number formats
- row = wb.getSheetAt(0).getRow(2);
+ row = wb.getSheetAt(0).getRow(3);
it = row.cellIterator();
log("\n==== INVALID NUMBER FORMATS ====");
while (it.hasNext()) {
@@ -227,21 +270,21 @@ public final class TestHSSFDataFormatter
}
// test Zip+4 format
- row = wb.getSheetAt(0).getRow(3);
+ row = wb.getSheetAt(0).getRow(4);
HSSFCell cell = row.getCell(0);
log("\n==== ZIP FORMAT ====");
log(formatter.formatCellValue(cell));
assertEquals("12345-6789", formatter.formatCellValue(cell));
// test phone number format
- row = wb.getSheetAt(0).getRow(4);
+ row = wb.getSheetAt(0).getRow(5);
cell = row.getCell(0);
log("\n==== PHONE FORMAT ====");
log(formatter.formatCellValue(cell));
assertEquals("(555) 123-4567", formatter.formatCellValue(cell));
// test SSN format
- row = wb.getSheetAt(0).getRow(5);
+ row = wb.getSheetAt(0).getRow(6);
cell = row.getCell(0);
log("\n==== SSN FORMAT ====");
log(formatter.formatCellValue(cell));
@@ -256,7 +299,7 @@ public final class TestHSSFDataFormatter
public void testGetFormattedCellValueHSSFCellHSSFFormulaEvaluator() {
// test formula format
- HSSFRow row = wb.getSheetAt(0).getRow(6);
+ HSSFRow row = wb.getSheetAt(0).getRow(7);
HSSFCell cell = row.getCell(0);
log("\n==== FORMULA CELL ====");
@@ -277,7 +320,7 @@ public final class TestHSSFDataFormatter
* format pattern cannot be parsed by DecimalFormat.
*/
public void testSetDefaultNumberFormat() {
- HSSFRow row = wb.getSheetAt(0).getRow(2);
+ HSSFRow row = wb.getSheetAt(0).getRow(3);
Iterator<Cell> it = row.cellIterator();
Format defaultFormat = new DecimalFormat("Balance $#,#00.00 USD;Balance -$#,#00.00 USD");
formatter.setDefaultNumberFormat(defaultFormat);
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org