vue，xlsx，xlsx-style，file-saver，生成Excel并导出，cptable报错，合并单元格 样式缺失

一，安装依赖

 

npm install xlsx@0.18.5 xlsx-style@0.8.13 file-saver@2.0.5 --save

 

二，导入依赖

import * as XLSX from 'xlsx';
import * as XLSX_STYLE from 'xlsx-style'
import {saveAs} from 'file-saver';

三，解决引入xlsx-style ./cptable模块找不到问题

This relative module was not found:

* ./cptable in ./node_modules/xlsx-style/dist/cpexcel.js

 

在vue.config.js中相应位置，添加配置

  configureWebpack: {
    externals: { './cptable': 'var cptable' },
  }

 

四代码

将eltable导出为excel

解决合并表格边框显示不正常的问题

// 导出excel
    exportExcel(show_fenxijieguo_type) {

      // 获取表格的DOM元素
      let elTable = this.$refs.coefTippingTable;
 

      let file_name = '_表格数据.xlsx'

      // 合并工作表
      // const wb = XLSX.utils.book_new();
      const wb = XLSX.utils.table_to_book(elTable);
      let workSheet1 = wb.Sheets.Sheet1

      console.log('-----elTable', elTable)
      console.log('-----workBook', wb)
      console.log('-----workSheet1', workSheet1)

      let borderAll = { // 单元格外侧框线
        top: {
          style: 'thin',
          color: {rgb: '000000'}
        },
        bottom: {
          style: 'thin',
          color: {rgb: '000000'}
        },
        left: {
          style: 'thin',
          color: {rgb: '000000'}
        },
        right: {
          style: 'thin',
          color: {rgb: '000000'}
        }
      }
      let keys = Object.keys(workSheet1)
      keys.sort()
      let newworkSheet1 = {}
      keys.forEach((key, index) => {
        console.log('-----index,key',index,key)
        if (!key.includes('!')) {
          workSheet1[key].s = {
            border: borderAll, // 边框样式设置
            alignment: { // 文字样式设置
              horizontal: 'center', // 字体水平居中
              vertical: 'center', // 垂直居中
              wrapText: 1 // 自动换行
            },
            // fill: { //背景色
            //   fgColor: {rgb: 'C0C0C0'}
            // },
            font: { // 单元格中字体的样式与颜色设置
              sz: 10,
              color: {
                rgb: '000000'
              },
              bold: false
            },
            bold: true,
            numFmt: 0
          }
        }
        if (index != keys.length - 1) {
          newworkSheet1[key] = workSheet1[key]
        }
      })
      workSheet1 = JSON.parse(JSON.stringify(newworkSheet1))

      this.addRangeBorder(workSheet1['!merges'], workSheet1)
      wb.Sheets.Sheet1 = workSheet1


      console.log('-----workSheet1  after delete', workSheet1)
      // XLSX.utils.book_append_sheet(wb, workSheet1, "Sheet1");
      const wbout = XLSX_STYLE.write(
        wb,
        {
          bookType: 'xlsx',
          bookSST: true,
          type: 'binary',
          cellStyles: true,
        });
      function s2ab2(s) {
        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;
      }
      try {
        // 导出Excel
        saveAs(new Blob([s2ab2(wbout)], {type: 'application/octet-stream'}), file_name);
      } catch (e) {
        if (typeof console !== 'undefined') console.error(e, wbout);
      }
      return wbout;

    },
    // 给合并单元格增加样式，边框
    addRangeBorder (range, ws) {
      if (!range || !(range instanceof Array) || range.length == 0){
        console.error('----not merge table or not array')
        return
      }
      // 合并表格，添加边框
      const cols = ['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 => {
        for (let i = item.s.c; i <= item.e.c; i++) {
          const num = Math.floor(i / 26)
          let cellX = ''
          if (num < 1) {
            cellX = cols[i]
          } else {
            cellX = cols[num - 1] + cols[i % 26]
          }
          for (let k = item.s.r + 1; k <= item.e.r + 1; k++) {
            if (!Object.keys(ws).includes(cellX + k)) {
              ws[cellX + k] = {
                // 这一步很关键，需要让单元格的值存在，才能有边框，没有的话单元格还是没边框
                t: 's',
                v: '',
                s: {
                  border: {
                    top: {
                      style: 'thin',
                      color: {rgb: '000000'}
                    },
                    bottom: {
                      style: 'thin',
                      color: {rgb: '000000'}
                    },
                    left: {
                      style: 'thin',
                      color: {rgb: '000000'}
                    },
                    right: {
                      style: 'thin',
                      color: {rgb: '000000'}
                    }
                  }
                }
              }
            }
          }
        }
      })
    },