利用PHPExcel转Excel饼图

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 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 needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header ('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