导出不再多做介绍,代码自动生成就会有,这里介绍一下如何实现导入:
一、首先介绍前台:
1)页面上添加导入按钮
<el-button
type="primary"
plain
icon="el-icon-download"
size="mini"
@click="handleImport"
>导入</el-button
>
2) hanleImport方法:
/** 导入按钮操作 */
handleImport() {
this.upload.title = "学校导入";
this.upload.open = true;
},
这里打开了一个对话框,让用户选择对应的文件上传,dialog内容如下:
<el-dialog
:title="upload.title"
:visible.sync="upload.open"
width="400px"
append-to-body
>
<el-upload
ref="upload"
:limit="1"
accept=".xlsx, .xls"
:headers="upload.headers"
:action="upload.url + '?updateSupport=' + upload.updateSupport"
:disabled="upload.isUploading"
:on-progress="handleFileUploadProgress"
:on-success="handleFileSuccess"
:auto-upload="false"
drag
>
<i class="el-icon-upload"></i>
<div class="el-upload__text">将文件拖到此处,或<em>点击上传</em></div>
<div class="el-upload__tip text-center" slot="tip">
<div class="el-upload__tip" slot="tip">
<el-checkbox v-model="upload.updateSupport" /> 是否更新已经存在的数据
</div>
<span>仅允许导入xls、xlsx格式文件。</span>
<el-link
type="primary"
:underline="false"
style="font-size: 12px; vertical-align: baseline"
@click="importTemplate"
>下载模板</el-link
>
</div>
</el-upload>
<div slot="footer" class="dialog-footer">
<el-button type="primary" @click="submitFileForm">确 定</el-button>
<el-button @click="upload.open = false">取 消</el-button>
</div>
</el-dialog>
3) data中的参数如下:
// 导入参数
upload: {
// 是否显示弹出层
open: false,
// 弹出层标题
title: "",
// 是否禁用上传
isUploading: false,
// 是否更新已经存在的用户数据
updateSupport: 0,
// 设置上传的请求头部
headers: getBaseHeader(),
// 上传的地址
url: process.env.VUE_APP_BASE_API + "/admin-api/oa/school/import",
},
这里的url直接会跳转到后台对应的controller,记得添加引用
import {getBaseHeader} from "@/utils/request";
4) 添加对应方法:
handleFileUploadProgress:
// 文件上传中处理
handleFileUploadProgress(event, file, fileList) {
this.upload.isUploading = true;
},
handleFileSuccess:
// 文件上传成功处理
handleFileSuccess(response, file, fileList) {
console.log("🚀 ~ handleFileSuccess ~ response:", response);
if (response.code !== 0) {
this.$modal.msgError(response.msg);
return;
}
this.upload.open = false;
this.upload.isUploading = false;
this.$refs.upload.clearFiles();
// 拼接提示语
let data = response.data;
let text = "创建成功数量:" + data.createSchoolnames.length;
for (const schoolname of data.createSchoolnames) {
text += "<br /> " + schoolname;
}
text += "<br />更新成功数量:" + data.updateSchoolnames.length;
for (const schoolname of data.updateSchoolnames) {
text += "<br /> " + schoolname;
}
text += "<br />更新失败数量:" + Object.keys(data.failureSchoolnames).length;
for (const schoolname in data.failureSchoolnames) {
text +=
"<br /> " +
schoolname +
":" +
data.failureSchoolnames[schoolname];
}
this.$alert(text, "导入结果", { dangerouslyUseHTMLString: true });
this.getList();
},
importTemplate: 需要调后台取模板
/** 下载模板操作 */
importTemplate() {
importTemplate().then((response) => {
this.$download.excel(response, "用户导入模板.xls");
});
},
最后是提交方法:
submitFileForm:
// 提交上传文件
submitFileForm() {
this.$refs.upload.submit();
},
5)js文件中添加:
// 下载用户导入模板
export function importTemplate() {
return request({
url: '/oa/school/get-import-template',
method: 'get',
responseType: 'blob'
})
}
el-upload参数说明
参数 | 说明 | 类型 | 可选值 | 默认值 |
---|---|---|---|---|
action | 必选参数,上传的地址 | string | - | - |
headers | 设置上传的请求头部 | object | - | - |
multiple | 是否支持多选文件 | boolean | - | - |
action | 接受上传的文件类型 | |||
disabled | disabled | boolean | false | |
on-progress | 文件上传时的钩子 | function(event, file, fileList) | ||
on-success | 文件上传成功时的钩子 | function(response, file, fileList) | ||
auto-upload | 是否在选取文件后立即进行上传 | boolean | true | |
drag | 是否启用拖拽上传 |
二、后台
1)创建实体类
新建两个类:SchoolImportExcelVO和SchoolImportRespVO
SchoolImportExcelVO: 用于导出模板,同时也是导入时需要将excel转成的实体类型
SchoolImportRespVO:用于在导入时,向前台返回导入的结果,具体内容如下:
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = false) // 设置 chain = false,避免导入有问题
public class SchoolImportExcelVO {
@ExcelProperty("学校名称")
private String schoolName;
@ExcelProperty("学校地址")
private String schoolAddress;
@ExcelProperty("学校联系方式")
private String schoolTel;
@ExcelProperty("项目名称")
private String projectName;
}
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Builder;
import lombok.Data;
import java.util.List;
import java.util.Map;
@Schema(description = "管理后台 - 学校导入 Response VO")
@Data
@Builder
public class SchoolImportRespVO {
@Schema(description = "创建成功的用户名数组", requiredMode = Schema.RequiredMode.REQUIRED)
private List<String> createSchoolnames;
@Schema(description = "更新成功的用户名数组", requiredMode = Schema.RequiredMode.REQUIRED)
private List<String> updateSchoolnames;
@Schema(description = "导入失败的用户集合,key 为用户名,value 为失败原因", requiredMode = Schema.RequiredMode.REQUIRED)
private Map<String, String> failureSchoolnames;
}
2)在对应的Controller中添加方法
@PostMapping("/import")
@Operation(summary = "导入学校")
@Parameters({
@Parameter(name = "file", description = "Excel 文件", required = true),
@Parameter(name = "updateSupport", description = "是否支持更新,默认为 false", example = "true")
})
// @PreAuthorize("@ss.hasPermission('system:user:import')")
public CommonResult<SchoolImportRespVO> importExcel(@RequestParam("file") MultipartFile file,
@RequestParam(value = "updateSupport", required = false, defaultValue = "false") Boolean updateSupport) throws Exception {
List<SchoolImportExcelVO> list = ExcelUtils.read(file, SchoolImportExcelVO.class);
return success(schoolService.importSchoolList(list, updateSupport));
}
@GetMapping("/get-import-template")
@Operation(summary = "获得导入学校模板")
public void importTemplate(HttpServletResponse response) throws IOException {
// 手动创建导出 demo
List<SchoolImportExcelVO> list = Arrays.asList();
// 输出
ExcelUtils.write(response, "学校导入模板.xls", "学校列表", SchoolImportExcelVO.class, list);
}
3) Service层
SchoolImportRespVO importSchoolList(List<SchoolImportExcelVO> list,boolean isUpdateSupport);
4)ServiceImpl层
@Override
@Transactional(rollbackFor = Exception.class) // 添加事务,异常则回滚所有导入
public SchoolImportRespVO importSchoolList(List<SchoolImportExcelVO> importSchools, boolean isUpdateSupport) {
if (CollUtil.isEmpty(importSchools)) {
throw exception(USER_IMPORT_LIST_IS_EMPTY);
}
SchoolImportRespVO respVO = SchoolImportRespVO.builder().createSchoolnames(new ArrayList<>())
.updateSchoolnames(new ArrayList<>())
.failureSchoolnames(new LinkedHashMap<>()).build();
importSchools.forEach(importSchool -> {
// 校验,判断是否有不符合的原因
// try {
// validateSchoolForCreateOrUpdate(null, importSchool.getSchoolName());
// } catch (ServiceException ex) {
// respVO.getFailureSchoolnames().put(importSchool.getSchoolName(), ex.getMessage());
// return;
// }
// 判断如果不存在,在进行插入
SchoolDO existSchool = schoolMapper.selectBySchoolname(importSchool.getSchoolName());
if (existSchool == null) {
schoolMapper.insert(BeanUtils.toBean(importSchool, SchoolDO.class));
respVO.getCreateSchoolnames().add(importSchool.getSchoolName());
return;
}
// 如果存在,判断是否允许更新
if (!isUpdateSupport) {
respVO.getFailureSchoolnames().put(importSchool.getSchoolName(), SCHOOL_EXISTS.getMsg());
return;
}
SchoolDO updateSchool = BeanUtils.toBean(importSchool, SchoolDO.class);
updateSchool.setId(existSchool.getId());
schoolMapper.updateById(updateSchool);
respVO.getUpdateSchoolnames().add(importSchool.getSchoolName());
});
return respVO;
}