文件上传（二）
作者：杨锦龙时间：2026-04-27点击量：0次
????️ 准备工作
如果你还没有安装 PhpSpreadsheet，请在项目根目录运行：
composer require phpoffice/phpspreadsheet
1.前端import_data.html
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Excel导入</title>
<link href="https://cdn.jsdelivr.net/npm/layui@2.6.8/dist/css/layui.css" rel="stylesheet">
</head>
<body>
<div class="layui-container" style="padding-top: 50px;">
<div class="layui-row">
<div class="layui-col-md6 layui-col-md-offset3">
<fieldset class="layui-elem-field layui-field-title">
<legend>旅游数据导入 (XLSX)</legend>
</fieldset>
<form class="layui-form" action="" lay-filter="importForm">
<div class="layui-form-item">
<label class="layui-form-label">选择文件</label>
<div class="layui-input-block">
<!-- 这里的 id="myfile" 用于绑定上传事件 -->
<button type="button" class="layui-btn" id="myfile">
<i class="layui-icon"></i> 选择XLSX文件
</button>
<div class="layui-form-mid layui-word-aux" id="filename_tip">未选择</div>
</div>
</div>
<div class="layui-form-item">
<div class="layui-input-block">
<!-- 提交按钮， lay-filter="submitBtn" 用于监听点击 -->
<button class="layui-btn layui-btn-normal" lay-submit lay-filter="submitBtn">开始导入</button>
</div>
</div>
</form>
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/layui@2.6.8/dist/layui.js"></script>
<script>
layui.use(['upload', 'form', 'layer'], function(){
var upload = layui.upload;
var form = layui.form;
var layer = layui.layer;
var $ = layui.$;
// 1. 渲染上传实例
var uploadInst = upload.render({
elem: '#myfile',
url: "{:url('Apply/saveImportData')}",
accept: 'file',
exts: 'xlsx|xls',
auto: false, // 禁止自动上传
// 关键修改：将 done 回调直接放在这里，而不是使用 uploadInst.on
done: function(res){
// 这里是上传完成后的回调
console.log(res);
if(res.code == 200){
layer.msg(res.msg, {icon: 1}, function(){
location.reload();
});
} else {
layer.msg(res.msg || '导入失败', {icon: 2});
}
},
error: function(){
layer.msg('上传请求异常', {icon: 2});
},
choose: function(obj){
var files = obj.pushFile();
$.each(files, function(index, file){
$('#filename_tip').text(file.name);
});
}
});
// 2. 监听表单提交事件
form.on('submit(submitBtn)', function(data){
// 检查是否选择了文件
if($('#filename_tip').text() === '未选择' || $('#filename_tip').text() === ''){
layer.msg('请先选择文件');
return false;
}
// 手动触发上传（调用实例的 upload 方法）
uploadInst.upload();
// 阻止表单默认跳转
return false;
});
});
</script>
</body>
</html>2.后端saveImportData.php
<?php
/**
* Created by YangKaiRui.
* User: Administrator
* Date: 2026/4/27
* Time: 10:56
*/
namespace app\yejuzhi\controller;
use think\Db;
use think\facade\Request;
use PhpOffice\PhpSpreadsheet\IOFactory; // 引入 PhpSpreadsheet
require_once APP_PATH."common/lib/PhpSpreadsheet/autoload.php";
class Apply extends BaseController
{
public function importData(){
return $this->fetch('import_data1');
}
public function saveImportData(){
$request = Request::instance();
// 1. 获取上传的文件
$file = $request->file('file'); // 对应前端 input name="file"
if (empty($file)) {
return json(['code' => 400, 'msg' => '请选择上传文件']);
}
// 2. 验证文件并移动到 runtime 目录
// 保存到 runtime/upload/ 目录下
$info = $file->validate(['ext' => 'xlsx,csv,xls'])->move(WEB_PATH . 'runtime' . DS . 'excel');
if (!$info) {
return json(['code' => 400, 'msg' => $file->getError()]);
}
// 3. 获取文件完整路径
$filePath = WEB_PATH . 'runtime' . DS . 'excel' . DS . $info->getSaveName();
try {
// 4. 使用 PhpSpreadsheet 读取 Excel
$spreadsheet = IOFactory::load($filePath);
$worksheet = $spreadsheet->getActiveSheet();
// 将工作表转换为数组
$rows = $worksheet->toArray();
// 5. 遍历数据 (跳过表头，即索引 0)
foreach ($rows as $key => $row) {
if ($key === 0) {
continue; // 跳过第一行表头
}
// 过滤空行
if (empty(array_filter($row))) {
continue;
}
/*$data = array();
$data['sb_date'] = intval($row[0]);
$data['today_num'] = intval($row[1]);// 当日接待游客人次
$data['gn_num'] = intval($row[2]);// 国内游客
$data['wg_num'] = intval($row[3]);// 外国游客
$data['gat_num'] = intval($row[4]);// 港澳台游客
$data['today_fee'] = floatval($row[5]);// 当日经营/营业收入
$data['ticket_fee'] = floatval($row[6]);// 门票收入
$data['good_fee'] = floatval($row[7]);// 商品收入
$data['food_fee'] = floatval($row[8]);// 餐饮收入
$data['traffic_fee'] = floatval($row[9]);// 交通收入
$data['hotel_fee'] = floatval($row[10]);// 住宿收入
$data['act_fee'] = floatval($row[11]);//演艺收入
$data['play_fee'] = floatval($row[12]);// 游乐收入
$data['other_fee'] = floatval($row[13]);// 其他收入
$data['place_id'] = intval($row[14]);// 景区ID
$place = $this->DbSy->FindWhere(41,array('id'=>$data['place_id']));
$data['title'] = $place['title'];
$data['user_id'] = $this->user['id'];
$data['province'] = $place['province'];
$data['city'] = $place['city'];
$data['county'] = $place['county'];
$data['address'] = $place['address'];
$data['admin_type'] = 1;
$data['status'] = 1;
$data['sb_time'] = strtotime($data['sb_date']);
$data['number'] = date("YmdHis",time()).rand(100,999);
$data['inputtime'] = $data['updatetime'] = time();
$data['add_date'] = $data['update_date'] = date("Y-m-d H:i:s",time());
$info = Db::name("yjz_apply")->insertGetId($data);
if($info){
$num++;
}*/
$data = [
'sb_date' => intval($row[0]),
'today_num' => intval($row[1]), // 当日接待游客人次
'gn_num' => intval($row[2]), // 国内游客
'wg_num' => intval($row[3]), // 外国游客
'gat_num' => intval($row[4]), // 港澳台游客
'today_fee' => floatval($row[5]), // 当日经营/营业收入
'ticket_fee' => floatval($row[6]), // 门票收入
'good_fee' => floatval($row[7]), // 商品收入
'food_fee' => floatval($row[8]), // 餐饮收入
'traffic_fee' => floatval($row[9]), // 交通收入
'hotel_fee' => floatval($row[10]), // 住宿收入
'act_fee' => floatval($row[11]),// 演艺收入
'play_fee' => floatval($row[12]),// 游乐收入
'other_fee' => floatval($row[13]), // 其他收入
'place_id' => floatval($row[14]) // 景区ID
];
$place = $this->DbSy->FindWhere(41,array('id'=>$data['place_id']));
$data['title'] = $place['title'];
$data['user_id'] = $this->user['id'];
$data['province'] = $place['province'];
$data['city'] = $place['city'];
$data['county'] = $place['county'];
$data['address'] = $place['address'];
$data['admin_type'] = 1;
$data['status'] = 1;
$data['sb_time'] = strtotime($data['sb_date']);
$data['number'] = date("YmdHis",time()).rand(100,999);
$data['inputtime'] = $data['updatetime'] = time();
$data['add_date'] = $data['update_date'] = date("Y-m-d H:i:s",time());
$values[] = $data;
}
// 6. 批量插入数据库
if (!empty($values)) {
Db::startTrans(); // 开启事务
try {
Db::name('yjz_apply')->strict(false)->insertAll($values);
Db::commit(); // 提交事务
} catch (\Exception $e) {
Db::rollback(); // 回滚
return json(['code' => 500, 'msg' => '数据库写入失败：' . $e->getMessage()]);
}
}
// 7. 返回成功 JSON (Layui 需要 code: 200)
return json([
'code' => 200,
'msg' => '导入成功，共 ' . count($values) . ' 条数据',
'data' => []
]);
} catch (\Exception $e) {
return json(['code' => 500, 'msg' => '文件解析失败：' . $e->getMessage()]);
} finally {
// 可选：导入完成后删除临时文件
if (file_exists($filePath)) {
@unlink($filePath);
}
}
}
}