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

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);
}

//统计导出
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;
}
}

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',$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',$value);
}
fputcsv($fp,$row);
}
}

asArray()->all(); $where2 = ''; $where2 .= ' AND (`issue_date` BETWEEN '.'\''.$date_min.'\''.' AND '.'\''.$date_max.'\')'; $where2 .= ' AND (`back_date` > \''.$date_max.'\')'; $map2 = 'select company.name,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,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; }

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

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