PHPSpreadsheet:为时间格式化单元格还包括日期

Question

我正在尝试使用 PHPSpreadsheet 格式化时间单元格，但在查看公式栏时它似乎也包括日期。从字符串、日期时间对象或 unix 时间戳转换时，似乎也存在一些不一致。

<?php

include '../vendor/autoload.php';

use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$times = [
    '16:00:00',
    new \DateTime('16:00:00'),
    \strtotime('16:00:00'),
    '2020-04-04 16:00:00',
    new \DateTime('2020-02-04 16:00:00'),
    \strtotime('2020-02-04 16:00:00'),  
];

$format = \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_TIME1;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

foreach ($times as $i => $time) {
    $sheet->setCellValue('A' . ($i+1), \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($time));
    $sheet->getStyle('A' . ($i+1))->getNumberFormat()->setFormatCode($format);  
}

$writer = new Xlsx($spreadsheet);

$writer->save('test.xlsx');

来自 \PHPOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_TIME1 : const FORMAT_DATE_TIME1 = 'h:mm AM/PM';
这是一个错误还是预期的功能？考虑为 const FORMAT_DATE_DATETIME = 'd/m/yy h:mm';确实包含一些日期参数，我认为发生了一些错误。

这是发生的一些屏幕截图:



但是，如果我们在单元格中输入“5:00 AM”，则编辑栏不包含日期:



这是从右键单击>“格式化单元格”弹出的屏幕:



如果我做错了什么，有人可以告诉我，谢谢。

Answer 1

我想出了如何解决这个问题:您需要计算时间戳的 Excel 表示，然后只获取小数点后的数字。

<?php

$timestamp = new \DateTime('16:00:00');
$excelTimestamp = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($timestamp);
$excelDate = floor($excelTimestamp);
$time = $excelTimestamp - $excelDate;

从文档中:

In Excel, dates and Times are stored as numeric values counting the number of days elapsed since 1900-01-01. For example, the date '2008-12-31' is represented as 39813. You can verify this in Microsoft Office Excel by entering that date in a cell and afterwards changing the number format to 'General' so the true numeric value is revealed. Likewise, '3:15 AM' is represented as 0.135417.

我希望这可以帮助其他人解决这个问题。