多表头数据导入
方式一
导入的表格样式如下
导入表格实体类
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