需求:
SpringBoot搭建项目实现文件的上传,对文件字段进行校验,将校验后的数据用对象封装,调用dao接口进行批量update操作,结果保存到MySQL数据库。对有格式有错误的数据进行统计并反馈到前端页面。
关于实现批量update操作,见(https://blog.csdn.net/m0_52850461/article/details/121504680?spm=1001.2014.3001.5501)
思路:
后端使用MultipartFile 接收文件,
使用EasyExcel组件完成Excel文件的处理,
在service目录创建一个监听器来监控字段,并进行校验。
实现:
准备表格:
pom文件:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
实体类:
@Data
@AllArgsConstructor
public class CustomNetworkBaseSupplyInfo {
@ExcelProperty("DNN")
private String dnnName;
@ExcelProperty("用户名称")
private String cusName;
@ExcelProperty("基站IP(多个以英文逗号相隔)")
private String basestationIp;
}
Controller层:
/**
* 批量导入信息
*
* @param file
* @throws IOException
*/
@PostMapping("ImportInfo")
@ApiOperation(value = "批量导入信息", httpMethod = "POST")
public ReturnJson importCustomNetworkInfo(@ApiParam(name = "file", value = "要导入的文件") MultipartFile file,) throws IOException {
ExcelImportResult result = customNetworkSupplyInfoService.importCustomNetworkInfo(file);
return new ReturnJson(result);
}
service层:
/**
* 通过Excel批量导入信息
*
* @param file
* @return
* @throws IOException
*/
@Override
public ExcelImportResult importCustomNetworkInfo(MultipartFile file) throws IOException {
//保存导出来的信息
List<CustomNetworkBaseSupplyInfo> lists = new ArrayList<>();
//记录错误的信息
StringBuffer errorMessage = new StringBuffer();
//记录操作的结果,初始化为false
String result = "false";
//创建监听器
CustomNetworkSupplyListener listener = new CustomNetworkSupplyListener(lists, errorMessage);
EasyExcel.read(file.getInputStream(), CustomNetworkBaseSupplyInfo.class, listener).sheet().doRead();
//判断Excel信息是否有错误
if (!StringUtils.isEmpty(errorMessage.toString())) {
result = "success";
//调用接口存到数据库
supplyInfoMapper.importCustomNetworkInfo(lists, updateName);
}
return new ExcelImportResult(result, errorMessage.toString());
}
监听器:
/**
* 信息监听器
*
* @author libo
* @data 2021/11/19 - 15:37
*/
public class CustomNetworkSupplyListener extends AnalysisEventListener<CustomNetworkBaseSupplyInfo> {
//保存导出来的信息
private List<CustomNetworkBaseSupplyInfo> lists;
//记录错误的信息
private StringBuffer errorMessage;
public CustomNetworkSupplyListener(List<CustomNetworkBaseSupplyInfo> lists, StringBuffer errorMessage) {
this.lists = lists;
this.errorMessage = errorMessage;
}
@Override
public void invoke(CustomNetworkBaseSupplyInfo customNetworkBaseSupplyInfo, AnalysisContext analysisContext) {
//格式检查
StringBuffer errorInfo = validInfo(customNetworkBaseSupplyInfo);
//获取当前Excel行号
ReadRowHolder readRowHolder = analysisContext.readRowHolder();
Integer index = readRowHolder.getRowIndex() + 1;
//判断当前行号的数据是否有数据错误
if (StringUtils.isNotEmpty(errorInfo)) {
errorMessage.append("第" + index + "行填写有误:" + errorInfo + "\n");
} else {
lists.add(customNetworkBaseSupplyInfo);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
public StringBuffer validInfo(CustomNetworkBaseSupplyInfo supplyInfo) {
//记录单个error
StringBuffer errorInfo = new StringBuffer();
//dnn非空
String dnnName = supplyInfo.getDnnName();
if (StringUtils.isEmpty(dnnName)) {
errorInfo.append("DNN的值不能为空;");
}
//cusName非空
String cusName = supplyInfo.getCusName();
if (StringUtils.isEmpty(cusName)) {
errorInfo.append("用户名称的值不能为空;");
}
//多个以英文逗号相隔,格式为:设备名称/设备IP
String equipmentB = supplyInfo.getEquipmentB();
if (!checkIp(equipmentB)) {
errorInfo.append("B设备格式有误");
}
/**
* ip的格式判断,格式为:设备名称1/设备IP1,设备名称2/设备IP2,...
*
* @param baseIp
* @return
*/
public boolean checkIp(String baseIp) {
if (!StringUtils.isEmpty(baseIp)) {
String[] baseIps = baseIp.split(",");
//大于1
if (baseIps.length >= 1) {
for (String ip : baseIps) {
String[] lists = ip.split("/");
if (lists.length >= 2 && !"NULL".equals(lists[1])) { //ip为有效值
if (Validator.isIpv4(lists[1]) || Validator.isIpv6(lists[1])) {
continue;
} else { //不符合Ipv4或者Ipv6即为错误,其他为正确值
return false;
}
}
}
}
}
return true;
}
}
Mapper层:
Integer importCustomNetworkInfo(@Param("supplyInfos")List<CustomNetworkBaseSupplyInfo> customNetworkBaseSupplyInfo);
Mapper.xml文件:
<update id="importCustomNetworkInfo">
update cus_network_info set
<foreach collection="supplyInfos" item="supplyInfo" open="cus_name = case dnn_name" close="end,">
when #{supplyInfo.dnnName} then #{supplyInfo.cusName}
</foreach>
<foreach collection="supplyInfos" item="supplyInfo" open="basestation_ip = case dnn_name" close="end">
when #{supplyInfo.dnnName} then #{supplyInfo.basestationIp}
</foreach>
where dnn_name in
<foreach collection="supplyInfos" item="supplyInfo" open="(" close=")" separator=",">
#{supplyInfo.dnnName}
</foreach>
</update>