利用PHPExcel轉Excel餅圖_PHP教程

PHPExcel處理Excel檔真是個強大的工具，我有張報表，帶餅圖，需要轉成Excel, PHPExcel有一個相關的例子，參考並修改後實現了這個效果，

可以讓使用者在點擊下載過程中產生和下載Excel檔，並在Excel中檔產生餅圖。

對其例子主要做了兩方面的修改：

1. 改成從MySQL資料庫取資料

2. 加上了中文檔案名稱在部份瀏覽器，如IE下，下載時名字亂碼的解決方案.

PHP報表如下:

轉成xls的:

<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KPHA+tPrC68jnz8I6PC9wPgo8cD4gPHByZSBjbGFzcz0="brush:java;">getProperties()->setCreator("XiongChuanLiang") ->setLastModifiedBy("XiongChuanLiang") ->setTitle("匯總表");$objActSheet = $objPHPExcel->getActiveSheet(); $objActSheet->getColumnDimension('A')->setWidth(50); $objActSheet->getColumnDimension('B')->setWidth(50); $objActSheet->getRowDimension(1)->setRowHeight(30); $objActSheet->getRowDimension(2)->setRowHeight(16); $objActSheet->mergeCells('A1:C1'); $objActSheet->mergeCells('A2:C2'); //設定置中對齊 $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objFontA1 = $objActSheet->getStyle('A1')->getFont(); $objFontA1->setSize(18); $objFontA1->setBold(true); /////////////////////////////////////////////////////////////////////////$sql = mysql_query("SELECT * AS state_name, count( * ) AS stat_count FROM ( ...... ) k GROUP BY status ORDER BY status "); $info = mysql_fetch_array($sql); $objActSheet->setCellValue('A1', '匯總表'); if(strlen( trim( $sdev_model)) > 0 ) { $objActSheet->setCellValue('A2',"型號:xxxxxx"); } $row=3; $objActSheet->setCellValue('A'.$row,'狀態'); $objActSheet->setCellValue('B'.$row, '總數量'); $row=4; do{ $objActSheet->setCellValueExplicit('A'.$row,$info['state_name'],PHPExcel_Cell_DataType::TYPE_STRING); $objActSheet->setCellValueExplicit('B'.$row,$info['stat_count'],PHPExcel_Cell_DataType::TYPE_NUMERIC); $objActSheet->setCellValue('A'.$row, $info['state_name']); $objActSheet->setCellValue('B'.$row, $info['stat_count']); $row++; }while($info=mysql_fetch_array($sql));///////////////////////////////////////////////////////////////////////// for ($currrow = 3; $currrow < $row; $currrow++) { //設定邊框 $objActSheet->getStyle('A'.$currrow)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('A'.$currrow)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('A'.$currrow)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('A'.$currrow)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('B'.$currrow)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('B'.$currrow)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('B'.$currrow)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet->getStyle('B'.$currrow)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); }////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// Set the Labels for each data series we want to plot// Datatype// Cell reference for data// Format Code// Number of datapoints in series// Data values// Data Marker$dataseriesLabels1 = array( new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$3', NULL, 1), );// Set the X-Axis Labels// Datatype// Cell reference for data// Format Code// Number of datapoints in series// Data values// Data Marker$xAxisTickValues1 = array( new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$4:$A$'.$row, NULL, 4), );// Set the Data values for each data series we want to plot// Datatype// Cell reference for data// Format Code// Number of datapoints in series// Data values// Data Marker$dataSeriesValues1 = array( new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$4:$B$'.$row, NULL, 4), );// Build the dataseries$series1 = new PHPExcel_Chart_DataSeries( PHPExcel_Chart_DataSeries::TYPE_PIECHART, // plotType PHPExcel_Chart_DataSeries::GROUPING_STANDARD, // plotGrouping range(0, count($dataSeriesValues1)-1), // plotOrder $dataseriesLabels1, // plotLabel $xAxisTickValues1, // plotCategory $dataSeriesValues1 // plotValues);// Set up a layout object for the Pie chart$layout1 = new PHPExcel_Chart_Layout();$layout1->setShowVal(TRUE);$layout1->setShowPercent(TRUE);// Set the series in the plot area$plotarea1 = new PHPExcel_Chart_PlotArea($layout1, array($series1));// Set the chart legend$legend1 = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);$title1 = new PHPExcel_Chart_Title('匯總表');// Create the chart$chart1 = new PHPExcel_Chart( 'chart1', // name $title1, // title $legend1, // legend $plotarea1, // plotArea true, // plotVisibleOnly 0, // displayBlanksAs NULL, // xAxisLabel NULL // yAxisLabel - Pie charts don't have a Y-Axis);// Set the position where the chart should appear in the worksheet$row += 2;$chart1->setTopLeftPosition('A'.$row);$row += 10;$chart1->setBottomRightPosition('C'.$row);// Add the chart to the worksheet$objPHPExcel->getActiveSheet()->addChart($chart1);//////////////////////////////////////////////////////////////////////////////////////////// Set active sheet index to the first sheet, so Excel opens this as the first sheet$objPHPExcel->setActiveSheetIndex(0);$filename = '匯總表_'.date("Y_m_d").".xlsx";// Redirect output to a client’s web browser (Excel2007)header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//header('Content-Disposition: attachment;filename="'.$filename.'"'); //devrent.xlsx//////////////////////////////////////////處理中文檔案名稱亂碼問題$ua = $_SERVER["HTTP_USER_AGENT"]; $encoded_filename = urlencode($filename);$encoded_filename = str_replace("+", "%20",$encoded_filename);header('Content-Type: application/octet-stream');if (preg_match("/MSIE/", $ua)) { header('Content-Disposition: attachment;filename="' . $encoded_filename . '"');}else if (preg_match("/Firefox/", $ua)){ header('Content-Disposition: attachment; filename*="utf8\'\'' . $filename . '"');}else { header('Content-Disposition: attachment; filename="' . $filename . '"');}////////////////////////////////////////header('Cache-Control: max-age=0');// If you're serving to IE 9, then the following may be neededheader('Cache-Control: max-age=1');// If you're serving to IE over SSL, then the following may be neededheader ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the pastheader ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modifiedheader ('Cache-Control: cache, must-revalidate'); // HTTP/1.1header ('Pragma: public'); // HTTP/1.0$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');$objWriter->setIncludeCharts(TRUE);$objWriter->save('php://output');exit; 另要注意的地方是，Excel的餅圖，通過指定其標籤，值所對應的單元格範圍，自動產生，所以主要是在代碼中計算好。另在非Windows伺服器，產生會失敗。

MAIL: xcl_168@aliyun.com

BLOG: http:/./blog.csdn.ent/xcl168

http://www.bkjia.com/PHPjc/766057.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/766057.htmlTechArticlePHPExcel處理Excel檔真是個強大的工具，我有張報表，帶餅圖，需要轉成Excel, PHPExcel有一個相關的例子，參考並修改後實現了這個效果， 可以...

