excel批量数据导入时用poi将数据转化成指定实体工具类

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 {@Overridepublic 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 {@Overridepublic Object evaluateDataByType(Cell dataCell, List<List<java.lang.String>> validateList) {double cellValue = dataCell.getNumericCellValue();return cellValue != 0 ? DateUtil.getJavaDate(cellValue) : null;}}, BigDecimal {@Overridepublic 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.获取要操作的sheetSheet 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 {@Autowiredprivate ResourceLoader resourceLoader;@Testvoid 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);}}

最终结果