spring框架实现的将数据库中的数据表生成Excel的样式提供用户下载

1.下载Excel的步骤
 1.配置jar包依赖
  <dependency>
    <groupId>org.apache.poi</groupId>
     <artifactId>poi-ooxml</artifactId>
     <version>3.16</version>
  </dependency>
 2.在controller的方法上配置请求映射的参数；
  @RequestMapping(value="/downloadExcel.do", produces="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
  @ResponseBody
 3.在方法中手动设置下载的响应头：
  response.setHeader("Content-Disposition", "attactionment;filename=\"ok.xlsx\"");
 4.将Excel文件转换为一个byte[]发送给客户端页面；
 例：
 @RequestMapping(value="/downloadExcel.do", produces="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
 @ResponseBody
 public byte[] dowmloadExcel(HttpServletResponse response) throws IOException {
  //手工设置下载头：Content-Disposition
  response.setHeader("Content-Disposition", "attactionment;filename=\"ok.xlsx\"");
  byte[] bytes = createExcel();
  return bytes;
 }
5.生成一个Excel的方法：
 

public byte[] createExcel() throws IOException {
  //创建一个工作簿
  XSSFWorkbook workbook = new XSSFWorkbook();
  //在工作薄中添加工作表
  XSSFSheet sheet1 = workbook.createSheet("花名册");
  //在工作表中添加两行
  XSSFRow head = sheet1.createRow(0);
  XSSFRow row = sheet1.createRow(1);
  //第一行作为表头
  XSSFCell c0 = head.createCell(0);
  //表头第一个格子添加“编号”
  c0.setCellValue("编号");
  head.createCell(1).setCellValue("姓名");
  head.createCell(2).setCellValue("年龄");
  //第一行的内容
  row.createCell(0).setCellValue(1);
  row.createCell(1).setCellValue("小明");
  row.createCell(2).setCellValue(12);
  //将Excel保存为bytes
  ByteArrayOutputStream out = new ByteArrayOutputStream();
  //表放到流里面
  workbook.write(out);
  //将流转换为byte数组
  byte[] bytes = out.toByteArray();
  return bytes;
  
 }

6.从数据库中读取数据生成一个Excel表格给用户
 

@RequestMapping(value="/userExcel.do", produces="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
 @ResponseBody
 public byte[] dowmloadUserExcel(HttpServletResponse response) throws IOException {
  //手工设置下载头：Content-Disposition
  response.setHeader("Content-Disposition", "attactionment;filename=\"ok.xlsx\"");
  byte[] bytes = userContentExcel();
  return bytes;
 }
 /**
  * 读取数据库中的用户信息生成一个Excel返回给客户端
  * @throws IOException 
  */
 public byte[] userContentExcel() throws IOException {
  //获取用户的信息
  List<User> users = userService.getAllUser();
  System.out.println("获取到的所有用户信息为："+users);
  //创建一个工作簿
  XSSFWorkbook workbook = new XSSFWorkbook();
  //在工作薄中添加工作表
  XSSFSheet sheet1 = workbook.createSheet("用户信息");
  //在工作表中添加一行作为表头
  XSSFRow head = sheet1.createRow(0);
  //设置表头
  head.createCell(0).setCellValue("id");
  head.createCell(1).setCellValue("username");
  head.createCell(2).setCellValue("password");
  head.createCell(3).setCellValue("phone");
  head.createCell(4).setCellValue("email");
  //将用户信息读取出来循环设置每一行
  for (int i=0;i<users.size();i++) {
   //添加一行
   XSSFRow row = sheet1.createRow(i+1);
   //设置这一行的内容
   User user = users.get(i);
   row.createCell(0).setCellValue(user.getId());
   row.createCell(1).setCellValue(user.getUsername());
   row.createCell(2).setCellValue(user.getPassword());
   row.createCell(3).setCellValue(user.getPhone());
   row.createCell(4).setCellValue(user.getEmail());
  }
  //将Excel保存为bytes
  ByteArrayOutputStream out = new ByteArrayOutputStream();
  //表放到流里面
  workbook.write(out);
  //将流转换为byte数组
  byte[] bytes = out.toByteArray();
  return bytes;
  
 }