将完整的Excel日期序列格式转换为Unix时间戳

Question

将完整的Excel日期序列格式转换为Unix时间戳

phpimport-from-excel

18

我看到很多关于将Excel串行日期格式的“日期”部分转换的参考资料，但每个人似乎都跳过了其中的“时间”部分。

这是我需要做的事情：

我正在导入一个Excel文件。使用的是PHP。

我遇到了Excel日期串行格式（dddddd.tttttt），需要将其转换为完整的Unix时间戳。

我尝试了几种不同的方法，但卡在如何以连续的方式完成此操作上。

- timebinder

strtotime() 函数返回什么？ - arijeet

strtotime 不会帮助处理 Excel 序列值，该值是自 1900 年 1 月 1 日以来的天数（或根据使用的 Windows 1900 或 Mac 1904 日历而定可能是自 1904 年 1 月 1 日以来的天数）。 - Mark Baker

4个回答

14

这个单行命令在我使用PHPExcel时有效。

$date_formated = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($date_int_val));

- Elzo Valugi

8

PhpExcel 现已弃用，它的直接继承者 PhpSpreadsheet，允许我们使用以下方法：\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($date_int_val)。需要注意的是，在涉及翻译数字日期时，请仔细检查其格式。 - Guicara

如果您在Phpspreadsheet文档中搜索（使用其搜索栏），函数excelToDateTimeObject将不会显示。甚至'datetimeobject'也不会出现。您必须搜索“datetime”或类似的内容，然后浏览页面。参考链接为https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine/#php-datetime-objects。然后您可以搜索该页面。不确定为什么这样一个重要的函数那么难找到。如果不是@Guicara的评论，我就不会知道它的存在。 - McAuley

@McAuley的确，文档不是很好！我很高兴我的评论能帮到你。 - Guicara

7

你显然没有认真查看：

直接从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()

根据你使用的Excel基准日历（Windows 1900或Mac 1904），将self::$ExcelBaseDate设置为self::CALENDAR_WINDOWS_1900。如果你需要一个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()

- Mark Baker

谢谢Mark。请记住，我可能没有足够的经验，但大部分时间我能找到东西。在这种情况下，当我解析电子表格时，我是否需要调用函数并传递单元格值？ - timebinder

你实际上是如何读取Excel工作簿的？ - Mark Baker

打开PHPExcel，对每一行进行迭代，获取每个单元格的值。例如，对于日期值所在的A列，我基本上是这样做的：$cell = $sheet->getCell('A' . $rowIndex)，然后 $array_data[$rowIndex]['A'] = $cell->getValue()。然后，我只想将其设置为一个Unix时间戳格式的变量。 - timebinder

马克，谢谢。我从上面获取了所需内容，并创建了一个私有函数以在需要时使用。干杯。 - timebinder

如果您正在使用PHPExcel，则不必使用私有函数：我引用的代码已经内置于PHPExcel中 - PHPExcel_Shared_Date :: ExcelToPHP（）或PHPExcel_Shared_Date :: ExcelToPHPObject（） - 只需调用它们即可。 - Mark Baker

0

我在一个项目中遇到了同样的问题，我正在寻找一个能够将Excel读取为PHP数组的PHP类。我很幸运地找到了'SimpleXLSX'类。它可以很好地处理Excel数据，但是...但是...哦！！但是.... :( 我意识到从Excel中读取日期字段存在一些问题。在Excel中，值看起来很好，但当我们尝试导入它们时，日期值会变得不同。有时我们得到正确的值，有时只是一个数字，有时是浮点数值。我们一直在寻找解决方案。

为什么会发生这种情况？为什么PHP无法从Excel中获取正确的日期？

然后，在大量搜索之后，我们找到了原因：

@来源：在php中读取xls日期

根据Excel格式，41397是2013年5月3日。Excel将日期和时间存储为表示自1900年1月0日以来的天数的数字，加上24小时一天的小数部分：ddddd.tttttt。这被称为序列日期或序列日期时间。

@来源：Excel日期转换使用PHP Excel

将Excel日期转换为Unix日期，然后将Unix日期转换为PHP日期

因此，我制作了一个小助手类来读取Excel日期以供在PHP中使用。我希望它能帮助某些人并减少搜索和努力。

以下是我的代码，用于读取Excel作为PHP数组()和将Excel日期解析为PHP日期()

对于初学者：

从给定的示例代码中下载 SimpleXLSX.php
将 Excel 数据（字段/值）转换为 PHP 数组()
将 Excel 日期转换为 PHP
现在.. 是的!! Excel 数据已准备好作为 PHP 数组移动到 MySQL 表中...

以下是 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://dev59.com/l3TYa4cB1Zd3GeqPysnm
       */
      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://dev59.com/l3TYa4cB1Zd3GeqPysnm
     */
    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);

希望这段代码能帮助到某些人。我曾经也遇到过同样的问题，所以我写了这个小脚本来节省其他人的时间。

祝 PHP 编程愉快！！！:)

- Nono

用户正在寻求使用PHPExcel的解决方案。安装一个全新的库并不是一个解决方案。 - Isidro Moran

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ahmed Eissa · Accepted Answer

58

请使用以下公式将Excel日期转换为Unix日期，然后您可以使用“gmdate”在PHP中获取实际日期：

UNIX_DATE = (EXCEL_DATE - 25569) * 86400

要将 Unix 日期转换为 Excel 日期，请使用以下公式：

EXCEL_DATE = 25569 + (UNIX_DATE / 86400)

将此公式存储在变量中后，您可以使用以下示例在PHP中获取实际日期：

$UNIX_DATE = ($EXCEL_DATE - 25569) * 86400;
echo gmdate("d-m-Y H:i:s", $UNIX_DATE);

谢谢。

- Ahmed Eissa

2

运行良好

function ExcelDateToUnix($dateValue = 0) {
    return ($dateValue - 25569) * 86400;
}

- vonUbisch

再一次...很好的答案... ;) - deemi-D-nadeem

哇，我为此四处搜寻了很久。这是一个非常好的答案，终于让我显示出了正确的日期。（对我来说，这是在 PHP 的 Google Sheets API 中实现的）谢谢！ - smts

解释这些数字：默认情况下，Microsoft Excel for Windows 使用的是1900年日期系统，意味着第一个日期是1900年1月1日。默认情况下，Unix纪元时间使用的是1970年日期系统，意味着第一个日期是1970年1月1日。

25569 是1900年到1970年之间70年的天数。
86400 是一天（24小时）的秒数。

- ino

解释一下这些数字：默认情况下，Windows 上的 Microsoft Excel 使用的是 1900 年日期系统，这意味着第一个日期是 1900 年 1 月 1 日。默认情况下，Unix 纪元时间 使用的是 1970 年日期系统，这意味着第一个日期是 1970 年 1 月 1 日。

25569 是 1900 年到 1970 年之间 70 年的天数。
86400 是一天（24 小时）的秒数。

- undefined