涉及到合并单元+去网格线+总列数不固定的情况

使用的是xlsx文档，使用方法等同于SheetJS中文文档 。

• 最终效果

  
  image.png

步骤1：安装xlsx及xlsx

cnpm install xlsx --save
cnpm install xlsx-style --save

image.png

步骤2：修改xlsx-style插件包的代码

• 修改1：找到 node_modules-----> xlsx-style---->dist---->cpexcel.js

807行将 var cpt = require('./cpt' + 'able');改为var cpt = cptable;

image.png

• 修改2：找到 node_modules-----> xlsx-style---->ods.js

10行将 return require('../' + 'xlsx').utils;改为return require('./' + 'xlsx').utils;

image.png

• 修改3：如果你的excel想要设置行高功能的话，xlsx-style是无法设置的，但是该功能xlsx是有的，因此将相关功能代码复制粘贴到xlsx-style中就可以让使其具有设置行高的功能。
  找到xlsx下的xlsx.js,复制如下两个截图代码到xlsx-style下的xlsx.js中

var DEF_PPI = 96, PPI = DEF_PPI;
function px2pt(px) { return px * 96 / PPI; }
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("");
}

image.png
image.png
image.png

步骤3：使用

  <script>
  import * as XLSX from 'xlsx' 
  import xlsxStyleObj from 'xlsx-style'
  export default {
    data() {
      return {
        maxNum:0,
        globalRowArray: [],
        // 网络数据，其中couponDetailList和otherCouponDetailList包含的数据的个数，最终决定有多少列数据
        allOrderListAll: [
          { "originalPayment": 251, "totalDiscountMoney": 22, "payment": 229, "couponName": "游泳消费券3", "discountMoney": 50, "enterpriseCouponName": "测试券1", "enterpriseCouponDiscountMoney": 22, "originalPrice": 360, "couponDetailList": [], "couponsDiscountMoney": 0, "otherCouponDetailList": [], "otherCouponsDiscountMoney": 50, "allInPayOrderNo": null, "price": 251, "title": null, "num": null, "accountNo": null, "benefitCategoryCode": null, "benefitCategoryName": null, "storeName": null, "couponList": null, "orderInfo": null, },
          { "originalPayment": 251, "totalDiscountMoney": 110, "payment": 141, "couponName": "体育消费券1", "discountMoney": 10, "enterpriseCouponName": null, "enterpriseCouponDiscountMoney": null, "originalPrice": 360, "couponDetailList": [{ "couponNo": "23J4J5HHLU ", "isOnline": null, "couponName": "体育消费券66", "discountMoney": 50, "couponId": null, "userId": null, "managerId": null }], "couponsDiscountMoney": 0, "otherCouponDetailList": [{ "couponNo": "1NAUJ885A3", "isOnline": null, "couponName": "体育消费券77", "discountMoney": 60, "couponId": null, "userId": null, "managerId": null }], "otherCouponsDiscountMoney": 50, "allInPayOrderNo": null, "price": 251, "title": null, "num": null, "accountNo": null, "benefitCategoryCode": null, "benefitCategoryName": null, "storeName": null, "couponList": null, "orderInfo": null, },
          { "originalPayment": 251, "totalDiscountMoney": 73, "payment": 178, "couponName": "体育消费券6", "discountMoney": 50, "enterpriseCouponName": "测试券34", "enterpriseCouponDiscountMoney": 43, "originalPrice": 360, "couponDetailList": [{ "couponNo": "4R1O833EUI", "isOnline": null, "couponName": "体育消费券5", "discountMoney": 30, "couponId": null, "userId": null, "managerId": null }], "couponsDiscountMoney": 0, "otherCouponDetailList": [], "otherCouponsDiscountMoney": 50, "allInPayOrderNo": null, "price": 251, "title": null, "num": null, "accountNo": null, "benefitCategoryCode": null, "benefitCategoryName": null, "storeName": null, "couponList": null, "orderInfo": null, }
        ]
      };
    },
    methods: {
       // 按钮点击
      exportBtn() {
          // 1、数据源设置
          for (var i = 0; i < this.allOrderListAll.length; i++) {// 动态向数据源中添加带有索引的couponName,discountMoney属性 并赋值，为的是在excel列中取出该属性的属性值并展示
            var list1 = [...this.allOrderListAll[i].couponDetailList];
            var list2 = [...this.allOrderListAll[i].otherCouponDetailList];
  
            if (list1.length || list2.length) {
              var list3 = [];
              list1.forEach(item => {
                list3.push(item);
              });
              list2.forEach(item => {
                list3.push(item);
              });
              for (var j = 0; j < list3.length; j++) {           
                // 必须修改数据源，这样导出的时候，自动匹配数据源中的key
                this.allOrderListAll[i]['couponName' + (j + 1)] = list3[j] ? list3[j].couponName : '';
                this.allOrderListAll[i]['discountMoney' + (j + 1)] = list3[j].discountMoney ? list3[j].discountMoney : '';
              }
            }
          }
  
          var couponCountList = [];
          for (var i = 0; i < this.allOrderListAll.length; i++) {// 统计数据中，最多使用了几张券
            couponCountList.push(this.allOrderListAll[i].couponDetailList.length + this.allOrderListAll[i].otherCouponDetailList.length);
          }
          console.log('couponCountList---',couponCountList);
          this.maxNum = Math.max(...couponCountList);// 所有数据中，找到每条数据中最多使用的券的张数. 用于计算excel中一共多少列数据
  
  
  
          // 2、表头设置
          let rowArray = [ // 3行
            ["核销结算表"],
            ["年  月  日    至", '', ''],
            ["商品原价(元)", "总优惠(元)","实付金额(元)"],
          ];

          for (var i = 0; i < this.maxNum; i++) {
            rowArray[1].push('');
            rowArray[1].push('');
            rowArray[2].push('惠民券名称' + (i + 1) + '');
            rowArray[2].push('惠民券金额' + (i + 1) + '(元)');  
          }
          rowArray[1].push('');
          rowArray[1].push('    年  月  日');
          rowArray[2].push('企业券名称');
          rowArray[2].push('企业券金额');

  
          // 3、表头对应字段设置
          var keyList = this.allOrderListAll.map((x, index) => {
          var tempList = [x.originalPayment, x.totalDiscountMoney, x.payment];

            for (var i = 0; i < this.maxNum; i++) {
              tempList.push(x["couponName" + (i + 1)]);
              tempList.push(x["discountMoney" + (i + 1)]);
            }
            tempList.push(x["enterpriseCouponName"]);
            tempList.push(x["enterpriseCouponDiscountMoney"]);
            return tempList;
  
          })
          var finalList = [];
          for (var i = 0; i < keyList.length; i++) {
            var dealList = [];
            for (var j = 0; j < keyList[0].length; j++) {
              dealList.push(keyList[i][j] == null ? '' : keyList[i][j]);
            }
            finalList.push(dealList);
          }

          const body = finalList;
  
          body.unshift(...rowArray);
          this.globalRowArray = rowArray;
          var monenyList = [];

  
          monenyList.push('汇总金额合计：');
          monenyList.push('');
          monenyList.push('');
  
          for (var i = 0; i < this.maxNum; i++) {
            monenyList.push('');
            monenyList.push('');
          }
          monenyList.push('');
          monenyList.push('');
 
          body.push(monenyList)

          var footerList = [];
          footerList.push('填表人：');
          footerList.push('');
          footerList.push('时间：');
          footerList.push('');
          footerList.push('单位：（盖章）');
          footerList.push('');
          body.push(footerList)
          const sheet = this.addStyleFunc(body);
          this.downloadFunc(this.sheetToBlobFunc(sheet),  rowArray[0] + '.xlsx');
      },

      // 将Blob下载到本地
      downloadFunc(data, filename) {
        if (window.navigator && window.navigator.msSaveOrOpenBlob) {// IE浏览器使用 msSaveOrOpenBlob()方法下载
            window.navigator.msSaveOrOpenBlob(data, filename)// 兼容IE
        } else {// 其他浏览器
            const url = URL.createObjectURL(data);// 创建URL对象
            const link = document.createElement('a');// 创建a标签
            link.href = url;// 给a标签添加超链接
            link.download = filename;
            document.body.appendChild(link);// 模拟点击链接实现下载操作
            link.click();
            document.body.removeChild(link);
            URL.revokeObjectURL(url); // 释放URL对象
        }
     },


      // 将sheet转成Blob
      sheetToBlobFunc(sheet) {
        var workbookJson = {
          SheetNames: ["zb_sheet1"],
          Sheets: {},
        };
        workbookJson.Sheets["zb_sheet1"] = sheet;
        // 配置excel的相关属性
        var workbookOptions = {
          bookType: "xlsx", // 要生成的文件类型
          bookSST: false, 
          showGridLines: false,// 是否显示网格线，默认为true
          type: "binary",
        };
        var generateFile = xlsxStyleObj.write(workbookJson, workbookOptions);
        var blobObj = new Blob([this.strToArrayBufferFunc(generateFile)], { type: "application/octet-stream" });
        return blobObj;
      },
      // 字符串转成ArrayBuffer
       strToArrayBufferFunc(str) {
          var buffer = new ArrayBuffer(str.length);
          var view = new Uint8Array(buffer);
          for (var i = 0; i !== str.length; ++i) view[i] = str.charCodeAt(i) & 0xff;
          return buffer;
        },
      // 为excel表格增加样式
      addStyleFunc(xlsx) {
        const sheet = XLSX.utils.aoa_to_sheet(xlsx);// 将JS数据的数组转换为工作表。
        console.log('----sheet---', sheet);// 包含着每行每列的相关信息(v: 单元格的值;t: 单元格的类型，b布尔值、n数字、e错误、s字符串、d日期;s: 单元格的样式)。空行空列不
        var mergeArr = []; // 要合并的单元格数组
        // const rowList = []; // 表格每列高度
        // 单元格边框-自定义边框样式
        const borderCustomer = {
          top: { style: "medium", color:{rgb: "000000"} },
          bottom: { style: "medium", color:{rgb: "000000"} },
          left: { style: "medium", color:{rgb: "000000"} },
          right: { style: "medium", color:{rgb: "000000"} },
        };
        // 单元格边框--去掉边框
        const borderCut = {
          top: { style: "" },
          bottom: { style: "" },
          left: { style: "" },
          right: { style: "" },
        };
  
        for (const key in sheet) {
          if (Object.hasOwnProperty.call(sheet, key)) {
            const element = sheet[key];
            console.log('sheet--', sheet, '---key---', key, '----element--', element);
            if (typeof element === "object") {// v: 单元格的值;t: 单元格的类型，b布尔值、n数字、e错误、s字符串、d日期;s: 单元格的样式
              // slice() 方法可从已有的数组中返回选定的元素。 
              // key如果为A234,那么key.slice(2)，就从第2个索引开始，截到末尾，也就是34  (已验证)
              const index = Number(key.slice(1)) - 1;// 所有同行的，index一样。如A2和B2，得到的index都为1
              console.log('index---', index);
  
              element.s = {
                alignment: {
                  horizontal: "center", // 所有单元格剧中对其
                  vertical: "center", // 所有单元格垂直居中
                },
                font: {
                  name: "黑体",
                  sz: 10,
                  italic: false,
                  underline: false,
                },
                border: borderCustomer,
                fill: {
                  fgColor: { rgb: "FFFFFFFF" },
                },
              };
              
              // if (key.indexOf("H") != -1) {// key中有H，则右对齐。  -1表示没找到H。
              //   element.s.alignment.horizontal = "right";
              // }
              if (index === 0) {
                element.s.font.bold = true;
                element.s.font.sz = 24;
                element.s.border = borderCut;
                element.s.fill.fgColor = { rgb: "ffffff" };
              } else if (index === 1) {
                element.t = 's';
                element.s.alignment.horizontal = 'right';
                element.s.font.sz = 14;
                element.s.font.bold = true;
                element.s.border = borderCut;
              } else if (index === 2) {
                element.s.font.bold = true;
                element.s.fill.fgColor = { rgb: "FFD39B" };
                element.t = 's';
                element.s.font.sz = 13;
                element.s.font.color =  { rgb: "1C1C1C" }; /// FF00FF b8ddb0

              } else if (index === this.allOrderListAll.length + 3) {
                element.s.font.bold = true;
                element.s.font.sz = 20;
                element.s.font.name = 'Courier';
              } else if (index === this.allOrderListAll.length + 4) {
                element.s.font.bold = true;
                element.s.font.sz = 15;
                element.s.font.name = 'Courier';
                element.s.border = borderCut;
              }

  
            }
          }
        }
        let maxColumnNumber = 1; // 默认最大列数为1
        this.globalRowArray.map(item => item.length > maxColumnNumber ? maxColumnNumber = item.length : '');// 遍历每一行，找到最大列数
        console.log('--globalRowArray--', this.globalRowArray.length, '---maxColumnNumber--', maxColumnNumber);
        mergeArr = [
          { s: { c: 0, r: 0 }, e: { c: maxColumnNumber - 1, r: 0 } },
          { s: { c: 0, r: 1 }, e: { c: maxColumnNumber - 2, r: 1 } },
          { s: { c: 0, r: 3 + this.allOrderListAll.length }, e: { c: 1, r: 3 + this.allOrderListAll.length } },
          { s: { c: 0, r: 4 + this.allOrderListAll.length }, e: { c: 1, r: 4 + this.allOrderListAll.length } },
          { s: { c: 2, r: 4 + this.allOrderListAll.length }, e: { c: 3, r: 4 + this.allOrderListAll.length } },
          { s: { c: 4, r: 4 + this.allOrderListAll.length }, e: { c: 5, r: 4 + this.allOrderListAll.length } }
        ];
        sheet["!merges"] = mergeArr;
  
  
        // 单元格的列宽
        sheet['!cols'] = [];
        for(var i = 0;i < 5+this.maxNum*2;i++){
          var json = {wpx:200};
          sheet['!cols'].push(json);
        }
        // 单元格的行宽
        sheet['!rows'] = [];
        for(var i = 0;i < 5+this.allOrderListAll.length;i++){
          if(i == 0){
            var json = {hpx:58};
            sheet['!rows'].push(json);
          }else if(i == 1){
            var json = {hpx:38};
            sheet['!rows'].push(json);
          }else if(i == 2){
            var json = {hpx:32};
            sheet['!rows'].push(json);
          }else if(i == this.allOrderListAll.length + 3){
            var json = {hpx:58};
            sheet['!rows'].push(json);
          }else if(i == this.allOrderListAll.length + 4){
            var json = {hpx:58};
            sheet['!rows'].push(json);
          }else{
            var json = {hpx:26};
            sheet['!rows'].push(json);
          }
        }
        return sheet;
      },
    },
  
    mounted() {
  
  
    },
 
  };
  </script>
  
  
  <style scoped>

  </style>