php is garbled in excel everywhere

PHP is a scripting language widely used in web development. It has special flexibility and operability. However, some common problems, such as garbled characters when exporting Excel files, cause a lot of problems to developers. inconvenient. So, in this article, we will delve into this issue and provide some solutions that will enable you to resolve this troublesome issue easily.

1. Understand how Excel files are encoded

Excel files are stored in binary format, not text like HTML or JSON. Therefore, when reading and writing Excel files, you need to pay special attention to how they are encoded. The most common encodings are UTF-8 and GBK.

UTF-8 is currently the most commonly used encoding method. It supports multiple languages ​​and character sets and is especially suitable for international applications. GBK is a Chinese encoding method whose character set only contains Simplified Chinese and Traditional Chinese characters.

2. Set the encoding method of PHP and Excel files

When using PHP to export Excel files, you need to ensure that both the script file and the Excel file use the same encoding method. You can use the PHP built-in function mb_convert_encoding() to convert content from one encoding to another.

For example, if you use UTF-8 encoding in PHP, and your Excel file uses GBK encoding, you can convert the content to GBK encoding:

$data = "Chinese Content" ;
$data = mb_convert_encoding($data, "GBK", "UTF-8");

You can also use the setCellValueExplicit() method in the PHPExcel library to set the text in the Excel cell to Specified encoding method:

$objPHPExcel->getActiveSheet()->setCellValueExplicit('A1', 'Chinese content', PHPExcel_Cell_DataType::TYPE_STRING,'GBK');

3. Use the correct HTTP header to set the encoding

When you output the Excel file to the browser, you need to use the correct HTTP header to set the encoding method. You can add the following to ensure the browser uses UTF-8 encoding:

header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header(' Content-Disposition: attachment;filename="example.xls"');
header('Cache-Control: max-age=0');

4. Avoid using unsupported characters

Using some special characters, such as emoticons or unconventional characters, in Excel files may cause garbled characters. Therefore, care should be taken to avoid using these characters when creating Excel files.

5. Using the PHPExcel library

PHPExcel is a popular PHP library for reading and writing Excel files. It includes various file reading methods and export methods in multiple formats, including PDF and CSV, etc. In addition, PHPExcel also has built-in encoding conversion and automatic calculation of column widths and cell formats, etc., which makes it one of the preferred libraries for processing Excel files.

For example, to write data to an Excel file and export it to XLS format, you can use the following code:

require_once 'PHPExcel.php';

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Name');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Gender');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Age');

$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Zhang San');
$objPHPExcel->getActiveSheet()->setCellValue('B2', 'Male') ;
$objPHPExcel->getActiveSheet()->setCellValue('C2', '20');

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="example.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory: :createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

Conclusion

When processing Excel files in PHP , we need to always keep in mind the encoding used and make sure everything is encoded the same way. At the same time, care should be taken to avoid using special characters and use appropriate library functions to process Excel files. By following these best practices, you should be able to easily resolve Excel garbled issues and create efficient Excel files.

The above is the detailed content of php is garbled in excel everywhere. For more information, please follow other related articles on the PHP Chinese website!