easyexcel的简单使用(execl模板导出)

模板支持功能点

支持列表
支持自定义头名称
支持自定义fileName
支持汇总

模板示例

在这里插入图片描述

操作

pom引入

  <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>${easyexcel.version}</version></dependency>

版本

  <easyexcel.version>3.3.4</easyexcel.version>

这里尽量使用新版本，用老版本的话可能会有问题。

简单封装easyexcel方法

public class EasyExportUtil {/*** 指定流输出* @param fileName* @param response* @param sheetName* @param templateFileName* @param list* @param <T>*/public static <T>  void exportWithTemplate(String fileName,HttpServletResponse response, String sheetName, String templateFileName, List<T> list) {try {response.setHeader("content-disposition", "attachment;filename="+ URLEncoder.encode(fileName, "UTF-8"));} catch (UnsupportedEncodingException e) {e.printStackTrace();}try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templateFileName).registerWriteHandler(new CustomTemplateSheetStrategy(0,sheetName)).build()) {WriteSheet writeSheet = EasyExcel.writerSheet().build();FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();excelWriter.fill(list, fillConfig, writeSheet);Map<String, Object> map = new HashMap<>();map.put("date", "2019年10月9日13:28:28");map.put("total", 1000);excelWriter.fill(map, writeSheet);} catch (IOException e) {e.printStackTrace();}}}

自定义拦截器

public class CustomTemplateSheetStrategy implements SheetWriteHandler {private Integer sheetNo;private String sheetName;public CustomTemplateSheetStrategy(String sheetName) {this.sheetName = sheetName;}public CustomTemplateSheetStrategy(Integer sheetNo, String sheetName) {this.sheetNo = sheetNo;this.sheetName = sheetName;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}/*** 功能：动态修改模板中sheet的名称* sheet创建完成后调用* @param writeWorkbookHolder* @param writeSheetHolder*/@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {if (sheetName == null) {return;}if (sheetNo == null) {sheetNo = 0;}writeWorkbookHolder.getCachedWorkbook().setSheetName(sheetNo, sheetName);}}

date和total 我这里就写死了，大家根据自己的情况进行更改。

接口示例

@GetMapping("/export")public void exportDeviceList(HttpServletResponse response) {List<ExcelData3> list = new ArrayList<>();for (int i = 0; i <= 10; i++) {ExcelData3 excelData3 = new ExcelData3();excelData3.setA("testA");excelData3.setB("testB");excelData3.setC("testC");excelData3.setD("testD");excelData3.setE("testE");excelData3.setF("testF");excelData3.setG("testG");excelData3.setH("testH");excelData3.setI("testI");list.add(excelData3);}EasyExportUtil.exportWithTemplate(System.currentTimeMillis() + ".xlsx", response, "我是一个sheetName", CommonUtlis.resolvePath("test.xlsx"), list);}