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