Home > Article > Backend Development > Convert Excel pie chart using PHPExcel_PHP tutorial
Convert Excel pie chart using PHPExcel_PHP tutorial
- WBOYOriginal
- 2016-07-13 10:30:211337browse
PHPExcel is a really powerful tool for processing Excel files. I have a report with a pie chart that needs to be converted into Excel. PHPExcel has a relevant example. I referenced and modified it to achieve this effect.
It allows users to generate and download Excel files during the download process, and generate pie charts in Excel.
The example has been modified in two main ways:
1. Change to fetch data from MySQL database
2. Added a solution for Chinese file names to be garbled when downloading under some browsers, such as IE.
The PHP report is as follows:
Rendering of converting to 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