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

前言

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

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

一.普通excel格式表格输出

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

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

1.controller文件

//导出统计

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 appmodels;//model层的命名空间
 //注意要引用yii的arrayhelper
 use yiihelpersArrayHelper;
 use Yii;
 class Recharge extends yiidbActiveRecord
 {
 //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

memory_limit = 128M

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

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

1.controller文件

//导出清单

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

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

到这里基本就完成所有的任务了！

总结

以上就是这篇文章的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对网页设计的支持。