November 18, 2008

PhpExcelReader UTF-8 Problem HOW TO

At one of my works i had to read Excel files and write their contents to database for a Turkish company. The site's encoding is UTF-8 and the table's encoding is described as 'utf8_turkish_ci'.
I used the PhpExcelReader to read Excel files and i wrote down this code from their examples;
require_once('Excel/reader.php');
$data = new Spreadsheet_Excel_reader();
$data->setOutputEncoding('UTF-8');
$data->read($file_path);


but always when PhpExcelReader saw a Turkish character it stopped reading. So i decided that there may be an encoding mistake, so first i changed the 'outputEncoding's value. But it didn't change anything, also vitiated the words spellings. So i put another encoding conversion while it was reading the Excel files content.

for ($i = 2, $j = 0; $i <= $data->sheets[0]['numRows']; $i++, $j++) {
$value = mb_convert_encoding($data->sheets[0]['cells'][$i][$value_row], "UTF-8", "ISO-8859-9");
}

So that's it. After putting these conversions the contents are saved straightly. Also here is a good trick for ya;

After the excel file is uploaded to the system check all the first rows and ask user to couple them with your database rows from dropdown lists. After user couples the excel rows with database rows, go start from the 2nd row of excel file to read, and save the selected rows to database rows and then get 3rd and 4th etc. So you not need to enforce the user to obey your standarts.

No comments: