Attachment #20938 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 (-27 / +14 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)
		39	* @author Pavel Krupets (pkrupets at palmtreebusiness dot com)
38	*/	40	*/
39		41
40	public class HSSFDateUtil	42	public class HSSFDateUtil
Lines 54-61 Link Here
54	* @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)	56	* @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
55	* @param date the Date	57	* @param date the Date
56	*/	58	*/
57		59
58	public static double getExcelDate(Date date)	60	public static double getExcelDate(Date date, boolean use1904windowing)
59	{	61	{
60	Calendar calStart = new GregorianCalendar();	62	Calendar calStart = new GregorianCalendar();
61		63
Lines 80-110 Link Here
80	) * 1000 + calStart.get(Calendar.MILLISECOND)	82	) * 1000 + calStart.get(Calendar.MILLISECOND)
81	) / ( double ) DAY_MILLISECONDS;	83	) / ( double ) DAY_MILLISECONDS;
82	calStart = dayStart(calStart);	84	calStart = dayStart(calStart);
83
84	double value = fraction + absoluteDay(calStart);
85		85
86	if (value >= 60) {	86	double value = fraction + absoluteDay(calStart, use1904windowing);
		87
		88	if (!use1904windowing && value >= 60) {
87	value += 1;	89	value += 1;
88	}	90	}
89		91
90	return value;	92	return value;
91	}	93	}
92	}	94	}
93
94	/**
95	* Given a excel date, converts it into a Date.
96	* Assumes 1900 date windowing.
97	*
98	* @param date the Excel Date
99	*
100	* @return Java representation of a date (null if error)
101	* @see #getJavaDate(double,boolean)
102	*/
103
104	public static Date getJavaDate(double date)
105	{
106	return getJavaDate(date,false);
107	}
108		95
109	/**	96	/**
110	* Given an Excel date with either 1900 or 1904 date windowing,	97	* Given an Excel date with either 1900 or 1904 date windowing,
Lines 296-305 Link Here
296	* @exception IllegalArgumentException if date is invalid	283	* @exception IllegalArgumentException if date is invalid
297	*/	284	*/
298		285
299	private static int absoluteDay(Calendar cal)	286	static int absoluteDay(Calendar cal, boolean use1904windowing)
300	{	287	{
301	return cal.get(Calendar.DAY_OF_YEAR)	288	return cal.get(Calendar.DAY_OF_YEAR)
302	+ daysInPriorYears(cal.get(Calendar.YEAR));	289	+ daysInPriorYears(cal.get(Calendar.YEAR), use1904windowing);
303	}	290	}
304		291
305	/**	292	/**
Lines 307-320 Link Here
307	*	294	*
308	* @return days number of days in years prior to yr.	295	* @return days number of days in years prior to yr.
309	* @param yr a year (1900 < yr < 4000)	296	* @param yr a year (1900 < yr < 4000)
		297	* @param use1904windowing
310	* @exception IllegalArgumentException if year is outside of range.	298	* @exception IllegalArgumentException if year is outside of range.
311	*/	299	*/
312		300
313	private static int daysInPriorYears(int yr)	301	private static int daysInPriorYears(int yr, boolean use1904windowing)
314	{	302	{
315	if (yr < 1900) {	303	if ((!use1904windowing && yr < 1900) \|\| (use1904windowing && yr < 1900)) {
316	throw new IllegalArgumentException(	304	throw new IllegalArgumentException("'year' must be 1900 or greater");
317	"'year' must be 1900 or greater");
318	}	305	}
319		306
320	int yr1 = yr - 1;	307	int yr1 = yr - 1;
Lines 323-329 Link Here
323	+ yr1 / 400 // plus years divisible by 400	310	+ yr1 / 400 // plus years divisible by 400
324	- 460; // leap days in previous 1900 years	311	- 460; // leap days in previous 1900 years
325		312
326	return 365 * (yr - 1900) + leapDays;	313	return 365 * (yr - (use1904windowing ? 1904 : 1900)) + leapDays;
327	}	314	}
328		315
329	// set HH:MM:SS fields of cal to 00:00:00:000	316	// set HH:MM:SS fields of cal to 00:00:00:000

Lines 521-527 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (-1 / +1 lines)
521	*/	521	*/
522	public void setCellValue(Date value)	522	public void setCellValue(Date value)
523	{	523	{
524	setCellValue(HSSFDateUtil.getExcelDate(value));	524	setCellValue(HSSFDateUtil.getExcelDate(value, this.book.isUsing1904DateWindowing()));
525	}	525	}
526		526
527	/**	527	/**

Lines 70-76 Link Here

(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Date.java (-1 / +1 lines)
70	c.set(year, month, day, 0, 0, 0);	70	c.set(year, month, day, 0, 0, 0);
71	c.set(Calendar.MILLISECOND, 0);	71	c.set(Calendar.MILLISECOND, 0);
72		72
73	return new NumberEval(HSSFDateUtil.getExcelDate(c.getTime()));	73	return new NumberEval(HSSFDateUtil.getExcelDate(c.getTime(), false)); // XXX fix 1900/1904 problem
74	}	74	}
75	}	75	}
76		76

Lines 51-57 Link Here

(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Year.java (-1 / +1 lines)
51	if (ve instanceof NumericValueEval) {	51	if (ve instanceof NumericValueEval) {
52	NumericValueEval ne = (NumericValueEval) ve;	52	NumericValueEval ne = (NumericValueEval) ve;
53	if (HSSFDateUtil.isValidExcelDate(ne.getNumberValue())) {	53	if (HSSFDateUtil.isValidExcelDate(ne.getNumberValue())) {
54	java.util.Date d = HSSFDateUtil.getJavaDate(ne.getNumberValue());	54	java.util.Date d = HSSFDateUtil.getJavaDate(ne.getNumberValue(), false); // XXX fix 1900/1904 problem
55	retval = new NumberEval(d.getYear()+1900);	55	retval = new NumberEval(d.getYear()+1900);
56	} else {	56	} else {
57	retval = ErrorEval.NUM_ERROR;	57	retval = ErrorEval.NUM_ERROR;

Lines 46-52 Link Here

(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Day.java (-1 / +1 lines)
46	if (ve instanceof NumericValueEval) {	46	if (ve instanceof NumericValueEval) {
47	NumericValueEval ne = (NumericValueEval) ve;	47	NumericValueEval ne = (NumericValueEval) ve;
48	if (HSSFDateUtil.isValidExcelDate(ne.getNumberValue())) {	48	if (HSSFDateUtil.isValidExcelDate(ne.getNumberValue())) {
49	java.util.Date d = HSSFDateUtil.getJavaDate(ne.getNumberValue());	49	java.util.Date d = HSSFDateUtil.getJavaDate(ne.getNumberValue(), false); // XXX fix 1900/1904 problem
50	java.util.Calendar c = java.util.Calendar.getInstance();	50	java.util.Calendar c = java.util.Calendar.getInstance();
51	c.setTime(d);	51	c.setTime(d);
52	retval = new NumberEval(c.get(java.util.Calendar.DAY_OF_MONTH));	52	retval = new NumberEval(c.get(java.util.Calendar.DAY_OF_MONTH));

Lines 50-56 Link Here

(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Month.java (-1 / +1 lines)
50	if (ve instanceof NumericValueEval) {	50	if (ve instanceof NumericValueEval) {
51	NumericValueEval ne = (NumericValueEval) ve;	51	NumericValueEval ne = (NumericValueEval) ve;
52	if (HSSFDateUtil.isValidExcelDate(ne.getNumberValue())) {	52	if (HSSFDateUtil.isValidExcelDate(ne.getNumberValue())) {
53	java.util.Date d = HSSFDateUtil.getJavaDate(ne.getNumberValue());	53	java.util.Date d = HSSFDateUtil.getJavaDate(ne.getNumberValue(), false); // XXX fix 1900/1904 problem
54	retval = new NumberEval(d.getMonth()+1);	54	retval = new NumberEval(d.getMonth()+1);
55	} else {	55	} else {
56	retval = ErrorEval.NUM_ERROR;	56	retval = ErrorEval.NUM_ERROR;

Lines 283-289 Link Here

(-)src/scratchpad/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java (-1 / +1 lines)
283	format = format.replaceAll("\\\\-","-");	283	format = format.replaceAll("\\\\-","-");
284		284
285	// Format as a date	285	// Format as a date
286	Date d = HSSFDateUtil.getJavaDate(value);	286	Date d = HSSFDateUtil.getJavaDate(value, false);
287	DateFormat df = new SimpleDateFormat(format);	287	DateFormat df = new SimpleDateFormat(format);
288	return df.format(d);	288	return df.format(d);
289	} else {	289	} else {

Lines 36-41 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java (-25 / +38 lines)
36	*	36	*
37	* @author Dan Sherman (dsherman at isisph.com)	37	* @author Dan Sherman (dsherman at isisph.com)
38	* @author Hack Kampbjorn (hak at 2mba.dk)	38	* @author Hack Kampbjorn (hak at 2mba.dk)
		39	* @author Pavel Krupets (pkrupets at palmtreebusiness dot com)
		40	* @author Alex Jacoby (ajacoby at gmail.com)
39	* @version %I%, %G%	41	* @version %I%, %G%
40	*/	42	*/
41		43
Lines 67-76 Link Here
67	GregorianCalendar date = new GregorianCalendar(2002, 0, 1,	69	GregorianCalendar date = new GregorianCalendar(2002, 0, 1,
68	hour, 1, 1);	70	hour, 1, 1);
69	double excelDate =	71	double excelDate =
70	HSSFDateUtil.getExcelDate(date.getTime());	72	HSSFDateUtil.getExcelDate(date.getTime(), false);
71		73
72	assertEquals("Checking hour = " + hour, date.getTime().getTime(),	74	assertEquals("Checking hour = " + hour, date.getTime().getTime(),
73	HSSFDateUtil.getJavaDate(excelDate).getTime());	75	HSSFDateUtil.getJavaDate(excelDate, false).getTime());
74	}	76	}
75		77
76	// check 1900 and 1904 date windowing conversions	78	// check 1900 and 1904 date windowing conversions
Lines 110-116 Link Here
110		112
111	cal.set(Calendar.HOUR_OF_DAY, hour);	113	cal.set(Calendar.HOUR_OF_DAY, hour);
112	Date javaDate = cal.getTime();	114	Date javaDate = cal.getTime();
113	double excelDate = HSSFDateUtil.getExcelDate(javaDate);	115	double excelDate = HSSFDateUtil.getExcelDate(javaDate, false);
114	double difference = excelDate - Math.floor(excelDate);	116	double difference = excelDate - Math.floor(excelDate);
115	int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;	117	int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;
116	assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",	118	assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
Lines 118-124 Link Here
118	differenceInHours);	120	differenceInHours);
119	assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",	121	assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
120	javaDate.getTime(),	122	javaDate.getTime(),
121	HSSFDateUtil.getJavaDate(excelDate).getTime());	123	HSSFDateUtil.getJavaDate(excelDate, false).getTime());
122	}	124	}
123	}	125	}
124		126
Lines 130-136 Link Here
130	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");	132	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
131	TimeZone.setDefault(cet);	133	TimeZone.setDefault(cet);
132	Calendar cal = new GregorianCalendar(2004, Calendar.MARCH, 28);	134	Calendar cal = new GregorianCalendar(2004, Calendar.MARCH, 28);
133	double excelDate = HSSFDateUtil.getExcelDate(cal.getTime());	135	double excelDate = HSSFDateUtil.getExcelDate(cal.getTime(), false);
134	double oneHour = 1.0 / 24;	136	double oneHour = 1.0 / 24;
135	double oneMinute = oneHour / 60;	137	double oneMinute = oneHour / 60;
136	for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {	138	for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {
Lines 142-151 Link Here
142	}	144	}
143		145
144	cal.set(Calendar.HOUR_OF_DAY, hour);	146	cal.set(Calendar.HOUR_OF_DAY, hour);
145	Date javaDate = HSSFDateUtil.getJavaDate(excelDate);	147	Date javaDate = HSSFDateUtil.getJavaDate(excelDate, false);
146	assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",	148	assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
147	excelDate,	149	excelDate,
148	HSSFDateUtil.getExcelDate(javaDate), oneMinute);	150	HSSFDateUtil.getExcelDate(javaDate, false), oneMinute);
149	}	151	}
150	}	152	}
151		153
Lines 160-166 Link Here
160	for (int hour = 0; hour < 24; hour++) {	162	for (int hour = 0; hour < 24; hour++) {
161	cal.set(Calendar.HOUR_OF_DAY, hour);	163	cal.set(Calendar.HOUR_OF_DAY, hour);
162	Date javaDate = cal.getTime();	164	Date javaDate = cal.getTime();
163	double excelDate = HSSFDateUtil.getExcelDate(javaDate);	165	double excelDate = HSSFDateUtil.getExcelDate(javaDate, false);
164	double difference = excelDate - Math.floor(excelDate);	166	double difference = excelDate - Math.floor(excelDate);
165	int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;	167	int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;
166	assertEquals("Checking " + hour + " hour on Daylight Saving Time end date",	168	assertEquals("Checking " + hour + " hour on Daylight Saving Time end date",
Lines 168-174 Link Here
168	differenceInHours);	170	differenceInHours);
169	assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",	171	assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
170	javaDate.getTime(),	172	javaDate.getTime(),
171	HSSFDateUtil.getJavaDate(excelDate).getTime());	173	HSSFDateUtil.getJavaDate(excelDate, false).getTime());
172	}	174	}
173	}	175	}
174		176
Lines 180-194 Link Here
180	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");	182	TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
181	TimeZone.setDefault(cet);	183	TimeZone.setDefault(cet);
182	Calendar cal = new GregorianCalendar(2004, Calendar.OCTOBER, 31);	184	Calendar cal = new GregorianCalendar(2004, Calendar.OCTOBER, 31);
183	double excelDate = HSSFDateUtil.getExcelDate(cal.getTime());	185	double excelDate = HSSFDateUtil.getExcelDate(cal.getTime(), false);
184	double oneHour = 1.0 / 24;	186	double oneHour = 1.0 / 24;
185	double oneMinute = oneHour / 60;	187	double oneMinute = oneHour / 60;
186	for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {	188	for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {
187	cal.set(Calendar.HOUR_OF_DAY, hour);	189	cal.set(Calendar.HOUR_OF_DAY, hour);
188	Date javaDate = HSSFDateUtil.getJavaDate(excelDate);	190	Date javaDate = HSSFDateUtil.getJavaDate(excelDate, false);
189	assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",	191	assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
190	excelDate,	192	excelDate,
191	HSSFDateUtil.getExcelDate(javaDate), oneMinute);	193	HSSFDateUtil.getExcelDate(javaDate, false), oneMinute);
192	}	194	}
193	}	195	}
194		196
Lines 315-337 Link Here
315	}	317	}
316		318
317	public void testDateBug_2Excel() {	319	public void testDateBug_2Excel() {
318	assertEquals(59.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_FEBRUARY, 28)), 0.00001);	320	assertEquals(59.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_FEBRUARY, 28), false), 0.00001);
319	assertEquals(61.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_MARCH, 1)), 0.00001);	321	assertEquals(61.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_MARCH, 1), false), 0.00001);
320		322
321	assertEquals(37315.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_FEBRUARY, 28)), 0.00001);	323	assertEquals(37315.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_FEBRUARY, 28), false), 0.00001);
322	assertEquals(37316.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_MARCH, 1)), 0.00001);	324	assertEquals(37316.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_MARCH, 1), false), 0.00001);
323	assertEquals(37257.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_JANUARY, 1)), 0.00001);	325	assertEquals(37257.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_JANUARY, 1), false), 0.00001);
324	assertEquals(38074.00, HSSFDateUtil.getExcelDate(createDate(2004, CALENDAR_MARCH, 28)), 0.00001);	326	assertEquals(38074.00, HSSFDateUtil.getExcelDate(createDate(2004, CALENDAR_MARCH, 28), false), 0.00001);
325	}	327	}
326		328
327	public void testDateBug_2Java() {	329	public void testDateBug_2Java() {
328	assertEquals(createDate(1900, Calendar.FEBRUARY, 28), HSSFDateUtil.getJavaDate(59.0));	330	assertEquals(createDate(1900, Calendar.FEBRUARY, 28), HSSFDateUtil.getJavaDate(59.0, false));
329	assertEquals(createDate(1900, Calendar.MARCH, 1), HSSFDateUtil.getJavaDate(61.0));	331	assertEquals(createDate(1900, Calendar.MARCH, 1), HSSFDateUtil.getJavaDate(61.0, false));
330		332
331	assertEquals(createDate(2002, Calendar.FEBRUARY, 28), HSSFDateUtil.getJavaDate(37315.00));	333	assertEquals(createDate(2002, Calendar.FEBRUARY, 28), HSSFDateUtil.getJavaDate(37315.00, false));
332	assertEquals(createDate(2002, Calendar.MARCH, 1), HSSFDateUtil.getJavaDate(37316.00));	334	assertEquals(createDate(2002, Calendar.MARCH, 1), HSSFDateUtil.getJavaDate(37316.00, false));
333	assertEquals(createDate(2002, Calendar.JANUARY, 1), HSSFDateUtil.getJavaDate(37257.00));	335	assertEquals(createDate(2002, Calendar.JANUARY, 1), HSSFDateUtil.getJavaDate(37257.00, false));
334	assertEquals(createDate(2004, Calendar.MARCH, 28), HSSFDateUtil.getJavaDate(38074.00));	336	assertEquals(createDate(2004, Calendar.MARCH, 28), HSSFDateUtil.getJavaDate(38074.00, false));
335	}	337	}
336		338
337	private Date createDate(int year, int month, int day) {	339	private Date createDate(int year, int month, int day) {
Lines 341-350 Link Here
341	return c.getTime();	343	return c.getTime();
342	}	344	}
343		345
		346	/**
		347	* Check if HSSFDateUtil.getAbsoluteDay works as advertised.
		348	*/
		349	public void testAbsoluteDay() {
		350	// 1 Jan 1900 is 1 day after 31 Dec 1899
		351	GregorianCalendar calendar = new GregorianCalendar(1900, 0, 1);
		352	assertEquals("Checking absolute day (1 Jan 1900)", 1, HSSFDateUtil.absoluteDay(calendar, false));
		353	// 1 Jan 1901 is 366 days after 31 Dec 1899
		354	calendar = new GregorianCalendar(1901, 0, 1);
		355	assertEquals("Checking absolute day (1 Jan 1901)", 366, HSSFDateUtil.absoluteDay(calendar, false));
		356	}
		357
344	public static void main(String [] args) {	358	public static void main(String [] args) {
345	System.out	359	System.out
346	.println("Testing org.apache.poi.hssf.usermodel.TestHSSFDateUtil");	360	.println("Testing org.apache.poi.hssf.usermodel.TestHSSFDateUtil");
347	junit.textui.TestRunner.run(TestHSSFDateUtil.class);	361	junit.textui.TestRunner.run(TestHSSFDateUtil.class);
348	}	362	}
349	}	363	}
350




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

Lines 63-69 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestReadWriteChart.java (-1 / +1 lines)
63	//System.out.println("first assertion for date");	63	//System.out.println("first assertion for date");
64	assertEquals(new GregorianCalendar(2000, 0, 1, 10, 51, 2).getTime(),	64	assertEquals(new GregorianCalendar(2000, 0, 1, 10, 51, 2).getTime(),
65	HSSFDateUtil	65	HSSFDateUtil
66	.getJavaDate(firstCell.getNumericCellValue()));	66	.getJavaDate(firstCell.getNumericCellValue(), false));
67	HSSFRow row = sheet.createRow(( short ) 15);	67	HSSFRow row = sheet.createRow(( short ) 15);
68	HSSFCell cell = row.createCell(( short ) 1);	68	HSSFCell cell = row.createCell(( short ) 1);
69		69

Return to bug 43551

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