工作日记之EasyExcel

关于EasyExcel使用流读取 相关问题 ：

1. 相同文件不能读取两次 ，该方法会在本地C盘/local/Temp下创建临时文件

2. 对于使用文件File读取时，则需要关闭流，不然程序只能执行一次，例如下图中未关闭流则会出现文件读取完成，再次执行会报错，该文件仍在占用中

关于EasyExcel相应工具类

package cn.com.casco.ats.listener;

import cn.com.casco.ats.core.FileSystemException;
import cn.com.casco.ats.core.ServiceException;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.nio.charset.Charset;
import java.util.List;

/**
 * @author liuyl
 * @class ExcelAnalysisHelper
 * @des $读取excel工具类
 * @date 2022/11/28 10:17
 */
public class ExcelAnalysisHelper<T> {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelAnalysisHelper.class);

    public List<T> getList(MultipartFile file, Class<T> clazz) {
        return getList(file, clazz, 0, 1);
    }

    public List<T> getList(MultipartFile file, Class<T> clazz, Integer sheetNo, Integer headRowNumber) {
        ReadExcelListener<T> listener = new ReadExcelListener<>(headRowNumber);
        try {
            EasyExcel.read(file.getInputStream(), clazz, listener).extraRead(CellExtraTypeEnum.MERGE).sheet(sheetNo).headRowNumber(headRowNumber).doRead();
        } catch (IOException e) {
            LOGGER.error(e.getMessage());
        }
        List<CellExtra> extraMergeInfoList = listener.getExtraMergeInfoList();
        if (CollectionUtils.isEmpty(extraMergeInfoList)) {
            return listener.getData();
        }
        return explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber);
    }

    public List<T> getList(File file, Class<T> clazz, Integer sheetNo, Integer headRowNumber) {
        ReadExcelListener<T> listener = new ReadExcelListener<>(headRowNumber);
        try {
            EasyExcel.read(file, clazz, listener).extraRead(CellExtraTypeEnum.MERGE).sheet(sheetNo).headRowNumber(headRowNumber).doRead();
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
        }
        List<CellExtra> extraMergeInfoList = listener.getExtraMergeInfoList();
        if (CollectionUtils.isEmpty(extraMergeInfoList)) {
            return listener.getData();
        }
        return explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber);
    }

    public List<T> getList(File file, Class<T> clazz, String sheetName, Integer headRowNumber) {
        ReadExcelListener<T> listener = new ReadExcelListener<>(headRowNumber);
        try {
            EasyExcel.read(file, clazz, listener).extraRead(CellExtraTypeEnum.MERGE).sheet(sheetName).headRowNumber(headRowNumber).doRead();
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
        }
        List<CellExtra> extraMergeInfoList = listener.getExtraMergeInfoList();
        if (CollectionUtils.isEmpty(extraMergeInfoList)) {
            return listener.getData();
        }
        return explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber);
    }

    /**
     * 解决乱码问题 读取cvs文件
     * @param file  要读取的文件
     * @param clazz 转换的类
     * @param sheetNo sheet表编号
     * @param headRowNumber 从第几行开始
     * @param excelType   文件类型
     * @param charset 文件编码
     * @return  对应的list
     */
    public List<T> getList(File file, Class<T> clazz, Integer sheetNo, Integer headRowNumber,ExcelTypeEnum excelType,String charset) {
        ReadExcelListener<T> listener = new ReadExcelListener<>(headRowNumber);
        try {
            EasyExcel.read(file, clazz, listener).extraRead(CellExtraTypeEnum.MERGE).excelType(excelType).charset(Charset.forName(charset)).sheet(sheetNo).headRowNumber(headRowNumber).doRead();
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
        }
        List<CellExtra> extraMergeInfoList = listener.getExtraMergeInfoList();
        if (CollectionUtils.isEmpty(extraMergeInfoList)) {
            return listener.getData();
        }
        return explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber);
    }

    /**
     * 读取文件流 可以支持读取网络文件
     * @param in    文件流
     * @param clazz 对应文件vo类
     * @param sheetName 读取对应的表
     * @param headRowNumber 行
     * @return  返回对应list
     */
    public List<T> getList(InputStream in, Class<T> clazz, String sheetName, Integer headRowNumber) {
        ReadExcelListener<T> listener = new ReadExcelListener<>(headRowNumber);
        try {
            EasyExcel.read(in, clazz, listener).extraRead(CellExtraTypeEnum.MERGE).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).headRowNumber(headRowNumber).doRead();
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
        }
        List<CellExtra> extraMergeInfoList = listener.getExtraMergeInfoList();
        if (CollectionUtils.isEmpty(extraMergeInfoList)) {
            return listener.getData();
        }
        return explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber);
    }

    /**
     * 处理合并单元格
     *
     * @param data               解析数据
     * @param extraMergeInfoList 合并单元格信息
     * @param headRowNumber      起始行
     * @return 填充好的解析数据
     */
    private List<T> explainMergeData(List<T> data, List<CellExtra> extraMergeInfoList, Integer headRowNumber) {
        // 循环所有合并单元格信息
        /*extraMergeInfoList.forEach(cellExtra -> {

        });*/
        for (CellExtra cellExtra : extraMergeInfoList) {
            int firstRowIndex = cellExtra.getFirstRowIndex() - headRowNumber;
            int lastRowIndex = cellExtra.getLastRowIndex() - headRowNumber;
            // firstColumnIndex 列索引值  firstRowIndex 行索引值 索引值皆从0开始 0为第一行
            int firstColumnIndex = cellExtra.getFirstColumnIndex();
            int lastColumnIndex = cellExtra.getLastColumnIndex();
            // 获取初始值
            Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, data);
            // 设置值
            for (int i = firstRowIndex; i <= lastRowIndex; i++) {
                for (int j = firstColumnIndex; j <= lastColumnIndex; j++) {
                    if (i >= data.size()) {
                        break;
                    }
                    setInitValueToList(initValue, i, j, data);
                }
            }
        }
        return data;
    }

    /**
     * 设置合并单元格的值
     *
     * @param filedValue  值
     * @param rowIndex    行
     * @param columnIndex 列
     * @param data        解析数据
     */
    public void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> data) {
        // 这里需要对size 与index 做对比
        T object = data.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) {
                        throw new FileSystemException("解析数据时发生异常!");
                    }
                }
            }
        }
    }

    /**
     * 获取合并单元格的初始值
     * rowIndex对应list的索引
     * columnIndex对应实体内的字段
     *
     * @param firstRowIndex    起始行
     * @param firstColumnIndex 起始列
     * @param data             列数据
     * @return 初始值
     */
    private Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<T> data) {
        Object filedValue = null;
        if (data.size() > firstRowIndex) {
            T object = data.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) {
                            throw new FileSystemException("解析数据时发生异常!");
                        }
                    }
                }
            }
        }
        return filedValue;
    }
    
    
    /**
     * 根据传入的表明查询相应的表
     *
     * @param file      文件
     * @param sheetName 表名
     * @param clazz     相应的vo类
     * @return 返回读取的内容列表
     */
    public List<T> readExcel(File file, String sheetName, Class<T> clazz) {
        
        ExcelReaderBuilder excelReaderBuilder = EasyExcel.read(file);
        ExcelReader excelReader = excelReaderBuilder.build();
        List<ReadSheet> readSheets = excelReader.excelExecutor().sheetList();
        
        if (CollectionUtils.isEmpty(readSheets)) {
            throw new ServiceException("表中无数据，请检查");
        }
        for (ReadSheet sheet : readSheets) {
            if (sheet.getSheetName().trim().equals(sheetName)) {
                return getList(file, clazz, sheetName, 2);
            }
        }
        return null;
    }
}

-- 模板读取类

package cn.com.casco.ats.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellExtra;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;

/**
 * @author liuyl
 * @class ReadExcelListener
 * @des 模板读取类
 * @date 2022/11/28 13:02
 */
@Slf4j
public class ReadExcelListener<T> extends AnalysisEventListener<T> {
    /**
     * 解析的数据
     */
    List<T> list = new ArrayList<>();

    /**
     * 正文起始行
     */
    private Integer headRowNumber;
    /**
     * 合并单元格
     */
    private List<CellExtra> extraMergeInfoList = new ArrayList<>();

    public ReadExcelListener(Integer headRowNumber) {
        this.headRowNumber = headRowNumber;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context context
     */
    @Override
    public void invoke(T data, AnalysisContext context) {
        list.add(data);
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("所有数据解析完成！");
    }

    public List<T> getData() {
        return list;
    }

    @Override
    public void extra(CellExtra extra, AnalysisContext context) {
       // log.info("读取到了一条额外信息:{}", JSON.toJSONString(extra));
        switch (extra.getType()) {
            case COMMENT: {
                log.info("额外信息是批注,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(), extra.getColumnIndex(),
                        extra.getText());
                break;
            }
            case HYPERLINK: {
                if ("Sheet1!A1".equals(extra.getText())) {
                    log.info("额外信息是超链接,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(),
                            extra.getColumnIndex(), extra.getText());
                } else if ("Sheet2!A1".equals(extra.getText())) {
                    log.info(
                            "额外信息是超链接,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{},"
                                    + "内容是:{}",
                            extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
                            extra.getLastColumnIndex(), extra.getText());
                } else {
                    log.error("断言失败");
                }
                break;
            }
            case MERGE: {
               /* log.info(
                        "额外信息是合并单元格,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{}",
                        extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
                        extra.getLastColumnIndex());*/
                if (extra.getRowIndex() >= headRowNumber) {
                    extraMergeInfoList.add(extra);
                }
                break;
            }
            default: {
            }
        }
    }

    public List<CellExtra> getExtraMergeInfoList() {
        return extraMergeInfoList;
    }
}

使用样例:

    /**
     *  加载版本信息输入中的联锁tle/bool数据版本信息
     * @param projectId    项目主键ID
     * @return    加载版本信息输入中的联锁tle/bool数据版本信息
     */
    @Override
    public Map<String, Object> getTleBoolDataVersionInfo(String projectId) {
        Map<String, Object> map = new HashMap<>(2);
        DesignFile designFile = designFileDao.selectDesignFileByProjectIdAndFileType(projectId, "ats_sub_system_data_ready_desc");
        if (ObjectUtil.isNull(designFile)) {
            throw new ServiceException("未查询到系统数据准备文档，请上传后重试！");
        }

        InputStream inputStream = MinIoUtil.getObject(FileSystemConstans.BUCKET_PROJECTS, designFile.getFileName());
        if (ObjectUtil.isNotEmpty(inputStream)) {
            List<StationInfoVo> tList;
            try {
                ExcelAnalysisHelper excelAnalysisHelper = new ExcelAnalysisHelper();
                tList = excelAnalysisHelper.getList(new BufferedInputStream(inputStream), StationInfoVo.class,"1.4 站台信息定义", 2);
            } catch (Exception e) {
                log.error("系统参数定义表VO类异常,异常原因为: {}", e.getMessage());
                throw new ServiceException("文件生成异常/系统参数定义表获取异常！");
            }
            map.put("tle_bool_data_version", tList.stream().map(stationInfoVo -> {
                return new TleBoolDataVersionInfoVo(stationInfoVo.getConcentrateStationNum(), stationInfoVo.getStationName());
            }).distinct().filter(tleBoolDataVersionInfoVo -> {
                try {
                    Integer.parseInt(tleBoolDataVersionInfoVo.getCenterStationNum());
                    return true;
                } catch (Exception e) {
                    return false;
                }
            }).collect(Collectors.toList()));
        } else {
            throw new ServiceException("网络原因导致数据准备文件下载失败");
        }
        closeStream(null,inputStream);
        return map;
    }