Spring Boot + Vue 实现Excel文件批量导入_vue excel 导入sql

Spring Boot + Vue 实现Excel文件批量导入

开发工具：IDEA 2020.3 + VScode + MySql 5.7

1、数据库设计

2、前端部分

导入按钮

      <el-col :span="1.5">
        <el-button
          type="info"
          icon="el-icon-upload2"
          size="mini"
          @click="handleImport"
          v-hasPermi="['wxapp:houseInfo:export']"
          >导入
          </el-button>
     </el-col>
1
2
3
4
5
6
7
8
9
10

导入Excel文件对话框

  <!-- 房屋信息导入对话框 -->
  <el-dialog
      :title="uploadExcel.title"
      :visible.sync="uploadExcel.open"
      width="400px"
      append-to-body
    >
      <el-upload
        ref="uploadExcel"
        :limit="4"
        accept=".xlsx, .xls"
        :headers="uploadExcel.headers"
        :action="
          uploadExcel.url + '?updateSupport=' + uploadExcel.updateSupport
        "
        :disabled="uploadExcel.isUploading"
        :on-progress="handleFileUploadProgress"
        :on-success="handleFileSuccess"
        :auto-upload="false"
        drag
      >
        <div class="el-upload__text">
          将文件拖到此处，或
          <em>点击上传</em>
        </div>
        <div class="el-upload__tip" slot="tip">
          <el-checkbox
            v-model="uploadExcel.updateSupport"
          />是否更新已经存在的房屋信息数据
          <el-link
            type="info"
            style="font-size: 12px; color: blue"
            @click="importTemplate"
            >下载模板</el-link
          >
        </div>
        <div class="el-upload__tip" style="color: red" slot="tip">
          步骤提示：1、下载模板；2、填好数据；3、上传excel文件
        </div>
        <div class="el-upload__tip" style="color: red" slot="tip">
          其他提示：每次只能上传一个excel文件！
        </div>
      </el-upload>

      <div slot="footer" class="dialog-footer">
        <el-button type="primary" @click="submitFileForm">确 定</el-button>
        <el-button @click="uploadExcel.open = false">取 消</el-button>
      </div>
    </el-dialog>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

在 methods: {…}中添加导入文件相关触发函数

 methods: {
    /** 导入按钮操作 */
    handleImport() {
      this.uploadExcel.title = "从业人员信息导入";
      this.uploadExcel.open = true;
    },
    /** 下载模板操作 */
    importTemplate() {
      importTemplate().then((response) => {
        this.download(response.msg);
      });
    },
    // 文件上传中处理
    handleFileUploadProgress(event, file, fileList) {
      this.uploadExcel.isUploading = true;
    },
    // 文件上传成功处理
    handleFileSuccess(response, file, fileList) {
      this.uploadExcel.open = false;
      this.uploadExcel.isUploading = false;
      this.$refs.uploadExcel.clearFiles();
      this.$alert(response.msg, "导入结果", { dangerouslyUseHTMLString: true });
      this.getList();
    },
    // 提交上传文件
    submitFileForm() {
      this.$refs.uploadExcel.submit();
    },
  }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

在export default{…}添加一些导入参数设置，url为后端处理接口，getToken要导入相关包

export default {
  name: "HouseInfo",
  data() {
    return {
      // 房间导入参数
      uploadExcel: {
        // 是否显示弹出层（房间信息导入）
        open: false,
        // 弹出层标题（房间信息导入）
        title: "",
        // 是否禁用上传
        isUploading: false,
        // 是否更新已经存在的房屋信息
        updateSupport: 0,
        // 设置上传的请求头部
        headers: { Authorization: "Bearer " + getToken() },
        // 上传的地址
        url: process.env.VUE_APP_BASE_API + "/wxapp/houseInfo/importData",
      },
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

import { 
  listHouseInfo,
  getHouseInfo, 
  delHouseInfo, 
  addHouseInfo, 
  updateHouseInfo, 
  exportHouseInfo ,
  importTemplate,
  } from "@/api/wxapp/houseInfo";

import { getToken } from "@/utils/auth";
1
2
3
4
5
6
7
8
9
10
11

3、后端部分

1. 实体类domain
   在每个实体类的字段上加上注解@Excel，此注解既可导入也可导出。

/** 房间编号 */
   @Excel(name = "房间编号")
   @TableId(type = IdType.UUID)
   private String fjid;

   /** 业主编号 */
   @Excel(name = "业主编号")
   private String yzid;

   /** 业主姓名 */
   //@Excel(name = "业主姓名")
   private String yzName;

   /** 租户编号 */
   @Excel(name = "租户编号")
   private String zhid;

   /** 房间状态 */
   @Excel(name = "房间状态")
   private Integer state;

   /** 建筑面积 */
   @Excel(name = "建筑面积")
   private Double area;

   /** 楼号 */
   @Excel(name = "楼号")
   private String floorid;

   /** 楼名 */
   //@Excel(name = "楼名")
   private String floorName;

   /** 层数 */
   @Excel(name = "层数")
   private Integer layers;

   /** 门牌号 */
   @Excel(name = "门牌号")
   private String housenumber;

   /** 楼名 */
   @Excel(name = "楼名")
   private String FloorName;

   /** 物业费余额 */
   @Excel(name = "物业费余额")
   private Double propertyBalance;

   /** 水费余额 */
   @Excel(name = "水费余额")
   private Double waterBalance;

   /** 电费余额 */
   @Excel(name = "电费余额")
   private Double electricBalance;

   /** 燃气费余额 */
   @Excel(name = "燃气费余额")
   private Double gasBalance;

   /** 供暖费余额 */
   @Excel(name = "供暖费余额")
   private Double heatingBalance;

   /** 删除 */
   @Excel(name = "删除")
   private Integer deleted;

   /** 创建时间 */
   @JsonFormat(pattern = "yyyy-MM-dd")
   @Excel(name = "创建时间", width = 30, dateFormat = "yyyy-MM-dd")
   private String createTime;

   /** 更新时间 */
   @JsonFormat(pattern = "yyyy-MM-dd")
   @Excel(name = "更新时间", width = 30, dateFormat = "yyyy-MM-dd")
   private String updateTime;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

2. 控制层Controller

/**
* 房间信息Controller
* 
* @author lee
* @date 2021-04-25
*/
@RestController
@RequestMapping("/wxapp/houseInfo")
public class HouseInfoController extends BaseController
{
   @Autowired
   private IHouseInfoService houseInfoService;

    /**
    * 导入房间信息
    * */
   @PostMapping("/importData")
   @ResponseBody
   public AjaxResult importData(MultipartFile file, boolean updateSupport) throws Exception
   {
       ExcelUtil<HouseInfo> util = new ExcelUtil<HouseInfo>(HouseInfo.class);
       List<HouseInfo> userList = util.importExcel(file.getInputStream());
       for (HouseInfo houseInfo : userList) {
           houseInfoService.addHouseInfoExcel(houseInfo);
           //return toAjax(houseInfoService.addHouseInfoExcel(houseInfo));
       }
       //String operName = ShiroUtils.getSysUser().getLoginName();
       //String message = houseInfoService.insertHouseInfo(userList);
       //return AjaxResult.success(message);
       return AjaxResult.success();
   }
 }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

3. service层

    /**
    * 批量导入房间信息
    *
    * @param houseInfo 房间信息
    * @return 结果
    */
   public int addHouseInfoExcel(HouseInfo houseInfo);
1
2
3
4
5
6
7

4. serviceImpl层

    /**
    * Excel导入房间信息
    *
    * @param houseInfo 房间信息
    * @return 结果
    */
/*    @Override
   public List<HouseInfo> addHouseInfoExcel(HouseInfo houseInfo)
   {
       return houseInfoMapper.addHouseInfoExcel(houseInfo);
   }*/
   @Override
   public int addHouseInfoExcel(HouseInfo houseInfo)
   {
       return houseInfoMapper.addHouseInfoExcel(houseInfo);
   }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

5. mapper

    /**
    * 批量导入房间信息
    *
    * @param houseInfo 房间信息
    * @return 结果
    */
   //public List<HouseInfo> addHouseInfoExcel(HouseInfo houseInfo);
   public int addHouseInfoExcel(HouseInfo houseInfo);
1
2
3
4
5
6
7
8

6. mapper.xml

    <!-- excel导入 -->
   <insert id="addHouseInfoExcel" parameterType="HouseInfo" >
       INSERT INTO db_house_info
           (FJID, YZID, ZHID, state, area, floorId, layers, houseNumber, property_balance, water_balance, electric_balance, gas_balance, heating_balance, deleted, create_time, update_time)
       VALUES
           (#{fjid},#{yzid},#{zhid},#{state},#{area},#{floorid},#{layers},#{housenumber},#{propertyBalance},#{waterBalance},#{electricBalance},#{gasBalance},#{heatingBalance},#{deleted},#{createTime},#{updateTime})
   </insert>
1
2
3
4
5
6
7

4、结果演示

点击导入，选择导入的excel文件，点击确定即可导入！

结果提示：