EasyPOI复杂表格导入

多表头数据导入

方式一

导入的表格样式如下

导入表格实体类

package com.demo.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

import java.io.Serializable;

@Data
public class SerailNum implements Serializable {

    /**
     * 序号
     */
    @Excel(name = "序号")
    private String serialNum;
}

package com.demo.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

import java.io.Serializable;

@Data
public class BasicInfo implements Serializable {

    @Excel(name = "姓名")
    private String name;

    @Excel(name = "性别")
    private String gender;
}

package com.demo.entity;

import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import lombok.Data;

import java.io.Serializable;

@Data
public class UserBO implements Serializable {

    @ExcelEntity(name = "序号")
    private SerailNum serailNum;

    @ExcelEntity(name = "基本信息")
    private BasicInfo basicInfo;
}

文件导入代码

package com.easy.poi.demo.controller;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.hutool.core.collection.CollectionUtil;
import com.easy.poi.demo.entity.UserBO;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.Collections;
import java.util.List;

/**
 * 复杂表格导入
 */
@RestController
public class ExcelController {
    @PostMapping(value = "/excel/complex/import")
    public List<UserBO> excelComplexImport(@RequestParam("file") MultipartFile file) throws Exception {
        ImportParams params = new ImportParams();
        params.setTitleRows(1);//标题列占几行
        params.setHeadRows(1);//header列占几行
        // 字段真正值和列标题之间的距离 默认0行
        /**
         *表格真正有值的行数离表格的标题列还差一行，如果表格列下的第一行为表格的实际内容，则不需要
         *设置  params.setStartRows(1);
         */
        params.setStartRows(1);
        InputStream inputStream = file.getInputStream();
        List<UserBO> userBOList = ExcelImportUtil.importExcel(inputStream, UserBO.class, params);
        if (CollectionUtil.isEmpty(userBOList)) {
            return Collections.emptyList();
        }
        return userBOList;
    }
}

测试结果

方式二

导入表格实体类

package com.easy.poi.demo.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

import java.io.Serializable;

@Data
public class UserBO2 implements Serializable {
    @Excel(name = "序号")
    private String serialNum;

    @Excel(name = "姓名")
    private String name;

    @Excel(name = "性别")
    private String gender;
}

文件导入代码

package com.easy.poi.demo.controller;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.hutool.core.collection.CollectionUtil;
import com.easy.poi.demo.entity.UserBO2;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.Collections;
import java.util.List;

@RestController
public class ExcelController2 {

    @PostMapping(value = "/excel/complex/import2")
    public List<UserBO2> excelComplexImport(@RequestParam("file") MultipartFile file) throws Exception {
        ImportParams params = new ImportParams();
        params.setTitleRows(1);//标题列占几行
        params.setHeadRows(1);//header列占几行
        // 字段真正值和列标题之间的距离 默认0行
        /**
         *表格真正有值的行数离表格的标题列还差一行，如果表格列下的第一行为表格的实际内容，则不需要
         *设置  params.setStartRows(1);
         */
        params.setStartRows(1);
        // 文件输入流读取数据
        InputStream inputStream = file.getInputStream();
        List<UserBO2> userBOList = ExcelImportUtil.importExcel(inputStream, UserBO2.class, params);
        if (CollectionUtil.isEmpty(userBOList)) {
            return Collections.emptyList();
        }
        return userBOList;
    }
}

测试结果

总结

方式一和方式二，不同之处在于导入表格实体的设计，方式一是通过@ExcelEntity来映射合并的表格列，方式二是直接通过 @Excel来映射要读取的表格列，然后在导入的时候，设置表格真正要读取的表格列和表格字段真正值从哪行读取

设置表格从哪行读取表格内容

通过ImportParams设置startRows

代码地址

https://gitee.com/BAIXUEQIAN/java-study/tree/develop/easypoi-demo