java定时任务：oracle导出excel后，发送excel作为附件的邮件

定时任务类：

package com.cairenhui.sec.task;import java.io.File;import java.io.FileOutputStream;import java.io.OutputStream;import java.io.UnsupportedEncodingException;import java.net.URLEncoder;import java.util.Date;import java.util.List;import java.util.Map;import javax.mail.internet.MimeMessage;import org.apache.log4j.Logger;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.core.io.FileSystemResource;import org.springframework.mail.javamail.JavaMailSenderImpl;import org.springframework.mail.javamail.MimeMessageHelper;import org.springframework.stereotype.Component;import com.cairenhui.sec.service.ObsUserHkInfoService;import com.cairenhui.sec.util.ExcelUtil;import com.cairenhui.sec.utils.PropertiesUtils;/** *  * @author jinxd 2014-12-29 从数据库中导出excel，然后调用邮件发送接口，发送邮件 */@Componentpublic class HKTask {@Autowiredprivate ObsUserHkInfoService obsUserHkInfoService;@Autowiredprivate JavaMailSenderImpl mailSender;private static final Logger log = Logger.getLogger(HKTask.class);/** * 港股通业务，通过OBS_USER_HK_INFO表，每天23:55导出excel，并发送邮件 */public void sendHKInfoEmailJob() {long t1 = System.currentTimeMillis();log.info("--------------  执行港股通定时任务 start --------------------" + t1);try {@SuppressWarnings("unchecked")List<Map<String, Object>> list = obsUserHkInfoService.findTheSameDay();// 查询数据if (list.size() > 0) {StringBuilder fileName = new StringBuilder();String name = "HKStockCount_" + (new Date()).getTime();// 表格名字String encoderName = "";try {encoderName = URLEncoder.encode(name, "UTF-8");} catch (UnsupportedEncodingException e) {e.printStackTrace();}fileName.append(encoderName).append(".xls");// 建立excel表格OutputStream os = new FileOutputStream("c:\\temp\\" + fileName);// linux和windows路径不一样String[] titleArr = new String[] { "编号", "用户ID", "姓名", "客户资金账号", "客户风险等级", "知识测评分数", "指定交易", "沪A账号", "账户资产金额", "创建时间", "完成时间", "处理意见", "处理状态(4成功，5失败)", "备注" };String[] codeArr = new String[] { "OUHI_ID", "USER_ID", "CLIENT_NAME", "FUND_ACCOUNT", "CORP_RISK_LEVEL", "PAPER_SCORE", "REG_FLAG", "STOCK_ACCOUNT_A", "ASSET_BALANCE_VALUE", "CREATE_TIME", "FINISH_TIME", "RESULT_COMMENT", "STATUS", "REMARK" };ExcelUtil.export(os, list, titleArr, codeArr, name);log.info("--------------  导出excel成功,建立临时文件end --------------------" + (System.currentTimeMillis() - t1) + "ms");String sPath = "c:\\temp\\" + fileName.toString();// linux和windows路径不一样String toMail = PropertiesUtils.get("mail.hk.send.to", "");// mail.properties里面配置String fromMail = PropertiesUtils.get("mail.send.from", "");// 是否需要发邮件，根据配置是否为空来判断if (!toMail.isEmpty()) {// 将excel作为附件发送，并删除本地的excel文件String subject = "港股通开户信息";String content = (new Date()).toString() + "_港股通开户信息报表";MimeMessage mailMessage = mailSender.createMimeMessage();MimeMessageHelper messageHelper = new MimeMessageHelper(mailMessage, true, "utf-8");messageHelper.setTo(toMail);// 接收方邮箱messageHelper.setFrom(fromMail);// 发送方邮箱messageHelper.setSubject(subject);// 设置邮件主题messageHelper.setText(content, true);// 设置邮件主题内容FileSystemResource fileR = new FileSystemResource(new File(sPath));// 读取附件// 这里的方法调用和插入图片是不同的。messageHelper.addAttachment("HKStockCount.xls", fileR); // 设置附件log.info("--------------  发送邮件开始  --------------");mailSender.send(mailMessage); // 发送邮件log.info("--------------  发送邮件成功,任务结束 end --------------------" + (System.currentTimeMillis() - t1) + "ms");} else {log.info("-------------- 发件邮箱或收件邮箱未配置,不发送港股通开户信息邮件  --------------------");}File file = new File(sPath);// 路径为文件且不为空则进行删除if (file.isFile() && file.exists()) {file.delete();log.info("--------------  临时文件删除成功 --------------------");}}else{log.info("--------------  当日没有开通港股通信息记录 --------------------");log.info("--------------  港股通定时任务 end ---------------------------");}} catch (Exception e) {log.error("sendHKInfoEmailJob异常:", e);e.printStackTrace();}}}

ExcelUtil.export方法：

<pre name="code" class="java">public static void export(OutputStream os, List<Map<String, Object>> list, String[] titleArr,String[] codeArr, String name){try {//创建一个工作簿，也就是整个文档WritableWorkbook wbook = Workbook.createWorkbook(os);//设置字体WritableFont wfont = new WritableFont(WritableFont.ARIAL, 10,           WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,           Colour.BLACK);//定义单元格格式           WritableCellFormat wcf = new WritableCellFormat(wfont);           double maxSheetSize = 60000.0;           int sheetNum = (int) Math.ceil(list.size() / maxSheetSize);           for(int num = 0; num < sheetNum; num++){           WritableSheet wsheet = wbook.createSheet(name + "("+(num+1)+")", num);                               String title=null;               for(int i=0; i<titleArr.length; i++){               title = titleArr[i];               wsheet.addCell(new Label(i, 0, title, wcf));               }            for(int i = num*(int)maxSheetSize ; i < (num+1)*(int)maxSheetSize && i < list.size(); i++){               Map<String, Object> m = list.get(i);               int temp = i - num*(int)maxSheetSize + 1;               for(int j=0; j<codeArr.length; j++){               String value = String.valueOf(m.get(codeArr[j])==null?"":m.get(codeArr[j]));               wsheet.addCell(new Label(j, temp, value));               }               }           }                      wbook.write();           wbook.close();           os.close();}catch(Exception e){log.info("exception heppended in "+ExcelUtil.class+" cause: ", e);}}

quartz.xml：

<!-- 港股通：定义要使用哪个bean中的哪个方法作为要定时执行的任务 --><bean id="HKInfoSendJob" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean"> <property name="targetObject">     <ref bean="HKTask" />     </property>     <property name="targetMethod">     <value>sendHKInfoEmailJob</value>    </property> </bean><!-- 港股通：触发器的bean的设置 --><bean id="HKInfoSendJobTrigger" class="org.springframework.scheduling.quartz.CronTriggerBean"><property name="jobDetail"><ref bean="HKInfoSendJob" /></property><property name="cronExpression"><value>0 46 09 * * ?</value></property></bean>    <!-- 任务调度工厂 --><!-- 启动定时器，triggers属性接受【一组】触发器 --><bean id="schedulerFactory" autowire="no" class="org.springframework.scheduling.quartz.SchedulerFactoryBean"> <property name="triggers"> <list> <!-- <ref local="channelFinishJobTrigger"/> --><ref local="pollingBusinessJobTrigger"/><ref local="HKInfoSendJobTrigger"/></list> </property> </bean>

配置文件：

####邮件系统参数设置开始#邮件服务器地址mail.server.host=smtp.163.com#邮件服务器端口mail.server.port=25#邮件服务器登录用户名、密码mail.server.username=[e-mail]=05702611054#开户验证身份 true/falsemail.server.auth=true#邮件发送地址mail.send.from=[e-mail]#邮件接收地址mail.hk.send.to=[e-mail]#邮件内容：邮箱绑定mail.content.bind=mail.content.bind####邮件系统参数结束