Spring Boot + Vue 实现Excel文件批量导入
开发工具：IDEA 2020.3 + VScode + MySql 5.7
1、数据库设计
2、前端部分
导入按钮
<el-col :span="1.5">
<el-button
type="info"
icon="el-icon-upload2"
size="mini"
@click="handleImport"
v-hasPermi="['wxapp:houseInfo:export']"
>导入
</el-button>
</el-col>
导入Excel文件对话框
<!-- 房屋信息导入对话框 -->
<el-dialog
:title="uploadExcel.title"
:visible.sync="uploadExcel.open"
width="400px"
append-to-body
>
<el-upload
ref="uploadExcel"
:limit="4"
accept=".xlsx, .xls"
:headers="uploadExcel.headers"
:action="
uploadExcel.url + '?updateSupport=' + uploadExcel.updateSupport
"
:disabled="uploadExcel.isUploading"
:on-progress="handleFileUploadProgress"
:on-success="handleFileSuccess"
:auto-upload="false"
drag
>
<div class="el-upload__text">
将文件拖到此处，或
<em>点击上传</em>
</div>
<div class="el-upload__tip" slot="tip">
<el-checkbox
v-model="uploadExcel.updateSupport"
/>是否更新已经存在的房屋信息数据
<el-link
type="info"
style="font-size: 12px; color: blue"
@click="importTemplate"
>下载模板</el-link
>
</div>
<div class="el-upload__tip" style="color: red" slot="tip">
步骤提示：1、下载模板；2、填好数据；3、上传excel文件
</div>
<div class="el-upload__tip" style="color: red" slot="tip">
其他提示：每次只能上传一个excel文件！
</div>
</el-upload>
<div slot="footer" class="dialog-footer">
<el-button type="primary" @click="submitFileForm">确 定</el-button>
<el-button @click="uploadExcel.open = false">取 消</el-button>
</div>
</el-dialog>
在 methods: {…}中添加导入文件相关触发函数
methods: {
/** 导入按钮操作 */
handleImport() {
this.uploadExcel.title = "从业人员信息导入";
this.uploadExcel.open = true;
},
/** 下载模板操作 */
importTemplate() {
importTemplate().then((response) => {
this.download(response.msg);
});
},
// 文件上传中处理
handleFileUploadProgress(event, file, fileList) {
this.uploadExcel.isUploading = true;
},
// 文件上传成功处理
handleFileSuccess(response, file, fileList) {
this.uploadExcel.open = false;
this.uploadExcel.isUploading = false;
this.$refs.uploadExcel.clearFiles();
this.$alert(response.msg, "导入结果", { dangerouslyUseHTMLString: true });
this.getList();
},
// 提交上传文件
submitFileForm() {
this.$refs.uploadExcel.submit();
},
}
在export default{…}添加一些导入参数设置，url为后端处理接口，getToken要导入相关包
export default {
name: "HouseInfo",
data() {
return {
// 房间导入参数
uploadExcel: {
// 是否显示弹出层（房间信息导入）
open: false,
// 弹出层标题（房间信息导入）
title: "",
// 是否禁用上传
isUploading: false,
// 是否更新已经存在的房屋信息
updateSupport: 0,
// 设置上传的请求头部
headers: { Authorization: "Bearer " + getToken() },
// 上传的地址
url: process.env.VUE_APP_BASE_API + "/wxapp/houseInfo/importData",
},
import {
listHouseInfo,
getHouseInfo,
delHouseInfo,
addHouseInfo,
updateHouseInfo,
exportHouseInfo ,
importTemplate,
} from "@/api/wxapp/houseInfo";
import { getToken } from "@/utils/auth";
3、后端部分
- 实体类domain
在每个实体类的字段上加上注解@Excel，此注解既可导入也可导出。
/** 房间编号 */
@Excel(name = "房间编号")
@TableId(type = IdType.UUID)
private String fjid;
/** 业主编号 */
@Excel(name = "业主编号")
private String yzid;
/** 业主姓名 */
//@Excel(name = "业主姓名")
private String yzName;
/** 租户编号 */
@Excel(name = "租户编号")
private String zhid;
/** 房间状态 */
@Excel(name = "房间状态")
private Integer state;
/** 建筑面积 */
@Excel(name = "建筑面积")
private Double area;
/** 楼号 */
@Excel(name = "楼号")
private String floorid;
/** 楼名 */
//@Excel(name = "楼名")
private String floorName;
/** 层数 */
@Excel(name = "层数")
private Integer layers;
/** 门牌号 */
@Excel(name = "门牌号")
private String housenumber;
/** 楼名 */
@Excel(name = "楼名")
private String FloorName;
/** 物业费余额 */
@Excel(name = "物业费余额")
private Double propertyBalance;
/** 水费余额 */
@Excel(name = "水费余额")
private Double waterBalance;
/** 电费余额 */
@Excel(name = "电费余额")
private Double electricBalance;
/** 燃气费余额 */
@Excel(name = "燃气费余额")
private Double gasBalance;
/** 供暖费余额 */
@Excel(name = "供暖费余额")
private Double heatingBalance;
/** 删除 */
@Excel(name = "删除")
private Integer deleted;
/** 创建时间 */
@JsonFormat(pattern = "yyyy-MM-dd")
@Excel(name = "创建时间", width = 30, dateFormat = "yyyy-MM-dd")
private String createTime;
/** 更新时间 */
@JsonFormat(pattern = "yyyy-MM-dd")
@Excel(name = "更新时间", width = 30, dateFormat = "yyyy-MM-dd")
private String updateTime;
- 控制层Controller
/**
* 房间信息Controller
*
* @author lee
* @date 2021-04-25
*/
@RestController
@RequestMapping("/wxapp/houseInfo")
public class HouseInfoController extends BaseController
{
@Autowired
private IHouseInfoService houseInfoService;
/**
* 导入房间信息
* */
@PostMapping("/importData")
@ResponseBody
public AjaxResult importData(MultipartFile file, boolean updateSupport) throws Exception
{
ExcelUtil<HouseInfo> util = new ExcelUtil<HouseInfo>(HouseInfo.class);
List<HouseInfo> userList = util.importExcel(file.getInputStream());
for (HouseInfo houseInfo : userList) {
houseInfoService.addHouseInfoExcel(houseInfo);
//return toAjax(houseInfoService.addHouseInfoExcel(houseInfo));
}
//String operName = ShiroUtils.getSysUser().getLoginName();
//String message = houseInfoService.insertHouseInfo(userList);
//return AjaxResult.success(message);
return AjaxResult.success();
}
}
- service层
/**
* 批量导入房间信息
*
* @param houseInfo 房间信息
* @return 结果
*/
public int addHouseInfoExcel(HouseInfo houseInfo);
- serviceImpl层
/**
* Excel导入房间信息
*
* @param houseInfo 房间信息
* @return 结果
*/
/* @Override
public List<HouseInfo> addHouseInfoExcel(HouseInfo houseInfo)
{
return houseInfoMapper.addHouseInfoExcel(houseInfo);
}*/
@Override
public int addHouseInfoExcel(HouseInfo houseInfo)
{
return houseInfoMapper.addHouseInfoExcel(houseInfo);
}
- mapper
/**
* 批量导入房间信息
*
* @param houseInfo 房间信息
* @return 结果
*/
//public List<HouseInfo> addHouseInfoExcel(HouseInfo houseInfo);
public int addHouseInfoExcel(HouseInfo houseInfo);
- mapper.xml
<!-- excel导入 -->
<insert id="addHouseInfoExcel" parameterType="HouseInfo" >
INSERT INTO db_house_info
(FJID, YZID, ZHID, state, area, floorId, layers, houseNumber, property_balance, water_balance, electric_balance, gas_balance, heating_balance, deleted, create_time, update_time)
VALUES
(#{fjid},#{yzid},#{zhid},#{state},#{area},#{floorid},#{layers},#{housenumber},#{propertyBalance},#{waterBalance},#{electricBalance},#{gasBalance},#{heatingBalance},#{deleted},#{createTime},#{updateTime})
</insert>
4、结果演示
点击导入，选择导入的excel文件，点击确定即可导入！
结果提示：
