【Spring学习笔记-MVC-2】spring导出Excel

说明：
1.结合Spring MVC实现Excel导出功能；
2. 在MVC配置文件中配置Excel视图解析器；

需要的jar包
以poi开头的jar包都是必须的


web.xml
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
  3. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4. xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
  5. http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
  6. <context-param>
  7. <param-name>contextConfigLocation</param-name>
  8. <param-value>classpath:/conf/applicationContext.xml</param-value>
  9. </context-param>

  11. <listener>
  12. <listener-clas
  13. s>org.springframework.web.context.ContextLoaderListener</listener-class>
  14. </listener>

  16. <servlet>
  17. <servlet-name>baobaotao</servlet-name>
  18. <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
  19. <load-on-startup>1</load-on-startup>
  20. </servlet>
  21. <servlet-mapping>
  22. <servlet-name>baobaotao</servlet-name>
  23. <url-pattern>/</url-pattern>
  24. </servlet-mapping>
  25. </web-app>

baobaotao-servlet.xml ==>MVC配置文件
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <beans xmlns="http://www.springframework.org/schema/beans"
  3. 	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
  4. 	xmlns:context="http://www.springframework.org/schema/context"
  5. 	xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:util="http://www.springframework.org/schema/util"
  6. 	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
  7. 		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
  8.         http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd
  9.         http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd">
  11. 	<context:component-scan base-package="com.baobaotao.web" />
  12. 	<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"
  13. 		p:prefix="/WEB-INF/views/" p:suffix=".jsp" />
  14. 	
  15. 		<!-- Excel及PDF视图解析器配置 -->
  16. 	<bean class="org.springframework.web.servlet.view.BeanNameViewResolver"
  17. 		p:order="10" />
  18. 	<bean id="userListExcel" class="com.baobaotao.web.UserListExcelView" />
  19. 	<!-- <bean id="userListPdf" class="com.baobaotao.web.UserListPdfView" /> -->
  20. </beans>
说明：Excel视图对应的视图解析器为：BeanNameViewResolver，即根据bean的名称来解析视图，解析到的视图实际上
是一个bean，即上面的<bean id="userListExcel" class="com.baobaotao.web.UserListExcelView" />；


UserController.java
  1. package com.baobaotao.web;
  3. import java.util.ArrayList;
  4. import java.util.Calendar;
  5. import java.util.GregorianCalendar;
  6. import java.util.List;
  8. import javax.validation.Valid;
  10. import org.springframework.beans.factory.annotation.Autowired;
  11. import org.springframework.stereotype.Controller;
  12. import org.springframework.ui.ModelMap;
  13. import org.springframework.validation.BindingResult;
  14. import org.springframework.web.bind.annotation.ModelAttribute;
  15. import org.springframework.web.bind.annotation.PathVariable;
  16. import org.springframework.web.bind.annotation.RequestMapping;
  17. import org.springframework.web.bind.annotation.RequestMethod;
  18. import org.springframework.web.bind.annotation.RequestParam;
  19. import org.springframework.web.servlet.ModelAndView;
  21. import com.baobaotao.UserService;
  22. import com.baobaotao.domain.User;
  24. @Controller
  25. @RequestMapping("/user")
  26. public class UserController {
  27. 	
  28. 	@RequestMapping(value = "/showUserListByXls")
  29. 	public String showUserListInExcel(ModelMap mm) {
  30. 		Calendar calendar = new GregorianCalendar();
  32. 		List<User> userList = new ArrayList<User>();
  33. 		User user1 = new User();
  34. 		user1.setUserName("tom");
  35. 		user1.setRealName("汤姆");
  36. 		calendar.set(1980, 1, 1);
  37. 		user1.setBirthday(calendar.getTime());
  38. 		
  39. 		User user2 = new User();
  40. 		user2.setUserName("john");
  41. 		user2.setRealName("约翰");
  42. 		user2.setBirthday(calendar.getTime());
  43. 		
  44. 		userList.add(user1);
  45. 		userList.add(user2);
  46. 		mm.addAttribute("userList", userList);
  47. 		return "userListExcel";
  48. 	}
  50. }
说明：return "userListExcel";
视图解析器BeanNameViewResolver根据返回值userListExcel解析bean名称为userListExcel的bean；



UserListExcelView.java
  1. package com.baobaotao.web;
  3. import java.util.List;
  4. import java.util.Map;
  6. import javax.servlet.http.HttpServletRequest;
  7. import javax.servlet.http.HttpServletResponse;
  9. import org.apache.commons.lang.time.DateFormatUtils;
  10. import org.apache.poi.hssf.usermodel.HSSFCell;
  11. import org.apache.poi.hssf.usermodel.HSSFRow;
  12. import org.apache.poi.hssf.usermodel.HSSFSheet;
  13. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  14. import org.springframework.stereotype.Component;
  15. import org.springframework.web.servlet.view.document.AbstractExcelView;
  17. import com.baobaotao.domain.User;
  19. public class UserListExcelView extends AbstractExcelView {
  20. 	
  21. 	@Override
  22. 	protected void buildExcelDocument(Map<String, Object> model,
  23. 			HSSFWorkbook workbook, HttpServletRequest request,
  24. 			HttpServletResponse response) throws Exception {		
  25. 		response.setHeader("Content-Disposition", "inline; filename="+ 
  26. 				new String("用户列表".getBytes(), "iso8859-1"));    //iso8899-1：解决Excel文档名称出现中文乱码的问题 
  27. 		List<User> userList = (List<User>) model.get("userList");
  28. 		
  29. 		HSSFSheet sheet = workbook.createSheet("users");
  30. 		HSSFRow header = sheet.createRow(0);   //第0行
  31. 		for(int i=0;i<3;i++){
  32. 			header.createCell((short) i);
  33. 			sheet.getRow(0).getCell((short)i).setEncoding(HSSFCell.ENCODING_UTF_16);  //解决Excel单元格出现中文乱码的问题
  34. 			sheet.getRow(0).getCell((short)i).setCellValue((i==0)?"账号":((i==1)?"姓名":"生日"));
  35. 		}
  36. 		
  38. 		int rowNum = 1;
  39. 		for (int i=0;i<userList.size();i++) {
  40. 			HSSFRow row = sheet.createRow(rowNum++);  //创建行
  41. 			User user = userList.get(i);      
  42. 			
  43. 			row.createCell((short) 0).setCellValue(user.getUserName());	
  44. 			
  45. 			row.createCell((short) 1);
  46. 			row.getCell((short)1).setEncoding(HSSFCell.ENCODING_UTF_16);  //解决Excel单元格出现中文乱码的问题
  47. 			row.getCell((short)1).setCellValue(user.getRealName());
  48. 			
  49. 			String createDate = DateFormatUtils.format(user.getBirthday(),
  50. 					"yyyy-MM-dd");
  51. 			row.createCell((short) 2).setCellValue(createDate);
  52. 		}
  53. 	}
  54. }

访问：
在浏览器中输入：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：
  1. <bean class="org.springframework.web.servlet.view.BeanNameViewResolver"
  2. 		p:order="10" />
根据返回值userListExcel找到名称为userListExcel的bean，即：
  1. <bean id="userListExcel" class="com.baobaotao.web.UserListExcelView" />
5. UserListExcelView.java类实现了AbstractExcelView类中的buildExcelDocument接口，
因此会生成Excel文档；

结果
会出现提示下载Excel，打开之后：
 




















【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/ssslinppp/p/4432434.html