File Source for Date.php

PHPExcel_Shared

# Source for file Date.php

Documentation is available at Date.php

1. <?php
3. /**
4. * PHPExcel
5. *
6. * Copyright (c) 2006 - 2011 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 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
25. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
26. * @version 1.7.6, 2011-02-27
27. */
30. /**
31. * PHPExcel_Shared_Date
32. *
33. * @category PHPExcel
34. * @package PHPExcel_Shared
35. * @copyright Copyright (c) 2006 - 2011 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
43. private static $ExcelBaseDate = self:: CALENDAR_WINDOWS_1900;
45. public static $dateTimeObjectType = 'DateTime';
48. /**
49. * Set the Excel calendar (Windows 1900 or Mac 1904)
50. *
51. * @param integer $baseDate Excel base date
52. * @return boolean Success or failure
53. */
54. public static function setExcelCalendar($baseDate) {
55. if (($baseDate == self:: CALENDAR_WINDOWS_1900) ||
56. ($baseDate == self:: CALENDAR_MAC_1904)) {
57. self::$ExcelBaseDate = $baseDate;
58. return True;
59. }
60. return False;
61. } // function setExcelCalendar()
64. /**
65. * Return the Excel calendar (Windows 1900 or Mac 1904)
66. *
67. * @return integer $baseDate Excel base date
68. */
69. public static function getExcelCalendar() {
70. return self::$ExcelBaseDate;
71. } // function getExcelCalendar()
74. /**
75. * Convert a date from Excel to PHP
76. *
77. * @param long $dateValue Excel date/time value
78. * @return long PHP serialized date/time
79. */
80. public static function ExcelToPHP($dateValue = 0) {
81. if (self::$ExcelBaseDate == self:: CALENDAR_WINDOWS_1900) {
82. $myExcelBaseDate = 25569;
83. // Adjust for the spurious 29-Feb-1900 (Day 60)
84. if ($dateValue < 60) {
85. --$myExcelBaseDate;
86. }
87. } else {
88. $myExcelBaseDate = 24107;
89. }
91. // Perform conversion
92. if ($dateValue >= 1) {
93. $utcDays = $dateValue - $myExcelBaseDate;
94. $returnValue = round($utcDays * 24 * 60 * 60);
95. if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
96. $returnValue = (integer) $returnValue;
97. }
98. } else {
99. $hours = round($dateValue * 24);
100. $mins = round($dateValue * 24 * 60) - round($hours * 60);
101. $secs = round($dateValue * 24 * 60 * 60) - round($hours * 60 * 60) - round($mins * 60);
102. $returnValue = (integer) gmmktime($hours, $mins, $secs);
103. }
105. // Return
106. return $returnValue;
107. } // function ExcelToPHP()
110. /**
111. * Convert a date from Excel to a PHP Date/Time object
112. *
113. * @param long $dateValue Excel date/time value
114. * @return long PHP date/time object
115. */
116. public static function ExcelToPHPObject($dateValue = 0) {
117. $dateTime = self:: ExcelToPHP($dateValue);
118. $days = floor($dateTime / 86400);
119. $time = round((($dateTime / 86400) - $days) * 86400);
120. $hours = round($time / 3600);
121. $minutes = round($time / 60) - ($hours * 60);
122. $seconds = round($time) - ($hours * 3600) - ($minutes * 60);
124. $dateObj = date_create('1-Jan-1970+'.$days.' days');
125. $dateObj->setTime($hours,$minutes,$seconds);
127. return $dateObj;
128. } // function ExcelToPHPObject()
131. /**
132. * Convert a date from PHP to Excel
133. *
134. * @param mixed $dateValue PHP serialized date/time or date object
135. * @return mixed Excel date/time value
136. * or boolean False on failure
137. */
138. public static function PHPToExcel($dateValue = 0) {
139. $saveTimeZone = date_default_timezone_get();
140. date_default_timezone_set('UTC');
141. $retValue = False;
142. if (( is_object($dateValue)) && ($dateValue instanceof self::$dateTimeObjectType)) {
143. $retValue = self:: FormattedPHPToExcel( $dateValue->format('Y'), $dateValue->format('m'), $dateValue->format('d'),
144. $dateValue->format('H'), $dateValue->format('i'), $dateValue->format('s')
145. );
146. } elseif ( is_numeric($dateValue)) {
147. $retValue = self:: FormattedPHPToExcel( date('Y',$dateValue), date('m',$dateValue), date('d',$dateValue),
148. date('H',$dateValue), date('i',$dateValue), date('s',$dateValue)
149. );
150. }
151. date_default_timezone_set($saveTimeZone);
153. return $retValue;
154. } // function PHPToExcel()
157. /**
158. * FormattedPHPToExcel
159. *
160. * @param long $year
161. * @param long $month
162. * @param long $day
163. * @param long $hours
164. * @param long $minutes
165. * @param long $seconds
166. * @return long Excel date/time value
167. */
168. public static function FormattedPHPToExcel($year, $month, $day, $hours=0, $minutes=0, $seconds=0) {
169. if (self::$ExcelBaseDate == self:: CALENDAR_WINDOWS_1900) {
170. //
171. // Fudge factor for the erroneous fact that the year 1900 is treated as a Leap Year in MS Excel
172. // This affects every date following 28th February 1900
173. //
174. $excel1900isLeapYear = True;
175. if (($year == 1900) && ($month <= 2)) { $excel1900isLeapYear = False; }
176. $myExcelBaseDate = 2415020;
177. } else {
178. $myExcelBaseDate = 2416481;
179. $excel1900isLeapYear = False;
180. }
182. // Julian base date Adjustment
183. if ($month > 2) {
184. $month = $month - 3;
185. } else {
186. $month = $month + 9;
187. --$year;
188. }
190. // Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0)
191. $century = substr($year,0,2);
192. $decade = substr($year,2,2);
193. $excelDate = floor((146097 * $century) / 4) + floor((1461 * $decade) / 4) + floor((153 * $month + 2) / 5) + $day + 1721119 - $myExcelBaseDate + $excel1900isLeapYear;
195. $excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400;
197. return (float) $excelDate + $excelTime;
198. } // function FormattedPHPToExcel()
201. /**
202. * Is a given cell a date/time?
203. *
204. * @param PHPExcel_Cell $pCell
205. * @return boolean
206. */
207. public static function isDateTime(PHPExcel_Cell $pCell) {
208. return self:: isDateTimeFormat($pCell->getParent()->getStyle($pCell->getCoordinate())->getNumberFormat());
209. } // function isDateTime()
212. /**
213. * Is a given number format a date/time?
214. *
215. * @param PHPExcel_Style_NumberFormat $pFormat
216. * @return boolean
217. */
218. public static function isDateTimeFormat(PHPExcel_Style_NumberFormat $pFormat) {
219. return self:: isDateTimeFormatCode($pFormat->getFormatCode());
220. } // function isDateTimeFormat()
223. private static $possibleDateFormatCharacters = 'ymdHs';
225. /**
226. * Is a given number format code a date/time?
227. *
228. * @param string $pFormatCode
229. * @return boolean
230. */
231. public static function isDateTimeFormatCode($pFormatCode = '') {
232. // Switch on formatcode
233. switch ($pFormatCode) {
234. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_YYYYMMDD:
235. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_YYYYMMDD2:
236. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_DDMMYYYY:
237. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_DMYSLASH:
238. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_DMYMINUS:
239. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_DMMINUS:
240. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_MYMINUS:
241. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_DATETIME:
242. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_TIME1:
243. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_TIME2:
244. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_TIME3:
245. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_TIME4:
246. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_TIME5:
247. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_TIME6:
248. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_TIME7:
249. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_TIME8:
250. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_YYYYMMDDSLASH:
251. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_XLSX14:
252. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_XLSX15:
253. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_XLSX16:
254. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_XLSX17:
255. case PHPExcel_Style_NumberFormat:: FORMAT_DATE_XLSX22:
256. return true;
257. }
259. // Typically number, currency or accounting (or occasionally fraction) formats
260. if (( substr($pFormatCode,0,1) == '_') || ( substr($pFormatCode,0,2) == '0 ')) {
261. return false;
262. }
263. // Try checking for any of the date formatting characters that don't appear within square braces
264. if ( preg_match('/(^|\])[^\[]*['.self::$possibleDateFormatCharacters.']/i',$pFormatCode)) {
265. // We might also have a format mask containing quoted strings...
266. // we don't want to test for any of our characters within the quoted blocks
267. if (strpos($pFormatCode,'"') !== false) {
268. $i = false;
269. foreach( explode('"',$pFormatCode) as $subVal) {
270. // Only test in alternate array entries (the non-quoted blocks)
271. if (($i = !$i) && ( preg_match('/(^|\])[^\[]*['.self::$possibleDateFormatCharacters.']/i',$subVal))) {
272. return true;
273. }
274. }
275. return false;
276. }
277. return true;
278. }
280. // No date...
281. return false;
282. } // function isDateTimeFormatCode()
285. /**
286. * Convert a date/time string to Excel time
287. *
288. * @param string $dateValue Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
289. * @return float|false Excel date/time serial value
290. */
291. public static function stringToExcel($dateValue = '') {
292. if ( strlen($dateValue) < 2)
293. return false;
294. 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))
295. return false;
297. $dateValueNew = PHPExcel_Calculation_DateTime:: DATEVALUE($dateValue);
299. if ($dateValueNew === PHPExcel_Calculation_Functions:: VALUE()) {
300. return false;
301. } else {
302. if ( strpos($dateValue, ':') !== false) {
303. $timeValue = PHPExcel_Calculation_DateTime:: TIMEVALUE($dateValue);
304. if ($timeValue === PHPExcel_Calculation_Functions:: VALUE()) {
305. return false;
306. }
307. $dateValueNew += $timeValue;
308. }
309. return $dateValueNew;
310. }
313. }
315. }

Documentation generated on Sun, 27 Feb 2011 16:29:22 -0800 by phpDocumentor 1.4.3