tp5实现excel导出

tp5实现excel导出

1、下载类文件
https://github.com/PHPOffice/PHPExcel
将下载后的目录放在项目根目录的 extend/下
即 extend/PHPExcel/

2、封装函数
	use think\Loader;

	public static function generateExcel($filename, $data, $field = [])
    {
        Loader::import('PHPExcel.PHPExcel');
        Loader::import('PHPExcel.PHPExcel.Writer.PHPExcel_Writer_Excel2007');

        // 创建一个excel
        $objPHPExcel = new \PHPExcel();
        // 保存excel—2007格式
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        //或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式

        $start = 'A';

        foreach($field as $key => $val){
            $objPHPExcel->getActiveSheet()->setCellValue(chr(ord($start)+$key).'1', self::convertUTF8($val[1]));
        }

        if($data){
            foreach($data as $key => $val){
                foreach ($field as $k => $v) {
                    $objPHPExcel->getActiveSheet()->setCellValue(chr(ord($start)+$k).($key+2), self::convertUTF8($val[$v[0]]));
                }
            }
        }

        // 保存为文件
        // $dir = './excel/'.date('Y-m-d');
        // if(!is_dir($dir)){
        //     mkdir($dir, 0777, true);
        // }
        // $fullname = $dir . '/' . $filename . ".xlsx";
        // $objWriter->save($fullname);

        // 输出到浏览器
        self::outputHeader($filename);
        $objWriter->save('php://output');

        return true;
    }

    public static function outputHeader($filename)
    {
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename="'.$filename.'.xlsx"');
        header("Content-Transfer-Encoding:binary");
    }

    public static function convertUTF8($str)
    {
       return mb_convert_encoding($str, 'utf-8');
    }

3、使用
	
	public function downloadAll(){
        $condition = $this->initRequestParam();
        if ($condition === false) {
            return $this->sendError($this->error_code, $this->error_msg);
        }

        if(date('m', $condition->start) != date('m', $condition->end)){
            return $this->sendError(10003, '请最多选择一个自然月的数据进行导出');
        }
        
        if(!$condition->partner_id){
            return $this->sendError(100003, '参数错误');
        }
        $partner = PartnerModel::model()->getOne(['partner_id'=>$condition->partner_id]);
        if(!$partner){
            return $this->sendError(100003, '推广渠道不存在');
        }
        
        $list = PartnerIncomeMonthModel::model()->getPartnerList($condition);

        foreach ($list->items() as $item) {
            $item->recharge_real = $item->recharge_data;
            // 充值总额
            $item->recharge_data = round($item->recharge_data * (1 - $item->der_rate), 2);
            // 提现总额
            $item->withdraw      = round($item->withdraw_ali + $item->withdraw_bank, 2);
            // 利润
            $item->profit        = $item->recharge_data - $item->withdraw;
            // 我的收益
            $item->income        = $item->profit * $item->share_rate;
            // 日期
            $item->time          = date('Y-m-d',  $item->time);
            // 利润分成占比
            $item->share_rate    = $item->share_rate * 100 .'%';
        }
        $field = [
            ['player_num','新增账户'],
            ['player_active','活跃账户'],
            ['recharge_num','充值账户数'],
            ['recharge_real','真实充值总额'],
            ['recharge_data','扣量后充值总额'],
            ['withdraw','提现总额'],
            ['profit','利润'],
            ['share_rate','利润分成占比'],
            ['income','渠道收益'],
            ['date','日期'],
        ];
        Helper::generateExcel('推广渠道报表_总后台', $list, $field);
    }