phpexcel日期读取的格式错误 [英] phpexcel date is reading wrong format
问题描述
我知道有很多与此类似的问题，但实际上没有一个有帮助.
I know there are a lot of questions similar to this but none of them actually has helped.
我的excel文件中有一个必须读取的单元格.
I'm having a cell in my excel file that I have to read.
，excel文件中的格式如下
and the format in the excel file is like below
在我的php代码中，我将其包含在一个函数中，该函数获取工作表名称和单元格以读取单元格.
In my php code I have it in a function that gets the sheet name and cell to read the cell .
$cellDate = $xls->getSheetByName($sheet)->getCell($cell)->getValue();
我也尝试过使用getFormattedValue()并获得相同的结果
然后我得到这个格式
$format = $xls->getSheetByName($sheet)->getCell($cell)->getStyle()->getNumberFormat()->getFormatCode();
当我在PHP中将其回显时，日期显示为
when I echo this out in PHP it shows the date as
13/4/2015
并显示
mm-dd-yyyy
显示为dd-mm-yyyy
where it show as dd-mm-yyyy
我现在需要获取这些格式的Y-m-d格式，这些格式我已经尝试使用PHPExcel中的所有可用函数，但结果仍然不正确.
I now need to get Y-m-d format of these I've tried using all the available functions in PHPExcel but it's still the result is not correct.
我无法对格式进行硬编码，因为存在多个具有多种格式的单元格，我需要自己来理解
I can't hard code the format because there are multiple cells with multiple formats and I need this to understand by itself
我尝试使用
$objPHPExcel->getActiveSheet()
->getStyle('A1')
->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2 );
我真的很需要这个，我已经浪费了一个星期.
I really need this and I've already wasted 1 week on it.
是否有必要告诉PHPEXCEL将单元格中的任何日期解析为Y-m-d格式?
Is there anyway to tell PHPEXCEL to parse whatever date is in the cell to Y-m-d format ?
更新
当我阅读注释时，我回过头来检查了我编写的函数，发现其中一个单元格(甚至)是在Excel中格式化的，它解析了错误的格式，在这种情况下，这是通用格式；
As I was reading through the comments I went back and checked the function I wrote, I found that one of the cells [even] that it's formatted in Excel ,it's parsing a wrong format which in that case was a General Format;
然后我得到了getValue()和getFormattedValue()的值，并比较了这两个值(如果它们不相同)，这意味着它们的格式正确，并且我可以使用PHPExcel从中获取日期.
I then got the value of getValue() and getFormattedValue() and compared these two if they are not the same that means it's formatted correctly and i can use PHPExcel to get dates from it.
但是，(总是有一个but).多亏了Microsoft及其出色的专业知识，我仍然无法与2016年2月1日和1月2日有所不同！永远存在的问题.
BUT , (There is always a but). Thanks to Microsoft and its excel I'm still not able to differ 1st Feb 2016 and 2nd Of January ! which forever remains the question.
我唯一的建议是永远不要相信excel格式！
My only recommendation is to never trust excel formats !
推荐答案
如果getValue()和getFormattedValue()都返回包含13/4/2015的字符串，则您的单元格内容不是MS Excel日期/时间值，而是标准字符串，并且尝试将日期格式掩码应用于字符串将不会有任何效果. PHPExcel的日期格式功能仅在单元格内容确实是MS Excel序列化的日期时间值而不是字符串的情况下才有效.
If getValue() and getFormattedValue() both return a string containing 13/4/2015 then your cell content isn't a MS Excel date/time value but a standard string, and trying to apply a date format mask to a string won't achieve anything. PHPExcel's date formatting functions will only work if the cell content really is an MS Excel serialized datetime value, not simply string.
您将必须使用标准的PHP函数，如DateTime :: createFromFormat()将字符串值转换为datetime对象，然后可以使用format()方法将其转换为所需的任何格式.
You'll have to use standard PHP functions like DateTime::createFromFormat() to convert the string value to a datetime object, and can then use the format() method to convert it to whatever format you want.
或者，尝试将值传递给PHPExcel_Shared_Date :: PHPToExcel()，以将其转换为MS Excel序列化的时间戳；并将其设置为单元格值，然后可以设置格式掩码并使用getFormattedValue().
Alternatively, try passing the value to the PHPExcel_Shared_Date::PHPToExcel() to convert it to an MS Excel serialized timestamp; and setting that as the cell value, and then you can set a format mask and use getFormattedValue().
这篇关于phpexcel日期读取的格式错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
