用PHP EXCEL读取日期格式 [英] Read Date Format in PHP EXCEL

问题描述

我已经使用PHP excel读取了excel文件.

I have read excel file using PHP excel .

但是该excel文件包含一些日期(时间格式)，PHP excel在这种情况下返回错误的值

But that excel file contain some date (time format) PHP excel return wrong values for that case

我的代码在下面

enter code hereinclude 'Classes/PHPExcel/IOFactory.php';

$inputFileName = 'index.xlsx';

//  Read your Excel workbook
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
} catch (Exception $e) {
    die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME)
        . '": ' . $e->getMessage());
}

//  Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();

//  Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++) {
    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
        NULL, TRUE, FALSE);

    foreach ($rowData[0] as $k => $v)
        echo "Row: " . $row . "- Col: " . ($k + 1) . " = " . $v . "<br />";
}

任何人都可以为此提供帮助. 在PHPExcel中针对这种情况具有一些特定的功能.

any one can help for this.. Have some specific function for this case in PHPExcel..

我的输入excel文件在下面

My input excel file is in below

2013-12-12， 2013-12-13

2013-12-12, 2013-12-13

我的输出在下面

41621， 41631

41621, 41631

有一些隐秘的日期格式输出数据的方法吗?

Have some method to covert date format output data?

推荐答案

当您使用PHPExcel_Reader_Excel5 lib读取xls文件时，文件中的数据为39984，但是Excel使用日期格式掩码将其格式化为"2009-06-20" ?

When you read xls file with PHPExcel_Reader_Excel5 lib, the data in file is 39984, but Excel formats it using a date formatting mask as '2009-06-20'?

Excel将日期保存为自1900年1月1日以来的天数(在Windows 1900日历上). PHPExcel以相同的方式存储日期，但不会自动为您设置日期格式.

Excel holds dates as a count of the number of days since 1st January 1900 (on Windows 1900 calendar). PHPExcel stores its dates in the same way, but doesn't automatically format them for you.

您可以使用PHPExcel_Style_NumberFormat::toFormattedString(39984,PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY)
或
PHPExcel_Style_NumberFormat
中的任何其他格式掩码自行设置其格式，或使用
，然后使用PHP的date()
函数按需设置其格式

You can format them yourself using PHPExcel_Style_NumberFormat::toFormattedString(39984,PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY)
or any of the other format masks in
PHPExcel_Style_NumberFormat
, or convert it to a PHP date using
PHPExcel_Shared_Date::ExcelToPHP(39984) and then use PHP's date()
function to format it as you wish

示例:

$val = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($cell->getValue()));

这篇关于用PHP EXCEL读取日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！