miniUI ExcelExport导出JAVA实现
一、miniUI官方前台页面代码
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title> 导出Excel</title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <meta http-equiv="content-type" content="text/html; charset=UTF-8" /><link href="../demo.css" rel="stylesheet" type="text/css" /> <script src="../../scripts/boot.js" type="text/javascript"></script> </head> <body> <h1> 导出Excel</h1> <div style="padding-bottom:5px;"> <span>员工姓名：</span><input type="text" id="key" /> <input type="button" value="查找" onclick="search()"/> <input type="button" value="导出Excel" onclick="ExportExcel()" style="margin-left:50px;"/> </div> <div id="datagrid1" class="mini-datagrid" style="width:700px;height:280px;" url="../data/AjaxService.jsp?method=SearchEmployees" idField="id" sizeList="[5,10,20,50]" pageSize="10" > <div property="columns"> <div type="indexcolumn" ></div> <div field="loginname" width="120" headerAlign="center" allowSort="true">员工帐号</div> <div field="name" width="120" headerAlign="center" allowSort="true">姓名</div> <div field="gender" width="100" renderer="onGenderRenderer" align="center" headerAlign="center">性别</div> <div field="salary" width="100" allowSort="true">薪资</div> <div field="age" width="100" allowSort="true">年龄</div> <div field="createtime" width="100" headerAlign="center" dateFormat="yyyy-MM-dd" allowSort="true">创建日期</div> </div> </div> <iframe id="exportIFrame" style="display:none;"></iframe> <!--导出Excel相关HTML--> <form id="excelForm" action="JXLExample.jsp" method="post" target="excelIFrame"> <input type="hidden" name="columns" id="excelData" /> </form> <iframe id="excelIFrame" name="excelIFrame" style="display:none;"></iframe> <script type="text/javascript"> mini.parse(); var grid = mini.get("datagrid1"); grid.load(); //对"createtime"字段，进行降级排序 grid.sortBy("createtime", "desc"); function search() { var key = document.getElementById("key").value; grid.load({ key: key }); } $("#key").bind("keydown", function (e) { if (e.keyCode == 13) { search(); } }); /////////////////////////////////////////////////////// var Genders = [{ id: 1, text: '男' }, { id: 2, text: '女'}]; function onGenderRenderer(e) { for (var i = 0, l = Genders.length; i < l; i++) { var g = Genders[i]; if (g.id == e.value) return g.text; } return ""; } function ExportExcel() { var columns = grid.getBottomColumns(); function getColumns(columns) { columns = columns.clone(); for (var i = columns.length - 1; i >= 0; i--) { var column = columns[i]; if (!column.field) { columns.removeAt(i); } else { var c = { header: column.header, field: column.field }; columns[i] = c; } } return columns; } var columns = getColumns(columns); var json = mini.encode(columns); document.getElementById("excelData").value = json; var excelForm = document.getElementById("excelForm"); excelForm.submit(); } </script> <div class="description"> <h3>Description</h3> </div> </body> </html>
这里主要与导出相关的代码就是ExportExcel()这个JS方法，主要是将页面上的表头columns以json的形式通过excelForm 这个表单传给后台。
JAVA这里跟官方页面的代码有所不同，官方页面实现方式是.Net。
<!--导出Excel相关HTML--> <form id="excelForm" action="JXLExample.jsp" method="post" target="excelIFrame"> <input type="hidden" name="columns" id="excelData" /> </form> <iframe id="excelIFrame" name="excelIFrame" style="display:none;"></iframe>
这个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<rows.size();i++){ HashMap hm = (HashMap)rows.get(i); Iterator iterator = hm.keySet().iterator(); label = new Label(i,0,hm.get("header").toString()); sheet.addCell(label); } //写出数据 ArrayList list = (ArrayList)data.get("data"); for (int i = 1; i < list.size(); i++) { HashMap hm1 = (HashMap)list.get(i); for (int k = 1; k < hm1.size(); k++) { for(int j=0;j<rows.size();j++){ HashMap hm = (HashMap)rows.get(j); String key =hm.get("field").toString(); String value=String.valueOf(hm1.get(key)); label = new Label(j,i,value); sheet.addCell(label); } } } // 写入数据 workbook.write(); // 关闭文件 workbook.close(); out.close(); // long end = System.currentTimeMillis(); //System.out.println("----完成该操作共用的时间是:"+(end-start)/1000); } catch (Exception e) { // System.out.println("---出现异常---"); e.printStackTrace(); } } %>
这里最主要需要修改的代码就是
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对象 <li> * json格式：{"name":"admin","retries":"3fff","testname" * :"ddd","testretries":"fffffffff"} */ private static HashMap<String, String> JSONOBJECTtoHashMap(JSONObject object) { HashMap<String, String> data = new HashMap<String, String>(); // 将json字符串转换成jsonObject JSONObject jsonObject = object; Iterator<String> 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<HashMap> toListMap(){ List<HashMap> list = new ArrayList<HashMap>(); JSONArray rows = deptJSON_Array(nd,btxbm,txbm,nr,isLD); try { for(int i=0;i<rows.length();i++){ HashMap<String, String> 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<HashMap> list = bmfy_zb.toListMap();
以下下代码可以删除
public HashMap SearchEmployees(HttpServletRequest request, HttpServletResponse response) throws Exception { 。。。。。。 } HashMap data = SearchEmployees(request, response);
导出结果如下：
四、结语
由于涉及到的东西有点杂，怕忘记了所以做以上记录。
转载请注明出处：http://www.cnblogs.com/CryOnMyShoulder/p/7760029.html
本文来自博客园，作者：迷神图卷，转载请注明原文链接：https://www.cnblogs.com/CryOnMyShoulder/p/7760029.html
