1.实现目标
excel进行批量数据导入时，将批量数据转化成指定的实体集合用于数据操作，实现思路：使用注解将属性与表格中的标题进行同名绑定来赋值。
2.代码实现
2.1 目录截图如下
2.2 代码实现
package poi.constants;
/**
* @description: 用来定义一些通用的常量数据
* @author: zengwenbo
* @date: 2024/3/10 13:08
*/
public class Constant {
public final static String POINT = ".";
public final static String SPACE = " ";
}
package poi.exception;
/**
* @description: 用于解析excel报错提供的异常
* @author: zengwenbo
* @date: 2024/3/10 12:47
*/
public class ExcelException extends RuntimeException {
public ExcelException() {
super();
}
public ExcelException(String message) {
super(message);
}
public ExcelException(String message, Throwable cause) {
super(message, cause);
}
}
package poi.annotation;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.springframework.util.StringUtils;
import java.lang.annotation.*;
import java.util.List;
import static poi.constants.Constant.SPACE;
/**
* @description: 用于绑定excel和实体字段属性的注解
* @author: zengwenbo
* @date: 2024/3/10 12:51
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelDescription {
String desc(); // excel描述的标题的内容
DataType type() default DataType.String; // 指定当前title数据的数据类型
enum DataType {
String {
@Override
public Object evaluateDataByType(Cell dataCell, List<List<java.lang.String>> validateList) {
String cellValue = dataCell.getStringCellValue();
if (StringUtils.hasLength(cellValue) && StringUtils.hasLength(cellValue.trim())) {
// 判断当前值是否在序列中，在的话用SPACE进行划分取前面的值，不在的话原值返回
return validateList.stream()
.filter(item -> item.contains(cellValue))
.map(item -> cellValue.split(SPACE)[0])
.findFirst()
.orElse(cellValue);
}
return cellValue;
}
}, Date {
@Override
public Object evaluateDataByType(Cell dataCell, List<List<java.lang.String>> validateList) {
double cellValue = dataCell.getNumericCellValue();
return cellValue != 0 ? DateUtil.getJavaDate(cellValue) : null;
}
}, BigDecimal {
@Override
public Object evaluateDataByType(Cell dataCell, List<List<java.lang.String>> validateList) {
return java.math.BigDecimal.valueOf(dataCell.getNumericCellValue());
}
};
/**
* 根据数据类型来获取excel的数据值
*
* @param dataCell excel单元格对象
* @param validateList excel当前的序列数据有效性
* @return
*/
public abstract Object evaluateDataByType(Cell dataCell, List<List<String>> validateList);
}
}
package poi.utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.StringUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import poi.annotation.ExcelDescription;
import poi.exception.ExcelException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.nio.file.OpenOption;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Optional;
import static poi.constants.Constant.POINT;
/**
* @description: 提供解析excel的工具类
* @author: zengwenbo
* @date: 2024/3/10 12:42
*/
public class ExcelUtil {
private final static String EXCEL_XLS = "xls";
private final static String EXCEL_XLSX = "xlsx";
/**
* 将excel中table里面的数据转换成对应的实体
*
* @param clazz 需要转化实体的类对象
* @param titleRowNum table表格的标题行
* @param sheetIndex sheet的下标
* @return 返回转换后对象的list集合
*/
public static <T> List<T> transTableToEntity(MultipartFile file, Class<T> clazz, int titleRowNum, int sheetIndex) {
// 1.获取文件的后缀
String filename = file.getOriginalFilename();
String suffix = filename.substring(filename.lastIndexOf(POINT) + 1);
// 2.根据获取的后缀名获取操作excel的对象
Workbook workbook;
try (InputStream inputStream = file.getInputStream()) {
switch (suffix) {
case EXCEL_XLS:
workbook = new HSSFWorkbook(inputStream);
break;
case EXCEL_XLSX:
workbook = new XSSFWorkbook(inputStream);
break;
default:
throw new ExcelException("后缀名不符");
}
} catch (IOException e) {
throw new ExcelException("文件解析失败", e);
}
// 3.获取要操作的sheet
Sheet sheet = workbook.getSheetAt(sheetIndex);
// 4.通过表格标题获取操作的开始列和结束列
Row titleRow = sheet.getRow(titleRowNum);
short firstCellNum = titleRow.getFirstCellNum();
short lastCellNum = titleRow.getLastCellNum();
// 5.获取表格中序列的数据有效性
List<List<String>> validateList = new ArrayList<>();
if (null != sheet.getDataValidations()) {
sheet.getDataValidations().forEach(item -> {
// 筛选出有效性数据时序列的进行添加
if (null != item.getValidationConstraint().getExplicitListValues()) {
validateList.add(Arrays.asList(item.getValidationConstraint().getExplicitListValues()));
}
});
}
// 6.遍历数据进行解析
ArrayList<T> list = new ArrayList<>();
Field[] fields = clazz.getDeclaredFields();
for (int i = titleRowNum + 1; i < sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
try {
// 获取实例对每个绑定的属性进行赋值
T t = clazz.newInstance();
for (int j = firstCellNum; j < lastCellNum; j++) {
Cell titleCell = titleRow.getCell(j);
if (null == titleCell) {
throw new ExcelException("标题缺失，请检查导入模板是否正常");
}
String title = titleCell.getStringCellValue();
if (!StringUtils.hasLength(title)) {
throw new ExcelException("标题内容为空，请检查导入模板是否正常");
}
Optional.ofNullable(row.getCell(j))
.ifPresent(value -> evaluateField(t, fields, value, title, validateList));
}
list.add(t);
} catch (InstantiationException e) {
throw new ExcelException("创建实例异常，该类缺失无参构造方法");
} catch (IllegalAccessException e) {
throw new ExcelException("创建实例异常，权限不足");
}
}
return list;
}
/**
* 通过单元格的值给对象的属性进行赋值
*
* @param t 对象实体
* @param fields 对象对应的属性数组
* @param dataCell 单元格对象
* @param title 单元格对象对应的title
* @param validateList 数据有效性列表
*/
private static <T> void evaluateField(T t, Field[] fields, Cell dataCell,
String title, List<List<String>> validateList) {
for (Field field : fields) {
// 处理属性上有ExcelDescription注解的数据进行赋值
if (field.isAnnotationPresent(ExcelDescription.class)) {
ExcelDescription annotation = field.getAnnotation(ExcelDescription.class);
// 获取注解的描述
String desc = annotation.desc();
// 获取注解的数据类型
ExcelDescription.DataType type = annotation.type();
// 如果title和描述desc一致，则将cell里面的值赋值给该属性
if (title.equals(desc)) {
// 获取value的值
Object value = type.evaluateDataByType(dataCell, validateList);
field.setAccessible(true);
try {
field.set(t, value);
} catch (IllegalAccessException e) {
throw new ExcelException("对象属性赋值权限异常");
}
}
}
}
}
}
3.测试数据
测试实体
package poi.bean;
import lombok.Data;
import poi.annotation.ExcelDescription;
import java.math.BigDecimal;
import java.util.Date;
/**
* @description:
* @author: zengwenbo
* @date: 2024/3/10 14:07
*/
@Data
public class Person {
@ExcelDescription(desc = "名称")
private String name;
@ExcelDescription(desc = "年龄", type = ExcelDescription.DataType.BigDecimal)
private BigDecimal age;
@ExcelDescription(desc = "生日", type = ExcelDescription.DataType.Date)
private Date birth;
@ExcelDescription(desc = "国籍")
private String country;
}
测试的excel文件数据截图
对国籍数据进行了有效性填充
测试代码：将excel文件放在resource目录下
package com.example.demo;
import com.example.demo.redis.User;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;
import poi.bean.Person;
import poi.utils.ExcelUtil;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.nio.file.Files;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
class DemoApplicationTests {
@Autowired
private ResourceLoader resourceLoader;
@Test
void TestExcel() throws Exception {
Resource resource = resourceLoader.getResource("classpath:test.xls" );
String fileName = resource.getFilename();
byte[] fileBytes = Files.readAllBytes(resource.getFile().toPath());
MultipartFile multipartFile = new MockMultipartFile(fileName, fileName, "text/plain", fileBytes);
List<Person> list = ExcelUtil.transTableToEntity(multipartFile, Person.class, 0, 0);
}
}
最终结果
本文含有隐藏内容，请 开通VIP 后查看