exporting php output as excel

Question

include_once 'mysqlconn.php';
include_once "functions.php";
$filename = $_GET['par'].".xls";
header("Content-type: application/x-msexcel"); 
header('Content-Disposition: attachment; filename="'.basename($filename).'"'); 
if ($_GET['i'] == "par1") {
  func1();
} else if ($_GET['i'] == "par2") {
  echo "şşşıııİİİ";
  func2();  
} else if ($_GET['i'] == "par3") {  
  echo "şşşıııİİİ";
  func3();  
}

this is my export2excel.php file and func1,2,3 are in functions.php file and produces table output all work well except character encoding in a strange way. I am using utf-8 encoding for all my files. 2nd else if statement above produces healthy encoded output but rest 2 are encodes my output with strange characters like "BÃœTÃ‡E Ä°Ã‡Ä°". it is "BÜTÇE İÇİ" in turkish.

in short. same files, same encoding, same database but different results.

any idea?

Use the "code" tags around your code so it is actually readable! — Liam Spencer, Aug 12 '10 at 08:07

score 19 · Accepted Answer · edited May 23 '17 at 12:17

19

Excel uses UTF-16LE + BOM as default Unicode encoding.
So you have to convert your output to UTF-16LE and prepend the UTF-16LE-BOM "\xFF\xFE".

Some further information:

Instead I would use one of the existing libraries

PHP Excel Extension PECL extension by Ilia Alshanetsky (Core PHP Developer & Release Master)
Spreadsheet_Excel_Writer PEAR Package
PHPExcel

Edit:
Some code that could help if you really not want to use an existing library

<?php
$output = <<<EOT
<table>
    <tr>
        <td>Foo</td>
        <td>IñtërnâtiônàlizætiøöäÄn</td>
    </tr>
    <tr>
        <td>Bar</td>
        <td>Перевод русского текста в транслит</td>
    </tr>
</table>
EOT;

// Convert to UTF-16LE
$output = mb_convert_encoding($output, 'UTF-16LE', 'UTF-8'); 

// Prepend BOM
$output = "\xFF\xFE" . $output;

header('Pragma: public');
header("Content-type: application/x-msexcel"); 
header('Content-Disposition: attachment;  filename="utf8_bom.xls"');

echo $output;

edited May 23 '17 at 12:17

Community

1
1

answered Aug 12 '10 at 08:57

Benjamin Cremer

4,842
1
24
30

In windows everything works fine but In redhat linux line fwrite($fh, b"\xFF\xFE"); produces error: "PHP Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /var/www/...." why is it so? – edib Aug 12 '10 at 14:30
when I change the code like this it worked. $fh = fopen('foo.xls', "wb"); // Prepend BOM fwrite($fh, "\xFF\xFE"); – edib Aug 12 '10 at 14:42
i am using ms excel 2007 . and there cells are not visible when i export file. it gives message before open that "the file you are trying to open has different format than specified by the extension" – Dashrath Mar 16 '13 at 10:31
@Dashrath yes, it is actually another format, an html file, but who cares! Excel can open and edit it. – edib Jan 01 '15 at 10:55

score 1 · Answer 2 · answered Mar 06 '13 at 15:57

if anyone is trying to use the excel_writer in moodle and is getting encoding issues with output - say if you're developing a report that has a url as data in a field - then in this instance to simply fix this issue I wrapped the data in quotes so it at least opened up in excel here's my example:

// Moodles using the PEAR excel_writer export

$table->setup();

$ex=new table_excel_export_format($table);

$ex->start_document( {string} );
$ex->start_table( {string} );

// heading on the spreadsheet
$title = array('Report Title'=>'Report 1');
$ex->add_data($title);
// end heading

$ex->output_headers( array_keys($table->columns) );

**foreach($data as $row){

        $string="'".trim($row->resname,"'")."'";
        $row->resname=$string;
        $ex->add_data( $table->get_row_from_keyed($row) );
}**

$ex->finish_table();
$ex->finish_document();

score 0 · Answer 3 · answered Aug 12 '10 at 08:16

0

Excel uses UTF-16LE as the default encoding. So you should either convert UTF-8 to UTF-16LE yourself or use one of the tried and tested Excel PHP libs instead of trying to reinvent the wheel. I would recommend using PHPExcel...

answered Aug 12 '10 at 08:16

wimvds

12,790
2
41
42

I have only 8 pages to convert excel. and as I read from docs, phpexcel classes can only convert cell by cell conversions but not raw php html output. so I will have to recode all things for phpexcel and it classes' size is 7 mB. It is unnecassary I think. – edib Aug 12 '10 at 09:16
I'm not sure exactly what you're trying to do, but PHPExcel can generate "raw" html output... as for the library size, I've commented on that elsewhere on SO.. Note that 4.5 MB of that 7MB is the tcpdf library that PHPExcel includes to support export to PDF... if you don't need PDF, then delete it, and the disk footprint is less than 3MB – Mark Baker Aug 12 '10 at 09:47

exporting php output as excel

3 Answers3