(九)ATP应用测试平台——使用EasyExcel实现excel导入、导出、多sheet填充、模板下载等功能案例实战

前言

Java开发中实现Excel的导入、导出、填充、多sheet页操作等常用功能也是我们经常要面对的开发需求，本文以easyexcel为例，将excel中的常用功能整理成一个个小案例，参考使用。案例源码地址：https://gitee.com/northcangap/atp。喜欢的朋友可以star一下哦，创作不易。效果如下：

  

正文

• excel模板下载

①引入easyexcel的pom

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>2.2.10</version>
</dependency>

②前端代码

<template>
  <div class="container">
    <div class="title">
      <span>EasyExcel示例</span>
      <el-divider direction="vertical"></el-divider>
      <router-link to="home">
        <span style="font-size: 18px;">退出</span>
      </router-link>
    </div>
    <el-divider>Test Staring</el-divider>
    <el-form :inline="true" :model="query">
      <el-form-item>
        <el-button type="primary" @click="importExcel">导入</el-button>
      </el-form-item>
      <el-form-item>
        <el-button type="success" @click="exportExcel">导出</el-button>
      </el-form-item>
    </el-form>
    <el-table
        :data="data"
        border
        stripe
        v-loading="loading"
        element-loading-text="数据加载中...">
      <el-table-column
          prop="id"
          label="ID">
      </el-table-column>
      <el-table-column
          prop="name"
          label="用户名">
      </el-table-column>
      <el-table-column
          prop="pass"
          label="密码">
      </el-table-column>
      <el-table-column label="操作" align="left">
        <template slot-scope="scope">
          <el-button type="text" @click="fillExcel(scope.row)">填充</el-button>
        </template>
      </el-table-column>
    </el-table>
    <el-pagination layout="total,sizes,prev,pager,next,jumper"
                   @size-change="handlerSizeChange"
                   @current-change="handlerCurrentChange"
                   :current-page="query.page.current"
                   :page-sizes="query.page.sizes"
                   :page-size="query.page.size"
                   :total="query.page.total"
                   class="page"
                   background>
    </el-pagination>
    <el-dialog
        width="30%"
        title="导入"
        :visible.sync="dialogVisible">
      <div style="text-align: center;">
        <el-upload
            ref="upload"
            class="upload-demo"
            drag
            :on-success="handlerOnSuccess"
            action="/sys/user/import">
          <i class="el-icon-upload"></i>
          <div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
          <div class="el-upload__tip" slot="tip">只能上传jpg/png文件，且不超过500kb</div>
          <div class="el-upload__tip" slot="tip" style="color: royalblue;cursor: pointer;"><span @click="downloadModel">下载模板</span>
          </div>
        </el-upload>
      </div>
    </el-dialog>
  </div>
</template>

<script>
export default {
  name: "EasyExcel",
  data() {
    return {
      data: [],
      loading: false,
      query: {
        page: {
          total: 0,
          current: 1,
          size: 10,
          pageSizes: [10, 50, 100, 500],
        }
      },
      form: {},
      show: false,
      title: '',
      rules: {
        name: [
          {required: true, message: '用户名称不得为空！', trigger: 'blur'},
          {min: 6, max: 18, message: '长度在 6 到 18 个字符', trigger: 'blur'}
        ],
        pass: [
          {required: true, message: '密码不得为空！', trigger: 'blur'},
          {min: 6, max: 18, message: '长度在 6 到 18 个字符', trigger: 'blur'}
        ],
      },
      dialogVisible: false,
    }
  },
  created() {
    this.listPage();
  },
  methods: {
    listPage() {
      this.$http.post('/sys/user/listPage', this.query).then(res => {
        if (res.data.code === 1) {
          this.data = res.data.data.records;
          this.query.page.total = res.data.data.total;
        } else {
          this.$message.warning(res.data.msg);
        }
      }).catch(error => {
        this.$message.error(error);
      });
    },
    handlerSizeChange(data) {
      this.query.page.size = data;
      this.listPage();
    },
    handlerCurrentChange(data) {
      this.query.page.current = data;
      this.listPage();
    },
    importExcel() {
      this.dialogVisible = true;
    },
    exportExcel() {
      this.$http.get('/sys/user/export',{responseType: "blob",}).then(res => {
        const url = window.URL.createObjectURL(new Blob([res.data], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8'}));
        const link = document.createElement('a');
        link.href = url;
        link.setAttribute('download', '用户信息.xlsx') // 下载文件的名称及文件类型后缀
        document.body.appendChild(link)
        link.click();
        document.body.removeChild(link); // 下载完成移除元素
        window.URL.revokeObjectURL(url); // 释放掉blob对象
      }).catch(error => {
        this.$message.error(error);
      });
    },
    fillExcel(data) {
      this.$http.get('/sys/user/fill?id='+data.id,{responseType: "blob",}).then(res => {
        const url = window.URL.createObjectURL(new Blob([res.data], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8'}));
        const link = document.createElement('a');
        link.href = url;
        link.setAttribute('download', '用户信息反馈表.xlsx') // 下载文件的名称及文件类型后缀
        document.body.appendChild(link)
        link.click();
        document.body.removeChild(link); // 下载完成移除元素
        window.URL.revokeObjectURL(url); // 释放掉blob对象
      }).catch(error => {
        this.$message.error(error);
      });
    },
    downloadModel() {
      this.$http.get('/sys/user/downloadModel',{responseType: "blob",}).then(res => {
        const url = window.URL.createObjectURL(new Blob([res.data], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8'}));
        const link = document.createElement('a');
        link.href = url;
        link.setAttribute('download', '用户信息.xlsx') // 下载文件的名称及文件类型后缀
        document.body.appendChild(link)
        link.click();
        document.body.removeChild(link); // 下载完成移除元素
        window.URL.revokeObjectURL(url); // 释放掉blob对象
      }).catch(error => {
        this.$message.error(error);
      });
    },
    //文件上传成功的回调
    handlerOnSuccess(){
      this.$message.success("上传成功！");
      this.dialogVisible = false;
      this.listPage();
      this.$refs.upload.clearFiles();
    }
  }
}
</script>

<style scoped lang="scss">
.container {
  padding: 10px;

  .page {
    float: right;
    margin-top: 20px;
  }

  a {
    text-decoration: none;
  }

  .title {
    font-size: 20px;
    font-weight: bold;
  }
}
</style>

 

③后端代码

/*
 * ******************************************************************************************************************************************
 * Copyright (c) 2021 .
 * All rights reserved.
 * 项目名称：atp-platform
 * 项目描述：应用测试平台管理端
 * 版权说明：本软件属云嘀科技有限公司所有，在未获得云嘀科技有限公司正式授权情况下，任何企业和个人，不能获取、阅读、安装、传播本软件涉及的任何受知识产权保护的内容。
 * *******************************************************************************************************************************************
 */
package com.yundi.atp.platform.module.sys.controller;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.yundi.atp.platform.common.Result;
import com.yundi.atp.platform.module.sys.entity.User;
import com.yundi.atp.platform.module.sys.excel.UploadDataListener;
import com.yundi.atp.platform.module.sys.excel.UserExcel;
import com.yundi.atp.platform.module.sys.service.UserService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 用户管理 前端控制器
 * </p>
 *
 * @author yanp
 * @since 2021-03-12
 */
@Slf4j
@Api(tags = {"用户管理"})
@RestController
@RequestMapping("/sys/user")
public class UserController {
    @Autowired
    private UserService userService;

    @ApiOperation(value = "登录")
    @PostMapping(value = "/login")
    public Result login(@RequestParam(value = "name") String name, @RequestParam(value = "pass") String pass) {
        Integer count = userService.count(new QueryWrapper<User>().eq("name", name).eq("pass", pass));
        if (count > 0) {
            return Result.success();
        }
        return Result.fail("用户名或者密码错误！");
    }

    @ApiOperation(value = "查询")
    @PostMapping(value = "listPage")
    public Result listPage(@RequestBody User user) {
        Page page = userService.page(user.getPage(), new QueryWrapper<User>().eq(StringUtils.isNotBlank(user.getName()), "name", user.getName()));
        return Result.success(page);
    }

    @ApiOperation(value = "保存")
    @PostMapping(value = "/save")
    public Result save(@RequestBody User user) {
        userService.saveUser(user);
        return Result.success();
    }

    @ApiOperation(value = "修改")
    @PostMapping(value = "/update")
    public Result update(@RequestBody User user) {
        userService.updateUserById(user);
        return Result.success();
    }

    @ApiOperation(value = "删除")
    @DeleteMapping(value = "/remove")
    public Result remove(@RequestParam(value = "id") String id) {
        userService.removeUserById(id);
        return Result.success();
    }

    @ApiOperation(value = "查询用户详情")
    @PostMapping(value = "/info")
    public Result info(@RequestParam(value = "id") String id) {
        User user = userService.findUserInfoById(id);
        return Result.success(user);
    }

    @ApiOperation(value = "查询全部用户信息详情")
    @GetMapping(value = "/findAllUserInfo")
    public Result findAllUserInfo() {
        List<User> userList = userService.findAllUserInfo();
        return Result.success(userList);
    }

    @ApiOperation(value = "导入")
    @PostMapping(value = "/import")
    public Result importUser(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), UserExcel.class, new UploadDataListener(userService)).sheet().doRead();
        return Result.success();
    }

    @ApiOperation(value = "导出")
    @GetMapping(value = "/export")
    public void exportUser(HttpServletResponse response) throws IOException {
        try {
            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("用户信息导出", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), UserExcel.class).sheet("用户信息").doWrite(userService.list());
        } catch (IOException ioException) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<>(16);
            map.put("status", "failure");
            map.put("message", "下载文件失败" + ioException.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

    @ApiOperation(value = "模板下载")
    @GetMapping(value = "/downloadModel")
    public void downloadModel(HttpServletResponse response) throws IOException {
        try {
            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("用户信息导出模板", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), UserExcel.class).sheet("用户信息").doWrite(null);
        } catch (IOException ioException) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<>(16);
            map.put("status", "failure");
            map.put("message", "下载文件失败" + ioException.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

    @ApiOperation(value = "填充")
    @GetMapping(value = "/fill")
    public void fillUserBill(HttpServletResponse response, String id) throws IOException {
        User user = userService.getById(id);
        String templateFileName = UserController.class.getResource("/").getPath() + "test.xlsx";
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templateFileName).build();
            WriteSheet writeSheetOne = EasyExcel.writerSheet(0, "用户信息审批单A").build();
            excelWriter.fill(user, writeSheetOne);
            WriteSheet writeSheetTwo = EasyExcel.writerSheet(1, "用户信息审批单B").build();
            user.setId("378954751351457571");
            excelWriter.fill(user, writeSheetTwo);
            WriteSheet writeSheetThree= EasyExcel.writerSheet(2, "用户信息审批单C").build();
            user.setId("3419549819590183945");
            user.setName("王重阳");
            excelWriter.fill(user, writeSheetThree);
            log.info("sheet生成结束！");
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }

        //单sheet页填充
//        EasyExcel.write(response.getOutputStream()).withTemplate(templateFileName).sheet().doFill(user);
    }

}

 ④验证结果

• excel导入

①同上

②前端代码

③后端代码

package com.yundi.atp.platform.module.sys.excel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.yundi.atp.platform.module.sys.entity.User;
import com.yundi.atp.platform.module.sys.service.UserService;
import com.yundi.atp.platform.module.sys.service.impl.UserServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * @Author: yanp
 * @Description:
 * @Date: 2021/8/19 14:27
 * @Version: 1.0.0
 */
@Slf4j
public class UploadDataListener extends AnalysisEventListener<UserExcel> {
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<User> list = new ArrayList<User>();

    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private UserService userService;

    public UploadDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        userService = new UserServiceImpl();
    }

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param userService
     */
    public UploadDataListener(UserService userService) {
        this.userService = userService;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(UserExcel data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        User user = new User();
        BeanUtils.copyProperties(data, user);
        list.add(user);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());
        userService.saveBatch(list);
        log.info("存储数据库成功！");
    }
}

 

④验证结果

• excel导出

①同上

②前端代码

③后端代码

 ④验证结果

• excel填充

①同上

②前端代码

③后端代码

④ 填充模板

 ⑤验证结果 

结语

本节关于使用EasyExcel实现excel导入、导出、多sheet填充、模板下载等功能案例实战到这里就结束了，我们下期见。。。