在项目根目录安装
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"><i
class="layui-icon"></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);
//关闭当前frame
parent.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');
