php - 如何使用 php 将包含印地语文本的表导出到 Excel 中？

Question

这个问题已经有答案了:

How can I output a UTF-8 CSV in PHP that Excel will read properly? (26 个回答)

已关闭 8 年前。

我正在尝试使用 php 将印地语数据导出到 Excel 中。导出印地语数据后，它以不同的格式显示“????????? ??????”。我之前的代码是

<?php
include 'config.php';
setlocale(LC_ALL, "en_US.UTF-8");
$DB_TBLName = "feedback"; //MySQL Table Name   
$filename = "feedback";         //File Name
/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/    
//create MySQL connection   
$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database   
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno()); 

//execute query 

$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); 
$file_ending = "xls";

//setlocale(LC_ALL, "en_US.UTF-8");

//$result=mb_convert_encoding($result, 'ISO-8859-13','UTF-8');

/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";

}

print("\n");    
//end of printing column names  
//start while loop to get data
    while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
                if(!isset($row[$j]))
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".$sep;
            else
                $schema_insert .= "".$sep;
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "\n";
    } 


    //header info for browser

    header("Content-Type: application/vnd-ms-excel");
    header("Content-Disposition: attachment; filename=$filename.xls");  
    header("Pragma: no-cache"); 
    header("Expires: 0");

?>

经过一些研究后，有人建议在从表中获取数据之前添加字符集。我添加了行 mysql_set_charser('utf-8') 。现在它以不同的格式导出णतšà¤¤à¥‚छठछतम，但我没有得到印地语版本.

谢谢。

Answer 1

使用 Php 将 UTF-8 编码数据从 MySQL 导出到 Excel
本手册演示了如何将 UTF-8 编码的数据从 MySQL 导出到 Excel。就我而言，我尝试将印地语数据导出到 Excel 中，效果很好。我们可以通过两种方式导出
1) 手动导出和
2) 使用 php 以编程方式导出。

1) 手动导出:
以下步骤是从数据库手动导出。
1) 从数据库导出时将导出的文件另存为csv或xls
2)打开Excel
3) 使用Data导入数据
-->导入外部数据
-->导入数据
4) 选择文件类型“csv”或“xls”并浏览到您的文件
5) 在导入向导中将 File_Origin 更改为“65001 UTF”(或选择正确的语言字符标识符)
6) 将分隔符更改为逗号
7) 选择导入位置并完成
这样特殊字符应该正确显示。

2) 使用 php 以编程方式导出:
在 php 中我们需要遵循两个步骤。
第1步:在从数据库获取数据之前添加mysql_set_charset(“utf8”)。
例如:

mysql_set_charset("utf8");
$result = @mysql_query("Select * from $DB_TBLName",$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());    

第 2 步:现在将 BOM(字节顺序标记)代码添加到 header 。
例如:

header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
echo "\xEF\xBB\xBF";

php 中的最终代码:

<?php
include 'config.php';
$filename = "student_details";         //File Name
//create MySQL connection   
$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database   
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());   
//execute query 
mysql_set_charset("utf8");
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());    
$file_ending = "xls";
//header info for browser
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
echo "\xEF\xBB\xBF";
/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . ",";
}
print("\n");    
//end of printing column names  
//start while loop to get data
    while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
            if(!isset($row[$j]))
                $schema_insert .= "NULL".",";
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".",";
            else
                $schema_insert .= "".",";
        }
        $schema_insert = str_replace(","."$", "", $schema_insert);
        $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "\n";
    } 
?>