Document not loading

Topics: Developer Forum, User Forum
Jul 10, 2008 at 3:50 AM
Edited Jul 10, 2008 at 4:49 AM
I am exporting a mysql database to excel using the library. Mostly work except for one of the custom exports ( exporting based on site) the resultant file will either not load in excel 2007 or give conversion error. I  have tried to take care of potential illegal characters with the below code

$out = preg_replace('/[^\x20-\x7E\x09\x0A\x0D]/',"",$in);
 $out = preg_replace('/&(?!\w{2,6};)/', '&', $out);
 $out = str_replace('<','&gt',$out);

but is still happening.

anyone got any ideas?

Have also tried

$out = preg_replace('/[^\x20-\x7E\x09\x0A\x0D]/',"",$in);
 $out = preg_replace('/([\xc0-\xdf].)/se', "'&#' . ((ord(substr('$1', 0, 1)) - 192) * 64 + (ord(substr('$1', 1, 1)) - 128)) . ';'", $out);
 $out = preg_replace('/([\xe0-\xef]..)/se', "'&#' . ((ord(substr('$1', 0, 1)) - 224) * 4096 + (ord(substr('$1', 1, 1)) - 128) * 64 + (ord(substr('$1', 2, 1)) - 128)) . ';'", $out);
 $out = preg_replace('/&(?!\w{2,6};)/', '&amp;', $out);
 $out = str_replace('<','&gt',$out);

to no effect

Jul 15, 2008 at 2:18 AM
Just a quick update
I am running every thing through the below function (and have tried a number of other variants)to try and strip out anything that might be causing problems

function strip_invalid_xml_chars ($in)
{
 $out = utf8_encode($in);
 $out = iconv("UTF-8","UTF-8//IGNORE",$out);
 $out = str_replace("\r"," ",$out);
 $out = preg_replace('/&(?!\w{2,6};)/', '&amp;', $out);
 $out = preg_replace('/[^\x20-\x7E\x09\x0A]/',"",$out);
 $out = preg_replace('/([\xc0-\xdf].)/se', "'&#' . ((ord(substr('$1', 0, 1)) - 192) * 64 + (ord(substr('$1', 1, 1)) - 128)) . ';'", $out);
 $out = preg_replace('/([\xe0-\xef]..)/se', "'&#' . ((ord(substr('$1', 0, 1)) - 224) * 4096 + (ord(substr('$1', 1, 1)) - 128) * 64 + (ord(substr('$1', 2, 1)) - 128)) . ';'", $out);

return $out;

}

and I am still only able to open some of the exported files.

I can unzip the files and have looked at the xml files directly and cannot see anything that is causing the problems  manually.

Has anybody got any ideas of what else I can look at to try and solve this rather picky problem.

Developer
Jul 15, 2008 at 7:29 AM
I don't know what the problem is, but maybe others would be able to help if you say what kind of data you are trying to export and where are all those illegal characters coming from? I guess you aren't exporting ordinary text?
Coordinator
Jul 15, 2008 at 7:34 AM
Can you send me a sample, non-opening XLSX file via e-mail? (maarten at php excel dot net)
Jul 15, 2008 at 11:58 PM

Hi Maarten, I have sent you both a working and nonworking file. They are both produced by the same code just a different dataset of the DB.

 

I have also exported it as html to see i I could see anything there and it opened fine in IE and could not see any issues there.

 

Hi Koyama thanks for your reply. yes the data should just be text but as it is sometimes cut and pasted from other apps it could contain characters such as & < and those wierd quotes that word can do, as well as possible others.