原php报表效果:
转成excel后的效果:
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 youre serving to ie 9, then the following may be neededheader(cache-control: max-age=1);// if youre 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