java springmvc poi 导出Excel，先简单记录，后期会详细描写
POI jar包下载 : http://poi.apache.org/download.html
jsp代码
1 <%@ page language="java" contentType="text/html; charset=utf-8" 2 pageEncoding="utf-8"%> 3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 4 <html> 5 <head> 6 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> 7 <title>收发文系统</title> 8 <link rel="stylesheet" type="text/css" 9 href="${pageContext.request.contextPath}/lib/easyui/themes/default/easyui.css" /> 10 <link rel="stylesheet" type="text/css" 11 href="${pageContext.request.contextPath}/lib/easyui/themes/icon.css" /> 12 <link rel="stylesheet" type="text/css" 13 href="${pageContext.request.contextPath}/lib/easyui/demo/demo.css" /> 14 <script type="text/javascript" 15 src="${pageContext.request.contextPath}/lib/easyui/jquery.min.js"></script> 16 <script type="text/javascript" 17 src="${pageContext.request.contextPath}/lib/easyui/jquery.easyui.min.js"></script> 18 <style> 19 html,body { 20 width: 100%; 21 height: 100%; 22 margin: 0; 23 padding: 0; 24 } 25 26 .easyui-datagrid { 27 position: absolute; 28 visibility: hidden; 29 } 30 </style> 31 32 </head> 33 <body> 34 <table id="dg" class="easyui-datagrid" 35 style="width: 100%; height: auto" 36 data-options=" 37 iconCls: 'icon-edit', 38 singleSelect: true, 39 toolbar: '#tb', 40 rownumbers:true, 41 fit:true, 42 pagination:true, 43 pageSize:10"> 44 <thead> 45 <tr> 46 <th data-options="field:'ID',width:100,hidden:'true'">ID</th> 47 <th data-options="field:'userName',width:100,halign: 'center'">用户名</th> 48 <th data-options="field:'userCode',width:80,halign: 'center'">登陆名</th> 49 <th 50 data-options="field:'userRole',width:80,align:'right',halign: 'center'">角色</th> 51 </tr> 52 </thead> 53 </table> 54 55 56 <div id="tb" style="padding: 5px; height: auto"> 57 <div> 58 用户名: <input class="easyui-textbox" id="query-UserName" 59 style="width: 80px"> 登陆名: <input class="easyui-textbox" 60 id="query-UserCode" style="width: 80px"> 角色: <select 61 class="easyui-combobox" id="query-UserRole" name="state" 62 style="width: 100px;"> 63 <option value="ALL">ALL</option> 64 <option value="管理员">管理员</option> 65 <option value="会员">会员</option> 66 <option value="收文员">收文员</option> 67 <option value="发文员">发文员</option> 68 </select> <a href="#" class="easyui-linkbutton" iconCls="icon-search" 69 onclick="query()">查询</a> 70 <a href="#" class="easyui-linkbutton" iconCls="icon-export" 71 onclick="userExport()">导出</a> 72 </div> 73 </div> 74 75 76 <script type="text/javascript"> 77 $(function() { 78 query(); 79 var p = $('#dg').datagrid('getPager'); 80 $(p).pagination({ 81 beforePageText : '第',//页数文本框前显示的汉字 82 afterPageText : '页 共 {pages} 页', 83 displayMsg : '第{from}到{to}条，共{total}条', 84 onSelectPage : function() { 85 //问题在这里，已经选择过的页数，再次选择不会触发onSelectPage事件，怎么取消这个缓存？？？ 86 query(); 87 } 88 }); 89 }); 90 </script> 91 <script type="text/javascript"> 92 function userExport() { 93 var userName = $('#query-UserName').textbox('getValue'); 94 var userCode = $('#query-UserCode').textbox('getValue'); 95 var userRole = $('#query-UserRole').combobox('getText'); 96 window.location.href = "/gwsfw/user/userExport?userName=" 97 + userName + "&userCode=" + userCode + "&userRole=" 98 + userRole; 99 } 100 101 function query() { 102 var options = $("#dg").datagrid("getPager").data("pagination").options; 103 var curr = options.pageNumber; 104 // alert(curr); 105 var size = options.pageSize; 106 var userName = $('#query-UserName').textbox('getValue'); 107 var userCode = $('#query-UserCode').textbox('getValue'); 108 var userRole = $('#query-UserRole').combobox('getText'); 109 $.ajax({ 110 type : "POST", 111 url : "/gwsfw/user/getList", 112 dataType : "json", 113 data : JSON.stringify({ 114 pageNumber : curr, 115 pageSize : size, 116 userName : userName, 117 userCode : userCode, 118 userRole : userRole 119 }), 120 contentType : "application/json", 121 122 success : function(data) { 123 124 $('#dg').datagrid('loadData', data); 125 }, 126 error : function(err) { 127 alert("error"); 128 } 129 }); 130 131 } 132 133 </script> 134 </body> 135 </html>
Controller 代码
1 @RequestMapping(value = "/userExport") 2 public void userExport( 3 HttpServletRequest request, 4 @RequestParam(value = "userName", required = false) String userName, 5 @RequestParam(value = "userCode", required = false) String userCode, 6 @RequestParam(value = "userRole", required = false) String userRole, 7 HttpServletResponse response) throws Exception { 8 String strWhere = " where UserStatus=0 "; 9 if (!userName.equals("")) 10 strWhere += " and UserName='" + userName + "'"; 11 if (!userRole.equals("ALL")) 12 strWhere += " and UserRole='" + userRole + "'"; 13 if (!userCode.equals("")) 14 strWhere += " and UserCode='" + userCode + "'"; 15 16 String[] titles = { "用户编号", "用户名", "登录名", "用户角色" }; 17 HSSFWorkbook workbook = bll.createExcel(titles, strWhere); 18 19 SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss"); // 定义文件名格式 20 21 try { 22 // 定义excle名称 ISO-8859-1防止名称乱码 23 String msg = new String( 24 ("用户信息_" + format.format(new Date()) + ".xls").getBytes(), 25 "ISO-8859-1"); 26 // 以导出时间作为文件名 27 response.setContentType("application/vnd.ms-excel"); 28 response.addHeader("Content-Disposition", "attachment;filename=" 29 + msg); 30 workbook.write(response.getOutputStream()); 31 } catch (IOException e) { 32 e.printStackTrace(); 33 } 34 }
bll 代码 createExcel
1 public HSSFWorkbook createExcel(String[] titles, String strWhere, String... parameter) throws Exception { 2 try { 3 // 第一步，创建一个workbook，对应一个Excel文件 4 HSSFWorkbook workbook = new HSSFWorkbook(); 5 // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet 6 HSSFSheet hssfSheet = workbook.createSheet("sheet1"); 7 // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short 8 HSSFRow hssfRow = hssfSheet.createRow(0); 9 // 第四步，创建单元格，并设置值表头 设置表头居中 10 HSSFCellStyle hssfCellStyle = workbook.createCellStyle(); 11 // 居中样式 12 hssfCellStyle.setAlignment(HorizontalAlignment.CENTER); 13 14 HSSFCell hssfCell = null; 15 for (int i = 0; i < titles.length; i++) { 16 hssfCell = hssfRow.createCell(i);// 列索引从0开始 17 hssfCell.setCellValue(titles[i]);// 列名1 18 hssfCell.setCellStyle(hssfCellStyle);// 列居中显示 19 } 20 21 // 第五步，写入实体数据 从数据库中获取List<User> 22 List<User> users = dao.queryUser(strWhere, parameter); 23 24 if (users != null && !users.isEmpty()) { 25 for (int i = 0; i < users.size(); i++) { 26 hssfRow = hssfSheet.createRow(i + 1); 27 User user = users.get(i); 28 29 // 第六步，创建单元格，并设置值 30 int userid = 0; 31 if (user.getID() != 0) { 32 userid = user.getID(); 33 } 34 hssfRow.createCell(0).setCellValue(userid); 35 String username = ""; 36 if (user.getUserName() != null) { 37 username = user.getUserName(); 38 } 39 hssfRow.createCell(1).setCellValue(username); 40 String userCode = ""; 41 if (user.getUserCode() != null) { 42 userCode = user.getUserCode(); 43 } 44 hssfRow.createCell(2).setCellValue(userCode); 45 String userRole = ""; 46 if (user.getUserRole() != null) { 47 userRole = user.getUserRole(); 48 } 49 hssfRow.createCell(3).setCellValue(userRole); 50 } 51 } 52 return workbook; 53 54 } catch (Exception e) { 55 e.printStackTrace(); 56 throw new Exception("导出信息失败！"); 57 } 58 59 }
