jsgkj/gkjsjy/trunk/src/com/xtframe/util/ExcelTool.java

package com.xtframe.util;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
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.xt.jygl.gkjygl.cghzyxxgl.dcsb.ctl.DcsbybbCtl;
import com.xt.jygl.gkyxtjyfx.hxhbtj.vo.HxhbtjVo;
import sun.misc.Unsafe;

public class ExcelTool {
	public static int START_ROW = 1;//被复制的开始行
	/**
	 * 将传入数据映射到MAP中
	 * 
	 * @param classType
	 *            传入数据类型
	 * @param dataObj
	 *            传入数据
	 * @return 布尔值 是否复制成功
	 * @throws IOException
	 */
	public static Map reflectForgetData(Object dataObj)
			throws IOException {
		// 输出结果
		Map<String, Object> map = new HashMap();
		try{
			if (dataObj == null) return map;
			// 取得实例
			Class<?> realDataObj = dataObj.getClass();
			// 将所有OBJ内的数据放入map
			//创建此类型的空对象  
			Field fu = Unsafe.class.getDeclaredField("theUnsafe");  
			fu.setAccessible(true);  
			Unsafe us = (Unsafe) fu.get(null);  
			Object newObj = us.allocateInstance(realDataObj);  
			//获取所有成员(包括private)的值,并拷贝到新对象中  
			Field[] fields = realDataObj.getDeclaredFields();  
			for (Field f : fields) {  
				//不拷贝static成员和final成员  
				if (Modifier.isStatic(f.getModifiers()) || Modifier.isFinal(f.getModifiers())) {  
					continue;  
				}  
				f.setAccessible(true);  
				Object fieldValue = f.get(dataObj);  
				f.set(newObj, fieldValue);
				map.put(f.getName(), fieldValue);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return map;
	}
	
	/**
	 * 导出EXCEL
	 * @param request
	 * @param response
	 * @param list 待导出的数据
	 * @param type 导出的类型 1、全省港口集装箱航线航班月度统计    2、xx    3、xx
	 * @throws Exception
	 */
	public static void exportExcel(HttpServletRequest request, HttpServletResponse response,
			List list,int type,String exportFileName)
			throws Exception {
		
		// 选择模板文件：
		String templetFile =  "hxhbtj.xls";
		String fileName = "全省港口集装箱航线航班月度统计.xls";
		if(StringUtils.isNotEmpty(exportFileName)){
			fileName = exportFileName + ".xls";
		}
		if (type==2) {
//			templetFile="gbjlhzb.xls";
//			fileName = "过磅记录汇总表.xls";
		}else if (type==3) {
//			templetFile="cgbjjlb.xls";
//			fileName = "储罐报警记录表.xls";
		}else if (type==4){
//			templetFile="smsdetail.xls";
//			fileName = "信息发送详情.xls";
		}

		fileName = URLEncoder.encode(fileName, "UTF-8");
		response.setContentType("application/msexcel; charset=utf-8");
		response.setHeader("Content-disposition", "attachment; filename=" + fileName);// 设定输出文件头

        InputStream is = null;// 输入流对象  
		try {  
			   
			    // 文件路径
			    //String realpath = File.separator + "template" + File.separator  + templetFile;
	            //is = request.getSession().getServletContext().getResourceAsStream(realpath);// 获取文件输入流  
			    is=new BufferedInputStream(ExcelTool.class.getResourceAsStream("/templete/" + templetFile));
	            HSSFWorkbook workbook2003 = new HSSFWorkbook(is);// 创建Excel2007文件对象  
	            HSSFSheet sheet = workbook2003.getSheetAt(0);// 取出第一个工作表，索引是0 
	            
	            //list数据
	            if (type==1) {
	            	setListInfo(sheet, HxhbtjVo.class, list);
				}else if (type==2) {
					//setListInfo(sheet, GbhzVo.class, list);
				}else if (type==3){
					//setListInfo(sheet, SbbjjlVo.class, list);
				}else if (type==4){
					//setListInfo(sheet, SendHistory.class, list);
				}
				OutputStream bos = response.getOutputStream();
				workbook2003.write(bos);

	            response.flushBuffer();
                bos.flush();
                bos.close();

	        } catch (Exception e) {  
	            e.printStackTrace();  
	        } finally {
	        	// 关闭文件流  
	            if (is != null) {  
	                try {  
	                    is.close();  
	               } catch (IOException e) {  
	                    e.printStackTrace();  
	               }  
	            } 
	        }  
	}
	public static void setListInfo(HSSFSheet sheet, Class classType, List dataList) {
		int END_ROW = START_ROW + dataList.size();
        List mapList;
		try {
			mapList = reflectForgetData(classType, dataList);
		} catch (IOException e) {
			mapList = new ArrayList();
			e.printStackTrace();
		}
        if (mapList == null || mapList.size() == 0) {
        	HSSFRow modRow = sheet.getRow(START_ROW + 1);
        	sheet.removeRow(modRow);
        }
          // 获取模板行对象  
    	  HSSFRow modRow = sheet.getRow(START_ROW + 1);
          for(int j = 1; j < mapList.size(); j++) {
            	// 为各数据对象单位添加行
//        	    sheet.shiftRows(startRow + j + 1, sheet.getLastRowNum(), 1, true, false); 
        	  sheet.shiftRows(START_ROW + j + 1, END_ROW, 1, true, false); 
        		HSSFRow newXSSFRow = sheet.createRow(START_ROW + j + 1);
        		// 把模板里各行的内容复制到新生成的行里
        		for (int k = 0; k < modRow.getLastCellNum(); k++ ) {  
        			HSSFCell modcell = modRow.getCell(k);
        			HSSFCell newcell = newXSSFRow.createCell(k);
        			if(null == modcell) {
        				continue;
        			}
        			// 把模板里的cell相关属性设入新增cell
        			newcell.setCellStyle(modcell.getCellStyle());
        			newcell.setCellType(modcell.getCellType()); 
        			if (null != modcell.getStringCellValue() && !"".equals(modcell.getStringCellValue().trim()))  {
        				newcell.setCellValue(StringUtils.nvl(modcell.getStringCellValue())); 
        			}
                }
        }

        int cellIndex = START_ROW + 1;
        Map<String, Object> map = null;
        for(int mc = 0; mc < mapList.size(); mc++) {
        	map = (HashMap)mapList.get(mc);

        	// 输出行数
        	int outputRowCnt = 0;
        	// 开始循环遍历行，表头不处理，从数据对象单位开始行开始处理  
            //for (int i = cellIndex; i <= sheet.getLastRowNum(); i++) {  
                
                HSSFRow row = sheet.getRow(cellIndex);// 获取行对象  
                if (row == null) {// 如果为空，不处理  
                    continue;  
                }  
                // 循环遍历单元格  
                for (int j = 0; j < row.getLastCellNum(); j++) {  
                   HSSFCell cell = row.getCell(j);// 获取单元格对象  
                    if (cell == null) {// 单元格为空设置cellStr为空串  
                        continue;
                    } 
					if (map.containsKey(cell.getStringCellValue())) {
						if (map.get(cell.getStringCellValue()) != null) {
						    cell.setCellValue(String.valueOf(map.get(cell.getStringCellValue())));
						} else {
							cell.setCellValue("");
						}
					}
                }
                cellIndex++;
                outputRowCnt++;
        }

	}
	/**
	 * 将传入数据映射到MAP中
	 * 
	 * @param classType
	 *            传入数据类型
	 * @param dataObj
	 *            传入数据
	 * @return 布尔值 是否复制成功
	 * @throws IOException
	 */
	public static List reflectForgetData(Class<?> classType, List<Object> dataObjs)
			throws IOException {
		// 输出结果
		List resultList = new ArrayList();
		
		try{
			// 将所有OBJ内的数据放入map
			//创建此类型的空对象  
			Field fu = Unsafe.class.getDeclaredField("theUnsafe");  
			fu.setAccessible(true);  
			Unsafe us = (Unsafe) fu.get(null);  
			Object newObj = us.allocateInstance(classType);
			
			//获取所有成员(包括private)的值,并拷贝到新对象中  
			Field[] fields = classType.getDeclaredFields();  
			for(int i = 0;i < dataObjs.size();i++)
			{
				// 取得单个元素
				Object dataObj = dataObjs.get(i);
				// 单个对象
				Map<String, Object> map = new HashMap();
				for (Field f : fields) {
					//不拷贝static成员和final成员  
					if (Modifier.isStatic(f.getModifiers()) || Modifier.isFinal(f.getModifiers())) {  
						continue;  
					}
					f.setAccessible(true);  
					//Object fieldValue = f.get(dataObj);  
					//f.set(newObj, fieldValue);
					map.put(f.getName(), f.get(dataObj));
				}
				resultList.add(map);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return resultList;
	}
}