前言
表格的导入导出是我们在日常开发中经常会遇到的一个功能，正巧在最近的项目中做到了关于表格输出的功能，并且之前用tp的时候也做过，所以想着趁着这次功能比较多样的机会整理一下，方便以后需要的时候，或者有需要的朋友们参考学习，下面话不多说了，来一起看看详细的介绍：
本文是基于yii2框架进行开发的，不同框架可能会需要更改
一.普通excel格式表格输出
先是最普通的导出.xls格式的表格。首先先看一下表格在网站的显示效果
这里可以看到整个表格一共是7列。下面来看代码的实现。
1.controller文件
|
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
|
//导出统计
public function actionstatistics(){
//设置内存
ini_set("memory_limit", "2048m");
set_time_limit(0);
//获取用户id
$id = yii::$app->user->identity->getid();
//去用户表获取用户信息
$user = employee::find()->where(['id'=>$id])->one();
//获取传过来的信息（时间，公司id之类的，根据需要查询资料生成表格）
$params = yii::$app->request->get();
$objectphpexcel = new \phpexcel();
//设置表格头的输出
$objectphpexcel->setactivesheetindex()->setcellvalue('a1', '代理公司');
$objectphpexcel->setactivesheetindex()->setcellvalue('b1', '收入');
$objectphpexcel->setactivesheetindex()->setcellvalue('c1', '成本');
$objectphpexcel->setactivesheetindex()->setcellvalue('d1', '稿件数');
$objectphpexcel->setactivesheetindex()->setcellvalue('e1', '毛利（收入-成本）');
$objectphpexcel->setactivesheetindex()->setcellvalue('f1', '毛利率（毛利/收入）*100%');
$objectphpexcel->setactivesheetindex()->setcellvalue('g1', 'arpu值');
//跳转到recharge这个model文件的statistics方法去处理数据
$data = recharge::statistics($params);
//指定开始输出数据的行数
$n = 2;
foreach ($data as $v){
$objectphpexcel->getactivesheet()->setcellvalue('a'.($n) ,$v['company_name']);
$objectphpexcel->getactivesheet()->setcellvalue('b'.($n) ,$v['company_cost']);
$objectphpexcel->getactivesheet()->setcellvalue('c'.($n) ,$v['cost']);
$objectphpexcel->getactivesheet()->setcellvalue('d'.($n) ,$v['num']);
$objectphpexcel->getactivesheet()->setcellvalue('e'.($n) ,$v['gross_margin']);
$objectphpexcel->getactivesheet()->setcellvalue('f'.($n) ,$v['gross_profit_rate']);
$objectphpexcel->getactivesheet()->setcellvalue('g'.($n) ,$v['arpu']);
$n = $n +1;
}
ob_end_clean();
ob_start();
header('content-type : application/vnd.ms-excel');
//设置输出文件名及格式
header('content-disposition:attachment;filename="代理公司统计'.date("ymdhis").'.xls"');
//导出.xls格式的话使用excel5,若是想导出.xlsx需要使用excel2007
$objwriter= \phpexcel_iofactory::createwriter($objectphpexcel,'excel5');
$objwriter->save('php://output');
ob_end_flush();
//清空数据缓存
unset($data);
}
|
2.model文件
|
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
|
<?php
namespace app\models;//model层的命名空间
//注意要引用yii的arrayhelper
use yii\helpers\arrayhelper;
use yii;
class recharge extends \yii\db\activerecord
{
//excel一次导出条数
const excel_size = 10000;
//统计导出
public static function statistics($params){
//导出时间条件
if(empty($params['min'])){
$date_max = date("y-m-d",strtotime("-1 day"));
$date_min = date("y-m-d",strtotime("-31 day"));
}else{
$date_min = $params['min'];
$date_max = $params['max'];
}
$where = '';
$where .= '(`issue_date` between '.'\''.$date_min.'\''.' and '.'\''.$date_max.'\')';
//查找指定数据
$sql = 'select
article.company_id,
article.cost,
article.company_cost
from article where article.status=2 and '.$where;
$article = article::findbysql($sql)->asarray()->all();
$article = arrayhelper::index($article,null,'company_id');
$companys = [];
foreach ($article as $key=>$v){
if(empty($key)){
continue;
}else{
$number = count($v);
$company = company::find()->where(['id'=>$key])->select('name')->one();
$company_name = $company['name'];
$cost = 0;
$company_cost = 0;
foreach ($v as $n){
$cost += $n['cost'];
$company_cost += $n['company_cost'];
}
if($company_cost == 0){
$company_cost =1;
}
//这里注意，数据的存储顺序要和输出的表格里的顺序一样
$companys[] = [
//公司名
'company_name' => $company_name,
//收入
'company_cost' => $company_cost,
//成本
'cost' => $cost,
//稿件数
'num' => $number,
//毛利
'gross_margin' => $company_cost-$cost,
//毛利率
'gross_profit_rate' => round(($company_cost-$cost)/$company_cost*100,2).'%',
//arpu值
'arpu' => round($company_cost/$number,2),
];
}
}
return $companys;
}
}
|
最终导出的效果（单元格大小导出后调整过）可以看到和网页显示的基本一样。
二.大数据表格导出
这时老板说了，我们不能只看总和的数据，最好是把详细数据也给导出来。既然老板发话了，那就做吧。还是按照第一种的方法去做，结果提示我php崩溃了，再试一次发现提示写入字节超出。打开php的配置文件php.ini
|
1
|
memory_limit = 128m
|
发现默认内存已经给到128m，应该是足够的了。于是我打开数据库一看，嚯！
接近83万条的数据进行查询并导出，可不是会出问题嘛！怎么办呢，于是我google了一下，发现对于大数据(2万条以上)的导出，最好是以.csv的形式。不说废话，直接上代码
1.controller文件
|
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
|
//导出清单
public function actioninventory(){
ini_set("memory_limit", "2048m");
set_time_limit(0);
$id = yii::$app->user->identity->getid();
$user = employee::find()->where(['id'=>$id])->one();
$params = yii::$app->request->get();
//类似的，跳转到recharge这个model文件里的inventory方法去处理数据
$data = recharge::inventory($params);
//设置导出的文件名
$filename = iconv('utf-8', 'gbk', '代理商统计清单'.date("y-m-d"));
//设置表头
$headlist = array('代理商','文章id','文章标题','媒体','统计时间范围','状态','创建时间','审核时间','发稿时间','退稿时间','财务状态','成本','销售额','是否是预收款媒体类型','订单类别');
header('content-type: application/vnd.ms-excel');
//指明导出的格式
header('content-disposition: attachment;filename="'.$filename.'.csv"');
header('cache-control: max-age=0');
//打开php文件句柄,php://output 表示直接输出到浏览器
$fp = fopen('php://output', 'a');
//输出excel列名信息
foreach ($headlist as $key => $value) {
//csv的excel支持gbk编码，一定要转换，否则乱码
$headlist[$key] = iconv('utf-8', 'gbk', $value);
}
//将数据通过fputcsv写到文件句柄
fputcsv($fp, $headlist);
//每隔$limit行，刷新一下输出buffer，不要太大，也不要太小
$limit = 100000;
//逐行取出数据，不浪费内存
foreach ($data as $k => $v) {
//刷新一下输出buffer，防止由于数据过多造成问题
if ($k % $limit == 0 && $k!=0) {
ob_flush();
flush();
}
$row = $data[$k];
foreach ($row as $key => $value) {
$row[$key] = iconv('utf-8', 'gbk', $value);
}
fputcsv($fp, $row);
}
}
|
2.model文件（因为这部分我要处理的过多，所以只选择了部分代码），在查询数据那部分，因为要查的数据较多，所以可以结合我之前写的关于mysql大数据查询处理的文章看一下
//清单导出
|
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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
|
public static function inventory($params){
//统计时间范围
if(!empty($params['min']) && !empty($params['max'])){
$ti = strtotime($params['max'])+3600*24;
$max = date('y-m-d',$ti);
$time = $params['min'].'-'.$params['max'];
$date_min = $params['min'];
$date_max = $max;
}else{
$date_max = date('y-m-d');
$date_min = date('y-m-d',strtotime("-31 day"));
$time = $date_min.'-'.$date_max;
}
//查询数据
if($params['state'] == 1){
$where = '';
$where .= ' and (`issue_date` between '.'\''.$date_min.'\''.' and '.'\''.$date_max.'\')';
$map = 'select
company.name,
article.id,
article.title,
media.media_name,
article.status,
article.created,
article.audit_at,
article.issue_date,
article.back_date,
article.finance_status,
article.cost,
article.company_cost,
media.is_advance
from article
left join custom_package on custom_package.id = article.custom_package_id
left join `order` on custom_package.order_id = `order`.`id`
left join company on company.id = article.company_id
left join media on media.id = article.media_id
where article.status=2 and `order`.package=0'.$where;
//查找的第一部分数据，使用asarray方法可以使我们查找的结果直接形成数组的形式，没有其他多余的数据占空间（注意：我这里查找分三部分是因为我要查三种不同的数据）
$list1 = article::findbysql($map)->asarray()->all();
$where2 = '';
$where2 .= ' and (`issue_date` between '.'\''.$date_min.'\''.' and '.'\''.$date_max.'\')';
$where2 .= ' and (`back_date` > \''.$date_max.'\')';
$map2 = 'select
company.name,
article.id,
article.title,
media.media_name,
article.status,
article.created,
article.audit_at,
article.issue_date,
article.back_date,
article.finance_status,
article.cost,
article.company_cost,
media.is_advance
from article
left join custom_package on custom_package.id = article.custom_package_id
left join `order` on custom_package.order_id = `order`.`id`
left join company on company.id = article.company_id
left join media on media.id = article.media_id
where article.status=3 and `order`.package=0 '.$where2;
//查找的第二部分数据
$list2 = article::findbysql($map2)->asarray()->all();
$where3 = '';
$where3 .= ' and (`issue_date` between '.'\''.$date_min.'\''.' and '.'\''.$date_max.'\')';
$map3 = 'select
company.name,
article.id,
article.title,
media.media_name,
article.status,
article.created,
article.audit_at,
article.issue_date,
article.back_date,
article.finance_status,
article.cost,
article.company_cost,
media.is_advance
from article
left join custom_package on custom_package.id = article.custom_package_id
left join `order` on custom_package.order_id = `order`.`id`
left join company on company.id = article.company_id
left join media on media.id = article.media_id
where article.status=5 '.$where3;
//查找的第三部分数据
$list3 = article::findbysql($map3)->asarray()->all();
$list4 = arrayhelper::merge($list1,$list2);
$list = arrayhelper::merge($list4,$list3);
}
//把结果按照显示顺序存到返回的数组中
if(!empty($list)){
foreach ($list as $key => $value){
//代理公司
$inventory[$key]['company_name'] = $value['name'];
//文章id
$inventory[$key]['id'] = $value['id'];
//文章标题
$inventory[$key]['title'] = $value['title'];
//媒体
$inventory[$key]['media'] = $value['media_name'];
//统计时间
$inventory[$key]['time'] = $time;
//状态
switch($value['status']){
case 2:
$inventory[$key]['status'] = '已发布';
break;
case 3:
$inventory[$key]['status'] = '已退稿';
break;
case 5:
$inventory[$key]['status'] = '异常稿件';
break;
}
//创建时间
$inventory[$key]['created'] = $value['created'];
//审核时间
$inventory[$key]['audit'] = $value['audit_at'];
//发稿时间
$inventory[$key]['issue_date'] = $value['issue_date'];
//退稿时间
$inventory[$key]['back_date'] = $value['back_date'];
//财务状态
switch($value['finance_status']){
case 0:
$inventory[$key]['finance_status'] = '未到结算期';
break;
case 1:
$inventory[$key]['finance_status'] = '可结算';
break;
case 2:
$inventory[$key]['finance_status'] = '资源审批中';
break;
case 3:
$inventory[$key]['finance_status'] = '财务审批中';
break;
case 4:
$inventory[$key]['finance_status'] = '已结款';
break;
case 5:
$inventory[$key]['finance_status'] = '未通过';
break;
case 6:
$inventory[$key]['finance_status'] = '财务已审批';
break;
}
//成本
$inventory[$key]['cost'] = $value['cost'];
//销售额
$inventory[$key]['company_cost'] = $value['company_cost'];
//是否是预售
switch($value['is_advance']){
case 0:
$inventory[$key]['is_advance'] = '否';
break;
case 1:
$inventory[$key]['is_advance'] = '是';
break;
case 2:
$inventory[$key]['is_advance'] = '合同';
break;
}
//订单类别
switch($params['state']){
case 1:
$inventory[$key]['order_type'] = '时间区间无退稿完成订单';
break;
case 2:
$inventory[$key]['order_type'] = '时间区间发布前退稿订单';
break;
case 3:
$inventory[$key]['order_type'] = '时间区间发布后时间区间退稿订单';
break;
case 4:
$inventory[$key]['order_type'] = '时间区间之前发布时间区间内退稿订单';
break;
case 5:
$inventory[$key]['order_type'] = '异常订单';
break;
}
}
}else{
$inventory[0]['company_name'] = '无数据导出';
}
return $inventory;
}
|
3.导出结果
导出数量
导出的文件
基本上可以保证整个过程在2~4秒内处理完成
三.合并单元格
老板一看做的不错，说你顺便把充值统计的导出也做了把，想想我都是处理过这么多数据的人了，还不是分分钟搞定的事？来，上原型图
噗，一口老血，话都说了，搞吧。在做的时候我发现，这次的导出主要是要解决单元格合并的问题。经过查资料发现，php本身是实现不了单元格合并的，于是我打算通过phpexcel来实现
如果是使用phpexcel的话，基本操作是这样的（合并a1到e1）
|
1
2
3
|
$objphpexcel->getactivesheet()->mergecells('a1:e1');
// 表格填充内容
$objphpexcel->getactivesheet()->setcellvalue('a1','the quick brown fox.');
|
结果
或者这样的（合并a1到e4）
|
1
2
|
$objphpexcel->getactivesheet()->mergecells('a1:e4');
$objphpexcel->getactivesheet()->setcellvalue('a1','the quick brown fox.');
|
结果
这样并不能满足我的要求，首先它是一个一个合并的，其次我要显示的充值金额下面的类型是会变化的，不可能固定写死，然后每次都更改。所以放弃了这种方法。
后来在小伙伴的帮助下尝试用html转存excel的方法
1.方法文件（因为我要每天定时执行，所以并没有写到controller层）
|
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
|
public function actionexcelrechargestatistics(){
//先定义一个excel文件
$filename = date('【充值统计表】('.date('y-m-d').'导出)').".xls";
header("content-type: application/vnd.ms-execl");
header("content-type: application/vnd.ms-excel; charset=utf-8");
header("content-disposition: attachment; filename=$filename");
header("pragma: no-cache");
header("expires: 0");
//时间条件
if(empty($params['min'])){
$time = date('y-m-d',strtotime("+1 day"));
$where = ' created < \' '.$time.'\'';
}else{
$time = $params['min']+3600*24;
$time_end = $params['max']+3600*24;
$where = ' created <= \' '.$time_end.'\' and created >= \''.$time.'\' ';
}
//充值类型列表
$recharge_type = recharge::find()->asarray()->all();
if(empty($recharge_type)){
$rechargelist[0]= '';
}else{
$rechargelist = arrayhelper::map($recharge_type,'id','recharge_name');
}
$rechargelist1 = $rechargelist;
$count = count($rechargelist1);
//使用html语句生成显示的格式
$excel_content = '<meta http-equiv="content-type" content="application/ms-excel; charset=utf-8"/>';
$excel_content .= '<table border="1" style="font-size:14px;">';
$excel_content .= '<thead>
<tr>
<th rowspan="2">id</th>
<th rowspan="2">公司名称</th>
<th colspan='.$count.'>充值金额</th>
<th rowspan="2">充值大小</th>
<th rowspan="2">实际消费</th>
<th rowspan="2">当前余额</th>
</tr>
<tr>
';
foreach ($rechargelist1 as $v => $t){
$excel_content .= '<th colspan="1">'.$t.'</th>';
}
$excel_content .= '</tr>
</thead>';
//查找最新的固化数据
$search = rechargestatistics::find()->where($where)->asarray()->all();
if(!empty($search)){
foreach ($search as $key => $value){
$search[$key]['recharge'] = unserialize($value['recharge']);
}
}
//html语句填充数据
if(empty($search)){
}else{
foreach ($search as $k) {
$excel_content .= '<td>'.$k['company_id'].'</td>';
$excel_content .= '<td>'.$k['company_name'].'</td>';
foreach ($rechargelist1 as $v=>$t){
$price = 0;
foreach ($k['recharge'] as $q=>$w){
if($w['recharge_id'] == $v){
$price = $w['price'];
break;
}
}
$excel_content .= '<td>'.$price.'</td>';
}
$excel_content .= '<td>'.$k['total'].'</td>';
$excel_content .= '<td>'.$k['consume'].'</td>';
$excel_content .= '<td>'.($k['total']-$k['consume']).'</td></tr>';
}
}
$excel_content .= '</table>';
echo $excel_content;
die;
}
|
2.结果
到这里基本就完成所有的任务了！
总结
以上就是这篇文章的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对服务器之家的支持。
原文链接：https://segmentfault.com/a/1190000010237469