用户的业务需求经常发生变化,对一些现有的固定报表势必提出新的需求,所以下面我给大家讲讲java/web树形动态报表的制作,当然表头的设计是关键.
1、首先要生成的表头可能是有关联的几张表，也有可能是有自身关系的一张表，这里我们介绍有关联关系的多张表。
2、表一,表二,表三具有上下级关系
表一
表二
表一的子表
表三
表二的子表
3、用dhtmlxTree操作数据库,把三张表组装成树形结构,如图所示:
三张表的树形结构
4、定义一个表单表和其子表（表头表），表单表字段包括id、表单名称、表单项目、表单层数。子表字段 id、显示状态、名称、自身编码、父编码、跨行数、跨列数、引用的表单编码。
选择表单项如图：
选择表头需要显示的项
5、保存表单的时候表单表和子表同时保存。其中保存的时候计算跨行跨列数和表头显示状态state,代码:
public void updateTableHeadChild(String operFormCode) {
try {
String hql = ” from OperTable where headId is not null “;
if (operFormCode != null) {
hql += ” and operFormCode =’” + operFormCode + “‘”;
}
List<OperTable> list = service.findObjs(hql);
Connection conn = new PoolDbc().getMycon();
for (int i = 0; i < list.size(); i++) {
String code = list.get(i).getHeadCode();
Obj obj = new Obj();
obj.setCode(code);
obj.setFcode(operFormCode);
int colNum = FormStringUtil.getColNum(obj, conn);
int rowNum = FormStringUtil.getRowNum(code, operFormCode, conn);
list.get(i).setHeadColNum(colNum + “”);
list.get(i).setHeadRowNum(rowNum + “”);
service.updateObj(list.get(i));
}
} catch (Exception e) {
e.printStackTrace();
}
}
FormStringUtil类
public class FormStringUtil {
public static int getColNum(Obj fobj, Connection conn) throws SQLException {
int ceng = 0;
List<Obj> list = new ArrayList<Obj>();
String sql = “select t.head_name,t.head_code,t.oper_form_code from oper_table t where t.head_parent_code = ? and t.oper_form_code = ?”;
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, fobj.getCode()); // 自身code
pstmt.setString(2, fobj.getFcode());// 表单code
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Obj obj = new Obj();
obj.setName(rs.getString(1));
obj.setCode(rs.getString(2));
obj.setFcode(rs.getString(3));
list.add(obj);
}
fobj.setSubObj(list);
try {
rs.close();
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
if (list.size() > 0) {
for (Obj obj : list) {
ceng += getColNum(obj, conn);
}
} else {
ceng = 1;
}// #if
return ceng;
}// #getObjs
public static int getRowNum(String code, String tmplCode, Connection conn)
throws SQLException {
long len = 0;
String sql = “SELECT max(length(head_code)) FROM oper_table where oper_form_code = ‘”
+ tmplCode + “‘”;
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
len = rs.getLong(1);
}
try {
rs.close();
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
String sql1 = “select t.head_name,t.head_code from oper_table t where t.head_parent_code = ? and t.oper_form_code = ?”;
PreparedStatement pstmt1 = conn.prepareStatement(sql1);
pstmt1.setString(1, code);
pstmt1.setString(2, tmplCode);
ResultSet rs1 = pstmt1.executeQuery();
boolean flag = false;
while (rs1.next()) {
flag = true;
}
try {
rs1.close();
pstmt1.close();
} catch (Exception e) {
e.printStackTrace();
}
return flag ? 1 : (int) len / 2 – code.length() / 2 + 1;
}
public static int getStateValue(Obj fobj, Connection conn) throws Exception {
int ceng = 0;
List<Obj> list = new ArrayList<Obj>();
String sql = “select t.head_name,t.head_code,t.oper_form_code from oper_table t where t.head_parent_code = ? and t.oper_form_code = ?”;
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, fobj.getCode());
pstmt.setString(2, fobj.getFcode());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Obj obj = new Obj();
obj.setName(rs.getString(1));
obj.setCode(rs.getString(2));
obj.setFcode(rs.getString(3));
list.add(obj);
}
fobj.setSubObj(list);
try {
rs.close();
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
if (list.size() > 0) {
ceng = 1;
} else {
ceng = 0;
}// #if
return ceng;
}
}
结果如下:
表单子表
6、读取子表，生成表头,代码如下:
int myj = 0；
public String getTableChildByParentCode(String parentCode,
String headParentCode, int myj, int ceng) {
StringBuffer nameTemp = new StringBuffer();
try {
String[] parentCodes = parentCode.split(“;”);
String hql = “from OperForm where tableHeadParentCode =’”
+ headParentCode + “‘ and (headParentCode=’”
+ parentCodes[0] + “‘”;
for (int i = 1; i < parentCodes.length; i++) {
hql += ” or headParentCode=’” + parentCodes[i] + “‘”;
}
hql += “) order by assessHeadId”;
List<ActiveTable> list = service.findObjs(hql);
if (list != null && list.size() > 0) {
nameTemp.append(“<tr>”);
if (myj == 0) {
nameTemp.append(“<td colspan=’1′ rowspan=’” + ceng
+ “‘>部门</td>”);
}
for (ActiveTable child : list) {
nameTemp.append(“<td colspan=’” + child.getHeadColNum()
+ “‘ rowspan=’” + child.getHeadRowNum() + “‘> “);
if ((child.getContectScore() + “”).endsWith(“.0″)) {
nameTemp.append(child.getHeadName() + “(”
+ child.getContectScore().intValue() + “)”);
} else {
nameTemp.append(child.getHeadName() + “(”
+ child.getContectScore() + “)”);
}
nameTemp.append(“</td>”);
}
if (myj == 0) {
nameTemp.append(“<td colspan=’1′ rowspan=’” + ceng
+ “‘>总分</td>”);
nameTemp.append(“<td colspan=’1′ rowspan=’” + ceng
+ “‘>排名</td>”);
}
nameTemp.append(“</tr>”);
// 取得层数相同的code,同一层的code组装起来
String code = “”;
for (ActiveTable child : list) {
code += child.getHeadCode() + “;”;
}
myj++;
nameTemp.append(getTableChildByParentCode(code, headParentCode,
myj, ceng));
}
} catch (Exception e) {
e.printStackTrace();
}
return nameTemp.toString();
}
7、页面上显示结果：
表头结果图