EasyExcel 动态导入导出
一、通过自定义注解的方式来对日期格式和空值进行校验
@ExcelValid (message = "配件名称不能为空")
@ExcelDateValid(message = "购置时间不正确")
package com.github.jfcloud.v42device.domain.excel.data;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.github.jfcloud.v42device.domain.excel.annotation.ExcelDateValid;
import com.github.jfcloud.v42device.domain.excel.annotation.ExcelDynamic;
import com.github.jfcloud.v42device.domain.excel.annotation.ExcelValid;
import com.github.jfcloud.v42device.domain.excel.converter.UnitExcelConverter;
import lombok.Data;
import java.util.Map;
/**
* 仪器配件Excel
*
* @author xzy
* @create 2022/9/21 10:53
*/
@Data
@HeadRowHeight(20)
@ColumnWidth(20)
public class InstrumentSparePartsExcelData {
@ExcelProperty("配件名称")
@ExcelValid(message = "配件名称不能为空")
private String name;
@ExcelProperty(value = "配件编码")
private String code;
@ExcelProperty(value = "配件数量")
private Integer number;
@ExcelProperty(value = "单位" ,converter = UnitExcelConverter.class)
private String unit;
//@ExcelProperty(value = "产品编码")
//@ExcelValid(message = "产品编码不能为空")
//private String productCode;
@ExcelProperty(value = "配件类型")
@ExcelValid(message = "配件类型不能为空")
private String type;
@ExcelProperty(value = "购置时间")
@ExcelDateValid(message = "购置时间不正确")
private String purchaseTime;
@ExcelProperty(value = "存放位置编码")
@ExcelValid(message = "存放位置编码不能为空")
private String locationCode;
@ExcelDynamic
@ExcelIgnore
private Map<String, Object> formValue;
}
package com.github.jfcloud.v42device.domain.excel.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Excel导入日期格式校验注解
*
* @author xzy
* @create 2022/9/19 19:12
*/
@Target({ ElementType.FIELD, ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelDateValid {
String message() default "导入的日期格式不正确";
}
package com.github.jfcloud.v42device.domain.excel.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Excel导入必填校验注解
*
* @author xzy
* @create 2022/9/19 19:12
*/
@Target({ ElementType.FIELD, ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelValid {
String message() default "导入有未填入的字段";
}
通过通用的监听器来进行校验
package com.github.jfcloud.v42device.domain.excel.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.github.jfcloud.v42device.domain.excel.utils.ExcelImportValid;
import com.github.jfcloud.v42device.domain.excel.exception.ExceptionCustom;
import java.util.ArrayList;
import java.util.List;
/**
* 通用监听器 后期可做优化
*
* @author xzy
* @create 2022/9/27 11:14
*/
public class GenericListener<T> extends AnalysisEventListener<T> {
private List<T> list = new ArrayList();
public List<T> getList() {
return this.list;
}
public void setList(List<T> list) {
this.list = list;
}
@Override
public void invoke(T t, AnalysisContext analysisContext) {
try {
//通用方法数据校验
ExcelImportValid.valid(t);
}catch (ExceptionCustom e){
int row = analysisContext.readRowHolder().getRowIndex() + 1;
System.out.println(e.getMessage());
//在easyExcel监听器中抛出业务异常
throw new ExcelAnalysisException("第" + row + "行"+e.getMessage());
}
this.list.add(t);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
校验方法
package com.github.jfcloud.v42device.domain.excel.utils;
import com.github.jfcloud.v42device.domain.excel.exception.ExceptionCustom;
import com.github.jfcloud.v42device.domain.excel.annotation.ExcelDateValid;
import com.github.jfcloud.v42device.domain.excel.annotation.ExcelValid;
import java.lang.reflect.Field;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.Objects;
/**
* Excel导入字段校验
*
* @author xzy
* @create 2022/9/19 19:11
*/
public class ExcelImportValid {
/**
* Excel导入字段校验
*
* @param object 校验的JavaBean 其属性须有自定义注解
*/
public static void valid(Object object) throws ExceptionCustom {
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 ExceptionCustom("IMPORT_PARAM_CHECK_FAIL", "导入参数检查失败");
}
//是否包含必填校验注解
boolean isExcelValid = field.isAnnotationPresent(ExcelValid.class);
if (isExcelValid && Objects.isNull(fieldValue)) {
throw new ExceptionCustom("NULL", field.getAnnotation(ExcelValid.class).message());
}
boolean isExcelDateValid = field.isAnnotationPresent(ExcelDateValid.class);
if(isExcelDateValid ){
if(!Objects.isNull(fieldValue)){
String s = fieldValue.toString();
try {
LocalDate.parse(s, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
}catch (DateTimeParseException e){
throw new ExceptionCustom("NULL", field.getAnnotation(ExcelDateValid.class).message());
}
}
}
}
}
}
自定义的异常类
package com.github.jfcloud.v42device.domain.excel.exception;
import lombok.Data;
import lombok.EqualsAndHashCode;
/**
* 自定义注解异常
*
* @author xzy
* @create 2022/9/19 19:12
*/
@EqualsAndHashCode(callSuper = true)
@Data
public class ExceptionCustom extends RuntimeException {
private static final long serialVersionUID = 1L;
public ExceptionCustom() {
}
/**
* 错误编码
*/
private String errorCode;
/**
* 消息是否为属性文件中的Key
*/
private boolean propertiesKey = true;
/**
* 构造一个基本异常.
*
* @param message
* 信息描述
*/
public ExceptionCustom(String message)
{
super(message);
}
/**
* 构造一个基本异常.
*
* @param errorCode
* 错误编码
* @param message
* 信息描述
*/
public ExceptionCustom(String errorCode, String message)
{
this(errorCode, message, true);
}
/**
* 构造一个基本异常.
*
* @param errorCode
* 错误编码
* @param message
* 信息描述
*/
public ExceptionCustom(String errorCode, String message, Throwable cause)
{
this(errorCode, message, cause, true);
}
/**
* 构造一个基本异常.
*
* @param errorCode
* 错误编码
* @param message
* 信息描述
* @param propertiesKey
* 消息是否为属性文件中的Key
*/
private ExceptionCustom(String errorCode, String message, boolean propertiesKey)
{
super(message);
this.setErrorCode(errorCode);
this.setPropertiesKey(propertiesKey);
}
/**
* 构造一个基本异常.
*
* @param errorCode
* 错误编码
* @param message
* 信息描述
*/
public ExceptionCustom(String errorCode, String message, Throwable cause, boolean propertiesKey)
{
super(message, cause);
this.setErrorCode(errorCode);
this.setPropertiesKey(propertiesKey);
}
/**
* 构造一个基本异常.
*
* @param message
* 信息描述
* @param cause
* 根异常类（可以存入任何异常）
*/
public ExceptionCustom(String message, Throwable cause)
{
super(message, cause);
}
}
具体使用
EasyExcel.read(file.getInputStream(), InstrumentSparePartsExcelData.class, new GenericListener<InstrumentSparePartsExcelData>()).sheet().doReadSync();
二、通过自定义注解来实现动态表头的导入
在项目中动态表单是通过mongo存储 Map<String, Object> formValue
这里自定义了@ExcelDynamic 来表示为动态表头
- 通过Excels 和 DynamicReadListener 具体实现
package com.github.jfcloud.v42device.domain.excel.utils;
import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.alibaba.excel.read.listener.ModelBuildEventListener;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.github.jfcloud.v42device.domain.excel.RowWriteHandlerImpl;
import com.github.jfcloud.v42device.domain.excel.annotation.ExcelDynamic;
import com.github.jfcloud.v42device.domain.excel.listener.DynamicReadListener;
import com.github.jfcloud.v42device.domain.excel.listener.GenericListener;
import org.apache.poi.ss.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;
/**
* Excel 工具类
*
* @author xzy
* @create 2022/9/28 19:11
*/
public class Excels extends EasyExcel {
private Excels() {}
/**
* 导入带有动态列的数据
*
* @param fileStream 文件流
* @param clazz 导入类型class
* @param <T> 导入类型
* @return List<T>
*/
public static <T> List<T> importsDynamic(InputStream fileStream, Class<T> clazz,List<String> keys) throws IllegalAccessException, InstantiationException {
//初始化 处理动态列 readListener
DynamicReadListener dynamicReadListener = DynamicReadListener.init();
//初始化 同步读取数据 syncReadListener
GenericListener<T> syncReadListener = new GenericListener<>();
/*
* useDefaultListener = false
* 默认的 readListener 即 ModelBuildEventListener 会第一个去处理数据，导致ReadHolder中的 currentRowAnalysisResult 已转为 Model 类型，
* dynamicReadListener 调用到 invoke 时会报错, 因此需要 dynamicReadListener 排在 readListenerList 的第一位，保证能够接收到 map 类型，处理动态列
* 但我们仍需要 ModelBuildEventListener 所以我们手动注册
*/
ExcelReaderSheetBuilder sheet = read(fileStream).useDefaultListener(false).head(clazz).sheet();
sheet.registerReadListener(dynamicReadListener);
//注册 map转 model readListener
sheet.registerReadListener(new ModelBuildEventListener());
sheet.registerReadListener(syncReadListener);
sheet.doRead();
return build( syncReadListener.getList(), dynamicReadListener, keys);
}
/**
* 处理源数据 为其实例化其中的动态列属性
*
* @param targets 处理目标List<T> 中的动态列 为其实例化动态列属性
* @param <T> 处理数据类型
* @return 源数据
* @throws IllegalAccessException IllegalAccessException
*/
private static <T> List<T> build(List<T> targets, DynamicReadListener listener,List<String> collect) throws IllegalAccessException, InstantiationException {
List<Map<String, String>> dataList = listener.getDataList();
if (CollectionUtil.isNotEmpty(targets)){
for (int i = 0; i < targets.size(); i++) {
T target = targets.get(i);
Map<String, String> map = dataList.get(i);
Map<String, String> formValue = new HashMap<>();
collect.forEach(c->formValue.put(c,map.get(c)));
// 初始化带有 @ExcelDynamic 标志的属性
for (Field targetField : target.getClass().getDeclaredFields()) {
if (Objects.nonNull(targetField.getAnnotation(ExcelDynamic.class))){
targetField.setAccessible(true);
targetField.set(target, formValue);
}
}
}
}
return targets;
}
public static void downloadExcel(HttpServletResponse response, List<List<String>> heads) throws IOException {
// 表头样式策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 设置数据格式
// 是否换行
headWriteCellStyle.setWrapped(false);
// 水平对齐方式
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 垂直对齐方式
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 前景色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// 背景色
headWriteCellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
// 设置为1时，单元格将被前景色填充
headWriteCellStyle.setFillPatternType(FillPatternType.NO_FILL);
// 控制单元格是否应自动调整大小以适应文本过长时的大小
headWriteCellStyle.setShrinkToFit(false);
// 单元格边框类型
headWriteCellStyle.setBorderBottom(BorderStyle.NONE);
headWriteCellStyle.setBorderLeft(BorderStyle.NONE);
headWriteCellStyle.setBorderRight(BorderStyle.NONE);
headWriteCellStyle.setBorderTop(BorderStyle.NONE);
// 单元格边框颜色
headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.index);
headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.index);
headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
// 字体策略
WriteFont writeFont = new WriteFont();
// 是否加粗/黑体
writeFont.setBold(false);
// 字体颜色
writeFont.setColor(Font.COLOR_NORMAL);
// 字体名称
writeFont.setFontName("宋体");
// 字体大小
writeFont.setFontHeightInPoints((short) 11);
// 是否使用斜体
writeFont.setItalic(false);
// 是否在文本中使用横线删除
writeFont.setStrikeout(false);
// 设置要使用的文本下划线的类型
writeFont.setUnderline(Font.U_NONE);
// 设置要使用的字符集
headWriteCellStyle.setWriteFont(writeFont);
// 内容样式策略策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.GENERAL);
contentWriteCellStyle.setBorderBottom(BorderStyle.NONE);
contentWriteCellStyle.setBorderLeft(BorderStyle.NONE);
contentWriteCellStyle.setBorderRight(BorderStyle.NONE);
contentWriteCellStyle.setBorderTop(BorderStyle.NONE);
contentWriteCellStyle.setFillPatternType(FillPatternType.NO_FILL);
contentWriteCellStyle.setWrapped(false);
contentWriteCellStyle.setDataFormat((short)49);
EasyExcel.write(response.getOutputStream()).head(heads)
.registerWriteHandler(new RowWriteHandlerImpl())
.registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
.registerWriteHandler(new SimpleColumnWidthStyleStrategy(16)) // 列宽
.sheet("sheet1").doWrite(null);
}
}
package com.github.jfcloud.v42device.domain.excel.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.util.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.util.NumberToTextConverter;
import java.util.*;
/**
* Excel生成动态表头
*
* @author xzy
* @create 2022/9/27 8:59
*/
public class DynamicReadListener extends AnalysisEventListener<Map<Integer, CellData<?>>> {
/**
* 表头数据（存储所有的表头数据）
*/
private final List<Map<Integer, String>> headList = new ArrayList<>();
/**
* 数据体
*/
//private final List<Map<Integer, String>> dataList = new ArrayList<>();
private final List<Map<String,String>> dataList = new ArrayList<>();
/**
* 实例化 Listener
* @return Listener
*/
public static DynamicReadListener init(){
return new DynamicReadListener();
}
/**
* 这里会一行行的返回头
* @param headMap 表头
* @param context 上线文
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
headList.add(headMap);
}
@Override
public void invoke(Map<Integer, CellData<?>> data, AnalysisContext context) {
Map<Integer, String> integerStringMap = headList.get(0);
HashMap<String, String> map = new HashMap<>();
for (int i = 0; i < integerStringMap.size(); i++) {
CellData<?> cellData = data.get(i);
String s;
if(cellData !=null && cellData.getType() == CellDataTypeEnum.NUMBER ){
s = NumberToTextConverter.toText(cellData.getNumberValue().doubleValue());
if(cellData.getDataFormatString().equals("yyyy/m/d")){
s = DateUtils.format(HSSFDateUtil.getJavaDate(cellData.getNumberValue().doubleValue(),
false, null), "yyyy-MM-dd");
}
}else{
s = cellData ==null?"": cellData.toString();
}
map.put(integerStringMap.get(i), s);
}
dataList.add(map);
}
/**
* 后置处理
* @param context 上线文
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {}
public List<Map<String, String>> getDataList() {
return dataList;
}
}
Excels.importsDynamic(file.getInputStream(), InstrumentExcelData.class, collect);
三、动态表头导出
需要在实体类的基础上加上动态表单的数据作为表头并导出
- 关键代码
package com.github.jfcloud.v42device.domain.excel.utils;
import com.alibaba.excel.annotation.ExcelProperty;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
/**
* 动态添加表头
*
* @author xzy
* @create 2022/9/22 15:04
*/
public class ExcelHead {
/**
* excel表中表头
* @param clazz 某类
* @param listKey 特有表头集合
* @return List<List<String>>
*/
public static List<List<String>> head(Class<?> clazz, List<String> listKey) {
List<List<String>> list = new ArrayList<>();
List<String> listHeads = new ArrayList<>();
//通过实体类@ExcelProperty注解获取固定表头，存到listHeads中
Field[] declaredFields = clazz.getDeclaredFields();
for (Field declaredField : declaredFields) {
Annotation[] annotations = declaredField.getAnnotations();
for (Annotation annotation : annotations) {
if(annotation.annotationType()==ExcelProperty.class){
String[] valueHead =((ExcelProperty) annotation).value();
listHeads.addAll(Arrays.asList(valueHead));
}
}
}
//将listHeads存到list中
for (String formHead : listHeads) {
List<String> head = new ArrayList<>();
head.add(formHead);
list.add(head);
}
//将所有表头存到list中
listKey.forEach(item -> list.add(Collections.singletonList(item)));
return list;
}
}
public void downloadExcel(HttpServletResponse response,@PathVariable("productId")Long productId) throws IOException {
final Product byId = this.productService.getById(productId);
if (null == byId.getCategoryId()) {
return;
}
final Category category = categoryService.getById(byId.getCategoryId());
final String formId = category.getFormId();
List<AttributeVo> ableShowColumn = dfsUtils.getAbleShowColumn(formId);
List<String> collect = ableShowColumn.stream().map(DataNameLabelVo::getLabel).collect(Collectors.toList());
List<List<String>> heads = ExcelHead.head(InstrumentSparePartsExcelData.class,collect);
Excels.downloadExcel(response,heads);
}
