Laravel PHPExcel convert date into double

Question

I've tried to import an excel file with a column formated DATE by PHPExcel with Laravel 5.4. PHPExcel read the file and output date into double number. The date() function of PHP cannot convert into date for importing into database.

Is there anybody get the same issue?

Update: this is my code

Excel::filter('chunk')
                ->selectSheetsByIndex(1)
                ->load(config('excel.import.storage').$fileName)
                ->chunk(1000, function($rows) {
                    foreach ($rows as $i=>$row) {
                        dd($row->toArray());
                    }
            });

The date in excel column is 03/03/2017. The output is 42797.0 So, the date() function cannot work.

provided at least something you tried ? also provide an example of what you get from this column after you read it through PHPExcel. — Gal Sisso, Apr 08 '17 at 13:46
Possible duplicate of [Excel date conversion using PHP Excel](http://stackoverflow.com/questions/11119631/excel-date-conversion-using-php-excel) — Gal Sisso, Apr 08 '17 at 17:55

score 0 · Answer 1 · answered Apr 09 '17 at 10:07

Internally, MS Excel stores dates as a float/double, a serialized timetamp (similar to a Unix timestamp, but counting the number of days rather than seconds, and with a different base date). I'm not familiar enough with the Maatwebsite wrapper for laravel to explain why ->formatDates(true) isn't converting that timestamp to a formatted date/time, but PHPExcel does provide native functionality for converting between Excel timestamps and formatted date/times (or indeed to unix timesstamps or DateTime objects in the \PHPExcel_Shared_Date class.

Sorry! "->formatDates(true)" is a mistake I didn't delete before I posted my code. — Tommy Hoang, Apr 10 '17 at 03:20

score 0 · Answer 2 · answered Apr 10 '17 at 08:18

Thanks guys for your comments. I find myself the answer which is easier than I think. You consider the code below to import the Excel files have date columns.

Excel::selectSheetsByIndex(1)
                    ->load(config('excel.import.storage').$fileName, function ($reader){
                        $rows = $reader->all();
                        dd($rows[0]->ngay->format('Y-m-d'));
                    });

The result will be printed as your format string d-m-Y, m-d-Y, ...

Laravel PHPExcel convert date into double

2 Answers2