文章目录
前言一、读取Excel模板二、读取Hbase数据1.创建Hbase连接和查询条件2.获取数据 三、向Excel模板写入相关数据，并生成本地文件四、将新生成的Excel以流的方式，进行邮件发送五、编写定时任务总结前言
在做航空方面的业务，需求为根据现有Excel模板，进行数据处理，并定时发送excel附件的相关邮件。
一、读取Excel模板
//读取excel模板ClassPathResource resource = new ClassPathResource(template);InputStream in = resource.getInputStream();XSSFWorkbook wb = new XSSFWorkbook(in);
二、读取Hbase数据
1.创建Hbase连接和查询条件
代码如下：需求查询两个hbase表数据，可根据实际需求进行调整
String tomorrowDateStr = tomorrowDateStr();String splitPrefix = tomorrowDateStr.substring(tomorrowDateStr.length() - 1);String rowKey = splitPrefix + "-" + tomorrowDateStr;try {Connection conn = null;Table inbndHTable = null;Table transHTable = null;conn = hBaseDataSource.getConnection();inbndHTable = HTableUtil.getTable(conn, HbaseName1);transHTable = HTableUtil.getTable(conn, HbaseName2);Scan inbndScan = new Scan();inbndScan.setCaching(conn.getConfiguration().getInt(HConstants.HBASE_CLIENT_SCANNER_CACHING, 10000));inbndScan.setCacheBlocks(true);inbndScan.setRowPrefixFilter(Bytes.toBytes(rowKey));Scan transScan = new Scan();transScan.setCaching(conn.getConfiguration().getInt(HConstants.HBASE_CLIENT_SCANNER_CACHING, 10000));transScan.setCacheBlocks(true);transScan.setRowPrefixFilter(Bytes.toBytes(rowKey));
2.获取数据
代码如下（示例）：
Map<String, Map<String, byte[]>> inbndckiResults = HTableUtil.scan(inbndHTable, inbndScan);Map<String, Map<String, byte[]>> transckiResults = HTableUtil.scan(transHTable, transScan);
三、向Excel模板写入相关数据，并生成本地文件
public void exportTemplateSendEmail(List platinumForecastLists, List platinumTeansForecastLists) throws Exception {Date date = new Date();//取时间String todayStr = sdf1.format(date);//读取excel模板ClassPathResource resource = new ClassPathResource(template);InputStream in = resource.getInputStream();XSSFWorkbook wb = new XSSFWorkbook(in);XSSFSheet indndSheet = wb.getSheetAt(0);XSSFSheet transSheet = wb.getSheetAt(1);XSSFCellStyle style = wb.createCellStyle();//单元格边框属性设置style.setBorderBottom(BorderStyle.THIN);style.setBorderLeft(BorderStyle.THIN);style.setBorderRight(BorderStyle.THIN);style.setBorderTop(BorderStyle.THIN);indndSheet.createRow(1).createCell(0).setCellValue("日期：" + todayStr);transSheet.createRow(1).createCell(0).setCellValue("日期：" + todayStr);indndSheet.getRow(1).getCell(0).setCellStyle(style);transSheet.getRow(1).getCell(0).setCellStyle(style);indndSheet.getRow(1).createCell(7).setCellStyle(style);transSheet.getRow(1).createCell(10).setCellStyle(style);style.setVerticalAlignment(VerticalAlignment.CENTER);style.setAlignment(HorizontalAlignment.CENTER);for (int i = 0; i < platinumForecastLists.size(); i++) {PlatinumInbndForecastBean platinumForecastBean = (PlatinumInbndForecastBean) platinumForecastLists.get(i);// row从0开始 cell 从0开始的indndSheet.createRow(i + 3).createCell(0).setCellValue(i + 1);indndSheet.getRow(i + 3).createCell(1).setCellValue(platinumForecastBean.getFullName());indndSheet.getRow(i + 3).createCell(2).setCellValue(platinumForecastBean.getDepartureTime());indndSheet.getRow(i + 3).createCell(3).setCellValue(platinumForecastBean.getFlightNum());indndSheet.getRow(i + 3).createCell(4).setCellValue(platinumForecastBean.getFQTVNumber());indndSheet.getRow(i + 3).createCell(6).setCellValue(platinumForecastBean.getCheckInInfoStatus());indndSheet.getRow(i + 3).createCell(7).setCellValue(platinumForecastBean.getTerminal());indndSheet.getRow(i + 3).getCell(0).setCellStyle(style);indndSheet.getRow(i + 3).getCell(1).setCellStyle(style);indndSheet.getRow(i + 3).getCell(2).setCellStyle(style);indndSheet.getRow(i + 3).getCell(3).setCellStyle(style);indndSheet.getRow(i + 3).getCell(4).setCellStyle(style);indndSheet.getRow(i + 3).createCell(5).setCellStyle(style);indndSheet.getRow(i + 3).getCell(6).setCellStyle(style);indndSheet.getRow(i + 3).getCell(7).setCellStyle(style);}for (int i = 0; i < platinumTeansForecastLists.size(); i++) {PlatinumTransForecastBean platinumForecastBean = (PlatinumTransForecastBean) platinumTeansForecastLists.get(i);// row从0开始 cell 从0开始的transSheet.createRow(i + 3).createCell(0).setCellValue(i + 1);transSheet.getRow(i + 3).createCell(1).setCellValue(platinumForecastBean.getInbndFltNum());transSheet.getRow(i + 3).createCell(2).setCellValue(platinumForecastBean.getArrTime());transSheet.getRow(i + 3).createCell(3).setCellValue(platinumForecastBean.getLastSeg());transSheet.getRow(i + 3).createCell(4).setCellValue(platinumForecastBean.getFullName());transSheet.getRow(i + 3).createCell(5).setCellValue(platinumForecastBean.getTransFltNum());transSheet.getRow(i + 3).createCell(6).setCellValue(platinumForecastBean.getDepTime());transSheet.getRow(i + 3).createCell(7).setCellValue(platinumForecastBean.getNextSeg());transSheet.getRow(i + 3).createCell(9).setCellValue(platinumForecastBean.getFQTVNumber());transSheet.getRow(i + 3).createCell(10).setCellValue(platinumForecastBean.getTelNum());transSheet.getRow(i + 3).getCell(0).setCellStyle(style);transSheet.getRow(i + 3).getCell(1).setCellStyle(style);transSheet.getRow(i + 3).getCell(2).setCellStyle(style);transSheet.getRow(i + 3).getCell(3).setCellStyle(style);transSheet.getRow(i + 3).getCell(4).setCellStyle(style);transSheet.getRow(i + 3).getCell(5).setCellStyle(style);transSheet.getRow(i + 3).getCell(6).setCellStyle(style);transSheet.getRow(i + 3).getCell(7).setCellStyle(style);transSheet.getRow(i + 3).createCell(8).setCellStyle(style);transSheet.getRow(i + 3).getCell(9).setCellStyle(style);transSheet.getRow(i + 3).getCell(10).setCellStyle(style);}indndSheet.createFreezePane(0, 3, 0, 3);transSheet.createFreezePane(0, 3, 0, 3);//写入磁盘保存文件FileOutputStream output = new FileOutputStream(patinumFileName);wb.write(output);output.close();//作为输出流发邮件ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();wb.write(byteArrayOutputStream);List<String> emailList = new ArrayList<>();emailList.add("自定义邮箱");for (String email : emailList) {boolean rst = sendMail(new ByteArrayInputStream(byteArrayOutputStream.toByteArray()), "白金卡预报", email);if (rst) {LOGGER.info("向" + email + "邮件发送成功！");} else {LOGGER.info("向" + email + "邮件发送失败！");}}}
四、将新生成的Excel以流的方式，进行邮件发送
public boolean sendMail(InputStream is, String fileName, String toMail) {Transport transport = null;try {System.setProperty("mail.mime.splitlongparameters", "false");Properties props = new Properties();// 设置发送邮件的邮件服务器的属性（这里使用网易的smtp服务器）props.put("mail.smtp.host", host);props.put("mail.smtp.port", port);props.put("mail.smtp.socketFactory.class", ".ssl.SSLSocketFactory");props.put("mail.smtp.ssl.trust", "*");// 需要经过授权，也就是有户名和密码的校验，这样才能通过验证（一定要有这一条）props.put("mail.smtp.auth", "true");// 用刚刚设置好的props对象构建一个sessionSession session = Session.getDefaultInstance(props);// 有了这句便可以在发送邮件的过程中在console处显示过程信息，供调试使// 用（你可以在控制台（console)上看到发送邮件的过程）session.setDebug(false);// 用session为参数定义消息对象MimeMessage message = new MimeMessage(session);// 加载发件人地址message.setFrom(new InternetAddress(username));// 加载收件人地址message.addRecipient(Message.RecipientType.TO, new InternetAddress(toMail));// 加载标题message.setSubject(fileName);// 向multipart对象中添加邮件的各个部分内容，包括文本内容和附件Multipart multipart = new MimeMultipart();// 设置邮件的文本内容BodyPart contentPart = new MimeBodyPart();contentPart.setText("自定义邮件内容");multipart.addBodyPart(contentPart);// 添加附件BodyPart messageBodyPart = new MimeBodyPart();DataSource source = new ByteArrayDataSource(is, "application/msexcel");// 添加附件的内容messageBodyPart.setDataHandler(new DataHandler(source));// 添加附件的标题// 这里很重要，通过下面的Base64编码的转换可以保证你的中文附件标题名在发送时不会变成乱码messageBodyPart.setFileName(MimeUtility.encodeText(fileName + ".xlsx"));multipart.addBodyPart(messageBodyPart);// 将multipart对象放到message中message.setContent(multipart);// 保存邮件message.saveChanges();// 发送邮件transport = session.getTransport(protocol);// 连接服务器的邮箱 发件人和授权码transport.connect(host, username, password);// 把邮件发送出去transport.sendMessage(message, message.getAllRecipients());return true;} catch (Exception e) {e.printStackTrace();return false;} finally {try {transport.close();} catch (Exception e) {e.printStackTrace();}}}
五、编写定时任务
定时下午5点进行邮件发送； task.sendMsg();调用了exportTemplateSendEmail（List list1，List list2）
@Scheduled(cron = "0 0 17 * * *")public void taskSync() {try {task.sendMsg();} catch (Exception e) {LOGGER.warn("PlatinumForecast email send failed!", e);}}
总结
遇到的问题，SSL协议证书问题（连接邮箱主机失败），将SSL协议设置为全部跳过，详见标题四
props.put("mail.smtp.ssl.trust", "*")
