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++)