java配置文件动态配置excel导出动态数据并发送email

package com.kmi.mail.action; import java.io.BufferedReader; import java.io.File; import java.io.IOException; import java.io.InputStreamReader; import java.net.URL; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Properties; import java.util.ResourceBundle; import javax.activation.DataHandler; import javax.activation.FileDataSource; import javax.mail.Address; import javax.mail.Authenticator; import javax.mail.Message; import javax.mail.Multipart; import javax.mail.SendFailedException; import javax.mail.Session; import javax.mail.Transport; import javax.mail.internet.InternetAddress; import javax.mail.internet.MimeBodyPart; import javax.mail.internet.MimeMessage; import javax.mail.internet.MimeMultipart; import javax.mail.internet.MimeUtility; import com.kmi.mail.dao.ModelDao; import com.kmi.mail.entity.Model; import com.kmi.mail.util.JavaReader2DOM; import jxl.Workbook; import jxl.format.Alignment; import jxl.format.Border; import jxl.format.BorderLineStyle; import jxl.format.Colour; import jxl.format.VerticalAlignment; import jxl.write.Label; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import jxl.write.biff.RowsExceededException; /** * 把促销员信息写入excel通过附件发送邮件 * * @author Administrator * */ public class Mail { String resourceFile = "email"; // 资源文件名 ResourceBundle resource = ResourceBundle.getBundle(resourceFile); private static String path = Thread.currentThread().getContextClassLoader() .getResource("").getPath().substring(1); // private String mail_subject = "";// 主题、标题 private String mail_body = "";// 正文 body /** * 发送邮件 */ public void sendMail(List<Model> list,String mail_subject) throws SendFailedException { String host = resource.getString("host"); String mail_from = resource.getString("mail_from"); try { Properties props = new Properties();// 获取系统环境 Authenticator auth = new Email_Authenticator();// 进行邮件服务用户认证 props.put("mail.smtp.host", host); props.put("mail.smtp.auth", "true"); System.out.println("start..." + props); Session session = Session.getDefaultInstance(props, auth); // 设置session,和邮件服务器进行通讯 MimeMessage message = new MimeMessage(session); message.setSentDate(new Date());// 设置邮件发送时期 Address address = new InternetAddress(mail_from); message.setFrom(address);// 设置邮件发送者的地址 String filePath = path + "excel/" + mail_subject + ".xls"; mail_body = getMail_body(""); writeExcel(filePath,list); message.setSubject(mail_subject);// 设置邮件主题 String[] mail_tos = resource.getString("mail_tos").split(",");// 接受者邮箱地址集合 Address[] toaddress = new InternetAddress[mail_tos.length];// 设置邮件接收者的地址 for (int i = 0; i < mail_tos.length; i++) { String mail_to = mail_tos[i];// 接受者邮箱地址 toaddress[i] = new InternetAddress(mail_to); } message.addRecipients(Message.RecipientType.TO, toaddress); Multipart mp = new MimeMultipart(); MimeBodyPart mbp = new MimeBodyPart(); mbp.setContent(mail_body, "text/html;charset=GB2312"); // 正文 FileDataSource fds = new FileDataSource(filePath);// 添加附件 MimeBodyPart mbp2 = new MimeBodyPart(); mbp2.setDataHandler(new DataHandler(fds)); mbp2.setFileName(MimeUtility.encodeWord(fds.getName(), "GB2312", null)); mp.addBodyPart(mbp); mp.addBodyPart(mbp2); message.setContent(mp); message.saveChanges(); Transport.send(message); System.out.println("Send Mail Ok!"); } catch (Exception e) { e.printStackTrace(); } } /** * 生成一个Excel文件 * * @param fileName * 要生成的Excel文件名 */ public void writeExcel(String fileName,List<Model> list) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); WritableWorkbook wwb = null; try { // 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象 wwb = Workbook.createWorkbook(new File(fileName)); } catch (IOException e) { e.printStackTrace(); } if (wwb != null) { for (int k = 0; k < list.size(); k++) { Model m = list.get(k); // 创建一个可写入的工作表 // Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置 WritableSheet ws = wwb.createSheet(m.getSheet_name(), k); try { ws.setRowView(0, 400);// 设置第一行的高度 } catch (RowsExceededException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } WritableCellFormat wcf = getWritableCellFormat(); ModelDao dao = new ModelDao(); //获取单元格显示数据 List dlist = null; if (m.getSql_type().equals("1")) { dlist = dao.getModelList(m.getSql(),Integer.parseInt(m.getColumn())); }else if(m.getSql_type().equals("2")){ dlist = dao.executeSprocInParams(m.getSql(), Integer.parseInt(m.getColumn()), ""); }else{ System.out.println("xml配置出错。"); return; } // 生成表头 String excel_top = m.getExcel_top(); String[] arr = excel_top.split(","); String[] excel_column_type = new String[arr.length];//excel字段的类型 for (int i = 0; i < arr.length; i++) { String[] ar = arr[i].split(":"); String topName = ar[0];//表头名称 String topWidth = ar[1];//表头宽度 String labelType = null;//类型 if (ar.length > 2) { labelType = ar[2]; } excel_column_type[i] = labelType; Label l = new Label(i, 0, topName, wcf); ws.setColumnView(i, Integer.parseInt(topWidth)); // 设置列的宽度 try { ws.addCell(l); } catch (RowsExceededException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (WriteException e) { // TODO Auto-generated catch block e.printStackTrace(); } } // 下面开始添加单元格 for (int i = 1; i < dlist.size() + 1; i++) { List<Object> mlist = (List<Object>) dlist.get(i-1); for (int j = 0; j < mlist.size(); j++) { String s = (String) mlist.get(j); // 这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行 Label labelC = null; if (excel_column_type[j] == null) { labelC = new Label(j, i, s); try { // 将生成的单元格添加到工作表中 ws.addCell(labelC); } catch (RowsExceededException e1) { e1.printStackTrace(); } catch (WriteException e1) { e1.printStackTrace(); } }else{ if (excel_column_type[j].equals("1")) {//文本格式 labelC = new Label(j, i, s); try { // 将生成的单元格添加到工作表中 ws.addCell(labelC); } catch (RowsExceededException e1) { e1.printStackTrace(); } catch (WriteException e1) { e1.printStackTrace(); } }else if(excel_column_type[j].equals("2")){//数字格式 jxl.write.Number ln = new jxl.write.Number(j, i, Integer.parseInt(s)); try { // 将生成的单元格添加到工作表中 ws.addCell(ln); } catch (RowsExceededException e1) { e1.printStackTrace(); } catch (WriteException e1) { e1.printStackTrace(); } }else if(excel_column_type[j].equals("3")){//日期格式 Date date = null; try { date = sdf.parse(s); } catch (ParseException e) { System.out.println("日期格式不正确"); e.printStackTrace(); } jxl.write.DateTime ld = new jxl.write.DateTime(j, i, date); try { // 将生成的单元格添加到工作表中