本文实例讲述了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程序设计有所帮助。