根据SQL结果集构建动态二维列表展示在excel中

近期在项目中用到根据SQL结果集构建动态二维列表展示在excel中。把它贴出来希望对大家有帮助。
数据库是mysql,操纵excel是使用apache POI SpreadSheet，如对POI不熟，请查看我的另外一文章Apache POI SpreadSheet的一些简单应用。
代码如下:

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

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.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
/**
 * 
 * 
 * 主要是将一些数据组织成二维表以便汇总
 * 
 * 如:
 * 
 * 			战略1	战略2	战略3
 * 
 * 项目1		20		20		30
 * 项目2		30		15		40
 * ...
 * 
 */
public class XZou {

	static Connection con = null;

	static {

		try {
			Class.forName("com.mysql.jdbc.Driver");

			con = DriverManager
					.getConnection(
							"jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8",
							"root", "root");
			
			PreparedStatement pst = con.prepareStatement("drop table if exists project;");
			
			pst.execute();
			
			pst = con.prepareStatement("create table project (pro varchar(20) not null comment '项目名称',sta varchar(20) not null comment '战略名称',mark float comment '值');");
			
			pst.execute();
			
			pst =  con.prepareStatement("insert into project (pro,sta,mark) values (?,?,?)");
			
			///////////////////////////////////////////////
			pst.setString(1, "项目1"); pst.setString(2, "战略1"); pst.setFloat(3, 30.0f);
			pst.addBatch();
			pst.setString(1, "项目1"); pst.setString(2, "战略2"); pst.setFloat(3, 40.0f);
			pst.addBatch();
			pst.setString(1, "项目1"); pst.setString(2, "战略3"); pst.setFloat(3, 50.0f);
			pst.addBatch();
			pst.setString(1, "项目2"); pst.setString(2, "战略1"); pst.setFloat(3, 40.0f);
			pst.addBatch();
			pst.setString(1, "项目2"); pst.setString(2, "战略2"); pst.setFloat(3, 50.0f);
			pst.addBatch();
			pst.setString(1, "项目2"); pst.setString(2, "战略3"); pst.setFloat(3, 60.0f);
			pst.addBatch();
			pst.setString(1, "项目2"); pst.setString(2, "战略4"); pst.setFloat(3, 40.0f);
			pst.addBatch();
			pst.setString(1, "项目3"); pst.setString(2, "战略1"); pst.setFloat(3, 50.0f);
			pst.addBatch();
			pst.setString(1, "项目3"); pst.setString(2, "战略2"); pst.setFloat(3, 40.0f);
			pst.addBatch();
			pst.setString(1, "项目3"); pst.setString(2, "战略4"); pst.setFloat(3, 30.0f);
			pst.addBatch();
			pst.setString(1, "项目4"); pst.setString(2, "战略1"); pst.setFloat(3, 50.0f);
			pst.addBatch();
			pst.setString(1, "项目4"); pst.setString(2, "战略2"); pst.setFloat(3, 20.50f);
			pst.addBatch();
			pst.setString(1, "项目4"); pst.setString(2, "战略3"); pst.setFloat(3, 30.0f);
			pst.addBatch();
			pst.setString(1, "项目4"); pst.setString(2, "战略4"); pst.setFloat(3, 90.0f);
			pst.addBatch();
			pst.setString(1, "项目5"); pst.setString(2, "战略1"); pst.setFloat(3, 40.0f);
			pst.addBatch();
			pst.setString(1, "项目5"); pst.setString(2, "战略3"); pst.setFloat(3, 30.0f);
			pst.addBatch();
			pst.setString(1, "项目5"); pst.setString(2, "战略4"); pst.setFloat(3, 30.0f);
			pst.addBatch();
			
			
			pst.executeBatch();

		} catch (Exception ex) {
			throw new ExceptionInInitializerError(ex);
		}
	}

	public static void main(String[] args) throws Exception {

		int startRow = 8, startCell = 6;
		
		List<X> xList = new ArrayList<X>();
		
		PreparedStatement pst = con.prepareStatement("select pro from project group by pro");
		
		ResultSet rs = pst.executeQuery();
		
		while(rs.next()){
			
			xList.add(new X(rs.getString(1)));
			
		}
		
		Map<String, Integer> yMap = new HashMap<String, Integer>();
		
		
		
		pst = con.prepareStatement("select sta from project group by sta");
		
		rs = pst.executeQuery();
		
		int tempStartCell = startCell;
		
		while(rs.next()){
			
			yMap.put(rs.getString(1), ++tempStartCell);
			
		}
		
		

		pst = con.prepareStatement("select pro,sta,mark from project group by pro,sta");
		
		rs = pst.executeQuery();
		
		while(rs.next()){
			
			
			for(X x: xList){
				
				if(x.getName().equals(rs.getString(1))){
					
					x.getYList().add(new Y(rs.getString(2),rs.getFloat(3)));
				}
				
			}
			
		}
			
		rs.close();
		
		pst.close();
		
		////////////////////////////////////////////////////////////////////
		
		HSSFWorkbook wb = new HSSFWorkbook();
			
		HSSFSheet sheet = wb.createSheet("测试");
		
		HSSFCellStyle style = wb.createCellStyle();
		
		HSSFFont font = wb.createFont();
		
		font.setColor(HSSFColor.RED.index);
		
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中现实

		style.setFont(font);//设置颜色
		
		HSSFRow xRow = sheet.createRow(startRow - 1);//确立X，所有列
		
		for(Iterator<Map.Entry<String, Integer>> it = yMap.entrySet().iterator(); it.hasNext(); ){
			
			Map.Entry<String, Integer> map = it.next();
			
			HSSFCell cell = xRow.createCell(map.getValue());
			
			cell.setCellStyle(style);

			cell.setCellValue(new HSSFRichTextString(map.getKey()));
			
		}
		
		for(int i = 0; i<xList.size(); i++){//确立Y,行
			
			X x = xList.get(i);
			
			HSSFRow row = sheet.createRow(startRow + i);//创建行
			
			HSSFCell cell = row.createCell(startCell);
			
			cell.setCellStyle(style);
			
			cell.setCellValue(new HSSFRichTextString(x.getName()));
			
			for(int j = 0; j<x.getYList().size(); j++){
				
				Y y = x.getYList().get(j);
				
				cell = row.createCell(yMap.get(y.getName()));
				
				cell.setCellStyle(style);
				
				cell.setCellValue(y.getValue());
				
			}
			
			
		}

		wb.write(new FileOutputStream("c:/wd.xls"));//写一个excel文件
		
		
		
		
	}

}

/**
 * X 项目
 */
class X{
	
	private String name;//项目名称
	
	private List<Y> yList = new ArrayList<Y>();//项目对应的所有的战略集合
	
	public X(){
		
	}
	public X(String name){
		this.name = name;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public List<Y> getYList() {
		return yList;
	}

	public void setYList(List<Y> list) {
		yList = list;
	}
	
	
	
	
}

/**
 * 战略
 */
class Y{
	
	private String name;//战略名称
	
	private float value;//项目名称关联战略名称对应的mark值
	
	public Y(String name,float value){
		
		this.name = name;
		
		this.value = value;
		
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public float getValue() {
		return value;
	}

	public void setValue(float value) {
		this.value = value;
	}
	
	
	
}