vue导出excel表格并支持样式、行高修改xlsx&xlsx-style

前言

使用插件xlsx、xlsx-style导出带有样式的表格，两者都可以实现创建excel，但是xlsx免费版仅仅只能做单元格合并，收费版才能设置单元格样式，所以需要用到xlsx-style插件实现表格样式的修改

一、使用步骤

1. 安装环境

• vue: 2.5.22
• xlsx: 0.18.5
• xlsx-style: 0.8.13

2. 引入xlsx库

npm i xlsx xlsx-style -S

3. 修改js文件

这里我们需要两个文件：

• node_modules/xlsx/dist里的xlsx.extendscript.js

• node_modules/xlsx/dist里的xlsx.js

  修改xlsx.extendscript.js里所有XLSX变量为XLSX2，避免和xlsx.js里的变量冲突

  var XLSX = {};
  ...

  修改xlsx.js里的方法，使导出支持自定义行高功能

  // 增加行高设置
  var DEF_PPI = 96, PPI = DEF_PPI;
  function px2pt(px) { return px * 96 / PPI; }
  function pt2px(pt) { return pt * PPI / 96; }
  function write_ws_xml_data(ws, opts, idx, wb) {
    var o = [], r = [], range = safe_decode_range(ws['!ref']), cell="", ref, rr = "", cols = [], R=0, C=0, rows = ws['!rows'];
    var dense = Array.isArray(ws);
    var params = ({r:rr}), row, height = -1;
    for(C = range.s.c; C <= range.e.c; ++C) cols[C] = encode_col(C);
    for(R = range.s.r; R <= range.e.r; ++R) {
        r = [];
        rr = encode_row(R);
        for(C = range.s.c; C <= range.e.c; ++C) {
            ref = cols[C] + rr;
            var _cell = dense ? (ws[R]||[])[C]: ws[ref];
            if(_cell === undefined) continue;
            if((cell = write_ws_xml_cell(_cell, ref, ws, opts, idx, wb)) != null) r.push(cell);
        }
        if(r.length > 0 || (rows && rows[R])) {
            params = ({r:rr});
            if(rows && rows[R]) {
                row = rows[R];
                if(row.hidden) params.hidden = 1;
                height = -1;
                if (row.hpx) height = px2pt(row.hpx);
                else if (row.hpt) height = row.hpt;
                if (height > -1) { params.ht = height; params.customHeight = 1; }
                if (row.level) { params.outlineLevel = row.level; }
            }
            o[o.length] = (writextag('row', r.join(""), params));
        }
    }
    if(rows) for(; R < rows.length; ++R) {
        if(rows && rows[R]) {
            params = ({r:R+1});
            row = rows[R];
            if(row.hidden) params.hidden = 1;
            height = -1;
            if (row.hpx) height = px2pt(row.hpx);
            else if (row.hpt) height = row.hpt;
            if (height > -1) { params.ht = height; params.customHeight = 1; }
            if (row.level) { params.outlineLevel = row.level; }
            o[o.length] = (writextag('row', "", params));
        }
    }
    return o.join("");
  }

  创建自定义的js文件export2Excel.js 内容如下

  // 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
  function sheet2blob(sheet, sheetName) {
    sheetName = sheetName || 'sheet1';
    var workbook = {
        SheetNames: [sheetName],
        Sheets: {}
    };
    workbook.Sheets[sheetName] = sheet; // 生成excel的配置项
   
    var wopts = {
        bookType: 'xlsx', // 要生成的文件类型
        bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
        type: 'binary'
    };
    var wbout = XLSX.write(workbook, wopts);
    var blob = new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    }); // 字符串转ArrayBuffer
    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;
    }
    return blob;
  }
   
  // 将workbook装化成blob对象,可支持多个sheet
  function workbook2blob(workbook) {
    // 生成excel的配置项
    var wopts = {
        // 要生成的文件类型
        bookType: "xlsx",
        // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
        bookSST: false,
        type: "binary"
    };
    var wbout = XLSX.write(workbook, wopts);
    // 将字符串转ArrayBuffer
    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;
    }
    var blob = new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    });
    return blob;
  }
  /**
   * 通用的打开下载对话框方法
   * @param url 下载地址，也可以是一个blob对象，必选
   * @param saveName 保持文件名，可选
   */
  function openDownloadDialog(url, saveName) {
    if (typeof url == 'object' && url instanceof Blob) {
        // 创建blob地址
        url = URL.createObjectURL(url);
    }
    var aLink = document.createElement('a');
    aLink.href = url;
    // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
    aLink.download = saveName || '';
    var event;
    if (window.MouseEvent) event = new MouseEvent('click');
    else {
        event = document.createEvent('MouseEvents');
        event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
    }
    aLink.dispatchEvent(event);
  }
  // 为合并项添加边框
  function addRangeBorder(range, ws) {
    let arr = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
    range.forEach(item => {
        let startColNumber = Number(item.s.r), endColNumber = Number(item.e.r);
        let startRowNumber = Number(item.s.c), endRowNumber = Number(item.e.c);
        const test = ws[arr[startRowNumber] + (startColNumber + 1)];
        for (let col = startColNumber; col <= endColNumber; col++) {
            for (let row = startRowNumber; row <= endRowNumber; row++) {
                ws[arr[row] + (col + 1)] = test;
            }
        }
    })
    return ws;
  }

  4. index.html引入

  <script src="xlsx.js"></script>
  <script src="export2Excel.js"></script>
  <script src="xlsx.extendscript.js"></script>

  5. vue代码

  <template>
    <div class="config-form">
        <el-button @click="exportExcel">下载文档</el-button>
    </div>
  </template>
  <script>
    export default {
        data() {
            return {
                value: [20, 50],
                excelAoa: [
                    ['XXXXXXXXXX记录表', null, null, null, null, null, null],
                    ['与会者', null, null, null, null, null, null]
                    ['序号', '姓名', '性别', '年龄', '修改人', '修改日期', '提交日期']
                ]
            }
        },
        methods: {
            exportExcel() {
                const aoa = [
                    ...this.excelAoa
                ];
                //这个就是修改格式的代码
                sheet["A1"].s = {
                    font: {
                        sz: 14,
                        bold: true,
                        color: {
                            rgb: "0000FF"
                        }
                    },
                    fill: {
                        bgColor: {
                            indexed: 64
                        },
                        fgColor: {
                            rgb: "FFFF00"
                        }
                    }
                }; //<====设置xlsx单元格样式
                const sheet = this.xlsxAddStyle(aoa);
                openDownloadDialog(sheet2blob(sheet), '导出.xlsx');
            },
            // 表格样式设置
            xlsxAddStyle(aoa) {
                // 注意XLSX2变量
                const sheet = XLSX2.utils.aoa_to_sheet(aoa);
                // 合并单元格 s 开始坐标  e结束坐标  c-x轴  r-y轴
                const mergeArr = [
                    {s: {c: 0, r: 0}, e: {c: 6, r: 0}},
                    {s: {c: 1, r: 1}, e: {c: 6, r: 1}}
                ]
                const rowH = []; // 表格每列高度
                // 单元格外侧有框线
                const borderAll = {
                    top: { style: "thin" },
                    bottom: { style: "thin" },
                    left: { style: "thin" },
                    right: { style: "thin" },
                };
                // 单元格外侧无框线
                const noBorder = {
                    top: { style: "" },
                    bottom: { style: "" },
                    left: { style: "" },
                    right: { style: "" },
                };
                for (const key in sheet) {
                    if (Object.hasOwnProperty.call(sheet, key)) {
                        const element = sheet[key];
                        if (typeof element === "object") {
                            const index = Number(key.slice(1)) - 1;
                            rowH[index] = { hpx: 30 };
                            element.s = {
                                alignment: {
                                    horizontal: "center", // 所有单元格右对齐
                                    vertical: "center", // 所有单元格垂直居中
                                    wrapText: true // 换行
                                },
                                font: {
                                    name: "微软雅黑",
                                    sz: 10,
                                    italic: false,
                                    underline: false,
                                },
                                border: borderAll,
                                fill: {
                                    fgColor: { rgb: "FFFFFFFF" },
                                },
                            };
                            // 标题的样式
                            if (['A1'].includes(key)) {
                                element.s.alignment.horizontal = 'center';
                                element.s.font.bold = true;
                                element.s.font.sz = 15;
                                element.s.fill.border = noBorder;
                            }
                        }
                    }
                }
  
                // 单元格的列宽
                sheet["!cols"] = [
                    { wpx: 60 }, 
                    { wpx: 100 },
                    { wpx: 120 }, 
                    { wpx: 120 }, 
                    { wpx: 100 }, 
                    { wpx: 100 },
                    { wpx: 100 }
                ];
                sheet["!rows"] = rowH;
                sheet["!merges"] = mergeArr;
                // 合并项添加边框
                const sheetNew = addRangeBorder(sheet['!merges'], sheet);
                return sheetNew
            }
        }
    }
  </script>