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

日期：2020-06-26 栏目：程序人生浏览：次

这时老板说了，我们不能只看总和的数据，最好是把详细数据也给导出来。既然老板发话了，那就做吧。还是按照第一种的方法去做，结果提示我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.导出结果

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