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属性，定义优先级