解析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 ;
}
}
————————————————
版权声明:本文为CSDN博主「FangsShancang」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/FangsShancang/article/details/122244369