2019獨(dú)角獸企業(yè)重金招聘Python工程師標(biāo)準(zhǔn)>>>

最近的項(xiàng)目做到關(guān)于表格輸出的功能，之前用TP的時(shí)候也做過，趁著這次功能比較多樣的機(jī)會(huì)整理一下

?

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

?

一.普通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文件

<?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)計(jì)導(dǎo)出public static function statistics($params){//導(dǎo)出時(shí)間條件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 = 'selectarticle.company_id,article.cost,article.company_costfrom 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ù)的存儲(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)頁(yè)顯示的基本一樣。

二.大數(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ù)庫(kù)一看，嚯！

接近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ù)查詢處理的文章看一下

PHP

//清單導(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 = 'selectcompany.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_advancefrom articleLEFT JOIN custom_package ON custom_package.id = article.custom_package_idLEFT JOIN `order`????????ON custom_package.order_id = `order`.`id`LEFT JOIN company????????ON company.id = article.company_idLEFT JOIN media??????????ON media.id = article.media_idwhere 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 = 'selectcompany.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_advancefrom articleLEFT JOIN custom_package ON custom_package.id = article.custom_package_idLEFT JOIN `order`????????ON custom_package.order_id = `order`.`id`LEFT JOIN company????????ON company.id = article.company_idLEFT JOIN media??????????ON media.id = article.media_idwhere 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 = 'selectcompany.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_advancefrom articleLEFT JOIN custom_package ON custom_package.id = article.custom_package_idLEFT JOIN `order`????????ON custom_package.order_id = `order`.`id`LEFT JOIN company????????ON company.id = article.company_idLEFT JOIN media??????????ON media.id = article.media_idwhere 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??????=?? '<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">實(shí)際消費(fèi)</th><th rowspan="2">當(dāng)前余額</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é)果

到這里基本就完成所有的任務(wù)了！

轉(zhuǎn)載于:https://my.oschina.net/u/1587469/blog/1456656

總結(jié)

以上是生活随笔為你收集整理的YII2框架的excel表格导出的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。