芋道----如何在自己的业务模块中添加导入导出功能

导出不再多做介绍，代码自动生成就会有，这里介绍一下如何实现导入：

一、首先介绍前台：

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 />&nbsp;&nbsp;&nbsp;&nbsp;" + schoolname;
      }
      text += "<br />更新成功数量：" + data.updateSchoolnames.length;
      for (const schoolname of data.updateSchoolnames) {
        text += "<br />&nbsp;&nbsp;&nbsp;&nbsp;" + schoolname;
      }
      text += "<br />更新失败数量：" + Object.keys(data.failureSchoolnames).length;
      for (const schoolname in data.failureSchoolnames) {
        text +=
          "<br />&nbsp;&nbsp;&nbsp;&nbsp;" +
          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;
    }