thinkphp5 excel导出

最新推荐文章于 2024-02-23 10:17:21 发布

微齐天大圣.

最新推荐文章于 2024-02-23 10:17:21 发布

阅读量215

点赞数 1

分类专栏： thinkphp5 文章标签： thinkphp5 tp5 excel导出

本文链接：https://blog.csdn.net/qq_39521804/article/details/88243997

版权

thinkphp5 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

excel目录：/vendor/Classes

下面是贴出的代码：

<?php
namespace app\index\controller;
use think\Controller;
use think\Model;
use think\Db;

class Excel extends Controller{
     /*单个数据导出*/
    public function excels($id,$start_time,$end_time){
        $Mdevice=Model('device');
        $Md_user=Model('deviceuser');
        $Muser=Model('user');
        $Mdevicelog=Model('devicelog');

        $start_time=strtotime($start_time);
        $end_time=strtotime($end_time);
        $day=24*60*60;
        $overhaul=time() - 60*24*60*60;
        $score=100;                    //综合得分
        $fault=0;                      //故障次数
        $overproof=0;                  //超标次数
        $standar=array();              //记录超标值
        $i=1;
        $user_where['name']=session('pcname');
        $user=$Muser->field("id,name,phone,user_name,city_id,shop_id,ywh_stand,type")->where($user_where)->find();
        $ywh_stand=$user['ywh_stand'] ? 2:2; //超标标准
        $device_find=$Mdevice
            ->field("b.device_id,a.code,b.shop_name,b.address,a.id,a.ywh,a.pm,a.nmhc,(CASE WHEN a.overhaul< $overhaul THEN '需清洗' ELSE '正常' END) AS overhaul")
            ->alias("a")
            ->join("sn_deviceuser b","a.id=b.device_id")
            ->where("a.id={$id}")
            ->find();
        // 导出数据
        $devicelog_where['device_id']=$id;
        $devicelog_where['times']=array("BETWEEN","$start_time,$end_time");
        $devicelog_where['ywh']=["NEQ",0];
        $devicelog=$Mdevicelog
            ->field("ywh,pm,nmhc,state,times")
            ->where($devicelog_where)
            ->order("id desc")
            ->select();
        foreach($devicelog as $k=>$v){
        	$standar[$k]['state']=Dstate($v['state']);
            $standar[$k]['ywh']=$v['ywh'];
            $standar[$k]['pm']=$v['pm'];
            $standar[$k]['nmhc']=$v['nmhc'];
            $standar[$k]['times']=date("Y-m-d H:i:s",$v['times']);
            $standar[$k]['overhaul']='正常';
            if($v['state']==3){
            	$fault++;
                $standar[$k]['overhaul']='故障';
            }
            if($v['state']==2){
                $standar[$k]['overhaul']='离线';
            }
            if($v['ywh']>=$ywh_stand){
            	$overproof++;
                $standar[$k]['state']=Dstate($v['state']);
                $standar[$k]['ywh']=$v['ywh'];
                $standar[$k]['pm']=$v['pm'];
                $standar[$k]['nmhc']=$v['nmhc'];
                $standar[$k]['times']=date("Y-m-d H:i:s",$v['times']);
                $standar[$k]['overhaul']='超标';
            }
        }
        vendor("Classes.PHPExcel");
        vendor("Classes.PHPExcel.Writer.Writer");
        vendor("Classes.PHPExcel.Writer.Abstract");
        vendor("Classes.PHPExcel.Writer.Excel5");
        vendor("Classes.PHPExcel.Writer.Excel2007");
        vendor("Classes.PHPExcel.IOFactory");
        $objPHPExcel=new \PHPExcel;
        $objWriter=new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter=new \PHPExcel_Writer_Excel2007($objPHPExcel);

        //->mergeCells()  合并单元格
        if($user['type']=='cityrun'){
            $objPHPExcel->setActiveSheetIndex(0)
                // ->mergeCells('A1:B1:C1:D1:E1:F1')
                ->setCellValue('A1', '数据监测统计表');
        }else{
            $objPHPExcel->setActiveSheetIndex(0)
                // ->mergeCells('A1:B1:C1:D1:E1:F1')
                ->setCellValue('A1', '数据监测统计表');
        }

        $i+=1;
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, '状态');
        $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, '浓度');
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, '颗粒物');
        $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, '非甲烷总烃');
        $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, '时间');
        $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, '超标状态');
        foreach($standar as $k=>$v) {
            $i++;
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $v['state']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $v['ywh']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $v['pm']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $v['nmhc']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $v['times']);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $v['overhaul']);
        }
        
        $objPHPExcel->getActiveSheet()->setTitle('user');
        $objPHPExcel->setActiveSheetIndex(0);
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $PHPWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel,"Excel2007");
        $a3 =date('m',time());
        header('Content-Disposition: attachment;filename="' . $a3 . '月份编号'. $device_find['code'] .'.xlsx');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output");
    }
}

有其他问题可加我QQ：3053916151

PHPExcel下载地址：https://codeload.github.com/PHPOffice/PHPExcel/zip/1.8