Attachment #20918 for bug #43551

View | Details | Raw Unified | Return to bug 43551
Collapse All | Expand All

Lines 35-40 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java (-12 / +37 lines)
35	* @author Glen Stampoultzis (glens at apache.org)	35	* @author Glen Stampoultzis (glens at apache.org)
36	* @author Dan Sherman (dsherman at isisph.com)	36	* @author Dan Sherman (dsherman at isisph.com)
37	* @author Hack Kampbjorn (hak at 2mba.dk)	37	* @author Hack Kampbjorn (hak at 2mba.dk)
		38	* @author Alex Jacoby (ajacoby at gmail.com)
38	*/	39	*/
39		40
40	public class HSSFDateUtil	41	public class HSSFDateUtil
Lines 49-67 Link Here
49		50
50	/**	51	/**
51	* Given a Date, converts it into a double representing its internal Excel representation,	52	* Given a Date, converts it into a double representing its internal Excel representation,
52	* which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.	53	* which is the number of days since 12/31/1899. Fractional days represent hours, minutes, and seconds.
53	*	54	*
54	* @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)	55	* @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
55	* @param date the Date	56	* @param date the Date
		57	* @deprecated Not safe to use: Assumes 1900 date windowing.
		58	* @see #getExcelDate(Date,boolean)
56	*/	59	*/
57		60
58	public static double getExcelDate(Date date)	61	public static double getExcelDate(Date date)
59	{	62	{
		63	return getExcelDate(date, false);
		64	}
		65
		66	/**
		67	* Given a Date, converts it into a double representing its internal Excel representation,
		68	* which is the number of days since 12/31/1899 (or 1/1/1904 if using 1904 date windowing).
		69	* Fractional days represent hours, minutes, and seconds.
		70	*
		71	* @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
		72	* @param date the Date
		73	* @param use1904windowing true if date uses 1904 windowing,
		74	* or false if using 1900 date windowing.
		75	*/
		76
		77	public static double getExcelDate(Date date, boolean use1904windowing)
		78	{
60	Calendar calStart = new GregorianCalendar();	79	Calendar calStart = new GregorianCalendar();
61		80
62	calStart.setTime(	81	calStart.setTime(
63	date); // If date includes hours, minutes, and seconds, set them to 0	82	date); // If date includes hours, minutes, and seconds, set them to 0
64	if (calStart.get(Calendar.YEAR) < 1900)	83	int firstYear = use1904windowing? 1904 : 1900;
		84	if (calStart.get(Calendar.YEAR) < firstYear)
65	{	85	{
66	return BAD_DATE;	86	return BAD_DATE;
67	}	87	}
Lines 79-92 Link Here
79	) * 60 + calStart.get(Calendar.SECOND)	99	) * 60 + calStart.get(Calendar.SECOND)
80	) * 1000 + calStart.get(Calendar.MILLISECOND)	100	) * 1000 + calStart.get(Calendar.MILLISECOND)
81	) / ( double ) DAY_MILLISECONDS;	101	) / ( double ) DAY_MILLISECONDS;
82	calStart = dayStart(calStart);	102	resetTime(calStart);
83		103
84	double value = fraction + absoluteDay(calStart);	104	double value = fraction + absoluteDay(calStart);
85		105
86	if (value >= 60) {	106	// Handle Excel bug (doesn't apply to 1904 date windowing)
		107	if ((value >= 60) && !use1904windowing) {
87	value += 1;	108	value += 1;
88	}	109	}
89		110
		111	if (use1904windowing) {
		112	value -= (365 * 4) + 1; // 4 years and 1 day
		113	}
		114
90	return value;	115	return value;
91	}	116	}
92	}	117	}
Lines 132-138 Link Here
132	int wholeDays = (int)Math.floor(date);	157	int wholeDays = (int)Math.floor(date);
133	if (use1904windowing) {	158	if (use1904windowing) {
134	startYear = 1904;	159	startYear = 1904;
135	dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as the first day	160	dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as day 1 (1/1/1900 is day 0)
136	}	161	}
137	else if (wholeDays < 61) {	162	else if (wholeDays < 61) {
138	// Date is prior to 3/1/1900, so adjust because Excel thinks 2/29/1900 exists	163	// Date is prior to 3/1/1900, so adjust because Excel thinks 2/29/1900 exists
Lines 289-309 Link Here
289	}	314	}
290		315
291	/**	316	/**
292	* Given a Calendar, return the number of days since 1900/12/31.	317	* Given a Calendar, return the number of days since 1899/12/31
		318	* (<b>non-private only for testing!</b>).
293	*	319	*
294	* @return days number of days since 1900/12/31	320	* @return days number of days since 1899/12/31
295	* @param cal the Calendar	321	* @param cal the Calendar
296	* @exception IllegalArgumentException if date is invalid	322	* @exception IllegalArgumentException if date is invalid
297	*/	323	*/
298		324
299	private static int absoluteDay(Calendar cal)	325	static int absoluteDay(Calendar cal)
300	{	326	{
301	return cal.get(Calendar.DAY_OF_YEAR)	327	return cal.get(Calendar.DAY_OF_YEAR)
302	+ daysInPriorYears(cal.get(Calendar.YEAR));	328	+ daysInPriorYears(cal.get(Calendar.YEAR));
303	}	329	}
304		330
305	/**	331	/**
306	* Return the number of days in prior years since 1900	332	* Return the number of days in prior years since 1900.
307	*	333	*
308	* @return days number of days in years prior to yr.	334	* @return days number of days in years prior to yr.
309	* @param yr a year (1900 < yr < 4000)	335	* @param yr a year (1900 < yr < 4000)
Lines 326-333 Link Here
326	return 365 * (yr - 1900) + leapDays;	352	return 365 * (yr - 1900) + leapDays;
327	}	353	}
328		354
329	// set HH:MM:SS fields of cal to 00:00:00:000	355	/** Set HH:MM:SS fields of cal to 00:00:00:000. */
330	private static Calendar dayStart(final Calendar cal)	356	private static void resetTime(final Calendar cal)
331	{	357	{
332	cal.get(Calendar	358	cal.get(Calendar
333	.HOUR_OF_DAY); // force recalculation of internal fields	359	.HOUR_OF_DAY); // force recalculation of internal fields
Lines 337-343 Link Here
337	cal.set(Calendar.MILLISECOND, 0);	363	cal.set(Calendar.MILLISECOND, 0);
338	cal.get(Calendar	364	cal.get(Calendar
339	.HOUR_OF_DAY); // force recalculation of internal fields	365	.HOUR_OF_DAY); // force recalculation of internal fields
340	return cal;
341	}	366	}
342		367
343	// ---------------------------------------------------------------------------------------------------------	368	// ---------------------------------------------------------------------------------------------------------

Lines 51-56 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (-1 / +3 lines)
51	* @author Dan Sherman (dsherman at isisph.com)	51	* @author Dan Sherman (dsherman at isisph.com)
52	* @author Brian Sanders (kestrel at burdell dot org) Active Cell support	52	* @author Brian Sanders (kestrel at burdell dot org) Active Cell support
53	* @author Yegor Kozlov cell comments support	53	* @author Yegor Kozlov cell comments support
		54	* @author Alex Jacoby 1904 date windowing bugfix
54	* @version 1.0-pre	55	* @version 1.0-pre
55	*/	56	*/
56		57
Lines 521-527 Link Here
521	*/	522	*/
522	public void setCellValue(Date value)	523	public void setCellValue(Date value)
523	{	524	{
524	setCellValue(HSSFDateUtil.getExcelDate(value));	525	setCellValue(HSSFDateUtil.getExcelDate(value,
		526	book.isUsing1904DateWindowing()));
525	}	527	}
526		528
527	/**	529	/**




    }

    /**
     * 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.
     */




 * 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

    }

    /**
     * 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);

         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();
     }

     /**
      * 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
     */

Return to bug 43551

Lines 93-98 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java (+16 lines)
93	}	93	}
94		94
95	/**	95	/**
		96	* Check if HSSFDateUtil.getAbsoluteDay works as advertised.
		97	*/
		98	public void testAbsoluteDay() {
		99	// 1 Jan 1900 is 1 day after 31 Dec 1899
		100	GregorianCalendar calendar = new GregorianCalendar(1900, 0, 1);
		101	assertEquals("Checking absolute day (1 Jan 1900)",
		102	1,
		103	HSSFDateUtil.absoluteDay(calendar));
		104	// 1 Jan 1901 is 366 days after 31 Dec 1899
		105	calendar = new GregorianCalendar(1901, 0, 1);
		106	assertEquals("Checking absolute day (1 Jan 1901)",
		107	366,
		108	HSSFDateUtil.absoluteDay(calendar));
		109	}
		110
		111	/**
96	* Checks the conversion of a java.util.date to Excel on a day when	112	* Checks the conversion of a java.util.date to Excel on a day when
97	* Daylight Saving Time starts.	113	* Daylight Saving Time starts.
98	*/	114	*/

Lines 44-49 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java (-33 / +98 lines)
44	* paticular datatypes, etc.	44	* paticular datatypes, etc.
45	* @author Andrew C. Oliver (andy at superlinksoftware dot com)	45	* @author Andrew C. Oliver (andy at superlinksoftware dot com)
46	* @author Dan Sherman (dsherman at isisph.com)	46	* @author Dan Sherman (dsherman at isisph.com)
		47	* @author Alex Jacoby (ajacoby at gmail.com)
47	*/	48	*/
48		49
49	public class TestHSSFCell	50	public class TestHSSFCell
Lines 107-148 Link Here
107	}	108	}
108		109
109	/**	110	/**
110	* Checks that the recognition of files using 1904 date windowing	111	* Checks that the recognition of files using 1904 date windowing
111	* is working properly. Conversion of the date is also an issue,	112	* is working properly. Conversion of the date is also an issue,
112	* but there's a separate unit test for that.	113	* but there's a separate unit test for that.
113	*/	114	*/
114	public void testDateWindowing() throws Exception {	115	public void testDateWindowingRead() throws Exception {
115	GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000	116	GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000
116	Date date = cal.getTime();	117	Date date = cal.getTime();
117	String path = System.getProperty("HSSF.testdata.path");	118	String path = System.getProperty("HSSF.testdata.path");
118		119
119	// first check a file with 1900 Date Windowing	120	// first check a file with 1900 Date Windowing
120	String filename = path + "/1900DateWindowing.xls";	121	String filename = path + "/1900DateWindowing.xls";
121	FileInputStream stream = new FileInputStream(filename);	122	FileInputStream stream = new FileInputStream(filename);
122	POIFSFileSystem fs = new POIFSFileSystem(stream);	123	POIFSFileSystem fs = new POIFSFileSystem(stream);
123	HSSFWorkbook workbook = new HSSFWorkbook(fs);	124	HSSFWorkbook workbook = new HSSFWorkbook(fs);
124	HSSFSheet sheet = workbook.getSheetAt(0);	125	HSSFSheet sheet = workbook.getSheetAt(0);
125		126
126	assertEquals("Date from file using 1900 Date Windowing",	127	assertEquals("Date from file using 1900 Date Windowing",
127	date.getTime(),	128	date.getTime(),
128	sheet.getRow(0).getCell((short)0)	129	sheet.getRow(0).getCell((short)0)
129	.getDateCellValue().getTime());	130	.getDateCellValue().getTime());
130	stream.close();	131	stream.close();
131		132
132	// now check a file with 1904 Date Windowing	133	// now check a file with 1904 Date Windowing
133	filename = path + "/1904DateWindowing.xls";	134	filename = path + "/1904DateWindowing.xls";
134	stream = new FileInputStream(filename);	135	stream = new FileInputStream(filename);
135	fs = new POIFSFileSystem(stream);	136	fs = new POIFSFileSystem(stream);
136	workbook = new HSSFWorkbook(fs);	137	workbook = new HSSFWorkbook(fs);
137	sheet = workbook.getSheetAt(0);	138	sheet = workbook.getSheetAt(0);
138		139
139	assertEquals("Date from file using 1904 Date Windowing",	140	assertEquals("Date from file using 1904 Date Windowing",
140	date.getTime(),	141	date.getTime(),
141	sheet.getRow(0).getCell((short)0)	142	sheet.getRow(0).getCell((short)0)
142	.getDateCellValue().getTime());	143	.getDateCellValue().getTime());
143	stream.close();	144	stream.close();
144	}	145	}
145		146
		147	/**
		148	* Checks that dates are properly written to both types of files:
		149	* those with 1900 and 1904 date windowing. Note that if the
		150	* previous test ({@link #testDateWindowingRead}) fails, the
		151	* results of this test are meaningless.
		152	*/
		153	public void testDateWindowingWrite() throws Exception {
		154	GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000
		155	Date date = cal.getTime();
		156	String path = System.getProperty("HSSF.testdata.path");
		157
		158	// first check a file with 1900 Date Windowing
		159	String filename = path + "/1900DateWindowing.xls";
		160	writeCell(filename, 0, (short) 1, date);
		161	assertEquals("Date from file using 1900 Date Windowing",
		162	date.getTime(),
		163	readCell(filename, 0, (short) 1).getTime());
		164
		165	// now check a file with 1904 Date Windowing
		166	filename = path + "/1904DateWindowing.xls";
		167	writeCell(filename, 0, (short) 1, date);
		168	assertEquals("Date from file using 1900 Date Windowing",
		169	date.getTime(),
		170	readCell(filename, 0, (short) 1).getTime());
		171	}
		172
		173	/**
		174	* Sets cell value and writes file.
		175	*/
		176	private void writeCell(String filename,
		177	int rowIdx, short colIdx, Date date) throws Exception {
		178	FileInputStream stream = new FileInputStream(filename);
		179	POIFSFileSystem fs = new POIFSFileSystem(stream);
		180	HSSFWorkbook workbook = new HSSFWorkbook(fs);
		181	HSSFSheet sheet = workbook.getSheetAt(0);
		182	HSSFRow row = sheet.getRow(rowIdx);
		183	HSSFCell cell = row.getCell(colIdx);
		184
		185	if (cell == null) {
		186	cell = row.createCell(colIdx);
		187	}
		188	cell.setCellValue(date);
		189
		190	// Write the file
		191	stream.close();
		192	FileOutputStream oStream = new FileOutputStream(filename);
		193	workbook.write(oStream);
		194	oStream.close();
		195	}
		196
		197	/**
		198	* Reads cell value from file.
		199	*/
		200	private Date readCell(String filename,
		201	int rowIdx, short colIdx) throws Exception {
		202	FileInputStream stream = new FileInputStream(filename);
		203	POIFSFileSystem fs = new POIFSFileSystem(stream);
		204	HSSFWorkbook workbook = new HSSFWorkbook(fs);
		205	HSSFSheet sheet = workbook.getSheetAt(0);
		206	HSSFRow row = sheet.getRow(rowIdx);
		207	HSSFCell cell = row.getCell(colIdx);
		208	return cell.getDateCellValue();
		209	}
		210
146	/**	211	/**
147	* Tests that the active cell can be correctly read and set	212	* Tests that the active cell can be correctly read and set
148	*/	213	*/