PHP-ExcelReader Solutions

PHP-ExcelReader is great for working with Excel spreadsheets in PHP code, but it has a few bugs and tricks. Here's a couple that I've run into, along with solutions.

Problem: Fatal error: Allowed memory size of [varies] bytes exhausted (tried to allocate [varies] bytes) in...

Solution: In my case, I was trying to work with a 1.3 MB file, and PHP-ExcelReader used the full 32 MB of allowable memory before dying. Yeah, apparently it's got memory issues. Anyway, increase the amount of memory allowed to PHP.

First, try adding the following code in your PHP script:
>ini_set("memory_limit", "64M");

If this doesn't correctly up the memory limit, due to some restriction on your host, see if you can edit your php.ini file directly (mine is located /etc/php.ini). Change the line that says "memory_limit = 32M" to something larger, such as "memory_limit = 64M"

Problem: Dates

Solution: My dates showed up in my spreadsheet as 06/03/09, but were obviously stored differently in the backend of Excel. When I would get the value from PHP-ExcelReader, they were simply numbers (39967, in the case of 06/03/09). A little research showed that the numbers were days, offset from (discovered by trial and error) 12/30/1899. Not sure if this will hold for everyone. Anyway, to convert them to proper MySQL date format, I used the following MySQL code:
>FROM_DAYS(39967+TO_DAYS('1899-12-30'))

Problem: Cells missing values

Solution: Perhaps this should be classified more as a warning than as a bug. Then again, warnings don't bite and bugs do, and this bit me.

When reading through the cells in a row, don't use "foreach". The cells will have sequentially numbered keys, but there could be numbers missing!

For example, a row of data in your spreadsheet like so:
aaa bbb ccc ddd eee
--- --- --- --- ---
111 222     444 555

...will result in a row of data like so:

array(1=>111, 2=>222, 4=>444, 5=>555);

Note how if you're sequencing through it with "foreach", expecting to get a blank value for key "3", you won't get it.

Old code: foreach ($row as $col_num=>$cell)
New code: for ($i=1; $i <= $num_cols; $i++)

It's your own fault, cat.

Our cat, for one of those reasons known only to the perpetrator, decided to go to the bathroom in our living room instead of in her litterbox. As a result, I locked her in her private room (without TV--remember that, parents, when you send your kids to their room!) which is a closet off our kitchen.

Acouchi decided to retaliate by kicking over her water dish, which I had newly filled this morning, but that plan backfired when she found herself standing in a pool of water. The only retreat was into her litterbox, which promptly clumped with surprising efficiency around her wet feet. Unaware of this, I finally decided she'd learned her lesson and I let her out, at which point she promptly streaked across the kitchen trailing litter.

She was heading for our bed, but I snagged her and carried her to the bathroom, where I filled the tub with an inch of water. Did you know that water is about as effective on cleaning litter designed to clump when it gets wet as it is on oil? I finally dried off Acouchi and let her go, but now I have a closet with a wet floor smeared with kitty litter.

And my wife's in California for 13 more hours.

Come home soon!

Thesis Defense

My thesis defense is completed. I'm done! ...except for chasing down a dozen signatures, printing off copies of my thesis, running forms to various offices...I think it was easier to write the thesis.