查看: 2658|回复: 4

Converting UTF-8 to ANSI for CSV Export

[复制链接]

Crystal-Mist

2009-3-10 04:46 PM

因使用的系統某些原因必須使用utf8環境，其產生的csv檔中文字格式也是utf8的，但用excel開啟時會變成亂碼，用work開啟卻ok
不知是excel不認得utf8還是怎樣

將utf8的csv用記事本或是word重新開啟再另存新檔成ansi格式後，excel就能開了
但是將此種方法告訴用戶實在是有點不負責任，不知道有什麼方式能讓excel直接就能讀取utf8格式的csv呢？

code :

<?php
ob_start();
session_start();

function encoding_conv($var, $enc_out, $enc_in='utf-8') {
$var = htmlentities($var, ENT_QUOTES, $enc_in);
return html_entity_decode($var, ENT_QUOTES, $enc_out);
}

include_once ("include/include_file.php");
include_once ("../include/my_admin_security.php");

$table = 'tblappointment';
$file = 'consultation';

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].", ";
$i++;
}
}
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table."");

while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].", ";

}
$csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
$csv_output = encoding_conv($csv_output, 'Windows-1252', $enc_in);
print $csv_output;
exit;
?>

这个code是否有任何错误？因为这无法执行转换的功能

[ 本帖最后由 Crystal-Mist 于 2009-3-10 05:32 PM 编辑 ]

Super-Tomato

2009-3-11 09:58 AM

原帖由 Crystal-Mist 于 2009-3-10 04:46 PM 发表
因使用的系統某些原因必須使用utf8環境，其產生的csv檔中文字格式也是utf8的，但用excel開啟時會變成亂碼，用work開啟卻ok
不知是excel不認得utf8還是怎樣

將utf8的csv用記事本或是word重新開啟再另存新檔 ...

function encoding_conv($var, $enc_out, $enc_in='utf-8') {
$var = htmlentities($var, ENT_QUOTES, $enc_in);
return html_entity_decode($var, ENT_QUOTES, $enc_out);
}

不明白你这段的用意，你该在 mysql_fetch_assoc 讀取之後把 $row['Field'] 直接用 mb_convert_string 轉換

回复 #2 Super-Tomato 的帖子

Crystal-Mist

2009-3-11 02:27 PM

感谢supertomato的提醒. 但之后，我改变 code 为 :

header("Content-type: application/vnd.ms-excel, charset=ISO-8859-1//TRANSLIT");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
$csv_output = mb_convert_encoding($csv_output, "UTF-8", "ISO-8859-1");
print $csv_output;
exit;

还是没有成功....
output example : ( æ˜¯ ) in database, ( Ã¦Â˜Â¯) in csv excel, 我希望结果是 ("是")

希望有excel 专家能帮助我... em0051

Super-Tomato

2009-3-12 06:55 AM

原帖由 Crystal-Mist 于 2009-3-11 02:27 PM 发表
感谢supertomato的提醒. 但之后，我改变 code 为 :

header("Content-type: application/vnd.ms-excel, charset=ISO-8859-1//TRANSLIT");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
...

<?php
ob_start();
session_start();

include_once ("include/include_file.php");
include_once ("../include/my_admin_security.php");

$table = 'tblappointment';
$file = 'consultation';

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= mb_convert_encoding($row['Field'], "UTF-8", "GB2312").", ";
$i++;
}
}
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table."");

while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++)
$csv_output .= mb_convert_encoding($row[$j], "UTF-8", "GB2312").", ";

$csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: text/csv");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");

print $csv_output;

?>

回复 #4 Super-Tomato 的帖子

Crystal-Mist

2009-3-12 11:46 AM

谢谢supertomato, 我已经尝试另一种方式解决这问题.

我部分的编码:
<?php
$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-Type: application/vnd.ms-excel");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("content-disposition: attachment;filename=".$filename.".xls");

iconv_set_encoding("internal_encoding", "ISO-8859-1");
iconv_set_encoding("output_encoding", "UTF-8");
ob_start("ob_iconv_handler");
?>
<html>
<?php
select * from table
?>
<head>
<title>Your Title</title>
<meta http-equiv="Content-Type" content="text/html; charset=GB2312">
</head>
........<?php echo iconv('utf-8','GB2312',$YourValue); ?>
</html>

em0005 用我整天创建的编码......em0014

返回列表

Converting UTF-8 to ANSI for CSV Export

回复 #2 Super-Tomato 的帖子

回复 #4 Super-Tomato 的帖子

浏览过的版块