Vue导出表格数据、xlsx-style样式编辑

一、导出数据

　   方案一

https://blog.csdn.net/xiaoshihoukediaole/article/details/81296327    相当详细的一篇文档

 

　　方案二：插件vue-json-excel（vue）

　　参考：https://blog.csdn.net/junkaicool/article/details/96423061

　　1.安装依赖

npm install vue-json-excel -S

　　

　　2.main.js引入并注册

import JsonExcel from 'vue-json-excel'
Vue.component('downloadExcel', JsonExcel)

　　

　　3.页面中使用

        <download-excel :data="json_data" :fields="json_fields" name="filename.xls">
            <button>导出EXCEL</button>
        </download-excel>

　　　　data() {
            return {
                json_fields: {  
                    "名称": "name",    // “名称”为表格内列头，“name”为表格数据json_data对应的键
                    "电话": "phone", 
                    "年龄": "custom.age", // 支持嵌套属性
                    "自定义": {
                        field: "custom.number",
                        //自定义回调函数
                        callback: value => {
                            return `number - ${value}`;
                        }
                    }
                },
                json_data: [    // 表格数据
                    {
                        name: "张三",
                        phone: "188****8888",
                        custom: {
                            age: "11",
                            number: 2222
                        }
                    }
                ],
            }
        }

　　下载的表格效果：

 

 二、合并单元格、背景字体、换行，插件xlsx-style（vue）

　　参考：https://www.cnblogs.com/yinxingen/p/11052184.html

　　1.安装依赖

　　xlsx插件同样可以导出excel，xlsx-style在xlsx的基础上还能修改表格样式

npm i xlsx-style -S

 

　　2.安装依赖后直接使用会出现报错，这里给出三种解决方案

　　2.1 方案一：修改依赖包源码，将\node_modules\xlsx-style\dist\cpexcel.js 807行 的 var cpt = require(’./cpt’ + ‘able’); 改成 var cpt = cptable;（弊端：每次拉包时都需要修改）

　　

　　2.2 方案二：复制依赖包该文件

 　　在index.html中引入

    <script src="./static/xlsx.core.min.js"></script>

 　　

　　2.3 方案三：修改webpack配置（参考：https://blog.csdn.net/sunyv1/article/details/108601772）

module.exports = {
    externals: {
        './cptable': 'var cptable'
     }
}

　　

　　3.在src下创建vendor/excelOut.js

　　

　　excelOut.js（可直接使用，根据需要进行调试相应逻辑参数即可）

// excelOut.js

// 这个可以单独搞个文件，文件名excelOut，在这个文件夹下vendor/excelOut.js
/* eslint-disable */
require('script-loader!file-saver');   
// import XLSX from 'xlsx-style'    // 方案一、方案三

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['!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({
    title,
    multiHeader = [],
    header,
    data,
    filename,
    merges = [],
    autoWidth = true,
    bookType = 'xlsx',
    myRowFont = '1'
} = {}) {
    /* original data */
    filename = filename || 'excel-list'
    data = [...data]
    data.unshift(header);
    data.unshift(title);
    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 dataInfo = wb.Sheets[wb.SheetNames[0]];

    const borderAll = {  //单元格外侧框线
        top: {
            style: 'thin'
        },
        bottom: {
            style: 'thin'
        },
        left: {
            style: 'thin'
        },
        right: {
            style: 'thin'
        }
    };
    //给所以单元格加上边框
    for (var i in dataInfo) {
        if (i == '!ref' || i == '!merges' || i == '!cols' || i == 'A1') {

        } else {
            dataInfo[i + ''].s = {
                border: borderAll
            }
        }
    }
    // 去掉标题边框
    let arr = ["A1", "B1", "C1", "D1", "E1", "F1", "G1", "H1", "I1", "J1", "K1", "L1", "M1", "N1", "O1", "P1", "Q1", "R1", "S1", "T1", "U1", "V1", "W1", "X1", "Y1", "Z1"];
    arr.some(v => {
        // console.log(v)
        let a = merges[0].split(':')
        // console.log(a)
        if (v == a[1]) {
            console.log(dataInfo[v])
            dataInfo[v].s = {}
            return true;
        } else {
            console.log(dataInfo[v])
            dataInfo[v]
            dataInfo[v].s = {}
        }
    })

    //设置某单元格的样式
    dataInfo["A1"].s = {
        font: {
            // name: '宋体',    // 字体
            sz: 20, // 字体大小
            color: { rgb: "000000" },   // 字体颜色
            bold: true, // 粗体
            italic: false,  // 斜体
            underline: false    // 下划线
        },
        alignment: {
            horizontal: "center",  // 水平垂直 
            vertical: "center",
            wrapText:1  // 自动换行，换行字符："\r\n"
        },
        fill: {
            fgColor: { rgb: "008000" },
        },
    };
    // 这是表头行的样式
    var tableTitleFont = {
        font: {
            name: '宋体',
            sz: 18,
            color: { rgb: "ff0000" },
            bold: true,
            italic: false,
            underline: false
        },
        border: borderAll,
        alignment: {
            horizontal: "center",
            vertical: "center"
        },
        fill: {
            fgColor: { rgb: "008000" },
        },
    };

    for (var b in dataInfo) {
        if (b.indexOf(myRowFont) > -1) {
            if(dataInfo[b].v){
                dataInfo[b].s = tableTitleFont
            }
        }
    }

    // console.log(merges);
    // console.log(dataInfo);

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

 

　　4.使用示例

<button @click="handleDownload">下载模板</button>

　　

methods: {
            formatJson(filterVal, jsonData) {
                return jsonData.map(v => filterVal.map(j => v[j]))
            },
            // 导出模板
            handleDownload() {
                import('@/vendor/excelOut').then(excel => {
                    //表头
                    const tHeader = ['船名', '船长', '货种', '载重吨', '净吨', '锚地', '预抵时间', '下锚时间', '预靠泊位'] 
                    //标题
                    const title = ['锚地船舶', '', '', '', '', '', '', '', '']  
                    //表头对应字段
                    const filterVal = ['NAME', 'VESSEL_LENGTH', 'CARGO_NAME', 'DEADWEIGHT_TONNAGE', 'NET_TONNAGE', 'ANCHORAGE_ID', 'EXP_ARCHORAGE_TIME', 'AC_ARCHORAGE_TIME', 'RECOMMEND_BERTH']
                    const list = [{'NAME':'泰坦尼克号','VESSEL_LENGTH':'杰克'}]
                    const data = this.formatJson(filterVal, list)
                    data.map(item => {
                        // console.log(item)
                        item.map((i, index) => {
                            if (!i) {
                                item[index] = ''
                            }
                        })
                    })
                    const merges = ['A1:I1']  //合并单元格
                    excel.export_json_to_excel({
                        title: title,
                        header: tHeader,
                        data,
                        merges,
                        filename: '锚地船舶',   // 文件名
                        autoWidth: true,
                        bookType: 'xlsx'
                    })
                })

            },
        },

　　下载的表格效果：

 

 　　5.表格行高（xlsx-style）

　　参考：https://blog.csdn.net/qq_39738977/article/details/102948222　　

　　xlsx-style并没有封装行高设置，但是xlsx中是有哒，如果需要xlsx-style的样式又需要修改行高的话

　　需要去依赖包中，将xlsx-style下的xlsx.js中的write_ws_xml_data方法，用xlsx依赖包下的xlsx.js中的write_ws_xml_data方法及 相关参数 进行覆盖，其实就是把xlsx中的行高逻辑代码搬过来

　　在excelOut.js中的使用示例：

　　 ws['!rows'] = [{hpx: 120}]  // 设置表格第一行高度为120

　　最后xlsx-style>xlsx.js下的write_ws_xml_data：

 　　关于表格行高，如果嫌此操作麻烦，毕竟每次拉项目都需要修改依赖包，那么直接使用lixin-xlsx-style依赖包即可，注意excelOut.js中的‘xlsx-style’同时也要修改为‘lixin-xlsx-style’