データ転送EXCEL後に直接ストリーム形式でメールを送信する方式(ファイルが着地しない)(java転送excelを完了するUtilクラス)

68011 ワード

リーダーは最近、定時タスクを要求し、メールを送信するタスクを渡しました.
具体的には、指定データベースライブラリから指定データを検出してExcelテーブルに入れ、最後に指定メールボックスに送信するように要求しますが、ファイルが着地しないように要求します.JAvaでデータをストリームからexcelファイルに変換できるのはapacheが開発したpoiとnet.の2つのパッケージです.sourceforge.Jexcelapiは、韓国人が開発したバッグだそうです.私はできるだけ2種類のバッグを書きます.会社の要求でpoiを使っています.私はまずpoiでこの変換を実現しました.表のスタイルの内容は設定されていません.poi Excelセルスタイルの設定:https://www.cnblogs.com/linkstar/p/5910916.html具体的なコードはこのお兄さんの考えを参考にしました.https://blog.csdn.net/yixin605691235/article/details/82429156具体的には以下の(poi実装):データ転送Excelツールクラス:
package com.www.kx;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class Data2Excel {

	/**
	 *    Excel  
	 * 
	 * @param list
	 *                 
	 *            			   
	 * @param sheetSize
	 *                  
	 * @param request
	 *                   
	 * @param response
	 *                   
	 * @param fileName
	 *               
	 * @param sheetName
	 *                 
	 * @param title
	 *                
	 *            key-        javabean           
	 *            	                   ,        , userName ,          , student.department.name 
	 *            value-     excel       
	 */
	public static void list2ExcelFile(List<T> list, int sheetSize, HttpServletRequest request,
			HttpServletResponse response, String fileName, String sheetName, LinkedHashMap<String, String> title) {
		OutputStream outputStream = null;
		//     list       
		if (list == null || list.size() == 0) {
			//         0   return       
			return;
			// throw new Exception("             ");
		}
		try {
			//         
			setResp(request, response, fileName);
			outputStream = response.getOutputStream();
			Workbook wb = new HSSFWorkbook();
			Sheet sheet = wb.createSheet(sheetName);
			fillVeticalSheet(sheet, list, title);
			wb.write(outputStream);
			if (wb != null) {
				wb.close();
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if (outputStream != null) {
				try {
					outputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 *     
	 * 
	 * @param list
	 *                 
	 * @param sheetSize
	 *                  
	 * @param request
	 *                   
	 * @param response
	 *                   
	 * @param fileName
	 *               
	 * @param sheetName
	 *                 
	 * @param title
	 *                
	 *            key-        javabean           
	 *            	                   ,        , userName ,          , student.department.name 
	 *            value-     excel       
	 * @return ByteArrayInputStream      
	 * @throws Exception
	 *                   
	 */
	public static ByteArrayInputStream list2EcelStream(List<T> list, int sheetSize,
			/* HttpServletRequest request,HttpServletResponse response, */String fileName, String sheetName,
			LinkedHashMap<String, String> title) throws Exception {
		byte[] data = null;
		ByteArrayOutputStream out = null;
		ByteArrayInputStream in = null;
		//     list       
		if (list == null || list.size() == 0) {
			//         0   return       
			// return;
			throw new Exception("             ");
		}
		try {
			//         
			/*
			 * setResp(request,response,fileName); outputStream =
			 * response.getOutputStream();
			 */
			out = new ByteArrayOutputStream();
			Workbook wb = new HSSFWorkbook();
			Sheet sheet = wb.createSheet(sheetName);
			fillVeticalSheet(sheet, list, title);
			wb.write(out);
			data = out.toByteArray();
			in = new ByteArrayInputStream(data);
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if (out != null) {
				try {
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return in;
	}

	/**
	 *      Excel  
	 * 
	 * @param list
	 *                      map   key-value    key       key    
	 * @param sheetSize
	 *                  
	 * @param request
	 *                   
	 * @param response
	 *                   
	 * @param fileName
	 *               
	 * @param sheetName
	 *                 
	 * @param title
	 *                
	 *            key-        javabean           
	 *            	                   ,        , userName ,          , student.department.name 
	 *            value-     excel       
	 */
	public static void map2ExcelFile(List<Map<String, Object>> list, int sheetSize, HttpServletRequest request,
			HttpServletResponse response, String fileName, String sheetName, LinkedHashMap<String, String> title) {
		OutputStream outputStream = null;
		//     list       
		if (list == null || list.size() == 0) {
			//         0   return       
			return;
			// throw new Exception("             ");
		}
		try {
			//         
			setResp(request, response, fileName);
			outputStream = response.getOutputStream();
			Workbook wb = new HSSFWorkbook();
			Sheet sheet = wb.createSheet(sheetName);
			fillVeticalSheet(sheet, list, title);
			wb.write(outputStream);
			if (wb != null) {
				wb.close();
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if (outputStream != null) {
				try {
					outputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 *      Excel  
	 * 
	 * @param list
	 *                      map   key-value    key     key      
	 * @param sheetSize
	 *                  
	 * @param request
	 *                   
	 * @param response
	 *                   
	 * @param fileName
	 *               
	 * @param sheetName
	 *                 
	 * @param title
	 *                
	 *            key-        javabean           
	 *            	                   ,        , userName ,          , student.department.name 
	 *            value-     excel       
	 * @return ByteArrayInputStream 
	 * 			        
	 * @throws Exception
	 *                  
	 */
	public static ByteArrayInputStream map2ExcelSteam(List<Map<String, Object>> list, int sheetSize,
			/* HttpServletRequest request,HttpServletResponse response, */String fileName, String sheetName,
			LinkedHashMap<String, String> title) throws Exception {
		byte[] data = null;
		ByteArrayOutputStream out = null;
		ByteArrayInputStream in = null;
		//     list       
		if (list == null || list.size() == 0) {
			//         0   return       
			// return;
			throw new Exception("             ");
		}
		try {
			//         
			/*
			 * setResp(request,response,fileName); outputStream =
			 * response.getOutputStream();
			 */
			out = new ByteArrayOutputStream();
			Workbook wb = new HSSFWorkbook();
			Sheet sheet = wb.createSheet(sheetName);
			fillVeticalSheet(sheet, list, title);
			wb.write(out);
			data = out.toByteArray();
			in = new ByteArrayInputStream(data);
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if (out != null) {
				try {
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return in;
	}

	/**
	 *            
	 * 
	 * @param request
	 * @param response
	 * @param fileName
	 *               
	 */
	private static void setResp(HttpServletRequest request, HttpServletResponse response, String fileName) {
		//           ,          
		final String userAgent = request.getHeader("USER-AGENT");
		response.reset();
		// response.setContentType("application/vnd.ms-excel");
		response.setContentType("application/x-msdownload");
		try {
			String finalFileName = null;
			if (StringUtils.contains(userAgent, "MSIE")) {// IE
				finalFileName = URLEncoder.encode(fileName, "UTF8");
			} else if (StringUtils.contains(userAgent, "Mozilla")) {// chrome firefox
				finalFileName = new String(fileName.getBytes(), "ISO8859-1");
			} else {
				finalFileName = URLEncoder.encode(fileName, "UTF8");
			}
			String dataStr = new SimpleDateFormat("yyyMMddHHmmss").format(new Date()).toString();
			//          ,             
			response.setHeader("COntent-Disposition", "attachment; filename=" + finalFileName + "_" + dataStr + ".xls");
		} catch (UnsupportedEncodingException e) {//     
			e.printStackTrace();
		}
	}

	/**
	 *        
	 * 
	 * @param sheet
	 *                 
	 * @param list
	 *                 list
	 * @param title
	 *                       
	 */
	private static void fillVeticalSheet(Sheet sheet, List<T> list, LinkedHashMap<String, String> title) {
		if (null == list || null == title) {
			return;
		}
		//                    
		String[] enFields = new String[title.size()];
		String[] cnFields = new String[title.size()];
		//     
		int count = 0;
		for (Entry<String, String> entry : title.entrySet()) {
			enFields[count] = entry.getKey();
			cnFields[count] = entry.getValue();
			count++;
		}
		//     
		Row row = sheet.createRow(0);
		for (int j = 0; j < cnFields.length; j++) {
			Cell cell = row.createCell(j);
			cell.setCellValue(cnFields[j]);
		}
		//     
		for (int i = 0; i < list.size(); i++) {
			T item = list.get(i);
			row = sheet.createRow(i + 1);
			for (int j = 0; j < enFields.length; j++) {
				Object objValue = getFieldValueByNameSequence(enFields[j], item);
				if (objValue instanceof String) {
					row.createCell(j).setCellValue((String) objValue);
				} else if (objValue instanceof Double) {
					row.createCell(j).setCellValue((Double) objValue);
				} else if (objValue instanceof RichTextString) {
					row.createCell(j).setCellValue((RichTextString) objValue);
				} else if (objValue instanceof Date) {
					row.createCell(j).setCellValue((Date) objValue);
				} else if (objValue instanceof Boolean) {
					row.createCell(j).setCellValue((Boolean) objValue);
				}
			}
		}
	}

	/**
	 *        
	 * 
	 * @param sheet
	 *                 
	 * @param list
	 *              list   map    Key alue     
	 * @param title
	 *                       
	 */
	private static void fillVeticalSheet(Sheet sheet, List<Map<String, Object>> list, Map<String, String> title) {
		if (null == list) {
			return;
		}
		//   list            
		String[] enFields = new String[title.size()];
		String[] cnFields = new String[title.size()];
		int count = 0;
		for (Entry<String, String> entry : title.entrySet()) {
			enFields[count] = entry.getKey();
			cnFields[count] = entry.getValue();
			count++;
		}
		//     
		Row row = sheet.createRow(0);
		for (int j = 0; j < cnFields.length; j++) {
			Cell cell = row.createCell(j);
			cell.setCellValue(cnFields[j]);
		}
		//     
		int i = 1;
		for (Map<String, Object> map : list) {
			row = sheet.createRow(i + 1);
			for (int j = 0; j < cnFields.length; j++) {
				Object objValue = map.get(enFields[j]);
				if (objValue instanceof String) {
					row.createCell(j).setCellValue((String) objValue);
				} else if (objValue instanceof Double) {
					row.createCell(j).setCellValue((Double) objValue);
				} else if (objValue instanceof RichTextString) {
					row.createCell(j).setCellValue((RichTextString) objValue);
				} else if (objValue instanceof Date) {
					row.createCell(j).setCellValue((Date) objValue);
				} else if (objValue instanceof Boolean) {
					row.createCell(j).setCellValue((Boolean) objValue);
				}
			}
		}
	}

	/**
	 *                    ,        , userName ,          , student.department.
     * name 
	 * 
	 * @param fieldNameSequence
	 *               
	 * @param o
	 *              
	 * @return     
	 */
	private static Object getFieldValueByNameSequence(String fieldNameSequence, Object o) {

		Object value = null;
		try {
			//  fieldNameSequence    
			String[] attributes = fieldNameSequence.split("\\.");
			if (attributes.length == 1) {
				value = PropertyUtils.getProperty(o, fieldNameSequence);

			} else {
				//            
				Object fieldObj = PropertyUtils.getProperty(o, attributes[0]);

				String subFieldNameSequence = fieldNameSequence.substring(fieldNameSequence.indexOf(".") + 1);
				value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
			}

			if (value instanceof Date) {
				value = new SimpleDateFormat("yyyy-MM-dd").format(value).toString();
			}

			// if (value.toString().endsWith(".0000")) {
			// String txt =value.toString();
			// value = txt.substring(0, txt.lastIndexOf("."));
			// }
		} catch (Exception e) {
			e.printStackTrace();
		}
		return value;
	}
}