转载：开源社区http://www.oschina.net/code/snippet_1424099_49530?p=2
代码] [Java]代码 该代码实现了Excel复杂表头的生成 基于sql server 的一张树表 你们可以 看代码自行建库
package com.jingjiu.util;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.Region;
import org.aspectj.weaver.patterns.ThisOrTargetAnnotationPointcut;
import com.google.common.collect.Table.Cell;
public class GenerExcle {
HSSFWorkbook wb =null;
HSSFSheet sheet =null;
HSSFCellStyle style = null;
public void reData(){
//所有表头集合
List allTableHead = new ArrayList();
}
public GenerExcle(){
wb = new HSSFWorkbook();
sheet = wb.createSheet("new sheet");
style = wb.createCellStyle(); // 样式对象
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
//style.setBottomBorderColor(HSSFCellStyle.BORDER_DOTTED);
style.setWrapText(true);
}
/**
* 保存生成的文件
* @param name
*/
public void saveAaSpecName(String name){
OutputStream out;
try {
out = new FileOutputStream(name);
wb.write(out);
System.out.println(out);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void addRow(int count){
for(int i = 0; i<count; i++)
sheet.createRow((short) i);
}
public void testAll(){
this.addRow(3);
sheet.addMergedRegion(new Region(0,(short)0,2,(short)0));
HSSFRow row = sheet.getRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue("公司名");
this.saveAaSpecName("e:/like.xls");
}
/**
* 合并cell并设置值
* @param startRow
* @param startCell
* @param endRow
* @param endCell
* @param leve
* @param cellMove
* @param content
*/
public void mergeCell(int startRow,int startCell,int endRow,int endCell,int leve,int cellMove,String content){
sheet.addMergedRegion(new Region(startRow,(short)startCell,endRow,(short)endCell));
HSSFRow row = sheet.getRow(startRow);
HSSFCell cell= row.createCell(cellMove);
cell.setCellValue(content);
cell.setCellStyle(style);
}
public StringBuffer excleToHtml(){
ExcelShower es = new ExcelShower();
StringBuffer excleToHtml = es.excleToHtml(this.wb);
return excleToHtml;
}
public static void main(String [] args){
GenerExcle ge = new GenerExcle();
// ge.testAll();
if(true){
// return;
}
int reportId = 1;
getReportHeaderById(ge, reportId);
}
/**
* 根据报表ID遍历该报表的所有表头
* 并以主表头为原子单位进行处理
* @param ge
* @param reportId
*/
private static void getReportHeaderById(GenerExcle ge, int reportId) {
//拿到煤报表的一级所有一级表头
String sql =
"with tem_header(id,pid,headervalue,leve,custom_col) as(\n" +
"\n" +
"select id ,pid, headvalue,leve, custom_col from dbo.headerinfo where pid = 0 and report_id = ?\n" +
"union all\n" +
"select a.id,a.pid,a.headvalue,a.leve ,a.custom_col from dbo.headerinfo a ,tem_header b\n" +
"where a.pid = b.id\n" +
"\n"+
"and a.report_id = ?"+
"\n" +
"\n" +
")\n" +
"select * from tem_header where pid = 0";
PreparedStatement ps = null;
ResultSet rs = null;
Connection connection = null;
try {
connection= DBUtil.getConnection();
ps = connection.prepareStatement(sql);
ps.setInt(1, reportId);
ps.setInt(2, reportId);
ps.executeQuery();
rs = ps.getResultSet();
int max = 0;
while (rs.next()) {
int id = rs.getInt("id");
int pid = rs.getInt("pid");
String headvalue = rs.getString("headervalue");
int leve = rs.getInt("leve");
System.out.println("ID"+id+"Pid"+pid+"头名"+headvalue);
//先判断最深层有多少层
int maxLeve = getMaxLeve(reportId,id,connection,ps,rs);
if(maxLeve>max){
max = maxLeve;
}
}
//根据最深层添加行
ge.addRow(max);
ps.executeQuery();
rs = ps.getResultSet();
int currentIndex = 0;
int index = 0;
Map<String, Integer> moveInfoMap = new HashMap<String, Integer>();
Map<String, Integer> leveMoveInfoMap = new HashMap<String, Integer>();
moveInfoMap.put("currentIndex",0);
moveInfoMap.put("cellMove", 0);
while (rs.next()) {
moveInfoMap.put("index",index);
int id = rs.getInt("id");
int pid = rs.getInt("pid");
String headvalue = rs.getString("headervalue");
int leve = rs.getInt("leve");
int custom_col = rs.getInt("custom_col");
if( leveMoveInfoMap.get(leve+"")==null){
leveMoveInfoMap.put(leve+"",0);
}
System.out.println("ID"+id+"Pid"+pid+"头名"+headvalue);
generHeader(id,
pid,
headvalue,
custom_col,
max,
rs,
ps,
connection,
ge,
reportId,
leve,
currentIndex,
moveInfoMap,
leveMoveInfoMap);
//当前索引自加1
currentIndex++;
index++;
}
// ge.saveAaSpecName("e:/today.xls");
ge.excleToHtml();
DBUtil.closeResources(connection, ps, rs);
System.out.println(connection);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.closeResources(connection, ps, rs);
}
}
/**
* 判断最深有多少层
* @param i
* @param id
* @param rs
* @param ps
* @return
*/
private static int getMaxLeve(int reportId, int id,Connection con, PreparedStatement ps, ResultSet rs) {
String sql=
"with tem_header(id,pid,headervalue,leve) as(\n" +
"\n" +
"select id ,pid, headvalue,leve from dbo.headerinfo where pid = 0 and report_id = ? and id = ?\n" +
"union all\n" +
"select a.id,a.pid,a.headvalue,a.leve from dbo.headerinfo a ,tem_header b\n" +
"where a.pid = b.id\n" +
"\n"+
"and a.report_id = ?"+
"\n" +
")\n" +
"select count(1) from (select t.leve from tem_header t group by t.leve) mm";
try {
ps = con.prepareStatement(sql);
ps.setObject(1, reportId);
ps.setObject(2, id);
ps.setObject(3,reportId);
rs = ps.executeQuery();
if(rs.next())
return rs.getInt(1);
return -1;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
private static void generAfterHeader(int id,
int pid,
String headvalue,
int max,
ResultSet rs,
PreparedStatement ps,
Connection connection,
GenerExcle ge,
int reportId,
int leve,
int currentIndex,
Map<String, Integer> moveInfoMap,
Map<String, Integer> leveMoveInfoMap,
Map<String, Map<String,Integer>> repairMovePoint,
int index){
/**
* 处理多级表头的合并
*/
//但到表头的直接后继表头
rs = getDirAfter(id,pid,reportId,leve,connection,ps,rs);
//存放直接后继的子头集合
List<DataModel> listResultSets = new ArrayList<DataModel>();
boolean flag = false;
try {
// rs.last();
// int size = rs.getRow();
//rs.first();
while(rs.next()){
int _id= rs.getInt("id");
int _pid = rs.getInt("pid");
String _headvalue = rs.getString("headervalue");
int _leve = rs.getInt("leve");
if( leveMoveInfoMap.get(_leve+"")==null){
leveMoveInfoMap.put(_leve+"",0);
}
Map<String,Integer> infoMovePlus = null;
if(repairMovePoint.get(_leve+1+"")==null){
infoMovePlus = new HashMap<String, Integer>();
}else{
infoMovePlus =repairMovePoint.get(_leve+1+"");
}
infoMovePlus.put(0+"", 0);
infoMovePlus.put(rs.getRow()+"", 0);
repairMovePoint.put(_leve+1+"", infoMovePlus);
/**
* 判断该表头有没有直接后继节点
* 如果有不做向下合并处理
* 如果没有则合并下面的空格
*/
//如果不是最后一行则会发生空出一格的情况这时候需要进行行合并
int _temDownMovePonit = 0;
int _currentMaxLeve = leveMoveInfoMap.get("currentMaxLeve");
if(_leve<_currentMaxLeve&&_leve>1){
ResultSet _rs = getDirAfter(_id, _pid, reportId, _leve, connection, ps, rs);
//如果没有直接后继节点
if(!_rs.next()){
//判断需要向下合并几个位移量
_temDownMovePonit = 3-_leve;
//让以下每一级的指针右移1位补空位
/* if(row==1){
for(int i = 0;i<3-_leve;i++){
// leveMoveInfoMap.put(_leve+i+1+"",leveMoveInfoMap.get(_leve+i+1+"")+1);
}
｝*/
//如果是连续有值
if(infoMovePlus.get(rs.getRow()-1+"")!=null&&infoMovePlus.get(rs.getRow()-1+"")>=1){
// infoMovePlus.put(rs.getRow()-1+"",infoMovePlus.get(rs.getRow()-1+"")+1);
}
infoMovePlus.put(rs.getRow()+"",infoMovePlus.get(rs.getRow()-1+"")+1);
}
}
int plusPoint = 0;
if(!flag){
if(repairMovePoint.get(_leve+"")!=null){
Map<String, Integer> map = repairMovePoint.get(_leve+"");
int _rowCount = rs.getRow();
for(int i = 1;i<=index;i++)
if(map.get(i+"")!=null){
plusPoint = map.get(i+"");
}
flag = true;
}
}
int _sizeMoveCount = leveMoveInfoMap.get(_leve-1+"");
int sizeMoveCount = leveMoveInfoMap.get(_leve+"")+plusPoint;
// int _tem=moveInfoMap.get("cellMove");
int start =_leve-1+(repairMovePoint.get("mainRowCount").get("mainRowCount")-1);
int maxCurrentLeveMegreCell = getMaxCurrentLeveMegreCell(_id, reportId, _pid, ps, rs, connection);
// ge.mergeCell(_leve-1, sizeMoveCount, _leve-1+_temDownMovePonit, sizeMoveCount+maxCurrentLeveMegreCell-1, _leve, sizeMoveCount, _headvalue);
ge.mergeCell(start, sizeMoveCount, start+_temDownMovePonit, sizeMoveCount+maxCurrentLeveMegreCell-1, _leve, sizeMoveCount, _headvalue);
leveMoveInfoMap.put(_leve+"",sizeMoveCount+maxCurrentLeveMegreCell);
//_temDownMovePonit= 0;
DataModel dm = new DataModel();
dm.setId(_id);
dm.setPid(_pid);
dm.setHeadvalue(_headvalue);
dm.setMax(max);
dm.setRs(rs);;
dm.setPs(ps);
dm.setConnection(connection);
dm.setGe(ge);
dm.setReportId(reportId);
dm.setLeve(_leve);
dm.setCurrentIndex(currentIndex);
dm.setMoveInfoMap(leveMoveInfoMap);
dm.setLeveMoveInfoMap(leveMoveInfoMap);
dm.setRepairMoveInfo(repairMovePoint);
listResultSets.add(dm);
// getDirAfter(_id, _pid, reportId, maxCurrentLeveMegreCell, connection, ps, rs);
//break;
// System.out.println("leve 2ID"+_id+"Pid"+_pid+"头名"+_headvalue+""+maxCurrentLeveMegreCell);
}
/**
* 判断该表的最终级别有几列 说明该表的合并列为多少
* 所有单元格的合并最终是看当前表头开始它的最终有多少个子表头
* 也就是最后一行
*/
if(listResultSets!=null&&listResultSets.size()>0){
for(int i = 0 ;i<listResultSets.size() ; i ++){
DataModel dModel = listResultSets.get(i);
dModel.setIndex(i);
generAfterHeader(dModel.getId() , dModel.getPid(), dModel.getHeadvalue(),
dModel.getMax(),
dModel.getRs(),
dModel.getPs(),
dModel.getConnection(),
dModel.getGe(),
dModel.getReportId(),
dModel.getLeve(),
dModel.getCurrentIndex(),
dModel.getMoveInfoMap(),
dModel.getLeveMoveInfoMap(),
dModel.getRepairMoveInfo(),
dModel.getIndex());
}
}
} catch (SQLException e2) {
// TODO Auto-generated catch block
e2.printStackTrace();
}
}
/**
* 生成表头
* @param id
* @param pid
* @param headvalue
* @param max
* @param max
* @param rs
* @param ps
* @param connection
* @param ge
* @param leve
* @param currentIndex
* @param moveInfoMap
* @param leveMoveInfoMap
*/
private static void generHeader(int id,
int pid,
String headvalue,
int customCol,
int max, ResultSet rs,
PreparedStatement ps,
Connection connection,
GenerExcle ge,
int reportId,
int leve,
int currentIndex,
Map<String, Integer> moveInfoMap,
Map<String, Integer> leveMoveInfoMap) {
//获取当前主表头应该跨几列
int maxMoveCell = getMaxCurrentLeveMegreCell(id,reportId,pid,ps,rs,connection);
Map<String, Integer> _moveInfoMap = new HashMap<String, Integer>();
//获得该主表头的层级
int currentMaxLeve = getMaxLeve(reportId, id, connection, ps, rs);
//获取当前主表头应该占几行
int mainRowCount = max - (currentMaxLeve -1);
if(mainRowCount!=max){
getMax(id, pid, rs, ps, connection, reportId, leve, _moveInfoMap,currentMaxLeve);
if(_moveInfoMap.get("addOtherCell")!=null){
String _max = _moveInfoMap.get("addOtherCell").toString();
maxMoveCell +=Integer.parseInt(_max);
}
}
//以主表头层级关系每开一个主表头的位移量想当于下一个主表头的开始量（这是个累加的过程）
maxMoveCell+=customCol;
if(leveMoveInfoMap.get("allColMove")==null){
leveMoveInfoMap.put("allColMove",maxMoveCell);
}else{
leveMoveInfoMap.put("allColMove",leveMoveInfoMap.get("allColMove")+maxMoveCell);
}
/*
* 把当前主表头的层级关系带进去 以便控制子表如果下沉合并时的位移量
* */
leveMoveInfoMap.put("currentMaxLeve", currentMaxLeve);
System.out.println(maxMoveCell);
//初始化每级行的位移量
for(int i = 1 ;i<=max;i++){
if(leveMoveInfoMap.get(i+"")==null)
leveMoveInfoMap.put(i+"",0);
}
//进行单元格合并
ge.mergeCell(leve-1,
moveInfoMap.get("cellMove"),
mainRowCount-1,
moveInfoMap.get("cellMove")+maxMoveCell-1,
leveMoveInfoMap.get(leve+""),
moveInfoMap.get("cellMove"),
headvalue);
moveInfoMap.put("currentIndex",moveInfoMap.get("currentIndex")+maxMoveCell);
moveInfoMap.put("cellMove",moveInfoMap.get("cellMove")+maxMoveCell);
//如果当前表应该占几行与最大行相同则说明该表头没有子表头
if (mainRowCount == max) {
// 如果该主表头没有子表则所有单元格只位移1个
for (int i = 1; i <= max; i++) {
leveMoveInfoMap.put(i + "", leveMoveInfoMap.get(i + "") + 1+customCol);
}
return;
}
/**
* 开始处理后继表头的合并工作
*/
Map<String,Map<String,Integer>> repairMovePoint = new HashMap<String,Map<String,Integer>>();
Map<String,Integer> _tem = new HashMap<String, Integer>();
_tem.put("mainRowCount", mainRowCount);
repairMovePoint.put("mainRowCount",_tem);
generAfterHeader(id,
pid,
headvalue,
maxMoveCell,
rs,
ps,
connection,
ge,
reportId,
leve,
currentIndex,
moveInfoMap,
leveMoveInfoMap,
repairMovePoint,0);
//当一个主表头以级它下面的子表头的填冲合并工作完成后记录最大移动了多少列
//因为下一个主表头的所有数据填冲都会心这个为开始
for(int i=1;i<=max;i++){
leveMoveInfoMap.put(i + "", leveMoveInfoMap.get("allColMove"));
}
}
/**
* 获取当前层级元素应该合并多少个单元格
* @param id
* @param reportId
* @param pid
* @param ps
* @param rs
* @param connection
* @return
*/
private static int getMaxCurrentLeveMegreCell(int id, int reportId, int pid,
PreparedStatement ps, ResultSet rs, Connection connection) {
/* String maxCell =
"with tem_header(id,pid,headervalue,leve) as(\n" +
"\n" +
"select id ,pid, headvalue,leve from dbo.headerinfo where pid = ? and report_id = ? and id =?\n" +
"union all\n" +
"select a.id,a.pid,a.headvalue,a.leve from dbo.headerinfo a ,tem_header b\n" +
"where a.pid = b.id\n" +
"\n and a.report_id = ?" +
"\n" +
"\n" +
")\n" +
"select max(aa.a) from (select count(1) a from tem_header group by leve) aa";*/
String maxCell =
"with tem_header(id,pid,headervalue,leve) as(\n" +
"\n" +
" select id ,pid, headvalue,leve from dbo.headerinfo where pid = ? and report_id = ? and id =?\n" +
" union all\n" +
" select a.id,a.pid,a.headvalue,a.leve from dbo.headerinfo a ,tem_header b\n" +
" where a.pid = b.id\n" +
" and a.report_id = ?\n" +
"\n" +
"\n" +
" )\n" +
"\n" +
"\n" +
" select top 1 aa.a from (select count(1) a ,leve le from tem_header group by leve) aa order by aa.le desc";
try {
ps = connection.prepareStatement(maxCell);
ps.setInt(1, pid);
ps.setInt(2,reportId);
ps.setInt(3, id);
ps.setInt(4,reportId);
ps.executeQuery();
rs = ps.getResultSet();
if(rs.next()){
return rs.getInt(1);
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
return 0;
}
/**
*
* @param id
* @param pid
* @param leve
* @param leve2
* @param rs
* @param ps
* @param connection
* @return
*/
private static ResultSet getDirAfter(int id,
int pid,
int reportid,
int leve,
Connection connection,
PreparedStatement ps,
ResultSet rs) {
String sql =
"\n" +
"with tem_header(id,pid,headervalue,leve) as(\n" +
"\n" +
"select id ,pid, headvalue,leve from dbo.headerinfo where pid = ? and report_id = ?\n" +
"union all\n" +
"select a.id,a.pid,a.headvalue,a.leve from dbo.headerinfo a ,tem_header b\n" +
"where a.pid = b.id\n" +
"\n"+
"and a.report_id = ?"+
"\n" +
"\n" +
")\n" +
"select * from tem_header t where t.leve = ?";
try {
ps = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
ps.setInt(1, id);
ps.setInt(2,reportid);
ps.setInt(4, leve+1);
ps.setInt(3,reportid);
ps.executeQuery();
return ps.getResultSet();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
private static void getMax(int id,
int pid,
ResultSet rs,
PreparedStatement ps,
Connection connection,
int reportId,
int leve,
Map<String, Integer> moveInfoMap,
int currentMaxLeve){
/**
* 处理多级表头的合并
*/
//但到表头的直接后继表头
rs = getDirAfter(id,pid,reportId,leve,connection,ps,rs);
/*try {
if(!rs.next()&&leve!=3){
if(moveInfoMap.get("addOtherCell")==null){
moveInfoMap.put("addOtherCell", 0);
moveInfoMap.put("addOtherCell", moveInfoMap.get("addOtherCell")+1);
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}*/
// rs = getDirAfter(id,pid,reportId,leve,connection,ps,rs);
//存放直接后继的子头集合
List<DataModel> listResultSets = new ArrayList<DataModel>();
try {
while(rs.next()){
int _id= rs.getInt("id");
int _pid = rs.getInt("pid");
String _headvalue = rs.getString("headervalue");
int _leve = rs.getInt("leve");
//如果当前层级不是最深层
//判断该表头有没有直接后继节点
if(_leve<currentMaxLeve){
ResultSet _rs = getDirAfter(_id,_pid,reportId,_leve,connection,ps,rs);
//如果没有直接后继节点
if(!_rs.next()){
if(moveInfoMap.get("addOtherCell")==null){
moveInfoMap.put("addOtherCell", 0);
moveInfoMap.put("addOtherCell", moveInfoMap.get("addOtherCell")+1);
}else{
moveInfoMap.put("addOtherCell", moveInfoMap.get("addOtherCell")+1);
}
}
}
DataModel dm = new DataModel();
dm.setId(_id);
dm.setPid(_pid);
dm.setHeadvalue(_headvalue);
dm.setRs(rs);;
dm.setPs(ps);
dm.setConnection(connection);
dm.setReportId(reportId);
dm.setLeve(_leve);
dm.setMoveInfoMap(moveInfoMap);
listResultSets.add(dm);
// getDirAfter(_id, _pid, reportId, maxCurrentLeveMegreCell, connection, ps, rs);
//break;
// System.out.println("leve 2ID"+_id+"Pid"+_pid+"头名"+_headvalue+""+maxCurrentLeveMegreCell);
}
/**
* 判断该表的最终级别有几列 说明该表的合并列为多少
* 所有单元格的合并最终是看当前表头开始它的最终有多少个子表头
* 也就是最后一行
*/
if(listResultSets!=null&&listResultSets.size()>0){
for(int i = 0 ;i<listResultSets.size() ; i ++){
DataModel dModel = listResultSets.get(i);
getMax(dModel.getId() , dModel.getPid(),
dModel.getRs(),
dModel.getPs(),
dModel.getConnection(),
dModel.getReportId(), dModel.getLeve(),dModel.getMoveInfoMap(),
currentMaxLeve
);
}
}
} catch (SQLException e2) {
// TODO Auto-generated catch block
e2.printStackTrace();
}
}
}
