easyexcel按模板导出工具类

easyexcel按模板导出工具类

用来装填数据的类

@Data
public class ExportExcelDTO {
  /** 普通数据 */
  private Map<String, Object> normalDate;

  /** List */
  private Map<String, List<Map<String, Object>>> listData;

  public ExportExcelDTO() {
    this.normalDate = new HashMap<>();
    this.listData = new HashMap<>();
  }

  public void setNormalDate(String k, Object v) {
    normalDate.put(k, v);
  }

  public void setListData(String k, List<Map<String, Object>> v) {
    listData.put(k, v);
  }
}

工具实现

  /**
   * 按照模板导出
   *
   * @param template
   * @param fileName
   * @param exportExcelDTO
   * @param response
   * @return void
   */
  public static void exportExcel(
      String template,
      String fileName,
      ExportExcelDTO exportExcelDTO,
      HttpServletResponse response) {
    try {
      setExcelResponseProp(response, fileName);
      InputStream inputStream = new ClassPathResource(template).getInputStream();
      ExcelWriter excelWriter =
          EasyExcel.write()
              .file(response.getOutputStream())
              .withTemplate(inputStream)
              .excelType(ExcelTypeEnum.XLSX)
              .build();
      WriteSheet writeSheet = EasyExcel.writerSheet().build();
      // 填充普通数据
      excelWriter.fill(exportExcelDTO.getNormalDate(), writeSheet);
      // 填充list数据
      exportExcelDTO
          .getListData()
          .forEach((k, v) -> excelWriter.fill(new FillWrapper(k, v), writeSheet));
      excelWriter.finish();
    } catch (Exception e) {
      log.error("导出异常:{}", e.getMessage());
      throw new BusinessException("导出失败");
    }
  }


  /**
   * 设置响应结果
   *
   * @param response 响应结果对象
   * @param rawFileName 文件名
   * @throws UnsupportedEncodingException 不支持编码异常
   */
  public static void setExcelResponseProp(HttpServletResponse response, String rawFileName)
      throws UnsupportedEncodingException {
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    String fileName =
        URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
  }