Strange Square Characters at End of String

Topics: User Forum
Jul 28, 2008 at 11:47 AM


I have a strange problem when writing a date-string with PHPExcel. When I open the file saved with PHPExcel (in Excel5 format - unfortunately this is the requirement), it looks fine in OpenOffice, however, when I open it I get two "square" characters (indicating missing characters?) at the end of my date string. However, if I output the values to the console, using print statements, the characters do not appear.

Is there anything special I should consider when writing date strings to the Excel5 format?

Here is a snippet from my code:


  $datum = $objPHPExcel->getActiveSheet()->getCell($alphabet[$col].$row)->getValue();
$datum = preg_replace('/"."/','.',$datum);
$datum = preg_replace('/\n|\r/','',$datum);
//behandle Datumsformat

Here is how I read the file originally:

$objPHPExcel = $objReader->load("test.xls");

and here is how I save it:

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

Thank you for any idea how to get rid of the two square characters.

Jul 29, 2008 at 12:46 AM
I have not been doing much date formatting with PHPExcel so probably I cannot help. But what does the spreadsheet test.xls look like that you are reading? And is PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY_POINT your home-made constant? I don't see it in the class definition?

Maybe if you can upload your test files somewhere then someone can inspect them and help.
Jul 29, 2008 at 1:00 PM
I did a little more research on the issue.

The two "invisible characters", represented as a square in MS Excel are in fact two invisible ascii characters: ASCII 30 (record separator RS) and ASCII 4 (EOT character). No idea how these characters came into the excel file.

I tried to replace them with

$datum = str_replace(chr(4),"",$datum);
$datum = str_replace(chr(30),"",$datum);

but this doesn't seem to help.

- yes, the "PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY_POINT" is my own home-made constant, since there wasn't any constant for the german date format available.

Any ideas on how to get rid of the ASCII 4 and 30 characters would be appreciated!

Jul 29, 2008 at 2:27 PM
hm - experimenting with some other files I see that the problem arises only with 1 or 2 files, not with other files. I can live with that.

The strange thing is that those "invisible" low-ascii characters aren't visible in the file before the file was manipulated by PHPExcel, but are visible afterwards.

I will monitor the issue and see if the problem appears with more files. If yes, I will upload a sample file somewhere.