PHP中使用PhpSpreadsheet 读取mysql ,生成excel下载

安装phpoffice/phpspreadsheet

composer require phpoffice/phpspreadsheet

github地址: https://github.com/PHPOffice/PhpSpreadsheet

PhpSpreadsheet's documentation相关文档:https://phpspreadsheet.readthedocs.io/en/develop/

用法示例：https://blog.csdn.net/gc258_2767_qq/article/details/81003656

https://www.wj0511.com/site/detail.html?id=347

使用范例(范例使用ThinkPHP5.0框架)

  /**
     * 表格数据导出
     */
    public function export_excel()
    {
        //获取任务对应的表名,以及采集数据id
        $param = $this->request->param();
        $id = $param['id'];
        $info = Db::name('crawler_task')->where('id', $id)->find();
        $table_name = $info['table_name'];
        $crawler_id = json_decode($info['crawler_id'], true);
        //获取数据
        $data = Db::table($info['table_name'])->whereIn('id', $crawler_id)->select()->toArray();

        //获取表信息
        $sql = "SHOW FULL COLUMNS FROM $table_name";
        $table_structure = Db::table($table_name)->query($sql);
        $table_field = array_column($table_structure, 'Field');
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $field_len = count($table_field);
        $end_alpha = chr(63 + $field_len);


//        设置表标题名称
        $worksheet->setTitle($info['task_name']);

        //循环填充第一行数据
        foreach ($table_structure as $key => $value) {
            if ($value['Field'] != 'id') { //去除id表头
                $worksheet->setCellValueByColumnAndRow($key, 1, $value['Field']);
            }
        }
        //设置第一行样式
        $styleArray = [
            'font' => [
                'bold' => true
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $worksheet->getStyle('A1:' . $end_alpha . '1')->applyFromArray($styleArray)->getFont()->setSize(14);

        //填充表数据
        $len = count($data);
        for ($i = 0; $i < $len; $i++) {
            $j = $i + 2;
            foreach ($table_field as $key => $value) {
                if ($key != 0) { //去除id数据
                    $worksheet->setCellValueByColumnAndRow($key, $j, $data[$i][$value]);
                }
            }
        }

        //设置数据表格样式
        $styleArrayBody = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => '666666'],
                ],
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];

        $total_rows = $len + 1;
        //添加所有边框/居中
        $worksheet->getStyle('A1:' . $end_alpha . $total_rows)->applyFromArray($styleArrayBody);

        $file_name = $info['task_name'] . ".xls";
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename=' . $file_name );
        header('Cache-Control: max-age=0');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        $writer->save('php://output');


    }

mysql-table里面的数据与导出变化比较大时

public function exportExcel()
    {
        //获取任务对应的表名,以及采集数据id
        $id = input('post.id');
        $info = Db::name('task')->where('id', $id)->find();
        $table_info = Db::name('templates')->where('id', $info['temp_id'])->find();
        $table_name = $table_info['table_name'];

        //获取数据
        $data = Db::table($table_name )->where('col_task_id', $id)->where('is_delete', 0)->select();

        //获取表信息
        $sql = "SHOW FULL COLUMNS FROM $table_name";
        $table_structure = Db::table($table_name)->query($sql);
        $table_field = array_column($table_structure, 'Field');
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $field_len = count($table_field);
        $end_alpha = chr(63 + $field_len - 2);


//        设置表标题名称
        $worksheet->setTitle($info['name']);

        //循环填充第一行数据
        foreach ($table_structure as $key => $value) {
//            print_r(11111);
            if ($value['Field'] != 'id' && $value['Field'] != 'is_delete' && $value['Field'] != 'col_task_id' ) { //去除id表头， is_delete col_task_id
                $worksheet->setCellValueByColumnAndRow($key-1, 1, $value['Field']);
            }
        }
        //设置第一行样式
        $styleArray = [
            'font' => [
                'bold' => true
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $worksheet->getStyle('A1:' . $end_alpha . '1')->applyFromArray($styleArray)->getFont()->setSize(14);

        //填充表数据
        $len = count($data);
        for ($i = 0; $i < $len; $i++) {
            $j = $i + 2;
            foreach ($table_field as $key => $value) {
                if ($key != 0 && $key != 1 && $key != 7) { //去除id, is_delete,  col_task_id数据 
                    $worksheet->setCellValueByColumnAndRow($key - 1, $j, $data[$i][$value]);
                }
            }
        }

        //设置数据表格样式
        $styleArrayBody = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => '666666'],
                ],
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];

        $total_rows = $len + 1;
        //添加所有边框/居中
        $worksheet->getStyle('A1:' . $end_alpha . $total_rows)->applyFromArray($styleArrayBody);

        $file_name = $info['name'] . ".xls";
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename=' . urlencode($file_name) );
        header('Cache-Control: max-age=0');
        header('Access-Control-Expose-Headers: Content-Disposition');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        $writer->save('php://output');
    }

 

示例二

<?php

namespace app\index\controller;



use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use think\Db;

class Index
{
    //统计表存放地址
    private $shangzhibo_Excel = __DIR__ . "/2020.2.12数据.xlsx";
    //生成的统计表存放位置
    private $statistics_excel_save_path = __DIR__;
    //读取Excel数据
    public function readExcel($file)
    {
        $sheetname = ['地域分布'];
        /* 转码 */
        $file = iconv("utf-8", "gb2312", $file);
        $reader = new Xlsx();
        $reader->setLoadSheetsOnly($sheetname);
        $spreadsheet = $reader->load($file);
        $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        // 获取单元行列信息 例如第一行 A列 数据
        return $sheetData;
    }

    /**
     * @param $video_id string 视频id
     * @param $redis array redis 配置信息
     * @return array
     */
    public function getShareData($video_id, $redis)
    {
        $redis->hgetall('empno_key__video_' . $video_id . '__share_code_list');
        //获取非白名单进入工号生成的分享码 ['工号'=>'分享码','工号'=>'分享码']
        $job_numbers_key_share_code_list = $redis->hgetall('empno_key__video_' . $video_id . '__share_code_list');
        if (!empty($job_numbers_key_share_code_list)) {
            //非白名单进入的游客['分享码'=>'次数','分享码'=>'次数']
            $share_count_list = $redis->hgetall('share_key__video_' . $video_id . '__count_list');
            return array($job_numbers_key_share_code_list, $share_count_list);
        } else {
            //获取白名单进 ['工号'=>'分享码','工号'=>'分享码']
            $job_numbers_key_share_code_list = $redis->hgetall('empno_key__video_' . $video_id . '__white_list');
            //白名单进入的游客['分享码'=>'次数','分享码'=>'次数']
            $share_count_list = $redis->hgetall('share_key__video_' . $video_id . '__white_list');
            return array($job_numbers_key_share_code_list, $share_count_list);
        }
    }

    /**
     * 获取在线时长信息
     * @param $video_id string 视频id
     * @param $redis
     * @return mixed
     */
    public function getOnlineTime($video_id, $redis)
    {
        $redis->hgetall('empno_key__video_' . $video_id . '__share_code_list');
        //业务员获取观看时长
        $online_time_data = $redis->hgetall("empno_key__video_" . $video_id . "__online_duration_count");
        return $online_time_data;
    }

    /**
     * @param $redis_config array redis配置信息
     * @param $video_id string 视频id
     * @return array
     */
    private function getSingleRedisServiceData($redis_config, $video_id)
    {
        $redis = new \Redis();
        $redis->connect($redis_config["ip"], $redis_config["port"]);
        $redis->auth($redis_config["password"]);
        //获取在线观看时长数据 工号=>分钟
        $online_time_data = $this->getOnlineTime($video_id, $redis);
        //获取分享数据
        list($job_numbers_key_share_code_list, $share_count_list) = $this->getShareData($video_id, $redis);
        $redis->close();
        return array($online_time_data, $job_numbers_key_share_code_list, $share_count_list);
    }

    public function index()
    {
        $video_id = 36;
        //根据工号 video_id 获取工号 公司信息
        $users_info = Db::table("snake_record")->distinct(true)->field("empno, company")->where("room_id", $video_id)->where("empno", "<>", "unknow")->select();
        $users_info = array_combine(array_column($users_info, "empno"), $users_info);
        $redis_configs = array(
            array("ip" => "127.0.1", "port" => 6379, "password" => "123456"),
        );

        $district = array("合计", "安徽", "北京", "江苏", "常州", "无锡", "苏州", "福建", "厦门", "甘肃", "广东", "深圳", "广西", "贵州", "海南", "河北", "黑龙江", "吉林", "江西", "辽宁", "大连", "内蒙古", "宁夏", "山东", "青岛", "青海", "山西", "陕西", "上海", "上海自贸区", "四川", "天津", "新疆", "豫北", "豫东", "豫南", "豫西", "浙江", "宁波", "重庆", "湖北", "湖南", "云南");
        $province_area = array("江苏" => array("常州", "无锡", "苏州"), "福建" => array("厦门"), "广东" => array("深圳"), "辽宁" => array("大连"), "山东" => array("青岛"), "上海" => array("上海自贸区"), "浙江" => array("宁波"), "豫北" => array("豫东", "豫南", "豫西"));
        $cities = array("常州", "无锡", "苏州", "厦门", "深圳", "大连", "青岛", "上海自贸区", "宁波", "豫北", "豫东", "豫南", "豫西");
        //获取空白的地区数据，以及地区需要合并单元格的起始位置列表
        list($district_data, $merage_position) = $this->getEmptyDistrictData($district, $province_area);
        //填充的统计数据
        $district_data = $this->setTotalData($district_data, $district);
        $users_data = array();
        //遍历redis服务器，统计观看时长，分享数据
        foreach ($redis_configs as $redis_config) {
            list($online_time_data, $job_numbers_key_share_code_list, $share_count_list) = $this->getSingleRedisServiceData($redis_config, $video_id);
            统计各地区业务员观看人数，总有效分享次数，同时统计业务员观看及分享数据
            list($users_data, $district_data) = $this->handleData($users_info, $job_numbers_key_share_code_list, $share_count_list, $online_time_data, $users_data, $district_data, $district);
        }
        //计算各地区游客观看人数
        $district_data = $this->ComputeTheVisitorsCount($district_data, $province_area, $cities);
        //创建Excel表格
        $this->createExcel($district_data, $this->arraySort(array_values($users_data), "company"), $merage_position);
        return json_encode(array_slice($users_data, 0, 12));
    }

    /**
     * @param $district_data array 分公司统计信息
     * @param $district array 分公司信息
     * @return mixed
     */
    private function setTotalData($district_data, $district)
    {
        //读取统计数据
        $shangzhibo_statistical_data = $this->readExcel($this->shangzhibo_Excel);
        //计算各地中总观看人数
        $total = 0;
        foreach ($shangzhibo_statistical_data as $key => $value) {
            if (in_array($value["A"], $district)) {
                $num = (int)$value["B"];
                $district_data[$value["A"]]["viewers_total_count"] = $num;
                $total += $num;
            } elseif ($value["A"] == "河南") {
                $num = (int)$value["B"];
                $district_data["豫北"]["viewers_total_count"] = $num;
                $total += $num;
            }
        }
        $district_data["合计"]["viewers_total_count"] = $total;
        return $district_data;
    }

    /**
     * @param $district array 分公司列表
     * @param $province_area   array 包含有地市级分公司的省份
     * @return array
     */
    private function getEmptyDistrictData($district, $province_area)
    {
        //地区空白统计数据
        $district_data = array();
        //需要合并的单元格起始行号
        $merage_position = array();
        //组装地区数据
        foreach ($district as $key => $value) {
            $district_data[$value] = array("company" => $value, "staff_viewers_count" => 0, "visitors_count" => 0, "viewers_total_count" => 0, "share_count" => 0);
            if (in_array($value, array_keys($province_area))) {
                array_push($merage_position, array("start" => $key + 2, "end" => count($province_area[$value]) + $key + 2));
            }
        }
        return [$district_data, $merage_position];
    }

    /**
     * @param $users_info array 用户数据信息
     * @param $job_numbers_key_share_code_list array 工号与分享码对应列表
     * @param $share_count_list array 工号与分享次数对应列表
     * @param $online_time_data array 工号与在线时长对应列表
     * @param $users_data array 统计的职员信息
     * @param $district_data array 统计的地区信息
     * @param $district array 分公司列表
     * @return array array 地市级分公司列表
     */
    private function handleData($users_info, $job_numbers_key_share_code_list, $share_count_list, $online_time_data, $users_data, $district_data, $district)
    {
        //涉及到分享数据的员工工号列表
        $job_numbers_in_share = array_keys($job_numbers_key_share_code_list);
        $job_numbers_online = array_unique(array_keys($online_time_data));
        //统计各地区业务员观看人数，总有效分享次数，同时统计业务员观看及分享数据
        foreach ($users_info as $user) {
            $company = $user["company"];
            $job_number = $user["empno"];
            if (array_key_exists($job_number, $job_numbers_key_share_code_list)) {
                //分享数据有该工号
                if (in_array($job_number, $job_numbers_in_share)) {
                    $share_code = $job_numbers_key_share_code_list[$job_number];
                    $share_count = (int)$share_count_list[$share_code] - 1;
                } else {
                    $share_count = 0;
                }
                if (in_array($job_number, $job_numbers_online)) {
                    $online_time = (int)$online_time_data[$job_number];
                    if (!in_array($job_number, array_keys($users_data))) {
                        $users_data[$job_number] = array("job_number" => $job_number, "company" => $company, "online_time" => $online_time, "share_count" => $share_count);
                    } else {
                        $users_data[$job_number]["online_time"] += $online_time;
                    }
                }
                if (in_array($company, $district)) {
                    $district_data[$company]["staff_viewers_count"] = $district_data[$company]["staff_viewers_count"] + 1;
                    $district_data[$company]["share_count"] = $district_data[$company]["share_count"] + $share_count;
                    $district_data["合计"]["staff_viewers_count"] = $district_data["合计"]["staff_viewers_count"] + 1;
                    $district_data["合计"]["share_count"] = $district_data["合计"]["share_count"] + $share_count;
                }
            }
        }

        return [$users_data, $district_data];
    }

    /**
     * 计算各分公司游客观看人数
     * @param $district_data array 分公司统计信息
     * @param $province_area array 包含有地市级分公司的省份
     * @param $cities array 地市级分公司
     * @return mixed array 分公司统计信息
     */
    private function ComputeTheVisitorsCount($district_data, $province_area, $cities)
    {
        //计算各地区游客观看人数
        foreach ($district_data as $key => &$value) {
            if (in_array($key, array_keys($province_area))) {
                $staff_viewers_count = $value["staff_viewers_count"];
                foreach ($province_area[$key] as $city) {
                    $staff_viewers_count += $district_data[$city]["staff_viewers_count"];
                }
                $value["visitors_count"] = $value["viewers_total_count"] - $staff_viewers_count;
            } elseif (in_array($key, $cities)) {
                $value["visitors_count"] = 0;
            } else {
                $value["visitors_count"] = $value["viewers_total_count"] - $value["staff_viewers_count"];
            }
        }
        return $district_data;
    }


    /**
     * 二维数组根据某个字段排序
     * @param array $array 要排序的数组
     * @param string $keys 要排序的键字段
     * @param string $sort 排序类型  SORT_ASC     SORT_ASC
     * @return array 排序后的数组
     */
    public function arraySort($array, $keys, $sort = SORT_ASC)
    {
        $keysValue = [];
        foreach ($array as $k => $v) {
            $keysValue[$k] = $v[$keys];
        }
        array_multisort($keysValue, $sort, $array);
        return $array;
    }

    /**
     * 数字转大写字母
     * @param $index int 数字
     * @param int $start
     * @return string 字母
     */
    private function numberToLetter($index, $start = 65)
    {
        $str = '';
        if (floor($index / 26) > 0) {
            $str .= $this->numberToLetter(floor($index / 26) - 1);
        }
        return $str . chr($index % 26 + $start);
    }

    /**
     * @param $spreadSheet
     * @param $worksheet
     * @param $header array 头部列表
     * @param $data array 统计数据
     * @param array $merge_positons 合并单元格起始列
     */
    private function writeAndSetSheet($spreadSheet, $worksheet, $header, $data, $merge_positons = array())
    {
        //循环填充第一行数据
        foreach ($header as $key => $value) {
            $spreadSheet->getActiveSheet()->getColumnDimension($this->numberToLetter($key))->setWidth(30);
            $worksheet->setCellValueByColumnAndRow($key + 1, 1, $value);
        }
        //填充统计数据
        $row = 2;
        foreach ($data as $key => $value) {
            $worksheet->setCellValueByColumnAndRow(1, $row, $key);
            $column = 1;
            foreach ($value as $item) {
                $worksheet->setCellValueByColumnAndRow($column, $row, $item);
                $column += 1;
            }
            $row += 1;
        }
        //设置第一行样式
        $styleHead = [
            'font' => [
                'bold' => true,
                "name" => "宋体",
                "size" => 12
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                "vertical" => Alignment::VERTICAL_CENTER

            ],
        ];
        //设置数据样式
        $stylebody = [
            'font' => [
                'bold' => false,
                "name" => "宋体",
                "size" => 12
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                "vertical" => Alignment::VERTICAL_CENTER
            ],
        ];
        if (!empty($merge_positons)) {
            foreach ($merge_positons as $merge_positon) {
                $worksheet->mergeCells('C' . $merge_positon["start"] . ':C' . $merge_positon["end"]);
                $worksheet->mergeCells('D' . $merge_positon["start"] . ':D' . $merge_positon["end"]);
            }
        }
        $column_len = count($header);
        $row_len = (count($data) + 1);
        $end_alpha = $this->numberToLetter($column_len);
        $worksheet->getStyle('A1:' . $end_alpha . '1')->applyFromArray($styleHead);
        $worksheet->getStyle('A2:' . $end_alpha . $row_len)->applyFromArray($stylebody);
    }

    /**
     * @param $district_data array 分公司统计数据
     * @param $users_data 职员统计数据
     * @param $merge_positon array 合并单元格起始列
     */
    private function createExcel($district_data, $users_data, $merge_positon)
    {
        $spreadSheet = new Spreadsheet();
        //写入分公司统计数据
        $spreadSheet->setActiveSheetIndex(0);
        $worksheet = $spreadSheet->getActiveSheet();
        $worksheet->setTitle("地区观看人数统计");
        $header = ["分总司", "观看人数(工号)", "观看人数(游客)", "总人数", "总有效分享次数"];
        $this->writeAndSetSheet($spreadSheet, $worksheet, $header, $district_data, $merge_positon);
        //写入职员统计数据
        $spreadSheet->createSheet();
        $spreadSheet->setActiveSheetIndex(1);
        $worksheet = $spreadSheet->getActiveSheet();
        $worksheet->setTitle("业务员观看及分享统计");
        $header2 = ["工号", "公司名称", "观看时长/分钟", "有效分享次数"];
        $this->writeAndSetSheet($spreadSheet, $worksheet, $header2, $users_data);
        //保存文件
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadSheet);
        $writer->save($this->statistics_excel_save_path."/".date('YmdHis', time())."直播统计信息".".xlsx");
    }

}