關於YII2架構中如何將excel表格匯出的圖文代碼教程

最近在研究PHP的Yii架構，很喜歡，碰到匯出Excel的問題，研究了一下，就有了下面這篇文章，這篇文章主要給大家介紹了關於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 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

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

到這裡基本就完成所有的任務了！

總結