easyExcel百万级别数据入库、导入、写文件操作

YelloweY
于 2024-07-19 16:41:25 发布
阅读量274
点赞数 2
文章标签： java
本文链接：https://blog.csdn.net/qq_42206404/article/details/140553561
版权
最近公司有个大批量excel数据入库的需求，原封装的poi工具老是内存溢出，现考虑使用alibaba的easyExcel，废话不多说，直接上代码，可以按照步骤来，根据自己的业务需求修改即可，百万数据的话，用了时间在一两分钟之内。学习时间不长，各位大佬看哪些不合理的地方，提出指导意见，不胜感激！
步骤一：
	添加依赖等操作就忽略了
	创建实体类，加入ExcelProperty注解，对应excel列名
@Data
public class MissWireOriDataExcelInfo {

    @ExcelProperty(index=0,value = "电能表资产编号")
    private String meterNo;
    
    @ExcelProperty(index=1,value ="A相电压")
    private double uA;

    @ExcelProperty(index=2,value ="B相电压")
    private double uB;

    @ExcelProperty(index=3,value ="C相电压")
    private double uC;

    @ExcelProperty(index=4,value ="A相电流")
    private double iA;

    @ExcelProperty(index=5,value ="B相电流")
    private double iB;

    @ExcelProperty(index=6,value ="C相电流")
    private double iC;

    @ExcelProperty(index=7,value ="A相有功功率")
    private double pA;

    @ExcelProperty(index=8,value ="B相有功功率")
    private double pB;

    @ExcelProperty(index=9,value ="C相有功功率")
    private double pC;

    @ExcelProperty(index=10,value ="有功功率总")
    private double pTotal;

    @ExcelProperty(index=11,value ="A相无功功率")
    private double qA;

    @ExcelProperty(index=12,value ="B相无功功率")
    private double qB;

    @ExcelProperty(index=13,value ="C相无功功率")
    private double qC;

    @ExcelProperty(index=14,value ="无功功率总")
    private double qTOTAL;

    @ExcelProperty(index=15,value ="数据时间")
    private String dataTime;
}
步骤二：
	创建对应的service、dao、controller此处省略
步骤三(核心)：
	创建对应的监听器
	
/**
 * @Author: huang
 * @Description: 批量excel数据导入监听器
 * @DateTime: 2024/7/17 17:49
 **/
@Slf4j
public class EasyExcelImportHandler implements ReadListener<MissWireOriDataExcelInfo> {
    //线程处理
    private static final ThreadPoolExecutor threadPoolExecutor;
    static {
        // 系统可用处理器的虚拟机数量
        int processors = Runtime.getRuntime().availableProcessors();
        threadPoolExecutor = new ThreadPoolExecutor(
                processors + 1,              // 核心线程数
                processors * 2 + 1,          // 最大线程数
                10 * 60,                     // 线程空闲时间
                TimeUnit.SECONDS,            // 时间单位
                new LinkedBlockingQueue<>(1000000) // 工作队列
        );
    }

    /*成功数据*/
    private Date now = new Date();
    private String batchId = DateUtil.dateToString(now, "yyyyMMddHHmmss");
    private List<MissWireOriDataExcelInfo> successList = new ArrayList<>();
    //    /*单次处理条数*/
    private final static int BATCH_COUNT = 100000;

    //必须通过构造函数注入
    private MissWireOridataMapper missWireOridataMapper;

    private MissWireBatchRecordMapper missWireBatchRecordMapper;

    private MissWireOridataService missWireOridataService;

    public EasyExcelImportHandler(MissWireOridataMapper missWireOridataMapper) {
        this.missWireOridataMapper = missWireOridataMapper;
    }

    public EasyExcelImportHandler(MissWireOridataMapper missWireOridataMapper, MissWireBatchRecordMapper missWireBatchRecordMapper, MissWireOridataService missWireOridataService) {
        this.missWireOridataMapper = missWireOridataMapper;
        this.missWireBatchRecordMapper = missWireBatchRecordMapper;
        this.missWireOridataService = missWireOridataService;
    }

    @Value("${misswire.excel.basepath}")
    private String basePath;

    private static StringBuilder stringBuilder = new StringBuilder();
    private String fileName;
    private MultipartFile file;

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public void setFile(MultipartFile file) {
        this.file = file;
    }


//    /**
//     * @Description: 转换异常，获取其他异常下会调用本接口，抛出异常则停止读取，如果不抛出异常，继续读取下一行
//     * @DateTime: 17:56 2024/7/17
//     * @Params: [e, analysisContext]
//     * @Return void
//     */
    @Override
    public void onException(Exception e, AnalysisContext analysisContext) throws Exception {
        throw e;
    }


    
//    /**
//     * @Author: 
//     * @Description: 读取表格内容，解析表格每条数据
//     * @DateTime: 17:53 2024/7/17
//     * @Params: [user, analysisContext]
//     * @Return void
//     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void invoke(MissWireOriDataExcelInfo oriData, AnalysisContext analysisContext) {
//        log.info("解析到一条数据:{}", oriData);
        stringBuilder.setLength(0);
        //数据校验
        validateData(oriData);

        // 如果有错误信息，抛出异常
        if (stringBuilder.length() > 0) {
            log.warn("数据校验失败: {}", stringBuilder.toString());
            throw new ValidationException(stringBuilder.toString());
        }

        successList.add(oriData);
        if (successList.size() >= BATCH_COUNT) {
//            log.info("读取数据：{}", successList.size());
            saveData();
        }
    }

    @Override
    public void extra(CellExtra cellExtra, AnalysisContext analysisContext) {
    }

    private void validateData(MissWireOriDataExcelInfo data) {
        String numberPattern = "^0|null|\\d+(\\.\\d+)?$";
        String dateTimePattern = "^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}$";
        //进行数据判断
        if (StringUtils.isEmpty(data.getMeterNo())) {
            stringBuilder.append("电能表不能为空");
        }
        if (data.getDataTime() == null || !data.getDataTime().toString().matches(dateTimePattern)) {
            stringBuilder.append("数据时间 (Data_TIME) 不能为空，且时间要为yyyy-MM-dd HH:mm:ss格式！<br/>");
        }
    }

    //
    private boolean isValidNumber(double value, String pattern) {
        return String.valueOf(value).matches(pattern);
    }

    //
//    /**
//     * 采用多线程读取数据
//     */
    @Transactional(rollbackFor = Exception.class)
    void saveData() {
        log.info("开始进入批量入库保存");
        List<List<MissWireOriDataExcelInfo>> lists = ListUtil.split(successList, 20000);
        CountDownLatch countDownLatch = new CountDownLatch(lists.size());
        for (List<MissWireOriDataExcelInfo> list : lists) {
            threadPoolExecutor.execute(() -> {
                try {
                    List<MissWireOridata> collect = list.stream().map(o -> {
                        int segmentId = DateUtil.GetSegIdByDate(DateUtil.stringToDate(o.getDataTime()), 15);
                        MissWireOridata ori = new MissWireOridata();
                        ori.setSegmentId(segmentId);
                        ori.setBatchId(batchId);
                        ori.setMeterNo(o.getMeterNo());
                        ori.setDataTime(DateUtil.stringToDate(o.getDataTime()));
                        ori.setUa((float) o.getUA());
                        ori.setUb((float) o.getUB());
                        ori.setUc((float) o.getUC());
                        ori.setIa((float) o.getIA());
                        ori.setIb((float) o.getIB());
                        ori.setIc((float) o.getIC());
                        ori.setPTotal((float) o.getPTotal());
                        ori.setPa((float) o.getPA());
                        ori.setPb((float) o.getPB());
                        ori.setPc((float) o.getPC());
                        ori.setQa((float) o.getQA());
                        ori.setQb((float) o.getQB());
                        ori.setQc((float) o.getQC());
                        ori.setQTotal((float) o.getQTOTAL());
                        return ori;
                    }).collect(Collectors.toList());
                    missWireOridataService.saveBatch(collect);
                } catch (Exception e) {
                    log.error("启动线程失败,e:{}", e.getMessage(), e);
                } finally {
                    //执行完一个线程减1,直到执行完
                    countDownLatch.countDown();
                }
            });
        }
        // 等待所有线程执行完
        try {
            countDownLatch.await();
        } catch (Exception e) {
            log.error("等待所有线程执行完异常,e:{}", e.getMessage(), e);
        }
        // 提前将不再使用的集合清空，释放资源
        successList.clear();
        lists.clear();
    }

    public List<MissWireOridata> getOriData(List<MissWireOriDataExcelInfo> excelInfoList) {
        List<MissWireOridata> oriDataList = new ArrayList<>();
        if (CollectionUtil.isNotEmpty(excelInfoList)) {
            for (MissWireOriDataExcelInfo wireOriDataExcelInfo : excelInfoList) {
                MissWireOridata m = new MissWireOridata();
                BeanUtil.copyProperties(wireOriDataExcelInfo, m);
                oriDataList.add(m);
            }
        }
        return oriDataList;
    }
    //
//    /**
//     * 所有数据读取完成之后调用
//     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        String s = StrUtil.subBefore(fileName, '.', false);
        String suffix = fileName.substring(fileName.lastIndexOf("."));
        String filePathName = s + "_" + batchId + suffix;
        //读取剩余数据
        if (CollectionUtils.isNotEmpty(successList)) {
//            log.info("读取数据：{}条", successList.size());
            List<MissWireOridata> oriData = getOriData(successList);
            if (CollectionUtil.isNotEmpty(oriData)) {
                missWireOridataService.saveBatch(oriData);
            }
            //定义批次数据入库
            MissWireBatchRecord batchRecord = new MissWireBatchRecord();
            batchRecord.setId(batchId);
            batchRecord.setDataSource("导入");
            //源数据名称 源文件名称+批次号
            batchRecord.setSourceData(s);
            batchRecord.setAnalysisState(CommonConstant.MISS_WIRE_ANALYSIS_STATE);
            batchRecord.setImportType(CommonConstant.IMPORT_COLLECT_TYPE);
            batchRecord.setRecordDate(DateUtil.dateToString(now, "yyMMdd"));
            batchRecord.setInsertTime(new Date());
            batchRecord.setUpdateTime(new Date());
            //批量保存
            missWireBatchRecordMapper.insert(batchRecord);
        }
        try {
            saveFile(file, "D:/uploadColData", filePathName);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    private void saveFile(MultipartFile file, String directory, String fileName) throws IOException {
        File dir = new File(directory);
        if (!dir.exists()) {
            if (!dir.mkdirs()) {
                throw new IOException("无法创建目录: " + directory);
            }
        }

        File destFile = new File(dir, fileName);
        try (InputStream in = file.getInputStream();
             OutputStream out = new FileOutputStream(destFile)) {
            byte[] buffer = new byte[1024];
            int bytesRead;
            while ((bytesRead = in.read(buffer)) != -1) {
                out.write(buffer, 0, bytesRead);
            }
            log.info("保存数据文件成功,路径{}", destFile);
        } catch (IOException e) {
            log.error("文件保存失败: {}", e.getMessage(), e);
            throw e; // 重新抛出异常以便上层调用者处理
        }
    }


步骤四：
    @PostMapping("/importOriData")
    @QueryTransferAnnotation
    public ResultVo importOriData(MissWireBatchRecordQo qo) throws IOException {
        try {
            log.info("采集数据导入开始：");
            long start = System.currentTimeMillis();
            EasyExcelImportHandler handler=new EasyExcelImportHandler(missWireOridataMapper,missWireBatchRecordMapper,missWireOridataService);
            String originalFilename = qo.getFile().getOriginalFilename();
//            // 将文件名传递给 EasyExcelImportHandler
            handler.setFileName(originalFilename);
            handler.setFile(qo.getFile());
//            // 将文件名传递给 EasyExcelImportHandler
            // 使用 EasyExcel 读取文件并处理
            EasyExcel.read(qo.getFile().getInputStream(),
                    MissWireOriDataExcelInfo.class, handler).sheet().doRead();
            long end = System.currentTimeMillis();
            log.info("采集数据导入结束，消耗时间：{}",end-start);
            return ResultVo.build().success("数据导入成功");
        } catch (ValidationException e) {
            return ResultVo.build().failed(e.getMessage());
        }
    }