phpexcel setUseBOM unavailable

Topics: Developer Forum, Project Management Forum, User Forum
Sep 3, 2012 at 8:53 AM

$objPHPExcel = new PHPExcel();
$filename = date('Ymd-His').".csv";
$sortarr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
$data = array(
    "0"=>array('名字','地址','電話號碼','年齡'),
    "1"=>array('布賴恩','123假聖','一十八億〇三十零萬〇三百九','18'),
    "2"=>array('阿蘭圖靈','美國','62 09 3928二千八百三十','83')
);
$row = 1;
foreach($data as $k=>$v)
{
    $col = 1;               
    foreach($v as $kk => $vv)
    {
        $position = $sortarr[$col-1].$row;
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($position, $vv);
        $col++;
    }
    $row ++;
}
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type:application/csv;charset=UTF-8');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
$objWriter->setUseBOM(true);
$objWriter->save('php://output');

It show unreadable code when I view csv file via office 2007.
can anyone help me?

Coordinator
Sep 3, 2012 at 11:50 AM

If you open csv file with notepad, is the encoding correct?

Sep 4, 2012 at 12:40 AM

Thanks for your reply.

Yes, it is encoding correct when i open the csv file with notepad, here is the image :

Coordinator
Sep 4, 2012 at 6:42 AM

So PHPExcel is creating the file correctly. It is a valid CSV file. It opens correctly in MS Excel 2010. It imports correctly into Excel2007 if I open Excel and then select file/open (but fails to recognise the BOM if I simply open the file by double-clicking). I'm really not certain what I can do to fix Excel 2007 and get it to recognise and interpret the BOM correctly when the file is opened with a double-click.

Why not save the file as an xls or xlsx instead?

Sep 4, 2012 at 9:03 AM

For user convenience, he could select xls / xlsx / csv file to download when export data, above code is a example.

If PHPExcel can't fix the problem, Mabye export data to xls / xlsx with PHPExcel, export data to csv with no php class, I  can fix it.

Thanks for your help.

Coordinator
Sep 4, 2012 at 10:56 AM

The problem isn't PHPExcel, you confirm yourself that the CSV file is correctly encoded when opened in Notepad.... the problem is a fault in MS Excel 2007... so PHPExcel can't fix it. Somehow, I doubt that you will find a solution although I'll try and think of something that might help.

Sep 5, 2012 at 1:17 AM

Result is blelow and it works fine.

here is the code:

<?php
function export_csv($data,$file_name='')
{
    $file_name = $file_name.'_'.date('YmdHi').'.csv';
    $encoded_filename  = urlencode($file_name);
    $encoded_filename  = str_replace("+","%20",$encoded_filename );
    $content = array_to_string($data);
    header('Cache-control: private');
    $ua = $_SERVER["HTTP_USER_AGENT"];
    if (preg_match("/MSIE/", $ua)) {
        header('Content-Disposition: attachment; filename="' . $encoded_filename . '"');
    }
    else if (preg_match("/Firefox/", $ua)) {
        header('Content-Disposition: attachment; filename*="utf8\'\'' . $file_name . '"');
    }
    else {
        header('Content-Disposition: attachment; filename="' . $file_name . '"');
    }
    if(function_exists('mb_convert_encoding')){
        header('Content-type: text/csv; charset=UTF-16LE');
        echo(chr(255).chr(254));
        echo(mb_convert_encoding($content,"UTF-16LE","UTF-8"));
        exit;
    }
}

function array_to_string($result)
{
    if(empty($result)){
        exit("this is no data");
    }
    $size_result = count($result);
    $data = "";
    for($i = 0 ; $i < $size_result ;  $i++) {
        $data .= $result[$i]."\n";
    }
    return $data;
}

$data = array('你好','hello','Últimos','電話號碼');
export_csv($data,"mycsv");
?>

Coordinator
Sep 5, 2012 at 1:18 PM

Alright, I've added a setExcelCompatibility(TRUE) option to the CSV Writer that will generate CSV files encoded in a way that they can be opened with a double-click (or direct open) with MS Excel 2007 and below. Code is in github develop branch.

Sep 10, 2012 at 2:17 AM

Great, I have checked code is in github develop branch, and test it with new class, it works fine now, good job ~. ^_^, thank you !

Jun 20, 2013 at 7:59 AM
Edited Jun 20, 2013 at 8:04 AM
Hi,

I have that kind of problem with Turkish characters. And although I set the php script to UTF8 without BOM, in notepad the result looks ANSI. Here is my code:
<?php
$Host = "localhost";
$User = "xxx";
$Password = "";
$DBName = "mydb";
$TableName = "satislar";

$link = mysql_connect ($Host, $User, $Password) or die('Could not connect: ' . mysql_error());
mysql_select_db($DBName) or die('Could not select database');
//mysql_set_charset($link, 'utf8');//if not by default

$select = "SELECT * FROM satislar";
$export = mysql_query($select);
$fields = mysql_num_fields($export);
    
for ($i = 0; $i < $fields; $i++) {
$csv_output .= mysql_field_name($export, $i) . "\t";
}

while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=Aktif2000_Tum_Satislar.xls");
header("Pragma: no-cache");
header("Expires: 0");
print $csv_output."\n".$data;
exit;
?>
Could you tell me how you solved it or give me the link to solution?