该博客介绍了如何使用Java实现每月1号自动发送包含Excel附件的邮件。首先，通过查询数据库生成Excel数据流，包括设置单元格样式，然后创建包含附件的邮件，并使用SMTP发送邮件到指定收件人。
java 定时任务每月1号发送excel邮件
1、生成excel 数据流
public ByteArrayInputStream exportxls(){
String preDate = "";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
ByteArrayInputStream iss = null;
try{
String sql = " ";
//页面总合计
String sql5 ="";
RowMapper<CorrelationDetailDTO2> rowMapper=new BeanPropertyRowMapper<CorrelationDetailDTO2>(CorrelationDetailDTO2.class);
List<CorrelationDetailDTO2> list= jdbcTemplate.query(sql, rowMapper);
List<Map<String,Object>> l = jdbcTemplate.queryForList(sql5);
String stockPcs = "";
String stockBox = "";
String totalWeight = "";
String totalVolume = "";
if(l.get(0).get("stock_pcs") != null &&l.get(0).get("stock_pcs") != "") {
stockPcs = l.get(0).get("stock_pcs").toString().trim();
}
if(l.get(0).get("stock_box") != null &&l.get(0).get("stock_box") != "") {
stockBox = l.get(0).get("stock_box").toString().trim();
}
if(l.get(0).get("total_weight") != null &&l.get(0).get("total_weight") != "") {
totalWeight = l.get(0).get("total_weight").toString().trim();
}
if(l.get(0).get("total_volume") != null &&l.get(0).get("total_volume") != "") {
totalVolume = l.get(0).get("total_volume").toString().trim();
}
List<Map<String, Object>> maps = new ArrayList<>();
Map<String, Object> map1 = new HashMap<>();
maps.add(map1);
maps.get(0).put("list", list);
maps.get(0).put("stockPcs", stockPcs);
maps.get(0).put("stockBox", stockBox);
maps.get(0).put("totalWeight", totalWeight);
maps.get(0).put("totalVolume", totalVolume);
TemplateExportParams params = new TemplateExportParams("export\\template\\kucunhuowudaochu2.xls", "库存货物明细(按库位)", 0);
Workbook workbook = ExcelExportUtil.exportExcel(params, maps.get(0));
Sheet sheetAt = workbook.getSheetAt(0);
sheetAt.setColumnWidth(18, 50*256);
CellStyle style = workbook.createCellStyle();
CellStyle style1 = workbook.createCellStyle();
CellStyle style2 = workbook.createCellStyle(); //倒数第二行格式
CellStyle style3 = workbook.createCellStyle(); //表格内格式
CellStyle style4 = workbook.createCellStyle(); //合计行格式
CellStyle style5 = workbook.createCellStyle(); //合计那列上面一行格式
DataFormat f= workbook.createDataFormat();
Font font = workbook.createFont();
font.setFontName("微软雅黑");
font.setFontHeightInPoints((short) 16);
Font font1 = workbook.createFont();
font1.setFontName("微软雅黑");
font1.setFontHeightInPoints((short) 16);
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //粗体
style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
style.setWrapText(true);//自动换行
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直对齐的样式为居中对齐;
style.setFont(font);
style1.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
style1.setWrapText(true);//自动换行
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直对齐的样式为居中对齐;
style1.setFont(font);
style2.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style2.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);//右边框
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
style2.setWrapText(true);//自动换行
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直对齐的样式为居中对齐;
style2.setFont(font);
style3.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style3.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边框
style3.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直对齐的样式为居中对齐;
style3.setDataFormat(f.getFormat("#,##0.00"));
style3.setFont(font);
style4.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style4.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//颜色
style4.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
style4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直对齐的样式为居中对齐;
style4.setDataFormat(f.getFormat("#,##0.00"));
style4.setFont(font1);
style5.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边框
style5.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style5.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style5.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
style5.setWrapText(true);//自动换行
style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直对齐的样式为居中对齐;
style5.setDataFormat(f.getFormat("#,##0.00"));
style5.setFont(font);
int i = 0;
for(CorrelationDetailDTO2 c :list) {
Row r = sheetAt.getRow(6+i);
if(StringUtil.isNotEmpty(c.getStockPcs())) {
r.getCell(11).setCellValue(c.getStockPcs()*1.00);
r.getCell(11).setCellStyle(style3);
}
if(StringUtil.isNotEmpty(c.getStockBox())) {
r.getCell(13).setCellValue(Double.parseDouble(c.getStockBox()));
r.getCell(13).setCellStyle(style3);
}
if(StringUtil.isNotEmpty(c.getTotalWeight())) {
r.getCell(16).setCellValue(Double.parseDouble(c.getTotalWeight()));
r.getCell(16).setCellStyle(style3);
}
if(StringUtil.isNotEmpty(c.getTotalVolume())) {
r.getCell(17).setCellValue(Double.parseDouble(c.getTotalVolume()));
r.getCell(17).setCellStyle(style3);
}
i++;
}
Row r = sheetAt.getRow(5+list.size());
r.getCell(0).setCellStyle(style);
r.getCell(1).setCellStyle(style1);
r.getCell(2).setCellStyle(style1);
r.getCell(3).setCellStyle(style1);
r.getCell(4).setCellStyle(style1);
r.getCell(5).setCellStyle(style1);
r.getCell(6).setCellStyle(style1);
r.getCell(7).setCellStyle(style1);
r.getCell(8).setCellStyle(style1);
r.getCell(9).setCellStyle(style1);
r.getCell(10).setCellStyle(style1);
r.getCell(11).setCellStyle(style5);
r.getCell(12).setCellStyle(style1);
r.getCell(13).setCellStyle(style5);
r.getCell(14).setCellStyle(style1);
r.getCell(15).setCellStyle(style1);
r.getCell(16).setCellStyle(style5);
r.getCell(17).setCellStyle(style5);
r.getCell(18).setCellStyle(style1);
r.getCell(19).setCellStyle(style1);
r.getCell(20).setCellStyle(style1);
r.getCell(21).setCellStyle(style1);
r.getCell(22).setCellStyle(style1);
r.getCell(23).setCellStyle(style2);
//合计行
r = sheetAt.getRow(6+list.size());
if(StringUtil.isNotEmpty(stockPcs)) {
r.getCell(11).setCellValue(Double.parseDouble(stockPcs));
r.getCell(11).setCellStyle(style4);
}
if(StringUtil.isNotEmpty(stockBox)) {
r.getCell(13).setCellValue(Double.parseDouble(stockBox));
r.getCell(13).setCellStyle(style4);
}
if(StringUtil.isNotEmpty(totalWeight)) {
r.getCell(16).setCellValue(Double.parseDouble(totalWeight));
r.getCell(16).setCellStyle(style4);
}
if(StringUtil.isNotEmpty(totalVolume)) {
r.getCell(17).setCellValue(Double.parseDouble(totalVolume));
r.getCell(17).setCellStyle(style4);
}
workbook.getSheetAt(0).autoSizeColumn(16);
ByteArrayOutputStream os = new ByteArrayOutputStream();
workbook.write(os);
iss = new ByteArrayInputStream(os.toByteArray());
os.close();
return iss;
}catch(Exception e){
System.out.println(e.getMessage());
logger.info("----------库存货物明细（按库存）excel生成失败-----"+sdf.format(new Date())+"--------");
}
return iss;
}
2、创建一封包含附件的邮件
/**
* 包含附件的邮件
* 创建一封包含附件的邮件
* @param session
* @return
* @throws MessagingException
*/
public static MimeMessage createAttachMailXlsx(Session session, String title, String content, String toUser, ByteArrayInputStream iss, String suffix) throws MessagingException, IOException {
MimeMessage message = new MimeMessage(session);
message.setFrom(new InternetAddress("3005120450@qq.com","江苏环际益供应链管理有限公司"));
InternetAddress[] sendTo = InternetAddress.parse(toUser);
message.setRecipients(MimeMessage.RecipientType.TO, sendTo);
message.setSubject(title);
message.setSentDate(new Date());
MimeBodyPart bodyPart = new MimeBodyPart();
bodyPart.setContent(content, "text/html;charset=UTF-8");
MimeBodyPart attachPart = new MimeBodyPart();
DataSource dataSource = new ByteArrayDataSource(iss, "application/excel");
DataHandler dataHandler = new DataHandler(dataSource);
attachPart.setDataHandler(dataHandler);
attachPart.setFileName(title+suffix);
MimeMultipart multipart = new MimeMultipart();
multipart.addBodyPart(bodyPart);
multipart.addBodyPart(attachPart);
multipart.setSubType("mixed");
message.setContent(multipart);
return message;
}
3、发送邮件
public void run() {
String preDate = "";
SimpleDateFormat format=new SimpleDateFormat("MMddHHmmss");
String time="库存货物明细（按库位）"+format.format(new Date());
try{
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//发送邮件
Properties prop = new Properties();
prop.setProperty("mail.smtp.auth", "true");
prop.setProperty("mail.smtp.host", "smtp.qq.com");
prop.setProperty("mail.transport.protocol", "smtp");
MailSSLSocketFactory sf = new MailSSLSocketFactory();
sf.setTrustAllHosts(true);
prop.put("mail.smtp.ssl.enable", "true");
prop.put("mail.smtp.ssl.socketFactory", sf);
prop.put("mail.smtp.socketFactory.class", "javax.net.ssl.SSLSocketFactory");
prop.put("mail.smtp.socketFactory.fallback", "false");
Session session = Session.getInstance(prop);
session.setDebug(true);
Transport ts = session.getTransport();
ts.connect("smtp.qq.com", "111111@qq.com", reportController.getPwd());
String content = time;
String title = sdf.format(new Date())+"库存货物明细";
String toUser = "111@qq.com";
//导出excel
ByteArrayInputStream inputStream = exportxls();;
Message message = createAttachMailXlsx(session,title,content,toUser,inputStream,".xls");
ts.sendMessage(message, message.getAllRecipients());
ts.close();
logger.info("----库存货物明细（按库存）---邮件发送成功---");
}catch(Exception e){
System.out.println(e.getMessage());
logger.info("----------库存货物明细（按库存）邮件发送成功--------");
}
}
4、页面录入定时任务，配置定时任务规则
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
