php 将 FULL Excel 日期序列格式转换为 Unix 时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11172644/
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
Convert the FULL Excel date serial format to Unix timestamp
提问by timebinder
I've seen lots of references to converting the "date" portion of the Excel serial date format, but everyone seems to skip the "time" portion of it.
我已经看到很多关于转换 Excel 串行日期格式的“日期”部分的参考资料，但每个人似乎都跳过了它的“时间”部分。
Here is what I need to do:
这是我需要做的：
I have an Excel file I'm importing. PHP in use.
我有一个要导入的 Excel 文件。正在使用的 PHP。
I am encountering the Excel Date Serial format (dddddd.tttttt) and need to convert this to an entire Unix timestamp.
我遇到了 Excel 日期序列格式 (dddddd.tttttt)，需要将其转换为整个 Unix 时间戳。
I've tried a few different things, but am getting hung up on how to do this in a fluid motion.
我已经尝试了一些不同的方法，但是我对如何以流畅的动作做到这一点感到困惑。
回答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 doing 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);
Thanks.
谢谢。
回答by Elzo Valugi
This oneliner worked for me, using PHPExcel of course.
这个 oneliner 对我有用，当然使用 PHPExcel。
$date_formated = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($date_int_val));
回答by Mark Baker
You clearly haven't looked very hard:
你显然没有看起来很努力：
Taken directly from the PHPExcel Date handling code:
直接取自 PHPExcel 日期处理代码：
public static function ExcelToPHP($dateValue = 0) {
if (self::$ExcelBaseDate == self::CALENDAR_WINDOWS_1900) {
$myExcelBaseDate = 25569;
// Adjust for the spurious 29-Feb-1900 (Day 60)
if ($dateValue < 60) {
--$myExcelBaseDate;
}
} else {
$myExcelBaseDate = 24107;
}
// 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()
Set self::$ExcelBaseDate == self::CALENDAR_WINDOWS_1900 as necessary to indicate the Excel base calendar that you're using: Windows 1900 or Mac 1904
根据需要设置 self::$ExcelBaseDate == self::CALENDAR_WINDOWS_1900 以指示您正在使用的 Excel 基准日历：Windows 1900 或 Mac 1904
and if you want a PHP DateTime object instead:
如果你想要一个 PHP DateTime 对象：
public static function ExcelToPHPObject($dateValue = 0) {
$dateTime = self::ExcelToPHP($dateValue);
$days = floor($dateTime / 86400);
$time = round((($dateTime / 86400) - $days) * 86400);
$hours = round($time / 3600);
$minutes = round($time / 60) - ($hours * 60);
$seconds = round($time) - ($hours * 3600) - ($minutes * 60);
$dateObj = date_create('1-Jan-1970+'.$days.' days');
$dateObj->setTime($hours,$minutes,$seconds);
return $dateObj;
} // function ExcelToPHPObject()
回答by Neeraj Singh
I had same issue on a project, I was looking for a PHP Class to Read Excel as PHP Array.
I was lucky that I found 'SimpleXLSX' Class. It can handle excel data very well, but.. but... ohh!! but.... :(
I have realised that there is some issue with Date Reading From Excelfield. In excel values was looking fine but when we were
trying to import them, The Date Values Getting Different. Some time we were getting correct values some times just a number and some times a float values.
We were looking for solution '
我在一个项目上遇到了同样的问题，我正在寻找一个PHP Class to Read Excel as PHP Array. 我很幸运我找到了'SimpleXLSX' Class。它可以很好地处理excel数据，但是……但是……哦！！但是.... :( 我意识到Date Reading From Excel字段存在一些问题。在 excel 中，值看起来不错，但是当我们尝试导入它们时，日期值变得不同。有时我们会得到正确的值，有时只是数字，有时是浮点值。我们正在寻找解决方案“
WHY IT IS HAPPENING, WHY PHP NOT GETTING CORRECT DATE FROM EXCEL
为什么会发生这种情况，为什么 PHP 没有从 Excel 中获取正确的日期
' Then after the lots of goggling we found the reason:
'然后经过大量的护目镜我们找到了原因：
@Source: reading xls date in php
@Source：在 php 中读取 xls 日期
According to excel format 41397 is 2013-05-03 Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt. This is called a serial date, or serial date-time.
根据 excel 格式 41397 是 2013-05-03 Excel 将日期和时间存储为一个数字，表示自 1900-Jan-0 以来的天数，加上 24 小时制的小数部分：ddddd.tttttt。这称为序列日期或序列日期时间。
@Source: Excel date conversion using PHP Excel
@Source：使用 PHP Excel 进行 Excel 日期转换
Convert Excel Date To Unix Date and Then Convert Unix Date to PHP Date
将 Excel 日期转换为 Unix 日期，然后将 Unix 日期转换为 PHP 日期
So, I made a little helper class to read Excel Date for use in PHP. I hope it will help someone and will reduce goggling and efforts.
所以，我做了一个小助手类来读取 Excel 日期以在 PHP 中使用。我希望它会帮助某人，并会减少目击和努力。
Here is my code to Read Excel as PHP Array()and Parse Excel Date as PHP Date
这是我的代码Read Excel as PHP Array()和Parse Excel Date as PHP Date
For Beginners:
对新手而言：
- Download
SimpleXLSX.phpfrom given example code - Convert excel data (field/value) in PHP array()
- Convert excel date into PHP
- Now.. Yeah!! Excel Data is Ready as an PHP Array to moving into MySQL table...
SimpleXLSX.php从给定的示例代码下载- 转换PHP数组中的excel数据（字段/值）（）
- 将excel日期转换为PHP
- 现在..是啊！！Excel 数据已准备好作为 PHP 数组移入 MySQL 表...
Here is PHP Code:
这是PHP代码：
<?php
/*
EXCEL DATA EXAMPLE:
----------------------------
ID Consumer_Date Delivery_Date Date_of_Dispatch Gift_Date_Created Active_Date Approved_Date
536 -No-Data- 9-Nov-15 7-Nov-15 -No-Data- 10/31/2015 12:00:00 AM 4/11/2015 10:21
537 -No-Data- -No-Data- 7-Nov-15 -No-Data- 10/23/2015 12:00:00 AM 3/11/2015 16:24
*/
/*
EXCEL DATA IN PHP ARRAY FORMAT
-------------------------------
Array
(
[0] => Array
(
[ID] => 536
[Consumer_Date] => -No-Data-
[Delivery_Date] => 42317
[Date_of_Dispatch] => 42315
[Gift_Date_Created] => -No-Data-
[Active_Date] => 10/31/2015 12:00:00 AM
[Approved_Date] => 42105.431574074
)
[1] => Array
(
[ID] => 537
[Consumer_Date] => -No-Data-
[Delivery_Date] => -No-Data-
[Date_of_Dispatch] => 42315
[Gift_Date_Created] => -No-Data-
[Active_Date] => 10/23/2015 12:00:00 AM
[Approved_Date] => 42074.683958333
)
)
*/
/* ----------------- */
/* Excel_Date_Parser.php */
/* ----------------- */
// Numbers of days between January 1, 1900 and 1970 (including 19 leap years)
define("MIN_DATES_DIFF", 25569);
// Numbers of second in a day:
define("SEC_IN_DAY", 86400);
/** Set default timezone (will throw a notice otherwise) */
date_default_timezone_set('Asia/Kolkata');
/**
* Class Excel_Date_Parser
*
* SetDateString($excel_date_value) : send excel date column value
* GetDateString(): get your php date in Y-m-d format (MySQL)
*/
class Excel_Date_Parser
{
/**
* Send Excel Date String Value Here
* @param [type] $date_from_excel [description]
* @return instance Excel_Date_Parser
*/
public function SetDateString($date_from_excel) {
$this->date_from_excel = $date_from_excel;
return $this;
}
/**
* Set Date Format Here, default is "Y-m-d"
* @param string $set_format_date [description]
*/
public function SetDateFormat($set_format_date = "Y-m-d") {
$this->set_format_date = $set_format_date;
}
/**
* Get what format is set
*/
public function GetDateFormat() {
return $this->set_format_date;
}
/**
* Return PHP date according to Set Format from Excel Date
* @return string php date
*/
public function GetDateString() {
// if value is valid date string
if (strtotime($this->date_from_excel)) {
/**
* Excel stores dates and times as a number representing the number of days since 1900-Jan-0,
* plus a fractional portion of a 24 hour day: ddddd.tttttt.
* This is called a serial date, or serial date-time.
*
* @source: https://stackoverflow.com/questions/17808750/reading-xls-date-in-php
*/
if (is_float($this->date_from_excel)) {
// date format 2015-25-12
$this->SetDateFormat("Y-d-m");
$this->date_from_excel = date($this->GetDateFormat() , (mktime(0, 0, -1, 1, $this->date_from_excel, 1900)));
}
else {
// date format 2015-12-25
$this->SetDateFormat();
// return converted date string in php format date format 2015-12-25
$this->date_from_excel = date($this->GetDateFormat() , strtotime($this->date_from_excel));
}
}
/**
* Excel stores dates and times as a number representing the number of days since 1900-Jan-0,
* plus a fractional portion of a 24 hour day: ddddd.tttttt .
* This is called a serial date, or serial date-time.
*
* According to excel format 41397 is 2013-05-03
* @source: https://stackoverflow.com/questions/17808750/reading-xls-date-in-php
*/
else if (is_integer($this->date_from_excel)) {
$this->SetDateFormat();
$this->date_from_excel = gmdate($this->GetDateFormat() , (($this->date_from_excel - MIN_DATES_DIFF) * SEC_IN_DAY));
}
// return real value
else {
$this->date_from_excel = $this->date_from_excel;
}
// return date
return $this->date_from_excel;
}
}
/* ----------------- */
/* Excel_Reader.php */
/* ----------------- */
/* Show errors */
error_reporting(1);
/* display error */
ini_set('display_errors', 1);
/**
* Using class SimpleXLSX
*
* Big Thanks!!!! to Sergey Shuchkin, Who made Excel Reader Class
*
* This class can be used to parse and retrieve data from Excel XLS spreadsheet files.
* It can parse a given Excel XLS file by extracting its contents files and parsing the
* contained XML spreadsheet file.
*
* The class provides functions to retrieve data for the spreadsheet worksheets, rows and cells.
*
* @link: http://www.phpclasses.org/package/6279-PHP-Parse-and-retrieve-data-from-Excel-XLS-files.html
* @author: Sergey Shuchkin
* @download: http://www.phpclasses.org/browse/download/zip/package/6279/name/simple-xlsx-2013-10-13.zip
*/
require_once 'SimpleXLSX.php';
/* Adding my class Excel_Date_Parser */
require_once 'Excel_Date_Parser.php';
/**
* [toPhpDate description]
* @param [type] $array [description]
*/
function toPhpDate($array) {
// create class object
$ed = new Excel_Date_Parser();
// parse array and set
$array['Consumer_Date'] = $ed->SetDateString($array['Consumer_Date'])->GetDateString();
$array['Delivery_Date'] = $ed->SetDateString($array['Delivery_Date'])->GetDateString();
$array['Date_of_Dispatch'] = $ed->SetDateString($array['Date_of_Dispatch'])->GetDateString();
$array['Gift_Date_Created'] = $ed->SetDateString($array['Gift_Date_Created'])->GetDateString();
$array['Active_Date'] = $ed->SetDateString($array['Active_Date'])->GetDateString();
$array['Approved_Date'] = $ed->SetDateString($array['Approved_Date'])->GetDateString();
// return php array
return $array;
}
// make xls object
$xlsx = new SimpleXLSX('Sony_RedemptionFormat 8-Dec-15.xlsx');
// get excel data as array
$Excel_Array_Data = $xlsx->rows();
// Get Column Name
$Excel_Column = $Excel_Array_Data[0];
// Remove Column Name From Array
unset($Excel_Array_Data[0]);
// Rest Data is Excel Data without Column
$Excel_Data = $Excel_Array_Data;
// Combine array for inserting in database
foreach ($Excel_Array_Data as $key => $Excel_Data) {
$insert_data[] = array_combine($Excel_Column, $Excel_Data);
}
// show array data
echo "<pre>", print_r($insert_data, true);
// update array excel date
$insert_data = array_map('toPhpDate', $insert_data);
// show array data after update date
echo "<pre>", print_r($insert_data, true);
Hope this code will help someone. I was struggling same problem So I made this little script to save others time.
希望此代码对某人有所帮助。我也在为同样的问题苦苦挣扎所以我制作了这个小脚本来节省其他人的时间。
Happy PHPING!!!! :)
PHPING 快乐！！！:)
