PhpExcel PHP TP6导出excel，导出excel

在前后端开发中，我们会遇见这样的需求，导出excel，相信大家都遇见过此类场景。目前有2种纬度的解决方案：

1. 介绍 ：前端库（js-export-excel），2.后端（PhpExcel）库。

本章主要讲解如果在TP6里面进行excel的导出，并且导出的excel受保护某些指定的列不能被修改。

演示 ：

php导出的excel.gif

2. 安装

在tp6的项目的根目录执行
$: composer require phpoffice/phpexcel

3. 项目中使用

因为通过composer加载的第三方类,所以直接在头部引入一下就可以了.

use PHPExcel_IOFactory;
use PHPExcel;

4. 整体代码块

<?php
/**
 * Created by PhpStorm.
 * User: faith
 * motto 学习可以改变自己
 * Date: 2020-02-24
 * Time: 22:25
 */


namespace app\api\controller;
use app\BaseController;
use PHPExcel_IOFactory;
use PHPExcel;
use think\facade\Cache;
class PhpToExcel extends BaseController {

    /**
     * 该方法是配合dataExportExcel 用作数据的缓存
     * Created by PhpStorm.
     * User: faith
     * motto 学习可以改变自己
     * Date: 2020-02-25
     * Time: 00:31
     * @return \think\response\Json
     */
    public function setDataRedis() {
        $data_redis = isset($_REQUEST['data_redis']) ? $_REQUEST['data_redis'] : '';
        if(empty($data_redis)) {
            return setResponse(config("status.param_null"), "缺少参数data_redis", false);
        }
        $key = md5(uniqid(md5(microtime(true)),true));
        Cache::set($key, $data_redis, 60);
        return setResponse(config("status.success"),"导出excel缓存数据成功", $key);
    }


    /**
     * 该方法是生成escel表格的
     * Created by PhpStorm.
     * User: faith
     * motto 学习可以改变自己
     * Date: 2020-02-25
     * Time: 00:32
     * @param array $data
     * @param string $name
     * @return \think\response\Json
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function dataExportExcel($data = [],$name = 'excel') {
        $data_redis_key = isset($_REQUEST['data_redis_key']) ? $_REQUEST['data_redis_key'] : '';
        if(empty($data_redis_key)) {
            return setResponse(config("status.param_null"), "缺少参数data_redis_key", false);
        }
        $res = Cache::get($data_redis_key);
        if(empty($res)) {
            return setResponse(config("status.no_find"), "没有找到对应的数据", false);
        }
        $user_arr = json_decode($res);
        $excel = new \PHPExcel(); //引用phpexcel
        iconv('UTF-8', 'gb2312', $name); //针对中文名转码
        $excel->setActiveSheetIndex(0);
        $excel->getActiveSheet()->setTitle($name); //设置表名
        $excel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(18);
        $excel->getActiveSheet()->getColumnDimension('B')->setWidth(80);
        //        $excel->getActiveSheet()->freezePaneByColumnAndRow(1,1);//冻结首列
        $excel->getActiveSheet()->protectCells('A1:A2', 'order_id');

        //受保护的地址列
        //$excel->getActiveSheet()->protectCells('A1:A7', 'password');

        //解除指定列锁定
        // $excel->getActiveSheet()->getStyle('B1:B7')->getProtection()->setLocked(\PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
        $excel->getActiveSheet()->getStyle('H')->getProtection()->setLocked(\PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);

        //设置sheet锁定属性

        $excel->getActiveSheet()->getProtection()->setSheet(true);


        // 设置单元格的宽度
        $excel->getActiveSheet()->getColumnDimension('A')->setWidth(15);#设置单元格宽度
        $excel->getActiveSheet()->getColumnDimension('B')->setWidth(30);#设置单元格宽度
        $excel->getActiveSheet()->getColumnDimension('C')->setWidth(30);#设置单元格宽度
        $excel->getActiveSheet()->getColumnDimension('D')->setWidth(15);#设置单元格宽度
        $excel->getActiveSheet()->getColumnDimension('E')->setWidth(15);#设置单元格宽度
        $excel->getActiveSheet()->getColumnDimension('F')->setWidth(15);#设置单元格宽度
        $excel->getActiveSheet()->getColumnDimension('G')->setWidth(15);#设置单元格宽度
        $excel->getActiveSheet()->getColumnDimension('H')->setWidth(15);#设置单元格宽度


        //设置表头
        $excel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'id')
            ->setCellValue('B1', '合作方名称')
            ->setCellValue('C1', '统一社会信用代码')
            ->setCellValue('D1', '经营者')
            ->setCellValue('E1', '联系方式')
            ->setCellValue('F1', '任务领取时间')
            ->setCellValue('G1', '结算月份')
            ->setCellValue('H1', '结算金额');


        //写入数据
        $data = [[
            "order_id" => 1,
            "order_status" => 1,
            "name" => 1,
            "order_name" => 1,
            "create_time" => 1,
            "remark" => 1,
            "poper_size" => 1,
            "color_num" => 1,
        ]];

        foreach ($user_arr as $k => $v) {
            //从第二行开始写入数据（第一行为表头）
            //$excel->getActiveSheet()->setCellValue('A'.($k+2),$v['gid']);
//            $excel->getActiveSheet()->setCellValue('A' . ($k + 2), $v['id']."\t");
            $excel->getActiveSheet()->setCellValue('A' . ($k + 2), $v->id."\t");
            $excel->getActiveSheet()->setCellValue('B' . ($k + 2), $v->合作方名称);
            $excel->getActiveSheet()->setCellValue('C' . ($k + 2), $v->统一社会信用代码 ?: '');
            $excel->getActiveSheet()->setCellValue('D' . ($k + 2), $v->经营者);
            $excel->getActiveSheet()->setCellValue('E' . ($k + 2), $v->联系方式);
            $excel->getActiveSheet()->setCellValue('F' . ($k + 2), $v->任务领取时间);
            $excel->getActiveSheet()->setCellValue('G' . ($k + 2), $v->结算月份);
            $excel->getActiveSheet()->setCellValue('H' . ($k + 2), $v->结算金额);
        }

        //设置单元格边框
        $excel->getActiveSheet()->getStyle("A1:E" . (count($data) + 1))->getBorders()->getAllBorders()->setBorderStyle();

        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xls"');
        header('Cache-Control: max-age=0');

        $res_excel = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
        $res_excel->save('php://output');

    }
}

5. 调用接口就能直接下载excel。