JAVA读取复杂表头Excel

EASYPOI一对多导入

EasyPoi官方文档：(http://easypoi.mydoc.io/)

前言

利用EasyPoi实现一对多的表格导入，主要注解@ExcelCollection

一、导入表格示例

二、使用步骤

1.POM依赖

springboot整合EasyPoi

 <dependency>
   <groupId>cn.afterturn</groupId>
   <artifactId>easypoi-base</artifactId>
   <version>4.1.0</version>
 </dependency>
 <dependency>
  <groupId>cn.afterturn</groupId>
  <artifactId>easypoi-web</artifactId>
  <version>4.1.0</version>
 </dependency>
 <dependency>
   <groupId>cn.afterturn</groupId>
   <artifactId>easypoi-annotation</artifactId>
   <version>4.1.0</version>
 </dependency>
 <dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-test</artifactId>
 </dependency>

2.导入对应的实体

@Excel：作用在字段上面，对应Excel的某一列的描述。

@ExcelCollection：集合，主要针对一对多的导出。

@Data
public class Test {

    @Excel(name = "审计准则")
    private String auditing;

    @Excel(name = "*被审计单位名称")
    private String auditedEntity;

    @ExcelCollection(name = "1.银行存款")
    private List<TestA> testAList;

}

@Data
public class TestA {
    
    @Excel(name = "*是否实施")
    private String implemented;
    
    @Excel(name = "账户名称")
    private String accountBalance;
}

TEST

public class Test {
    public static void main(String[] args) throws Exception {
        String fileName = "C:\\Users\\ext.manhengwei1\\Desktop\\格式一模版TEST.xlsx";
        MultipartFile multipartFile = filePath2MultipartFile(fileName);
        List<Test> functionList = EasyPoiExcelUtil.importExcel(multipartFile, 2, 1, Test.class);
        System.out.println(JSON.toJSONString(functionList));
    }
    private static MultipartFile filePath2MultipartFile(String fileName) throws IOException {
        File file = new File(fileName);
        FileInputStream input = new FileInputStream(file);
        return new MockMultipartFile(file.getName(),
                file.getName(), "text/plain", input);
    }
}

 校验excel字段 t为excel对象

public static <T> Response validateExcelFields(T t) {
    ValidatorFactory validatorFactory = Validation.buildDefaultValidatorFactory();
    Validator validator = validatorFactory.getValidator();
    Set<ConstraintViolation<T>> constraintViolations = validator.validate(t);
    if (!constraintViolations.isEmpty()) {
        // 处理校验失败的情况
        for (ConstraintViolation<T> violation : constraintViolations) {
            String field = violation.getPropertyPath().toString();
            String message = violation.getMessage();
            Object invalidValue = violation.getInvalidValue();
            // 处理校验失败的情况，例如打印日志或者返回错误信息等
            log.error(message);
            return Response.buildFailure("200021", message);
        }
    }
    return Response.buildSuccess();
}

EasyPoiExcelUtil工具类:

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.alibaba.fastjson.JSON;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.List;
import java.util.NoSuchElementException;

/**
 * @description: EasyPoiExcel工具类
 * @author: ext.manhengwei1
 * @create: 2022-09-15 14:41
 **/
@Component
public class EasyPoiExcelUtil {

    /**
     * 导入
     *
     * @param file       需要导入的文件
     * @param headerRows 头部行下标
     * @param titleRows  从第一行开始解析
     * @param pojoClass  转化为对应的实体类
     * @param <T>        Excel实体类
     * @return 返回解析后的实体类对象集合
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer headerRows, Integer titleRows, Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setHeadRows(headerRows);
        params.setTitleRows(titleRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            //throw new NotFoundException("excel文件不能为空");
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
            // throw new NotFoundException(e.getMessage());
        }
        return list;
    }

}