解决EasyExcel工具读取Excel空数据行的问题

EasyExcel是Alibaba开源的一个Java处理Excel的工具。

官网解读：快速、简洁、解决大文件内存溢出的java处理Excel工具

快速

快速的读取excel中的数据。

简洁

映射excel和实体类，让代码变的更加简洁。

大文件

在读写大文件的时候使用磁盘做缓存，更加的节约内存。

官网地址：https://easyexcel.opensource.alibaba.com/

感兴趣可自己琢磨，该工具简单易上手，且性能相对比较高。

首先解释为什么会产生空数据行：简单解释就是你在Excel中设置了单元的样式，却没有给单元格设值。因此，该工具在读取数据时便没有判断这一步，直接读取到整行数据均为null。

理解了核心问题后，要解决这个问题，实现思路也不难。

莫非就是把这种空数据行过滤即可。

本文是基于批处理监听器实现数据读取的，自定义集成该监听器（com.alibaba.excel.read.listener.PageReadListener），实现自己的逻辑即可解决问题。

package xin.cosmos.basic.easyexcel.framework;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.util.ListUtils;
import lombok.extern.slf4j.Slf4j;
import xin.cosmos.basic.util.ObjectsUtil;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.function.Consumer;
import java.util.stream.Collectors;


@Slf4j
public class BatchPageReadListener extends PageReadListener {

    
    private List cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    
    private final Consumer> consumer;

    public BatchPageReadListener(Consumer> consumer) {
        super(consumer);
        this.consumer = consumer;
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        // 如果一行Excel数据均为空值，则不装载该行数据
        if (isLineNullValue(data)) {
            return;
        }
        cachedDataList.add(data);
        if (cachedDataList.size() >= BATCH_COUNT) {
            consumer.accept(cachedDataList);
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (ObjectsUtil.isNull(cachedDataList)) {
            return;
        }
        consumer.accept(cachedDataList);
    }

    
    private boolean isLineNullValue(T data) {
        if (data instanceof String) {
            return ObjectsUtil.isNull(data);
        }
        try {
            List fields = Arrays.stream(data.getClass().getDeclaredFields())
                    .filter(f -> f.isAnnotationPresent(ExcelProperty.class))
                    .collect(Collectors.toList());
            List lineNullList = new ArrayList<>(fields.size());
            for (Field field : fields) {
                field.setAccessible(true);
                Object value = field.get(data);
                if (ObjectsUtil.isNull(value)) {
                    lineNullList.add(Boolean.TRUE);
                } else {
                    lineNullList.add(Boolean.FALSE);
                }
            }
            return lineNullList.stream().allMatch(Boolean.TRUE::equals);
        } catch (Exception e) {
            log.error("读取数据行[{}]解析失败: {}", data, e.getMessage());
        }
        return true;
    }

}

package xin.cosmos.basic.easyexcel.helper;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.fastjson.JSON;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.multipart.MultipartFile;
import xin.cosmos.basic.define.ResultVO;
import xin.cosmos.basic.easyexcel.framework.BatchPageReadListener;
import xin.cosmos.basic.easyexcel.template.HeadVO;
import xin.cosmos.basic.exception.PlatformException;
import xin.cosmos.basic.util.BeanMapUtil;
import xin.cosmos.basic.util.ObjectsUtil;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;


@Slf4j
public class EasyExcelHelper {

    
    public static  List doReadExcelData(InputStream stream, Class entityClass) {
        List data = new LinkedList<>();
        EasyExcelFactory.read(stream, entityClass, new PageReadListener(data::addAll)).sheet().doRead();
        return data;
    }

    
    public static  List doReadExcelData(InputStream stream, Class entityClass, Comparator comparator) {
        List data = new LinkedList<>();
        EasyExcelFactory.read(stream, entityClass, new BatchPageReadListener(list -> {
            if (comparator != null) {
                list.sort(comparator);
            }
            data.addAll(list);
        })).sheet().doRead();
        return data;
    }

    
    @SneakyThrows
    public static  List doReadExcelData(MultipartFile file, Class entityClass) {
        return doReadExcelData(file.getInputStream(), entityClass);
    }

    
    @SneakyThrows
    public static  List doReadExcelData(File file, Class entityClass) {
        List data = new LinkedList<>();
        EasyExcelFactory.read(file, entityClass, new BatchPageReadListener(data::addAll)).sheet().doRead();
        return data;
    }

    
    @SneakyThrows
    public static  void downloadExcel(String pathName, List data, Class entityClazz) {
        try {
            // 构建Excel表头及数据体
            ExcelWriterSheetBuilder builder = EasyExcel.write(pathName)
                    .autoCloseStream(true)
                    .sheet("sheet1");
            doWriteWithDynamicColumns(builder, entityClazz, data);
        } catch (Exception e) {
            log.error("写文件错误：{}", e.getMessage());
            throw new PlatformException("Excel下载数据错误");
        }
    }


    
    public static  void downloadExcelToResponse(HttpServletResponse response, String excelFileName, List data, Class entityClazz) {
        if (ObjectsUtil.isNull(data)) {
            log.error("写文件错误：{}", "暂无可下载的数据");
            writeErrMsg(response, "暂无可下载的数据");
            return;
        }
        try {
            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            if (excelFileName.endsWith(".xlsx") || excelFileName.endsWith(".xls") ||
                    excelFileName.endsWith(".XLSX") || excelFileName.endsWith(".XLS")) {
                excelFileName = excelFileName.substring(0, excelFileName.lastIndexOf("."));
            }
            // 这里URLEncoder.encode可以防止中文乱码 当然和easy excel没有关系
            String urlFileName = URLEncoder.encode(excelFileName, "UTF-8").replaceAll("\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + urlFileName + ".xlsx");
            response.setHeader("excel-file-name", urlFileName + ".xlsx");

            // 构建Excel表头及数据体
            ExcelWriterSheetBuilder builder = EasyExcel.write(response.getOutputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .autoCloseStream(true)
                    .sheet("sheet1");
            doWriteWithDynamicColumns(builder, entityClazz, data);
        } catch (Exception e) {
            log.error("写文件错误：{}", e.getMessage());
            writeErrMsg(response, e.getMessage());
        }
    }


    
    public static  void doWriteWithDynamicColumns(ExcelWriterSheetBuilder builder, Class entityClazz, List data) {
        List customizeHeads = new ArrayList<>();
        Field[] fieldArray = entityClazz.getDeclaredFields();
        // 获取类的注解
        for (Field field : fieldArray) {
            // 忽略导出属性
            if (field.isAnnotationPresent(ExcelIgnore.class)) {
                continue;
            }
            if (field.isAnnotationPresent(ExcelProperty.class)) {
                ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
                List head = Arrays.asList(excelProperty.value());
                int index = excelProperty.index();
                int order = excelProperty.order();
                HeadVO headVO = HeadVO.builder().headTitle(head).index(index).order(order).field(field.getName()).build();
                customizeHeads.add(headVO);
            }
        }

        // 表头排序
        Collections.sort(customizeHeads);

        // 处理表头
        List> heads = new ArrayList<>();
        List fields = new ArrayList<>();
        for (int i = 0; i <= customizeHeads.size() - 1; i++) {
            heads.add(customizeHeads.get(i).getHeadTitle());
            fields.add(customizeHeads.get(i).getField());
        }

        // 处理数据
        List> objs = new ArrayList<>();
        List> maps = BeanMapUtil.beansToMaps(data);
        maps.forEach(map -> {
            List