Java根据excel/word模板进行值替换并且打成压缩包

lhf@@yxg

已于 2022-11-10 11:53:59 修改

阅读量1.7k

点赞数 2

文章标签： java servlet html

于 2022-10-17 10:49:51 首次发布

本文链接：https://blog.csdn.net/weixin_49143405/article/details/127358932

版权

一前端代码（MbTemplateList.vue）

二后端代码

MbTemplateController.java

IMbTemplateService.java

MbTemplateServiceImpl.java

提供了zip的压缩和解压的api

提供删除文件夹下面内容的方法

一前端代码（MbTemplateList.vue）

模板制作和文件夹创建

xlsx模板制作

docx模板制作

替换excel的依赖

<!--创建表的插件-->
<dependency>
    <groupId>com.gitee.sunchenbin.mybatis.actable</groupId>
    <artifactId>mybatis-enhance-actable</artifactId>
    <version>1.1.1.RELEASE</version>
</dependency>
<!--druid数据源-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.21</version>
</dependency>
<!-- poi 相关 表中值替换 -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.4.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>com.lowagie</groupId>
    <artifactId>itext</artifactId>
    <version>2.1.7</version>
    <scope>compile</scope>
</dependency>
<dependency>
    <groupId>com.mashape.unirest</groupId>
    <artifactId>unirest-java</artifactId>
    <version>1.4.9</version>
</dependency>

替换word模板需要的依赖

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.cn/repository/maven-public/</url>
    </repository>
</repositories>

*使用这个依赖会产生水印
<dependency>
    <groupId>e-iceblue</groupId>
    <artifactId>spire.doc</artifactId>
    <version>5.4.2</version>
</dependency>

#使用这个依赖不会产生水印

<dependency>
    <groupId> e-iceblue </groupId>
    <artifactId>spire.doc.free</artifactId>
    <version>5.2.0</version>
</dependency>

文件夹创建

1 模板excel或者word文件存放位置

D:\opt\upFiles\temp

2 替换后的excel文件存放 (同样也是打包的文件夹)-----自己随便创建一个

D:\opt\wd

<template>
    <a-card :bordered="false">
        <!-- 查询区域 -->
        <div class="table-page-search-wrapper">
            <a-form layout="inline"
                @keyup.enter.native="searchQuery">
                <a-row :gutter="24">
                </a-row>
            </a-form>
        </div>
        <!-- 查询区域-END -->
        <div class="table-page-search-wrapper">
            <a-form layout="inline"
                @keyup.enter.native="searchQuery">
                <a-row :gutter="24">
                    <a-col :md="4"
                        :sm="12">
                        <a-form-item label="姓名">
                            <j-input placeholder="输入姓名模糊查询"
                                v-model="queryParam.name"></j-input>
                        </a-form-item>
                    </a-col>
                    <a-col :md="5"
                        :sm="12">
                        <a-form-item label="身份证号">
                            <j-input placeholder="输入身份证查询"
                                v-model="queryParam.idCard"></j-input>
                        </a-form-item>
                    </a-col>
                    <a-col :md="5"
                        :sm="12">
                        <a-form-item label="联系电话">
                            <j-input placeholder="输入电话查询"
                                v-model="queryParam.phone"></j-input>
                        </a-form-item>
                    </a-col>
                    <a-col :md="6"
                        :sm="8">
                        <span style="float: left;overflow: hidden;"
                            class="table-page-search-submitButtons">
                            <a-button type="primary"
                                @click="searchQuery"
                                icon="search">搜索</a-button>
                            <a-button type="primary"
                                @click="searchReset"
                                icon="reload"
                                style="margin-left: 8px">重置</a-button>
                        </span>
                    </a-col>
                </a-row>
            </a-form>
        </div>
        <!-- 操作按钮区域 -->
        <div class="table-operator">
            <a-button @click="handleAdd"
                type="primary"
                icon="plus">新增贷款</a-button>
            <a-upload name="file"
                :showUploadList="false"
                :multiple="false"
                :headers="tokenHeader"
                :action="importExcelUrl"
                @change="handleImportExcel">
                <a-button type="primary"
                    icon="import">导入贷款</a-button>
            </a-upload>
            <a-button type="primary"
                icon="download"
                @click="handleExportXls('个人贷款模板')">导出贷款</a-button>

            <!-- 高级查询区域 -->
            <!-- <j-super-query :fieldList="superFieldList" ref="superQueryModal" @handleSuperQuery="handleSuperQuery"></j-super-query>
      <a-dropdown v-if="selectedRowKeys.length > 0">
        <a-menu slot="overlay">
          <a-menu-item key="1" @click="batchDel"><a-icon type="delete"/>删除</a-menu-item>
        </a-menu>
        <a-button style="margin-left: 8px"> 批量操作 <a-icon type="down" /></a-button>
      </a-dropdown> -->
        </div>

        <!-- table区域-begin -->
        <div>
            <div class="ant-alert ant-alert-info"
                style="margin-bottom: 16px;">
                <i class="anticon anticon-info-circle ant-alert-icon"></i> 已选择 <a
                    style="font-weight: 600">{{ selectedRowKeys.length }}</a>项
                <a style="margin-left: 24px"
                    @click="onClearSelected">清空</a>
            </div>

            <a-table ref="table"
                size="middle"
                :scroll="{x:true}"
                bordered
                rowKey="id"
                :columns="columns"
                :dataSource="dataSource"
                :pagination="ipagination"
                :loading="loading"
                :rowSelection="{selectedRowKeys: selectedRowKeys, onChange: onSelectChange}"
                class="j-table-force-nowrap"
                @change="handleTableChange">

                <template slot="htmlSlot"
                    slot-scope="text">
                    <div v-html="text"></div>
                </template>
                <template slot="imgSlot"
                    slot-scope="text">
                    <span v-if="!text"
                        style="font-size: 12px;font-style: italic;">无图片</span>
                    <img v-else
                        :src="getImgView(text)"
                        height="25px"
                        alt=""
                        style="max-width:80px;font-size: 12px;font-style: italic;" />
                </template>
                <template slot="fileSlot"
                    slot-scope="text">
                    <span v-if="!text"
                        style="font-size: 12px;font-style: italic;">无文件</span>
                    <a-button v-else
                        :ghost="true"
                        type="primary"
                        icon="download"
                        size="small"
                        @click="downloadFile(text)">
                        下载
                    </a-button>
                </template>

                <span slot="action"
                    slot-scope="text, record">
                    <a @click="handleEdit(record)">编辑</a>
                    <a-divider type="vertical" />
                    <a @click="showModal(record)">下载文档</a>
                    <a-divider type="vertical" />
                    <a-popconfirm title="确定删除吗?"
                        @confirm="() => handleDelete(record.id)">
                        <a>删除</a>
                    </a-popconfirm>

                </span>
            </a-table>
        </div>
        <mb-template-modal ref="modalForm"
            @ok="modalFormOk"></mb-template-modal>
        <j-modal title="选择文档"
            :width="800"
            :visible="docVisible"
            switchFullscreen
            @ok="handlePrint"
            @cancel="docVisible = false"
            cancelText="关闭">
            <a-checkbox-group @change="onChange"
                style="width: 100%">
                <div class="check-box"
                    v-for="(items, index) in docList"
                    :key="index">
                    <div class="label" v-if="items.children.length > 0">{{items.label}}</div>
                    <a-row>
                        <a-col :span="8"
                            v-for="(item, i) in items.children"
                            :key="i">
                            <a-checkbox :value="item.id">
                                {{item.name}}
                            </a-checkbox>
                        </a-col>
                    </a-row>
                </div>
            </a-checkbox-group>

        </j-modal>
    </a-card>
</template>

<script>

import '@/assets/less/TableExpand.less'
import { mixinDevice } from '@/utils/mixin'
import { JeecgListMixin } from '@/mixins/JeecgListMixin'
import MbTemplateModal from './modules/MbTemplateModal'
import { filterDictTextByCache, initDictOptions } from '@/components/dict/JDictSelectUtil'
import { getAction, downFile } from '@/api/manage'

export default {
    name: 'MbTemplateList',
    mixins: [JeecgListMixin, mixinDevice],
    components: {
        MbTemplateModal,
    },
    data() {
        return {
            description: '个人贷款模板管理页面',
            // 表头
            columns: [
                {
                    title: '添加时间',
                    align: "center",
                    dataIndex: 'createTime'
                },
                {
                    title: '姓名',
                    align: "center",
                    dataIndex: 'name'
                },
                {
                    title: '身份证号码',
                    align: "center",
                    dataIndex: 'idCard'
                },
                {
                    title: '地址',
                    align: "center",
                    dataIndex: 'adder'
                },
                {
                    title: '电话',
                    align: "center",
                    dataIndex: 'phone'
                },
                {
                    title: '借款金额-(万)',
                    align: "center",
                    dataIndex: 'loanAmount'
                },
                {
                    title: '借款期限-(月)',
                    align: "center",
                    dataIndex: 'loanTerm'
                },
                {
                    title: '网点',
                    align: "center",
                    dataIndex: 'network'
                },
                {
                    title: '客户经理',
                    align: "center",
                    dataIndex: 'accountManager'
                },
                {
                    title: '状态',
                    align: "center",
                    dataIndex: 'status',
                    customRender: (text) => {
                        //字典值替换通用方法
                        return filterDictTextByCache('me_status', text);
                    }
                },
                {
                    title: '操作',
                    dataIndex: 'action',
                    align: "center",
                    fixed: "right",
                    width: 147,
                    scopedSlots: { customRender: 'action' }
                }
            ],
            url: {
                list: "/mbTemplate/list",
                delete: "/mbTemplate/delete",
                deleteBatch: "/mbTemplate/deleteBatch",
                exportXlsUrl: "/mbTemplate/exportXls",
                importExcelUrl: "/mbTemplate/importExcel",

            },
            dictOptions: {},
            superFieldList: [],
            docVisible: false,
            docList: [],
            statusList: [],
            checkList: [],
            recordObj: {},
        }
    },
    created() {
        this.getSuperFieldList();
        this.getStatusList();
    },
    computed: {
        importExcelUrl: function () {
            return `${window._CONFIG['domianURL']}/${this.url.importExcelUrl}`;
        },
    },
    methods: {
        initDictConfig() {
        },
        getSuperFieldList() {
            let fieldList = [];
            fieldList.push({ type: 'string', value: 'name', text: '姓名', dictCode: '' })
            fieldList.push({ type: 'string', value: 'sex', text: '性别', dictCode: '' })
            fieldList.push({ type: 'string', value: 'age', text: '年龄', dictCode: '' })
            fieldList.push({ type: 'string', value: 'idCard', text: '身份证号码', dictCode: '' })
            fieldList.push({ type: 'string', value: 'adder', text: '地址', dictCode: '' })
            fieldList.push({ type: 'string', value: 'phone', text: '电话', dictCode: '' })
            fieldList.push({ type: 'BigDecimal', value: 'loanAmount', text: '借款金额', dictCode: '' })
            fieldList.push({ type: 'string', value: 'loanTerm', text: '借款期限', dictCode: '' })
            fieldList.push({ type: 'string', value: 'network', text: '网点', dictCode: '' })
            fieldList.push({ type: 'string', value: 'accountManager', text: '客户经理', dictCode: '' })
            fieldList.push({ type: 'string', value: 'status', text: '状态', dictCode: '' })
            this.superFieldList = fieldList
        },
        getStatusList() {
            initDictOptions('m_status').then(res => {
                this.statusList = res.result
            })
        },
        showModal(record) {
            this.recordObj = record
            getAction('/mbTemplate/mbwd').then(res => {
                let list = res.result.map(item => {
                    // let name = item.documentTemplate.split('/')[1].split('.')[0]
                    let name = item.documentTemplate.split('.')[0]
                    return {
                        ...item,
                        name: name
                    }
                })
                this.docList = this.statusList.map(item => {
                    return {
                        label: item.text,
                        children: list.filter(val => { return +val.status === +item.value })
                    }
                })
                this.docVisible = true
            })
        },
        handlePrint() {
            if(this.checkList.length === 0) {
                this.$message.warning('请选择要下载的文档')
                return
            }
            // if(this.checkList.length === 1) {
            //     this.handleDownloadXls(this.checkList[0])
            // } else {
            //     // this.checkList.forEach(item => {
            //     //     this.handleDownloadXls(item)
            //     // })
            // }
            let mbId = this.checkList.map(item => {return item.documentTemplate}).join(',')
            this.handleDownloadZip(mbId)
        },
        handleDownloadZip(mbId) {
            let fileName = this.recordObj.name + '文档'
            downFile("/mbTemplate/export", {mbId: mbId, id: this.recordObj.id}).then(data => {
                if (!data) {
                    this.$message.warning("下载文档失败")
                    return
                }
                if (typeof window.navigator.msSaveBlob !== 'undefined') {
                    window.navigator.msSaveBlob(new Blob([data], { type: 'application/vnd.ms-excel' }), fileName + '.zip')
                } else {
                    let url = window.URL.createObjectURL(new Blob([data], { type: 'application/vnd.ms-excel' }))
                    let link = document.createElement('a')
                    link.style.display = 'none'
                    link.href = url
                    link.setAttribute('download', fileName + '.zip')
                    document.body.appendChild(link)
                    link.click()
                    document.body.removeChild(link); //下载完成移除元素
                    window.URL.revokeObjectURL(url); //释放掉blob对象
                }
                this.docVisible = false
            })
        },
        onChange(checkedValues) {
            this.checkList = this.docList.map(item => {
                return item.children.filter(val => { return checkedValues.includes(val.id) })
            }).flat()
        },
    }
}
</script>
<style scoped>
@import '~@assets/less/common.less';
.label {
    padding-bottom: 10px;
    font-size: 14px;
    font-weight: bold;
    color: #333;
}
.check-box + .check-box {
    padding-top: 10px;
}
</style>