如何解決phpexcel匯出xls亂碼的問題-php教程-PHP中文網

如何解決phpexcel匯出xls亂碼的問題

*文

發布： 2023-03-18 22:30:02

原創

1523 人瀏覽過

如何解決phpexcel匯出xls亂碼的問題？下面小編就為大家帶來一篇完美解決phpexcel匯出到xls檔案出現亂碼的問題。小編覺得蠻不錯的，現在就分享給大家，也給大家做個參考。一起跟著小編過來看看吧，希望對大家有幫助。

解決方法如下：

<?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;);
?>

登入後複製

在header() 前面加上ob_end_clean() 函數，清除緩衝區, 這樣就不會亂碼了!