Alibaba EasyExcel 导入导出全家桶

一、阿里巴巴EasyExcel的优势

        首先说下EasyExcel相对 Apache poi的优势：

        EasyExcel也是阿里研发在poi基础上做了封装，改进产物。它替开发者做了注解列表解析，表格填充等一系列代码编写工作，并将此抽象成通用和可扩展的框架。相对poi，在数据量比较大的时候，它有着更优越的性能体现。导出的时候，easyexcel使用优化的反射技术，避免poi频繁的去创建cell和row对象；导入的时候，它的解析器AnalysisEventListener，可设置批量阈值 BATCH_COUNT，达到阈值就往数据库插入数据，然后清空解析器内部缓存，相同的表格，easyexcel导入所占用的内存要比poi节省90%，避免了大数据量导入的时候，造成的内存占用井喷（这使得stop the world的时间可能会被集中，而系统可能会出现短暂的停摆。），而GC不能均衡调动垃圾回收。同时也避免堆积数据后，sql的巨量数据的批量插入，导致超出mybatis批量插入语句能承受的最大长度限制。

二、EasyExcel核心util类

@Slf4j
public class EasyExcels {public static final String EXT_NAME_XLSX = "xlsx";public static final String EXT_NAME_XLS = "xls";/**** @param response* @param data* @param filename* @param sheetName* @param selectMap  自定义下拉列，但是既然数据都导出了，下拉用处何在？这个需求比较少* @param <T>* @throws IOException*/public static <T> void write(HttpServletResponse response, List<T> data, String filename, String sheetName,List<KeyValue<ExcelColumn, List<String>>> selectMap) throws IOException {setResponse(response, filename);if (StringUtils.isBlank(sheetName)) {sheetName = filename;}// 输出 Exceltry {EasyExcel.write(response.getOutputStream(), data != null && !data.isEmpty() ? data.get(0).getClass() : null).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度，自动适配。最大 255 宽度.registerWriteHandler(new CustomCellWriteWeightConfig()) // Excel 列宽自适应.registerWriteHandler(EasyExcelStyle.horizontalCellStyleStrategy) //内容样式.registerWriteHandler(new SelectWriteHandler(selectMap)) // 基于固定 sheet 实现下拉框.sheet(sheetName).doWrite(data);} catch (Exception e) {e.printStackTrace();} finally {response.getOutputStream().close();}}// 简单导入读取，不做解析，不做校验public static <T> List<T> read(MultipartFile file, Class<T> head) throws IOException {return EasyExcel.read(file.getInputStream(), head, null).autoCloseStream(false)  // 不要自动关闭，交给 Servlet 自己处理.doReadAllSync();}// 需要配合监听器解析数据public static <T> void read(MultipartFile file, Class<T> head, ReadListener<T> listener) throws IOException {EasyExcel.read(file.getInputStream(), head, listener).sheet().doRead();}// 不带下拉列的导出，用的比较多public static <T> void export(HttpServletResponse response, List<T> data, String filename, String sheetName) throws IOException {setResponse(response, filename);if (StringUtils.isBlank(sheetName)) {sheetName = filename;}EasyExcel.write(response.getOutputStream(), data != null && !data.isEmpty() ? data.get(0).getClass() : null).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CustomCellWriteWeightConfig()).registerWriteHandler(EasyExcelStyle.horizontalCellStyleStrategy).sheet(sheetName).doWrite(data);}// 用于合并单元格列的导出public static <T> void export(HttpServletResponse response, List<T> data, String filename, String sheetName, RowWriteHandler handler) throws IOException {setResponse(response, filename);EasyExcel.write(response.getOutputStream(), data != null && !data.isEmpty() ? data.get(0).getClass() : null).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CustomCellWriteWeightConfig()).registerWriteHandler(EasyExcelStyle.horizontalCellStyleStrategy).registerWriteHandler(handler).sheet(sheetName).doWrite(data);}// 用于导出表头模板，填充导入数据用的excel模板，因为是模板，所以肯定会有下拉列的需求public static <T> void export(HttpServletResponse response, Class<T> clazz, String filename) throws IOException {setResponse(response, filename);Map<Integer, ExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(clazz);EasyExcel.write(response.getOutputStream(), clazz).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
//                .registerWriteHandler(new CustomCellWriteHeightConfig()).registerWriteHandler(new CustomCellWriteWeightConfig()).registerWriteHandler(EasyExcelStyle.horizontalCellStyleStrategy).registerWriteHandler(new SelectSheetWriteHandler(selectedMap)).sheet(filename).doWrite(Collections.emptyList());}/*** 解析表头类中的下拉注解* @param head 表头类* @param <T> 泛型* @return Map<下拉框列索引, 下拉框内容> map*/private static <T> Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {Map<Integer, ExcelSelectedResolve> selectedMap = new HashMap<>();// getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性Field[] fields = head.getDeclaredFields();for (int i = 0; i < fields.length; i++){Field field = fields[i];// 解析注解信息ExcelSelected selected = field.getAnnotation(ExcelSelected.class);ExcelProperty property = field.getAnnotation(ExcelProperty.class);if (selected != null) {ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve();String[] source = excelSelectedResolve.resolveSelectedSource(selected);if (source != null && source.length > 0){excelSelectedResolve.setSource(source);excelSelectedResolve.setFirstRow(selected.firstRow());excelSelectedResolve.setLastRow(selected.lastRow());if (property != null && property.index() >= 0){selectedMap.put(property.index(), excelSelectedResolve);} else {selectedMap.put(i, excelSelectedResolve);}}}}return selectedMap;}public static void setResponse(HttpServletResponse response, String filename) throws IOException {setResponse(response, filename, EXT_NAME_XLSX);}public static void setResponse(HttpServletResponse response, String filename, String extName) throws IOException {String exportFilename = URLEncoder.encode(filename, StandardCharsets.UTF_8.name());response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");response.setHeader("Access-Control-Expose-Headers", "token,Content-Type,Content-disposition");response.setHeader("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept, Connection, User-Agent, Cookie, token,Content-Type,Content-disposition");response.setHeader("Content-disposition", exportFilename + "." + extName);}}

三、导入解析监听器

那要使用easyexcel，首先要解决解析器抽象类的实现：

        当时第一次使用easyexcel的时候，对这个工具框架不熟悉，项目时间被催的紧，没时间去做设计，当时修改每个类的字段注解index属性，每个字段单独写校验语句，简直苦不堪言。我只想说，磨刀不误砍柴工，不注重设计的公司，只会被拖延更多的时间。

/*** @Title: ExcelImportReadListener* @Description: 其他人如果觉得invoke()方法不满足其需求，可以自己实现一下* @Author: wenrong* @Date: 2024/4/25 17:08* @Version:1.0*/
@Data
public abstract class ExcelImportReadListener<T extends ValidateBaseBo> extends AnalysisEventListener<T> {private static final Logger log = LoggerFactory.getLogger("excelReadListener");public static int BATCH_COUNT = 1000;private AtomicLong successNum = new AtomicLong();private final Class<T> clazz;private Validator<T> validator;private List<T> successData = new ArrayList<>();private List<T> failureData = new ArrayList<>();public ExcelImportReadListener(Class<T> clazz) {this.clazz = clazz;}@Overridepublic void invoke(T data, AnalysisContext context) {log.info("解析到一条数据:{}", JSONObject.toJSON(data));StringBuilder errMsg = new StringBuilder();try {//根据excel数据实体中的javax.validation + 正则表达式来校验excel数据errMsg.append(EasyExcelValidateHandler.validateEntity(data));// 额外自定义校验，以及设置数据属性的逻辑if (validator != null) {errMsg.append(validator.validate(data));}} catch (NoSuchFieldException e) {log.error(e.getMessage());}if (StringUtils.isNotEmpty(errMsg.toString())) {data.setErrMsg(errMsg.toString());failureData.add(data);} else {successData.add(data);successNum.incrementAndGet();}if (BATCH_COUNT != 0 && successData.size() >= BATCH_COUNT) {try {saveData();} catch (Exception e) {log.error(e.getMessage(), e);}successData.clear();}}@Overridepublic final void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {// 验证表头if (headMap.isEmpty()) {throw new ServiceException("无表头");}if (clazz != null) {try {Map<Integer, String> indexNameMap = getIndexNameMap(clazz);for (Integer index : indexNameMap.keySet()) {log.info("表头字段：{}", headMap.get(index));if (StringUtils.isEmpty(headMap.get(index))) {throw new ServiceException("未设置index");}// 对比excel表头和解析数据的java实体类的，看是否匹配if (!headMap.get(index).equals(indexNameMap.get(index))) {throw new ServiceException("导入模板错误");}}} catch (NoSuchFieldException e) {log.error(e.getMessage(), e);}}}@Overridepublic final void doAfterAllAnalysed(AnalysisContext context) {log.info("所有数据解析完成！共校验成功{}条数据，校验失败{}条数据", successNum.get(), failureData.size());try {saveData();} catch (Exception e) {log.error(e.getMessage(), e);}}/*** 将该类做成抽象类，在各service中实现saveDate方法，* 不侵入业务，同时不会让解析占用内存*/public void saveData() throws Exception {log.info("开始往数据库插入数据");}private Map<Integer, String> getIndexNameMap(Class<T> clazz) throws NoSuchFieldException {Map<Integer, String> excelPropertyMap = new HashMap<>();Field field;Field[] fields = clazz.getDeclaredFields();int sequence = 0;for (Field item : fields) {field = clazz.getDeclaredField(item.getName());field.setAccessible(true);ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);if (excelProperty != null) {// 避免每个列都要写index,插入或删除一个字段，所有的index都需要修改。默认为java实体类中字段的顺序。int index = excelProperty.index() == -1 ? sequence : excelProperty.index();String[] values = excelProperty.value();StringBuilder value = new StringBuilder();for (String v : values) {value.append(v);}excelPropertyMap.put(index, value.toString());sequence++;}}return excelPropertyMap;}}

3.1、解析成功的数据直接落库，错误数据导出

        ValidateBaseBo：用在导入的时候，将校验的错误保留下来，然后再把有问题的数据过滤出来，再导出，或者显示在前端的导入结果里，操作者可以按照错误信息把表格里的数据修改好后，再次导入，而且只将导入失败的数据导出，不用去原表中大片的数据中去找有错误信息的数据，目的是方便操作者快速定位表格里的问题数据。

/*** @Title: ValidateBaseBo* @Description:* @Author: wenrong* @Date: 2024/10/17 上午11:02* @Version:1.0*/
@Data
public abstract class ValidateBaseBo {@ExcelProperty(value = "错误信息")@TableField(exist = false)@ApiModelProperty(hidden = true)private String errMsg;
}

3.2、解析过程中校验数据正确性 

        除了javax.validation，基础的注解校验之外，如果还需要额外的校验，就自定义校验器作补充。

/*** @Title: ValidData* @Description: javax.validation 以外校验函数* @Author: wenrong* @Date: 2024/4/26 19:51* @Version:1.0*/
public interface Validator<T> {/*** 这里的实现方法，最后返回的如果为null，一定要返回""，否则会被转化为"null"** @param T t* @return ""*/String validate(T t);
}

3.3、导入/导出 Convertor 

        excel导入数据对应的实体类：要注意表格中的汉字和实际存入到数据库中数值的转换：Convertor

/*** @author wenrong* @date 2024-11-25 17:38:26*/
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@TableName("····")
public class YourClass extends ValidateBaseBo implements Serializable {private static final long serialVersionUID = 1L;public static Map<String, DyzProvinceSchool> provinceMap = new HashMap<>();public static Map<String, DyzProvinceSchool> schoolMap = new HashMap<>();@ExcelIgnore@TableId(type = IdType.AUTO)private Long id;@ApiModelProperty(value = "省份ID")@ExcelProperty(value = "省份", converter = ProvinceConvertor.class)@NotNull(message = "不能为空")private Integer provinceId;@ApiModelProperty(value = "学校ID")@ExcelProperty(value = "学校", converter = SchoolConvertor.class)@NotNull(message = "不能为空")private Integer schoolId;@ApiModelProperty(value = "节目代码")@ExcelProperty(value = "节目代码")@NotNull(message = "不能为空")private String worksNumber;@ApiModelProperty(value = "节目/项目名称")@ExcelProperty(value = "节目/项目名称")@NotNull(message = "不能为空")private String ``````;public String validate(Map<Integer, DyzProvinceSchool> provinceMap, Map<Integer, DyzProvinceSchool> schoolMap) {StringBuilder sb = new StringBuilder(this.getErrMsg() == null ? "" : this.getErrMsg());if (provinceMap.get(this.provinceId) == null) {sb.append("省份不存在: ").append(provinceId);}if (schoolMap.get(this.schoolId) == null) {sb.append("学校不存在: ").append(schoolId);}return sb.toString();}public static class GroupTypeConvertor implements Converter<Integer> {//导入的时候，将表格的汉字转换成java对应数据库的字段@Overridepublic Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) {switch (cellData.getStringValue()) {default:return 2;case "小学组":return 0;case "中学组":return 1;}}// 导出的时候，将数据库中存储的值，转换为用户能看懂的汉字@Overridepublic WriteCellData<?> convertToExcelData(Integer value,ExcelContentProperty excelContentProperty,GlobalConfiguration globalConfiguration) {switch (value) {default:return new WriteCellData<>("其他组");case 0:return new WriteCellData<>("小学组");case 1:return new WriteCellData<>("中学组");}}}public static class PresentConvertor implements Converter<Integer> {@Overridepublic Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) {if (cellData.getStringValue().equals("否")) {return 0;} else {return 1;}}@Overridepublic WriteCellData<?> convertToExcelData(Integer value,ExcelContentProperty excelContentProperty,GlobalConfiguration globalConfiguration) {switch (value) {default:return new WriteCellData<>("-");case 0:return new WriteCellData<>("否");case 1:return new WriteCellData<>("是");}}}public static class ProvinceConvertor implements Converter<Integer> {@Overridepublic Integer convertToJavaData(ReadCellData<?> cellData,ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) throws Exception {if (provinceMap.isEmpty()) {throw new Exception("省份配置数据为空");}return StringUtils.isBlank(cellData.getStringValue()) ? null : provinceMap.get(cellData.getStringValue()).getId();}}public static class SchoolConvertor implements Converter<Integer> {@Overridepublic Integer convertToJavaData(ReadCellData<?> cellData,ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) throws Exception {if (schoolMap.isEmpty()) {throw new Exception("学校配置数据为空");}return StringUtils.isBlank(cellData.getStringValue()) ? null : schoolMap.get(cellData.getStringValue()).getId();}// 导出转换省略掉}}

3.4、实现导入解析监听器    

        上面的解析监听器是个抽象类，是一种模板模式的设计思想应用，我们根据不同的业务，自己扩展invoke方法和saveData方法，但其实saveData也可以做成模板方法，只是需要依赖内部绑定一个数据层dao接口，Mapper，对于有的人来说，会耦合dao层，但我觉得如果dao层取一个接口，那么也没什么耦合的问题。节省不必要的重复代码，还是值得的。

那么上述的那个模板抽象解析监听器可以改为：

/*** @Title: ExcelImportReadListener* @Description: 其他人如果觉得invoke()方法不满足其需求，可以自己实现一下* @Author: wenrong* @Date: 2024/4/25 17:08* @Version:1.0*/
@Data
public abstract class ExcelImportReadListener<T extends ValidateBaseBo, S extends IService<T>> extends AnalysisEventListener<T> {private static final Logger log = LoggerFactory.getLogger("excelReadListener");public static int BATCH_COUNT = 1000;private AtomicLong successNum = new AtomicLong();private final Class<T> clazz;private S service;private Validator<T> validator;private List<T> successData = new ArrayList<>();private List<T> failureData = new ArrayList<>();public ExcelImportReadListener(Class<T> clazz, S service) {this.clazz = clazz;this.service = service;}@Overridepublic void invoke(T data, AnalysisContext context) {log.info("解析到一条数据:{}", JSONObject.toJSON(data));StringBuilder errMsg = new StringBuilder();try {//根据excel数据实体中的javax.validation + 正则表达式来校验excel数据errMsg.append(EasyExcelValidateHandler.validateEntity(data));// 额外自定义校验，以及设置数据属性的逻辑if (validator != null) {errMsg.append(validator.validate(data));}} catch (NoSuchFieldException e) {log.error(e.getMessage());}if (StringUtils.isNotEmpty(errMsg.toString())) {data.setErrMsg(errMsg.toString());failureData.add(data);} else {successData.add(data);successNum.incrementAndGet();}if (BATCH_COUNT != 0 && successData.size() >= BATCH_COUNT) {try {saveData();} catch (Exception e) {log.error(e.getMessage(), e);}successData.clear();}}@Overridepublic final void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {// 验证表头if (headMap.isEmpty()) {throw new ServiceException("无表头");}if (clazz != null) {try {Map<Integer, String> indexNameMap = getIndexNameMap(clazz);for (Integer index : indexNameMap.keySet()) {log.info("表头字段：{}", headMap.get(index));if (StringUtils.isEmpty(headMap.get(index))) {throw new ServiceException("未设置index");}if (!headMap.get(index).equals(indexNameMap.get(index))) {throw new ServiceException("导入模板错误");}}} catch (NoSuchFieldException e) {log.error(e.getMessage(), e);}}}@Overridepublic final void doAfterAllAnalysed(AnalysisContext context) {log.info("所有数据解析完成！共校验成功{}条数据，校验失败{}条数据", successNum.get(), failureData.size());try {saveData();} catch (Exception e) {log.error(e.getMessage(), e);}}/*** 将该类做成抽象类，在各service中实现saveDate方法，* 不侵入业务，同时不会让解析占用内存*/public void saveData() throws Exception {log.info("开始往数据库插入数据");List<T> successData = this.getSuccessData();List<T> failureData = this.getFailureData();boolean saved = service.saveBatch(successData);if (!saved) {successData.forEach(work -> work.setErrMsg("保存失败"));failureData.addAll(successData);} else {this.setSuccessData(successData);}}private Map<Integer, String> getIndexNameMap(Class<T> clazz) throws NoSuchFieldException {Map<Integer, String> excelPropertyMap = new HashMap<>();Field field;Field[] fields = clazz.getDeclaredFields();int sequence = 0;for (Field item : fields) {field = clazz.getDeclaredField(item.getName());field.setAccessible(true);ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);if (excelProperty != null) {int index = excelProperty.index() == -1 ? sequence : excelProperty.index();String[] values = excelProperty.value();StringBuilder value = new StringBuilder();for (String v : values) {value.append(v);}excelPropertyMap.put(index, value.toString());sequence++;}}return excelPropertyMap;}}

业务代码中实现模板解析监听器的代码示例：

    @Override@Transactionalpublic ExcelImportReadListener<BasicWorks> importExcel(MultipartFile file) throws IOException {List<DyzProvinceSchool> schoolList = dyzProvinceSchoolService.getSchoolList();List<DyzProvinceSchool> provinceList = dyzProvinceSchoolService.getProvinceList();Map<String, DyzProvinceSchool> schoolMap = schoolList.stream().collect(Collectors.toMap(DyzProvinceSchool::getSchoolName, s -> s));Map<Integer, DyzProvinceSchool> schoolMap1 = schoolList.stream().collect(Collectors.toMap(DyzProvinceSchool::getId, s -> s));Map<String, DyzProvinceSchool> provinceMap = provinceList.stream().collect(Collectors.toMap(DyzProvinceSchool::getProvinceName, s -> s));Map<Integer, DyzProvinceSchool> provinceMap1 = provinceList.stream().collect(Collectors.toMap(DyzProvinceSchool::getId, s -> s));BasicWorks.schoolMap = schoolMap;BasicWorks.provinceMap = provinceMap;// 匿名内部类扩展模板监听器ExcelImportReadListener<BasicWorks> readListener = new ExcelImportReadListener<BasicWorks>(BasicWorks.class) {@Overridepublic void invoke(BasicWorks data, AnalysisContext context) {Set<String> allDataExistInExcel = new HashSet<>();Set<String> allDataExistInDataSource = list().stream().map(BasicWorks::getWorksNumber).collect(Collectors.toSet());List<BasicWorks> failureData = this.getFailureData();StringBuilder errMsg = new StringBuilder(data.getErrMsg() == null ? "" : data.getErrMsg());if (StringUtils.isBlank(data.getWorksNumber())) {errMsg.append("节目代码不能为空，");data.setErrMsg(errMsg.toString());failureData.add(data);} else {if (allDataExistInExcel.contains(data.getWorksNumber())) {errMsg.append("Excel表格中存在重复的数据，").append("节目代码：").append(data.getWorksNumber());data.setErrMsg(errMsg.toString());failureData.add(data);allDataExistInExcel.add(data.getWorksNumber());}if (allDataExistInDataSource.contains(data.getWorksNumber())) {errMsg.append("数据库中存在重复的数据，").append("节目代码：").append(data.getWorksNumber());data.setErrMsg(errMsg.toString());failureData.add(data);allDataExistInExcel.add(data.getWorksNumber());}}allDataExistInExcel.add(data.getWorksNumber());super.invoke(data, context);}// 设置javax.validation以外校验器，将会在invoke方法里执行校验readListener.setValidator(work -> work.validate(provinceMap1, schoolMap1));// 导入 ExcelEasyExcels.read(file, BasicWorks.class, readListener);return readListener;}

另外还有需要将表格中图片导入后自动上传到文件服务，然后将url保存在数据库的需求：

public ExcelImportReadListener<BasicHotel> importExcel(MultipartFile file) throws IOException {//获取图片，联合Apache 的ExcelUtil，ExcelPicUtil工具类，获取图片数据对象PictureDataExcelReader reader = ExcelUtil.getReader(file.getInputStream());Map<String, PictureData> picMap = ExcelPicUtil.getPicMap(reader.getWorkbook(), 0);ExcelImportReadListener<BasicHotel> readListener = new ExcelImportReadListener<BasicHotel>(BasicHotel.class) {@Overridepublic void invoke(BasicHotel data, AnalysisContext context) {Set<String> allDataExistInDataSource = list().stream().map(BasicHotel::getHotelName).collect(Collectors.toSet());Set<String> allDataExistInExcel = new HashSet<>();List<BasicHotel> failureData = this.getFailureData();StringBuilder errMsg = new StringBuilder(data.getErrMsg() == null ? "" : data.getErrMsg());if (StringUtils.isEmpty(data.getErrMsg())) {errMsg.append("酒店名称不能为空，");data.setErrMsg(errMsg.toString());failureData.add(data);} else {if (allDataExistInExcel.contains(data.getHotelName())) {errMsg.append("Excel表格中存在重复的数据，").append("酒店名称：").append(data.getHotelName());data.setErrMsg(errMsg.toString());failureData.add(data);allDataExistInExcel.add(data.getHotelName());}if (allDataExistInDataSource.contains(data.getHotelName())) {errMsg.append("数据库中存在重复的数据，").append("酒店名称：").append(data.getHotelName());data.setErrMsg(errMsg.toString());failureData.add(data);allDataExistInExcel.add(data.getHotelName());}}allDataExistInExcel.add(data.getHotelName());String err = "";int rowIndex = context.readRowHolder().getRowIndex() + 1;PictureData pictureData = picMap.get(rowIndex + "_0");if (pictureData == null) {err = String.format(data.getErrMsg() + "第%s行，%s", rowIndex, "酒店照片为空");}try {// 上传图片String fileUrl = ossFileController.ftpUploadFile(pictureData.getData(), "", data.getHotelName());data.setPicture(fileUrl);} catch (IOException ex) {err = String.format(data.getErrMsg() + "第%s行，%s", rowIndex, "酒店照片为空上传失败");}data.setErrMsg(err);super.invoke(data, context);}};// 导入 ExcelEasyExcels.read(file, BasicHotel.class, readListener);return readListener;
}

        这是导入部分，导出部分，五花八门的需求就比较多了。

四、导出

4.1、导出 数据导入模板

模板一般会有下拉选项列的需求，下拉列一般用注解枚举几个就行了：

import java.lang.annotation.*;/*** 标注导出的列为下拉框类型，并为下拉框设置内容*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelSelected {/*** 固定下拉内容*/String[] source() default {};/*** 设置下拉框的起始行，默认为第二行*/int firstRow() default 1;/*** 设置下拉框的结束行，默认为最后一行*/int lastRow() default 0x10000;
}

下拉注解解析器：

@Data
@Slf4j
public class ExcelSelectedResolve {/*** 下拉内容*/private String[] source;/*** 设置下拉框的起始行，默认为第二行*/private int firstRow;/*** 设置下拉框的结束行，默认为最后一行*/private int lastRow;public String[] resolveSelectedSource(ExcelSelected excelSelected) {if (excelSelected == null) {return null;}// 获取固定下拉框的内容String[] source = excelSelected.source();if (source.length > 0) {return source;}//        // 获取动态下拉框的内容
//        Class<? extends ExcelDynamicSelect>[] classes = excelSelected.sourceClass();
//        if (classes.length > 0) {
//            try {
//                ExcelDynamicSelect excelDynamicSelect = classes[0].newInstance();
//                String[] dynamicSelectSource = excelDynamicSelect.getSource();
//                if (dynamicSelectSource != null && dynamicSelectSource.length > 0) {
//                    return dynamicSelectSource;
//                }
//            } catch (InstantiationException | IllegalAccessException e) {
//                log.error("解析动态下拉框数据异常", e);
//            }
//        }return null;}}

下拉handler:

public class SelectSheetWriteHandler implements SheetWriteHandler {private final Map<Integer, ExcelSelectedResolve> selectedMap;public SelectSheetWriteHandler(Map<Integer, ExcelSelectedResolve> selectedMap) {this.selectedMap = selectedMap;}/*** Called before create the sheet*/@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}/*** Called after the sheet is created*/@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 这里可以对cell进行任何操作Sheet sheet = writeSheetHolder.getSheet();DataValidationHelper helper = sheet.getDataValidationHelper();selectedMap.forEach((k, v) -> {// 设置下拉列表的行： 首行，末行，首列，末列CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);// 设置下拉列表的值DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());// 设置约束DataValidation validation = helper.createValidation(constraint, rangeList);// 阻止输入非下拉选项的值validation.setErrorStyle(DataValidation.ErrorStyle.STOP);validation.setShowErrorBox(true);validation.setSuppressDropDownArrow(true);validation.createErrorBox("提示", "请输入下拉选项中的内容");sheet.addValidationData(validation);});}}

        其实如果是动态的下拉列表，不能固定枚举的话，直接从配置数据表中拉出业务配置列表，将列表作为传参，使用util类EasyExcels第一个方法就好。

    @ExcelProperty(value = "组别")@ExcelSelected(source = {"小学组", "初中组"})@ApiModelProperty(value = "组别:0-小学组，1-初中组，2-其他组")@NotNull(message = "不能为空")private String groupType;@ExcelProperty(value = "是否出席")@ExcelSelected(source = {"是", "否"})@ApiModelProperty(value = "是否出席：0-否，1是")@NotNull(message = "不能为空")private String present;

4.2、图片导出convertor：

public class UrlPictureConverter implements Converter<String> {public static int urlConnectTimeout = 2000;public static int urlReadTimeout = 6000;@Overridepublic Class<?> supportJavaTypeKey() {return String.class;}@Overridepublic WriteCellData<?> convertToExcelData(String url, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) throws IOException {InputStream inputStream = null;try {URL value = new URL(url);if (ObjectUtils.isEmpty(value)) {return new WriteCellData<>("");}URLConnection urlConnection = value.openConnection();urlConnection.setConnectTimeout(urlConnectTimeout);urlConnection.setReadTimeout(urlReadTimeout);inputStream = urlConnection.getInputStream();byte[] bytes = IoUtils.toByteArray(inputStream);return new WriteCellData<>(bytes);} catch (Exception e) {log.info("图片获取异常", e);return new WriteCellData<>("图片获取异常");} finally {if (inputStream != null) {inputStream.close();}}}
}

4.3、有合并单元格导出：

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@ExcelIgnoreUnannotated
@Slf4j
public class WorkJudgesStatisticsVo implements Serializable {private static final long serialVersionUID = 1L;@ExcelProperty(value = "序号", index = 0)private String sequence;public static class MergeStrategy implements RowWriteHandler {private int totalRowNum;public MergeStrategy(int totalRowNum) {this.totalRowNum = totalRowNum;}public static MergeStrategy build(int totalRowNum) {return new MergeStrategy(totalRowNum);}@Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {if (isHead) {// 处理表头return;}log.info("当前行号:{}", row.getRowNum());log.info("总行数:{}", totalRowNum);// 合并if (row.getRowNum() != totalRowNum + 1) {return;}writeSheetHolder.getSheet().addMergedRegion(new CellRangeAddress(writeSheetHolder.getLastRowIndex(), writeSheetHolder.getLastRowIndex(), 5, 6));}}}

4.4、行转列，并使用模板的方式导出：

@Override
public void selectWorksJudgesResultReview(HttpServletResponse response) throws IOException {List<Map<String, Object>> views = scoreReviewWorksJudgesMapper.selectWorksJudgesResultReview();List<DyzScoreReviewWorksJudges> reviewWorksJudges = scoreReviewWorksJudgesMapper.selectList();List<DyzScoreWorksFiles> scoreWorksFiles = worksFilesMapper.selectList();HashMap<Long, List<DyzScoreWorksFiles>> fileMap = new HashMap<>();scoreWorksFiles.forEach(f -> {List<DyzScoreWorksFiles> files = fileMap.computeIfAbsent(f.getWorksId(), k -> new ArrayList<>());files.add(f);});HashMap<Long, List<DyzScoreReviewWorksJudges>> scoreMap = new HashMap<>();reviewWorksJudges.forEach(judge -> {List<DyzScoreReviewWorksJudges> judges = scoreMap.computeIfAbsent(judge.getWorksId(), k -> new ArrayList<>());judges.add(judge);});AtomicInteger sequence = new AtomicInteger(0);views.forEach(map -> {map.put("sequence", String.valueOf(sequence.incrementAndGet()));Long workId = Long.valueOf(map.get("workId").toString());List<DyzScoreReviewWorksJudges> judges = scoreMap.get(workId);for (int i = 0; i < 15; i++) {map.put("score" + (i + 1), "");map.put("correctness" + (i + 1), "");}map.put("avgScore", "");map.put("avgScore1", "");if (judges != null && judges.size() > 0) {AtomicInteger serialNo = new AtomicInteger(0);AtomicInteger serialNo1 = new AtomicInteger(0);judges.forEach(j -> {map.put("score" + serialNo.incrementAndGet(), j.getScore());map.put("correctness" + serialNo1.incrementAndGet(), j.getRemark());});judges.sort(Comparator.comparing(DyzScoreReviewWorksJudges::getScore));BigDecimal sum = judges.stream().map(DyzScoreReviewWorksJudges::getScore).reduce(BigDecimal.ZERO, BigDecimal::add);BigDecimal avg = sum.divide(BigDecimal.valueOf(judges.size()), 2, BigDecimal.ROUND_HALF_UP);map.put("avgScore", avg);if (judges.size() > 3) {judges.remove(0);judges.remove(judges.size() - 1);BigDecimal sum1 = judges.stream().map(DyzScoreReviewWorksJudges::getScore).reduce(BigDecimal.ZERO, BigDecimal::add);BigDecimal avg1 = sum1.divide(BigDecimal.valueOf(judges.size()), 2, BigDecimal.ROUND_HALF_UP);map.put("avgScore1", avg1);}}for (int i = 0; i < 4; i++) {map.put("fileName" + (i + 1), "");}List<DyzScoreWorksFiles> files = fileMap.get(workId);if (files != null && files.size() > 0) {AtomicInteger serialNo = new AtomicInteger(0);files.forEach(f -> map.put("fileName" + serialNo.incrementAndGet(), f.getUrl()));}});ConcurrentHashSet<String> columns = views.stream().flatMap(map -> map.keySet().stream()).collect(Collectors.toCollection(ConcurrentHashSet::new));List<String> scoreColumns = columns.stream().filter(c -> c.contains("score") || c.contains("avgScore")).collect(Collectors.toList());List<String> correctnessColumns = columns.stream().filter(c -> c.contains("correctness")).collect(Collectors.toList());//输入流InputStream inputStream = null;ServletOutputStream outputStream = null;ExcelWriter excelWriter = null;try {org.springframework.core.io.Resource templateFile = resourceLoader.getResource("classpath:templates\\XXXX报表.xlsx");inputStream = templateFile.getInputStream();// 获取文件名并转码response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");outputStream = response.getOutputStream();// 创建填充配置FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();// 创建写对象excelWriter = EasyExcel.write(outputStream).withTemplate(inputStream).build();// 创建Sheet对象WriteSheet sheet = EasyExcel.writerSheet(0, "报名数量统计").build();excelWriter.fill(views, fillConfig, sheet);excelWriter.fill(new FillWrapper("scoreColumns", scoreColumns), sheet);excelWriter.fill(new FillWrapper("correctnessColumns", correctnessColumns), sheet);} catch (Exception e) {log.error("导出失败={}", e.getMessage());} finally {if (excelWriter != null) {excelWriter.finish();}//关闭流if (outputStream != null) {try {outputStream.close();} catch (IOException e) {log.error("关闭输出流失败", e);}}if (inputStream != null) {try {inputStream.close();} catch (IOException e) {log.error("关闭输入流失败", e);}}}
}

模板里面的取值占位符写法

        还有一些表格宽度，高度自适应策略，美化风格的代码就不贴了，需要的话到我的资源中去下载。