YII2框架中excel表格导出的方法详解

2018-09-07 13:53

阅读：225

　　前言

　　表格的导入导出是我们在日常开发中经常会遇到的一个功能，正巧在最近的项目中做到了关于表格输出的功能，并且之前用TP的时候也做过，所以想着趁着这次功能比较多样的机会整理一下，方便以后需要的时候，或者有需要的朋友们参考学习，下面话不多说了，来一起看看详细的介绍：

　　本文是基于YII2框架进行开发的，不同框架可能会需要更改

　　一.普通excel格式表格输出

　　先是最普通的导出.xls格式的表格。首先先看一下表格在网站的显示效果

　　这里可以看到整个表格一共是7列。下面来看代码的实现。

　　 //导出统计 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文件

　　 <?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.\); //查找指定数据any_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的配置文件

　　 memory_limit = 128M

　　发现默认内存已经给到128M，应该是足够的了。于是我打开数据库一看，嚯！

　　接近83万条的数据进行查询并导出，可不是会出问题嘛！怎么办呢，于是我Google了一下，发现对于大数据(2万条以上)的导出，最好是以.csv的形式。不说废话，直接上代码

　　 //导出清单 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大数据查询处理的文章看一下

　　//清单导出

　　 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; } //查询数据any_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）

　　 $objPHPExcel->getActiveSheet()->mergeCells(A1:E1); // 表格填充内容 $objPHPExcel->getActiveSheet()->setCellValue(A1,The quick brown fox.);

　　结果

　　或者这样的（合并A1到E4）

　　 $objPHPExcel->getActiveSheet()->mergeCells(A1:E4); $objPHPExcel->getActiveSheet()->setCellValue(A1,The quick brown fox.);

　　结果

　　这样并不能满足我的要求，首先它是一个一个合并的，其次我要显示的充值金额下面的类型是会变化的，不可能固定写死，然后每次都更改。所以放弃了这种方法。

　　后来在小伙伴的帮助下尝试用html转存excel的方法

　　1.方法文件（因为我要每天定时执行，所以并没有写到controller层）

　　 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.结果