首先做基本查询的设计,使用ChinaExcel.SetStatDataSource设置Action(服务端)的url路径,由后台从数据库查询出数据返回给页面。
1:设计流程:
2:前端的封装部分
/**
* 分析统计脚本
*/
readStatScript : function() {
var strStatScript = ChinaExcel.GetStatScript(1);
if (strStatScript.length != 0) {
var strQueryParameter = strQueryParameterUrl();
// alert(strQueryParameter);
this.initStatScript(strStatScript);
var jsonObject = this.createJosnObject();
Ext.apply(jsonObject, this.objParameter);
for (i = 1; i <= this.dcount; i++)// 根据数据源名重新设置取数方式
{
var url = "RptFrmGet_getTabFileData";
var value = "";
jsonObject.func = "GetSqlResult";
jsonObject.dtype = this.dtype[i];
if (this.dSQL1[i].indexOf("${") == -1
&& this.dSQL1[i].indexOf("${") == -1) {
value = "?jsonObject=" + Ext.encode(jsonObject) + "&sql="
+ encodeURIComponent(this.dSQL1[i]) + "&sql2="
+ encodeURIComponent(this.dSQL2[i]);
} else {
value = "?jsonObject=" + Ext.encode(jsonObject) + "&sql="
+ encodeURIComponent(this.dSQL1[i]) + "&sql2="
+ encodeURIComponent(this.dSQL2[i]) + strQueryParameter;
}
ChinaExcel.SetStatDataSource1(url + value, 2, this.dname[i]);
}
// ChinaExcel.SetOnlyShowTipMessage(true);
}
}
cal : function() {
ChinaExcel.DesignMode = false;
// ChinaExcel.Calculate();
ChinaExcel.ReCalculate();
ChinaExcel.SetOnlyShowTipMessage(false);
ChinaExcel.SetCanRefresh(true);
}
3:action的处理部分
/*
* 获取报表文件的数据
*/
public void getTabFileData() throws IOException {
try {
// 请求参数的获取
HttpServletRequest req = ServletActionContext.getRequest();
String strSQL1 = new String(req.getParameter("sql").getBytes(
"ISO-8859-1"), "UTF-8");
String strSQL2 = new String(req.getParameter("sql2").getBytes(
"ISO-8859-1"), "UTF-8");
// 动态参数的处理(内部查询,级联)
Enumeration paramNames = req.getParameterNames();
while (paramNames.hasMoreElements()) {
String paramName = (String) paramNames.nextElement();
String[] paramValues = req.getParameterValues(paramName);
if (paramValues.length == 1) {
paramName = new String(paramName.getBytes("ISO-8859-1"),
"UTF-8");
String paramValue = new String(
paramValues[0].getBytes("ISO-8859-1"), "UTF-8");
System.out.println(paramName + " " + paramValue);
if (!paramName.equals("undefined")
&& !paramName.equals("func")
&& !paramName.equals("dtype")
&& !paramName.equals("sql")
&& !paramName.equals("sql2")) {
strSQL1 = strSQL1.replace("${" + paramName + "}",
paramValue);
if (strSQL2 != "") {
strSQL2 = strSQL2.replace("${" + paramName + "}",
paramValue);
}
}
}
}
// 对转义后字符串进行反向操作
strSQL1 = HtmlUtils.htmlUnescape(strSQL1);
strSQL2 = HtmlUtils.htmlUnescape(strSQL2);
// 请求数据的封装
JSONObject jsonObj = JSONObject.fromObject(this.jsonObject);
jsonObj.put("sql1", strSQL1);
jsonObj.put("sql2", strSQL2);
// 获取报表数据处理
String data = tabDataSrv.getTabFileData(jsonObj);
responsePage(data);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
writeJson(false,e.getMessage());
}
}
4:dao层请求数据的获取
/**
* 从数据库中获取返回到报表页面的数据
*
* @param jsonObj
* @param jsonPrmt
* @return data字符串
*/
public String getTabFileData(final JSONObject jsonObj) {
System.out.println("getFunc " + jsonObj.getString("func"));
System.out.println("getDtype " + jsonObj.getString("dtype"));
System.out.println("getSql1 " + jsonObj.getString("sql1"));
System.out.println("getSql2 " + jsonObj.getString("sql2"));
this.getSession().doWork(new Work() {
@Override
public void execute(Connection con) throws SQLException {
// 1:变量定义
String strType = jsonObj.getString("dtype");
String strSQL1 = jsonObj.getString("sql1");
String strSQL2 = jsonObj.getString("sql2");
String tempValue;
String xmlstring = "";
ArrayList xmlfieldnames = new ArrayList();
Statement odbcstmt = con.createStatement();
// 2:对应第一条sql语句,返回值设定xmlString
if (strType.equals("2")) {
xmlstring = "=" + "\n";
}
if (strSQL1 == "") {
xmlstring = xmlstring + "\n";
} else {
xmlstring = getXmlString(strSQL1, xmlstring, xmlfieldnames,
odbcstmt);
}
// 3:以下处理和上面类似,对应主从表的第二条sql语句
if (strType.equals("2")) {
xmlstring = xmlstring + "=" + "\n";
xmlfieldnames.clear();
if (strSQL2 != "") {
xmlstring = getXmlString(strSQL2, xmlstring, xmlfieldnames,
odbcstmt);
}
}
odbcstmt.close();
jsonObj.put("xmlstring", xmlstring);
}
/**
* 检索字符串结果设定
*
* @param strSQL
* @param xmlstring
* @param xmlfieldnames
* @param odbcstmt
* @return
* @throws SQLException
*/
private String getXmlString(String strSQL, String xmlstring,
ArrayList xmlfieldnames, Statement odbcstmt)
throws SQLException {
String tempValue;
String xmlfieldname;
ResultSet odbcrs;
ResultSetMetaData metaDate;
int fieldnumber;
int i;
odbcrs = odbcstmt.executeQuery(strSQL);
metaDate = odbcrs.getMetaData();
fieldnumber = metaDate.getColumnCount();
// 2-1:为了修正数据库字段存在同名部分的问题,对字段升序后再进行报表处理
String[] tempfieldAry = new String[fieldnumber];
for (i = 1; i <= fieldnumber; i++) {
tempfieldAry[i - 1] = metaDate.getColumnName(i)
.toString();
}
for (i = 0; i < tempfieldAry.length - 1; i++) {
for (int j = i + 1; j < tempfieldAry.length; j++) {
if (tempfieldAry[i].length() > tempfieldAry[j]
.length()) {
String temp = tempfieldAry[i];
tempfieldAry[i] = tempfieldAry[j];
tempfieldAry[j] = temp;
}
}
}
// 2-2:获得字段头
for (i = 1; i <= fieldnumber; i++) {
xmlfieldname = tempfieldAry[i - 1];
if (xmlstring.indexOf(xmlfieldname) == -1) {
xmlstring = xmlstring + xmlfieldname;
if (i == fieldnumber) {
xmlstring = xmlstring + "\n";
} else {
xmlstring = xmlstring + "\t";
}
xmlfieldnames.add(xmlfieldname);
}
}
// 2-3:获得字段对应数值
while (odbcrs.next()) {
fieldnumber = xmlfieldnames.size();
for (i = 1; i <= fieldnumber; i++) {
tempValue = odbcrs.getString(xmlfieldnames.get(
i - 1).toString());
if (StringUtils.isEmpty(tempValue)) {
tempValue = "";
}
xmlstring = xmlstring + tempValue;
if (i == fieldnumber) {
xmlstring = xmlstring + "\n";
} else {
xmlstring = xmlstring + "\t";
}
}
}
odbcrs.close();
return xmlstring;
}
});
return jsonObj.getString("xmlstring");
}
补充说明:参照官方的例子流程解析报表请求(修正了它的部分bug),使用hibernate中调用jdbc的方式,翻页的处理和这个类似,就不再详述。
下一部分将要描述带参数的报表查询方式(内,外部查询可以通用),请期待。