001    /**
002     * =========================================
003     * LibFormula : a free Java formula library
004     * =========================================
005     *
006     * Project Info:  http://reporting.pentaho.org/libformula/
007     *
008     * (C) Copyright 2006-2007, by Pentaho Corporation and Contributors.
009     *
010     * This library is free software; you can redistribute it and/or modify it under the terms
011     * of the GNU Lesser General Public License as published by the Free Software Foundation;
012     * either version 2.1 of the License, or (at your option) any later version.
013     *
014     * This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
015     * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
016     * See the GNU Lesser General Public License for more details.
017     *
018     * You should have received a copy of the GNU Lesser General Public License along with this
019     * library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
020     * Boston, MA 02111-1307, USA.
021     *
022     * [Java is a trademark or registered trademark of Sun Microsystems, Inc.
023     * in the United States and other countries.]
024     *
025     *
026     * ------------
027     * $Id: HSSFDateUtil.java 3522 2007-10-16 10:56:57Z tmorgner $
028     * ------------
029     * (C) Copyright 2006-2007, by Pentaho Corporation.
030     */
031    
032    /*
033     * DateUtil.java
034     *
035     * Created on January 19, 2002, 9:30 AM
036     */
037    package org.jfree.formula.util;
038    
039    import java.util.Calendar;
040    import java.util.Date;
041    import java.util.GregorianCalendar;
042    
043    import org.jfree.formula.LibFormulaBoot;
044    
045    /**
046     * Contains methods for dealing with Excel dates.
047     * <br/>
048     * Modified by Cedric Pronzato
049     *
050     * @author  Michael Harhen
051     * @author  Glen Stampoultzis (glens at apache.org)
052     * @author  Dan Sherman (dsherman at isisph.com)
053     * @author  Hack Kampbjorn (hak at 2mba.dk)
054     */
055    
056    public class HSSFDateUtil
057    {
058        private HSSFDateUtil()
059        {
060        }
061    
062        private static final int    BAD_DATE          =
063            -1;   // used to specify that date is invalid
064        private static final long   DAY_MILLISECONDS  = 24 * 60 * 60 * 1000;
065        private static final double CAL_1900_ABSOLUTE =
066            ( double ) absoluteDay(new GregorianCalendar(1900, Calendar
067            .JANUARY, 1)) - 2.0;
068    
069        /**
070         * Given a Date, converts it into a double representing its internal Excel representation,
071         *   which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
072         *
073         * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
074         * @param  date the Date
075         */
076    
077        public static double getExcelDate(final Date date)
078        {
079            Calendar calStart = new GregorianCalendar();
080    
081            calStart.setTime(
082                date);   // If date includes hours, minutes, and seconds, set them to 0
083    //        if (calStart.get(Calendar.YEAR) < 1900)
084    //        {
085    //            return BAD_DATE;
086    //        }
087    //        else
088    //        {
089                // Because of daylight time saving we cannot use
090                //     date.getTime() - calStart.getTimeInMillis()
091                // as the difference in milliseconds between 00:00 and 04:00
092                // can be 3, 4 or 5 hours but Excel expects it to always
093                // be 4 hours.
094                // E.g. 2004-03-28 04:00 CEST - 2004-03-28 00:00 CET is 3 hours
095                // and 2004-10-31 04:00 CET - 2004-10-31 00:00 CEST is 5 hours
096                final double fraction = (((calStart.get(Calendar.HOUR_OF_DAY) * 60
097                                     + calStart.get(Calendar.MINUTE)
098                                    ) * 60 + calStart.get(Calendar.SECOND)
099                                   ) * 1000 + calStart.get(Calendar.MILLISECOND)
100                                  ) / ( double ) DAY_MILLISECONDS;
101                calStart = dayStart(calStart);
102    
103                return fraction + ( double ) absoluteDay(calStart)
104                       - CAL_1900_ABSOLUTE;
105            }
106    //    }
107    
108        /**
109         * Given a excel date, converts it into a Date.
110         * Assumes 1900 date windowing.
111         *
112         * @param  date the Excel Date
113         *
114         * @return Java representation of a date (null if error)
115         * @see #getJavaDate(double,boolean)
116         */
117    
118        public static Date getJavaDate(final double date)
119        {
120          final String dateSystem = LibFormulaBoot.getInstance().getGlobalConfig()
121            .getConfigProperty("org.jfree.formula.datesystem.1904", "false");
122          return getJavaDate(date, "true".equals(dateSystem));
123        }
124    
125        /**
126         *  Given an Excel date with either 1900 or 1904 date windowing,
127         *  converts it to a java.util.Date.
128         *
129         *  NOTE: If the default <code>TimeZone</code> in Java uses Daylight
130         *  Saving Time then the conversion back to an Excel date may not give
131         *  the same value, that is the comparison
132         *  <CODE>excelDate == getExcelDate(getJavaDate(excelDate,false))</CODE>
133         *  is not always true. For example if default timezone is
134         *  <code>Europe/Copenhagen</code>, on 2004-03-28 the minute after
135         *  01:59 CET is 03:00 CEST, if the excel date represents a time between
136         *  02:00 and 03:00 then it is converted to past 03:00 summer time
137         *
138         *  @param date  The Excel date.
139         *  @param use1904windowing  true if date uses 1904 windowing,
140         *   or false if using 1900 date windowing.
141         *  @return Java representation of the date, or null if date is not a valid Excel date
142         *  @see java.util.TimeZone
143         */
144        public static Date getJavaDate(final double date, final boolean use1904windowing) {
145            if (isValidExcelDate(date)) {
146                int startYear = 1900;
147                int dayAdjust = -1; // Excel thinks 2/29/1900 is a valid date, which it isn't
148                final int wholeDays = (int)Math.floor(date);
149                if (use1904windowing) {
150                    startYear = 1904;
151                    dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as the first day
152                }
153                else if (wholeDays < 61) {
154                    // Date is prior to 3/1/1900, so adjust because Excel thinks 2/29/1900 exists
155                    // If Excel date == 2/29/1900, will become 3/1/1900 in Java representation
156                    dayAdjust = 0;
157                }
158                final GregorianCalendar calendar = new GregorianCalendar(startYear,0,
159                                                         wholeDays + dayAdjust);
160                final int millisecondsInDay = (int)((date - Math.floor(date)) *
161                                              (double) DAY_MILLISECONDS + 0.5);
162                calendar.set(GregorianCalendar.MILLISECOND, millisecondsInDay);
163                return calendar.getTime();
164            }
165            else {
166                return null;
167            }
168        }
169    
170        /**
171         * given a format ID this will check whether the format represents
172         * an internal date format or not.
173         */
174        public static boolean isInternalDateFormat(final int format) {
175          boolean retval;
176    
177                switch(format) {
178                    // Internal Date Formats as described on page 427 in
179                    // Microsoft Excel Dev's Kit...
180                    case 0x0e:
181                    case 0x0f:
182                    case 0x10:
183                    case 0x11:
184                    case 0x12:
185                    case 0x13:
186                    case 0x14:
187                    case 0x15:
188                    case 0x16:
189                    case 0x2d:
190                    case 0x2e:
191                    case 0x2f:
192                        retval = true;
193                        break;
194    
195                    default:
196                        retval = false;
197                        break;
198                }
199           return retval;
200        }
201    
202    
203    
204        /**
205         * Given a double, checks if it is a valid Excel date.
206         *
207         * @return true if valid
208         * @param  value the double value
209         */
210    
211        public static boolean isValidExcelDate(final double value)
212        {
213            return (value > -Double.MIN_VALUE);
214        }
215    
216        /**
217         * Given a Calendar, return the number of days since 1600/12/31.
218         *
219         * @return days number of days since 1600/12/31
220         * @param  cal the Calendar
221         * @exception IllegalArgumentException if date is invalid
222         */
223    
224        private static int absoluteDay(final Calendar cal)
225        {
226            return cal.get(Calendar.DAY_OF_YEAR)
227                   + daysInPriorYears(cal.get(Calendar.YEAR));
228        }
229    
230        /**
231         * Return the number of days in prior years since 1601
232         *
233         * @return    days  number of days in years prior to yr.
234         * @param     yr    a year (1600 < yr < 4000)
235         * @exception IllegalArgumentException if year is outside of range.
236         */
237    
238        private static int daysInPriorYears(final int yr)
239        {
240            if (yr < 1601)
241            {
242                throw new IllegalArgumentException(
243                    "'year' must be 1601 or greater");
244            }
245            final int y    = yr - 1601;
246    
247          return 365 * y      // days in prior years
248                     + y / 4      // plus julian leap days in prior years
249                     - y / 100    // minus prior century years
250                     + y / 400;
251        }
252    
253        // set HH:MM:SS fields of cal to 00:00:00:000
254        private static Calendar dayStart(final Calendar cal)
255        {
256            cal.get(Calendar
257                .HOUR_OF_DAY);   // force recalculation of internal fields
258            cal.set(Calendar.HOUR_OF_DAY, 0);
259            cal.set(Calendar.MINUTE, 0);
260            cal.set(Calendar.SECOND, 0);
261            cal.set(Calendar.MILLISECOND, 0);
262            cal.get(Calendar
263                .HOUR_OF_DAY);   // force recalculation of internal fields
264            return cal;
265        }
266    
267        // ---------------------------------------------------------------------------------------------------------
268    }