根据xlsx模板生成excel数据文件发送邮件代码

package mail;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Calendar;

import java.util.Date;

import java.util.List;

import java.util.Map;

import javax.mail.internet.MimeUtility;

import org.apache.commons.mail.EmailAttachment;

import org.apache.commons.mail.MultiPartEmail;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**

 * @comments

 * @author  xxx

 * @version 创建时间：2014年6月15日

 * 将该类从单实例,变成了 Spring 管理的类

 */

public class EmailService {

    private final static String EMAIL_APP_TYPE = "email";

//    private static final String INSURANCE_EMAIL_TYPE="insurance_email";

    private final static String HOST_NAME_ITEM_NAME = "server_host";

    private final static String FROM_ADDR_ITEM_NAME = "from_addr";

    private final static String FROM_NAME_ITEM_NAME = "from_name";

    private final static String PASSWORD_ITEM_NAME = "password";

//    private final static String TO_EMAIL_ITEM_NAME = "to_email";  //发送对象。

    private static final String CPIC_TEMPLET_FILE = "C:\\hzCoupon2.xlsx";  //模板文件

    public static String getHostName() {

        return "smtp.***.***.com";

    }

    public static String getPassword() {

        return "pwd**";

    }

    public static String getFromAddr() {

        return "service@****.com";

    }

    public static String getFromName() {

        return "****Service";

    }

    /**

     * 入口

     * @param list

     * @throws Exception

     */

    public static boolean sendPolicyEmail(String toEmail,List<HzCouponModel> list) throws Exception{

        boolean result = false;

//        String currTime = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date());//System.currentTimeMillis();

        Calendar c = Calendar.getInstance();

        c.add(Calendar.DAY_OF_MONTH, -1);  //往前一天

        String yesterday = "-test-";  //xxxx年xx月xx日

        String cpicFileName = "hzCoupon" + yesterday + ".xls";

        try {

            String fileNames = createHzCouponXLS(list, cpicFileName);

            if(fileNames != null && !"".equals(fileNames)){

                sendEmailWithAttch(toEmail,fileNames);

                result = true;

            }

        } catch (Exception e) {

            result = false;

            e.printStackTrace();

            throw e;

        }

        return result;

    }

    private static void sendEmailWithAttch(String toEmail, String cpicNamePath) throws Exception{

        String yesterday = "-test-";

        EmailAttachment insurAttach = getAttachment("***保数据"+yesterday+".xlsx", cpicNamePath, "***保数据(**提供)");

        Calendar c = Calendar.getInstance();

        c.add(Calendar.DAY_OF_MONTH, -1);  //往前一天

//        String yesterday = CommonConstants.DATE_YEAR_MONTH_DAY_CN_FORMAT(c.getTime());  //xxxx年xx月xx日

        String res1 = sendAttchEmail(toEmail, "***保数据"+yesterday+"明细", "你好，附件是"+yesterday+"***保数据表格，请查收。", insurAttach);

        System.out.println("发送“***资格数据” email res:{}"+ res1);

    }

    private static EmailAttachment getAttachment(String attachName, String fileNamePath, String desc) throws Exception{

        EmailAttachment attachment = new EmailAttachment();

        attachment.setPath(fileNamePath);

        attachment.setDisposition(EmailAttachment.ATTACHMENT);

        attachment.setName(MimeUtility.encodeText(attachName));

        attachment.setDescription(desc);

        return attachment;

    }

    private static String sendAttchEmail(String to, String subject, String content, EmailAttachment attachment) throws Exception{

        MultiPartEmail  email = new MultiPartEmail();

        email.setHostName(getHostName());

        email.setAuthentication(getFromAddr(), getPassword());

        email.setFrom(getFromAddr(), getFromName());

        email.addTo(to);

        email.setSubject(subject);

        email.setMsg(content);

        email.attach(attachment);

        email.addHeader("Disposition-Notification-To", getFromAddr());//邮件回执

        return email.send();

    }

    /**

     * 获取模板文件流

     * @return

     */

    private static InputStream getCpicTemplet(){

//        return CpicCouponEmailService.class.getResourceAsStream(CPIC_TEMPLET_FILE);

        //文件流

        InputStream in = null;

        File f = new File(CPIC_TEMPLET_FILE);

        try {

            in = new FileInputStream(f);

        } catch (FileNotFoundException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return in;

    }

    @SuppressWarnings("unchecked")

    private static String createHzCouponXLS(Object data, String cpicFileName) throws Exception{

        InputStream assIn = null;

        FileOutputStream assOut = null;

        try {

            //------------------------------------------------------------------------------------------------------------

            assIn = getCpicTemplet();//InsuranceUtils.class.getResourceAsStream("/conf/resource/Roadside-Assistance.xlsx");

            XSSFWorkbook assWb = new XSSFWorkbook(assIn);//XSSFWorkbook assWb = (XSSFWorkbook) WorkbookFactory.create(assIn);

            XSSFSheet assSheet = assWb.getSheetAt(0);

//            String currDate = new SimpleDateFormat("yyyy/MM/dd").format(new Date());

            if(data instanceof List){

                List<HzCouponModel> list = (List<HzCouponModel>) data;

                for (int i = 0; i < list.size() ; i++) {

                    HzCouponModel model = list.get(i);

                    setData(i, model, assWb, assSheet);

                }

            }else if(data instanceof Map){

                HzCouponModel model =  (HzCouponModel) data;

                setData(0, model, assWb, assSheet);

            }else{

                return null;

            }

//            String insurPolicyNamePath = File.createTempFile(cpicFileName,".xlsx").getPath(); //临时文件

            String insurPolicyNamePath = "C:/save/"+cpicFileName;

            assOut = new FileOutputStream(insurPolicyNamePath);

            assWb.write(assOut);//写入Excel文件，这才是关键。

            System.out.println("创建“***保数据”Excel文件:{}" + insurPolicyNamePath);

            return insurPolicyNamePath;

        } catch (Exception e) {

            e.printStackTrace();

            throw e;

        }finally{

            if(assOut != null){

                assOut.close();

            }

            if(assIn != null){

                assIn.close();

            }

        }

    }

    /**

     * 数据填充

     * @param idx

     * @param currDate

     * @param map

     * @param insruWb

     * @param insurSheet

     * @param assWb

     * @param assSheet

     * @throws Exception

     */

    private static void setData(int idx, HzCouponModel model, XSSFWorkbook assWb, XSSFSheet assSheet) throws Exception{

        XSSFRow assRow = createRowAndCel(assWb, assSheet, idx+1, 12);//assSheet.getRow(i+1);//可能为null

        assRow.getCell(0).setCellValue(model.getCreateTime());//

        assRow.getCell(1).setCellValue(model.getPartner());//

        assRow.getCell(2).setCellValue(model.getStartPolicyDate());//

        assRow.getCell(3).setCellValue(model.getFiliale());//

        assRow.getCell(4).setCellValue(model.getBranch());//

        assRow.getCell(5).setCellValue(model.getPolicyMobile());//

        assRow.getCell(6).setCellValue(model.getInsuredMobile());//

        assRow.getCell(7).setCellValue(model.getBrandType());//

        assRow.getCell(8).setCellValue(model.getPlateNum());//

        assRow.getCell(9).setCellValue(model.getFrameNo());//

        assRow.getCell(10).setCellValue(model.getCouponType());//

        assRow.getCell(11).setCellValue(model.getInsuredNumber());//

    }

    /**

     * 创建Excel的行(row)和列(cel)

     * @param sheet

     * @param rowIdx

     * @param cellNum

     */

    private static XSSFRow createRowAndCel(XSSFWorkbook wb, XSSFSheet sheet, int rowIdx, int cellNum){

        XSSFCellStyle cellStyle = wb.createCellStyle();//创建cell样式

        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//设置右边框

        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//设置下边框

        sheet.createRow(rowIdx);

        XSSFRow row = sheet.getRow(rowIdx);

        for (int i = 0; i < cellNum; i++) {

            XSSFCell cell = row.createCell(i);

            cell.setCellStyle(cellStyle);

        }

        return row;

    }

    //错误: 找不到或无法加载主类 testJava2.email.CpicCouponEmailService

    public static void main(String[] args) throws Exception{

        List<HzCouponModel> list = new ArrayList<HzCouponModel>();

        HzCouponModel model = new HzCouponModel("2017-06-04", "*保", "2017-06-06", "sz分公司", "电子商务部门", "137****1198", "137****1199", "奥迪A8", "沪A***78", "WFSFJFKSJFa7844", "经济型套餐", "3100245456");

        list.add(model);

        model = new HzCouponModel("2017-06-04", "*安", "2017-06-08", "sz分公司", "电子商务部门", "137****2298", "137****2299", "奥迪A8", "沪A**78", "WFSFJFKSJFa7844", "豪华型套餐", "3100245456");

        list.add(model);

        sendPolicyEmail("jhuang.sjtu@qq.com", list);

    }

}
 

1.异常处理

Exception in thread "main" org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException: The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg HSSF instead of XSSF)

原因是：
HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls 
XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx

2.NoClassDefFoundError: org/openxmlformats/schemas/drawingml/x2006/main/ThemeDocument

解决方法：添加poi-ooxml-schemas-3.12-20150511.jar

3. javax.mail.NoSuchProviderException: smtp 报错

下了最新的mailapi  1.4.7  版本,  把原来的那两个jar(activation-1.1.1.jar  和 mailapi-1.4.3 jar)从classpath中去掉， 把1.4.7 中lib全部加到classpath， 然后就发送成功了。

根据xlsx模板生成excel数据文件发送邮件代码的相关教程结束。