PHPExcel日期字段与源电子表格不匹配 [英] PHPExcel date field does not match source spreadsheet

问题描述

这是我的电子表格中的内容:

12/04/2011  8:56:17 p.m. (xls dateserial = 40645.87242)

这是我用来提取日期并将其转换为PHP中日期字符串的代码:

$txn_date_xls = $sheet->getCell("H$row")->getValue();               
echo "Txn Date (original): $txn_date_xls<br />";

$txn_date = PHPExcel_Shared_Date::ExcelToPHP($txn_date_xls);
echo "Txn Date: ".date('Y-m-d H:i:s', $txn_date)."<br />";

这是我脚本的输出:

Txn Date (original): 40644.55783637732
Txn Date: 2011-04-11 13:23:17

日期序列与原始电子表格不匹配. 因此，我试图确定这是否是PHPExcel的错误，还是我错过了设置或某些事情，而这是我需要从该单元格中提取日期信息之前要做的事情.可能是时区问题?

对不起，我对PHPExcel库不是很熟悉.

解决方案

对此我不太确定，但是...

PHP中的功能 date()取决于服务器的时区设置. /p>

PHPExcel_Shared_Date :: ExcelToPHP()假定提供的日期为格林尼治标准时间，并相应地返回Unix时间.

因此，请尝试在脚本开头为PHP时区设置GMT/UTC(使用 date_default_timezone_set())

date_default_timezone_set('UTC');

或类似的方法(使用 ini_set())

ini_set('date.timezone', 'UTC');

此外，请尝试使用 gmdate()而不是 http://phpexcel.codeplex.com/

This is what is in my spreadsheet:

12/04/2011  8:56:17 p.m. (xls dateserial = 40645.87242)

this is my code I'm using to extract the date and convert to date string in PHP:

$txn_date_xls = $sheet->getCell("H$row")->getValue();               
echo "Txn Date (original): $txn_date_xls<br />";

$txn_date = PHPExcel_Shared_Date::ExcelToPHP($txn_date_xls);
echo "Txn Date: ".date('Y-m-d H:i:s', $txn_date)."<br />";

This is the output from my script:

Txn Date (original): 40644.55783637732
Txn Date: 2011-04-11 13:23:17

The date serial doesn't match the original spreadsheet. So, I'm trying to determine if this is a bug with PHPExcel or I have missed a setting or something, that I need to do before extracting the dateserial from the cell. Possibly a timezone issue?

Sorry, I'm not very familiar with the PHPExcel library yet.

解决方案

I'm not so sure about this but...

Function date() in PHP depends of server's time zone setting.

PHPExcel_Shared_Date::ExcelToPHP() assumes that the date supplied is GMT and returns the Unix time accordingly.

So try to set GMT/UTC for PHP timezone at the beginning of script (using date_default_timezone_set())

date_default_timezone_set('UTC');

or like this (using ini_set())

ini_set('date.timezone', 'UTC');

Also, try using gmdate() instead of date(), otherwise you may get a 1 day offset.

Usable link:

• http://phpexcel.codeplex.com/

这篇关于PHPExcel日期字段与源电子表格不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！