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
– Liam Spencer, Commented Aug 12, 2010 at 8:07

Community · Accepted Answer · 2017-05-23 12:17:26Z

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, 2017 at 12:17

CommunityBot

11 silver badge

answered Aug 12, 2010 at 8:57

Benjamin Cremer

4,8221 gold badge26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

edib Over a year ago

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 Over a year ago

when I change the code like this it worked. $fh = fopen('foo.xls', "wb"); // Prepend BOM fwrite($fh, "\xFF\xFE");

Dashrath Over a year ago

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"

edib Over a year ago

@Dashrath yes, it is actually another format, an html file, but who cares! Excel can open and edit it.

user2140705 · Accepted Answer · 2013-03-06 15:57:17Z

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();

wimvds · Accepted Answer · 2010-08-12 08:16:36Z

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, 2010 at 8:16

wimvds

12.8k2 gold badges43 silver badges42 bronze badges

2 Comments

edib Over a year ago

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.

Mark Baker Over a year ago

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

Collectives™ on Stack Overflow

exporting php output as excel

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related