SSM整合EasyExcel

1.导入依赖

<!-- Excel-->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>3.0.5</version>
</dependency>

2.Excel 上传工具类

public class ExcelListener extends AnalysisEventListener<User> {

    @Resource
    private UserMapper userMapper;
    @Resource
    private RoleMapper roleMapper;

    private static final int BATCH_COUNT = 5;
    List<User> list = new ArrayList<>();
    List<Integer> userIdList = new ArrayList<>();

    @Override
    public void invoke(User data, AnalysisContext analysisContext) {
        list.add(data);
    }

    private void saveData() {
        userMapper.insertMore(list);
        for (User user : list) {
            userIdList.add(user.getUserId());
        }
        roleMapper.insertRoleMore(userIdList);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
    }
}

3.Excel 下载案例实体类

@Data
public class UserExcel {
    // index 会影响excel数据排列顺序
    @ExcelProperty(value = "user_id",index = 0)
    private Integer userId;
    @ExcelProperty(value = "头像",index = 1)
    private String userImage;
    @ExcelProperty(value = "用户名",index = 2)
    private String username;
    @ExcelProperty(value = "密码",index = 3)
    private String password;
    @ExcelProperty(value = "年龄",index = 4)
    private Integer userAge;
    @ExcelProperty(value = "性别",index = 5)
    private String userGender;
    @ExcelProperty(value = "状态",index = 6)
    private Integer userState;
}

4.案例

User 类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private Integer userId;
    private String userImage;
    private String username;
    private String password;
    private Integer userAge;
    private String userGender;
    private Integer userState;
}

 Role 类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Role {
    private Integer roleId;
    private String roleName;
}

UserMapper 接口

@Insert("<script>INSERT INTO `user`(`username`,`password`,`user_age`,`user_gender`) VALUES " +
        "<foreach collection='list' separator=',' item='item'> " +
        "(#{item.username},#{item.password},#{item.userAge},#{item.userGender}) " +
        "</foreach> " +
        "</script>")
@Options(useGeneratedKeys = true,keyColumn = "user_id",keyProperty = "userId")
void insertMore(@Param("list") List<User> list);

RoleMapper 接口

@Insert("<script>INSERT INTO `user_role_rel`(`user_id`,`role_id`) VALUES " +
        "<foreach collection='list' separator=',' item='item'> " +
        "(#{item},2) " +
        "</foreach> " +
        "</script>")
void insertRoleMore(List<Integer> userIdList);

UserService 接口等省略

UserController

@GetMapping("/excelDownload")
public void download(HttpServletResponse response) {
    List<User> list = userService.selectList();
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    String fileName = null;
    try {
        fileName = URLEncoder.encode("userInfo", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), UserExcel.class).sheet("userInfo").doWrite(list);
    } catch (IOException e) {
        e.printStackTrace();
    }
}

@PostMapping("/excelUpload")
public ResponseMessage upload(MultipartFile file) {
    try {
        EasyExcel.read(file.getInputStream(),User.class,excelListener).sheet().doRead();
        return new ResponseMessage(200,"上传完成");
    } catch (IOException e) {
        return new ResponseMessage(500,"上传失败",e.getMessage());
    }
}

前端下载

<el-button type="info" @click="excelDownload" style="float:left;">导出数据</el-button>

excelDownload(){
  let _this = this;
  this.$axios({
    method: 'get',
    url: 'user/excelDownload',
    responseType: 'arraybuffer',
  }).then((rs) => {
    if (!rs) return alert('文件下载失败');
    let url = window.URL.createObjectURL(new Blob([rs.data], {type: '.xlsx'}));
    let a = document.createElement('a');
    a.style.display = 'none';
    a.href = url;
    a.setAttribute('download', `userInfo.xlsx`);
    document.body.appendChild(a);
    a.click();
    url = window.URL.revokeObjectURL(url);
    document.body.removeChild(a)
  }).catch((error) => {
    _this.$message(error);
  })
},

前端上传

<el-upload
    class="avatar-uploader"
    action
    :limit="1"
    :show-file-list="false"
    :http-request="uploadExcel"
    :on-success="handleAvatarSuccess">
    <el-button type="default" style="float:left;margin-left: 8px">上传数据</el-button>
</el-upload>

uploadExcel(param){
  let _this = this;
  const formData = new FormData() 
  formData.append('file', param.file) 
  this.$axios({
    method: 'post',
    url: 'user/excelUpload',
    headers:{
      'content-type': 'multipart/form-data'
    },
    data:formData
  }).then((rs) => {
    _this.$message(rs.data.msg);
    setTimeout(function () {
      _this.$router.go(0);
    }, 2000);
  }).catch((error) => {
    _this.$message(error);
  })
},
handleAvatarSuccess(rs, file, fileList){
  this.$message(rs.data.msg);
}