润乾ajax,润乾报表-动态生成交叉报表

package com.supporter.prj.bi.util;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import com.runqian.report4.usermodel.Area;

import com.runqian.report4.usermodel.ByteMap;

import com.runqian.report4.usermodel.Context;

import com.runqian.report4.usermodel.DataSetMetaData;

import com.runqian.report4.usermodel.Engine;

import com.runqian.report4.usermodel.IByteMap;

import com.runqian.report4.usermodel.INormalCell;

import com.runqian.report4.usermodel.IReport;

import com.runqian.report4.usermodel.SQLDataSetConfig;

import com.runqian.report4.util.ReportUtils;

import com.supporter.prj.bi.query.entity.CustomQueryCol;

import com.supporter.prj.log.LoggerClient;

// ~ File Information

//

====================================================================================================================

public class TestCroosReport {

public static void main(String[] args) {

List cols = new

ArrayList();

CustomQueryCol col = new

CustomQueryCol();

col.setColInnerName("BUDGET_MONTH");

col.setColName("BUDGET_MONTH");

cols.add(col);

// CustomQueryCol col2 = new

CustomQueryCol();

// col2.setColInnerName("PROC_INNER_NAME");

// col2.setColName("PROC_INNER_NAME");

// cols.add(col2);

// // CustomQueryCol col3 = new

CustomQueryCol();

// col3.setColInnerName("BORROW_STATUS");

// col3.setColName("BORROW_STATUS");

// cols.add(col3);

List rows = new

ArrayList();

CustomQueryCol row0 = new

CustomQueryCol();

row0.setColInnerName("BUDGET_DEPT_NAME");

row0.setColName("BUDGET_DEPT_NAME");

rows.add(row0);

// CustomQueryCol row1 = new

CustomQueryCol();

// row1.setColInnerName("APPLY_DEPT_NAME");

// row1.setColName("APPLY_DEPT_NAME");

// rows.add(row1);

// CustomQueryCol row2 = new

CustomQueryCol();

// row2.setColInnerName("APPLIER_NAME");

// row2.setColName("APPLIER_NAME");

// rows.add(row2);

// CustomQueryCol row3 = new

CustomQueryCol();

// row3.setColInnerName("CURRENCY_CATEGORY_ID");

// row3.setColName("CURRENCY_CATEGORY_ID");

// rows.add(row3);

List funs = new

ArrayList();

CustomQueryCol fun = new

CustomQueryCol();

fun.setFunName("COUNT()");

fun.setColName("COUNT");

fun.setColInnerName("COUNT");

funs.add(fun);

// CustomQueryCol fun2 = new

CustomQueryCol();

// fun2.setFunName("sum(TOTAL_AMOUNT_APPLIED)");

// fun2.setColName("");

// fun2.setColInnerName("申请额");

// funs.add(fun2);

TestCroosReport

testCroosReport = new TestCroosReport();

IReport report

= testCroosReport.createCrossReport(cols, rows,

funs, true);

try {

FileOutputStream

fileOutputStream = new

FileOutputStream("E:/java_workspace/reportTest/new.raq");

ReportUtils.write(fileOutputStream,

report);

} catch (FileNotFoundException

e) {

e.printStackTrace();

} catch (Exception e) {

e.printStackTrace();

}

System.out.println("报表生成完毕。。。。");

}

public IReport createCrossReport( List

cols,List rows,

List funs, boolean isSaveAs) {

IReport

ireport = null;

try {

String path =

"E:/java_workspace/reportTest/CRTEN.raq";

ireport =

ReportUtils.read(path);

} catch (Exception e)

{ LoggerClient.getLogger().error("getIconList():"

+ e.getMessage());

}

DataSetMetaData dsmd=new

DataSetMetaData(); //构造数据集元数据

// 第一数据源

SQLDataSetConfig sdc=new

SQLDataSetConfig(); //构造数据集定义

sdc.setName("ds1");

//设置数据集名

sdc.setSQL("select b.BUDGET_DEPT_NAME,b.APPLY_DEPT_NAME,b.APPLIER_NAME,b.CURRENCY_CATEGORY_ID,b.BUDGET_MONTH,b.PROC_INNER_NAME,b.BORROW_STATUS,

b.BORROW_ID,b.TOTAL_AMOUNT_APPLIED from bm_2015.BM_BORROW b");

//设置sql语句

sdc.setDataSourceName("bmv5");//设置数据源

dsmd.addDataSetConfig(sdc);

//把数据集定义添加到数据集元数据

ireport.setDataSetMetaData(dsmd);//把数据集元数据赋给IReport

int queryRowLen =

rows.size();

int queryColumnsLen =

cols.size();

int queryDataLen =

funs.size();

// 删除多余的行，，报表中共10行

for (int row = (queryColumnsLen

+ 1); row <= 10; row ++) {

ireport.removeRow(2);

}

// 删除多余的列，，报表中共10列

for (int col = (queryRowLen +

1); col <= 10; col ++) {

ireport.removeCol((short)1);

}

// 删除多余的函数区，，报表中共10个数据区

int delStartColIndex =

queryDataLen + 11;

if(queryDataLen >

1){//如果增加了自定义合计列

delStartColIndex++;

}

for (int col =

delStartColIndex; col <= 20; col ++) {

ireport.removeCol((short)(queryRowLen

+ 1));

}

// 斜线区

INormalCell cell0 =

ireport.getCell(2,(short)1);

String queryName =

"行维度,列维度,汇总";

cell0.setValue(queryName);

//行

for (int i = 1; i <=

queryRowLen; i ++) {

INormalCell

cell4 = ireport.getCell((queryColumnsLen + 3),(short)(i));

IByteMap map4

= new ByteMap();

map4.put(INormalCell.VALUE,

"ds1.Group(" + rows.get(i-1).getColInnerName()+", false)");

cell4.setExpMap(map4);

cell4.setExtendMode(INormalCell.EXTEND_VERTICAL);

}

//增加统计行

for(int i = 1; i <=

queryRowLen;i++){

ireport.addRow();

}

//合并统计行

int mergeStartIndex =

queryColumnsLen + 3 + 1;

int mergeEndIndex

= queryColumnsLen + 3 +

queryRowLen;

int handleRow = 0;

for(int mergeIndex =

mergeStartIndex; mergeIndex <=

mergeEndIndex;mergeIndex++){

//设置单元格值、合并行

for(int i =

queryRowLen - handleRow;i <= queryRowLen;i++){

//合并行单元格

Area

rowArea = new Area(mergeIndex, (short) (queryRowLen - handleRow),

mergeIndex, (short)(queryRowLen));

//设置单元格值,

INormalCell

cell = ireport.getCell(mergeIndex, (short) (i));

cell.setValue("合计");

cell.setMergedArea(rowArea);

//设置样式

setCellStyle(cell);

}

//设置单元格值、合并列

int

startMergeRow = mergeStartIndex - 1;

short

currMergeCol = (short) (queryRowLen - handleRow);

for(int j =

startMergeRow; j < mergeIndex ; j++){

if((mergeIndex

- mergeStartIndex) > 0 ){

Area

colArea = new

Area(startMergeRow,currMergeCol,mergeIndex-1,currMergeCol);

//设置单元格值,

INormalCell

cell = ireport.getCell(j, currMergeCol);

cell.setMergedArea(colArea);

}

}

handleRow

++;

// System.out.println("mergeIndex="+mergeIndex);

}

// 数据区名

int funLen =

queryDataLen;

if(queryDataLen > 1){

//增加一个自定义列

funLen++;

}

for (int i = 0; i < funLen;

i ++) {

if(i <

queryDataLen){

INormalCell

cell3 = ireport.getCell((queryColumnsLen + 2),(short)(queryRowLen +

1 + i));