getValue() issue from reading accent

Topics: Developer Forum
Jul 4, 2013 at 5:50 AM
Hello everybody,

I have the following issue

I read a .xls sheet by using getValue() method like this :
$objPHPExcel = PHPExcel_IOFactory::load($_FILES['excel']['tmp_name']);

$sheet = $objPHPExcel->getSheet(0);

foreach($sheet->getRowIterator() as $row){
     $tab = array();
     foreach($row->getCellIterator() as $cell){
          array_push($tab, $cell->getValue());
}
}
Then I stock the contents of each cell up on a database (mySQL/phpMyAdmin).
The contents of each cell is a string.
It works well except for the cells which contain a string with accent (french string), for them
getValue() return nothing, it looks like the cells are empty.

Thanks for help
Jul 4, 2013 at 6:43 AM
At first, I would go for a problem of encoding (character set) and not on the side of phpexcel.
Jul 4, 2013 at 6:58 AM
Thanks for your response,

Do you mean that I should look for problem of encoding on the side of the excel sheet?

I have unsuccessfully tried to use iconv and utf8_encode/utf8_decode functions but
I'm not sure of how using them.

I read that getValue returns UTF8 encoded string by default and my php file are also UTF8 encoded
so I realy don't know how I can resolve the problem
Jul 4, 2013 at 7:19 AM
Hum... If PHPExcel encounters an unknown page code, it raises an exception... Your Excel file was created by what program, on which platform? But unless something exotic, it would surprise me that the problem comes from there: over time, PHPExcel has seen many sources and the problems were resolved.

Assured you that getValue() returns actually empty. If this is the case, a small sample file with the problem would be useful.
Jul 4, 2013 at 7:42 AM
I'm sure that getValue return empty when I "echo" each getValue, the results are printed
except those contain accents like it is the case in the database.

Here the column which causes the problem in the excel sheet

!!Image

And here what is written in my phpMyAdmin database

Image

As you can see, 2 rows are empty in the database and they correspond to the 2 rows in the excel sheet which contain
an "à".

When I manually change the "à" in "a" directly in the excel sheet, the rows are written in the database without any problem.

I don't know if it is important but the excel file comes from another site.
I have to develop a php app based on the excel file datas
Jul 4, 2013 at 9:01 AM
I tried this : change the coding of the excel file by "save as" -> excel 97-2003 -> tools -> coding and I replace "windows" by "unicode (UTF-8)" and save the file with another name, uploaded it in my app and it works!

So now I try to find how I can change the coding of the whole excel file in php before using getValue() in my php file, because I can't manage coding of the excel file
to the extent that the user export the file from another website only with .xls extension (.csv export isn't possible).

Do you know how I could change the coding when I load the excel file in my PHPObject?
Jul 4, 2013 at 9:41 AM
Change the page code is perhaps not so obvious.
It might be interesting to know the code page recorded in the file and know if he lies.
The first point, to show the code when PHPExcel made the transcript.
A bit horrible, but the simplest is to display the value by the NumberToName method of the Shared_CodePage class in Shared\CodePage.php
Jul 4, 2013 at 12:07 PM
I edited the CodePage.php file to see how works the NumberToName method. It needs a number as argument but I don't know how I can obtain this number from my excel(.xls) file
Jul 4, 2013 at 12:34 PM
Despite including the file by using require_once("mypath/CodePage.php") it displays that NumberToName is undefined..
After several hours of search I know that Excel force the windows-latin1 code when it exports or opens file which correspond to the number 1252.

Does a way to force the coding of my PHPExcelObject to UTF-8 exists?
Jul 4, 2013 at 1:49 PM
You do not need to include it, PHPExcel does it all alone; Similarly, it is he who will communicate the codepage that he read to the method. Just add, on an interim basis what to display it.

Do you know if the basis file is created under Excel or if it is a creation by a server application?

Can you also try to save the file under a different name without change of parameters and see if this new file also poses problems?
Jul 4, 2013 at 2:18 PM
I don't know how to display the results of the method, the following message appears "Undefined function NumberToName()..."

The basis file come from another app which proposes data export in .xls or .pdf or directly print the datas.

I tried with the same export but another name and still the same result nothing return by getValue() from the cells containing accent.

I also tried utf8_encode and iconv methods but as $cell->getValue() returns nothing it doesn't work.

The only way it works is instead of export the file after have saved it, is to open it -> "save as" -> excel 97-2003 -> tools -> coding and chose "utf-8"
Jul 4, 2013 at 2:43 PM
Does not seek to call the method yourself.
Edit the file CodePage.php that you'll find in PHPExcel\Shared.
You'll find the NumberToName method, before the switch, adds an echo $codePage;
I think it'll display 1252, but see.
I suspect the creative application to generate an inconsistency between what she announces and what it produces, because from what I know, PHPExcel has no problems with accented letters.