EasyExcel基本使用
用来实现系统的导出和导入
一、准备工作
<!--easyexcel依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.0-beta1</version>
</dependency>
二、注解意思
//用于匹配excel和实体类的匹配
@ExcelProperty
| 名称 | 描述 |
|---|---|
| value | 用于匹配excel中的头，必须全匹配,如果有多行头，会匹配最后一行头 |
| order | 优先级高于value，会根据order的顺序来匹配实体和excel中数据的顺序（很少用） |
| index | 优先级高于value和order，会根据index直接指定到excel中具体的哪一列 |
| converter | 指定当前字段用什么转换器 |
//默认所有字段都会和excel去匹配，加了这个注解会忽略该字段;不加则用字段名去当excel的列名;加在字段上面
@ExcelIgnore
//默认不加@ExcelProperty注解的都会参与读写，加了@ExcelIgnoreUnannotated不会参与;加在类名上面
@ExcelIgnoreUnannotated
//自带日期格式转换（将String类型的日期转换成自定义模板的Date类型日期）
@DateTimeFormat(pattern="yyyy-MM-dd")
private Date date;
//自带数字转换（将String类型的数字转换为自定义模板的数字类型数字）
@NumberFormat(pattern="#,###,###.#")
private Float number;
三、导出
1、为导出对象添加注解
@Data
@TableName("t_user")
public class User {
@TableId
private Integer id;
@TableField("name")
@ExcelProperty(index = 0 , value = "姓名")
private String name;
@TableField("sex")
@ExcelProperty(index = 1 , value = "性别")
private Short sex;
@TableField("age")
@ExcelProperty(index = 2 , value = "年龄")
private Integer age;
@TableField("birthday")
@ExcelProperty(index = 3 , value = "出生日期")
private Date birthday;
@TableField("money")
@ExcelProperty(index = 4 , value = "薪资")
private double money;
}
2、编写工具类
//导出
public static <T> void easyExportWithModel(HttpServletResponse response, List<T> records, Class<T> classname, String fileName) throws IOException {
String ext = ExcelTypeEnum.XLSX.getValue();
response.setContentType("application/ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename="
.concat(String.valueOf(URLEncoder.encode(fileName + ext, "UTF-8"))));
try {
EasyExcel.write(response.getOutputStream(), classname).sheet("sheet1").doWrite(records);
} catch (IOException e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<String, String>(16);
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
3、编写controller
//导出
@PostMapping("export")
public Result export(@RequestBody PageDTO dto, HttpServletResponse response) throws IOException {
String fileName = "用户名单";
dto.setSize(3000);
List<User> userList = userService.list(dto).getRecords();
if(CollectionUtils.isEmpty(userList)){
return new Result("400",Message.EXPORT_FAIL);
}
EasyExcelUtil.easyExportWithModel(response, userList, User.class, fileName);
return new Result("200",Message.EXPORT_SUCCESS);
}
4、结果
存在问题：
- 出生日期列没有展示数据
- 性别没有从枚举类型转成男女
- id列不想展示出来却出现了
5、问题优化
1、出生日期列没有展示数据
原因：是因为列的宽度没处理好才导致数据没展示出来
//@ContentRowHeight(30) 表体行高
//@HeadRowHeight(20) 表头行高
@Data
@ColumnWidth(20) //将所有列的宽都定义为20，也可以加在某个字段上（将对应列的宽定义为20）
@TableName("t_user")
public class User {
@TableId
private Integer id;
@TableField("name")
@ExcelProperty(index = 0 , value = "姓名")
private String name;
@TableField("sex")
@ExcelProperty(index = 1 , value = "性别")
private Short sex;
@TableField("age")
@ExcelProperty(index = 2 , value = "年龄")
private Integer age;
@TableField("birthday")
@ExcelProperty(index = 3 , value = "出生日期")
private Date birthday;
@TableField("money")
@ExcelProperty(index = 4 , value = "薪资")
private double money;
}
2、性别没有从枚举类型转成男女
原因：需要自己去自定义一个转换器来将数字类型转为字符串类型，EasyExcel提供了两个注解@DateTimeFormat和@NumberFormat，自定义日期以及数字的格式化转化。
@TableField("birthday")
@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
@ExcelProperty(index = 3 , value = "出生日期")
private Date birthday;
@TableField("money")
@NumberFormat("#.##￥")
@ExcelProperty(index = 4 , value = "薪资")
private double money;
结果：
也可以自定义转换器
1、新建枚举类
@Getter
@AllArgsConstructor
public enum SexEnum {
MAN(1,"男生"),
WOMAN(2,"女生");
private final Integer number;
private final String description;
}
2、自定义转换器
//Converter<Short> 这里的Short是指，数据库存的字段是Short类型
public class SexEnumConvert implements Converter<Short> {
@Override
public Class<?> supportJavaTypeKey() {
return null;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return null;
}
//excel转java
@Override
public Short convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return SexEnum.MAN.getDescription().equals(cellData.getStringValue()) ? SexEnum.MAN.getNumber().shortValue() : SexEnum.WOMAN.getNumber().shortValue();
}
//java转excel
@Override
public WriteCellData<?> convertToExcelData(Short value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return new WriteCellData((value == SexEnum.MAN.getNumber().shortValue())? SexEnum.MAN.getDescription() : SexEnum.WOMAN.getDescription());
}
}
3、在对象的注解上添加转换
@TableField("sex")
@ExcelProperty(index = 1 , value = "性别" , converter = SexEnumConvert.class)
private Short sex;
4、结果
3、id列不想展示出来却出现了
原因：默认不加@ExcelProperty注解的都会参与读写，假如不想展示，则在对应的字段加上注解
@TableId
@ExcelIgnore
private Integer id;
或者是在对象上加个全局的注解
@Data
@ColumnWidth(30)
@ExcelIgnoreUnannotated
@TableName("t_user")
public class User {
@TableId
private Integer id;
@TableField("name")
@ExcelProperty(index = 0 , value = "姓名")
private String name;
@TableField("sex")
@ExcelProperty(index = 1 , value = "性别" , converter = SexEnumConvert.class)
private Short sex;
@TableField("age")
@ExcelProperty(index = 2 , value = "年龄")
private Integer age;
@TableField("birthday")
@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
@ExcelProperty(index = 3 , value = "出生日期")
private Date birthday;
@TableField("money")
@NumberFormat("#.##￥")
@ExcelProperty(index = 4 , value = "薪资")
private double money;
}
结果：
四、导出的高级用法
1、复杂头导出
//格式为
public class ComplexHeadData {
@ExcelProperty({"主标题", "字符串标题"})
private String string;
@ExcelProperty({"主标题", "日期标题"})
private Date date;
@ExcelProperty({"主标题", "数字标题"})
private Double doubleData;
}
@Data
@ColumnWidth(30)
@ExcelIgnoreUnannotated
@TableName("t_user")
public class User {
@TableId
private Integer id;
@TableField("name")
@ExcelProperty(index = 0 , value = {"基本信息","姓名"})
private String name;
@TableField("sex")
@ExcelProperty(index = 1 , value = {"基本信息","性别"} , converter = SexEnumConvert.class)
private Short sex;
@TableField("age")
@ExcelProperty(index = 2 , value = {"基本信息","年龄"})
private Integer age;
@TableField("birthday")
@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
@ExcelProperty(index = 3 , value = "出生日期")
private Date birthday;
@TableField("money")
@NumberFormat("#.##￥")
@ExcelProperty(index = 4 , value = "薪资")
private double money;
}
结果：
2、合并单元格导出
// 将第6-7行的2-3列合并成一个单元格
// @OnceAbsoluteMerge(firstRowIndex = 5, lastRowIndex = 6, firstColumnIndex = 1, lastColumnIndex = 2)
public class DemoMergeData {
// 这一列 每隔2行 合并单元格
@ContentLoopMerge(eachRow = 2)
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
}
五、导入
1、为导入对象添加注解
@Data
@TableName("t_user")
public class UserImport {
@TableId(type = IdType.AUTO)
private Integer id;
// 这里不建议 index 和 name 同时用，要么一个对象只用index，要么一个对象只用name去匹配
// 用名字去匹配，这里需要注意，如果名字重复，会导致只有一个字段读取到数据
@TableField("name")
@ExcelProperty(index = 0)
private String name;
@TableField("sex")
@ExcelProperty(index = 1 , converter = SexEnumConvert.class)
private Short sex;
@TableField("age")
@ExcelProperty(index = 2)
private Integer age;
@TableField("birthday")
@ExcelProperty(index = 3)
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
private Date birthday;
@TableField("money")
@NumberFormat("#.##")
@ExcelProperty(index = 4)
private double money;
}
2、编写工具类
public static <T> void importData(MultipartFile file, Class<T> cls, ReadListener listener, Boolean readAll) throws Exception {
if (file.isEmpty() && file.getSize() == 0) {
throw new ServiceException("导入的文件为空！");
}
// 文件名合法校验
FileUtils.assertAllowed(file, MimeTypeUtils.EXCEL_EXTENSION);
InputStream inputStream = file.getInputStream();
ExcelReader excelReader = null;
if (readAll) {
// 读取excel文件，全部sheet
// 这里需要注意listener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个listener里面写
EasyExcel.read(inputStream, cls, listener).doReadAll();
} else {
try {
// 读取部分sheet
excelReader = EasyExcel.read(inputStream).build();
// 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
ReadSheet readSheet1 =
EasyExcel.readSheet(0).head(cls).registerReadListener(listener).build();
ReadSheet readSheet2 =
EasyExcel.readSheet(1).head(cls).registerReadListener(listener).build();
// 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
excelReader.read(readSheet1, readSheet2);
} finally {
if (excelReader != null) {
// 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
excelReader.finish();
}
}
}
}
/**
* 默认大小 50M
*/
public static final long DEFAULT_MAX_SIZE = 30 * 1024 * 1024;
/**
* 默认的文件名最大长度 100
*/
public static final int FILE_NAME_MAX = 100;
//校验文件名合法性
public static final void assertAllowed(MultipartFile file, String[] allowedExtension)
throws Exception {
if (file.getOriginalFilename() != null) {
int fileNamelength = file.getOriginalFilename().length();
if (fileNamelength > FILE_NAME_MAX) {
throw new Exception("文件名过长");
}
}
long size = file.getSize();
if (size > DEFAULT_MAX_SIZE) {
throw new Exception("文件过大");
}
String extension = getExtension(file);
if (allowedExtension != null && !isAllowedExtension(extension, allowedExtension)) {
throw new Exception("请上传指定类型的文件！");
}
}
/**
* 判断MIME类型是否是允许的MIME类型
*
* @param extension
* @param allowedExtension
* @return
*/
public static final boolean isAllowedExtension(String extension, String[] allowedExtension) {
for (String str : allowedExtension) {
if (str.equalsIgnoreCase(extension)) {
return true;
}
}
return false;
}
/**
* 获取文件名的后缀
*
* @param file 表单文件
* @return 后缀名
*/
public static final String getExtension(MultipartFile file) {
String fileName = file.getOriginalFilename();
String extension = null;
if (fileName == null) {
return null;
} else {
int index = indexOfExtension(fileName);
extension = index == -1 ? "" : fileName.substring(index + 1);
}
if (StringUtils.isEmpty(extension)) {
extension = MimeTypeUtils.getExtension(file.getContentType());
}
return extension;
}
/**
* 媒体类型工具类
*
* @author ruoyi
*/
public class MimeTypeUtils
{
public static final String IMAGE_PNG = "image/png";
public static final String IMAGE_JPG = "image/jpg";
public static final String IMAGE_JPEG = "image/jpeg";
public static final String IMAGE_BMP = "image/bmp";
public static final String IMAGE_GIF = "image/gif";
public static final String[] IMAGE_EXTENSION = { "bmp", "gif", "jpg", "jpeg", "png" };
public static final String[] FLASH_EXTENSION = { "swf", "flv" };
public static final String[] MEDIA_EXTENSION = { "swf", "flv", "mp3", "wav", "wma", "wmv", "mid", "avi", "mpg",
"asf", "rm", "rmvb" };
public static final String[] VIDEO_EXTENSION = { "mp4", "avi", "rmvb" };
public static final String[] DEFAULT_ALLOWED_EXTENSION = {
// 图片
"bmp", "gif", "jpg", "jpeg", "png",
// word excel powerpoint
"doc", "docx", "xls", "xlsx", "ppt", "pptx", "html", "htm", "txt",
// 压缩文件
"rar", "zip", "gz", "bz2",
// 视频格式
"mp4", "avi", "rmvb",
// pdf
"pdf" };
public static final String[] EXCEL_EXTENSION = { "xls", "xlsx" };
public static final String[] DEFECT_CENTER_EXTENSION = { "xls", "xlsx", "pdf", "doc", "docx" };
public static String getExtension(String prefix)
{
switch (prefix)
{
case IMAGE_PNG:
return "png";
case IMAGE_JPG:
return "jpg";
case IMAGE_JPEG:
return "jpeg";
case IMAGE_BMP:
return "bmp";
case IMAGE_GIF:
return "gif";
default:
return "";
}
}
}
3、编写监听器
//有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
@Slf4j
@Component
public class UserListener extends AnalysisEventListener<UserImport> {
private static UserImportService userImportService;
@Autowired
public void setUserImportService(UserImportService userImportService) {
UserListener.userImportService = userImportService;
}
/**
* 每隔3000条存储数据库，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 3000;
private List<UserImport> userImportList = new ArrayList<>();
//这个每一条数据解析都会来调用
@Override
@Transactional(rollbackFor = Exception.class)
public void invoke(UserImport userImport, AnalysisContext analysisContext) {
//判断一条数据是否为空，为空则停止
if(isLineNullValue(userImport)){
return;
}
//这里就可以进行每一条数据的单元格数据校验
//这里可以写一些逻辑复杂的校验
log.info("解析到一条数据:{}", JSON.toJSONString(userImport));
userImportList.add(userImport);
if (userImportList.size() >= BATCH_COUNT) {
saveUser();
userImportList.clear();
}
}
//所有数据解析完成了,都会来调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveUser();
log.info("所有数据解析完成！");
}
//数据转换异常时的处理
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
//异常属于excel数据转换的
if (exception instanceof ExcelDataConvertException) {
Integer columnIndex = ((ExcelDataConvertException) exception).getColumnIndex() + 1;
Integer rowIndex = ((ExcelDataConvertException) exception).getRowIndex() + 1;
String message = String.format("第%s行，第%s列数据格式有误，请核实", rowIndex, columnIndex);
throw new ServiceException(message, Message.EXPORT_FAIL_CODE);
} else if (exception instanceof RuntimeException) {
throw exception;
} else {
super.onException(exception, context);
}
}
/**
* 将数据存入数据库
*/
public void saveUser() {
log.info("{}条数据，开始存储数据库！", userImportList.size());
userImportService.saveBatch(userImportList,BATCH_COUNT);
log.info("存储数据库成功！");
}
/**
* 判断整行单元格数据是否均为空
*/
private boolean isLineNullValue(UserImport data) {
try {
List<Field> fields = Arrays.stream(data.getClass().getDeclaredFields())
.filter(f -> f.isAnnotationPresent(ExcelProperty.class))
.collect(Collectors.toList());
List<Boolean> lineNullList = new ArrayList<>(fields.size());
for (Field field : fields) {
field.setAccessible(true);
Object value = field.get(data);
if (Objects.isNull(value)) {
lineNullList.add(Boolean.TRUE);
} else {
lineNullList.add(Boolean.FALSE);
}
}
return lineNullList.stream().allMatch(Boolean.TRUE::equals);
} catch (Exception e) {
log.error("读取数据行[{}]解析失败: {}", data, e.getMessage());
}
return true;
}
}
4、编写controller
//导入
@PostMapping("import")
public Result importExcel(@RequestParam MultipartFile excelFile) throws Exception {
EasyExcelUtil.importData(excelFile, UserImport.class, new UserListener(), true);
return new Result("200",Message.IMPORT_SUCCESS);
}
5、结果
存在问题：
Excel中日期列假如为1998.12.1等不是日期类型的，会报错
6、问题优化
1、注解法
@TableField("birthday")
@ExcelProperty(index = 3)
//接收excel对应单元格为yyyy-MM-dd HH:mm:ss的字符串，不是这格式会报错
//若要接收1998.12.1格式，则
//@DateTimeFormat("yyyy.MM.dd")
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
private Date birthday;
2、创多一个实体与导出实体区分开
//实体（当数据库的字段定义为datetime类型，入库会自动帮你转），但出库的数据则会定死格式，不会帮你转成你想要的日期格式
@TableField("birthday")
@ExcelProperty(index = 3)
//若要接收1998.12.1格式，则
//@DateTimeFormat("yyyy.MM.dd")
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
private String birthday;
六、导入校验问题
//写个业务异常类
public final class ServiceException extends RuntimeException {
private static final long serialVersionUID = 1L;
/**
* 错误码
*/
private String code;
/**
* 错误提示
*/
private String message;
/**
* 错误明细，内部调试错误
* <p>
* 和 一致的设计
*/
private String detailMessage;
/**
* 空构造方法，避免反序列化问题
*/
public ServiceException() {
}
public ServiceException(String message) {
this.message = message;
}
public ServiceException(String message, String code) {
this.message = message;
this.code = code;
}
public ServiceException(String detailMessage,String message, String code) {
this.detailMessage = detailMessage;
this.message = message;
this.code = code;
}
public String getDetailMessage() {
return detailMessage;
}
public String getMessage() {
return message;
}
public String getCode() {
return code;
}
public ServiceException setMessage(String message) {
this.message = message;
return this;
}
public ServiceException setDetailMessage(String detailMessage) {
this.detailMessage = detailMessage;
return this;
}
}
1、日期检验
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelFormatValid {
//自己定义该注解下所接收的模板
String format() default "yyyy-MM-dd";
String message() default "时间填写不符合规范！";
}
2、字符串长度校验
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelStrValid {
//自己定义长度
int length() default 0;
String message() default "文字填写超出长度要求";
}
3、单元格必填校验
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelValid {
String message() default "导入有未填入的字段";
}
4、Decimal类型校验
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelDecimalValid {
String min();
String max();
String message() default "小数类型数字填写超出范围";
}
5、Int类型校验
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelIntValid {
int min();
int max();
String message() default "整数数字填写超出范围";
}
6、自己写个校验工具类
//校验excel每条数据
@Component
public class ExcelImportValidUtil {
/**
* Excel导入字段校验
* @param object 校验的JavaBean 其属性须有自定义注解
*/
public void valid(Object object, AnalysisContext analysisContext) {
Field[] fields = object.getClass().getDeclaredFields();
for (Field field : fields) {
//设置可访问
field.setAccessible(true);
//属性的值
Object fieldValue;
try {
fieldValue = field.get(object);
} catch (IllegalAccessException e) {
throw new ServiceException(String.format("第%s行编号为空，请核实", analysisContext.readRowHolder().getRowIndex() + 1) + " | " + field.getAnnotation(ExcelValid.class).message(), Message.IMPORT_FAIL_CODE);
}
//是否包含必填校验注解
boolean isExcelValid = field.isAnnotationPresent(ExcelValid.class);
if (isExcelValid && Objects.isNull(fieldValue)) {
throw new ServiceException(String.format("第%s行字段为空，请核实", analysisContext.readRowHolder().getRowIndex() + 1) + " | " + field.getAnnotation(ExcelValid.class).message(), Message.IMPORT_FAIL_CODE);
}
// 是否包含日期格式校验注解
boolean isExcelDateFormatValid = field.isAnnotationPresent(ExcelFormatValid.class);
if (isExcelDateFormatValid) { // 若输入的时间字段被excel自动格式化了
if(!(fieldValue instanceof Date)) {
throw new ServiceException(String.format("第%s行字段格式错误，请核实", analysisContext.readRowHolder().getRowIndex() + 1) + " | " + field.getAnnotation(ExcelFormatValid.class).message(), Message.IMPORT_FAIL_CODE);
} else { // 若没被excel转换，则传入的为String类型
String cellStr = fieldValue.toString();
String df = field.getAnnotation(ExcelFormatValid.class).format();
DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(df);
try {
dateTimeFormatter.parse(cellStr); // 如果报错则说明输入的时间格式有问题
}catch (Exception e) {
throw new ServiceException(String.format("第%s行字段格式错误，请核实", analysisContext.readRowHolder().getRowIndex() + 1) + " | " + field.getAnnotation(ExcelFormatValid.class).message(),Message.IMPORT_FAIL_CODE);
}
}
}
//是否包含字符串长度校验注解
boolean isExcelStrValid = field.isAnnotationPresent(ExcelStrValid.class);
if (isExcelStrValid) {
String cellStr = fieldValue.toString();
int length = field.getAnnotation(ExcelStrValid.class).length();
if (StringUtils.isNotBlank(cellStr) && cellStr.length() > length) {
throw new ServiceException(String.format("第%s行字段过长错误，请核实", analysisContext.readRowHolder().getRowIndex() + 1) + " | " + field.getAnnotation(ExcelStrValid.class).message(),Message.IMPORT_FAIL_CODE);
}
}
//是否包含int类型校验注解
boolean isExcelIntValid = field.isAnnotationPresent(ExcelIntValid.class);
if (isExcelIntValid) {
if (fieldValue instanceof Integer) {
int cellInt = Integer.parseInt(fieldValue.toString());
int min = field.getAnnotation(ExcelIntValid.class).min();
int max = field.getAnnotation(ExcelIntValid.class).max();
if (cellInt < min || cellInt > max) {
throw new ServiceException(String.format("第%s行字段过大，请核实", analysisContext.readRowHolder().getRowIndex() + 1) + " | " + field.getAnnotation(ExcelIntValid.class).message(),Message.IMPORT_FAIL_CODE);
}
}
}
//是否包含decimal类型注解
boolean isExcelDecimalValid = field.isAnnotationPresent(ExcelDecimalValid.class);
if (isExcelDecimalValid) {
if (isBigDecimal(fieldValue.toString())) {
BigDecimal cellDecimal = new BigDecimal(fieldValue.toString());
BigDecimal min = new BigDecimal(field.getAnnotation(ExcelDecimalValid.class).min());
BigDecimal max = new BigDecimal(field.getAnnotation(ExcelDecimalValid.class).max());
if (cellDecimal.compareTo(min) < 0 || cellDecimal.compareTo(max) > 0) {
throw new ServiceException(String.format("第%s行字段过大，请核实", analysisContext.readRowHolder().getRowIndex() + 1) + " | " + field.getAnnotation(ExcelDecimalValid.class).message(),Message.IMPORT_FAIL_CODE);
}
} else {
throw new ServiceException(String.format("第%s行字段类型错误，请核实", analysisContext.readRowHolder().getRowIndex() + 1) + " | " + "不是小数数字类型",Message.IMPORT_FAIL_CODE);
}
}
}
}
private static boolean isBigDecimal(String decimal) {
try {
BigDecimal bd = new BigDecimal(decimal);
return true;
} catch (NumberFormatException e) {
return false;
}
}
}
7、监听器补加校验
//有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
@Slf4j
@Component
public class UserListener extends AnalysisEventListener<UserImport> {
private static UserImportService userImportService;
private static ExcelImportValidUtil excelImportValidUtil;
@Autowired
public void setUserImportService(UserImportService userImportService) {
UserListener.userImportService = userImportService;
}
@Autowired
public void setExcelImportValidUtil(ExcelImportValidUtil excelImportValidUtil) {
UserListener.excelImportValidUtil = excelImportValidUtil;
}
/**
* 每隔3000条存储数据库，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 3000;
private List<UserImport> userImportList = new ArrayList<>();
//这个每一条数据解析都会来调用
@Override
@Transactional(rollbackFor = Exception.class)
public void invoke(UserImport userImport, AnalysisContext analysisContext) {
//判断一条数据是否为空，为空则停止
if(isLineNullValue(userImport)){
return;
}
excelImportValidUtil.valid(userImport,analysisContext);
//通常这里来写校验
//这里就可以进行每一条数据的单元格数据校验
log.info("解析到一条数据:{}", JSON.toJSONString(userImport));
userImportList.add(userImport);
if (userImportList.size() >= BATCH_COUNT) {
saveUser();
userImportList.clear();
}
}
//所有数据解析完成了,都会来调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveUser();
log.info("所有数据解析完成！");
}
//数据转换异常时的处理
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
//异常属于excel数据转换的
if (exception instanceof ExcelDataConvertException) {
Integer columnIndex = ((ExcelDataConvertException) exception).getColumnIndex() + 1;
Integer rowIndex = ((ExcelDataConvertException) exception).getRowIndex() + 1;
String message = String.format("第%s行，第%s列数据格式有误，请核实", rowIndex, columnIndex);
throw new ServiceException(message, Message.EXPORT_FAIL_CODE);
} else if (exception instanceof RuntimeException) {
throw exception;
} else {
super.onException(exception, context);
}
}
/**
* 将数据存入数据库
*/
public void saveUser() {
log.info("{}条数据，开始存储数据库！", userImportList.size());
userImportService.saveBatch(userImportList,BATCH_COUNT);
log.info("存储数据库成功！");
}
/**
* 判断整行单元格数据是否均为空
*/
private boolean isLineNullValue(UserImport data) {
try {
List<Field> fields = Arrays.stream(data.getClass().getDeclaredFields())
.filter(f -> f.isAnnotationPresent(ExcelProperty.class))
.collect(Collectors.toList());
List<Boolean> lineNullList = new ArrayList<>(fields.size());
for (Field field : fields) {
field.setAccessible(true);
Object value = field.get(data);
if (Objects.isNull(value)) {
lineNullList.add(Boolean.TRUE);
} else {
lineNullList.add(Boolean.FALSE);
}
}
return lineNullList.stream().allMatch(Boolean.TRUE::equals);
} catch (Exception e) {
log.error("读取数据行[{}]解析失败: {}", data, e.getMessage());
}
return true;
}
}
8、导入对象加上校验注解
@Data
@TableName("t_user")
public class UserImport {
@TableId(type = IdType.AUTO)
private Integer id;
// 这里不建议 index 和 name 同时用，要么一个对象只用index，要么一个对象只用name去匹配
// 用名字去匹配，这里需要注意，如果名字重复，会导致只有一个字段读取到数据
@TableField("name")
@ExcelProperty(index = 0)
private String name;
@ExcelValid
@TableField("sex")
@ExcelProperty(index = 1 , converter = SexEnumConvert.class)
private Short sex;
@ExcelValid
@TableField("age")
@ExcelProperty(index = 2)
private Integer age;
@ExcelValid
@TableField("birthday")
@ExcelProperty(index = 3)
@DateTimeFormat("yyyy-MM-dd")
private Date birthday;
@ExcelValid
@TableField("money")
@NumberFormat("#.##")
@ExcelProperty(index = 4)
private double money;
}
9、结果
异常捕捉成功！
浙公网安备 33010602011771号