PHPExcel how to solve the encoding issue when reading a file

Question

I was working on an Yii2 API where i need to upload a .csv or .xlsx file and read from it using PHPExcel(DEPRECATED now , but i am stuck with it as new one PhpSpreadsheet requires PHP version 5.6 or newer) and return the array of data .

This was the code used in the API function

public function actionUpload()
{
    $params = $_FILES['uploadFile'];
    if($params)
    {
        $data = array();
        $model = new UploadForm();
        $model->uploadFile = $_FILES['uploadFile'];
        $file =  UploadedFile::getInstanceByname('uploadFile');
        $inputFileName = $model->getpath($file,$data);
        //  Read your Excel workbook
        try
        {
            $inputFileType = \PHPExcel_IOFactory::identify($inputFileName['link']);
            $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
            if($inputFileType == 'CSV')
            {   


                if (mb_check_encoding(file_get_contents($inputFileName['link']), 'UTF-8'))
                {
                    $objReader->setInputEncoding('UTF-8');
                }
                else
                {
                     $objReader->setInputEncoding('Windows-1255');
                     //$objReader->setInputEncoding('ISO-8859-8');
                }


            }
            $objPHPExcel = $objReader->load($inputFileName['link']);
        }
        catch(Exception $e)
        {
            die('Error loading file "'.pathinfo($inputFileName['link'],PATHINFO_BASENAME).'": '.$e->getMessage());
        }

        //  Get worksheet dimensions
        $sheet = $objPHPExcel->getSheet(0); 
        $highestRow = $sheet->getHighestRow(); 
        $highestColumn = $sheet->getHighestColumn();
        $fileData = array();
        //  Loop through each row of the worksheet in turn
        for ($row = 1; $row <= $highestRow; $row++)
        { 
            //  Read a row of data into an array
            $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
                                            NULL,
                                            TRUE,
                                            FALSE);
            array_push($fileData,$rowData[0]);
            //  Insert row data array into your database of choice here
        }
        return $fileData;
    }

}

But there are encoding issues when we upload a excel file containing hebrew data in it . As you can see the code below from the above code was used to address this issue

if (mb_check_encoding(file_get_contents($inputFileName['link']), 'UTF-8'))
{
    $objReader->setInputEncoding('UTF-8');
}
else
{
        $objReader->setInputEncoding('Windows-1255');

}

Later i found that UTF-8 and Windows-1255 are not the only possible encoding for the flies that may be uploaded but other encoding like UTF-16 or other ones depending upon the Operating System of user. Is there any better way to find the encoding other than using mb_check_encoding

The common error that occur during the process of reading the data in file is :

iconv(): Detected an illegal character in input string

As you can see the above error occurs due to the inability to detect the appropriate encoding of the file. Is there any workaround ?

score 2 · Answer 1 · answered Jun 04 '18 at 22:48

2

You can attempt to use mb_detect_encoding to detect the file encoding but I find that results vary. You might have to manually specify a custom match order of encodings to get proper results. Here is an example substitute for the if statement in question:

if(inputFileType == 'CSV')
{
    // Try to detect file encoding
    $encoding = mb_detect_encoding(file_get_contents($inputFileName['link']),
                     // example of a manual detection order
                    'ASCII,UTF-8,ISO-8859-15');

    $objReader->setInputEncoding($encoding);
}

answered Jun 04 '18 at 22:48

ofurkusi

133
1
6

1

actually i had used that method before by using mb_detect_encoding against an array of encoding types (as i had to consider a bunch of encoding types ) but , the problem is mb_detect_encoding will not detect exact encoding its just guessing and you may get different result and depends upon the list of encoding you are compare with . Currently the only possible working method is to read the file using JavaScript in the client side and change the encoding there before sending it to server . Since i solved it in JavaScript instead of above method i did not post the answer here – Joel Joseph Jun 06 '18 at 07:40
1

also if anyone is looking for the solution to this problem , this is how i solved it in JavaScript , checkout my other stackoverflow thread : https://stackoverflow.com/questions/50385028/filereader-read-file-using-correct-encoding-when-read-as-readasarraybuffer/ – Joel Joseph Jun 06 '18 at 08:14
just an update here: i could not find a solution at php side, so solved the encoding problem using JavaScript . ie before sending the data to the php .check the link above to see the solution – Joel Joseph Jun 12 '19 at 08:37

score 0 · Answer 2 · answered Apr 03 '18 at 09:01

0

Make sure the first clean the output buffer in your page:

ob_end_clean();
header( "Content-type: application/vnd.ms-excel" );
header('Content-Disposition: attachment; filename="uploadFile.xls"');
header("Pragma: no-cache");
header("Expires: 0");
ob_end_clean();

answered Apr 03 '18 at 09:01

zoymoy1726426

31
1

Thanks, but method will not work in this case as its an API and here i am reading file at the server and sending back the data as json – Joel Joseph Jun 06 '18 at 07:48

PHPExcel how to solve the encoding issue when reading a file

2 Answers2