说明:
1.结合Spring MVC实现Excel导出功能;
2. 在MVC配置文件中配置Excel视图解析器;
说明: return " userListExcel ";
访问:
在浏览器中输入: http://localhost:8080/springQs/user/showUserListByXls
需要的jar包
![](https://i-blog.csdnimg.cn/blog_migrate/ffd4c150ac20c01333354e3678b6d1a6.png)
以poi开头的jar包都是必须的
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:/conf/applicationContext.xml</param-value>
</context-param>
<listener>
<listener-clas
s>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<servlet>
<servlet-name>baobaotao</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>baobaotao</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
</web-app>
baobaotao-servlet.xml ==>MVC配置文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd">
<context:component-scan base-package="com.baobaotao.web" />
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"
p:prefix="/WEB-INF/views/" p:suffix=".jsp" />
- <!-- Excel及PDF视图解析器配置 -->
<bean class="org.springframework.web.servlet.view.BeanNameViewResolver"
p:order="10" />
<bean id="userListExcel" class="com.baobaotao.web.UserListExcelView" />
<!-- <bean id="userListPdf" class="com.baobaotao.web.UserListPdfView" /> -->
</beans>
说明:Excel视图对应的视图解析器为:
BeanNameViewResolver,即根据bean的名称来解析视图,解析到的视图实际上
是一个bean,即上面的
<bean
id
=
"userListExcel"
class
=
"com.baobaotao.web.UserListExcelView"
/>;
UserController.java
package com.baobaotao.web;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;
import javax.validation.Valid;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;
import com.baobaotao.UserService;
import com.baobaotao.domain.User;
@Controller
@RequestMapping("/user")
public class UserController {
-
@RequestMapping(value = "/showUserListByXls")
public String showUserListInExcel(ModelMap mm) {
Calendar calendar = new GregorianCalendar();
List<User> userList = new ArrayList<User>();
User user1 = new User();
user1.setUserName("tom");
user1.setRealName("汤姆");
calendar.set(1980, 1, 1);
user1.setBirthday(calendar.getTime());
User user2 = new User();
user2.setUserName("john");
user2.setRealName("约翰");
user2.setBirthday(calendar.getTime());
userList.add(user1);
userList.add(user2);
mm.addAttribute("userList", userList);
return "userListExcel";
}
}
说明: return " userListExcel ";
视图解析器
BeanNameViewResolver根据返回值
userListExcel
解析bean名称为
userListExcel
的bean;
UserListExcelView.java
package com.baobaotao.web;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.web.servlet.view.document.AbstractExcelView;
import com.baobaotao.domain.User;
public class UserListExcelView extends AbstractExcelView {
@Override
protected void buildExcelDocument(Map<String, Object> model,
HSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
response.setHeader("Content-Disposition", "inline; filename="+
new String("用户列表".getBytes(), "iso8859-1")); //iso8899-1:解决Excel文档名称出现中文乱码的问题
List<User> userList = (List<User>) model.get("userList");
HSSFSheet sheet = workbook.createSheet("users");
HSSFRow header = sheet.createRow(0); //第0行
for(int i=0;i<3;i++){
header.createCell((short) i);
sheet.getRow(0).getCell((short)i).setEncoding(HSSFCell.ENCODING_UTF_16); //解决Excel单元格出现中文乱码的问题
sheet.getRow(0).getCell((short)i).setCellValue((i==0)?"账号":((i==1)?"姓名":"生日"));
}
int rowNum = 1;
for (int i=0;i<userList.size();i++) {
HSSFRow row = sheet.createRow(rowNum++); //创建行
User user = userList.get(i);
row.createCell((short) 0).setCellValue(user.getUserName());
row.createCell((short) 1);
row.getCell((short)1).setEncoding(HSSFCell.ENCODING_UTF_16); //解决Excel单元格出现中文乱码的问题
row.getCell((short)1).setCellValue(user.getRealName());
String createDate = DateFormatUtils.format(user.getBirthday(),
"yyyy-MM-dd");
row.createCell((short) 2).setCellValue(createDate);
}
}
}
在浏览器中输入: http://localhost:8080/springQs/user/showUserListByXls
执行流程如下:
1.
user/showUserListByXls
请求到
UserController.java类中的
public String showUserListInExcel(ModelMap mm)方法;
2. 方法
String showUserListInExcel(ModelMap mm)显示的将
userList添加到模型属性中,即:
mm.addAttribute("userList", userList);
3.
showUserListInExcel方法返回“
userListExcel
”,即:
return "userListExcel";
4. 视图解析器
BeanNameViewResolver:
<bean class="org.springframework.web.servlet.view.BeanNameViewResolver"
p:order="10" />
根据返回值
userListExcel找到名称为
userListExcel的bean,即:
<bean id="userListExcel" class="com.baobaotao.web.UserListExcelView" />
5. UserListExcelView.java类实现了AbstractExcelView类中的buildExcelDocument接口,
因此会生成Excel文档;
结果
会出现提示下载Excel,打开之后:
![](https://i-blog.csdnimg.cn/blog_migrate/26b9ba3fbdb419c70886c66fc37a3363.png)