⭐⭐⭐------------------------------------------
在实际应用场景中，我们常常需要迁移应用到另一个环境中。「应用的导入导出」功能可以便捷实现应用的迁移与重塑，甚至可以选择不同菜单，数据模型，与审批流程，业务事件，选择性导入，更高效便捷完成应用的迁移。其次，在导入的应用中，选择所需导入的部分，可以全选，也可以选择部分导入。「部分导入」实现了模块迁移的效果，对于企业级应用迁移来说，不仅利用率高，适用性也极为普遍。有了「应用导入导出」功能，就可以轻松迁移各类应用。同时，在使用应用市场中的应用，与更新应用方面，都非常灵活易用。
下面来介绍，这个功能性是如何实现的。
------------------------------------------⭐⭐⭐
演示GIF
- 导入
- 导出
准备工作
- MYSQL数据表
CREATE TABLE `t_infantmessage` (
`infantmessageid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`zongnumber` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '总序号',
`number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '序号',
`infantname` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '幼儿姓名',
`nikename` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '昵称',
`sex` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '性别',
`age` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '年龄',
`dateof` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '出生日期',
`infantidnumber` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '幼儿身份证号',
`nation` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '民族',
`birthplace` text COLLATE utf8mb4_unicode_ci COMMENT '出生地',
`infantnative` text COLLATE utf8mb4_unicode_ci COMMENT '籍贯',
`infanthukou` text COLLATE utf8mb4_unicode_ci COMMENT '户口所在地',
`dwell` text COLLATE utf8mb4_unicode_ci COMMENT '实际居住地址',
`isalone` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '是否独生子女',
`ischild` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '是否留守儿童',
`isfarmer` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '父母是否务农',
`health` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '健康情况',
`name` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`relation` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`phone` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`fatherworkunit` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`bankcard` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`idnumber` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`currentdate` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '当前日期',
PRIMARY KEY (`infantmessageid`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci- pom.xml文件引入hutool的相关依赖
<!--hutool-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.20</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>- Result 统一结果返回包装类
package com.ywl.common;
import com.ywl.entity.Staff;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Map;
/**
*接口统一返回包装类
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Result<T> {
private String code;
private String msg;
private T data;
public static Result succeed(){
return new Result(Constants.CODE_200,"成功",null);
}
public static Result succeed(Object data){
return new Result(Constants.CODE_200,"成功",data);
}
public static Result succeed(String msg, Object data){
return new Result(Constants.CODE_200,msg,data);
}
public static Result succeed(String code, String msg,Object data) {
return new Result(Constants.CODE_200,msg,data);
}
public static Result error(String code, String msg,Object data){
return new Result(code,msg,null);
}
public static Result error(String code, String msg) {
return new Result(code,msg,msg);
}
}1、实体类
package com.ywl.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import org.springframework.format.annotation.DateTimeFormat;
import java.io.Serializable;
import java.util.Date;
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
@TableName("t_infantmessage")
public class InfantMessage implements Serializable {
@TableId(type = IdType.AUTO)
private transient Integer infantmessageid;
@ApiModelProperty(value = "总序号")
private String zongnumber;
@ApiModelProperty(value = "序号")
private String number;
@ApiModelProperty(value = "幼儿姓名")
private String infantname;
@ApiModelProperty(value = "昵称")
private String nikename;
@ApiModelProperty(value = "性别")
private String sex;
@ApiModelProperty(value = "年龄")
private String age;
@ApiModelProperty(value = "出生日期")
private String dateof;
@ApiModelProperty(value = "身份证号")
private String infantidnumber;
@ApiModelProperty(value = "民族")
private String nation;
@ApiModelProperty(value = "出生地")
private String birthplace;
@ApiModelProperty(value = "籍贯")
private String infantnative;
@ApiModelProperty(value = "户口所所在地")
private String infanthukou;
@ApiModelProperty(value = "实际居住地址(具体到门牌号)")
private String dwell;
@ApiModelProperty(value = "是否独生子女")
private String isalone;
@ApiModelProperty(value = "是否留守儿童")
private String ischild;
@ApiModelProperty(value = "父母是否务农")
private String isfarmer;
@ApiModelProperty(value = "健康情况")
private String health;
//关系
@ApiModelProperty(value = "姓名")
private String name;
@ApiModelProperty(value = "关系")
private String relation;
@ApiModelProperty(value = "手机号")
private String phone;
@ApiModelProperty(value = "工作单位")
private String fatherworkunit;
@ApiModelProperty(value = "银行卡")
private String bankcard;
@ApiModelProperty(value = "身份证号")
private String idnumber;
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(locale = "zh", timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
private transient Date currentdate;
}2、Dao层接口
package com.ywl.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ywl.entity.ClassRoom;
import com.ywl.entity.InfantMessage;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface InfantMessageMapper {
/**查询全部*/
List<InfantMessage> listAllInfantMessage();
}3、SQL语句的xml文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ywl.mapper.InfantMessageMapper">
<!-- 查出全部数据-->
<select id="listAllInfantMessage" resultType="com.ywl.entity.InfantMessage">
SELECT * FROM t_infantmessage
</select>
</mapper>
4、Service层接口
package com.ywl.service;
import com.ywl.common.Result;
import com.ywl.entity.InfantMessage;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public interface InfantMessageService {
/***
* 查询全部
* @return
*/
Result listInfantMessage();
}
5、ServiceImpl实现层
package com.ywl.service.impl;
import com.ywl.common.Constants;
import com.ywl.common.Result;
import com.ywl.entity.InfantMessage;
import com.ywl.mapper.InfantMessageMapper;
import com.ywl.service.InfantMessageService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class InfantMessageServiceImpl implements InfantMessageService {
@Autowired
private InfantMessageMapper infantMessageMapper;
/***
* 查询全部
* @return
*/
@Override
public Result listInfantMessage() {
//从数据库查出所有的数据
List<InfantMessage> list = infantMessageMapper.listAllInfantMessage();
return Result.succeed(Constants.CODE_200,"查出全部成功",list);
}6、Controller控制层
package com.ywl.controller;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.ywl.common.Result;
import com.ywl.entity.InfantMessage;
import com.ywl.mapper.InfantMessageMapper;
import com.ywl.service.InfantMessageService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("/infantMessage")
public class InfantMessageController {
@Autowired
private InfantMessageService infantMessageService;
/**
* 导出接口
*/
@GetMapping("/export")
public void export(HttpServletResponse response) throws Exception {
//从数据库查出所有的数据
List<InfantMessage> list = infantMessageMapper.listAllInfantMessage();
//在内存操作写到浏览器
ExcelWriter writer = ExcelUtil.getWriter(true);
writer.merge(list.size()+23," 年 月会员信息统计表");
//自定义标题别名
writer.addHeaderAlias("zongnumber", "总序号");
writer.addHeaderAlias("number", "序号");
writer.addHeaderAlias("infantname", "幼儿姓名");
writer.addHeaderAlias("nikename", "昵称");
writer.addHeaderAlias("sex", "性别");
writer.addHeaderAlias("age", "年龄");
writer.addHeaderAlias("dateof", "出生日期");
writer.addHeaderAlias("infantidnumber", "身份证号");
writer.addHeaderAlias("nation", "民族");
writer.addHeaderAlias("birthplace", "出生地");
writer.addHeaderAlias("infantnative", "籍贯");
writer.addHeaderAlias("infanthukou", "户口所所在地");
writer.addHeaderAlias("dwell", "实际居住地址(具体到门牌号)");
writer.addHeaderAlias("isalone", "是否独生子女");
writer.addHeaderAlias("ischild", "是否留守儿童");
writer.addHeaderAlias("isfarmer", "父母是否务农");
writer.addHeaderAlias("health", "健康情况");
//关系
writer.addHeaderAlias("name", "姓名");
writer.addHeaderAlias("relation", "关系");
writer.addHeaderAlias("phone", "手机号");
writer.addHeaderAlias("fatherworkunit", "工作单位");
writer.addHeaderAlias("bankcard", "银行卡");
writer.addHeaderAlias("idnumber", "身份证号");
//一次性写出list对象到Excel使用默认样式，强制输出标题
writer.write(list, true);
//设置浏览器响应的格式
response.setContentType("application/vnd.openxmlformats-officeedocument.sheet;charset=utf-8");
String fileName = URLEncoder.encode("ywlchild", "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
ServletOutputStream out = response.getOutputStream();
writer.flush(out, true);
out.close();
writer.close();
}
/**
* 导入接口
*/
@PostMapping("/import")
public Result imp(MultipartFile file) throws Exception {
InputStream inputStream = file.getInputStream();
ExcelReader reader = ExcelUtil.getReader(inputStream);
//直接读数据表的内容
List<List<Object>> list = reader.read(1);
//使用hutool里面的工具类
List<InfantMessage> infantMessages = CollUtil.newArrayList();
for (List<Object> row : list) {
InfantMessage infantMessage = new InfantMessage();
infantMessage.setZongnumber(row.get(0).toString());
infantMessage.setNumber(row.get(1).toString());
infantMessage.setInfantname(row.get(2).toString());
infantMessage.setNikename(row.get(3).toString());
infantMessage.setSex(row.get(4).toString());
infantMessage.setAge(row.get(5).toString());
infantMessage.setDateof(row.get(6).toString());
infantMessage.setInfantidnumber(row.get(7).toString());
infantMessage.setNation(row.get(8).toString());
infantMessage.setBirthplace(row.get(9).toString());
infantMessage.setInfantnative(row.get(10).toString());
infantMessage.setInfanthukou(row.get(11).toString());
infantMessage.setDwell(row.get(12).toString());
infantMessage.setIsalone(row.get(13).toString());
infantMessage.setIschild(row.get(14).toString());
infantMessage.setIsfarmer(row.get(15).toString());
infantMessage.setHealth(row.get(16).toString());
infantMessage.setName(row.get(17).toString());
infantMessage.setRelation(row.get(18).toString());
infantMessage.setPhone(row.get(19).toString());
infantMessage.setFatherworkunit(row.get(20).toString());
infantMessage.setBankcard(row.get(21).toString());
infantMessage.setIdnumber(row.get(22).toString());
infantMessages.add(infantMessage);
}
infantMessageService.saveBatch(infantMessages);
return Result.succeed(true);
}7、前端Vue
<template>
<!-- 导入文件按钮 后端端口路径：apiPath -->
<el-upload
:action="apiPath + 'infantMessage/import'"
:show-file-list="false"
:on-success="handleExcelImportSuccess"
accept=".xlsx"
style="display: inline-block"
>
<el-button type="primary">导入</el-button>
</el-upload>
<!--导出Excel-->
<el-button
type="success"
icon="el-icon-upload"
@click="infantMessageExport">
导出Excel
</el-button>
<!--列表-->
<el-table
:data="listChildMsgIn"
:header-cell-style="{ background: '#F0F9EB' }"
class="table-top"
highlight-current-row
v-loading="loading"
border
stripe
element-loading-text="拼命加载中"
>
<el-table-column align="center" type="selection"></el-table-column>
<el-table-column
fixed
prop="currentdate"
label="保存数据的当前时间"
></el-table-column>
<el-table-column prop="infantname" label="幼儿姓名" width="90">
</el-table-column>
<el-table-column prop="nikename" label="昵称" width="80">
</el-table-column>
<el-table-column prop="sex" label="性别" width="50"> </el-table-column>
<el-table-column prop="dateof" label="出生日期" width="150">
</el-table-column>
<el-table-column prop="age" label="年龄" width="70"> </el-table-column>
<el-table-column prop="nation" label="民族" width="70"> </el-table-column>
<el-table-column prop="birthplace" label="出生地"> </el-table-column>
<el-table-column prop="infantnative" label="籍贯"> </el-table-column>
<el-table-column fixed="right" label="操作" width="150">
</el-table-column>
</el-table>
</template>
<script>
export default {
data() {
return {
listChildMsgIn: [], //列表数据
loading: false, //是显示加载
};
},
mounted() {
this.listAllChildMsgIn();
},
methods: {
//导入
handleExcelImportSuccess() {
this.$message.success("导入成功");
this.listAllChildMsgIn();
},
//导出
infantMessageExport() {
//后端路径
window.open(this.apiPath + "infantMessage/export");
this.$message.success("导出成功！");
},
}