在 PHP EXCEL 中读取日期格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32624838/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Read Date Format in PHP EXCEL
提问by murrometz
I have read excel file using PHP excel .
我已经使用 PHP excel 读取了 excel 文件。
But that excel file contain some date (time format) PHP excel return wrong values for that case
但是那个 excel 文件包含一些日期（时间格式）PHP excel 在这种情况下返回错误的值
My code is in below
我的代码在下面
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 />";
}
any one can help for this.. Have some specific function for this case in PHPExcel..
任何人都可以为此提供帮助..在PHPExcel中为这种情况提供一些特定的功能..
My input excel file is in below
我的输入excel文件在下面
2013-12-12, 2013-12-13
2013-12-12, 2013-12-13
My output is in below
我的输出在下面
41621, 41631
41621、41631
Have some method to covert date format output data?
有一些方法可以转换日期格式输出数据吗？
回答by murrometz
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'?
当您使用 PHPExcel_Reader_Excel5 lib 读取 xls 文件时，文件中的数据为 39984，但 Excel 使用日期格式掩码将其格式化为“2009-06-20”？
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.
Excel 将日期保存为自 1900 年 1 月 1 日以来的天数计数（在 Windows 1900 日历上）。PHPExcel 以相同的方式存储其日期，但不会自动为您设置格式。
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
您可以使用PHPExcel_Style_NumberFormat::toFormattedString(39984,PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY)
或 中的任何其他格式掩码自己格式化它们PHPExcel_Style_NumberFormat
，或者使用将其转换为 PHP 日期PHPExcel_Shared_Date::ExcelToPHP(39984)，然后使用 PHP 的date()
函数根据需要对其进行格式化
Example:
例子：
$val = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($cell->getValue()));
回答by Ismael Fdez
An other way is using gmdate:
另一种方法是使用 gmdate：
$excel_date = 43010; //here is that value 41621 or 41631
$unix_date = ($excel_date - 25569) * 86400;
$excel_date = 25569 + ($unix_date / 86400);
$unix_date = ($excel_date - 25569) * 86400;
echo gmdate("Y-m-d", $unix_date);
//result is 2017-10-02
回答by Max Cuttins
To retrieve the date you have to convert from Excel Timestamp to Linux Timestamp. What you need to know is:
要检索日期，您必须将 Excel 时间戳转换为 Linux 时间戳。你需要知道的是：
- Excel express dates in dayswhile Linux do it in seconds.
- Excel start to count from year 1900 while Linux do it from year 1970.
- All timestamps are in GM (greenwitch meantime), so you should notconvert it with
gmdate();but instead use justdate();
- Excel 以天为单位表示日期，而 Linux 以秒为单位。
- Excel 从 1900 年开始计算，而 Linux 从 1970 年开始计算。
- 所有时间均在GM（greenwitch同时），所以你不应该用它转换
gmdate();，而是只使用date();
So, in order to convert you simply from Excel to Linux you have to:
因此，为了将您简单地从 Excel 转换为 Linux，您必须：
- remove 70 years of days:
25569 - multiply for seconds in a day:
86400
- 删除 70 年的天数：
25569 - 一天乘以几秒：
86400
This is the code:
这是代码：
function fromExcelToLinux($excel_time) {
return ($excel_time-25569)*86400;
}
$linux_time = fromExcelToLinux(30637);
echo date("Y-m-d",$linux_time);
//should print 1983-11-17
That's all the folks.
这就是所有的人。
回答by Madhan
If you are using excel_reader for excel import, You can use Code like this.
如果您使用 excel_reader 进行 excel 导入，您可以使用这样的代码。
include('excel_reader/xlsxreader/simplexlsx.class.php');
$xlsx_class = new SimpleXLSX();
$j['IST_Time'] = '43010';
echo date('Y-m-d',$xlsx_class->unixstamp($j['IST_Time'])); // Result will be 2017-10-02
And For Time
和时间
$j['IST_Time'] = '0.416666666667';
echo date('h:i A',$xlsx_class->unixstamp($j['IST_Time'])); //10:00 AM
or view simplexlsx.class.php
或查看 simplexlsx.class.php
