PHP Spreadsheet_Excel_Reader - Reading Dates

Question

I'm trying to use this class to import a large amount of data. Most of the data is being read correctly, however I have two date columns which are giving me problems.

The dates are in the format DD/MM/YYYY and the values returned are one day ahead of those in the spreadsheet. For example, 04/03/2011 becomes 05/03/2011

I have tried accessing the data like this:

$data->sheets[$sheet]['cells'][$row][$col];

I have also tried using the raw data:

$data->sheets[$sheet]['cellsInfo'][$row][$col]['raw']

Which returns the date as a unix timestamp but still it is one day ahead of what it should be.

Is there anyway I can force the class to return the value of the column as a simple string?

Any advice appreciated.

Thanks.

NullUserException · Accepted Answer · 2011-11-13 16:37:05Z

2

Spreadsheet_Excel_Reader converst the Excel numeric date/timestamp to a PHP date string using the standard PHP date() function. The numeric value held as a date/timestamp in Excel is effectively UTC, so this is likely to be a timezone issue. Try issuing a date_default_timezone_set() to UTC in your PHP script before importing the workbook.

edited Nov 13, 2011 at 16:37

NullUserException

85.8k31 gold badges212 silver badges239 bronze badges

answered Mar 7, 2011 at 11:23

Mark Baker

213k34 gold badges354 silver badges391 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dan Over a year ago

Thanks. UTC didn't work but I managed to get it working with another timezone.

Searock · Accepted Answer · 2011-12-21 12:01:31Z

I have the same problem, I resolve it with a little function...

$zone=dateadde("d",-1,$cell);

where : [ # enter date = 23/08/2011

 function dateadde($interval, $number, $date) 
 {
    $jour=substr("$date", 0, 2);
    $mois=substr("$date", 3, 2);
    $annee=substr("$date", 6, 4);
    $adate = mktime(0,0,0,$mois,$jour,$annee);

    $date_time_array = getdate($adate);
    $hours = $date_time_array['hours'];
    $minutes = $date_time_array['minutes'];
    $seconds = $date_time_array['seconds'];
    $month = $date_time_array['mon'];
    $day = $date_time_array['mday'];
    $year = $date_time_array['year'];
    switch ($interval) 
    {
        case 'yyyy':
            $year+=$number;
            break;
        case 'q':
            $year+=($number*3);
            break;
        case 'm':
            $month+=$number;
            break;
        case 'y':
        case 'd':
        case 'w':
            $day+=$number;
            break;
        case 'ww':
            $day+=($number*7);
            break;  
    }
    $timestamp= mktime(0,0,0,$month,$day,$year);
    $jourascii=strftime("%d/%m/%Y",$timestamp);
    return $jourascii;
 }

]

so bad editor !!

Curlas · Accepted Answer · 2011-03-07 11:29:34Z

0

I think that the dates in file are not in Excel date format. It's posible?

Can you get the filed like string and then format another string in MM/DD/YYYY format and use strtotime function?

answered Mar 7, 2011 at 11:29

Curlas

8995 silver badges14 bronze badges

Collectives™ on Stack Overflow

PHP Spreadsheet_Excel_Reader - Reading Dates

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related