testhssfdateutil.java

介绍java核心技术的pio编程方法以及基本概念

            cal.set(Calendar.HOUR_OF_DAY, hour);
            Date javaDate = HSSFDateUtil.getJavaDate(excelDate, false);
            assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
                    excelDate,
                    HSSFDateUtil.getExcelDate(javaDate, false), oneMinute);
        }
    }
    
    /**
     * Tests that we correctly detect date formats as such
     */
    public void testIdentifyDateFormats() {
    	// First up, try with a few built in date formats
    	short[] builtins = new short[] { 0x0e, 0x0f, 0x10, 0x16, 0x2d, 0x2e };
    	for(int i=0; i<builtins.length; i++) {
    		String formatStr = HSSFDataFormat.getBuiltinFormat(builtins[i]);
    		assertTrue( HSSFDateUtil.isInternalDateFormat(builtins[i]) );
    		assertTrue( HSSFDateUtil.isADateFormat(builtins[i],formatStr) );
    	}
    	
    	// Now try a few built-in non date formats
    	builtins = new short[] { 0x01, 0x02, 0x17, 0x1f, 0x30 };
    	for(int i=0; i<builtins.length; i++) {
    		String formatStr = HSSFDataFormat.getBuiltinFormat(builtins[i]);
    		assertFalse( HSSFDateUtil.isInternalDateFormat(builtins[i]) );
    		assertFalse( HSSFDateUtil.isADateFormat(builtins[i],formatStr) );
    	}
    	
    	// Now for some non-internal ones
    	// These come after the real ones
    	int numBuiltins = HSSFDataFormat.getNumberOfBuiltinBuiltinFormats();
    	assertTrue(numBuiltins < 60);
    	short formatId = 60;
    	assertFalse( HSSFDateUtil.isInternalDateFormat(formatId) );
    	
    	// Valid ones first
    	String[] formats = new String[] {
    			"yyyy-mm-dd", "yyyy/mm/dd", "yy/mm/dd", "yy/mmm/dd",
    			"dd/mm/yy", "dd/mm/yyyy", "dd/mmm/yy",
    			"dd-mm-yy", "dd-mm-yyyy",
    			"dd\\-mm\\-yy", // Sometimes escaped
    			
    			// These crazy ones are valid
    			"yyyy-mm-dd;@", "yyyy/mm/dd;@",
    			"dd-mm-yy;@", "dd-mm-yyyy;@",
    			// These even crazier ones are also valid
    			// (who knows what they mean though...)
    			"[$-F800]dddd\\,\\ mmm\\ dd\\,\\ yyyy",
    			"[$-F900]ddd/mm/yyy",
    	};
    	for(int i=0; i<formats.length; i++) {
    		assertTrue( HSSFDateUtil.isADateFormat(formatId, formats[i]) );
    	}
    	
    	// Then invalid ones
    	formats = new String[] {
    			"yyyy:mm:dd", 
    			"0.0", "0.000",
    			"0%", "0.0%",
    			"", null
    	};
    	for(int i=0; i<formats.length; i++) {
    		assertFalse( HSSFDateUtil.isADateFormat(formatId, formats[i]) );
    	}
    }

    /**
     * Test that against a real, test file, we still do everything
     *  correctly
     */
    public void testOnARealFile() throws Exception {
        String path     = System.getProperty("HSSF.testdata.path");
        String filename = path + "/DateFormats.xls";
        POIFSFileSystem fs =
            new POIFSFileSystem(new FileInputStream(filename));
        HSSFWorkbook workbook = new HSSFWorkbook(fs);
        HSSFSheet sheet       = workbook.getSheetAt(0);
        Workbook wb           = workbook.getWorkbook();
        
        HSSFRow  row;
        HSSFCell cell;
        HSSFCellStyle style;
        
        double aug_10_2007 = 39304.0;
        
        // Should have dates in 2nd column
        // All of them are the 10th of August
        // 2 US dates, 3 UK dates
        row  = sheet.getRow(0);
        cell = row.getCell((short)1);
        style = cell.getCellStyle();
        assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
        assertEquals("d-mmm-yy", style.getDataFormatString(wb));
        assertTrue(HSSFDateUtil.isInternalDateFormat(style.getDataFormat()));
        assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString(wb)));
        assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
        
        row  = sheet.getRow(1);
        cell = row.getCell((short)1);
        style = cell.getCellStyle();
        assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
        assertFalse(HSSFDateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()));
        assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString(wb)));
        assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
        
        row  = sheet.getRow(2);
        cell = row.getCell((short)1);
        style = cell.getCellStyle();
        assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
        assertTrue(HSSFDateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()));
        assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString(wb)));
        assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
        
        row  = sheet.getRow(3);
        cell = row.getCell((short)1);
        style = cell.getCellStyle();
        assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
        assertFalse(HSSFDateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()));
        assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString(wb)));
        assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
        
        row  = sheet.getRow(4);
        cell = row.getCell((short)1);
        style = cell.getCellStyle();
        assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
        assertFalse(HSSFDateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()));
        assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString(wb)));
        assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
    }
    
    public void testDateBug_2Excel() {
        assertEquals(59.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_FEBRUARY, 28), false), 0.00001);
        assertEquals(61.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_MARCH, 1), false), 0.00001);

        assertEquals(37315.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_FEBRUARY, 28), false), 0.00001);
        assertEquals(37316.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_MARCH, 1), false), 0.00001);
        assertEquals(37257.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_JANUARY, 1), false), 0.00001);
        assertEquals(38074.00, HSSFDateUtil.getExcelDate(createDate(2004, CALENDAR_MARCH, 28), false), 0.00001);
    }
    
    public void testDateBug_2Java() {
        assertEquals(createDate(1900, CALENDAR_FEBRUARY, 28), HSSFDateUtil.getJavaDate(59.0, false));
        assertEquals(createDate(1900, CALENDAR_MARCH, 1), HSSFDateUtil.getJavaDate(61.0, false));
        
        assertEquals(createDate(2002, CALENDAR_FEBRUARY, 28), HSSFDateUtil.getJavaDate(37315.00, false));
        assertEquals(createDate(2002, CALENDAR_MARCH, 1), HSSFDateUtil.getJavaDate(37316.00, false));
        assertEquals(createDate(2002, CALENDAR_JANUARY, 1), HSSFDateUtil.getJavaDate(37257.00, false));
        assertEquals(createDate(2004, CALENDAR_MARCH, 28), HSSFDateUtil.getJavaDate(38074.00, false));
    }
    
    public void testDate1904() {
        assertEquals(createDate(1904, CALENDAR_JANUARY, 2), HSSFDateUtil.getJavaDate(1.0, true));
        assertEquals(createDate(1904, CALENDAR_JANUARY, 1), HSSFDateUtil.getJavaDate(0.0, true));
        assertEquals(0.0, HSSFDateUtil.getExcelDate(createDate(1904, CALENDAR_JANUARY, 1), true), 0.00001);
        assertEquals(1.0, HSSFDateUtil.getExcelDate(createDate(1904, CALENDAR_JANUARY, 2), true), 0.00001);
        
        assertEquals(createDate(1998, CALENDAR_JULY, 5), HSSFDateUtil.getJavaDate(35981, false));
        assertEquals(createDate(1998, CALENDAR_JULY, 5), HSSFDateUtil.getJavaDate(34519, true));
        
        assertEquals(35981.0, HSSFDateUtil.getExcelDate(createDate(1998, CALENDAR_JULY, 5), false), 0.00001);
        assertEquals(34519.0, HSSFDateUtil.getExcelDate(createDate(1998, CALENDAR_JULY, 5), true), 0.00001);
    }
    
    private Date createDate(int year, int month, int day) {
        Calendar c = new GregorianCalendar();
        c.set(year, month, day, 0, 0, 0);
        c.set(Calendar.MILLISECOND, 0);
        return c.getTime();
    }
    
    /**
     * Check if HSSFDateUtil.getAbsoluteDay works as advertised.
     */
    public void testAbsoluteDay() {
        // 1 Jan 1900 is 1 day after 31 Dec 1899
        GregorianCalendar calendar = new GregorianCalendar(1900, 0, 1);
        assertEquals("Checking absolute day (1 Jan 1900)", 1, HSSFDateUtil.absoluteDay(calendar, false));
        // 1 Jan 1901 is 366 days after 31 Dec 1899
        calendar = new GregorianCalendar(1901, 0, 1);
        assertEquals("Checking absolute day (1 Jan 1901)", 366, HSSFDateUtil.absoluteDay(calendar, false));
    }
    
    public static void main(String [] args) {
        System.out
                .println("Testing org.apache.poi.hssf.usermodel.TestHSSFDateUtil");
        junit.textui.TestRunner.run(TestHSSFDateUtil.class);
    }
}