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

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