springboot引入easyExcel

1、pom

  com.alibaba
  easyexcel
  2.2.10

2、上传文件解释成map

// 上传excel文件转成 实体类, 一行代表一个map
@PostMapping("/explainExcelToMap")
public void explainExcelToMap(MultipartFile multipartFile) throws IOException {
  // 首先判断格式是否正确
  String originalFilename = multipartFile.getOriginalFilename();
  String type = originalFilename.substring(originalFilename.lastIndexOf("."));
  Preconditions.checkArgument(".xls".equals(type) || ".xlsx".equals(type),"格式不正确");

  // excel 转成map
  List> objects = EasyExcelFactory.read(multipartFile.getInputStream(), new SyncReadListener()).sheet().doReadSync();
  System.out.println("objects = " + JSONObject.toJSONString(objects));
}

上传的excel转实体类 (实体类字段需要加 @ExcelProperty 注解)

// 上传excel文件转成 实体类
@PostMapping("/explainUploadExcel")
public void explainUploadExcel(MultipartFile multipartFile) throws IOException {
  String originalFilename = multipartFile.getOriginalFilename();
  String type = originalFilename.substring(originalFilename.lastIndexOf("."));
  Preconditions.checkArgument(".xls".equals(type) || ".xlsx".equals(type),"格式不正确");

  // excel 转成 studentVO
  final StudentDTOListener studentDTOListener = new StudentDTOListener();
  EasyExcel.read(multipartFile.getInputStream(), StudentDTO.class,studentDTOListener).sheet().doRead();
}

3、第一种下载excel方式

第一步：建实体类

// 实体类 要加@ExcelProperty
@TableName("student")
public class Student implements Serializable {
    private static final long serialVersionUID = -56875290812731031L;
    @TableId(type = IdType.AUTO)
    @ExcelProperty(value = "学生编号",index = 0)
    private Integer id;
    @ExcelProperty(value = "学生名字",index = 1)
    private String name;
    @ExcelProperty(value = "年龄",index = 2)
    private Integer age;
    @ExcelProperty(value = "性别",index = 4)
    private String sex;
}

导出代码

//一行表头
@GetMapping("exportExcel")
public void testExport(HttpServletRequest request, HttpServletResponse response) {

  List studentList = studentService.queryAllByLimit(0, 1000);
  String filename = "test01.xlsx";
  try (OutputStream outputStream = response.getOutputStream()){
    // 设置名称
    response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8"));
    // 设置成流，只下载，不会直接打开
    response.setContentType("application/octet-stream");

    String[] title = {"1", "2", "e", "3", "e", "h"};
    List> collect = Arrays.stream(title).map(ImmutableList::of).collect(Collectors.toList());
    // 写入excel
    EasyExcel.write(outputStream).head(collect).sheet("第一个表").doWrite(studentList);

  } catch (IOException e) {
    e.printStackTrace();
  }
}

如果要两行表头的用以下方式

// 两行表头
@GetMapping("test02")
public void testExport2(HttpServletRequest request, HttpServletResponse response) {

  List students = studentService.queryAllByLimit(0, 1000);
  String filename = "test01.xlsx";
  try (OutputStream outputStream = response.getOutputStream()){
    // 设置名称
    response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8"));
    // 设置成流，只下载，不会直接打开
    response.setContentType("application/octet-stream");

    List> twoTitle = new ArrayList<>(ImmutableList.of(
      ImmutableList.of("编号", "编号"),
      ImmutableList.of("详情", "名字"),
      ImmutableList.of("详情", "年龄"),
      ImmutableList.of("详情", "性别"),
      ImmutableList.of("详情", "特色"),
      ImmutableList.of("详情", "喜好")
    ));

    EasyExcel.write(outputStream).head(twoTitle).sheet("第一个表").doWrite(students);
  } catch (IOException e) {
    e.printStackTrace();
  }
}

4、下载excel第二种方式

即将数据库返回来的 List> 导出到excel

/**
     *  动态表头  给出字段就可以动态导出对应的excel
     *    一个Map 转成 List 代表一行
     */
@GetMapping("test03")
public void testExport3(HttpServletRequest request, HttpServletResponse response) throws Exception {

  String sqlA  =" select * from student";
  List> studentMapList = jdbcTemplate.queryForList(sqlA);

  // 表头
  String[] title = {"学生编号", "学生名字", "年龄","性别"};
  // 字段
  List strField = ImmutableList.of("id", "name", "favorite","sex");

  // 一个List