Re: Help on international characters with Spreadsheet/Excel/Writer

Vincent.Bartro@alcatel.fr wrote:

Hello all, I'm using PhpBugTracker (freeware bug tracking tool, based on php/mysql), which includes an excel export functionality, using Spreadsheet/Excel/Writer. Strangely, all international characters (like French accentuated characters) show correctly in the web pages, but they don't in the excel export. I have tried several combinations of column types in mysql, of policies in excel, of charset setup in Spreadsheet/Excel/Writer (using $worksheet->setInputEncoding('utf-8') for exampel), with no success. Here is the piece of code which I identified in phpBT which does the excel export. As you can see, I have added (identified with VB comments) a setVersion and a setInput Encoding. As you may also noticed, I have added hard coded accentuated data in an extra column, just to try to figure out if the problem comes from the Spreadsheet/Excel/Writer or from the data coming from the database. (see the essai accent éùà). This last column shows OK... I'm now stuck. Does the issue come from Spreadsheet/Excel/Writer? Seems not... Does it come from mysql? Seems not (as it shows OK in the html pages)... Does it come from somewhere else???? Thanks all for your help. Please note I am using : EasyPhp v1.8.01 Spreadsheet/Excel/Write v0.9.1 OLE 0.5 Code extract :

        include_once('Spreadsheet/Excel/Writer.php');
        $workbook = new Spreadsheet_Excel_Writer();
        $workbook->send('buglist.xls');

//VB

        $workbook->setVersion(9);

//VB

        error_reporting(0);
        $boldformat =& $workbook->addformat(array('bold' => 1));
        $worksheet =& $workbook->addworksheet('buglist');

//VB

        $worksheet->setInputEncoding('utf-8');

//VB

        $row = 0;
        for ($i = 0, $colcount = count($fields); $i < $colcount; $i++) {
                        $worksheet->write($row, $i, $titles[$i], $boldformat);
        }
        $row++;
        for ($i = 0, $bugcount = count($data); $i < $bugcount; $i++) {
                for ($j = 0; $j < $colcount; $j++) {
                        $worksheet->setInputEncoding('utf-8');
                        $worksheet->write($row, $j, format_spreadsheet_col($data[$i][$fields[$j]], $fields[$j]));
                }
                $worksheet->write($row, $j, "essai accent éùà" . CHARSET,$boldformat);
                $row++;
        }
        $worksheet->freezepanes(array(1, 0));
        $workbook->close();

See this comment in the PHP manual: http://ca3.php.net/manual/en/ref.mbstring.php#62787 It's for sending CSV-formatted text to Excel but it might be useful to you, as well, somehow. -- snip -- UTF-16LE solution for CSV for Excel by Eugene Murai works well: $unicode_str_for_Excel = chr(255).chr(254).mb_convert_encoding( $utf8_str, 'UTF-16LE', 'UTF-8'); However, then Excel on Mac OS X doesn't identify columns properly and its puts each whole row in its own cell. In order to fix that, use TAB "\\t" character as CSV delimiter rather than comma or colon. You may also want to use HTTP encoding header, such as header( "Content-type: application/vnd.ms-excel; charset=UTF-16LE" ); -- snip -- This requires that PHP was compiled with multi-byte character support, however. brian