spring boot导入导出excel,集成EasyExcel

一、安装依赖

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.2.0</version></dependency>

二、新建导出工具类

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.2.0</version></dependency>

三、新建实体类

package com.example.springbootclickhouse.Excel;import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.example.springbootclickhouse.ExcelValidTools.ExcelValid;
import com.example.springbootclickhouse.ExcelValidTools.NumberValid;
import com.example.springbootclickhouse.utils.GenderConverter;
import lombok.*;import java.io.Serializable;
import java.time.LocalDateTime;@Data
@AllArgsConstructor
@NoArgsConstructor
public class StudentExcel implements Serializable {private static final long serialVersionUID = 1L;@ExcelProperty("姓名")@ColumnWidth(20)@ExcelValid(message = "姓名列不能有空数据！")private String name;@ExcelProperty("年龄")@ColumnWidth(20)@NumberValid(message = "年龄不得小于0")private Integer age;@ExcelProperty(value = "性别",converter = GenderConverter.class)@ColumnWidth(20)private Integer gender;@ExcelProperty("日期")@ColumnWidth(20)private LocalDateTime date;}

@ExcelProperty： 核心注解，value属性可用来设置表头名称，converter属性可以用来设置类型转换器；

@ColumnWidth： 用于设置表格列的宽度；

@DateTimeFormat： 用于设置日期转换格式；

@NumberFormat： 用于设置数字转换格式。

四、如果需要有字段进行转换的，则为新建转换类，并在实体类上加注解
1、实体类上加如下

@ExcelProperty(value = "性别",converter = GenderConverter.class)

2、新建枚举类

package com.example.springbootclickhouse.ExcelEnum;import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.Getter;import java.util.stream.Stream;@AllArgsConstructor
@Getter
public enum GenderEnum {/*** 未知*/UNKNOWN(0, "未知"),/*** 男性*/MALE(1, "男性"),/*** 女性*/FEMALE(2, "女性");private final Integer value;@JsonFormatprivate final String description;public static GenderEnum convert(Integer value) {
//        用于为给定元素创建顺序流
//        values:获取枚举类型的对象数组return Stream.of(values()).filter(bean -> bean.value.equals(value)).findAny().orElse(UNKNOWN);}public static GenderEnum convert(String description) {return Stream.of(values()).filter(bean -> bean.description.equals(description)).findAny().orElse(UNKNOWN);}
}

3、新建转换器类

package com.example.springbootclickhouse.utils;import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;import com.example.springbootclickhouse.ExcelEnum.GenderEnum;public class GenderConverter implements Converter<Integer> {@Overridepublic Class<?> supportJavaTypeKey() {// 实体类中对象属性类型return Integer.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {// Excel中对应的CellData(单元格数据)属性类型return CellDataTypeEnum.STRING;}/*** 将单元格里的数据转为java对象，也就是女转成2，男转成1，用于导入excel时对性别字段进行转换* */@Overridepublic Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {// 从CellData中读取数据，判断Excel中的值，将其转换为预期的数值return GenderEnum.convert(context.getReadCellData().getStringValue()).getValue();}/*** 将java对象转为单元格数据，也就是2转成女，1转成男，用于导出excel时对性别字段进行转换* */@Overridepublic WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {// 判断实体类中获取的值，转换为Excel预期的值，并封装为CellData对象return new WriteCellData<>(GenderEnum.convert(context.getValue()).getDescription());}
}

五、导入时，数据进行验证的话
1、新建注解

package com.example.springbootclickhouse.ExcelValidTools;import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelValid {String message() default "导入有未填入的字段";
}

2、新建验证类

package com.example.springbootclickhouse.ExcelValidTools;import java.lang.reflect.Field;
import java.util.Objects;public class ExcelImportValid {/*** Excel导入字段校验* @param object 校验的JavaBean 其属性须有自定义注解*/public static void valid(Object object,Integer rowIndex) throws Exception {Field[] fields = object.getClass().getDeclaredFields();for (Field field : fields) {//设置可访问field.setAccessible(true);//属性的值Object fieldValue = null;try {fieldValue = field.get(object);} catch (IllegalAccessException e) {throw new Exception("第" + rowIndex + "行" + field.getAnnotation(ExcelValid.class).message(),e);}//是否包含必填校验注解boolean isExcelValid = field.isAnnotationPresent(ExcelValid.class);if (isExcelValid && Objects.isNull(fieldValue)) {throw new Exception("excel中第" + rowIndex + "行的" + field.getAnnotation(ExcelValid.class).message());}else if(isExcelValid && !(fieldValue instanceof Integer)){throw new Exception("excel中第" + rowIndex + "行的类型不正确" + field.getAnnotation(ExcelValid.class).message());}}}public static void validNumber(Object object,Integer rowIndex) throws Exception {Field[] fields = object.getClass().getDeclaredFields();for (Field field : fields) {//设置可访问field.setAccessible(true);//属性的值Object fieldValue = null;try {fieldValue = field.get(object);} catch (IllegalAccessException e) {throw new Exception("第" + rowIndex + "行" + field.getAnnotation(NumberValid.class).message(),e);}//是否包含必填校验注解boolean isExcelValid = field.isAnnotationPresent(NumberValid.class);if (isExcelValid && (Integer)fieldValue<=0) {throw new Exception("excel中第" + rowIndex + "行的" + field.getAnnotation(NumberValid.class).message());}}}
}

3、新建监听器类

package com.example.springbootclickhouse.ExcelValidTools;import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.springbootclickhouse.Excel.StudentExcel;
import lombok.Getter;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;import static com.alibaba.druid.sql.dialect.mysql.ast.clause.MySqlFormatName.JSON;@Slf4j
public class StudentListener extends AnalysisEventListener<StudentExcel> {private static final int BATCH_COUNT = 500;@GetterList<StudentExcel> list=new ArrayList<>(BATCH_COUNT);@Override@SneakyThrowspublic void invoke(StudentExcel studentExcel, AnalysisContext analysisContext) {log.info("数据校验:"+studentExcel);ExcelImportValid.valid(studentExcel,analysisContext.readRowHolder().getRowIndex()+1);ExcelImportValid.validNumber(studentExcel,analysisContext.readRowHolder().getRowIndex()+1);//可不用
//        list.add(studentExcel);
//        if (list.size() >= BATCH_COUNT) {
//            log.info("已经解析"+list.size()+"条数据");
//            list.clear();
//        }}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}}

4、在实体类上使用注解@ExcelValid(message = “姓名列不能有空数据！”)，同时在导入时新增

.registerReadListener(new StudentListener())

五、如果要求导出的excel加下拉选项框的话
1、新建导出处理类

package com.example.springbootclickhouse.ExcelHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;public class CustomSheetWriteHandler implements SheetWriteHandler {/*** 想实现Excel引用其他sheet页数据作为单元格下拉选项值，* 需要重写该方法** @param writeWorkbookHolder* @param writeSheetHolder*/@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 构造样例数据，该数据可根据实际需要，换成业务数据// 实际数据可通过构造方法，get、set方法等由外界传入List<String> selectDataList = new ArrayList<>();for (int i = 0; i < 100; i++) {selectDataList.add("下拉选项" + i);}// 构造下拉选项单元格列的位置，以及下拉选项可选参数值的map集合// key：下拉选项要放到哪个单元格，比如A列的单元格那就是0，C列的单元格，那就是2// value：key对应的那个单元格下拉列表里的数据项，比如这里就是下拉选项1..100Map<Integer, List<String>> selectParamMap = new HashMap<>();selectParamMap.put(0, selectDataList);// 获取第一个sheet页Sheet sheet = writeSheetHolder.getCachedSheet();// 获取sheet页的数据校验对象DataValidationHelper helper = sheet.getDataValidationHelper();// 获取工作簿对象，用于创建存放下拉数据的字典sheet数据页Workbook workbook = writeWorkbookHolder.getWorkbook();// 迭代索引，用于存放下拉数据的字典sheet数据页命名int index = 1;for (Map.Entry<Integer, List<String>> entry : selectParamMap.entrySet()) {// 设置存放下拉数据的字典sheet，并把这些sheet隐藏掉，这样用户交互更友好String dictSheetName = "dict_hide_sheet" + index;Sheet dictSheet = workbook.createSheet(dictSheetName);// 隐藏字典sheet页workbook.setSheetHidden(index++, true);// 设置下拉列表覆盖的行数，从第一行开始到最后一行，这里注意，Excel行的// 索引是从0开始的，我这边第0行是标题行，第1行开始时数据化，可根据实// 际业务设置真正的数据开始行，如果要设置到最后一行，那么一定注意，// 最后一行的行索引是1048575，千万别写成1048576，不然会导致下拉列表// 失效，出不来CellRangeAddressList infoList = new CellRangeAddressList(1, 1048575, entry.getKey(), entry.getKey());int rowLen = entry.getValue().size();for (int i = 0; i < rowLen; i++) {// 向字典sheet写数据，从第一行开始写，此处可根据自己业务需要，自定// 义从第几行还是写，写的时候注意一下行索引是从0开始的即可dictSheet.createRow(i).createCell(0).setCellValue(entry.getValue().get(i));}// 设置关联数据公式，这个格式跟Excel设置有效性数据的表达式是一样的String refers = dictSheetName + "!$A$1:$A$" + entry.getValue().size();Name name = workbook.createName();name.setNameName(dictSheetName);// 将关联公式和sheet页做关联name.setRefersToFormula(refers);// 将上面设置好的下拉列表字典sheet页和目标sheet关联起来DataValidationConstraint constraint = helper.createFormulaListConstraint(dictSheetName);DataValidation dataValidation = helper.createValidation(constraint, infoList);sheet.addValidationData(dataValidation);}}
}

2、在导出代码注册

 EasyExcel.write(response.getOutputStream()).registerWriteHandler(new CustomSheetWriteHandler()).head(StudentExcel.class).sheet("sheet1").doWrite(userList);