insure/application/api/controller/ExportData.php

<?php
namespace app\api\controller;
use app\admin\model\Category;
use app\common\controller\Api;
use think\Db;

/**
 * 数据导出
 * Class ExportData
 * @package app\api\controller
 */
class ExportData extends Api{


    protected $noNeedLogin = '*';
    protected $noNeedRight = '*';


    /**
     * 导出
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function export(){
        $id = $this->request->post("id",0);
        vendor('PHPExcel.PHPExcel');
        $fileName = '保险'."_" . date("Y_m_d_H_i_s",time()) . ".xls";
        //$file = '456.xls';
        $file_path = 'download/'.$fileName;
        $PHPExcel = new \PHPExcel();
        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle("保险"); //给当前活动sheet设置名称



        $result = Db::name('product')->where(array('category_id'=>$id))
            ->field('id,name,content,sort')->order('sort asc')->select();



        $zm = ['C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];

        $id_names = Db::name('category')->where(array('id'=>$id))->value('name');
        $PHPSheet->setCellValue("A1",'排名');
        $PHPSheet->setCellValue("B1",$id_names);

        $i = 2;
        $j = 1;


//        if($result){
//            $category_content = Db::name('Category')->where(array('id'=>$id))->value('content');
//            $content = json_decode($category_content,true);
//            p($content);
//            die;
//        }

        if($result){
//            foreach ($result as $k=>$v){
//                $result[$k]['len'] = mb_strlen($result[$k]['content'],'UTF8');
//            }
//            $len = array_column($result,'len');
//            array_multisort($len,SORT_DESC,$result);

            foreach ($result as $key=>$value){
                $outh = json_decode($result[$key]['content'],true);
                $category_content = Db::name('Category')->where(array('id'=>$id))->value('content');
                $content = json_decode($category_content,true);
                $c = $content;
                $count = count($content);

                //$end = '';
                foreach ($c as $k=>$v){
                    if($key == 0){
                        $PHPSheet->setCellValue($zm[$k].'1',$v['key']);
                        $end = $zm[$k].'1';
                    }
                    $PHPSheet->setCellValue($zm[$count].'1','其他属性');
                    $PHPSheet->getColumnDimension($zm[$count])->setWidth(40);
                    $PHPSheet->setCellValue('A'.$i,$value['sort']);
                    $PHPSheet->setCellValue('B'.$i,$value['name']);
                    if(isset($v['name'])){
                        $PHPSheet->setCellValue($zm[$k].$i,$outh[$k]['name']);
                    }else{
                        $PHPSheet->setCellValue($zm[$k].$i,'无');
                    }
                    $PHPSheet->getColumnDimension($zm[$k])->setWidth(12);
                    $PHPSheet->getRowDimension($j)->setRowHeight(20);
//                    if($k >= 1){
//
//                    }
//                    $PHPSheet->setCellValue($zm[$count].$k,'其他属性11111');

                }
                if($outh){
                    $con = '';
                    $outh = $this->test($outh,$content);
                    foreach ($outh as $k=>$v){
                        $con .= $outh[$k]['key'].' : '.$outh[$k]['name']." \r\n ";
                    }
                }
                $PHPSheet->getStyle()->getAlignment($zm[$count].($key+2))->setWrapText(true);
                $PHPSheet->setCellValue($zm[$count].($key+2),$con);
                $end = $zm[$count].'1';
                $j++;
                $i++;

            }

            $PHPSheet->getStyle('A1:'.$end)->getFont()->setBold(true)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);
            $PHPSheet->getStyle('A1:'.$end)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('094EC0');
            //合并单元格
           // $PHPSheet->mergeCells('A1:B1');
            //设置行高
            $PHPSheet->getRowDimension('1')->setRowHeight(30);    //第一行行高
            $PHPSheet->getColumnDimension('B')->setWidth(20);
           // $PHPSheet->getColumnDimension('i')->setWidth(40);
            
        $PHPSheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
                //$PHPSheet->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_LEFT);
            
            //$PHPSheet->setCellValue("A1",$id_names);
           // $PHPSheet->getStyle("A1:".$end)->getFont()->setBold(true);
            //$PHPSheet->getStyle("A1:".$end)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //$PHPSheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//            $PHPSheet->getStyle("A1:".$end)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //左右居中
//
//            $PHPSheet->getStyle("A1:".$end)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中
            $list['file_path'] = 'http://'.$_SERVER['HTTP_HOST'].'/'.$file_path;
            $objWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel2007');
            $objWriter->save($file_path); //文件直接下载我指定的服务器目录中
            $this->success(__('成功'),$list);
        }else{
            $this->error('没有数据');
        }
    }


    public function test($row = [],$rss = []){
        $content = $row;   //当前全部属性
        $content_one = $rss; //模板属性
        $cha = [];//客户顾定模板设置
        $cha_arr = []; //自由追加的数据
        if($content)
        {

            foreach ($content as $key=>$val)
            {
                if(!isset($val['key']))  continue;
                if(isset($content_one[$key]))
                {     //print_r($content_one[$key]);
                    /// var_dump($val['key']);
                    if ($val['key'] == $content_one[$key]['key'])
                    {
                        $cha[] = $val;
                    }
                    else
                    {
                        $cha_arr[] = $val;
                    }

                } else {
                    $cha_arr[] = $val;
                }


            }
            return $cha_arr;

        }
    }


    /**
     * 获取一级分类
     */
    public function getPrimaryClassification(){
        $list = Category::where(array('pid'=>0))->field('id,name,type')->select();
        $this->success(__('成功'),$list);
    }

    /**
     * 获取二级分类
     */
    public function getSecondaryClassification(){
        $id = $this->request->post("id",0);
        $list = Category::where(array('pid'=>$id))->field('id,name,type')->select();
        $this->success(__('成功'),$list);
    }


    /**
     * 获取三级分类
     */
    public function getThreeClassification(){
        $id = $this->request->post("id",0);
        $list = Category::where(array('pid'=>$id))->field('id,name,type')->select();
        $this->success(__('成功'),$list);
    }


    /**
     * 导出所有用户数据
     * 直接url访问，不能使用ajax，因为ajax要求返回数据，和PHPExcel一会浏览器输出冲突！将数据作为参数
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Writer_Exception
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function out(){

        $result = Db::name('category')->field('id,name,pid')->order('id desc')->select();

        $filename = "用户测评数据";
        vendor('PHPExcel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        //设置保存版本格式
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);

        //设置表头
        $objPHPExcel->getActiveSheet()->setCellValue('A1','id');
        $objPHPExcel->getActiveSheet()->setCellValue('B1','姓名');
        $objPHPExcel->getActiveSheet()->setCellValue('C1','手机');
        $objPHPExcel->getActiveSheet()->setCellValue('D1','意向院校');
        $objPHPExcel->getActiveSheet()->setCellValue('E1','测评分值');
        $objPHPExcel->getActiveSheet()->setCellValue('F1','学历背景得分');
        $objPHPExcel->getActiveSheet()->setCellValue('G1','企业背景得分');
        $objPHPExcel->getActiveSheet()->setCellValue('H1','收入水平得分');
        $objPHPExcel->getActiveSheet()->setCellValue('I1','发展潜力得分');
        $objPHPExcel->getActiveSheet()->setCellValue('J1','管理经验得分');
        $objPHPExcel->getActiveSheet()->setCellValue('K1','英语水平得分');
        $objPHPExcel->getActiveSheet()->setCellValue('L1','背景优势');
        $objPHPExcel->getActiveSheet()->setCellValue('M1','背景劣势');
        $objPHPExcel->getActiveSheet()->setCellValue('N1','匹配院校');

        //改变此处设置的长度数值
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
        //输出表格
        $str = '用户未进行测评';
        foreach ($result as $key => &$val) {

            $i=$key+2;//表格是从2开始的
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$i,$val['id']);
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$i,$val['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('C'.$i,$val['pid']);

        }


        $fileName = iconv("utf-8", "gb2312", $filename); // 重命名表

        $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表

        header('Content-Type: application/vnd.ms-excel');

        header("Content-Disposition: attachment;filename='$fileName'");

        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $objWriter->save('php://output'); // 文件通过浏览器下载

        exit();

//
//        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.'.xls');
//        header("Content-Transfer-Encoding:binary");
//        $objWriter->save('php://output');
    }
}