使用EasyExcel读取Excel文件

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
github地址:github.com/alibaba/eas…

读取的表格样式

npm install xe-utils vxe-table@next

用到了spring的类package org.springframework.web.multipart类 MultipartFile是SpringMVC提供简化上传操作的工具类。

在不使用框架之前，都是使用原生的HttpServletRequest来接收上传的数据，文件是以二进制流传递到后端的，然后需要我们自己转换为File类。使用了MultipartFile工具类之后，我们对文件上传的操作就简便许多了。

这个类用来接收Excel文件

@Slf4j @Service public class ExcelService {

public void read(MultipartFile excel) throws Exception {
    ExcelReadListener readListener = new ExcelReadListener();
    ExcelReader excelReader = null;
    List<ReadSheet> readSheetList = null;
    try {
        ExcelReaderBuilder readerBuilder = EasyExcel.read(excel.getInputStream(), readListener);
        excelReader = readerBuilder.build();
        readSheetList = excelReader.excelExecutor().sheetList();
    } catch (IOException e) {
        log.warn("excel文件错误！");
    }
    if (GeneralUtils.isEmpty(readSheetList)) {
        log.warn("请选择一个有效的excel文件！");
        throw new RuntimeException("excel file is empty！");
    }
    for (ReadSheet readSheet : readSheetList) {
        excelReader.read(readSheet);
        Map<Integer, Object> headMap = readListener.getHeadMap();
        List<Map<Integer, Object>> contentList = readListener.getContentList();
        for (Map<Integer, Object> contentMap : contentList) {
            for (Map.Entry<Integer, Object> entry : headMap.entrySet()) {
                Integer index = entry.getKey();
                Object header = entry.getValue();
                Object content = contentMap.get(index);
                log.info("---->>> index: {}, header: {}, content: {}",index, header, content);
            }
        }
        log.info("<{}> to be handling data！",readSheet.getSheetName());
    }
}

@Slf4j
public class ExcelReadListener extends AnalysisEventListener<Map<Integer, Object>> {
    private Map<Integer, Object> headMap = new HashMap<>();
    private List<Map<Integer, Object>> contentList = new ArrayList<>();
    private String dept;

    @Override
    public void invoke(Map<Integer, Object> contentMap, AnalysisContext context) {
        ReadRowHolder readRowHolder = context.readRowHolder();
        Integer rowIndex = readRowHolder.getRowIndex();

        if (rowIndex == 1) {
            this.headMap = contentMap;
        } else {

            for (Map.Entry<Integer, Object> entry : contentMap.entrySet()) {
                Integer key = entry.getKey();
                Object value = entry.getValue();

                if (key == 0) {
                    if (value == null) {
                        contentMap.replace(key,dept);
                    } else {
                        dept = String.valueOf(value);
                    }
                }
            }
        }
        log.info("<{}> content: {} ", rowIndex, JsonUtils.parseJson(contentMap));
        this.contentList.add(contentMap);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        ReadSheetHolder readSheetHolder = context.readSheetHolder();
        String sheetName = readSheetHolder.getSheetName();
        log.info("<{}> read successfully! total: {}", sheetName, this.contentList.size());

    }

    public Map<Integer, Object> getHeadMap() {
        return headMap;
    }

    public List<Map<Integer, Object>> getContentList() {
        return contentList;
    }
}

读取结果 部门名称原来只能跟着这一部门的第一个人 经过处理，将接下来部门字段为空的人把当前部门第一个人的部门名称保存起来，赋值给这个部门其余的人