SpringMvc导出Excel

说明：本篇文章是在SpringMvc框架的基础上完成的，主要是导出Excel

在功能开始之前需要在Maven的pom.xml文件中加入需要的jar包

<!--导出Excel用包-->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml-schemas</artifactId>
  <version>3.10-FINAL</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.10-FINAL</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.10-FINAL</version>
</dependency>

接下来是代码了，为了方便直接写在Controller中了

@RequestMapping("toExportDone")
@ResponseBody
public String exportDo(HttpServletResponse response){
    response.setContentType("application/binary;charset=UTF-8");
    try{
        ServletOutputStream out=response.getOutputStream();
        String fileName=new String((new SimpleDateFormat("yyyy-MM-dd").format(new Date())).getBytes(),"UTF-8");
        response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");
        String[] titles = { "用户编号", "用户姓名", "用户地址" };
        export(titles, out);
        return "success";
    } catch(Exception e){
        e.printStackTrace();
        return "导出信息失败";
     }
}

public void export(String[] titles,ServletOutputStream out) {
    // 1.创建一个workbook，对应一个Excel文件
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 2.在webbook中添加一个sheet,对应Excel文件中的sheet
    HSSFSheet hssfSheet = workbook.createSheet("sheet1");
    // 3.在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
    HSSFRow hssfRow = hssfSheet.createRow(0);
    // 4.创建单元格，并设置值表头 设置表头居中
    HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
    //居中样式
    hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFCell hssfCell = null;
    for (int i = 0; i < titles.length; i++) {
        hssfCell = hssfRow.createCell(i);//列索引从0开始
        hssfCell.setCellValue(titles[i]);//列名1
        hssfCell.setCellStyle(hssfCellStyle);//列居中显示
    }

    // 5.写入实体数据
    List<User> users = userService.findUserAll(new UserQuery());//查询所有的用户
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    if (users.size()>0) {
        for (int i = 0; i < users.size(); i++) {
            hssfRow = hssfSheet.createRow(i + 1);
            User user = users.get(i);
            // 6.创建单元格，并设置值
            String userid = "";
            if (user.getId()!= null) {
                userid = user.getId();
            }
            hssfRow.createCell(0).setCellValue(userid);
            String name = "";
            if (user.getName() != null) {
                name = user.getName();
            }
            hssfRow.createCell(1).setCellValue(name);
            String adress = "";
            if (user.getAdress() != null) {
                adress = user.getAdress();
            }
            hssfRow.createCell(2).setCellValue(adress);
        }
    }
    // 7.将文件输出到客户端浏览器
    try {
        workbook.write(out);
        out.flush();
        out.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

接下来我们访问一下   http://localhost:8080/except/toExceptDone就可以了