插件合集
安装
composer require phpoffice/phpspreadsheet
TP6基类
<?php
/**
* Created by PhpStorm.
* User: Zhangyongfeng
* Date: 2020/12/1
* Time: 11:31
*
* ━━━━━━━━━神兽出没━━━━━━━━━
*
* ┏┓ ┏┓+ +
* ┏┛┻━━━┛┻┓ + +
* ┃ ┃
* ┃ ━ ┃ ++ + + +
* ████━████ ┃+
* ┃ ┃ +
* ┃ ┻ ┃
* ┃ ┃ + +
* ┗━┓ ┏━┛
* ┃ ┃
* ┃ ┃ + + + +
* ┃ ┃ Code is far away from bug with the animal protecting
* ┃ ┃ + 神兽保佑,代码无bug
* ┃ ┃
* ┃ ┃ +
* ┃ ┗━━━┓ + +
* ┃ ┣┓
* ┃ ┏┛
* ┗┓┓┏━┳┓┏┛ + + + +
* ┃┫┫ ┃┫┫
* ┗┻┛ ┗┻┛+ + + +
*
* ━━━━━━━━━感觉萌萌哒━━━━━━━━━
*/
namespace zyf;
use think\facade\Filesystem;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
class Excel
{
static public function returnData($code = '', $msg = '', $data = []){
return \app\common\controller\Base::returnData($code, $msg, $data);
}
/**
* [exportExcel 导出表格]
* @param string $fileName excel表的表名
* @param array $arr 要导出excel表的数据，接受一个二维数组
* @param array $headAr excel表的表头，接受一个一维数组
* @param array $keyAr $arr中对应表头的键的数组，接受一个一维数组
* @return file 到uploads目录下
* @author zyf <1322816443@qq.com>
*/
public function exportExcel($fileName = '表格', $arr = [], $headAr = [], $keyAr = [])
{
// 计算所需表头数量
$count = count($headAr)-1;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// 设置列
$rowAr = self::setRowAr($count);
foreach ($rowAr as $k => $v) {
if($k > $count) break;
$sheet->setCellValue($v.'1', $headAr[$k]);
}
// 写入值
foreach ($arr as $k => $v) {
foreach ($rowAr as $ke => $ve){
if($ke > $count) break;
$sheet->setCellValue($ve.($k+2), $v[$keyAr[$ke]]); // 给单元格设置值
$spreadsheet->getActiveSheet()->getColumnDimension($ve)->setWidth(10); // 固定列宽,看着更整齐
}
}
// 冻结首行
$spreadsheet->getActiveSheet()->freezePaneByColumnAndRow(0,2);
// 在输出Excel前，缓冲区中处理BOM头
ob_end_clean();
ob_start();
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$fileName.date('_Ymd_Hi',time()).'.xlsx"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
// 删除清空：
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
exit;
}
/**
* [importExcel 导入数据]
* @return array
* @author zyf <1322816443@qq.com>
* */
public function importExcel()
{
set_time_limit(0);
// 文件上传导入
$res = self::uploadFileImport();
if($res['code'] == 999){ return $res; }
// 进行读取
$spreadsheet = IOFactory::load($res['data']);
$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
return $sheetData;
}
/**
* [uploadFileImport 导入文件（数据不入库）]
* @param file file 文件
* @return array
* @author zyf <1322816443@qq.com>
* */
public function uploadFileImport()
{
$param = request()->param();
$file = request()->file('file');
if ($file == null) return $this::returnData(999, '请上传文件');
// 上传路径
$disk = !empty($param['disk']) ? $param['disk'] : 'excel';
// 文件名称
$saveName = Filesystem::disk($disk)->putFile('', $file);
$savePath = str_replace(date('Ymd', time()) .'\\', '', '/uploads/' . $disk . "/" . date('Ymd', time()) . "/" . $saveName);
return $this::returnData(200, '上传成功', $savePath);
}
/**
* [setRowAr 设置列]
* @param int $count 列总数
* @return array
* @author zyf <1322816443@qq.com>
* */
public function setRowAr($count = 26)
{
$indData = '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';
$indData = explode(',',$indData);
$curCount = 26;
for ($i = 0; $i <26 ; $i++) {
for ($j = 0; $j < 26 ; $j++) {
if($curCount >= $count) return $indData;
$indData[] = $indData[$i].$indData[$j];
$curCount++;
}
}
return $indData;
}
}
TP5.1基类
<?php
/**
* Created by PhpStorm.
* User: Zhangyongfeng
* Date: 2020/12/1
* Time: 11:31
*
* ━━━━━━━━━神兽出没━━━━━━━━━
*
* ┏┓ ┏┓+ +
* ┏┛┻━━━┛┻┓ + +
* ┃ ┃
* ┃ ━ ┃ ++ + + +
* ████━████ ┃+
* ┃ ┃ +
* ┃ ┻ ┃
* ┃ ┃ + +
* ┗━┓ ┏━┛
* ┃ ┃
* ┃ ┃ + + + +
* ┃ ┃ Code is far away from bug with the animal protecting
* ┃ ┃ + 神兽保佑,代码无bug
* ┃ ┃
* ┃ ┃ +
* ┃ ┗━━━┓ + +
* ┃ ┣┓
* ┃ ┏┛
* ┗┓┓┏━┳┓┏┛ + + + +
* ┃┫┫ ┃┫┫
* ┗┻┛ ┗┻┛+ + + +
*
* ━━━━━━━━━感觉萌萌哒━━━━━━━━━
*/
namespace zyf;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
class Excel
{
/**
* [exportExcel 导出表格]
* @param string $fileName excel表的表名
* @param array $arr 要导出excel表的数据，接受一个二维数组
* @param array $headAr excel表的表头，接受一个一维数组
* @param array $keyAr $arr中对应表头的键的数组，接受一个一维数组
* @return mixed 到uploads目录下
* @author zyf <1322816443@qq.com>
*/
public function exportExcel($fileName = '表格', $arr = [], $headAr = [], $keyAr = [])
{
// 计算所需表头数量
$count = count($headAr)-1;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// 设置列
$rowAr = self::setRowAr($count);
foreach ($rowAr as $k => $v) {
if($k > $count) break;
$sheet->setCellValue($v.'1', $headAr[$k]);
}
// 写入值
foreach ($arr as $k => $v) {
foreach ($rowAr as $ke => $ve){
if($ke > $count) break;
$sheet->setCellValue($ve.($k+2), $v[$keyAr[$ke]]); // 给单元格设置值
$spreadsheet->getActiveSheet()->getColumnDimension($ve)->setWidth(10); // 固定列宽,看着更整齐
}
}
// 冻结首行
// $spreadsheet->getActiveSheet()->freezePaneByColumnAndRow(0,2);
// 在输出Excel前，缓冲区中处理BOM头
ob_end_clean();
ob_start();
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$fileName.date('_Ymd_Hi',time()).'.xlsx"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
// 删除清空：
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
exit;
}
/**
* [setRowAr 设置列]
* @param int $count 列总数
* @return array
* @author zyf <1322816443@qq.com>
* */
public function setRowAr($count = 26)
{
$indData = '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';
$indData = explode(',',$indData);
$curCount = 26;
for ($i = 0; $i <26 ; $i++) {
for ($j = 0; $j < 26 ; $j++) {
if($curCount >= $count) return $indData;
$indData[] = $indData[$i].$indData[$j];
$curCount++;
}
}
return $indData;
}
}
导出后台
// 导出excel-实现
public function export()
{
// 获取数据
$systemMod = new \app\common\model\System();
$data = $systemMod->getAll('');
// 设置表头
$headAr = [
'姓名',
'邮箱',
'电话',
];
$keyAr = [
'name',
'email',
'tel',
];
$phpOffice = new Excel();
$phpOffice->exportExcel('数据', $data, $headAr, $keyAr);
}
导入后台
// 导入excel-实现
public function import()
{
if(IS_POST){
$phpOffice = new Excel();
$data = $phpOffice->importExcel();
halt($data);
}
return $this->render();
}
导入前端
导入前台用的layui
<div class="layui-form-item">
<label class="layui-form-label">上传Excel：</label>
<div class="layui-input-block">
<div class="layui-upload">
<button type="button" class="layui-btn upload_excel">Excel</button>
</div>
</div>
</div>
导入JS
// 导入上传
upload.render({
elem: '.upload_excel'
, url: 'import'
, multiple: false
, accept: 'file'
, before: function(obj){ layer.load(1); }
, done: function (res) {
console.log(res)
}, error: function(index, upload){
layer.closeAll('loading');
}
})