基于EasyExcel的Excel读取-Toy模板网

这篇具有很好参考价值的文章主要介绍了基于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;
        }

        @Override
        public void invoke(Map<Integer, String> data, AnalysisContext context) {
            totalCount ++;
            invoke(data, context.readRowHolder().getRowIndex());
        }

        @Override
        public void invokeHeadMap(Map<Integer, String> head, AnalysisContext context) {
            invoke(head, context.readRowHolder().getRowIndex());
        }

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