SpringBoot集成EasyPoi

1、引入EasyPoi依赖

<!-- 集成EasyPOI -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.4.0</version>
</dependency>

2、定义模版类-后面都使用该模版类

@Getter
@Setter
public class DicExcel implements Serializable {

	// 类型 
	// width属性定义每列宽度
	@Excel(name = "类型",width = 20)
	private String dicType;

	// 字典key
	@Excel(name = "字典key",width = 20)
	private String dicKey;

	// 字典value
	@Excel(name = "字典value",width = 20)
	private String dicValue;

	// 字典描述
	@Excel(name = "字典描述",width = 20)
	private String dicDesc;


}

3、使用EaysPoi进行模版导入

3.1、创建导入模版

导入Excel模版如下

3.2、导入解析Excel

public void importDic(MultipartFile file) {
   ImportParams params = new ImportParams();
   params.setTitleRows(0);
   params.setHeadRows(1);
   //params.setNeedVerify(true);//设置需要校验
   // 1、解析Excel里面的数据
   List<TrademarkDicJwExcel> list = ExcelImportUtil.importExcel(file.getInputStream(),TrademarkDicJwExcel.class, params);
   log.info("*****解析出来的数据:{}", JSON.toJSONString(list));

}

4、使用EasyPoi进行模版导出

4.1、定义接口导出

@GetMapping("/v1/testExport2")
 public void testExport(HttpServletRequest request,HttpServletResponse response){
     List<DicExcel> exportList = new ArrayList<>();
     DicExcel dic1 = new DicExcel();
     dic1.setDicType("1");
     dic1.setDicKey("CN");
     dic1.setDicValue("中国");

     DicExcel dic2 = new DicExcel();
     dic2.setDicType("1");
     dic2.setDicKey("AN");
     dic2.setDicValue("泰国");

     DicExcel dic3 = new DicExcel();
     dic3.setDicType("1");
     dic3.setDicKey("JP");
     dic3.setDicValue("日本");

     DicExcel dic4 = new DicExcel();
     dic4.setDicType("1");
     dic4.setDicKey("AM");
     dic4.setDicValue("美国");
     exportList.add(dic1);
     exportList.add(dic2);
     exportList.add(dic3);
     exportList.add(dic4);

     // 1、定义标题和sheet名称
     // 标题可以为null
     ExportParams params = new ExportParams("字典表", "我是一个sheet", ExcelType.XSSF);
     Map<String,Object> map = new HashMap<>();
     map.put(NormalExcelConstants.DATA_LIST, exportList);
     map.put(NormalExcelConstants.CLASS, TrademarkDicJwExcel.class);
     map.put(NormalExcelConstants.PARAMS, params);
     map.put(NormalExcelConstants.FILE_NAME, "dic_data");//指定excel名称
     PoiBaseView.render(map, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);

 }

4.2、导出结果

5、使用EasyPoi进行动态列导出

5.1、定义导出接口

@GetMapping("/v1/testExport2")
public void testExport(HttpServletRequest request,HttpServletResponse response){
    // 1、定义要导出的数据
    List<DicExcel> exportList = getTestData();

    // 2、这里模拟前端传过来要导出的列
    Map<String,String> exportColumnMap = new HashMap<>();
    exportColumnMap.put("字典key","dicKey");
    exportColumnMap.put("字典value","dicValue");

    // 3、封装要导出的列
    // 使用EasyPOI提供的ExcelExportEntity类，可以实现动态字段到处
    List<ExcelExportEntity> exportEntityLis = new ArrayList<>();
    for(Map.Entry<String,String> m : exportColumnMap.entrySet()){
        ExcelExportEntity excelExportEntity = new ExcelExportEntity(m.getKey(),m.getValue());
        excelExportEntity.setWidth(20);
        exportEntityLis.add(excelExportEntity);
    }

    // 4、开始导出-使用EasyPoi提供的ExcelExportUtil类
    ExportParams params = new ExportParams(null, "我是一个sheet", ExcelType.XSSF);
    try (Workbook workbook = ExcelExportUtil.exportExcel(params, exportEntityLis, exportList)){
        // 重置响应对象
        response.reset();
        // 指定下载的文件名--设置响应头
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("境外商标数据" + System.currentTimeMillis(), "UTF-8") + ".xlsx");
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        workbook.write(response.getOutputStream());
        log.info("*****导出成功~");

    }catch (Exception e){
        log.error("*****导出=接口出现异常:{}",e.toString());
        e.printStackTrace();
    }

}

5.2、导出结果