PhpSpreadsheet文件下载与上传
在实际项目中，使用excel上传和下载数据，是一个经常性的需求，我使用的一个软件中，一年要一万元，但是合同列表需要一份一份补录，浪费了我10来个员工将近一个月的时间，可谓费时费力。偶尔看到了“欧阳克”老师的视频课，经过学习，结合laravel框架知识，将PhpSpreadsheet实现在laravel中。
一、前端代码
1、数据列表及下载数据
@extends('base')@section('title','日志列表')@section('content')<section class="Hui-article-box"><nav class="breadcrumb"><i class="Hui-iconfont"></i> 首页<span class="c-gray en">></span>日志管理<span class="c-gray en">></span>日志列表<a class="btn btn-success radius r" style="line-height:1.6em;margin-top:3px" href="javascript:location.replace(location.href);" title="刷新" ><i class="Hui-iconfont"></i></a></nav><div class="Hui-log"><article class="cl pd-20"><div class="text-c"><span class="select-box inline"><select name="" class="select"><option value="0">日志标题</option></select></span><input type="text" name="wd" id="wd" onblur="search()" placeholder=" 标题关键字" style="width:250px" class="input-text" value=""><button name="" id="" onclick="search()" class="btn btn-success" type="button"><i class="Hui-iconfont"></i> 搜日志</button></div><div class="cl pd-5 bg-1 bk-gray mt-20"><span class="l"><a href="javascript:;" onclick="datadel()" class="btn btn-danger radius"><i class="Hui-iconfont"></i> 批量删除</a><a class="btn btn-primary radius" data-title="添加日志" onclick="log_add()" href="javascript:;"><i class="Hui-iconfont"></i> 添加日志</a><a href="/test/download" class="btn btn-primary radius" ><i class="Hui-iconfont"></i> 导出数据</a><a href="javascript:;" onclick="data_upload()" class="btn btn-primary radius" ><i class="Hui-iconfont"></i> 导入数据</a></span><span class="r">共有数据：<strong>{{$total}}</strong> 条</span></div><div class="mt-20"><table class="table table-border table-bordered table-bg table-hover table-sort"><thead><tr class="text-c"><th width="40"><input type="checkbox" name="" value=""></th><th width="40">ID</th><th width="40">UID</th><th width="40">Client</th><th width="40">添加时间</th><th width="40">IP</th><th width="80">操作</th></tr></thead><tbody>@foreach($logs as $log)<tr class="text-c"><td><input type="checkbox" value="" name=""></td><td>{{$log['ip']}}</td><td>{{$log['uid']}}</td><td>{{$log['client']}}</td><td>{{$log['add_time']}}</td><td>{{$log['ip']}}</td><td><a style="text-decoration:none" onClick="log_stop(this,{{$log['ip']}})" href="javascript:;" title="下架"><i class="Hui-iconfont"></i></a><a style="text-decoration:none" class="ml-5" onclick="log_add({{$log['ip']}})" href="javascript:;" title="编辑"><i class="Hui-iconfont"></i></a><a style="text-decoration:none" class="ml-5" onclick="log_del(this,{{$log['ip']}})" href="javascript:;" title="删除"><i class="Hui-iconfont"></i></a></td></tr>@endforeach</tbody></table></div><div >{{$links}}</div></article></div></section><script type="text/javascript">function data_upload() {layer.open({type:2,shadeClose:true,shadow:0.2,area:['480px','360px'],title:'数据上传',content:'/test/upload'})}</script>@endsection
2、数据上传及下载模板
@extends('base')@section('title','导入页面')<link rel="stylesheet" href="/static/layui/css/layui.css">@section('content')<section class="Hui-article-box"><nav class="breadcrumb"><i class="Hui-iconfont"></i> 首页<span class="c-gray en">></span>图片管理<span class="c-gray en">></span>图片列表<a class="btn btn-success radius r" style="line-height:1.6em;margin-top:3px" href="javascript:location.replace(location.href);" title="刷新" ><i class="Hui-iconfont"></i></a></nav><div class="Hui-log">@csrf<div class="cl pd-5 bg-1 bk-gray mt-20" style="margin: 80px 100px"><span class="l"><button id="up" class="btn btn-primary radius" ><i class="Hui-iconfont"></i> 选择文件</button><button onclick="model_download()" class="btn btn-primary radius" ><i class="Hui-iconfont"></i> 下载模板</button></span></div></div></section><script type="text/javascript" src="/static/layui/layui.all.js"></script><script type="text/javascript" src="/static/layui/layui.js"></script><script type="text/javascript">layui.use('upload', function() {var $ = layui.jquery;var upload = layui.upload;upload.render({elem: '#up', url: '/test/uploadsave', data: {'_token': function () {return $('input[name="_token"]').val();}}, accept: 'file' //普通文件, done: function (res) {if (res.code == 0) {for (var i = 0; i < res.data.length; i++) {$("#log").append('<div>' + res.data[i] + '</div>');}}}});})//model_downloadfunction model_download() {window.location.href = '/test/model_download'}</script>@endsection
二、服务器端代码
1、引入文件
use Illuminate\Http\Request;use App\Http\Controllers\Controller;use Illuminate\Support\Facades\DB;use PhpOffice\PhpSpreadsheet\Spreadsheet;use PhpOffice\PhpSpreadsheet\Writer\Xlsx;use PhpOffice\PhpSpreadsheet\IOFactory;use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
2、下载数据
//下载数据public function download(){//设置表头$spreadsheet = new Spreadsheet();$sheet = $spreadsheet -> getActiveSheet();$cell1= $sheet->getCell('A1')->setValue('ID');$cell2= $sheet->getCell('B1')->setValue('用户ID');$cell3= $sheet->getCell('C1')->setValue('登录设备');$cell4= $sheet->getCell('D1')->setValue('登录时间');$cell5= $sheet->getCell('E1')->setValue('登录IP');//查询数据$logs = DB::table('login_log')->lists();//添加数据$sheet->fromArray($logs,null,'A2');//下载数据// MIME 协议，文件的类型，不设置，会默认htmlheader('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');// MIME 协议的扩展header('Content-Disposition:attachment;filename=登录日志.xlsx');// 缓存控制header('Cache-Control:max-age=0');$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');// php://output 它是一个只写数据流, 允许你以 print 和 echo一样的方式写入到输出缓冲区。$writer->save('php://output');}
3、上传页面
//upload页面public function upload(){return view('admins.test.upload');}
4、上传处理
public function uploadsave(){$file = $_FILES['file']['tmp_name'];# 创建读操作$reader = IOFactory::createReader('Xlsx');# 打开文件、载入excel表格$spreadsheet = $reader->load($file);# 获取活动工作薄$sheet = $spreadsheet->getActiveSheet();# 获取总列数$highestColumn = $sheet->getHighestColumn();# 获取总行数$highestRow = $sheet->getHighestRow();# 列数 改为数字显示$highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);$log = [];for($a=2;$a<$highestRow;$a++){$id = $sheet->getCellByColumnAndRow(1,$a)->getValue();$uid = $sheet->getCellByColumnAndRow(2,$a)->getValue();$client = $sheet->getCellByColumnAndRow(3,$a)->getValue();$add_time = $sheet->getCellByColumnAndRow(4,$a)->getValue();$ip = $sheet->getCellByColumnAndRow(5,$a)->getValue();**$data = ['id' => $id,'uid' => $uid,'client' => $client,'add_time' => $add_time,'ip' => $ip,];**$ins = DB::table('login_log')->insert($data);if($ins){$log[] = '第'.$a.'条，插入成功';}else{$log[] = '第'.$a.'条，插入失败';}}return json_encode(['code'=>0,'msg'=>'成功','data'=>$log]);}
4、下载模板
public function model_download(){$spreadsheet = new Spreadsheet();$sheet = $spreadsheet -> getActiveSheet();$cell1= $sheet->getCell('A1')->setValue('ID');$cell2= $sheet->getCell('B1')->setValue('用户ID');$cell3= $sheet->getCell('C1')->setValue('登录设备');$cell4= $sheet->getCell('D1')->setValue('登录时间');$cell5= $sheet->getCell('E1')->setValue('登录IP');//download// MIME 协议，文件的类型，不设置，会默认htmlheader('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');// MIME 协议的扩展header('Content-Disposition:attachment;filename=模板文件.xlsx');// 缓存控制header('Cache-Control:max-age=0');$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');// php://output 它是一个只写数据流, 允许你以 print 和 echo一样的方式写入到输出缓冲区。$writer->save('php://output');}
三、问题
1、单元格格式具体有哪些？视频中只提到了设置的方法，没有列举更多的格式，
尤其是如果单元格需要是时间格式时，总不能要求把我们习惯的“Y-m-d H:i:s”写入时间表？
