如何通過xlsx-style 修改導(dǎo)出excel的樣式

實(shí)現(xiàn)導(dǎo)出的excel是帶有邊框的，滿足打印的時候可以盡可能的鋪滿A4紙

安裝 xlsx-style

npm install xlsx-style --save

修改xlsx-style 源碼 解決報(bào)錯

在\node_modules\xlsx-style\dist\cpexcel.js  
var cpt = require('./cpt' + 'able');  改為   var cpt = cptable; 

修改vue中的代碼

完整代碼

import XLSX from "xlsx";
import XLSXS from "xlsx-style";
import FileSaver from "file-saver";
export default {
  name: "excelDemo",
  methods: {
    toExcel() {
      let wb = XLSX.utils.table_to_book(this.$refs.table, { sheet: "分組表" });
      this.setExlStyle(wb["Sheets"]["分組表"]);
      this.addRangeBorder(
        wb["Sheets"]["分組表"]["!merges"],
        wb["Sheets"]["分組表"]
      );
      var ws = XLSXS.write(wb, {
        type: "buffer",
      });
      try {
        FileSaver.saveAs(
          new Blob([ws], { type: "application/octet-stream" }),
          `5.xlsx`
        );
      } catch (e) {
        if (typeof console !== "undefined") console.log(e, ws);
      }
      return ws;
    },
    setExlStyle(data) {
      let borderAll = {
        //單元格外側(cè)框線
        top: {
          style: "thin",
        },
        bottom: {
          style: "thin",
        },
        left: {
          style: "thin",
        },
        right: {
          style: "thin",
        },
      };
      data["!cols"] = [];
      for (let key in data) {
        if (data[key] instanceof Object) {
          data[key].s = {
            border: borderAll,
            alignment: {
              horizontal: "center", //水平居中對齊
              vertical: "center",
            },
            font: {
              sz: 11,
            },
            bold: true,
            numFmt: 0,
          };
          data["!cols"].push({ wpx: 115 });
        }
      }
      return data;
    },
    addRangeBorder(range, ws) {
        // s:起始位置，e:結(jié)束位置
      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 startRowNumber = Number(item.s.r), startColumnNumber = Number(item.s.c),endColumnNumber = Number(item.e.c),
          endRowNumber = Number(item.e.r);
        //   合并單元格時會丟失邊框樣式，例如A1->A4 此時內(nèi)容在A1 而range內(nèi)獲取到的是從0開始的，所以開始行數(shù)要+2
        for (let i = startColumnNumber; i <= endColumnNumber; i++) {
            for(let j = startRowNumber+2 ;j<= endRowNumber+1 ; j++) {
                        console.log(arr[i] + j)
                          ws[arr[i] + j] = {
                            s: {
                            border: {
                                top: { style: "thin" },
                                left: { style: "thin" },
                                bottom: { style: "thin" },
                                right: { style: "thin" },
                            },
                            },
                        };
            }

        }
      });
      console.log(ws);
      return ws;
    },
  },
};