萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 將 Excel 序列數值轉換為日期格式的正確姿勢

問題背景

匯入 Excel 報表時,日期欄位常以序列數值(如 40847)呈現,而非格式化字串。直接用 date() 處理會得到錯誤結果:

<?php
echo date('Y-m-d', 40847); // 1970-01-01 11:52:30 ❌
?>

根本原因:兩套時間基準的差異

體系起始基準日單位備註
Unix / PHP1970-01-01 00:00:00 UTC秒標準時間戳
Excel (Windows 1900 日期系統)1900-01-01天含 1900/02/29 錯誤閏日
Excel (Mac 1904 日期系統)1904-01-01天較少見

Excel 將 1900-01-01 視為第 1 天,而 PHP 以 1970-01-01 為第 0 秒。兩者相差 25,569 天(含 1900 年錯誤閏日)。

標準轉換公式

<?php
/**
 * Excel 序列數值 → Unix 時間戳(秒)
 *
 * @param int|float $excelSerial Excel 日期序列數
 * @param bool $is1904System 是否為 Mac 1904 系統(預設 false)
 * @return int Unix timestamp
 */
function excelSerialToTimestamp($excelSerial, $is1904System = false) {
    $baseDays = $is1904System ? 24107 : 25569; // 1904 系統基準不同
    $secondsPerDay = 86400;

    // 去除小數部分(時間部分另行處理)
    $days = floor($excelSerial);

    return ($days - $baseDays) * $secondsPerDay;
}

// 示範
$excelDate = 40847; // 2011-10-31
$timestamp = excelSerialToTimestamp($excelDate);
echo date('Y-m-d', $timestamp); // 2011-10-31 ✅
?>

進階:保留時間精度(含小數部分)

Excel 序列數值的小數部份代表當天時間比例(0.5 = 12:00)。

<?php
function excelSerialToDateTime($excelSerial, $timezone = 'UTC', $is1904System = false) {
    $baseDays = $is1904System ? 24107 : 25569;
    $secondsPerDay = 86400;

    // 完整天數與秒數
    $totalSeconds = ($excelSerial - $baseDays) * $secondsPerDay;

    // 修正 1900 年錯誤閏日:Excel 以 1900/02/29 為第 60 天,實際不存在
    if (!$is1904System && $excelSerial > 60) {
        $totalSeconds -= $secondsPerDay; // 扣除一天
    }

    $dt = new DateTime('@' . (int)$totalSeconds);
    $dt->setTimezone(new DateTimeZone($timezone));

    // 處理小數秒(毫秒級)
    $fraction = $excelSerial - floor($excelSerial);
    $microseconds = (int)round($fraction * $secondsPerDay * 1_000_000);
    $dt->setTime(
        (int)$dt->format('H'),
        (int)$dt->format('i'),
        (int)$dt->format('s'),
        $microseconds
    );

    return $dt;
}

// 40847.5 = 2011-10-31 12:00:00
$dt = excelSerialToDateTime(40847.5, 'Asia/Taipei');
echo $dt->format('Y-m-d H:i:s.u'); // 2011-10-31 12:00:00.000000
?>

實務建議:使用成熟套件

手寫轉換易遺漏邊界條件(1900 閏日、1904 系統、時區、DST)。推薦直接使用:

  • PhpSpreadsheet:\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($serial)
  • Box/Spout:內建 Cell::getDateTimeValue()
<?php
require 'vendor/autoload.php';

use PhpSpreadsheet\Shared\Date;

$serial = 40847.5;
$dt = Date::excelToDateTimeObject($serial); // 自動處理 1900/1904、時區
$dt->setTimezone(new DateTimeZone('Asia/Taipei'));
echo $dt->format('Y-m-d H:i:s'); // 2011-10-31 12:00:00
?>

常見坑點速查

  1. 1900/02/29 不存在:Excel 故意保留此 Bug 以相容 Lotus 1-2-3,序列 60 對應此虛構日期,實際轉換需扣一天。
  2. 負數序列:1900 系統不支援 1900 年前日期;1904 系統可支援至 1904 年前。
  3. 時區陷阱:date() 受 date_default_timezone_set() 影響,建議統一用 DateTime + DateTimeZone。
  4. 浮點精度:大數值運算可能產生微小誤差,建議用 round() 或 bcmath 處理。

結語

Excel 日期序列數轉換的核心在於基準日差值(25569 天)與1900 年閏日修正。掌握原理後,建議在專案中封裝工具函式或直接引用 PhpSpreadsheet,避免重複造輪子導致邊界錯誤。

copyright © 萬盛學電腦網 all rights reserved