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.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.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 查询语句
	 * @param titleList 头的“Label”集合 头包括：表格大标题  和每列的标题 
	 * @param footList 脚的“Label”集合
	 * @param sumCall 需要统计的列
	 * @param mergeCellList 合并列的坐标集合
	 */
	public void createExcelFile(OutputStream os,String sql,List<String> titleList,List<String> 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(int i=0;i<titleList.size();i++){
					if(i==0){
						ws.addCell(new Label(0, 0, titleList.get(i).toString(),CreateExcelFile.getHeader()));
					}else if(i==1){
						ws.addCell(new Label(0, 1,titleList.get(i).toString(),CreateExcelFile.getHeader_Right()));
					}else{
						ws.addCell(new Label(i-2, 2,titleList.get(i).toString(),CreateExcelFile.getTitle()));
					}

				}

			}			

			// 填充数据
			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++){					
					String[] param=footList.get(i).split(",");					
					int call=Integer.parseInt(param[0]);		
					String str=param[1]==null||param[1].equals("")||param[1].toLowerCase().equals("null")?null:param[1];	

					if(str==null||"".equals(str)){
						ws.addCell(new Label(call,rowNumber,sumCall.get(keyList[keyIndex]).toString(),CreateExcelFile.getTitle()));
						keyIndex++;
					}else{
						ws.addCell(new Label(call,rowNumber,str,CreateExcelFile.getTitle()));						
					}						
				}				
			}else if(footList!=null){
				for(int i=0;i<footList.size();i++){
					String[] param=footList.get(i).split(",");					
					int call=Integer.parseInt(param[0]);		
					String str=param[1]==null||param[1].equals("")||param[1].toLowerCase().equals("null")?null:param[1];
					ws.addCell(new Label(call,rowNumber,str,CreateExcelFile.getTitle()));
				}

			}
		} 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
	 */
	private static WritableCellFormat getHeader() {
		WritableFont font = new WritableFont(WritableFont.TIMES, 24,
				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.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
	 */
	private static WritableCellFormat getHeader_Right() {
		WritableFont font = new WritableFont(WritableFont.TIMES, 12,
				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
	 */
	private static WritableCellFormat getTitle() {
		WritableFont font = new WritableFont(WritableFont.TIMES, 12,WritableFont.BOLD);
		try {
			font.setColour(Colour.BLACK);// 字体颜色

		} 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
	 */
	private 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<String > titleList = new ArrayList<String>();
//		titleList.add("商品查询表");  //大标题
//		titleList.add("统计日期：2009年 11月 17日");  //副标题
//		titleList.add("商品编号");
//		titleList.add("商品名称");
//		titleList.add("规格");
//		titleList.add("单位");
//		titleList.add("产地");
//		titleList.add("网站价");
//		
//		
//		
//		//脚参数 (列坐标      ,    要显示的内容；如果为“null”才放计算结果)   中间用 “,”分割
//		List<String> footList = new ArrayList<String>();
//		footList.add("0,统计：");
//		footList.add("5,null");
//		
//		
//		
//		// 统计用的 参数 callNum.put(结果集的列号（从1开始）, 必须是“Double.parseDouble("0")”);
//		Map sumCall = new HashMap<Integer, Integer>();
//		sumCall.put(6, Double.parseDouble("0"));

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