Subversion Repositories eFlore/Applications.cel

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
2390 jpm 1
<?php
2
 
3
/**
4
 * PHPExcel
5
 *
6
 * Copyright (c) 2006 - 2013 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 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
25
 * @license	http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
26
 * @version	##VERSION##, ##DATE##
27
 */
28
 
29
 
30
/**
31
 * PHPExcel_Shared_Date
32
 *
33
 * @category   PHPExcel
34
 * @package	PHPExcel_Shared
35
 * @copyright  Copyright (c) 2006 - 2013 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
		// Switch on formatcode
284
		switch ($pFormatCode) {
285
			//	General contains an epoch letter 'e', so we trap for it explicitly here
286
			case PHPExcel_Style_NumberFormat::FORMAT_GENERAL:
287
				return FALSE;
288
			//	Explicitly defined date formats
289
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD:
290
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2:
291
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY:
292
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYSLASH:
293
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYMINUS:
294
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMMINUS:
295
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_MYMINUS:
296
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME:
297
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME1:
298
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME2:
299
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3:
300
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4:
301
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME5:
302
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME6:
303
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME7:
304
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME8:
305
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH:
306
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14:
307
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15:
308
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX16:
309
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX17:
310
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX22:
311
				return TRUE;
312
		}
313
 
314
		//	Typically number, currency or accounting (or occasionally fraction) formats
315
		if ((substr($pFormatCode,0,1) == '_') || (substr($pFormatCode,0,2) == '0 ')) {
316
			return FALSE;
317
		}
318
		// Try checking for any of the date formatting characters that don't appear within square braces
319
		if (preg_match('/(^|\])[^\[]*['.self::$possibleDateFormatCharacters.']/i',$pFormatCode)) {
320
			//	We might also have a format mask containing quoted strings...
321
			//		we don't want to test for any of our characters within the quoted blocks
322
			if (strpos($pFormatCode,'"') !== FALSE) {
323
				$segMatcher = FALSE;
324
				foreach(explode('"',$pFormatCode) as $subVal) {
325
					//	Only test in alternate array entries (the non-quoted blocks)
326
					if (($segMatcher = !$segMatcher) &&
327
						(preg_match('/(^|\])[^\[]*['.self::$possibleDateFormatCharacters.']/i',$subVal))) {
328
						return TRUE;
329
					}
330
				}
331
				return FALSE;
332
			}
333
			return TRUE;
334
		}
335
 
336
		// No date...
337
		return FALSE;
338
	}	//	function isDateTimeFormatCode()
339
 
340
 
341
	/**
342
	 * Convert a date/time string to Excel time
343
	 *
344
	 * @param	string	$dateValue		Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
345
	 * @return	float|FALSE		Excel date/time serial value
346
	 */
347
	public static function stringToExcel($dateValue = '') {
348
		if (strlen($dateValue) < 2)
349
			return FALSE;
350
		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))
351
			return FALSE;
352
 
353
		$dateValueNew = PHPExcel_Calculation_DateTime::DATEVALUE($dateValue);
354
 
355
		if ($dateValueNew === PHPExcel_Calculation_Functions::VALUE()) {
356
			return FALSE;
357
		} else {
358
			if (strpos($dateValue, ':') !== FALSE) {
359
				$timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($dateValue);
360
				if ($timeValue === PHPExcel_Calculation_Functions::VALUE()) {
361
					return FALSE;
362
				}
363
				$dateValueNew += $timeValue;
364
			}
365
			return $dateValueNew;
366
		}
367
 
368
 
369
	}
370
 
371
    public static function monthStringToNumber($month) {
372
        $monthIndex = 1;
373
        foreach(self::$_monthNames as $shortMonthName => $longMonthName) {
374
            if (($month === $longMonthName) || ($month === $shortMonthName)) {
375
                return $monthIndex;
376
            }
377
            ++$monthIndex;
378
        }
379
        return $month;
380
    }
381
 
382
    public static function dayStringToNumber($day) {
383
		$strippedDayValue = (str_replace(self::$_numberSuffixes,'',$day));
384
		if (is_numeric($strippedDayValue)) {
385
		    return $strippedDayValue;
386
		}
387
		return $day;
388
    }
389
 
390
}