springMVC中使用POI方式导出excel至客户端、服务器实例

　Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

　　这里的方法支持导出excel至项目所在服务器，或导出至客户端浏览器供用户下载，下面我把两个实例都放出来。

 

1.下载所需POI的jar包，并导入项目。

 

2.添加一个User类，用于存放用户实体，类中内容如下：

 1 package com.mvc.po;
 2 
 3 public class User {
 4     private int id;
 5     private String name;
 6     private String password;
 7     private int age;
 8 
 9     public User() {
10 
11     }
12 
13     public User(int id, String name, String password, int age) {
14         this.id = id;
15         this.name = name;
16         this.password = password;
17         this.age = age;
18     }
19     public int getId() {
20         return id;
21     }
22     public void setId(int id) {
23         this.id = id;
24     }
25     public String getName() {
26         return name;
27     }
28     public void setName(String name) {
29         this.name = name;
30     }
31     public String getPassword() {
32         return password;
33     }
34     public void setPassword(String password) {
35         this.password = password;
36     }
37     public int getAge() {
38         return age;
39     }
40     public void setAge(int age) {
41         this.age = age;
42     }
43 }

 

3.添加一个UserController类，类中内容如下：

 1 package com.mvc.controller;
 2  
 3 import java.text.SimpleDateFormat;
 4 import java.util.Date;
 5  
 6 import javax.servlet.ServletOutputStream;
 7 import javax.servlet.http.HttpServletResponse;
 8  
 9 import org.springframework.stereotype.Controller;
10 import org.springframework.beans.factory.annotation.Autowired;
11 import org.springframework.web.bind.annotation.RequestMapping;
12 import org.springframework.web.bind.annotation.ResponseBody;
13  
14 import com.mvc.po.User;
15 import com.mvc.service.UserService;
16  
17 @Controller
18 public class UserController {
19  
20     @Autowired
21     private UserService userService;
22 
23     @RequestMapping("/export.do")
24     public @ResponseBody String export(HttpServletResponse response){    
25         response.setContentType("application/binary;charset=UTF-8");
26         try{
27             ServletOutputStream out=response.getOutputStream();
28             String fileName=new String(("UserInfo "+ new SimpleDateFormat("yyyy-MM-dd").format(new 				 Date())).getBytes(),"UTF-8");

               String agent = request.getHeader("USER-AGENT").toLowerCase();
	       response.setContentType("application/vnd.ms-excel");
	       String codedFileName = java.net.URLEncoder.encode(fileName, "UTF-8");
	       if (agent.contains("firefox")) {
	   response.setCharacterEncoding("utf-8");
		     response.setHeader("content-disposition", 

                          "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xls");
		} else {
		 response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
		}
29             //response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");

	       //response.setContentType("application/vnd.ms-excel; charset=utf-8");
30             String[] titles = { "用户编号", "用户姓名", "用户密码", "用户年龄" }; 
31             userService.export(titles, out);
32             return "success";
33         } catch(Exception e){
34             e.printStackTrace();
35             return "导出信息失败";
36         }
37     }
38 }

 

4.添加一个接口类UserService和实现类UserServiceImpl，类中内容如下：

1 package com.mvc.service;
2 
3 import javax.servlet.ServletOutputStream;
4 import com.mvc.po.User;
5 
6 public interface UserService {
7     public void export(String[] titles, ServletOutputStream out);
8 }

 1 package com.mvc.service.impl;
 2 
 3 import java.text.SimpleDateFormat;
 4 import java.util.List;
 5 
 6 import javax.servlet.ServletOutputStream;
 7 
 8 import com.mvc.dao.UserDAO;
 9 import com.mvc.po.User;
10 import com.mvc.service.UserService;
11 
12 import org.apache.poi.hssf.usermodel.HSSFCell;
13 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
14 import org.apache.poi.hssf.usermodel.HSSFRow;
15 import org.apache.poi.hssf.usermodel.HSSFSheet;
16 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
17 import org.springframework.beans.factory.annotation.Autowired;
18 import org.springframework.stereotype.Service;
19 
20 @Service
21 public class UserServiceImpl implements UserService {
22 
23     @Autowired
24     private UserDAO userDAO;
25     
26     @Override
27     public void export(String[] titles, ServletOutputStream out) {                
28         try{
29             // 第一步，创建一个workbook，对应一个Excel文件
30             HSSFWorkbook workbook = new HSSFWorkbook();
31             // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
32             HSSFSheet hssfSheet = workbook.createSheet("sheet1");
33             // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
34             HSSFRow hssfRow = hssfSheet.createRow(0);
35             // 第四步，创建单元格，并设置值表头 设置表头居中
36             HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
37             //居中样式
38             hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
39 
40             HSSFCell hssfCell = null;
41             for (int i = 0; i < titles.length; i++) {
42                 hssfCell = hssfRow.createCell(i);//列索引从0开始
43                 hssfCell.setCellValue(titles[i]);//列名1
44                 hssfCell.setCellStyle(hssfCellStyle);//列居中显示                
45             }
46             
47             // 第五步，写入实体数据 
48             List<User> users = userDAO.query();            
49 
50             SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
51             if(users != null && !users.isEmpty()){
52                 for (int i = 0; i < users.size(); i++) {
53                     hssfRow = hssfSheet.createRow(i+1);                
54                     User user = users.get(i);
55                     
56                     // 第六步，创建单元格，并设置值
57                     int userid = 0;
58                     if(user.getId() != 0){
59                         userid = user.getId();
60                     }
61                     hssfRow.createCell(0).setCellValue(userid);
62                     String username = "";
63                     if(user.getName() != null){
64                         username = user.getName();
65                     }
66                     hssfRow.createCell(1).setCellValue(username);
67                     String password = "";
68                     if(user.getPassword() != null){
69                         password = user.getPassword();
70                     }
71                     hssfRow.createCell(2).setCellValue(password);
72                     int age = 0;
73                     if(user.getAge() != 0){
74                         age = user.getAge();
75                     }
76                     hssfRow.createCell(3).setCellValue(age);
77                 }
78             }
79             
80             // 第七步，将文件输出到客户端浏览器
81             try {
82                 workbook.write(out);
83                 out.flush();
84                 out.close();
85 
86             } catch (Exception e) {
87                 e.printStackTrace();
88             }
89         }catch(Exception e){
90             e.printStackTrace();
91             throw new Exception("导出信息失败！");
92         }    
93     }
94 }

 

5.添加一个接口类UserDAO和实现类UserDAOImpl，类中内容如下：

1 package com.mvc.dao;
2 
3 import java.util.List;
4 import com.mvc.po.User;
5 
6 public interface UserDAO {
7     List<User> query();    
8 }

 1 package com.mvc.dao.impl;
 2 
 3 import java.util.List;
 4 import java.sql.ResultSet;
 5 import java.sql.SQLException;
 6 
 7 import com.mvc.dao.UserDAO;
 8 import com.mvc.po.User;
 9 
10 import org.springframework.stereotype.Repository;
11 import org.springframework.beans.factory.annotation.Autowired;
12 import org.springframework.jdbc.core.JdbcTemplate;
13 import org.springframework.jdbc.core.RowMapper;
14 
15 @Repository
16 public class UserDAOImpl implements UserDAO {
17 
18     @Autowired
19     private JdbcTemplate jdbcTemplate;
20 
21     public List<User> query() {
22         return this.jdbcTemplate.query("select * from student",
23                 new RowMapper<User>() {
24                     public User mapRow(ResultSet rs, int arg1)
25                             throws SQLException {
26                         return new User(rs.getInt("sId"),
27                                 rs.getString("sName"), rs.getString("sPwd"), rs
28                                         .getInt("sAge"));
29                     }
30                 });
31     }
32 }

 

　　这样就完成了excel导出至客户端浏览器，当然有时候也会用到导出excel至服务器上。只需要对本文步骤4中的第七步文件输出方式进行修改，如下：

1 // 第七步，将文件存到指定位置
2             try {
3                 FileOutputStream fileOutputStream = new FileOutputStream("C:/user.xls");//指定路径与名字和格式
4                 workbook.write(fileOutputStream);//将数据写出去
5                 fileOutputStream.close();//关闭输出流
6             } catch (Exception e) {
7                 e.printStackTrace();
8             }

　　然后去除controller类中的out参数设置就ok了。也可以看出其实两种方式只是最终保存方式不同，其他步骤是共通的。