YII2框架的excel表格导出

最近的项目做到关于表格输出的功能,之前用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文件

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


最终导出的效果(单元格大小导出后调整过)可以看到和网页显示的基本一样。

二.大数据表格导出

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

PHP

//清单导出
    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; charset=utf-8");
        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      =   '<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">实际消费</th>
                                        <th rowspan="2">当前余额</th>
                                     </tr>
                                     <tr>
                                 ';
        foreach ($rechargelist1 as $v => $t){
                $excel_content     .=   '<th colspan="1">'.$t.'</th>';
        }
        $excel_content     .=   '</tr>
                                 </thead>';
        //查找最新的固化数据
        $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  .= '<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.结果

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

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页