上传到mysql时,excel的日期发生变化 [英] Date from excel changes when uploaded into mysql
问题描述
我正在尝试上传包含日期和其他文字的列的excel电子表格。
I am trying to upload an excel spreadsheet with a column that contains dates and also other text.
日期格式为1/24/2012,但是我将其加载到mysql中,将其更改为某些十进制格式,例如40932 ????
the date is in the format 1/24/2012 but when i load it into mysql it changes it to some decimal format e.g. 40932 ????
如何才能上传它,没有改变? o
我可以做什么我真的坚持在这一个!!!!
how can i just upload it as is, without it changing? o What can i do i'm really stuck on this one!!!!
任何帮助将不胜感激。
推荐答案
Excel将日期值作为基准日期后的真实天数,可以是1900年1月1日(Windows版本的Excel的默认值) )或1904年1月1日(Mac版本的Excel的默认值):时间是小数部分,所以在任何给定日期的中午是0.5大于午夜。为了增加苦难,1900年2月29日是Windows 1900日历的有效日期。
Excel holds date values as the "real" number of days since a base date, which can be either 1st January 1900 (the default for Windows versions of Excel) or 1st January 1904 (the default for Mac versions of Excel): the time is the fractional part, so midday on any given date is 0.5 greater than midnight. To add to the misery, Feb29th 1900 is a valid date for the Windows 1900 calendar.
假设Windows 1900日历:
Assuming the Windows 1900 calendar:
function ExcelToPHP($dateValue = 0) {
$myExcelBaseDate = 25569;
// Adjust for the spurious 29-Feb-1900 (Day 60)
if ($dateValue < 60) {
--$myExcelBaseDate;
}
// Perform conversion
if ($dateValue >= 1) {
$utcDays = $dateValue - $myExcelBaseDate;
$returnValue = round($utcDays * 86400);
if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
$returnValue = (integer) $returnValue;
}
} else {
$hours = round($dateValue * 24);
$mins = round($dateValue * 1440) - round($hours * 60);
$secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
$returnValue = (integer) gmmktime($hours, $mins, $secs);
}
// Return
return $returnValue;
} // function ExcelToPHP()
如果Mac 1904基础,替换
if Mac 1904 base, replace
$myExcelBaseDate = 25569;
// Adjust for the spurious 29-Feb-1900 (Day 60)
if ($dateValue < 60) {
--$myExcelBaseDate;
}
与
$myExcelBaseDate = 24107;
这将返回PHP日期/时间值(标准1970基准日期),然后可以格式化您希望使用date();
This will return a PHP date/time value (standard 1970 base date), that you can then format as you wish using date();
这篇关于上传到mysql时,excel的日期发生变化的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!