Use Phpexcel to turn Excel pie charts

Phpexcel processing Excel file is really a powerful tool, I have a report, with pie chart, need to turn into Excel, Phpexcel have a relevant example, reference and modified to achieve this effect,

Allows users to generate and download Excel files during the download process and generate pie charts in Excel mid-range.

There are two main modifications to the example:

1. Change from MySQL database to data access

2. Add the Chinese file name in some browsers, such as IE, download the name garbled solution.

The PHP report is as follows:

Effect map to XLS:

<喎?http: www.2cto.com kf ware vc " target="_blank" class="keylink"> Vcd4kpha+tprc68jnz8i6pc9wpgo8cd4gphbyzsbjbgfzcz0= "Brush:java;" > GetProperties ()->setcreator ("Xiongchuanliang")->setlastmodifiedby ("Xiongchuanliang")->settitle ("Summary table") ; $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"); Set the center alignment $objActSheet->getstyle ("A1")->getalignment ()->sethorizontal (phpexcel_style_alignment:: Horizontal_center); $objActSheet->getstyle ("A2")->getalignment ()->sethorizontal (Phpexcel_style_alignment::horizontal_ CENTER); $objFontA 1 = $objActSheet->getstyle ("A1")->getfont (); $objFontA 1->setsize (18); $objFontA 1->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", "Summary table"); if (strlen (Trim ($sdev _model)) > 0) {$objActSheet->setcellvalue ("A2", "Model: xxxxxx"); $row = 3; $objActSheet->setcellvalue ("A". $row, "state"); $objActSheet->setcellvalue ("B". $row, "total quantity"); $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++) {//Set Border $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//F Ormat Code//Number of datapoints in series//Data values//Data Marker $dataseriesLabels 1 = array (new Phpexcel_chart_ Dataseriesvalues ("String", "worksheet! $B $", NULL, 1),); Set the x-axis Labels//Datatype/Cell Reference for Data//Format Code//Number of datapoints in series//Data V Alues//Data Marker $xAxisTickValues 1 = array (new Phpexcel_chart_dataseriesvalues ("String", "worksheet! $A $: $A $". $row , NULL, 4),); Set the data values for each of the data series we want to plot//Datatype//Cell reference for Data//Format Code//Numbe R of DataPoints in series//Data values//Data Marker $dataSeriesValues 1 = array (new Phpexcel_chart_dataseriesvalues ("N Umber "," worksheet! $B $: $B $ ". $row, NULL, 4),); Build the dataseries $series 1 = new Phpexcel_chart_dataseries (Phpexcel_chart_dataseries::type_piechart,//PlotType P Hpexcel_chart_dataseries::groUping_standard,//Plotgrouping range (0, COUNT ($dataSeriesValues 1)-1),//Plotorder $dataseriesLabels 1,//Plotlabel $ XAxisTickValues1,//plotcategory $dataSeriesValues 1//plotvalues); Set up a layout object for the Pie chart $layout 1 = new Phpexcel_chart_layout (); $layout 1->setshowval (TRUE); $layout 1->setshowpercent (TRUE); Set the series in the plot area $plotarea 1 = new Phpexcel_chart_plotarea ($layout 1, Array ($series 1)); Set the chart legend $legend 1 = new Phpexcel_chart_legend (phpexcel_chart_legend::P osition_right, NULL, false); $title 1 = new Phpexcel_chart_title ("Summary table"); Create the chart $chart 1 = new Phpexcel_chart ("Chart1",//Name $title 1,//title $legend 1,//Legend $plotarea 1,//P Lotarea 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; $chart 1->settopleftposition ("A". $row); $row + 10; $chart 1->setboTtomrightposition ("C". $row); ADD the chart to the worksheet $objPHPExcel->getactivesheet ()->addchart ($chart 1); Set Active sheet Index to the the ' sheet ', so Excel opens this as the ' the ' the ' the ' I Sheet $objPHPExcel->setactivesheetindex (0); $filename = "Summary Table _". 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//////////////////////////////////////////handling Chinese filename garbled problem $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 are "re serving to IE 9, then the following could be needed header (" cache-control:max-age=1 "); If you ' re serving to IE over SSL, then the following could be needed header ("Expires:mon, June June 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"); ExitAnother place to note is that the pie chart for Excel is automatically generated by specifying its label and the range of cells corresponding to the value, so it's mostly computationally good in code. On another non-Windows server, the build fails.

Mail:xcl_168@aliyun.com

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