easyexcel的使用

easyexcel的使用

导包

        
            com.alibaba
            easyexcel
            3.0.1
        

简单的使用导出

@Data
public class PlanRegionDto {
    
    @ExcelProperty("排序")
    @ApiModelProperty(value = "排序")
    private Integer ordering;
    
    @ExcelProperty("区域名称")
    @ApiModelProperty(value = "区域名称")
    private String regionId;

}

@GetMapping("/export")
  public void export(HttpServletResponse response) {
    try {
    //导出的数据
      ArrayList data = new ArrayList<>();
      response.setContentType("application/vnd.ms-excel");
      response.setCharacterEncoding("utf-8");
      String fileName = URLEncoder.encode("导入模板", "UTF-8");
      response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
      EasyExcel.write(response.getOutputStream(), PlanRegionDto.class).sheet("模板").doWrite(data);
    } catch (Exception e) {
    }
  }

简单的使用导入

@PostMapping("/import")
 public void importSave(@RequestParam("file") MultipartFile file) {
 try {
      EasyExcel.read(
              file.getInputStream(),
              PlanRegionDto.class,
              new PlanRegionDtoListener(dao))
          .sheet()
          .doRead();
    } catch (IOException e) {
      e.printStackTrace();
      log.error("导入失败", e);
      return R.failed(e.getLocalizedMessage());
    }
 }

@Slf4j
public class PlanRegionDtoListener extends AnalysisEventListener {
	//导入dao
	 private PlanRegionDao dao
  public PlanRegionExcelListener(PlanRegionDao  dao) {
    this.dao= dao;
  }

  
  @Override
  public void onException(Exception exception, AnalysisContext context) throws Exception {
    log.error("解析失败，但是继续解析下一行:{}", exception.getMessage());
    // 如果是某一个单元格的转换异常 能获取到具体行号
    if (exception instanceof ExcelDataConvertException) {
      ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
      String error =
          MessageFormat.format(
              "第{0}行，第{1}列解析异常，数据为:{2}",
              excelDataConvertException.getRowIndex(),
              excelDataConvertException.getColumnIndex(),
              StringUtils.isBlank(excelDataConvertException.getCellData().toString())
                  ? ""
                  : excelDataConvertException.getCellData());
      log.error(error);
    
    }
    this.fail++;
  }

  
  @Override
  public void invoke(PlanRegionDto data, AnalysisContext context) {
	dao.insert(data)
  }

  
  @Override
  public void doAfterAllAnalysed(AnalysisContext context) {
    log.info("所有数据解析完成！");
   
  }
}

规定格式的导出

response.setHeader("Content-disposition", "attachment; filename=" + "catagory.xls");
	  response.setContentType("application/msexcel;charset=UTF-8");//设置类型
	  response.setHeader("Pragma", "No-cache");//设置头
	  response.setHeader("Cache-Control", "no-cache");//设置头
	  response.setDateHeader("Expires", 0);//设置日期头
	  ServletOutputStream outputStream = response.getOutputStream();
	  //加载对应的模板
	  ExcelWriter excelWriter  = EasyExcel.write(outputStream).withTemplate(ResourceUtils.getFile("classpath:templates/excel.xlsx")).build();
	  //设置sheet
	  WriteSheet writeSheet = EasyExcel.writerSheet().build();
	  ArrayList list = new ArrayList<>();
	  list.add(new Entity("123"));
	  list.add(new Entity("564"));
	  //填充配置
	  FillConfig fillConfig  = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
	  excelWriter.fill(list,fillConfig,writeSheet);
	  //填充普通变量
	  Map map = new HashMap();
	  map.put("time", LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd hh:mm:ss")));
	  map.put("name", "张三");
	  excelWriter.fill(map, writeSheet);
	  excelWriter.finish();
	  outputStream.flush();
	  response.getOutputStream().close();

package com.zhk.study.easyexcel;

import lombok.AllArgsConstructor;
import lombok.Data;

@Data
@AllArgsConstructor
public class Entity {
    private String qwe;
}

{.qwe}是list