使用 PHP Excel 转换 Excel 日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11119631/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel date conversion using PHP Excel
提问by Abdul basit
i am reading date from excel which is in this format 12/5/2012 day/month/year using this code to read . using PHP EXCEL
我正在从 excel 中读取日期，格式为 12/5/2012 日/月/年，使用此代码读取。使用 PHP EXCEL
PHPExcel_Style_NumberFormat::toFormattedString($value['A'],'YYYY-MM-DD' );
its working like charm converting the above date '12/5/2012' to '2012-12-05'
它的工作就像将上述日期“12/5/2012”转换为“2012-12-05”的魅力
now the problem is if the date is lets says 18/5/2012 or you can say if i set day greater than 12 it gives me this date 18/5/2012 in this format 18/5/2012 after formating
现在的问题是，如果日期是 2012 年 5 月 18 日，或者你可以说如果我设置的日期大于 12 天，它会以这种格式给我这个日期 18/5/2012 格式化后的 18/5/2012
i tried this thing as well
我也试过这个东西
$temp = strtotime( PHPExcel_Style_NumberFormat::toFormattedString($value['A'],'YYYY-MM-DD' );
$actualdate = date('Y-m-d',$temp) ;
THis is also converting the date '12/5/2012' correct but in this case 18/5/2012 it gives output as 1970-01-01
这也将日期 '12/5/2012' 转换为正确但在这种情况下 18/5/2012 它给出的输出为 1970-01-01
回答by Ahmed Eissa
Please use this formula to change from Excel date to Unix date, then you can use "gmdate" to get the real date in PHP:
请使用此公式从 Excel 日期更改为 Unix 日期，然后您可以使用“gmdate”在 PHP 中获取实际日期：
UNIX_DATE = (EXCEL_DATE - 25569) * 86400
and to convert from Unix date to Excel date, use this formula:
要将 Unix 日期转换为 Excel 日期，请使用以下公式：
EXCEL_DATE = 25569 + (UNIX_DATE / 86400)
After putting this formula into a variable, you can get the real date in PHP using this example:
将此公式放入变量后，您可以使用以下示例在 PHP 中获取实际日期：
$UNIX_DATE = ($EXCEL_DATE - 25569) * 86400;
echo gmdate("d-m-Y H:i:s", $UNIX_DATE);
回答by caponica
When using PHPExcel you can use the built in function:
使用 PHPExcel 时，您可以使用内置函数：
$excelDate = $cell->getValue(); // gives you a number like 44444, which is days since 1900
$stringDate = \PHPExcel_Style_NumberFormat::toFormattedString($excelDate, 'YYYY-MM-DD');
回答by izn
An easy way...
一个简单的方法...
<?php
$date = date_create('30-12-1899');
date_add($date, date_interval_create_from_date_string("{$value['A']} days"));
echo date_format($date, 'Y-m-d');
回答by SeanC
It appears your variable is a string, or is expecting a US format date.
use 'DateTime::createFromFormat' to cast the date into an actual date format
看来您的变量是一个字符串，或者期待美国格式的日期。
使用 'DateTime::createFromFormat' 将日期转换为实际日期格式
$date = DateTime::createFromFormat('d/m/y', $value['A']);
echo $date->format('Y-m-d');
回答by Titi
If you're using python I resolved this issue by add xldate class from xlrd lib, here I show you the code (This is in Odoo 10 module):
如果您使用的是 python，我通过从 xlrd lib 添加 xldate 类解决了这个问题，在这里我向您展示代码（这是在 Odoo 10 模块中）：
from xlrd import open_workbook, xldate
wb = open_workbook(file_contents=excel_file)
data_sheets = []
# Parse all data from Excel file
for s in wb.sheets():
data_rows = []
headers = []
for row_key, row in enumerate(range(s.nrows)):
if row_key != 0:
data_row = {}
for index, col in enumerate(range(s.ncols)):
value = s.cell(row, col).value
key = headers[int(index)]
if key == 'Date' and (isinstance(value, float) or isinstance(value, int)):
value = xldate.xldate_as_datetime(value, wb.datemode)
data_row[key] = value
else:
data_row[key] = value
data_rows.append(data_row)
else:
for index, col in enumerate(range(s.ncols)):
value = (s.cell(row, col).value)
headers.append(value)
data_sheets.append(data_rows)
value = xldate.xldate_as_datetime(value, wb.datemode)will return datetime object with correct values
value = xldate.xldate_as_datetime(value, wb.datemode)将返回具有正确值的日期时间对象
