php 使用 UTF-8 编码将数据从 MySQL 导出到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17108249/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export data from MySQL to Excel with UTF-8 encoding
提问by bukowski
I have a mysql row with utf8_general_cicollation, when I export it to csv, instead of correct utf-8 characters I get ?…ā|Ietc, how to make excel understand UTF-8 encoding here is my code:
我有一个带有utf8_general_ci排序规则的 mysql 行，当我将它导出到 csv 时，我得到的不是正确的 utf-8 字符?…ā|I等等，如何让 excel 理解这里的 UTF-8 编码是我的代码：
$conn = mysql_connect('localhost', 'root', 'asdfggh') or die(mysql_error());
mysql_query("SET CHARACTER SET utf8");
mysql_query("SET NAMES utf8");
mysql_select_db('table_name', $conn) or die(mysql_error($conn));
$query = sprintf('SELECT * FROM sudraba_birzs');
$result = mysql_query($query, $conn) or die(mysql_error($conn));
header('Content-Encoding: UTF-8');
header('Content-type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="'.date("d-m-Y_H:i") . '.csv'.'"');
echo "\xef\xbb\xbf";
$row = mysql_fetch_assoc($result);
if ($row) {
echocsv(array_keys($row));
}
while ($row) {
echocsv($row);
$row = mysql_fetch_assoc($result);
}
function echocsv($fields)
{
$separator = '';
foreach ($fields as $field) {
if (preg_match('/\r|\n|,|"/', $field)) {
$field = '"' . str_replace('"', '""', $field) . '"';
}
echo $separator . $field;
$separator = ',';
}
echo "\r\n";
}
How to export it to get all characters display correctly (make Excel understand utf-8) and to maintain table layout too(with rows and columns)
如何导出它以正确显示所有字符（使 Excel 理解 utf-8）并保持表格布局（带有行和列）
回答by álvaro González
You are generating CSV, which is basically a plain text file. There's no way to specify encoding information in such kind of files. Most text editors implement (better or worse) encoding auto-detection. Excel doesn't. Excel will simply assume ANSI when you right-click on a CSV file. (You need to use the "Open" menu in order to be prompted about encoding.)
您正在生成 CSV，它基本上是一个纯文本文件。无法在此类文件中指定编码信息。大多数文本编辑器实现（更好或更坏）编码自动检测。Excel 没有。当您右键单击 CSV 文件时，Excel 将简单地假定为 ANSI。（您需要使用“打开”菜单才能收到有关编码的提示。）
Your only option left (apart from switching to another output format) is converting data to ANSI, either with mb_convert_encoding()or with iconv(). But now you have another problem: ANSI is not a real encoding, it basically means "whatever encoding is set in myWindows computer". You first have to find out the typical encoding most of your users have. That mostly depends on the country. For instance, many Western Europe countries use Win-1252.
您剩下的唯一选择（除了切换到另一种输出格式）是使用mb_convert_encoding()或iconv()将数据转换为 ANSI 。但现在你有另一个问题：ANSI 不是真正的编码，它基本上意味着“我的Windows 计算机中设置的任何编码”。您首先必须找出大多数用户使用的典型编码。这主要取决于国家。例如，许多西欧国家使用 Win-1252。
回答by manuelmatas
I had the same problem (common problem in databases with spanish language). I wrote this and it worked:
我遇到了同样的问题（西班牙语数据库中的常见问题）。我写了这个并且它起作用了：
This is a Class that connects with the database and the functions will do whatever you want using mysqli and PHP. In this case, calling this class (require or include), just use the "downloadCsv()" function.
这是一个与数据库连接的类，函数将使用 mysqli 和 PHP 执行您想要的任何操作。在这种情况下，调用这个类（需要或包含），只需使用“downloadCsv()”函数。
As an example, this would be the "class.php" file:
例如，这将是“class.php”文件：
<?php
class DB{
private $con;
//this constructor connects with the database
public function __construct(){
$this->con = new mysqli("Your_Host","Your_User","Your_Pass","Your_DatabaseName");
if($this->con->connect_errno > 0){
die('There was a problem [' . $con->connect_error . ']');
}
}
//create the function that will download a csv file from a mysqli query
public function downloadCsv(){
$count = 0;
$header = "";
$data = "";
//query
$result = $this->con->query("SELECT * FROM Your_TableName");
//count fields
$count = $result->field_count;
//columns names
$names = $result->fetch_fields();
//put column names into header
foreach($names as $value) {
$header .= $value->name.";";
}
}
//put rows from your query
while($row = $result->fetch_row()) {
$line = '';
foreach($row as $value) {
if(!isset($value) || $value == "") {
$value = ";"; //in this case, ";" separates columns
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . ";"; //if you change the separator before, change this ";" too
}
$line .= $value;
} //end foreach
$data .= trim($line)."\n";
} //end while
//avoiding problems with data that includes "\r"
$data = str_replace("\r", "", $data);
//if empty query
if ($data == "") {
$data = "\nno matching records found\n";
}
$count = $result->field_count;
//Download csv file
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=FILENAME.csv");
header("Pragma: no-cache");
header("Expires: 0");
echo $header."\n".$data."\n";
}
?>
After creating the "class.php" file, in this example, use that function on "download.php" file:
创建“class.php”文件后，在本例中，在“download.php”文件上使用该函数：
<?php
//call the "class.php" file
require_once 'class.php';
//instantiate DB class
$export = new DB();
//call function
$export->downloadCsv();
?>
After download, open the file with MS Excel.
下载后，用 MS Excel 打开文件。
I hope this help you, I think I wrote it well, I didn't feel comfortable with the text and code field.
我希望这对您有所帮助，我认为我写得很好，我对文本和代码字段感到不舒服。
回答by mee
As an alternate and much sipmler solution, you can try this, i am using it for a few years by now, and never had a problem with it.
作为替代的多 sipmler 解决方案，您可以试试这个，我已经使用它几年了，从来没有遇到过问题。
db_connect.php
db_connect.php
<?php
$mysqli = new mysqli($mysqli_host, $mysqli_user, $mysqli_pass, $mysqli_db);
if ($mysqli->connect_errno)
{
$debug = $debug.'<br/>Failed to connect to MySQL: (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error;
}
else
{
$debug = $debug.'<br/>Connected to '. $mysqli->host_info;
}
?>
Export function
导出功能
function export($query_exp,$name)
{
require '../lib/db_config.php';
require '../lib/db_connect.php';
$filename = $name.' - '.date('Y.m.d').'.xls'; /*set desired output file name here*/
function cleanData(&$str)
{
$str = preg_replace("/\t/", "\t", $str);
$str = preg_replace("/\r?\n/", "\n", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
if ($str=='') {$str='-';}
}
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");
$flag = false;
if ($result = $mysqli->query($query_exp))
{
if ($result->num_rows>0)
{
$result->data_seek(0);
while ($row = $result->fetch_assoc())
{
if(!$flag)
{
print implode("\t", array_keys($row)) . "\r\n";
$flag = true;
}
array_walk($row, 'cleanData');
print implode("\t", array_values($row)) . "\r\n";
}
}
else { $debug = $debug.'<br/>Empty result'; /*DEBUG*/ }
}
else { $debug = $debug.'<br/>Oups, Query error!<br/>Query: '.$query_exp.'<br/>Error: '.$mysqli->error.'.'; /*DEBUG*/ }
require '../lib/db_disconnect.php';
}
You can call the function as:
您可以将函数调用为：
export('SELECT * FROM SAMPLE WHERE 1;','desired_file_name.extension')
