描述
项目因为要导入大量数据,要做一个导入功能,将数据从前台上传文件,后台读取数据,将数据导入数据库,文件需要一定格式,就再加了一个模板文件下载,经过大佬的指点后下面总结
页面显示
前端代码
指定导入的文件是csv文件
因为模板文件是放在服务器上的,下载这一块就给了一个链接打开
handleDownload() {
window.open("static/document/模板.csv");
}
下面讲导入,上代码
//本人项目要进行引用
import config from "@/config/index";
import VueCookies from "vue-cookies";
computed: {
uploadUrl() {
return config.baseUrl + "pool/upload";
},
uploadHeaders() {
return {
Authorization: `Bearer ${VueCookies.get("token")}`
};
}
}
<!-- 对文件进行验证-->
handleFormatError(file) {
this.$Notice.warning({
title: "文件格式不正确",
desc: "文件 " + file.name + " 格式不正确,请上传.csv文件。"
});
this.$refs.upload.clearFiles();
},
<!-- 文件上传成功导入后的操作-->
handleSuccess(res, file) {
if (res.success) {
this.$Message.success({ content: "数据导入成功!", duration: 3 });
} else {
this.$Message.error({ content: res.errorMsg, duration: 3 });
}
this.$refs.upload.clearFiles();
},
<!-- 文件导入失败后的操作-->
handleError(error, file) {
this.$Message.error({
content: "数据导入失败!",
duration: 3
});
this.$refs.upload.clearFiles();
}
后端代码
引入maven依赖
<dependency>
<groupId>net.sourceforge.javacsv</groupId>
<artifactId>javacsv</artifactId>
<version>2.0</version>
</dependency>
//Controller
@PostMapping("/upload")
public ResponseInfo upload(MultipartFile file) {
return ResponseInfo.success(service.upload(file));
}
导入这边做的是先把数据从文件读出来放到List
里面,只有全部读成功了才会把数据导入到数据库
//service
public boolean upload(MultipartFile file) {
List<Pool> poolList = new ArrayList<Pool>();
CsvReader reader;
//指定表头列名
String[] headerStr = { "名称", "所属地", "列名3", "列名4", "列名5", "列名6", "列名7", "列名8", "列名9", "列名10" };
try {
reader = new CsvReader(file.getInputStream(), Charset.forName("GBK"));
reader.readHeaders();
String[] hs = reader.getHeaders();
//文件表头与指定表头匹配
if (headerStr.length == hs.length) {
for (int i = 0; i < headerStr.length; i++) {
if (!headerStr[i].equals(hs[i])) {
throw new VerifyException("文件字段错误");
}
}
} else {
throw new VerifyException("文件列数有误");
}
while (reader.readRecord()) {
String name = reader.get(0);
if (name.isEmpty()) {
throw new VerifyException("名称列存在空缺");
}
String dep = reader.get(1);
Integer tempDep;
if (!dep.isEmpty()) {
DepartmentExample ex = new DepartmentExample();
ex.createCriteria().andNameEqualTo(reader.get(1));
if (!depMapper.selectByExample(ex).isEmpty()) {
tempDep = depMapper.selectByExample(ex).get(0).getId();
} else {
throw new VerifyException("系统不存在所属地" + reader.get(1));
}
} else {
throw new VerifyException("所属地列存在空缺");
}
Pool pool = new Pool();
pool.setName(name);
pool.setDepId(tempDep);
pool.setOwner(reader.get(2));
pool.setOwerTel(reader.get(3));
Double caArea = Double.valueOf(reader.get(4));
Double irArea = Double.valueOf(reader.get(5));
Integer affectVil = Integer.valueOf(reader.get(6));
Integer affectPop = Integer.valueOf(reader.get(7));
BigDecimal lon = new BigDecimal(reader.get(8));
BigDecimal lat = new BigDecimal(reader.get(9));
pool.setCatchmentArea(caArea);
pool.setIrrigatedArea(irArea);
pool.setAffectVillage(affectVil);
pool.setAffectPopulation(affectPop);
pool.setLongitude(lon);
pool.setLatitude(lat);
poolList.add(pool);
}
reader.close();
List<Pool> poolAll = mapper.selectByExample(new PoolExample());
if (!poolAll.isEmpty()) {
for (int i = 0; i < poolAll.size(); i++) {
mapper.deleteByPrimaryKey(poolAll.get(i).getId());
}
}
for (int i = 0; i < poolList.size(); i++) {
mapper.insertSelective(poolList.get(i));
}
} catch (NumberFormatException e) {
throw new VerifyException("文件数据填写有误或存在数据空缺");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return true;
}