how to read 09/09/2017 exactly from xls using php?

Question

 var_dump($objPHPExcel->getActiveSheet()->getCell('B2')->getValue());

i tried the above code and i get "float(42987)" as output. i just want the exact value of that cell!

 var_dump($objPHPExcel->getActiveSheet()->getCell('C2')->getValue());

when i execute this i get the correct value "2017/09/12"

so how will i get the data's in this "09/09/2017" format from xls?

EDIT 1 : the data cannot be predicted ! it can be a string or date with different formats ! example : B2 can be 'string','09/09/2017','09-09-2017','2017/09/09','10/20-25/14'

like this example any data can be there! so i just want exact data from the cell provided by the user!

EDIT 2 : i am using rangeToArray

 for ($row = 2; $row <= $highestRow; $row++){                 
              $rowData = $sheet->rangeToArray('A'.$row.':' . $highestColumn.$row,NULL,TRUE,FALSE);
}

so how will i implement ->getFormattedValue() in rangeToArray ?

score 2 · Answer 1 · answered Sep 21 '17 at 08:18

2

MS Excel uses a serialized timestamp (a bit like a unix timestamp) for dates, and that is the float(42987) that PHPExcel returns when you call getValue().... note that this is the exact value of the cell.... That float is converted to a date/time display in MS Excel by a number format mask. What you see in MS Excel is the formatted value of the cell, with the number format mask applied, so you need to tell PHPExcel to get the formatted value and not the exact (raw) value.

As long as you haven't loaded the file with readDataOnly set to true (which tells PHPExcel not to load style and formatting data), then use

 var_dump($objPHPExcel->getActiveSheet()->getCell('B2')->getFormattedValue());

answered Sep 21 '17 at 08:18

Mark Baker

209,507
32
346
385

is there any way to get `09/09/2017` instead of `float(42987)` ? – Tobin Thomas Sep 21 '17 at 09:24
2

Yes! Read what I posted as my answer!!!! `$objPHPExcel->getActiveSheet()->getCell('B2')->getFormattedValue()` will return the __formatted__ value, which for a timestamp means format it as a date (as long as you haven't told PHPExcel to load only the data and not the formatting) – Mark Baker Sep 21 '17 at 09:27
There are also a whole host of functions in PHPExcel_Shared_Date to convert by hand if you want to do so, and plenty of documentation in the PHPExcel docs and examples, and answers here on SO already like [this one](https://stackoverflow.com/questions/31603451/phpexcel-get-formatted-date-as-is-visible-in-excel-file/31604174#31604174) – Mark Baker Sep 21 '17 at 09:31
can you please go through my edit 2 in the question? – Tobin Thomas Sep 21 '17 at 09:59

score 2 · Accepted Answer · answered Sep 21 '17 at 10:14

If you are using the rangeToArray() method to get the data, then take a look at the arguments for rangeToArray()

/**
 * Create array from a range of cells
 *
 * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
 * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
 * @param boolean $calculateFormulas Should formulas be calculated?
 * @param boolean $formatData Should formatting be applied to cell values?
 * @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
 *                               True - Return rows and columns indexed by their actual row and column IDs
 * @return array
 */

So to get the formatted values returned by rangeToArray() you need to call it with the $formatData argument set to true

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

jeroen · Answer 3 · 2017-09-21T09:25:04.420

1

I think there are several ways to do that like for example ExcelToPHP() and toFormattedString().

Using the latter, you can convert and Excel value $value to the string you are looking for like:

$string = \PHPExcel_Style_NumberFormat::toFormattedString($value, 'DD/MM/YYYY');

edited Sep 21 '17 at 09:25

answered Sep 21 '17 at 08:13

jeroen

91,079
21
114
132

i cannot use 'DD-MM-YYYY' because whatever format the user gives , i should giveback the exact format! – Tobin Thomas Sep 21 '17 at 09:21
@TobinThomas You cannot really process data if you don't know what the data represents; how would you distinguish between a valid number `42987` and a date in Excel? – jeroen Sep 21 '17 at 09:24
i don't need any conversion to `42987` ! how to avoid this conversion? – Tobin Thomas Sep 21 '17 at 09:28
@TobinThomas - MS Excel does that conversion to 42987.... that's the serialized timestamp that MS Excel uses internally.... it only displays like a date because there is a number format mask applied to the cell telling MS Excel to format it as a date – Mark Baker Sep 21 '17 at 09:29
@TobinThomas The only conversion that takes place is in Excel when it shows you a formatted string when the value really is a number. Check this answer to see if a cell is formatted as a date: https://stackoverflow.com/a/31604174/42139 – jeroen Sep 21 '17 at 09:30

Jack · Answer 4 · 2017-09-21T08:40:32.363

That's an OADate (OLE Automation Date). What you're getting back float(42987) IS the exact value underneath. Excel just displays it as a date in whatever format you chose.

Use this class to convert it.

class OLEAutomationDateConverter
{
    /**
     * Get the OLE Automation Date epoch
     *
     * @return DateTimeImmutable
     */
    public static function BaseDate()
    {
        static $baseDate = null;
        if ($baseDate == null) {
            $baseDate = new DateTimeImmutable('1899-12-30 00:00:00');
        }
        return $baseDate;
    }
    /**
     * Convert a DateTime object to a float representing an OLE Automation Date
     *
     * @param DateTimeInterface $dateTime
     * @return float
     */
    public static function DateTimeToOADate(DateTimeInterface $dateTime)
    {
        $interval = self::BaseDate()->diff($dateTime);
        $mSecs = ($interval->h * 3600000)
            + ($interval->i * 60000)
            + ($interval->s * 1000)
            + floor($dateTime->format('u') / 1000);
        return $interval->days + ($mSecs / 86400000);
    }
    /**
     * Convert a float representing an OLE Automation Date to a DateTime object
     *
     * The returned value has a microsecond component, but resolution is millisecond and even
     * this should not be relied upon as it is subject to floating point precision errors
     *
     * @param float $oaDate
     * @return DateTime
     */
    public static function OADateToDateTime($oaDate)
    {
        $days = floor($oaDate);
        $msecsFloat = ($oaDate - $days) * 86400000;
        $msecs = floor($msecsFloat);
        $hours = floor($msecs / 3600000);
        $msecs %= 3600000;
        $mins = floor($msecs / 60000);
        $msecs %= 60000;
        $secs = floor($msecs / 1000);
        $msecs %= 1000;
        $dateTime = self::BaseDate()
            ->add(new DateInterval(sprintf('P%sDT%sH%sM%sS', $days, $hours, $mins, $secs)))
            ->format('Y-m-d H:i:s');
        return new DateTime("$dateTime.$msecs");
    }
}

Alternatively, if you can use javascript, use the moment library. There's a function to convert OADates TO and FROM.

https://github.com/markitondemand/moment-msdate#about-ole-automation-dates

Convert an OA date to a moment (or to a JavaScript date):

moment.fromOADate(41493) returns Wed Aug 07 2013 00:00:00 GMT-0600 (MDT)

For exact date and time (time is the value right of the decimal):

moment.fromOADate(41493.706892280097000) returns Wed Aug 07 2013 16:57:55 GMT-0600 (MDT)

By default moment.fromOADate() uses the server time as the offset to UTC a second argument can be provided that indicates the offset of the OA date to UTC in minutes.

moment.fromOADate(42754.835023148145, 360) returns Fri Jan 20 2017 02:02:25 GMT+0000 (UTC)

For Moment formatting:

//convert OA date into Moment (JavaScript date)
var momentDate = moment.fromOADate(41493.706892280097000);

//use Moment's awesomeness
var formattedDate = momentDate.format('MMM Do YY);

//formattedDate === "Aug 7th 13"

This could easily be chained together as:

moment.fromOADate(41493.706892280097000).format('MMM Do YY); //Aug 7th 13

Note: OLE Automation dates are unspecified, meaning they’re based on the local timezone by default.

how to read 09/09/2017 exactly from xls using php?

4 Answers4

Linked