欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  php教程

利用PHPExcel转Excel饼图

程序员文章站 2022-05-07 20:01:26
...
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