java生成excel——（参数复杂，但使用灵活）版

package com.jy02363756.base;


import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.jy02363756.base.db.BaseDAO;


import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * 生成Excel
 * @author jy02363756
 *2009-11-17
 */
public class CreateExcelFile extends BaseDAO {

	/**
	 * 入口方法 <b>参数不明白的看最底下</b>
	 * @param os 输出流
	 * @param sql 查询语句
	 */
	public void createExcelFile(OutputStream os,String sql){
		this.createExcelFile(os, sql, null, null, null);
	}

	/**
	 * 入口方法 <b>参数不明白的看最底下</b>
	 * @param os 输出流
	 * @param sql 查询语句
	 * @param titleList 头的“Label”集合 头包括：表格大标题  和每列的标题
	 */
	public void createExcelFile(OutputStream os,String sql,List<Label> titleList){
		this.createExcelFile(os, sql, titleList, null, null);
	}


	/**
	 * 入口方法 <b>参数不明白的看最底下</b>
	 * @param os 输出流
	 * @param sql 查询语句
	 * @param titleList 头的“Label”集合 头包括：表格大标题  和每列的标题
	 * @param footList 脚的“Label”集合
	 */
	public void createExcelFile(OutputStream os,String sql,List<Label> titleList,List<Label> footList){
		this.createExcelFile(os, sql, titleList, footList, null);
	}



	/**
	 * 入口方法 <b>参数不明白的看最底下</b>
	 * @param os 输出流
	 * @param sql 查询语句
	 * @param titleList 头的“Label”集合 头包括：表格大标题  和每列的标题 
	 * @param footList 脚的“Label”集合
	 * @param sumCall 需要统计的列
	 * @param mergeCellList 合并列的坐标集合
	 */
	public void createExcelFile(OutputStream os,String sql,List<Label> titleList,List<Label> footList,Map<Integer,Double> sumCall ) {

		WritableWorkbook wwb = null;
		Connection conn=null;
		Statement stmt=null;
		ResultSet rs=null;
		try {
			//直接拿链接
			//Class.forName("oracle.jdbc.driver.OracleDriver");
			//Connection conn = DriverManager.getConnection("连接串", "用户名","密码");			
			//通过spring获得链接		
			conn=this.jdbcTemplate.getDataSource().getConnection();//获得数据库链接

			stmt = conn.createStatement();		

			rs = stmt.executeQuery(sql);

			wwb = Workbook.createWorkbook(os);

			jxl.write.WritableSheet ws = wwb.createSheet("Sheet1", 0);

//			if(mergeCellList!=null){
//				// 合并单元格  合并标题				
//				for(int i=0;i<mergeCellList.size();i++){					
//					int[] mergeCell=mergeCellList.get(i);
//					ws.mergeCells(mergeCell[0], mergeCell[1], mergeCell[2], mergeCell[3]);	
//				}
//							
//			}

			if(titleList!=null){	
				// 合并单元格  合并标题	
				ws.mergeCells(0,0,titleList.size()-3,0);	
				ws.mergeCells(0,1,titleList.size()-3,1);	

				// 添加表头
				for (Label title : titleList) {
					ws.addCell(title);
				}
			}			

			// 填充数据
			ResultSetMetaData msmd = rs.getMetaData();
			int callNumber = msmd.getColumnCount();
			int rowNumber = ws.getRows();
			while (rs.next()) {				
				//设置列宽度
				for(int i=0;i<callNumber;i++){
					ws.setColumnView(i, 20);
				}		
				//真正填充
				for (int e = 0; e < callNumber; e++) {
					String callStr = rs.getString(e + 1);
					Label label = new Label(e, rowNumber, callStr,getNormolCell());
					ws.addCell(label);
					//累加					
					if (sumCall!=null&&sumCall.get(e + 1) != null) {
						Double number = Double.parseDouble(sumCall.get(e + 1).toString());
						number += Double.parseDouble(callStr != null ? callStr: "0");
						sumCall.put(e + 1, number);
					}
				}
				rowNumber++;
			}			

			// 添加统计			
			if(sumCall!=null&&sumCall.size()>0&&footList!=null){
				Set set=sumCall.keySet();			
				Object[] keyList=set.toArray();	
				int keyIndex=0;				
				for(int i=0;i<footList.size();i++){
					int call=footList.get(i).getColumn();		
					String str=footList.get(i).getString();
					CellFormat callFormat=footList.get(i).getCellFormat();					
					if(str==null||"".equals(str)){
						ws.addCell(new Label(call,rowNumber,sumCall.get(keyList[keyIndex]).toString(),callFormat));
						keyIndex++;
					}else{
						ws.addCell(new Label(call,rowNumber,str,callFormat));						
					}						
				}				
			}else if(footList!=null){
				for(int i=0;i<footList.size();i++){
					int call=footList.get(i).getColumn();		
					String str=footList.get(i).getString();
					CellFormat callFormat=footList.get(i).getCellFormat();
					ws.addCell(new Label(call,rowNumber,str,callFormat));
				}

			}
		} catch (Exception e) {
				e.printStackTrace();
		} finally {
			if (wwb != null) {
				try {
					wwb.write();
					wwb.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			try {
				if(rs!=null){rs.close();}				
				if(stmt!=null){	stmt.close();}				
				if(conn!=null){	conn.close();}			
			} catch (SQLException e) {
				e.printStackTrace();
			}	
		}
	}

	/**
	 * 设置头的样式
	 * 
	 * @return
	 */
	public static WritableCellFormat getHeader() {
		WritableFont font = new WritableFont(WritableFont.TIMES, 24,
				WritableFont.BOLD);// 定义字体
		try {
			font.setColour(Colour.BLUE);// 蓝色字体
		} catch (WriteException e1) {
			// TODO 自动生成 catch 块
			e1.printStackTrace();
		}
		WritableCellFormat format = new WritableCellFormat(font);
		try {
			format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中
			format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
			format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
			format.setBackground(Colour.YELLOW);// 黄色背景
		} catch (WriteException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return format;
	}
	/**
	 * 设置头的样式_右边对齐
	 * 
	 * @return
	 */
	public static WritableCellFormat getHeader_Right() {
		WritableFont font = new WritableFont(WritableFont.TIMES, 15,
				WritableFont.BOLD);// 定义字体
		try {
			font.setColour(Colour.BLACK);// 黑色字体
		} catch (WriteException e1) {
			// TODO 自动生成 catch 块
			e1.printStackTrace();
		}
		WritableCellFormat format = new WritableCellFormat(font);
		try {
			format.setAlignment(jxl.format.Alignment.RIGHT);// 右对齐
			format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
			format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
			format.setBackground(Colour.YELLOW);// 黄色背景
		} catch (WriteException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return format;
	}

	/** */
	/**
	 * 设置标题样式
	 * 
	 * @return
	 */
	public static WritableCellFormat getTitle() {
		WritableFont font = new WritableFont(WritableFont.TIMES, 14,WritableFont.BOLD);
		try {
			font.setColour(Colour.BLUE);// 蓝色字体

		} catch (WriteException e1) {
			e1.printStackTrace();
		}
		WritableCellFormat format = new WritableCellFormat(font);

		try {
			format.setAlignment(jxl.format.Alignment.CENTRE);
			format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		} catch (WriteException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return format;
	}

	/** */
	/**
	 * 设置其他单元格样式
	 * 
	 * @return
	 */
	public static WritableCellFormat getNormolCell() {// 12号字体,上下左右居中,带黑色边框
		WritableFont font = new WritableFont(WritableFont.TIMES, 12);
		WritableCellFormat format = new WritableCellFormat(font);
		try {
			format.setAlignment(jxl.format.Alignment.CENTRE);
			format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		} catch (WriteException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return format;
	}	
}


//参数例子	
//		response.setContentType("application/vnd.ms-excel");
//
//		String sql="select GOODS_CODE,GOODS_NAME,SPEC,UNIT,ORIGIN,WEB_PRICE from goods_info";
//		
//		// 头参数
//		List<Label> titleList = new ArrayList<Label>();
//		titleList.add(new Label(0, 0, "商品查询表", CreateExcelFile.getHeader()));
//		titleList.add(new Label(0, 1, "统计日期：2009年 11月 17日",CreateExcelFile.getHeader_Right()));
//		titleList.add(new Label(0, 2, "商品编号",CreateExcelFile.getTitle()));
//		titleList.add(new Label(1, 2, "商品名称",CreateExcelFile.getTitle()));
//		titleList.add(new Label(2, 2, "规格",CreateExcelFile.getTitle()));
//		titleList.add(new Label(3, 2, "单位",CreateExcelFile.getTitle()));
//		titleList.add(new Label(4, 2, "产地",CreateExcelFile.getTitle()));
//		titleList.add(new Label(5, 2, "网站价",CreateExcelFile.getTitle()));
//		
//		int[] ee1={0,0,5,0};
//		int[] ee2={0,1,5,1};
//		List<int[]> mergeCellList=new ArrayList<int[]>();
//		mergeCellList.add(ee1);
//		mergeCellList.add(ee2);
//		
//		//脚参数  这里就需要  Label(Excel表的列号（从0开始）必须要设置！, 程序里用不到, 如果为“null”才放计算结果,必须)
//		List<Label> footList = new ArrayList<Label>();
//		footList.add(new Label(0, 0, "统计：",CreateExcelFile.getTitle()));
//		footList.add(new Label(5, 0, null,CreateExcelFile.getTitle()));
//		
//		// 统计用的 参数 callNum.put(结果集的列号（从1开始）, 必须是“Double.parseDouble("0")”);
//		Map sumCall = new HashMap<Integer, Integer>();
//		sumCall.put(6, Double.parseDouble("0"));

//注：footList的 null内容LABLE 个数要跟 sumCall个数匹配