Java实现Excel文件中多张有关联的表导入数据库
一、功能展示
(1)Excel文件主表(AerosolItemsExcelDTO)
对应的数据库表(AerosolItems)
重点说明:此处‘气溶胶条目标识’对应数据库‘id’。这个标识用于将同一Excel文件中不同的表关联起来,并不写入数据库,数据库的id采用自增的。
(2)和主表相关联的表( AerosolDegraStruExcelDTO)
对应的数据库表(AerosolDegraStructure)
重点说明:‘气溶胶条目标识’ 即和主表的‘气溶胶条目标识’相对应,用于做关联并不存入数据库。 ‘itemid’是将主表的信息写入数据库后在数据库的id,这样通过‘itemid’就可以找到其关联的主表信息
最后效果
主表某条记录导入后自增产生一个id
与主表关联的表自增产生id,关联的主表id在itemId中
二、工具
1.使用了阿里巴巴的开源工具Easy Excel,官网链接
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel-core</artifactId>
<version>3.1.1</version>
</dependency>
2.对数据库的读写操作使用了Mybatis-plus框架
三、步骤与代码
- 创建Excel表的实体类ExcelDTO和数据库的实体类Entity。(因为数据库字段和Excel表字段不完全对应,所以分开创建两个不同的实体类)
//主表ExcelDTO
//@ExcelProperty(index = 0) 设置Excel字段序号和Excel文件对应
@Data
@ToString
@EqualsAndHashCode
public class AerosolItemsExcelDTO {
/**
* 编号(假)
*/
@ExcelProperty(index = 0)
private String fakeId;
/**
* 气溶胶三级类别
*/
@ExcelProperty(index = 1)
private String category;
/**
* 气溶胶成分
*/
@ExcelProperty(index = 2)
private String componentString;
/**
* 标签
*/
@ExcelProperty(index = 3)
private String label;
/**
* 浓度
*/
@ExcelProperty(index = 4)
private String concen;
............
}
//关联表ExcelDTO
@Data
@ToString
@EqualsAndHashCode
public class AerosolDegraStruExcelDTO {
/**
* 编号(假)
*/
@ExcelProperty(index = 0)
private String aerosolFaketId;
/**
* 菌群名称
*/
@ExcelProperty(index = 1)
private String name;
/**
* 浓度%
*/
@ExcelProperty(index = 2)
private String concen;
/**
* 百分比
*/
@ExcelProperty(index = 3)
private String percentage;
/**
* 优势种
*/
@ExcelProperty(index = 4)
private String advantage;
}
//主表数据库的实体类Entity
@Data
@TableName("aerosol_items")
@EqualsAndHashCode(callSuper = true)
@ApiModel(value = "气溶胶条目")
public class AerosolItems extends Model<AerosolItems> {
private static final long serialVersionUID = 1L;
/**
* 编号
*/
@TableId
@ApiModelProperty(value="编号")
private Integer id;
/**
* 气溶胶三级类别
*/
@ApiModelProperty(value="气溶胶三级类别")
private Integer l3cate;
/**
* 气溶胶成分
*/
@ApiModelProperty(value="气溶胶成分")
private Integer component;
/**
* 浓度
*/
@ApiModelProperty(value="浓度")
private String concen;
/**
* 气溶胶结构
*/
@ApiModelProperty(value="气溶胶结构")
private String structure;
/**
* 采样标签
*/
@ApiModelProperty(value="采样标签")
private String label;
/**
* 链接显示名
*/
@ApiModelProperty(value="链接显示名")
private String refText;
........
//关联表数据库Entity
@Data
@TableName("aerosol__degra_structure")
@EqualsAndHashCode(callSuper = true)
@ApiModel(value = "")
public class AerosolDegraStructure extends Model<AerosolDegraStructure> {
private static final long serialVersionUID = 1L;
/**
* 编号
*/
@TableId
@ApiModelProperty(value="编号")
private Integer id;
/**
* 气溶胶编号
*/
@ApiModelProperty(value="气溶胶编号")
private Integer itemid;
/**
* 菌群名称
*/
@ApiModelProperty(value="菌群名称")
private String name;
/**
* 浓度
*/
@ApiModelProperty(value="浓度")
private String concen;
/**
* 百分比
*/
@ApiModelProperty(value="百分比")
private String percentage;
/**
* 优势种
*/
@ApiModelProperty(value="优势种")
private String advantage;
}
- 将Excel文件中各个表的内容分别读取到各自的Excel表实体类中,最后用List存放多条记录
//excelFile对应excel文件,head(ExcelDto),sheet(Excel文件中表的顺序),headRowNumber(Excel表的第几行开始读数据)
List<AerosolItemsExcelDTO> aerosolItemsExcelList;
List<AerosolDegraStruExcelDTO> aerosolDegraStruExcelList;
try {
aerosolItemsExcelList = EasyExcel.read(excelFile).head(AerosolItemsExcelDTO.class)
.sheet(0).headRowNumber(1).doReadSync();
aerosolDegraStruExcelList = EasyExcel.read(excelFile).head(AerosolDegraStruExcelDTO.class)
.sheet(1).headRowNumber(1).doReadSync();
} catch (Exception e) {
throw new CheckedException("文件解析错误,请重新下载模板!");
}
- 创建Map,主表Map结构为Map<String,ExcelDTO>,关联表Map结构为<String,List
<
ExcelDTO>
>(因为主表的某一行数据,可能关联表有几行关联的数据,所以关联表用List。String为Excel文件中的关联标识)
//将List——>Map
Map<String, AerosolItemsExcelDTO> aerosolItemsMap;
Map<String, List<AerosolDegraStruExcelDTO>> aerosolDegraStruMap;
try {
aerosolItemsMap = aerosolItemsExcelList.stream()
.collect(Collectors.toMap(AerosolItemsExcelDTO::getFakeId, Function.identity()));
} catch (Exception e) {
throw new CheckedException("气溶胶条目标识不唯一,请重新修改!");
}
aerosolDegraStruMap = aerosolDegraStruExcelList.stream()
.collect(Collectors.groupingBy(AerosolDegraStruExcelDTO::getAerosolFaketId));
- 遍历主表Map,将ExcelDTO转换为Entity,存入数据库,存入数据库后得到其在数据id,然后根据fakeId找到和主表某一行记录关联的关联表记录。
for (Map.Entry<String, AerosolItemsExcelDTO> entry : aerosolItemsMap.entrySet()) {
//拿到Excel文件中的条目标识,用于找其关联的信息
String fakeId = entry.getKey();
AerosolItemsExcelDTO aerosolItemsExcelDTO = entry.getValue();
//创建数据库Entity
AerosolItems aerosolItems = new AerosolItems();
//数据拷贝
BeanUtil.copyProperties(aerosolItemsExcelDTO, aerosolItems);
...
//使用mybatis-plus 保存数据到数据库
aerosolItemsService.save(aerosolItems);
//返回在数据库的id,传入到第五步中
Integer id = aerosolItems.getId();
List<AerosolDegraStruExcelDTO> aerosolDegraStruDTOList = null;
//根据Map的键(fakeId)找关联的记录
if (ObjectUtils.isEmpty(aerosolDegraStruMap.get(fakeId))) {
aerosolDegraStruDTOList = new ArrayList<>();
} else {
//找到了
aerosolDegraStruDTOList = aerosolDegraStruMap.get(fakeId);
}
- 遍历找到的记录存入数据库
aerosolDegraStruDTOList.forEach(aerosolDegraStruExcelDTO -> {
AerosolDegraStructure aerosolDegraStructure = new AerosolDegraStructure();
BeanUtil.copyProperties(aerosolDegraStruExcelDTO, aerosolDegraStructure);
//第4步中的id
aerosolDegraStructure.setItemid(id);
aerosolDegraStructureList.add(aerosolDegraStructure);
aerosolDegraStructureService.saveBatch(aerosolDegraStructureList);
});