PHPspreadsheet and JS: How to support arabic?

Question

I'm getting data from the database, some strings are English, some are Arabic, some are half Arabic half English.

I'm generating the spreadsheet .xlsx like this

function writeToFile($spreadsheet, $filename)
{
    header("Content-Encoding: UTF-8");
    header('Content-Type: application/vnd.ms-excel; charset=UTF-8"');
    header('Content-Disposition: attachment; filename=' . $filename);
    $writer = new Xlsx($spreadsheet);

    $writer->save("php://output");
}

But I'm not downloading it directly, this generates a blob which gets downloaded using javascript.

fetch(url)
    .then(resp => resp.blob())
    .then(file => {
        if (file.size > 0) {
            const url = window.URL.createObjectURL(file);
            const a = document.createElement('a');
            a.style.display = 'none';
            a.href = url;
            a.download = filename;
            document.body.appendChild(a);
            a.click();
            window.URL.revokeObjectURL(url);
        } else {
             console.log('error');
        }
    })
    .catch(() => console.log('error'));

The problem is with PHPspreadsheet, I don't know if I fix that, I'll get another problem related to JS, but I do know that if I generate the file without using JS, I'll still have the Arabic issue.

What if you download the `.xlsx` directly without js, is it encoded correctly then? — Patrick, Jan 20 '20 at 09:30
@Patrick I'll try that just to see if it works but even then, that won't be an option because by downloading it without JS, I'd have to open a new tab and won't know if the file is empty or not before downloading it. — Lynob, Jan 20 '20 at 09:33
the idea is to figure out if the error occurs on PHP side og JS side — Patrick, Jan 20 '20 at 10:01
@Patrick just tested it. The problem is with PHPspreadsheet, I don't know if I fix that, I'll get another problem related to JS, but I do know that if I generate the file without using JS, I'll still have the Arabic issue. — Lynob, Jan 20 '20 at 10:06
What about to save the file first on directory and then download the result? — Collie-IT Anne K. Frey, Jan 20 '20 at 12:31
@Sundance_Raphael tried that, tried to save to a directory and then viewed the file created in the directory without any download. The Arabic part is still messed up. — Lynob, Jan 20 '20 at 12:36
Have you but the right spelling direktion to the arabic thinks? — Collie-IT Anne K. Frey, Jan 20 '20 at 12:57
@Sundance_Raphael, No, I don't even see the letters, I see question marks `????` — Lynob, Jan 20 '20 at 13:08
Looks like an encoding issue are you sure that you use an encoding in excel that understand arabic characters? The standard encoding is "Westeuropäuisch" means has no set for arrabic characters. — Collie-IT Anne K. Frey, Jan 20 '20 at 13:43
@Sundance_Raphael My excel is perfectly able to read arabic, I opened and viewed [this sample file](http://www.meabank.com/sites/default/files/Personal%20Loan%20Application.xlsx) successfully, are you able to write an example arabic spreadsheet? — Lynob, Jan 20 '20 at 17:25

score 0 · Accepted Answer · answered Jan 20 '20 at 21:29

Per this answer, It turns out I need to add

array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'")

To the PDO connection, so it becomes like this

 try {
        $db = new PDO("mysql:host=$server;dbname=$database", $user, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"));
        // set the PDO error mode to exception
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        echo "Connection failed: " . $e->getMessage();
    }
    return $db;
}

PHPspreadsheet and JS: How to support arabic?

1 Answers1