import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.mail.javamail.MimeMessageHelper;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.annotation.Resource;
import javax.mail.BodyPart;
import javax.mail.Multipart;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.mail.internet.MimeUtility;
import java.io.*;
import java.math.BigDecimal;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.StandardCopyOption;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* 定时生成高风险拉别报告
* @author Hewenjun
*
*/
@Component
@Slf4j
public class XXGenerateScheduler {
public SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss E"); //年月日 时分秒 周
public static String FILE_NAME = "/excelTemplate/XX.xlsx";
@Value("${ldt.system.mail}") //properties文件中定义的变量值(邮件发送者)
String mailFrom;
public String excelName = emailTitle + ".xlsx";
@Autowired
JavaMailSender mailSender;
// @Scheduled(cron = "0 0 0 * * ?") //每天晚上0点0分0秒执行一次
@Scheduled(cron = "0 */1 * * * ?") //一分钟执行一次
public void process(){
List<XX> list = xxxxxxxxxxxxxxxxxxx一个数据集合;
File excel = createXLS(list);
//发送邮件
sendMail(mailFrom, 收件人邮件 , 邮件标题, 邮件内容, excel(附件));
}
/**
* 生成xlsx文件
*/
@SuppressWarnings("unchecked")
private File createXLS(Object data) throws Exception{
FileInputStream assIn = null;
FileOutputStream assOut = null;
try {
//获取springboot项目static路径下/excelTemplate/XX.xlsx---模板文件
File templateFile = new File(Objects.requireNonNull(this.getClass().getClassLoader().getResource("excelTemplate/XX.xlsx")).getPath());//获取模板
File tempFile = new File("D:\\XX\\"+excelName);//临时文件
if(!tempFile.exists()){
tempFile.getParentFile().mkdir();//创建文件夹
tempFile.createNewFile();//生成临时文件
}
copyFileContent(templateFile, tempFile);//把模板拷贝到临时文件,再把数据写入临时文件中
assIn = new FileInputStream(tempFile);
XSSFWorkbook assWb = new XSSFWorkbook(assIn);
XSSFSheet assSheet = assWb.getSheetAt(0);
if(data instanceof List){
List<xx> list = (List<XX>) data;
list.sort(Comparator.comparing(XX::getXXX).reversed());//以XXX倒序排序
for (int i = 0; i < list.size() ; i++) {
XX model = list.get(i);
setData(i, model, assWb, assSheet);
}
log.info("Excel附件数据成功生成: " + exportCount + "条,失败" + exportErrCount +"条 <br>");
}else{
log.error("Excel附件数据生成失败 -----》 无数据");
return null;
}
assOut = new FileOutputStream(tempFile);
assWb.write(assOut);
log.info("Excel附件生成结束 <br>");
return tempFile;
} catch (Exception e) {
e.printStackTrace();
log.error("生成excel失败 : " + e);
}finally{
if(assOut != null){
assOut.close();
}
if(assIn != null){
assIn.close();
}
}
return null;
}
/**
* 数据填充
*/
private void setData(int idx, XX model, XSSFWorkbook assWb, XSSFSheet assSheet) {
try {
//idx+2代表从第三行开始写入,第一行是标题,第二行是列名,2需要根据实际列数修改,否则获取第17行会空指针
XSSFRow assRow = createRowAndCel(assWb, assSheet, idx + 2, 2);
assRow.getCell(0).setCellValue(idx+1);//序号
assRow.getCell(1).setCellValue(model.xxx);//某某字段
} catch (Exception e) {
log.error("插入excel失败 : " + e);
msg.append("生成excel附件失败记录 : ").append(JSONObject.toJSONString(model)).append(" <br>");
}
}
/**
* 创建Excel的行(row)和列(cel)
*/
private XSSFRow createRowAndCel(XSSFWorkbook wb, XSSFSheet sheet, int rowIdx, int cellNum){
XSSFCellStyle cellStyle = wb.createCellStyle();//创建cell样式
sheet.createRow(rowIdx);
XSSFRow row = sheet.getRow(rowIdx);
for (int i = 0; i < cellNum; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellStyle(cellStyle);
}
return row;
}
/**
* 将文件fromFile 的内容复制到 toFile文件中
* 复制的可以是Excel等多种格式
*/
public void copyFileContent(File fromFile, File toFile) throws IOException {
FileInputStream ins = new FileInputStream(fromFile);
FileOutputStream out = new FileOutputStream(toFile);
byte[] b = new byte[1024];
int n;
while ((n = ins.read(b)) != -1) {
out.write(b, 0, n);
}
ins.close();
out.close();
}
//邮件内容格式:将生成的数据通过excel附件的方式,进行发送。其他文字:xxxx年x月x日,0:00要素高风险拉别数据如附件,请参考。
public void sendMail(String mailFrom, String mailTo, String title, String content, File file) throws Exception {
System.setProperty("mail.mime.splitlongparameters", "false");//1.设置系统值 ---处理文件名乱码
MimeMessage mimeMessage = mailSender.createMimeMessage();
MimeMessageHelper msg = new MimeMessageHelper(mimeMessage, true, "utf-8");//2.在创建对象的时候定义编码格式(utf-8)
msg.addAttachment(MimeUtility.encodeWord(excelName,"utf-8","B"), file); //3.在添加附件的时候,附件名是需要定义编码的 ---以上3条都得有,否则文件名乱码
msg.setFrom(mailFrom);
msg.setTo(mailTo);
msg.setSubject(title);
msg.setText(content, true);
mailSender.send(mimeMessage);
}
}
获取excel模板复制到临时文件中并写入数据作为附件发送邮件
最新推荐文章于 2022-11-10 15:45:54 发布