这在附另一个转柱形图的效果及代码.
原php报表效果:
转成excel后的效果:
喎?http://www.bkjia.com/kf/ware/vc/ target=_blank class=keylink>vcd4kpha+ul3jz7t6wus6pc9wpgo8cd48chjlignsyxnzpq==brush:java;>getproperties()->setcreator(xiongchuanliang) ->setlastmodifiedby(xiongchuanliang) ->settitle(明细表);$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'); //设置居中对齐 $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); $objfonta1 = $objactsheet->getstyle('a1')->getfont(); $objfonta1->setsize(18); $objfonta1->setbold(true); ///////////////////////////////////////////////////////////////////////// $sqlselect=select ...... from ( ...... ) k order by ...... ; $sql = mysql_query($sqlselect); $info = mysql_fetch_array($sql); $objactsheet->setcellvalue('a1', '明细表'); if(strlen( trim( $rent_time_begin)) > 0 && strlen( trim( $rent_time_end)) > 0 ) { $objactsheet->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['...']); //分别累加 $tmpstatus = $info['...']; if($tmpstatus == 'a'){ $status1++; }else if($tmpstatus == 'b'){ $status2++; }else if($tmpstatus == 'c'){ $status3++; }else if($tmpstatus == 'd'){ $status4++; }else{ $status0 ++; } $row++; }while($info=mysql_fetch_array($sql));/////////////////////////////////////////////////////////////////////////for ($currrow = 4; $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 ); $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, '状态'); $objactsheet->setcellvalue('b'.$row, '总数'); $row++; $objactsheet->setcellvalue('a'.$row, '...'); $objactsheet->setcellvalue('b'.$row, $status0); $row++; $objactsheet->setcellvalue('a'.$row, '...'); $objactsheet->setcellvalue('b'.$row, $status1); $row++; $objactsheet->setcellvalue('a'.$row, '...'); $objactsheet->setcellvalue('b'.$row, $status2); $row++; $objactsheet->setcellvalue('a'.$row, '...'); $objactsheet->setcellvalue('b'.$row, $status3); $row++; $objactsheet->setcellvalue('a'.$row, '...'); $objactsheet->setcellvalue('b'.$row, $status4); $tablastrow = $row; for ($currrow = $tabinitrow; $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$dataserieslabels = array( new phpexcel_chart_dataseriesvalues('string', 'worksheet!$b$'.$tabinitrow , null, 1), // 2010 ); //new phpexcel_chart_dataseriesvalues('string', 'worksheet!$c$1', null, 1), // 2011 //new phpexcel_chart_dataseriesvalues('string', 'worksheet!$d$1', 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 ++;$xaxistickvalues = 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 phpexcel_chart_dataseriesvalues('number', 'worksheet!$b$'.$tabinitrow.':$b$'.$tablastrow, null, 4),);// build the dataseries$series = new phpexcel_chart_dataseries( phpexcel_chart_dataseries::type_barchart, // plottype phpexcel_chart_dataseries::grouping_clustered, // plotgrouping range(0, count($dataseriesvalues)-1), // plotorder $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::direction_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(phpexcel_chart_legend::position_right, null, false);$title = new phpexcel_chart_title('状态汇总');$yaxislabel = new phpexcel_chart_title('总数');// create the chart$chart = new phpexcel_chart( 'chart1', // name $title, // title $legend, // 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 += 15;$chart->setbottomrightposition('f'.$tablastrow );// add the chart to the worksheet$objactsheet->addchart($chart);//////////////////////////////////////////////////////////////////////////////////////// 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 neededheader('cache-control: max-age=1');// if you're serving to ie over ssl, then the following may be neededheader ('expires: mon, 26 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, 'excel2007');$objwriter->setincludecharts(true);$objwriter->save('php://output');exit;
mail: xcl_168@aliyun.com
blog: http://blog.csdn.net/xcl168
http://www.bkjia.com/phpjc/766058.htmlwww.bkjia.comtruehttp://www.bkjia.com/phpjc/766058.htmltecharticle这在附另一个转柱形图的效果及代码. 原php报表效果: 转成excel后的效果: vcd4kpha+ul3jz7t6wus6pc9wpgo8cd48chjlignsyxnzpq==brush:java;> getproperties()->setc...