EasyExcel——读Excel官方文档链接
maven依赖：
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version></dependency>
监听器完整代码：
/*** 多sheet、有合并单元格的excel导入*/public class ImportExcelListener<T> extends AnalysisEventListener<T> {private static final Logger LOGGER = LoggerFactory.getLogger(ImportExcelListener.class);/*** 最终返回的解析数据list*/private final List<T> data = new ArrayList<>();/*** 解析数据* key是sheetName，value是相应sheet的解析数据*/private final Map<String, List<T>> dataMap = new HashMap<>();/*** 合并单元格* key键是sheetName，value是相应sheet的合并单元格数据*/private final Map<String, List<CellExtra>> mergeMap = new HashMap<>();/*** 正文起始行*/private final Integer headRowNumber;public ImportExcelListener(Integer headRowNumber) {this.headRowNumber = headRowNumber;}@Overridepublic void invoke(T data, AnalysisContext context) {String sheetName = context.readSheetHolder().getSheetName();puteIfAbsent(sheetName, k -> new ArrayList<>());dataMap.get(sheetName).add(data);}@Overridepublic void extra(CellExtra extra, AnalysisContext context) {String sheetName = context.readSheetHolder().getSheetName();switch (extra.getType()) {case MERGE: // 额外信息是合并单元格if (extra.getRowIndex() >= headRowNumber) {puteIfAbsent(sheetName, k -> new ArrayList<>());mergeMap.get(sheetName).add(extra);}break;case COMMENT:case HYPERLINK:default:}}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {LOGGER.info("Excel解析完成");}/*** 获取解析数据*/public List<T> getData(InputStream in, Class<T> clazz) {try {EasyExcel.read(in, clazz, this).extraRead(CellExtraTypeEnum.MERGE).headRowNumber(headRowNumber).doReadAll();} catch (Exception e) {LOGGER.error("Excel读取异常：" + e);}convertDataMapToData();return data;}/*** 将具有多个sheet数据的dataMap转变成一个data*/private void convertDataMapToData() {Iterator<Map.Entry<String, List<T>>> iterator = dataMap.entrySet().iterator();while (iterator.hasNext()) {Map.Entry<String, List<T>> next = iterator.next();String sheetName = next.getKey();List<T> list = next.getValue();List<CellExtra> mergeList = mergeMap.get(sheetName);if (CollectionUtils.isNotEmpty(mergeList)) {list = explainMergeData(list, mergeList);}data.addAll(list);}}/*** 处理有合并单元格的数据* @param list 解析数据* @param mergeList 合并单元格信息* @return 填充好的解析数据*/private List<T> explainMergeData(List<T> list, List<CellExtra> mergeList) {// 循环所有合并单元格信息mergeList.forEach(item -> {Integer firstRowIndex = item.getFirstRowIndex() - headRowNumber;Integer lastRowIndex = item.getLastRowIndex() - headRowNumber;Integer firstColumnIndex = item.getFirstColumnIndex();Integer lastColumnIndex = item.getLastColumnIndex();// 获取初始值Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, list);// 设置值for (int i = firstRowIndex; i <= lastRowIndex; i++) {for (int j = firstColumnIndex; j<= lastColumnIndex; j++) {setInitValueToList(initValue, i, j, list);}}});return list;}/*** 获取合并单元格的初始值* rowIndex对应list的索引* columnIndex对应实体内的字段* @param firstRowIndex 起始行* @param firstColumnIndex 起始列* @param list 列数据* @return 初始值*/private Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<T> list) {Object filedValue = null;T object = list.get(firstRowIndex);for (Field field : object.getClass().getDeclaredFields()) {field.setAccessible(true);ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);if (annotation != null) {if (annotation.index() == firstColumnIndex) {try {filedValue = field.get(object);break;} catch (IllegalAccessException e) {LOGGER.error("获取合并单元格的初始值异常："+e.getMessage());}}}}return filedValue;}/*** 设置合并单元格的值* @param filedValue 值* @param rowIndex 行* @param columnIndex 列* @param list 解析数据*/public void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> list) {T object = list.get(rowIndex);for (Field field : object.getClass().getDeclaredFields()) {field.setAccessible(true);ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);if (annotation != null) {if (annotation.index() == columnIndex) {try {field.set(object, filedValue);break;} catch (IllegalAccessException e) {LOGGER.error("设置合并单元格的值异常："+e.getMessage());}}}}}}
导入对象：
@Datapublic class WbsExcelField {@ExcelProperty(value = "单位工程", index = 0)private String unitWork;@ExcelProperty(value = "单位工程编码", index = 1)private String unitWorkCode;@ExcelProperty(value = "子单位工程", index = 2)private String subUnitWork;@ExcelProperty(value = "子单位工程编码", index = 3)private String subUnitWorkCode;@ExcelProperty(value = "分部工程", index = 4)private String divisionalWork;@ExcelProperty(value = "分部工程编码", index = 5)private String divisionalWorkCode;@ExcelProperty(value = "子分部工程", index = 6)private String subDivisionalWork;@ExcelProperty(value = "子分部工程编码", index = 7)private String subDivisionalWorkCode;@ExcelProperty(value = "分项工程", index = 8)private String itemWork;@ExcelProperty(value = "分项工程编码", index = 9)private String itemWorkCode;@ExcelProperty(value = "子分项工程", index = 10)private String subItemWork;@ExcelProperty(value = "子分项工程编码", index = 11)private String subItemWorkCode;}
导入文档截图（桥梁、隧道、道路三个sheet表头一致）：
导入测试类：
public class ImportTest {public static void main(String[] args) throws Exception {String path = "......"; // 文件目录String fileName = path + "导入模板TEST.xlsx";File file = new File(fileName);ImportExcelListener<WbsExcelField> listener = new ImportExcelListener<>(2);List<WbsExcelField> data = listener.getData(new FileInputStream(file), WbsExcelField.class);for (WbsExcelField wbsExcelField : data) {System.out.println(wbsExcelField.toString());}}}
输出结果：
对以上代码的注意点说明：
1.因为多个sheet的内容格式一致，所有有一个convertDataMapToData方法进行数据转换。
2.listener中没有对数据进行分批处理，而是直接将数据全部解析到data中。
3.EasyExcel......doReadAll()方法会读取全部sheet，doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。
