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

框架(架构)  /  管理员 发布于 7年前   208

前言

表格的导入导出是我们在日常开发中经常会遇到的一个功能，正巧在最近的项目中做到了关于表格输出的功能，并且之前用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文件

 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; "); 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 = ''; $excel_content .= ''; $excel_content .= '  '; foreach ($rechargelist1 as $v => $t){ $excel_content .= ''; } $excel_content .= ''; //查找最新的固化数据 $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 .= ''; $excel_content .= ''; foreach ($rechargelist1 as $v=>$t){ $price = 0; foreach ($k['recharge'] as $q=>$w){  if($w['recharge_id'] == $v){  $price = $w['price'];  break;  } } $excel_content .= ''; } $excel_content .= ''; $excel_content .= ''; $excel_content .= ''; } } $excel_content .= '
   
   
ID
   
公司名称
   
充值金额
   
充值大小
   
实际消费
   
当前余额
   
   
'.$t.'
  
'.$k['company_id'].'
'.$k['company_name'].'
'.$price.'
'.$k['total'].'
'.$k['consume'].'
'.($k['total']-$k['consume']).'
'; echo $excel_content; die;}

2.结果

到这里基本就完成所有的任务了！

总结

以上就是这篇文章的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对AIDI的支持。

您可能感兴趣的文章:

• Yii中使用PHPExcel导出Excel的方法
• Yii2框架中使用PHPExcel导出Excel文件的示例
• Yii框架使用PHPExcel导出Excel文件的方法分析【改进版】
• Yii Framework框架使用PHPExcel组件的方法示例
• Yii安装与使用Excel扩展的方法
• Yii框架扩展CGridView增加导出CSV功能的方法
• Yii2使用自带的UploadedFile实现的文件上传
• Yii配置文件用法详解
• Yii2中YiiBase自动加载类、引用文件方法分析(autoload)
• Yii2实现UploadedFile上传文件示例
• YII中Ueditor富文本编辑器文件和图片上传的配置图文教程
• Yii框架中使用PHPExcel的方法分析