PHP Microsoft Excel Reader and Serial Killer Dates

So, my sister's ex-company of about 6 years that has shut down operations, but still has outstanding loan accounts to collect on...

They have this arcane accounting system and it generates fixed-width field .TXT files, with the wrong dates (with 2-digit year, of course).

The correct dates are in some Excel spreadsheets.

So they wanted a quick hack script to change columns 254 through 259 in some .TXT files based on a simple lookup in some Excel files.

Some developer told them it would take two weeks, and my sister didn't believe that, and they weren't real keen on spending that much $$$.

I told her it was more like a 2 hour job, which might turn into 2 days, when all is said and done.

It actually only took about 8 hours, all told, and most of that was spent in attempting to use various Excel reader PHP scripts that just plain didn't work.

The one up on PHPClasses ends up using parse_url to try to figure out where a local file is, and bombs out. It's a really nifty stream-filter OOP thingie, if it only actually worked...

And what's with all the annoying ads? Maybe less ads would get more traffic would get more revenue... [shrug]

There were several Excel Reader commercial options in the $100 to $200 range, which didn't appeal at all.

There's an Excel Writer in the PEAR Repository, but I couldn't see how to make it read Excel files.

There was some other Excel Reader script, but I forget now why I rejected it. Oh well.

Finally, I found an Excel Reader PEAR package, only it's on Sourceforge instead of in PEAR.

Go figure.

It also had a bug, where it was doing an include of 'OLERead.php' but the file was actually named 'OLERead.inc'

Not quite sure how that passed by a QA process, any QA process but it's trivial to fix.

I did submit a bug report, so hopefully it will get fixed. That is the nice thing about OpenSource.

The example to read a whole sheet in as an array was pretty much all I needed after that quick '.php' -> '.inc' hack.

I didn't even try the reading as a stream thing, since there are only a few thousand accounts.

So I had a nice Excel Reader to do the account number to date lookup.

Yippee.

Then came the joy of Microsoft Excel internal date format...

It's basically a "count" of days from January 1 1900 for the integer part, and a count of seconds for the fractional/decimal part.

Of course, Microsoft aped Lotus 123 and knowingly left in the bug of 1900 being a leap year (it's not)!

So from 0 to 60, the date is "off" by one, and at 60, the bogus date of February 29, 1900 is output. Everything is great from 61 up to a zillion or so where you get to December 31, 9999.

Now, granted, none of these loans date back to the first couple months of 1900, but it's still pretty irksome...

And you'd want a conversion function to be correct and re-usable, rather than something that only works for a limited input set. (Y2K anybody?)

I found a conversion function in C, and ported it to PHP:
http://l-i-e.com/excel_date.phps

I'm not claiming it's the best code ever, and I don't even know what it does, really, as I just changed the variables to have $ in front, and swapped int() function into floor() function.

Slapped in an sprintf instead of returning the individual month/day/year as pass-by-access args, since it seemed easier.

That pretty much sums up the past couple evenings for me.

I occasionally run into a Microsoft devotee who wonders why I hate Microsoft so much.

Really, if this rant doesn't make it clear why I hate Microsoft, I simply cannot hold a rational conversation with you...

It would have been a 2-hour job if MS wasn't so stupid, but it was an 8-hour job because Microsoft is, well, stupid.