POI技术实现对excel的导出
需求:客户端传来两个参数,当前页码和每页的条数,根据传来的参数实现对数据的导出
1.导入依赖
<!-- 报表相关 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.10.1</version> </dependency>
2.定义controller类
/* * 同步(跳转到excel视图) * 有参 * 返回值 * */ @RequestMapping(value="export/excel",method=RequestMethod.POST) public ModelAndView exportExcel(Integer page,Integer rows){ ModelAndView mv = new ModelAndView("excelview"); PageInfo<User> list = this.userService.queryUserList(page, rows); List<User> users = list.getList(); mv.addObject("users", users); return mv; }
3.因为上面代码返回的是一个excel的视图,所以编写excel视图(该视图对数据的导出)
package cn.usermanage.view; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.RichTextString; import org.springframework.format.datetime.joda.DateTimeFormatterFactory; import org.springframework.format.datetime.standard.DateTimeContext; import org.springframework.web.servlet.view.document.AbstractExcelView; import cn.usermanage.bean.Constants; import cn.usermanage.pojo.User; public class UserExcelView extends AbstractExcelView { @Override protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // 获取查询到的数据 @SuppressWarnings("unchecked") List<User> users = (List<User>) model.get("users"); // 创建表格 HSSFSheet sheet = workbook.createSheet("会员列表"); // 创建标题行 HSSFRow header = sheet.createRow(0); header.createCell(0).setCellValue("ID"); header.createCell(1).setCellValue("用户名"); header.createCell(2).setCellValue("姓名"); header.createCell(3).setCellValue("年龄"); header.createCell(4).setCellValue("性别"); header.createCell(5).setCellValue("出生日期"); header.createCell(6).setCellValue("创建时间"); header.createCell(7).setCellValue("更新时间"); // 填充数据 int rowNum = 1; for (User user : users) { HSSFRow row = sheet.createRow(rowNum); row.createCell(0).setCellValue(user.getId()); row.createCell(1).setCellValue(user.getuserName()==null?"":user.getuserName()); row.createCell(2).setCellValue(user.getName()); row.createCell(3).setCellValue(user.getAge()); row.createCell(4).setCellValue(parseSex(user.getSex())); // row.createCell(5).setCellValue(new DateTime(user.getBirthday()).toString(Constants.DATE)); // row.createCell(6).setCellValue(new DateTime(user.getCreated()).toString(Constants.DATE_TIME)); // row.createCell(7).setCellValue(new DateTime(user.getUpdated()).toString(Constants.DATE_TIME)); rowNum++; } // 设置相应头信息,以附件形式下载并且指定文件名 response.setHeader("Content-Disposition", "attachment;filename=" + new String("会员列表.xls".getBytes(),"ISO-8859-1")); } public String parseSex(Integer code){ String sexStr; if (code== 1) { sexStr = "男"; } else if (code == 2) { sexStr = "女"; } else { sexStr = "未知"; } return sexStr; } }
4.视图注册到Spring容器,由Spring帮我们创建出来
<!--注册Excel视图 -->
<bean name="excelview" class="cn.usermanage.view.UserExcelView"></bean>
5.在Springmvc中添加视图解析器
<!-- 配置第二个视图解析器 配置有多个视图解析器时 一定要定义优先级-->
<bean class="org.springframework.web.servlet.view.BeanNameViewResolver">
<property name="order" value="1"></property>
</bean>
注意:在多视图解析器的时候,一定要设置order属性,定义优先级
今天要比昨天好