java代码生成Excl表格然后以附件的形式进行发送，里面的内容是用数据库的信息进行填充的。

首先注意不同的java类生成的EXCL表格会有所不同，例如：

形如前缀为HSS的java类生成的是以.xls结尾的excl表格，就是2003版本以前的。

HSSFWorkbook book = new HSSFWorkbook();
HSSFSheet sheet = book.createSheet();
HSSFRow row = null;
HSSFCell cell = null;

形如前缀为XSS的java类生成的是以.xlsx结尾的excl表格，就是2007版本以后的。

XSSFWorkbook book = new XSSFWorkbook();
XSSFSheet sheet = book.createSheet();
XSSFRow row = null;
XSSFCell cell = null;

1、首先利用jdbc连接到数据库，然后遍历查询的结果，把结果存进一个List集合中方便，之后把数据填充进Excl表格中。

PublicHousingDailyReportDao.java

package com.hthk.iisz.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.hthk.iisz.util.JdbcConn;

public class PublicHousingDailyReportDao implements IPublicHousingDailyReportDao {
	Connection conn;
	PreparedStatement stmt;
	ResultSet rs;

	private void getConnection() throws SQLException {
		conn = conn == null ? JdbcConn.getConn() : conn;
		if (conn.isClosed())
			conn = JdbcConn.getConn();
	}

	@Override
	public List<String[]> getCustInfo() throws SQLException {
		getConnection();
		List<String[]> result = null;
		String sql = "select CUSTOMER,SALUTATION,CUST_NAME,CONTACT_NO,EMAIL,ADDRESS,SERVER_TYPE,IS_ACCEPT from PUBLIC_HOUSING where trunc(crte_date) = trunc(SYSDATE-1)";
		try {
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			String[] info = null;
			while (rs.next()) {
				if (result == null)
					result = new ArrayList<String[]>();
				info = new String[8];
				for (int i = 0; i < 8; i++) {
					info[i] = rs.getString(i + 1) != null ? rs.getString(i + 1)
							: "";
					if (i == 1) {
						info[i] = info[i].split(" ")[0];
					}
				}
				result.add(info);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
		return result;
	}
}

2、生成Excl表格并调用发送邮件的函数以附件的形式进行发送。

PublicHousingDailyReportService.java

package com.hthk.iisz.service;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import javax.mail.MessagingException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.hthk.iisz.dao.IPublicHousingDailyReportDao;
import com.hthk.iisz.dao.IRegTypeDao;
import com.hthk.iisz.dao.PublicHousingDailyReportDao;
import com.hthk.iisz.dao.RegTypeDaoImpl;
import com.hthk.iisz.model.RegType;
import com.hthk.iisz.util.Constant;
import com.hthk.iisz.util.EmailUtil;

public class PublicHousingDailyReportService {

	private final Log log = LogFactory.getLog(getClass());
	public static final String TYPE_APPOINTMENT = "public_housing";
	private static final String FILENAME = "PublicHousingDailyReport.xls";
	private static final String FILENAME_NO_EXT = "PublicHousingDailyReport";
	private IPublicHousingDailyReportDao dao = new PublicHousingDailyReportDao();
	private IRegTypeDao regTypeDao = new RegTypeDaoImpl();

	/** Send daily report of Public_Housing Page */
	@SuppressWarnings("deprecation")
	public void sendReport() {
		log.info("----Start PublicHousingDailyReportTrigger----"
				+ new Date().toLocaleString());
		try {
			// check if need gen report
			RegType regType = regTypeDao.getByType(TYPE_APPOINTMENT);
			if (regType == null || RegType.stat_inactive == regType.getStatus()) {
				log.info("PublicHousing service status is inactive.");
				return;
			}

			// get DB records
			List<String[]> custList = dao.getCustInfo();

			// gen report
			if (custList != null) {
				genReport(custList);
			}

			String[] contents = regType.getEmailContent().split("\n");
			String content = custList != null ? contents[0] : contents[1];

			String sendDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
					.format(new Date());
			content += "\n\n---- Send at: " + sendDate;

			if (custList != null) {
				EmailUtil.sendEmail_publicHousing(regType.getEmailRecipient()
						.split(","), regType.getEmailSubject(), content,
						FILENAME_NO_EXT + "_" + dateTimeStr() + ".xls",
						Constant.dayReportPath + File.separator + FILENAME);
			} else {
				EmailUtil.sendEmail_publicHousing(regType.getEmailRecipient()
						.split(","), regType.getEmailSubject(), content, null,
						null);
			}
		} catch (Exception e) {
			log.error("sendReport():", e);
			reportError(e);
		}

		log.info("----End appointmentDailyReportTrigger----"
				+ new Date().toLocaleString());
	}

	private boolean genReport(List<String[]> custList) throws IOException {
		HSSFWorkbook book = new HSSFWorkbook();
		HSSFSheet sheet = book.createSheet();
		HSSFRow row = null;
		HSSFCell cell = null;
		String liner[] = null;

		// gen header
		row = sheet.createRow(0);
		String[] header = { "CUSTOMER", "SALUTATION", "CUST_NAME",
				"CONTACT_NO", "EMAIL", "ADDRESS", "SERVER_TYPE", "IS_ACCEPT" };
		for (int i = 0; i < header.length; i++) {
			cell = row.createCell(i);
			cell.setCellValue(header[i]);
		}

		// gen body
		for (int i = 0; i < custList.size(); i++) {
			row = sheet.createRow(i + 1);
			liner = custList.get(i);
			for (int j = 0; j < liner.length; j++) {
				cell = row.createCell(j);
				cell.setCellValue(liner[j]);
			}
		}

		// gen file
		FileOutputStream os = null;
		try {
			File file = new File(Constant.dayReportPath);
			if (!file.exists())
				file.mkdirs();

			os = new FileOutputStream(Constant.dayReportPath + File.separator
					+ FILENAME);
			book.write(os);
			return true;
		} catch (FileNotFoundException e) {
			throw new IOException(e);
		} finally {
			if (os != null) {
				os.close();
			}
		}
	}

	private String dateTimeStr() {
		Calendar cal = Calendar.getInstance();
		cal.setTime(new Date((new Date()).getTime() - 1000L * 60L * 60L * 24L
				* 1));
		int year = cal.get(Calendar.YEAR);
		int month = cal.get(Calendar.MONTH) + 1;
		int date = cal.get(Calendar.DATE);

		String yearStr = "";
		String monthStr = "";
		String dateStr = "";

		yearStr = "" + year;
		if (month < 10) {
			monthStr = "0" + month;
		} else {
			monthStr = "" + month;
		}
		if (date < 10) {
			dateStr = "0" + date;
		} else {
			dateStr = "" + date;
		}
		String dateTimeStr = yearStr + monthStr + dateStr;
		return dateTimeStr;
	}

	private void reportError(Exception e) {
		String[] to = { "Cong@qq.com" };
		StringWriter sw = new StringWriter();
		PrintWriter pw = new PrintWriter(sw, true);
		e.printStackTrace(pw);
		pw.flush();
		sw.flush();
		try {
			EmailUtil.sendEmail_publicHousing(to,
					"[ERROR] Public_Housing Daily Report", sw.toString(), null,
					null);
		} catch (UnsupportedEncodingException e1) {
			log.error("reportError()", e1);
		} catch (MessagingException e1) {
			log.error("reportError()", e1);
		}
	}

	public static void main(String[] args) {
		PublicHousingDailyReportService service = new PublicHousingDailyReportService();
		try {
			service.sendReport();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

3、发送邮件的工具类。

EmailUtil.java

package com.hthk.iisz.util;

import java.io.File;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.io.UnsupportedEncodingException;
import java.util.Properties;
import javax.mail.MessagingException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
import org.springframework.mail.javamail.JavaMailSenderImpl;
import org.springframework.mail.javamail.MimeMessageHelper;

public class EmailUtil {

	public static void sendEmail_publicHousing(String[] recipient,
			String subject, String content, String attachName, String attachPath)
			throws MessagingException, UnsupportedEncodingException {

		try {
			JavaMailSenderImpl mailSender = null;
			Properties props = null;
			MimeMessage mimeMessage = null;
			MimeMessageHelper messageHelper = null;

			mailSender = new JavaMailSenderImpl();
			mailSender.setHost(Constant.mailHost);
			mailSender.setUsername(Constant.mailUserName);
			mailSender.setPassword(Constant.mailPassWord);

			props = new Properties();
			props.put("mail.transport.protocol", "smtp");
			props.put("mail.smtp.auth", Constant.mailSmtpAuth);
			props.put("mail.smtp.timeout", Constant.mailSmtpTimeout);

			mailSender.setJavaMailProperties(props);
			mimeMessage = mailSender.createMimeMessage();
			messageHelper = new MimeMessageHelper(mimeMessage, true, "utf-8");

			InternetAddress[] to = new InternetAddress[recipient.length];
			for (int i = 0; i < recipient.length; i++) {
				to[i] = new InternetAddress(recipient[i]);
			}

			InternetAddress sender = new InternetAddress(
					"Public_Housing@qq.com", "",
					"UTF-8");

			messageHelper.setFrom(sender);
			messageHelper.setTo(to);
			messageHelper.setSubject(subject);
			messageHelper.setText(content);

			// 添加附件
			if (attachName != null && attachName.trim().length() > 0
					&& attachPath != null && attachPath.trim().length() > 0) {
				File file = new File(attachPath);
				if (file.exists()) {
					messageHelper.addAttachment(attachName, file);
				}
			}
			mailSender.send(mimeMessage);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void reportError(String subject, Exception e) {
		String[] to = { "Cong@qq.com" };
		subject = "[UAT]" + subject;
		StringWriter sw = new StringWriter();
		PrintWriter pw = new PrintWriter(sw, true);
		e.printStackTrace(pw);
		pw.flush();
		sw.flush();
		try {
			EmailUtil.sendEmail(to, subject, sw.toString(), null, null);
		} catch (UnsupportedEncodingException ignore) {
		} catch (MessagingException ignore) {
		}
	}

}