Date and Time fix for the reader :)

[Brian Hall]

Hey everyone,

I wanted to use this handy XLS reader to import dates into a calendar
on a website I'm working on. Since the dates and times didn't work, I
took the liberty of fixing it.

In _getCellDetails replace the if ($type == 'date') { section with
this code:

if ($type == 'date') {
$format = $this->formatRecords['xfrecords'][$xfindex]
['format'];
$formatIndex = $this->formatRecords['xfrecords'][$xfindex]
['formatIndex'];
$rectype = 'date';
// Convert numeric value into a date
$utcDays = floor($numValue - ($this->nineteenFour ?
SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904 :
SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS));
$utcValue = ($utcDays+1) * SPREADSHEET_EXCEL_READER_MSINADAY;
$dateinfo = getdate($utcValue);

$raw = $numValue;
$day = floor($numValue);
$fractionalDay = $numValue - $day;

$totalseconds = floor(SPREADSHEET_EXCEL_READER_MSINADAY *
$fractionalDay);
$secs = $totalseconds % 60;
$totalseconds -= $secs;
$hours = floor($totalseconds / (60 * 60));
$mins = floor($totalseconds / 60) % 60;

$string = date ($format, mktime($hours, $mins, $secs, $dateinfo
["mon"], $dateinfo["mday"], $dateinfo["year"]));
}

All your dates and times will work properly thereafter.

Enjoy!

Brian Hall

[matthew...@gmail.com]

Do you have an example or test case that shows this working on a
variety of dates/times?
I haven't looked at the logic in detail so I don't know what it's
doing differently, but I would be afraid to plug it into the code
without first seeing a bunch of examples that work correctly. I don't
have time to do this myself right now :)

Thanks!

Matt

[matthew...@gmail.com]

Seems to work, but I've noticed one quirk.
If I have a date/time like:

1/1/2009 1:45:00 PM

in excel, the PHP reader says 1:44 instead of 1:45.
If I add a second to the value in excel, the PHP reader properly shows
1:45.

Matt


On Jan 23, 12:32 pm, Brian Hall <brianhall12...@gmail.com> wrote:

[yureshwar ravuri]

what is the size limit of this php excel reader? Whether i can give more than 10mb of excel file?
Regards,
Yureshwar Ravuri
Mob: 9241214987
Web: www.yuresh.net

[matthew...@gmail.com]

I have resolve this issue by arbitrarily adding .0000001 seconds to
the date. It looks like a difference in how excel vs php stored the
internal fractional number. I will be putting this fix in shortly and
releasing a new version. Thank you, Brian!

Matt

[Brian Hall]

I have an updated fix to my fix. :)

I noticed times with no dates at all would be off.  I now do this:

      if ($type == 'date') {

        $format = $this->formatRecords['xfrecords'][$xfindex]['format'];

        $formatIndex = $this->formatRecords['xfrecords'][$xfindex]['formatIndex'];

        $rectype = 'date';

        // Convert numeric value into a date

        $utcDays = floor($numValue - ($this->nineteenFour ? SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904 : SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS));

        $utcValue = ($utcDays+1) * SPREADSHEET_EXCEL_READER_MSINADAY;

        if ($utcDays < 1)

          $utcValue = time();

        $dateinfo = getdate($utcValue);

        

        $raw = $numValue;

        $day = floor($numValue);

        $fractionalDay = $numValue - $day;

        

        $totalseconds = intval(SPREADSHEET_EXCEL_READER_MSINADAY * $fractionalDay);

        $secs = $totalseconds % 60;

        $hours = ($totalseconds - $secs) / (60 * 60);

        $mins = ($totalseconds / 60) % 60;

        

        $thedate = mktime($hours, $mins, $secs, $dateinfo["mon"], $dateinfo["mday"], $dateinfo["year"], 0);

        $string = date ($format, $thedate);

      }

The only thing I'm trying to sort out now is why on some servers, dates that are just dates and no time are getting an extra day tacked on.  I need to look into that... I'm sure its another silly thing that mktime does.

[Brian Hall]

Incidentally I tested all this stuff with your example XLS file and an XLS file that I use to import some calendar events into my website.  That XLS file plain dates and plain times but no date and times.  I'll let you know if I sort out the problem I was running into with a day getting added in if there was no time, but only on this one other server I was testing on.

[Alec Pin]

пятница, 23 января 2009 г., 20:32:05 UTC+2 пользователь Brian Hall написал:

One issue happens with case of XLS file where use date in one column, and time in another.
It can be simply corrected as following:

last operator at date block:

$string = date ($format, mktime($hours, $mins, $secs, $dateinfo["mon"], $dateinfo["mday"], $dateinfo["year"]));

must be changed to (i do not use date formatting):

if ($numValue > 1) {
        $string = mktime($hours, $mins, $secs, $dateinfo["mon"], $dateinfo["mday"], $dateinfo["year"]);
} else {
        $string = mktime($hours, $mins, $secs, 1, 1, 70);
}

If somebody require formatting, please add date($format... in front of  mktime. I do not do so because utc date and time can be added together getting real date (with time) if they are in different columns.

[Alec Pin]

пятница, 23 января 2009 г., 20:32:05 UTC+2 пользователь Brian Hall написал:

I've made a little improvement to my code. It is better to use direct calculation of time to avoid automatic time zone correction:

if ($numValue > 1) {
      $string = mktime($hours, $mins, $secs, $dateinfo["mon"], $dateinfo["mday"], $dateinfo["year"]);
} else {
      $string = floor($hours * 60 * 60 + $mins * 60 + $secs);
}

[Todd Albert]

I made all of the suggested changes and I it is still returning my dates as 'MM/DD/YYYY' (that exact string, NOT a date) and datetimes as 'MM/DD/YYYY HH:MM:SS' (again, that string, not an actual time), even though the times and dates are in the .xls file.

Any suggestions??

-Todd