Using Phpexcel to Excel column chart

This is accompanied by the effect and code of a rotating column chart.

Original PHP Report effect:

Effect after turning into Excel:

Attached code:

<?
php/** * Phpexcel * * Copyright (C) 2006-2014 phpexcel * * This library is free software; You can redistribute it and/or * modify it under the terms of the GNU Lesser general public * License as published by the Free software Foundation; either * Version 2.1 of the License, or (at your option) any later version. * * This library was distributed in the hope that it'll be useful, * but without any WARRANTY;  Without even the implied warranty of * merchantability or FITNESS for A particular PURPOSE. See the GNU * Lesser general public License for more details. * * You should has received a copy of the GNU Lesser general public * License along with this library; If not, write to the free software * Foundation, Inc., Wuyi Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA * @c Ategory Phpexcel * @package phpexcel * @copyright Copyright (c) 2006-2014 Phpexcel (http://www.codeplex.com/PHPExc EL) * @license Http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txtLGPL * @version 1.8.0, 2014-03-02 *//** Error reporting */error_reporting (E_all); Ini_set (' display_errors ', TRUE); Ini_set (' Display_ Startup_errors ', TRUE);//date_default_timezone_set (' Europe/london ');d ate_default_timezone_set ("Asia/Shanghai"); if (Php_sapi = = ' cli ') die (' This example should is run from a Web Browser ');/** Include phpexcel */require_once Dirnam E (__file__). '/libxls/classes/phpexcel.php '; require_once dirname (__file__). ' /inc/xcl_conn.php ';////////////////////////////////////////stores the total number of states $status0 = 0; $status 1 = 0; $status 2 = 0; $status 3 = 0; $status 4 = 0;////////////////////////////////////////Create new Phpexcel object$objphpexcel = new Phpexcel ();//Set do Cument properties$objphpexcel->getproperties ()->setcreator ("Xiongchuanliang")->setLastModifiedBy ("    Xiongchuanliang ")->settitle (" Schedule "), $objActSheet = $objPHPExcel->getactivesheet ();    $objActSheet->getcolumndimension (' A ')->setwidth (12); $objActSheet->getcolumndimension (' B ')->setwidth (20);    $objActSheet->getcolumndimension (' C ')->setwidth (12);    $objActSheet->getcolumndimension (' D ')->setwidth (20);    $objActSheet->getcolumndimension (' E ')->setwidth (20);    $objActSheet->getcolumndimension (' F ')->setwidth (12);    $objActSheet->getcolumndimension (' G ')->setwidth (20);    $objActSheet->getcolumndimension (' H ')->setwidth (18);    $objActSheet->getcolumndimension (' I ')->setwidth (18);    $objActSheet->getcolumndimension (' J ')->setwidth (30); $objActSheet->getcolumndimension (' K ')->setwidth (20);    $objActSheet->getrowdimension (1)->setrowheight (30);    $objActSheet->getrowdimension (2)->setrowheight (16);  $objActSheet->getrowdimension (3)->setrowheight (16);    $objActSheet->mergecells (' a1:k1 ');    $objActSheet->mergecells (' a2:k2 ');    $objActSheet->mergecells (' A3:k3 '); Set the center alignment $objActSheet->getstyle (' A1 ')->getalignment ()->sethorizontal (phpexcel_style_alignment:: Horizontal_cenTER); $objActSheet->getstyle (' A2 ')->getalignment ()->sethorizontal (Phpexcel_style_alignment::horizontal_ CENTER); $objActSheet->getstyle (' A3 ')->getalignment ()->sethorizontal (Phpexcel_style_alignment::horizontal_ CENTER);       $objFontA 1 = $objActSheet->getstyle (' A1 ')->getfont ();     $objFontA 1->setsize (18);      $objFontA 1->setbold (TRUE);        $sqlSelect = "Select ... From (...) k order by .....     ";     $sql = mysql_query ($sqlSelect);     $info = Mysql_fetch_array ($sql);     $objActSheet->setcellvalue (' A1 ', ' schedules '); if (strlen (Trim ($rent _time_begin)) > 0 && strlen (Trim ($rent _time_end)) > 0) {$objActSheet-&    Gt;setcellvalue (' A2 ', "(". $rent _time_begin. "~". $rent _time_end. ")");    $objActSheet->setcellvalue (' A3 ', "(demo)");  $row =4; $objActSheet->setcellvalue (' A '. $row, ' ... '); $objActSheet->setcellvalue (' B '. $row, ' ... ');  $objActSheet->setcellvalue (' C '. $row, ' ... '); $objActSheet->setcellvalue (' D '. $row, ' ... '); $objActSheet->setcellvalue (' E '. $row, ' ... '); $objActSheet Setcellvalue (' F '. $row, ' ... '); $objActSheet->setcellvalue (' G '. $row, ' ... '); $objActSheet->setcellvalue (' H '. $    Row, ' ... '); $objActSheet->setcellvalue (' I '. $row, ' ... '); $objActSheet->setcellvalue (' J ' $row, ' ... ');    $row = 5;    do{$objActSheet->setcellvalue (' A '. $row, $info [' ... ']);    $objActSheet->setcellvalue (' B '. $row, $info [' ... ']);  $objActSheet->setcellvalue (' C '. $row, $info [' ... ']);    $objActSheet->setcellvalue (' D '. $row, $info [' ... ']);    $objActSheet->setcellvalue (' E '. $row, $info [' ... ']);    $objActSheet->setcellvalue (' F '. $row, $info [' ... ']);    $objActSheet->setcellvalue (' G '. $row, $info [' ... ']);    $objActSheet->setcellvalue (' H '. $row, $info [' ... ']);  $objActSheet->setcellvalue (' I '. $row, $info [' ... ']); $objActSheet->setcellvalue (' J '. $row,$info [' ... '];      Accumulate $tmpstatus respectively = $info [' ... '];      if ($tmpstatus = = ' A ') {$status 1++;      }else if ($tmpstatus = = ' B ') {$status 2++;       }else if ($tmpstatus = = ' C ') {$status 3++;       }else if ($tmpstatus = = ' D ') {$status 4++;      }else{$status 0 + +;    } $row + +; }while ($info =mysql_fetch_array ($sql));//////////////////////////////////////////////////////////////////////// /for ($currrow = 4; $currrow < $row; $currrow + +) {//Set Border $objActSheet->getstyle (' A '. $currrow)->getborders ()-&       Gt;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); $objActSheet->getstyle (' C '. $currrow)->getborders ()->gettop ()->setborderstyle (Phpexcel_style_border       :: Border_thin); $objActSheet->getstyle (' C '. $currrow)->getborders ()->getleft ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' C '. $currrow)->getborders ()->getright ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActsheet->getstyle (' C '. $currrow)->getborders ()->getbottom ()->setborderstyle (phpexcel_style_border:       : Border_thin); $objActSheet->getstyle (' D '. $currrow)->getborders ()->gettop ()->setborderstyle (Phpexcel_style_border       :: Border_thin); $objActSheet->getstyle (' D '. $currrow)->getborders ()->getleft ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' D '. $currrow)->getborders ()->getright ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' D '. $currrow)->getborders ()->getbottom ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' E '. $currrow)->getborders ()->gettop ()->setborderstyle (Phpexcel_style_border       :: Border_thin); $objActSheet->getstyle (' E '. $currrow)->getborders ()->getleft ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' E '. $currrow)->getborders ()->geTright ()->setborderstyle (Phpexcel_style_border::border_thin); $objActSheet->getstyle (' E '. $currrow)->getborders ()->getbottom ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' F '. $currrow)->getborders ()->gettop ()->setborderstyle (Phpexcel_style_border       :: Border_thin); $objActSheet->getstyle (' F '. $currrow)->getborders ()->getleft ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' F '. $currrow)->getborders ()->getright ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' F '. $currrow)->getborders ()->getbottom ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' G '. $currrow)->getborders ()->gettop ()->setborderstyle (Phpexcel_style_border       :: Border_thin); $objActSheet->getstyle (' G '. $currrow)->getborders ()->getleft ()->setborderstyle (phpexcel_style_ border::border_tHIN); $objActSheet->getstyle (' G '. $currrow)->getborders ()->getright ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' G '. $currrow)->getborders ()->getbottom ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' H '. $currrow)->getborders ()->gettop ()->setborderstyle (Phpexcel_style_border       :: Border_thin); $objActSheet->getstyle (' H '. $currrow)->getborders ()->getleft ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' H '. $currrow)->getborders ()->getright ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' H '. $currrow)->getborders ()->getbottom ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' I '. $currrow)->getborders ()->gettop ()->setborderstyle (Phpexcel_style_border       :: Border_thin); $objActSheet->getstyle (' I '. $currrow)->gEtborders ()->getleft ()->setborderstyle (Phpexcel_style_border::border_thin); $objActSheet->getstyle (' I '. $currrow)->getborders ()->getright ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' I '. $currrow)->getborders ()->getbottom ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' J '. $currrow)->getborders ()->gettop ()->setborderstyle (Phpexcel_style_border       :: Border_thin); $objActSheet->getstyle (' J '. $currrow)->getborders ()->getleft ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' J '. $currrow)->getborders ()->getright ()->setborderstyle (phpexcel_style_       Border::border_thin); $objActSheet->getstyle (' J '. $currrow)->getborders ()->getbottom ()->setborderstyle (phpexcel_style_      Border::border_thin); }/////////////////////////////////////////////bar///////////////////////////////////////////////$row + = 3;  $tabInitRow = $row;  $objActSheet->setcellvalue (' A '. $row, ' state ');  $objActSheet->setcellvalue (' B '. $row, ' total ');  $row + +;  $objActSheet->setcellvalue (' A '. $row, ' ... ');  $objActSheet->setcellvalue (' B '. $row, $status 0);  $row + +;  $objActSheet->setcellvalue (' A '. $row, ' ... ');  $objActSheet->setcellvalue (' B '. $row, $status 1);  $row + +;  $objActSheet->setcellvalue (' A '. $row, ' ... ');  $objActSheet->setcellvalue (' B '. $row, $status 2);  $row + +;  $objActSheet->setcellvalue (' A '. $row, ' ... ');  $objActSheet->setcellvalue (' B '. $row, $status 3);  $row + +;  $objActSheet->setcellvalue (' A '. $row, ' ... ');  $objActSheet->setcellvalue (' B '. $row, $status 4);  $tabLastRow = $row; for ($currrow = $tabInitRow; $currrow <= $tabLastRow; $currrow + +) {//Set Border $objActSheet->getstyle (' A '. $c         Urrrow)->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//cells reference for data//Format code//    Number of datapoints in series//data values//data Marker$dataserieslabels = Array (new Phpexcel_chart_dataser  Iesvalues (' String ', ' worksheet! $B $ '. $tabInitRow, NULL, 1),//2010); New Phpexcel_chart_dataseriesvalues (' String ', ' worksheet! $C $ ', NULL, 1),////new Phpexcel_chart_  Dataseriesvalues (' String ', ' worksheet! $D $ ', NULL, 1),//2012//Set the x-axis labels//datatype//Cell Reference For data//Format code//number of datapoints in series//data values//data marker$tabinitrow + +; $xAxisTickV alues = Array (new Phpexcel_chart_dataseriesvalues (' String ', ' worksheet! $A $ '. $tabInitRow. ': $A $ '. $tabLastRow, NULL, 4)     ,//Q1 to Q4);//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$dataseriesvalues = Array (new Phpex Cel_chart_dataseriesvalues (' number ', ' worksheet! $B $ '. $tabInitRow. ': $B $ '. $tabLastRow, NULL, 4);//Build The dataseries$series = new Phpexcel_chart_dataseries (Phpexcel_chart_dataseries::type_barchart,//PLO Ttype phpexcel_chart_dataseries::grouping_clustered,//Plotgrouping range (0, COUNT ($dataSeriesValues)-1),//Ploto               Rder $dataseriesLabels,//Plotlabel $xAxisTickValues,//Plotcategory $dataSeriesValues plotvalues);//Set additional dataseries parameters//make it a horizontal bar rather than a vertical Column graph$series->setplotdirection (phpexcel_chart_dataseries::D irection_bar);//Set the series in the plot area$ PlotArea = new Phpexcel_chart_plotarea (NULL, Array ($series));//Set the Chart legend$legend = new Phpexcel_chart_legend (P Hpexcel_chart_legend::P osition_right, NULL, false), $title = new Phpexcel_chart_title (' state Rollup '); $yAxisLabel = new Phpexcel_chart_title (' total ');//Create The Chart$chart = new Phpexcel_chart (' Chart1 ',//name $title,//Title $l Egend,//Legend $plotarea,//PlotArea true,//Plotvisibleonly 0,//Displayblanksas NULL,//Xaxislabel $yAxisLabel//Yaxislabel );//Set the position where the chart should appear in the worksheet $tabLastRow + = 2; $chart->settopleftposition (' A '. $tabLastRow), $tabLastRow + =, $chart->setbottomrightposition (' F '. $tabLastRow);//ADD the chart to the Worksheet$ob Jactsheet->addchart ($chart);/////////////////////////////////////////////////////////////////////////////// Set Active sheet Index to the first sheet, so Excel opens this as the first Sheet$objphpexcel->setactivesheet Index (0); $filename = ' Schedule _ '. 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//////////////////////////////////////////processing Chinese file name 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 _filena Me. ‘"‘);} 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 and then the Following May Neededheader (' cache-control:max-age=1 ');/If you ' re serving to IE over SSL and then the following May n Eededheader (' Expires:mon, 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, ' Excel20$objWriter->setincludecharts (TRUE); $objWriter->save (' Php://output '); exit; 

MAIL: [Email protected]

blog:http://blog.csdn.net/xcl168

Using Phpexcel to Excel column chart