基于EasyExcel的Excel读取

1.引入依赖

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.11</version>
</dependency>

2.读取器代码：

package com.vz.utils.excel;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.cache.MapCache;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import lombok.Builder;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.function.BiConsumer;
import java.util.function.Consumer;/*** @author visy.wang* @description: Excel读取器* @date 2023/8/18 11:03*/
@Slf4j
public class ExcelReader {public static void read(String fileName,BiConsumer<Integer, List<String>> readHandler,Consumer<Integer> finishHandler){EasyExcel.read(fileName, NoModelDataListener.create(readHandler, finishHandler)).readCache(new MapCache()).sheet().doRead();}public static void read(MultipartFile file,BiConsumer<Integer, List<String>> readHandler,Consumer<Integer> finishHandler) throws IOException {EasyExcel.read(file.getInputStream(), NoModelDataListener.create(readHandler, finishHandler)).readCache(new MapCache()).sheet().doRead();}private static class NoModelDataListener  extends AnalysisEventListener<Map<Integer, String>> {private int totalCount = 0;private static final int BATCH_SIZE = 5;private final List<RowData> list = new ArrayList<>(BATCH_SIZE);private final Consumer<Integer> finishHandler;private final BiConsumer<Integer, List<String>> readHandler;public static NoModelDataListener create(BiConsumer<Integer, List<String>> readHandler,Consumer<Integer> finishHandler){return new NoModelDataListener(readHandler, finishHandler);}private NoModelDataListener(BiConsumer<Integer, List<String>> readHandler,Consumer<Integer> finishHandler) {this.readHandler = readHandler;this.finishHandler = finishHandler;}@Overridepublic void invoke(Map<Integer, String> data, AnalysisContext context) {totalCount ++;invoke(data, context.readRowHolder().getRowIndex());}@Overridepublic void invokeHeadMap(Map<Integer, String> head, AnalysisContext context) {invoke(head, context.readRowHolder().getRowIndex());}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {if(!CollectionUtils.isEmpty(list)){batchHandle();}log.info("所有数据已读取完毕！！！");if(Objects.nonNull(finishHandler)){finishHandler.accept(totalCount);}}private void invoke(Map<Integer, String> data, Integer rowIndex){list.add(RowData.builder().rowIndex(rowIndex).data(map2list(data)).build());if(list.size() >= BATCH_SIZE){batchHandle();list.clear();}}private void batchHandle(){log.info("-----------------------------------------------");list.forEach(rowData -> {readHandler.accept(rowData.getRowIndex(), rowData.getData());});}private List<String> map2list(Map<Integer, String> mp){List<String> list = new ArrayList<>(mp.size());for(int i=0; i<mp.size(); i++){list.add(mp.get(i));}return list;}}@Data@Builderprivate static class RowData {private Integer rowIndex;private List<String> data;}
}

3.测试

public static void main(String[] args) {String fileName = "E:\\test\\exl\\test.xlsx";ExcelReader.read(fileName, (rowNo, data) -> {if(rowNo == 0){log.info("第{}行,表头：{}", rowNo+1, JSON.toJSONString(data));}else{log.info("第{}行，数据：{}", rowNo+1, JSON.toJSONString(data));}}, total -> {log.info("一共读取了{}行数据", total);});
}