Spring Boot + Vue + EasyExcel导出功能的实现

数据库设计

sql文件：

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80100
 Source Host           : localhost:3306
 Source Schema         : day20240306

 Target Server Type    : MySQL
 Target Server Version : 80100
 File Encoding         : 65001

 Date: 12/03/2024 16:04:34
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for bin_type
-- ----------------------------
DROP TABLE IF EXISTS `bin_type`;
CREATE TABLE `bin_type`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `bin_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for goods_shelf
-- ----------------------------
DROP TABLE IF EXISTS `goods_shelf`;
CREATE TABLE `goods_shelf`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `bin_type_id` int NULL DEFAULT NULL,
  `list_style_id` int NULL DEFAULT NULL,
  `img` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `slideshow_id` int NULL DEFAULT NULL,
  `shelf_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for list_style
-- ----------------------------
DROP TABLE IF EXISTS `list_style`;
CREATE TABLE `list_style`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `list_style` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for slideshow
-- ----------------------------
DROP TABLE IF EXISTS `slideshow`;
CREATE TABLE `slideshow`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `slideshow` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

Vue前端代码

<template>
    <div>
        <el-dialog :title="title" :visible.sync="dialogFormVisible" @close="resetForm('form')">
            <el-form :model="form" :rules="rules" ref="form">
                <el-form-item label="货架名称" :label-width="formLabelWidth" prop="shelfName">
                    <el-input class="add-input" v-model="goodsShelf.shelfName" autocomplete="off"></el-input>
                </el-form-item>
                <el-form-item label="货架类型" :label-width="formLabelWidth" prop="binType">
                    <el-select v-model="goodsShelf.binTypeId" placeholder="请选择" style="width: 580px;">
                        <el-option
                                v-for="freight in binTypes"
                                :key="freight.id"
                                :label="freight.binType"
                                :value="freight.id">
                        </el-option>
                    </el-select>
                </el-form-item>
                <div v-if="goodsShelf.binTypeId == 2">
                    <el-form-item label="轮播图" :label-width="formLabelWidth">
                        <el-upload
                                class="avatar-uploader"
                                action="http://localhost:8080/upload"
                                :show-file-list="false"
                                :on-success="handleAvatarSuccess"
                                :before-upload="beforeAvatarUpload">
                            <img v-if="goodsShelf.img" :src="goodsShelf.img" class="avatar">
                            <i v-else class="el-icon-plus avatar-uploader-icon"></i>
                            <div slot="tip" class="el-upload__tip">图片大小不能超过3M</div>
                        </el-upload>
                    </el-form-item>
                </div>
                <el-form-item label="列表样式" :label-width="formLabelWidth" prop="listStyle">
                    <el-select v-model="goodsShelf.listStyleId" placeholder="请选择" style="width: 580px;">
                        <el-option
                                v-for="freight in listStyles"
                                :key="freight.id"
                                :label="freight.listStyle"
                                :value="freight.id">
                        </el-option>
                    </el-select>
                </el-form-item>
                <el-form-item label="轮播图" :label-width="formLabelWidth">
                    <el-select v-model="goodsShelf.slideshowId" placeholder="请选择" style="width: 580px;">
                        <el-option
                                v-for="freight in slideshows"
                                :key="freight.id"
                                :label="freight.slideshow"
                                :value="freight.id">
                        </el-option>
                    </el-select>
                </el-form-item>
            </el-form>
            <div slot="footer" class="dialog-footer">
                <el-button @click="dialogFormVisible = false, resetForm('form')">取 消</el-button>
                <el-button type="primary" @click="submitForm('form')">确 定</el-button>
            </div>
        </el-dialog>

        <el-input class="input" v-model="shelfName" placeholder="请输入查询货品名称" style="width: 200px" clearable></el-input>
        <el-button type="primary" icon="el-icon-search" @click="loadGoodsShelfs(page)">搜索</el-button>
        <el-button type="text" @click="addButton">添加</el-button>
        <span v-show="deleteList.length != 0">
                    <el-button type="danger"
                               icon="el-icon-delete"
                               @click="deleteChoose"
                               size="medium">批量删除</el-button>
        </span>
        <span v-show="deleteList.length == 0">
            <el-button type="danger"
                       icon="el-icon-delete"
                       @click="deleteChoose"
                       size="medium" disabled>批量删除</el-button>
        </span>

        <span v-show="deleteList.length != 0">
                    <el-button type="success"
                               icon="el-icon-download"
                               @click="exportList"
                               size="medium">导出</el-button>
        </span>
        <span v-show="deleteList.length == 0">
            <el-button type="success"
                       icon="el-icon-download"
                       @click="exportList"
                       size="medium" disabled>导出</el-button>
        </span>

        <el-button type="success"
                   icon="el-icon-download"
                   @click="exportAll"
                   size="medium">导出全部</el-button>

        <el-table
                :data="goodsShelfs"
                stripe
                style="width: 100%; height: 450px"
                @select="selectOne"
                @select-all="selectAll">
            <el-table-column
                    type="selection"
                    width="55"
                    align="center">
            </el-table-column>
            <el-table-column
                    label="序号"
                    width="180">
                <template slot-scope="scope">
                    <p>{{ scope.row.id }}</p>
                </template>
            </el-table-column>
            <el-table-column
                    label="货架名称"
                    width="180">
                <template slot-scope="scope">
                    <p>{{ scope.row.shelfName }}</p>
                </template>
            </el-table-column>
            <el-table-column
                    label="货架类型"
                    width="180">
                <template slot-scope="scope">
                    <p>{{ binTypes[scope.row.binTypeId].binType }}</p>
                </template>
            </el-table-column>
            <el-table-column
                    label="操作"
                    width="200">
                <template slot-scope="scope">
                    <el-button type="primary" icon="el-icon-edit" @click="updateButton(scope.row),  updateFormVisible = true"></el-button>
                    <el-button type="primary" icon="el-icon-delete" @click="deleteOne(scope.row)"></el-button>
                </template>
            </el-table-column>
        </el-table>

        <el-pagination
                background
                layout="total,prev, pager, next,jumper"
                :total="total"
                :page-size="size"
                :current-page.sync="page"
                @current-change="getPageNum">
        </el-pagination>
    </div>
</template>

<script>
    export default {
        name: "OrderView",
        data() {
            return {
                shelfName:null,
                goodsShelfs:[],
                goodsShelf:{id:null,binTypeId:null,listStyleId:null,img:null,slideshowId:null,shelfName:null},
                binTypes:[],
                listStyles:[],
                slideshows:[],
                dialogFormVisible:false,
                updateVisible:false,
                page:1,
                size:2,
                total:0,
                pages:1,
                title:null,
                form: {
                    name: '',
                    region: '',
                    date1: '',
                    date2: '',
                    delivery: false,
                    type: [],
                    resource: '',
                    desc: ''
                },
                formLabelWidth: '140px',
                rules: {
                    shelfName: [
                        { required: true, message: '请输入货架名称', trigger: 'blur' }
                    ],
                    binType: [
                        { required: true, message: '请选择货架类型', trigger: 'blur' }
                    ],
                    listStyle: [
                        { required: true, message: '请选择列表样式', trigger: 'blur' }
                    ]
                },
                // 批量删除选择 或 批量操作的所选择的id号
                deleteList:[],
            }
        },
        methods: {
            // 加载主要数据
            loadGoodsShelfs(page) {
                this.deleteList = [];
                this.page = page;
                this.$http.get("/goodsShelf-page?page=" + this.page + "&size=" + this.size + "&shelfName=" + this.shelfName)
                    .then(result =>{
                        this.goodsShelfs = result.data.data.records;
                        this.pages= result.data.data.pages;
                        this.total = result.data.data.total;
                    })
            },
            // 点击添加按钮
            addButton() {
                this.dialogFormVisible = true;
                this.title = "添加货架";
                this.goodsShelf={id:null,binTypeId:null,listStyleId:null,img:null,slideshowId:null,shelfName:null};
            },
            // 点击修改按钮
            updateButton(row) {
                this.dialogFormVisible=true;
                this.title = "修改货架";
                this.goodsShelf = JSON.parse(JSON.stringify(row));
            },
            // 添加or修改
            addOrUpdate() {
                this.dialogFormVisible = false;
                if(this.goodsShelf.id == null) {
                    this.$http.post("/goodsShelf", this.goodsShelf)
                        .then(result => {
                            this.$message({
                                type: 'success',
                                message: '添加成功!'
                            });
                            this.loadGoodsShelfs(this.page);
                        })
                } else {
                    this.$http.put("/goodsShelf", this.goodsShelf)
                        .then(result => {
                            this.loadGoodsShelfs(this.page);
                            this.$message({
                                type: 'success',
                                message: '修改成功!'
                            });
                        })
                }
            },
            // 删除单个数据
            deleteOne(row) {
                this.$confirm('此操作将永久删除该文件, 是否继续?', '提示', {
                    confirmButtonText: '确定',
                    cancelButtonText: '取消',
                    type: 'warning'
                }).then(() => {
                    this.$http.delete("/goodsShelf/" + row.id)
                        .then(result => {
                            if (result.data.code == 200) {
                                this.loadGoodsShelfs(1);
                                this.$message({
                                    type: 'success',
                                    message: '删除成功!'
                                });
                            }
                        })
                }).catch(() => {
                    this.$message({
                        type: 'info',
                        message: '已取消删除'
                    });
                });
            },
            // 删除选择数据
            deleteChoose() {
                this.$confirm('此操作将永久删除这些文件, 是否继续?', '提示', {
                    confirmButtonText: '确定',
                    cancelButtonText: '取消',
                    type: 'warning'
                }).then(() => {
                    this.$http.post("/goodsShelf-deleteList", this.deleteList)
                        .then(result => {
                            if (result.data.code == 200) {
                                this.loadGoodsShelfs(1);
                                this.$message({
                                    type: 'success',
                                    message: '删除成功!'
                                });
                            }
                        })
                }).catch(() => {
                    this.$message({
                        type: 'info',
                        message: '已取消删除'
                    });
                });
            },
            // 翻页
            getPageNum(val) {
                this.loadGoodsShelfs(val);
            },
            // 获取图片的url
            handleAvatarSuccess(res, file) {
                this.goodsShelf.img = URL.createObjectURL(file.raw);
            },
            // 判断上传图片是否3mb
            beforeAvatarUpload(file) {
                const isLt3M = file.size / 1024 / 1024 < 3;
                if (!isLt3M) {
                    this.$message.error('上传图片大小不能超过 3MB!');
                }
                return isLt3M;
            },
            // 加载参数
            loadBinTypes() {
                this.$http.get("/binTypes")
                    .then(res => {
                        this.binTypes = res.data.data;
                    })
            },
            // 加载参数
            loadListStyles() {
                this.$http.get("/listStyles")
                    .then(res => {
                        this.listStyles = res.data.data;
                    })
            },
            // 加载参数
            loadSlideshows() {
                this.$http.get("/slideshows")
                    .then(res => {
                        this.slideshows = res.data.data;
                    })
            },
            // 表单验证
            submitForm(formName) {
                if (this.goodsShelf.shelfName != null && this.goodsShelf.binTypeId != null
                    && this.goodsShelf.listStyleId != null) {
                    this.addOrUpdate();
                    return
                }
                this.$refs[formName].validate((valid) => {
                    if (valid) {
                        this.addOrUpdate();
                    } else {
                        console.log('error submit!!');
                        return false;
                    }
                });
            },
            // 清除表单验证
            resetForm(formName) {
                this.$refs[formName].resetFields();
            },
            // 多选框 选择的数据对象
            selectOne(val) {
                this.deleteList = [];
                for (let i = 0; i < val.length; i++) {
                    this.deleteList[i] = val[i].id;
                }
            },
            selectAll(val) {
                this.deleteList = [];
                for (let i = 0; i < val.length; i++) {
                    this.deleteList[i] = val[i].id;
                }
            },
            // 导出所选
            exportList() {
                let msg = '此操作将导出这些数据, 是否继续?';
                this.exportChoose(msg);
            },
            // 导出所有
            exportAll() {
                let msg = '此操作将导出所有数据, 是否继续?';
                this.deleteList = [];
                this.exportChoose(msg);
            },
            // 导出操作
            exportChoose(msg) {
                this.$confirm(msg, '提示', {
                    confirmButtonText: '确定',
                    cancelButtonText: '取消',
                    type: 'success'
                }).then(() => {
                    this.$http.post("/goodsShelf-export", this.deleteList, {
                        responseType: 'blob'
                    })
                    .then(res => {
                        // 创建下载链接
                        const url = window.URL.createObjectURL(new Blob([res.data]));
                        const link = document.createElement('a');
                        link.href = url;
                        // 触发下载链接
                        link.setAttribute('download', 'test.xlsx');
                        document.body.appendChild(link);
                        link.click();
                        // 释放url对象
                        document.body.removeChild(link);
                        this.loadGoodsShelfs(this.page);
                    })
                }).catch(() => {
                    this.$message({
                        type: 'info',
                        message: '已取消导出'
                    });
                });
            }
        },
        // 界面渲染后
        mounted() {
            this.loadGoodsShelfs(1);
        },
        // 界面渲染前
        created() {
            this.loadBinTypes();
            this.loadListStyles();
            this.loadSlideshows();
        }
    }
</script>

<style scoped>
    .avatar-uploader .el-upload {
        border: 1px dashed #d9d9d9;
        border-radius: 6px;
        cursor: pointer;
        position: relative;
        overflow: hidden;
    }
    .avatar-uploader .el-upload:hover {
        border-color: #409EFF;
    }
    .avatar-uploader-icon {
        font-size: 28px;
        color: #8c939d;
        width: 178px;
        height: 178px;
        line-height: 178px;
        text-align: center;
    }
    .avatar {
        width: 178px;
        height: 178px;
        display: block;
    }
</style>

后台代码（核心代码）

git后端代码：mzh_gch/day240312demohttps://gitee.com/mzh-gch/day240312demo.git

后端接口

@Resource
    private ExportService exportService;
/**
 * 导出数据
 * @param response
 * @param exportIdList
 * @throws IOException
 * @throws InterruptedException
 */
@PostMapping("/goodsShelf-export")
public void exportExcel4(HttpServletResponse response, @RequestBody List<Long> exportIdList) throws IOException, InterruptedException {
    exportService.exportExcel1(response, exportIdList);
}

service

    // 写一个sheet
   public void exportExcel1(HttpServletResponse response, List<Long> exportIdList) throws IOException {

//       setExportHeader(response);
       List<GoodsShelf> goodsShelf;
       if (exportIdList == null || exportIdList.size() == 0) {
           goodsShelf = goodsShelfMapper.selectGoodsShelfByExcel(null);
       } else {
           StringBuilder stringBuilder = new StringBuilder();
           for (int i = 0; i < exportIdList.size(); i++) {
               stringBuilder.append(exportIdList.get(i));
               if (i + 1 != exportIdList.size()) {
                   stringBuilder.append(",");
               }
           }
           goodsShelf = goodsShelfMapper.selectGoodsShelfByExcel(stringBuilder.toString());
       }
       // 设置响应头部信息
       response.setContentType("application/octet-stream");
       response.setCharacterEncoding(StandardCharsets.UTF_8.name());
       String fileName = URLEncoder.encode("test.xlsx", "UTF-8");
       response.setHeader("Content-disposition", "attachment;filename=" + fileName);

       // 将数据写入 Excel 文件并写入 Response 的输出流中
       EasyExcel.write(response.getOutputStream(), GoodsShelf.class).sheet().doWrite(goodsShelf);
       // 刷新并关闭输出流
       response.getOutputStream().flush();
       response.getOutputStream().close();
   }

// 多线程分页查 每页数据写入一个sheet 需要自己按照上面的进行修改
    public void exportExcel4(HttpServletResponse response) throws IOException, InterruptedException {

        setExportHeader(response);

        Long count = goodsShelfMapper.selectCount(null);

        Integer pages = 20;
        Long size = count / pages;

        ExecutorService executorService = Executors.newFixedThreadPool(pages);
        CountDownLatch countDownLatch = new CountDownLatch(pages);

        Map<Integer, Page<GoodsShelf>> pageMap = new HashMap<>();
        for (int i = 0; i < pages; i++) {
            int finalI = i;
            executorService.submit(new Runnable() {
                @Override
                public void run() {
                    Page<GoodsShelf> page = new Page<>();
                    page.setCurrent(finalI + 1);
                    page.setSize(size);
                    Page<GoodsShelf> selectPage = goodsShelfMapper.selectPage(page, null);

                    pageMap.put(finalI, selectPage);
                    countDownLatch.countDown();
                }
            });
        }

        countDownLatch.await();

        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), GoodsShelf.class).build()) {
            for (Map.Entry<Integer, Page<GoodsShelf>> entry : pageMap.entrySet()) {
                Integer num = entry.getKey();
                Page<GoodsShelf> goodsShelfPage = entry.getValue();
                WriteSheet writeSheet = EasyExcel.writerSheet(num, "模板" + num).build();
                excelWriter.write(goodsShelfPage.getRecords(), writeSheet);
            }
        }
    }

mapper层

List<GoodsShelf> selectGoodsShelfByExcel(@Param("exportIds") String exportIds);

Mapper.xml

<select id="selectGoodsShelfByExcel" resultType="GoodsShelf">
        select g.*, b.bin_type, l.list_style, s.slideshow from goods_shelf g
        join bin_type b on b.id = g.bin_type_id
        join list_style l on l.id = g.list_style_id
        join slideshow s on s.id = g.slideshow_id
    <where>
        <if test="exportIds != null and exportIds != ''">
           g.id in (#{exportIds})
        </if>
    </where>
   
</select>