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 {
            @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);
    }

}

最终结果