文件下载

文件下载是实际项目中经常用的一个接口，不同于图片下载客户端自己保存就可以了，需要开放对应的接口。同时，一般会结合PHPExcel导出EXCEL表格。随着 PhpSpreadsheet的更新，三种方式一并在此总结下。

1.普通文件下载

        //文件下载, readfile实现  
        $fileinfo = pathinfo($filename['path']);
        header('Content-type: application/x-'.$fileinfo['extension']);  
        header('Content-Disposition: attachment; filename='.$fileinfo['basename']);  
        header('Content-Length: '.$filename['size']);
        readfile(ROOT_PATH ."public" . DS  . $filename['path']); 
        exit(); 

2.1文件下载+PHPExcel（头铁版）

缺点：表格设置需要代码层实现，较为复杂
优点：仅需要在代码层更改

    //河长信息导出
    public function getRiverOwnerDown(){

        $condition = [];
        RequestFunc::conditionAdd($condition, $this->get, ["pageNo", "pageSize", "river_type", "river_style", 'town_id', 'village_id','role_ids','searchWord','level_id']);

        $orderBy['groupBy'] =['ro.user_id'];
        RequestFunc::buildOrderBy($orderBy,$this->get);

        $expTitle = "河长信息";

        $expCellName = ['河长姓名','河长级别','河道名称','工作单位','所属村镇','办公电话','联系方式','任职详情'];

        //只查看河长数据
        $condition['check_brother'] = 1;
        //非市级河长办只能看到本镇的数据
        //$this->admin['town_id'] =0;
        if($this->admin['town_id'] !== 0){
            $condition['town_id'] = $this->admin['town_id'];
        }

        $fields = ["au.user_name", 'rl.level_name', "r.river_name", "au.department", "CONCAT(au.town,au.village) as address", 'au.department_tel', 'au.mobile','au.position'];
        $expTableData = $this->dataDao->river_riverOwner_town_village($condition, $fields)->select();

        foreach ($expTableData as $key => $value) {
            $expTableData[$key] = array_values($value);
        }

        ExcelFunc::phpExcelOutput($expTitle,$expCellName,$expTableData);
         
    }

2.2文件下载+PHPExcel（结合模板下载方式）

优点：表格设置可直接修改对应模板xls
缺点：需要单独上传空模板xls

    /*
     * 河长数据导出
     */
    public function userExport()
    {
        $excel = $_SERVER['DOCUMENT_ROOT'] . "/../application/common/json/user.xlsx";
        $link = [
            "B" => "user_name",//河长姓名
            "C" => "level_name",//河长级别
            "D" => "river_name",//河道名称
            "E" => "river_style",//河道类型
            "F" => "river_type",//河道等级
            "G" => "department",//工作单位
            "H" => "town",//所属镇村
            "I" => "mobile",//联系电话
            "J" => "department_tel",//办公电话
        ];
        $userService = new UserService();
        CommonFunc::arrEmptyReplace($this->get,['level_id'],'1,2,4,5,8,9,10,11,12,13,14,15,16,17,18,19,20');
        if(!empty($this->get['river_type']))
        {
            switch($this->get['river_type']){
                //市级河道
                case 1:
                    $this->get['level_id'] = array_intersect([4,14,5,15,8,16,10,18],explode(",",$this->get['level_id']));
                    break;
                //镇级河道
                case 2:
                    $this->get['level_id'] = array_intersect([9,17,11,19],explode(",",$this->get['level_id']));
                    break;
                //村级河道
                case 3:
                    $this->get['level_id'] = array_intersect([13,20],explode(",",$this->get['level_id']));
                    break;
                //市级河道XX段
                case 4:
                    $this->get['level_id'] = array_intersect([4,14,5,15,8,16,10,18],explode(",",$this->get['level_id']));
                    break;
                default:
                    break;
            }          
        }
        $list = $userService->userList($this->get)["list"];

        foreach ($list as $k => &$v) {
            //导出河道——river_type字段特别显示
            if ($v['river_style'] == 0) {
                $river_type_exchange = ["1" => "市级河道", "2" => "镇级河道", "3" => "村级河道"];
            }else if ($v['river_style'] ==1) {
                $river_type_exchange = ["1" => "市级湖泊", "2" => "镇级湖泊", "3" => "村级湖泊"];
            }else{
                $river_type_exchange = ["1" => "水库", "2" => "水库", "3" => "水库"];
            }
            CommonFunc::arrReplace($v, [
                "river_type" => $river_type_exchange,
                "river_style" => ["0" => "河道", "1" => "湖泊", "2" => "水库"],
            ]);
        }
        $title = "河长信息表";
        ExcelFunc::outPutByLoad($excel, $link, $list, $title);
    }

下面附上以上两种方法用到的PHPExcel公共函数

需要用composer安装——composer require phpoffice/phpexcel
疑问：这里没用引入也能直接实例化，说是什么工厂类之类的，反正就是可以吧，有点醉

<?php
namespace app\common\func;

/**
 * Class Common
 * @package App\Func
 */
class ExcelFunc
{

    public static function phpExcelOutput($expTitle, $expCellName, $expTableData, $style = [])
    {
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称 将字符串从utf-8编码转为gb2312编码
        $cellNum = count($expCellName);//获取文件的列数
        $dataNum = count($expTableData);//获取数据的条数
        $objPHPExcel = new \PHPExcel();//生成PHPExcel类实例
        //A-AZ列
        $cellName = ['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',
            'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ',
            'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'AO', 'AP', 'AQ'];
        // 设置excel文档的属性
        $objPHPExcel->getProperties()->setCreator("php")//设置文档属性作者
        ->setLastModifiedBy("php")//设置最后修改人
        ->setTitle("Microsoft Office Excel Document")//设置文档属性标题
        ->setSubject("php")//设置文档属性文档主题
        ->setDescription("php")//设置文档属性备注
        ->setKeywords("php")//设置文档属性关键字
        ->setCategory("php");//设置文档属性类别

        //设置表的名称
        $objPHPExcel->getActiveSheet()->setTitle($expTitle);

        if (!empty($style)) {
            if (!empty($style['width'])) {
                foreach ($style['width'] as $k => $v) {
                    $objPHPExcel->getActiveSheet()->getColumnDimension($k)->setWidth($v);
                }
            }
            if (!empty($style['height'])) {
                foreach ($style['height'] as $k => $v) {
                    $objPHPExcel->getActiveSheet()->getRowDimension($k)->setRowHeight($v);
                }
            }
            //固定表头
            if (!empty($style['freezePane'])) {
                $objPHPExcel->getActiveSheet()->freezePane($style['freezePane']);
            }
        }

        //自动换行、左右垂直居中
        $objPHPExcel->getDefaultStyle()->getAlignment()->setWrapText(true);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        for ($i = 0; $i < $cellNum; $i++) {
            //遍历设置单元格的值 设置列名
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '1', $expCellName[$i]);
            if (!empty($expCellName[$i]['color'])) {
                $objPHPExcel->getActiveSheet()->getStyle($cellName[$i] . '1')->getFont()->getColor()->setARGB($expCellName[$i]['color']);
            }
        }
        //让总循环次数小于数据条数
        for ($i = 0; $i < $dataNum; $i++) {
            //让每列的数据数小于列数
            for ($j = 0; $j < $cellNum; $j++) {
                //设置单元格的值
                $objPHPExcel->getActiveSheet()->setCellValue($cellName[$j] . ($i + 2), ' '.$expTableData[$i][$j]);
            }
        }

        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
        header("Content-Disposition:attachment;filename=$expTitle.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    }

    /*
     * 加载表格，填充数据
     */
    public static function outPutByLoad($excel, $link, $list, $title)
    {

        $Reader = new \PHPExcel_Reader_Excel2007();
        $PHPExcel = $Reader->load($excel);

        foreach($list as $k => $v){
            $PHPExcel->getActiveSheet()->setCellValue("A".($k+2), $k+1);
            foreach($link as $k1 => $v1){
                $key = $k1.($k+2);
                $PHPExcel->getActiveSheet()->setCellValue($key, $v[$v1]);
            }
        }
        header('pragma:public');
        $xlsTitle = 100;
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename={$title}.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    }

}

3.文件导入+PhpSpreadsheet

优点：官方推荐的新方法，导出速度更快，量更大
缺点：英文文档读的一脸懵逼，相关demo不多，引入就难了半天
官方手册：phpspreadsheet手册
大神指南：使用PhpSpreadsheet读取和写入Excel

<?php
/**
 * @authors ZL 
 * @email 987968469@qq.com
 * @date    2018-06-04 13:59:05  
 */
namespace app\admin\controller;

use app\common\controller\Admin;
use app\common\myFunc\ExcelFunc;
use think\Db;
//引入后就能使用了PHPSpreadsheet了——我也不知道为什么
require_once "./vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\IOFactory;

class Department extends Admin {
    /**
     * 数据导入
     */
    private function importData($inputFileName,$name){
        if (!file_exists($inputFileName)) {
            die('no file!');
        }
        vendor('PhpOffice.PhpSpreadsheet.IOFactory');
        /**  Identify the type of $inputFileName  **/
        $inputFileType = IOFactory::identify($inputFileName);
        /**  Create a new Reader of the type that has been identified  **/
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
        /**  Load $inputFileName to a Spreadsheet Object  **/
        $spreadsheet = $reader->load($inputFileName);
        $list = $spreadsheet->getActiveSheet()->toArray();
        //获取json
        $json = file_get_contents(dirname(__FILE__) .'../../../common/Json/'.$name.'.json');
        $json = json_decode($json, true);
        //将表中的数据重组成数据库录入的数组
        $data = $this->buildArray($list,$json);//halt($data);
        
        $report = db('company_month_report')->where('date_time',$data[0]['date_time'])->column('id');//halt($report);
        foreach ($data as $key => $value) {
            if(in_array($value['id'], $report)){
                $result = db('company_month_report')
                    ->where('date_time',$value['date_time'])
                    ->where('id',$value['id'])
                    ->update($value);
            }else{
                $result = db('company_month_report')->insert($value);
            }
        }
        // halt(session('user_auth.is_government'));
        //该部门数据导入成功后，向cxly_department_work表中写入数据，方便定时任务提醒
        $result  =db('department_work')->insert([
            'date_time'=>$data[0]['date_time'],
            'department_id'=>session('user_auth.is_government')
        ]);
        if($result){
            return $this->success("导入成功！", url('enterprise/index'));
        }
        
    }
}