ChinaExcel报表平台搭建（二基本查询）

   首先做基本查询的设计，使用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 blocke.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() {@Overridepublic 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语句，返回值设定xmlStringif (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的方式，翻页的处理和这个类似，就不再详述。

下一部分将要描述带参数的报表查询方式（内，外部查询可以通用），请期待。