這篇文章給大家介紹使用YII2框架怎么導(dǎo)出excel表格，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。
為秀峰等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計(jì)制作服務(wù)，及秀峰網(wǎng)站建設(shè)行業(yè)解決方案。主營(yíng)業(yè)務(wù)為網(wǎng)站設(shè)計(jì)、網(wǎng)站制作、秀峰網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會(huì)得到認(rèn)可，從而選擇與我們長(zhǎng)期合作。這樣，我們也可以走得更遠(yuǎn)！一.普通excel格式表格輸出
先是最普通的導(dǎo)出.xls格式的表格。首先先看一下表格在網(wǎng)站的顯示效果
這里可以看到整個(gè)表格一共是7列。下面來看代碼的實(shí)現(xiàn)。
1.controller文件
重慶分公司
重慶分公司
//導(dǎo)出統(tǒng)計(jì)
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();
//獲取傳過來的信息（時(shí)間，公司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這個(gè)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)計(jì)'.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文件
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ù)的存儲(chǔ)順序要和輸出的表格里的順序一樣
$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í)老板說了，我們不能只看總和的數(shù)據(jù)，好是把詳細(xì)數(shù)據(jù)也給導(dǎo)出來。既然老板發(fā)話了，那就做吧。還是按照第一種的方法去做，結(jié)果提示我php崩潰了，再試一次發(fā)現(xiàn)提示寫入字節(jié)超出。打開php的配置文件php.ini
memory_limit = 128M
發(fā)現(xiàn)默認(rèn)內(nèi)存已經(jīng)給到128M，應(yīng)該是足夠的了。于是我打開數(shù)據(jù)庫一看，嚯！
接近83萬條的數(shù)據(jù)進(jìn)行查詢并導(dǎo)出，可不是會(huì)出問題嘛！怎么辦呢，于是我Google了一下，發(fā)現(xiàn)對(duì)于大數(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這個(gè)model文件里的inventory方法去處理數(shù)據(jù)
$data = Recharge::inventory($params);
//設(shè)置導(dǎo)出的文件名
$fileName = iconv('utf-8', 'gbk', '代理商統(tǒng)計(jì)清單'.date("Y-m-d"));
//設(shè)置表頭
$headlist = array('代理商','文章ID','文章標(biāo)題','媒體','統(tǒng)計(jì)時(shí)間范圍','狀態(tài)','創(chuàng)建時(shí)間','審核時(shí)間','發(fā)稿時(shí)間','退稿時(shí)間','財(cái)務(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ù)，不浪費(fèi)內(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文件（因?yàn)檫@部分我要處理的過多，所以只選擇了部分代碼），在查詢數(shù)據(jù)那部分，因?yàn)橐榈臄?shù)據(jù)較多，所以可以結(jié)合我之前寫的關(guān)于Mysql大數(shù)據(jù)查詢處理的文章看一下
//清單導(dǎo)出
public static function inventory($params){
//統(tǒng)計(jì)時(shí)間范圍
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;
//查找的第一部分?jǐn)?shù)據(jù)，使用asArray方法可以使我們查找的結(jié)果直接形成數(shù)組的形式，沒有其他多余的數(shù)據(jù)占空間（注意：我這里查找分三部分是因?yàn)槲乙槿N不同的數(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;
//查找的第二部分?jǐn)?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;
//查找的第三部分?jǐn)?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'];
//文章標(biāo)題
$inventory[$key]['title'] = $value['title'];
//媒體
$inventory[$key]['media'] = $value['media_name'];
//統(tǒng)計(jì)時(shí)間
$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)建時(shí)間
$inventory[$key]['created'] = $value['created'];
//審核時(shí)間
$inventory[$key]['audit'] = $value['audit_at'];
//發(fā)稿時(shí)間
$inventory[$key]['issue_date'] = $value['issue_date'];
//退稿時(shí)間
$inventory[$key]['back_date'] = $value['back_date'];
//財(cái)務(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'] = '財(cái)務(wù)審批中';
break;
case 4:
$inventory[$key]['finance_status'] = '已結(jié)款';
break;
case 5:
$inventory[$key]['finance_status'] = '未通過';
break;
case 6:
$inventory[$key]['finance_status'] = '財(cái)務(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'] = '時(shí)間區(qū)間無退稿完成訂單';
break;
case 2:
$inventory[$key]['order_type'] = '時(shí)間區(qū)間發(fā)布前退稿訂單';
break;
case 3:
$inventory[$key]['order_type'] = '時(shí)間區(qū)間發(fā)布后時(shí)間區(qū)間退稿訂單';
break;
case 4:
$inventory[$key]['order_type'] = '時(shí)間區(qū)間之前發(fā)布時(shí)間區(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)出的文件
基本上可以保證整個(gè)過程在2~4秒內(nèi)處理完成
三.合并單元格
老板一看做的不錯(cuò)，說你順便把充值統(tǒng)計(jì)的導(dǎo)出也做了把，想想我都是處理過這么多數(shù)據(jù)的人了，還不是分分鐘搞定的事？來，上原型圖
噗，一口老血，話都說了，搞吧。在做的時(shí)候我發(fā)現(xiàn)，這次的導(dǎo)出主要是要解決單元格合并的問題。經(jīng)過查資料發(fā)現(xiàn)，PHP本身是實(shí)現(xiàn)不了單元格合并的，于是我打算通過phpexcel來實(shí)現(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é)果
這樣并不能滿足我的要求，首先它是一個(gè)一個(gè)合并的，其次我要顯示的充值金額下面的類型是會(huì)變化的，不可能固定寫死，然后每次都更改。所以放棄了這種方法。
后來在小伙伴的幫助下嘗試用html轉(zhuǎn)存excel的方法
1.方法文件（因?yàn)槲乙刻於〞r(shí)執(zhí)行，所以并沒有寫到controller層）
public function actionExcelRechargeStatistics(){
//先定義一個(gè)excel文件
$filename = date('【充值統(tǒng)計(jì)表】('.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");
//時(shí)間條件
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 = '';
$excel_content .= '';
$excel_content .= '
ID
公司名稱
充值金額
充值大小
實(shí)際消費(fèi)
當(dāng)前余額
';
foreach ($rechargelist1 as $v => $t){
$excel_content .= ''.$t.'';
}
$excel_content .= '
';
//查找新的固化數(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 .= ''.$k['company_id'].' ';
$excel_content .= ''.$k['company_name'].' ';
foreach ($rechargelist1 as $v=>$t){
$price = 0;
foreach ($k['recharge'] as $q=>$w){
if($w['recharge_id'] == $v){
$price = $w['price'];
break;
}
}
$excel_content .= ''.$price.' ';
}
$excel_content .= ''.$k['total'].' ';
$excel_content .= ''.$k['consume'].' ';
$excel_content .= ''.($k['total']-$k['consume']).' ';
}
}
$excel_content .= '';
echo $excel_content;
die;
} 2.結(jié)果
關(guān)于使用YII2框架怎么導(dǎo)出excel表格就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。
