Java创建和下载excel文件

package com.g3.hrp.customer_setting.jde.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.apache.commons.beanutils.BeanUtils;
/**
 * Excel操作工具类
 *
 * @author penghuaiyi
 * @date 2011-6-20
 *
 */
public class ExcelUtil {

	private String[] fTags = null;  //标签代号信息

	private String[] fNames = null; //对象属性信息

	private Hashtable allTags= null ; //标签缓存

	public ExcelUtil(){

	}

	public ExcelUtil(String[] fTags,String[] fNames,Hashtable allTags){
		this.fTags = fTags;
		this.fNames = fNames;
		this.allTags = allTags;
	}

	/**
	 * 生成Excel文件
	 * @param path
	 * @param dataList
	 * @return
	 */
	public String createExcel(String path,List dataList){
		File file = new File(path);
		if(!file.exists()){
			file.mkdirs();
		}
       // deleteAllFile(file);
		file = new File(file,System.currentTimeMillis()+".xls");
		WritableWorkbook book=null;
		try {
			book = Workbook.createWorkbook(file);
			WritableSheet sheet = book.createSheet(file.getName(), 0);
			this.setHeader(sheet); //设置Excel标题信息
            this.setBody(sheet,dataList); // 设置Excel内容主体信息
			book.write();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		} catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				book.close();
			} catch (WriteException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return file.getAbsolutePath();
	}

	/**
	 * 清空file下的所有子文件
	 * @param file
	 */
	public void deleteAllFile(File file){
		try{
			File[] files = file.listFiles();
			for(int i=0;i<files.length;i++){
				files[i].delete();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	/**
	 * 设置Excel标题信息
	 * @param sheet
	 * @throws WriteException
	 */
	public void setHeader(WritableSheet sheet) throws WriteException{
		String[] header = new String[fTags.length];
		for(int i=0;i<fTags.length;i++){
			String fTagsName=(String)allTags.get("F_"+fTags[i].toUpperCase());
			header[i] = fTagsName!=null ? fTagsName : fTags[i];
		}
		this.setHeader(sheet, header);
	}

	/**
	 * 设置Excel标题信息
	 * @param sheet
	 * @param column
	 * @throws WriteException
	 */
	public void setHeader(WritableSheet sheet,String[] column) throws WriteException{
		WritableCellFormat headerFormat = new  WritableCellFormat();
        headerFormat.setAlignment(Alignment.CENTRE);  //水平居中对齐
        headerFormat.setVerticalAlignment(VerticalAlignment.CENTRE);   //竖直方向居中对齐
		headerFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
		for(int i=0;i<column.length;i++){
			Label label = new Label(i,0,column[i],headerFormat);
			sheet.addCell(label);
			sheet.setColumnView(i, 20);
			sheet.setRowView(0, 500);
		}
	}



    /**
     * 设置Excel内容主体信息
     * @param sheet
     * @param rowList
     * @throws Exception
     */
	public void setBody(WritableSheet sheet,List rowList) throws Exception{
	    this.setBody(sheet, rowList, fNames);
	}

	 /**
     * 设置Excel内容主体信息
     * @param sheet
     * @param rowList
     * @param column
     * @throws Exception
     */
	public void setBody(WritableSheet sheet,List rowList,String[] column) throws Exception{
	   WritableCellFormat bodyFormat = new  WritableCellFormat();
	   bodyFormat.setAlignment(Alignment.CENTRE); //水平居中对齐
	   bodyFormat.setVerticalAlignment(VerticalAlignment.CENTRE);   //竖直方向居中对齐
	   bodyFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
	   Object obj =null;
	   Label label = null;
	   for(int i=0;i<rowList.size();i++){
		   obj = rowList.get(i);
		   for(int j=0;j<column.length;j++){
			   if(obj instanceof Map){
				   label = new Label(j,i+1,String.valueOf(((Map)obj).get(column[j].toLowerCase())),bodyFormat);
			   }else{
				   label = new Label(j,i+1,BeanUtils.getProperty(obj, column[j]),bodyFormat);
			   }
			   sheet.addCell(label);
			   sheet.setRowView(i+1, 350);
		   }
	   }
	}


	/**
	 * 文件下载
	 * @param response
	 * @param filePath 文件路径
	 * @param fileName 文件名称
	 */
	public void download(HttpServletResponse response, String filePath, String fileName)throws IOException {
		FileInputStream fis = null;
		OutputStream os = null;
		try {
			fis = new FileInputStream(filePath);
			os = response.getOutputStream();// 取得输出流
			response.reset();// 清空输出流
			response.setHeader("Content-disposition", "attachment; filename=" + fileName);// 设定输出文件头
			response.setContentType("application/x-download");
			byte[] mybyte = new byte[8192];
			int len = 0;
			while ((len = fis.read(mybyte)) != -1) {
				os.write(mybyte, 0, len);
			}
			os.close();
		}catch (IOException e) {
            throw e;
		}
	}

}

评论

1 楼 u010940863 2013-07-18  

大神，这个工具类怎么用啊！求讲解啊！