How to solve the problem of garbled characters exported by php xls

In the process of php development, we often need to use the export function of excel. However, you may encounter a common problem when exporting, that is, the exported excel file appears garbled. In response to this situation, this article will introduce how to solve the problem of garbled characters exported from php xls.

1. Cause analysis

When exporting php excel, the main reasons for garbled characters are as follows:

1. Encoding issues: exported excel files and programs The encoding used in is inconsistent, resulting in garbled characters;
2. Special character problem: The exported data contains special characters, such as Chinese, English, etc., which may also cause garbled characters;
3. Improper use: During the export process In addition, improper handling of excel files may also lead to garbled characters.

2. Solution

1. Unified encoding

When using php to export excel, you need to ensure that the encoding of the exported excel file is consistent with the encoding used in the program. You can use the iconv or mb_convert_encoding function to convert the string encoding to ensure that the data is correctly converted between different encodings and avoid garbled characters. For example, if you take the data set out of the database, you need to convert it to UTF-8 encoding, and specify the output encoding as UTF-8 during the export process:

header("Content-type:text/html;charset=utf-8");
$data = array(); // 数据集
// 从数据库中获取数据集
$dsn = 'mysql:host=localhost;dbname=test;charset=utf8';
$pdo = new PDO($dsn, 'root', '');
$stmt = $pdo->query('SELECT * FROM user');
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
// 将数据集转为UTF-8编码
foreach ($data as &$row) {
    foreach ($row as $key => &$value) {
        $value = mb_convert_encoding($value, 'UTF-8', 'auto');
    }
}
unset($row);
// 导出Excel文件
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setTitle('title'); // 指定Excel文件标题
$objPHPExcel->getActiveSheet()->fromArray($data); // 将数据集导入Excel表格
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
header('Content-Type: application/vnd.ms-excel;charset=utf-8');
header('Content-Disposition: attachment;filename="example.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');

2. Use the htmlentities function

When exporting excel files, when the data contains html special characters such as ">, <, spaces, etc., you can use the htmlentities function to add escape characters in front of these special characters. The code example is as follows:

$data = array( 
    array("name", "gender", "age", "info"), 
    array("张三", 1, 18, "hello world"), 
    array("李四", 0, 20, "2021/06/23"), 
);
//遍历数据，将特殊字符进行转义
foreach($data as &$row){
    foreach($row as &$cell){
        $cell = htmlentities($cell, ENT_COMPAT, 'UTF-8');
    }
}
unset($cell);
unset($row);
//将数据写入到Excel文件中
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A1');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="example.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');</p>
<p>3. Specify the output format</p>
<p>When exporting excel with PHP, you can also specify the output format to solve the garbled problem. For example, when using PHPExcel to export an excel file, you can set the file format to UTF- 8 (specified through the $pdf->setUTF8(true) method) to avoid garbled characters caused by encoding problems. The sample code is as follows: </p>
<pre class="brush:php;toolbar:false">$data = array( 
    array("name", "gender", "age"), 
    array("张三", 1, 18), 
    array("李四", 0, 20), 
);
//将数据写入到Excel文件中
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A1');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->setUseUTF8(true); // 设置文件格式为UTF-8
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="example.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');

3. Conclusion

Exporting php xls The problem of garbled characters is a very common problem. Usually, this problem can be solved by ensuring that the encoding used by the data and the program is consistent, using the htmlentities function to escape special characters, or specifying the output format. In specific operations, for Different data sets and program environments require corresponding methods to solve the garbled problem in order to ensure the quality and usability of the final exported excel file.

The above is the detailed content of How to solve the problem of garbled characters exported by php xls. For more information, please follow other related articles on the PHP Chinese website!