本文实例讲述了thinkphp+phpexcel实现excel报表输出功能。分享给大家供大家参考，具体如下：
准备工作：
1.下载phpexcel1.7.6类包；
2.解压至tp框架的thinkphp\vendor目录下，改类包文件夹名为phpexcel176，目录结构如下图；
编写代码(以一个订单汇总数据为例)：
1. 创建数据库及表;
2. 创建tp项目，配置项目的数据库连接，这些基本的就不说了；
3. 在项目的lib\action下创建一个新的类文件exportstatisticsaction.class.php，然后在 index方法中实现excel导出；
4. 导出方法的步骤：
①查询数据
②导入phpexcel类库
③创建excel对象并设置excel对象的属性
④设置excel的行列样式(字体、高宽、颜色、边框、合并等)
⑤绘制报表表头
⑥将查询数据写入excel
⑦设置excel的sheet的名称
⑧设置excel报表打开后初始的sheet
⑨设置输出的excel的头参数及文件名
⑩调用创建excel的方法生成excel文件
代码如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
|
<?php/** * created by lonm.shi. * date: 2012-02-09 * time: 下午4:54 * to change this template use file | settings | file templates. */class exportstatisticsaction extends action { public function index(){ $model= d("ordersview"); $ordersdata= $model->select(); //查询数据得到$ordersdata二维数组 vendor("phpexcel176.phpexcel"); // create new phpexcel object $objphpexcel = new phpexcel(); // set properties $objphpexcel->getproperties()->setcreator("ctos") ->setlastmodifiedby("ctos") ->settitle("office 2007 xlsx test document") ->setsubject("office 2007 xlsx test document") ->setdescription("test document for office 2007 xlsx, generated using php classes.") ->setkeywords("office 2007 openxml php") ->setcategory("test result file"); //set width $objphpexcel->getactivesheet()->getcolumndimension('a')->setwidth(8); $objphpexcel->getactivesheet()->getcolumndimension('b')->setwidth(10); $objphpexcel->getactivesheet()->getcolumndimension('c')->setwidth(25); $objphpexcel->getactivesheet()->getcolumndimension('d')->setwidth(12); $objphpexcel->getactivesheet()->getcolumndimension('e')->setwidth(50); $objphpexcel->getactivesheet()->getcolumndimension('f')->setwidth(10); $objphpexcel->getactivesheet()->getcolumndimension('g')->setwidth(12); $objphpexcel->getactivesheet()->getcolumndimension('h')->setwidth(12); $objphpexcel->getactivesheet()->getcolumndimension('i')->setwidth(12); $objphpexcel->getactivesheet()->getcolumndimension('j')->setwidth(30); //设置行高度 $objphpexcel->getactivesheet()->getrowdimension('1')->setrowheight(22); $objphpexcel->getactivesheet()->getrowdimension('2')->setrowheight(20); //set font size bold $objphpexcel->getactivesheet()->getdefaultstyle()->getfont()->setsize(10); $objphpexcel->getactivesheet()->getstyle('a2:j2')->getfont()->setbold(true); $objphpexcel->getactivesheet()->getstyle('a2:j2')->getalignment()->setvertical(phpexcel_style_alignment::vertical_center); $objphpexcel->getactivesheet()->getstyle('a2:j2')->getborders()->getallborders()->setborderstyle(phpexcel_style_border::border_thin); //设置水平居中 $objphpexcel->getactivesheet()->getstyle('a1')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_left); $objphpexcel->getactivesheet()->getstyle('a')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); $objphpexcel->getactivesheet()->getstyle('b')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); $objphpexcel->getactivesheet()->getstyle('d')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); $objphpexcel->getactivesheet()->getstyle('f')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); $objphpexcel->getactivesheet()->getstyle('g')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); $objphpexcel->getactivesheet()->getstyle('h')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); $objphpexcel->getactivesheet()->getstyle('i')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); //合并cell $objphpexcel->getactivesheet()->mergecells('a1:j1'); // set table header content $objphpexcel->setactivesheetindex(0) ->setcellvalue('a1', '订单数据汇总 时间:'.date('y-m-d h:i:s')) ->setcellvalue('a2', '订单id') ->setcellvalue('b2', '下单人') ->setcellvalue('c2', '客户名称') ->setcellvalue('d2', '下单时间') ->setcellvalue('e2', '需求机型') ->setcellvalue('f2', '需求数量') ->setcellvalue('g2', '需求交期') ->setcellvalue('h2', '确认bom料号') ->setcellvalue('i2', 'pmc确认交期') ->setcellvalue('j2', 'pmc交货备注'); // miscellaneous glyphs, utf-8 for($i=0;$i<count($ordersdata)-1;$i++){ $objphpexcel->getactivesheet(0)->setcellvalue('a'.($i+3), $ordersdata[$i]['id']); $objphpexcel->getactivesheet(0)->setcellvalue('b'.($i+3), $ordersdata[$i]['realname']); $objphpexcel->getactivesheet(0)->setcellvalue('c'.($i+3), $ordersdata[$i]['customer_name']); $objphpexcel->getactivesheet(0)->setcellvalue('d'.($i+3), todate($ordersdata[$i]['create_time'])); //这里调用了common.php的时间戳转换函数 $objphpexcel->getactivesheet(0)->setcellvalue('e'.($i+3), $ordersdata[$i]['require_product']); $objphpexcel->getactivesheet(0)->setcellvalue('f'.($i+3), $ordersdata[$i]['require_count']); $objphpexcel->getactivesheet(0)->setcellvalue('g'.($i+3), $ordersdata[$i]['require_time']); $objphpexcel->getactivesheet(0)->setcellvalue('h'.($i+3), $ordersdata[$i]['product_bom_encoding']); $objphpexcel->getactivesheet(0)->setcellvalue('i'.($i+3), $ordersdata[$i]['delivery_time']); $objphpexcel->getactivesheet(0)->setcellvalue('j'.($i+3), $ordersdata[$i]['delivery_memo']); $objphpexcel->getactivesheet()->getstyle('a'.($i+3).':j'.($i+3))->getalignment()->setvertical(phpexcel_style_alignment::vertical_center); $objphpexcel->getactivesheet()->getstyle('a'.($i+3).':j'.($i+3))->getborders()->getallborders()->setborderstyle(phpexcel_style_border::border_thin); $objphpexcel->getactivesheet()->getrowdimension($i+3)->setrowheight(16); } // sheet命名 $objphpexcel->getactivesheet()->settitle('订单汇总表'); // set active sheet index to the first sheet, so excel opens this as the first sheet $objphpexcel->setactivesheetindex(0); // excel头参数 header('content-type: application/vnd.ms-excel'); header('content-disposition: attachment;filename="订单汇总表('.date('ymd-his').').xls"'); //日期为文件名后缀 header('cache-control: max-age=0'); $objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel5'); //excel5为xls格式，excel2007为xlsx格式 $objwriter->save('php://output'); }} |
5.调用导出方法直接 http://项目/index.php/exportstatistics/index，项目中调用直接__app__/exportstatistics/index,生成的报表是下载方式来保存。phpexcel1.7.6没有发现什么编码问题，速度也很快，注意导出的方法中不能有任何页面输出信息或调试信息，否则导出的excel会提示格式不对。效果如下：
导出报表
希望本文所述对大家基于thinkphp框架的php程序设计有所帮助。
