blocks|key|5539573|text|只有在使用chunk时才会发生这种情况。这个问题可以通过以下方式解决：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5539574|$UNIX_DATE+=+($row->DOB+-+25569)+*+86400;
$date_column+=+gmdate("d-m-Y+H:i:s",+$UNIX_DATE);|code-block|syntax|javascript|5539575|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This only happend when use chunk. This issue could be solved by:

<pre><code>$UNIX_DATE = ($row-&gt;DOB - 25569) * 86400;
$date_column = gmdate("d-m-Y H:i:s", $UNIX_DATE);
</code></pre>

blocks|key|418446|text|已知错误，请参阅https://github.com/Maatwebsite/Laravel-Excel/issues/404了解详细信息。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|418447|但基本上，当使用chunk()读取单元格时，它无法将Excel的日期时间格式从浮点型转换为碳日期对象。|418448|目前还没有修复，您可以在调用load之前调用config来解决此问题：|418449|config(['excel.import.dates.columns'+=>+[
++++++++'deleted_at',
++++++++'updated_at'
++++]]);

Excel::filter('chunk')->load($file)->chunk(100+function($rows)+{+...+});|code-block|syntax|javascript|418450|如果您没有使用分块过滤器，请参阅http://www.maatwebsite.nl/laravel-excel/docs/import#dates，了解如何显式设置单元格的格式(setDateColumns())，但这些格式应该会自动转换，除非您更改了默认值。|418451|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/Maatwebsite/Laravel-Excel/issues/404|1|http://www.maatwebsite.nl/laravel-excel/docs/import#dates^0|8|1J|0|0|0|0|0|G|1L|1|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|D|3|E|5|6|7|12|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|13|8|@]|9|@]|C|$]]|$1|H|3|I|5|J|7|14|8|@]|9|@]|C|$K|L]]|$1|M|3|N|5|6|7|15|8|@]|9|@$A|16|B|17|1|18]]|C|$]]|$1|O|3|-4|5|6|7|19|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]|W|$5|R|S|T|C|$U|X]]]]

Known bug, see <a href="https://github.com/Maatwebsite/Laravel-Excel/issues/404" rel="nofollow noreferrer">https://github.com/Maatwebsite/Laravel-Excel/issues/404</a> for details.
But basically, when using chunk() to read the cells in, it fails to convert Excel's datetime format from a float into a Carbon date object.
There is currently no fix, You can work around this by calling config before the call to load:
<pre><code>config(['excel.import.dates.columns' =&gt; [
 'deleted_at',
 'updated_at'
 ]]);

Excel::filter('chunk')-&gt;load($file)-&gt;chunk(100 function($rows) { ... });
</code></pre>
If you're not using the chunk filter, then see <a href="http://www.maatwebsite.nl/laravel-excel/docs/import#dates" rel="nofollow noreferrer">http://www.maatwebsite.nl/laravel-excel/docs/import#dates</a> on how to explicty set formats on cells (setDateColumns()), but those should be converting automatically unless you change the defaults.

blocks|key|3427674|text|将导入文件的格式更改为.csv，并将日期列的格式设置为所需的日期格式(dd-mm-yyyy)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3427675|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Change the format your import file to .csv and format the date column to your required date format (dd-mm-yyyy)

blocks|key|5539589|text|这个“浮点数”是一个excel时间戳，它在内部存储日期和时间数据。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5539590|例如：|5539591|123213.0:+it's+just+a+date
213233.1233:+is+a+date+and+time
0.1233:+it's+one+hour|code-block|syntax|javascript|5539592|要解决此问题，必须将浮点数转换为日期。|5539593|如果您的需要涉及动态解析日期时间字段，我已经编写了一个方法，该方法负责动态地自动检测值是否为日期时间(不管您是否知道该列中是否会有日期时间)，或者我已经尝试了各种数据类型，它工作得很好|5539594|+++/**
+*+@param+Cell+$cell
+*+@param+$value
+*+
+*+@return+boolean;
+*/
public+function+bindValue(Cell+$cell,+$value)
{
++++$formatedCellValue+=+$this->formatDateTimeCell($value,+$datetime_output_format+=+"d-m-Y+H:i:s",+$date_output_format+=+"d-m-Y",+$time_output_format+=+"H:i:s"+);
++++if($formatedCellValue+!=+false){
++++++++$cell->setValueExplicit($formatedCellValue,+DataType::TYPE_STRING);
++++++++return+true;
++++}

++++//+else+return+default+behavior
++++return+parent::bindValue($cell,+$value);
}


/**
+*+
+*+Convert+excel-timestamp+to+Php-timestamp+and+again+to+excel-timestamp+to+compare+both+compare
+*+By+Leonardo+J.+Jauregui+(+@Nanod10+%7C+siskit+dot+com+)
+*+
+*+@param+$value+(cell+value)
+*+@param+String+$datetime_output_format
+*+@param+String+$date_output_format
+*+@param+String+$time_output_format
+*+
+*+@return+$formatedCellValue
+*/
private+function+formatDateTimeCell(+$value,+$datetime_output_format+=+"Y-m-d+H:i:s",+$date_output_format+=+"Y-m-d",+$time_output_format+=+"H:i:s"+)
{

++++//+is+only+time+flag
++++$is_only_time+=+false;
++++
++++//+Divide+Excel-timestamp+to+know+if+is+Only+Date,+Only+Time+or+both+of+them
++++$excel_datetime_exploded+=+explode(".",+$value);

++++//+if+has+dot,+maybe+date+has+time+or+is+only+time
++++if(strstr($value,".")){
++++++++//+Excel-timestamp+to+Php-DateTimeObject
++++++++$dateTimeObject+=+\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value);
++++++++//+if+Excel-timestamp+>+0+then+has+Date+and+Time+
++++++++if(intval($excel_datetime_exploded[0])+>+0){
++++++++++++//+Date+and+Time
++++++++++++$output_format+=+$datetime_output_format;
++++++++++++$is_only_time+=+false;
++++++++}else{
++++++++++++//+Only+time
++++++++++++$output_format+=+$time_output_format;
++++++++++++$is_only_time+=+true;
++++++++}
++++}else{
++++++++//+Only+Date
++++++++//+Excel-timestamp+to+Php-DateTimeObject
++++++++$dateTimeObject+=+\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value);
++++++++$output_format+=+$date_output_format;
++++++++$is_only_time+=+false;
++++}
++++++++
++++//+Php-DateTimeObject+to+Php-timestamp
++++$phpTimestamp+=+$dateTimeObject->getTimestamp();

++++//+Php-timestamp+to+Excel-timestamp
++++$excelTimestamp+=+\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(+$phpTimestamp+);
++++++++
++++//+if+is+only+Time
++++if($is_only_time){
++++++++//+01-01-1970+=+25569
++++++++//+Substract+to+match+PhpToExcel+conversion
++++++++$excelTimestamp+=+$excelTimestamp+-+25569;
++++}

++++/*+
++++//+uncoment+to+debug+manualy+and+see+if+working
++++$debug_arr+=+[
++++++++++++"value"=>$value,
++++++++++++"value_float"=>floatval($value),
++++++++++++"dateTimeObject"=>$dateTimeObject,
++++++++++++"phpTimestamp"=>$phpTimestamp,
++++++++++++"excelTimestamp"=>$excelTimestamp,
++++++++++++"default_date_format"=>$dateTimeObject->format('Y-m-d+H:i:s'),
++++++++++++"custom_date_format"=>$dateTimeObject->format($output_format)
++++++++];
++++++++
++++if($cell->getColumn()=="Q"){
++++++++if($cell->getRow()=="2"){
++++++++++++if(floatval($value)===$excelTimestamp){
++++++++++++++++dd($debug_arr);
++++++++++++}
++++++++}
++++}

++++*/
++++
++++//+if+the+values+match
++++if(+floatval($value)+===+$excelTimestamp+){
++++++++//+is+a+fucking+date!+;)
++++++++$formatedCellValue+=+$dateTimeObject->format($output_format);
++++++++return+$formatedCellValue;
++++}else{
++++++++//+return+normal+value
++++++++return+false;
++++}
++++
}|5539595|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|V|8|@]|9|@]|A|$G|H]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

That &quot;floating number&quot; is an excel timestamp, that way it stores the date and time data internally.
for example:
<pre><code>123213.0: it's just a date
213233.1233: is a date and time
0.1233: it's one hour
</code></pre>
To solve this you must convert that floating point number to a date.
if your need involves resolving datetime fields dynamically, I have written a method that is responsible for automatically detecting if the value is a datetime dynamically (regardless of whether or not you know if there will be a datetime in that column) or I have tried various data types and it works fine
<pre><code> /**
 * @param Cell $cell
 * @param $value
 * 
 * @return boolean;
 */
public function bindValue(Cell $cell, $value)
{
 $formatedCellValue = $this-&gt;formatDateTimeCell($value, $datetime_output_format = &quot;d-m-Y H:i:s&quot;, $date_output_format = &quot;d-m-Y&quot;, $time_output_format = &quot;H:i:s&quot; );
 if($formatedCellValue != false){
 $cell-&gt;setValueExplicit($formatedCellValue, DataType::TYPE_STRING);
 return true;
 }

 // else return default behavior
 return parent::bindValue($cell, $value);
}


/**
 * 
 * Convert excel-timestamp to Php-timestamp and again to excel-timestamp to compare both compare
 * By Leonardo J. Jauregui ( @Nanod10 | siskit dot com )
 * 
 * @param $value (cell value)
 * @param String $datetime_output_format
 * @param String $date_output_format
 * @param String $time_output_format
 * 
 * @return $formatedCellValue
 */
private function formatDateTimeCell( $value, $datetime_output_format = &quot;Y-m-d H:i:s&quot;, $date_output_format = &quot;Y-m-d&quot;, $time_output_format = &quot;H:i:s&quot; )
{

 // is only time flag
 $is_only_time = false;
 
 // Divide Excel-timestamp to know if is Only Date, Only Time or both of them
 $excel_datetime_exploded = explode(&quot;.&quot;, $value);

 // if has dot, maybe date has time or is only time
 if(strstr($value,&quot;.&quot;)){
 // Excel-timestamp to Php-DateTimeObject
 $dateTimeObject = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value);
 // if Excel-timestamp &gt; 0 then has Date and Time 
 if(intval($excel_datetime_exploded[0]) &gt; 0){
 // Date and Time
 $output_format = $datetime_output_format;
 $is_only_time = false;
 }else{
 // Only time
 $output_format = $time_output_format;
 $is_only_time = true;
 }
 }else{
 // Only Date
 // Excel-timestamp to Php-DateTimeObject
 $dateTimeObject = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value);
 $output_format = $date_output_format;
 $is_only_time = false;
 }
 
 // Php-DateTimeObject to Php-timestamp
 $phpTimestamp = $dateTimeObject-&gt;getTimestamp();

 // Php-timestamp to Excel-timestamp
 $excelTimestamp = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel( $phpTimestamp );
 
 // if is only Time
 if($is_only_time){
 // 01-01-1970 = 25569
 // Substract to match PhpToExcel conversion
 $excelTimestamp = $excelTimestamp - 25569;
 }

 /* 
 // uncoment to debug manualy and see if working
 $debug_arr = [
 &quot;value&quot;=&gt;$value,
 &quot;value_float&quot;=&gt;floatval($value),
 &quot;dateTimeObject&quot;=&gt;$dateTimeObject,
 &quot;phpTimestamp&quot;=&gt;$phpTimestamp,
 &quot;excelTimestamp&quot;=&gt;$excelTimestamp,
 &quot;default_date_format&quot;=&gt;$dateTimeObject-&gt;format('Y-m-d H:i:s'),
 &quot;custom_date_format&quot;=&gt;$dateTimeObject-&gt;format($output_format)
 ];
 
 if($cell-&gt;getColumn()==&quot;Q&quot;){
 if($cell-&gt;getRow()==&quot;2&quot;){
 if(floatval($value)===$excelTimestamp){
 dd($debug_arr);
 }
 }
 }

 */
 
 // if the values match
 if( floatval($value) === $excelTimestamp ){
 // is a fucking date! ;)
 $formatedCellValue = $dateTimeObject-&gt;format($output_format);
 return $formatedCellValue;
 }else{
 // return normal value
 return false;
 }
 
}
</code></pre>

By using Maatwebsite/Laravel-Excel to import excel sheet, here I faced an issue date time column of the excel sheet returns float value. How to solve this?
Example : Consider Cell value "08-04-2016 13:08:29" and returns as "42104.487060185" when import.

Laravel excel import, date column in excel cell returns as floating value. How to solve this?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

通过使用Maatwebsite/Laravel-Excel导入excel表格，我在这里遇到了一个问题: excel表格的日期时间列返回浮点值。如何解决这个问题？示例:假设单元格值为“08-04-201613:08:29”，导入时返回为"42104.487060185“。

问Laravel excel导入，excel单元格中的日期列返回为浮点值。如何解决这个问题？
EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Laravel excel导入，excel单元格中的日期列返回为浮点值。如何解决这个问题？EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Laravel excel导入，excel单元格中的日期列返回为浮点值。如何解决这个问题？
EN