EasyExcel介绍

介绍

easyexcel是阿里读写Excel工具类。

pom

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

Demo，读取多个Sheet，生成1个Excel

@Data
@Slf4j
public class SessionExtract {
    /**
     * 读文件1
     */
    private static String filePath = "/1.xlsx";
    /**
     * 写文件
     */
    private static String filePath2 = "/2.xlsx";
    /**
     * 读文件内容1
     */
    private static Map<String, List<String>> queryMap = new ConcurrentHashMap<>();
    /**
     * 读文件内容2
     */
    private static Map<String, List<SessionData>> sessionMap = new ConcurrentHashMap<>();

    private static List<SessionData> resultList = new ArrayList<>();

    @Data
    @ToString
    public static class QueryData {
        /**
         * 指定读取的excel索引
         */
        @ExcelProperty(index = 0)
        String query;
    }

    @Data
    @ToString
    public static class SessionData {
        @ExcelProperty(index = 0)
        private String uid;
        @ExcelProperty(index = 1)
        private String sessionId;
        @ExcelProperty(index = 2)
        private String query;
        @ExcelProperty(index = 3)
        private String requestTime;

    }

    /**
     * 监听读取文件
     */
    public static class QueryDataListener extends AnalysisEventListener<QueryData> {

        /**
         * 逐行读
         *
         * @param queryData
         * @param analysisContext
         */
        @Override
        public void invoke(QueryData queryData, AnalysisContext analysisContext) {
            queryMap.put(queryData.getQuery(), new ArrayList<>());
        }

        /**
         * 读完成
         *
         * @param analysisContext
         */
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            log.info("queryMap size:{}", queryMap.size());
        }
    }

    public static class SessionDataListener extends AnalysisEventListener<SessionData> {

        @Override
        public void invoke(SessionData sessionData, AnalysisContext analysisContext) {
            if (sessionData == null || sessionData.getSessionId() == null || sessionData.getQuery() == null) {
                return;
            }
            List<SessionData> sessionDataList = sessionMap.getOrDefault(sessionData.getSessionId(), new ArrayList<>());
            sessionDataList.add(sessionData);
            sessionMap.put(sessionData.getSessionId(), sessionDataList);

            if (queryMap.containsKey(sessionData.getQuery())) {
                queryMap.get(sessionData.getQuery()).add(sessionData.getSessionId());
            }
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            log.info("sessionMap size:{}", sessionMap.size());
        }
    }

    public static void main(String[] args) {

        ExcelReader excelReader = EasyExcel.read(filePath).build();
        //sheet1
        ReadSheet readSheet1 =
                EasyExcel.readSheet(0).head(QueryData.class).registerReadListener(new QueryDataListener()).build();
        //sheet2
        ReadSheet readSheet2 =
                EasyExcel.readSheet(3).head(SessionData.class).
                        registerReadListener(new SessionDataListener()).build();
        //读取多个sheet
        excelReader.read(readSheet2, readSheet1);

        // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
        excelReader.finish();

        //生成写数据
        queryMap.forEach((k, v) -> v.forEach(e -> resultList.addAll(sessionMap.get(e))));

        log.info("resultList size:{}", resultList.size());

        //写文件
        EasyExcel.write(filePath2, SessionData.class).sheet(0).doWrite(resultList);
    }
}