Excel操作 >> 导出

ExcelBean.java

public class ExcelBean {
	
	private String path; //Excel寫入的路徑名
	private String sheetName; //工作表名稱（Excel的左下角）
	private int row; //行數
	private int col; //列數
	private List<String> titleList; //列表頭標題
	private int[] width; //每列所占寬度
	private List dataList; //數據集合

           //setter getter

}

 

 ExcelUtil.java

package app.other.poi.excel;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

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

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.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;




/**
 * @version 創建時間：Apr 13, 2009 1:49:49 PM
 * 類說明:Excel工具類
 */
public class ExcelUtil{
	
	private static final Log log = LogFactory.getLog(ExcelUtil.class);//日志

	/**
	 * 創建Excel對象
	 * @param excelBean
	 * @return
	 */
	public static HSSFWorkbook creatExcel(ExcelBean excelBean){
		HSSFWorkbook wb = new HSSFWorkbook(); //創建Excel工作薄
		//在Excel工作薄中建一工作表，默認為缺省值
//		HSSFSheet sheet = wb.createSheet();
        HSSFSheet sheet = wb.createSheet(excelBean.getSheetName());
        //設置工作表各列寬度
       // setColumnWidth(sheet, excelBean.getWidth());
        //設置字體，樣式
        //HSSFFont font = setFont(wb, "宋體");
        //HSSFCellStyle style = setCellStyle(wb, font);
        //設置第一行
        List<String> titleList = excelBean.getTitleList();
        setRowValue(sheet, titleList, 0); //在索引0的位置創建行（最頂端的行）
        //設置數據行
        List dataList = excelBean.getDataList();
        if(dataList != null){
        	for(int i=0; i<dataList.size(); i++){        		
        		List rowList = (List) dataList.get(i);
        		setRowValue(sheet, rowList, i+1); //在索引1的位置創建行（第2行）        		
        	}
        }
        return wb;
	}
	
	/**
	 * 創建Excel對象  
	 * @param excelBean
	 * @param excelBean2
	 * @return
	 */
	public static HSSFWorkbook creatExcel(ExcelBean excelBean, ExcelBean excelBean2){
		HSSFWorkbook wb = new HSSFWorkbook(); //創建Excel工作薄
		//在Excel工作薄中建一工作表，默認為缺省值
//		HSSFSheet sheet = wb.createSheet();
        HSSFSheet sheet = wb.createSheet(excelBean.getSheetName());
        //設置工作表各列寬度
       // setColumnWidth(sheet, excelBean.getWidth());
        //設置字體，樣式
        //HSSFFont font = setFont(wb, "宋體");
        //HSSFCellStyle style = setCellStyle(wb, font);
        //設置第一行
        List<String> titleList = excelBean.getTitleList();
        setRowValue(sheet, titleList, 0); //在索引0的位置創建行（最頂端的行）
        //設置數據行
        List dataList = excelBean.getDataList();
        if(dataList != null){
        	for(int i=0; i<dataList.size(); i++){        		
        		List rowList = (List) dataList.get(i);
        		setRowValue(sheet, rowList, i+1); //在索引1的位置創建行（第2行）        		
        	}
        }
        HSSFSheet sheet2 = wb.createSheet(excelBean2.getSheetName());
        //設置工作表各列寬度
       // setColumnWidth(sheet, excelBean.getWidth());
        //設置字體，樣式
        //HSSFFont font = setFont(wb, "宋體");
        //HSSFCellStyle style = setCellStyle(wb, font);
        //設置第一行
        List<String> titleList2 = excelBean2.getTitleList();
        setRowValue(sheet2, titleList2, 0); //在索引0的位置創建行（最頂端的行）
        //設置數據行
        List dataList2 = excelBean2.getDataList();
        if(dataList2 != null){
        	for(int i=0; i<dataList2.size(); i++){        		
        		List rowList = (List) dataList2.get(i);
        		setRowValue(sheet2, rowList, i+1); //在索引1的位置創建行（第2行）        		
        	}
        }
        return wb;
	}
	
	/**
	 * 寫入到物理地址中
	 * @param wb
	 * @param path
	 * @return
	 */
	public static boolean writeExcel(HSSFWorkbook wb, String path){
		boolean flag = true;
		try{
            // Write the output to a file
            FileOutputStream fileOut = new FileOutputStream(path);
            wb.write(fileOut);
            fileOut.close();
            log.info("Excel寫入到" + path + ", 成功！");
        }catch(Exception e){
        	log.error("Excel寫入到" + path + ", 失敗！");
        	flag = false;
            e.printStackTrace();
        }
        return flag;
	}
	
	/**
	 * 下載Excel
	 * @param wb
	 * @param filename MemberList.xls
	 * @param response
	 * @return
	 */
	public static boolean downloadExcel(HSSFWorkbook wb, String filename, HttpServletResponse response){
		boolean flag = true;		
		//設置輸入流
		OutputStream output = null;
		try {
//			設置響應類型
			response.setHeader("Content-disposition", "attachment;filename="+new String(filename.getBytes("GBK"),"ISO-8859-1"));
			response.setContentType("application/x-msdownload");
			
			output = response.getOutputStream();			
			wb.write(output);//輸出到網頁中
			output.flush();
			log.info("下載Excel：" + filename + ", 成功！");
		} catch (Exception e) {
			flag = false;
			log.info("下載Excel：" + filename + ", 失敗！");
			e.printStackTrace();
		} finally{
			try {
				output.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return flag;		
	}
	
	/**
	 * 設置工作表各列寬度
	 * @param sheet
	 * @param width
	 */
	public static void setColumnWidth(HSSFSheet sheet, int[] width){
        for(int i=0;i<width.length;i++){
            sheet.setColumnWidth((short)i, (short)(width[i]*256));
        }
    }
	
	/**
	 * 設置字體
	 * @param wb
	 * @param fontName 
	 */
	public static HSSFFont setFont(HSSFWorkbook wb, String fontName){
		HSSFFont font = wb.createFont();
		font.setFontName(fontName); //宋體， 
		return font;		
	}
	
	/**
	 * 將字體對象賦值給單格樣式對象
	 * @param wb
	 * @param font
	 * @return
	 */
	public static HSSFCellStyle setCellStyle(HSSFWorkbook wb, HSSFFont font){
		HSSFCellStyle style = wb.createCellStyle();
        style.setFont(font);
        return style;
	}
	
	/**
	 * 設置行值
	 * @param sheet
	 * @param List
	 * @return
	 */
	public static HSSFSheet setRowValue(HSSFSheet sheet, List<String> list,int rowNo){
		if(list != null){
			//在索引rowNo的位置創建行
			HSSFRow rowTitle = sheet.createRow(rowNo);
			for(int i= 0 ; i<list.size();i++){
				String title = list.get(i);
				//從索引0的位置開始創建單元格（左上端）
				HSSFCell cell = rowTitle.createCell((short)i);
				cell.setCellValue(title); //設置單元格內容
			}
		}
		return sheet;
	}
	
	/**
	 * 設置行值
	 * @param sheet
	 * @param List
	 * @return
	 */
	public static HSSFSheet setRowValue(HSSFSheet sheet, List<String> list,
			int rowNo,  HSSFCellStyle style){
		if(list != null){
			//在索引0的位置創建行（最頂端的行）
			HSSFRow rowTitle = sheet.createRow(rowNo);
			for(int i= 0 ; i<list.size();i++){
				String title = list.get(i);
				//從索引0的位置開始創建單元格（左上端）
				HSSFCell cell = rowTitle.createCell((short)i);
				cell.setCellValue(title); //設置單元格內容
				cell.setCellStyle(style);//單元格樣式  
			}
		}
		return sheet;
	}
	
	/**
	 * 下載（从服务器上下载现程的Excel文件）
	 * @param request
	 * @param response
	 * @param path 路径Z:\PowerTest\
	 * @param fileName 文件名 ExampleFile/UserProfileExample.xls
	 * @throws Exception
	 * @return loadFlag ---true 下載成功， false  下載失敗
	 */
	public static boolean download(HttpServletRequest request, HttpServletResponse response, String path, String fileName) throws Exception{  
		boolean loadFlag = true; //下載成功標志位
		File file = null;
		InputStream in = null; // 輸入流
		OutputStream out = null; // 輸出流
		//在下載附件之前設置響應類型和頭部文件
		response.setContentType("application/x-msdownload");
		response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(ExcelUtil.subLastStr(fileName, "/"),"UTF-8"));		
		try{
			file = new File(path + fileName); //取得文件對像
//			從下載附件創建輸入流，并向請求獲取并寫入（客戶端或網絡）輸出流
			in = new BufferedInputStream(new FileInputStream(file));
			out = response.getOutputStream();
			int readSize = 1024 * 10; //讀取大小
			int length = 0; //讀取剩余大小
			byte[] readByte = new byte[readSize];
			while ((length = in.read(readByte, 0, readSize)) != -1) {
				out.write(readByte, 0, length);
			}
			out.flush();
		}catch(Exception e){
			loadFlag = false;
			e.printStackTrace();
		}finally{
			if(in!=null || out!=null ){
				try {
					in.close();
					out.close();
				} catch (IOException e) {
					loadFlag = false;
					e.printStackTrace();
				}	
			}
		}
		return loadFlag;
	}
	
	/**
	 * 取得分割符最后的字符串
	 * @param path 路径
	 * @param regex '/'
	 * @return
	 */
	public static String subLastStr(String path, String regex){
		String temp = "";
		if(path == null){
			temp = "";
		}else{
			String[] arr = path.split(regex);
			temp = arr[arr.length-1];
		}
		return temp;		
	}


}

 

 

实例：

//匯出Excel
	public void sendExcel(List list,HttpServletResponse response) throws Exception{
		
		ExcelBean excelBean = new ExcelBean();
		excelBean.setWidth(new int[3033]);
		excelBean.setSheetName("會員信息汇出");
//		List<String> topNameList = new ArrayList();
//		topNameList.add("會員管理");
//		excelBean.setTitleList(topNameList);// 設置第一行標題列表
		List<String> titleList = new ArrayList();
		titleList.add("序號");
		titleList.add("帳號");
		titleList.add("姓名");
		titleList.add("E-Mail");
		titleList.add("訂閱電子報");
		titleList.add("加入日期");
		titleList.add("剩餘影片點數");
		List excelList = new ArrayList();
		excelBean.setTitleList(titleList);// 設置標題列表
//		excelList.add(titleList);// 設置標題列表
		int i = 0;
		//循環取出list的值
		for (Iterator iter = list.iterator(); iter.hasNext();) {
			++i;
			Member member = (Member) iter.next();
			List rowList = new ArrayList();
			rowList.add(String.valueOf(i));//給rowList賦值序號
			rowList.add(member.getLoginId());//給rowList賦值帳號
			rowList.add(member.getName());//給rowList賦值姓名
			rowList.add(member.getEmail());//給rowList賦值E-Mail
			//先判斷訂閱電子報，如果有值就進行轉換，然後給rowList賦值訂閱電子報
			if(member.getEpaper()!=null&&member.getEpaper()!=""){
				if("Y".equals(member.getEpaper())){
					rowList.add("是");
				}else{
					rowList.add("否");
				}
			}else{
				rowList.add("");
			}
			//先判斷加入日期，如果有值就進行轉換，然後給rowList賦值加入日期
			if(member.getCreateDT()!=null){
				String dt = String.valueOf(member.getCreateDT());
				int dtLen = dt.indexOf(" ");
				dt = dt.substring(0, dtLen);
				rowList.add(dt);
			}else{
				rowList.add("");
			}
			
			rowList.add(member.getSurplusFilmNum());//給rowList賦值剩餘影片點數
			excelList.add(rowList);
		}
	    
		excelBean.setDataList(excelList);
		HSSFWorkbook wb = ExcelUtil.creatExcel(excelBean);
		ExcelUtil.downloadExcel(wb, "member.xls", response);
		
	}