easyExcel工具类使用

依赖

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

读取结果监听器

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.fastjson.JSON;
import com.ncse.metamanage.bean.db.StandardAggregateInfo;
import com.ncse.metamanage.service.StandardAggregateInfoService;
import lombok.extern.slf4j.Slf4j;

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

/**
 * @author xipeng
 */
@Slf4j
public class StandardAggregateInfoListener implements ReadListener<StandardAggregateInfo> {

    /**
     * 每隔100条入库，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;

    private List<StandardAggregateInfo> cachedDataList = new ArrayList<>(BATCH_COUNT + 1);

    private StandardAggregateInfoService standardAggregateInfoService;

    public StandardAggregateInfoListener(StandardAggregateInfoService standardAggregateInfoService) {
        this.standardAggregateInfoService = standardAggregateInfoService;
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
//        log.error("解析失败，但是继续解析下一行:{}", exception.getMessage());
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(),
                    excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
        }
        throw exception;
    }

    @Override
    public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
        //解析头数据
    }

    @Override
    public void invoke(StandardAggregateInfo data, AnalysisContext context) {
        log.debug("解析到一条数据:{}", JSON.toJSONString(data));
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
        }
    }

    private void saveData() {
        standardAggregateInfoService.saveBatch(cachedDataList);
        cachedDataList.clear();
    }

    @Override
    public void extra(CellExtra extra, AnalysisContext context) {

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
    }

    @Override
    public boolean hasNext(AnalysisContext context) {
        return false;
    }
}

工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.ReadListener;
import com.ncse.entity.enums.CommonEnum;
import com.ncse.metamanage.exception.ProjectException;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * @description: 导出excel工具类
 * @author： XP
 * @date： 2022/3/31 上午10:18
 */
public class ExcelUtil {
    public static void excelWrite(String fileName, List list, Class clazz, HttpServletResponse response) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        try {
            EasyExcel.write(response.getOutputStream())
                    .head(clazz)
                    .sheet("标准集模板")
                    .doWrite(list);
        } catch (IOException e) {
            throw new ProjectException(CommonEnum.EXPORT_ERROR, "导出失败");
        }
    }

    public static void readExcel(InputStream inputStream, Class cl, ReadListener readListener) {
        EasyExcel.read(inputStream, cl, readListener)
                .sheet()
                .headRowNumber(1)
                .doRead();
    }
}