给既有的用户表添加籍贯字段并导出

Element-UI 级联选择器的使用

需求：给既有的用户表sys_user添加province（籍贯）字段并导出excel表格

添加籍贯字段并将其标记为需要导出的字段
SysUser.java

/** 籍贯 */
@Excel(name = "籍贯", type = Type.EXPORT)
private String province;

更新的sql语句
SysUserMapper.xml

<update id="updateUser" parameterType="SysUser">
 		update sys_user
 		<set>
 			<if test="deptId != null and deptId != 0">dept_id = #{deptId},</if>
 			<if test="userName != null and userName != ''">user_name = #{userName},</if>
 			<if test="nickName != null and nickName != ''">nick_name = #{nickName},</if>
 			<if test="email != null ">email = #{email},</if>
 			<if test="phonenumber != null ">phonenumber = #{phonenumber},</if>
 			<if test="sex != null and sex != ''">sex = #{sex},</if>
 			<if test="avatar != null and avatar != ''">avatar = #{avatar},</if>
 			<if test="password != null and password != ''">password = #{password},</if>
 			<if test="status != null and status != ''">status = #{status},</if>
 			<if test="loginIp != null and loginIp != ''">login_ip = #{loginIp},</if>
 			<if test="loginDate != null">login_date = #{loginDate},</if>
 			<if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>
 			<if test="remark != null">remark = #{remark},</if>
			<if test="province != null">province = #{province},</if>
 			update_time = sysdate()
 		</set>
 		where user_id = #{userId}
	</update>

SysProfileController.java

@RestController
@RequestMapping("/system/user/profile")
public class SysProfileController extends BaseController
{
    @Autowired
    private ISysUserService userService;

    @Autowired
    private TokenService tokenService;

    /**
     * 个人信息
     */
    @GetMapping
    public AjaxResult profile()
    {
        LoginUser loginUser = getLoginUser();
        SysUser user = loginUser.getUser();
        AjaxResult ajax = AjaxResult.success(user);
        ajax.put("roleGroup", userService.selectUserRoleGroup(loginUser.getUsername()));
        ajax.put("postGroup", userService.selectUserPostGroup(loginUser.getUsername()));
        return ajax;
    }

    /**
     * 修改用户
     */
    @Log(title = "个人信息", businessType = BusinessType.UPDATE)
    @PutMapping
    public AjaxResult updateProfile(@RequestBody SysUser user)
    {
        LoginUser loginUser = getLoginUser();
        SysUser currentUser = loginUser.getUser();
        currentUser.setNickName(user.getNickName());
        currentUser.setEmail(user.getEmail());
        currentUser.setPhonenumber(user.getPhonenumber());
        currentUser.setSex(user.getSex());
        // 籍贯
        currentUser.setProvince(user.getProvince());

        if (StringUtils.isNotEmpty(user.getPhonenumber()) && !userService.checkPhoneUnique(currentUser))
        {
            return error("修改用户'" + loginUser.getUsername() + "'失败，手机号码已存在");
        }
        if (StringUtils.isNotEmpty(user.getEmail()) && !userService.checkEmailUnique(currentUser))
        {
            return error("修改用户'" + loginUser.getUsername() + "'失败，邮箱账号已存在");
        }
        if (userService.updateUserProfile(currentUser) > 0)
        {
            // 更新缓存用户信息
            tokenService.setLoginUser(loginUser);
            return success();
        }
        return error("修改个人信息异常，请联系管理员");
    }

级联选择器
userInfo.vue

<template>
  <el-form ref="form" :model="form" :rules="rules" label-width="80px">
    <el-form-item label="用户昵称" prop="nickName">
      <el-input v-model="form.nickName" maxlength="30" />
    </el-form-item>
    <el-form-item label="手机号码" prop="phonenumber">
      <el-input v-model="form.phonenumber" maxlength="11" />
    </el-form-item>
    <el-form-item label="邮箱" prop="email">
      <el-input v-model="form.email" maxlength="50" />
    </el-form-item>
    <el-form-item label="性别">
      <el-radio-group v-model="form.sex">
        <el-radio label="0">男</el-radio>
        <el-radio label="1">女</el-radio>
      </el-radio-group>
    </el-form-item>
    
    <el-form-item label="籍贯" prop="province">
      <el-cascader
        v-model="selectedOptions"
        :options="pcTextArr"
        :props="{ expandTrigger: 'hover' }"                                    //鼠标悬停自动展开
        @change="handleAreaChange"
        ></el-cascader>
    </el-form-item>
    <el-form-item>
      <el-button type="primary" size="mini" @click="submit">保存</el-button>
      <el-button type="danger" size="mini" @click="close">关闭</el-button>
    </el-form-item>
  </el-form>
</template>

<script>
  import { updateUserProfile } from "@/api/system/user";
  **import { pcTextArr } from 'element-china-area-data';**            //调用国省市区数据插件以获得数据

  export default {
    props: {
      user: {
        type: Object
      }
    },
    data() {
      return {
        form: {},
        // 表单校验
        rules: {
          nickName: [
            { required: true, message: "用户昵称不能为空", trigger: "blur" }
          ],
          email: [
            { required: true, message: "邮箱地址不能为空", trigger: "blur" },
            {
              type: "email",
              message: "请输入正确的邮箱地址",
              trigger: ["blur", "change"]
            }
          ],
          phonenumber: [
            { required: true, message: "手机号码不能为空", trigger: "blur" },
            {
              pattern: /^1[3|4|5|6|7|8|9][0-9]\d{8}$/,
              message: "请输入正确的手机号码",
              trigger: "blur"
            }
          ]
        },

        **selectedOptions: [],**                                          **// 用于存储用户选择的级联选项**
        **pcTextArr,**                                                         **// 直接使用导入的省市区数据纯汉字**
      };
    },
    watch: {
      user: {
        handler(user) {
          if (user) {
            this.form = { nickName: user.nickName, phonenumber: user.phonenumber, email: user.email, sex: user.sex };
          }
        },
        immediate: true
      }
    },
    methods: {
      handleAreaChange(value) {
        // 将级联选择器的值同步到form.province
        this.form.province = value.join(",");                    // 以,分隔省份、城市
        // console.log(value);
      },
      submit() {
        this.$refs["form"].validate(valid => {
          if (valid) {
            updateUserProfile(this.form).then(response => {
              this.$modal.msgSuccess("修改成功");
              this.user.phonenumber = this.form.phonenumber;
              this.user.email = this.form.email;
            }).catch(error => {
              this.$modal.msgError("修改失败：" + error.message);
            });
          }
        });
      },
      close() {
        this.$tab.closePage();
      }
    }
  };
</script>

修改用户籍贯

汉字存入数据库中

导出excel表格

表格中存在籍贯列