一、miniUI官方前台页面代码

     导出Excel    
         
     
                              导出Excel
          
                     
      员工姓名：
              
                      
          
     
    
             
                  
       
            
       
        员工帐号
       
                
       
        姓名
       
                                        
       
        性别
       
            
       
        薪资
       
                                                
       
        年龄
       
            
       
        创建日期
       
                        
      
    
     
                   
          
             
          
     
            
             
      Description

这里主要与导出相关的代码就是ExportExcel()这个JS方法，主要是将页面上的表头columns以json的形式通过excelForm 这个表单传给后台。

JAVA这里跟官方页面的代码有所不同，官方页面实现方式是.Net。

这个form表单中的action填写为自己对应的JSP页面即可。

二、官方JAVA后台代码

JXLExample.jsp文件

<%@page import="java.io.OutputStream"%><%@page import="jxl.Workbook"%><%@page import="jxl.write.Label"%><%@page import="jxl.write.WritableSheet"%><%@page import="jxl.write.WritableWorkbook"%><%@page import="javax.print.attribute.standard.Finishings"%><%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8" import="java.util.*,Test.*,java.lang.reflect.*"%><%             request.setCharacterEncoding("UTF-8");    response.setCharacterEncoding("UTF-8");            excelWrite(request, response);    %><%!public HashMap SearchEmployees(HttpServletRequest request, HttpServletResponse response) throws Exception{         //查询条件    String key = request.getParameter("key");    //字段排序    String sortField = request.getParameter("sortField");    String sortOrder = request.getParameter("sortOrder");    HashMap result = new Test.TestDB().SearchEmployees(key, 0, 10000, sortField, sortOrder);    return result;}public void excelWrite(HttpServletRequest request, HttpServletResponse response) throws Exception{        OutputStream out=response.getOutputStream();    String fname = "grid";    response.reset();//清空输出流        response.setCharacterEncoding("UTF-8");//设置相应内容的编码格式    fname = java.net.URLEncoder.encode(fname,"UTF-8");    response.setHeader("Content-Disposition","attachment;filename="+new String(fname.getBytes("UTF-8"),"GBK")+".xls");    response.setContentType("application/ms-excel");//定义输出类型    String json = request.getParameter("columns");    ArrayList rows = (ArrayList)Test.JSON.Decode(json);    HashMap data = SearchEmployees(request, response);//需要的数据       try {                 // 获得开始时间                // long start = System.currentTimeMillis();                         // 创建Excel工作薄                 WritableWorkbook workbook = Workbook.createWorkbook(out);                 // 添加第一个工作表并设置第一个Sheet的名字               WritableSheet sheet = workbook.createSheet("grid1", 0);                 Label label;            //写出列名            for(int i=0;i

这里最主要需要修改的代码就是

ArrayList rows = (ArrayList)Test.JSON.Decode(json);

这里是对前台传过来的表头的json字符串转化为list列表。

前台传递过来的JSON字符串如下：

[{\"header\":\"\\n\\t\\t\\t\\t\\t\\t内容\\n\\t\\t\\t\\t\\t\",\"field\":\"FYXMC\"},{\"header\":\"\\n\\t\\t\\t\\t\\t\\t上一年度实际开支（元）\\n\\t\\t\\t\\t\\t\",\"field\":\"ZFY\"},{\"header\":\"\\n\\t\\t\\t\\t\\t\\t本年度预算（元）\\n\\t\\t\\t\\t\\t\",\"field\":\"ZFY\"},{\"header\":\"\\n\\t\\t\\t\\t\\t\\t内容说明\\n\\t\\t\\t\\t\\t\",\"field\":\"SM\"}]

JAVAminiUI试用版的文件结构如下：（JSONTEST.JAVA这个文件是我加的测试文件）

Test.JSON.Decode()这个方法需要用到StringUtil.java和DateTransformer.java，将相关的三个文件都考到自己miniUI web项目下即可使用Test.JSON.Decode()这个方法。

public HashMap SearchEmployees(HttpServletRequest request, HttpServletResponse response) throws Exception{   　　。。。。。。}

HashMap data = SearchEmployees(request, response);

以上这两行代码就是从自己的项目取数据并且做拼接。miniUI只是前段框架，所以excel导出还是需要经过后台取数。

只要取出的数据符合List<HashMap>的数据结构形式，就可以直接使用剩余其他代码，SearchEmployees方法相关的代码都可以不要。List<HashMap>的Map里存放的是一列数据的哈希集合。

三、本人的取数方法

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import javax.sql.RowSet;

import com.bl.platform.tools.json.JSONArray;

import com.bl.platform.tools.json.JSONException;

import com.bl.platform.tools.json.JSONObject;

import com.sdjxd.pms.platform.data.DbOper;

public class bmfy_zb {    public static String nd = "";    public static String btxbm = "";    public static String txbm = "";    public static String nr = "";    public static String isLD = "";    /**     *      * @param ysbh 预算编号     * @param btxbm 被填写部门     * @param txbm 填写部门     * @return JSON对象数组     */    public static JSONArray deptJSON_Array(String nd,String btxbm,String txbm,String nr,String isLD){        JSONArray rows = new JSONArray();        String flownodeid = "0";//2为归档，0为开始，由于数据有限，先用起始阶段的数据进行测试        String txbmsql = "";        String fyzgbmsql = "";        if("2".equals(isLD)){
   //如果是领导            txbmsql = "";            fyzgbmsql = "";        }else if("1".equals(isLD)){
   //如果是主管部门            txbmsql = " AND TXBM = '"+txbm+"' ";            fyzgbmsql = "AND FYZGBMMC = '"+txbm+"' ";        }else if("3".equals(isLD)){
   //如果是普通部门            txbmsql = "";            fyzgbmsql = "";        }        String sql1 = "SELECT FYXID, FFXID, ZFY, FYXMC, SM FROM (SELECT SUM(YSFY) AS ZFY, FYXMC FROM (SELECT F.* FROM (SELECT E.* FROM (SELECT MAX(NODEINSTANCEID) AS NODEINSTANCEID, FLOWINSTANCEID FROM (SELECT * FROM JXD7_WF_NODEINSTANCE WHERE FLOWID = 'B1E7FA2F-E857-4135-8009-47C3159FC9AE' ) GROUP BY FLOWINSTANCEID ) D LEFT JOIN JXD7_WF_NODEINSTANCE E ON D.FLOWINSTANCEID = E.FLOWINSTANCEID WHERE D.NODEINSTANCEID = E.NODEINSTANCEID AND E.FLOWNODEID = '"+flownodeid+"' ) A LEFT JOIN JXD7_WF_FORMINSTANCE B ON A.FLOWINSTANCEID = B.FLOWINSTANCEID LEFT JOIN BL_T_CB_NDBMFYYSBZB C ON B.FORMINSTANCEID = C.FORMINSTANCEID LEFT JOIN BL_T_CB_NDBMFYYSZXB F ON C.YSBH = F.YSBH WHERE 1 = 1 AND BTXBM = '" + btxbm +"'" + txbmsql +" AND C.YSND = '"+nd+"' ) GROUP BY FYXMC ) G LEFT JOIN BL_T_CB_GSCMFYXB H ON G.FYXMC = H.FYXNR WHERE 1=1 "+fyzgbmsql;        String sql2 = " UNION SELECT FYXID, FFXID, ZFY, FYXMC, SM FROM BL_T_CB_GGFYXB H LEFT JOIN (SELECT SUM(YSFY) AS ZFY, FYXMC FROM (SELECT F.* FROM (SELECT E.* FROM (SELECT MAX(NODEINSTANCEID) AS NODEINSTANCEID, FLOWINSTANCEID FROM (SELECT * FROM JXD7_WF_NODEINSTANCE WHERE FLOWID = 'B1E7FA2F-E857-4135-8009-47C3159FC9AE' ) GROUP BY FLOWINSTANCEID ) D LEFT JOIN JXD7_WF_NODEINSTANCE E ON D.FLOWINSTANCEID = E.FLOWINSTANCEID WHERE D.NODEINSTANCEID = E.NODEINSTANCEID AND E.FLOWNODEID = '"+flownodeid+"' ) A LEFT JOIN JXD7_WF_FORMINSTANCE B ON A.FLOWINSTANCEID = B.FLOWINSTANCEID LEFT JOIN BL_T_CB_NDBMFYYSBZB C ON B.FORMINSTANCEID = C.FORMINSTANCEID LEFT JOIN BL_T_CB_NDBMFYYSZXB F ON C.YSBH = F.YSBH WHERE 1 = 1 AND BTXBM = '"+btxbm +"'"+ txbmsql+" AND C.YSND = '"+nd+"' ) GROUP BY FYXMC ORDER BY FYXMC ) G ON G.FYXMC = H.FYXNR ";        String sql = "";        if(!"".equals(nr)){            sql1 += " AND FYXMC LIKE '%"+nr+"%'";            sql2 += " AND FYXMC LIKE '%"+nr+"%'";        }        if("公司层面费用".equals(btxbm)){            sql = sql1 + " ORDER BY FYXMC ";        }else{            sql = "SELECT * FROM (" +sql1 + sql2 + ") ORDER BY FYXMC ";        }        sql = "SELECT * FROM (" + sql + ") WHERE FYXID IS NOT NULL";//过滤无效数据        if(btxbm==""){            return rows;        }        try {            RowSet rs = DbOper.executeQuery(sql);            while(rs.next()){                JSONObject row = new JSONObject();                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {                    try {                        row.put(rs.getMetaData().getColumnName(i).toUpperCase(),                                rs.getObject(i));                    } catch (JSONException e) {                        e.printStackTrace();                    }                }                rows.put(row);            }        } catch (SQLException e) {            e.printStackTrace();        }        return rows;    }        /**     * 返回JSON字符串     * @param nd     * @param btxbm     * @param txbm     * @param nr     * @param isLD     * @return     */    public static String deptJSON(String nd1,String btxbm1,String txbm1,String nr1,String isLD1){        nd = nd1;//保存查询条件        btxbm = btxbm1;        txbm = txbm1;        nr = nr1;        isLD = isLD1;        return deptJSON_Array(nd1,btxbm1,txbm1,nr1,isLD1).toString();    }    /**         * 将json对象解析成Map对象          * json格式：{"name":"admin","retries":"3fff","testname"         * :"ddd","testretries":"fffffffff"}         */         private static HashMap
      
        JSONOBJECTtoHashMap(JSONObject object)         {             HashMap
       
         data = new HashMap
        
         ();             // 将json字符串转换成jsonObject             JSONObject jsonObject = object;            Iterator
         
           it = jsonObject.keys();             // 遍历jsonObject数据，添加到Map对象             try {               while (it.hasNext())                 {                         String key = it.next();                      String value = jsonObject.getString(key);                              data.put(key, value);                 }             } catch (JSONException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }             return data;         }             /**       * 将查询结果转化成ListMap供导出EXCEL使用       * @return       */     public static List
          
            toListMap(){ List
           
             list = new ArrayList
            
             (); JSONArray rows = deptJSON_Array(nd,btxbm,txbm,nr,isLD); try { for(int i=0;i
             
               map = JSONOBJECTtoHashMap((JSONObject) rows.get(i)); list.add(map); } } catch (JSONException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; }}

将deptJSON_Array和deptJSON分开是因为，在网上没有找到json字符串转化为json对象的包，所以就拆开来，导出的时候使用deptJSON_Array方法可以根据保存的查询条件重新查询，直接输出JSON对象，然后使用JSONOBJECTtoHashMap方法转成hashMap。

之后就符合一列数据一个Map这个数据结构，然后用toListMap方法加到List中即可。

相应的JXLEXAMPLE.JSP中相关代码要改一下：

ArrayList list = (ArrayList)data.get("data");

改成

List
     
       list = bmfy_zb.toListMap();

以下下代码可以删除

public HashMap SearchEmployees(HttpServletRequest request, HttpServletResponse response) throws Exception{   　　。。。。。。}HashMap data = SearchEmployees(request, response);

导出结果如下：

四、结语

由于涉及到的东西有点杂，怕忘记了所以做以上记录。

转载请注明出处：

转载于:https://www.cnblogs.com/CryOnMyShoulder/p/7760029.html

你可能感兴趣的文章

【MPI学习3】MPI并行程序设计模式：不同通信模式MPI并行程序的设计