最终效果
1、导出
1、在实体类上加注解
@Excel(name = “客户类型名称”)
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class UserType extends BaseEntity2 implements Serializable {@Excel(name = "客户类型ID", cellType = Excel.ColumnType.NUMERIC, prompt = "客户类型ID")private Long usertypeId; //客户类型ID@Excel(name = "客户类型名称")private String usertypeName ; //客户类型名称@Excel(name = "备注")private String usertypeComment; //备注@Transientprivate Date BeginCreateTime; //查询创建开始时间（数据库中不存在）@Transientprivate Date EndCreateTime; //查询创建结束时间（数据库中不存在）@Transientprivate Date BeginUpdateTime; //查询更新开始时间（数据库中不存在）@Transientprivate Date EndUpdateTime; //查询更新结束时间（数据库中不存在）
}
2、编写controller导出类 用你的实体类替换我的实体类就行
然后调用你的查询所有结果的方法
@PostMapping("/export")
public void export(HttpServletResponse response, UserType userType)
{List<UserType> list = userTypeService.findUserType(userType);ExcelUtil<UserType> util = new ExcelUtil<UserType>(UserType.class);util.exportExcel(response, list, "用户数据");
}
3、编写前端代码
<el-col :span="1.5"><el-buttontype="warning"plainicon="el-icon-download"size="mini"@click="handleExport"v-hasPermi="['yy:type:export']">导出</el-button></el-col>
在方法js中添加以下方法
/** 导出按钮操作 */handleExport() {this.download('/yy/userType/export', {...this.queryParams}, `type_${new Date().getTime()}.xlsx`)},
到此结束
2、导入
编写controller层 依然是替换
// @Log(title = "用户管理", businessType = BusinessType.IMPORT)@RequiresPermissions("yy:user:userType:import")@PostMapping("/importData")public AjaxResult importData(MultipartFile file, boolean updateSupport) throws Exception{ExcelUtil<UserType> util = new ExcelUtil<UserType>(UserType.class);List<UserType> userTypeList = util.importExcel(file.getInputStream());String operName = SecurityUtils.getUsername();String message = userTypeService.importUserType(userTypeList, updateSupport, operName);return success(message);}@PostMapping("/importTemplate")public void importTemplate(HttpServletResponse response) throws IOException{ExcelUtil<UserType> util = new ExcelUtil<UserType>(UserType.class);util.importTemplateExcel(response, "用户数据");}
编写ServiceImpl层
@Override
public String importUserType(List<UserType> userTypeList, Boolean isUpdateSupport, String operName){if (StringUtils.isNull(userTypeList) || userTypeList.size() == 0){throw new ServiceException("导入用户数据不能为空！");}int successNum = 0;int failureNum = 0;StringBuilder successMsg = new StringBuilder();StringBuilder failureMsg = new StringBuilder();for (UserType userType : userTypeList){try{// 验证是否存在这个用户 通过循环的userType查询到对应的用户UserType u = userTypeMapper.selectUserTypeByUsertypeName(userType.getUsertypeName());//如果为空，则说明这条数据不存在，可以存入数据库if (StringUtils.isNull(u)){BeanValidators.validateWithException(validator, userType);userType.setUsertypeId(userType.getUsertypeId());userType.setUsertypeName(userType.getUsertypeName());userType.setUsertypeComment(userType.getUsertypeComment());userType.setCreateTime(userType.getCreateTime());userType.setUpdateTime(userType.getUpdateTime());userType.setUpdateBy(userType.getUpdateBy());userType.setCreateBy(userType.getCreateBy());userTypeMapper.insertUserType(userType);successNum++;successMsg.append("<br/>" + successNum + "、用户类型 " + userType.getUsertypeName() + " 导入成功");}else if (isUpdateSupport){BeanValidators.validateWithException(validator, userType);userType.setUsertypeName(u.getUsertypeName());userType.setUsertypeComment(u.getUsertypeComment());userType.setUpdateBy(operName);userTypeMapper.updateUserType(userType);successNum++;successMsg.append("<br/>" + successNum +"、用户类型 " + userType.getUsertypeName()+ " 更新成功");}//已经存在就失败，存不进去，失败条数加1else{failureNum++;failureMsg.append("<br/>" + failureNum + "、用户类型 " + userType.getUsertypeName() + " 已存在");}}catch (Exception e){failureNum++;String msg = "<br/>" + failureNum +"、用户类型 " + userType.getUsertypeName() + " 导入失败：";failureMsg.append(msg + e.getMessage());log.error(msg, e);}}if (failureNum > 0){failureMsg.insert(0, "很抱歉，导入失败！共 " + failureNum + " 条数据格式不正确，错误如下：");throw new ServiceException(failureMsg.toString());}else{successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条，数据如下：");}return successMsg.toString();
}
前端：
添加导入按钮
<el-col :span="1.5"><el-buttontype="info"plainicon="el-icon-upload2"size="mini"@click="handleImport"v-hasPermi="['yy:type:import']">导入</el-button></el-col><el-col :span="1.5">
添加导入对话框
<!-- 用户导入对话框 --><el-dialog :title="upload.title" :visible.sync="upload.open" width="400px" append-to-body><el-uploadref="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>
在js中的data里添加
upload: {// 是否显示弹出层（用户导入）open: false,// 弹出层标题（用户导入）title: "",// 是否禁用上传isUploading: false,// 是否更新已经存在的用户数据updateSupport: 0,// 设置上传的请求头部headers: { Authorization: "Bearer " + getToken() },// 上传的地址url: process.env.VUE_APP_BASE_API + "/yy/userType/importData"},
导入按钮操作
/** 导入按钮操作 */handleImport() {this.upload.title = "用户导入";this.upload.open = true;},// /** 下载模板操作 */// importTemplate() {// this.download('/yy/userType/importTemplate', {// }, `user_template_${new Date().getTime()}.xlsx`)// },
其他按钮操作
handleFileUploadProgress(event, file, fileList) {this.upload.isUploading = true;},// 文件上传成功处理handleFileSuccess(response, file, fileList) {this.upload.open = false;this.upload.isUploading = false;this.$refs.upload.clearFiles();this.$alert("<div style='overflow: auto;overflow-x: hidden;max-height: 70vh;padding: 10px 20px 0;'>" + response.msg + "</div>", "导入结果", { dangerouslyUseHTMLString: true });this.getList();},// 提交上传文件submitFileForm() {this.$refs.upload.submit();},
