Need help - Uploading Excel XLS files and converting Excel's Date and Time format

[eluser]skattabrain[/eluser]
Hello,

I'm trying to make uploading Excel data as easy as possible for my users. In the past, I had users convert their XLS files to CSV ... then they could upload and import their data using my tools.

This time, it has to be straight XLS files. So I did a quick search on the wiki and found this - http://codeigniter.com/wiki/Excel_Reader_Class/ (thanks to Derek & James Gifford for the work there).

So this works great, but I'm having trouble with 1 crucial piece ... taking the date and time field from the XLS and converting it into a date I can work with.

I found a couple functions searching google for converting the date part ... but not the time. Excel stores it's dates and times in a serial format. I can't seem to figure this out ... driving me nuts!

This will return the date, but no time ...

Code:

function excel_D2DMY($days) {
   if ($days <1) return "";
   if ($days == 60)   {
      return array('day'=>29,'month'=>2,'year'=>1900);
   } else {
      if ($days < 60)   {
         // Because of the 29-02-1900 bug, any serial date
         // under 60 is one off... Compensate.
         ++$days;
      }
      // Modified Julian to DMY calculation with an addition of 2415019
     $l = $days + 68569 + 2415019;
     $n = floor(( 4 * $l ) / 146097);
     $l = $l - floor(( 146097 * $n + 3 ) / 4);
     $i = floor(( 4000 * ( $l + 1 ) ) / 1461001);
     $l = $l - floor(( 1461 * $i ) / 4) + 31;
     $j = floor(( 80 * $l ) / 2447);
     $nDay = $l - floor(( 2447 * $j ) / 80);
     $l = floor($j / 11);
     $nMonth = $j + 2 - ( 12 * $l );
     $nYear = 100 * ( $n - 49 ) + $i + $l;
     $ret = $nDay.' - '.$nMonth.' - '.$nYear;
     return $ret;
   }
}

Here is a snippet of the array the Excel Reader creates ...

Code:

array(13) {
    [0]=>
    string(13) "NUM"
    [1]=>
    string(4) "DATE"
    [2]=>
    string(15) "TIME (HH:MM:SS)"
    [3]=>
    string(13) "NUMC"
    [4]=>
    string(4) "MODE"
    [5]=>
    string(11) "LENGTH"
    [6]=>
    string(18) "DURATION"
    [7]=>
    string(9) "CTYPE"
    [8]=>
    string(4) "ZX"
    [9]=>
    string(7) "DOL"
    [10]=>
    string(5) "POUND"
    [11]=>
    string(11) "DEST"
    [12]=>
    string(8) "LOC"
  }
  [1]=>
  array(8) {
    [0]=>
    float(7852210022)
    [1]=>
    int(39537)
    [2]=>
    float(0.33478009259259)
    [3]=>
    float(7748967521)
    [5]=>
    float(0.0024421296296296)
    [6]=>
    int(4)
    [8]=>
    string(2) "UK"
    [9]=>
    float(2.6)
  }

Specifically ... these pieces of the array ...

Code:

[1]=>
    int(39537)
    [2]=>
    float(0.33478009259259)

Any ideas?

My users have several of these and their different XLS files have different date formatting ... so CSV exports will be a problem ... I chose to keep XLS because Excel keeps the the date in this particular format ... so if I can convert it ... then I'm set!

Thanks if you have input.

[eluser]CARP[/eluser]
Hi
Could you solve this issue?

[eluser]Randy Casburn[/eluser]
There is a new datatime XML format available in Excel 2007. I use this to avoid this very situation. I've included a Windows-only (sorry) way to demonstrate how easy it is to retrieve a date string directly from Excel.

This will only work if you have MS Excel 2007 on your Windows box. It will fail if you have an earlier version or don't have Excel at all.

I realize this does not meet your needs since I'm using PHP COM, but I thought perhaps you could explore what's here and use it to discover a better solution for yourselves.

The key part you want to look at is in index.php line 18. Note the three cells are datetime types and I use a parameter in the last one to return the string rather than the funky double data type (variant).

Hope this is helpful.

Well drat! The forum won't let me attach a zip file. Send me a pm and I'll send you the file that way or via e-mail if you're interested.

Randy

[eluser]skattabrain[/eluser]
CARP - check this thread - http://ellislab.com/forums/viewthread/47299/P45/#456333

i haven't dealt with Excel 2007 file uploads yet ... client didn't mind using excel 2003 or jsut saving them in a XLS format.

i haven't put much time in thinking about it ... but i wonder if OpenOffice has an API that those of us on *nix could use as an engine for document conversion.

[eluser]CARP[/eluser]
thanks skattabrain

[eluser]Randy Casburn[/eluser]
Right - I saw that too. I wasn't sure if that fixed CARP's problem or not. I'm using PHP COM in stand alone apps directly on the client with Excel. Pulling charts/graphs out of Excel directly and utilizing Excel's functionality where I need to. I don't manipulate the raw data file too much. Do the same thing with MS Word as well. Outlook is a little trickier because of security.

Just thought I would provide a different insight since CARP re-opened this rather old post.

Randy