--- src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java (revision 581725) +++ src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java (working copy) @@ -35,6 +35,7 @@ * @author Glen Stampoultzis (glens at apache.org) * @author Dan Sherman (dsherman at isisph.com) * @author Hack Kampbjorn (hak at 2mba.dk) + * @author Alex Jacoby (ajacoby at gmail.com) */ public class HSSFDateUtil @@ -49,19 +50,38 @@ /** * Given a Date, converts it into a double representing its internal Excel representation, - * which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds. + * which is the number of days since 12/31/1899. Fractional days represent hours, minutes, and seconds. * * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1) * @param date the Date + * @deprecated Not safe to use: Assumes 1900 date windowing. + * @see #getExcelDate(Date,boolean) */ public static double getExcelDate(Date date) { + return getExcelDate(date, false); + } + + /** + * Given a Date, converts it into a double representing its internal Excel representation, + * which is the number of days since 12/31/1899 (or 1/1/1904 if using 1904 date windowing). + * Fractional days represent hours, minutes, and seconds. + * + * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1) + * @param date the Date + * @param use1904windowing true if date uses 1904 windowing, + * or false if using 1900 date windowing. + */ + + public static double getExcelDate(Date date, boolean use1904windowing) + { Calendar calStart = new GregorianCalendar(); calStart.setTime( date); // If date includes hours, minutes, and seconds, set them to 0 - if (calStart.get(Calendar.YEAR) < 1900) + int firstYear = use1904windowing? 1904 : 1900; + if (calStart.get(Calendar.YEAR) < firstYear) { return BAD_DATE; } @@ -79,14 +99,19 @@ ) * 60 + calStart.get(Calendar.SECOND) ) * 1000 + calStart.get(Calendar.MILLISECOND) ) / ( double ) DAY_MILLISECONDS; - calStart = dayStart(calStart); + resetTime(calStart); double value = fraction + absoluteDay(calStart); - if (value >= 60) { + // Handle Excel bug (doesn't apply to 1904 date windowing) + if ((value >= 60) && !use1904windowing) { value += 1; } + if (use1904windowing) { + value -= (365 * 4) + 1; // 4 years and 1 day + } + return value; } } @@ -132,7 +157,7 @@ int wholeDays = (int)Math.floor(date); if (use1904windowing) { startYear = 1904; - dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as the first day + dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as day 1 (1/1/1900 is day 0) } else if (wholeDays < 61) { // Date is prior to 3/1/1900, so adjust because Excel thinks 2/29/1900 exists @@ -289,21 +314,22 @@ } /** - * Given a Calendar, return the number of days since 1900/12/31. + * Given a Calendar, return the number of days since 1899/12/31 + * (non-private only for testing!). * - * @return days number of days since 1900/12/31 + * @return days number of days since 1899/12/31 * @param cal the Calendar * @exception IllegalArgumentException if date is invalid */ - private static int absoluteDay(Calendar cal) + static int absoluteDay(Calendar cal) { return cal.get(Calendar.DAY_OF_YEAR) + daysInPriorYears(cal.get(Calendar.YEAR)); } /** - * Return the number of days in prior years since 1900 + * Return the number of days in prior years since 1900. * * @return days number of days in years prior to yr. * @param yr a year (1900 < yr < 4000) @@ -326,8 +352,8 @@ return 365 * (yr - 1900) + leapDays; } - // set HH:MM:SS fields of cal to 00:00:00:000 - private static Calendar dayStart(final Calendar cal) + /** Set HH:MM:SS fields of cal to 00:00:00:000. */ + private static void resetTime(final Calendar cal) { cal.get(Calendar .HOUR_OF_DAY); // force recalculation of internal fields @@ -337,7 +363,6 @@ cal.set(Calendar.MILLISECOND, 0); cal.get(Calendar .HOUR_OF_DAY); // force recalculation of internal fields - return cal; } // --------------------------------------------------------------------------------------------------------- --- src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (revision 581725) +++ src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (working copy) @@ -51,6 +51,7 @@ * @author Dan Sherman (dsherman at isisph.com) * @author Brian Sanders (kestrel at burdell dot org) Active Cell support * @author Yegor Kozlov cell comments support + * @author Alex Jacoby 1904 date windowing bugfix * @version 1.0-pre */ @@ -521,7 +522,8 @@ */ public void setCellValue(Date value) { - setCellValue(HSSFDateUtil.getExcelDate(value)); + setCellValue(HSSFDateUtil.getExcelDate(value, + book.isUsing1904DateWindowing())); } /** --- src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java (revision 581725) +++ src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java (working copy) @@ -93,6 +93,22 @@ } /** + * 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)); + // 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)); + } + + /** * Checks the conversion of a java.util.date to Excel on a day when * Daylight Saving Time starts. */ --- src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java (revision 581725) +++ src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java (working copy) @@ -44,6 +44,7 @@ * paticular datatypes, etc. * @author Andrew C. Oliver (andy at superlinksoftware dot com) * @author Dan Sherman (dsherman at isisph.com) + * @author Alex Jacoby (ajacoby at gmail.com) */ public class TestHSSFCell @@ -107,42 +108,106 @@ } /** - * Checks that the recognition of files using 1904 date windowing - * is working properly. Conversion of the date is also an issue, - * but there's a separate unit test for that. - */ - public void testDateWindowing() throws Exception { - GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000 - Date date = cal.getTime(); - String path = System.getProperty("HSSF.testdata.path"); + * Checks that the recognition of files using 1904 date windowing + * is working properly. Conversion of the date is also an issue, + * but there's a separate unit test for that. + */ + public void testDateWindowingRead() throws Exception { + GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000 + Date date = cal.getTime(); + String path = System.getProperty("HSSF.testdata.path"); - // first check a file with 1900 Date Windowing - String filename = path + "/1900DateWindowing.xls"; - FileInputStream stream = new FileInputStream(filename); - POIFSFileSystem fs = new POIFSFileSystem(stream); - HSSFWorkbook workbook = new HSSFWorkbook(fs); - HSSFSheet sheet = workbook.getSheetAt(0); + // first check a file with 1900 Date Windowing + String filename = path + "/1900DateWindowing.xls"; + FileInputStream stream = new FileInputStream(filename); + POIFSFileSystem fs = new POIFSFileSystem(stream); + HSSFWorkbook workbook = new HSSFWorkbook(fs); + HSSFSheet sheet = workbook.getSheetAt(0); - assertEquals("Date from file using 1900 Date Windowing", - date.getTime(), - sheet.getRow(0).getCell((short)0) - .getDateCellValue().getTime()); - stream.close(); - - // now check a file with 1904 Date Windowing - filename = path + "/1904DateWindowing.xls"; - stream = new FileInputStream(filename); - fs = new POIFSFileSystem(stream); - workbook = new HSSFWorkbook(fs); - sheet = workbook.getSheetAt(0); + assertEquals("Date from file using 1900 Date Windowing", + date.getTime(), + sheet.getRow(0).getCell((short)0) + .getDateCellValue().getTime()); + stream.close(); + + // now check a file with 1904 Date Windowing + filename = path + "/1904DateWindowing.xls"; + stream = new FileInputStream(filename); + fs = new POIFSFileSystem(stream); + workbook = new HSSFWorkbook(fs); + sheet = workbook.getSheetAt(0); - assertEquals("Date from file using 1904 Date Windowing", - date.getTime(), - sheet.getRow(0).getCell((short)0) - .getDateCellValue().getTime()); - stream.close(); - } - + assertEquals("Date from file using 1904 Date Windowing", + date.getTime(), + sheet.getRow(0).getCell((short)0) + .getDateCellValue().getTime()); + stream.close(); + } + + /** + * Checks that dates are properly written to both types of files: + * those with 1900 and 1904 date windowing. Note that if the + * previous test ({@link #testDateWindowingRead}) fails, the + * results of this test are meaningless. + */ + public void testDateWindowingWrite() throws Exception { + GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000 + Date date = cal.getTime(); + String path = System.getProperty("HSSF.testdata.path"); + + // first check a file with 1900 Date Windowing + String filename = path + "/1900DateWindowing.xls"; + writeCell(filename, 0, (short) 1, date); + assertEquals("Date from file using 1900 Date Windowing", + date.getTime(), + readCell(filename, 0, (short) 1).getTime()); + + // now check a file with 1904 Date Windowing + filename = path + "/1904DateWindowing.xls"; + writeCell(filename, 0, (short) 1, date); + assertEquals("Date from file using 1900 Date Windowing", + date.getTime(), + readCell(filename, 0, (short) 1).getTime()); + } + + /** + * Sets cell value and writes file. + */ + private void writeCell(String filename, + int rowIdx, short colIdx, Date date) throws Exception { + FileInputStream stream = new FileInputStream(filename); + POIFSFileSystem fs = new POIFSFileSystem(stream); + HSSFWorkbook workbook = new HSSFWorkbook(fs); + HSSFSheet sheet = workbook.getSheetAt(0); + HSSFRow row = sheet.getRow(rowIdx); + HSSFCell cell = row.getCell(colIdx); + + if (cell == null) { + cell = row.createCell(colIdx); + } + cell.setCellValue(date); + + // Write the file + stream.close(); + FileOutputStream oStream = new FileOutputStream(filename); + workbook.write(oStream); + oStream.close(); + } + + /** + * Reads cell value from file. + */ + private Date readCell(String filename, + int rowIdx, short colIdx) throws Exception { + FileInputStream stream = new FileInputStream(filename); + POIFSFileSystem fs = new POIFSFileSystem(stream); + HSSFWorkbook workbook = new HSSFWorkbook(fs); + HSSFSheet sheet = workbook.getSheetAt(0); + HSSFRow row = sheet.getRow(rowIdx); + HSSFCell cell = row.getCell(colIdx); + return cell.getDateCellValue(); + } + /** * Tests that the active cell can be correctly read and set */