tp6 实现excel 导入功能

在项目根目录安装

composer require phpoffice/phpspreadsheet

我们看一下郊果图，如下

点击导入excel表格数据

 出现弹窗选择文件，控制台打开输出文档内容

前端layui代码

<form id="uploadForm" class="form-horizontal"  enctype="multipart/form-data"><input type="file" name="file" class="file" multiple="multiple" required=""><button type="button" id="upload" class="btn btn-xs btn-purple"><span class="ace-icon fa fa-cloud-upload icon-on-right bigger-110"></span>上传文件</button>
</form>

layui.use(['form','layer'],function () {$("#input_excel_data").click(function () {// console.log('点击导入了');// layer.msg('点击导入了');layer.open({type: 1,area: ["500px", "360px"],title: "导入excel文件",content:$("#file_upload_div"),});});$("#upload_file").click(function () {//上传文档// var data = new  FormData;// data.append('file',my_file);// data.append('name',my_file.name);var formData = new FormData($('#uploadForm')[0]);$.ajax({'type':'post','url':'user/input_excel_data',contentType:false,processData:false,'data':formData,success:function (data) {console.log(data);layer.msg('导入成功');}});})
});

整个laui页面文件如下

{extend name="public/layout"}
{block name="content"}
<div class="layui-col-md12"><div class="layui-card"><div class="layui-card-header"><!-- <button class="layui-btn" onclick="xadmin.open('添加用户组','{:admin_url('admin_user/add')}')">添加管理员</button> --><form id="uploadForm" class="form-horizontal"  enctype="multipart/form-data"><div id="file_upload_div" style="display: none" class="text-center"><input type="file" name="file" class="file" multiple="multiple" required="">
<!--                <button type="button" id="upload_file" class="btn btn-xs btn-purple">-->
<!--                    　　<span class="ace-icon fa fa-cloud-upload icon-on-right bigger-110"></span>上传文件-->
<!--                    　　</button>--><button type="button"  class="layui-btn mt-2" id="upload_file">开始上传</button></div></form></div><div class="layui-card-header" style="height:auto"><form class="layui-form layui-col-space5" id="main-form" ><div class="layui-inline layui-show-xs-block">{if isset($params['nickname']) && !empty($params['nickname'])}<input type="text" name="nickname" placeholder="用户名" value="{$params['nickname']}" autocomplete="off"class="layui-input">{else /}<input type="text" name="nickname" placeholder="用户名" value="" autocomplete="off"class="layui-input">{/if}</div><div class="layui-inline layui-show-xs-block">{if isset($params['phone'])}<input type="text" name="phone" placeholder="手机号" value="{$params['phone']}" autocomplete="off"class="layui-input">{else/}<input type="text" name="phone" placeholder="手机号" value="" autocomplete="off"class="layui-input">{/if}</div><div class="layui-inline layui-show-xs-block"><select name="enable"><option value="">状态</option>{if isset($params['enable'])}<option value="1" {eq name="$params['enable']" value='1'}selected{/eq}>正常</option><option value="2" {eq name="$params['enable']" value='2'}selected{/eq}>禁用</option>{else/}<option value="1">正常</option><option value="2">禁用</option>{/if}</select></div><div class="layui-inline layui-show-xs-block"><button class="layui-btn" lay-submit="" lay-filter="search"><iclass="layui-icon">&#xe615;</i></button><button class="layui-btn" type="button" lay-submit=""onclick="location.href = location.pathname">重置</button><button class="layui-btn" type="button" lay-submit="" onclick="location.reload()">刷新</button><button class="layui-btn" type="button" lay-submit="" id="input_excel_data">导入excel表格数据</button></div></form></div><div class="layui-card-body "><table class="layui-table layui-form"><thead><tr><!-- <th><input type="checkbox" name=""  lay-skin="primary"></th> --><th>头像</th><th>手机号</th><th>昵称</th><th>性别</th><th>状态</th><th>创建时间</th><th>操作</th></tr></thead><tbody>{volist name="datalist" id="vo"}<tr><!-- <td><input type="checkbox" name=""  lay-skin="primary"> </td> --><td><img style="width: 50px;height: 50px" src="{$vo['avatar']}" alt=""></td><td>{$vo['phone']}</td><td>{$vo['nickname']}</td><td>{$vo['sex']}</td><td>{$vo['enable']}</td><td>{$vo['create_time']}</td><td class="td-manage"><button  onclick="xadmin.open(this.innerText,this.dataset.url)" data-url="{:admin_url('user/edit',['id'=>$vo['id']])}" type="button" class="layui-btn">编辑</button><button data-url="{:admin_url('user/del',['id'=>$vo['id']])}" type="button" class="layui-btn layui-btn-danger delete-btn">删除</button></td></tr>{/volist}</tbody></table></div><div class="layui-card-body ">{$datalist->render()|raw}</div></div>
</div>{/block}{block name="foot"}
<script>
(function(){$('.layui-table').on('click','.delete-btn',function(){var obj = this;layer.confirm('确认要删除吗？',function(index){//发异步删除数据console.log(obj.dataset)var index = layer.load()$.ajax({'type':'post','url':obj.dataset.url,success:function(data){layer.close(index)layer.alert(data.msg||'服务器出现错误',{icon:data.err_code === 200?6:5,end:function(){if(data.err_code === 200){$($(obj).parents('tr')[0]).remove();}}});}})});});
})();//不添加这个，无法显示下拉列表
layui.use("form", function () {})
layui.use(['form','layer'],function () {$("#input_excel_data").click(function () {// console.log('点击导入了');// layer.msg('点击导入了');layer.open({type: 1,area: ["500px", "360px"],title: "导入excel文件",content:$("#file_upload_div"),});});$("#upload_file").click(function () {//上传文档// var data = new  FormData;// data.append('file',my_file);// data.append('name',my_file.name);var formData = new FormData($('#uploadForm')[0]);$.ajax({'type':'post','url':'user/input_excel_data',contentType:false,processData:false,'data':formData,success:function (data) {console.log(data);// layer.msg('导入成功');layer.alert(data.msg,{icon:data.err_code === 200?6:5,end:function(){if(data.err_code === 200){// 获得frame索引var index = parent.layer.getFrameIndex(window.name);//关闭当前frameparent.layer.close(index);parent.location.reload();}}});},error:function(){layer.close(index);layer.alert("服务器出现错误")}});})
});</script>
{/block}

接下来我们看一看Tp6后台的代码 

这是控制器代码，需要在控制器中添加引入

use PhpOffice\PhpSpreadsheet\IOFactory;

   /*** 导入excel文档数据* @return \think\response\Json*/function input_excel_data(){$file = request()->file('file');if (!$file) {print_r('请选择需要导入的文件');die;}// 加载文件$spreadsheet = IOFactory::load($file->getRealPath());$sheet = $spreadsheet->getActiveSheet();// 处理文件数据$data = [];foreach ($sheet->getRowIterator() as $row) {$rowIndex = $row->getRowIndex();// 不读取第一行 标题if ($rowIndex == 1) {continue;}$cellIterator = $row->getCellIterator();$row = [];foreach ($cellIterator as $cell) {$row[] = $cell->getValue();}$data[] = $row;}// 数据入库处理print_r($data);die;return success_json('导入数据');}

需要添加路由

//上传文档，导入excel文档数据
Route::post('productImport', 'ProductOrder/importExcel');