SpringBoot+EasyExcel+Vue3实现前后端导出导入Excel，解决Vue Excel损坏打不开【前后端完整代码】。

SpringBoot+EasyExcel+Vue3实现前后端导出导入Excel，解决Vue Excel损坏打不开【前后端完整代码】。后端导入Controller@PostMapping("/upload")public Result upload(MultipartFile file) throws IOException {return userService.upLoad(file);}Ser

干净的坏蛋

4954人浏览 · 2022-03-31 16:22:42

干净的坏蛋 · 2022-03-31 16:22:42 发布

SpringBoot+EasyExcel+Vue3实现前后端导出导入Excel，解决Vue Excel损坏打不开【前后端完整代码】。

后端

导入

Controller

@PostMapping("/upload")
    public Result upload(MultipartFile file) throws IOException {
        return userService.upLoad(file);
    }

Service

EasyExcel的DataListener

package com.erju.excel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.erju.dao.UserMapper;
import com.erju.pojo.User;
import com.erju.utils.JasyptUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * 有个很重要的点 DataListener 不能被spring管理，要每次读取excel都要new,
 * 然后里面用到spring可以构造方法传进去
 *
 * @author sunyuan
 * @date 2022/3/22 18:09
 */

public class DataListener extends AnalysisEventListener<User> {
    private static final Logger LOGGER = LoggerFactory.getLogger(DataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<User> list = new ArrayList<User>();

    private UserMapper userMapper;

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

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param user
     * @param context
     */
    @Override
    public void invoke(User user, AnalysisContext context) {
        String password = JasyptUtil.setPass(user.getUsername());
        //默认密码是用户名
        user.setPassword(password);
        //默认角色是学生
        user.setPermissions("student");
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(user));
        list.add(user);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            System.out.println(list);
            // 存储完成清理 list
            list.clear();
        }
    }

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

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

接口

Result<?> upLoad(MultipartFile file) throws IOException;

实现类

 @Override
    public Result upLoad(MultipartFile file) throws IOException {
        try {
            EasyExcel.read(file.getInputStream(), User.class, new DataListener(userMapper))
                    .sheet()
                    .doRead();
            return Result.success("导入成功");
        } catch (IOException e) {
            e.printStackTrace();
            return Result.error(e.getMessage());
        }
    }

Dao

 void batchInsert(@Param("list") List<User> list);

批量插入数据库提高效率

<insert id="batchInsert">
        insert into `sl_user`
        (
        username, password, user_type, use_flag, phone_number,permissions
        )
        values
        <foreach collection="list" item="user" separator=",">
            (#{user.username}, #{user.password}, #{user.user_type}, #{user.use_flag},
            #{user.phone_number},#{user.permissions})
        </foreach>
    </insert>

导出

controller

 @GetMapping("/downloadFailedUsingJson")
    public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
        userService.downloadFailedUsingJson(response);
    }

service

void downloadFailedUsingJson(HttpServletResponse response) throws IOException;

实现类

public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("测试", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            //数据
            List<User> list = userMapper.selectAllStudent();
            // 这里需要设置不关闭流 数据在response.OutPutStream里面  前端需要res.data
            EasyExcel.write(response.getOutputStream(), User.class).autoCloseStream(Boolean.FALSE).sheet("模板")
                    .doWrite(list);
//            return Result.success("导出成功");
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
//            return Result.error(e.getMessage());
        }

    }

dao

List<User> selectAllStudent();

<select id="selectAllStudent" resultType="com.erju.pojo.User">
        select username, phone_number
        from slab.sl_user
        where user_type = 2;
    </select>

前端

我也是前端小白，参考了很多大佬的博客才勉强实现了功能，前端代码逻辑较乱，仅供参考。

我封装的axios，在导出的时候用到了request的get方法，设置了一下responseType=blob 不然导出的excel无法打开

export const request = (method, url, headers = 'application/json;charset=UTF-8', responseType = null) => {
    if (method == "post") {
        return axios({
            method: 'post',
            url: "http://localhost:8888/" + url,
            // url: url,
            data: data,
            headers: {
                'Content-Type': headers,
                'Authorization': localStorage.getItem("token")
            },
            responseType: responseType // 相应类型如果有的话，就用接口中的，如果没有就默认为null
        });
    } else if (method == "get") {
        return axios({
            method: 'get',
            url: "http://localhost:8888/" + url,
            // url: url,
            headers: {
                'Content-Type': headers,
                'Authorization': localStorage.getItem("token")
            },
            responseType: responseType // 相应类型如果有的话，就用接口中的，如果没有就默认为null
        });
    } else {
        return;
    }
}

导入

导入的时候有个问题是 upload的action没有办法在请求的时候带上token

所以在

:headers=“myHeaders” 里带上了请求的token

const myHeaders = ref({
Authorization: localStorage.getItem(“token”)
})

<el-col :span="1">
                <el-upload
                    action="http://localhost:8888/user/upload"
                    :on-success="handleImportSuccess"
                    :on-error="handleImportError"
                    :headers="myHeaders"
                >
                    <el-button type="primary">导入</el-button>
                </el-upload>
            </el-col>

/**
 * excel 导入
 */
const handleImportSuccess = () => {
    ElMessage.success('导入成功')
    getList();
}
const handleImportError = () => {
    ElMessage.error('导入失败')
    getList();
}
const myHeaders = ref({
    Authorization: localStorage.getItem("token")
})

导出

//导入封装的axios
import {request} from "../../api/index"

<el-col :span="1">
                <el-button type="primary"
                           plain
                           :icon="Plus"
                           @click="expExcel">导出
                </el-button>
</el-col>

const expExcel = () => {
    request("get", `user/downloadFailedUsingJson`, 'application/json; charset=UTF-8', 'blob').then(res => {
        let blob = new Blob([res.data], {type: 'application/vnd.ms-excel'});
        let url = URL.createObjectURL(blob);
        const link = document.createElement('a'); //创建a标签
        link.href = url;
        link.download = '导出学生信息.xlsx'; //重命名文件
        link.click();
        URL.revokeObjectURL(url);
        console.log("下载文件" + res);
    })
}