What to do if php outputs garbled characters in excel

The solution to the garbled code output by php: first open the corresponding PHP file; then add the "ob_end_clean()" function in front of "header()" and clear the buffer to solve the garbled code problem.

#The operating environment of this article: Windows7 system, PHP7.1, Dell G3 computer.

Perfectly solve the problem of garbled characters when exporting phpexcel to xls files

The following editor will bring you an article that perfectly solves the problem of garbled characters when exporting phpexcel to xls files .

The solution is as follows:

<?php
include &#39;global.php&#39;;
$ids = $_GET[&#39;ids&#39;];
 
$sql = "select * from crm_cost_end where id in ( {$ids} )";
$result = $db->findAll($sql);
//echo $result[1][&#39;sn&#39;];
 
//创建一个excel对象
$objPHPExcel = new PHPExcel();
// Set properties
 
$objPHPExcel->getProperties()->setCreator("ctos")
    ->setLastModifiedBy("ctos")
    ->setTitle("Office 2007 XLSX Test Document")
    ->setSubject("Office 2007 XLSX Test Document")
    ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
    ->setKeywords("office 2007 openxml php")
    ->setCategory("Test result file");
 
//set width
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;A&#39;)->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;B&#39;)->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;C&#39;)->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;D&#39;)->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;E&#39;)->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;F&#39;)->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;G&#39;)->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;H&#39;)->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;I&#39;)->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;J&#39;)->setWidth(30);
 
//设置行高度
$objPHPExcel->getActiveSheet()->getRowDimension(&#39;1&#39;)->setRowHeight(22);
 
$objPHPExcel->getActiveSheet()->getRowDimension(&#39;2&#39;)->setRowHeight(20);
 
//set font size bold
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
$objPHPExcel->getActiveSheet()->getStyle(&#39;A2:J2&#39;)->getFont()->setBold(true);
 
$objPHPExcel->getActiveSheet()->getStyle(&#39;A2:J2&#39;)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle(&#39;A2:J2&#39;)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
 
//设置水平居中
$objPHPExcel->getActiveSheet()->getStyle(&#39;A1&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle(&#39;A&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle(&#39;B&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle(&#39;D&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle(&#39;F&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle(&#39;G&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle(&#39;H&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle(&#39;I&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
 
//
$objPHPExcel->getActiveSheet()->mergeCells(&#39;A1:J1&#39;);
 
// set table header content
$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue(&#39;A1&#39;, &#39;订单数据汇总 时间:&#39; . date(&#39;Y-m-d H:i:s&#39;))
    ->setCellValue(&#39;A2&#39;, &#39;订单ID&#39;)
    ->setCellValue(&#39;B2&#39;, &#39;下单人&#39;)
    ->setCellValue(&#39;C2&#39;, &#39;客户名称&#39;)
    ->setCellValue(&#39;D2&#39;, &#39;下单时间&#39;)
    ->setCellValue(&#39;E2&#39;, &#39;需求机型&#39;)
    ->setCellValue(&#39;F2&#39;, &#39;需求数量&#39;)
    ->setCellValue(&#39;G2&#39;, &#39;需求交期&#39;)
    ->setCellValue(&#39;H2&#39;, &#39;确认BOM料号&#39;)
    ->setCellValue(&#39;I2&#39;, &#39;PMC确认交期&#39;)
    ->setCellValue(&#39;J2&#39;, &#39;PMC交货备注&#39;);
 
// Miscellaneous glyphs, UTF-8
 
for ($i = 0; $i < count($result) - 1; $i++) {
  $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;A&#39; . ($i + 3), $result[$i][&#39;id&#39;]);
  $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;B&#39; . ($i + 3), $result[$i][&#39;realname&#39;]);
  $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;C&#39; . ($i + 3), $result[$i][&#39;customer_name&#39;]);
  $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;D&#39; . ($i + 3), $OrdersData[$i][&#39;create_time&#39;]);
  $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;E&#39; . ($i + 3), $result[$i][&#39;require_product&#39;]);
  $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;F&#39; . ($i + 3), $result[$i][&#39;require_count&#39;]);
  $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;G&#39; . ($i + 3), $result[$i][&#39;require_time&#39;]);
  $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;H&#39; . ($i + 3), $result[$i][&#39;product_bom_encoding&#39;]);
  $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;I&#39; . ($i + 3), $result[$i][&#39;delivery_time&#39;]);
  $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;J&#39; . ($i + 3), $result[$i][&#39;delivery_memo&#39;]);
  $objPHPExcel->getActiveSheet()->getStyle(&#39;A&#39; . ($i + 3) . &#39;:J&#39; . ($i + 3))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
  $objPHPExcel->getActiveSheet()->getStyle(&#39;A&#39; . ($i + 3) . &#39;:J&#39; . ($i + 3))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  $objPHPExcel->getActiveSheet()->getRowDimension($i + 3)->setRowHeight(16);
}
 
 
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle(&#39;订单汇总表&#39;);
 
 
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
 
 
// Redirect output to a client&#39;s web browser (Excel5)
<span style="color:#ff0000;">ob_end_clean();//清除缓冲区,避免乱码</span>
header(&#39;Content-Type: application/vnd.ms-excel&#39;);
header(&#39;Content-Disposition: attachment;filename="订单汇总表(&#39; . date(&#39;Ymd-His&#39;) . &#39;).xls"&#39;);
header(&#39;Cache-Control: max-age=0&#39;);
 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, &#39;Excel5&#39;);
$objWriter->save(&#39;php://output&#39;);
?>

Add the ob_end_clean() function in front of header() to clear the buffer, so that there will be no garbled characters!

[Recommended : "PHP Video Tutorial"]

The above is the detailed content of What to do if php outputs garbled characters in excel. For more information, please follow other related articles on the PHP Chinese website!