五大工程塑料,林肯娱乐招商q262913,花季王朝
phpexcel处理excel档真是个强大的工具，我有张报表，带饼图，需要转成excel, phpexcel有一个相关的例子，参考并修改后实现了这个效果，
可以让用户在点击下载过程中生成和下载excel档，并在excel中档生成饼图。
对其例子主要做了两方面的修改：
1. 改成从mysql取资料
2. 加上了中文文件名在部份，如ie下，下载时名字乱码的解决方法.
php报表如下:
转成xls的效果图:
<喎? f/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 < $row;="" $currrow++)="" {="" 设置边框="" $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
// 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
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
PHP date_default_timezone_set()设置时区操作实例分析
我要评论
网友评论