需求 : 导入excel 校验第二列 数据格式 不正确则导出excel并提示错误信息
导入图：
预期效果：
引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
二 ：创建读监听器 继承 AbstractEasyExcelDataListener 并重写 validator, save 方法 校验数据和保存数据库操作 到此就大功告成
package com.example.jddemo.excel.upload.excellistener;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
@Scope("prototype")// 开启多例 单例存在并发安全问题 [使用如下]
/**
* @Resource
* private ObjectFactory<MyDataEasyExcelListener> obj;
*
* 使用: AbstractEasyExcelDataListener listener=obj.getObject();
*/
public class MyDataEasyExcelListener extends AbstractEasyExcelDataListener {
@Override
protected void validator(List<Object> row, StringBuilder msg) {
// 数据校验
Object number = row.get(1);
if (!isNumber(number.toString(), 2)) {
// System.out.println(number + "不是数字");
msg.append(number + "不是数字");
excelDto.setHaveError(true);
}
//校验 。。。。。。。。。
if (true) {
}
}
/**
* 保存数据库
*
* @param excelDto
*/
@Override
protected void save(ExcelDto excelDto) {
if(excelDto.isHaveError()){
System.out.println("校验数据有错误信息 不执行数据库操作");
return;
}
List<List<Object>> content = excelDto.getContent();
System.out.println("保存的数据:"+content);
}
/**
* 数字类型 最多保留两位小数
*
* @param str 校验的字符串
* @param scale 保留小数位
* @return
*/
public static boolean isNumber(String str, int scale) {
String reg = "^(\\d{1,8})(\\.\\d{1," + scale + "})?$";
return str.matches(reg);
}
}
三 ：AbstractEasyExcelDataListener 为抽象公共类 用户只需要继承它 重写 validator, save 方法
package com.example.jddemo.excel.upload.excellistener;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.apache.commons.lang3.StringUtils;
import java.io.File;
import java.io.IOException;
import java.util.*;
public abstract class AbstractEasyExcelDataListener extends AnalysisEventListener<Map<Integer, String>> {
protected ExcelDto excelDto = null;
private File tempFile = null;//临时文件
private ExcelWriter excelWriter = null;
private WriteSheet writeSheet = null;
private StringBuilder msg = null;// 行数据校验 提示信息
private List<List<String>> head =null;//生成动态表头
public AbstractEasyExcelDataListener() {
}
public AbstractEasyExcelDataListener buildSheetName(String sheetName){
excelDto.setSheetName(sheetName);
return this;
}
public AbstractEasyExcelDataListener buildFileName(String fileName){
excelDto.setFileName(fileName);
return this;
}
public void open() {
//生成临时文件
try {
excelDto = new ExcelDto();
head = new ArrayList<>();
tempFile = File.createTempFile("tmp", ".xlsx");
writeSheet = EasyExcel.writerSheet(StringUtils.isBlank(excelDto.getSheetName())?"模板":excelDto.getSheetName()).sheetNo(0).build();
//写入临时文件
//excelWriter = EasyExcel.write(tempFile).head(head).build();
excelWriter = EasyExcel.write("D:\\abc.xlsx").head(head).build();
excelDto.setHead(head);
} catch (IOException e) {
e.printStackTrace();
}
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
List<String> list = new ArrayList<>(headMap.values());
//初始化表头
if (head.size() == 0) {
head.add(new ArrayList<String>(Collections.singleton("错误列信息提示")));
list.forEach(item -> {
head.add(new ArrayList<String>(Collections.singleton(item)));
});
return;
}
for (int i = 0; i < list.size(); i++) {
String str = list.get(i);
List<String> headI = this.head.get(i + 1);
headI.add(str);
}
}
@Override
public void invoke(Map<Integer, String> row, AnalysisContext context) {
List<Object> responseRow = new ArrayList<>(row.values());
Integer rowIndex = context.readRowHolder().getRowIndex();
System.out.println("读取行" + rowIndex + "数据");
msg = new StringBuilder("");
//校验数据
this.validator(responseRow, msg);
responseRow.add(0, msg.toString());
excelDto.getTmpContent().add(responseRow);
//保存全量数据 高并发或者数据量较大时不建议使用
if (this.enableContent()) {
excelDto.getContent().add(responseRow);
}
// 三千条数据 写入一次
if (excelDto.getTmpContent().size() >= excelDto.getCount()) {
if (!enableContent()) {
excelDto.setContent(excelDto.getTmpContent());
this.save(excelDto);//数据落库
}
excelWriter.write(excelDto.getTmpContent(), writeSheet);//写入新文件
excelDto.getTmpContent().clear();
}
}
/**
* 返回 true ExcelDto content 会保存excel 中全量数据
* 返回 false ExcelDto content 不会保存数据
*
* @return
*/
protected boolean enableContent() {
return false;
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("数据读取完毕");
if (!enableContent()) {
excelDto.setContent(excelDto.getTmpContent());
}
excelWriter.write(excelDto.getTmpContent(), writeSheet);//写入新文件
// 千万别忘记finish 会帮忙关闭流
if (excelWriter != null) {
excelWriter.finish();
}
this.save(excelDto);//数据落库
this.saveFile(tempFile);// 保存临时文件 到服务器
excelDto.getTmpContent().clear();//清除临时数据
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
//手动抛出异常 读操作终止
throw new RuntimeException("excel处理数据监听异常:" + exception.getMessage());
}
/**
* 保存附件到服务器
*
* @param tempFile
*/
protected void saveFile(File tempFile) {
System.out.println("保存文件到文件服务器");
this.excelDto.setUrl("url://文件服务器地址");
}
public void close() {
// 千万别忘记finish 会帮忙关闭流
if (excelWriter != null) {
excelWriter.finish();
}
if (tempFile != null && tempFile.exists()) {
tempFile.delete();
}
}
/**
* 行数据校验
*
* @param row
* @param msg
*/
protected abstract void validator(List<Object> row, StringBuilder msg);
/**
* 数据库操作 保存数据
*
* @param excelDto
*/
protected abstract void save(ExcelDto excelDto);
public ExcelDto getExcelDto() {
return excelDto;
}
public void setExcelDto(ExcelDto excelDto) {
this.excelDto = excelDto;
}
}
四： 实体类
package com.example.jddemo.excel.upload.excellistener;
import java.util.ArrayList;
import java.util.List;
public class ExcelDto {
private List<List<String>> head = new ArrayList<>();//表头
private List<List<Object>> tmpContent = new ArrayList<>();//临时数据
private List<List<Object>> content = new ArrayList<>();//保存全量数据 高并发或者数据量较大时不建议使用 [如需开启 重写 enableContent()方法 返回true]
private boolean isHaveError;//默认false
private int count = 3000;//三千条数据 提交一次
private String url;//文件上传后的地址
public List<List<String>> getHead() {
return head;
}
public void setHead(List<List<String>> head) {
this.head = head;
}
public List<List<Object>> getTmpContent() {
return tmpContent;
}
public void setTmpContent(List<List<Object>> tmpContent) {
this.tmpContent = tmpContent;
}
public boolean isHaveError() {
return isHaveError;
}
public void setHaveError(boolean haveError) {
isHaveError = haveError;
}
public int getCount() {
return count;
}
public void setCount(int count) {
this.count = count;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public List<List<Object>> getContent() {
return content;
}
public void setContent(List<List<Object>> content) {
this.content = content;
}
@Override
public String toString() {
return "ExcelDto{" +
"head=" + head +
", tmpContent=" + tmpContent +
", saveContent=" + content +
", isHaveError=" + isHaveError +
", count=" + count +
", url='" + url + '\'' +
'}';
}
}
测试：
package com.example.jddemo.excel.upload;
import com.alibaba.excel.EasyExcel;
import com.example.jddemo.excel.upload.excellistener.AbstractEasyExcelDataListener;
import com.example.jddemo.excel.upload.excellistener.ExcelDto;
import com.example.jddemo.excel.upload.excellistener.MyDataEasyExcelListener;
import com.example.jddemo.response.ResponseEntity;
import org.springframework.beans.factory.ObjectFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.annotation.Resource;
/**
* 程序员 by dell
* time 2020-12-09
**/
@Controller
public class AppController {
@Resource
private ObjectFactory<MyDataEasyExcelListener> obj;
@RequestMapping(value = "/excel")
public void exce(String file){
AbstractEasyExcelDataListener listener=obj.getObject();
System.out.println("对象:"+listener);
ResponseEntity<String> response = new ResponseEntity<>();
response.setCode(ResponseEntity.CODE_NORMAL);
ExcelDto excelDto = null;
try {
listener.open();//1606185114659.xlsx
listener.buildFileName("文件名")
.buildSheetName("shee名");
EasyExcel.read("D:\\"+file).sheet().headRowNumber(1)
.registerReadListener(listener).doRead();
excelDto = listener.getExcelDto();
if (excelDto.isHaveError()) {
throw new RuntimeException("校验数据不通过 请更改数据后重新导入");
}
System.out.println(excelDto);
} catch (RuntimeException e) {
e.printStackTrace();
response.setCode(ResponseEntity.CODE_WARN);
response.setMessage(e.getMessage());
response.setData(excelDto.getUrl());
}finally {
listener.close();
}
}
}
文件默认导出D盘 也可上传文件服务器
如果帮到你点个赞关注下！！！ O(∩_∩)O哈哈~
1581
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
