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=" 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',100,hidden:'true'">ID</th>
 47                 <th data-options="field:'userName',100,halign: 'center'">用户名</th>
 48                 <th data-options="field:'userCode',80,halign: 'center'">登陆名</th>
 49                 <th
 50                     data-options="field:'userRole',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=" 80px"> 登陆名: <input class="easyui-textbox"
 60                 id="query-UserCode" style=" 80px"> 角色: <select
 61                 class="easyui-combobox" id="query-UserRole" name="state"
 62                 style=" 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     }

相关阅读:
linq查询结果指定列的两种方式
 MVC HTML辅助类常用方法记录
 如何获取google地图、baidu百度地图的坐标
 js解析Json字符串的方法
 EF 官方API
sqlserver 计算百分比
 js 判断js函数、变量是否存在
 JS 去字符串空格总结
 sql 转换日期格式只保留月份和日期
 C# json object互转工具
原文地址：https://www.cnblogs.com/Insein/p/8944888.html