PHP原生代码导出EXCEL功能

php

浏览数:270

2019-1-8

AD:资源代下载服务
class UserExcelController extends Controller
{
    /**
     * 导出报表功能
     * @param $dataArr 数据源 (二维结构);
     * @param $fieldArr 字段信息(一维数组,键值存放$dataArr中存在的key名,对应的value存放要导出表头的字段名称) ;
     * @param $tname excel表名(默认自动在最后加上导出的时间戳);
     * @author qingfeng.guo
     * 2016-06-21
     */
    function exportExcel($dataArr,$fieldArr,$tname="报表导出")
    {
        if(!is_array($fieldArr) || empty($fieldArr))
            die("导出异常,请重试!");

        if(!empty($dataArr) && count($dataArr) > 10000)
            die("单次导出的数据量过大(≥10000条),请分批导出!");

        $thead = ''; //表头
        $tbody = ''; //表数据
        $ttail = ''; //表尾
        $clos = count($fieldArr); //列数(字段量)
        //生成表头
        $thead .= '';
        foreach($fieldArr as $k => $v)
        {
            $thead .= ''.$v.'';
        }
        $thead .= '';
        //生成表尾
        $ttail = ' 报表内容:'.$tname.' , 导出时间:'.date("Y-m-d H:i:s").' ';

        //生成需要导出的数据源
        $datas = array();
        foreach($dataArr as $k => $v)
        {
            $v['created_at'] = date('Y-m-d H:i:s',$v['created_at']);
            $fieldFormat = $fieldArr; //这里需要复制原本的字段格式,以防标题和内容没对应上
            if(!empty($v) && is_array($v))
            {
                foreach($v as $k2 => $v2)
                {
        //根据key值决定当前字段是否需要导出
                    if(array_key_exists($k2, $fieldArr))
                    {
                        $fieldFormat[$k2] = $v2;
                    }
                }
            }
            $datas[] = $fieldFormat;
        }
        //生成表体
        foreach ($datas as $k => $v)
        {
            $tbody .= '';
            foreach($v as $k2 => $v2)
            {
                if(!is_numeric($v2))
                {
                    $tbody .= ''.$v2.'';
                }else{
                    $tbody .= ''.$v2.'';
                }
            }
            $tbody .= '';
        }

        $content = $thead.$tbody.$ttail;
        header("content-type:application/vnd.ms-excel; charset=utf8");
        header("Content-Disposition:attachment;filename={$tname}".time().".xls");
        echo "";
        if(mb_detect_encoding($content) != 'UTF-8')
        {
            echo iconv('UTF-8',"GB2312",$content);
        }else{
            echo $content;
        }
        echo "
"; } //查出所需要导出数据 public function userExcel(Request $request){ $list = User::join('users as u','u.id','=','users.pid') ->select('users.id','users.nickname','u.nickname as u_nickname','users.phone','u.phone as u_phone','users.email','users.created_at') ->paginate(10)->toArray(); $list =$list['data']; $fieldArr = array( 'id' => '用戶id', 'nickname' => '用户名', 'u_nickname' => '推荐人', 'phone' =>'手机号', 'u_phone'=>'推荐人手机号', 'email'=>'邮箱', 'created_at'=>'加入时间', ); $this->exportExcel($list, $fieldArr,'用戶表'); } }