How to convert date and time in PHPExcel to PHP timestamp

When using PHPExcel (a high-quality PHP Excel reading and writing library) to process Excel files, we usually need to convert the date or time data in the Excel table into PHP timestamp format for related business processing. So, how to convert date and time in PHPExcel to PHP timestamp?

First of all, we need to know that PHPExcel's time and date format is based on Excel's internal "January 1, 1900" and is calculated in days. That is, there is a time offset between the Excel date and the PHP timestamp. By consulting the official documentation of PHPExcel, we can get the following calculation formula:

$timestamp = ($excelTime - 25569) * 86400;

Among them, $timestamp represents the PHP timestamp, and $excelTime represents the time format in Excel. 25569 is a constant that represents the date offset of Excel, because the starting point of Excel's date is January 1, 1900, which is equivalent to 25569 days pushed forward from January 1, 1970 in the UNIX timestamp.

Next, we use PHPExcel to read the date data in the Excel table, and convert it into PHP timestamp format through the above formula. The code is as follows:

// 引入PHPExcel库
require_once 'PHPExcel.php';
require_once 'PHPExcel/IOFactory.php';

// 加载Excel文件
$excelFile = 'demo.xlsx';
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($excelFile);

// 获取活动sheet
$sheet = $objPHPExcel->getActiveSheet();

// 日期单元格所在的行和列
$row = 1;
$col = 'A';

// 读取日期单元格的值
$excelTime = $sheet->getCell($col.$row)->getValue();

// 计算PHP时间戳
$timestamp = ($excelTime - 25569) * 86400;

echo 'Excel时间戳为：' . $excelTime . '<br>';
echo 'PHP时间戳为：' . $timestamp;

Here is the first step to read Excel Taking the date cell in the first column of the row as an example, read the value of the cell, calculate the PHP timestamp through the above formula, and output it to the screen. We can encapsulate this code into a function so that it can be used multiple times in the program:

function excelTimeToTimestamp($excelTime)
{
    return ($excelTime - 25569) * 86400;
}

Note: If the time format you read from Excel is "date time format with hours, minutes and seconds", then You need to change 86400 to 86400 3600*8 because the Chinese standard time zone is 8 hours ahead of the Greenwich Mean Time zone.

Finally, we should be able to easily convert PHPExcel's date and time data into PHP timestamp format, and then perform related business processing.

The above is the detailed content of How to convert date and time in PHPExcel to PHP timestamp. For more information, please follow other related articles on the PHP Chinese website!