php写入Excel6,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. 整体代码块

/**

* 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。