Phpexcel Get Formatted Date as Is Visible in Excel File

PHPEXCEL get formatted date as is visible in excel file

I have found out the solution:

Method _formatAsDate in the file PHPExcel/Style/NumberFormat.php

if the date is like 16/11/2014, when passed to strtotime will result in false as the date is supposed to be in format m/d/Y by strtotime. So if you change the format to m/d/Y if it's d/m/Y then the solution will always be correct.

Earlier:

1. 16/11/2014==1970-01-01 (Row: 1)
2. 16/11/2014==1970-01-01 (Row: 2)
3. 23/12/2014==1970-01-01 (Row: 3).

Now:

1. 11/16/2014==2014-11-16 (Row: 1)
2. 11/16/2014==2014-11-16 (Row: 2)
3. 12/23/2014==2014-12-23 (Row: 3)

Code is still the same and simple to import the file:

protected function importExcel($filePath) {
    $excelData = array();
    if ($filePath) {
        $objPHPExcel = PHPExcel_IOFactory::load($filePath);
        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
            $worksheetTitle = $worksheet->getTitle();
            $highestRow = $worksheet->getHighestRow(); // e.g. 10
            $highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
            $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
            $nrColumns = ord($highestColumn) - 64;
            $data = array();
            for ($row = 1; $row <= $highestRow; ++$row) {
                $values = array();
                for ($col = 0; $col < $highestColumnIndex; ++$col) {
                    $cell = $worksheet->getCellByColumnAndRow($col, $row);
                    $val = $cell->getValue();
                    if (isset($val) && $val)
                        $data[$row][$col] = $val;
                }
            }
            $excelData[$worksheetTitle] = $data;
        }
        return $excelData;
    }
    return FALSE;
}

PHPExcel - Get date with forced format

You can return the raw value from the cell using the getValue() method,

$excelTimestampValue = $objPHPExcel->getActiveSheet()
    ->getCell('A12')
    ->getValue();

which will return an MS Excel serialized timestamp value; that is, a floating point number representing the number of days since 1st January 1900 (or 1st January 1904, depending on which calendar the spreadsheet file is using). From that timestamp value, you have a number of options:

Convert that timestamp to a unix timestamp, which you can then format however you wish using PHP's date() function.

$unixTimestamp = PHPExcel_Shared_Date::ExcelToPHP($excelTimestampValue);
echo date('d-M-Y', $unixTimestamp);

Convert that timestamp to a PHP DateTime object, which you can then format however you wish using the format() method.

$dto = PHPExcel_Shared_Date::ExcelToPHPObject($excelTimestampValue);
echo $dto->format('d-M-Y');

Format it directly to a string, using an MS Excel date format mask.

echo PHPExcel_Style_NumberFormat::toFormattedString($excelTimestampValue, 'dd-mmm-yyyy');

PHPExcel date issue on file iteration

If the date format mask for the cell is 10/03/17, then that is the format that a call to getFormattedValue() will return; which isn't a good format to insert in the database. You're better off explicitly using a different format mask, or even retrieving the value as a DateTime object (or a unix timestamp) than a formatted string.

However, more critical is

if (PHPExcel_Shared_Date::isDateTime($val)) {

where you're passing the MS Excel timestamp value (a simple float) to see if it's a date/time. PHPExcel can't identify whether a float is a date or not; it needs to be able to read the number format mask for the cell. You need to pass the cell itself to isDateTime(), not just the value.

if (PHPExcel_Shared_Date::isDateTime($cell)) {

as shown in the PHPExcel API Documentation; and exactly as the error message

"Uncaught TypeError: Argument 1 passed to PHPExcel_Shared_Date::isDateTime() must be an instance of PHPExcel_Cell, string given"

tells you:

Read Date Format in PHP EXCEL

When you read xls file with PHPExcel_Reader_Excel5 lib, the data in file is 39984, but Excel formats it using a date formatting mask as '2009-06-20'?

Excel holds dates as a count of the number of days since 1st January 1900 (on Windows 1900 calendar). PHPExcel stores its dates in the same way, but doesn't automatically format them for you.

You can format them yourself using PHPExcel_Style_NumberFormat::toFormattedString(39984,PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY)
or any of the other format masks in
PHPExcel_Style_NumberFormat
, or convert it to a PHP date using
PHPExcel_Shared_Date::ExcelToPHP(39984) and then use PHP's date()
function to format it as you wish

Example:

$val = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($cell->getValue()));

PHPExcel issue with date format

Your problem is

$objReader->setReadDataOnly(true);

You're telling PHPExcel only to read the raw data from the cell, without any formatting information; but MS Excel uses a float for dates (number of days since 1/1/1900 or since 1/1/1904, depending on the calendar used) and a number format mask to format it as a date.... by setting read data only to true, you're telling PHPExcel not to read the number format masks, so there is no way of differentiating a date value from any other float value.

Solution: don't use

$objReader->setReadDataOnly(true);

PHPExcel: how to set date format for a cell

$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.

Look at the date Examples in the PHPExcel Documentation and Examples, and you'll see that you need to set the cell value to a MS Excel serialized timestamp (a float value of the number of days since 1st January 1900). You can use the PHPExcel functions like PHPExcel_Shared_Date::PHPToExcel() to convert human dates/PHP DateTime objects/Unix timestamps to MS Excel Serialized timestamps.

$sheet->setCellValueByColumnAndRow(0, 1, PHPExcel_Shared_Date::PHPToExcel( '2014-10-16' ));

Once you've stored the value as a timestamp, you can then apply whatever date format mask you want to that cell to get your desired formatting

PHPExcel library get date

$v is just a numeric value, the serialized timestamp value used by MS Excel, no different to any other float value; it isn't possible to tell if $v represents a date or not without being able to access the format mask for the cell, which is why the isDateTime() method needs to read the cell itself to determine if it contains a date or not.

Ensure that your array is returned as an associative array (by setting the additional last argument for the method as true), which gives you the address of each cell:

$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE, TRUE);

This will return an array indexed by its row address, as a nested array by column address, so your foreach will need to reflect the row number:

foreach($rowData[$row] as $k=>$v)

and $k will then be the column address.

You can then use $row and $k to get the cell, and pass that to the isDateTime() call:

if(PHPExcel_Shared_Date::isDateTime($sheet->getCell($k . $row))) 

---

And note that

$arr[$i] = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($InvDate));

should be

$arr[$i] = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($v));

in your code

phpexcel date is reading wrong format

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.

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.

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().

---

Related Topics