热门标签
热门文章
- 1【笔记】ArkTS 语言（OpenHarmony系统）_arkts语言
- 2opencv 实现特定颜色线条提取与定位_根据颜色提取线的代码
- 3GraspNet-1Billion 论文阅读
- 4Ubuntu系统安装mysql_ubuntu安装mysql
- 5RDD编程指南_三,spark 3.2.1安装哪个版本的python
- 6VTK读取序列DCM格式医学图像_读取dcm 文件 cpp
- 7MinIO单机版部署及常用功能使用_minio单机部署
- 8【GitHub】github学生认证，在vscode中使用copilot的教程
- 9Python大数据-电商产品评论情感数据分析，大数据开发音频面试_情感data数据
- 10常用的区块链数据查询网站_区块链交易查询网站
当前位置: article > 正文
java 定时任务每月1号发送excel邮件_定时邮件发送excelv表格java实现
作者：从前慢现在也慢 | 2024-05-19 05:54:39
赞
踩
定时邮件发送excelv表格java实现
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; }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
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; }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
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("----------库存货物明细（按库存）邮件发送成功--------"); } }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
4、页面录入定时任务，配置定时任务规则
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/591733
推荐阅读
相关标签
