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

(-)src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java (-2 / +23 lines)
Lines 70-75 Link Here
70
 * @author  Michael Harhen
70
 * @author  Michael Harhen
71
 * @author  Glen Stampoultzis (glens at apache.org)
71
 * @author  Glen Stampoultzis (glens at apache.org)
72
 * @author  Dan Sherman (dsherman at isisph.com)
72
 * @author  Dan Sherman (dsherman at isisph.com)
73
 * @author  Hack Kampbjorn (hak at 2mba.dk)
73
 */
74
 */
74
75
75
public class HSSFDateUtil
76
public class HSSFDateUtil
Lines 105-113 Link Here
105
        }
106
        }
106
        else
107
        else
107
        {
108
        {
109
	    // Because of daylight time saving we cannot use
110
	    //     date.getTime() - calStart.getTimeInMillis()
111
	    // as the difference in milliseconds between 00:00 and 04:00
112
	    // can be 3, 4 or 5 hours but Excel expects it to always
113
	    // be 4 hours.
114
	    // E.g. 2004-03-28 04:00 CEST - 2004-03-28 00:00 CET is 3 hours
115
	    // and 2004-10-31 04:00 CET - 2004-10-31 00:00 CEST is 5 hours
116
            double fraction = (((calStart.get(Calendar.HOUR_OF_DAY) * 60
117
                                 + calStart.get(Calendar.MINUTE)
118
                                ) * 60 + calStart.get(Calendar.SECOND)
119
                               ) * 1000 + calStart.get(Calendar.MILLISECOND)
120
                              ) / ( double ) DAY_MILLISECONDS;
108
            calStart = dayStart(calStart);
121
            calStart = dayStart(calStart);
109
            double fraction = (date.getTime() - calStart.getTime().getTime())
110
                              / ( double ) DAY_MILLISECONDS;
111
122
112
            return fraction + ( double ) absoluteDay(calStart)
123
            return fraction + ( double ) absoluteDay(calStart)
113
                   - CAL_1900_ABSOLUTE;
124
                   - CAL_1900_ABSOLUTE;
Lines 133-142 Link Here
133
     *  Given an Excel date with either 1900 or 1904 date windowing,
144
     *  Given an Excel date with either 1900 or 1904 date windowing,
134
     *  converts it to a java.util.Date.
145
     *  converts it to a java.util.Date.
135
     *
146
     *
147
     *  NOTE: If the default <code>TimeZone</code> in Java uses Daylight
148
     *  Saving Time then the conversion back to an Excel date may not give
149
     *  the same value, that is the comparison
150
     *  <CODE>excelDate == getExcelDate(getJavaDate(excelDate,false))</CODE>
151
     *  is not always true. For example if default timezone is
152
     *  <code>Europe/Copenhagen</code>, on 2004-03-28 the minute after
153
     *  01:59 CET is 03:00 CEST, if the excel date represents a time between
154
     *  02:00 and 03:00 then it is converted to past 03:00 summer time
155
     *
136
     *  @param date  The Excel date.
156
     *  @param date  The Excel date.
137
     *  @param use1904windowing  true if date uses 1904 windowing,
157
     *  @param use1904windowing  true if date uses 1904 windowing,
138
     *   or false if using 1900 date windowing.
158
     *   or false if using 1900 date windowing.
139
     *  @return Java representation of the date, or null if date is not a valid Excel date
159
     *  @return Java representation of the date, or null if date is not a valid Excel date
160
     *  @see java.util.TimeZone
140
     */
161
     */
141
    public static Date getJavaDate(double date, boolean use1904windowing) {
162
    public static Date getJavaDate(double date, boolean use1904windowing) {
142
        if (isValidExcelDate(date)) {
163
        if (isValidExcelDate(date)) {
(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java (+110 lines)
Lines 58-64 Link Here
58
import junit.framework.TestCase;
58
import junit.framework.TestCase;
59
59
60
import java.util.Date;
60
import java.util.Date;
61
import java.util.Calendar;
61
import java.util.GregorianCalendar;
62
import java.util.GregorianCalendar;
63
import java.util.TimeZone;
62
64
63
/**
65
/**
64
 * Class TestHSSFDateUtil
66
 * Class TestHSSFDateUtil
Lines 66-71 Link Here
66
 *
68
 *
67
 * @author
69
 * @author
68
 * @author  Dan Sherman (dsherman at isisph.com)
70
 * @author  Dan Sherman (dsherman at isisph.com)
71
 * @author  Hack Kampbjorn (hak at 2mba.dk)
69
 * @version %I%, %G%
72
 * @version %I%, %G%
70
 */
73
 */
71
74
Lines 115-118 Link Here
115
                        dateIf1904.getTime(),
118
                        dateIf1904.getTime(),
116
                           HSSFDateUtil.getJavaDate(excelDate,true).getTime());
119
                           HSSFDateUtil.getJavaDate(excelDate,true).getTime());
117
    }
120
    }
121
122
    /**
123
     * Checks the conversion of a java.util.date to Excel on a day when
124
     * Daylight Saving Time starts.
125
     */
126
    public void testExcelConversionOnDSTStart() {
127
        TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
128
	TimeZone.setDefault(cet);
129
	Calendar cal = new GregorianCalendar(2004, Calendar.MARCH, 28);
130
	for (int hour = 0; hour < 24; hour++) {
131
132
            // Skip 02:00 CET as that is the Daylight change time
133
            // and Java converts it automatically to 03:00 CEST
134
            if (hour == 2) {
135
                continue;
136
            }
137
138
	    cal.set(Calendar.HOUR_OF_DAY, hour);
139
	    Date javaDate = cal.getTime();
140
	    double excelDate = HSSFDateUtil.getExcelDate(javaDate);
141
	    double difference = excelDate - Math.floor(excelDate);
142
            int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;
143
	    assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
144
                             hour,
145
                                differenceInHours);
146
	    assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
147
                             javaDate.getTime(),
148
                                HSSFDateUtil.getJavaDate(excelDate).getTime());
149
	}
150
    }
151
152
    /**
153
     * Checks the conversion of an Excel date to a java.util.date on a day when
154
     * Daylight Saving Time starts.
155
     */
156
    public void testJavaConversionOnDSTStart() {
157
        TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
158
	TimeZone.setDefault(cet);
159
	Calendar cal = new GregorianCalendar(2004, Calendar.MARCH, 28);
160
        double excelDate = HSSFDateUtil.getExcelDate(cal.getTime());
161
        double oneHour = 1.0 / 24;
162
        double oneMinute = oneHour / 60;
163
	for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {
164
165
            // Skip 02:00 CET as that is the Daylight change time
166
            // and Java converts it automatically to 03:00 CEST            
167
            if (hour == 2) {
168
                  continue;
169
            }
170
171
	    cal.set(Calendar.HOUR_OF_DAY, hour);
172
            Date javaDate = HSSFDateUtil.getJavaDate(excelDate);
173
            assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
174
                             excelDate,
175
                                HSSFDateUtil.getExcelDate(javaDate), oneMinute);
176
	}
177
    }
178
179
    /**
180
     * Checks the conversion of a java.util.Date to Excel on a day when
181
     * Daylight Saving Time ends.
182
     */
183
    public void testExcelConversionOnDSTEnd() {
184
        TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
185
	TimeZone.setDefault(cet);
186
	Calendar cal = new GregorianCalendar(2004, Calendar.OCTOBER, 31);
187
	for (int hour = 0; hour < 24; hour++) {
188
	    cal.set(Calendar.HOUR_OF_DAY, hour);
189
	    Date javaDate = cal.getTime();
190
	    double excelDate = HSSFDateUtil.getExcelDate(javaDate);
191
	    double difference = excelDate - Math.floor(excelDate);
192
            int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;
193
	    assertEquals("Checking " + hour + " hour on Daylight Saving Time end date",
194
                             hour,
195
                                differenceInHours);
196
	    assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
197
                             javaDate.getTime(),
198
                                HSSFDateUtil.getJavaDate(excelDate).getTime());
199
	}
200
    }
201
202
    /**
203
     * Checks the conversion of an Excel date to java.util.Date on a day when
204
     * Daylight Saving Time ends.
205
     */
206
    public void testJavaConversionOnDSTEnd() {
207
        TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
208
	TimeZone.setDefault(cet);
209
	Calendar cal = new GregorianCalendar(2004, Calendar.OCTOBER, 31);
210
        double excelDate = HSSFDateUtil.getExcelDate(cal.getTime());
211
        double oneHour = 1.0 / 24;
212
        double oneMinute = oneHour / 60;
213
	for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {
214
	    cal.set(Calendar.HOUR_OF_DAY, hour);
215
            Date javaDate = HSSFDateUtil.getJavaDate(excelDate);
216
            assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
217
                             excelDate,
218
                                HSSFDateUtil.getExcelDate(javaDate), oneMinute);
219
	}
220
    }
221
222
    public static void main(String [] args) {
223
        System.out
224
        .println("Testing org.apache.poi.hssf.usermodel.TestHSSFDateUtil");
225
        junit.textui.TestRunner.run(TestHSSFDateUtil.class);
226
    }
227
118
}
228
}

Return to bug 27574