php导出excel

三步操作完成导出

第一步控制器中添加函数

    public function excel()
    {
        $post = $this->input->post();
        $rs = $this->log_model->getList('excel', $post);     
        lwReturn($rs);
    }

第二步 在log_model 创建getlist()方法

public function getList($type, $post, $page = null)
    {
        $this->load->library('hza_pagination');
        $post = lwCheckValue($post, ['param']);
        //  $post = lwCheckValue($post, ['param', 'startDate', 'endDate']);
        if ($post === false) {
            $this->rs['msg'] = '参数缺失';
            return $this->rs;
        }
        $param = $post['param'];
       // $startDate = $post['startDate'];
        //$startTime = strtotime($startDate.' 00:00:00');
      //  $endTime = $post['endDate'] ? strtotime($post['endDate'].' 23:59:59') : strtotime($startDate.' 23:59:59');
       // $where = "record_time BETWEEN {$startTime} AND {$endTime}";
        $where = " 1=1 ";
        $sql = "SELECT
                    a.*
                FROM
                    " . TB_SYS_LOG . " a
                WHERE {$where}";
        $hasWhere = true;
        $group = null;
        $order = "a.id desc";
        $paramFilter = ['a.controller_id', 'a.method_id'];
        switch ($type) {
            case 'list':
                $data = $this->hza_pagination->lists($sql, $param, $page, $hasWhere, $group, $order, $paramFilter, $pageSize = 10);
                $this->rs['success'] = true;
                $this->rs['data'] = $data;
                break;
            case 'excel':
                $this->load->helper('excel');
                // 设置变体
                $title = ['操作人', 'IP', '控制器', '方法', 'GET参数', 'POST参数', '记录日期'];
                $width = [];
                for ($i = 0; $i < sizeof($title); $i++) {
                    $width[$i] = 30;
                }
                // 获取数据 
                $sql = $this->hza_pagination->get_sql_by_param($sql, $param, $hasWhere, $group, $order, $paramFilter);//hza_pagination 因为用的CI，创建在application/libraries中
                $list = [];
                $query = $this->db->query($sql);
                while ($row = $query->result_id->fetch_assoc()) {
                    $info = [];
                    array_push($info, $row['admin_name']);
                    array_push($info, $row['ip']);
                    array_push($info, $row['controller_describe']);
                    array_push($info, $row['method_describe']);
                    array_push($info, $row['get_params']);
                    array_push($info, $row['post_params']);
                 //   array_push($info, date('Y-m-d H:i:s', $row['record_time']));
                    array_push($list, $info);
                }
                //判断路径 并创建
         //       $date = date('Y-d', time());
                $uniStr = $this->hza_string->getUniName();
           //   $path = "outputExcel/log/" . $date;
                $path = "outputExcel/log/";
                if (!is_dir($path)) {
                    mkdir($path, 0777, true);
                }
                $fn = $path . "/" . $uniStr . ".xls";
                getExcel($title, $width, $list, "$fn");
                $this->rs['success'] = true;
                $this->rs['msg'] = 'excel导出';
                $this->rs['excelPath'] = base_url($fn);;
                break;
            default:
                $this->rs['msg'] = '';
                break;
        }
        return $this->rs;
    }

最后一步 创建getList()方法中使用的两个类 Hza_pagination、Hza_string

    <?php

    class Hza_pagination
    {

        protected $CI;

        public function __construct()
        {
            $this->CI =& get_instance();
            $this->CI->load->database();
            $this->CI->load->library('hza_string');
        }

        public function lists($sql, $param, $page, $hasWhere = false, $group = null, $order = null, $paramFilter = [], $pageSize = PAGESIZE, $action = "getListByPage", $dataName = 'dataList', $type = 1)
        {
            $pagination = $this->normalPagination($sql, $param, $page, $hasWhere, $group, $order, $paramFilter, $pageSize, $action, $type);
            //MARK: 当前页数/总行数，总页数。
            $data[$dataName] = $pagination['pageData'];
            $data['pagination'] = $pagination['pageConfig'];
            $data['pageHtml'] = $this->CI->load->view('tpl/pagination', $data, true);
            return $data;
        }

        /*
         * $sql: 原始sql语句
         * $param: sql参数
         * $page: 当前页
         * $order: 排序字段
         * $action: 页码点击事件
         * $hasWhere: 原始sql是否存在where，默认不存在
         * $type :1 代表默认方法 通过此方法 调用 a标签 onclick 时间 跳转页面，2 通过 href 跳转 actions即使 url
         * */
        public function normalPagination($sql, $param, $page, $hasWhere = false, $group = null, $order = null, $paramFilter = [], $pageSize = PAGESIZE, $action = "getListByPage", $type = 1)
        {
            $sql = $this->get_sql_by_param($sql, $param, $hasWhere, $group, $order, $paramFilter);
            /*echo $sql;exit;*/
            /*我们要的数据*/
            $totalRows = $this->CI->db->query($sql)->num_rows();
            $totalPage = ceil($totalRows / $pageSize);
            $pageData = $this->get_rows_by_page($totalRows, $sql, $page, $pageSize);
            $pageConfig = [
                'page' => $page,
                'action' => $action,
                'totalRows' => $totalRows,
                'pageSize' => $pageSize,
                'type' => $type,
            ];
            return [
                'pageData' => $pageData,
                'pageConfig' => $pageConfig
            ];
        }

        /**
         * @param $sql 原始SQL
         * @param $param 参数
         * @param bool|false $hasWhere 排序
         * @param null $group 分组
         * @param null $order 排序
         * @param array $paramFilter 过滤数组
         * @return string 完整 SQL
         */
        public function get_sql_by_param($sql, $param, $hasWhere = false, $group = null, $order = null, $paramFilter = [])
        {
            if ($param) {
                $arrParams = json_decode($param, true);
                $sqlWhere = $this->array_to_sql($arrParams, $paramFilter);
                if ($sqlWhere) {
                    if ($hasWhere) {
                        $sql .= " and {$sqlWhere}";
                    } else {
                        $sql .= " where {$sqlWhere}";
                    }
                }
            }

            // 如果有 group 要先执行
            if ($group) {
                if (stristr($group, 'group by')) {
                    $sql .= " " . $group;
                } else {
                    $sql .= " group by " . $group;
                }
            }

            if ($order) {
                if (stristr($order, 'orer by')) {
                    $sql .= " " . $order;
                } else {
                    $sql .= " order by " . $order;
                }
            }
            return $sql;
        }

        /**
         * @param $arr 参数数组
         * @param array $paramFilter
         * @return string
         */
        function array_to_sql($arr, $paramFilter = [])
        {
            $sep = $sqlWhere = "";
            foreach ($arr as $key => $val) {
                if ($val != "") {
                    switch ($key) {
                        case 'equal':
                            foreach ($val as $k => $v) {
                                // 如果不存在 过滤数组，或者当前字段在过滤数组当中，可以拼接
                                if (!$paramFilter || !in_array($k, $paramFilter)) continue;
                                $sqlWhere .= "{$sep} {$k} = '{$v}' ";
                                $sep = ' AND';
                            }
                            break;
                        case 'like':
                            foreach ($val as $k => $v) {
                                if (!$paramFilter || !in_array($k, $paramFilter)) continue;
                                $sqlWhere .= "{$sep} {$k} like '%{$v}%' ";
                                $sep = ' AND';
                            }
                            break;
                        case 'sql':
                            if (!$paramFilter || !in_array('sql', $paramFilter)) continue;
                            if (stripos(substr($val, 0, 5), 'and') !== false) {
                                $sqlWhere .= " {$val} ";
                            } else {
                                $sqlWhere .= " {$sep} {$val} ";
                            }
                            break;
                        default:
                            if (!$paramFilter || !in_array($key, $paramFilter)) continue;
                            // 默认模式：like
                            if ($val == 'null') {
                                $sqlWhere .= "{$sep} {$key} is null ";
                            } else {
                                $sqlWhere .= "{$sep} {$key} like '%{$val}%' ";
                            }
                            $sep = ' AND';
                            break;
                    }
                }
            }
            return $sqlWhere;
        }

        /** 通过页数获取对应 pageSize 的数据
         * @param $totalRows ：总数据
         * @param $sql ：完整sql
         * @param $page ：当前页
         * @param $pageSize ：每页数据数
         * @return bool：返回数据
         */
        public function get_rows_by_page($totalRows, $sql, $page, $pageSize)
        {
            if ($totalRows) {
                $totalPage = ceil($totalRows / $pageSize);
                if ($page < 1 || $page == null || !is_numeric($page)) {
                    $page = 1;
                }
                $page = $page >= $totalPage ? $totalPage : $page;
                $offSet = ($page - 1) * $pageSize;
                $sql .= " limit {$offSet},{$pageSize}";
                $rows = $this->CI->db->query($sql)->result_array();
                return $rows;
            } else {
                return false;
            }
        }
    }

<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class Hza_string
{

    protected $CI;

    public function __construct()
    {
        $this->CI =& get_instance();
    }

    /**
     * 生成唯一字符号
     *
     * @return string
     */
    function getUniName()
    {
        return md5(uniqid(microtime(true), true));
    }

    /**
     * 得到文件的扩展名
     *
     * @param string $fileName
     * @return string
     */
    function getExt($fileName)
    {
        @$ext = strtolower(end(explode(".", $fileName)));
        return $ext;
    }

    function buildRandomString($type = 1, $length = 4)
    {
        // 生产一个含有所有字符的字符串
        if ($type == 1) {
            $chars = join("", range(0, 9));
        } elseif ($type == 2) {
            $chars = join("", array_merge(range("a", "z"), range(0, 9)));
        } elseif ($type == 3) {
            $chars = join("", array_merge(range("a", "z"), range("A", "Z"), range(0, 9)));
        } elseif ($type == 4) {
            // 验证码: 全部大写字母+数组-(0, O)
            $chars = join("", array_merge(range("A", "Z"), range(1, 9)));
        }
        if ($length > strlen($chars)) {
            exit ("字符串长度不够");
        }
        // 打乱字符串
        $chars = str_shuffle($chars);
        return substr($chars, 0, $length);
    }
}