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

xiaoxiao2025-02-04  16

近期在项目中用到根据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; }} 相关资源:敏捷开发V1.0.pptx
转载请注明原文地址: https://www.6miu.com/read-5023976.html

最新回复(0)