利用xlsx-syle前端导出excel且支持自定义样式

前言

本文的代码是基于react的。
本文仅用于记录我在前端导出excel遇到的问题的笔记整理。

需求描述

需要前端来实现对数据的导出，导出成excel格式。
excel打开后的样式要符合需求图
需求图：

导出1.png

前期解决过程

• 我在网上找到前端用于导出的插件js-xlsx，按照操作说明鼓捣完成后导出excel的样子只是最基础的模板

导出2.png

• 当我想进一步去修改样式，让他达到需求那样，结果发现xlsx插件开源版不支持修改excel的样式，需要付费版才有支持功能，然而付费要700美元，果断放弃。
• 后来我找到了xlsx-style插件可以代替，它支持修改样式且是免费的。

中期解决过程

• 然而xlsx-style插件不像js-xlsx插件那样乖巧，在安装和引入阶段也会有各种问题
• import引入xlsx-stle时一般都会报错：This relative module was not found: ./cptable in ./node_modules/xlsx-style@0.8.13@xlsx-style/dist/cpexcel.js
  这个问题需要去修改xlsx-style插件里的源码，即要改动node_modules文件夹。
• 然而对于非本地demo的项目来说，node_modules因是存放依赖的，且很大，代码提交或上传时都会忽略node_modules，需要使用时才会安装依赖，就算改动xlsx-style的源码，依赖重新安装后也会被覆盖掉。
• 这个问题可以藉由将这个依赖的代码从node_modules拿出来放在项目里，在最外层index.html里引入即可。
• 然而对于高版本的Hzero，最外层的index.html文件被隐藏了，无法引入，需要把node_modules/hzero-boot/public/index.html的index.html文件复制到外层public文件夹下，才可以使用。
  此时xlsx-style插件已经全局引入了，可以开始写导出功能了。

导出功能的代码

导出的方法

function saveAs(obj, fileName) {
    const tmpa = document.createElement('a');
    tmpa.download = fileName || '未命名';
    // 兼容ie
    if ('msSaveOrOpenBlob' in navigator) {
      window.navigator.msSaveOrOpenBlob(obj, '下载的文件名' + '.xlsx');
    } else {
      tmpa.href = URL.createObjectURL(obj);
    }
    tmpa.click();
    setTimeout(function() {
      URL.revokeObjectURL(obj);
    }, 100);
  }

function s2ab(s) {
    if (typeof ArrayBuffer !== 'undefined') {
      const buf = new ArrayBuffer(s.length);
      const view = new Uint8Array(buf);
      for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
      return buf;
    } else {
      const buf = new Array(s.length);
      for (let i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xff;
      return buf;
    }
  }

// 获取26个英文字母用来表示excel的列
  function getCharCol(n) {
    const temCol = '';
    let s = '';
    let m = 0;
    while (n > 0) {
      m = (n % 26) + 1;
      s = String.fromCharCode(m + 64) + s;
      n = (n - m) / 26;
    }
    return s;
  }

function downloadExl(json, type, options) {
    var tmpdata = json[0];
    // 定制化改动地方
    json.unshift({}, {}, {}, {}); // 向表格数据中插入4行位置（标题和参数）
    const keyMap = []; // 获取keys
    for (const k in tmpdata) { // 为插入的4行位置添加数据
      keyMap.push(k);
      // 定制化改动地方
      json[3][k] = k; // json[3][k] = k  3为插入4行的最后一行索引，用于展示列头
    }
    var tmpdata = []; // 用来保存转换好的json
    json
      .map((v, i) => {
        const data = keyMap.map((k, j) => {
          return Object.assign(
            {},
            {
              v: v[k],
              position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 2), // 表格数据的位置
            }
          );
        });
        return data;
      })
      .reduce((prev, next) => prev.concat(next))
      .forEach(
        (v, i) =>
          (tmpdata[v.position] = {
            v: v.v,
          })
      );
    let outputPos = Object.keys(tmpdata); // 设置区域,比如表格从A1到D10
    // 定制化改动地方
    tmpdata.A1 = { v: options.dataTitle };  // A1-A4区域的内容
    tmpdata.A2 = { v: options.reportCompany };
    tmpdata.A3 = { v: options.date };
    tmpdata.A4 = { v: options.reportType };
    // 定制化改动地方
    outputPos = ['A1'].concat(['A2'], ['A3'], ['A4'], outputPos);
    // 定制化改动地方
    tmpdata.A1.s = {
      font: { sz: 14, bold: true, vertAlign: true },
      alignment: { vertical: 'center', horizontal: 'center' }, // 垂直、水平
      fill: { bgColor: { rgb: 'E8E8E8' }, fgColor: { rgb: 'E8E8E8' } },
    }; // <====设置xlsx单元格样式
    tmpdata.A2.s = {
      font: { sz: 12, bold: true, vertAlign: true },
      alignment: { vertical: 'center', horizontal: 'bottom' },
    }; // <====设置xlsx单元格样式
    tmpdata.A3.s = {
      font: { sz: 12, bold: true, vertAlign: true },
      alignment: { vertical: 'center', horizontal: 'bottom' },
    }; // <====设置xlsx单元格样式
    tmpdata.A4.s = {
      font: { sz: 12, bold: true, vertAlign: true },
      alignment: { vertical: 'center', horizontal: 'bottom' },
    }; // <====设置xlsx单元格样式
    // s-e 代表区域 c-r 代表列-行的索引
    // 定制化改动地方
    tmpdata['!merges'] = [
      {
        s: { c: 0, r: 0 },
        e: { c: 3, r: 0 },
      },
      {
        s: { c: 0, r: 1 },
        e: { c: 3, r: 1 },
      },
      {
        s: { c: 0, r: 2 },
        e: { c: 3, r: 2 },
      },
      {
        s: { c: 0, r: 3 },
        e: { c: 3, r: 3 },
      },
    ]; // <====合并单元格
    let dataArrWidth = []
    // 定制化改动地方
    json.forEach(item => {
      dataArrWidth.push({ wpx: 100 })
    })
    tmpdata['!cols'] = dataArrWidth;// <====设置一列宽度， 代表20列都是300宽
    const tmpWB = {
      SheetNames: ['mySheet'], // 保存的表标题
      Sheets: {
        mySheet: Object.assign(
          {},
          tmpdata, // 内容
          {
            '!ref': `${outputPos[0]}:${outputPos[outputPos.length - 1]}`, // 设置填充区域（表格渲染区域）
          }
        ),
      },
    };
    const tmpDown = new Blob(
      [
        s2ab(
          XLSX.write(
            tmpWB,
            { bookType: type == undefined ? 'xlsx' : type.bookType, bookSST: false, type: 'binary' } // 这里的数据是用来定义导出的格式类型
          )
        ),
      ],
      {
        type: '',
      }
    );
    // 定制化改动地方
    saveAs(tmpDown, `${'超合同清账&明细' + '.'}${type.bookType == 'biff2' ? 'xls' : type.bookType}`);
  }
  function s2ab(s) {
    if (typeof ArrayBuffer !== 'undefined') {
      const buf = new ArrayBuffer(s.length);
      const view = new Uint8Array(buf);
      for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
      return buf;
    } else {
      const buf = new Array(s.length);
      for (let i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xff;
      return buf;
    }
  }

downloadExl方法里进行excel导出后的样式自定义
所有注释 // 定制化改定地方处即为样式自定义时需要改动的内容，
常用样式处也在代码中进行了注释。

导出方法的使用

function handleClick() {
   // 模拟数据
    // 定制化改动地方
    let data = [];
    for (let i = 0; i < 10; i++) {
      let obj = {
        '合同编号': '移YZ合同[2017]1491号',
        '合同所在地市': '扬州',
        '合同名称': '扬州分公司与仪征技师学院签订的仪征技师学院室内分布系统合作协议书',
        '是否补充协议': '否',
        '原合同编号': '',
        '原合同名称': '',
        '供应商编号': 'MDM_200123316',
        '供应商名称': '乐山市中心城区星飞探汽车装饰用品店',
        '是否集采': 'PURCHASING_03',
        '主执行部门名称': '江苏\扬州\仪征分公司\技术业务支撑中心',
        '合同性质': 'SINGLE_CONTRACT',
        '合同性质': '单项合同',
        '关联类型': '未知',
        '收支类型': '',
        '相对方类型': '供应商',
        '合同状态': 7,
        '省公司所在部门': '江苏\扬州\仪征分公司',
        '合同总金额': 0,
        '调整后合同总金额': '',
        '在途支付金额': 0,
        '合同累计报账金额': 217158.15,
        '合同累计支付金额': 217158.15,
        '超付金额=累计支付-合同总金额': 217158.15,
        '期初累计报账金额': 91472.7,
        '期初累计支付金额': 91472.7,
        '总计': 125685.45,
      };
      data.push(obj);
    }
      // 表格标题
      // 定制化改动地方
      const options = {
        dataTitle: '超合同清账&明细',
        reportCompany: `报账公司: XXX`,
        date: `日期: XXXX-XX-XX`,
        reportType: `报账单类型: XXX`,
      }
      // 配置文件类型
      const wopts = { bookType: 'xlsx', bookSST: true, type: 'binary', cellStyles: true };
      downloadExl(data, wopts, options);
    }

options 配置非表格数据的所有内容
然后在downloadExl方法中对options的内容进行样式排版
如此样式不能满足所需，修改 // 定制化改定地方注释处即可，
更多内容可以参考官网文档 https://www.npmjs.com/package/xlsx-style
最后上效果图：

导出3.png

总结

因为时间原因，本文只说了下xlsx-style插件的使用前置和给出了个导出demo的部分代码，其实这个导出还有很多问题没有处理，比如导出20列5W条数据时就会程序崩溃，数据越多导出越慢，理想是1-2万条。导出后的excel中数字不是数字格式，需要手动修改成数字。这个导出功能我现在还只是做了个demo，很多数据都是写死的，等这段时间忙完后，我会抽空把导出功能抽离成组件来更方便使用的

image.png