Merged revisions 638786-638802,638805-638811,638813-638814,638816-639230,639233-639241,639243-639253,639255-639486,639488-639601,639603-639835,639837-639917,639919-640056,640058-640710,640712-641156,641158-641184,641186-641795,641797-641798,641800-641933,641935-641963,641965-641966,641968-641995,641997-642230,642232-642562,642564-642565,642568-642570,642572-642573,642576-642736,642739-642877,642879,642881-642890,642892-642903,642905-642945,642947-643624,643626-643653,643655-643669,643671,643673-643830,643832-643833,643835-644342,644344-644472,644474-644508,644510-645347,645349-645351,645353-645559,645561-645565,645568-645951,645953-646193,646195-646311,646313-646404,646406-646665,646667-646853,646855-646869,646871-647151,647153-647185,647187-647277,647279-647566,647568-647573,647575,647578-647711,647714-647737,647739-647823,647825-648155,648157-648202,648204-648273,648275,648277-648302,648304-648333,648335-648588,648590-648622,648625-648673,648675-649141,649144,649146-649556,649558-649795,649799,649801-649910,649912-649913,649915-650128,650131-650132,650134-650137,650140-650914,650916-651991,651993-652284,652286-652287,652289,652291,652293-652297,652299-652328,652330-652425,652427-652445,652447-652560,652562-652933,652935,652937-652993,652995-653116,653118-653124,653126-653483,653487-653519,653522-653550,653552-653607,653609-653667,653669-653674,653676-653814,653817-653830,653832-653891,653893-653944,653946-654055,654057-654355,654357-654365,654367-654648,654651-655215,655217-655277,655279-655281,655283-655911,655913-656212,656214,656216-656251,656253-656698,656700-656756,656758-656892,656894-657135,657137-657165,657168-657179,657181-657354,657356-657357,657359-657701,657703-657874,657876-658032,658034-658284,658286,658288-658301,658303-658307,658309-658321,658323-658335,658337-658348,658351,658353-658832,658834-658983,658985,658987-659066,659068-659402,659404-659428,659430-659451,659453-659454,659456-659461,659463-659477,659479-659524,659526-659571,659574,659576-660255,660257-660262,660264-660279,660281-660343,660345-660473,660475-660827,660829-660833,660835-660888,660890-663321,663323-663435,663437-663764,663766-663854,663856-664219,664221-664489,664494-664514,664516-668013,668015-668142,668144-668152,668154,668156-668256,668258,668260-669139,669141-669455,669457-669657,669659-669808,669810-670189,670191-671321,671323-672229,672231-672549,672551-672552,672554-672561,672563-672566,672568,672571-673049,673051-673852,673854-673862,673864-673986,673988-673996,673998-674347,674349-674890,674892-674910,674912-674936,674938-674952,674954-675078,675080-675085,675087-675217,675219-675660,675662-675670,675672-675716,675718-675726,675728-675733,675735-675775,675777-675782,675784,675786-675791,675794-675852,675854-676200,676202,676204,676206-676220,676222-676309,676311-676456,676458-676994,676996-677027,677030-677040,677042-677056,677058-678287 via svnmerge from

https://svn.apache.org:443/repos/asf/poi/trunk

........
  r677376 | josh | 2008-07-16 19:47:13 +0100 (Wed, 16 Jul 2008) | 1 line
  
  Patch 45410 - removed dependency on commons beanutils, collections and lang
........
  r677969 | nick | 2008-07-18 18:02:29 +0100 (Fri, 18 Jul 2008) | 1 line
  
  Patch from Jukka from bug #45394 - tidy up examples bit of build
........
  r677972 | nick | 2008-07-18 18:11:51 +0100 (Fri, 18 Jul 2008) | 1 line
  
  Update homepage for 3.5.1 beta 1
........
  r677974 | nick | 2008-07-18 18:16:50 +0100 (Fri, 18 Jul 2008) | 1 line
  
  Patch from bug #45398 - Support detecting date formats containing "am/pm" as date times
........
  r677995 | nick | 2008-07-18 19:40:11 +0100 (Fri, 18 Jul 2008) | 1 line
  
  Patch from bug #45414 - Don't add too many UncalcedRecords to sheets with charts in them
........
  r678287 | nick | 2008-07-20 18:18:07 +0100 (Sun, 20 Jul 2008) | 1 line
  
  Apply, with some tweaks, the patch from bug #45404 - New class, hssf.usermodel.HSSFDataFormatter, for formatting numbers and dates in the same way that Excel does
........


git-svn-id: https://svn.apache.org/repos/asf/poi/branches/ooxml@678288 13f79535-47bb-0310-9956-ffa450edef68

3 files changed, 304 insertions, 1 deletions

diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
index 40e4bd34dd..69937d1034 100644
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
@@ -1363,4 +1363,24 @@ public final class TestBugs extends TestCase {
         HSSFSheet sh = wb.getSheetAt(0);
         for(short i=0; i < 30; i++) sh.autoSizeColumn(i);
      }
+    
+    /**
+     * We used to add too many UncalcRecords to sheets
+     *  with diagrams on. Don't any more
+     */
+    public void test45414() throws Exception {
+    	HSSFWorkbook wb = openSample("WithThreeCharts.xls");
+    	wb.getSheetAt(0).setForceFormulaRecalculation(true);
+    	wb.getSheetAt(1).setForceFormulaRecalculation(false);
+    	wb.getSheetAt(2).setForceFormulaRecalculation(true);
+    	
+    	// Write out and back in again
+    	// This used to break
+    	HSSFWorkbook nwb = writeOutAndReadBack(wb);
+    	
+    	// Check now set as it should be
+    	assertTrue(nwb.getSheetAt(0).getForceFormulaRecalculation());
+    	assertFalse(nwb.getSheetAt(1).getForceFormulaRecalculation());
+    	assertTrue(nwb.getSheetAt(2).getForceFormulaRecalculation());
+    }
 }
diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java
new file mode 100644
index 0000000000..39baedd885
--- /dev/null
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java
@@ -0,0 +1,282 @@
+/* ====================================================================
+   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.hssf.usermodel;
+
+import java.text.DecimalFormat;
+import java.text.Format;
+import java.util.Iterator;
+
+import junit.framework.TestCase;
+
+/**
+ * Unit tests for HSSFDataFormatter.java
+ * 
+ * @author James May (james dot may at fmr dot com)
+ *
+ */
+public class TestHSSFDataFormatter extends TestCase {
+
+	HSSFDataFormatter formatter;
+	HSSFWorkbook wb;
+	
+	public TestHSSFDataFormatter() {
+		// create the formatter to test
+		formatter = new HSSFDataFormatter();
+		
+		// create a workbook to test with
+	    wb = new HSSFWorkbook();
+	    HSSFSheet sheet = wb.createSheet();
+	    HSSFDataFormat format = wb.createDataFormat();
+  
+	    // create a row and put some cells in it
+	    HSSFRow row = sheet.createRow((short)0);
+
+	    // date value for July 8 1901 1:19 PM
+	    double dateNum = 555.555;
+	    
+	    //valid date formats -- all should have "Jul" in output
+	    String[] goodDatePatterns = new String[] {
+    		"[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy",
+    		"mmm/d/yy\\ h:mm PM;@",
+    		"mmmm/d/yy\\ h:mm;@",
+    		"mmmm/d;@",
+    		"mmmm/d/yy;@",
+    		"mmm/dd/yy;@",
+    		"[$-409]d\\-mmm;@",
+    		"[$-409]d\\-mmm\\-yy;@",
+    		"[$-409]dd\\-mmm\\-yy;@",
+    		"[$-409]mmm\\-yy;@",
+    		"[$-409]mmmm\\-yy;@",
+    		"[$-409]mmmm\\ d\\,\\ yyyy;@",
+    		"[$-409]mmm/d/yy\\ h:mm:ss;@",
+    		"[$-409]mmmm/d/yy\\ h:mm:ss am;@",
+    		"[$-409]mmmmm;@",
+    		"[$-409]mmmmm\\-yy;@",
+    		"mmmm/d/yyyy;@",
+    		"[$-409]d\\-mmm\\-yyyy;@"
+	    };
+	    
+	    // valid number formats
+	    String[] goodNumPatterns = new String[] {
+	    		"#,##0.0000",
+	    		"#,##0;[Red]#,##0",
+	    		"(#,##0.00_);(#,##0.00)",
+	    		"($#,##0.00_);[Red]($#,##0.00)",
+	    		"$#,##0.00",
+	    		"[$�-809]#,##0.00",
+	    		"[$�-2] #,##0.00",
+	    		"0000.00000%",
+	    		"0.000E+00",
+	    		"0.00E+00",
+	    };
+	    
+	    // invalid date formats -- will throw exception in DecimalFormat ctor
+	    String[] badNumPatterns = new String[] {
+	    		"#,#$'#0.0000",
+	    		"'#','#ABC#0;##,##0",
+	    		"000 '123 4'5'6 000",
+	    		"#''0#0'1#10L16EE"
+	    };	    
+	    
+	    // create cells with good date patterns
+	    for (int i = 0; i < goodDatePatterns.length; i++) {
+	    	HSSFCell cell = row.createCell((short) i);
+	    	cell.setCellValue(dateNum);
+	    	HSSFCellStyle cellStyle = wb.createCellStyle();
+	    	cellStyle.setDataFormat(format.getFormat(goodDatePatterns[i]));
+	    	cell.setCellStyle(cellStyle);
+	    }
+	    row = sheet.createRow(1);
+	    
+	    // create cells with num patterns
+	    for (int i = 0; i < goodNumPatterns.length; i++) {
+	    	HSSFCell cell = row.createCell((short) i);
+	    	cell.setCellValue(-1234567890.12345);
+	    	HSSFCellStyle cellStyle = wb.createCellStyle();
+	    	cellStyle.setDataFormat(format.getFormat(goodNumPatterns[i]));
+	    	cell.setCellStyle(cellStyle);
+	    }
+	    row = sheet.createRow(2);
+	    
+	    // create cells with bad num patterns
+	    for (int i = 0; i < badNumPatterns.length; i++) {
+	    	HSSFCell cell = row.createCell((short) i);
+	    	cell.setCellValue(1234567890.12345);
+	    	HSSFCellStyle cellStyle = wb.createCellStyle();
+	    	cellStyle.setDataFormat(format.getFormat(badNumPatterns[i]));
+	    	cell.setCellStyle(cellStyle);
+	    }
+	    
+	    // Built in formats
+
+	    { // Zip + 4 format
+	    	row = sheet.createRow(3);
+	    	HSSFCell cell = row.createCell((short) 0);
+	    	cell.setCellValue(123456789);
+	    	HSSFCellStyle cellStyle = wb.createCellStyle();
+	    	cellStyle.setDataFormat(format.getFormat("00000-0000"));
+	    	cell.setCellStyle(cellStyle);
+	    }
+	    
+	    { // Phone number format
+	    	row = sheet.createRow(4);
+	    	HSSFCell cell = row.createCell((short) 0);
+	    	cell.setCellValue(5551234567D);
+	    	HSSFCellStyle cellStyle = wb.createCellStyle();
+	    	cellStyle.setDataFormat(format.getFormat("[<=9999999]###-####;(###) ###-####"));
+	    	cell.setCellStyle(cellStyle);
+	    }
+	    
+	    { // SSN format
+	    	row = sheet.createRow(5);
+	    	HSSFCell cell = row.createCell((short) 0);
+	    	cell.setCellValue(444551234);
+	    	HSSFCellStyle cellStyle = wb.createCellStyle();
+	    	cellStyle.setDataFormat(format.getFormat("000-00-0000"));
+	    	cell.setCellStyle(cellStyle);
+	    }
+	    
+	    { // formula cell
+	    	row = sheet.createRow(6);
+	    	HSSFCell cell = row.createCell((short) 0);
+	        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
+	        cell.setCellFormula("SUM(12.25,12.25)/100");
+	    	HSSFCellStyle cellStyle = wb.createCellStyle();
+	    	cellStyle.setDataFormat(format.getFormat("##.00%;"));
+	    	cell.setCellStyle(cellStyle);
+	    }	    
+	}
+
+	/**
+	 * Test getting formatted values from numeric and date cells.
+	 */
+	public void testGetFormattedCellValueHSSFCell() {
+		// Valid date formats -- cell values should be date formatted & not "555.555"
+		HSSFRow row = wb.getSheetAt(0).getRow(0);
+		Iterator it = row.cellIterator();
+		System.out.println("==== VALID DATE FORMATS ====");
+		while (it.hasNext()) {
+			HSSFCell cell = (HSSFCell) it.next();
+			System.out.println(formatter.formatCellValue(cell));
+
+			// should not be equal to "555.555"
+			assertTrue( ! "555.555".equals(formatter.formatCellValue(cell)));
+			
+			// should contain "Jul" in the String
+			assertTrue( formatter.formatCellValue(cell).indexOf("Jul") > -1);
+		}
+
+		// test number formats
+		row = wb.getSheetAt(0).getRow(1);
+		it = row.cellIterator();
+		System.out.println("\n==== VALID NUMBER FORMATS ====");
+		while (it.hasNext()) {
+			HSSFCell cell = (HSSFCell) it.next();
+			System.out.println(formatter.formatCellValue(cell));
+			
+			// should not be equal to "1234567890.12345"
+			assertTrue( ! "1234567890.12345".equals(formatter.formatCellValue(cell)));
+		}
+		
+		// test bad number formats
+		row = wb.getSheetAt(0).getRow(2);
+		it = row.cellIterator();
+		System.out.println("\n==== INVALID NUMBER FORMATS ====");
+		while (it.hasNext()) {
+			HSSFCell cell = (HSSFCell) it.next();
+			System.out.println(formatter.formatCellValue(cell));
+			// should be equal to "1234567890.12345"
+			assertEquals("1234567890.12345", formatter.formatCellValue(cell));
+		}
+		
+		// test Zip+4 format
+		row = wb.getSheetAt(0).getRow(3);
+		HSSFCell cell = row.getCell(0);
+		System.out.println("\n==== ZIP FORMAT ====");
+		System.out.println(formatter.formatCellValue(cell));
+		assertEquals("12345-6789", formatter.formatCellValue(cell));
+		
+		// test phone number format
+		row = wb.getSheetAt(0).getRow(4);
+		cell = row.getCell(0);
+		System.out.println("\n==== PHONE FORMAT ====");
+		System.out.println(formatter.formatCellValue(cell));
+		assertEquals("(555) 123-4567", formatter.formatCellValue(cell));
+		
+		// test SSN format
+		row = wb.getSheetAt(0).getRow(5);
+		cell = row.getCell(0);
+		System.out.println("\n==== SSN FORMAT ====");
+		System.out.println(formatter.formatCellValue(cell));
+		assertEquals("444-55-1234", formatter.formatCellValue(cell));		
+		
+		// null test-- null cell should result in empty String
+		assertEquals(formatter.formatCellValue(null), "");
+		
+		// null test-- null cell should result in empty String
+		assertEquals(formatter.formatCellValue(null), "");		
+		
+	}
+
+	public void testGetFormattedCellValueHSSFCellHSSFFormulaEvaluator() {
+		// test formula format
+		HSSFRow row = wb.getSheetAt(0).getRow(6);
+		HSSFCell cell = row.getCell(0);
+		System.out.println("\n==== FORMULA CELL ====");
+		
+		// first without a formula evaluator
+		System.out.println(formatter.formatCellValue(cell) + "\t (without evaluator)");
+		assertEquals("SUM(12.25,12.25)/100", formatter.formatCellValue(cell));	
+		
+		// now with a formula evaluator		
+		HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb.getSheetAt(0), wb);
+		//! must set current row !
+		evaluator.setCurrentRow(row);
+		System.out.println(formatter.formatCellValue(cell, evaluator) + "\t\t\t (with evaluator)");
+		assertEquals("24.50%", formatter.formatCellValue(cell,evaluator));
+	}
+
+
+
+	/**
+	 * Test using a default number format. The format should be used when a
+	 * format pattern cannot be parsed by DecimalFormat.
+	 */
+	public void testSetDefaultNumberFormat() {
+		HSSFRow row = wb.getSheetAt(0).getRow(2);
+		Iterator it = row.cellIterator();	
+		Format defaultFormat = new DecimalFormat("Balance $#,#00.00 USD;Balance -$#,#00.00 USD");
+		formatter.setDefaultNumberFormat(defaultFormat);
+		double value = 10d;
+		System.out.println("\n==== DEFAULT NUMBER FORMAT ====");
+		while (it.hasNext()) {
+			HSSFCell cell = (HSSFCell) it.next();		
+			cell.setCellValue(cell.getNumericCellValue() * Math.random() / 1000000 - 1000);
+			System.out.println(formatter.formatCellValue(cell));
+			assertTrue(formatter.formatCellValue(cell).startsWith("Balance "));
+			assertTrue(formatter.formatCellValue(cell).endsWith(" USD"));
+		}	
+	}
+
+    public static void main(String [] args) {
+        System.out
+                .println("Testing org.apache.poi.hssf.usermodel.TestHSSFDataFormatter");
+        junit.textui.TestRunner.run(TestHSSFDataFormatter.class);
+    }	
+
+}
diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java
index 4f526b61c0..281d1b1cb0 100644
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java
@@ -273,7 +273,8 @@ public class TestHSSFDateUtil extends TestCase {
                 "yyyy-mm-dd hh:mm:ss", "yyyy/mm/dd HH:MM:SS", 
                 "mm/dd HH:MM", "yy/mmm/dd SS",
                 "mm/dd HH:MM AM", "mm/dd HH:MM am",
-                "mm/dd HH:MM PM", "mm/dd HH:MM pm" 
+                "mm/dd HH:MM PM", "mm/dd HH:MM pm",
+                "m/d/yy h:mm AM/PM"
         };
         for(int i=0; i<formats.length; i++) {
             assertTrue(