1: <?php
  2: 
  3: /**
  4:  * PHPExcel
  5:  *
  6:  * Copyright (c) 2006 - 2014 PHPExcel
  7:  *
  8:  * This library is free software; you can redistribute it and/or
  9:  * modify it under the terms of the GNU Lesser General Public
 10:  * License as published by the Free Software Foundation; either
 11:  * version 2.1 of the License, or (at your option) any later version.
 12:  *
 13:  * This library is distributed in the hope that it will be useful,
 14:  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 15:  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 16:  * Lesser General Public License for more details.
 17:  *
 18:  * You should have received a copy of the GNU Lesser General Public
 19:  * License along with this library; if not, write to the Free Software
 20:  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 21:  *
 22:  * @category   PHPExcel
 23:  * @package PHPExcel_Shared
 24:  * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
 25:  * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt   LGPL
 26:  * @version 1.8.0, 2014-03-02
 27:  */
 28: 
 29: 
 30: /**
 31:  * PHPExcel_Shared_Date
 32:  *
 33:  * @category   PHPExcel
 34:  * @package PHPExcel_Shared
 35:  * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
 36:  */
 37: class PHPExcel_Shared_Date
 38: {
 39:     /** constants */
 40:     const CALENDAR_WINDOWS_1900 = 1900;     //  Base date of 1st Jan 1900 = 1.0
 41:     const CALENDAR_MAC_1904 = 1904;         //  Base date of 2nd Jan 1904 = 1.0
 42: 
 43:     /*
 44:      * Names of the months of the year, indexed by shortname
 45:      * Planned usage for locale settings
 46:      *
 47:      * @public
 48:      * @var string[]
 49:      */
 50:     public static $_monthNames = array( 'Jan' => 'January',
 51:                                         'Feb' => 'February',
 52:                                         'Mar' => 'March',
 53:                                         'Apr' => 'April',
 54:                                         'May' => 'May',
 55:                                         'Jun' => 'June',
 56:                                         'Jul' => 'July',
 57:                                         'Aug' => 'August',
 58:                                         'Sep' => 'September',
 59:                                         'Oct' => 'October',
 60:                                         'Nov' => 'November',
 61:                                         'Dec' => 'December',
 62:                                       );
 63: 
 64:     /*
 65:      * Names of the months of the year, indexed by shortname
 66:      * Planned usage for locale settings
 67:      *
 68:      * @public
 69:      * @var string[]
 70:      */
 71:     public static $_numberSuffixes = array( 'st',
 72:                                             'nd',
 73:                                             'rd',
 74:                                             'th',
 75:                                           );
 76: 
 77:     /*
 78:      * Base calendar year to use for calculations
 79:      *
 80:      * @private
 81:      * @var int
 82:      */
 83:     protected static $_excelBaseDate    = self::CALENDAR_WINDOWS_1900;
 84: 
 85:     /**
 86:      * Set the Excel calendar (Windows 1900 or Mac 1904)
 87:      *
 88:      * @param    integer    $baseDate           Excel base date (1900 or 1904)
 89:      * @return   boolean                        Success or failure
 90:      */
 91:     public static function setExcelCalendar($baseDate) {
 92:         if (($baseDate == self::CALENDAR_WINDOWS_1900) ||
 93:             ($baseDate == self::CALENDAR_MAC_1904)) {
 94:             self::$_excelBaseDate = $baseDate;
 95:             return TRUE;
 96:         }
 97:         return FALSE;
 98:     }   //  function setExcelCalendar()
 99: 
100: 
101:     /**
102:      * Return the Excel calendar (Windows 1900 or Mac 1904)
103:      *
104:      * @return   integer    Excel base date (1900 or 1904)
105:      */
106:     public static function getExcelCalendar() {
107:         return self::$_excelBaseDate;
108:     }   //  function getExcelCalendar()
109: 
110: 
111:     /**
112:      *  Convert a date from Excel to PHP
113:      *
114:      *  @param      long        $dateValue          Excel date/time value
115:      *  @param      boolean     $adjustToTimezone   Flag indicating whether $dateValue should be treated as
116:      *                                                  a UST timestamp, or adjusted to UST
117:      *  @param      string      $timezone           The timezone for finding the adjustment from UST
118:      *  @return     long        PHP serialized date/time
119:      */
120:     public static function ExcelToPHP($dateValue = 0, $adjustToTimezone = FALSE, $timezone = NULL) {
121:         if (self::$_excelBaseDate == self::CALENDAR_WINDOWS_1900) {
122:             $my_excelBaseDate = 25569;
123:             //  Adjust for the spurious 29-Feb-1900 (Day 60)
124:             if ($dateValue < 60) {
125:                 --$my_excelBaseDate;
126:             }
127:         } else {
128:             $my_excelBaseDate = 24107;
129:         }
130: 
131:         // Perform conversion
132:         if ($dateValue >= 1) {
133:             $utcDays = $dateValue - $my_excelBaseDate;
134:             $returnValue = round($utcDays * 86400);
135:             if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
136:                 $returnValue = (integer) $returnValue;
137:             }
138:         } else {
139:             $hours = round($dateValue * 24);
140:             $mins = round($dateValue * 1440) - round($hours * 60);
141:             $secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
142:             $returnValue = (integer) gmmktime($hours, $mins, $secs);
143:         }
144: 
145:         $timezoneAdjustment = ($adjustToTimezone) ?
146:             PHPExcel_Shared_TimeZone::getTimezoneAdjustment($timezone, $returnValue) :
147:             0;
148: 
149:         // Return
150:         return $returnValue + $timezoneAdjustment;
151:     }   //  function ExcelToPHP()
152: 
153: 
154:     /**
155:      * Convert a date from Excel to a PHP Date/Time object
156:      *
157:      * @param   integer     $dateValue      Excel date/time value
158:      * @return  integer                     PHP date/time object
159:      */
160:     public static function ExcelToPHPObject($dateValue = 0) {
161:         $dateTime = self::ExcelToPHP($dateValue);
162:         $days = floor($dateTime / 86400);
163:         $time = round((($dateTime / 86400) - $days) * 86400);
164:         $hours = round($time / 3600);
165:         $minutes = round($time / 60) - ($hours * 60);
166:         $seconds = round($time) - ($hours * 3600) - ($minutes * 60);
167: 
168:         $dateObj = date_create('1-Jan-1970+'.$days.' days');
169:         $dateObj->setTime($hours,$minutes,$seconds);
170: 
171:         return $dateObj;
172:     }   //  function ExcelToPHPObject()
173: 
174: 
175:     /**
176:      *  Convert a date from PHP to Excel
177:      *
178:      *  @param  mixed       $dateValue          PHP serialized date/time or date object
179:      *  @param  boolean     $adjustToTimezone   Flag indicating whether $dateValue should be treated as
180:      *                                                  a UST timestamp, or adjusted to UST
181:      *  @param  string      $timezone           The timezone for finding the adjustment from UST
182:      *  @return mixed       Excel date/time value
183:      *                          or boolean FALSE on failure
184:      */
185:     public static function PHPToExcel($dateValue = 0, $adjustToTimezone = FALSE, $timezone = NULL) {
186:         $saveTimeZone = date_default_timezone_get();
187:         date_default_timezone_set('UTC');
188:         $retValue = FALSE;
189:         if ((is_object($dateValue)) && ($dateValue instanceof DateTime)) {
190:             $retValue = self::FormattedPHPToExcel( $dateValue->format('Y'), $dateValue->format('m'), $dateValue->format('d'),
191:                                                    $dateValue->format('H'), $dateValue->format('i'), $dateValue->format('s')
192:                                                  );
193:         } elseif (is_numeric($dateValue)) {
194:             $retValue = self::FormattedPHPToExcel( date('Y',$dateValue), date('m',$dateValue), date('d',$dateValue),
195:                                                    date('H',$dateValue), date('i',$dateValue), date('s',$dateValue)
196:                                                  );
197:         }
198:         date_default_timezone_set($saveTimeZone);
199: 
200:         return $retValue;
201:     }   //  function PHPToExcel()
202: 
203: 
204:     /**
205:      * FormattedPHPToExcel
206:      *
207:      * @param   long    $year
208:      * @param   long    $month
209:      * @param   long    $day
210:      * @param   long    $hours
211:      * @param   long    $minutes
212:      * @param   long    $seconds
213:      * @return  long                Excel date/time value
214:      */
215:     public static function FormattedPHPToExcel($year, $month, $day, $hours=0, $minutes=0, $seconds=0) {
216:         if (self::$_excelBaseDate == self::CALENDAR_WINDOWS_1900) {
217:             //
218:             //  Fudge factor for the erroneous fact that the year 1900 is treated as a Leap Year in MS Excel
219:             //  This affects every date following 28th February 1900
220:             //
221:             $excel1900isLeapYear = TRUE;
222:             if (($year == 1900) && ($month <= 2)) { $excel1900isLeapYear = FALSE; }
223:             $my_excelBaseDate = 2415020;
224:         } else {
225:             $my_excelBaseDate = 2416481;
226:             $excel1900isLeapYear = FALSE;
227:         }
228: 
229:         //  Julian base date Adjustment
230:         if ($month > 2) {
231:             $month -= 3;
232:         } else {
233:             $month += 9;
234:             --$year;
235:         }
236: 
237:         //  Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0)
238:         $century = substr($year,0,2);
239:         $decade = substr($year,2,2);
240:         $excelDate = floor((146097 * $century) / 4) + floor((1461 * $decade) / 4) + floor((153 * $month + 2) / 5) + $day + 1721119 - $my_excelBaseDate + $excel1900isLeapYear;
241: 
242:         $excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400;
243: 
244:         return (float) $excelDate + $excelTime;
245:     }   //  function FormattedPHPToExcel()
246: 
247: 
248:     /**
249:      * Is a given cell a date/time?
250:      *
251:      * @param    PHPExcel_Cell  $pCell
252:      * @return   boolean
253:      */
254:     public static function isDateTime(PHPExcel_Cell $pCell) {
255:         return self::isDateTimeFormat(
256:             $pCell->getWorksheet()->getStyle(
257:                 $pCell->getCoordinate()
258:             )->getNumberFormat()
259:         );
260:     }   //  function isDateTime()
261: 
262: 
263:     /**
264:      * Is a given number format a date/time?
265:      *
266:      * @param    PHPExcel_Style_NumberFormat    $pFormat
267:      * @return   boolean
268:      */
269:     public static function isDateTimeFormat(PHPExcel_Style_NumberFormat $pFormat) {
270:         return self::isDateTimeFormatCode($pFormat->getFormatCode());
271:     }   //  function isDateTimeFormat()
272: 
273: 
274:     private static  $possibleDateFormatCharacters = 'eymdHs';
275: 
276:     /**
277:      * Is a given number format code a date/time?
278:      *
279:      * @param    string $pFormatCode
280:      * @return   boolean
281:      */
282:     public static function isDateTimeFormatCode($pFormatCode = '') {
283:         if (strtolower($pFormatCode) === strtolower(PHPExcel_Style_NumberFormat::FORMAT_GENERAL))
284:             //  "General" contains an epoch letter 'e', so we trap for it explicitly here (case-insensitive check)
285:             return FALSE;
286:         if (preg_match('/[0#]E[+-]0/i', $pFormatCode))
287:             //  Scientific format
288:             return FALSE;
289:         // Switch on formatcode
290:         switch ($pFormatCode) {
291:             //  Explicitly defined date formats
292:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD:
293:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2:
294:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY:
295:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYSLASH:
296:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYMINUS:
297:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMMINUS:
298:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_MYMINUS:
299:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME:
300:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME1:
301:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME2:
302:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3:
303:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4:
304:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME5:
305:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME6:
306:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME7:
307:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME8:
308:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH:
309:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14:
310:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15:
311:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX16:
312:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX17:
313:             case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX22:
314:                 return TRUE;
315:         }
316: 
317:         //  Typically number, currency or accounting (or occasionally fraction) formats
318:         if ((substr($pFormatCode,0,1) == '_') || (substr($pFormatCode,0,2) == '0 ')) {
319:             return FALSE;
320:         }
321:         // Try checking for any of the date formatting characters that don't appear within square braces
322:         if (preg_match('/(^|\])[^\[]*['.self::$possibleDateFormatCharacters.']/i',$pFormatCode)) {
323:             //  We might also have a format mask containing quoted strings...
324:             //      we don't want to test for any of our characters within the quoted blocks
325:             if (strpos($pFormatCode,'"') !== FALSE) {
326:                 $segMatcher = FALSE;
327:                 foreach(explode('"',$pFormatCode) as $subVal) {
328:                     //  Only test in alternate array entries (the non-quoted blocks)
329:                     if (($segMatcher = !$segMatcher) &&
330:                         (preg_match('/(^|\])[^\[]*['.self::$possibleDateFormatCharacters.']/i',$subVal))) {
331:                         return TRUE;
332:                     }
333:                 }
334:                 return FALSE;
335:             }
336:             return TRUE;
337:         }
338: 
339:         // No date...
340:         return FALSE;
341:     }   //  function isDateTimeFormatCode()
342: 
343: 
344:     /**
345:      * Convert a date/time string to Excel time
346:      *
347:      * @param   string  $dateValue      Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
348:      * @return  float|FALSE     Excel date/time serial value
349:      */
350:     public static function stringToExcel($dateValue = '') {
351:         if (strlen($dateValue) < 2)
352:             return FALSE;
353:         if (!preg_match('/^(\d{1,4}[ \.\/\-][A-Z]{3,9}([ \.\/\-]\d{1,4})?|[A-Z]{3,9}[ \.\/\-]\d{1,4}([ \.\/\-]\d{1,4})?|\d{1,4}[ \.\/\-]\d{1,4}([ \.\/\-]\d{1,4})?)( \d{1,2}:\d{1,2}(:\d{1,2})?)?$/iu', $dateValue))
354:             return FALSE;
355: 
356:         $dateValueNew = PHPExcel_Calculation_DateTime::DATEVALUE($dateValue);
357: 
358:         if ($dateValueNew === PHPExcel_Calculation_Functions::VALUE()) {
359:             return FALSE;
360:         } else {
361:             if (strpos($dateValue, ':') !== FALSE) {
362:                 $timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($dateValue);
363:                 if ($timeValue === PHPExcel_Calculation_Functions::VALUE()) {
364:                     return FALSE;
365:                 }
366:                 $dateValueNew += $timeValue;
367:             }
368:             return $dateValueNew;
369:         }
370: 
371: 
372:     }
373: 
374:     public static function monthStringToNumber($month) {
375:         $monthIndex = 1;
376:         foreach(self::$_monthNames as $shortMonthName => $longMonthName) {
377:             if (($month === $longMonthName) || ($month === $shortMonthName)) {
378:                 return $monthIndex;
379:             }
380:             ++$monthIndex;
381:         }
382:         return $month;
383:     }
384: 
385:     public static function dayStringToNumber($day) {
386:         $strippedDayValue = (str_replace(self::$_numberSuffixes,'',$day));
387:         if (is_numeric($strippedDayValue)) {
388:             return $strippedDayValue;
389:         }
390:         return $day;
391:     }
392: 
393: }
394: