php导出excel封装类

安装包
composer require phpoffice/phpexcel
<?php
/**
 * 公共的导出类@author:zhaoyaei
 * $column     :导出的第一行标题
 * $result     :需要导出的结果集
 * $path       :导出文件的保存路径
 * $sheet_num  :每个工作薄的最大行数(可选)
 * @return     :保存的地址
 */
class Export{
    
    public function __construct(){
        //初始化类，引入相关类文件
        include "PHPExcel.php";
        include "PHPExcel/IOFactory.php";
    }

    //导出设置
    public function createxcel($column,$result,$path,$sheet_num = null){
        //检查数据的合法性
        if(empty($column) || empty($path) || $path == "" || empty($result)){
            return false;
        }

        //如果数据较大，则分薄导出
        if(empty($sheet_num) || $sheet_num <= 0){
            $sheet_num = 2000;
        }

        $path = $this->check_encod("GBK",$path);
        //创建PHPExcel实例
        $objPHPExcel = new \PHPExcel();
        //总数据行数和数据列数
        $arr_num     = count($result);
        $field_count = count($column);
        //工作薄数目，工作薄数量太多，可以调节煤业导出的数据条数减少工作薄数目
        $get_num     = ceil($arr_num / $sheet_num);
        if($get_num > 20){
            return false;
        }
        
        //生成列信息
        $ary = array("", "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");
        for($i = 1;$i <= $field_count; $i++){
            $ary_info[$i]     = $ary[$i/27].$ary[$i%27];
            $fieldwidth[$i-1] = 20; //设置列宽默认是20
        }
        

        //循环每个工作薄
        for($sheet = 0;$sheet < $get_num; $sheet++){
            //创建工作薄，设置起始工作薄
            $objPHPExcel->createSheet($sheet);
            $Sheet = $objPHPExcel->setActiveSheetIndex($sheet);
            //设置工作薄样式
            $i = 1;
            foreach ($column as $key => $value){
                //设置第一行的值
                $Sheet->setCellValue($ary_info[$i] .'1', $value);
                //设置第一行加粗
                $objPHPExcel->getActiveSheet()->getStyle($ary_info[$i] .'1')->getFont()->setBold(true);
                //设置行宽
                $objPHPExcel->getActiveSheet()->getColumnDimension($ary_info[$i] )->setWidth($fieldwidth[$i-1]);
                $i = $i + 1;
            }
            
            //每个工作薄开始的行数
            $hang_num  = 2;
            //本工作薄结束行数开始为0
            $end_num   = 0;
            //start_num本工作薄开始行数
            $start_num = $sheet*$sheet_num;
            //计算出每次导出的开始行数和结束行数（多工作薄导出）
            if($arr_num > ($start_num + $sheet_num)){
                $end_num = $start_num + $sheet_num;
            }else{
                $end_num = $arr_num;
            }

            //循环行数
            for($i = $start_num; $i < $end_num; $i++){
                //循环列数
                for($j = 1;$j <= $field_count; $j++){
                    //写入数据
                    $Sheet->setCellValue($ary_info[$j].($hang_num)," ".$result[$i][$j]);
                }
                $hang_num++;
            }
            //设置sheet的名称
            $objPHPExcel->getActiveSheet($sheet)->setTitle('sheet'.$sheet,$sheet);
            //设置sheet的起始位置
            $objPHPExcel->setActiveSheetIndex($sheet);
        }
        
        //通过PHPExcel_IOFactory的写函数将上面数据写出来
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //设置时区
        date_default_timezone_set("Asia/Shanghai");
        //保存并返回保存路径
        $objWriter->save($path);
        return $this->check_encod("utf-8",$path);
    }

    public function check_encod($encod,$string){
        //判断字符编码
        $encode = mb_detect_encoding($string, array("ASCII","UTF-8","GB2312","GBK","BIG5"));
        if($encode != $encod){
            $string = iconv($encode, $encod, $string);
        }
        return $string;
    }
}
?>