查询数据库设计excel并发送邮件

最近公司运维几乎把权限都收走了，所以统计数据这块发邮件审批通过开始计算很不方便，所以弄成excel发到自己的邮箱吧，excel是普通的功能但是我几乎没有做过，哈哈~也是意想不到，开始吧

从库或者缓存中查出数据

设计excel，这不没有生成文件，差的几步从网上找一下还是有的

  private HSSFWorkbook exportDate(Map<Integer, List<UserDto>> resultMap) {

        List<String> headers = new ArrayList<>();
        headers.add("goddess");
        headers.add("userName");
        headers.add("goldNum");
        headers.add("totalDuration");
        headers.add("callTime");

        HSSFWorkbook workbook = new HSSFWorkbook();
        for (Map.Entry<Integer, List<UserDto>> listEntry : resultMap.entrySet()) {

            HSSFSheet sheet = workbook.createSheet(String.valueOf(listEntry.getKey()));
            log.info("cur sheet {}", listEntry.getKey());

            HSSFRow hssfRow = sheet.createRow(0);
            for (int i = 0, l = headers.size(); i < l; i++) {
                HSSFCell cell = hssfRow.createCell(i);
                cell.setCellValue(new HSSFRichTextString(headers.get(i)));
            }

            int i = 1;
            for (UserDto userDto : listEntry.getValue()) {
                HSSFRow newRos = sheet.createRow(i);//数据从第二行开始

                HSSFCell cell = newRos.createCell(0);
                cell.setCellValue(userDto.getUserId());
                HSSFCell cell1 = newRos.createCell(1);
                cell1.setCellValue(userDto.getUserName());
                HSSFCell cell2 = newRos.createCell(2);
                cell2.setCellValue(userDto.getSnowflake());

                i++;
            }

        }

        log.info("excel success ");

        return workbook;
    }

发送邮件

配置，这块还是不要硬编码，我也不能老守着这块，万一离职了呐，到时候换个email就可了

private void sendMail(HSSFWorkbook workbook, String reviveEmail) throws Exception {

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        baos.flush();
        byte[] bt = baos.toByteArray();
        InputStream is = new ByteArrayInputStream(bt, 0, bt.length);
        baos.close();

        Session session = getSession();//获得回话

        Message msg = new MimeMessage(session);//获取邮件
        msg.setSubject("女神活动排行");//主题
        msg.setFrom(new InternetAddress(formMail));//设置发件人（必须与授权地址一致）
        msg.setRecipient(Message.RecipientType.TO, new InternetAddress(reviveEmail));

        MimeMultipart multipart = new MimeMultipart();

        BodyPart filebodypart = new MimeBodyPart();
        DataSource source = new ByteArrayDataSource(is, "application/msexcel");
        filebodypart.setDataHandler(new DataHandler(source));
        filebodypart.setFileName(MimeUtility.encodeText("goddessRankInfo.xls"));//设置附件名
        multipart.addBodyPart(filebodypart);

        BodyPart textbodypart = new MimeBodyPart();
        textbodypart.setText("请查看附件");
        multipart.addBodyPart(textbodypart);
        msg.setContent(multipart);

        Transport trans = session.getTransport();
        trans.connect(mailUser, mailPwd);//登陆邮箱
        trans.sendMessage(msg, msg.getAllRecipients());
        trans.close();

    }

    private Session getSession() {
        Properties props = new Properties();
        props.put("xmail.transport.protocol", "smtp");//链接协议
        props.put("mail.smtp.host", "smtp.exmail.qq.com");//主机名	smtp.exmail.qq.com：腾讯企业邮
        props.put("mail.smtp.port", 465);//端口号
        props.put("mail.smtp.auth", "true");
        props.put("mail.smtp.starttls.enable", true);
        props.put("mail.smtp.ssl.enable", "true");//使用ssl安全链接
        props.put("mail.debug", "true");//控制台打印debug信息
        return Session.getInstance(props);//获得回话
    }

万一失败的话，发个邮件告知一下

  private void sendAlarm() {
        SimpleMailMessage message = new SimpleMailMessage();
        message.setFrom(formMail);
        message.setTo(toMail);
        message.setSubject("error goddess Rank");
        message.setText("error ");
        mailSender.send(message);
    }

这块业务业务的原因设置了分布式锁，当然锁这块就离题了，大概是这样，谢谢阅读。