Excel导出工具

该工具集成了PHPExcel的功能，可自动冻结首行、自动处理超长数字、导出到下载、导出到文件目录

话不多说直接上代码

<?php

namespace func;
require_once('PHPExcel.php');
use PHPExcel;
use PHPExcel_Cell;
use PHPExcel_Style_Alignment;

/**
 * Excel表格导出工具
 * Class ExcelExporter
 * @package func
 */
class ExcelExporter
{
     const UPLOAD_SITE_URL = $_SERVER['HTTP_HOST'];
    /**
     * 导出到http下载
     * @param $name
     * @param $data
     * @param $header
     * @throws \PHPExcel_Writer_Exception
     */
    public static function exportHttp($name,$data,$header){
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename='.$name.'.xlsx');
        $objWriter = self::createDataWriter($data,$header);
        //下载文件在浏览器窗口
        $objWriter->save('php://output');
    }

    /**
     * 导出到文件目录
     * @param $name
     * @param $data
     * @param $header
     * @param string $path
     * @return string
     * @throws \PHPExcel_Writer_Exception
     */
    public static function exportFile($name,$data,$header,$path = ''){
        $relativePath = '/'.($path?:'daily').'/';
        $fileDir = BASE_UPLOAD_PATH.$relativePath;
        if(!file_exists($fileDir)){
            mkdir($fileDir,0755,true);
        }
        $fileName = $name.date('-YmdHis').'.xlsx';
        $objWriter = self::createDataWriter($data,$header);
        $objWriter->save($fileDir.$fileName);
        return UPLOAD_SITE_URL.$relativePath.$fileName;
    }
    private static function createDataWriter($data,$header){
        //set_time_limit(0);
        ini_set('memory_limit','1024M');
        $objPHPExcel = new PHPExcel();
        //单元格居中和设置字体和字体大小
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);//居中
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//居中
        $objPHPExcel->getDefaultStyle()->getAlignment()->setShrinkToFit(true);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setWrapText(false);
        $objPHPExcel->getDefaultStyle()->getFont()->setName('Microsoft YaHei Light')->setSize(11);//设置字体
        $objPHPExcel->setActiveSheetIndex(0);
        $sheet = $objPHPExcel->getActiveSheet();
        $sheet->getRowDimension(1)->setRowHeight(30);//设置行高
        $sheet->getDefaultColumnDimension()->setAutoSize(true);//设置宽度
        $i=0;
        foreach ($header as $key=>$val) {
            $t_key = PHPExcel_Cell::stringFromColumnIndex($i);

            $sheet->getStyle($t_key.'1')->getFont()->setBold(true);//设置加粗
            $sheet->getColumnDimension($t_key)->setAutoSize(true);//设置宽度
            $sheet->setCellValue($t_key.'1',$val);
            $i++;
        }

        $a = 2; //合并起始行
        //设置导出内容
        foreach ($data as $key=> $item){
            if($key%1000 == 0){
                ob_flush();
                flush();
            }
            if($item['sub_rows']>1){
                $sheet->getRowDimension($a)->setRowHeight(20*$item['sub_rows']);
            }
            $i=0;
            foreach ($header as $head_key=>$val){
                $t_key = PHPExcel_Cell::stringFromColumnIndex($i);
                //字符化显示0开头的数字、长数字
                if(preg_match("/^0\\d+$/",$item[$head_key]) || (is_numeric($item[$head_key]) && strlen($item[$head_key])>10)){
                    $sheet->setCellValueExplicit($t_key.$a,$item[$head_key]??'');
                }else{
                    $sheet->setCellValue($t_key.$a,$item[$head_key]??'');
                }

                $i++;
            }
            $a++;
        }
        $sheet->freezePane('A2');
        //生成excel文件
        return \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    }
}