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

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

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;
	}



}