| 2388 |
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 |
}
|