vue 前端导出Excel表格（基础版 + 多级标题）纯前端导出

先看效果

纯前端基础导出的Excel表格

纯前端多级表头导出的Excel表格

基础导出下面赋源代码

1、安装依赖

npm install vue-json-excel

2、在项目的入口 main.js 引入

import JsonExcel from 'vue-json-excel'// 引入导出Excel
Vue.component('downloadExcel', JsonExcel)

3、直接使用

<!--
    :data="tableData" // 要导出的数据   :fields="json_fields" // 导出数据的配置
    :header="title" // 导出的标题       name="订单.xls" // 导出的表格名字及格式
-->
<download-excel style="margin-right: 10px;" class="export-excel-wrapper" :data="tableData" :fields="json_fields" :header="title" name="订单.xls">
    <el-button >导 出</el-button>
</download-excel>    

<------------------------------------分割---------------------------------------->
配置项
json_fields: { // 导出对应表格头部以及数据
    "id":'id',
    "名字":'name',
    "颜色":'amount1',
    "字段1":'amount2',
    "字段2":'amount3',
},

4、完整代码直接复制即可

<template>
    <div>
        <!--
            :data="tableData" // 要导出的数据
            :fields="json_fields" // 导出数据的配置
            :header="title" // 导出的标题
            name="订单.xls" // 导出的表格名字及格式
        -->
        <download-excel style="margin-right: 10px;" class="export-excel-wrapper" :data="tableData" :fields="json_fields" :header="title" name="订单.xls">
            <el-button >导 出</el-button>
        </download-excel>    

        <el-table :data="tableData" border height="200" style="width: 100%; margin-top: 20px">
            <el-table-column prop="id" label="ID" width="180"></el-table-column>
            <el-table-column prop="name" label="姓名"></el-table-column>
            <el-table-column prop="amount1" label="数值 1（元）"></el-table-column>
            <el-table-column prop="amount2" label="数值 2（元）"></el-table-column>
            <el-table-column prop="amount3" label="数值 3（元）"></el-table-column>
        </el-table>
    </div>

</template>

<script>
export default {
    data() {
        return {
            title: "标题",// 
            json_fields: { // 导出对应表格头部以及数据
                "id":'id',
                "名字":'name',
                "颜色":'amount1',
                "字段1":'amount2',
                "字段2":'amount3',
            },
            tableData: [
                {
                    id: '12987122',
                    name: '王小虎',
                    amount1: '234',
                    amount2: '3.2',
                    amount3: 10
                },
                {
                    id: '12987123',
                    name: '王小虎',
                    amount1: '165',
                    amount2: '4.43',
                    amount3: 12
                },
                {
                    id: '12987124',
                    name: '王小虎',
                    amount1: '324',
                    amount2: '1.9',
                    amount3: 9
                },
                {
                    id: '12987125',
                    name: '王小虎',
                    amount1: '621',
                    amount2: '2.2',
                    amount3: 17
                },
                {
                    id: '12987126',
                    name: '王小虎',
                    amount1: '539',
                    amount2: '4.1',
                    amount3: 15
                }
            ]
        };
    },
    methods: {}
};
</script>

<style></style>

<------------------------------------分割-------------------------------------------->

多级表头导出

1、还是npm下载依赖

npm install --save xlsx file-saver

2、要新建一个文件，Export2Excel.js 文件，我是从某个网址下载的，我给忘了，这里我直接复制过来,在src目录下创建文件夹vendor，然后创建Export2Excel.js文件

/* eslint-disable */
import {
    saveAs
} from 'file-saver'
import XLSX from 'xlsx'

function generateArray(table) {
    var out = [];
    var rows = table.querySelectorAll('tr');
    var ranges = [];
    for (var R = 0; R < rows.length; ++R) {
        var outRow = [];
        var row = rows[R];
        var columns = row.querySelectorAll('td');
        for (var C = 0; C < columns.length; ++C) {
            var cell = columns[C];
            var colspan = cell.getAttribute('colspan');
            var rowspan = cell.getAttribute('rowspan');
            var cellValue = cell.innerText;
            if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;

            //Skip ranges
            ranges.forEach(function(range) {
                if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e
                    .c) {
                    for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
                }
            });

            //Handle Row Span
            if (rowspan || colspan) {
                rowspan = rowspan || 1;
                colspan = colspan || 1;
                ranges.push({
                    s: {
                        r: R,
                        c: outRow.length
                    },
                    e: {
                        r: R + rowspan - 1,
                        c: outRow.length + colspan - 1
                    }
                });
            };

            //Handle Value
            outRow.push(cellValue !== "" ? cellValue : null);

            //Handle Colspan
            if (colspan)
                for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
        }
        out.push(outRow);
    }
    return [out, ranges];
};

function datenum(v, date1904) {
    if (date1904) v += 1462;
    var epoch = Date.parse(v);
    return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}

function sheet_from_array_of_arrays(data, opts) {
    var ws = {};
    var range = {
        s: {
            c: 10000000,
            r: 10000000
        },
        e: {
            c: 0,
            r: 0
        }
    };
    for (var R = 0; R != data.length; ++R) {
        for (var C = 0; C != data[R].length; ++C) {
            if (range.s.r > R) range.s.r = R;
            if (range.s.c > C) range.s.c = C;
            if (range.e.r < R) range.e.r = R;
            if (range.e.c < C) range.e.c = C;
            var cell = {
                v: data[R][C]
            };
            if (cell.v == null) continue;
            var cell_ref = XLSX.utils.encode_cell({
                c: C,
                r: R
            });

            if (typeof cell.v === 'number') cell.t = 'n';
            else if (typeof cell.v === 'boolean') cell.t = 'b';
            else if (cell.v instanceof Date) {
                cell.t = 'n';
                cell.z = XLSX.SSF._table[14];
                cell.v = datenum(cell.v);
            } else cell.t = 's';

            ws[cell_ref] = cell;
        }
    }
    if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
    return ws;
}

function Workbook() {
    if (!(this instanceof Workbook)) return new Workbook();
    this.SheetNames = [];
    this.Sheets = {};
}

function s2ab(s) {
    var buf = new ArrayBuffer(s.length);
    var view = new Uint8Array(buf);
    for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
    return buf;
}

export function export_table_to_excel(id) {
    var theTable = document.getElementById(id);
    var oo = generateArray(theTable);
    var ranges = oo[1];

    /* original data */
    var data = oo[0];
    var ws_name = "SheetJS";

    var wb = new Workbook(),
        ws = sheet_from_array_of_arrays(data);

    /* add ranges to worksheet */
    // ws['!cols'] = ['apple', 'banan'];
    ws['!merges'] = ranges;

    /* add worksheet to workbook */
    wb.SheetNames.push(ws_name);
    wb.Sheets[ws_name] = ws;

    var wbout = XLSX.write(wb, {
        bookType: 'xlsx',
        bookSST: false,
        type: 'binary'
    });

    saveAs(new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    }), "test.xlsx")
}

export function export_json_to_excel({
    multiHeader = [],
    header,
    data,
    filename,
    merges = [],
    autoWidth = true,
    bookType = 'xlsx'
} = {}) {
    /* original data */
    filename = filename || 'excel-list'
    data = [...data]
    data.unshift(header);

    for (let i = multiHeader.length - 1; i > -1; i--) {
        data.unshift(multiHeader[i])
    }

    var ws_name = "SheetJS";
    var wb = new Workbook(),
        ws = sheet_from_array_of_arrays(data);

    if (merges.length > 0) {
        if (!ws['!merges']) ws['!merges'] = [];
        merges.forEach(item => {
            ws['!merges'].push(XLSX.utils.decode_range(item))
        })
    }

    if (autoWidth) {
        /*设置worksheet每列的最大宽度*/
        const colWidth = data.map(row => row.map(val => {
            /*先判断是否为null/undefined*/
            if (val == null) {
                return {
                    'wch': 10
                };
            }
            /*再判断是否为中文*/
            else if (val.toString().charCodeAt(0) > 255) {
                return {
                    'wch': val.toString().length * 2
                };
            } else {
                return {
                    'wch': val.toString().length
                };
            }
        }))
        /*以第一行为初始值*/
        let result = colWidth[0];
        for (let i = 1; i < colWidth.length; i++) {
            for (let j = 0; j < colWidth[i].length; j++) {
                if (result[j]['wch'] < colWidth[i][j]['wch']) {
                    result[j]['wch'] = colWidth[i][j]['wch'];
                }
            }
        }
        ws['!cols'] = result;
    }

    /* add worksheet to workbook */
    wb.SheetNames.push(ws_name);
    wb.Sheets[ws_name] = ws;

    var wbout = XLSX.write(wb, {
        bookType: bookType,
        bookSST: false,
        type: 'binary'
    });
    saveAs(new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    }), `${filename}.${bookType}`);
}

3、直接导出，看注释

<div style="padding: 20px 0;"><el-button size="small" type="primary" @click="handleDownload">导出EXCEl</el-button></div>
<!-- 这里是按钮 -->

// 下面是对应的方法
handleDownload1() {  // 手写导出导出
    import('@/vendor/Export2Excel').then(excel => {
        const multiHeader = [['物料信息', '', '', '', '', '', '', '上月库存', '', '','本月入库', '', '', '本月出库','', '', '本月库存', '', '']];// 这里是第一级的标题,合并的格子一定要用空写出来不能直接不写
        const header =['物料名称', '类型', '规格', '颜色', '厚度', '计量单位', '品牌', '单价', '数量', '金额','单价', '数量', '金额','单价', '数量', '金额','单价', '数量', '金额',] //第二行; // 最后一行的表头数据
        const filterVal = ['name', 'type', 'specification', 'color', 'thicknes', 'measuring_unit','brand','shang_cash','shang_inventory_num','shangyue_money',
        'brdanjia', 'brsum', 'brmoney',  'bcdanjia', 'bcsum', 'bcmoney',  'bdanjia', 'bsum', 'bmoney'];//这里代表字段与上面的对应起来
        this.tableData.forEach(item=>{// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!这里不用管我,我这里由于业务需要 修改了一下
            item.brdanjia = item.benyueruku.danjia
            item.brsum = item.benyueruku.sum
            item.brmoney = item.benyueruku.money
            item.bcdanjia = item.benyuechuku.danjia
            item.bcsum = item.benyuechuku.sum
            item.bcmoney = item.benyuechuku.money
            item.bdanjia = item.benyuekucun.danjia
            item.bsum =    item.benyuekucun.sum
            item.bmoney = item.benyuekucun.money
        })
        const list = this.tableData;// 数据  这里对应上面我修改好的数据
        const data = this.formatJson(filterVal, list);// 不用改
        const merges = ['A1:G1', 'H1:J1', 'K1:M1', 'N1:P1', 'Q1:S1']; // 合并单元格的数据，对应一级表头要合并的格子,可以自己打开Excel表格看看要合并的格子写上对应的字母
        const filename = '导出'
        excel.export_json_to_excel({
            multiHeader,
            header,
            merges,
            data,
            filename
        });
    });
},
formatJson(filterVal, jsonData) {
    return jsonData.map(v =>
        filterVal.map(j => {
            return v[j];
        })
    );
},

4、源代码 直接复制就行 小例子

<template>
    <div style="padding: 20px;">
        <div style="padding: 20px 0;"><el-button size="small" type="primary" @click="handleDownload">导出EXCEl</el-button></div>
        <el-table class="center-table" :data="tableData" size="small">
            <el-table-column prop="date" label="日期" width="150" />
            <el-table-column label="配送信息">
                <el-table-column prop="name" label="姓名" width="120" />
                <el-table-column label="地址">
                    <el-table-column prop="province" label="省份" width="120" />
                    <el-table-column prop="city" label="市区" width="120" />
                    <el-table-column prop="address" label="地址" />
                    <el-table-column prop="zip" label="邮编" width="120" />
                </el-table-column>
            </el-table-column>
        </el-table>
    </div>
</template>

<script>
export default {
    name: 'MergeHeader',
    data() {
        return {
            tableData: [
                {
                    date: '2016-05-03',
                    name: '王小虎',
                    province: '上海',
                    city: '普陀区',
                    address: '上海市普陀区金沙江路 1518 弄',
                    zip: 200333
                },
                {
                    date: '2016-05-02',
                    name: '王小虎',
                    province: '上海',
                    city: '普陀区',
                    address: '上海市普陀区金沙江路 1518 弄',
                    zip: 200333
                },
                {
                    date: '2016-05-04',
                    name: '王小虎',
                    province: '上海',
                    city: '普陀区',
                    address: '上海市普陀区金沙江路 1518 弄',
                    zip: 200333
                },
                {
                    date: '2016-05-01',
                    name: '王小虎',
                    province: '上海',
                    city: '普陀区',
                    address: '上海市普陀区金沙江路 1518 弄',
                    zip: 200333
                },
                {
                    date: '2016-05-08',
                    name: '王小虎',
                    province: '上海',
                    city: '普陀区',
                    address: '上海市普陀区金沙江路 1518 弄',
                    zip: 200333
                },
                {
                    date: '2016-05-06',
                    name: '王小虎',
                    province: '上海',
                    city: '普陀区',
                    address: '上海市普陀区金沙江路 1518 弄',
                    zip: 200333
                },
                {
                    date: '2016-05-07',
                    name: '王小虎',
                    province: '上海',
                    city: '普陀区',
                    address: '上海市普陀区金沙江路 1518 弄',
                    zip: 200333
                }
            ]
        };
    },

    methods: {
        handleDownload() {
            import('@/vendor/Export2Excel').then(excel => {
                const multiHeader = [
                    ['日期', '配送信息', '', '', '', ''], //第一行
                    ['', '姓名', '地址', '', '', ''] //第二行
                ]; // 前两行的表头数据，二维数组，不够的用空白补全
                const header = ['', '', '省份', '市区', '地址', '邮编']; // 最后一行的表头数据
                const filterVal = ['date', 'name', 'province', 'city', 'address', 'zip'];
                const list = this.tableData;
                const data = this.formatJson(filterVal, list);
                const merges = ['A1:A3', 'B1:F1', 'B2:B3', 'C2:F2']; // 合并单元格的数据，
                excel.export_json_to_excel({
                    multiHeader,
                    header,
                    merges,
                    data
                });
            });
        },
        formatJson(filterVal, jsonData) {
            return jsonData.map(v =>
                filterVal.map(j => {
                    return v[j];
                })
            );
        }
    }
};
</script>
<style>
.center-table td,
.center-table th {
    text-align: center;
}
</style>