springboot引入easyExcel导出excel

1、pom

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>2.2.10</version>
</dependency>

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<Map<String,Object>> 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();
}

listener 实体类

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSONObject;

public class StudentVoListener extends AnalysisEventListener<StudentVO> {
    @Override
    public void invoke(StudentVO studentVO, AnalysisContext analysisContext) {
        System.out.println(" 解释到studentVO :"+ JSONObject.toJSONString(studentVO));
    }
    // 这一步是整个表格解释完成后才执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("保存studentVo方法");
    }
}

 

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<Student> 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<List<String>> 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<Student> 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<List<String>> 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<Map<String,Object>> 导出到excel

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

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

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

  // 一个List<Object> 代表一行 （核心：Map<String, Object> 转成 List<String>）
  List<List<Object>> data = studentMapList.stream().map(o1 -> trans(o1,strField)).collect(Collectors.toList());

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

    List<List<String>> collect = Arrays.stream(title).map(ImmutableList::of).collect(Collectors.toList());
    EasyExcel.write(outputStream).head(collect).sheet("第一个表").doWrite(data);
  } catch (IOException e) {
    e.printStackTrace();
  }
}

// Map<String, Object> 按顺序转成 List<String>
public List<Object> trans(Map<String,Object> map,List<String> head) {
  return head.stream().map(map::get).collect(Collectors.toList());
}

 

 

状态转换，先写一个转换类， 然后在导出的实体类字段 添加注解     @ExcelProperty(value = "发货状态",converter = SendConverter.class)

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

// 将状态转成文字
public class SendConverter implements Converter<Integer> {

    public SendConverter() {
    }

    @Override
    public Class supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if (cellData.getData() == null) return null;

        String data = (String) cellData.getData();
        if ("未接单".equals(data)) return 0;
        if ("待出库".equals(data)) return 1;
        if ("已发货".equals(data)) return 2;
        return null;
    }

    @Override
    public CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if(integer==0 ) return new CellData("未接单");
        if(integer==1 ) return new CellData("待出库");
        if(integer==2 ) return new CellData("已发货");
        return null;
    }
}