0

I have a system that developed using Yii2 framework, and I've an Excel file that consist a date value imported to the system, for example:

ExcelFile

-------------------------------------------------
    Name   |   Birthdate   |    Place of Birth
-------------------------------------------------
   Archie  |  1995-09-18   |        Nevada
   Gerry   |  1989-01-23   |        Gotham

I'm using PHPExcel for reading the data to PHP, and then the data set to new array, for example

Array

Array
(
    [0] => Array
        (
           [0] => Archie 
           [1] => 42265
           [2] => Nevada
        )
    [1] => Array
        (
           [0] => Gerry
           [1] => 36217
           [2] => Gotham
        )
)

That is the array result after reading data from Excel file.

You can see the result above, the data in array [0][1] and [1][1] doesn't consist date value like the date data from Excel file.

Anyone have an idea how to solve this? or anyone knows why I got this?

Edtited

Code in Controller

This is the code for reading the Excel file

  $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();
        $arrayData = [];
        for ($row = 1; $row <= $highestRow; ++$row) {
            $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
            if (!is_null($rowData[0][0])) {
                $arrayData[] = array_map(function($values) {
                    $tempArrayKey = [];
                    foreach ($values as $key => $value) {
                        $newKey = $key + 1;
                        $tempArrayKey[] = $newKey . '_' . $value;
                    }
                    return $tempArrayKey;
                }, $rowData);

Any help would be appreciated. Thanks

Community
  • 1
  • 1
Blackjack
  • 1,016
  • 1
  • 20
  • 51

2 Answers2

0

you can get date field like this:
$sheet->setCellValueByColumnAndRow(0, 1, "2014-10-16");
Sets a string value in the cell, not a date. Just because you interpret that as a date, doesn't mean that computer programs automagically interpret it as a date.
Or
You can use this in your updated code:
$arr[$i] = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($birthdate));

Amit Sahu
  • 896
  • 6
  • 7
  • You need to include PHPExcel_Shared_Date using use in your controller if you are using PHPExcel library – Amit Sahu Dec 29 '16 at 10:15
  • Yah I'm sorry , I've been add that. But when I use `$birthdate`, it's return `undefined variable birthdate`. Then I try to use `'birthdate'`, and it's return the current date. – Blackjack Dec 29 '16 at 10:18
  • I'm sorry, but that's not what I need. I need the php read the date data from Excel and set into array also in date format. In the example above one of date is `1995-09-18`. Not the current date. – Blackjack Dec 29 '16 at 10:25
  • use this link for reference: http://stackoverflow.com/questions/31603451/phpexcel-get-formatted-date-as-is-visible-in-excel-file – Amit Sahu Dec 29 '16 at 10:30
0

You should get the cell value as follows... 

$excelDate = $cell->getValue(); // gives you a number like 44444, which is days since 1900
$stringDate = \PHPExcel_Style_NumberFormat::toFormattedString($excelDate, 'YYYY-MM-DD');

where $excelDate is the cell value(Your Date Cell Value) and 'YYYY-MM-DD' is the date format you need. \PHPExcel_Style_NumberFormat::toFormattedString is a built in PHPExcel function.

Nimantha Harshana Perera
  • 909
  • 7
  • 23