导出数据到EXCEL

前端ajax请求

exportVehcilesInfo:function () {
        var postData={};
        $.ajax({
            url: getWebBaseUrl() +'/index.php?r=CustomerVehicles/vehicles/export-excel',
            type: "post",
            dataType: 'json',
            data : $.extend({postData:postData},$.systemUtils.siginAuth.generateSigin("post")),
            success: function(data) {
                if(data.status){
                    $("#downloadVehcilesInfo").attr('href','');
                    $("#downloadVehcilesInfo").attr('href',getWebBaseUrl()+'/index.php?r=CustomerVehicles/vehicles/download-vehciles-info&fileName='+data.data+'&'+$.systemUtils.siginAuth.generateSigin("get"));

                    $("#downloadVehcilesInfo")[0].click();
                }else {
                    $.Alert.warning(data.msg);
                }
            },
            error: function(err) {
                //$.Alert.error('异常.<br>' + err.responseText);
            }
        });
    }

Controller层

	/**
     * 导出excel
     */
    public function actionExportExcel(){
        try{
            if(Yii::$app->request->isPost){
               return json_encode(['status'=>true,'msg'=>'','data'=>Vehicles::exportExcel()]); ;
            }
        }catch (\Exception $exception){
            return json_encode(['status'=>false,'msg'=>'系统异常','data'=>'']); ;
        }
    }

    /**
     * 下载文件
     */
    public function actionDownloadVehcilesInfo(){

        $fileName=$_GET['fileName'];
        $filePath="../modules/CustomerVehicles/other/vehicleExcelFile/exportExcel/".$fileName;

        header("Content-type:application/octet-stream");

        header("Content-Disposition:attachment;filename = ".basename($filePath));
        header("Accept-ranges:bytes");
        header("Accept-length:".filesize($filePath));
        readfile($filePath);

        if (file_exists($filePath)) {
            unlink($filePath);
        }
    }

model层

    /**
     * 导出excel
     */
    public static function exportExcel(){


        $vehicles = Vehicles::find()
            ->select(['vehicles_for_app.vin','vehicles_for_app.vin_17','vehicles_for_app.vehicle_type','vehicles_for_app.car_no',
                'vehicles_for_app.simno','vehicles_for_app.terminal_id','vehicles_for_app.ev_terminal_id',
                'vehicles_for_app.country6_terminal_id','vehicles_for_app.controller_id','vehicles_for_app.cabin_type',
                'vehicles_for_app.create_date',
                'vehicles_appkey_app_rel.org_id'])
            ->join('INNER JOIN',
                'vehicles_appkey_app_rel',
                'vehicles_for_app.vin=vehicles_appkey_app_rel.vin')
            ->andWhere(['vehicles_appkey_app_rel.deleted'=>0,'vehicles_appkey_app_rel.appkey'=>AdminUser::getCurrentAppkey()['appkey']])
            ->asArray()->all();

        $filePath = "../modules/CustomerVehicles/other/vehicleExcelFile/exportExcel/";
        self::createDir($filePath);

        $mtimestamp = sprintf("%.3f", microtime(true)); // 带毫秒的时间戳
        $timestamp = floor($mtimestamp); // 时间戳
        $milliseconds = round(($mtimestamp - $timestamp) * 1000); // 毫秒
        $fileName= date("YmdHis", $timestamp) . $milliseconds;
        $outputFileName=$filePath.$fileName.'.csv';


        $header=["VIN码","17位VIN码","车辆类型","车牌号","SIM卡号","终端号","EV终端号","国六终端号","控制器设备号","驾驶室类型","创建时间"];
        file_put_contents($outputFileName,self::charsetToGBK(implode(",", $header)).PHP_EOL, FILE_APPEND);

        $vehicleTypeArr=[
            1 => '柴油车',
            2 => '天然气车',
            3 => '新能源车',
            4 => '柴油混合动力车',
            5 => '天然气混合动力车'
        ];

        foreach ($vehicles as $key=>$value){
            $vehicle_type=isset($vehicleTypeArr[$value['vehicle_type']])?$vehicleTypeArr[$value['vehicle_type']]:$value['vehicle_type'];
            $content=$value['vin'].','.$value['vin_17'].','.$vehicle_type.','.$value['car_no'].','.$value['simno'].','.$value['terminal_id'].','.$value['ev_terminal_id']
                .','.$value['country6_terminal_id'].','.$value['controller_id'].','.$value['cabin_type'].','.$value['create_date'];
            file_put_contents($outputFileName,self::charsetToGBK($content).PHP_EOL, FILE_APPEND);
        }

        return basename($outputFileName);
    }

    /**
     * 建立文件夹
     *
     * @param string $aimUrl
     * @return boolean
     */
   public static function createDir($aimUrl)
    {
        $aimUrl = str_replace('', '/', $aimUrl);
        $aimDir = '';
        $arr = explode('/', $aimUrl);
        $result = true;
        foreach ($arr as $str) {
            $aimDir .= $str . '/';
            if (!file_exists($aimDir)) {
                $result = mkdir($aimDir);
            }
        }
        return $result;
    }

    /**
     * 将非GBK字符集的编码转为GBK
     *
     * @param mixed $mixed 源数据
     *
     * @return mixed GBK格式数据
     */
    public static function charsetToGBK($mixed)
    {
        if (is_array($mixed)) {
            foreach ($mixed as $k => $v) {
                if (is_array($v)) {
                    $mixed[$k] = charsetToGBK($v);
                } else {
                    $encode = mb_detect_encoding($v, array('ASCII', 'UTF-8', 'GB2312', 'GBK', 'BIG5'));
                    if ($encode == 'UTF-8') {
                        $mixed[$k] = iconv('UTF-8', 'GBK', $v);
                    }
                }
            }
        } else {
            $encode = mb_detect_encoding($mixed, array('ASCII', 'UTF-8', 'GB2312', 'GBK', 'BIG5'));
            if ($encode == 'UTF-8') {
                $mixed = iconv('UTF-8', 'GBK', $mixed);
            }
        }
        return $mixed;
    }