使用EasyExcel的AnalysisEventListener读取EXCEL导入数据

1、实体对象VO

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class PrizeLogImportExcelVO {
    @ExcelProperty("订单编号")
    private String prizeSn;
    @ExcelProperty("快递公司")
    private String expressName;
    @ExcelProperty("快递单号")
    private String expressSn;
    @ExcelProperty("快递编码")
    private String expressCode;
    @ExcelProperty("快递ID")
    private Integer expressId;
}

 2、接口类

public interface IJmPrizeLogService {
    /**
     * 导入
     *
     * @param list
     */
    void importExcel(List<PrizeLogImportExcelVO> list);
}

3、接口实现类

@Service
public class JmPrizeLogServiceImpl extends ServiceImpl<JmPrizeLogMapper, JmPrizeLog> implements IJmPrizeLogService {
    public static List<String> errorPrizeSns = new ArrayList<>();
    @Resource
    private JmPrizeLogMapper prizeLogMapper;
    @Resource
    private JmExpressMapper expressMapper;
    private static final ExecutorService POOL = Executors.newCachedThreadPool();

    @Override
    public void importExcel(List<PrizeLogImportExcelVO> list) {
        errorPrizeSns.clear();
        if (CollectionUtil.isEmpty(list)) {
            errorPrizeSns.add("excel中无数据，无法导入发货！");
            return;
        }
        List<PrizeLogImportExcelVO> result = new ArrayList<>();
        for (PrizeLogImportExcelVO importExcelVO : list) {
            String prizeSn = importExcelVO.getPrizeSn();
            if (StringUtils.isEmpty(prizeSn)) {
                continue;
            }
            if (StringUtils.isBlank(importExcelVO.getExpressName())) {
                errorPrizeSns.add(prizeSn + " 快递公司不能为空！");
                continue;
            }
            if (StringUtils.isBlank(importExcelVO.getExpressSn())) {
                errorPrizeSns.add(prizeSn + " 快递单号不能为空！");
                continue;
            }
            QueryWrapper<JmExpress> expressQueryWrapper = new QueryWrapper<>();
            expressQueryWrapper.eq("name", importExcelVO.getExpressName());
            JmExpress jmExpress = expressMapper.selectOne(expressQueryWrapper);
            if (!ObjectUtil.isNotEmpty(jmExpress)) {
                errorPrizeSns.add(prizeSn + " 快递公司有误！");
                continue;
            } else {
                importExcelVO.setExpressCode(jmExpress.getCode());
                importExcelVO.setExpressId(jmExpress.getId().intValue());
            }
            result.add(importExcelVO);
        }
        if (CollectionUtil.isEmpty(errorPrizeSns)) {
            POOL.execute(() -> {
                prizeLogMapper.batchExpressInfo(result);
            });
        }
    }
}

4、 Mapper接口

public interface JmPrizeLogMapper extends BaseMapper<JmPrizeLog> {
    /**
     * 批量导入快递信息
     *
     * @param list
     */
    @Update("<script><foreach collection='list' item='o' index='index' separator=';' >UPDATE `jm_prize_log` SET express_name =#{o.expressName}, express_sn = #{o.expressSn}, express_code = #{o.expressCode}, express_id = #{o.expressId} WHERE prize_sn = #{o.prizeSn}</foreach></script>")
    void batchExpressInfo(@Param("list") List<PrizeLogImportExcelVO> list);
}

5、定义一个ExcelHander工具类继承AnalysisEventListener 最重要的是重写invoke方法，去执行读EXCEL逻辑。

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.jumi.microservice.sale.entity.vo.PrizeLogImportExcelVO;
import com.jumi.microservice.sale.service.IJmPrizeLogService;

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


public class PrizeLogImportListener extends AnalysisEventListener<PrizeLogImportExcelVO> {
    private IJmPrizeLogService prizeLogService;
    private List<PrizeLogImportExcelVO> list = new ArrayList<>();

    @Override
    public void invoke(PrizeLogImportExcelVO data, AnalysisContext context) {
        list.add(data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        prizeLogService.importExcel(list);
    }

    public PrizeLogImportListener(IJmPrizeLogService prizeLogService) {
        this.prizeLogService = prizeLogService;
    }

    public List<PrizeLogImportExcelVO> getList() {
        return list;
    }
}

6、调用逻辑

    @Resource
    private IJmPrizeLogService prizeLogService;
    
//    @PostMapping("/import/excel")
//    @ApiOperation("导入excel")
//    public ResponseResult<List<String>> importExcel(@RequestParam(value = "multipartFile") MultipartFile request) throws IOException {
//        PrizeLogImportListener listener = new PrizeLogImportListener(prizeLogService);
//        EasyExcel.read(request.getInputStream(), PrizeLogImportExcelVO.class, listener).sheet().doRead();
//        return ResponseResult.success(JmPrizeLogServiceImpl.errorPrizeSns);
//    }

    @PostMapping("/import/excel")
    @ApiOperation("导入excel")
    public ResponseResult<List<String>> importExcel(@RequestParam(value = "multipartFile") MultipartFile file) throws IOException {
        if (file == null || file.isEmpty() || ObjectUtils.isEmpty(file.getOriginalFilename())) {
            throw new BaseException(500, "文件不能为空");
        }
        String fileName = file.getOriginalFilename();
        if (!(fileName.endsWith(".xlsx") || fileName.endsWith(".xls") || fileName.endsWith(".csv"))) {
            throw new BaseException(500, "文件类型错误，只支持：xlsx、xls、csv");
        }
        try {
            InputStream inputStream = file.getInputStream();
            if (fileName.endsWith(".csv")) {
                inputStream = CsvToXlsxUtil.csvStream2xlsxStream(file.getInputStream(), fileName);
            }
            PrizeLogImportListener listener = new PrizeLogImportListener(prizeLogService);
            EasyExcel.read(inputStream, PrizeLogImportExcelVO.class, listener).sheet().doRead();
        } catch (ExcelAnalysisException | ExcelCommonException | IOException e) {
            System.out.println(e);
            throw new BaseException(500, "文件异常，请检查确认");
        }
        return ResponseResult.success(JmPrizeLogServiceImpl.errorPrizeSns);
    }

7、CsvToXlsxUtil工具类

import cn.hutool.core.util.ObjectUtil;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jumpmind.symmetric.csv.CsvReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
import java.io.*;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
 
/**
 * Csv 转 Xlsx 工具类
 */
public class CsvToXlsxUtil {
    private static final Logger log = LoggerFactory.getLogger(CsvToXlsxUtil.class);
    /**
     * CSV常用分隔符，如需动态扩展设置成配置项
     */
    private static final char[] DELIMITERS = {
            ',',
            ';',
            '\001',
            ' ',
            '\t',
            '|',
            '#',
            '&'
    };
 
    /**
     * 读取CSV文件并写入到XLSX文件中，默认编码
     *
     * @param csvFileAddress 文件地址
     */
    public static String csvToXlsx(String csvFileAddress) {
        return csvToXlsx(csvFileAddress, "UTF-8");
    }
 
    /**
     * @param inputStream 输入流
     */
    public static InputStream csv2xlsx(InputStream inputStream, String fileName) {
        return csvStream2xlsxStream(inputStream, fileName);
    }
 
    /**
     * 读取CSV文件并写入到XLSX文件中，指定CSV文件编码
     *
     * @param csvFileAddress 文件地址
     * @param charset        编码
     */
    public static String csvToXlsx(String csvFileAddress, String charset) {
        String xlsxFileAddress = "";
        FileOutputStream fileOutputStream = null;
        try {
            char delimiter = getDelimiter(csvFileAddress);
            //xlsx file address
            xlsxFileAddress = csvFileAddress.replace("csv", "xlsx");
            XSSFWorkbook workBook = new XSSFWorkbook();
            XSSFSheet sheet = workBook.createSheet(getSheetName(csvFileAddress));
            int rowNum = -1;
            CsvReader csvReader = new CsvReader(csvFileAddress, delimiter, Charset.forName(charset));
            while (csvReader.readRecord()) {
                rowNum++;
                XSSFRow currentRow = sheet.createRow(rowNum);
                for (int i = 0; i < csvReader.getColumnCount(); i++) {
                    currentRow.createCell(i).setCellValue(csvReader.get(i));
                }
            }
            fileOutputStream = new FileOutputStream(xlsxFileAddress);
            workBook.write(fileOutputStream);
            return getFileName(xlsxFileAddress);
        } catch (Exception e) {
            log.error("CsvToXlsxUtil exception :", e);
        } finally {
            try {
                assert fileOutputStream != null;
                fileOutputStream.close();
            } catch (IOException e) {
                log.error("CsvToXlsxUtil close FileOutputStream exception :", e);
            }
        }
        return getFileName(xlsxFileAddress);
    }
 
    /**
     * @param inputStream 输入流
     */
    public static InputStream csvStream2xlsxStream(InputStream inputStream, String fileName) {
        FileOutputStream fileOutputStream = null;
        try {
            fileName = fileName.replace(".csv", ".xlsx");
            XSSFWorkbook workBook = new XSSFWorkbook();
            XSSFSheet sheet = workBook.createSheet("sheet1");
            int rowNum = -1;
            CsvReader csvReader = new CsvReader(inputStream, StandardCharsets.UTF_8);
            while (csvReader.readRecord()) {
                rowNum++;
                XSSFRow currentRow = sheet.createRow(rowNum);
                for (int i = 0; i < csvReader.getColumnCount(); i++) {
                    currentRow.createCell(i).setCellValue(csvReader.get(i));
                }
            }
            File file = new File("/" + fileName);
            fileOutputStream = new FileOutputStream(file);
            workBook.write(fileOutputStream);
            InputStream input = new FileInputStream(file);
            file.delete();
            return input;
        } catch (Exception e) {
            log.error("CsvToXlsxUtil exception :", e);
        } finally {
            try {
                if (ObjectUtil.isNotNull(fileOutputStream)) {
                    assert fileOutputStream != null;
                    fileOutputStream.close();
                }
            } catch (IOException e) {
                log.error("CsvToXlsxUtil close FileOutputStream exception :", e);
            }
        }
        return null;
    }
 
    /**
     * 设置excel文件的sheet名称
     * 获取CSV文件名作为Excel文件的sheet名称
     *
     * @param path 资源路径
     */
    private static String getSheetName(String path) {
        try {
            String[] file = getFileName(path).split("\\.");
            return file[0];
        } catch (Exception e) {
            log.error("CsvToXlsxUtil get sheet name exception : ", e);
            return "Sheet";
        }
    }
 
    /**
     * 根据资源路径切割获取文件名
     *
     * @param path 资源路径
     */
    private static String getFileName(String path) {
        String[] paths = path.contains("\\") ? path.split("\\\\") : path.split("/");
        return paths[paths.length - 1];
    }
 
    /**
     * 常用CSV分隔符数组遍历资源第一行，分隔的字段数多的为资源分隔符
     * 异常情况下默认用’,‘作为分隔符
     *
     * @param path 资源路径
     */
    private static char getDelimiter(String path) {
        BufferedReader br = null;
        char delimiter = ',';
        try {
            br = new BufferedReader(new FileReader(path));
            String line = br.readLine();
            CsvReader csvReader;
            int columCount = 0;
            for (char delimiterTest : DELIMITERS) {
                csvReader = new CsvReader(getStringStream(line), delimiterTest, StandardCharsets.UTF_8);
                if (csvReader.readRecord()) {
                    int newColumnCount = csvReader.getColumnCount();
                    if (newColumnCount > columCount) {
                        columCount = newColumnCount;
                        delimiter = delimiterTest;
                    }
                }
            }
        } catch (Exception e) {
            log.error("CsvToXlsxUtil get delimiter exception :", e);
        } finally {
            try {
                assert br != null;
                br.close();
            } catch (IOException e) {
                log.error("CsvToXlsxUtil get delimiter close BufferedReader exception :", e);
            }
        }
        return delimiter;
    }
 
    /**
     * 字符串转输入流
     * 把CSV文件第一行数据转成输入流
     *
     * @param sInputString 字符串
     */
    private static InputStream getStringStream(String sInputString) {
        if (null != sInputString && !"".equals(sInputString)) {
            try {
                return new ByteArrayInputStream(sInputString.getBytes());
            } catch (Exception e) {
                log.error("CsvToXlsxUtil get StringStream exception :", e);
            }
        }
        return null;
    }
}