EasyExcel——多sheet、有合并单元格的excel导入

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;
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        String sheetName = context.readSheetHolder().getSheetName();
        dataMap.computeIfAbsent(sheetName, k -> new ArrayList<>());
        dataMap.get(sheetName).add(data);
    }

    @Override
    public void extra(CellExtra extra, AnalysisContext context) {
        String sheetName = context.readSheetHolder().getSheetName();
        switch (extra.getType()) {
            case MERGE: // 额外信息是合并单元格
                if (extra.getRowIndex() >= headRowNumber) {
                    mergeMap.computeIfAbsent(sheetName, k -> new ArrayList<>());
                    mergeMap.get(sheetName).add(extra);
                }
                break;
            case COMMENT:
            case HYPERLINK:
            default:
        }
    }

    @Override
    public 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());
                    }
                }
            }
        }
    }
}

导入对象：

@Data
public 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读取完毕后调用一次。