ERROR: Unreadable content

Topics: Developer Forum, Project Management Forum, User Forum
Aug 21, 2009 at 2:21 PM

I start to use PHP Excel some weeks ago. Everythig is fine, this is a great php class. But I have a problem. I'm trying to export all products from my database and convert it in to 2007 Excel file. If I put in my MySQL query LIMIT 392, everything is ok. The log for this is: Peak memory usage: 43 MB. But for all products export, ~4000 items, I have a problem. When I try to open the resulted file, the error messages is in the fallow images:

http://chiriluta.ro/x/error1.jpg

http://chiriluta.ro/x/error2.jpg

and this is the rezulted excel file:

http://chiriluta.ro/x/excel_file.xlsx

In my php script, I use

ini_set('memory_limit', -1);
set_time_limit(0);

but this doesn't help me so much..

The problem exist just on a public hosting server, in my http://localhost everything is ok.

So, that's all.. Do you have any ideea what I have to do in order to resolve my problem..?!!

Waiting for some answers, thanks a lot..

Developer
Aug 21, 2009 at 2:37 PM

>> and this is the rezulted excel file:

I think you posted the "recovered" file. We will unfortunately need to see the original file before it is opened in MS Office Excel. Can you post that one?

The usual cause of encoding errors when retrieving from MySQL database is that data isn't retrieved as UTF-8. Remember to use "set names". There is a thread here:
http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=50415

Aug 21, 2009 at 2:48 PM

The original file is here:

http://chiriluta.ro/x/original_excel_file.xlsx

How can I set names to UTF8 in my PHP script? Not in php.ini or .htaccess.

But I don't think the problem is from here. My database have MySQL charset: UTF-8 Unicode (utf8) , maybe is something about the memory used ot I don't know..

Thanks for help..

Developer
Aug 21, 2009 at 3:21 PM

>> The original file is here:

That's definitely ISO-8859-1 like I expected. If you change the extension from .xlsx file to .zip, unzip it and inspect xl/sharedStrings.xml at the position it is complaining about you will notice symbol "®" which is encoded as ISO-8859-1 when it should really be UTF-8

>> But I don't think the problem is from here. My database have MySQL charset:
>> UTF-8 Unicode (utf8)


It is the connection character set between PHP and MySQL that is the problem. Not the database character set. By default connection characer set is ISO-8859-1 which is annoying for those who prefer UTF-8. Look at that thread I linked to, it will solve your problem.

P.S. What version of PHPExcel are you using? I think you are not using PHPExcel 1.7.0. There are many improvements, please try it, it also strips illegal UTF-8 characters so you will not experience this problem.

Aug 25, 2009 at 2:59 PM

Hi again,

Everything it's ok now. I used

$product_name = str_replace(array('"', '&', '®', 'Â', '>'), "", $product_name);

Anybody know how to remove all the expressions "&...;" from a string, using regular expressions, ereg or preg_match? Thanks again for all..!

Developer
Aug 25, 2009 at 3:40 PM

Try like this:

$str = 'abcd"efg®';
$str = preg_replace('/&[^;]+;/', '', $str);