vue+element 使用Export2Excel导出表格组件

下载表格组件是根据我自己的业务需求来封装的

使用的是vue中 xlsx 的插件，需要安装新的依赖及配置

 

仅供参考 不保证和你百分百匹配

 

 

安装依赖

npm install -S file-saver xlsx

npm install -D script-loader

下载所需js

链接: https://pan.baidu.com/s/170okRAPiWxQrBvlDEpp7SQ 提取码: gsf5 

百度网盘真特喵的是个好东西啊

因为我的子组件引用了该js 如果你不想自己再修改位置，可以跟我一样，

在src中，建立excel文件，将下载的js扔进去

 

 

组件调用

直接说调用部分，文章最后会把组件的代码贴出来

 

父组件引入子组件

import exportTable from '@/components/common/exportTable';

......

components:{

　　exportTable

}

父组件调用子组件

<export-table ref="childRefName" :export-prepare.sync="exportPrepare" :list-query.sync="listQuery"></export-table>

 

　　

 

 参数详解

exportPrepare:{
    percentage: 0, //导出数据完成的百分比
    name:'消耗记录',//导出表格名称
    pageType:'pagination',//导出数据获取方式：offset为偏移量、pagination为分页 默认值为pagination
    limit:200,//单页数据数量 最大值为200 超过200 按200读取（自定义）或 perPage: 200（根据接口参数决定使用limit/perPage，暂时仅支持limit/perPage）
    total:0,//导出数据总数
    json_fields: {  // excel 表头（具体名称根据实际情况而定）
        '耗材编号': 'String',
        '耗材品牌': 'String',
        '耗材类型': 'String',
        '耗材名称': 'String',
        '规格型号': 'String',
        '耗材属性': 'String',
        '耗材价格': 'Number',
        '消耗类型': 'String',
        '消耗地点': 'String',
        '出库仓': 'String',
        '目标设备': 'String',
        '操作人': 'String',
        '操作时间': 'String'
    },
    json_relationship:{ //导出数据对应的字段名称（具体字段根据实际情况而定 key值与表头保持一致）
        '耗材编号': 'supplies_number',
        '耗材品牌': 'brand_name',
        '耗材类型': 'type',
        '耗材名称': 'supplies_name',
        '规格型号': 'spec_name',
        '耗材属性': 'spec_attribute',
        '耗材价格': 'cost',
        '消耗类型': 'supplies_type',
        '消耗地点': 'out_address',
        '出库仓': 'warehouse_name',
        '目标设备': 'device_id',
        '操作人': 'staff_name',
        '操作时间': 'updated_at'
    },
    param:{},//获取导引数据接口所需的参数
    dataList:[]
}

　　注：json_relationship的值一定与json_fields的值保持一致，否则导出的表会出现空表的问题

listQuery: {    // 列表请求数据
    where: {},//where是我的接口用到的筛选数据的条件 没有可以直接去掉
    page: 1,
    limit: 20
}

　　注：子组件导出数据时页码，会引起父页面修改exportPrepare的param的page、limit参数，使用listQuery进行独立数据深拷贝，子组件的参数变化就不会引起父页面的变化

 

页面处理导出数据

父组件内获取导出数据的方法

getExcelData(){
    this.$refs.exportExcel.index+=1;
    if(this.$refs.exportExcel.index<this.$refs.exportExcel.params.length) {
        suppliesWaitInExcelData( this.$refs.exportExcel.params[this.$refs.exportExcel.index]).then((response) => {
            const data = this.data_ext ? response[this.data_ext].data : response.data;
            //data的值，需要根据实际的response数据格式进行赋值
            const relationship = this.exportPrepare.json_relationship;
            for (let i in data) {
                var item = {};
                for (let attr in relationship) {
                    item[attr] = !data[i][relationship[attr]] && data[i][relationship[attr]] !== 0 ? '' : data[i][relationship[attr]];
                }
                this.exportPrepare.dataList.push(item);
            }
            this.exportPrepare.percentage = Number((this.exportPrepare.dataList.length / this.exportPrepare.total*100).toFixed(0)) || 0;
            this.getExcelData();
        })
        .catch(e=>{//这里是处理接口获取失败的情况 => 重新执行该条 知道走通（不需要可以把这个部分清空）
            console.log('失败了')
            this.$refs.exportExcel.index-=1;
            this.getExcelData();
        })
    }
}

　　注：getExcelData：为固定方法名称，不可以自定义（子组件内有调用 除非两者保持一致）
　　　　suppliesWaitInExcelData：获取导出数据的接口api名称，根据具体项目需求更换（接口封装方法不同，调用api方法不同，请根据实际情况修改）

 

子组件封装

注：子组件中用到this.$parents.doSomeThing()的方式去寻找父组件的方法，所以子组件不能被包裹在别的组件中（包括ele、el-row等element或自定义组件），否则会因为父组件层级问题，找不到对应的父组件方法

或者根据具体情况修改子组件内的方法 ex:this.$parents.$parents.doSomeThing() 多找一层parent 有可能会找到 但是不推荐

 

<template>
    <div id="export-excel">
        <el-button type="primary" icon="document" class="export-btn" @click="init">导出数据</el-button>
        <el-dialog title="导出" :visible.sync="form.dialogFormVisible" size="tiny" :before-close="handleClose">
            <div class="prev-btn right">
                <el-progress :percentage="exportPrepare.percentage" style="margin-bottom: 20px;"></el-progress>
                <p style="color:#F56C6C;font-size:12px;" v-show="exportTips">数据量过大，推荐选择精确数据下载</p>
                <el-button type="primary" @click="exportAction" v-if="exportPrepare.total == exportPrepare.dataList.length">导出</el-button>
                <el-button type="primary" class="cancel-action" v-else>导出</el-button>
                <el-button @click="handleClose">取消</el-button>
            </div>
        </el-dialog>
    </div>
</template>

<script type="application/ecmascript">
export default {
    name: "export-excel",
    props: {
        exportPrepare: {
            type: Object
        },
        listQuery: {
            type: Object
        }

    },
    data() {
        return {
            params: [],
            paramsItem: {},
            index: -1,
            // 导出表单所需数据对象
            form: {
                loading: false,
                dialogFormVisible: false,
            },
            pageType: 'pagination', //获取数据方式 pagination为分页方式  offset为偏移量方式
            json_meta: [ // 设置字符集
                [{
                    key: "charset",
                    value: "utf-8"
                }]
            ], // excel表头
            json_data: [], // 要导出的数据
            dataList: [], // 要导出的数据
            button_text: '', // 导出按钮名称
            data_ext: '', // 数据后缀 防止返回的数据格式不是[obj, obj...]
            childExportPrepare:{},
            exportTips:false,//数据过大提示
        };
    },
    mounted() {
        this.pageType = this.exportPrepare.pageType || 'pagination'
        this.childExportPrepare =  JSON.parse(JSON.stringify(this.listQuery))
    },
    watch: {
        listQuery(newValue, oldValue) {
            this.childExportPrepare =  JSON.parse(JSON.stringify(this.listQuery))
        }
    },
    methods: {
        // 导出初始化
        init() {
            this.form.dialogFormVisible = true;
            this.data_ext = this.exportPrepare.data_ext || '';
            this.fetchExportList();
        },

        // 数据列表分次获取
        fetchExportList() {
            this.childExportPrepare =  JSON.parse(JSON.stringify(this.listQuery))
            this.exportPrepare.dataList = [];
            this.index = -1;
            this.params = [];
            this.exportPrepare.percentage = 0;
            this.json_data = [];
            if (this.pageType == 'offset') {
                if(this.exportPrepare.limit){
                    if (this.exportPrepare.total > this.exportPrepare.limit) {
                        this.json_data = [];
                        let param = {}
                        param = this.childExportPrepare
                        for (let i = 0; i < Math.ceil(this.exportPrepare.total / this.exportPrepare.limit); i++) {
                            if(i>=20){
                                this.exportTips = true;
                            }else{
                                this.exportTips = false;
                            }
                            if(this.exportPrepare.limit>200){
                                this.exportPrepare.limit = 200;
                            }
                            var startPage = 0,
                                endlimit = 0;
                            startPage = i * this.exportPrepare.limit;
                            this.$set(param,'page',startPage)
                            endlimit = this.exportPrepare.limit;
                            if (i >= Math.ceil(this.exportPrepare.total / this.exportPrepare.limit) - 1 && this.exportPrepare.total % this.exportPrepare.limit != 0) {
                                endlimit = this.exportPrepare.total % this.exportPrepare.limit
                            }
                            this.$set(param,'limit',endlimit)
                            let paramList = {page:param.page,limit:param.limit};
                            this.form.loading = true;
                            this.params.push(Object.assign(paramList,this.childExportPrepare));
                        }
                        this.$parent.$parent.getExcelData()
                        this.json_data = this.exportPrepare.dataList;
                    } else {

                        this.json_data = [];
                        this.params = [];
                        this.childExportPrepare.page = 0;
                        this.childExportPrepare.limit = this.exportPrepare.total;
                        let param = [];
                        param.page = 0;
                        param.limit = this.exportPrepare.total;
                        let paramList = {page:param.page,limit:param.limit};
                        this.params.push(Object.assign(paramList,this.childExportPrepare));
                        this.form.loading = true;
                        this.$parent.$parent.getExcelData();
                        this.json_data = this.exportPrepare.dataList;
                    }
                }else{
                    if (this.exportPrepare.total > this.exportPrepare.perPage) {
                        this.json_data = [];
                        let param = {};
                        param = this.childExportPrepare
                        for (let i = 0; i < Math.ceil(this.exportPrepare.total / this.exportPrepare.perPage); i++) {
                            if(i>=20){
                                this.exportTips = true;
                            }else{
                                this.exportTips = false;
                            }
                            if(this.exportPrepare.perPage>200){
                                this.exportPrepare.perPage = 200;
                            }
                            var startPage = 0,
                                endlimit = 0;
                            startPage = i * this.exportPrepare.perPage;
                            this.$set(param,'page',startPage)
                            endlimit = this.exportPrepare.perPage;
                            if (i >= Math.ceil(this.exportPrepare.total / this.exportPrepare.perPage) - 1 && this.exportPrepare.total % this.exportPrepare.perPage != 0) {
                                endlimit = this.exportPrepare.total % this.exportPrepare.perPage
                            }
                            this.$set(param,'perPage',endlimit)
                            let paramList = {page:param.page,perPage:param.perPage};
                            this.form.loading = true;
                            this.params.push(Object.assign(paramList,this.childExportPrepare));
                        }
                        this.$parent.$parent.getExcelData()
                        this.json_data = this.exportPrepare.dataList;
                    } else {
                        this.json_data = [];
                        this.params = [];
                        this.childExportPrepare.page = 0;
                        this.childExportPrepare.limit = this.exportPrepare.total;
                        let param = [];
                        param.page = 0;
                        param.perPage = this.exportPrepare.total;
                        let paramList = {page:param.page,perPage:param.perPage};
                        this.params.push(Object.assign(paramList,this.childExportPrepare));
                        this.form.loading = true;
                        this.$parent.$parent.getExcelData();
                        this.json_data = this.exportPrepare.dataList;
                    }
                }
            } else {
                if(this.exportPrepare){
                    if (this.exportPrepare.total > this.exportPrepare.limit) {
                        this.json_data = [];
                        let param = {}
                        param = this.childExportPrepare
                        for (let i = 0; i < Math.ceil(this.exportPrepare.total / this.exportPrepare.limit); i++) {
                            if(i>=20){
                                this.exportTips = true;
                            }else{
                                this.exportTips = false;
                            }
                            if(this.exportPrepare.limit>200){
                                this.exportPrepare.limit = 200;
                            }
                            let startPage = 0, endlimit = 0;
                            startPage = i + 1;
                            this.$set(param,'page',startPage)
                            endlimit = this.exportPrepare.limit;
                            if (i >= Math.ceil(this.exportPrepare.total / this.exportPrepare.limit) - 1 && this.exportPrepare.total % this.exportPrepare.limit != 0) {
                                endlimit = this.exportPrepare.total % this.exportPrepare.limit
                            }
                            this.$set(param,'limit',endlimit)
                            let paramList = {page:param.page,limit:param.limit};
                            this.form.loading = true;
                            this.params.push(Object.assign(paramList,this.childExportPrepare));
                        }
                        this.$parent.$parent.getExcelData()
                        this.json_data = this.exportPrepare.dataList;
                    } else {
                        this.json_data = [];
                        this.params = [];
                        this.childExportPrepare.page = 1;
                        this.childExportPrepare.limit = this.exportPrepare.total;
                        let param = [];
                        param.page = 1;
                        param.limit = this.exportPrepare.total;
                        let paramList = {page:param.page,limit:param.limit};
                        this.params.push(Object.assign(paramList,this.childExportPrepare));
                        this.form.loading = true;
                        this.$parent.$parent.getExcelData();
                        this.json_data = this.exportPrepare.dataList;
                    }
                }else{
                    if (this.exportPrepare.total > this.exportPrepare.perPage) {
                        this.json_data = [];
                        let param = {}
                        param = this.childExportPrepare
                        for (let i = 0; i < Math.ceil(this.exportPrepare.total / this.exportPrepare.perPage); i++) {
                            if(i>=20){
                                this.exportTips = true;
                            }else{
                                this.exportTips = false;
                            }
                            if(this.exportPrepare.perPage>200){
                                this.exportPrepare.perPage = 200;
                            }
                            var startPage = 0,
                                endlimit = 0;
                            startPage = i + 1;
                            this.$set(param,'page',startPage)
                            endlimit = this.exportPrepare.perPage;
                            if (i >= Math.ceil(this.exportPrepare.total / this.exportPrepare.perPage) - 1 && this.exportPrepare.total % this.exportPrepare.perPage != 0) {
                                endlimit = this.exportPrepare.total % this.exportPrepare.perPage
                            }
                            this.$set(param,'perPage',endlimit)
                            let paramList = {page:param.page,perPage:param.perPage};
                            this.form.loading = true;
                            this.params.push(Object.assign(paramList,this.childExportPrepare));
                        }
                        this.$parent.$parent.getExcelData()
                        this.json_data = this.exportPrepare.dataList;

                    } else {
                        this.json_data = [];
                        this.params = [];
                        this.childExportPrepare.page = 1;
                        this.childExportPrepare.limit = this.exportPrepare.total;
                        let param = [];
                        param.page = 1;
                        param.perPage = this.exportPrepare.total;
                        let paramList = {page:param.page,perPage:param.perPage};
                        this.params.push(Object.assign(paramList,this.childExportPrepare));
                        this.form.loading = true;
                        this.$parent.$parent.getExcelData();
                        this.json_data = this.exportPrepare.dataList;
                    }
                }
            }
        },

        //导出
        exportAction() {
            if (this.exportPrepare.total == this.json_data.length) {
                require.ensure([], () => {
                    const {
                        export_json_to_excel
                    } = require('../../excel/Export2Excel');
                    const tHeader = [];
                    for (let i in this.exportPrepare.json_fields) {
                        tHeader.push(i);
                    }
                    if (this.json_data.length < 1) {
                        this.fetchExportList();
                        return
                    }
                    const list = this.json_data;
                    const data = this.formatJson(tHeader, list);
                    export_json_to_excel(tHeader, data, this.exportPrepare.name || '导出数据');
                    this.form.loading = true;
                    setTimeout(() => {
                        this.form.loading = false;
                        this.$notify({
                            title: '成功',
                            message: '导出成功',
                            type: 'success',
                            duration: 1500
                        });
                        this.form.dialogFormVisible = false;
                        this.json_data = [];
                        this.dataList = [];
                        this.exportPrepare.dataList = [];
                    }, 500);
                })
            }
        },

        formatJson(filterVal, jsonData) {
            return jsonData.map(v => filterVal.map(j => v[j]))
        },

        // 关闭导出对话框
        handleClose() {
            this.form.dialogFormVisible = false;
            this.exportPrepare.dataList = [];
            this.json_data = [];
            this.dataList = [];
            this.params = [];
            this.exportPrepare.percentage = 0;
        }
    }
}
</script>

<style scoped="scoped" lang="scss">
.cancel-action {
    opacity: .4;
}

.right {
    text-align: right;
}
#export-excel{
  float: right;
}
</style>