直接从数据库中查询数据生成email附件(excel)

算是自己做一个记录和备份吧

直接上代码，生成Excel的。

1.ExcelUtil4

package com.vstrong.utils;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
 
public class ExcelUtil4 {
	
    
	public static void writeToExcel(Workbook wb, OutputStream out){
		try {
			wb.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
    
    
    /**
     * TODO 全新版本    向工作本workbook追加  
     * @param rows
     * @param sheetName
     * @param type
     * @return
     */
    public static SXSSFWorkbook listAddToWorkbook2(SXSSFWorkbook wb,List<?> rows,int rowbegin,String sheetName){
		Row row = null;
		Cell cell = null;
		//对sheet长度名字进行处理
		if(sheetName.length() > 31) {
			sheetName = sheetName.substring(0,31);
		}
		Sheet sh = wb.getSheet(sheetName);
		if(sh==null) {
			sh = wb.createSheet(sheetName);
		}
		
		CellStyle cellStyleIsInteger = wb.createCellStyle();//整数型格式
		CellStyle cellStyleIsNotInteger = wb.createCellStyle();
		CellStyle cellStyleText = wb.createCellStyle();
        cellStyleIsInteger.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,#0"));//数据格式只显示整数
        cellStyleIsNotInteger.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.000"));//保留三位小数点
        cellStyleText.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));//文本格式
		
        if(null!=rows){
        	
			for(int i=0; i<rows.size(); i++){
//				System.err.println(""+i+"========="+DateUtils.getCurrentDatetime()+"=================");
				Object obj = rows.get(i);
				row = sh.createRow(rowbegin++);
				if (obj instanceof Collection) {
					Collection<?> r = (Collection<?>) obj;
					Iterator<?> it = r.iterator();
					int j = 0;
					while(it.hasNext()){
						Object iteratorObejct = it.next();
						
//						cell = row.createCell(j++);
//						cell.setCellStyle(cellStyleText);
//						cell.setCellValue(String.valueOf(iteratorObejct));
						
						
						Boolean isNum = false;//data是否为数值型
		                Boolean isInteger=false;//data是否为整数
		                Boolean isPercent=false;//data是否为百分数
		                if (iteratorObejct != null || "".equals(iteratorObejct)) {
		                    //判断data是否为数值型
		                    isNum = iteratorObejct.toString().matches("^(-?\\d+)(\\.\\d+)?$");
		                    //判断data是否为整数（小数部分是否为0）
		                    isInteger=iteratorObejct.toString().matches("^[-\\+]?[\\d]*$");
		                    //判断data是否为百分数（是否包含“%”）
		                    isPercent=iteratorObejct.toString().contains("%");
		                }
		                cell = row.createCell(j++);
		                //如果单元格内容是数值类型，涉及到金钱（金额、本、利），则设置cell的类型为数值型，设置data的类型为数值类型
		                if (isNum && !isPercent) {
		                    if (isInteger) {
		                    	cell.setCellStyle(cellStyleIsInteger);
		                    	if(String.valueOf(iteratorObejct).length()>10) {
		                    		cell.setCellStyle(cellStyleText);//如果打印10位数字则显示为文本格式
		                    	}
		                    }else{
		                    	cell.setCellStyle(cellStyleIsNotInteger);
		                    }
		                } else {
		                	cell.setCellStyle(cellStyleText);//普通的单元格设置为文本格式
		                }
		                
		                //set CellValue
		                if(iteratorObejct == null ){
//	                    	System.out.println("null");
	                    	cell.setCellValue("");
	                    }else if(iteratorObejct instanceof String ){
//		                	System.out.println("String");
	                    	cell.setCellValue((String)iteratorObejct);
	                    }else if(iteratorObejct instanceof Integer){
//	                    	System.out.println("Integer");
	                    	cell.setCellValue((Integer)iteratorObejct);
	                    }else if(iteratorObejct instanceof Long){
//	                    	System.out.println("Long");
	                    	cell.setCellValue((Long)iteratorObejct);
	                    }else if(iteratorObejct instanceof Double){
//	                    	System.out.println("Double");
	                    	cell.setCellValue((Double)iteratorObejct);
	                    }else if(iteratorObejct instanceof Float){
//	                    	System.out.println("Float");
	                    	cell.setCellValue((Float)iteratorObejct);
	                    }else if(iteratorObejct instanceof Date){
//	                    	System.out.println("Date");
//	                    	cell.setCellValue((Date)iteratorObejct);
	                    	cell.setCellValue(String.valueOf(iteratorObejct));
	                    }else if(iteratorObejct instanceof BigDecimal){
//	                    	System.out.println("BigDecimal");
	                    	cell.setCellValue(((BigDecimal)iteratorObejct).doubleValue());
	                    }else {
	                    	cell.setCellValue(String.valueOf(iteratorObejct));
	                    }
					}
				}else if(obj instanceof Object[]){
					Object[] r = (Object[]) obj;
					for(int j=0; j<r.length; j++){
						cell = row.createCell(j);
						cell.setCellValue(String.valueOf(r[j]));
					}
				}else{
					cell = row.createCell(i);
					cell.setCellValue(String.valueOf(obj));
				}
			}
		}
		return wb;
	}
}

2.ExcelToMimeBodyPart

package com.vstrong.mailPush;

import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeUtility;
import javax.mail.util.ByteArrayDataSource;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.vstrong.i18n.MessageResource;
import com.vstrong.service.ICommonService;
import com.vstrong.service.IExcelService;
import com.vstrong.utils.DateUtils;
import com.vstrong.utils.ExcelUtil4;

public class ExcelToMimeBodyPart {

	// 大于100W行的数据暂不支持处理
	final static Integer MAXROWS = 1000000;

	/**
	 * @title: excelToStream
	 * @Description: 从数据库查询数据生成excel流
	 * @param DoCount
	 * @param GetFields
	 * @param tblName
	 * @param strOrder
	 * @param orderType
	 * @param strWhere
	 * @return
	 * @author kaixin
	 * @throws Exception 
	 * @throws Exception 将异常抛出
	 */
	@SuppressWarnings("resource")
	public static MimeBodyPart  excelToMimeBodyPart(int DoCount, String GetFields,
			String tblName, String strOrder, String orderType, String strWhere,String bosFileName) throws Exception{
		
		IExcelService excelService = (IExcelService) com.vstrong.utils.MySpringContext.getContext().getBean("excelService");
		ICommonService commonService = (ICommonService) com.vstrong.utils.MySpringContext.getContext().getBean("commonService");
		
		SXSSFWorkbook wb = new SXSSFWorkbook(500);
		ByteArrayOutputStream bos = new ByteArrayOutputStream();
		try {
			// 计算导出的有多少行
			Map<String, Object> map = new HashMap<>();
			
			//修改为导出本仓库的数据
			if(GetFields.contains("whouseno")) {
				strWhere += " and whouseno = '1000'";
			}
			map.put("DoCount", DoCount);
			map.put("GetFields", GetFields);
			map.put("tblName", tblName);
			map.put("strOrder", strOrder);
			map.put("orderType", orderType);
			map.put("strWhere", strWhere);
			map.put("TotCount", 0);
			// 计算行数 大于1000000行暂时不处理了
			excelService.getTotCount(map);

			// 数据行数
			Long execlRowsCount = (Long) map.get("TotCount");
			
			// filename
			String filename ="仓库编号_1000"+ "_";
			filename += DateUtils.getCurrentDate();// 所有导出文件添加日期
			
//			if (execlRowsCount > MAXROWS) {
//				filename = "数据行大于" + MAXROWS + ",导出信息失败";
//				// 暂时这样处理
//				ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
//				return byteArrayOutputStream;
//			}
			// 查询开始
			int pagesize = 10000;
			long cishu = execlRowsCount % pagesize == 0 ? execlRowsCount / pagesize : (execlRowsCount / pagesize) + 1;
			System.err.println(cishu + "=====需要查询的次数======");
			HashMap<String, Object> params = new HashMap<>();
			// map put相关参数

			params.put("PageSize", pagesize);
			params.put("DoCount", 1);
			params.put("GetFields", GetFields);
			params.put("tbName", tblName);
			params.put("strOrder", strOrder);
			params.put("orderType", orderType);
			params.put("strWhere", strWhere);
			params.put("TotCount", 1);

			String[] titles = GetFields.replaceAll("\\s*", "").split(",");
			List<String[]> header = new ArrayList<String[]>();
			String[] headerArr = new String[titles.length];
			int headerIndex = 0;
			for (String s : titles) {
				MessageResource messageResource = com.vstrong.utils.MySpringContext.getContext().getBean(MessageResource.class);
				if(s.contains("[")&&s.contains("]")) {
					s = s.substring(1, s.length()-1);
				}
				String f = messageResource.getText(s, Locale.getDefault());//默认使用中文字段
				headerArr[headerIndex++] = f;
			}
			// 向工作区写入表头
			header.add(headerArr);
			wb = ExcelUtil4.listAddToWorkbook2(wb, header, 0, filename);
			
//			System.err.println("=====处理开始时间=========" + DateUtils.getCurrentDatetime() + "=================");
			// 开始写入excel内容
			for (int i = 1; i < cishu + 1; i++) {
				params.put("PageIndex", i);
				// System.err.println(i+"==========="+DateUtils.getCurrentDatetime());
				List<HashMap<String, Object>> listTemp = (List<HashMap<String, Object>>) commonService
						.selectByPage(params);
				System.err.println(i + "=======行数" + listTemp.size());
				List<Object> rows = new ArrayList<Object>();
				for (Map<String, Object> m : listTemp) {
					List<Object> row = new ArrayList<Object>(header.size());
					for (String s : titles) {
						row.add(m.get(s));
					}
					rows.add(row);
				}
				wb = ExcelUtil4.listAddToWorkbook2(wb, rows, (i - 1) * pagesize + 1, filename);
			}
			
			wb.write(bos);
			
			//生成 MimeBodyPart
			MimeBodyPart excelBodyPart = new MimeBodyPart();
	        DataSource dataSource = new ByteArrayDataSource(bos.toByteArray(), "application/vnd.ms-excel");
	        DataHandler dataHandler = new DataHandler(dataSource);
	        excelBodyPart.setDataHandler(dataHandler);
	        bosFileName = (bosFileName!=null&&!"".equals(bosFileName))?bosFileName:"未命名推送文件.xlsx";
	        excelBodyPart.setFileName(MimeUtility.encodeText(bosFileName));
	        
	        return excelBodyPart;
		}catch(Exception e) {
			throw new Exception(e);
		}finally {
			if(wb != null) {
				wb.close();
			}
			if(bos != null) {
				bos.close();
			}
			
		}
	}
}

3.邮件发送的

package com.vstrong.mailPush;

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.mail.MessagingException;
import javax.mail.internet.AddressException;
import javax.mail.internet.MimeBodyPart;

import com.vstrong.pojo.QualityCommissioner;
import com.vstrong.utils.MailSenderFactory;
import com.vstrong.utils.SelectAllUtil;

public class UnqualityProductMailPush {

	public static void  pushMailByQualityuserid(QualityCommissioner qualityCommissioner) throws Exception{
		
		boolean success = true;
		
		try {
			// 1.收件人
			List<String> recipients = new ArrayList<>();
			String recipient = qualityCommissioner.getEmail();
			recipients.add(recipient);
			// 2.抄送人
			List<String> copyto = new ArrayList<>();
//			copyto.add("ruijie_li@vstrong.com");
			// 3.主题
			String subject = "【不合格品推送邮件】";
			// 4.内容
			Object content = "尊敬的"+qualityCommissioner.getCommissionername()+"：<br/>&nbsp;&nbsp;您好！<br/>"
					+ "&nbsp;&nbsp;此邮件为系统定时发送不合格品推送邮件，详情请查询附件。"
					+ "<br/>&nbsp;&nbsp;<red>此邮件为系统自动发送，请勿回复。(This mail is sent by system automatically, please do not reply it.)</red>"
					+ new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
			// 5.文件附件,暂无此类型文件，可以不输入
			String[] fileList = new String[1];
			// fileList[0] = "E:\\WeChat Files\\WeChat
			// Files\\xkx_xinwu\\FileStorage\\File\\2019-10\\阿波罗伸缩机-产品图册.pdf";
			// 6.文件流附件
			MimeBodyPart excelBodyPart = ExcelToMimeBodyPart.excelToMimeBodyPart(1,
					"qualityuserid, commissionername, email, storeid, works, whouseno, sectn, areano, addreno, palno, matno, batchno, " 
					+"productdate, validate, color, szno, price, qcstatus, quant, storeflag, sjdate, sjopera, indate, yhdate, yhopera, yhresult,"
					+"frozenstatu, frozenreason, frozenopera, frozendate, fnsysstatu, fnsysreason, fnsysdate, thawreason, thawopera,"
					+"thawdate, lastpdtime, lastpdopera, lastpdresult, [into], issueto, movieid, asnid, lotid, moveid",
					"view_store_unqualified", "indate", "asc", "", "不合格品邮件推送.xlsx");

			List<MimeBodyPart> excelBodyParts = new ArrayList<MimeBodyPart>();
			excelBodyParts.add(excelBodyPart);
			// 7.发送
			MailSenderFactory.getAttachFilesSender().sendAttachFiles(recipients, copyto, subject, content, fileList,
					excelBodyParts);

		} catch (IOException e) {
			e.printStackTrace();
			success = false;
		} catch (AddressException e) {
			e.printStackTrace();
			success = false;
		} catch (MessagingException e) {
			e.printStackTrace();
			success = false;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			success = false;
		}
		System.out.print("邮件发送结果:" + (success ? "成功" : "失败"));
	}
}