EasyExcel 超百万数据处理总结

2023-09-25 22:53:52

解析dada文件 并生产excel 压缩 然后发送邮件

EasyExcel单个sheet104万超过的话可以分sheet 处理

//解密后的data数据 
dataFileDecipher =xxxx.data
BufferedReader reader = new BufferedReader(new FileReader(dataFileDecipher));

List<EasyExcelPledgeDeailDataDto> list = new ArrayList<>() ;

ExcelWriter excelWriter = EasyExcel.write(excelName).build();

Long lineNumber = 0L;
int sheetNo = 0;
WriteSheet detailSheet = EasyExcel.writerSheet(sheetNo, "明细").head(EasyExcelPledgeDeailDataDto.class).build();
long sheetDataCount = 0L;

while (true){
    String line = null;
    lineNumber++;
    try {
        line = reader.readLine();
} catch (IOException e) {
    log.error("[" + dataFileDecipher + "]文件行数据读取异常，" + lineNumber, e);
    continue;
}

if(StringUtils.isBlank(line)){
    break;
}
    String[] lineDataArr = line.split(Constants.FLIT_SUFFIX);

EasyExcelPledgeDeailDataDto hzhcPledgeDetailDto =new EasyExcelPledgeDeailDataDto();

list.add(hzhcPledgeDetailDto);

//每1000条写入 清空list 继续 超过100万分sheet
if(list.size()==1000){
    excelWriter.write(list, detailSheet);
    sheetDataCount += 1000;
    list.clear();
    if (sheetDataCount >= 1000000) {
        log.info("sheetNo===={}",sheetNo);
        ++sheetNo;
        detailSheet = EasyExcel.writerSheet(sheetNo, "明细" + sheetNo).head(EasyExcelPledgeDeailDataDto.class).build();
        sheetDataCount = 0;
    }
    log.info("sheetDataCount:{},sheetNo{}",sheetDataCount,sheetNo);
}
}

excelWriter.write(list, detailSheet);

//汇总sheet 
List<EasyExcelPledgeDeailSumDataDto> sumList = new ArrayList<>();

EasyExcelPledgeDeailSumDataDto dataDto = new EasyExcelPledgeDeailSumDataDto();

sumList.add(dataDto);

WriteSheet sheet1 = EasyExcel.writerSheet(++sheetNo, "汇总文件").head(EasyExcelPledgeDeailSumDataDto.class).build();

excelWriter.write(sumList, sheet1);
excelWriter.finish();

// 分卷压缩 zip

List<String> sourceFilePathList = Arrays.asList(new String[]{excelName});
List<String> splitZipPath = null;
try {
    splitZipPath = ZipUtil.zipBySplit(sourceFilePathList, zipName, null, 20 * 1024 * 1024);
} catch (Exception e) {
    log.error("文件压缩失败：filePath:{}", excelName, e);
}
if (CollectionUtils.isEmpty(splitZipPath)) {
    log.error("文件压缩失败：filePath:{}", excelName);
    return;
}

/// 分卷压缩的方法

/**
 *
 * @param srcFiles 要压缩的文件绝对路径列表（支持多个文件的合并压缩）
 * @param destFile 要压缩的zip文件名
 * @param passwd   压缩密码
 * @param fileSize 分卷大小
 * @return    压缩文件路径数组
 */
public static List<String> zipBySplit(List<String> srcFiles, String destFile, String passwd, long fileSize) throws ZipException {

    File tmpFile = new File(destFile);
    if (tmpFile.exists()) {
        tmpFile.delete();
    }
    ArrayList<String> zipList = null;
    //创建压缩文件对象
    net.lingala.zip4j.core.ZipFile zipFile = new net.lingala.zip4j.core.ZipFile(destFile);
    //创建文档对象集合
    ArrayList<File> filesToAdd = new ArrayList<File>();
    //判断源压缩文件列表是否为空
    if (null != srcFiles && srcFiles.size()>0) {
        int fileCount = srcFiles.size();
        for (int i = 0; i < fileCount; i++) {
            filesToAdd.add(new File(srcFiles.get(i)));
        }

        //设置压缩参数
        ZipParameters parameters = new ZipParameters();
        //设置压缩密码
        if (!StringUtils.isBlank(passwd)) {
            parameters.setEncryptFiles(true);
            parameters.setEncryptionMethod(Zip4jConstants.ENC_METHOD_STANDARD);
            parameters.setPassword(passwd.toCharArray());
        }
        //设置压缩方式-默认
        parameters.setCompressionMethod(Zip4jConstants.COMP_DEFLATE);
        //设置压缩级别-一般
        parameters.setCompressionLevel(Zip4jConstants.DEFLATE_LEVEL_NORMAL);
        //设置默认分割大小为64KB
        //SplitLenth has to be greater than 65536 bytes
        if (fileSize == 0) {
            fileSize = 65536;
        }
        //创建分卷压缩文件
        zipFile.createZipFile(filesToAdd,parameters,true,fileSize);

        //获取分卷下载列表
        zipList = zipFile.getSplitZipFiles();
        if (null != zipList && zipList.size()>0) {
            String surFix = ".z010";
            String surFixReplace = ".z10";
            //单独处理第10个包的文件名做特殊处理
            for (int i = 0; i < zipList.size(); i++) {
                String file = zipList.get(i).trim();
                int length = file.length();
                String surFixTmp = file.substring(length - 5, length);
                if (surFix.equals(surFixTmp)) {
                    file = file.replace(surFix, surFixReplace);
                }
                zipList.set(i,file);
            }
        }

    }
    return zipList;
}

// 发送邮件方法如果邮件附件名字太长乱码下面有处理

public void sendZjyyMail(String[] toUserList, String subject, String text, List<File> attachmethes) {
    MimeMessage message = zjyyMailSender.createMimeMessage();
    try {
        //发送带附件和内联元素的邮件需要将第二个参数设置为true
        System.setProperty("mail.mime.splitlongparameters","false");
        MimeMessageHelper helper = new MimeMessageHelper(message, true,"UTF-8");
        //发送方邮箱，和配置文件中的mail.username要一致
        helper.setFrom(zjyyusername);
        //接收方
        helper.setTo(toUserList);
        //主题
        helper.setSubject(subject);
        //邮件内容
        helper.setText(text, true);
        if (!CollectionUtils.isEmpty(attachmethes)) {
            attachmethes.stream().forEach(file -> {
                try {
                   // helper.addAttachment(file.getName(), file);
                    helper.addAttachment(MimeUtility.encodeWord(file.getName(),"utf-8","B"), file);
                } catch (MessagingException | UnsupportedEncodingException e) {
                    log.error("附件添加失败，{}，{}", subject, file.getName(), e);
                }
            });
        }
        zjyyMailSender.send(message);
        log.info("zjyyMail邮件发送完成，{}", subject);
    } catch (MessagingException e) {
        log.error("zjyyMail邮件发送失败，{}", subject, e);
    } finally {
        if (CollectionUtils.isNotEmpty(attachmethes)) {
            for (File file : attachmethes) {
                FileUtil.delete(file);
            }
        }
    }
}

=================================================================

分多个 excel

protected void prepareDataPathDownload(String fundCode, String bizDate, String dataFilePath) {
// 文件ftp路径把路径日期进行转换
dataFilePath = localFileNameDictionaryReplace(dataFilePath, bizDate);
// 去除后缀 .data 后缀的名称
//upload/HZHCDB/{bizDate}/pledge_asset.data
String dataFilePathNoExtension = dataFilePath.replace(Constants.DATA_FILE_SUFFIX, "");
//String dataFileCtrl = dataFilePathNoExtension + Constants.CTRL_FILE_SUFFIX;
//解密后路径文件 /upload/HZHCDB/20211227/pledge_asset_decipher.data
String dataFileDecipher = dataFilePathNoExtension + Constants.DATA_FILE_SUFFIX_DECIPHER;

// log.info("ftp路径dataFilePath:{},去除后缀dataFilePathNoExtension:{},dataFileCtrl:{},解密后dataFileDecipher:{}",dataFilePath,dataFilePathNoExtension,dataFileCtrl,dataFileDecipher);

// 下载文件
try {
boolean downloadRet = downloadAndDecrypt(fundCode, bizDate, dataFilePath, null, dataFileDecipher);
if (!downloadRet) {
return;
}
// 处理解密后的数据
dataFileDecipher = partnerConfig.getLocalDataPath() + dataFilePath.replaceFirst("/", "");

if (!new File(dataFileDecipher).exists()) {
log.info("[{}质押明细文件不存在，filePath:{}", fundCode, dataFileDecipher);
return ;
}

// 开始处理数据
BufferedReader reader = null;

try {
reader = new BufferedReader(new FileReader(dataFileDecipher));
}catch (Exception e){
log.error("[{}]文件读取失败", dataFileDecipher, e);
}

List<EasyExcelPledgeDeailDataDto> list = new ArrayList<>() ;
List<EasyExcelPledgeDeailSumDataDto> sumList = new ArrayList<>();

int random =(int)(Math.random()*9+1)*10000;
String bianhao = getRandom(random);
String baseTimeExcelName =DateUtil.date2StringFormat(DateUtils.addDays(getPreMonthLastDay(), 0), JodaTimeUtil.LOCALDATE_FORMAT_NO_DELIMITER);
// 基准时间数据
String baseTime =DateUtil.date2StringFormat(DateUtils.addDays(getPreMonthLastDay(), 0), JodaTimeUtil.LOCALDATE_FORMAT_WITH_DELIMITER_CNH);

// 统计时间执行任务的当天
String statisticsTime=LocalDate.now().toString(JodaTimeUtil.LOCALDATE_FORMAT_WITH_DELIMITER_CNH);
// 截至基准时间应收未收担保费总额合计
BigDecimal unreceiveDbAmtSum = BigDecimal.ZERO;
// 截至基准时间应追偿未追偿款合计
BigDecimal uncompensateAmtSum=BigDecimal.ZERO;
// 截至基准时间应收未收担保违约金合计
BigDecimal breakAmtSum=BigDecimal.ZERO;
// 总合计
BigDecimal totalAmtSum=BigDecimal.ZERO;
// 可以当做笔数
int lineNumber = 0;
int sheetNo = 0;
//质押财产清单编号00001_统计时间20211205_基准时间20211130.xlsx
String excelName = partnerConfig.getLocalDataPath() +"质押财产清单"+bianhao+"_统计时间"+ LocalDate.now().toString("yyyyMMdd") +"_基准时间"+baseTimeExcelName+ ".xlsx";
String zName = partnerConfig.getLocalDataPath() +"质押财产清单"+bianhao+"_统计时间"+ LocalDate.now().toString("yyyyMMdd") +"_基准时间"+baseTimeExcelName+ ".zip";

String No="_"+sheetNo+"_统计时间";
String name=excelName.replace("_统计时间",No);
String zipName=zName.replace("_统计时间",No);

ExcelWriter excelWriter = null;
WriteSheet detailSheet = EasyExcel.writerSheet(0, "质押财产清单明细").head(EasyExcelPledgeDeailDataDto.class).build();
WriteSheet sheet1 = EasyExcel.writerSheet(1, "汇总文件").head(EasyExcelPledgeDeailSumDataDto.class).build();
excelWriter = EasyExcel.write(name).build();
long sheetDataCount = 0L;
while (true){
String line = null;
lineNumber++;
try {
line = reader.readLine();
} catch (IOException e) {
log.error("[" + dataFileDecipher + "]文件行数据读取异常，" + lineNumber, e);
continue;
}

if(StringUtils.isBlank(line)){
break;
}
String[] lineDataArr = line.split(Constants.FLIT_SUFFIX);
// 每一行转换成一个对象
// TODO 直接转成EasyExcelDto
// TOTO 每解析一行，要累计一次
EasyExcelPledgeDeailDataDto hzhcPledgeDetailDto =new EasyExcelPledgeDeailDataDto();
hzhcPledgeDetailDto.setDbContractNbr(lineDataArr[0]);
hzhcPledgeDetailDto.setDbContracttime(lineDataArr[1]);
hzhcPledgeDetailDto.setCustomName(lineDataArr[2]);
hzhcPledgeDetailDto.setCustId(lineDataArr[3]);
hzhcPledgeDetailDto.setUnreceiveDbAmt(StringUtils.isBlank(lineDataArr[4]) ? BigDecimal.ZERO : new BigDecimal(lineDataArr[4]));
hzhcPledgeDetailDto.setUncompensateAmt(StringUtils.isBlank(lineDataArr[5]) ? BigDecimal.ZERO : new BigDecimal(lineDataArr[5]));
hzhcPledgeDetailDto.setBreakAmt(StringUtils.isBlank(lineDataArr[6]) ? BigDecimal.ZERO : new BigDecimal(lineDataArr[6]));
unreceiveDbAmtSum =(unreceiveDbAmtSum.add(hzhcPledgeDetailDto.getUnreceiveDbAmt())).setScale(2, BigDecimal.ROUND_HALF_UP);
uncompensateAmtSum=(uncompensateAmtSum.add(hzhcPledgeDetailDto.getUncompensateAmt())).setScale(2, BigDecimal.ROUND_HALF_UP);
breakAmtSum=(breakAmtSum.add(hzhcPledgeDetailDto.getBreakAmt())).setScale(2, BigDecimal.ROUND_HALF_UP);
list.add(hzhcPledgeDetailDto);
if(list.size()==1000){
//TODO write excel
excelWriter.write(list, detailSheet);
sheetDataCount += 1000;
list.clear();
if (sheetDataCount >= 500000) {
// 合计
totalAmtSum=(unreceiveDbAmtSum.add(uncompensateAmtSum).add(breakAmtSum)).setScale(2, BigDecimal.ROUND_HALF_UP);
sheet1 = EasyExcel.writerSheet(1, "汇总文件").head(EasyExcelPledgeDeailSumDataDto.class).build();
EasyExcelPledgeDeailSumDataDto dataDto = new EasyExcelPledgeDeailSumDataDto();
dataDto.setPledgeDetailNo(bianhao);
dataDto.setStatisticsTime(statisticsTime);
dataDto.setBaseTime(baseTime+" 24时");
dataDto.setCount(sheetDataCount);
dataDto.setUnreceiveDbAmtSum(unreceiveDbAmtSum);
dataDto.setUncompensateAmtSum(uncompensateAmtSum);
dataDto.setBreakAmtSum(breakAmtSum);
dataDto.setTotalAmtSum(totalAmtSum);
sumList.add(dataDto);
//excelWriter.write(list, detailSheet);
excelWriter.write(sumList, sheet1);
excelWriter.finish();

log.info("sheetNo===={}",sheetNo);

//置0
unreceiveDbAmtSum = BigDecimal.ZERO;
uncompensateAmtSum=BigDecimal.ZERO;
breakAmtSum=BigDecimal.ZERO;
totalAmtSum=BigDecimal.ZERO;
sheetDataCount = 0;
++sheetNo;

//压缩
File fileDirectory = new File(name);
File[] zipSoureceFiles = {fileDirectory};
try {
log.info("zipName===={}",zipName);
ZipUtil.zipFileList(zipSoureceFiles, "", zipName);
} catch (Exception e) {
log.error("文件压缩失败：filePath:{}", excelName, e);
return ;
}
//发送邮件

List<File> attachmethes = new ArrayList<>();
attachmethes.add(new File(zipName));
String mailContent = mailHtml;
mailContent = mailContent.replace("{bianhao}", bianhao);
mailContent =mailContent.replace("{statisticsTime}",statisticsTime);
mailContent =mailContent.replace("{baseTime}",baseTime);
String subject="编号为{bianhao}的质押财产清单明细";
subject=subject.replace("{bianhao}",bianhao);
mailClient.sendZjyyMail(sendEmailToUser, subject, mailContent, attachmethes);

No="_"+sheetNo+"_统计时间";
name=excelName.replace("_统计时间",No);
log.info("name===={}",name);

excelWriter = EasyExcel.write(name).build();
detailSheet = EasyExcel.writerSheet(0, "质押财产清单明细").head(EasyExcelPledgeDeailDataDto.class).build();

zipName=zName.replace("_统计时间",No);
}

log.info("sheetDataCount:{},sheetNo{}",sheetDataCount,sheetNo);
}
}
//TODO write excel

// 小于50万的剩余的汇总
totalAmtSum=(unreceiveDbAmtSum.add(uncompensateAmtSum).add(breakAmtSum)).setScale(2, BigDecimal.ROUND_HALF_UP);

EasyExcelPledgeDeailSumDataDto dataDto = new EasyExcelPledgeDeailSumDataDto();
dataDto.setPledgeDetailNo(bianhao);
dataDto.setStatisticsTime(statisticsTime);
dataDto.setBaseTime(baseTime+" 24时");
dataDto.setCount(sheetDataCount);
dataDto.setUnreceiveDbAmtSum(unreceiveDbAmtSum);
dataDto.setUncompensateAmtSum(uncompensateAmtSum);
dataDto.setBreakAmtSum(breakAmtSum);
dataDto.setTotalAmtSum(totalAmtSum);
sumList.add(dataDto);
excelWriter.write(list, detailSheet);
excelWriter.write(sumList, sheet1);

excelWriter.finish();

// File[] zipSoureceFiles = new File[fileNameList.size()];
// for (int i = 0; i < zipSoureceFiles.length; i++) {
// zipSoureceFiles[i] = new File(fileNameList.get(i));
// }
log.info("name------{}",name);
File fileDirectory = new File(name);
File[] zipSoureceFiles = {fileDirectory};
try {
log.info("zipName------{}",zipName);
ZipUtil.zipFileList(zipSoureceFiles, "", zipName);
} catch (Exception e) {
log.error("文件压缩失败：filePath:{}", excelName, e);
return ;
}

List<File> attachmethes = new ArrayList<>();
attachmethes.add(new File(zipName));

String mailContent = mailHtml;
mailContent = mailContent.replace("{bianhao}", bianhao);
mailContent =mailContent.replace("{statisticsTime}",statisticsTime);
mailContent =mailContent.replace("{baseTime}",baseTime);
String subject="编号为{bianhao}的质押财产清单明细";
subject=subject.replace("{bianhao}",bianhao);
mailClient.sendZjyyMail(sendEmailToUser, subject, mailContent, attachmethes);
if (reader != null) {
try {
reader.close();
} catch (IOException e) {
log.error("io操作异常", e);
}
}
// 删除本地文件
// FileUtil.delete(dataFileDecipher);
// FileUtil.delete(excelName);
// FileUtil.delete(zipName);
} catch (Exception e) {
log.error("[{}文件处理][{}]文件下载解密失败", fundCode, dataFilePath, e);
return ;
}
}

码农公寓

相关文章