导出工具类

<?php

namespace app\service;

class ExportUtil
{
    public static $cellName = [
        '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', 'AA',
        'AB', 'AC','AD','AE', 'AF','AG','AH','AI', 'AJ', 'AK', 'AL','AM','AN','AO','AP','AQ','AR', 'AS', 'AT','AU', 'AV','AW', 'AX',
        'AY', 'AZ'
    ];

    /**
     * 公共数据导出实现功能
     * @param $expTitle string 导出文件名
     * @param $expCellName array 导出文件列名称数组
     * @param $expTableData array 导出数据
     * @param $searchCriteria array 搜索条件
     */
    public static function export_excel($expTitle,$expCellName,$expTableData,$searchCriteria = [])
    {
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $expTitle . date('_Ymd');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = self::getColumnNum($expCellName); //总列数
        $dataNum = count($expTableData); //总行数
        $objPHPExcel = new \PHPExcel();

        //设置头部导出时间备注
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . self::$cellName[$cellNum - 1] . '1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle . ' 导出时间:' . date('Y-m-d H:i:s'));
        //设置搜索条件
        $searchNum = count($searchCriteria);
        if($searchNum > 0){
            $search_arr = [];
            for ($i = 0; $i < count($searchCriteria); $i++) {
                $search_arr[] = $searchCriteria[$i]['title'].'：'.$searchCriteria[$i]['name'];
            }
            $search_str = implode(' | ',$search_arr);
            $objPHPExcel->getActiveSheet(0)->mergeCells('A2:' . self::$cellName[$cellNum - 1] . '2');//合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2' , $search_str);
        }


        //设置标题行
        $titleLine = 4;
        $maxTitleLine = self::getLevelNum($expCellName);//标题最大深度

        $col = 0;
        foreach ($expCellName as $i=>$item){
            //设置居中
            $objPHPExcel->getActiveSheet()->getStyle(self::$cellName[$col] . $titleLine)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//所有单元格文字居中
            $objPHPExcel->getActiveSheet()->getStyle(self::$cellName[$col] . $titleLine)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//所有单元格文字垂直居中
            //设置值
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue(self::$cellName[$col] . $titleLine, $item['title']);
            //设置宽度
            if(isset($item['width'])){
                $objPHPExcel->getActiveSheet(0)->getColumnDimension(self::$cellName[$col])->setWidth($item['width']);
            }else{
                $objPHPExcel->getActiveSheet(0)->getColumnDimension(self::$cellName[$col])->setAutoSize(true);//内容自适应
            }
            //其他处理
            if(isset($item['handleColumn'])){
                $item['handleColumn']($objPHPExcel,self::$cellName[$col]);
            }

            //合并行
            if(isset($item['rowSpan'])){
                $objPHPExcel->getActiveSheet()->mergeCells(self::$cellName[$col] . $titleLine.':'.self::$cellName[$col].($titleLine+$item['rowSpan']-1));
            }

            if(isset($item['children'])){
                $colSpan = self::getColumnNum($item['children']);
                //合并列
                $objPHPExcel->getActiveSheet()->mergeCells(self::$cellName[$col] . $titleLine.':'.self::$cellName[$col + $colSpan - 1].$titleLine);

                $temp_cal = $col;
                $col = $col + $colSpan - 1;

                //设置第二行标题
                foreach ($item['children'] as $ii=>$child){
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue(self::$cellName[$temp_cal] . ($titleLine+1), $child['title']);

                    //设置居中
                    $objPHPExcel->getActiveSheet()->getStyle(self::$cellName[$temp_cal] . ($titleLine+1))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//所有单元格文字居中
                    $objPHPExcel->getActiveSheet()->getStyle(self::$cellName[$temp_cal] . ($titleLine+1))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//所有单元格文字垂直居中

                    if(isset($child['width'])){
                        $objPHPExcel->getActiveSheet(0)->getColumnDimension(self::$cellName[$temp_cal])->setWidth($child['width']);
                    }else{
                        $objPHPExcel->getActiveSheet(0)->getColumnDimension(self::$cellName[$temp_cal])->setAutoSize(true);//内容自适应
                    }
                    if(isset($child['handleColumn'])){
                        $child['handleColumn']($objPHPExcel,self::$cellName[$temp_cal]);
                    }
                    $temp_cal++;
                }
            }

            $col++;
        }

        //赋值
        for ($i = 0; $i < $dataNum; $i++) {
            $lineData = $expTableData[$i];//行数据
            $col = 0;//列号
            $row = $i + $titleLine + $maxTitleLine; //行号
            foreach ($expCellName as $item){
                if(isset($item['children'])){
                    //二级
                    $colSpan = self::getColumnNum($item['children']);
                    foreach($item['children'] as $ii=>$child){
                        self::setColumn($objPHPExcel,$lineData,$child,$col+$ii,$row);
                    }
                    $col = $col + $colSpan - 1;
                }else{
                    self::setColumn($objPHPExcel,$lineData,$item,$col,$row);
                    //合并行
                    if(isset($lineData['rowSpan']) && isset($lineData['rowSpanCol']) && $lineData['rowSpanCol'] == $col){
                        $objPHPExcel->getActiveSheet()->mergeCells(self::$cellName[$col] . $row.':'.self::$cellName[$col].($row+$lineData['rowSpan']-1));
                    }
                }
                $col++;
            }
        }
//        exit;
        ob_end_clean();//这一步非常关键，用来清除缓冲区防止导出的excel乱码
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//"xls"参考下一条备注
        $objWriter = \PHPExcel_IOFactory::createWriter(
            $objPHPExcel, 'Excel5'
        );//"Excel2007"生成2007版本的xlsx，"Excel5"生成2003版本的xls
        $objWriter->save('php://output');
    }

    /**
     * 设置值
     * @param $objPHPExcel
     * @param $lineData array 行数据
     * @param $item array 标题信息
     * @param $col int 所在列
     * @param $row int 所在行
     */
    public static function setColumn($objPHPExcel,$lineData,$item,$col,$row)
    {
        $objPHPExcel->getActiveSheet()->getStyle(self::$cellName[$col] . $row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//所有单元格文字居中
        $objPHPExcel->getActiveSheet()->getStyle(self::$cellName[$col] . $row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//所有单元格文字垂直居中

        //处理赋值
        $value = $lineData[$item['name']];
        if(isset($item['render'])){
            $value = $item['render']($value,$lineData,$row);
        }
        if(isset($item['handleValue'])){
            $item['handleValue']($objPHPExcel,self::$cellName[$col] . $row,$value);
        }else{
            $objPHPExcel->getActiveSheet(0)->setCellValue(
                self::$cellName[$col] . $row, $value
            );
        }
    }

    /**
     * 获取总列数
     * @param $data
     * @param int $count
     * @return int
     */
    public static function getColumnNum($expCellName){
        $count = 0;
        foreach ($expCellName as $item){
            if(!isset($item['children'])){
                $count++;
            }else{
                $count += self::getColumnNum($item['children']);
            }
        }
        return $count;
    }

    public static function getLevelNum($expCellName)
    {
        $max_depth=1;
        foreach ($expCellName as $key=>$value){
            if(isset($value['children'])){
                $max=self::getLevelNum($value['children'])+1;
                if($max_depth<$max){
                    $max_depth=$max;
                }
            }
        }
        return $max_depth;
    }
}

使用方法

//导出产币标准
    public static function coinageRecord($data,$currencies)
    {
        $xlsName = "产币标准";
        //查出字段输出对应Excel对应的列名
        $xlsCell = [
            ['title'=>'日期','name'=>'day'],
        ];
        foreach ($currencies as $currency){
            $xlsCell[] = ['title'=>$currency['name'],'name'=>$currency['name']];
        }
        //搜索条件
        $search = [];
        $param = request()->param();
        if(isset($param['start_time']) && $param['start_time']){
            $search[] = ['title'=>'开始日期','name'=>$param['start_time']];
        }
        if(isset($param['end_time']) && $param['end_time']){
            $search[] = ['title'=>'结束日期','name'=>$param['end_time']];
        }
        //公共方法调用
        ExportUtil::export_excel($xlsName,$xlsCell,$data,$search);
    }