SpringBoot+VUE 导入导出excel表格

背景需求：在后台管理系统中批量导入教师信息，如图：

下载的excel模板如图所示：

 前端代码：

<template>
  <div>
    <avue-form :option="excelOption" v-model="excelForm" :upload-after="uploadAfter" class="uploadFiles">
      <template slot="excelTemplate">
        <el-button type="primary" @click="handleTemplate">
          点击下载<i class="el-icon-download el-icon--right"></i>
        </el-button>
      </template>
    </avue-form>
    <el-row>
      <div style="font-size: 10px;color: #76797c;margin-left: 30px;">
        提示：导入的时候，默认跳过标题栏，所以不用手动去掉。手机号是唯一字段，如果重复导入会更新原来的数据。
        <p>如果导入数据包含系统未录入的院系、专业、班级，则会跳过</p>
      </div>
    </el-row>
  </div>
</template>

<script>
  import {mapGetters} from "vuex";
  import {getToken} from '@/util/auth';
  import {jdConst} from "@/api/jd/jd.js"

  export default {
    props: {
      tabType: {
        type: String,
        default: "",
      }
    },
    data() {
      return {
        loading: true,
        excelForm: {},
        excelOption: {
          submitBtn: false,
          emptyBtn: false,
          column: [
            {
              label: '模板上传',
              prop: 'excelFile',
              type: 'upload',
              drag: true,
              loadText: '模板上传中，请稍等',
              span: 24,
              propsHttp: {
                res: 'data'
              },
              tip: '请上传 .xls,.xlsx 标准格式文件；',
              action: jdConst.url + "/import_data/import-result?type=" + this.tabType
            },
            {
              label: '模板下载',
              prop: 'excelTemplate',
              formslot: true,
              span: 24,
            }
          ]
        },
      };
    },
    computed: {
      ...mapGetters(["permission"]),
      permissionList() {
        return {
          addBtn: false,
          viewBtn: false,
          delBtn: false,
          editBtn: false
        };
      },
    },
    watch: {
      "tabType"() {
        if (this.tabType !== '') {
          const column = this.findObject(this.excelOption.column, "excelFile");
          column.action = jdConst.url + `/import_data/import-result?type=${this.tabType}`;
        }
      }
    },
    methods: {

      // 导出模板
      handleTemplate() {
        if (this.tabType === '' || this.tabType == null) {
          return;
        }
        window.open(jdConst.url + `/import_data/export-template?${this.website.tokenHeader}=${getToken()}&type=${this.tabType}`);
      },

      // uploadAfter(res, done, loading, column) {
      //   window.console.log(column);
      //   done();
      // },

    }
  };
</script>

<style scoped lang="scss">
  .uploadFiles {
    /deep/ .el-upload-list {
      display: none;
    }
  }
</style>

Java代码：

首先在maven中导入excel包

<dependency>
    <groupId>org.springblade</groupId>
    <artifactId>blade-starter-excel</artifactId>
</dependency>

导出模板代码：

 excel模板类：

@Data
@ColumnWidth(25)
@HeadRowHeight(20)
@ContentRowHeight(18)
public class TeacherTemplate implements Serializable {

    private static final long serialVersionUID = 1L;

    @ColumnWidth(30)
    @ExcelProperty("院系")
    private String collegeName;

    @ColumnWidth(30)
    @ExcelProperty("专业")
    private String majorName;

    @ColumnWidth(30)
    @ExcelProperty("教师名字")
    private String teacherName;

    @ColumnWidth(30)
    @ExcelProperty("手机号")
    private String mobile;

}

导入数据：

教师数据导入类：

@RequiredArgsConstructor
public class TeacherImporter implements ExcelImporter<TeacherTemplate> {

    private final ITeacherService teacherService;

    @Override
    public void save(List<TeacherTemplate> data) {
        teacherService.importResult(data);
    }
}

 将数据存入数据库：

    @Override
	public void importResult(List<TeacherTemplate> data) {
		data.forEach(teacherExcel -> {

			// 获取院系id
			Long collegeId = collegeService.getCollegeId(teacherExcel.getCollegeName());
			// 获取专业id
			Long majorId = majorService.getMajorId(teacherExcel.getMajorName());

			if (Func.isEmpty(collegeId)) {
				return;
			}
			if (Func.isEmpty(majorId)) {
				return;
			}

			// 判断某院系下是否存在该专业
			if (!majorService.isExitMajor(collegeId, majorId)) {
				return;
			}

			// 根据手机号码和教师姓名查询是否该教师存在
			Teacher teacher = this.getOne(Wrappers.<Teacher>lambdaQuery()
                .eq(Teacher::getMobile, teacherExcel.getMobile())
                .eq(Teacher::getTeacherName, teacherExcel.getTeacherName()));

			if (Func.isNotEmpty(teacher)) {// 如果教师存在，更新院系和专业
				teacher.setCollegeId(collegeId);
				teacher.setMajorId(majorId);
				this.updateById(teacher);// 更新数据
			} else {
				Teacher teacherNew = new Teacher();
				teacherNew.setCollegeId(collegeId);
				teacherNew.setMajorId(majorId);
				teacherNew.setTeacherName(teacherExcel.getTeacherName());
				teacherNew.setMobile(teacherExcel.getMobile());
				this.save(teacherNew);// 新增数据
			}

		});
	}

导入导出大概流程如上所述，完成！！！！！！

参考资料：

写excel · 语雀EasyExcel写Excel的示例https://www.yuque.com/easyexcel/doc/write