跟大家一起来讨论下如何把excel数据导入到数据库,废话少说,直接上干货。
1,首先大家先到网上下载phpExcel 代码包。
下载PHPExcel了扩展http://phpexcel.codeplex.com/
好了,下载需的工具自个儿去找吧,我就不跟你起哄了,废话少说直接上代码!!!
后端(ThinkPHP v6):
<?php
namespace api\admin\controller;
use think\admin\Controller;
use vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Spreadsheet;
use vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\IOFactory;
class WithdrawalMoney extends Controller
{
protected $table = 'withdrawal_money';
public function excel_import()
{
ini_set('memory_limit', '1024M');
require root_path() . "vendor/PHPExcel/Classes/PHPExcel.php";
require root_path() . "vendor/PHPExcel/Classes/PHPExcel/IOFactory.php";
header("Content-type:text/html;charset=utf-8");
//实例化主文件
$file = $_FILES['file'];//接收前台传过来的execl文件
//截取文件的后缀名,转化成小写
$extension = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
if ($extension == "xlsx") {
//2007(相当于是打开接收的这个excel)
$objReader = \PHPExcel_IOFactory::createReader('Excel2007');
} else {
//2003(相当于是打开接收的这个excel)
$objReader = \PHPExcel_IOFactory::createReader('Excel5');
}
$objContent = $objReader->load($file['tmp_name']);
if ($objContent) {
$sheetContent = $objContent->getSheet(0)->toArray();
$operator = $this->getUserId();//操作者
//删除第一行标题
unset($sheetContent[0]);
foreach ($sheetContent as $k => $v) {
$userId = $this->app->db->name('tth_user')->where(['userPhone' => $v[1]])->value('userId');
$arr['userPhone'] = $v[1];
$arr['withdrawal'] = $v[2];
$arr['createTime'] = time();
$arr['operator'] = $operator;
$arr['userId'] = $userId ?? null;
$res[] = $arr;
}
//执行写入
$re = $this->app->db->name($this->table)->insertAll($res);
if ($re) {
$this->success('导入成功 !');
} else {
$this->error('导入失败 !');
}
} else {
$this->error('请导入表格 !');
}
}
}
前端代码(layui):
<fieldset>
<legend>条件搜索</legend>
<form class="layui-form layui-form-pane form-search" action="{:request()->url()}" onsubmit="return false"
method="get" autocomplete="off">
<div class="layui-form-item layui-inline">
<label class="layui-form-label">创建时间</label>
<div class="layui-input-inline">
<input data-date-range name="createTime" value="{:input('get.createTime')}" placeholder="请选择创建时间"
class="layui-input">
</div>
</div>
<div class="layui-form-item layui-inline">
<button class="layui-btn layui-btn-primary"><i class="layui-icon"></i> 搜 索</button>
<a class="layui-btn layui-btn-primary import_btn" data-type="imports">导入数据</a>
</div>
</form>
</fieldset>
<script>
window.form.render();
//点击开始上传文件操作
layui.use(['upload'], function () {
var upload = layui.upload;
upload.render({
elem: '.import_btn',
url: "{:url('WithdrawalMoney/excel_import')}",
accept: 'file', //普通文件
exts: 'xls|excel|xlsx', //允许的文件后缀
before: function () {
uploadIndex = layer.msg('上传中,请稍后...', {time: 0, icon: 16, shade: 0.01})
},
done: function (res, index, upload) {
//执行导入接口后操作
if (res.code == 1) {
parent.layer.msg("上传成功!", {time: 1000}, function () {
//重新加载父页面
window.location.reload();
});
}
},
error: function (index, upload) {
layer.msg('上传失败!');
},
});
})
</script>
注意:引入PHPExcel插件的时候要注意路径。
效果图: