YII2框架中excel表格導(dǎo)出的方法詳解

更新時間：2017年07月21日 11:48:59 作者：xsctx7788

最近在研究PHP的Yii框架，很喜歡，碰到導(dǎo)出Excel的問題，研究了一下，就有了下面這篇文章，這篇文章主要給大家介紹了關(guān)于YII2框架中excel表格導(dǎo)出的相關(guān)資料，文中通過示例代碼介紹的非常詳細，需要的朋友可以參考借鑒，下面來一起看看吧。

前言

表格的導(dǎo)入導(dǎo)出是我們在日常開發(fā)中經(jīng)常會遇到的一個功能，正巧在最近的項目中做到了關(guān)于表格輸出的功能，并且之前用TP的時候也做過，所以想著趁著這次功能比較多樣的機會整理一下，方便以后需要的時候，或者有需要的朋友們參考學習，下面話不多說了，來一起看看詳細的介紹：

本文是基于YII2框架進行開發(fā)的，不同框架可能會需要更改

一.普通excel格式表格輸出

先是最普通的導(dǎo)出.xls格式的表格。首先先看一下表格在網(wǎng)站的顯示效果

這里可以看到整個表格一共是7列。下面來看代碼的實現(xiàn)。

1.controller文件

//導(dǎo)出統(tǒng)計

public function actionStatistics(){
 //設(shè)置內(nèi)存
 ini_set("memory_limit", "2048M");
 set_time_limit(0);

 //獲取用戶ID
 $id = Yii::$app->user->identity->getId();

 //去用戶表獲取用戶信息
 $user = Employee::find()->where(['id'=>$id])->one();

 //獲取傳過來的信息（時間，公司ID之類的，根據(jù)需要查詢資料生成表格）
 $params = Yii::$app->request->get();
 $objectPHPExcel = new \PHPExcel();

 //設(shè)置表格頭的輸出
 $objectPHPExcel->setActiveSheetIndex()->setCellValue('A1', '代理公司');
 $objectPHPExcel->setActiveSheetIndex()->setCellValue('B1', '收入');
 $objectPHPExcel->setActiveSheetIndex()->setCellValue('C1', '成本');
 $objectPHPExcel->setActiveSheetIndex()->setCellValue('D1', '稿件數(shù)');
 $objectPHPExcel->setActiveSheetIndex()->setCellValue('E1', '毛利（收入-成本）');
 $objectPHPExcel->setActiveSheetIndex()->setCellValue('F1', '毛利率（毛利/收入）*100%');
 $objectPHPExcel->setActiveSheetIndex()->setCellValue('G1', 'ARPU值');

 //跳轉(zhuǎn)到recharge這個model文件的statistics方法去處理數(shù)據(jù)
 $data = Recharge::statistics($params);

 //指定開始輸出數(shù)據(jù)的行數(shù)
 $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');

 //設(shè)置輸出文件名及格式
 header('Content-Disposition:attachment;filename="代理公司統(tǒng)計'.date("YmdHis").'.xls"');

 //導(dǎo)出.xls格式的話使用Excel5,若是想導(dǎo)出.xlsx需要使用Excel2007
 $objWriter= \PHPExcel_IOFactory::createWriter($objectPHPExcel,'Excel5');
 $objWriter->save('php://output');
 ob_end_flush();

 //清空數(shù)據(jù)緩存
 unset($data);
}

2.model文件

 <?php
 namespace app\models;//model層的命名空間
 //注意要引用yii的arrayhelper
 use yii\helpers\ArrayHelper;
 use Yii;
 class Recharge extends \yii\db\ActiveRecord
 {
 //excel一次導(dǎo)出條數(shù)
 const EXCEL_SIZE = 10000;
 
 //統(tǒng)計導(dǎo)出
 public static function statistics($params){

 //導(dǎo)出時間條件
 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.'\')';

 //查找指定數(shù)據(jù)
 $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;
 }

 //這里注意，數(shù)據(jù)的存儲順序要和輸出的表格里的順序一樣
 $companys[] = [
 //公司名
 'company_name' => $company_name,

 //收入
 'company_cost' => $company_cost,

 //成本
 'cost' => $cost,

 //稿件數(shù)
 '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;
 }
}

最終導(dǎo)出的效果（單元格大小導(dǎo)出后調(diào)整過）可以看到和網(wǎng)頁顯示的基本一樣。

二.大數(shù)據(jù)表格導(dǎo)出

這時老板說了，我們不能只看總和的數(shù)據(jù)，最好是把詳細數(shù)據(jù)也給導(dǎo)出來。既然老板發(fā)話了，那就做吧。還是按照第一種的方法去做，結(jié)果提示我php崩潰了，再試一次發(fā)現(xiàn)提示寫入字節(jié)超出。打開php的配置文件php.ini

memory_limit = 128M

發(fā)現(xiàn)默認內(nèi)存已經(jīng)給到128M，應(yīng)該是足夠的了。于是我打開數(shù)據(jù)庫一看，嚯！

接近83萬條的數(shù)據(jù)進行查詢并導(dǎo)出，可不是會出問題嘛！怎么辦呢，于是我Google了一下，發(fā)現(xiàn)對于大數(shù)據(jù)(2萬條以上)的導(dǎo)出，最好是以.csv的形式。不說廢話，直接上代碼

1.controller文件

//導(dǎo)出清單

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();
 
 //類似的，跳轉(zhuǎn)到recharge這個model文件里的inventory方法去處理數(shù)據(jù)
 $data = Recharge::inventory($params);
 
 //設(shè)置導(dǎo)出的文件名
 $fileName = iconv('utf-8', 'gbk', '代理商統(tǒng)計清單'.date("Y-m-d"));
 
 //設(shè)置表頭
 $headlist = array('代理商','文章ID','文章標題','媒體','統(tǒng)計時間范圍','狀態(tài)','創(chuàng)建時間','審核時間','發(fā)稿時間','退稿時間','財務(wù)狀態(tài)','成本','銷售額','是否是預(yù)收款媒體類型','訂單類別');
 header('Content-Type: application/vnd.ms-excel');
 
 //指明導(dǎo)出的格式
 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編碼，一定要轉(zhuǎn)換，否則亂碼
 $headlist[$key] = iconv('utf-8', 'gbk', $value);
 }
 
 //將數(shù)據(jù)通過fputcsv寫到文件句柄
 fputcsv($fp, $headlist);
 
 //每隔$limit行，刷新一下輸出buffer，不要太大，也不要太小
 $limit = 100000;
 
 //逐行取出數(shù)據(jù)，不浪費內(nèi)存
 foreach ($data as $k => $v) {
 //刷新一下輸出buffer，防止由于數(shù)據(jù)過多造成問題
 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文件（因為這部分我要處理的過多，所以只選擇了部分代碼），在查詢數(shù)據(jù)那部分，因為要查的數(shù)據(jù)較多，所以可以結(jié)合我之前寫的關(guān)于Mysql大數(shù)據(jù)查詢處理的文章看一下

//清單導(dǎo)出

public static function inventory($params){
 //統(tǒng)計時間范圍
 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;
 }
 //查詢數(shù)據(jù)
 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;
 //查找的第一部分數(shù)據(jù)，使用asArray方法可以使我們查找的結(jié)果直接形成數(shù)組的形式，沒有其他多余的數(shù)據(jù)占空間（注意：我這里查找分三部分是因為我要查三種不同的數(shù)據(jù)）
 $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;
 //查找的第二部分數(shù)據(jù)
 $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;
 //查找的第三部分數(shù)據(jù)
 $list3 = Article::findBySql($map3)->asArray()->all();
 $list4 = ArrayHelper::merge($list1,$list2);
 $list = ArrayHelper::merge($list4,$list3);
 }
 //把結(jié)果按照顯示順序存到返回的數(shù)組中
 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'];
 //統(tǒng)計時間
 $inventory[$key]['time'] = $time;
 //狀態(tài)
 switch($value['status']){
 case 2:
  $inventory[$key]['status'] = '已發(fā)布';
  break;
 case 3:
  $inventory[$key]['status'] = '已退稿';
  break;
 case 5:
  $inventory[$key]['status'] = '異常稿件';
  break;
 }
 //創(chuàng)建時間
 $inventory[$key]['created'] = $value['created'];
 //審核時間
 $inventory[$key]['audit'] = $value['audit_at'];
 //發(fā)稿時間
 $inventory[$key]['issue_date'] = $value['issue_date'];
 //退稿時間
 $inventory[$key]['back_date'] = $value['back_date'];
 //財務(wù)狀態(tài)
 switch($value['finance_status']){
 case 0:
  $inventory[$key]['finance_status'] = '未到結(jié)算期';
  break;
 case 1:
  $inventory[$key]['finance_status'] = '可結(jié)算';
  break;
 case 2:
  $inventory[$key]['finance_status'] = '資源審批中';
  break;
 case 3:
  $inventory[$key]['finance_status'] = '財務(wù)審批中';
  break;
 case 4:
  $inventory[$key]['finance_status'] = '已結(jié)款';
  break;
 case 5:
  $inventory[$key]['finance_status'] = '未通過';
  break;
 case 6:
  $inventory[$key]['finance_status'] = '財務(wù)已審批';
  break;
 }
 //成本
 $inventory[$key]['cost'] = $value['cost'];
 //銷售額
 $inventory[$key]['company_cost'] = $value['company_cost'];
 //是否是預(yù)售
 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'] = '時間區(qū)間無退稿完成訂單';
  break;
 case 2:
  $inventory[$key]['order_type'] = '時間區(qū)間發(fā)布前退稿訂單';
  break;
 case 3:
  $inventory[$key]['order_type'] = '時間區(qū)間發(fā)布后時間區(qū)間退稿訂單';
  break;
 case 4:
  $inventory[$key]['order_type'] = '時間區(qū)間之前發(fā)布時間區(qū)間內(nèi)退稿訂單';
  break;
 case 5:
  $inventory[$key]['order_type'] = '異常訂單';
  break;
 }
 }
 }else{
 $inventory[0]['company_name'] = '無數(shù)據(jù)導(dǎo)出';
 }
 return $inventory;
}

3.導(dǎo)出結(jié)果

導(dǎo)出數(shù)量

導(dǎo)出的文件

基本上可以保證整個過程在2~4秒內(nèi)處理完成

三.合并單元格

老板一看做的不錯，說你順便把充值統(tǒng)計的導(dǎo)出也做了把，想想我都是處理過這么多數(shù)據(jù)的人了，還不是分分鐘搞定的事？來，上原型圖

噗，一口老血，話都說了，搞吧。在做的時候我發(fā)現(xiàn)，這次的導(dǎo)出主要是要解決單元格合并的問題。經(jīng)過查資料發(fā)現(xiàn)，PHP本身是實現(xiàn)不了單元格合并的，于是我打算通過phpexcel來實現(xiàn)

如果是使用PHPExcel的話，基本操作是這樣的（合并A1到E1）

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

結(jié)果

或者這樣的（合并A1到E4）

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

結(jié)果

這樣并不能滿足我的要求，首先它是一個一個合并的，其次我要顯示的充值金額下面的類型是會變化的，不可能固定寫死，然后每次都更改。所以放棄了這種方法。

后來在小伙伴的幫助下嘗試用html轉(zhuǎn)存excel的方法

1.方法文件（因為我要每天定時執(zhí)行，所以并沒有寫到controller層）

public function actionExcelRechargeStatistics(){

 //先定義一個excel文件
 $filename = date('【充值統(tǒng)計表】('.date('Y-m-d').'導(dǎo)出)').".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>';
 //查找最新的固化數(shù)據(jù)
 $search = RechargeStatistics::find()->where($where)->asArray()->all();
 if(!empty($search)){
 foreach ($search as $key => $value){
 $search[$key]['recharge'] = unserialize($value['recharge']);
 }
 }
 //html語句填充數(shù)據(jù)
 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.結(jié)果