Easyexcel（2-文件多Sheet、不同对象、多数据读取）
同步读取读取单个Sheet
[*]通过sheet方法指定对应的Sheet名称或下标读取文件信息
[*]通过doReadSync方法实现同步读取
@Data
public class UserExcel {
@ExcelIgnore
private Integer id;
@ExcelProperty(index = 0, value = "姓名")
private String name;
@ExcelProperty(index = 1, value = "年龄")
private Integer age;
@DateTimeFormat(value = "yyyy-MM-dd")
@ExcelProperty(index = 2, value = "出生日期")
private Date birthday;
}@RestController
public class Test02Controller {
/**
* 上传单个文件, 同步读取excel文件
*/
@PostMapping("/uploadFile")
public void uploadFile(MultipartFile file) {
try (InputStream in = file.getInputStream()) {
List<UserExcel> userExcelList = EasyExcel.read(in)
// 读取第一个sheet
.sheet(0)
// 如果第一行才是标题，第二行是数据，从第二行开始读取
.headRowNumber(1)
.head(UserExcel.class)
.doReadSync();
for (UserExcel userExcel : userExcelList) {
System.out.println(userExcel);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}读取多个Sheet（同一个对象）
使用doReadAllSync方法读取所有Sheet，适用于每个Sheet的对象都一致的情况
@PostMapping("/uploadFile2")
public void uploadFile2(MultipartFile file) {
try (InputStream in = file.getInputStream()) {
List<UserExcel> userExcelList = EasyExcel.read(in)
// 如果第一行才是标题，第二行是数据，从第二行开始读取
.headRowNumber(1)
.head(UserExcel.class)
.doReadAllSync();
for (UserExcel userExcel : userExcelList) {
System.out.println(userExcel);
}
} catch (Exception e) {
e.printStackTrace();
}
}读取多个Sheet（不同对象）
当每个Sheet的对象不一致的情况下，使用doReadAllSync方法无法指定每个Sheet的对象，可以依次读取Sheet进行解析
注意：依次读取Sheet会出现重复读取流对象的情况，而一个流对象只能读取一次，重复使用会导致异常
@PostMapping("/uploadFile4")
public void uploadFile4(MultipartFile file) {
InputStream in = null;
try {
in = file.getInputStream();
List<UserExcel> userExcelList1 = EasyExcel.read(in)
// 读取第一个sheet
.sheet(0)
// 如果第一行才是标题，第二行是数据，从第二行开始读取
.headRowNumber(1)
.head(UserExcel.class)
.doReadSync();
// 读取剩余的sheet
in = file.getInputStream();
List<UserExcel> userExcelList2 = EasyExcel.read(in)
.sheet(1)
// 如果第一行才是标题，第二行是数据，从第二行开始读取
.headRowNumber(1)
.head(UserExcel.class)
.doReadSync();
List<UserExcel> userExcelList = new ArrayList<>();
userExcelList.addAll(userExcelList1);
userExcelList.addAll(userExcelList2);
for (UserExcel userExcel : userExcelList) {
System.out.println(userExcel);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (in != null) {
in.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}异步读取
监听器
查看监听器源码，通过实现ReadListener接口或继承AnalysisEventListener类可以自定义读取Sheet监听器
public interface ReadListener<T> extends Listener {
// 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则继续读取下一行
default void onException(Exception exception, AnalysisContext context) throws Exception {
throw exception;
}
// 获取表头数据
default void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {}
// 一行行读取表格内容
void invoke(T data, AnalysisContext context);
// 读取条额外信息:批注、超链接、合并单元格信息等
default void extra(CellExtra extra, AnalysisContext context) {}
// 读取完成后的操作
void doAfterAllAnalysed(AnalysisContext context);
// 是否还有数据
default boolean hasNext(AnalysisContext context) {
return true;
}
}异常处理
ExcelDateConvertException
表示数据转换异常错误，出现该异常时会继续解析文件信息
public abstract class AnalysisEventListener<T> implements ReadListener<T> {
// 解析表头数据
@Override
public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
invokeHeadMap(ConverterUtils.convertToStringMap(headMap, context), context);
}
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {}
}ExcelAnalysisStopException
非数据转换异常错误，在onexcetpion中抛出该异常后停止解析
@Getter
@Setter
@EqualsAndHashCode
public class ExcelDataConvertException extends ExcelRuntimeException {
private Integer rowIndex;
private Integer columnIndex;
private CellData<?> cellData;
private ExcelContentProperty excelContentProperty;
public ExcelDataConvertException(Integer rowIndex, Integer columnIndex, CellData<?> cellData,
ExcelContentProperty excelContentProperty, String message) {
super(message);
this.rowIndex = rowIndex;
this.columnIndex = columnIndex;
this.cellData = cellData;
this.excelContentProperty = excelContentProperty;
}
public ExcelDataConvertException(Integer rowIndex, Integer columnIndex, CellData<?> cellData,
ExcelContentProperty excelContentProperty, String message, Throwable cause) {
super(message, cause);
this.rowIndex = rowIndex;
this.columnIndex = columnIndex;
this.cellData = cellData;
this.excelContentProperty = excelContentProperty;
}
}读取单个Sheet（不指定对象）
读取文件时使用doRead方法进行异步操作，同时指定对应的监听器解析文件数据
Map中的key表示列号、value表示数据
public class ExcelAnalysisStopException extends ExcelAnalysisException {
public ExcelAnalysisStopException() {}
public ExcelAnalysisStopException(String message) {
super(message);
}
public ExcelAnalysisStopException(String message, Throwable cause) {
super(message, cause);
}
public ExcelAnalysisStopException(Throwable cause) {
super(cause);
}
}public class UserExcelListener1 extends AnalysisEventListener<Map<Integer, String>> {
Logger log = LoggerFactory.getLogger(getClass());
private List<Map<Integer, String>> userExcelList = new ArrayList<>();
@Override
public void invoke(Map<Integer, String> map, AnalysisContext analysisContext) {
log.info("解析到一条数据:{}", JSON.toJSONString(map));
userExcelList.add(map);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("已解析完所有数据!");
userExcelList.clear();
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException convertException = (ExcelDataConvertException) exception;
Integer row = convertException.getRowIndex();
log.error("第{}行数据转换失败，异常信息：{}", row, exception.getMessage());
} else {
log.error("导入其他异常信息：{}", exception.getMessage());
}
}
public List<Map<Integer, String>> getUserExcelList() {
return userExcelList;
}
public void setUserExcelList(List<Map<Integer, String>> userExcelList) {
this.userExcelList = userExcelList;
}
}读取单个Sheet（指定对象）
@PostMapping("/uploadFile1")
public void uploadFile1(MultipartFile file) {
try (InputStream in = file.getInputStream()) {
UserExcelListener1 listener = new UserExcelListener1();
EasyExcel.read(in, listener)
.sheet(0)
.headRowNumber(1) // 第一行是标题, 从第二行开始读取
.doRead();
} catch (Exception e) {
e.printStackTrace();
}
}public class UserExcelListener extends AnalysisEventListener<UserExcel> {
Logger log = LoggerFactory.getLogger(getClass());
private List<UserExcel> userExcelList = new ArrayList<>();
@Override
public void invoke(UserExcel userExcel, AnalysisContext analysisContext) {
log.info("解析到一条数据:{}", JSON.toJSONString(userExcel));
userExcelList.add(userExcel);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("已解析完所有数据!");
userExcelList.clear();
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException convertException = (ExcelDataConvertException) exception;
Integer row = convertException.getRowIndex();
log.error("第{}行数据转换失败，异常信息：{}", row, exception.getMessage());
} else {
log.error("导入其他异常信息：{}", exception.getMessage());
}
}
public List<UserExcel> getUserExcelList() {
return userExcelList;
}
public void setUserExcelList(List<UserExcel> userExcelList) {
this.userExcelList = userExcelList;
}
}读取多个Sheet
[*]获取Sheet的总数，通过循环遍历的方式指定每个Sheet的监听器进行解析
[*]使用构造器的方式传入Sheet对应的下标，在抛出异常时获取SheetNo和对应的行号，方便进行排查
@PostMapping("/uploadFile5")
public void uploadFile5(MultipartFile file) {
try (InputStream in = file.getInputStream()) {
UserExcelListener listener = new UserExcelListener();
EasyExcel.read(in, UserExcel.class, listener)
.sheet(0)
.headRowNumber(1) // 第一行是标题, 从第二行开始读取
.doRead();
} catch (Exception e) {
e.printStackTrace();
}
}public class UserExcelListener2 extends AnalysisEventListener<UserExcel> {
Logger log = LoggerFactory.getLogger(getClass());
private Integer sheetNo;
private List<UserExcel> userExcelList = new ArrayList<>();
public UserExcelListener2(Integer sheetNo) {
this.sheetNo = sheetNo;
}
@Override
public void invoke(UserExcel userExcel, AnalysisContext analysisContext) {
log.info("解析到一条数据:{}", JSON.toJSONString(userExcel));
userExcelList.add(userExcel);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("已解析完所有数据!");
userExcelList.clear();
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException convertException = (ExcelDataConvertException) exception;
Integer row = convertException.getRowIndex();
log.error("sheetNo：{}，第{}行数据转换失败，异常信息：{}", sheetNo, row, exception.getMessage());
} else {
log.error("导入其他异常信息：{}", exception.getMessage());
}
}
public List<UserExcel> getUserExcelList() {
return userExcelList;
}
public void setUserExcelList(List<UserExcel> userExcelList) {
this.userExcelList = userExcelList;
}
}分批读取（线程池操作）
[*]使用构造器的方式传入Sheet对应的下标和自定义线程池，使用这种分批处理的方式，避免内存的消耗，加快文件的解析入库
[*]数据库入库时可以使用MySQL的批量插入语法，同时指定每次插入数据的大小，相较于MyBatisPlus的批量插入方法较快
@PostMapping("/uploadFile6")
public void uploadFile6(MultipartFile file) {
try (InputStream in = file.getInputStream();
ExcelReader build = EasyExcel.read(in).build();) {
List<ReadSheet> readSheets = build.excelExecutor().sheetList();
for (int i = 0, len = readSheets.size(); i < len; i++) {
UserExcelListener2 listener = new UserExcelListener2(i);
ReadSheet sheet = EasyExcel.readSheet(readSheets.get(i).getSheetNo())
.head(UserExcel.class)
.headRowNumber(1)
.registerReadListener(listener)
.build();
build.read(sheet);
}
build.finish();
} catch (Exception e) {
e.printStackTrace();
}
}/**
* UserListener 不能被spring管理，要每次读取excel都要new，
* 然后里面用到spring可以构造方法传进去
*/
public class UserExcelListener3 extends AnalysisEventListener<UserExcel> {
Logger log = LoggerFactory.getLogger(getClass());
private static final Integer BATCH_SIZE = 1000;
private Integer sheetNo;
private Executor executor;
private List<UserExcel> userExcelList = new ArrayList<>();
public UserExcelListener3(Integer sheetNo, Executor executor) {
this.sheetNo = sheetNo;
this.executor = executor;
}
@Override
public void invoke(UserExcel userExcel, AnalysisContext analysisContext) {
log.info("解析到一条数据:{}", JSON.toJSONString(userExcel));
userExcelList.add(userExcel);
if (userExcelList.size() >= BATCH_SIZE) {
List<UserExcel> userExcels = BeanUtil.copyToList(userExcelList, UserExcel.class);
CompletableFuture.runAsync(() -> {
// 业务操作
// saveToDB(userExcels);
}, executor);
userExcelList.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("已解析完所有数据!");
if (!userExcelList.isEmpty()) {
List<UserExcel> userExcels = BeanUtil.copyToList(userExcelList, UserExcel.class);
CompletableFuture.runAsync(() -> {
// 业务操作
// saveToDB(userExcels);
}, executor);
userExcelList.clear();
}
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException convertException = (ExcelDataConvertException) exception;
Integer row = convertException.getRowIndex();
log.error("sheetNo：{}，第{}行数据转换失败，异常信息：{}", sheetNo, row, exception.getMessage());
} else {
log.error("导入其他异常信息：{}", exception.getMessage());
}
}
}事务操作
当使用监听器读取文件数据，使用分批插入数据的方法时，因为监听器不归Spring管理，所以无法使用Spring的事务注解进行事务的相关操作，怎么保证事务？
可以通过构造器的方式传入事务管理器，手动提交和回滚事务
@PostMapping("/uploadFile7")
public void uploadFile77(MultipartFile file) {
try (InputStream in = file.getInputStream();
ExcelReader build = EasyExcel.read(in).build();) {
ThreadPoolExecutor executor = new ThreadPoolExecutor(10, 20, 60L,
TimeUnit.MILLISECONDS, new ArrayBlockingQueue<>(1000), new ThreadPoolExecutor.AbortPolicy());
List<ReadSheet> readSheets = build.excelExecutor().sheetList();
for (int i = 0, len = readSheets.size(); i < len; i++) {
UserExcelListener3 listener = new UserExcelListener3(i, executor);
ReadSheet sheet = EasyExcel.readSheet(readSheets.get(i).getSheetNo())
.head(UserExcel.class)
.headRowNumber(1)
.registerReadListener(listener)
.build();
build.read(sheet);
}
build.finish();
} catch (Exception e) {
e.printStackTrace();
}
}
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]