- 遇到个需求需要将部分数据导入到数据库,调研已经将数据整理成excel表格了,于是果断选择从excel读取数据,处理数据后,然后入库。
- 在开源中国溜达了一番,找到个MyExcel工具,使用简单,功能强大。于是试着一顿操作。
步骤
- 引入依赖包
<dependency>
<groupId>com.github.liaochong</groupId>
<artifactId>myexcel</artifactId>
<version>3.4.2</version>
</dependency>
2.读取文件
File file = new File("C:\\Users\\86157\\Desktop\\Country Region.xlsx");
// (推荐)方式一:全部读取后处理,SAX模式,避免OOM,建议大量数据使用
List<ArtCrowdVo> result = SaxExcelReader.of(ArtCrowdVo.class).sheets("1","2").read(file);
/**
* 实现思路:先建立好关系再一次性全部入库
* 第一步先做父节点处理:ext1 = dataName,areas=0,dataValue = 330 + 10 ,parentid = 0,dictName = "海外区域"
*
* 第二步做子节点处理:1先找到父节点,2 建立关系:.parentid=dataValue. dataName英文 ext1中文 areas这里不变
* dataValue = 在区域的基础上继续加10吧
* dictName=“海外国家”
*/
List<ArtCrowdVo> parentList = result.stream().filter(i -> i.getExt1() == null).distinct().collect(Collectors.toList());
List<ArtCrowdVo> parentResult=new ArrayList<>();
HashMap<String, ArtCrowdVo> artCrowdVoHashMap = new HashMap<>(400);
int dataValue = 330;
for (ArtCrowdVo artCrowdVo : parentList){
ArtCrowdVo parentArtCrowdVo = new ArtCrowdVo(artCrowdVo.getDataName(),artCrowdVo.getDataName(),"0",String.valueOf(dataValue),"0","海外区域");
dataValue+=10;
parentResult.add(parentArtCrowdVo);
artCrowdVoHashMap.put(parentArtCrowdVo.getDataName(),parentArtCrowdVo);
}
//剩下的为子元素数据
result.removeAll(parentList);
List<ArtCrowdVo> childResult=new ArrayList<>();
for (ArtCrowdVo artCrowdVo : result){
String dataValue1 = artCrowdVoHashMap.get(artCrowdVo.getAreas()).getDataValue();
ArtCrowdVo childArtCrowdVo = new ArtCrowdVo(artCrowdVo.getDataName(), artCrowdVo.getExt1(), artCrowdVo.getAreas(), String.valueOf(dataValue), dataValue1, "国外国家");
childResult.add(childArtCrowdVo);
dataValue+=10;
}
parentResult.addAll(childResult);
parentResult.stream().forEach(i -> {
System.out.println(i.toString());
});
}
- po
@Data
public class ArtCrowdVo {
// Index represents column index, starting from 0
// Annotation free import is supported, i.e. it does not need to specify the column corresponding to the field @Excelcolumn, and will be imported in the default order of all fields
// Can be read according to the specified title
/**
* 国家英文
*/
@ExcelColumn(index = 0)
public String dataName;
/**
* 国家中文
*/
@ExcelColumn(index = 1)
public String ext1;
/**
* 国家所属区域
*/
@ExcelColumn(index = 2)
public String areas;
/**
* 字典码(自己设置)
*/
public String dataValue;
/**
* 父id 父码
*/
public String parentid;
/**
* dictName[海外国家,海外区域等等]
*/
public String dictName;
public String getDataName() {
return dataName;
}
public void setDataName(String dataName) {
this.dataName = dataName;
}
public String getExt1() {
return ext1;
}
public void setExt1(String ext1) {
this.ext1 = ext1;
}
public String getAreas() {
return areas;
}
public void setAreas(String areas) {
this.areas = areas;
}
public String getDataValue() {
return dataValue;
}
public void setDataValue(String dataValue) {
this.dataValue = dataValue;
}
public String getParentid() {
return parentid;
}
public void setParentid(String parentid) {
this.parentid = parentid;
}
public String getDictName() {
return dictName;
}
public void setDictName(String dictName) {
this.dictName = dictName;
}
public ArtCrowdVo() {
}
public ArtCrowdVo(String dataName, String ext1, String areas, String dataValue, String parentid, String dictName) {
this.dataName = dataName;
this.ext1 = ext1;
this.areas = areas;
this.dataValue = dataValue;
this.parentid = parentid;
this.dictName = dictName;
}
@Override
public String toString() {
return "ArtCrowdVo{" +
"dataName='" + dataName + '\'' +
", ext1='" + ext1 + '\'' +
", areas='" + areas + '\'' +
", dataValue='" + dataValue + '\'' +
", parentid='" + parentid + '\'' +
", dictName='" + dictName + '\'' +
'}';
}
}
- 处理结果
5.原始数据
分别处理两个不同的sheet