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

数据"/>

使用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<>();@Resourceprivate JmPrizeLogMapper prizeLogMapper;@Resourceprivate JmExpressMapper expressMapper;private static final ExecutorService POOL = Executors.newCachedThreadPool();@Overridepublic 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<>();@Overridepublic void invoke(PrizeLogImportExcelVO data, AnalysisContext context) {list.add(data);}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {prizeLogService.importExcel(list);}public PrizeLogImportListener(IJmPrizeLogService prizeLogService) {this.prizeLogService = prizeLogService;}public List<PrizeLogImportExcelVO> getList() {return list;}
}

6、调用逻辑

    @Resourceprivate 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 addressxlsxFileAddress = 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;}
}