动态行转列
来源:互联网 发布:排名优化网站建设 编辑:程序博客网 时间:2024/04/28 03:28
由于项目的需要,做一个报表工具
要求: 首先能使查寻出来的结果自动行转列,
然后拼接成表格字符串
使用者只需调用String getTableSytle()方法就可以得到一个完整的表格,它的参数是
sql1: 查询要显示的内容,
sql2: 查询要显示的列标题,
sql3: 查询纵向总加的语句
结果可以见图片
- import java.util.*;
- public class ReportTool {
- /*
- * param@ String sql1 "select e.DataDomain_Name,q.sTransMode, q.total from (select d.DataDomain_Id as DataDomain_Id ,d.DataDomain_Name as DataDomain_Name from tDataDomain d where 1=1 ) e left join (select p.sDataDomain as sDataDomain, p.sTransMode as sTransMode ,isnull( ceiling(round( avg(p.iCalcuValue),0)),0) as total from tbAnaNeEquip p where 1=1 and p.dMpRptType >= '2008-06-21' and p.dMpValue <= '2008-07-22' group by p.sTransMode,p.sDataDomain ) q on e.DataDomain_Id = q.sDataDomain order by q.sDataDomain , q.sTransMode"
- *
- * param@ String sql2 "select p.sTransMode from tbAnaNeEquip p where 1=1 group by p.sTransMode order by p.sTransMode"
- *
- * param@ String sql3 "select p.sTransMode ,isnull(ceiling(round( avg(p.iCalcuValue),0)),0) from tbAnaNeEquip p where 1=1 and p.dMpValue >= '2008-06-21' and p.dMpRptType <= '2008-07-22' group by p.sTransMode order by p.sTransMode"
- *
- * param@ String domainId 数据库所属域
- *
- * return@ String
- *
- * function@
- *
- *
- */
- public static String getTableSytle(String sql1, String sql2, String sql3, String domainid){
- List<String> titleList = new ArrayList<String>(); //放置标题的集合
- List<ReportBean> ReportBeanList = new ArrayList<ReportBean>(); //放置行的集合
- List<String[]> newList = new ArrayList<String[]>(); //行转列后新的集合
- String[] totalArray = null; //纵向相加的后的数字的数组
- int totalArraySize = 0;
- ReportBeanList = getRow(sql1,domainid); //得到行的集合
- titleList = getComlun(sql2,domainid); //得到标题
- //**********************把标题放置到集合中*****************
- String[] titleArray = new String[titleList.size()+ 2];
- titleArray[0] = "所在区域";
- for(int j = 0 ; j < titleList.size(); j++ ){
- titleArray[j+1] = (String)titleList.get(j);
- }
- titleArray[titleList.size()+ 1] = "合计";
- newList.add(titleArray);
- //******************************************************
- for(int i = 0 ; i < ReportBeanList.size(); i++){
- ReportBean newReportBean = (ReportBean)ReportBeanList.get(i);
- Map<String,String> map = newReportBean.getMap();
- int size = titleList.size()+ 2;
- totalArraySize = titleList.size()+ 2;
- String[] tempArray = new String[size];
- tempArray[0] = newReportBean.getDomain();
- long total = 0;
- for(int k = 0 ; k < titleList.size(); k++){
- if(null != map.get((String)titleList.get(k))){
- tempArray[k+1] = map.get((String)titleList.get(k));
- total += Long.parseLong(map.get((String)titleList.get(k)));
- }else{
- tempArray[k+1] = "--";
- total += 0;
- }
- }
- tempArray[size - 1] = Long.toString( total);
- newList.add(tempArray);
- }
- totalArray = getTotal(sql3,domainid,titleList);
- newList.add(totalArray);
- return toTable(newList);
- }
- /* param@ List<String[]> list 行集合
- * return@ String 字符串
- * function@ 把行集合的记录迭代成一个table的样式
- */
- private static String toTable(List<String[]> list ){
- StringBuffer table = new StringBuffer("<table class=table_line>");
- for(int i = 0 ; i < list.size(); i++){
- table.append("<tr class=table_line>");
- String[] tempArray = (String[])list.get(i);
- for(int k = 0; k < tempArray.length ; k++){
- if(i == 0){
- table.append("<td class=tabel_head>");
- }else{
- table.append("<td class=total_digital>");
- }
- table.append(tempArray[k]);
- table.append("</td>");
- }
- table.append("</tr>");
- }
- table.append("</table>");
- return table.toString();
- }
- /*
- *
- * param@ String sql 查询语句
- * param@ String domainId 数据库所属域
- * return@ List<ReportBean> 包含ReportBean的集合
- * function@ 把查询到的结果按domain合并成一个ReportBean,并把ReportBean放置到一个集合中
- *
- */
- private static List<ReportBean> getRow(String sql,String domain){
- List<ReportBean> rowList= new ArrayList<ReportBean>();
- ResultSet rs = null;
- com.catt.db.DBTool dbtool = new com.catt.db.DBTool();
- try{
- rs = dbtool.getResultSet(sql, domain);
- //System.out.println("ssss");
- ReportBean newReportBean = null;
- Map<String,String> map = null;
- String domainId = "";
- while(rs!=null&&rs.next()){
- if("".equals(domainId)){
- domainId = rs.getString(1) == null ? "未知":rs.getString(1);
- newReportBean = new ReportBean();
- map = new HashMap<String,String>();
- newReportBean.setDomain(domainId);
- }if(domainId.equals(rs.getString(1) == null ? "未知":rs.getString(1))){
- if(null!= rs.getString(2)){
- map.put(rs.getString(2), rs.getString(3));
- }else{
- map.put(null, "--");
- }
- }if(!domainId.equals(rs.getString(1) == null ? "未知":rs.getString(1))){
- domainId = rs.getString(1) == null ? "未知":rs.getString(1);
- if(newReportBean != null){
- newReportBean.setMap(map);
- rowList.add(newReportBean);
- }
- newReportBean = new ReportBean();
- newReportBean.setDomain(domainId);
- map = new HashMap<String,String>();
- if(null!= rs.getString(2)){
- map.put(rs.getString(2), rs.getString(3));
- }else{
- map.put(null, "--");
- }
- }
- }
- newReportBean.setMap(map);
- rowList.add(newReportBean);
- rs.close();
- }catch(Exception e){
- e.printStackTrace();
- }finally{
- dbtool.closeCon();
- }
- return rowList;
- }
- /* param@ String sql 查询语句
- * param@ String domainId 数据库所属域
- * return@ List<String> 字符串集合
- * function@ 查询标题并把它们放置在一个集合中
- */
- public static List<String> getComlun(String sql,String domainId){
- List<String> list = new ArrayList<String>() ;
- ResultSet rs = null;
- com.catt.db.DBTool dbtool = new com.catt.db.DBTool();
- try{
- rs = dbtool.getResultSet(sql, domainId);
- while(rs!=null&&rs.next()){
- list.add(rs.getString(1));
- }
- rs.close();
- }catch(Exception e){
- e.printStackTrace();
- }finally{
- dbtool.closeCon();
- }
- return list;
- }
- /* param@ String sql 查询语句
- * param@ String domainId 数据库所属域
- * param@ int size 字符串数组的长度
- * return@ String[] 字符串数组
- * function@ 获取到纵向相加的值
- */
- private static String[] getTotal(String sql , String domainId ,List<String> ComlunList ){
- String[] array = new String[ComlunList.size()+2];
- ResultSet rs = null;
- com.catt.db.DBTool dbtool = new com.catt.db.DBTool();
- array[0] = "合计";
- int i = 1;
- long total = 0;
- try{
- rs = dbtool.getResultSet(sql, domainId);
- Map<String ,String> tempMap = new HashMap<String,String>();
- while(rs!=null&&rs.next()){
- tempMap.put(rs.getString(1),rs.getString(2));
- }
- for(int k = 0; k < ComlunList.size(); k++ ){
- String comlunName = (String)ComlunList.get(k);
- String value = "0";
- if( tempMap.get(comlunName) != null){
- value = tempMap.get(comlunName);
- }
- array[i] = value;
- total += Long.parseLong(value);
- i++;
- }
- array[ComlunList.size()+1] = Long.toString(total);
- rs.close();
- }catch(Exception e){
- e.printStackTrace();
- }finally{
- dbtool.closeCon();
- }
- return array;
- }
- }
- 动态行转列
- 动态行转列
- 动态行转列
- 动态行转列
- 行转列的动态方法
- MySQL动态行转列
- 动态行转列(爱新觉罗)
- 行转列动态sql
- 行转列(动态列)
- sql动态行转列
- sql动态行转列
- 三表动态行转列
- --三表动态行转列
- sql2008 动态行转列 显示
- 动态的行转列
- Oracle 动态行转列
- 数据库动态行转列
- 动态行转列 oracle环境
- Linq to object ex
- 通过oracle,是同pl/sql实现发送mail功能。
- 一个特牛的日期时间判断正则表达式
- asp.net 打开文件.cs
- Microsoft.Net里面数字签名技术
- 动态行转列
- c#创建似outlook的功能的项目
- 3秒后将自动 转到首页
- Rman中的format格式介绍
- 关于游标cursor、游标引用 ref cursor
- DBA的职责
- 通过oracle,调用java类,并加载jar包到oracle中以支持java类。
- 反向链接简介
- 上传进度条代码