PHP Excel导出，遍历表头

最新推荐文章于 2023-07-16 15:29:15 发布

LB爱生活

最新推荐文章于 2023-07-16 15:29:15 发布

阅读量527

点赞数

分类专栏： PHP

本文链接：https://blog.csdn.net/qq_38388715/article/details/127774664

版权

php

PHP 专栏收录该内容

58 篇文章 0 订阅

订阅专栏

场景，后台创建了问卷题目，前台用户答卷，需求是把这套卷子答题的人及问卷详情都导出来

我的数据，题目是一串json,用户填写的内容是一串json

$newArr = [];//最后表格内容的对应关系
        if($data){
            foreach ($data as $k => $v){
                $data[$k]['ADDTIME'] = date('Y-m-d H:i:s',strtotime($v['ADDTIME']));
                $data[$k]['pfrom'] = json_decode($v['QCONTENT'],true);
                $data[$k]['pj_content'] = json_decode($v['SUB_CONTENT'],true);
            }

            foreach ($data as $k => $v){
                $newArr[$k][] = $v['ADDTIME'];
                $newArr[$k][] = $v['CUSTOMER_CODE'];
                $newArr[$k][] = $v['CUSTOMER_NAME'];
                $newArr[$k][] = $v['PJ_NAME'];
                $newArr[$k][] = $v['PJ_MOBILE'];
                foreach ($v['pj_content']['con'] as $j => $h){
                    $newArr[$k][] = $h;
                }
            }
           $pfrom = $data[0]['pfrom']['con'];
        }

        $basePath = Yii::$app->basePath;
        require_once $basePath."/phpexcel/PHPExcel.php";
        require_once $basePath."/phpexcel/PHPExcel/IOFactory.php";
        require_once $basePath."/phpexcel/PHPExcel/Cell.php";
        require_once $basePath."/phpexcel/PHPExcel/Writer/Excel2007.php";
        require_once $basePath."/phpexcel/PHPExcel/Cell/DataType.php";

        ini_set("memory_limit","-1");
        ini_set('max_execution_time', 1200);
        ini_set ('memory_limit', '256M');
        date_default_timezone_set('Asia/Shanghai');

        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        if(!$pfrom){
            return false;
        }
        //自定义表头
        $consult = [];
        $pfrom = array_column($pfrom,'name');//题目
        $consult = array('填写时间','客户编号','客户名称','填写人姓名','联系方式');//固定字段
        $pfromname = array_merge($consult,$pfrom);//合在一起组合成表头

        $letter_all=[];
        $letter=['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
        // 通过标题字段生成excel字母 例如3个字段，生成A-C
        $item=0;
        foreach($pfromname as $k => $v){
            if($item==26){
                $item=0;
            }
            $n=ceil(($k+1)/26);
            if($n==1){
                array_push($letter_all,$letter[$item]);
            }else{
                $n=$n-2;
                array_push($letter_all,$letter[$n].$letter[$item]);
            }
            $item++;
        }


        foreach ($pfromname as $k => $v){
            $objPHPExcel->getActiveSheet()->setCellValue($letter_all[$k].'1', $v);
            $objPHPExcel->getActiveSheet()->getStyle($letter_all[$k].'1')->getFont()->setBold(true);//字体加粗
            // $PHPExcel->getActiveSheet()->getColumnDimension($letter_all[$k])->setAutoSize(true);//内容自适应
        }

        $index=0;
//        print_r($newArr);die;
        if($newArr){
            foreach ($newArr as $key => $value) {
                foreach ($value as $k => $v){
                    if(is_array($v)){
                      $v = json_encode($v,JSON_UNESCAPED_UNICODE);
                    }
                    $objPHPExcel->getActiveSheet()->setCellValueExplicit($letter_all[$k].($index+2), $v, \PHPExcel_Cell_DataType::TYPE_STRING);
                }
                $index++;
            }

        }
        $objPHPExcel->getActiveSheet()->setTitle('问卷详情');
        $objPHPExcel->setActiveSheetIndex(0);
        $filename="问卷详情.xls";
        //解决IE不兼容问题
        $userBrowser = $_SERVER['HTTP_USER_AGENT'];
        if(preg_match('/MSIE/i',$userBrowser)){
            $filename = urlencode($filename);

        }
        $filename = iconv('UTF-8','GBK//IGNORE',$filename);
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="'.$filename.'"');
        header("Content-Transfer-Encoding: binary");
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');