CODEWITHSUNDEEP
phpphpspreadsheet
Finesse
Finesse

Reputation: 10801

How to retrieve date from table cell using PhpSpreadsheet?

I have xlsx tables and I use PhpSpreadsheet to parse them. Some cells are formatted as date. The problem is that PhpSpreadsheet returns the values from date-formatted cells in an unspecified format:

// What it looks in excel: 2017.04.08 0:00
$value = $worksheet->getCell('A1')->getValue();  // 42833 - doesn't look like a UNIX time

How to get the date from a cell in form of a UNIX time or a DateTimeInterface instance?

Upvotes: 39

Views: 60236

Answers (6)

Marián Černý
Marián Černý

Reputation: 15738

Excel stores dates as sequential serial numbers (so that they can be used in calculations), with 1 representing January 1, 1900. The fractional part represents the time portion.

If you don't want to use getFormattedValue(), you can check if the value is a date (using isDateTime()) and convert it to DateTime or Unix timestamp.

use PhpOffice\PhpSpreadsheet\Shared\Date as SharedDate;

$cell = $worksheet->getCell('A1');
$value = $cell->getValue();

if (SharedDate::isDateTime($cell, $value)) {
    $date = SharedDate::excelToDateTimeObject($value);
    // or $date = SharedDate::excelToTimestamp($value);
}

Following is the code you could use when you use getCellIterator():

use PhpOffice\PhpSpreadsheet\Shared\Date as SharedDate;

$rowIterator = $worksheet->getRowIterator();
foreach ($rowIterator as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false); // Include empty cells

    $rowData = [];
    foreach ($cellIterator as $cell) {
        $value = $cell->getValue();

        if (SharedDate::isDateTime($cell, $value)) {
            $date = SharedDate::excelToDateTimeObject($value);
            $rowData[] = $date->format("Y-m-d");
        } else {
            $rowData[] = $value;
        }
    }

    print_r($rowData);
}

Upvotes: 2

Omar Trkzi
Omar Trkzi

Reputation: 301

None of the above worked for me on a Symfony 5 application.

But this did:

$birthdate =
  \DateTime::createFromFormat('Y-m-d', ($sheet->getCellByColumnAndRow($col,$row)->getFormattedValue()));

If you use a different format on your Excel make sure to change it.

Upvotes: 1

Christophe Meheut Forik 21
Christophe Meheut Forik 21

Reputation: 101

I hope my answer will complete for those who are lost with reading date. I also had an issue with a column containing dates. When I read in Excel all dates were at the format d/m/Y, but using PhpOffice\PhpSpreadsheet, some lines were read as d/m/Y and others as m/d/Y. Here is how I do:

First, I check the format with ->getDataType()

my default format is m/d/Y but when ->getDataType() returns 's' it becomes d/m/Y

$cellDataType = $objSheetData->getCell("D".$i)->getDataType();
$cellFormat = 'm/d/Y';
if ($cellDataType=='s'){
    $cellFormat = 'd/m/Y';
}
$resultDate=\DateTime::createFromFormat($cellFormat, $sheetData[$i]['D']);

it works fine

Upvotes: 3

Jemshid m h
Jemshid m h

Reputation: 153

Since i cant add comment just adding an answer for future users. you can convert a excel timestamp to unix timestamp by using the following code (from accepted answer)

 $value = $worksheet->getCell('A1')->getValue();
 $date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($value);

And you can determine whether the given cell is Date Time using \PhpOffice\PhpSpreadsheet\Shared\Date::isDateTime($cell) function.

Upvotes: 11

Jay
Jay

Reputation: 741

When we are iterating with $row->getCellIterator() or we might have other kinds of value, it might be useful to use getFormattedValue instead

while using getValue()

  • Full name: Jane Doe => "Jane Doe"
  • DOB: 11/18/2000 => 36848.0

while using getFormattedValue()

  • Full name: Jane Doe => "Jane Doe"
  • DOB: 11/18/2000 => "11/18/2000"

Upvotes: 19

Finesse
Finesse

Reputation: 10801

The value is amount of days passed since 1900. You can use the PhpSpreadsheet built-in functions to convert it to a unix timestamp:

$value = $worksheet->getCell('A1')->getValue();
$date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($value);

Or to a PHP DateTime object:

$value = $worksheet->getCell('A1')->getValue();
$date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value);

Upvotes: 73

Related Questions