【EasyExcel实践】万能导出，一个接口导出多张表以及任意字段（可指定字段顺序）

文章目录 前言正文一、POM依赖二、核心Java文件2.1 自定义表头注解 ExcelColumnTitle2.2 自定义标题头的映射接口2.3 自定义有序map存储表内数据2.4 表头工厂2.5 表flag和表头映射枚举2.6 测试用的实体2.6.1 NameAndFactoryDemo2.6.2 StudentDemo 2.7 启动类2.8 测试控制器 三、测试测试1测试2测试3测试4

前言

日前，看到一个比较奇怪的导出功能。

需要根据不同的页面，以及指定不同的字段列表（任意顺序），然后导出对应的表格。

先假设一个场景： 假如你的系统有多个列表展示页，每页中可以依据筛选条件，调整展示的列的个数，顺序等。然后要求导出的时侯，导出一摸一样的格式。也就是“所见即所得”的表格。

那么基于以上场景，我们就来考虑下如何实现？ 本文就是对以上场景功能的一个实现。目前仅支持单sheet，不支持数据聚合等。

正文

本文项目环境： java 8，springboot2.2.0, easyexcel

一、POM依赖 <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> <version>2.2.0.RELEASE</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.2</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.11</version> <exclusions> <exclusion> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> </exclusion> </exclusions> </dependency> </dependencies> 二、核心Java文件

此处粘贴全部的java文件

2.1 自定义表头注解 ExcelColumnTitle package headbean; import java.lang.annotation.*; /** * 列名标题注解，标注列的标题 * * @author feng */ @Documented @Target({ElementType.FIELD}) @Retention(RetentionPolicy.RUNTIME) public @interface ExcelColumnTitle { String value(); } 2.2 自定义标题头的映射接口

此接口仅仅是用于规范实体，以及用于辅助实现导出功能。

package headbean; /** * excel头部映射接口，用于规范导出的实体类 * * @author feng */ public interface ExcelHeadMapInterface { } 2.3 自定义有序map存储表内数据

这个是表格导出时，字段数量，顺序的关键。

package headbean; import java.util.HashSet; import java.util.LinkedHashMap; import java.util.List; import java.util.Set; import java.util.stream.Collectors; /** * 表格数据专用的map，带顺序，而且初始化的时候，依据指定的表头变量字段名确定导出数据的顺序 * * @author feng */ public class ExcelDataLinkedHashMap<V> extends LinkedHashMap<String, V> { private static final long serialVersionUID = -8554095999151235982L; /** * 头部字段名缓存 */ private final Set<String> headColumnNamesCache; /** * ExcelDataLinkedHashMap构造器 * * @param headColumnNames 表头字段变量名，例如：[name,studentNo,age,className] */ public ExcelDataLinkedHashMap(List<String> headColumnNames) { // 字段名去重 headColumnNames = headColumnNames.stream().distinct().collect(Collectors.toList()); // 构建字段名缓存 this.headColumnNamesCache = new HashSet<>(headColumnNames); // 指定列数据排列顺序 for (String headColumnName : headColumnNames) { this.put(headColumnName, null); } } @Override public V put(String key, V value) { // 只保存字段名缓存中的key以及value if (headColumnNamesCache.contains(key)) { return super.put(key, value); } return null; } } 2.4 表头工厂

负责实现初始化表头字段名，以及后期使用时，从中获取表头信息。 核心功能是解析自定义的表头注解。

package factory; import enums.ExcelHeadBeanFlagEnum; import headbean.ExcelColumnTitle; import headbean.ExcelHeadMapInterface; import java.lang.reflect.Field; import java.util.HashMap; import java.util.Map; public class ExcelHeadMapFactory { /** * 全局表头名映射，ExcelHeadMapInterface实现类型为key，它内部变量的变量名和中文名映射为value */ private static final Map<Class<? extends ExcelHeadMapInterface>, Map<String, String>> HEAD_NAME_MAP = new HashMap<>(); public static void addHeadClass(Class<? extends ExcelHeadMapInterface> headClass) { HEAD_NAME_MAP.put(headClass, mapToPrepareHead(headClass)); } public static Map<String, String> getHeadMap(Class<? extends ExcelHeadMapInterface> headClass) { return HEAD_NAME_MAP.get(headClass); } public static Map<String, String> getHeadMapByFlag(String flag) { return getHeadMap(ExcelHeadBeanFlagEnum.getHeadClass(flag)); } private static Map<String, String> mapToPrepareHead(Class<?> excelHeadClass) { Map<String, String> namedMap = new HashMap<>(); Field[] declaredFields = excelHeadClass.getDeclaredFields(); for (Field declaredField : declaredFields) { boolean annotationPresent = declaredField.isAnnotationPresent(ExcelColumnTitle.class); if(annotationPresent) { ExcelColumnTitle excelProperty = declaredField.getAnnotation(ExcelColumnTitle.class); String chineseFieldName = excelProperty.value(); // 保存字段名和中文变量名 namedMap.put(declaredField.getName(), chineseFieldName); } } return namedMap; } } 2.5 表flag和表头映射枚举

这个枚举，如果你的系统这类功能很多。可以设计为数据库的方式做映射。然后以查字典表的方式，来处理。当然使用枚举大概率是够用了。

package enums; import headbean.ExcelHeadMapInterface; import headbean.NameAndFactoryDemo; import headbean.StudentDemo; import lombok.AllArgsConstructor; import lombok.Getter; import java.util.Arrays; /** * 表头flag枚举，映射flag与对应的实体类型；主要是可以根据flag找到对应实体类型。 * * @author feng */ @Getter @AllArgsConstructor public enum ExcelHeadBeanFlagEnum { NAME_AND_FACTORY_DEMO("NameAndFactoryDemo", NameAndFactoryDemo.class), STUDENT_DEMO("StudentDemo", StudentDemo.class) ; private final String flag; private final Class<? extends ExcelHeadMapInterface> headClass; public static Class<? extends ExcelHeadMapInterface> getHeadClass(String flag) { return Arrays.stream(values()). filter(bean -> bean.getFlag().equals(flag)) .findFirst() .orElseThrow(RuntimeException::new) .getHeadClass(); } } 2.6 测试用的实体 2.6.1 NameAndFactoryDemo package headbean; import lombok.Data; @Data public class NameAndFactoryDemo implements ExcelHeadMapInterface { @ExcelColumnTitle("名字") private String name; @ExcelColumnTitle("工厂") private String factory; } 2.6.2 StudentDemo package headbean; import lombok.Data; @Data public class StudentDemo implements ExcelHeadMapInterface { @ExcelColumnTitle("姓名") private String name; @ExcelColumnTitle("年龄") private Integer age; @ExcelColumnTitle("学号") private String studentNo; @ExcelColumnTitle("班级") private String className; } 2.7 启动类

主要是项目启动后，注册表头数据到内存。

package org.feng; import factory.ExcelHeadMapFactory; import headbean.ExcelHeadMapInterface; import headbean.NameAndFactoryDemo; import headbean.StudentDemo; import org.springframework.boot.CommandLineRunner; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import java.util.ArrayList; import java.util.List; @SpringBootApplication public class ExcelDemoApplication implements CommandLineRunner { public static void main(String[] args) { SpringApplication.run(ExcelDemoApplication.class, args); } @Override public void run(String... args) throws Exception { List<Class<? extends ExcelHeadMapInterface>> needRegisterExcelHeadClassList = new ArrayList<>(); needRegisterExcelHeadClassList.add(NameAndFactoryDemo.class); needRegisterExcelHeadClassList.add(StudentDemo.class); needRegisterExcelHeadClassList.forEach(ExcelHeadMapFactory::addHeadClass); } } 2.8 测试控制器 package org.feng; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.write.metadata.WriteSheet; import enums.ExcelHeadBeanFlagEnum; import factory.ExcelHeadMapFactory; import headbean.ExcelDataLinkedHashMap; import headbean.ExcelHeadMapInterface; import headbean.NameAndFactoryDemo; import headbean.StudentDemo; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import javax.annotation.PostConstruct; import javax.servlet.http.HttpServletResponse; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.util.*; import java.util.function.BiFunction; @Controller @RequestMapping("/excel") public class ExcelDemoController { @GetMapping("/exportDy") public String exportDy(@RequestParam("flag")String flag, @RequestParam("table")List<String> table, HttpServletResponse response) throws IOException { String fileName = System.currentTimeMillis() + ".xlsx"; Class<? extends ExcelHeadMapInterface> headClass = ExcelHeadBeanFlagEnum.getHeadClass(flag); Map<String, String> namedPrepareHeadMap = ExcelHeadMapFactory.getHeadMap(headClass); Map<String, String> head = new LinkedHashMap<>(); for (String fieldName : table) { head.put(namedPrepareHeadMap.get(fieldName), fieldName); } List<Map<String, String>> excelDataList = new ArrayList<>(); excelDataList.add(head); // 制造假数据 for (BiFunction<Class<? extends ExcelHeadMapInterface>, List<Map<String, String>>, Boolean> biFunction : bizList) { Boolean applied = biFunction.apply(headClass, excelDataList); if(applied) { break; } } byte[] bytes = easyOut(excelDataList); response.setHeader("Content-disposition", "attachment;filename=" + fileName); response.setContentType("application/x-msdownload"); response.setCharacterEncoding("utf-8"); response.getOutputStream().write(bytes); response.getOutputStream().flush(); return "success"; } static final List<BiFunction<Class<? extends ExcelHeadMapInterface>, List<Map<String, String>>, Boolean>> bizList = new ArrayList<>(); @PostConstruct private void init() { bizList.add(this::genStudentDemoData); bizList.add(this::genNameAndFactoryDemoData); } private boolean genStudentDemoData(Class<? extends ExcelHeadMapInterface> headClass, List<Map<String, String>> excelDataList) { if(headClass == StudentDemo.class) { Map<String, String> headMap = excelDataList.get(0); for (int i = 0; i < 5; i++) { Collection<String> fieldNames = headMap.values(); Map<String, String> data = new ExcelDataLinkedHashMap<>(new ArrayList<>(fieldNames)); excelDataList.add(data); data.put("name", "张三"+(i+1)); data.put("age", "年龄"+(i+1)); data.put("studentNo", "学号"+(i+1)); data.put("className", "班级"+(i+1)); } return true; } return false; } private boolean genNameAndFactoryDemoData(Class<? extends ExcelHeadMapInterface> headClass, List<Map<String, String>> excelDataList) { if(headClass == NameAndFactoryDemo.class) { Map<String, String> headMap = excelDataList.get(0); for (int i = 0; i < 5; i++) { Collection<String> fieldNames = headMap.values(); Map<String, String> data = new ExcelDataLinkedHashMap<>(new ArrayList<>(fieldNames)); excelDataList.add(data); data.put("name", "张三"+(i+1)); data.put("factory", "工厂"+(i+1)); } return true; } return false; } /** * 导出数据（单sheet） * @param exportData key 是sheet名称，value是每个sheet里面的数据，支持自定义表头 */ public static byte[] easyOut(List<Map<String, String>> exportData) { return easyOut(Collections.singletonMap("Sheet", exportData)); } /** * 导出数据（多sheet） * @param exportData key 是sheet名称，value是每个sheet里面的数据，可以自定义 */ public static byte[] easyOut(Map<String, List<Map<String, String>>> exportData) { // 导出数据 ByteArrayOutputStream out = new ByteArrayOutputStream(); com.alibaba.excel.ExcelWriter excelWriter = EasyExcel.write(out).build(); int i=0; for (Map.Entry<String, List<Map<String, String>>> entry: exportData.entrySet()) { WriteSheet writeSheet = EasyExcel.writerSheet(i, entry.getKey()).head(head(entry.getValue().get(0))).build(); i++; excelWriter.write(data(entry.getValue(), true), writeSheet); } excelWriter.finish(); return out.toByteArray(); } private static List<List<String>> head(Map<String, String> cellData) { List<List<String>> head = new ArrayList<>(); for (String key: cellData.keySet()) { head.add(Collections.singletonList(key)); } return head; } private static List<List<String>> data(List<Map<String, String>> sheetData, boolean skipHead) { List<List<String>> data = new ArrayList<>(); for (int i = 0; i < sheetData.size(); i++) { if(i == 0 && skipHead) { continue; } data.add(new ArrayList<>(sheetData.get(i).values())); } return data; } } 三、测试 测试1

http://localhost:8080/excel/exportDy?flag=StudentDemo&table=name,studentNo,age,className

获得的表格内容为：

测试2

http://localhost:8080/excel/exportDy?flag=StudentDemo&table=name,studentNo,age

获得的表格内容为：

测试3

http://localhost:8080/excel/exportDy?flag=NameAndFactoryDemo&table=factory,name 获得的表格内容为：

测试4

http://localhost:8080/excel/exportDy?flag=StudentDemo&table=className,name,studentNo 获得的表格内容为：